2 /*********************************************************************************
3 * SugarCRM Community Edition is a customer relationship management program developed by
4 * SugarCRM, Inc. Copyright (C) 2004-2011 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 public function setUp()
49 $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
50 $this->_db = &DBManagerFactory::getInstance();
51 $this->_helper = $this->_db->getHelper();
54 public function tearDown()
56 SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
57 unset($GLOBALS['current_user']);
58 $this->_db->disconnect();
61 public function testCreateTableSQL()
63 $sql = $this->_helper->createTableSQL(new Contact);
65 $this->assertRegExp("/create\s*table\s*contacts/i",$sql);
68 public function testCreateTableSQLParams()
72 $sql = $this->_helper->createTableSQLParams(
73 $bean->getTableName(),
74 $bean->getFieldDefinitions(),
77 $this->assertRegExp("/create\s*table\s*contacts/i",$sql);
80 public function testInsertSQL()
82 $sql = $this->_helper->insertSQL(new Contact);
84 $this->assertRegExp("/insert\s*into\s*contacts/i",$sql);
90 public function testInsertSQLProperlyDecodesHtmlEntities()
93 $bean->last_name = '"Test"';
95 $sql = $this->_helper->insertSQL($bean);
97 $this->assertNotContains(""",$sql);
100 public function testUpdateSQL()
102 $sql = $this->_helper->updateSQL(new Contact, array("id" => "1"));
104 $this->assertRegExp("/update\s*contacts\s*set/i",$sql);
105 $this->assertRegExp("/where\s*contacts.id\s*=\s*'1'/i",$sql);
111 public function testUpdateSQLProperlyDecodesHtmlEntities()
114 $bean->last_name = '"Test"';
116 $sql = $this->_helper->updateSQL($bean, array("id" => "1"));
118 $this->assertNotContains(""",$sql);
121 public function testDeleteSQL()
123 $sql = $this->_helper->deleteSQL(new Contact, array("id" => "1"));
125 $this->assertRegExp("/update\s*contacts\s*set\s*deleted\s*=\s*1/i",$sql);
126 $this->assertRegExp("/where\s*contacts.id\s*=\s*'1'/i",$sql);
129 public function testRetrieveSQL()
131 $sql = $this->_helper->retrieveSQL(new Contact, array("id" => "1"));
133 $this->assertRegExp("/select\s*\*\s*from\s*contacts/i",$sql);
134 $this->assertRegExp("/where\s*contacts.id\s*=\s*'1'/i",$sql);
137 public function testRetrieveViewSQL()
139 // TODO: write this test
142 public function testCreateIndexSQL()
144 $sql = $this->_helper->createIndexSQL(
146 array('id' => array('name'=>'id')),
149 $this->assertRegExp("/create\s*unique\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*\)/i",$sql);
151 $sql = $this->_helper->createIndexSQL(
153 array('id' => array('name'=>'id')),
157 $this->assertRegExp("/create\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*\)/i",$sql);
159 $sql = $this->_helper->createIndexSQL(
161 array('id' => array('name'=>'id'),'deleted' => array('name'=>'deleted')),
164 $this->assertRegExp("/create\s*unique\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*,\s*deleted\s*\)/i",$sql);
167 public function testGetFieldType()
170 'dbType' => 'varchar',
174 'data_type' => 'email',
177 $this->assertEquals($this->_helper->getFieldType($fieldDef),'varchar');
178 unset($fieldDef['dbType']);
179 $this->assertEquals($this->_helper->getFieldType($fieldDef),'int');
180 unset($fieldDef['dbtype']);
181 $this->assertEquals($this->_helper->getFieldType($fieldDef),'char');
182 unset($fieldDef['type']);
183 $this->assertEquals($this->_helper->getFieldType($fieldDef),'bool');
184 unset($fieldDef['Type']);
185 $this->assertEquals($this->_helper->getFieldType($fieldDef),'email');
187 public function testGetAutoIncrement()
192 $case->retrieve($case->id);
193 $lastAuto = $case->case_number;
194 $helperResult = $this->_helper->getAutoIncrement("cases", "case_number");
196 $GLOBALS['db']->query("DELETE FROM cases WHERE id= '{$case->id}'");
198 $this->assertEquals($lastAuto + 1, $helperResult);
200 public function testSetAutoIncrementStart()
205 $case->retrieve($case->id);
206 $lastAuto = $case->case_number;
207 $case->deleted = true;
209 $newAuto = $lastAuto + 5;
210 $this->_helper->setAutoIncrementStart("cases", "case_number", $newAuto);
211 $case2 = new aCase();
212 $case2->name = "foo2";
214 $case2->retrieve($case2->id);
215 $case_number = $case2->case_number;
217 $GLOBALS['db']->query("DELETE FROM cases WHERE id= '{$case->id}'");
218 $GLOBALS['db']->query("DELETE FROM cases WHERE id= '{$case2->id}'");
220 $this->assertEquals($newAuto, $case_number);
222 public function testAddColumnSQL()
224 $sql = $this->_helper->addColumnSQL(
226 array('foo' => array('name'=>'foo','type'=>'varchar'))
229 $this->assertRegExp("/alter\s*table\s*contacts/i",$sql);
232 public function testAlterColumnSQL()
234 $sql = $this->_helper->alterColumnSQL(
236 array('foo' => array('name'=>'foo','type'=>'varchar'))
239 $this->assertRegExp("/alter\s*table\s*contacts/i",$sql);
242 public function testDropTableSQL()
244 $sql = $this->_helper->dropTableSQL(new Contact);
246 $this->assertRegExp("/drop\s*table.*contacts/i",$sql);
249 public function testDropTableNameSQL()
251 $sql = $this->_helper->dropTableNameSQL('contacts');
253 $this->assertRegExp("/drop\s*table.*contacts/i",$sql);
256 public function testDeleteColumnSQL()
258 $sql = $this->_helper->deleteColumnSQL(
260 array('foo' => array('name'=>'foo','type'=>'varchar'))
262 $this->assertRegExp("/alter\s*table\s*contacts\s*drop\s*column\s*foo/i",$sql);
265 public function testDropColumnSQL()
267 $sql = $this->_helper->dropColumnSQL(
269 array('foo' => array('name'=>'foo','type'=>'varchar'))
271 $this->assertRegExp("/alter\s*table\s*contacts\s*drop\s*column\s*foo/i",$sql);
274 public function testMassageValue()
277 $this->_helper->massageValue(123,array('name'=>'foo','type'=>'int')),
280 if ( $this->_db->dbType == 'mssql'
283 $this->_helper->massageValue("'dog'",array('name'=>'foo','type'=>'varchar')),
288 $this->_helper->massageValue("'dog'",array('name'=>'foo','type'=>'varchar')),
293 public function testGetColumnType()
296 $this->_helper->getColumnType('int'),
301 public function testIsFieldArray()
304 $this->_helper->isFieldArray(array('name'=>'foo','type'=>array('int')))
308 $this->_helper->isFieldArray(array('name'=>'foo','type'=>'int'))
312 $this->_helper->isFieldArray(array('name'=>'foo'))
316 $this->_helper->isFieldArray(1)
320 public function testSaveAuditRecords()
322 // TODO: write this test
325 public function testGetDataChanges()
327 // TODO: write this test
330 public function testQuote()
333 $this->_helper->quote('foobar'),
334 "'".$this->_db->quote('foobar')."'"
338 public function testEscapeQuote()
341 $this->_helper->escape_quote('foobar'),
342 $this->_db->quote('foobar')
346 public function testGetIndices()
348 $indices = $this->_helper->get_indices('contacts');
350 foreach ( $indices as $index ) {
351 $this->assertTrue(!empty($index['name']));
352 $this->assertTrue(!empty($index['type']));
353 $this->assertTrue(!empty($index['fields']));
357 public function testAddDropConstraint()
359 $tablename = 'test' . date("YmdHis");
360 $sql = $this->_helper->add_drop_constraint(
365 'fields' => array('foo'),
370 $this->assertRegExp("/idx_foo/i",$sql);
371 $this->assertRegExp("/foo/i",$sql);
373 $tablename = 'test' . date("YmdHis");
374 $sql = $this->_helper->add_drop_constraint(
379 'fields' => array('foo'),
384 $this->assertRegExp("/idx_foo/i",$sql);
385 $this->assertRegExp("/foo/i",$sql);
386 $this->assertRegExp("/drop/i",$sql);
389 public function testRenameIndex()
391 // TODO: write this test
394 public function testNumberOfColumns()
396 $tablename = 'test' . date("YmdHis");
397 $this->_db->createTableParams($tablename,
408 $this->assertEquals($this->_helper->number_of_columns($tablename),1);
410 $this->_db->dropTableName($tablename);
413 public function testGetColumns()
415 $vardefs = $this->_helper->get_columns('contacts');
417 $this->assertTrue(isset($vardefs['id']));
418 $this->assertTrue(isset($vardefs['id']['name']));
419 $this->assertTrue(isset($vardefs['id']['type']));
422 public function testMassageFieldDefs()
424 // TODO: write this test
430 public function testEmptyPrecision()
432 $sql = $this->_helper->alterColumnSQL(
434 array('compensation_min' =>
437 'name' => 'compensation_min',
438 'vname' => 'LBL_COMPENSATION_MIN',
443 'importable' => 'true',
444 'duplicate_merge' => 'disabled',
445 'duplicate_merge_dom_value' => 0,
454 $this->assertNotRegExp("/float\s*\(18,\s*\)/i",$sql);
455 $this->assertRegExp("/float\s*\(18\)/i",$sql);
461 public function testBlankSpacePrecision()
463 $sql = $this->_helper->alterColumnSQL(
465 array('compensation_min' =>
468 'name' => 'compensation_min',
469 'vname' => 'LBL_COMPENSATION_MIN',
474 'importable' => 'true',
475 'duplicate_merge' => 'disabled',
476 'duplicate_merge_dom_value' => 0,
485 $this->assertNotRegExp("/float\s*\(18,\s*\)/i",$sql);
486 $this->assertRegExp("/float\s*\(18\)/i",$sql);
492 public function testSetPrecision()
494 $sql = $this->_helper->alterColumnSQL(
496 array('compensation_min' =>
499 'name' => 'compensation_min',
500 'vname' => 'LBL_COMPENSATION_MIN',
505 'importable' => 'true',
506 'duplicate_merge' => 'disabled',
507 'duplicate_merge_dom_value' => 0,
516 if ( $this->_db->dbType == 'mssql' )
517 $this->assertRegExp("/float\s*\(18\)/i",$sql);
519 $this->assertRegExp("/float\s*\(18,2\)/i",$sql);
525 public function testSetPrecisionInLen()
527 $sql = $this->_helper->alterColumnSQL(
529 array('compensation_min' =>
532 'name' => 'compensation_min',
533 'vname' => 'LBL_COMPENSATION_MIN',
538 'importable' => 'true',
539 'duplicate_merge' => 'disabled',
540 'duplicate_merge_dom_value' => 0,
547 if ( $this->_db->dbType == 'mssql' )
548 $this->assertRegExp("/float\s*\(18\)/i",$sql);
550 $this->assertRegExp("/float\s*\(18,2\)/i",$sql);
556 public function testEmptyPrecisionMassageFieldDef()
560 'name' => 'compensation_min',
561 'vname' => 'LBL_COMPENSATION_MIN',
566 'importable' => 'true',
567 'duplicate_merge' => 'disabled',
568 'duplicate_merge_dom_value' => 0,
574 $this->_helper->massageFieldDef($fielddef,'mytable');
576 $this->assertEquals("18",$fielddef['len']);
582 public function testBlankSpacePrecisionMassageFieldDef()
586 'name' => 'compensation_min',
587 'vname' => 'LBL_COMPENSATION_MIN',
592 'importable' => 'true',
593 'duplicate_merge' => 'disabled',
594 'duplicate_merge_dom_value' => 0,
600 $this->_helper->massageFieldDef($fielddef,'mytable');
602 $this->assertEquals("18",$fielddef['len']);
608 public function testSetPrecisionMassageFieldDef()
612 'name' => 'compensation_min',
613 'vname' => 'LBL_COMPENSATION_MIN',
618 'importable' => 'true',
619 'duplicate_merge' => 'disabled',
620 'duplicate_merge_dom_value' => 0,
626 $this->_helper->massageFieldDef($fielddef,'mytable');
628 $this->assertEquals("18,2",$fielddef['len']);
634 public function testSetPrecisionInLenMassageFieldDef()
638 'name' => 'compensation_min',
639 'vname' => 'LBL_COMPENSATION_MIN',
644 'importable' => 'true',
645 'duplicate_merge' => 'disabled',
646 'duplicate_merge_dom_value' => 0,
651 $this->_helper->massageFieldDef($fielddef,'mytable');
653 $this->assertEquals("18,2",$fielddef['len']);
656 public function testGetSelectFieldsFromQuery()
659 foreach(array("", "DISTINCT ") as $distinct) {
662 foreach(array("field", "''", "'data'", "sometable.field") as $data) {
663 if($data[0] != "'") {
665 $fields[] = "{$distinct}$data";
666 $dotfields = explode('.', $data);
667 $expected[] = $dotfields[count($dotfields)-1];
669 $as = "otherfield".($i++);
670 $fields[] = "{$distinct}$data $as";
672 $as = "otherfield".($i++);
673 $fields[] = "{$distinct}$data as $as";
676 $query = "SELECT ".join(', ', $fields);
677 $result = $this->_helper->getSelectFieldsFromQuery($query);
678 foreach($expected as $expect) {
679 $this->assertContains($expect, array_keys($result), "Result should include $expect");