2 require_once 'include/database/DBManagerFactory.php';
3 require_once 'modules/Contacts/Contact.php';
5 class DBManagerTest extends Sugar_PHPUnit_Framework_TestCase
9 protected $backupGlobals = FALSE;
11 public function setUp()
13 $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
14 $this->_db = DBManagerFactory::getInstance();
15 $GLOBALS['app_strings'] = return_application_language($GLOBALS['current_language']);
18 public function tearDown()
20 SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
21 unset($GLOBALS['current_user']);
22 unset($GLOBALS['app_strings']);
25 private function _createRecords(
30 for ( $i = 0; $i < $num; $i++ ) {
31 $bean = new Contact();
32 $bean->id = "$i-test" . date("YmdHis");
33 $bean->last_name = "foobar";
34 $this->_db->insert($bean);
35 $beanIds[] = $bean->id;
41 private function _removeRecords(
46 $this->_db->query("DELETE From contacts where id = '{$id}'");
49 public function testGetTableName()
51 $this->_db->createTableParams('MyTableName',array('foo'=>'foo'),array());
53 $this->assertEquals($this->_db->getTableName(),'MyTableName');
56 public function testGetDatabase()
58 if ( $this->_db instanceOf MysqliManager )
59 $this->assertType('Mysqli',$this->_db->getDatabase());
61 $this->assertTrue(is_resource($this->_db->getDatabase()));
64 public function testGetHelper()
66 $this->assertType('DBHelper',$this->_db->getHelper());
69 public function testCheckError()
71 $this->assertFalse($this->_db->checkError());
74 public function testCheckErrorNoConnection()
76 $this->_db->disconnect();
77 $this->assertTrue($this->_db->checkError());
78 $this->_db = &DBManagerFactory::getInstance();
81 public function testGetQueryTime()
83 $this->_db->version();
84 $this->assertTrue($this->_db->getQueryTime() > 0);
87 public function testCheckConnection()
89 $this->_db->checkConnection();
90 if ( $this->_db instanceOf MysqliManager )
91 $this->assertType('Mysqli',$this->_db->getDatabase());
93 $this->assertTrue(is_resource($this->_db->getDatabase()));
96 public function testInsert()
98 $bean = new Contact();
99 $bean->last_name = 'foobar' . date("YmdHis");
100 $bean->id = 'test' . date("YmdHis");
101 $this->_db->insert($bean);
103 $result = $this->_db->query("select id, last_name from contacts where id = '{$bean->id}'");
104 $row = $this->_db->fetchByAssoc($result);
105 $this->assertEquals($row['last_name'],$bean->last_name);
106 $this->assertEquals($row['id'],$bean->id);
108 $this->_db->query("delete from contacts where id = '{$row['id']}'");
111 public function testUpdate()
113 $bean = new Contact();
114 $bean->last_name = 'foobar' . date("YmdHis");
115 $bean->id = 'test' . date("YmdHis");
116 $this->_db->insert($bean);
119 $bean = new Contact();
120 $bean->last_name = 'newfoobar' . date("YmdHis");
121 $this->_db->update($bean,array('id'=>$id));
123 $result = $this->_db->query("select id, last_name from contacts where id = '{$id}'");
124 $row = $this->_db->fetchByAssoc($result);
125 $this->assertEquals($row['last_name'],$bean->last_name);
126 $this->assertEquals($row['id'],$id);
128 $this->_db->query("delete from contacts where id = '{$row['id']}'");
131 public function testDelete()
133 $bean = new Contact();
134 $bean->last_name = 'foobar' . date("YmdHis");
135 $bean->id = 'test' . date("YmdHis");
136 $this->_db->insert($bean);
139 $bean = new Contact();
140 $this->_db->delete($bean,array('id'=>$id));
142 $result = $this->_db->query("select deleted from contacts where id = '{$id}'");
143 $row = $this->_db->fetchByAssoc($result);
144 $this->assertEquals($row['deleted'],'1');
146 $this->_db->query("delete from contacts where id = '{$id}'");
149 public function testRetrieve()
151 $bean = new Contact();
152 $bean->last_name = 'foobar' . date("YmdHis");
153 $bean->id = 'test' . date("YmdHis");
154 $this->_db->insert($bean);
157 $bean = new Contact();
158 $result = $this->_db->retrieve($bean,array('id'=>$id));
159 $row = $this->_db->fetchByAssoc($result);
160 $this->assertEquals($row['id'],$id);
162 $this->_db->query("delete from contacts where id = '{$id}'");
165 public function testRetrieveView()
167 // TODO: Write this test
170 public function testCreateTable()
172 // TODO: Write this test
175 public function testCreateTableParams()
177 $tablename = 'test' . date("YmdHis");
178 $this->_db->createTableParams($tablename,
190 'fields' => array('foo'),
194 $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
196 $this->_db->dropTableName($tablename);
199 public function testRepairTable()
201 // TODO: Write this test
204 public function testRepairTableParams()
206 // TODO: Write this test
209 public function testCompareFieldInTables()
211 $tablename1 = 'test1_' . date("YmdHis");
212 $this->_db->createTableParams($tablename1,
222 $tablename2 = 'test2_' . date("YmdHis");
223 $this->_db->createTableParams($tablename2,
234 $res = $this->_db->compareFieldInTables(
235 'foo', $tablename1, $tablename2);
237 $this->assertEquals($res['msg'],'match');
239 $this->_db->dropTableName($tablename1);
240 $this->_db->dropTableName($tablename2);
243 public function testCompareFieldInTablesNotInTable1()
245 $tablename1 = 'test3_' . date("YmdHis");
246 $this->_db->createTableParams($tablename1,
256 $tablename2 = 'test4_' . date("YmdHis");
257 $this->_db->createTableParams($tablename2,
268 $res = $this->_db->compareFieldInTables(
269 'foo', $tablename1, $tablename2);
270 $this->assertEquals($res['msg'],'not_exists_table1');
272 $this->_db->dropTableName($tablename1);
273 $this->_db->dropTableName($tablename2);
276 public function testCompareFieldInTablesNotInTable2()
278 $tablename1 = 'test5_' . date("YmdHis");
279 $this->_db->createTableParams($tablename1,
289 $tablename2 = 'test6_' . date("YmdHis");
290 $this->_db->createTableParams($tablename2,
301 $res = $this->_db->compareFieldInTables(
302 'foo', $tablename1, $tablename2);
304 $this->assertEquals($res['msg'],'not_exists_table2');
306 $this->_db->dropTableName($tablename1);
307 $this->_db->dropTableName($tablename2);
310 public function testCompareFieldInTablesFieldsDoNotMatch()
312 $tablename1 = 'test7_' . date("YmdHis");
313 $this->_db->createTableParams($tablename1,
323 $tablename2 = 'test8_' . date("YmdHis");
324 $this->_db->createTableParams($tablename2,
334 $res = $this->_db->compareFieldInTables(
335 'foo', $tablename1, $tablename2);
337 $this->assertEquals($res['msg'],'no_match');
339 $this->_db->dropTableName($tablename1);
340 $this->_db->dropTableName($tablename2);
343 public function testCompareIndexInTables()
345 $tablename1 = 'test9_' . date("YmdHis");
346 $this->_db->createTableParams($tablename1,
358 'fields' => array('foo'),
362 $tablename2 = 'test10_' . date("YmdHis");
363 $this->_db->createTableParams($tablename2,
375 'fields' => array('foo'),
380 $res = $this->_db->compareIndexInTables(
381 'idx_foo', $tablename1, $tablename2);
383 $this->assertEquals($res['msg'],'match');
385 $this->_db->dropTableName($tablename1);
386 $this->_db->dropTableName($tablename2);
389 public function testCompareIndexInTablesNotInTable1()
391 $tablename1 = 'test11_' . date("YmdHis");
392 $this->_db->createTableParams($tablename1,
402 'name' => 'idx_foobar',
404 'fields' => array('foo'),
408 $tablename2 = 'test12_' . date("YmdHis");
409 $this->_db->createTableParams($tablename2,
421 'fields' => array('foo'),
426 $res = $this->_db->compareIndexInTables(
427 'idx_foo', $tablename1, $tablename2);
429 $this->assertEquals($res['msg'],'not_exists_table1');
431 $this->_db->dropTableName($tablename1);
432 $this->_db->dropTableName($tablename2);
435 public function testCompareIndexInTablesNotInTable2()
437 $tablename1 = 'test13_' . date("YmdHis");
438 $this->_db->createTableParams($tablename1,
450 'fields' => array('foo'),
454 $tablename2 = 'test14_' . date("YmdHis");
455 $this->_db->createTableParams($tablename2,
465 'name' => 'idx_foobar',
467 'fields' => array('foo'),
472 $res = $this->_db->compareIndexInTables(
473 'idx_foo', $tablename1, $tablename2);
475 $this->assertEquals($res['msg'],'not_exists_table2');
477 $this->_db->dropTableName($tablename1);
478 $this->_db->dropTableName($tablename2);
481 public function testCompareIndexInTablesIndexesDoNotMatch()
483 $tablename1 = 'test15_' . date("YmdHis");
484 $this->_db->createTableParams($tablename1,
496 'fields' => array('foo'),
500 $tablename2 = 'test16_' . date("YmdHis");
501 $this->_db->createTableParams($tablename2,
513 'fields' => array('foobar'),
518 $res = $this->_db->compareIndexInTables(
519 'idx_foo', $tablename1, $tablename2);
521 $this->assertEquals($res['msg'],'no_match');
523 $this->_db->dropTableName($tablename1);
524 $this->_db->dropTableName($tablename2);
527 public function testCreateIndex()
529 // TODO: Write this test
532 public function testAddIndexes()
534 $tablename1 = 'test17_' . date("YmdHis");
535 $this->_db->createTableParams($tablename1,
547 'fields' => array('foo'),
551 $tablename2 = 'test18_' . date("YmdHis");
552 $this->_db->createTableParams($tablename2,
563 // first test not executing the statement
564 $this->_db->addIndexes(
569 'fields' => array('foo'),
573 $res = $this->_db->compareIndexInTables(
574 'idx_foo', $tablename1, $tablename2);
576 $this->assertEquals($res['msg'],'not_exists_table2');
578 // now, execute the statement
579 $this->_db->addIndexes(
584 'fields' => array('foo'),
587 $res = $this->_db->compareIndexInTables(
588 'idx_foo', $tablename1, $tablename2);
590 $this->assertEquals($res['msg'],'match');
592 $this->_db->dropTableName($tablename1);
593 $this->_db->dropTableName($tablename2);
596 public function testDropIndexes()
598 $tablename1 = 'test19_' . date("YmdHis");
599 $this->_db->createTableParams($tablename1,
611 'fields' => array('foo'),
615 $tablename2 = 'test20_' . date("YmdHis");
616 $this->_db->createTableParams($tablename2,
628 'fields' => array('foo'),
633 $res = $this->_db->compareIndexInTables(
634 'idx_foo', $tablename1, $tablename2);
636 $this->assertEquals($res['msg'],'match');
638 // first test not executing the statement
639 $this->_db->dropIndexes(
644 'fields' => array('foo'),
648 $res = $this->_db->compareIndexInTables(
649 'idx_foo', $tablename1, $tablename2);
651 $this->assertEquals($res['msg'],'match');
653 // now, execute the statement
654 $sql = $this->_db->dropIndexes(
659 'fields' => array('foo'),
664 $res = $this->_db->compareIndexInTables(
665 'idx_foo', $tablename1, $tablename2);
667 $this->assertEquals($res['msg'],'not_exists_table2');
669 $this->_db->dropTableName($tablename1);
670 $this->_db->dropTableName($tablename2);
673 public function testModifyIndexes()
675 $tablename1 = 'test21_' . date("YmdHis");
676 $this->_db->createTableParams($tablename1,
693 'fields' => array('foo'),
697 $tablename2 = 'test22_' . date("YmdHis");
698 $this->_db->createTableParams($tablename2,
715 'fields' => array('foobar'),
720 $res = $this->_db->compareIndexInTables(
721 'idx_foo', $tablename1, $tablename2);
723 $this->assertEquals($res['msg'],'no_match');
725 $this->_db->modifyIndexes(
730 'fields' => array('foo'),
734 $res = $this->_db->compareIndexInTables(
735 'idx_foo', $tablename1, $tablename2);
737 $this->assertEquals($res['msg'],'no_match');
739 $this->_db->modifyIndexes(
744 'fields' => array('foo'),
748 $res = $this->_db->compareIndexInTables(
749 'idx_foo', $tablename1, $tablename2);
751 $this->assertEquals($res['msg'],'match');
753 $this->_db->dropTableName($tablename1);
754 $this->_db->dropTableName($tablename2);
757 public function testAddColumn()
759 $tablename1 = 'test23_' . date("YmdHis");
760 $this->_db->createTableParams($tablename1,
775 $tablename2 = 'test24_' . date("YmdHis");
776 $this->_db->createTableParams($tablename2,
787 $res = $this->_db->compareFieldInTables(
788 'foobar', $tablename1, $tablename2);
790 $this->assertEquals($res['msg'],'not_exists_table2');
792 $this->_db->addColumn(
803 $res = $this->_db->compareFieldInTables(
804 'foobar', $tablename1, $tablename2);
806 $this->assertEquals($res['msg'],'match');
808 $this->_db->dropTableName($tablename1);
809 $this->_db->dropTableName($tablename2);
812 public function testAlterColumn()
814 $tablename1 = 'test25_' . date("YmdHis");
815 $this->_db->createTableParams($tablename1,
831 $tablename2 = 'test26_' . date("YmdHis");
832 $this->_db->createTableParams($tablename2,
847 $res = $this->_db->compareFieldInTables(
848 'foobar', $tablename1, $tablename2);
850 $this->assertEquals($res['msg'],'no_match');
852 $this->_db->alterColumn(
864 $res = $this->_db->compareFieldInTables(
865 'foobar', $tablename1, $tablename2);
867 $this->assertEquals($res['msg'],'match');
869 $this->_db->dropTableName($tablename1);
870 $this->_db->dropTableName($tablename2);
873 public function testDropTable()
875 // TODO: Write this test
878 public function testDropTableName()
880 $tablename = 'test' . date("YmdHis");
881 $this->_db->createTableParams($tablename,
891 $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
893 $this->_db->dropTableName($tablename);
895 $this->assertFalse(in_array($tablename,$this->_db->getTablesArray()));
898 public function testDeleteColumn()
900 // TODO: Write this test
903 public function testDisconnectAll()
905 $this->_db->disconnectAll();
906 $this->assertTrue($this->_db->checkError());
907 $this->_db = &DBManagerFactory::getInstance();
910 public function testQuote()
912 $string = "'dog eat ";
914 if ( $this->_db->dbType == 'mysql')
915 $this->assertEquals($this->_db->quoteForEmail($string),"\'dog eat ");
917 $this->assertEquals($this->_db->quoteForEmail($string),"''dog eat ");
920 public function testQuoteForEmail()
922 $string = "'dog eat ";
924 if ( $this->_db->dbType == 'mysql')
925 $this->assertEquals($this->_db->quoteForEmail($string),"\'dog eat ");
927 $this->assertEquals($this->_db->quoteForEmail($string),"''dog eat ");
930 public function testArrayQuote()
932 $string = array("'dog eat ");
933 $this->_db->arrayQuote($string);
934 if ( $this->_db->dbType == 'mysql')
935 $this->assertEquals($string,array("\'dog eat "));
937 $this->assertEquals($string,array("''dog eat "));
940 public function testQuery()
942 $beanIds = $this->_createRecords(5);
944 $result = $this->_db->query("SELECT id From contacts where last_name = 'foobar'");
945 if ( $this->_db instanceOf MysqliManager )
946 $this->assertType('Mysqli_result',$result);
948 $this->assertTrue(is_resource($result));
950 while ( $row = $this->_db->fetchByAssoc($result) )
951 $this->assertTrue(in_array($row['id'],$beanIds),"Id not found '{$row['id']}'");
953 $this->_removeRecords($beanIds);
956 public function disabledLimitQuery()
958 $beanIds = $this->_createRecords(5);
959 $_REQUEST['module'] = 'contacts';
960 $result = $this->_db->limitQuery("SELECT id From contacts where last_name = 'foobar'",1,3);
961 if ( $this->_db instanceOf MysqliManager )
962 $this->assertType('Mysqli_result',$result);
964 $this->assertTrue(is_resource($result));
966 while ( $row = $this->_db->fetchByAssoc($result) ) {
967 if ( $row['id'][0] > 3 || $row['id'][0] < 0 )
968 $this->assertFalse(in_array($row['id'],$beanIds),"Found {$row['id']} in error");
970 $this->assertTrue(in_array($row['id'],$beanIds),"Didn't find {$row['id']}");
972 unset($_REQUEST['module']);
973 $this->_removeRecords($beanIds);
976 public function testGetOne()
978 $beanIds = $this->_createRecords(1);
980 $id = $this->_db->getOne("SELECT id From contacts where last_name = 'foobar'");
981 $this->assertEquals($id,$beanIds[0]);
983 $this->_removeRecords($beanIds);
986 public function testGetFieldsArray()
988 $beanIds = $this->_createRecords(1);
990 $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
991 $fields = $this->_db->getFieldsArray($result,true);
993 $this->assertEquals(array("id"),$fields);
995 $this->_removeRecords($beanIds);
998 public function testGetRowCount()
1000 $beanIds = $this->_createRecords(1);
1002 $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1004 $this->assertEquals($this->_db->getRowCount($result),1);
1006 $this->_removeRecords($beanIds);
1009 public function testGetAffectedRowCount()
1011 if ( ($this->_db instanceOf MysqliManager) )
1012 $this->markTestSkipped('Skipping on Mysqli; doesn\'t apply to this backend');
1014 $beanIds = $this->_createRecords(1);
1015 $result = $this->_db->query("DELETE From contacts where id = '{$beanIds[0]}'");
1016 $this->assertEquals($this->_db->getAffectedRowCount(),1);
1019 public function testFetchByAssoc()
1021 $beanIds = $this->_createRecords(1);
1023 $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1025 $row = $this->_db->fetchByAssoc($result);
1027 $this->assertTrue(is_array($row));
1028 $this->assertEquals($row['id'],$beanIds[0]);
1030 $this->_removeRecords($beanIds);
1033 public function testConnect()
1035 // TODO: Write this test
1038 public function testDisconnect()
1040 $this->_db->disconnect();
1041 $this->assertTrue($this->_db->checkError());
1042 $this->_db = &DBManagerFactory::getInstance();
1045 public function testGetTablesArray()
1047 $tablename = 'test' . date("YmdHis");
1048 $this->_db->createTableParams($tablename,
1052 'type' => 'varchar',
1059 $this->assertTrue($this->_db->tableExists($tablename));
1061 $this->_db->dropTableName($tablename);
1064 public function testVersion()
1066 $ver = $this->_db->version();
1068 $this->assertTrue(is_string($ver));
1071 public function testTableExists()
1073 $tablename = 'test' . date("YmdHis");
1074 $this->_db->createTableParams($tablename,
1078 'type' => 'varchar',
1085 $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1087 $this->_db->dropTableName($tablename);
1090 public function providerCompareVardefs()
1092 $returnArray = array(
1096 'type' => 'varchar',
1101 'type' => 'varchar',
1113 'type' => 'varchar',
1135 'type' => 'varchar',
1142 'type' => 'varchar',
1147 'type' => 'varchar',
1153 return $returnArray;
1157 * @dataProvider providerCompareVarDefs
1159 public function testCompareVarDefs($fieldDef1,$fieldDef2,$expectedResult)
1161 if ( $expectedResult ) {
1162 $this->assertTrue($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1165 $this->assertFalse($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1169 public function providerConvert()
1171 $db = DBManagerFactory::getInstance();
1173 $returnArray = array(
1175 array('foo','nothing'),
1179 if ( $db instanceOf MysqlManager )
1180 $returnArray += array(
1182 array('foo','today'),
1186 array('foo','left'),
1190 array('foo','left',array('1','2','3')),
1194 array('foo','date_format'),
1198 array('foo','date_format',array('1','2','3')),
1199 'DATE_FORMAT(foo,1,2,3)'
1202 array('foo','datetime',array("'%Y-%m'")),
1203 'DATE_FORMAT(foo, \'%Y-%m-%d %H:%i:%s\')'
1206 array('foo','IFNULL'),
1210 array('foo','IFNULL',array('1','2','3')),
1214 array('foo','CONCAT',array('1','2','3')),
1218 array('foo','text2char'),
1222 if ( $db instanceOf MssqlManager )
1223 $returnArray += array(
1225 array('foo','today'),
1229 array('foo','left'),
1233 array('foo','left',array('1','2','3')),
1237 array('foo','date_format'),
1238 'CONVERT(varchar(10),foo,120)'
1241 array('foo','date_format',array('1','2','3')),
1242 'CONVERT(varchar(10),foo,120)'
1245 array('foo','date_format',array("'%Y-%m'")),
1246 'CONVERT(varchar(7),foo,120)'
1249 array('foo','IFNULL'),
1253 array('foo','IFNULL',array('1','2','3')),
1257 array('foo','CONCAT',array('1','2','3')),
1261 array('foo','text2char'),
1262 'CAST(foo AS varchar(8000))'
1265 if ( $db instanceOf SqlsrvManager )
1266 $returnArray += array(
1268 array('foo','datetime'),
1269 'CONVERT(varchar(20),foo,120)'
1273 return $returnArray;
1278 * @dataProvider providerConvert
1280 public function testConvert(
1285 if ( count($parameters) < 3 )
1286 $this->assertEquals(
1287 $this->_db->convert($parameters[0],$parameters[1]),
1289 elseif ( count($parameters) < 4 )
1290 $this->assertEquals(
1291 $this->_db->convert($parameters[0],$parameters[1],$parameters[2]),
1294 $this->assertEquals(
1295 $this->_db->convert($parameters[0],$parameters[1],$parameters[2],$parameters[3]),
1302 public function testConcat()
1304 $ret = $this->_db->concat('foo',array('col1','col2','col3'));
1306 if ( $this->_db instanceOf MysqlManager )
1307 $this->assertEquals($ret,
1308 "CONCAT(IFNULL(foo.col1,''),' ',IFNULL(foo.col2,''),' ',IFNULL(foo.col3,''))"
1310 if ( $this->_db instanceOf MssqlManager )
1311 $this->assertEquals($ret,
1312 "CONCAT(IFNULL(foo.col1,''),' ',IFNULL(foo.col2,''),' ',IFNULL(foo.col3,''))"
1314 if ( $this->_db instanceOf OracleManager )
1315 $this->assertEquals($ret,
1316 "CONCAT(IFNULL(foo.col1,''),' ',IFNULL(foo.col2,''),' ',IFNULL(foo.col3,''))"
1320 public function providerFromConvert()
1322 $returnArray = array(
1324 array('foo','nothing'),
1328 if ( $this->_db instanceOf MssqlManager
1329 || $this->_db instanceOf OracleManager )
1330 $returnArray += array(
1332 array('2009-01-01 12:00:00','date'),
1336 array('2009-01-01 12:00:00','time'),
1341 return $returnArray;
1346 * @dataProvider providerFromConvert
1348 public function testFromConvert(
1353 $this->assertEquals(
1354 $this->_db->fromConvert($parameters[0],$parameters[1]),
1361 public function testMssqlNotClearingErrorResults()
1363 if ( get_class($this->_db) != 'MssqlManager' )
1364 $this->markTestSkipped('Skipping; only applies with php_mssql driver');
1366 // execute a bad query
1367 $this->_db->query("select dsdsdsdsdsdsdsdsdsd");
1368 // assert it found an error
1369 $this->assertTrue($this->_db->checkError());
1370 // now, execute a good query
1371 $this->_db->query("select * from config");
1372 // and make no error messages are asserted
1373 $this->assertFalse($this->_db->checkError());