ILIAS  release_5-4 Revision v5.4.26-12-gabc799a52e6
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
81 PHPUnit_Framework_Error_Notice::$enabled = false;
82 PHPUnit_Framework_Error_Deprecated::$enabled = false;
83
84 require_once('./libs/composer/vendor/autoload.php');
85 if (!defined('DEVMODE')) {
86 define('DEVMODE', true);
87 }
88 require_once('./Services/Database/classes/class.ilDBWrapperFactory.php');
89 $this->db = $this->getDBInstance();
90 global $DIC, $ilDB;
91 $DIC['ilDB'] = $this->db;
93 $this->connect($this->db);
94
95 switch ($this->type) {
96 default:
97 require_once('./Services/Database/test/Implementations/data/MySQL/class.ilDatabaseMySQLTestMockData.php');
98 require_once('./Services/Database/test/Implementations/data/MySQL/class.ilDatabaseMySQLTestsDataOutputs.php');
99 $this->mock = new ilDatabaseMySQLTestMockData();
100 $this->outputs = new ilDatabaseMySQLTestsDataOutputs();
101 break;
104 require_once('./Services/Database/test/Implementations/data/Postgres/class.ilDatabasePostgresTestMockData.php');
105 require_once('./Services/Database/test/Implementations/data/Postgres/class.ilDatabasePostgresTestsDataOutputs.php');
106 $this->mock = new ilDatabasePostgresTestMockData();
107 $this->outputs = new ilDatabasePostgresTestsDataOutputs();
108 break;
109 }
110 $this->set_up = true;
111 }
112
113
117 protected function getIniFile()
118 {
119 return $this->ini_file;
120 }
121
122
127 final protected function connect(ilDBInterface $ilDBInterface, $missing_ini = false)
128 {
129 require_once('./Services/Init/classes/class.ilIniFile.php');
130 require_once('./Services/Init/classes/class.ilErrorHandling.php');
131 $ilClientIniFile = new ilIniFile($this->getIniFile());
132 $ilClientIniFile->read();
133 $this->type = $ilClientIniFile->readVariable("db", "type");
134 if ($missing_ini) {
135 $ilClientIniFile = new ilIniFile('');
136 }
137 $ilDBInterface->initFromIniFile($ilClientIniFile);
138 $return = $ilDBInterface->connect($missing_ini);
139
140 return $return;
141 }
142
143
147 protected function getTableName()
148 {
149 return strtolower(self::TABLE_NAME . '_' . $this->db->getDBType());
150 }
151
152
153 protected function tearDown()
154 {
155 $this->db = null;
156 $this->mock = null;
157 $this->outputs = null;
158 $this->type = null;
159 $this->set_up = false;
160 error_reporting($this->error_reporting_backup);
161 }
162
163
168 abstract protected function getDBInstance();
169
170
174 public function testInstance()
175 {
176 $this->assertTrue($this->db instanceof ilDBInterface);
177 }
178
179
180 public function testConnection()
181 {
182 $this->assertTrue($this->connect($this->getDBInstance()));
183 }
184
185
186 public function testCompareCreateTableQueries()
187 {
191 $manager = $this->db->loadModule(ilDBConstants::MODULE_MANAGER);
192 $query = $manager->getTableCreationQuery($this->getTableName(), $this->mock->getDBFields(), array());
193 $this->assertEquals($this->outputs->getCreationQueryBuildByILIAS($this->getTableName()), $this->normalizeSQL($query));
194 }
195
196
200 public function testCreateDatabase()
201 {
202 $fields = $this->mock->getDBFields();
203 $this->db->createTable($this->getTableName(), $fields, true);
204 $this->db->addPrimaryKey($this->getTableName(), array( 'id' ));
205 $this->assertTrue($this->db->tableExists($this->getTableName()));
206
207 if (in_array($this->type, array( ilDBConstants::TYPE_PDO_POSTGRE, ilDBConstants::TYPE_POSTGRES ))) {
208 return; // SHOW CREATE TABLE CURRENTLY NOT SUPPORTED IN POSTGRES
209 }
210
211 $res = $this->db->query('SHOW CREATE TABLE ' . $this->getTableName());
212 $data = $this->db->fetchAssoc($res);
213
214 $data = array_change_key_case($data, CASE_LOWER);
215
216 $create_table = $this->normalizeSQL($data[self::CREATE_TABLE_ARRAY_KEY]);
217 $create_table_mock = $this->normalizeSQL($this->mock->getTableCreateSQL($this->getTableName(), $this->db->getStorageEngine()));
218
219 $this->assertEquals($create_table_mock, $create_table);
220 }
221
222
226 public function testInsertNative()
227 {
228 $values = $this->mock->getInputArray(false, false);
229 $id = $values['id'][1];
230
231 // PDO
232 $this->db->insert($this->getTableName(), $values);
233 $this->db->setLimit(1);
234 $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = $id");
235 $data_pdo = $this->db->fetchAssoc($res_pdo);
237 }
238
239
240 public function testQueryUtils()
241 {
242 $this->assertEquals($this->mock->getLike(), $this->db->like('column', 'text', 22));
243
244 $this->assertEquals($this->mock->getNow(), $this->db->now());
245
246 $this->assertEquals($this->mock->getLocate(), $this->db->locate('needle', 'mystring', 5));
247
248 $this->assertEquals($this->mock->getConcat(false), $this->db->concat(array( 'one', 'two', 'three' ), false));
249
250 $this->assertEquals($this->mock->getConcat(true), $this->db->concat(array( 'one', 'two', 'three' ), true));
251 }
252
253
257 public function testUpdateNative()
258 {
259 // With / without clob
260 $with_clob = $this->mock->getInputArray(2222, true);
261 $without_clob = $this->mock->getInputArray(2222, true, false);
262 $id = $with_clob['id'][1];
263
264 // PDO
265 $this->db->update($this->getTableName(), $with_clob, array( 'id' => array( 'integer', $id ) ));
266 $this->db->setLimit(1, 0);
267 $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = $id ");
268 $data_pdo = $this->db->fetchAssoc($res_pdo);
270
271 $this->db->update($this->getTableName(), $without_clob, array( 'id' => array( 'integer', $id ) ));
272 $this->db->setLimit(1, 0);
273 $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = $id ");
274 $data_pdo = $this->db->fetchAssoc($res_pdo);
276 }
277
278
282 public function testInsertSQL()
283 {
284 // PDO
285 $this->db->manipulate($this->mock->getInsertQuery($this->getTableName()));
286 $this->db->setLimit(1, 0);
287 $res_pdo = $this->db->query("SELECT * FROM " . $this->getTableName() . " WHERE id = 58");
288 $data_pdo = $this->db->fetchObject($res_pdo);
289
290 $this->assertEquals((object) ilDatabaseCommonTestsDataOutputs::$insert_sql_output, $data_pdo);
291 }
292
293
298 public function testSelectUsrData()
299 {
301
302 $query = 'SELECT usr_id, login, is_self_registered FROM usr_data WHERE usr_id = 6';
303 // PDO
304 $this->db->setLimit(1, 0);
305 $result = $this->db->query($query);
306 $data = $this->db->fetchObject($result);
307 $this->assertEquals($output, $data);
308
309 $result = $this->db->queryF('SELECT usr_id, login, is_self_registered FROM usr_data WHERE usr_id = %s', array( ilDBPdoFieldDefinition::T_INTEGER ), array( 6 ));
310 $this->db->setLimit(1, 0);
311 $data = $this->db->fetchObject($result);
312 $this->assertEquals($output, $data);
313
314 $query = 'SELECT usr_id, login, is_self_registered FROM usr_data WHERE ' . $this->db->in('usr_id', array( 6, 13 ), false, 'integer');
315 $this->db->setLimit(2, 0);
316 $result = $this->db->query($query);
317 $data = $this->db->fetchAll($result);
319 $this->assertTrue(in_array($item, $data));
320 }
321
322 $this->assertEquals(2, $this->db->numRows($result));
323 }
324
325
329 public function testIndices()
330 {
331 // Add index
332 $this->db->addIndex($this->getTableName(), array( 'init_mob_id' ), self::INDEX_NAME);
333 $this->assertTrue($this->db->indexExistsByFields($this->getTableName(), array( 'init_mob_id' )));
334
335 // Drop index
336 $this->db->dropIndex($this->getTableName(), self::INDEX_NAME);
337 $this->assertFalse($this->db->indexExistsByFields($this->getTableName(), array( 'init_mob_id' )));
338
339 // FullText
340 $this->db->addIndex($this->getTableName(), array( 'address' ), 'i2', true);
341 if ($this->db->supportsFulltext()) {
342 $this->assertTrue($this->db->indexExistsByFields($this->getTableName(), array( 'address' )));
343 } else {
344 $this->assertFalse($this->db->indexExistsByFields($this->getTableName(), array( 'address' )));
345 }
346
347 // Drop By Fields
348 $this->db->addIndex($this->getTableName(), array( 'elevation' ), 'i3');
349 $this->assertTrue($this->db->indexExistsByFields($this->getTableName(), array( 'elevation' )));
350
351 $this->db->dropIndexByFields($this->getTableName(), array( 'elevation' ));
352 $this->assertFalse($this->db->indexExistsByFields($this->getTableName(), array( 'elevation' )));
353 }
354
355
359 public function testTableColums()
360 {
361 $this->assertTrue($this->db->tableColumnExists($this->getTableName(), 'init_mob_id'));
362
363 $this->db->addTableColumn($this->getTableName(), "export", array( "type" => "text", "length" => 1024 ));
364 $this->assertTrue($this->db->tableColumnExists($this->getTableName(), 'export'));
365
366 $this->db->dropTableColumn($this->getTableName(), "export");
367 $this->assertFalse($this->db->tableColumnExists($this->getTableName(), 'export'));
368 }
369
370
374 public function testSequences()
375 {
376 if ($this->db->sequenceExists($this->getTableName())) {
377 $this->db->dropSequence($this->getTableName());
378 }
379 $this->db->createSequence($this->getTableName(), 10);
380 $this->assertEquals(10, $this->db->nextId($this->getTableName()));
381 $this->assertEquals(11, $this->db->nextId($this->getTableName()));
382 }
383
384
388 public function testReverse()
389 {
393 $reverse = $this->db->loadModule(ilDBConstants::MODULE_REVERSE);
394
395 // getTableFieldDefinition
396 $this->assertEquals($this->outputs->getTableFieldDefinition(), $reverse->getTableFieldDefinition($this->getTableName(), 'comment_mob_id'));
397
398 // getTableIndexDefinition
399 $this->db->addIndex($this->getTableName(), array( 'init_mob_id' ), self::INDEX_NAME);
400 $tableIndexDefinition = $reverse->getTableIndexDefinition($this->getTableName(), $this->db->constraintName($this->getTableName(), self::INDEX_NAME));
401 $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$table_index_definition_output, $tableIndexDefinition);
402 $this->db->dropIndex($this->getTableName(), self::INDEX_NAME);
403
404 // getTableConstraintDefinition
405 $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$table_constraint_definition_output, $reverse->getTableConstraintDefinition($this->getTableName(), $this->db->constraintName($this->getTableName(), strtolower($this->db->getPrimaryKeyIdentifier()))));
406 }
407
408
412 public function testManager()
413 {
417 $manager = $this->db->loadModule(ilDBConstants::MODULE_MANAGER);
418
419 // table fields
420 $this->assertEquals($this->outputs->getTableFields(), $manager->listTableFields($this->getTableName()));
421
422 // constraints
423 $this->assertEquals($this->outputs->getTableConstraints($this->getTableName()), $manager->listTableConstraints($this->getTableName()));
424
425 // Indices
426 $this->db->dropIndexByFields($this->getTableName(), array( 'init_mob_id' ));
427 $this->db->addIndex($this->getTableName(), array( 'init_mob_id' ), self::INDEX_NAME);
428 $this->assertEquals($this->outputs->getNativeTableIndices($this->getTableName(), $this->db->supportsFulltext()), $manager->listTableIndexes($this->getTableName()));
429
430 // listTables
431 $list_tables_output = $this->outputs->getListTables($this->getTableName());
432 sort($list_tables_output);
433 $list_tables_native = $manager->listTables();
434 sort($list_tables_native);
435 $this->assertEquals($list_tables_output, $list_tables_native);
436
437 // listSequences
438 $table_sequences_output = $this->outputs->getTableSequences($this->getTableName());
439 $this->assertTrue(count(array_diff($table_sequences_output, $manager->listSequences())) < 3);
440 }
441
442
446 public function testDBAnalyser()
447 {
448 require_once('./Services/Database/classes/class.ilDBAnalyzer.php');
449 $analyzer = new ilDBAnalyzer($this->db);
450
451 // Field info
452 // $this->assertEquals(ilDatabaseCommonTestsDataOutputs::$analyzer_field_info, $analyzer_pdo->getFieldInformation($this->getTableName())); // FSX
453
454 // getBestDefinitionAlternative
455 $def = $this->db->loadModule(ilDBConstants::MODULE_REVERSE)->getTableFieldDefinition($this->getTableName(), 'comment_mob_id');
456 $this->assertEquals(0, $analyzer->getBestDefinitionAlternative($def)); // FSX
457
458 // getAutoIncrementField
459 $this->assertEquals(false, $analyzer->getAutoIncrementField($this->getTableName()));
460
461 // getPrimaryKeyInformation
462 $this->assertEquals($this->outputs->getPrimaryInfo($this->getTableName()), $analyzer->getPrimaryKeyInformation($this->getTableName()));
463
464 // getIndicesInformation
465 if ($this->db->supportsFulltext()) {
466 $this->assertEquals($this->outputs->getIndexInfo(true, $this->getTableName()), $analyzer->getIndicesInformation($this->getTableName()));
467 } else {
468 $this->assertEquals($this->outputs->getIndexInfo(false, $this->getTableName()), $analyzer->getIndicesInformation($this->getTableName()));
469 }
470
471 // getConstraintsInformation
472 $this->assertEquals(array(), $analyzer->getConstraintsInformation($this->getTableName())); // TODO
473
474 // hasSequence
475 $this->assertEquals(59, $analyzer->hasSequence($this->getTableName()));
476 }
477
478
482 public function testDropSequence()
483 {
484 $this->assertTrue($this->db->sequenceExists($this->getTableName()));
485 if ($this->db->sequenceExists($this->getTableName())) {
486 $this->db->dropSequence($this->getTableName());
487 }
488 $this->assertFalse($this->db->sequenceExists($this->getTableName()));
489 }
490
491
492 public function testConstraints()
493 {
494 }
495
496
500 public function testChangeTableName()
501 {
502 $this->db->dropTable($this->getTableName() . '_a', false);
503 $this->db->renameTable($this->getTableName(), $this->getTableName() . '_a');
504 $this->assertTrue($this->db->tableExists($this->getTableName() . '_a'));
505 $this->db->renameTable($this->getTableName() . '_a', $this->getTableName());
506 }
507
508
512 public function testRenameTableColumn()
513 {
514 $this->changeGlobal($this->db);
515
516 $this->db->renameTableColumn($this->getTableName(), 'comment_mob_id', 'comment_mob_id_altered');
517 if (in_array($this->type, array( ilDBConstants::TYPE_PDO_POSTGRE, ilDBConstants::TYPE_POSTGRES ))) {
518 return; // SHOW CREATE TABLE CURRENTLY NOT SUPPORTED IN POSTGRES
519 }
520 $res = $this->db->query('SHOW CREATE TABLE ' . $this->getTableName());
521 $data = $this->db->fetchAssoc($res);
522 $data = array_change_key_case($data, CASE_LOWER);
523
524 $this->assertEquals($this->normalizeSQL($this->mock->getTableCreateSQLAfterRename($this->getTableName(), $this->db->getStorageEngine(), $this->db->supportsFulltext())), $this->normalizeSQL($data[self::CREATE_TABLE_ARRAY_KEY]));
525
526 $this->changeBack();
527 }
528
529
533 public function testModifyTableColumn()
534 {
535 $changes = array(
536 "type" => "text",
537 "length" => 250,
538 "notnull" => false,
539 'fixed' => false,
540 );
541
542 $this->changeGlobal($this->db);
543
544 $this->db->modifyTableColumn($this->getTableName(), 'comment_mob_id_altered', $changes);
545 if (in_array($this->type, array( ilDBConstants::TYPE_PDO_POSTGRE, ilDBConstants::TYPE_POSTGRES ))) {
546 return; // SHOW CREATE TABLE CURRENTLY NOT SUPPORTED IN POSTGRES
547 }
548 $res = $this->db->query('SHOW CREATE TABLE ' . $this->getTableName());
549 $data = $this->db->fetchAssoc($res);
550
551 $data = array_change_key_case($data, CASE_LOWER);
552
553 $this->changeBack();
554
555 $this->assertEquals($this->normalizeSQL($this->mock->getTableCreateSQLAfterAlter($this->getTableName(), $this->db->getStorageEngine(), $this->db->supportsFulltext())), $this->normalizeSQL($data[self::CREATE_TABLE_ARRAY_KEY]));
556 }
557
558
562 public function testLockTables()
563 {
564 $locks = array(
565 0 => array( 'name' => 'usr_data', 'type' => ilDBConstants::LOCK_WRITE ),
566 // 1 => array( 'name' => 'object_data', 'type' => ilDBConstants::LOCK_READ ),
567 );
568
569 $this->db->lockTables($locks);
570 $this->db->manipulate('DELETE FROM usr_data WHERE usr_id = -1');
571 $this->db->unlockTables();
572 }
573
574
578 public function testTransactions()
579 {
580 // PDO
581 // $this->db->beginTransaction();
582 // $this->db->insert($this->getTableName(), $this->mock->getInputArrayForTransaction());
583 // $this->db->rollback();
584 // $st = $this->db->query('SELECT * FROM ' . $this->getTableName() . ' WHERE id = ' . $this->db->quote(123456, 'integer'));
585 // $this->assertEquals(0, $this->db->numRows($st));
586 }
587
588
592 public function testDropTable()
593 {
594 $this->db->dropTable($this->getTableName());
595 $this->assertTrue(!$this->db->tableExists($this->getTableName()));
596 }
597
598 //
599 // HELPERS
600 //
601
605 protected function changeGlobal(ilDBInterface $ilDBInterface)
606 {
607 global $ilDB;
608 $this->ildb_backup = $ilDB;
609 $ilDB = $ilDBInterface;
610 }
611
612
613 protected function changeBack()
614 {
615 global $ilDB;
617 }
618
619
624 protected function normalizeSQL($sql)
625 {
626 return preg_replace('/[ \t]+/', ' ', preg_replace('/\s*$^\s*/m', " ", preg_replace("/\n/", "", preg_replace("/`/", "", $sql))));
627 }
628
629
634 protected function normalizetableName($sql)
635 {
636 return preg_replace("/" . $this->getTableName() . "|" . $this->getTableName() . "/", "table_name_replaced", $sql);
637 }
638}
$result
An exception for terminatinating execution or to throw for unit testing.
This class gives all kind of DB information using the database 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
Interface ilDBInterface.
initFromIniFile($tmpClientIniFile=null)
connect($return_false_on_error=false)
$query
global $DIC
Definition: saml.php:7
foreach($_POST as $key=> $value) $res
global $ilDB
$values
$data
Definition: bench.php:6