tests/include/database/DBHelperTest.php

   1 <?php
   2 /*********************************************************************************
   3  * SugarCRM Community Edition is a customer relationship management program developed by
   4  * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
   5  *
   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.
  12  *
  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
  16  * details.
  17  *
  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
  21  * 02110-1301 USA.
  22  *
  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.
  25  *
  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.
  29  *
  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  ********************************************************************************/
  36
  37
  38 require_once 'include/database/DBManagerFactory.php';
  39 require_once 'modules/Contacts/Contact.php';
  40 require_once 'modules/Cases/Case.php';
  41
  42 class DBHelperTest extends Sugar_PHPUnit_Framework_TestCase
  43 {
  44     private $_db;
  45     private $_helper;
  46
  47     static public function setupBeforeClass()
  48     {
  49         $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
  50         $GLOBALS['app_strings'] = return_application_language($GLOBALS['current_language']);
  51     }
  52
  53     static public function tearDownAfterClass()
  54     {
  55         SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
  56         unset($GLOBALS['current_user']);
  57         unset($GLOBALS['app_strings']);
  58     }
  59
  60     public function setUp()
  61     {
  62         $this->_db = DBManagerFactory::getInstance();
  63         $this->_helper = $this->_db;
  64     }
  65
  66     public function tearDown()
  67     {
  68         $this->_db->disconnect();
  69     }
  70
  71     public function testCreateTableSQL()
  72     {
  73         $sql = $this->_helper->createTableSQL(new Contact);
  74
  75         $this->assertRegExp('/create\s*table\s*contacts/i',$sql);
  76     }
  77
  78     public function testCreateTableSQLParams()
  79     {
  80         $bean = new Contact;
  81
  82         $sql = $this->_helper->createTableSQLParams(
  83             $bean->getTableName(),
  84             $bean->getFieldDefinitions(),
  85             $bean->getIndices());
  86
  87         $this->assertRegExp('/create\s*table\s*contacts/i',$sql);
  88     }
  89
  90     public function testInsertSQL()
  91     {
  92         $sql = $this->_helper->insertSQL(new Contact);
  93
  94         $this->assertRegExp('/insert\s*into\s*contacts/i',$sql);
  95     }
  96
  97     /**
  98      * ticket 38216
  99      */
 100     public function testInsertSQLProperlyDecodesHtmlEntities()
 101     {
 102         $bean = new Contact;
 103         $bean->last_name = '&quot;Test&quot;';
 104
 105         $sql = $this->_helper->insertSQL($bean);
 106
 107         $this->assertNotContains("&quot;",$sql);
 108     }
 109
 110     public function testUpdateSQL()
 111     {
 112         $sql = $this->_helper->updateSQL(new Contact, array("id" => "1"));
 113
 114         $this->assertRegExp('/update\s*contacts\s*set/i',$sql);
 115         $this->assertRegExp('/where\s*contacts.id\s*=\s*\'1\'/i',$sql);
 116     }
 117
 118     /**
 119      * ticket 38216
 120      */
 121     public function testUpdateSQLProperlyDecodesHtmlEntities()
 122     {
 123         $bean = new Contact;
 124         $bean->last_name = '&quot;Test&quot;';
 125
 126         $sql = $this->_helper->updateSQL($bean, array("id" => "1"));
 127
 128         $this->assertNotContains("&quot;",$sql);
 129     }
 130
 131     public function testDeleteSQL()
 132     {
 133         $sql = $this->_helper->deleteSQL(new Contact, array("id" => "1"));
 134
 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);
 137     }
 138
 139     public function testRetrieveSQL()
 140     {
 141         $sql = $this->_helper->retrieveSQL(new Contact, array("id" => "1"));
 142
 143         $this->assertRegExp('/select\s*\*\s*from\s*contacts/i',$sql);
 144         $this->assertRegExp('/where\s*contacts.id\s*=\s*\'1\'/i',$sql);
 145     }
 146
 147     public function testRetrieveViewSQL()
 148     {
 149         // TODO: write this test
 150     }
 151
 152     public function testCreateIndexSQL()
 153     {
 154         $sql = $this->_helper->createIndexSQL(
 155             new Contact,
 156             array('id' => array('name'=>'id')),
 157             'idx_id');
 158
 159         $this->assertRegExp('/create\s*unique\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*\)/i',$sql);
 160
 161         $sql = $this->_helper->createIndexSQL(
 162             new Contact,
 163             array('id' => array('name'=>'id')),
 164             'idx_id',
 165             false);
 166
 167         $this->assertRegExp('/create\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*\)/i',$sql);
 168
 169         $sql = $this->_helper->createIndexSQL(
 170             new Contact,
 171             array('id' => array('name'=>'id'),'deleted' => array('name'=>'deleted')),
 172             'idx_id');
 173
 174         $this->assertRegExp('/create\s*unique\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*,\s*deleted\s*\)/i',$sql);
 175     }
 176
 177     public function testGetFieldType()
 178     {
 179         $fieldDef = array(
 180             'dbType'    => 'varchar',
 181             'dbtype'    => 'int',
 182             'type'      => 'char',
 183             'Type'      => 'bool',
 184             'data_type' => 'email',
 185             );
 186
 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');
 196     }
 197     public function testGetAutoIncrement()
 198     {
 199         $case = new aCase();
 200         $case->name = "foo";
 201         $case->save();
 202         $case->retrieve($case->id);
 203         $lastAuto = $case->case_number;
 204         $helperResult = $this->_helper->getAutoIncrement("cases", "case_number");
 205
 206 //        $GLOBALS['db']->query("DELETE FROM cases WHERE id= '{$case->id}'");
 207
 208         $this->assertEquals($lastAuto + 1, $helperResult);
 209     }
 210     public function testSetAutoIncrementStart()
 211     {
 212         $case = new aCase();
 213         $case->name = "foo";
 214         $case->save();
 215         $case->retrieve($case->id);
 216         $lastAuto = $case->case_number;
 217         $case->deleted = true;
 218         $case->save();
 219         $newAuto = $lastAuto + 5;
 220         $this->_helper->setAutoIncrementStart("cases", "case_number", $newAuto);
 221         $case2 = new aCase();
 222         $case2->name = "foo2";
 223         $case2->save();
 224         $case2->retrieve($case2->id);
 225         $case_number = $case2->case_number;
 226
 227         $GLOBALS['db']->query("DELETE FROM cases WHERE id= '{$case->id}'");
 228         $GLOBALS['db']->query("DELETE FROM cases WHERE id= '{$case2->id}'");
 229
 230         $this->assertEquals($newAuto, $case_number);
 231     }
 232     public function testAddColumnSQL()
 233     {
 234         $sql = $this->_helper->addColumnSQL(
 235             'contacts',
 236             array('foo' => array('name'=>'foo','type'=>'varchar'))
 237             );
 238
 239         $this->assertRegExp('/alter\s*table\s*contacts/i',$sql);
 240     }
 241
 242     public function testAlterColumnSQL()
 243     {
 244         $sql = $this->_helper->alterColumnSQL(
 245             'contacts',
 246             array('foo' => array('name'=>'foo','type'=>'varchar'))
 247             );
 248
 249         // Generated SQL may be a sequence of statements
 250                 switch(gettype($sql)){
 251                         case 'array':
 252                                 $sql = $sql[0];
 253                         case 'string':
 254                                 $this->assertRegExp('/alter\s*table\s*contacts/i',$sql);
 255                                 break;
 256                         }
 257     }
 258
 259     public function testDropTableSQL()
 260     {
 261         $sql = $this->_helper->dropTableSQL(new Contact);
 262
 263         $this->assertRegExp('/drop\s*table.*contacts/i',$sql);
 264     }
 265
 266     public function testDropTableNameSQL()
 267     {
 268         $sql = $this->_helper->dropTableNameSQL('contacts');
 269
 270         $this->assertRegExp('/drop\s*table.*contacts/i',$sql);
 271     }
 272
 273     public function testDeleteColumnSQL()
 274     {
 275         $sql = $this->_helper->deleteColumnSQL(
 276             new Contact,
 277             array('foo' => array('name'=>'foo','type'=>'varchar'))
 278             );
 279             $this->assertRegExp('/alter\s*table\s*contacts\s*drop\s*column\s*foo/i',$sql);
 280     }
 281
 282     public function testDropColumnSQL()
 283     {
 284         $sql = $this->_helper->dropColumnSQL(
 285             'contacts',
 286             array('foo' => array('name'=>'foo','type'=>'varchar'))
 287             );
 288             $this->assertRegExp('/alter\s*table\s*contacts\s*drop\s*column\s*foo/i',$sql);
 289     }
 290
 291     public function testMassageValue()
 292     {
 293         $this->assertEquals(
 294             $this->_helper->massageValue(123,array('name'=>'foo','type'=>'int')),
 295             123
 296             );
 297         if (in_array($this->_db->dbType, array('mssql'
 298             )))
 299             $this->assertEquals(
 300                 $this->_helper->massageValue("'dog'",array('name'=>'foo','type'=>'varchar')),
 301                 "'''dog'''"
 302                 );
 303         else
 304             $this->assertEquals(
 305                 $this->_helper->massageValue("'dog'",array('name'=>'foo','type'=>'varchar')),
 306                 "'\\'dog\\''"
 307                 );
 308     }
 309
 310     public function testGetColumnType()
 311     {
 312         switch($this->_db->dbType){
 313             default:
 314                 $expected_type = 'int';
 315         }
 316
 317         $this->assertEquals($expected_type, $this->_helper->getColumnType('int'));
 318     }
 319
 320     public function testIsFieldArray()
 321     {
 322         $this->assertTrue(
 323             $this->_helper->isFieldArray(array('name'=>'foo','type'=>array('int')))
 324             );
 325
 326         $this->assertFalse(
 327             $this->_helper->isFieldArray(array('name'=>'foo','type'=>'int'))
 328             );
 329
 330         $this->assertTrue(
 331             $this->_helper->isFieldArray(array('name'=>'foo'))
 332             );
 333
 334         $this->assertFalse(
 335             $this->_helper->isFieldArray(1)
 336             );
 337     }
 338
 339     public function testSaveAuditRecords()
 340     {
 341         // TODO: write this test
 342     }
 343
 344     public function testGetDataChanges()
 345     {
 346         // TODO: write this test
 347     }
 348
 349     public function testQuoted()
 350     {
 351         $this->assertEquals(
 352             "'".$this->_db->quote('foobar')."'",
 353             $this->_db->quoted('foobar')
 354             );
 355     }
 356
 357     public function testGetIndices()
 358     {
 359         $indices = $this->_helper->get_indices('contacts');
 360
 361         foreach ( $indices as $index ) {
 362             $this->assertTrue(!empty($index['name']));
 363             $this->assertTrue(!empty($index['type']));
 364             $this->assertTrue(!empty($index['fields']));
 365         }
 366     }
 367
 368     public function testAddDropConstraint()
 369     {
 370         $tablename = 'test' . date("YmdHis");
 371         $sql = $this->_helper->add_drop_constraint(
 372             $tablename,
 373             array(
 374                 'name'   => 'idx_foo',
 375                 'type'   => 'index',
 376                 'fields' => array('foo'),
 377                 ),
 378             false
 379             );
 380
 381         $this->assertRegExp("/idx_foo/i",$sql);
 382         $this->assertRegExp("/foo/i",$sql);
 383
 384         $tablename = 'test' . date("YmdHis");
 385         $sql = $this->_helper->add_drop_constraint(
 386             $tablename,
 387             array(
 388                 'name'   => 'idx_foo',
 389                 'type'   => 'index',
 390                 'fields' => array('foo'),
 391                 ),
 392             true
 393             );
 394
 395         $this->assertRegExp("/idx_foo/i",$sql);
 396         $this->assertRegExp("/foo/i",$sql);
 397         $this->assertRegExp("/drop/i",$sql);
 398     }
 399
 400     public function testRenameIndex()
 401     {
 402         // TODO: write this test
 403     }
 404
 405     public function testNumberOfColumns()
 406     {
 407         $tablename = 'test' . date("YmdHis");
 408         $this->_db->createTableParams($tablename,
 409             array(
 410                 'foo' => array (
 411                     'name' => 'foo',
 412                     'type' => 'varchar',
 413                     'len' => '255',
 414                     ),
 415                 ),
 416             array()
 417             );
 418
 419         $this->assertEquals($this->_helper->number_of_columns($tablename),1);
 420
 421         $this->_db->dropTableName($tablename);
 422     }
 423
 424     public function testGetColumns()
 425     {
 426         $vardefs = $this->_helper->get_columns('contacts');
 427
 428         $this->assertTrue(isset($vardefs['id']));
 429         $this->assertTrue(isset($vardefs['id']['name']));
 430         $this->assertTrue(isset($vardefs['id']['type']));
 431     }
 432
 433     public function testMassageFieldDefs()
 434     {
 435         // TODO: write this test
 436     }
 437
 438     /**
 439      * @ticket 22921
 440      */
 441     public function testEmptyPrecision()
 442     {
 443         $sql = $this->_helper->alterColumnSQL(
 444             'contacts',
 445             array('compensation_min' =>
 446                  array(
 447                    'required' => false,
 448                    'name' => 'compensation_min',
 449                    'vname' => 'LBL_COMPENSATION_MIN',
 450                    'type' => 'float',
 451                    'massupdate' => 0,
 452                    'comments' => '',
 453                    'help' => '',
 454                    'importable' => 'true',
 455                    'duplicate_merge' => 'disabled',
 456                    'duplicate_merge_dom_value' => 0,
 457                    'audited' => 0,
 458                    'reportable' => 1,
 459                    'len' => '18',
 460                    'precision' => '',
 461                    ),
 462                  )
 463             );
 464
 465         $this->assertNotRegExp('/float\s*\(18,\s*\)/i',$sql);
 466         $this->assertRegExp('/float\s*\(18\)/i',$sql);
 467     }
 468
 469     /**
 470      * @ticket 22921
 471      */
 472     public function testBlankSpacePrecision()
 473     {
 474         $sql = $this->_helper->alterColumnSQL(
 475             'contacts',
 476             array('compensation_min' =>
 477                  array(
 478                    'required' => false,
 479                    'name' => 'compensation_min',
 480                    'vname' => 'LBL_COMPENSATION_MIN',
 481                    'type' => 'float',
 482                    'massupdate' => 0,
 483                    'comments' => '',
 484                    'help' => '',
 485                    'importable' => 'true',
 486                    'duplicate_merge' => 'disabled',
 487                    'duplicate_merge_dom_value' => 0,
 488                    'audited' => 0,
 489                    'reportable' => 1,
 490                    'len' => '18',
 491                    'precision' => ' ',
 492                    ),
 493                  )
 494             );
 495
 496         $this->assertNotRegExp('/float\s*\(18,\s*\)/i',$sql);
 497         $this->assertRegExp('/float\s*\(18\)/i',$sql);
 498     }
 499
 500     /**
 501      * @ticket 22921
 502      */
 503     public function testSetPrecision()
 504     {
 505         $sql = $this->_helper->alterColumnSQL(
 506             'contacts',
 507             array('compensation_min' =>
 508                  array(
 509                    'required' => false,
 510                    'name' => 'compensation_min',
 511                    'vname' => 'LBL_COMPENSATION_MIN',
 512                    'type' => 'float',
 513                    'massupdate' => 0,
 514                    'comments' => '',
 515                    'help' => '',
 516                    'importable' => 'true',
 517                    'duplicate_merge' => 'disabled',
 518                    'duplicate_merge_dom_value' => 0,
 519                    'audited' => 0,
 520                    'reportable' => 1,
 521                    'len' => '18',
 522                    'precision' => '2',
 523                    ),
 524                  )
 525             );
 526
 527         if ( $this->_db->dbType == 'mssql' )
 528                         $this->assertRegExp('/float\s*\(18\)/i',$sql);
 529         else
 530                 $this->assertRegExp('/float\s*\(18,2\)/i',$sql);
 531     }
 532
 533     /**
 534      * @ticket 22921
 535      */
 536     public function testSetPrecisionInLen()
 537     {
 538         $sql = $this->_helper->alterColumnSQL(
 539             'contacts',
 540             array('compensation_min' =>
 541                  array(
 542                    'required' => false,
 543                    'name' => 'compensation_min',
 544                    'vname' => 'LBL_COMPENSATION_MIN',
 545                    'type' => 'float',
 546                    'massupdate' => 0,
 547                    'comments' => '',
 548                    'help' => '',
 549                    'importable' => 'true',
 550                    'duplicate_merge' => 'disabled',
 551                    'duplicate_merge_dom_value' => 0,
 552                    'audited' => 0,
 553                    'reportable' => 1,
 554                    'len' => '18,2',
 555                    ),
 556                  )
 557             );
 558         if ( $this->_db->dbType == 'mssql' )
 559                         $this->assertRegExp('/float\s*\(18\)/i',$sql);
 560         else
 561                 $this->assertRegExp('/float\s*\(18,2\)/i',$sql);
 562     }
 563
 564     /**
 565      * @ticket 22921
 566      */
 567     public function testEmptyPrecisionMassageFieldDef()
 568     {
 569         $fielddef = array(
 570                'required' => false,
 571                'name' => 'compensation_min',
 572                'vname' => 'LBL_COMPENSATION_MIN',
 573                'type' => 'float',
 574                'massupdate' => 0,
 575                'comments' => '',
 576                'help' => '',
 577                'importable' => 'true',
 578                'duplicate_merge' => 'disabled',
 579                'duplicate_merge_dom_value' => 0,
 580                'audited' => 0,
 581                'reportable' => 1,
 582                'len' => '18',
 583                'precision' => '',
 584             );
 585         $this->_helper->massageFieldDef($fielddef,'mytable');
 586
 587         $this->assertEquals("18",$fielddef['len']);
 588     }
 589
 590     /**
 591      * @ticket 22921
 592      */
 593     public function testBlankSpacePrecisionMassageFieldDef()
 594     {
 595         $fielddef = array(
 596                'required' => false,
 597                'name' => 'compensation_min',
 598                'vname' => 'LBL_COMPENSATION_MIN',
 599                'type' => 'float',
 600                'massupdate' => 0,
 601                'comments' => '',
 602                'help' => '',
 603                'importable' => 'true',
 604                'duplicate_merge' => 'disabled',
 605                'duplicate_merge_dom_value' => 0,
 606                'audited' => 0,
 607                'reportable' => 1,
 608                'len' => '18',
 609                'precision' => ' ',
 610             );
 611         $this->_helper->massageFieldDef($fielddef,'mytable');
 612
 613         $this->assertEquals("18",$fielddef['len']);
 614     }
 615
 616     /**
 617      * @ticket 22921
 618      */
 619     public function testSetPrecisionMassageFieldDef()
 620     {
 621         $fielddef = array(
 622                'required' => false,
 623                'name' => 'compensation_min',
 624                'vname' => 'LBL_COMPENSATION_MIN',
 625                'type' => 'float',
 626                'massupdate' => 0,
 627                'comments' => '',
 628                'help' => '',
 629                'importable' => 'true',
 630                'duplicate_merge' => 'disabled',
 631                'duplicate_merge_dom_value' => 0,
 632                'audited' => 0,
 633                'reportable' => 1,
 634                'len' => '18',
 635                'precision' => '2',
 636             );
 637         $this->_helper->massageFieldDef($fielddef,'mytable');
 638
 639         $this->assertEquals("18,2",$fielddef['len']);
 640     }
 641
 642     /**
 643      * @ticket 22921
 644      */
 645     public function testSetPrecisionInLenMassageFieldDef()
 646     {
 647         $fielddef = array(
 648                'required' => false,
 649                'name' => 'compensation_min',
 650                'vname' => 'LBL_COMPENSATION_MIN',
 651                'type' => 'float',
 652                'massupdate' => 0,
 653                'comments' => '',
 654                'help' => '',
 655                'importable' => 'true',
 656                'duplicate_merge' => 'disabled',
 657                'duplicate_merge_dom_value' => 0,
 658                'audited' => 0,
 659                'reportable' => 1,
 660                'len' => '18,2',
 661             );
 662         $this->_helper->massageFieldDef($fielddef,'mytable');
 663
 664         $this->assertEquals("18,2",$fielddef['len']);
 665     }
 666
 667     public function testGetSelectFieldsFromQuery()
 668     {
 669         $i=0;
 670         foreach(array("", "DISTINCT ") as $distinct) {
 671             $fields = array();
 672             $expected = array();
 673             foreach(array("field", "''", "'data'", "sometable.field") as $data) {
 674                 if($data[0] != "'") {
 675                     $data .= $i++;
 676                     $fields[] = "{$distinct}$data";
 677                     $dotfields = explode('.', $data);
 678                     $expected[] = $dotfields[count($dotfields)-1];
 679                 }
 680                 $as = "otherfield".($i++);
 681                 $fields[] = "{$distinct}$data $as";
 682                 $expected[] = $as;
 683                 $as = "otherfield".($i++);
 684                 $fields[] = "{$distinct}$data as $as";
 685                 $expected[] = $as;
 686             }
 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");
 691             }
 692         }
 693     }
 694 }