ilDatabaseImplementationBaseTest.php

Go to the documentation of this file.

<?php
/*
        +-----------------------------------------------------------------------------+
        | ILIAS open source                                                           |
        +-----------------------------------------------------------------------------+
        | Copyright (c) 1998-2009 ILIAS open source, University of Cologne            |
        |                                                                             |
        | This program is free software; you can redistribute it and/or               |
        | modify it under the terms of the GNU General Public License                 |
        | as published by the Free Software Foundation; either version 2              |
        | of the License, or (at your option) any later version.                      |
        |                                                                             |
        | This program is distributed in the hope that it will be useful,             |
        | but WITHOUT ANY WARRANTY; without even the implied warranty of              |
        | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the               |
        | GNU General Public License for more details.                                |
        |                                                                             |
        | You should have received a copy of the GNU General Public License           |
        | along with this program; if not, write to the Free Software                 |
        | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA. |
        +-----------------------------------------------------------------------------+
*/

abstract class ilDatabaseImplementationBaseTest extends PHPUnit_Framework_TestCase {

        const INDEX_NAME = 'i1';
        const TABLE_NAME = 'il_ut_en';
        const CREATE_TABLE_ARRAY_KEY = 'create table';
        protected $error_reporting_backup = 0;
        protected $backupGlobals = false;
        protected $db;
        protected $mock;
        protected $outputs;
        protected $ildb_backup;
        protected $type = '';
        protected $ini_file = '/var/www/ilias/data/trunk/client.ini.php';
        protected $set_up = false;


        protected function setUp() {
                if ($this->set_up) {
                        return;
                }
                //              echo phpversion() . "\n";
                $this->error_reporting_backup = error_reporting();
                error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_WARNING & ~E_STRICT); // Due to PEAR Lib MDB2

                PHPUnit_Framework_Error_Notice::$enabled = false;
                PHPUnit_Framework_Error_Deprecated::$enabled = false;

                require_once('./libs/composer/vendor/autoload.php');
                if (!defined('DEVMODE')) {
                        define('DEVMODE', true);
                }
                require_once('./Services/Database/classes/class.ilDBWrapperFactory.php');
                $this->db = $this->getDBInstance();
                global $DIC, $ilDB;
                $DIC['ilDB'] = $this->db;
                $ilDB = $this->db;
                $this->connect($this->db);

