2 /*********************************************************************************
3 * SugarCRM Community Edition is a customer relationship management program developed by
4 * SugarCRM, Inc. Copyright (C) 2004-2012 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();
72 public function tearDown()
74 foreach($this->created as $table => $dummy) {
75 $this->_db->dropTableName($table);
79 protected function createTableParams($tablename, $fieldDefs, $indices)
81 $this->created[$tablename] = true;
82 return $this->_db->createTableParams($tablename, $fieldDefs, $indices);
85 protected function dropTableName($tablename)
87 unset($this->created[$tablename]);
88 return $this->_db->dropTableName($tablename);
91 private function _createRecords(
96 for ( $i = 0; $i < $num; $i++ ) {
97 $bean = new Contact();
98 $bean->id = "$i-test" . mt_rand();
99 $bean->last_name = "foobar";
100 $this->_db->insert($bean);
101 $beanIds[] = $bean->id;
107 private function _removeRecords(
111 foreach ($ids as $id)
112 $this->_db->query("DELETE From contacts where id = '{$id}'");
115 public function testGetDatabase()
117 if ( $this->_db instanceOf MysqliManager )
118 $this->assertInstanceOf('Mysqli',$this->_db->getDatabase());
120 $this->assertTrue(is_resource($this->_db->getDatabase()));
123 public function testCheckError()
125 $this->assertFalse($this->_db->checkError());
126 $this->assertFalse($this->_db->lastError());
129 public function testCheckErrorNoConnection()
131 $this->_db->disconnect();
132 $this->assertTrue($this->_db->checkError());
133 $this->_db = DBManagerFactory::getInstance();
136 public function testGetQueryTime()
138 $this->_db->version();
139 $this->assertTrue($this->_db->getQueryTime() > 0);
142 public function testCheckConnection()
144 $this->_db->checkConnection();
145 if ( $this->_db instanceOf MysqliManager )
146 $this->assertInstanceOf('Mysqli',$this->_db->getDatabase());
148 $this->assertTrue(is_resource($this->_db->getDatabase()));
151 public function testInsert()
153 $bean = new Contact();
154 $bean->last_name = 'foobar' . mt_rand();
155 $bean->id = 'test' . mt_rand();
156 $this->_db->insert($bean);
158 $result = $this->_db->query("select id, last_name from contacts where id = '{$bean->id}'");
159 $row = $this->_db->fetchByAssoc($result);
160 $this->assertEquals($row['last_name'],$bean->last_name);
161 $this->assertEquals($row['id'],$bean->id);
163 $this->_db->query("delete from contacts where id = '{$row['id']}'");
166 public function testUpdate()
168 $bean = new Contact();
169 $bean->last_name = 'foobar' . mt_rand();
170 $bean->id = 'test' . mt_rand();
171 $this->_db->insert($bean);
174 $bean = new Contact();
175 $bean->last_name = 'newfoobar' . mt_rand();
176 $this->_db->update($bean,array('id'=>$id));
178 $result = $this->_db->query("select id, last_name from contacts where id = '{$id}'");
179 $row = $this->_db->fetchByAssoc($result);
180 $this->assertEquals($row['last_name'],$bean->last_name);
181 $this->assertEquals($row['id'],$id);
183 $this->_db->query("delete from contacts where id = '{$row['id']}'");
186 public function testDelete()
188 $bean = new Contact();
189 $bean->last_name = 'foobar' . mt_rand();
190 $bean->id = 'test' . mt_rand();
191 $this->_db->insert($bean);
194 $bean = new Contact();
195 $this->_db->delete($bean,array('id'=>$id));
197 $result = $this->_db->query("select deleted from contacts where id = '{$id}'");
198 $row = $this->_db->fetchByAssoc($result);
199 $this->assertEquals($row['deleted'],'1');
201 $this->_db->query("delete from contacts where id = '{$id}'");
204 public function testRetrieve()
206 $bean = new Contact();
207 $bean->last_name = 'foobar' . mt_rand();
208 $bean->id = 'test' . mt_rand();
209 $this->_db->insert($bean);
212 $bean = new Contact();
213 $result = $this->_db->retrieve($bean,array('id'=>$id));
214 $row = $this->_db->fetchByAssoc($result);
215 $this->assertEquals($row['id'],$id);
217 $this->_db->query("delete from contacts where id = '{$id}'");
220 public function testRetrieveView()
222 // TODO: Write this test
225 public function testCreateTable()
227 // TODO: Write this test
230 public function testCreateTableParams()
232 $tablename = 'test' . mt_rand();
233 $this->createTableParams($tablename,
243 'name' => 'idx_'. $tablename,
245 'fields' => array('foo'),
249 $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
251 $this->dropTableName($tablename);
254 public function testRepairTable()
256 // TODO: Write this test
259 public function testRepairTableNoChanges()
261 $tableName = 'testRTNC_' . mt_rand();
263 /* VARDEF - id - ROW[name] => 'id' [vname] => 'LBL_ID' [required] => 'true' [type] => 'char' [reportable] => '' [comment] => 'Unique identifier' [dbType] => 'id' [len] => '36' */
271 'comment' => 'Unique identifier'
275 'name' => 'date_entered',
276 'vname' => 'LBL_DATE_ENTERED',
277 'type' => 'datetime',
279 'comment' => 'Date record created'
283 'name' => 'date_modified',
284 'vname' => 'LBL_DATE_MODIFIED',
285 'type' => 'datetime',
287 'comment' => 'Date record last modified'
289 'modified_user_id' =>
291 'name' => 'modified_user_id',
292 'rname' => 'user_name',
293 'id_name' => 'modified_user_id',
294 'vname' => 'LBL_MODIFIED',
295 'type' => 'assigned_user_name',
296 'table' => 'modified_user_id_users',
302 'comment' => 'User who last modified record'
306 'name' => 'created_by',
307 'rname' => 'user_name',
308 'id_name' => 'created_by',
309 'vname' => 'LBL_CREATED',
310 'type' => 'assigned_user_name',
311 'table' => 'created_by_users',
315 'comment' => 'User ID who created record'
321 'vname' => 'LBL_NAME',
323 'comment' => 'Name of the allowable action (view, list, delete, edit)'
327 'name' => 'category',
328 'vname' => 'LBL_CATEGORY',
332 'comment' => 'Category of the allowable action (usually the name of a module)'
337 'vname' => 'LBL_TYPE',
341 'comment' => 'Specifier for Category, usually "module"'
345 'name' => 'aclaccess',
346 'vname' => 'LBL_ACCESS',
350 'comment' => 'Number specifying access priority; highest access "wins"'
355 'vname' => 'LBL_DELETED',
358 'comment' => 'Record deletion indicator'
364 'relationship' => 'acl_roles_actions',
366 'vname'=>'LBL_USERS',
371 'vname' => 'LBL_REVERSE',
377 'name' => 'deleted2',
378 'vname' => 'LBL_DELETED2',
383 'primary_address_country' =>
385 'name' => 'primary_address_country',
386 'vname' => 'LBL_PRIMARY_ADDRESS_COUNTRY',
388 'group'=>'primary_address',
389 'comment' => 'Country for primary address',
390 'merge_filter' => 'enabled',
392 'refer_url' => array (
393 'name' => 'refer_url',
394 'vname' => 'LBL_REFER_URL',
397 'default' => 'http://',
398 'comment' => 'The URL referenced in the tracker URL; no longer used as of 4.2 (see campaign_trkrs)'
402 'vname' => 'LBL_CAMPAIGN_BUDGET',
403 'type' => 'currency',
404 'dbType' => 'double',
405 'comment' => 'Budgeted amount for the campaign'
407 'time_from' => array (
408 'name' => 'time_from',
409 'vname' => 'LBL_TIME_FROM',
412 'reportable' => false,
416 'name' => 'description',
417 'vname' => 'LBL_DESCRIPTION',
419 'comment' => 'Full text of the note',
423 'cur_plain' => array (
424 'name' => 'cur_plain',
425 'vname' => 'LBL_curPlain',
426 'type' => 'currency',
428 'cur_len_prec' => array (
429 'name' => 'cur_len_prec',
430 'vname' => 'LBL_curLenPrec',
431 'dbType' => 'decimal',
432 'type' => 'currency',
437 'vname' => 'LBL_curLen',
438 'dbType' => 'decimal',
439 'type' => 'currency',
442 'cur_len_prec2' => array (
443 'name' => 'cur_len_prec2',
444 'vname' => 'LBL_curLenPrec',
445 'dbType' => 'decimal',
446 'type' => 'currency',
452 'name' => 'token_ts',
455 'comment' => 'Token timestamp',
456 'function' => array('name' => 'displayDateFromTs', 'returns' => 'html', 'onListView' => true)
467 if($this->_db->tableExists($tableName)) {
468 $this->_db->dropTableName($tableName);
470 $this->createTableParams($tableName, $params, array());
472 $repair = $this->_db->repairTableParams($tableName, $params, array(), false);
474 $this->assertEmpty($repair, "Unexpected repairs: " . $repair);
476 $this->dropTableName($tableName);
479 public function testRepairTableParamsAddData()
481 $tableName = 'test1_' . mt_rand();
490 if($this->_db->tableExists($tableName)) {
491 $this->_db->dropTableName($tableName);
493 $this->createTableParams($tableName, $params, array());
495 $params['bar'] = array (
499 $cols = $this->_db->get_columns($tableName);
500 $this->assertArrayNotHasKey('bar', $cols);
502 $repair = $this->_db->repairTableParams($tableName, $params, array(), false);
503 $this->assertRegExp('#MISSING IN DATABASE.*bar#i', $repair);
504 $repair = $this->_db->repairTableParams($tableName, $params, array(), true);
505 $cols = $this->_db->get_columns($tableName);
506 $this->assertArrayHasKey('bar', $cols);
507 $this->assertEquals('bar', $cols['bar']['name']);
508 $this->assertEquals($this->_db->getColumnType('int'), $cols['bar']['type']);
510 $this->dropTableName($tableName);
513 public function testRepairTableParamsAddIndex()
515 $tableName = 'test1_' . mt_rand();
528 'name' => 'test_index',
530 'fields' => array('foo', 'bar', 'bazz'),
532 if($this->_db->tableExists($tableName)) {
533 $this->_db->dropTableName($tableName);
535 $this->createTableParams($tableName, $params, array());
536 $params['bazz'] = array (
541 $repair = $this->_db->repairTableParams($tableName, $params, array($index), false);
542 $this->assertRegExp('#MISSING IN DATABASE.*bazz#i', $repair);
543 $this->assertRegExp('#MISSING INDEX IN DATABASE.*test_index#i', $repair);
544 $repair = $this->_db->repairTableParams($tableName, $params, array($index), true);
546 $idx = $this->_db->get_indices($tableName);
547 $this->assertArrayHasKey('test_index', $idx);
548 $this->assertContains('foo', $idx['test_index']['fields']);
549 $this->assertContains('bazz', $idx['test_index']['fields']);
551 $cols = $this->_db->get_columns($tableName);
552 $this->assertArrayHasKey('bazz', $cols);
553 $this->assertEquals('bazz', $cols['bazz']['name']);
554 $this->assertEquals($this->_db->getColumnType('int'), $cols['bazz']['type']);
556 $this->dropTableName($tableName);
559 public function testRepairTableParamsAddIndexAndData()
561 $tableName = 'test1_' . mt_rand();
574 'name' => 'test_index',
576 'fields' => array('foo', 'bar'),
578 if($this->_db->tableExists($tableName)) {
579 $this->_db->dropTableName($tableName);
581 $this->createTableParams($tableName, $params, array());
583 $repair = $this->_db->repairTableParams($tableName, $params, array($index), false);
584 $this->assertRegExp('#MISSING INDEX IN DATABASE.*test_index#i', $repair);
585 $repair = $this->_db->repairTableParams($tableName, $params, array($index), true);
586 $idx = $this->_db->get_indices($tableName);
587 $this->assertArrayHasKey('test_index', $idx);
588 $this->assertContains('foo', $idx['test_index']['fields']);
589 $this->assertContains('bar', $idx['test_index']['fields']);
591 $this->dropTableName($tableName);
594 public function testCompareFieldInTables()
596 $tablename1 = 'test1_' . mt_rand();
597 $this->createTableParams($tablename1,
607 $tablename2 = 'test2_' . mt_rand();
608 $this->createTableParams($tablename2,
619 $res = $this->_db->compareFieldInTables(
620 'foo', $tablename1, $tablename2);
622 $this->assertEquals($res['msg'],'match');
624 $this->dropTableName($tablename1);
625 $this->dropTableName($tablename2);
628 public function testCompareFieldInTablesNotInTable1()
630 $tablename1 = 'test3_' . mt_rand();
631 $this->createTableParams($tablename1,
641 $tablename2 = 'test4_' . mt_rand();
642 $this->createTableParams($tablename2,
653 $res = $this->_db->compareFieldInTables(
654 'foo', $tablename1, $tablename2);
655 $this->assertEquals($res['msg'],'not_exists_table1');
657 $this->dropTableName($tablename1);
658 $this->dropTableName($tablename2);
661 public function testCompareFieldInTablesNotInTable2()
663 $tablename1 = 'test5_' . mt_rand();
664 $this->createTableParams($tablename1,
674 $tablename2 = 'test6_' . mt_rand();
675 $this->createTableParams($tablename2,
686 $res = $this->_db->compareFieldInTables(
687 'foo', $tablename1, $tablename2);
689 $this->assertEquals($res['msg'],'not_exists_table2');
691 $this->dropTableName($tablename1);
692 $this->dropTableName($tablename2);
695 public function testCompareFieldInTablesFieldsDoNotMatch()
697 $tablename1 = 'test7_' . mt_rand();
698 $this->createTableParams($tablename1,
708 $tablename2 = 'test8_' . mt_rand();
709 $this->createTableParams($tablename2,
719 $res = $this->_db->compareFieldInTables(
720 'foo', $tablename1, $tablename2);
722 $this->assertEquals($res['msg'],'no_match');
724 $this->dropTableName($tablename1);
725 $this->dropTableName($tablename2);
728 // public function testCompareIndexInTables()
730 // $tablename1 = 'test9_' . mt_rand();
731 // $this->_db->createTableParams($tablename1,
735 // 'type' => 'varchar',
741 // 'name' => 'idx_'. $tablename1,
742 // 'type' => 'index',
743 // 'fields' => array('foo'),
747 // $tablename2 = 'test10_' . mt_rand();
748 // $this->_db->createTableParams($tablename2,
752 // 'type' => 'varchar',
758 // 'name' => 'idx_'. $tablename2,
759 // 'type' => 'index',
760 // 'fields' => array('foo'),
765 // $res = $this->_db->compareIndexInTables(
766 // 'idx_foo', $tablename1, $tablename2);
768 // $this->assertEquals($res['msg'],'match');
770 // $this->_db->dropTableName($tablename1);
771 // $this->_db->dropTableName($tablename2);
774 // public function testCompareIndexInTablesNotInTable1()
776 // $tablename1 = 'test11_' . mt_rand();
777 // $this->_db->createTableParams($tablename1,
781 // 'type' => 'varchar',
787 // 'name' => 'idx_'. $tablename1,
788 // 'type' => 'index',
789 // 'fields' => array('foo'),
793 // $tablename2 = 'test12_' . mt_rand();
794 // $this->_db->createTableParams($tablename2,
798 // 'type' => 'varchar',
804 // 'name' => 'idx_'. $tablename2,
805 // 'type' => 'index',
806 // 'fields' => array('foo'),
811 // $res = $this->_db->compareIndexInTables(
812 // 'idx_foo', $tablename1, $tablename2);
814 // $this->assertEquals($res['msg'],'not_exists_table1');
816 // $this->_db->dropTableName($tablename1);
817 // $this->_db->dropTableName($tablename2);
820 // public function testCompareIndexInTablesNotInTable2()
822 // $tablename1 = 'test13_' . mt_rand();
823 // $this->_db->createTableParams($tablename1,
827 // 'type' => 'varchar',
833 // 'name' => 'idx_'. $tablename1,
834 // 'type' => 'index',
835 // 'fields' => array('foo'),
839 // $tablename2 = 'test14_' . mt_rand();
840 // $this->_db->createTableParams($tablename2,
844 // 'type' => 'varchar',
850 // 'name' => 'idx_'. $tablename2,
851 // 'type' => 'index',
852 // 'fields' => array('foo'),
857 // $res = $this->_db->compareIndexInTables(
858 // 'idx_foo', $tablename1, $tablename2);
860 // $this->assertEquals($res['msg'],'not_exists_table2');
862 // $this->_db->dropTableName($tablename1);
863 // $this->_db->dropTableName($tablename2);
866 // public function testCompareIndexInTablesIndexesDoNotMatch()
868 // $tablename1 = 'test15_' . mt_rand();
869 // $this->_db->createTableParams($tablename1,
873 // 'type' => 'varchar',
879 // 'name' => 'idx_foo',
880 // 'type' => 'index',
881 // 'fields' => array('foo'),
885 // $tablename2 = 'test16_' . mt_rand();
886 // $this->_db->createTableParams($tablename2,
889 // 'name' => 'foobar',
890 // 'type' => 'varchar',
896 // 'name' => 'idx_foo',
897 // 'type' => 'index',
898 // 'fields' => array('foobar'),
903 // $res = $this->_db->compareIndexInTables(
904 // 'idx_foo', $tablename1, $tablename2);
906 // $this->assertEquals($res['msg'],'no_match');
908 // $this->_db->dropTableName($tablename1);
909 // $this->_db->dropTableName($tablename2);
912 public function testCreateIndex()
914 // TODO: Write this test
917 public function testAddIndexes()
919 //TODO Fix test with normal index inspection
920 $this->markTestIncomplete(
921 'TODO Reimplement test not using compareIndexInTables.'
923 $tablename1 = 'test17_' . mt_rand();
924 $this->createTableParams($tablename1,
936 'fields' => array('foo'),
940 $tablename2 = 'test18_' . mt_rand();
941 $this->createTableParams($tablename2,
952 // first test not executing the statement
953 $this->_db->addIndexes(
958 'fields' => array('foo'),
962 $res = $this->_db->compareIndexInTables(
963 'idx_foo', $tablename1, $tablename2);
965 $this->assertEquals($res['msg'],'not_exists_table2');
967 // now, execute the statement
968 $this->_db->addIndexes(
973 'fields' => array('foo'),
976 $res = $this->_db->compareIndexInTables(
977 'idx_foo', $tablename1, $tablename2);
979 $this->assertEquals($res['msg'],'match');
981 $this->dropTableName($tablename1);
982 $this->dropTableName($tablename2);
985 public function testDropIndexes()
987 //TODO Fix test with normal index inspection
988 $this->markTestIncomplete(
989 'TODO Reimplement test not using compareIndexInTables.'
992 $tablename1 = 'test19_' . mt_rand();
993 $this->createTableParams($tablename1,
1003 'name' => 'idx_foo',
1005 'fields' => array('foo'),
1009 $tablename2 = 'test20_' . mt_rand();
1010 $this->createTableParams($tablename2,
1014 'type' => 'varchar',
1020 'name' => 'idx_foo',
1022 'fields' => array('foo'),
1027 $res = $this->_db->compareIndexInTables(
1028 'idx_foo', $tablename1, $tablename2);
1030 $this->assertEquals('match', $res['msg']);
1032 // first test not executing the statement
1033 $this->_db->dropIndexes(
1036 'name' => 'idx_foo',
1038 'fields' => array('foo'),
1042 $res = $this->_db->compareIndexInTables(
1043 'idx_foo', $tablename1, $tablename2);
1045 $this->assertEquals('match', $res['msg']);
1047 // now, execute the statement
1048 $sql = $this->_db->dropIndexes(
1051 'name' => 'idx_foo',
1053 'fields' => array('foo'),
1058 $res = $this->_db->compareIndexInTables(
1059 'idx_foo', $tablename1, $tablename2);
1061 $this->assertEquals('not_exists_table2', $res['msg']);
1063 $this->dropTableName($tablename1);
1064 $this->dropTableName($tablename2);
1067 public function testModifyIndexes()
1069 //TODO Fix test with normal index inspection
1070 $this->markTestIncomplete(
1071 'TODO Reimplement test not using compareIndexInTables.'
1073 $tablename1 = 'test21_' . mt_rand();
1074 $this->createTableParams($tablename1,
1078 'type' => 'varchar',
1083 'type' => 'varchar',
1089 'name' => 'idx_'. $tablename1,
1091 'fields' => array('foo'),
1095 $tablename2 = 'test22_' . mt_rand();
1096 $this->createTableParams($tablename2,
1100 'type' => 'varchar',
1105 'type' => 'varchar',
1111 'name' => 'idx_'. $tablename2,
1113 'fields' => array('foobar'),
1118 $res = $this->_db->compareIndexInTables(
1119 'idx_foo', $tablename1, $tablename2);
1121 $this->assertEquals($res['msg'],'no_match');
1123 $this->_db->modifyIndexes(
1126 'name' => 'idx_foo',
1128 'fields' => array('foo'),
1132 $res = $this->_db->compareIndexInTables(
1133 'idx_foo', $tablename1, $tablename2);
1135 $this->assertEquals($res['msg'],'no_match');
1137 $this->_db->modifyIndexes(
1140 'name' => 'idx_foo',
1142 'fields' => array('foo'),
1146 $res = $this->_db->compareIndexInTables(
1147 'idx_foo', $tablename1, $tablename2);
1149 $this->assertEquals($res['msg'],'match');
1151 $this->dropTableName($tablename1);
1152 $this->dropTableName($tablename2);
1155 public function testAddColumn()
1157 $tablename1 = 'test23_' . mt_rand();
1158 $this->createTableParams($tablename1,
1162 'type' => 'varchar',
1167 'type' => 'varchar',
1173 $tablename2 = 'test24_' . mt_rand();
1174 $this->createTableParams($tablename2,
1178 'type' => 'varchar',
1185 $res = $this->_db->compareFieldInTables(
1186 'foobar', $tablename1, $tablename2);
1188 $this->assertEquals($res['msg'],'not_exists_table2');
1190 $this->_db->addColumn(
1195 'type' => 'varchar',
1201 $res = $this->_db->compareFieldInTables(
1202 'foobar', $tablename1, $tablename2);
1204 $this->assertEquals($res['msg'],'match');
1206 $this->dropTableName($tablename1);
1207 $this->dropTableName($tablename2);
1210 public function alterColumnDataProvider()
1215 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255', 'required' => true, 'default' => 'sugar'),
1216 'temp' => array ('name' => 'foobar', 'type' => 'int') // Check if type conversion works
1220 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255', 'default' => 'kilroy'),
1221 'temp' => array ('name' => 'foobar', 'type' => 'double', 'default' => '99999') // Check if default gets replaced
1225 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255'),
1226 'temp' => array ('name' => 'foobar', 'type' => 'double', 'default' => '99999') // Check if default gets dropped
1230 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255', 'required' => true, 'default' => 'sweet'),
1231 'temp' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '1500',) // Check varchar shortening
1235 'target' => array ('name' => 'foobar', 'type' => 'longtext', 'required' => true),
1236 'temp' => array ('name' => 'foobar', 'type' => 'text', 'default' => 'dextrose') // Check clob(65k) to clob(2M or so) conversion
1240 'target' => array ('name' => 'foobar', 'type' => 'double', 'required' => true),
1241 'temp' => array ('name' => 'foobar', 'type' => 'int', 'default' => 0) // Check int to double change
1249 * @dataProvider alterColumnDataProvider
1255 public function testAlterColumn($i, $target, $temp)
1257 if($this->_db->dbType == "oci8" && ($i == 4 || $i == 6)) {
1258 $this->markTestSkipped("Cannot reliably shrink columns in Oracle");
1261 $foo_col = array ('name' => 'foo', 'type' => 'varchar', 'len' => '255'); // Common column between tables
1263 $tablebase = 'testac_'. mt_rand() . '_';
1265 $t1 = $tablebase . $i .'A';
1266 $t2 = $tablebase . $i .'B';
1267 $this->createTableParams( $t1,
1268 array('foo' => $foo_col, 'foobar' => $target),
1270 $this->createTableParams( $t2,
1271 array('foo' => $foo_col, 'foobar' => $temp),
1274 $res = $this->_db->compareFieldInTables('foobar', $t1, $t2);
1276 $this->assertEquals('no_match', $res['msg'],
1277 "testAlterColumn table columns match while they shouldn't for table $t1 and $t2: "
1278 . print_r($res,true) );
1280 $this->_db->alterColumn($t2, array('foobar' => $target));
1282 $res = $this->_db->compareFieldInTables('foobar', $t1, $t2);
1284 $this->assertEquals('match', $res['msg'],
1285 "testAlterColumn table columns don't match while they should for table $t1 and $t2: "
1286 . print_r($res,true) );
1288 $this->dropTableName($t1);
1289 $this->dropTableName($t2);
1292 public function testDropTable()
1294 // TODO: Write this test
1297 public function testDropTableName()
1299 $tablename = 'test' . mt_rand();
1300 $this->createTableParams($tablename,
1304 'type' => 'varchar',
1310 $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1312 $this->dropTableName($tablename);
1314 $this->assertFalse(in_array($tablename,$this->_db->getTablesArray()));
1317 public function testDeleteColumn()
1319 // TODO: Write this test
1322 public function testDisconnectAll()
1324 DBManagerFactory::disconnectAll();
1325 $this->assertTrue($this->_db->checkError());
1326 $this->_db = DBManagerFactory::getInstance();
1329 public function testQuery()
1331 $beanIds = $this->_createRecords(5);
1333 $result = $this->_db->query("SELECT id From contacts where last_name = 'foobar'");
1334 if ( $this->_db instanceOf MysqliManager )
1335 $this->assertInstanceOf('Mysqli_result',$result);
1337 $this->assertTrue(is_resource($result));
1339 while ( $row = $this->_db->fetchByAssoc($result) )
1340 $this->assertTrue(in_array($row['id'],$beanIds),"Id not found '{$row['id']}'");
1342 $this->_removeRecords($beanIds);
1345 public function disabledLimitQuery()
1347 $beanIds = $this->_createRecords(5);
1348 $_REQUEST['module'] = 'contacts';
1349 $result = $this->_db->limitQuery("SELECT id From contacts where last_name = 'foobar'",1,3);
1350 if ( $this->_db instanceOf MysqliManager )
1351 $this->assertInstanceOf('Mysqli_result',$result);
1353 $this->assertTrue(is_resource($result));
1355 while ( $row = $this->_db->fetchByAssoc($result) ) {
1356 if ( $row['id'][0] > 3 || $row['id'][0] < 0 )
1357 $this->assertFalse(in_array($row['id'],$beanIds),"Found {$row['id']} in error");
1359 $this->assertTrue(in_array($row['id'],$beanIds),"Didn't find {$row['id']}");
1361 unset($_REQUEST['module']);
1362 $this->_removeRecords($beanIds);
1365 public function testGetOne()
1367 $beanIds = $this->_createRecords(1);
1369 $id = $this->_db->getOne("SELECT id From contacts where last_name = 'foobar'");
1370 $this->assertEquals($id,$beanIds[0]);
1373 if ( $this->_db instanceOf MysqlManager ) {
1374 $id = $this->_db->getOne($this->_db->limitQuerySql("SELECT id From contacts where last_name = 'foobar'", 0, 1));
1375 $this->assertEquals($id,$beanIds[0]);
1378 $this->_removeRecords($beanIds);
1381 public function testGetFieldsArray()
1383 $beanIds = $this->_createRecords(1);
1385 $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1386 $fields = $this->_db->getFieldsArray($result,true);
1388 $this->assertEquals(array("id"),$fields);
1390 $this->_removeRecords($beanIds);
1393 public function testGetAffectedRowCount()
1395 if(!$this->_db->supports("affected_rows")) {
1396 $this->markTestSkipped('Skipping, backend doesn\'t support affected rows');
1399 $beanIds = $this->_createRecords(1);
1400 $result = $this->_db->query("DELETE From contacts where id = '{$beanIds[0]}'");
1401 $this->assertEquals(1, $this->_db->getAffectedRowCount($result));
1404 public function testFetchByAssoc()
1406 $beanIds = $this->_createRecords(1);
1408 $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1410 $row = $this->_db->fetchByAssoc($result);
1412 $this->assertTrue(is_array($row));
1413 $this->assertEquals($row['id'],$beanIds[0]);
1415 $this->_removeRecords($beanIds);
1418 public function testConnect()
1420 // TODO: Write this test
1423 public function testDisconnect()
1425 $this->_db->disconnect();
1426 $this->assertTrue($this->_db->checkError());
1427 $this->_db = DBManagerFactory::getInstance();
1430 public function testGetTablesArray()
1432 $tablename = 'test' . mt_rand();
1433 $this->createTableParams($tablename,
1437 'type' => 'varchar',
1444 $this->assertTrue($this->_db->tableExists($tablename));
1446 $this->dropTableName($tablename);
1449 public function testVersion()
1451 $ver = $this->_db->version();
1453 $this->assertTrue(is_string($ver));
1456 public function testTableExists()
1458 $tablename = 'test' . mt_rand();
1459 $this->createTableParams($tablename,
1463 'type' => 'varchar',
1470 $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1472 $this->dropTableName($tablename);
1475 public function providerCompareVardefs()
1477 $returnArray = array(
1481 'type' => 'varchar',
1486 'type' => 'varchar',
1498 'type' => 'varchar',
1520 'type' => 'varchar',
1527 'type' => 'varchar',
1532 'type' => 'varchar',
1538 return $returnArray;
1542 * @dataProvider providerCompareVarDefs
1544 public function testCompareVarDefs($fieldDef1,$fieldDef2,$expectedResult)
1546 if ( $expectedResult ) {
1547 $this->assertTrue($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1550 $this->assertFalse($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1557 public function test_Bug34892_MssqlNotClearingErrorResults()
1559 // execute a bad query
1560 $this->_db->query("select dsdsdsdsdsdsdsdsdsd", false, "test_Bug34892_MssqlNotClearingErrorResults", true);
1561 // assert it found an error
1562 $this->assertNotEmpty($this->_db->lastError(), "lastError should return true as a result of the previous illegal query");
1563 // now, execute a good query
1564 $this->_db->query("select * from config");
1565 // and make no error messages are asserted
1566 $this->assertEmpty($this->_db->lastError(), "lastError should have cleared the previous error and return false of the last legal query");
1569 public function vardefProvider()
1571 $GLOBALS['log']->info('DBManagerTest.vardefProvider: _db = ' . print_r($this->_db));
1572 $this->setUp(); // Just in case the DB driver is not created yet.
1573 $emptydate = $this->_db->emptyValue("date");
1574 $emptytime = $this->_db->emptyValue("time");
1575 $emptydatetime = $this->_db->emptyValue("datetime");
1578 array("testid", array (
1582 'type' => 'varchar',
1586 array("id" => "test123"),
1587 array("id" => "'test123'")
1589 array("testtext", array (
1593 'type' => 'varchar',
1599 'type' => 'varchar',
1603 array("text1" => "''"),
1606 array("testtext2", array (
1610 'type' => 'varchar',
1616 'type' => 'varchar',
1619 array('text1' => 'foo', 'text2' => 'bar'),
1620 array("text1" => "'foo'", 'text2' => "'bar'"),
1622 array("testreq", array (
1626 'type' => 'varchar',
1637 'name' => 'floatval',
1638 'type' => 'decimal',
1644 'type' => 'currency',
1649 'name' => 'test_dtm',
1650 'type' => 'datetime',
1655 'name' => 'test_dtm2',
1656 'type' => 'datetimecombo',
1661 'name' => 'test_dt',
1667 'name' => 'test_tm',
1672 array("id" => "test123", 'intval' => 42, 'floatval' => 42.24,
1673 'money' => 56.78, 'test_dtm' => '2002-01-02 12:34:56', 'test_dtm2' => '2011-10-08 01:02:03',
1674 'test_dt' => '1998-10-04', 'test_tm' => '03:04:05'
1676 array("id" => "'test123'", 'intval' => 42, 'floatval' => 42.24,
1677 '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'),
1678 'test_dt' => $this->_db->convert('\'1998-10-04\'', 'date'), 'test_tm' => $this->_db->convert('\'03:04:05\'', 'time')
1681 array("testreqnull", array (
1685 'type' => 'varchar',
1696 'name' => 'floatval',
1697 'type' => 'decimal',
1703 'type' => 'currency',
1708 'name' => 'test_dtm',
1709 'type' => 'datetime',
1714 'name' => 'test_dtm2',
1715 'type' => 'datetimecombo',
1720 'name' => 'test_dt',
1726 'name' => 'test_tm',
1732 array("id" => "''", 'intval' => 0, 'floatval' => 0,
1733 'money' => 0, 'test_dtm' => "$emptydatetime", 'test_dtm2' => "$emptydatetime",
1734 'test_dt' => "$emptydate", 'test_tm' => "$emptytime"
1738 array("testnull", array (
1742 'type' => 'varchar',
1751 'name' => 'floatval',
1752 'type' => 'decimal',
1757 'type' => 'currency',
1761 'name' => 'test_dtm',
1762 'type' => 'datetime',
1766 'name' => 'test_dtm2',
1767 'type' => 'datetimecombo',
1771 'name' => 'test_dt',
1776 'name' => 'test_tm',
1781 array("id" => "'123'"),
1784 array("testempty", array (
1788 'type' => 'varchar',
1797 'name' => 'floatval',
1798 'type' => 'decimal',
1803 'type' => 'currency',
1807 'name' => 'test_dtm',
1808 'type' => 'datetime',
1812 'name' => 'test_dtm2',
1813 'type' => 'datetimecombo',
1817 'name' => 'test_dt',
1822 'name' => 'test_tm',
1827 'name' => 'test_txt',
1828 'type' => 'varchar',
1831 array("id" => "", 'intval' => '', 'floatval' => '',
1832 'money' => '', 'test_dtm' => '', 'test_dtm2' => '',
1833 'test_dt' => '', 'test_tm' => '', 'text_txt' => null
1835 array("id" => "''", 'intval' => 0, 'floatval' => 0,
1836 'money' => 0, 'test_dtm' => "NULL", 'test_dtm2' => "NULL",
1837 'test_dt' => "NULL", 'test_tm' => 'NULL'
1839 array('intval' => 'NULL', 'floatval' => 'NULL',
1840 'money' => 'NULL', 'test_dtm' => 'NULL', 'test_dtm2' => 'NULL',
1841 'test_dt' => 'NULL', 'test_tm' => 'NULL'
1848 * Test InserSQL functions
1849 * @dataProvider vardefProvider
1850 * @param string $name
1851 * @param array $defs
1852 * @param array $data
1853 * @param array $result
1855 public function testInsertSQL($name, $defs, $data, $result)
1861 $obj = new TestSugarBean($name, $vardefs);
1863 foreach($data as $k => $v) {
1866 $sql = $this->_db->insertSQL($obj);
1867 $names = join('\s*,\s*',array_map('preg_quote', array_keys($result)));
1868 $values = join('\s*,\s*',array_map('preg_quote', array_values($result)));
1869 $this->assertRegExp("/INSERT INTO $name\s+\(\s*$names\s*\)\s+VALUES\s+\(\s*$values\s*\)/is", $sql, "Bad sql: $sql");
1873 * Test UpdateSQL functions
1874 * @dataProvider vardefProvider
1875 * @param string $name
1876 * @param array $defs
1877 * @param array $data
1879 * @param array $result
1881 public function testUpdateSQL($name, $defs, $data, $_, $result = null)
1883 $name = "update$name";
1888 // ensure it has an ID
1889 $vardefs['fields']['id'] = array (
1894 $vardefs['fields']['deleted'] = array (
1895 'name' => 'deleted',
1899 $obj = new TestSugarBean($name, $vardefs);
1901 foreach($defs as $k => $v) {
1902 if(isset($data[$k])) {
1903 $obj->$k = $data[$k];
1909 $obj->id = 'test_ID';
1910 $sql = $this->_db->updateSQL($obj);
1911 if(is_null($result)) {
1915 foreach($result as $k => $v) {
1916 if($k == "id" || $k == 'deleted') continue;
1917 $names_i[] = preg_quote("$k=$v");
1919 if(empty($names_i)) {
1920 $this->assertEquals("", $sql, "Bad sql: $sql");
1923 $names = join('\s*,\s*',$names_i);
1924 $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");
1928 * Test UpdateSQL functions
1929 * @dataProvider vardefProvider
1930 * @param string $name
1931 * @param array $defs
1932 * @param array $data
1934 * @param array $result
1936 public function testUpdateSQLNoDeleted($name, $defs, $data, $_, $result = null)
1938 $name = "updatenodel$name";
1943 // ensure it has an ID
1944 $vardefs['fields']['id'] = array (
1949 unset($vardefs['fields']['deleted']);
1951 $obj = new TestSugarBean($name, $vardefs);
1953 foreach($defs as $k => $v) {
1954 if(isset($data[$k])) {
1955 $obj->$k = $data[$k];
1961 $obj->id = 'test_ID';
1962 $sql = $this->_db->updateSQL($obj);
1963 if(is_null($result)) {
1967 foreach($result as $k => $v) {
1968 if($k == "id" || $k == 'deleted') continue;
1969 $names_i[] = preg_quote("$k=$v");
1971 if(empty($names_i)) {
1972 $this->assertEquals("", $sql, "Bad sql: $sql");
1975 $names = join('\s*,\s*',$names_i);
1976 $this->assertRegExp("/UPDATE $name\s+SET\s+$names\s+WHERE\s+$name.id\s*=\s*'test_ID'/is", $sql, "Bad sql: $sql");
1977 $this->assertNotContains(" AND deleted=0", $sql, "Bad sql: $sql");
1981 * Test the canInstall
1984 public function testCanInstall() {
1985 $DBManagerClass = get_class($this->_db);
1986 if(!method_exists($this->_db, 'version') || !method_exists($this->_db, 'canInstall'))
1987 $this->markTestSkipped(
1988 "Class {$DBManagerClass} doesn't implement canInstall or version methods");
1990 $method = new ReflectionMethod($DBManagerClass, 'canInstall');
1991 if($method->class == 'DBManager')
1992 $this->markTestSkipped(
1993 "Class {$DBManagerClass} or one of it's ancestors doesn't override DBManager's canInstall");
1995 // First assuming that we are only running unit tests against a supported database :)
1996 $this->assertTrue($this->_db->canInstall(), "Apparently we are not running this unit test against a supported database!!!");
1998 $DBstub = $this->getMock($DBManagerClass, array('version'));
1999 $DBstub->expects($this->any())
2001 ->will($this->returnValue('0.0.0')); // Expect that any supported version is higher than 0.0.0
2003 $this->assertTrue(is_array($DBstub->canInstall()), "Apparently we do support version 0.0.0 in " . $DBManagerClass);
2006 public function providerValidateQuery()
2009 array(true, 'SELECT * FROM accounts'),
2010 array(false, 'SELECT * FROM blablabla123'),
2015 * Test query validation
2016 * @dataProvider providerValidateQuery
2021 public function testValidateQuery($good, $sql)
2023 $check = $this->_db->validateQuery($sql);
2024 $this->assertEquals($good, $check);
2027 public function testTextSizeHandling()
2029 $tablename = 'testTextSize';// . mt_rand();
2039 'type' => 'longtext',
2044 'type' => 'longtext',
2049 $this->createTableParams($tablename, $fielddefs, array());
2050 $basestr = '0123456789abcdefghijklmnopqrstuvwxyz';
2052 while(strlen($str) < 159900)
2057 for($i = 0; $i < 50; $i++)
2060 $size = strlen($str);
2062 $this->_db->insertParams($tablename, $fielddefs, array('id' => $size, 'test' => $str, 'dummy' => $str));
2064 $select = "SELECT test FROM $tablename WHERE id = '$size'";
2065 $strresult = $this->_db->getOne($select);
2067 $this->assertEquals(0, mb_strpos($str, $strresult));
2071 public function testGetIndicesContainsPrimary()
2073 $indices = $this->_db->get_indices('accounts');
2075 // find if any are primary
2078 foreach($indices as $index)
2080 if($index['type'] == "primary") {
2086 $this->assertTrue($found, 'Primary Key Not Found On Module');
2090 * testDBGuidGeneration
2091 * Tests that the first 1000 DB generated GUIDs are unique
2093 public function testDBGuidGeneration()
2096 $sql = "SELECT {$this->_db->getGuidSQL()} {$this->_db->getFromDummyTable()}";
2097 for($i = 0; $i < 1000; $i++)
2099 $newguid = $this->_db->getOne($sql);
2100 $this->assertFalse(in_array($newguid, $guids), "'$newguid' already existed in the array of GUIDs!");
2101 $guids []= $newguid;