tests/include/database/DBHelperTest.php

   1 <?php
   2 require_once 'include/database/DBManagerFactory.php';
   3 require_once 'modules/Contacts/Contact.php';
   4 require_once 'modules/Cases/Case.php';
   5
   6 class DBHelperTest extends Sugar_PHPUnit_Framework_TestCase
   7 {
   8     private $_db;
   9     private $_helper;
  10
  11     public function setUp()
  12     {
  13         $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
  14         $this->_db = &DBManagerFactory::getInstance();
  15         $this->_helper = $this->_db->getHelper();
  16     }
  17
  18     public function tearDown()
  19     {
  20         SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
  21         unset($GLOBALS['current_user']);
  22         $this->_db->disconnect();
  23     }
  24
  25     public function testCreateTableSQL()
  26     {
  27         $sql = $this->_helper->createTableSQL(new Contact);
  28
  29         $this->assertRegExp("/create\s*table\s*contacts/i",$sql);
  30     }
  31
  32     public function testCreateTableSQLParams()
  33     {
  34         $bean = new Contact;
  35
  36         $sql = $this->_helper->createTableSQLParams(
  37             $bean->getTableName(),
  38             $bean->getFieldDefinitions(),
  39             $bean->getIndices());
  40
  41         $this->assertRegExp("/create\s*table\s*contacts/i",$sql);
  42     }
  43
  44     public function testInsertSQL()
  45     {
  46         $sql = $this->_helper->insertSQL(new Contact);
  47
  48         $this->assertRegExp("/insert\s*into\s*contacts/i",$sql);
  49     }
  50
  51     /**
  52      * ticket 38216
  53      */
  54     public function testInsertSQLProperlyDecodesHtmlEntities()
  55     {
  56         $bean = new Contact;
  57         $bean->last_name = '&quot;Test&quot;';
  58
  59         $sql = $this->_helper->insertSQL($bean);
  60
  61         $this->assertNotContains("&quot;",$sql);
  62     }
  63
  64     public function testUpdateSQL()
  65     {
  66         $sql = $this->_helper->updateSQL(new Contact, array("id" => "1"));
  67
  68         $this->assertRegExp("/update\s*contacts\s*set/i",$sql);
  69         $this->assertRegExp("/where\s*contacts.id\s*=\s*'1'/i",$sql);
  70     }
  71
  72     /**
  73      * ticket 38216
  74      */
  75     public function testUpdateSQLProperlyDecodesHtmlEntities()
  76     {
  77         $bean = new Contact;
  78         $bean->last_name = '&quot;Test&quot;';
  79
  80         $sql = $this->_helper->updateSQL($bean, array("id" => "1"));
  81
  82         $this->assertNotContains("&quot;",$sql);
  83     }
  84
  85     public function testDeleteSQL()
  86     {
  87         $sql = $this->_helper->deleteSQL(new Contact, array("id" => "1"));
  88
  89         $this->assertRegExp("/update\s*contacts\s*set\s*deleted\s*=\s*1/i",$sql);
  90         $this->assertRegExp("/where\s*contacts.id\s*=\s*'1'/i",$sql);
  91     }
  92
  93     public function testRetrieveSQL()
  94     {
  95         $sql = $this->_helper->retrieveSQL(new Contact, array("id" => "1"));
  96
  97         $this->assertRegExp("/select\s*\*\s*from\s*contacts/i",$sql);
  98         $this->assertRegExp("/where\s*contacts.id\s*=\s*'1'/i",$sql);
  99     }
 100
 101     public function testRetrieveViewSQL()
 102     {
 103         // TODO: write this test
 104     }
 105
 106     public function testCreateIndexSQL()
 107     {
 108         $sql = $this->_helper->createIndexSQL(
 109             new Contact,
 110             array('id' => array('name'=>'id')),
 111             'idx_id');
 112
 113         $this->assertRegExp("/create\s*unique\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*\)/i",$sql);
 114
 115         $sql = $this->_helper->createIndexSQL(
 116             new Contact,
 117             array('id' => array('name'=>'id')),
 118             'idx_id',
 119             false);
 120
 121         $this->assertRegExp("/create\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*\)/i",$sql);
 122
 123         $sql = $this->_helper->createIndexSQL(
 124             new Contact,
 125             array('id' => array('name'=>'id'),'deleted' => array('name'=>'deleted')),
 126             'idx_id');
 127
 128         $this->assertRegExp("/create\s*unique\s*index\s*idx_id\s*on\s*contacts\s*\(\s*id\s*,\s*deleted\s*\)/i",$sql);
 129     }
 130
 131     public function testGetFieldType()
 132     {
 133         $fieldDef = array(
 134             'dbType'    => 'varchar',
 135             'dbtype'    => 'int',
 136             'type'      => 'char',
 137             'Type'      => 'bool',
 138             'data_type' => 'email',
 139             );
 140
 141         $this->assertEquals($this->_helper->getFieldType($fieldDef),'varchar');
 142         unset($fieldDef['dbType']);
 143         $this->assertEquals($this->_helper->getFieldType($fieldDef),'int');
 144         unset($fieldDef['dbtype']);
 145         $this->assertEquals($this->_helper->getFieldType($fieldDef),'char');
 146         unset($fieldDef['type']);
 147         $this->assertEquals($this->_helper->getFieldType($fieldDef),'bool');
 148         unset($fieldDef['Type']);
 149         $this->assertEquals($this->_helper->getFieldType($fieldDef),'email');
 150     }
 151     public function testGetAutoIncrement()
 152     {
 153         $case = new aCase();
 154         $case->name = "foo";
 155         $case->save();
 156         $case->retrieve($case->id);
 157         $lastAuto = $case->case_number;
 158         $this->assertEquals($lastAuto + 1, $this->_helper->getAutoIncrement("cases", "case_number"));
 159         $case->deleted = true;
 160         $case->save();
 161     }
 162     public function testSetAutoIncrementStart()
 163     {
 164         $case = new aCase();
 165         $case->name = "foo";
 166         $case->save();
 167         $case->retrieve($case->id);
 168         $lastAuto = $case->case_number;
 169         $case->deleted = true;
 170         $case->save();
 171         $newAuto = $lastAuto + 5;
 172         $this->_helper->setAutoIncrementStart("cases", "case_number", $newAuto);
 173         $case2 = new aCase();
 174         $case2->name = "foo2";
 175         $case2->save();
 176         $case2->retrieve($case2->id);
 177         $this->assertEquals($newAuto, $case2->case_number);
 178         $case2->deleted = true;
 179         $case2->save();
 180     }
 181     public function testAddColumnSQL()
 182     {
 183         $sql = $this->_helper->addColumnSQL(
 184             'contacts',
 185             array('foo' => array('name'=>'foo','type'=>'varchar'))
 186             );
 187
 188         $this->assertRegExp("/alter\s*table\s*contacts/i",$sql);
 189     }
 190
 191     public function testAlterColumnSQL()
 192     {
 193         $sql = $this->_helper->alterColumnSQL(
 194             'contacts',
 195             array('foo' => array('name'=>'foo','type'=>'varchar'))
 196             );
 197
 198         $this->assertRegExp("/alter\s*table\s*contacts/i",$sql);
 199     }
 200
 201     public function testDropTableSQL()
 202     {
 203         $sql = $this->_helper->dropTableSQL(new Contact);
 204
 205         $this->assertRegExp("/drop\s*table.*contacts/i",$sql);
 206     }
 207
 208     public function testDropTableNameSQL()
 209     {
 210         $sql = $this->_helper->dropTableNameSQL('contacts');
 211
 212         $this->assertRegExp("/drop\s*table.*contacts/i",$sql);
 213     }
 214
 215     public function testDeleteColumnSQL()
 216     {
 217         $sql = $this->_helper->deleteColumnSQL(
 218             new Contact,
 219             array('foo' => array('name'=>'foo','type'=>'varchar'))
 220             );
 221             $this->assertRegExp("/alter\s*table\s*contacts\s*drop\s*column\s*foo/i",$sql);
 222     }
 223
 224     public function testDropColumnSQL()
 225     {
 226         $sql = $this->_helper->dropColumnSQL(
 227             'contacts',
 228             array('foo' => array('name'=>'foo','type'=>'varchar'))
 229             );
 230             $this->assertRegExp("/alter\s*table\s*contacts\s*drop\s*column\s*foo/i",$sql);
 231     }
 232
 233     public function testMassageValue()
 234     {
 235         $this->assertEquals(
 236             $this->_helper->massageValue(123,array('name'=>'foo','type'=>'int')),
 237             123
 238             );
 239         if ( $this->_db->dbType == 'mssql'
 240             )
 241             $this->assertEquals(
 242                 $this->_helper->massageValue("'dog'",array('name'=>'foo','type'=>'varchar')),
 243                 "'''dog'''"
 244                 );
 245         else
 246             $this->assertEquals(
 247                 $this->_helper->massageValue("'dog'",array('name'=>'foo','type'=>'varchar')),
 248                 "'\'dog\''"
 249                 );
 250     }
 251
 252     public function testGetColumnType()
 253     {
 254             $this->assertEquals(
 255                 $this->_helper->getColumnType('int'),
 256                 'int'
 257                 );
 258     }
 259
 260     public function testIsFieldArray()
 261     {
 262         $this->assertTrue(
 263             $this->_helper->isFieldArray(array('name'=>'foo','type'=>array('int')))
 264             );
 265
 266         $this->assertFalse(
 267             $this->_helper->isFieldArray(array('name'=>'foo','type'=>'int'))
 268             );
 269
 270         $this->assertTrue(
 271             $this->_helper->isFieldArray(array('name'=>'foo'))
 272             );
 273
 274         $this->assertFalse(
 275             $this->_helper->isFieldArray(1)
 276             );
 277     }
 278
 279     public function testSaveAuditRecords()
 280     {
 281         // TODO: write this test
 282     }
 283
 284     public function testGetDataChanges()
 285     {
 286         // TODO: write this test
 287     }
 288
 289     public function testQuote()
 290     {
 291         $this->assertEquals(
 292             $this->_helper->quote('foobar'),
 293             "'".$this->_db->quote('foobar')."'"
 294             );
 295     }
 296
 297     public function testEscapeQuote()
 298     {
 299         $this->assertEquals(
 300             $this->_helper->escape_quote('foobar'),
 301             $this->_db->quote('foobar')
 302             );
 303     }
 304
 305     public function testGetIndices()
 306     {
 307         $indices = $this->_helper->get_indices('contacts');
 308
 309         foreach ( $indices as $index ) {
 310             $this->assertTrue(!empty($index['name']));
 311             $this->assertTrue(!empty($index['type']));
 312             $this->assertTrue(!empty($index['fields']));
 313         }
 314     }
 315
 316     public function testAddDropConstraint()
 317     {
 318         $tablename = 'test' . date("YmdHis");
 319         $sql = $this->_helper->add_drop_constraint(
 320             $tablename,
 321             array(
 322                 'name'   => 'idx_foo',
 323                 'type'   => 'index',
 324                 'fields' => array('foo'),
 325                 ),
 326             false
 327             );
 328
 329         $this->assertRegExp("/idx_foo/i",$sql);
 330         $this->assertRegExp("/foo/i",$sql);
 331
 332         $tablename = 'test' . date("YmdHis");
 333         $sql = $this->_helper->add_drop_constraint(
 334             $tablename,
 335             array(
 336                 'name'   => 'idx_foo',
 337                 'type'   => 'index',
 338                 'fields' => array('foo'),
 339                 ),
 340             true
 341             );
 342
 343         $this->assertRegExp("/idx_foo/i",$sql);
 344         $this->assertRegExp("/foo/i",$sql);
 345         $this->assertRegExp("/drop/i",$sql);
 346     }
 347
 348     public function testRenameIndex()
 349     {
 350         // TODO: write this test
 351     }
 352
 353     public function testNumberOfColumns()
 354     {
 355         $tablename = 'test' . date("YmdHis");
 356         $this->_db->createTableParams($tablename,
 357             array(
 358                 'foo' => array (
 359                     'name' => 'foo',
 360                     'type' => 'varchar',
 361                     'len' => '255',
 362                     ),
 363                 ),
 364             array()
 365             );
 366
 367         $this->assertEquals($this->_helper->number_of_columns($tablename),1);
 368
 369         $this->_db->dropTableName($tablename);
 370     }
 371
 372     public function testGetColumns()
 373     {
 374         $vardefs = $this->_helper->get_columns('contacts');
 375
 376         $this->assertTrue(isset($vardefs['id']));
 377         $this->assertTrue(isset($vardefs['id']['name']));
 378         $this->assertTrue(isset($vardefs['id']['type']));
 379     }
 380
 381     public function testMassageFieldDefs()
 382     {
 383         // TODO: write this test
 384     }
 385
 386     /**
 387      * @group bug22921
 388      */
 389     public function testEmptyPrecision()
 390     {
 391         $sql = $this->_helper->alterColumnSQL(
 392             'contacts',
 393             array('compensation_min' =>
 394                  array(
 395                    'required' => false,
 396                    'name' => 'compensation_min',
 397                    'vname' => 'LBL_COMPENSATION_MIN',
 398                    'type' => 'float',
 399                    'massupdate' => 0,
 400                    'comments' => '',
 401                    'help' => '',
 402                    'importable' => 'true',
 403                    'duplicate_merge' => 'disabled',
 404                    'duplicate_merge_dom_value' => 0,
 405                    'audited' => 0,
 406                    'reportable' => 1,
 407                    'len' => '18',
 408                    'precision' => '',
 409                    ),
 410                  )
 411             );
 412
 413         $this->assertNotRegExp("/float\s*\(18,\s*\)/i",$sql);
 414         $this->assertRegExp("/float\s*\(18\)/i",$sql);
 415     }
 416
 417     /**
 418      * @group bug22921
 419      */
 420     public function testBlankSpacePrecision()
 421     {
 422         $sql = $this->_helper->alterColumnSQL(
 423             'contacts',
 424             array('compensation_min' =>
 425                  array(
 426                    'required' => false,
 427                    'name' => 'compensation_min',
 428                    'vname' => 'LBL_COMPENSATION_MIN',
 429                    'type' => 'float',
 430                    'massupdate' => 0,
 431                    'comments' => '',
 432                    'help' => '',
 433                    'importable' => 'true',
 434                    'duplicate_merge' => 'disabled',
 435                    'duplicate_merge_dom_value' => 0,
 436                    'audited' => 0,
 437                    'reportable' => 1,
 438                    'len' => '18',
 439                    'precision' => ' ',
 440                    ),
 441                  )
 442             );
 443
 444         $this->assertNotRegExp("/float\s*\(18,\s*\)/i",$sql);
 445         $this->assertRegExp("/float\s*\(18\)/i",$sql);
 446     }
 447
 448     /**
 449      * @group bug22921
 450      */
 451     public function testSetPrecision()
 452     {
 453         $sql = $this->_helper->alterColumnSQL(
 454             'contacts',
 455             array('compensation_min' =>
 456                  array(
 457                    'required' => false,
 458                    'name' => 'compensation_min',
 459                    'vname' => 'LBL_COMPENSATION_MIN',
 460                    'type' => 'float',
 461                    'massupdate' => 0,
 462                    'comments' => '',
 463                    'help' => '',
 464                    'importable' => 'true',
 465                    'duplicate_merge' => 'disabled',
 466                    'duplicate_merge_dom_value' => 0,
 467                    'audited' => 0,
 468                    'reportable' => 1,
 469                    'len' => '18',
 470                    'precision' => '2',
 471                    ),
 472                  )
 473             );
 474
 475         if ( $this->_db->dbType == 'mssql' )
 476                         $this->assertRegExp("/float\s*\(18\)/i",$sql);
 477         else
 478                 $this->assertRegExp("/float\s*\(18,2\)/i",$sql);
 479     }
 480
 481     /**
 482      * @group bug22921
 483      */
 484     public function testSetPrecisionInLen()
 485     {
 486         $sql = $this->_helper->alterColumnSQL(
 487             'contacts',
 488             array('compensation_min' =>
 489                  array(
 490                    'required' => false,
 491                    'name' => 'compensation_min',
 492                    'vname' => 'LBL_COMPENSATION_MIN',
 493                    'type' => 'float',
 494                    'massupdate' => 0,
 495                    'comments' => '',
 496                    'help' => '',
 497                    'importable' => 'true',
 498                    'duplicate_merge' => 'disabled',
 499                    'duplicate_merge_dom_value' => 0,
 500                    'audited' => 0,
 501                    'reportable' => 1,
 502                    'len' => '18,2',
 503                    ),
 504                  )
 505             );
 506         if ( $this->_db->dbType == 'mssql' )
 507                         $this->assertRegExp("/float\s*\(18\)/i",$sql);
 508         else
 509                 $this->assertRegExp("/float\s*\(18,2\)/i",$sql);
 510     }
 511
 512     /**
 513      * @group bug22921
 514      */
 515     public function testEmptyPrecisionMassageFieldDef()
 516     {
 517         $fielddef = array(
 518                'required' => false,
 519                'name' => 'compensation_min',
 520                'vname' => 'LBL_COMPENSATION_MIN',
 521                'type' => 'float',
 522                'massupdate' => 0,
 523                'comments' => '',
 524                'help' => '',
 525                'importable' => 'true',
 526                'duplicate_merge' => 'disabled',
 527                'duplicate_merge_dom_value' => 0,
 528                'audited' => 0,
 529                'reportable' => 1,
 530                'len' => '18',
 531                'precision' => '',
 532             );
 533         $this->_helper->massageFieldDef($fielddef,'mytable');
 534
 535         $this->assertEquals("18",$fielddef['len']);
 536     }
 537
 538     /**
 539      * @group bug22921
 540      */
 541     public function testBlankSpacePrecisionMassageFieldDef()
 542     {
 543         $fielddef = array(
 544                'required' => false,
 545                'name' => 'compensation_min',
 546                'vname' => 'LBL_COMPENSATION_MIN',
 547                'type' => 'float',
 548                'massupdate' => 0,
 549                'comments' => '',
 550                'help' => '',
 551                'importable' => 'true',
 552                'duplicate_merge' => 'disabled',
 553                'duplicate_merge_dom_value' => 0,
 554                'audited' => 0,
 555                'reportable' => 1,
 556                'len' => '18',
 557                'precision' => ' ',
 558             );
 559         $this->_helper->massageFieldDef($fielddef,'mytable');
 560
 561         $this->assertEquals("18",$fielddef['len']);
 562     }
 563
 564     /**
 565      * @group bug22921
 566      */
 567     public function testSetPrecisionMassageFieldDef()
 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' => '2',
 584             );
 585         $this->_helper->massageFieldDef($fielddef,'mytable');
 586
 587         $this->assertEquals("18,2",$fielddef['len']);
 588     }
 589
 590     /**
 591      * @group bug22921
 592      */
 593     public function testSetPrecisionInLenMassageFieldDef()
 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,2',
 609             );
 610         $this->_helper->massageFieldDef($fielddef,'mytable');
 611
 612         $this->assertEquals("18,2",$fielddef['len']);
 613     }
 614 }