ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
ilDatabaseImplementationBaseTest.php
Go to the documentation of this file.
1<?php
2/*
3 +-----------------------------------------------------------------------------+
4 | ILIAS open source |
5 +-----------------------------------------------------------------------------+
6 | Copyright (c) 1998-2009 ILIAS open source, University of Cologne |
7 | |
8 | This program is free software; you can redistribute it and/or |
9 | modify it under the terms of the GNU General Public License |
10 | as published by the Free Software Foundation; either version 2 |
11 | of the License, or (at your option) any later version. |
12 | |
13 | This program is distributed in the hope that it will be useful, |
14 | but WITHOUT ANY WARRANTY; without even the implied warranty of |
15 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
16 | GNU General Public License for more details. |
17 | |
18 | You should have received a copy of the GNU General Public License |
19 | along with this program; if not, write to the Free Software |
20 | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. |
21 +-----------------------------------------------------------------------------+
22*/
23
31{
32 const INDEX_NAME = 'i1';
33 const TABLE_NAME = 'il_ut_en';
34 const CREATE_TABLE_ARRAY_KEY = 'create table';
42 protected $backupGlobals = false;
46 protected $db;
50 protected $mock;
54 protected $outputs;
58 protected $ildb_backup;
62 protected $type = '';
66 protected $ini_file = '/var/www/ilias/data/trunk/client.ini.php';
70 protected $set_up = false;
71
72
73 protected function setUp()
74 {
75 if ($this->set_up) {
76 return;
77 }
78 // echo phpversion() . "\n";
79 $this->error_reporting_backup = error_reporting();
80 error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_WARNING & ~E_STRICT); // Due to PEAR Lib MDB2
81
82 PHPUnit_Framework_Error_Notice::$enabled = false;
83 PHPUnit_Framework_Error_Deprecated::$enabled = false;
84
85 require_once('./libs/composer/vendor/autoload.php');
86 if (!defined('DEVMODE')) {
87 define('DEVMODE', true);
88 }
89 require_once('./Services/Database/classes/class.ilDBWrapperFactory.php');
90 $this->db = $this->getDBInstance();
91 global $DIC, $ilDB;
92 $DIC['ilDB'] = $this->db;
94 $this->connect($this->db);
95
96 switch ($this->type) {
97 default:
98 require_once('./Services/Database/test/Implementations/data/MySQL/class.ilDatabaseMySQLTestMockData.php');
99 require_once('./Services/Database/test/Implementations/data/MySQL/class.ilDatabaseMySQLTestsDataOutputs.php');
100 $this->mock = new ilDatabaseMySQLTestMockData();
101 $this->outputs = new ilDatabaseMySQLTestsDataOutputs();
102 break;
105 require_once('./Services/Database/test/Implementations/data/Postgres/class.ilDatabasePostgresTestMockData.php');
106 require_once('./Services/Database/test/Implementations/data/Postgres/class.ilDatabasePostgresTestsDataOutputs.php');
107 $this->mock = new ilDatabasePostgresTestMockData();
108 $this->outputs = new ilDatabasePostgresTestsDataOutputs();
109 break;
110 }
111 $this->set_up = true;
112 }
113
114
118 protected function getIniFile()
119 {
120 return $this->ini_file;
121 }
122
123
128 final protected function connect(ilDBInterface $ilDBInterface, $missing_ini = false)
129 {
130 require_once('./Services/Init/classes/class.ilIniFile.php');
131 require_once('./Services/Init/classes/class.ilErrorHandling.php');
132 $ilClientIniFile = new ilIniFile($this->getIniFile());
133 $ilClientIniFile->read();
134 $this->type = $ilClientIniFile->readVariable("db", "type");
135 if ($missing_ini) {
136 $ilClientIniFile = new ilIniFile('');
137 }
138 $ilDBInterface->initFromIniFile($ilClientIniFile);
139 $return = $ilDBInterface->connect($missing_ini);
140
141 return $return;
142 }
143
144
148 protected function getTableName()
149 {
150 return strtolower(self::TABLE_NAME . '_' . $this->db->getDBType());
151 }
152
153
154 protected function tearDown()
155 {
156 $this->db = null;
157 $this->mock = null;
158 $this->outputs = null;
159 $this->type = null;
160 $this->set_up = false;
161 error_reporting($this->error_reporting_backup);
162 }
163
164
169 abstract protected function getDBInstance();
170
171
175 public function testInstance()
176 {
177 $this->assertTrue($this->db instanceof ilDBInterface);
178 }
179
180
181 public function testConnection()
182 {
183 // $this->assertFalse($this->connect($this->getDBInstance(), true)); // Currently MDB2 Postgres doesn't check if connections possible
184 $this->assertTrue($this->connect($this->getDBInstance()));
185 if ($this->db->supportsEngineMigration()) {
186 // $this->db->migrateAllTablesToEngine($this->db->getStorageEngine());
187 }
188 }
189
190
191 public function testCompareCreateTableQueries()
192 {
196 $manager = $this->db->loadModule(ilDBConstants::MODULE_MANAGER);
197 $query = $manager->getTableCreationQuery($this->getTableName(), $this->mock->getDBFields(), array());
198 $this->assertEquals($this->outputs->getCreationQueryBuildByILIAS($this->getTableName()), $this->normalizeSQL($query));
199 }
200
201
205 public function testCreateDatabase()
206 {
207 $fields = $this->mock->getDBFields();
208 $this->db->createTable($this->getTableName(), $fields, true);
209 $this->db->addPrimaryKey($this->getTableName(), array( 'id' ));
210 $this->assertTrue($this->db->tableExists($this->getTableName()));
211
212 if (in_array($this->type, array( ilDBConstants::TYPE_PDO_POSTGRE, ilDBConstants::TYPE_POSTGRES ))) {
213 return; // SHOW CREATE TABLE CURRENTLY NOT SUPPORTED IN POSTGRES
214 }
215
216 $res = $this->db->query('SHOW CREATE TABLE ' . $this->getTableName());
217 $data = $this->db->fetchAssoc($res);
218
219 $data = array_change_key_case($data, CASE_LOWER);
220
221 $create_table = $this->normalizeSQL($data[self::CREATE_TABLE_ARRAY_KEY]);
222 $create_table_mock = $this->normalizeSQL($this->mock->getTableCreateSQL($this->getTableName(), $this->db->getStorageEngine()));
223
224 $this->assertEquals($create_table_mock, $create_table);
225 }
226
227
231 public function testInsertNative()
232 {
233 $values = $this->mock->getInputArray(false, false);
234 $id = $values['id'][1];
235
236 // PDO
237 $this->db->insert($this->getTableName(), $values);
238 $this->db->setLimit(1);
239 $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = $id");
240 $data_pdo = $this->db->fetchAssoc($res_pdo);
242 }
243
244
245 public function testQueryUtils()
246 {
247 $this->assertEquals($this->mock->getLike(), $this->db->like('column', 'text', 22));
248
249 $this->assertEquals($this->mock->getNow(), $this->db->now());
250
251 $this->assertEquals($this->mock->getLocate(), $this->db->locate('needle', 'mystring', 5));
252
253 $this->assertEquals($this->mock->getConcat(false), $this->db->concat(array( 'one', 'two', 'three' ), false));
254
255 $this->assertEquals($this->mock->getConcat(true), $this->db->concat(array( 'one', 'two', 'three' ), true));
256 }
257
258
262 public function testUpdateNative()
263 {
264 // With / without clob
265 $with_clob = $this->mock->getInputArray(2222, true);
266 $without_clob = $this->mock->getInputArray(2222, true, false);
267 $id = $with_clob['id'][1];
268
269 // PDO
270 $this->db->update($this->getTableName(), $with_clob, array( 'id' => array( 'integer', $id ) ));
271 $this->db->setLimit(1, 0);
272 $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = $id ");
273 $data_pdo = $this->db->fetchAssoc($res_pdo);
275
276 $this->db->update($this->getTableName(), $without_clob, array( 'id' => array( 'integer', $id ) ));
277 $this->db->setLimit(1, 0);
278 $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = $id ");
279 $data_pdo = $this->db->fetchAssoc($res_pdo);
281 }
282
283
287 public function testInsertSQL()
288 {
289 // PDO
290 $this->db->manipulate($this->mock->getInsertQuery($this->getTableName()));
291 $this->db->setLimit(1, 0);
292 $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = 58");
293 $data_pdo = $this->db->fetchObject($res_pdo);
294
295 $this->assertEquals((object) ilDatabaseCommonTestsDataOutputs::$insert_sql_output, $data_pdo);
296 }
297
298
303 public function testSelectUsrData()
304 {
306
307 $query = 'SELECT usr_id, login, is_self_registered FROM usr_data WHERE usr_id = 6';
308 // PDO
309 $this->db->setLimit(1, 0);
310 $result = $this->db->query($query);
311 $data = $this->db->fetchObject($result);
312 $this->assertEquals($output, $data);
313
314 $result = $this->db->queryF('SELECT usr_id, login, is_self_registered FROM usr_data WHERE usr_id = %s', array( ilDBPdoFieldDefinition::T_INTEGER ), array( 6 ));
315 $this->db->setLimit(1, 0);
316 $data = $this->db->fetchObject($result);
317 $this->assertEquals($output, $data);
318
319 $query = 'SELECT usr_id, login, is_self_registered FROM usr_data WHERE ' . $this->db->in('usr_id', array( 6, 13 ), false, 'integer');
320 $this->db->setLimit(2, 0);
321 $result = $this->db->query($query);
322 $data = $this->db->fetchAll($result);
324 $this->assertTrue(in_array($item, $data));
325 }
326
327 $this->assertEquals(2, $this->db->numRows($result));
328 }
329
330
334 public function testIndices()
335 {
336 // Add index
337 $this->db->addIndex($this->getTableName(), array( 'init_mob_id' ), self::INDEX_NAME);
338 $this->assertTrue($this->db->indexExistsByFields($this->getTableName(), array( 'init_mob_id' )));
339
340 // Drop index
341 $this->db->dropIndex($this->getTableName(), self::INDEX_NAME);
342 $this->assertFalse($this->db->indexExistsByFields($this->getTableName(), array( 'init_mob_id' )));
343
344 // FullText
345 $this->db->addIndex($this->getTableName(), array( 'address' ), 'i2', true);
346 if ($this->db->supportsFulltext()) {
347 $this->assertTrue($this->db->indexExistsByFields($this->getTableName(), array( 'address' )));
348 } else {
349 $this->assertFalse($this->db->indexExistsByFields($this->getTableName(), array( 'address' )));
350 }
351
352 // Drop By Fields
353 $this->db->addIndex($this->getTableName(), array( 'elevation' ), 'i3');
354 $this->assertTrue($this->db->indexExistsByFields($this->getTableName(), array( 'elevation' )));
355
356 $this->db->dropIndexByFields($this->getTableName(), array( 'elevation' ));
357 $this->assertFalse($this->db->indexExistsByFields($this->getTableName(), array( 'elevation' )));
358 }
359
360
364 public function testTableColums()
365 {
366 $this->assertTrue($this->db->tableColumnExists($this->getTableName(), 'init_mob_id'));
367
368 $this->db->addTableColumn($this->getTableName(), "export", array( "type" => "text", "length" => 1024 ));
369 $this->assertTrue($this->db->tableColumnExists($this->getTableName(), 'export'));
370
371 $this->db->dropTableColumn($this->getTableName(), "export");
372 $this->assertFalse($this->db->tableColumnExists($this->getTableName(), 'export'));
373 }
374
375
379 public function testSequences()
380 {
381 if ($this->db->sequenceExists($this->getTableName())) {
382 $this->db->dropSequence($this->getTableName());
383 }
384 $this->db->createSequence($this->getTableName(), 10);
385 $this->assertEquals(10, $this->db->nextId($this->getTableName()));
386 $this->assertEquals(11, $this->db->nextId($this->getTableName()));
387 }
388
389
393 public function testReverse()
394 {
398 $reverse = $this->db->loadModule(ilDBConstants::MODULE_REVERSE);
399
400 // getTableFieldDefinition
401 $this->assertEquals($this->outputs->getTableFieldDefinition(), $reverse->getTableFieldDefinition($this->getTableName(), 'comment_mob_id'));
402
403 // getTableIndexDefinition
404 $this->db->addIndex($this->getTableName(), array( 'init_mob_id' ), self::INDEX_NAME);
405 $tableIndexDefinition = $reverse->getTableIndexDefinition($this->getTableName(), $this->db->constraintName($this->getTableName(), self::INDEX_NAME));
406 $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$table_index_definition_output, $tableIndexDefinition);
407 $this->db->dropIndex($this->getTableName(), self::INDEX_NAME);
408
409 // getTableConstraintDefinition
410 $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$table_constraint_definition_output, $reverse->getTableConstraintDefinition($this->getTableName(), $this->db->constraintName($this->getTableName(), strtolower($this->db->getPrimaryKeyIdentifier()))));
411 }
412
413
417 public function testManager()
418 {
422 $manager = $this->db->loadModule(ilDBConstants::MODULE_MANAGER);
423
424 // table fields
425 $this->assertEquals($this->outputs->getTableFields(), $manager->listTableFields($this->getTableName()));
426
427 // constraints
428 $this->assertEquals($this->outputs->getTableConstraints($this->getTableName()), $manager->listTableConstraints($this->getTableName()));
429
430 // Indices
431 $this->db->dropIndexByFields($this->getTableName(), array( 'init_mob_id' ));
432 $this->db->addIndex($this->getTableName(), array( 'init_mob_id' ), self::INDEX_NAME);
433 $this->assertEquals($this->outputs->getNativeTableIndices($this->getTableName(), $this->db->supportsFulltext()), $manager->listTableIndexes($this->getTableName()));
434
435 // listTables
436 $list_tables_output = $this->outputs->getListTables($this->getTableName());
437 sort($list_tables_output);
438 $list_tables_native = $manager->listTables();
439 sort($list_tables_native);
440 $this->assertEquals($list_tables_output, $list_tables_native);
441
442 // listSequences
443 $table_sequences_output = $this->outputs->getTableSequences($this->getTableName());
444 $this->assertTrue(count(array_diff($table_sequences_output, $manager->listSequences())) < 3);
445 }
446
447
451 public function testDBAnalyser()
452 {
453 require_once('./Services/Database/classes/class.ilDBAnalyzer.php');
454 $analyzer = new ilDBAnalyzer($this->db);
455
456 // Field info
457 // $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$analyzer_field_info, $analyzer_pdo->getFieldInformation($this->getTableName())); // FSX
458
459 // getBestDefinitionAlternative
460 $def = $this->db->loadModule(ilDBConstants::MODULE_REVERSE)->getTableFieldDefinition($this->getTableName(), 'comment_mob_id');
461 $this->assertEquals(0, $analyzer->getBestDefinitionAlternative($def)); // FSX
462
463 // getAutoIncrementField
464 $this->assertEquals(false, $analyzer->getAutoIncrementField($this->getTableName()));
465
466 // getPrimaryKeyInformation
467 $this->assertEquals($this->outputs->getPrimaryInfo($this->getTableName()), $analyzer->getPrimaryKeyInformation($this->getTableName()));
468
469 // getIndicesInformation
470 if ($this->db->supportsFulltext()) {
471 $this->assertEquals($this->outputs->getIndexInfo(true, $this->getTableName()), $analyzer->getIndicesInformation($this->getTableName()));
472 } else {
473 $this->assertEquals($this->outputs->getIndexInfo(false, $this->getTableName()), $analyzer->getIndicesInformation($this->getTableName()));
474 }
475
476 // getConstraintsInformation
477 $this->assertEquals(array(), $analyzer->getConstraintsInformation($this->getTableName())); // TODO
478
479 // hasSequence
480 $this->assertEquals(59, $analyzer->hasSequence($this->getTableName()));
481 }
482
483
487 public function testDropSequence()
488 {
489 $this->assertTrue($this->db->sequenceExists($this->getTableName()));
490 if ($this->db->sequenceExists($this->getTableName())) {
491 $this->db->dropSequence($this->getTableName());
492 }
493 $this->assertFalse($this->db->sequenceExists($this->getTableName()));
494 }
495
496
497 public function testConstraints()
498 {
499 }
500
501
505 public function testChangeTableName()
506 {
507 $this->db->dropTable($this->getTableName() . '_a', false);
508 $this->db->renameTable($this->getTableName(), $this->getTableName() . '_a');
509 $this->assertTrue($this->db->tableExists($this->getTableName() . '_a'));
510 $this->db->renameTable($this->getTableName() . '_a', $this->getTableName());
511 }
512
513
517 public function testRenameTableColumn()
518 {
519 $this->changeGlobal($this->db);
520
521 $this->db->renameTableColumn($this->getTableName(), 'comment_mob_id', 'comment_mob_id_altered');
522 if (in_array($this->type, array( ilDBConstants::TYPE_PDO_POSTGRE, ilDBConstants::TYPE_POSTGRES ))) {
523 return; // SHOW CREATE TABLE CURRENTLY NOT SUPPORTED IN POSTGRES
524 }
525 $res = $this->db->query('SHOW CREATE TABLE ' . $this->getTableName());
526 $data = $this->db->fetchAssoc($res);
527 $data = array_change_key_case($data, CASE_LOWER);
528
529 $this->assertEquals($this->normalizeSQL($this->mock->getTableCreateSQLAfterRename($this->getTableName(), $this->db->getStorageEngine(), $this->db->supportsFulltext())), $this->normalizeSQL($data[self::CREATE_TABLE_ARRAY_KEY]));
530
531 $this->changeBack();
532 }
533
534
538 public function testModifyTableColumn()
539 {
540 $changes = array(
541 "type" => "text",
542 "length" => 250,
543 "notnull" => false,
544 'fixed' => false,
545 );
546
547 $this->changeGlobal($this->db);
548
549 $this->db->modifyTableColumn($this->getTableName(), 'comment_mob_id_altered', $changes);
550 if (in_array($this->type, array( ilDBConstants::TYPE_PDO_POSTGRE, ilDBConstants::TYPE_POSTGRES ))) {
551 return; // SHOW CREATE TABLE CURRENTLY NOT SUPPORTED IN POSTGRES
552 }
553 $res = $this->db->query('SHOW CREATE TABLE ' . $this->getTableName());
554 $data = $this->db->fetchAssoc($res);
555
556 $data = array_change_key_case($data, CASE_LOWER);
557
558 $this->changeBack();
559
560 $this->assertEquals($this->normalizeSQL($this->mock->getTableCreateSQLAfterAlter($this->getTableName(), $this->db->getStorageEngine(), $this->db->supportsFulltext())), $this->normalizeSQL($data[self::CREATE_TABLE_ARRAY_KEY]));
561 }
562
563
567 public function testLockTables()
568 {
569 $locks = array(
570 0 => array( 'name' => 'usr_data', 'type' => ilDBConstants::LOCK_WRITE ),
571 // 1 => array( 'name' => 'object_data', 'type' => ilDBConstants::LOCK_READ ),
572 );
573
574 $this->db->lockTables($locks);
575 $this->db->manipulate('DELETE FROM usr_data WHERE usr_id = -1');
576 $this->db->unlockTables();
577 }
578
579
583 public function testTransactions()
584 {
585 // PDO
586 // $this->db->beginTransaction();
587 // $this->db->insert($this->getTableName(), $this->mock->getInputArrayForTransaction());
588 // $this->db->rollback();
589 // $st = $this->db->query('SELECT * FROM ' . $this->getTableName() . ' WHERE id = ' . $this->db->quote(123456, 'integer'));
590 // $this->assertEquals(0, $this->db->numRows($st));
591 }
592
593
597 public function testDropTable()
598 {
599 $this->db->dropTable($this->getTableName());
600 $this->assertTrue(!$this->db->tableExists($this->getTableName()));
601 }
602
603 //
604 // HELPERS
605 //
606
610 protected function changeGlobal(ilDBInterface $ilDBInterface)
611 {
612 global $ilDB;
613 $this->ildb_backup = $ilDB;
614 $ilDB = $ilDBInterface;
615 }
616
617
618 protected function changeBack()
619 {
620 global $ilDB;
622 }
623
624
629 protected function normalizeSQL($sql)
630 {
631 return preg_replace('/[ \t]+/', ' ', preg_replace('/\s*$^\s*/m', " ", preg_replace("/\n/", "", preg_replace("/`/", "", $sql))));
632 }
633
634
639 protected function normalizetableName($sql)
640 {
641 return preg_replace("/" . $this->getTableName() . "|" . $this->getTableName() . "/", "table_name_replaced", $sql);
642 }
643}
$result
An exception for terminatinating execution or to throw for unit testing.
This class gives all kind of DB information using the MDB2 manager and reverse module.
TestCase for the ilDatabaseCommonTest.
testInstance()
Test instance implements ilDBInterface and is ilDBInnoDB.
connect(ilDBInterface $ilDBInterface, $missing_ini=false)
Class ilDatabaseCommonTestMockData.
Class ilDatabaseCommonTestsDataOutputs.
INIFile Parser.
$def
Definition: croninfo.php:21
if(!array_key_exists('StateId', $_REQUEST)) $id
if(!is_dir( $entity_dir)) exit("Fatal Error ([A-Za-z0-9]+)\s+" &#(? foreach( $entity_files as $file) $output
Interface ilDBInterface.
initFromIniFile($tmpClientIniFile=null)
connect($return_false_on_error=false)
defined( 'APPLICATION_ENV')||define( 'APPLICATION_ENV'
Definition: bootstrap.php:27
$query
global $DIC
Definition: saml.php:7
foreach($_POST as $key=> $value) $res
global $ilDB