Subversion Repositories oidplus

#!/usr/bin/env php

<?php

/*

 * OIDplus 2.0

 * Copyright 2019 - 2022 Daniel Marschall, ViaThinkSoft

 *

 * Licensed under the Apache License, Version 2.0 (the "License");

 * you may not use this file except in compliance with the License.

 * You may obtain a copy of the License at

 *

 *     http://www.apache.org/licenses/LICENSE-2.0

 *

 * Unless required by applicable law or agreed to in writing, software

 * distributed under the License is distributed on an "AS IS" BASIS,

 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

 * See the License for the specific language governing permissions and

 * limitations under the License.

 */

// This script is used to detect problems with your database plugins

// ATTENTION: PLEASE DO NOT USE THIS SCRIPT ON A PRODUCTIVE DATABASE,

// BECAUSE IT ADDS AND CHANGES DATA DURING THE TESTING.

# ---

// TODO: UTF-8 tests

require_once __DIR__ . '/../includes/oidplus.inc.php';

use ViaThinkSoft\OIDplus\OIDplus;

use ViaThinkSoft\OIDplus\OIDplusDatabaseConnection;

$num_errs = 0;

$num_succ = 0;

if (PHP_SAPI != 'cli') echo '<pre>';

echo "OIDplus Database plugin testcases\n\n";

OIDplus::init(true, true);

# Test MySQL

try {

	OIDplus::baseConfig()->setValue('DATABASE_PLUGIN', 'MySQL');

	OIDplus::init(true, true);

	$db = OIDplus::db();

	OIDplus::init(true, true);

	if (function_exists('mysqli_fetch_all')) {

		OIDplus::baseConfig()->setValue('MYSQL_FORCE_MYSQLND_SUPPLEMENT', false);

		echo "[With MySQLnd support] ";

		dotest($db);

		OIDplus::baseConfig()->setValue('MYSQL_FORCE_MYSQLND_SUPPLEMENT', true);

	}

	echo "[Without MySQLnd support] ";

	dotest($db);

} catch (Exception $e) {

	echo "MySQL:\n";

	echo redtext($e->getMessage())."\n\n";

}

# Test PDO

try {

	OIDplus::baseConfig()->setValue('DATABASE_PLUGIN', 'PDO');

	OIDplus::init(true, true);

	$db = OIDplus::db();

	dotest($db);

} catch (Exception $e) {

	echo "PDO:\n";

	echo redtext($e->getMessage())."\n\n";

}

# Test ODBC

try {

	OIDplus::baseConfig()->setValue('DATABASE_PLUGIN', 'ODBC');

	OIDplus::init(true, true);

	$db = OIDplus::db();

	dotest($db);

} catch (Exception $e) {

	echo "ODBC:\n";

	echo redtext($e->getMessage())."\n\n";

}

# Test ADO

try {

	OIDplus::baseConfig()->setValue('DATABASE_PLUGIN', 'ADO');

	OIDplus::init(true, true);

	$db = OIDplus::db();

	dotest($db);

} catch (Exception $e) {

	echo "ADO:\n";

	echo redtext($e->getMessage())."\n\n";

}

# Test PgSQL

try {

	OIDplus::baseConfig()->setValue('DATABASE_PLUGIN', 'PgSQL');

	OIDplus::init(true, true);

	$db = OIDplus::db();

	dotest($db);

} catch (Exception $e) {

	echo "PgSQL:\n";

	echo redtext($e->getMessage())."\n\n";

}

# Test SQLite3

try {

	OIDplus::baseConfig()->setValue('DATABASE_PLUGIN', 'SQLite3');

	OIDplus::init(true, true);

	$db = OIDplus::db();

	dotest($db);

} catch (Exception $e) {

	echo "SQLite3:\n";

	echo redtext($e->getMessage())."\n\n";

}

# Test OCI8

try {

	OIDplus::baseConfig()->setValue('DATABASE_PLUGIN', 'Oracle (OCI8)');

	OIDplus::init(true, true);

	$db = OIDplus::db();

	dotest($db);

} catch (Exception $e) {

	echo "OCI8:\n";

	echo redtext($e->getMessage())."\n\n";

}

# ---

if (PHP_SAPI != 'cli') {

	die('</pre>');

} else {

	exit($num_errs > 0 ? 1 : 0);

}

# ---

/**

 * @param OIDplusDatabaseConnection $db

 * @return void

 */

