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 'modules/Cases/Case.php';
42 class DBHelperTest extends Sugar_PHPUnit_Framework_TestCase
47 static public function setupBeforeClass()
49 $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
50 $GLOBALS['app_strings'] = return_application_language($GLOBALS['current_language']);
53 static public function tearDownAfterClass()
55 SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
56 unset($GLOBALS['current_user']);
57 unset($GLOBALS['app_strings']);
60 public function setUp()
62 $this->_db = DBManagerFactory::getInstance();
63 $this->_helper = $this->_db;
66 public function tearDown()
68 $this->_db->disconnect();
71 public function testCreateTableSQL()
73 $sql = $this->_helper->createTableSQL(new Contact);
75 $this->assertRegExp('/create\s*table\s*contacts/i',$sql);
78 public function testCreateTableSQLParams()
82 $sql = $this->_helper->createTableSQLParams(
83 $bean->getTableName(),
84 $bean->getFieldDefinitions(),
87 $this->assertRegExp('/create\s*table\s*contacts/i',$sql);
90 public function testInsertSQL()
92 $sql = $this->_helper->insertSQL(new Contact);
94 $this->assertRegExp('/insert\s*into\s*contacts/i',$sql);
100 public function testInsertSQLProperlyDecodesHtmlEntities()
103 $bean->last_name = '"Test"';
105 $sql = $this->_helper->insertSQL($bean);
107 $this->assertNotContains(""",$sql);
110 public function testUpdateSQL()
112 $sql = $this->_helper->updateSQL(new Contact, array("id" => "1"));
114 $this->assertRegExp('/update\s*contacts\s*set/i',$sql);
115 $this->assertRegExp('/where\s*contacts.id\s*=\s*\'1\'/i',$sql);
121 public function testUpdateSQLProperlyDecodesHtmlEntities()
124 $bean->last_name = '"Test"';
126 $sql = $this->_helper->updateSQL($bean, array("id" => "1"));
128 $this->assertNotContains(""",$sql);
131 public function testDeleteSQL()
133 $sql = $this->_helper->deleteSQL(new Contact, array("id" => "1"));
135 $this->assertRegExp('/update\s*contacts\s*set\s*deleted\s*=\s*1/i',$sql);
136 $this->assertRegExp('/where\s*contacts.id\s*=\s*\'1\'/i',$sql);
139 public function testRetrieveSQL()
141 $sql = $this->_helper->retrieveSQL(new Contact, array("id" => "1"));
143 $this->assertRegExp('/select\s*\*\s*from\s*contacts/i',$sql);
144 $this->assertRegExp('/where\s*contacts.id\s*=\s*\'1\'/i',$sql);
147 public function testRetrieveViewSQL()
149 // TODO: write this test
152 public function testCreateIndexSQL()
154 $sql = $this->_helper->createIndexSQL(
156 array('id' => array('name'=>'id')),
159 $this->assertRegExp('/create\s*unique\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*\)/i',$sql);
161 $sql = $this->_helper->createIndexSQL(
163 array('id' => array('name'=>'id')),
167 $this->assertRegExp('/create\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*\)/i',$sql);
169 $sql = $this->_helper->createIndexSQL(
171 array('id' => array('name'=>'id'),'deleted' => array('name'=>'deleted')),
174 $this->assertRegExp('/create\s*unique\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*,\s*deleted\s*\)/i',$sql);
177 public function testGetFieldType()
180 'dbType' => 'varchar',
184 'data_type' => 'email',
187 $this->assertEquals($this->_helper->getFieldType($fieldDef),'varchar');
188 unset($fieldDef['dbType']);
189 $this->assertEquals($this->_helper->getFieldType($fieldDef),'int');
190 unset($fieldDef['dbtype']);
191 $this->assertEquals($this->_helper->getFieldType($fieldDef),'char');
192 unset($fieldDef['type']);
193 $this->assertEquals($this->_helper->getFieldType($fieldDef),'bool');
194 unset($fieldDef['Type']);
195 $this->assertEquals($this->_helper->getFieldType($fieldDef),'email');
197 public function testGetAutoIncrement()
202 $case->retrieve($case->id);
203 $lastAuto = $case->case_number;
204 $helperResult = $this->_helper->getAutoIncrement("cases", "case_number");
206 // $GLOBALS['db']->query("DELETE FROM cases WHERE id= '{$case->id}'");
208 $this->assertEquals($lastAuto + 1, $helperResult);
210 public function testSetAutoIncrementStart()
215 $case->retrieve($case->id);
216 $lastAuto = $case->case_number;
217 $case->deleted = true;
219 $newAuto = $lastAuto + 5;
220 $this->_helper->setAutoIncrementStart("cases", "case_number", $newAuto);
221 $case2 = new aCase();
222 $case2->name = "foo2";
224 $case2->retrieve($case2->id);
225 $case_number = $case2->case_number;
227 $GLOBALS['db']->query("DELETE FROM cases WHERE id= '{$case->id}'");
228 $GLOBALS['db']->query("DELETE FROM cases WHERE id= '{$case2->id}'");
230 $this->assertEquals($newAuto, $case_number);
232 public function testAddColumnSQL()
234 $sql = $this->_helper->addColumnSQL(
236 array('foo' => array('name'=>'foo','type'=>'varchar'))
239 $this->assertRegExp('/alter\s*table\s*contacts/i',$sql);
242 public function testAlterColumnSQL()
244 $sql = $this->_helper->alterColumnSQL(
246 array('foo' => array('name'=>'foo','type'=>'varchar'))
249 // Generated SQL may be a sequence of statements
250 switch(gettype($sql)){
254 $this->assertRegExp('/alter\s*table\s*contacts/i',$sql);
259 public function testDropTableSQL()
261 $sql = $this->_helper->dropTableSQL(new Contact);
263 $this->assertRegExp('/drop\s*table.*contacts/i',$sql);
266 public function testDropTableNameSQL()
268 $sql = $this->_helper->dropTableNameSQL('contacts');
270 $this->assertRegExp('/drop\s*table.*contacts/i',$sql);
273 public function testDeleteColumnSQL()
275 $sql = $this->_helper->deleteColumnSQL(
277 array('foo' => array('name'=>'foo','type'=>'varchar'))
279 $this->assertRegExp('/alter\s*table\s*contacts\s*drop\s*column\s*foo/i',$sql);
282 public function testDropColumnSQL()
284 $sql = $this->_helper->dropColumnSQL(
286 array('foo' => array('name'=>'foo','type'=>'varchar'))
288 $this->assertRegExp('/alter\s*table\s*contacts\s*drop\s*column\s*foo/i',$sql);
291 public function testMassageValue()
294 $this->_helper->massageValue(123,array('name'=>'foo','type'=>'int')),
297 if (in_array($this->_db->dbType, array('mssql'
300 $this->_helper->massageValue("'dog'",array('name'=>'foo','type'=>'varchar')),
305 $this->_helper->massageValue("'dog'",array('name'=>'foo','type'=>'varchar')),
310 public function testGetColumnType()
312 switch($this->_db->dbType){
314 $expected_type = 'int';
317 $this->assertEquals($expected_type, $this->_helper->getColumnType('int'));
320 public function testIsFieldArray()
323 $this->_helper->isFieldArray(array('name'=>'foo','type'=>array('int')))
327 $this->_helper->isFieldArray(array('name'=>'foo','type'=>'int'))
331 $this->_helper->isFieldArray(array('name'=>'foo'))
335 $this->_helper->isFieldArray(1)
339 public function testSaveAuditRecords()
341 // TODO: write this test
344 public function testGetDataChanges()
346 // TODO: write this test
349 public function testQuoted()
352 "'".$this->_db->quote('foobar')."'",
353 $this->_db->quoted('foobar')
357 public function testGetIndices()
359 $indices = $this->_helper->get_indices('contacts');
361 foreach ( $indices as $index ) {
362 $this->assertTrue(!empty($index['name']));
363 $this->assertTrue(!empty($index['type']));
364 $this->assertTrue(!empty($index['fields']));
368 public function testAddDropConstraint()
370 $tablename = 'test' . date("YmdHis");
371 $sql = $this->_helper->add_drop_constraint(
376 'fields' => array('foo'),
381 $this->assertRegExp("/idx_foo/i",$sql);
382 $this->assertRegExp("/foo/i",$sql);
384 $tablename = 'test' . date("YmdHis");
385 $sql = $this->_helper->add_drop_constraint(
390 'fields' => array('foo'),
395 $this->assertRegExp("/idx_foo/i",$sql);
396 $this->assertRegExp("/foo/i",$sql);
397 $this->assertRegExp("/drop/i",$sql);
400 public function testRenameIndex()
402 // TODO: write this test
405 public function testNumberOfColumns()
407 $tablename = 'test' . date("YmdHis");
408 $this->_db->createTableParams($tablename,
419 $this->assertEquals($this->_helper->number_of_columns($tablename),1);
421 $this->_db->dropTableName($tablename);
424 public function testGetColumns()
426 $vardefs = $this->_helper->get_columns('contacts');
428 $this->assertTrue(isset($vardefs['id']));
429 $this->assertTrue(isset($vardefs['id']['name']));
430 $this->assertTrue(isset($vardefs['id']['type']));
433 public function testMassageFieldDefs()
435 // TODO: write this test
441 public function testEmptyPrecision()
443 $sql = $this->_helper->alterColumnSQL(
445 array('compensation_min' =>
448 'name' => 'compensation_min',
449 'vname' => 'LBL_COMPENSATION_MIN',
454 'importable' => 'true',
455 'duplicate_merge' => 'disabled',
456 'duplicate_merge_dom_value' => 0,
465 $this->assertNotRegExp('/float\s*\(18,\s*\)/i',$sql);
466 $this->assertRegExp('/float\s*\(18\)/i',$sql);
472 public function testBlankSpacePrecision()
474 $sql = $this->_helper->alterColumnSQL(
476 array('compensation_min' =>
479 'name' => 'compensation_min',
480 'vname' => 'LBL_COMPENSATION_MIN',
485 'importable' => 'true',
486 'duplicate_merge' => 'disabled',
487 'duplicate_merge_dom_value' => 0,
496 $this->assertNotRegExp('/float\s*\(18,\s*\)/i',$sql);
497 $this->assertRegExp('/float\s*\(18\)/i',$sql);
503 public function testSetPrecision()
505 $sql = $this->_helper->alterColumnSQL(
507 array('compensation_min' =>
510 'name' => 'compensation_min',
511 'vname' => 'LBL_COMPENSATION_MIN',
516 'importable' => 'true',
517 'duplicate_merge' => 'disabled',
518 'duplicate_merge_dom_value' => 0,
527 if ( $this->_db->dbType == 'mssql' )
528 $this->assertRegExp('/float\s*\(18\)/i',$sql);
530 $this->assertRegExp('/float\s*\(18,2\)/i',$sql);
536 public function testSetPrecisionInLen()
538 $sql = $this->_helper->alterColumnSQL(
540 array('compensation_min' =>
543 'name' => 'compensation_min',
544 'vname' => 'LBL_COMPENSATION_MIN',
549 'importable' => 'true',
550 'duplicate_merge' => 'disabled',
551 'duplicate_merge_dom_value' => 0,
558 if ( $this->_db->dbType == 'mssql' )
559 $this->assertRegExp('/float\s*\(18\)/i',$sql);
561 $this->assertRegExp('/float\s*\(18,2\)/i',$sql);
567 public function testEmptyPrecisionMassageFieldDef()
571 'name' => 'compensation_min',
572 'vname' => 'LBL_COMPENSATION_MIN',
577 'importable' => 'true',
578 'duplicate_merge' => 'disabled',
579 'duplicate_merge_dom_value' => 0,
585 $this->_helper->massageFieldDef($fielddef,'mytable');
587 $this->assertEquals("18",$fielddef['len']);
593 public function testBlankSpacePrecisionMassageFieldDef()
597 'name' => 'compensation_min',
598 'vname' => 'LBL_COMPENSATION_MIN',
603 'importable' => 'true',
604 'duplicate_merge' => 'disabled',
605 'duplicate_merge_dom_value' => 0,
611 $this->_helper->massageFieldDef($fielddef,'mytable');
613 $this->assertEquals("18",$fielddef['len']);
619 public function testSetPrecisionMassageFieldDef()
623 'name' => 'compensation_min',
624 'vname' => 'LBL_COMPENSATION_MIN',
629 'importable' => 'true',
630 'duplicate_merge' => 'disabled',
631 'duplicate_merge_dom_value' => 0,
637 $this->_helper->massageFieldDef($fielddef,'mytable');
639 $this->assertEquals("18,2",$fielddef['len']);
645 public function testSetPrecisionInLenMassageFieldDef()
649 'name' => 'compensation_min',
650 'vname' => 'LBL_COMPENSATION_MIN',
655 'importable' => 'true',
656 'duplicate_merge' => 'disabled',
657 'duplicate_merge_dom_value' => 0,
662 $this->_helper->massageFieldDef($fielddef,'mytable');
664 $this->assertEquals("18,2",$fielddef['len']);
667 public function testGetSelectFieldsFromQuery()
670 foreach(array("", "DISTINCT ") as $distinct) {
673 foreach(array("field", "''", "'data'", "sometable.field") as $data) {
674 if($data[0] != "'") {
676 $fields[] = "{$distinct}$data";
677 $dotfields = explode('.', $data);
678 $expected[] = $dotfields[count($dotfields)-1];
680 $as = "otherfield".($i++);
681 $fields[] = "{$distinct}$data $as";
683 $as = "otherfield".($i++);
684 $fields[] = "{$distinct}$data as $as";
687 $query = "SELECT ".join(', ', $fields);
688 $result = $this->_helper->getSelectFieldsFromQuery($query);
689 foreach($expected as $expect) {
690 $this->assertContains($expect, array_keys($result), "Result should include $expect");