2 /*********************************************************************************
3 * SugarCRM Community Edition is a customer relationship management program developed by
4 * SugarCRM, Inc. Copyright (C) 2004-2013 SugarCRM Inc.
6 * This program is free software; you can redistribute it and/or modify it under
7 * the terms of the GNU Affero General Public License version 3 as published by the
8 * Free Software Foundation with the addition of the following permission added
9 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
10 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
11 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
13 * This program is distributed in the hope that it will be useful, but WITHOUT
14 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
18 * You should have received a copy of the GNU Affero General Public License along with
19 * this program; if not, see http://www.gnu.org/licenses or write to the Free
20 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
24 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
26 * The interactive user interfaces in modified source and object code versions
27 * of this program must display Appropriate Legal Notices, as required under
28 * Section 5 of the GNU Affero General Public License version 3.
30 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
31 * these Appropriate Legal Notices must retain the display of the "Powered by
32 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
33 * technical reasons, the Appropriate Legal Notices must display the words
34 * "Powered by SugarCRM".
35 ********************************************************************************/
38 require_once 'include/database/DBManagerFactory.php';
39 require_once 'modules/Contacts/Contact.php';
40 require_once 'tests/include/database/TestBean.php';
42 class DBManagerTest extends Sugar_PHPUnit_Framework_TestCase
48 protected $created = array();
50 protected $backupGlobals = FALSE;
52 static public function setupBeforeClass()
54 $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
55 $GLOBALS['app_strings'] = return_application_language($GLOBALS['current_language']);
58 static public function tearDownAfterClass()
60 SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
61 unset($GLOBALS['current_user']);
62 unset($GLOBALS['app_strings']);
65 public function setUp()
67 if(empty($this->_db)){
68 $this->_db = DBManagerFactory::getInstance();
70 $this->created = array();
73 public function tearDown()
75 foreach($this->created as $table => $dummy) {
76 $this->_db->dropTableName($table);
80 protected function createTableParams($tablename, $fieldDefs, $indices)
82 $this->created[$tablename] = true;
83 return $this->_db->createTableParams($tablename, $fieldDefs, $indices);
86 protected function dropTableName($tablename)
88 unset($this->created[$tablename]);
89 return $this->_db->dropTableName($tablename);
92 private function _createRecords(
97 for ( $i = 0; $i < $num; $i++ ) {
98 $bean = new Contact();
99 $bean->id = "$i-test" . mt_rand();
100 $bean->last_name = "foobar";
101 $this->_db->insert($bean);
102 $beanIds[] = $bean->id;
108 private function _removeRecords(
112 foreach ($ids as $id)
113 $this->_db->query("DELETE From contacts where id = '{$id}'");
116 public function testGetDatabase()
118 if ( $this->_db instanceOf MysqliManager )
119 $this->assertInstanceOf('Mysqli',$this->_db->getDatabase());
121 $this->assertTrue(is_resource($this->_db->getDatabase()));
124 public function testCheckError()
126 $this->assertFalse($this->_db->checkError());
127 $this->assertFalse($this->_db->lastError());
130 public function testCheckErrorNoConnection()
132 $this->_db->disconnect();
133 $this->assertTrue($this->_db->checkError());
134 $this->_db = DBManagerFactory::getInstance();
137 public function testGetQueryTime()
139 $this->_db->version();
140 $this->assertTrue($this->_db->getQueryTime() > 0);
143 public function testCheckConnection()
145 $this->_db->checkConnection();
146 if ( $this->_db instanceOf MysqliManager )
147 $this->assertInstanceOf('Mysqli',$this->_db->getDatabase());
149 $this->assertTrue(is_resource($this->_db->getDatabase()));
152 public function testInsert()
154 $bean = new Contact();
155 $bean->last_name = 'foobar' . mt_rand();
156 $bean->id = 'test' . mt_rand();
157 $this->_db->insert($bean);
159 $result = $this->_db->query("select id, last_name from contacts where id = '{$bean->id}'");
160 $row = $this->_db->fetchByAssoc($result);
161 $this->assertEquals($row['last_name'],$bean->last_name);
162 $this->assertEquals($row['id'],$bean->id);
164 $this->_db->query("delete from contacts where id = '{$row['id']}'");
167 public function testUpdate()
169 $bean = new Contact();
170 $bean->last_name = 'foobar' . mt_rand();
171 $bean->id = 'test' . mt_rand();
172 $this->_db->insert($bean);
175 $bean = new Contact();
176 $bean->last_name = 'newfoobar' . mt_rand();
177 $this->_db->update($bean,array('id'=>$id));
179 $result = $this->_db->query("select id, last_name from contacts where id = '{$id}'");
180 $row = $this->_db->fetchByAssoc($result);
181 $this->assertEquals($row['last_name'],$bean->last_name);
182 $this->assertEquals($row['id'],$id);
184 $this->_db->query("delete from contacts where id = '{$row['id']}'");
187 public function testDelete()
189 $bean = new Contact();
190 $bean->last_name = 'foobar' . mt_rand();
191 $bean->id = 'test' . mt_rand();
192 $this->_db->insert($bean);
195 $bean = new Contact();
196 $this->_db->delete($bean,array('id'=>$id));
198 $result = $this->_db->query("select deleted from contacts where id = '{$id}'");
199 $row = $this->_db->fetchByAssoc($result);
200 $this->assertEquals($row['deleted'],'1');
202 $this->_db->query("delete from contacts where id = '{$id}'");
205 public function testRetrieve()
207 $bean = new Contact();
208 $bean->last_name = 'foobar' . mt_rand();
209 $bean->id = 'test' . mt_rand();
210 $this->_db->insert($bean);
213 $bean = new Contact();
214 $result = $this->_db->retrieve($bean,array('id'=>$id));
215 $row = $this->_db->fetchByAssoc($result);
216 $this->assertEquals($row['id'],$id);
218 $this->_db->query("delete from contacts where id = '{$id}'");
221 public function testRetrieveView()
223 $this->markTestIncomplete('Write this test');
226 public function testCreateTable()
228 $this->markTestIncomplete('Write this test');
231 public function testCreateTableParams()
233 $tablename = 'test' . mt_rand();
234 $this->createTableParams($tablename,
244 'name' => 'idx_'. $tablename,
246 'fields' => array('foo'),
250 $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
252 $this->dropTableName($tablename);
255 public function testRepairTable()
257 $this->markTestIncomplete('Write this test');
260 public function testRepairTableNoChanges()
262 $tableName = 'testRTNC_' . mt_rand();
264 /* VARDEF - id - ROW[name] => 'id' [vname] => 'LBL_ID' [required] => 'true' [type] => 'char' [reportable] => '' [comment] => 'Unique identifier' [dbType] => 'id' [len] => '36' */
272 'comment' => 'Unique identifier'
276 'name' => 'date_entered',
277 'vname' => 'LBL_DATE_ENTERED',
278 'type' => 'datetime',
280 'comment' => 'Date record created'
284 'name' => 'date_modified',
285 'vname' => 'LBL_DATE_MODIFIED',
286 'type' => 'datetime',
288 'comment' => 'Date record last modified'
290 'modified_user_id' =>
292 'name' => 'modified_user_id',
293 'rname' => 'user_name',
294 'id_name' => 'modified_user_id',
295 'vname' => 'LBL_MODIFIED',
296 'type' => 'assigned_user_name',
297 'table' => 'modified_user_id_users',
303 'comment' => 'User who last modified record'
307 'name' => 'created_by',
308 'rname' => 'user_name',
309 'id_name' => 'created_by',
310 'vname' => 'LBL_CREATED',
311 'type' => 'assigned_user_name',
312 'table' => 'created_by_users',
316 'comment' => 'User ID who created record'
322 'vname' => 'LBL_NAME',
324 'comment' => 'Name of the allowable action (view, list, delete, edit)'
328 'name' => 'category',
329 'vname' => 'LBL_CATEGORY',
333 'comment' => 'Category of the allowable action (usually the name of a module)'
338 'vname' => 'LBL_TYPE',
342 'comment' => 'Specifier for Category, usually "module"'
346 'name' => 'aclaccess',
347 'vname' => 'LBL_ACCESS',
351 'comment' => 'Number specifying access priority; highest access "wins"'
356 'vname' => 'LBL_DELETED',
359 'comment' => 'Record deletion indicator'
365 'relationship' => 'acl_roles_actions',
367 'vname'=>'LBL_USERS',
372 'vname' => 'LBL_REVERSE',
378 'name' => 'deleted2',
379 'vname' => 'LBL_DELETED2',
384 'primary_address_country' =>
386 'name' => 'primary_address_country',
387 'vname' => 'LBL_PRIMARY_ADDRESS_COUNTRY',
389 'group'=>'primary_address',
390 'comment' => 'Country for primary address',
391 'merge_filter' => 'enabled',
393 'refer_url' => array (
394 'name' => 'refer_url',
395 'vname' => 'LBL_REFER_URL',
398 'default' => 'http://',
399 'comment' => 'The URL referenced in the tracker URL; no longer used as of 4.2 (see campaign_trkrs)'
403 'vname' => 'LBL_CAMPAIGN_BUDGET',
404 'type' => 'currency',
405 'dbType' => 'double',
406 'comment' => 'Budgeted amount for the campaign'
408 'time_from' => array (
409 'name' => 'time_from',
410 'vname' => 'LBL_TIME_FROM',
413 'reportable' => false,
417 'name' => 'description',
418 'vname' => 'LBL_DESCRIPTION',
420 'comment' => 'Full text of the note',
424 'cur_plain' => array (
425 'name' => 'cur_plain',
426 'vname' => 'LBL_curPlain',
427 'type' => 'currency',
429 'cur_len_prec' => array (
430 'name' => 'cur_len_prec',
431 'vname' => 'LBL_curLenPrec',
432 'dbType' => 'decimal',
433 'type' => 'currency',
438 'vname' => 'LBL_curLen',
439 'dbType' => 'decimal',
440 'type' => 'currency',
443 'cur_len_prec2' => array (
444 'name' => 'cur_len_prec2',
445 'vname' => 'LBL_curLenPrec',
446 'dbType' => 'decimal',
447 'type' => 'currency',
453 'name' => 'token_ts',
456 'comment' => 'Token timestamp',
457 'function' => array('name' => 'displayDateFromTs', 'returns' => 'html', 'onListView' => true)
468 if($this->_db->tableExists($tableName)) {
469 $this->_db->dropTableName($tableName);
471 $this->createTableParams($tableName, $params, array());
473 $repair = $this->_db->repairTableParams($tableName, $params, array(), false);
475 $this->assertEmpty($repair, "Unexpected repairs: " . $repair);
477 $this->dropTableName($tableName);
480 public function testRepairTableParamsAddData()
482 $tableName = 'test1_' . mt_rand();
491 if($this->_db->tableExists($tableName)) {
492 $this->_db->dropTableName($tableName);
494 $this->createTableParams($tableName, $params, array());
496 $params['bar'] = array (
500 $cols = $this->_db->get_columns($tableName);
501 $this->assertArrayNotHasKey('bar', $cols);
503 $repair = $this->_db->repairTableParams($tableName, $params, array(), false);
504 $this->assertRegExp('#MISSING IN DATABASE.*bar#i', $repair);
505 $repair = $this->_db->repairTableParams($tableName, $params, array(), true);
506 $cols = $this->_db->get_columns($tableName);
507 $this->assertArrayHasKey('bar', $cols);
508 $this->assertEquals('bar', $cols['bar']['name']);
509 $this->assertEquals($this->_db->getColumnType('int'), $cols['bar']['type']);
511 $this->dropTableName($tableName);
514 public function testRepairTableParamsAddIndex()
516 $tableName = 'test1_' . mt_rand();
529 'name' => 'test_index',
531 'fields' => array('foo', 'bar', 'bazz'),
533 if($this->_db->tableExists($tableName)) {
534 $this->_db->dropTableName($tableName);
536 $this->createTableParams($tableName, $params, array());
537 $params['bazz'] = array (
542 $repair = $this->_db->repairTableParams($tableName, $params, array($index), false);
543 $this->assertRegExp('#MISSING IN DATABASE.*bazz#i', $repair);
544 $this->assertRegExp('#MISSING INDEX IN DATABASE.*test_index#i', $repair);
545 $repair = $this->_db->repairTableParams($tableName, $params, array($index), true);
547 $idx = $this->_db->get_indices($tableName);
548 $this->assertArrayHasKey('test_index', $idx);
549 $this->assertContains('foo', $idx['test_index']['fields']);
550 $this->assertContains('bazz', $idx['test_index']['fields']);
552 $cols = $this->_db->get_columns($tableName);
553 $this->assertArrayHasKey('bazz', $cols);
554 $this->assertEquals('bazz', $cols['bazz']['name']);
555 $this->assertEquals($this->_db->getColumnType('int'), $cols['bazz']['type']);
557 $this->dropTableName($tableName);
560 public function testRepairTableParamsAddIndexAndData()
562 $tableName = 'test1_' . mt_rand();
575 'name' => 'test_index',
577 'fields' => array('foo', 'bar'),
579 if($this->_db->tableExists($tableName)) {
580 $this->_db->dropTableName($tableName);
582 $this->createTableParams($tableName, $params, array());
584 $repair = $this->_db->repairTableParams($tableName, $params, array($index), false);
585 $this->assertRegExp('#MISSING INDEX IN DATABASE.*test_index#i', $repair);
586 $repair = $this->_db->repairTableParams($tableName, $params, array($index), true);
587 $idx = $this->_db->get_indices($tableName);
588 $this->assertArrayHasKey('test_index', $idx);
589 $this->assertContains('foo', $idx['test_index']['fields']);
590 $this->assertContains('bar', $idx['test_index']['fields']);
592 $this->dropTableName($tableName);
595 public function testCompareFieldInTables()
597 $tablename1 = 'test1_' . mt_rand();
598 $this->createTableParams($tablename1,
608 $tablename2 = 'test2_' . mt_rand();
609 $this->createTableParams($tablename2,
620 $res = $this->_db->compareFieldInTables(
621 'foo', $tablename1, $tablename2);
623 $this->assertEquals($res['msg'],'match');
625 $this->dropTableName($tablename1);
626 $this->dropTableName($tablename2);
629 public function testCompareFieldInTablesNotInTable1()
631 $tablename1 = 'test3_' . mt_rand();
632 $this->createTableParams($tablename1,
642 $tablename2 = 'test4_' . mt_rand();
643 $this->createTableParams($tablename2,
654 $res = $this->_db->compareFieldInTables(
655 'foo', $tablename1, $tablename2);
656 $this->assertEquals($res['msg'],'not_exists_table1');
658 $this->dropTableName($tablename1);
659 $this->dropTableName($tablename2);
662 public function testCompareFieldInTablesNotInTable2()
664 $tablename1 = 'test5_' . mt_rand();
665 $this->createTableParams($tablename1,
675 $tablename2 = 'test6_' . mt_rand();
676 $this->createTableParams($tablename2,
687 $res = $this->_db->compareFieldInTables(
688 'foo', $tablename1, $tablename2);
690 $this->assertEquals($res['msg'],'not_exists_table2');
692 $this->dropTableName($tablename1);
693 $this->dropTableName($tablename2);
696 public function testCompareFieldInTablesFieldsDoNotMatch()
698 $tablename1 = 'test7_' . mt_rand();
699 $this->createTableParams($tablename1,
709 $tablename2 = 'test8_' . mt_rand();
710 $this->createTableParams($tablename2,
720 $res = $this->_db->compareFieldInTables(
721 'foo', $tablename1, $tablename2);
723 $this->assertEquals($res['msg'],'no_match');
725 $this->dropTableName($tablename1);
726 $this->dropTableName($tablename2);
729 public function testAddIndexes()
731 $tableName = 'test17_' . mt_rand();
743 'fields' => array('foo'),
746 $this->createTableParams($tableName, $fields, $indexes);
747 $indexesDB = $this->_db->get_indices($tableName);
748 $this->dropTableName($tableName);
749 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
751 $tableName = 'test18_' . mt_rand();
760 $this->createTableParams($tableName, $fields, $indexes);
765 'fields' => array('foo'),
769 // first test not executing the statement
770 $this->_db->addIndexes($tableName, $indexes, false);
771 $indexesDB = $this->_db->get_indices($tableName);
772 $this->assertEmpty($indexesDB, 'Indexes were created');
774 // now, execute the statement
775 $this->_db->addIndexes($tableName, $indexes);
776 $indexesDB = $this->_db->get_indices($tableName);
777 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
780 public function testDropIndexes()
782 $tableName = 'test19_' . mt_rand();
794 'fields' => array('foo'),
797 $this->createTableParams($tableName, $fields, $indexes);
798 $indexesDB = $this->_db->get_indices($tableName);
799 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
801 // first test not executing the statement
802 $this->_db->dropIndexes($tableName, $indexes, false);
803 $indexesDB = $this->_db->get_indices($tableName);
804 $this->assertEquals($indexes, $indexesDB, 'Indexes are missed');
806 // now, execute the statement
807 $this->_db->dropIndexes($tableName, $indexes);
808 $indexesDB = $this->_db->get_indices($tableName);
809 $this->assertEmpty($indexesDB, 'Indexes were not dropped');
812 public function testModifyIndexes()
814 $tableName = 'test21_' . mt_rand();
831 'fields' => array('foo'),
834 $this->createTableParams($tableName, $fields, $indexes);
836 $indexesNew = $indexes;
837 $indexesNew['idx_foo']['fields'] = array('foobar');
838 $this->_db->modifyIndexes($tableName, $indexesNew, false);
839 $indexesDB = $this->_db->get_indices($tableName);
840 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
842 $this->_db->modifyIndexes($tableName, $indexesNew);
843 $indexesDB = $this->_db->get_indices($tableName);
844 $this->assertEquals($indexesNew, $indexesDB, 'Indexes are incorrect');
847 public function testAddIndexByMultiQuery()
849 $tableName = 'test22_' . mt_rand();
850 $this->created[$tableName] = true;
861 $queries[] = $this->_db->createTableSQLParams($tableName, $fields, $indexes);
867 'fields' => array('foo'),
870 $tQueries = $this->_db->addIndexes($tableName, $indexes, false);
871 $queries = array_merge($queries, explode(";\n", rtrim($tQueries, ";\n")));
872 $this->_db->query($queries, true);
873 $indexesDB = $this->_db->get_indices($tableName);
874 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
877 public function testDropIndexByMultiQuery()
879 $tableName = 'test23_' . mt_rand();
880 $this->created[$tableName] = true;
891 $queries[] = $this->_db->createTableSQLParams($tableName, $fields, $indexes);
897 'fields' => array('foo'),
900 $tQueries = $this->_db->addIndexes($tableName, $indexes, false);
901 $queries = array_merge($queries, explode(";\n", rtrim($tQueries, ";\n")));
902 $tQueries = $this->_db->dropIndexes($tableName, $indexes, false);
903 $queries = array_merge($queries, explode(";\n", rtrim($tQueries, ";\n")));
904 $this->_db->query($queries, true);
905 $indexesDB = $this->_db->get_indices($tableName);
906 $this->assertEmpty($indexesDB, 'Indexes were not dropped');
909 public function testModifyIndexByMultiQuery()
911 $tableName = 'test24_' . mt_rand();
912 $this->created[$tableName] = true;
928 $queries[] = $this->_db->createTableSQLParams($tableName, $fields, $indexes);
934 'fields' => array('foo'),
937 $tQueries = $this->_db->addIndexes($tableName, $indexes, false);
938 $queries = array_merge($queries, explode(";\n", rtrim($tQueries, ";\n")));
940 $indexesNew = $indexes;
941 $indexesNew['idx_foo']['fields'] = array('foobar');
942 $tQueries = $this->_db->modifyIndexes($tableName, $indexesNew, false);
943 $queries = array_merge($queries, explode(";\n", rtrim($tQueries, ";\n")));
944 $this->_db->query($queries);
945 $indexesDB = $this->_db->get_indices($tableName);
946 $this->assertEquals($indexesNew, $indexesDB, 'Indexes are incorrect');
949 public function testAddMultiIndexes()
952 $tableName = 'test17_' . mt_rand();
966 $this->createTableParams($tableName, $fields, $indexes);
967 $indexesDB = $this->_db->get_indices($tableName);
968 $this->assertEmpty($indexesDB, 'Indexes are incorrect');
974 'fields' => array('foo'),
979 'fields' => array('bar'),
982 $this->_db->addIndexes($tableName, $indexes, false);
983 $indexesDB = $this->_db->get_indices($tableName);
984 $this->assertEmpty($indexesDB);
986 $this->_db->addIndexes($tableName, $indexes);
987 $indexesDB = $this->_db->get_indices($tableName);
988 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
991 public function testDropMultiIndexes()
993 $tableName = 'test17_' . mt_rand();
1002 'type' => 'varchar',
1008 'name' => 'idx_foo',
1010 'fields' => array('foo'),
1013 'name' => 'idx_bar',
1015 'fields' => array('bar'),
1018 $this->createTableParams($tableName, $fields, $indexes);
1019 $indexesDB = $this->_db->get_indices($tableName);
1020 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
1022 $this->_db->dropIndexes($tableName, $indexes, false);
1023 $indexesDB = $this->_db->get_indices($tableName);
1024 $this->assertEquals($indexes, $indexesDB);
1026 $this->_db->dropIndexes($tableName, $indexes);
1027 $indexesDB = $this->_db->get_indices($tableName);
1028 $this->assertEmpty($indexesDB, 'Indexes are incorrect');
1031 public function testModifyMultiIndexes()
1033 $tableName = 'test17_' . mt_rand();
1037 'type' => 'varchar',
1042 'type' => 'varchar',
1048 'name' => 'idx_foo',
1050 'fields' => array('foo'),
1053 'name' => 'idx_bar',
1055 'fields' => array('bar'),
1058 $this->createTableParams($tableName, $fields, $indexes);
1059 $indexesDB = $this->_db->get_indices($tableName);
1060 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
1062 $indexesNew = $indexes;
1063 $indexesNew['idx_foo']['fields'] = array('bar');
1064 $indexesNew['idx_bar']['fields'] = array('foo');
1065 $this->_db->modifyIndexes($tableName, $indexesNew, false);
1066 $indexesDB = $this->_db->get_indices($tableName);
1067 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
1069 $this->_db->modifyIndexes($tableName, $indexesNew);
1070 $indexesDB = $this->_db->get_indices($tableName);
1071 $this->assertEquals($indexesNew, $indexesDB, 'Indexes are incorrect');
1074 public function testAddMultiIndexesByMultiQuery()
1076 $tableName = 'test17_' . mt_rand();
1080 'type' => 'varchar',
1085 'type' => 'varchar',
1090 $this->createTableParams($tableName, $fields, $indexes);
1091 $indexesDB = $this->_db->get_indices($tableName);
1092 $this->assertEmpty($indexesDB, 'Indexes are incorrect');
1096 'name' => 'idx_foo',
1098 'fields' => array('foo'),
1101 'name' => 'idx_bar',
1103 'fields' => array('bar'),
1106 $queries = $this->_db->addIndexes($tableName, $indexes, false);
1107 $queries = explode(";\n", rtrim(trim($queries), ';'));
1108 $this->_db->query($queries);
1109 $indexesDB = $this->_db->get_indices($tableName);
1110 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
1113 public function testDropMultiIndexesByMultiQuery()
1115 $tableName = 'test17_' . mt_rand();
1119 'type' => 'varchar',
1124 'type' => 'varchar',
1130 'name' => 'idx_foo',
1132 'fields' => array('foo'),
1135 'name' => 'idx_bar',
1137 'fields' => array('bar'),
1140 $this->createTableParams($tableName, $fields, $indexes);
1141 $indexesDB = $this->_db->get_indices($tableName);
1142 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
1144 $queries = $this->_db->dropIndexes($tableName, $indexes, false);
1145 $queries = explode(";\n", rtrim(trim($queries), ';'));
1146 $this->_db->query($queries);
1147 $indexesDB = $this->_db->get_indices($tableName);
1148 $this->assertEmpty($indexesDB, 'Indexes are incorrect');
1151 public function testModifyMultiIndexesByMultiQuery()
1153 $tableName = 'test17_' . mt_rand();
1157 'type' => 'varchar',
1162 'type' => 'varchar',
1168 'name' => 'idx_foo',
1170 'fields' => array('foo'),
1173 'name' => 'idx_bar',
1175 'fields' => array('bar'),
1178 $this->createTableParams($tableName, $fields, $indexes);
1179 $indexesDB = $this->_db->get_indices($tableName);
1180 $this->assertEquals($indexes, $indexesDB, 'Indexes are incorrect');
1182 $indexesNew = $indexes;
1183 $indexesNew['idx_foo']['fields'] = array('bar');
1184 $indexesNew['idx_bar']['fields'] = array('foo');
1185 $queries = $this->_db->modifyIndexes($tableName, $indexesNew, false);
1186 $queries = explode(";\n", rtrim(trim($queries), ';'));
1187 $this->_db->query($queries);
1188 $indexesDB = $this->_db->get_indices($tableName);
1189 $this->assertEquals($indexesNew, $indexesDB, 'Indexes are incorrect');
1192 public function testAddColumn()
1194 $tablename1 = 'test23_' . mt_rand();
1195 $this->createTableParams($tablename1,
1199 'type' => 'varchar',
1204 'type' => 'varchar',
1210 $tablename2 = 'test24_' . mt_rand();
1211 $this->createTableParams($tablename2,
1215 'type' => 'varchar',
1222 $res = $this->_db->compareFieldInTables(
1223 'foobar', $tablename1, $tablename2);
1225 $this->assertEquals($res['msg'],'not_exists_table2');
1227 $this->_db->addColumn(
1232 'type' => 'varchar',
1238 $res = $this->_db->compareFieldInTables(
1239 'foobar', $tablename1, $tablename2);
1241 $this->assertEquals($res['msg'],'match');
1243 $this->dropTableName($tablename1);
1244 $this->dropTableName($tablename2);
1247 public function alterColumnDataProvider()
1252 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255', 'required' => true, 'default' => 'sugar'),
1253 'temp' => array ('name' => 'foobar', 'type' => 'int') // Check if type conversion works
1257 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255', 'default' => 'kilroy'),
1258 'temp' => array ('name' => 'foobar', 'type' => 'double', 'default' => '99999') // Check if default gets replaced
1262 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255'),
1263 'temp' => array ('name' => 'foobar', 'type' => 'double', 'default' => '99999') // Check if default gets dropped
1267 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255', 'required' => true, 'default' => 'sweet'),
1268 'temp' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '1500',) // Check varchar shortening
1272 'target' => array ('name' => 'foobar', 'type' => 'longtext', 'required' => true),
1273 'temp' => array ('name' => 'foobar', 'type' => 'text', 'default' => 'dextrose') // Check clob(65k) to clob(2M or so) conversion
1277 'target' => array ('name' => 'foobar', 'type' => 'double', 'required' => true),
1278 'temp' => array ('name' => 'foobar', 'type' => 'int', 'default' => 0) // Check int to double change
1286 * @dataProvider alterColumnDataProvider
1292 public function testAlterColumn($i, $target, $temp)
1294 if($this->_db->dbType == "oci8" && ($i == 4 || $i == 6)) {
1295 $this->markTestSkipped("Cannot reliably shrink columns in Oracle");
1298 $foo_col = array ('name' => 'foo', 'type' => 'varchar', 'len' => '255'); // Common column between tables
1300 $tablebase = 'testac_'. mt_rand() . '_';
1302 $t1 = $tablebase . $i .'A';
1303 $t2 = $tablebase . $i .'B';
1304 $this->createTableParams( $t1,
1305 array('foo' => $foo_col, 'foobar' => $target),
1307 $this->createTableParams( $t2,
1308 array('foo' => $foo_col, 'foobar' => $temp),
1311 $res = $this->_db->compareFieldInTables('foobar', $t1, $t2);
1313 $this->assertEquals('no_match', $res['msg'],
1314 "testAlterColumn table columns match while they shouldn't for table $t1 and $t2: "
1315 . print_r($res,true) );
1317 $this->_db->alterColumn($t2, array('foobar' => $target));
1319 $res = $this->_db->compareFieldInTables('foobar', $t1, $t2);
1321 $this->assertEquals('match', $res['msg'],
1322 "testAlterColumn table columns don't match while they should for table $t1 and $t2: "
1323 . print_r($res,true) );
1325 $this->dropTableName($t1);
1326 $this->dropTableName($t2);
1329 public function testDropTable()
1331 $this->markTestIncomplete('Write this test');
1334 public function testDropTableName()
1336 $tablename = 'test' . mt_rand();
1337 $this->createTableParams($tablename,
1341 'type' => 'varchar',
1347 $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1349 $this->dropTableName($tablename);
1351 $this->assertFalse(in_array($tablename,$this->_db->getTablesArray()));
1354 public function testDeleteColumn()
1356 $this->markTestIncomplete('Write this test');
1359 public function testDisconnectAll()
1361 DBManagerFactory::disconnectAll();
1362 $this->assertTrue($this->_db->checkError());
1363 $this->_db = DBManagerFactory::getInstance();
1366 public function testQuery()
1368 $beanIds = $this->_createRecords(5);
1370 $result = $this->_db->query("SELECT id From contacts where last_name = 'foobar'");
1371 if ( $this->_db instanceOf MysqliManager )
1372 $this->assertInstanceOf('Mysqli_result',$result);
1374 $this->assertTrue(is_resource($result));
1376 while ( $row = $this->_db->fetchByAssoc($result) )
1377 $this->assertTrue(in_array($row['id'],$beanIds),"Id not found '{$row['id']}'");
1379 $this->_removeRecords($beanIds);
1382 public function disabledLimitQuery()
1384 $beanIds = $this->_createRecords(5);
1385 $_REQUEST['module'] = 'contacts';
1386 $result = $this->_db->limitQuery("SELECT id From contacts where last_name = 'foobar'",1,3);
1387 if ( $this->_db instanceOf MysqliManager )
1388 $this->assertInstanceOf('Mysqli_result',$result);
1390 $this->assertTrue(is_resource($result));
1392 while ( $row = $this->_db->fetchByAssoc($result) ) {
1393 if ( $row['id'][0] > 3 || $row['id'][0] < 0 )
1394 $this->assertFalse(in_array($row['id'],$beanIds),"Found {$row['id']} in error");
1396 $this->assertTrue(in_array($row['id'],$beanIds),"Didn't find {$row['id']}");
1398 unset($_REQUEST['module']);
1399 $this->_removeRecords($beanIds);
1402 public function testGetOne()
1404 $beanIds = $this->_createRecords(1);
1406 $id = $this->_db->getOne("SELECT id From contacts where last_name = 'foobar'");
1407 $this->assertEquals($id,$beanIds[0]);
1410 if ( $this->_db instanceOf MysqlManager ) {
1411 $id = $this->_db->getOne($this->_db->limitQuerySql("SELECT id From contacts where last_name = 'foobar'", 0, 1));
1412 $this->assertEquals($id,$beanIds[0]);
1415 $this->_removeRecords($beanIds);
1418 public function testGetFieldsArray()
1420 $beanIds = $this->_createRecords(1);
1422 $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1423 $fields = $this->_db->getFieldsArray($result,true);
1425 $this->assertEquals(array("id"),$fields);
1427 $this->_removeRecords($beanIds);
1430 public function testGetAffectedRowCount()
1432 if(!$this->_db->supports("affected_rows")) {
1433 $this->markTestSkipped('Skipping, backend doesn\'t support affected rows');
1436 $beanIds = $this->_createRecords(1);
1437 $result = $this->_db->query("DELETE From contacts where id = '{$beanIds[0]}'");
1438 $this->assertEquals(1, $this->_db->getAffectedRowCount($result));
1441 public function testFetchByAssoc()
1443 $beanIds = $this->_createRecords(1);
1445 $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1447 $row = $this->_db->fetchByAssoc($result);
1449 $this->assertTrue(is_array($row));
1450 $this->assertEquals($row['id'],$beanIds[0]);
1452 $this->_removeRecords($beanIds);
1455 public function testConnect()
1457 $this->markTestIncomplete('Write this test');
1460 public function testDisconnect()
1462 $this->_db->disconnect();
1463 $this->assertTrue($this->_db->checkError());
1464 $this->_db = DBManagerFactory::getInstance();
1467 public function testGetTablesArray()
1469 $tablename = 'test' . mt_rand();
1470 $this->createTableParams($tablename,
1474 'type' => 'varchar',
1481 $this->assertTrue($this->_db->tableExists($tablename));
1483 $this->dropTableName($tablename);
1486 public function testVersion()
1488 $ver = $this->_db->version();
1490 $this->assertTrue(is_string($ver));
1493 public function testTableExists()
1495 $tablename = 'test' . mt_rand();
1496 $this->createTableParams($tablename,
1500 'type' => 'varchar',
1507 $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1509 $this->dropTableName($tablename);
1512 public function providerCompareVardefs()
1514 $returnArray = array(
1518 'type' => 'varchar',
1523 'type' => 'varchar',
1535 'type' => 'varchar',
1557 'type' => 'varchar',
1564 'type' => 'varchar',
1569 'type' => 'varchar',
1575 return $returnArray;
1579 * @dataProvider providerCompareVarDefs
1581 public function testCompareVarDefs($fieldDef1,$fieldDef2,$expectedResult)
1583 if ( $expectedResult ) {
1584 $this->assertTrue($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1587 $this->assertFalse($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1594 public function test_Bug34892_MssqlNotClearingErrorResults()
1596 // execute a bad query
1597 $this->_db->query("select dsdsdsdsdsdsdsdsdsd", false, "test_Bug34892_MssqlNotClearingErrorResults", true);
1598 // assert it found an error
1599 $this->assertNotEmpty($this->_db->lastError(), "lastError should return true as a result of the previous illegal query");
1600 // now, execute a good query
1601 $this->_db->query("select * from config");
1602 // and make no error messages are asserted
1603 $this->assertEmpty($this->_db->lastError(), "lastError should have cleared the previous error and return false of the last legal query");
1606 public function vardefProvider()
1608 $GLOBALS['log']->info('DBManagerTest.vardefProvider: _db = ' . print_r($this->_db));
1609 $this->setUp(); // Just in case the DB driver is not created yet.
1610 $emptydate = $this->_db->emptyValue("date");
1611 $emptytime = $this->_db->emptyValue("time");
1612 $emptydatetime = $this->_db->emptyValue("datetime");
1615 array("testid", array (
1619 'type' => 'varchar',
1623 array("id" => "test123"),
1624 array("id" => "'test123'")
1626 array("testtext", array (
1630 'type' => 'varchar',
1636 'type' => 'varchar',
1640 array("text1" => "''"),
1643 array("testtext2", array (
1647 'type' => 'varchar',
1653 'type' => 'varchar',
1656 array('text1' => 'foo', 'text2' => 'bar'),
1657 array("text1" => "'foo'", 'text2' => "'bar'"),
1659 array("testreq", array (
1663 'type' => 'varchar',
1674 'name' => 'floatval',
1675 'type' => 'decimal',
1681 'type' => 'currency',
1686 'name' => 'test_dtm',
1687 'type' => 'datetime',
1692 'name' => 'test_dtm2',
1693 'type' => 'datetimecombo',
1698 'name' => 'test_dt',
1704 'name' => 'test_tm',
1709 array("id" => "test123", 'intval' => 42, 'floatval' => 42.24,
1710 'money' => 56.78, 'test_dtm' => '2002-01-02 12:34:56', 'test_dtm2' => '2011-10-08 01:02:03',
1711 'test_dt' => '1998-10-04', 'test_tm' => '03:04:05'
1713 array("id" => "'test123'", 'intval' => 42, 'floatval' => 42.24,
1714 'money' => 56.78, 'test_dtm' => $this->_db->convert('\'2002-01-02 12:34:56\'', "datetime"), 'test_dtm2' => $this->_db->convert('\'2011-10-08 01:02:03\'', 'datetime'),
1715 'test_dt' => $this->_db->convert('\'1998-10-04\'', 'date'), 'test_tm' => $this->_db->convert('\'03:04:05\'', 'time')
1718 array("testreqnull", array (
1722 'type' => 'varchar',
1733 'name' => 'floatval',
1734 'type' => 'decimal',
1740 'type' => 'currency',
1745 'name' => 'test_dtm',
1746 'type' => 'datetime',
1751 'name' => 'test_dtm2',
1752 'type' => 'datetimecombo',
1757 'name' => 'test_dt',
1763 'name' => 'test_tm',
1769 array("id" => "''", 'intval' => 0, 'floatval' => 0,
1770 'money' => 0, 'test_dtm' => "$emptydatetime", 'test_dtm2' => "$emptydatetime",
1771 'test_dt' => "$emptydate", 'test_tm' => "$emptytime"
1775 array("testnull", array (
1779 'type' => 'varchar',
1788 'name' => 'floatval',
1789 'type' => 'decimal',
1794 'type' => 'currency',
1798 'name' => 'test_dtm',
1799 'type' => 'datetime',
1803 'name' => 'test_dtm2',
1804 'type' => 'datetimecombo',
1808 'name' => 'test_dt',
1813 'name' => 'test_tm',
1818 array("id" => "'123'"),
1821 array("testempty", array (
1825 'type' => 'varchar',
1834 'name' => 'floatval',
1835 'type' => 'decimal',
1840 'type' => 'currency',
1844 'name' => 'test_dtm',
1845 'type' => 'datetime',
1849 'name' => 'test_dtm2',
1850 'type' => 'datetimecombo',
1854 'name' => 'test_dt',
1859 'name' => 'test_tm',
1864 'name' => 'test_txt',
1865 'type' => 'varchar',
1868 array("id" => "", 'intval' => '', 'floatval' => '',
1869 'money' => '', 'test_dtm' => '', 'test_dtm2' => '',
1870 'test_dt' => '', 'test_tm' => '', 'text_txt' => null
1872 array("id" => "''", 'intval' => 0, 'floatval' => 0,
1873 'money' => 0, 'test_dtm' => "NULL", 'test_dtm2' => "NULL",
1874 'test_dt' => "NULL", 'test_tm' => 'NULL'
1876 array('intval' => 'NULL', 'floatval' => 'NULL',
1877 'money' => 'NULL', 'test_dtm' => 'NULL', 'test_dtm2' => 'NULL',
1878 'test_dt' => 'NULL', 'test_tm' => 'NULL'
1885 * Test InserSQL functions
1886 * @dataProvider vardefProvider
1887 * @param string $name
1888 * @param array $defs
1889 * @param array $data
1890 * @param array $result
1892 public function testInsertSQL($name, $defs, $data, $result)
1898 $obj = new TestSugarBean($name, $vardefs);
1900 foreach($data as $k => $v) {
1903 $sql = $this->_db->insertSQL($obj);
1904 $names = join('\s*,\s*',array_map('preg_quote', array_keys($result)));
1905 $values = join('\s*,\s*',array_map('preg_quote', array_values($result)));
1906 $this->assertRegExp("/INSERT INTO $name\s+\(\s*$names\s*\)\s+VALUES\s+\(\s*$values\s*\)/is", $sql, "Bad sql: $sql");
1910 * Test UpdateSQL functions
1911 * @dataProvider vardefProvider
1912 * @param string $name
1913 * @param array $defs
1914 * @param array $data
1916 * @param array $result
1918 public function testUpdateSQL($name, $defs, $data, $_, $result = null)
1920 $name = "update$name";
1925 // ensure it has an ID
1926 $vardefs['fields']['id'] = array (
1931 $vardefs['fields']['deleted'] = array (
1932 'name' => 'deleted',
1936 $obj = new TestSugarBean($name, $vardefs);
1938 foreach($defs as $k => $v) {
1939 if(isset($data[$k])) {
1940 $obj->$k = $data[$k];
1946 $obj->id = 'test_ID';
1947 $sql = $this->_db->updateSQL($obj);
1948 if(is_null($result)) {
1952 foreach($result as $k => $v) {
1953 if($k == "id" || $k == 'deleted') continue;
1954 $names_i[] = preg_quote("$k=$v");
1956 if(empty($names_i)) {
1957 $this->assertEquals("", $sql, "Bad sql: $sql");
1960 $names = join('\s*,\s*',$names_i);
1961 $this->assertRegExp("/UPDATE $name\s+SET\s+$names\s+WHERE\s+$name.id\s*=\s*'test_ID' AND deleted=0/is", $sql, "Bad sql: $sql");
1965 * Test UpdateSQL functions
1966 * @dataProvider vardefProvider
1967 * @param string $name
1968 * @param array $defs
1969 * @param array $data
1971 * @param array $result
1973 public function testUpdateSQLNoDeleted($name, $defs, $data, $_, $result = null)
1975 $name = "updatenodel$name";
1980 // ensure it has an ID
1981 $vardefs['fields']['id'] = array (
1986 unset($vardefs['fields']['deleted']);
1988 $obj = new TestSugarBean($name, $vardefs);
1990 foreach($defs as $k => $v) {
1991 if(isset($data[$k])) {
1992 $obj->$k = $data[$k];
1998 $obj->id = 'test_ID';
1999 $sql = $this->_db->updateSQL($obj);
2000 if(is_null($result)) {
2004 foreach($result as $k => $v) {
2005 if($k == "id" || $k == 'deleted') continue;
2006 $names_i[] = preg_quote("$k=$v");
2008 if(empty($names_i)) {
2009 $this->assertEquals("", $sql, "Bad sql: $sql");
2012 $names = join('\s*,\s*',$names_i);
2013 $this->assertRegExp("/UPDATE $name\s+SET\s+$names\s+WHERE\s+$name.id\s*=\s*'test_ID'/is", $sql, "Bad sql: $sql");
2014 $this->assertNotContains(" AND deleted=0", $sql, "Bad sql: $sql");
2018 * Test the canInstall
2021 public function testCanInstall() {
2022 $DBManagerClass = get_class($this->_db);
2023 if(!method_exists($this->_db, 'version') || !method_exists($this->_db, 'canInstall'))
2024 $this->markTestSkipped(
2025 "Class {$DBManagerClass} doesn't implement canInstall or version methods");
2027 $method = new ReflectionMethod($DBManagerClass, 'canInstall');
2028 if($method->class == 'DBManager')
2029 $this->markTestSkipped(
2030 "Class {$DBManagerClass} or one of it's ancestors doesn't override DBManager's canInstall");
2032 // First assuming that we are only running unit tests against a supported database :)
2033 $this->assertTrue($this->_db->canInstall(), "Apparently we are not running this unit test against a supported database!!!");
2035 $DBstub = $this->getMock($DBManagerClass, array('version'));
2036 $DBstub->expects($this->any())
2038 ->will($this->returnValue('0.0.0')); // Expect that any supported version is higher than 0.0.0
2040 $this->assertTrue(is_array($DBstub->canInstall()), "Apparently we do support version 0.0.0 in " . $DBManagerClass);
2043 public function providerValidateQuery()
2046 array(true, 'SELECT * FROM accounts'),
2047 array(false, 'SELECT * FROM blablabla123'),
2052 * Test query validation
2053 * @dataProvider providerValidateQuery
2058 public function testValidateQuery($good, $sql)
2060 $check = $this->_db->validateQuery($sql);
2061 $this->assertEquals($good, $check);
2064 public function testTextSizeHandling()
2066 $tablename = 'testTextSize';// . mt_rand();
2076 'type' => 'longtext',
2081 'type' => 'longtext',
2086 $this->createTableParams($tablename, $fielddefs, array());
2087 $basestr = '0123456789abcdefghijklmnopqrstuvwxyz';
2089 while(strlen($str) < 159900)
2094 for($i = 0; $i < 50; $i++)
2097 $size = strlen($str);
2099 $this->_db->insertParams($tablename, $fielddefs, array('id' => $size, 'test' => $str, 'dummy' => $str));
2101 $select = "SELECT test FROM $tablename WHERE id = '{$size}'";
2102 $strresult = $this->_db->getOne($select);
2104 $this->assertEquals(0, mb_strpos($str, $strresult));
2108 public function testGetIndicesContainsPrimary()
2110 $indices = $this->_db->get_indices('accounts');
2112 // find if any are primary
2115 foreach($indices as $index)
2117 if($index['type'] == "primary") {
2123 $this->assertTrue($found, 'Primary Key Not Found On Module');
2127 * testDBGuidGeneration
2128 * Tests that the first 1000 DB generated GUIDs are unique
2130 public function testDBGuidGeneration()
2134 $sql = "SELECT {$this->_db->getGuidSQL()} {$this->_db->getFromDummyTable()}";
2135 for($i = 0; $i < 1000; $i++)
2137 $newguid = $this->_db->getOne($sql);
2138 $this->assertFalse(in_array($newguid, $guids), "'$newguid' already existed in the array of GUIDs!");
2139 $guids []= $newguid;
2143 public function testAddPrimaryKey()
2145 $tablename = 'testConstraints';
2155 'type' => 'longtext',
2159 $this->createTableParams($tablename, $fielddefs, array());
2160 unset($this->created[$tablename]); // that table is required by testRemovePrimaryKey test
2162 $sql = $this->_db->add_drop_constraint(
2165 'name' => 'testConstraints_pk',
2166 'type' => 'primary',
2167 'fields' => array('id'),
2172 $result = $this->_db->query($sql);
2174 $indices = $this->_db->get_indices($tablename);
2176 // find if any are primary
2179 foreach($indices as $index)
2181 if($index['type'] == "primary") {
2187 $this->assertTrue($found, 'Primary Key Not Found On Table');
2191 * @depends testAddPrimaryKey
2193 public function testRemovePrimaryKey()
2195 $tablename = 'testConstraints';
2196 $this->created[$tablename] = true;
2198 $sql = $this->_db->add_drop_constraint(
2201 'name' => 'testConstraints_pk',
2202 'type' => 'primary',
2203 'fields' => array('id'),
2208 $result = $this->_db->query($sql);
2210 $indices = $this->_db->get_indices($tablename);
2212 // find if any are primary
2215 foreach($indices as $index)
2217 if($index['type'] == "primary") {
2223 $this->assertFalse($found, 'Primary Key Found On Table');