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