function dotest(OIDplusDatabaseConnection $db) {

	echo "Database: " . get_class($db) . "\n";

	try {

		$db->connect();

	} catch (Exception $e) {

		echo "Connection ".redtext("FAILED")." (check userdata/baseconfig/config.inc.php): ".$e->getMessage()."\n\n";

		return;

	}

	echo "Detected slang: " . $db->getSlang()::id()."\n";

	$db->query("delete from ###objects where parent = 'test:1'");

	$db->query("insert into ###objects (id, parent, title, description, confidential) values ('test:1.1', 'test:1', '', '', '0')");

	$db->query("insert into ###objects (id, parent, title, description, confidential) values ('test:1.2', 'test:1', '', '', '0')");

	try {

		// --- "SQL Date" handling

		try {

			$res = $db->query("update ###objects set created = ".$db->sqlDate()." where id = 'test:1.1'");

			echo "SQLDate (".$db->sqlDate().') '.greentext('PASSED')."\n";

		} catch (Exception $e) {

			echo "SQLDate (".$db->sqlDate().') '.redtext('FAILED')."\n";

		}

		// --- Delete+Insert using prepared statements

		$db->query("delete from ###objects where parent = ?", array('test:11'));

		echo "Delete using Prepared Statements: ".greentext('PASSED')."\n";

		$db->query("insert into ###objects (id, parent, title, description, confidential) values (?, ?, ?, ?, ?)", array('oid:3.1.11', 'test:11', '', '', '0'));

		$db->query("insert into ###objects (id, parent, title, description, confidential) values (?, ?, ?, ?, ?)", array('oid:3.1.12', 'test:11', '', '', '0'));

		echo "Insert using Prepared Statements: ".greentext('PASSED')."\n";

		$db->query("update ###objects set confidential = ? where parent = ?", array(true, 'test:11'));

		echo "Update using Prepared Statements: ".greentext('PASSED')."\n";

		// --- "Num rows" handling

		$res = $db->query("select id from ###objects where parent = ? order by id", array('test:XXXXXXXXX'));

		try {

			$num_rows = $res->num_rows();

		} catch (Exception $e) {

			$num_rows = $e->getMessage();

		}

		echo "Num rows empty: " . ($num_rows===0 ? greentext('PASSED') : redtext('FAILED'))." ($num_rows)\n";

		$res = $db->query("select id from ###objects where parent = ? order by id", array('test:1'));

		try {

			$num_rows = $res->num_rows();

		} catch (Exception $e) {

			$num_rows = $e->getMessage();

		}

		echo "Num rows: " . ($num_rows===2 ? greentext('PASSED') : redtext('FAILED'))." ($num_rows)\n";

		$res->fetch_array();

		try {

			$num_rows = $res->num_rows();

		} catch (Exception $e) {

			$num_rows = $e->getMessage();

		}

		echo "Num rows after something fetched: " . ($num_rows===2 ? greentext('PASSED') : redtext('FAILED'))." ($num_rows)\n";

		$nextid = $res->fetch_array()['id'];

		echo "Num rows does not change cursor: " . ($nextid == 'test:1.2' ? greentext('PASSED') : redtext('FAILED'))."\n";

		$next = $res->fetch_array();

		echo "Fetch after EOF gives null: " . (is_null($next) ? greentext('PASSED') : redtext('FAILED'))."\n";

		// --- Simultanous prepared statements

		$errmsg = '';

		try {

			$res = $db->query("select id from ###objects where parent = ? order by id", array('test:1'));

			$passed = false;

			//$res->num_rows(); // sponge

			while ($row = $res->fetch_array()) {

				$res2 = $db->query("select id from ###objects where parent = ? order by id", array($row['id']));

				while ($row2 = $res2->fetch_array()) {

				}

				if ($row['id'] == 'test:1.2') {

					$passed = true;

				}

			}

		} catch (Exception $e) {

			$passed = false;

			$errmsg = ' ('.$e->getMessage().')';

		}

		echo "Simultanous prepared statements: ".($passed ? greentext('PASSED') : redtext('FAILED'))."$errmsg\n";

		if (!$passed) {

			// We cannot continue because the connection is in a state that cannot be recovered easily

			echo "Cancelled!\n\n";

			return;

		}

		// --- Exception handling

		try {

			$db->query("SELECT * from ABCDEF");

			echo "Exception for DirectQuery: ".redtext('FAILED').", no Exception thrown\n";

		} catch (Exception $e) {

			if ((stripos($e->getMessage(), 'ABCDEF') !== false) || is_known_errormsg($e->getMessage())) {

				echo "Exception for DirectQuery: ".greentext('PASSED')."\n";

			} else {

				echo "Exception for DirectQuery: ".redtext('FAILED').", does probably not contain DBMS error string (".$e->getMessage().")\n";

			}

		}

		$msg = $db->error();

		if ((stripos($msg, 'ABCDEF') !== false) || is_known_errormsg($msg)) {

			echo "Error-Function after failed direct query: ".greentext('PASSED')."\n";

		} else {

			echo "Error-Function after failed direct query: ".redtext('FAILED').", does probably not contain DBMS error string ($msg)\n";

		}

		try {

			$db->query("SELECT * from FEDCBA", array());

			echo "Exception for PreparedQuery: ".redtext('FAILED').", no Exception thrown\n";

		} catch (Exception $e) {

			if ((stripos($e->getMessage(), 'FEDCBA') !== false) || is_known_errormsg($e->getMessage())) {

				echo "Exception for PreparedQuery: ".greentext('PASSED')."\n";

			} else {

				echo "Exception for PreparedQuery: ".redtext('FAILED').", does probably not contain DBMS error string (".$e->getMessage().")\n";

			}

		}

		$msg = $db->error();

		if ((stripos($msg, 'FEDCBA') !== false) || is_known_errormsg($msg)) {

			echo "Error-Function after failed prepared query: ".greentext('PASSED')."\n";

		} else {

			echo "Error-Function after failed prepared query: ".redtext('FAILED').", does probably not contain DBMS error string ($msg)\n";

		}

		$db->query("select 1");

		$msg = $db->error();

		if (!$msg) {

			echo "Error-Function gets cleared after non-failed query: ".greentext('PASSED')."\n";

		} else {

			echo "Error-Function gets cleared after non-failed query: ".redtext('FAILED').", does probably not contain DBMS error string\n";

		}

		// --- Boolean handling

		$db->query("update ###objects set confidential = ? where id = 'test:1.1'", array(true));

		$res = $db->query("select confidential from ###objects where id = 'test:1.1'");

		$val = $res->fetch_object()->confidential;

		echo "Boolean handling TRUE with prepared statement (fetch): " . ($val ? greentext('PASSED') : redtext('FAILED'))."\n";

		$res = $db->query("select * from ###objects where id = 'test:1.1' and confidential = ?", array(true));

		$val = $res->fetch_object();

		echo "Boolean handling TRUE with prepared statement (where): " . ($val ? greentext('PASSED') : redtext('FAILED'))."\n";

		$db->query("update ###objects set confidential = ? where id = 'test:1.1'", array(false));

		$res = $db->query("select confidential from ###objects where id = 'test:1.1'");

		$val = $res->fetch_object()->confidential;

		echo "Boolean handling FALSE with prepared statement (fetch): " . (!$val ? greentext('PASSED') : redtext('FAILED'))."\n";

		$res = $db->query("select * from ###objects where id = 'test:1.1' and confidential = ?", array(false));

		$val = $res->fetch_object();

		echo "Boolean handling FALSE with prepared statement (where): " . ($val ? greentext('PASSED') : redtext('FAILED'))."\n";

		$db->query("update ###objects set confidential = '1' where id = 'test:1.1'"); // NOTE: DO NOT USE THIS IN THE SOURCE CODE! Always use prepared statements for constant booleans

		$res = $db->query("select confidential from ###objects where id = 'test:1.1'");

		$val = $res->fetch_object()->confidential;

		echo "Boolean handling TRUE with normal statement (fetch): " . ($val ? greentext('PASSED') : redtext('FAILED'))."\n";

		if ($db->getSlang()::id() == 'access') {

			// Note: For Access, it must be 1, not '1'

			$res = $db->query("select * from ###objects where id = 'test:1.1' and confidential = 1"); // NOTE: DO NOT USE THIS IN THE SOURCE CODE! Always use prepared statements for constant booleans

		} else {

			// Note: For PgSQL, it must be '1', true, 'true', 't', but not 1

			$res = $db->query("select * from ###objects where id = 'test:1.1' and confidential = '1'"); // NOTE: DO NOT USE THIS IN THE SOURCE CODE! Always use prepared statements for constant booleans

		}

		$val = $res->fetch_object();

		echo "Boolean handling TRUE with normal statement (where): " . ($val ? greentext('PASSED') : redtext('FAILED'))."\n";

		$db->query("update ###objects set confidential = '0' where id = 'test:1.1'"); // NOTE: DO NOT USE THIS IN THE SOURCE CODE! Always use prepared statements for constant booleans

		$res = $db->query("select confidential from ###objects where id = 'test:1.1'");

		$val = $res->fetch_object()->confidential;

		echo "Boolean handling FALSE with normal statement (fetch): " . (!$val ? greentext('PASSED') : redtext('FAILED'))."\n";

		if ($db->getSlang()::id() == 'access') {

			// Note: For Access, it must be 0, not '0'

			$res = $db->query("select * from ###objects where id = 'test:1.1' and confidential = 0"); // NOTE: DO NOT USE THIS IN THE SOURCE CODE! Always use prepared statements for constant booleans

		} else {

			// Note: For PgSQL, it must be '0', false, 'false', 'f', but not 0

			$res = $db->query("select * from ###objects where id = 'test:1.1' and confidential = '0'"); // NOTE: DO NOT USE THIS IN THE SOURCE CODE! Always use prepared statements for constant booleans

		}

		$val = $res->fetch_object();

		echo "Boolean handling FALSE with normal statement (where): " . ($val ? greentext('PASSED') : redtext('FAILED'))."\n";

		// --- Check if transactions work

		if ($db->transaction_supported()) {

			echo "Transaction support: " . greentext('Supported') ."\n";

			$db->query("update ###objects set title = 'A' where id = 'test:1.1'");

			$db->transaction_begin();

			$db->query("update ###objects set title = 'B' where id = 'test:1.1'");

			$db->transaction_rollback();

			$res = $db->query("select title from ###objects where id = 'test:1.1'");

			$val = $res->fetch_object()->title;

			echo "Transaction rollback: " . ($val == 'A' ? greentext('PASSED') : redtext('FAILED'))."\n";

			$db->query("update ###objects set title = 'A' where id = 'test:1.1'");

			$db->transaction_begin();

			$db->query("update ###objects set title = 'B' where id = 'test:1.1'");

			$db->transaction_commit();

			$res = $db->query("select title from ###objects where id = 'test:1.1'");

			$val = $res->fetch_object()->title;

			echo "Transaction commit: " . ($val == 'B' ? greentext('PASSED') : redtext('FAILED'))."\n";

		} else {

			echo "Transaction support: " . redtext('Not supported') ."\n";

		}

		// --- Test insert_id()

		$db->query("delete from ###log_object where object = 'test:1'");

		$cur = $db->insert_id();

		echo "Insert ID on non-insert: " . ($cur == 0 ? greentext('PASSED') : redtext('FAILED'))." ($cur)\n";

		$db->query("insert into ###log_object (log_id, severity, object) values (1000, 0, 'test:1')");

		$prev = $db->insert_id();

		$db->query("insert into ###log_object (log_id, severity, object) values (2000, 1, 'test:1')");

		$cur = $db->insert_id();

		echo "Insert ID on actual inserts: " . ($cur == $prev+1 ? greentext('PASSED') : redtext('FAILED'))." ($prev => $cur)\n";

		if ($cur != $prev+1);

		$db->query("delete from ###log_object where object = 'test:1'");

		$cur = $db->insert_id();

		echo "Non-Insert query will reset insert ID: " . ($cur == 0 ? greentext('PASSED') : redtext('FAILED'))." ($cur)\n";

	} finally {

		try {

			$db->query("delete from ###objects where parent = 'test:1'");

		}catch(Exception $e) {}

	}

	$db->disconnect();

	echo "\n";

}

/**

 * @param string $str

 * @return string

 */

function redtext(string $str): string {

	global $num_errs;

	$num_errs++;

	return PHP_SAPI == 'cli' ? "\033[31m$str\033[0m" : '<font color="red">'.$str.'</font>';

}

/**

 * @param string $str

 * @return string

 */

function greentext(string $str): string {

	global $num_succ;

	$num_succ++;

	return PHP_SAPI == 'cli' ? "\033[32m$str\033[0m" : '<font color="green">'.$str.'</font>';

}

/**

 * @param string $msg

 * @return bool

 */

function is_known_errormsg(string $msg): bool {

	// Oracle:

	//Error-Function after failed direct query:

	//	==> OCIStmtExecute: ORA-00942: table or view does not exist  (ext\pdo_oci\oci_statement.c:155)

	//Error-Function after failed prepared query:

	//	==> OCIBindByPos: ORA-01036: illegal variable name/number (ext\pdo_oci\oci_statement.c:346)

	return strpos($msg,'ORA-') !== false;

}