WebSVN – oidplus – /trunk/dev/test_database_plugins

#!/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);

OIDplus::baseConfig()->delete('FORCE_DBMS_SLANG');

# 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";
        echo redtext($e->getTraceAsString())."\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";
        echo redtext($e->getTraceAsString())."\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";
        echo redtext($e->getTraceAsString())."\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";
        echo redtext($e->getTraceAsString())."\n\n";
}

# Test SQLSRV
try {
        OIDplus::baseConfig()->setValue('DATABASE_PLUGIN', 'SQLSRV');
        OIDplus::init(true, true);
        $db = OIDplus::db();
        dotest($db);
} catch (Exception $e) {
        echo "ADO:\n";
        echo redtext($e->getMessage())."\n";
        echo redtext($e->getTraceAsString())."\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";
        echo redtext($e->getTraceAsString())."\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";
        echo redtext($e->getTraceAsString())."\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";
        echo redtext($e->getTraceAsString())."\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;
}
Subversion Repositories oidplus

oidplus/trunk/dev/test_database_plugins – Rev 1238