                switch ($this->type) {
                        default:
                                require_once('./Services/Database/test/Implementations/data/MySQL/class.ilDatabaseMySQLTestMockData.php');
                                require_once('./Services/Database/test/Implementations/data/MySQL/class.ilDatabaseMySQLTestsDataOutputs.php');
                                $this->mock = new ilDatabaseMySQLTestMockData();
                                $this->outputs = new ilDatabaseMySQLTestsDataOutputs();
                                break;
                        case ilDBConstants::TYPE_POSTGRES:
                        case ilDBConstants::TYPE_PDO_POSTGRE:
                                require_once('./Services/Database/test/Implementations/data/Postgres/class.ilDatabasePostgresTestMockData.php');
                                require_once('./Services/Database/test/Implementations/data/Postgres/class.ilDatabasePostgresTestsDataOutputs.php');
                                $this->mock = new ilDatabasePostgresTestMockData();
                                $this->outputs = new ilDatabasePostgresTestsDataOutputs();
                                break;
                }
                $this->set_up = true;
        }


        protected function getIniFile() {
                return $this->ini_file;
        }


        protected final function connect(ilDBInterface $ilDBInterface, $missing_ini = false) {
                require_once('./Services/Init/classes/class.ilIniFile.php');
                require_once('./Services/Init/classes/class.ilErrorHandling.php');
                $ilClientIniFile = new ilIniFile($this->getIniFile());
                $ilClientIniFile->read();
                $this->type = $ilClientIniFile->readVariable("db", "type");
                if ($missing_ini) {
                        $ilClientIniFile = new ilIniFile('');
                }
                $ilDBInterface->initFromIniFile($ilClientIniFile);
                $return = $ilDBInterface->connect($missing_ini);

                return $return;
        }


        protected function getTableName() {
                return strtolower(self::TABLE_NAME . '_' . $this->db->getDBType());
        }


        protected function tearDown() {
                $this->db = null;
                $this->mock = null;
                $this->outputs = null;
                $this->type = null;
                $this->set_up = false;
                error_reporting($this->error_reporting_backup);
        }


        abstract protected function getDBInstance();


        public function testInstance() {
                $this->assertTrue($this->db instanceof ilDBInterface);
        }


        public function testConnection() {
                //              $this->assertFalse($this->connect($this->getDBInstance(), true)); // Currently MDB2 Postgres doesn't check if connections possible
                $this->assertTrue($this->connect($this->getDBInstance()));
                if ($this->db->supportsEngineMigration()) {
                        //                      $this->db->migrateAllTablesToEngine($this->db->getStorageEngine());
                }
        }


        public function testCompareCreateTableQueries() {
                $manager = $this->db->loadModule(ilDBConstants::MODULE_MANAGER);
                $query = $manager->getTableCreationQuery($this->getTableName(), $this->mock->getDBFields(), array());
                $this->assertEquals($this->outputs->getCreationQueryBuildByILIAS($this->getTableName()), $this->normalizeSQL($query));
        }


        public function testCreateDatabase() {
                $fields = $this->mock->getDBFields();
                $this->db->createTable($this->getTableName(), $fields, true);
                $this->db->addPrimaryKey($this->getTableName(), array( 'id' ));
                $this->assertTrue($this->db->tableExists($this->getTableName()));

                if (in_array($this->type, array( ilDBConstants::TYPE_PDO_POSTGRE, ilDBConstants::TYPE_POSTGRES ))) {
                        return; // SHOW CREATE TABLE CURRENTLY NOT SUPPORTED IN POSTGRES
                }

                $res = $this->db->query('SHOW CREATE TABLE ' . $this->getTableName());
                $data = $this->db->fetchAssoc($res);

                $data = array_change_key_case($data, CASE_LOWER);

                $create_table = $this->normalizeSQL($data[self::CREATE_TABLE_ARRAY_KEY]);
                $create_table_mock = $this->normalizeSQL($this->mock->getTableCreateSQL($this->getTableName(), $this->db->getStorageEngine()));

                $this->assertEquals($create_table_mock, $create_table);
        }


        public function testInsertNative() {
                $values = $this->mock->getInputArray(false, false);
                $id = $values['id'][1];

                // PDO
                $this->db->insert($this->getTableName(), $values);
                $this->db->setLimit(1);
                $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = $id");
                $data_pdo = $this->db->fetchAssoc($res_pdo);
                $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$output_after_native_input, $data_pdo);
        }


        public function testQueryUtils() {
                $this->assertEquals($this->mock->getLike(), $this->db->like('column', 'text', 22));

                $this->assertEquals($this->mock->getNow(), $this->db->now());

                $this->assertEquals($this->mock->getLocate(), $this->db->locate('needle', 'mystring', 5));

                $this->assertEquals($this->mock->getConcat(false), $this->db->concat(array( 'one', 'two', 'three' ), false));

                $this->assertEquals($this->mock->getConcat(true), $this->db->concat(array( 'one', 'two', 'three' ), true));
        }


        public function testUpdateNative() {
                // With / without clob
                $with_clob = $this->mock->getInputArray(2222, true);
                $without_clob = $this->mock->getInputArray(2222, true, false);
                $id = $with_clob['id'][1];

                // PDO
                $this->db->update($this->getTableName(), $with_clob, array( 'id' => array( 'integer', $id ) ));
                $this->db->setLimit(1, 0);
                $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = $id ");
                $data_pdo = $this->db->fetchAssoc($res_pdo);
                $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$output_after_native_update, $data_pdo);

                $this->db->update($this->getTableName(), $without_clob, array( 'id' => array( 'integer', $id ) ));
                $this->db->setLimit(1, 0);
                $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = $id ");
                $data_pdo = $this->db->fetchAssoc($res_pdo);
                $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$output_after_native_update, $data_pdo);
        }


        public function testInsertSQL() {
                // PDO
                $this->db->manipulate($this->mock->getInsertQuery($this->getTableName()));
                $this->db->setLimit(1, 0);
                $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = 58");
                $data_pdo = $this->db->fetchObject($res_pdo);

                $this->assertEquals((object)ilDatabaseCommonTestsDataOutputs::$insert_sql_output, $data_pdo);
        }


        public function testSelectUsrData() {
                $output = (object)ilDatabaseCommonTestsDataOutputs::$select_usr_data_output;

                $query = 'SELECT usr_id, login, is_self_registered FROM usr_data WHERE usr_id = 6';
                // PDO
                $this->db->setLimit(1, 0);
                $result = $this->db->query($query);
                $data = $this->db->fetchObject($result);
                $this->assertEquals($output, $data);

                $result = $this->db->queryF('SELECT usr_id, login, is_self_registered FROM usr_data WHERE usr_id = %s', array( ilDBPdoFieldDefinition::T_INTEGER ), array( 6 ));
                $this->db->setLimit(1, 0);
                $data = $this->db->fetchObject($result);
                $this->assertEquals($output, $data);

                $query = 'SELECT usr_id, login, is_self_registered FROM usr_data WHERE ' . $this->db->in('usr_id', array( 6, 13 ), false, 'integer');
                $this->db->setLimit(2, 0);
                $result = $this->db->query($query);
                $data = $this->db->fetchAll($result);
                foreach (ilDatabaseCommonTestsDataOutputs::$select_usr_data_2_output as $item) {
                        $this->assertTrue(in_array($item, $data));
                }

                $this->assertEquals(2, $this->db->numRows($result));
        }


        public function testIndices() {
                // Add index
                $this->db->addIndex($this->getTableName(), array( 'init_mob_id' ), self::INDEX_NAME);
                $this->assertTrue($this->db->indexExistsByFields($this->getTableName(), array( 'init_mob_id' )));

                // Drop index
                $this->db->dropIndex($this->getTableName(), self::INDEX_NAME);
                $this->assertFalse($this->db->indexExistsByFields($this->getTableName(), array( 'init_mob_id' )));

                // FullText
                $this->db->addIndex($this->getTableName(), array( 'address' ), 'i2', true);
                if ($this->db->supportsFulltext()) {
                        $this->assertTrue($this->db->indexExistsByFields($this->getTableName(), array( 'address' )));
                } else {
                        $this->assertFalse($this->db->indexExistsByFields($this->getTableName(), array( 'address' )));
                }

                // Drop By Fields
                $this->db->addIndex($this->getTableName(), array( 'elevation' ), 'i3');
                $this->assertTrue($this->db->indexExistsByFields($this->getTableName(), array( 'elevation' )));

                $this->db->dropIndexByFields($this->getTableName(), array( 'elevation' ));
                $this->assertFalse($this->db->indexExistsByFields($this->getTableName(), array( 'elevation' )));
        }


        public function testTableColums() {
                $this->assertTrue($this->db->tableColumnExists($this->getTableName(), 'init_mob_id'));

                $this->db->addTableColumn($this->getTableName(), "export", array( "type" => "text", "length" => 1024 ));
                $this->assertTrue($this->db->tableColumnExists($this->getTableName(), 'export'));

                $this->db->dropTableColumn($this->getTableName(), "export");
                $this->assertFalse($this->db->tableColumnExists($this->getTableName(), 'export'));
        }


        public function testSequences() {
                if ($this->db->sequenceExists($this->getTableName())) {
                        $this->db->dropSequence($this->getTableName());
                }
                $this->db->createSequence($this->getTableName(), 10);
                $this->assertEquals(10, $this->db->nextId($this->getTableName()));
                $this->assertEquals(11, $this->db->nextId($this->getTableName()));
        }


        public function testReverse() {
                $reverse = $this->db->loadModule(ilDBConstants::MODULE_REVERSE);

                // getTableFieldDefinition
                $this->assertEquals($this->outputs->getTableFieldDefinition(), $reverse->getTableFieldDefinition($this->getTableName(), 'comment_mob_id'));

                // getTableIndexDefinition
                $this->db->addIndex($this->getTableName(), array( 'init_mob_id' ), self::INDEX_NAME);
                $tableIndexDefinition = $reverse->getTableIndexDefinition($this->getTableName(), $this->db->constraintName($this->getTableName(), self::INDEX_NAME));
                $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$table_index_definition_output, $tableIndexDefinition);
                $this->db->dropIndex($this->getTableName(), self::INDEX_NAME);

                // getTableConstraintDefinition
                $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$table_constraint_definition_output, $reverse->getTableConstraintDefinition($this->getTableName(), $this->db->constraintName($this->getTableName(), strtolower($this->db->getPrimaryKeyIdentifier()))));
        }


        public function testManager() {
                $manager = $this->db->loadModule(ilDBConstants::MODULE_MANAGER);

                // table fields
                $this->assertEquals($this->outputs->getTableFields(), $manager->listTableFields($this->getTableName()));

                // constraints
                $this->assertEquals($this->outputs->getTableConstraints($this->getTableName()), $manager->listTableConstraints($this->getTableName()));

                // Indices
                $this->db->dropIndexByFields($this->getTableName(), array( 'init_mob_id' ));
                $this->db->addIndex($this->getTableName(), array( 'init_mob_id' ), self::INDEX_NAME);
                $this->assertEquals($this->outputs->getNativeTableIndices($this->getTableName(), $this->db->supportsFulltext()), $manager->listTableIndexes($this->getTableName()));

                // listTables
                $list_tables_output = $this->outputs->getListTables($this->getTableName());
                sort($list_tables_output);
                $list_tables_native = $manager->listTables();
                sort($list_tables_native);
                $this->assertEquals($list_tables_output, $list_tables_native);

                // listSequences
                $table_sequences_output = $this->outputs->getTableSequences($this->getTableName());
                $this->assertTrue(count(array_diff($table_sequences_output, $manager->listSequences())) < 3);
        }


        public function testDBAnalyser() {
                require_once('./Services/Database/classes/class.ilDBAnalyzer.php');
                $analyzer = new ilDBAnalyzer($this->db);

                // Field info
                //              $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$analyzer_field_info, $analyzer_pdo->getFieldInformation($this->getTableName())); // FSX

                // getBestDefinitionAlternative
                $def = $this->db->loadModule(ilDBConstants::MODULE_REVERSE)->getTableFieldDefinition($this->getTableName(), 'comment_mob_id');
                $this->assertEquals(0, $analyzer->getBestDefinitionAlternative($def)); // FSX

                // getAutoIncrementField
                $this->assertEquals(false, $analyzer->getAutoIncrementField($this->getTableName()));

                // getPrimaryKeyInformation
                $this->assertEquals($this->outputs->getPrimaryInfo($this->getTableName()), $analyzer->getPrimaryKeyInformation($this->getTableName()));

                // getIndicesInformation
                if ($this->db->supportsFulltext()) {
                        $this->assertEquals($this->outputs->getIndexInfo(true, $this->getTableName()), $analyzer->getIndicesInformation($this->getTableName()));
                } else {
                        $this->assertEquals($this->outputs->getIndexInfo(false, $this->getTableName()), $analyzer->getIndicesInformation($this->getTableName()));
                }

                // getConstraintsInformation
                $this->assertEquals(array(), $analyzer->getConstraintsInformation($this->getTableName())); // TODO

                // hasSequence
                $this->assertEquals(59, $analyzer->hasSequence($this->getTableName()));
        }


        public function testDropSequence() {
                $this->assertTrue($this->db->sequenceExists($this->getTableName()));
                if ($this->db->sequenceExists($this->getTableName())) {
                        $this->db->dropSequence($this->getTableName());
                }
                $this->assertFalse($this->db->sequenceExists($this->getTableName()));
        }


        public function testConstraints() {

        }


        public function testChangeTableName() {
                $this->db->dropTable($this->getTableName() . '_a', false);
                $this->db->renameTable($this->getTableName(), $this->getTableName() . '_a');
                $this->assertTrue($this->db->tableExists($this->getTableName() . '_a'));
                $this->db->renameTable($this->getTableName() . '_a', $this->getTableName());
        }


        public function testRenameTableColumn() {
                $this->changeGlobal($this->db);

                $this->db->renameTableColumn($this->getTableName(), 'comment_mob_id', 'comment_mob_id_altered');
                if (in_array($this->type, array( ilDBConstants::TYPE_PDO_POSTGRE, ilDBConstants::TYPE_POSTGRES ))) {
                        return; // SHOW CREATE TABLE CURRENTLY NOT SUPPORTED IN POSTGRES
                }
                $res = $this->db->query('SHOW CREATE TABLE ' . $this->getTableName());
                $data = $this->db->fetchAssoc($res);
                $data = array_change_key_case($data, CASE_LOWER);

                $this->assertEquals($this->normalizeSQL($this->mock->getTableCreateSQLAfterRename($this->getTableName(), $this->db->getStorageEngine(), $this->db->supportsFulltext())), $this->normalizeSQL($data[self::CREATE_TABLE_ARRAY_KEY]));

                $this->changeBack();
        }


        public function testModifyTableColumn() {
                $changes = array(
                        "type"    => "text",
                        "length"  => 250,
                        "notnull" => false,
                        'fixed'   => false,
                );

                $this->changeGlobal($this->db);

                $this->db->modifyTableColumn($this->getTableName(), 'comment_mob_id_altered', $changes);
                if (in_array($this->type, array( ilDBConstants::TYPE_PDO_POSTGRE, ilDBConstants::TYPE_POSTGRES ))) {
                        return; // SHOW CREATE TABLE CURRENTLY NOT SUPPORTED IN POSTGRES
                }
                $res = $this->db->query('SHOW CREATE TABLE ' . $this->getTableName());
                $data = $this->db->fetchAssoc($res);

                $data = array_change_key_case($data, CASE_LOWER);

                $this->changeBack();

                $this->assertEquals($this->normalizeSQL($this->mock->getTableCreateSQLAfterAlter($this->getTableName(), $this->db->getStorageEngine(), $this->db->supportsFulltext())), $this->normalizeSQL($data[self::CREATE_TABLE_ARRAY_KEY]));
        }


        public function testLockTables() {
                $locks = array(
                        0 => array( 'name' => 'usr_data', 'type' => ilDBConstants::LOCK_WRITE ),
                        //                      1 => array( 'name' => 'object_data', 'type' => ilDBConstants::LOCK_READ ),
                );

                $this->db->lockTables($locks);
                $this->db->manipulate('DELETE FROM usr_data WHERE usr_id = -1');
                $this->db->unlockTables();
        }


        public function testTransactions() {
                // PDO
                //              $this->db->beginTransaction();
                //              $this->db->insert($this->getTableName(), $this->mock->getInputArrayForTransaction());
                //              $this->db->rollback();
                //              $st = $this->db->query('SELECT * FROM ' . $this->getTableName() . ' WHERE id = ' . $this->db->quote(123456, 'integer'));
                //              $this->assertEquals(0, $this->db->numRows($st));
        }


        public function testDropTable() {
                $this->db->dropTable($this->getTableName());
                $this->assertTrue(!$this->db->tableExists($this->getTableName()));
        }

        //
        // HELPERS
        //

        protected function changeGlobal(ilDBInterface $ilDBInterface) {
                global $ilDB;
                $this->ildb_backup = $ilDB;
                $ilDB = $ilDBInterface;
        }


        protected function changeBack() {
                global $ilDB;
                $ilDB = $this->ildb_backup;
        }


        protected function normalizeSQL($sql) {
                return preg_replace('/[ \t]+/', ' ', preg_replace('/\s*$^\s*/m', " ", preg_replace("/\n/", "", preg_replace("/`/", "", $sql))));
        }


        protected function normalizetableName($sql) {
                return preg_replace("/" . $this->getTableName() . "|" . $this->getTableName() . "/", "table_name_replaced", $sql);
        }
}