tests/include/database/DBManagerTest.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
  41 class DBManagerTest extends Sugar_PHPUnit_Framework_TestCase
  42 {
  43     private $_db;
  44
  45     protected $backupGlobals = FALSE;
  46
  47     public function setUp()
  48     {
  49         $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
  50         $this->_db = DBManagerFactory::getInstance();
  51         $GLOBALS['app_strings'] = return_application_language($GLOBALS['current_language']);
  52     }
  53
  54     public function tearDown()
  55     {
  56         SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
  57         unset($GLOBALS['current_user']);
  58         unset($GLOBALS['app_strings']);
  59     }
  60
  61     private function _createRecords(
  62         $num
  63         )
  64     {
  65         $beanIds = array();
  66         for ( $i = 0; $i < $num; $i++ ) {
  67             $bean = new Contact();
  68             $bean->id = "$i-test" . mt_rand();
  69             $bean->last_name = "foobar";
  70             $this->_db->insert($bean);
  71             $beanIds[] = $bean->id;
  72         }
  73
  74         return $beanIds;
  75     }
  76
  77     private function _removeRecords(
  78         array $ids
  79         )
  80     {
  81         foreach ($ids as $id)
  82             $this->_db->query("DELETE From contacts where id = '{$id}'");
  83     }
  84
  85     public function testGetTableName()
  86     {
  87         $this->_db->createTableParams('MyTableName',array('foo'=>'foo'),array());
  88
  89         $this->assertEquals($this->_db->getTableName(),'MyTableName');
  90     }
  91
  92     public function testGetDatabase()
  93     {
  94         if ( $this->_db instanceOf MysqliManager )
  95             $this->assertInstanceOf('Mysqli',$this->_db->getDatabase());
  96         else
  97             $this->assertTrue(is_resource($this->_db->getDatabase()));
  98     }
  99
 100     public function testGetHelper()
 101     {
 102         $this->assertInstanceOf('DBHelper',$this->_db->getHelper());
 103     }
 104
 105     public function testCheckError()
 106     {
 107         $this->assertFalse($this->_db->checkError());
 108     }
 109
 110     public function testCheckErrorNoConnection()
 111     {
 112         $this->_db->disconnect();
 113         $this->assertTrue($this->_db->checkError());
 114         $this->_db = &DBManagerFactory::getInstance();
 115     }
 116
 117     public function testGetQueryTime()
 118     {
 119         $this->_db->version();
 120         $this->assertTrue($this->_db->getQueryTime() > 0);
 121     }
 122
 123     public function testCheckConnection()
 124     {
 125         $this->_db->checkConnection();
 126         if ( $this->_db instanceOf MysqliManager )
 127             $this->assertInstanceOf('Mysqli',$this->_db->getDatabase());
 128         else
 129             $this->assertTrue(is_resource($this->_db->getDatabase()));
 130     }
 131
 132     public function testInsert()
 133     {
 134         $bean = new Contact();
 135         $bean->last_name = 'foobar' . mt_rand();
 136         $bean->id   = 'test' . mt_rand();
 137         $this->_db->insert($bean);
 138
 139         $result = $this->_db->query("select id, last_name from contacts where id = '{$bean->id}'");
 140         $row = $this->_db->fetchByAssoc($result);
 141         $this->assertEquals($row['last_name'],$bean->last_name);
 142         $this->assertEquals($row['id'],$bean->id);
 143
 144         $this->_db->query("delete from contacts where id = '{$row['id']}'");
 145     }
 146
 147     public function testUpdate()
 148     {
 149         $bean = new Contact();
 150         $bean->last_name = 'foobar' . mt_rand();
 151         $bean->id   = 'test' . mt_rand();
 152         $this->_db->insert($bean);
 153         $id = $bean->id;
 154
 155         $bean = new Contact();
 156         $bean->last_name = 'newfoobar' . mt_rand();
 157         $this->_db->update($bean,array('id'=>$id));
 158
 159         $result = $this->_db->query("select id, last_name from contacts where id = '{$id}'");
 160         $row = $this->_db->fetchByAssoc($result);
 161         $this->assertEquals($row['last_name'],$bean->last_name);
 162         $this->assertEquals($row['id'],$id);
 163
 164         $this->_db->query("delete from contacts where id = '{$row['id']}'");
 165     }
 166
 167     public function testDelete()
 168     {
 169         $bean = new Contact();
 170         $bean->last_name = 'foobar' . mt_rand();
 171         $bean->id   = 'test' . mt_rand();
 172         $this->_db->insert($bean);
 173         $id = $bean->id;
 174
 175         $bean = new Contact();
 176         $this->_db->delete($bean,array('id'=>$id));
 177
 178         $result = $this->_db->query("select deleted from contacts where id = '{$id}'");
 179         $row = $this->_db->fetchByAssoc($result);
 180         $this->assertEquals($row['deleted'],'1');
 181
 182         $this->_db->query("delete from contacts where id = '{$id}'");
 183     }
 184
 185     public function testRetrieve()
 186     {
 187         $bean = new Contact();
 188         $bean->last_name = 'foobar' . mt_rand();
 189         $bean->id   = 'test' . mt_rand();
 190         $this->_db->insert($bean);
 191         $id = $bean->id;
 192
 193         $bean = new Contact();
 194         $result = $this->_db->retrieve($bean,array('id'=>$id));
 195         $row = $this->_db->fetchByAssoc($result);
 196         $this->assertEquals($row['id'],$id);
 197
 198         $this->_db->query("delete from contacts where id = '{$id}'");
 199     }
 200
 201     public function testRetrieveView()
 202     {
 203         // TODO: Write this test
 204     }
 205
 206     public function testCreateTable()
 207     {
 208         // TODO: Write this test
 209     }
 210
 211     public function testCreateTableParams()
 212     {
 213         $tablename = 'test' . mt_rand();
 214         $this->_db->createTableParams($tablename,
 215             array(
 216                 'foo' => array (
 217                     'name' => 'foo',
 218                     'type' => 'varchar',
 219                     'len' => '255',
 220                     ),
 221                 ),
 222             array(
 223                 array(
 224                     'name'   => 'idx_foo',
 225                     'type'   => 'index',
 226                     'fields' => array('foo'),
 227                     )
 228                 )
 229             );
 230         $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
 231
 232         $this->_db->dropTableName($tablename);
 233     }
 234
 235     public function testRepairTable()
 236     {
 237         // TODO: Write this test
 238     }
 239
 240     public function testRepairTableParams()
 241     {
 242         // TODO: Write this test
 243     }
 244
 245     public function testCompareFieldInTables()
 246     {
 247         $tablename1 = 'test1_' . mt_rand();
 248         $this->_db->createTableParams($tablename1,
 249             array(
 250                 'foo' => array (
 251                     'name' => 'foo',
 252                     'type' => 'varchar',
 253                     'len' => '255',
 254                     ),
 255                 ),
 256             array()
 257             );
 258         $tablename2 = 'test2_' . mt_rand();
 259         $this->_db->createTableParams($tablename2,
 260             array(
 261                 'foo' => array (
 262                     'name' => 'foo',
 263                     'type' => 'varchar',
 264                     'len' => '255',
 265                     ),
 266                 ),
 267             array()
 268             );
 269
 270         $res = $this->_db->compareFieldInTables(
 271             'foo', $tablename1, $tablename2);
 272
 273         $this->assertEquals($res['msg'],'match');
 274
 275         $this->_db->dropTableName($tablename1);
 276         $this->_db->dropTableName($tablename2);
 277     }
 278
 279     public function testCompareFieldInTablesNotInTable1()
 280     {
 281         $tablename1 = 'test3_' . mt_rand();
 282         $this->_db->createTableParams($tablename1,
 283             array(
 284                 'foobar' => array (
 285                     'name' => 'foobar',
 286                     'type' => 'varchar',
 287                     'len' => '255',
 288                     ),
 289                 ),
 290             array()
 291             );
 292         $tablename2 = 'test4_' . mt_rand();
 293         $this->_db->createTableParams($tablename2,
 294             array(
 295                 'foo' => array (
 296                     'name' => 'foo',
 297                     'type' => 'varchar',
 298                     'len' => '255',
 299                     ),
 300                 ),
 301             array()
 302             );
 303
 304         $res = $this->_db->compareFieldInTables(
 305             'foo', $tablename1, $tablename2);
 306         $this->assertEquals($res['msg'],'not_exists_table1');
 307
 308         $this->_db->dropTableName($tablename1);
 309         $this->_db->dropTableName($tablename2);
 310     }
 311
 312     public function testCompareFieldInTablesNotInTable2()
 313     {
 314         $tablename1 = 'test5_' . mt_rand();
 315         $this->_db->createTableParams($tablename1,
 316             array(
 317                 'foo' => array (
 318                     'name' => 'foo',
 319                     'type' => 'varchar',
 320                     'len' => '255',
 321                     ),
 322                 ),
 323             array()
 324             );
 325         $tablename2 = 'test6_' . mt_rand();
 326         $this->_db->createTableParams($tablename2,
 327             array(
 328                 'foobar' => array (
 329                     'name' => 'foobar',
 330                     'type' => 'varchar',
 331                     'len' => '255',
 332                     ),
 333                 ),
 334             array()
 335             );
 336
 337         $res = $this->_db->compareFieldInTables(
 338             'foo', $tablename1, $tablename2);
 339
 340         $this->assertEquals($res['msg'],'not_exists_table2');
 341
 342         $this->_db->dropTableName($tablename1);
 343         $this->_db->dropTableName($tablename2);
 344     }
 345
 346     public function testCompareFieldInTablesFieldsDoNotMatch()
 347     {
 348         $tablename1 = 'test7_' . mt_rand();
 349         $this->_db->createTableParams($tablename1,
 350             array(
 351                 'foo' => array (
 352                     'name' => 'foo',
 353                     'type' => 'varchar',
 354                     'len' => '255',
 355                     ),
 356                 ),
 357             array()
 358             );
 359         $tablename2 = 'test8_' . mt_rand();
 360         $this->_db->createTableParams($tablename2,
 361             array(
 362                 'foo' => array (
 363                     'name' => 'foo',
 364                     'type' => 'int',
 365                     ),
 366                 ),
 367             array()
 368             );
 369
 370         $res = $this->_db->compareFieldInTables(
 371             'foo', $tablename1, $tablename2);
 372
 373         $this->assertEquals($res['msg'],'no_match');
 374
 375         $this->_db->dropTableName($tablename1);
 376         $this->_db->dropTableName($tablename2);
 377     }
 378
 379     public function testCompareIndexInTables()
 380     {
 381         $tablename1 = 'test9_' . mt_rand();
 382         $this->_db->createTableParams($tablename1,
 383             array(
 384                 'foo' => array (
 385                     'name' => 'foo',
 386                     'type' => 'varchar',
 387                     'len' => '255',
 388                     ),
 389                 ),
 390             array(
 391                 array(
 392                     'name'   => 'idx_foo',
 393                     'type'   => 'index',
 394                     'fields' => array('foo'),
 395                     )
 396                 )
 397             );
 398         $tablename2 = 'test10_' . mt_rand();
 399         $this->_db->createTableParams($tablename2,
 400             array(
 401                 'foo' => array (
 402                     'name' => 'foo',
 403                     'type' => 'varchar',
 404                     'len' => '255',
 405                     ),
 406                 ),
 407             array(
 408                 array(
 409                     'name'   => 'idx_foo',
 410                     'type'   => 'index',
 411                     'fields' => array('foo'),
 412                     )
 413                 )
 414             );
 415
 416         $res = $this->_db->compareIndexInTables(
 417             'idx_foo', $tablename1, $tablename2);
 418
 419         $this->assertEquals($res['msg'],'match');
 420
 421         $this->_db->dropTableName($tablename1);
 422         $this->_db->dropTableName($tablename2);
 423     }
 424
 425     public function testCompareIndexInTablesNotInTable1()
 426     {
 427         $tablename1 = 'test11_' . mt_rand();
 428         $this->_db->createTableParams($tablename1,
 429             array(
 430                 'foo' => array (
 431                     'name' => 'foo',
 432                     'type' => 'varchar',
 433                     'len' => '255',
 434                     ),
 435                 ),
 436             array(
 437                 array(
 438                     'name'   => 'idx_foobar',
 439                     'type'   => 'index',
 440                     'fields' => array('foo'),
 441                     )
 442                 )
 443             );
 444         $tablename2 = 'test12_' . mt_rand();
 445         $this->_db->createTableParams($tablename2,
 446             array(
 447                 'foo' => array (
 448                     'name' => 'foo',
 449                     'type' => 'varchar',
 450                     'len' => '255',
 451                     ),
 452                 ),
 453             array(
 454                 array(
 455                     'name'   => 'idx_foo',
 456                     'type'   => 'index',
 457                     'fields' => array('foo'),
 458                     )
 459                 )
 460             );
 461
 462         $res = $this->_db->compareIndexInTables(
 463             'idx_foo', $tablename1, $tablename2);
 464
 465         $this->assertEquals($res['msg'],'not_exists_table1');
 466
 467         $this->_db->dropTableName($tablename1);
 468         $this->_db->dropTableName($tablename2);
 469     }
 470
 471     public function testCompareIndexInTablesNotInTable2()
 472     {
 473         $tablename1 = 'test13_' . mt_rand();
 474         $this->_db->createTableParams($tablename1,
 475             array(
 476                 'foo' => array (
 477                     'name' => 'foo',
 478                     'type' => 'varchar',
 479                     'len' => '255',
 480                     ),
 481                 ),
 482             array(
 483                 array(
 484                     'name'   => 'idx_foo',
 485                     'type'   => 'index',
 486                     'fields' => array('foo'),
 487                     )
 488                 )
 489             );
 490         $tablename2 = 'test14_' . mt_rand();
 491         $this->_db->createTableParams($tablename2,
 492             array(
 493                 'foo' => array (
 494                     'name' => 'foo',
 495                     'type' => 'varchar',
 496                     'len' => '255',
 497                     ),
 498                 ),
 499             array(
 500                 array(
 501                     'name'   => 'idx_foobar',
 502                     'type'   => 'index',
 503                     'fields' => array('foo'),
 504                     )
 505                 )
 506             );
 507
 508         $res = $this->_db->compareIndexInTables(
 509             'idx_foo', $tablename1, $tablename2);
 510
 511         $this->assertEquals($res['msg'],'not_exists_table2');
 512
 513         $this->_db->dropTableName($tablename1);
 514         $this->_db->dropTableName($tablename2);
 515     }
 516
 517     public function testCompareIndexInTablesIndexesDoNotMatch()
 518     {
 519         $tablename1 = 'test15_' . mt_rand();
 520         $this->_db->createTableParams($tablename1,
 521             array(
 522                 'foo' => array (
 523                     'name' => 'foo',
 524                     'type' => 'varchar',
 525                     'len' => '255',
 526                     ),
 527                 ),
 528             array(
 529                 array(
 530                     'name'   => 'idx_foo',
 531                     'type'   => 'index',
 532                     'fields' => array('foo'),
 533                     )
 534                 )
 535             );
 536         $tablename2 = 'test16_' . mt_rand();
 537         $this->_db->createTableParams($tablename2,
 538             array(
 539                 'foo' => array (
 540                     'name' => 'foobar',
 541                     'type' => 'varchar',
 542                     'len' => '255',
 543                     ),
 544                 ),
 545             array(
 546                 array(
 547                     'name'   => 'idx_foo',
 548                     'type'   => 'index',
 549                     'fields' => array('foobar'),
 550                     )
 551                 )
 552             );
 553
 554         $res = $this->_db->compareIndexInTables(
 555             'idx_foo', $tablename1, $tablename2);
 556
 557         $this->assertEquals($res['msg'],'no_match');
 558
 559         $this->_db->dropTableName($tablename1);
 560         $this->_db->dropTableName($tablename2);
 561     }
 562
 563     public function testCreateIndex()
 564     {
 565         // TODO: Write this test
 566     }
 567
 568     public function testAddIndexes()
 569     {
 570         $tablename1 = 'test17_' . mt_rand();
 571         $this->_db->createTableParams($tablename1,
 572             array(
 573                 'foo' => array (
 574                     'name' => 'foo',
 575                     'type' => 'varchar',
 576                     'len' => '255',
 577                     ),
 578                 ),
 579             array(
 580                 array(
 581                     'name'   => 'idx_foo',
 582                     'type'   => 'index',
 583                     'fields' => array('foo'),
 584                     )
 585                 )
 586             );
 587         $tablename2 = 'test18_' . mt_rand();
 588         $this->_db->createTableParams($tablename2,
 589             array(
 590                 'foo' => array (
 591                     'name' => 'foo',
 592                     'type' => 'varchar',
 593                     'len' => '255',
 594                     ),
 595                 ),
 596             array()
 597             );
 598
 599         // first test not executing the statement
 600         $this->_db->addIndexes(
 601             $tablename2,
 602             array(array(
 603                 'name'   => 'idx_foo',
 604                 'type'   => 'index',
 605                 'fields' => array('foo'),
 606                 )),
 607             false);
 608
 609         $res = $this->_db->compareIndexInTables(
 610             'idx_foo', $tablename1, $tablename2);
 611
 612         $this->assertEquals($res['msg'],'not_exists_table2');
 613
 614         // now, execute the statement
 615         $this->_db->addIndexes(
 616             $tablename2,
 617             array(array(
 618                 'name'   => 'idx_foo',
 619                 'type'   => 'index',
 620                 'fields' => array('foo'),
 621                 ))
 622             );
 623         $res = $this->_db->compareIndexInTables(
 624             'idx_foo', $tablename1, $tablename2);
 625
 626         $this->assertEquals($res['msg'],'match');
 627
 628         $this->_db->dropTableName($tablename1);
 629         $this->_db->dropTableName($tablename2);
 630     }
 631
 632     public function testDropIndexes()
 633     {
 634         $tablename1 = 'test19_' . mt_rand();
 635         $this->_db->createTableParams($tablename1,
 636             array(
 637                 'foo' => array (
 638                     'name' => 'foo',
 639                     'type' => 'varchar',
 640                     'len' => '255',
 641                     ),
 642                 ),
 643             array(
 644                 array(
 645                     'name'   => 'idx_foo',
 646                     'type'   => 'index',
 647                     'fields' => array('foo'),
 648                     )
 649                 )
 650             );
 651         $tablename2 = 'test20_' . mt_rand();
 652         $this->_db->createTableParams($tablename2,
 653             array(
 654                 'foo' => array (
 655                     'name' => 'foo',
 656                     'type' => 'varchar',
 657                     'len' => '255',
 658                     ),
 659                 ),
 660             array(
 661                 array(
 662                     'name'   => 'idx_foo',
 663                     'type'   => 'index',
 664                     'fields' => array('foo'),
 665                     )
 666                 )
 667             );
 668
 669         $res = $this->_db->compareIndexInTables(
 670             'idx_foo', $tablename1, $tablename2);
 671
 672         $this->assertEquals($res['msg'],'match');
 673
 674         // first test not executing the statement
 675         $this->_db->dropIndexes(
 676             $tablename2,
 677             array(array(
 678                 'name'   => 'idx_foo',
 679                 'type'   => 'index',
 680                 'fields' => array('foo'),
 681                 )),
 682             false);
 683
 684         $res = $this->_db->compareIndexInTables(
 685             'idx_foo', $tablename1, $tablename2);
 686
 687         $this->assertEquals($res['msg'],'match');
 688
 689         // now, execute the statement
 690         $sql = $this->_db->dropIndexes(
 691             $tablename2,
 692             array(array(
 693                 'name'   => 'idx_foo',
 694                 'type'   => 'index',
 695                 'fields' => array('foo'),
 696                 )),
 697             true
 698             );
 699
 700         $res = $this->_db->compareIndexInTables(
 701             'idx_foo', $tablename1, $tablename2);
 702
 703         $this->assertEquals($res['msg'],'not_exists_table2');
 704
 705         $this->_db->dropTableName($tablename1);
 706         $this->_db->dropTableName($tablename2);
 707     }
 708
 709     public function testModifyIndexes()
 710     {
 711         $tablename1 = 'test21_' . mt_rand();
 712         $this->_db->createTableParams($tablename1,
 713             array(
 714                 'foo' => array (
 715                     'name' => 'foo',
 716                     'type' => 'varchar',
 717                     'len' => '255',
 718                     ),
 719                 'foobar' => array (
 720                     'name' => 'foobar',
 721                     'type' => 'varchar',
 722                     'len' => '255',
 723                     ),
 724                 ),
 725             array(
 726                 array(
 727                     'name'   => 'idx_foo',
 728                     'type'   => 'index',
 729                     'fields' => array('foo'),
 730                     )
 731                 )
 732             );
 733         $tablename2 = 'test22_' . mt_rand();
 734         $this->_db->createTableParams($tablename2,
 735             array(
 736                 'foo' => array (
 737                     'name' => 'foo',
 738                     'type' => 'varchar',
 739                     'len' => '255',
 740                     ),
 741                 'foobar' => array (
 742                     'name' => 'foobar',
 743                     'type' => 'varchar',
 744                     'len' => '255',
 745                     ),
 746                 ),
 747             array(
 748                 array(
 749                     'name'   => 'idx_foo',
 750                     'type'   => 'index',
 751                     'fields' => array('foobar'),
 752                     )
 753                 )
 754             );
 755
 756         $res = $this->_db->compareIndexInTables(
 757             'idx_foo', $tablename1, $tablename2);
 758
 759         $this->assertEquals($res['msg'],'no_match');
 760
 761         $this->_db->modifyIndexes(
 762             $tablename2,
 763             array(array(
 764                 'name'   => 'idx_foo',
 765                 'type'   => 'index',
 766                 'fields' => array('foo'),
 767                 )),
 768             false);
 769
 770         $res = $this->_db->compareIndexInTables(
 771             'idx_foo', $tablename1, $tablename2);
 772
 773         $this->assertEquals($res['msg'],'no_match');
 774
 775         $this->_db->modifyIndexes(
 776             $tablename2,
 777             array(array(
 778                 'name'   => 'idx_foo',
 779                 'type'   => 'index',
 780                 'fields' => array('foo'),
 781                 ))
 782             );
 783
 784         $res = $this->_db->compareIndexInTables(
 785             'idx_foo', $tablename1, $tablename2);
 786
 787         $this->assertEquals($res['msg'],'match');
 788
 789         $this->_db->dropTableName($tablename1);
 790         $this->_db->dropTableName($tablename2);
 791     }
 792
 793     public function testAddColumn()
 794     {
 795         $tablename1 = 'test23_' . mt_rand();
 796         $this->_db->createTableParams($tablename1,
 797             array(
 798                 'foo' => array (
 799                     'name' => 'foo',
 800                     'type' => 'varchar',
 801                     'len' => '255',
 802                     ),
 803                 'foobar' => array (
 804                     'name' => 'foobar',
 805                     'type' => 'varchar',
 806                     'len' => '255',
 807                     ),
 808                 ),
 809             array()
 810             );
 811         $tablename2 = 'test24_' . mt_rand();
 812         $this->_db->createTableParams($tablename2,
 813             array(
 814                 'foo' => array (
 815                     'name' => 'foo',
 816                     'type' => 'varchar',
 817                     'len' => '255',
 818                     ),
 819                 ),
 820             array()
 821             );
 822
 823         $res = $this->_db->compareFieldInTables(
 824             'foobar', $tablename1, $tablename2);
 825
 826         $this->assertEquals($res['msg'],'not_exists_table2');
 827
 828         $this->_db->addColumn(
 829             $tablename2,
 830             array(
 831                 'foobar' => array (
 832                     'name' => 'foobar',
 833                     'type' => 'varchar',
 834                     'len' => '255',
 835                     )
 836                 )
 837             );
 838
 839         $res = $this->_db->compareFieldInTables(
 840             'foobar', $tablename1, $tablename2);
 841
 842         $this->assertEquals($res['msg'],'match');
 843
 844         $this->_db->dropTableName($tablename1);
 845         $this->_db->dropTableName($tablename2);
 846     }
 847
 848     public function testAlterColumn()
 849     {
 850         $tablename1 = 'test25_' . mt_rand();
 851         $this->_db->createTableParams($tablename1,
 852             array(
 853                 'foo' => array (
 854                     'name' => 'foo',
 855                     'type' => 'varchar',
 856                     'len' => '255',
 857                     ),
 858                 'foobar' => array (
 859                     'name' => 'foobar',
 860                     'type' => 'varchar',
 861                     'len' => '255',
 862                     'required' => true,
 863                     ),
 864                 ),
 865             array()
 866             );
 867         $tablename2 = 'test26_' . mt_rand();
 868         $this->_db->createTableParams($tablename2,
 869             array(
 870                 'foo' => array (
 871                     'name' => 'foo',
 872                     'type' => 'varchar',
 873                     'len' => '255',
 874                     ),
 875                 'foobar' => array (
 876                     'name' => 'foobar',
 877                     'type' => 'int',
 878                     ),
 879                 ),
 880             array()
 881             );
 882
 883         $res = $this->_db->compareFieldInTables(
 884             'foobar', $tablename1, $tablename2);
 885
 886         $this->assertEquals($res['msg'],'no_match');
 887
 888         $this->_db->alterColumn(
 889             $tablename2,
 890             array(
 891                 'foobar' => array (
 892                     'name' => 'foobar',
 893                     'type' => 'varchar',
 894                     'len' => '255',
 895                     'required' => true,
 896                     )
 897                 )
 898             );
 899
 900         $res = $this->_db->compareFieldInTables(
 901             'foobar', $tablename1, $tablename2);
 902
 903         $this->assertEquals($res['msg'],'match');
 904
 905         $this->_db->dropTableName($tablename1);
 906         $this->_db->dropTableName($tablename2);
 907     }
 908
 909     public function testDropTable()
 910     {
 911         // TODO: Write this test
 912     }
 913
 914     public function testDropTableName()
 915     {
 916         $tablename = 'test' . mt_rand();
 917         $this->_db->createTableParams($tablename,
 918             array(
 919                 'foo' => array (
 920                     'name' => 'foo',
 921                     'type' => 'varchar',
 922                     'len' => '255',
 923                     ),
 924                 ),
 925             array()
 926             );
 927         $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
 928
 929         $this->_db->dropTableName($tablename);
 930
 931         $this->assertFalse(in_array($tablename,$this->_db->getTablesArray()));
 932     }
 933
 934     public function testDeleteColumn()
 935     {
 936         // TODO: Write this test
 937     }
 938
 939     public function testDisconnectAll()
 940     {
 941         $this->_db->disconnectAll();
 942         $this->assertTrue($this->_db->checkError());
 943         $this->_db = &DBManagerFactory::getInstance();
 944     }
 945
 946     public function testQuote()
 947     {
 948         $string = "'dog eat ";
 949
 950         if ( $this->_db->dbType == 'mysql')
 951             $this->assertEquals($this->_db->quoteForEmail($string),"\'dog eat ");
 952         else
 953             $this->assertEquals($this->_db->quoteForEmail($string),"''dog eat ");
 954     }
 955
 956     public function testQuoteForEmail()
 957     {
 958         $string = "'dog eat ";
 959
 960         if ( $this->_db->dbType == 'mysql')
 961             $this->assertEquals($this->_db->quoteForEmail($string),"\'dog eat ");
 962         else
 963             $this->assertEquals($this->_db->quoteForEmail($string),"''dog eat ");
 964     }
 965
 966     public function testArrayQuote()
 967     {
 968         $string = array("'dog eat ");
 969         $this->_db->arrayQuote($string);
 970         if ( $this->_db->dbType == 'mysql')
 971             $this->assertEquals($string,array("\'dog eat "));
 972         else
 973             $this->assertEquals($string,array("''dog eat "));
 974     }
 975
 976     public function testQuery()
 977     {
 978         $beanIds = $this->_createRecords(5);
 979
 980         $result = $this->_db->query("SELECT id From contacts where last_name = 'foobar'");
 981         if ( $this->_db instanceOf MysqliManager )
 982             $this->assertInstanceOf('Mysqli_result',$result);
 983         else
 984             $this->assertTrue(is_resource($result));
 985
 986         while ( $row = $this->_db->fetchByAssoc($result) )
 987             $this->assertTrue(in_array($row['id'],$beanIds),"Id not found '{$row['id']}'");
 988
 989         $this->_removeRecords($beanIds);
 990     }
 991
 992     public function disabledLimitQuery()
 993     {
 994         $beanIds = $this->_createRecords(5);
 995         $_REQUEST['module'] = 'contacts';
 996         $result = $this->_db->limitQuery("SELECT id From contacts where last_name = 'foobar'",1,3);
 997         if ( $this->_db instanceOf MysqliManager )
 998             $this->assertInstanceOf('Mysqli_result',$result);
 999         else
1000             $this->assertTrue(is_resource($result));
1001
1002         while ( $row = $this->_db->fetchByAssoc($result) ) {
1003             if ( $row['id'][0] > 3 || $row['id'][0] < 0 )
1004                 $this->assertFalse(in_array($row['id'],$beanIds),"Found {$row['id']} in error");
1005             else
1006                 $this->assertTrue(in_array($row['id'],$beanIds),"Didn't find {$row['id']}");
1007         }
1008         unset($_REQUEST['module']);
1009         $this->_removeRecords($beanIds);
1010     }
1011
1012     public function testGetOne()
1013     {
1014         $beanIds = $this->_createRecords(1);
1015
1016         $id = $this->_db->getOne("SELECT id From contacts where last_name = 'foobar'");
1017         $this->assertEquals($id,$beanIds[0]);
1018
1019         // bug 38994
1020         if($this->_db instanceof MysqlManager) {
1021             $id = $this->_db->getOne("SELECT id From contacts where last_name = 'foobar' LIMIT 0,1");
1022             $this->assertEquals($id,$beanIds[0]);
1023         }
1024
1025         $this->_removeRecords($beanIds);
1026     }
1027
1028     public function testGetFieldsArray()
1029     {
1030         $beanIds = $this->_createRecords(1);
1031
1032         $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1033         $fields = $this->_db->getFieldsArray($result,true);
1034
1035         $this->assertEquals(array("id"),$fields);
1036
1037         $this->_removeRecords($beanIds);
1038     }
1039
1040     public function testGetRowCount()
1041     {
1042         $beanIds = $this->_createRecords(1);
1043
1044         $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1045
1046         $this->assertEquals($this->_db->getRowCount($result),1);
1047
1048         $this->_removeRecords($beanIds);
1049     }
1050
1051     public function testGetAffectedRowCount()
1052     {
1053         if ( ($this->_db instanceOf MysqliManager) )
1054             $this->markTestSkipped('Skipping on Mysqli; doesn\'t apply to this backend');
1055
1056         $beanIds = $this->_createRecords(1);
1057         $result = $this->_db->query("DELETE From contacts where id = '{$beanIds[0]}'");
1058         $this->assertEquals($this->_db->getAffectedRowCount(),1);
1059     }
1060
1061     public function testFetchByAssoc()
1062     {
1063         $beanIds = $this->_createRecords(1);
1064
1065         $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1066
1067         $row = $this->_db->fetchByAssoc($result);
1068
1069         $this->assertTrue(is_array($row));
1070         $this->assertEquals($row['id'],$beanIds[0]);
1071
1072         $this->_removeRecords($beanIds);
1073     }
1074
1075     public function testConnect()
1076     {
1077         // TODO: Write this test
1078     }
1079
1080     public function testDisconnect()
1081     {
1082         $this->_db->disconnect();
1083         $this->assertTrue($this->_db->checkError());
1084         $this->_db = &DBManagerFactory::getInstance();
1085     }
1086
1087     public function testGetTablesArray()
1088     {
1089         $tablename = 'test' . mt_rand();
1090         $this->_db->createTableParams($tablename,
1091             array(
1092                 'foo' => array (
1093                     'name' => 'foo',
1094                     'type' => 'varchar',
1095                     'len' => '255',
1096                     ),
1097                 ),
1098             array()
1099             );
1100
1101         $this->assertTrue($this->_db->tableExists($tablename));
1102
1103         $this->_db->dropTableName($tablename);
1104     }
1105
1106     public function testVersion()
1107     {
1108         $ver = $this->_db->version();
1109
1110         $this->assertTrue(is_string($ver));
1111     }
1112
1113     public function testTableExists()
1114     {
1115         $tablename = 'test' . mt_rand();
1116         $this->_db->createTableParams($tablename,
1117             array(
1118                 'foo' => array (
1119                     'name' => 'foo',
1120                     'type' => 'varchar',
1121                     'len' => '255',
1122                     ),
1123                 ),
1124             array()
1125             );
1126
1127         $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1128
1129         $this->_db->dropTableName($tablename);
1130     }
1131
1132     public function providerCompareVardefs()
1133     {
1134         $returnArray = array(
1135             array(
1136                 array(
1137                     'name' => 'foo',
1138                     'type' => 'varchar',
1139                     'len' => '255',
1140                     ),
1141                 array(
1142                     'name' => 'foo',
1143                     'type' => 'varchar',
1144                     'len' => '255',
1145                     ),
1146                 true),
1147             array(
1148                 array(
1149                     'name' => 'foo',
1150                     'type' => 'char',
1151                     'len' => '255',
1152                     ),
1153                 array(
1154                     'name' => 'foo',
1155                     'type' => 'varchar',
1156                     'len' => '255',
1157                     ),
1158                 false),
1159             array(
1160                 array(
1161                     'name' => 'foo',
1162                     'type' => 'char',
1163                     'len' => '255',
1164                     ),
1165                 array(
1166                     'name' => 'foo',
1167                     'len' => '255',
1168                 ),
1169                 false),
1170             array(
1171                 array(
1172                     'name' => 'foo',
1173                     'len' => '255',
1174                     ),
1175                 array(
1176                     'name' => 'foo',
1177                     'type' => 'varchar',
1178                     'len' => '255',
1179                     ),
1180                 true),
1181             array(
1182                 array(
1183                     'name' => 'foo',
1184                     'type' => 'varchar',
1185                     'len' => '255',
1186                     ),
1187                 array(
1188                     'name' => 'FOO',
1189                     'type' => 'varchar',
1190                     'len' => '255',
1191                     ),
1192                 true),
1193             );
1194
1195         return $returnArray;
1196     }
1197
1198     /**
1199      * @dataProvider providerCompareVarDefs
1200      */
1201     public function testCompareVarDefs($fieldDef1,$fieldDef2,$expectedResult)
1202     {
1203         if ( $expectedResult ) {
1204             $this->assertTrue($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1205         }
1206         else {
1207             $this->assertFalse($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1208         }
1209     }
1210
1211     public function providerConvert()
1212     {
1213         $db = DBManagerFactory::getInstance();
1214
1215         $returnArray = array(
1216             array(
1217                 array('foo','nothing'),
1218                 'foo'
1219                 )
1220             );
1221         if ( $db instanceOf MysqlManager )
1222             $returnArray += array(
1223                 array(
1224                     array('foo','today'),
1225                     'CURDATE()'
1226                     ),
1227                 array(
1228                     array('foo','left'),
1229                     'LEFT(foo)'
1230                 ),
1231             array(
1232                     array('foo','left',array('1','2','3')),
1233                     'LEFT(foo,1,2,3)'
1234                     ),
1235                 array(
1236                     array('foo','date_format'),
1237                     'DATE_FORMAT(foo)'
1238                         ),
1239                 array(
1240                     array('foo','date_format',array('1','2','3')),
1241                     'DATE_FORMAT(foo,1,2,3)'
1242                     ),
1243                 array(
1244                     array('foo','datetime',array("'%Y-%m'")),
1245                     'DATE_FORMAT(foo, \'%Y-%m-%d %H:%i:%s\')'
1246                         ),
1247                 array(
1248                     array('foo','IFNULL'),
1249                     'IFNULL(foo)'
1250                     ),
1251                 array(
1252                     array('foo','IFNULL',array('1','2','3')),
1253                     'IFNULL(foo,1,2,3)'
1254                     ),
1255                 array(
1256                     array('foo','CONCAT',array('1','2','3')),
1257                     'CONCAT(foo,1,2,3)'
1258                     ),
1259                 array(
1260                     array('foo','text2char'),
1261                     'foo'
1262                 ),
1263             );
1264         if ( $db instanceOf MssqlManager )
1265             $returnArray += array(
1266                 array(
1267                     array('foo','today'),
1268                     'GETDATE()'
1269                     ),
1270                 array(
1271                     array('foo','left'),
1272                     'LEFT(foo)'
1273                     ),
1274                 array(
1275                     array('foo','left',array('1','2','3')),
1276                     'LEFT(foo,1,2,3)'
1277                     ),
1278                 array(
1279                     array('foo','date_format'),
1280                     'CONVERT(varchar(10),foo,120)'
1281                     ),
1282                 array(
1283                     array('foo','date_format',array('1','2','3')),
1284                     'CONVERT(varchar(10),foo,120)'
1285                     ),
1286                 array(
1287                     array('foo','date_format',array("'%Y-%m'")),
1288                     'CONVERT(varchar(7),foo,120)'
1289                     ),
1290                 array(
1291                     array('foo','IFNULL'),
1292                     'ISNULL(foo)'
1293                     ),
1294                 array(
1295                     array('foo','IFNULL',array('1','2','3')),
1296                     'ISNULL(foo,1,2,3)'
1297                     ),
1298                 array(
1299                     array('foo','CONCAT',array('1','2','3')),
1300                     'foo+1+2+3'
1301                     ),
1302                 array(
1303                     array('foo','text2char'),
1304                     'CAST(foo AS varchar(8000))'
1305                     ),
1306                 );
1307         if ( $db instanceOf SqlsrvManager )
1308             $returnArray += array(
1309                 array(
1310                     array('foo','datetime'),
1311                     'CONVERT(varchar(20),foo,120)'
1312                     ),
1313                 );
1314
1315         return $returnArray;
1316     }
1317
1318     /**
1319      * @ticket 33283
1320      * @dataProvider providerConvert
1321      */
1322     public function testConvert(
1323          array $parameters,
1324          $result
1325         )
1326     {
1327          if ( count($parameters) < 3 )
1328              $this->assertEquals(
1329                  $this->_db->convert($parameters[0],$parameters[1]),
1330                  $result);
1331          elseif ( count($parameters) < 4 )
1332              $this->assertEquals(
1333                  $this->_db->convert($parameters[0],$parameters[1],$parameters[2]),
1334                  $result);
1335         else
1336             $this->assertEquals(
1337                  $this->_db->convert($parameters[0],$parameters[1],$parameters[2],$parameters[3]),
1338                  $result);
1339      }
1340
1341      /**
1342       * @ticket 33283
1343       */
1344      public function testConcat()
1345      {
1346          $ret = $this->_db->concat('foo',array('col1','col2','col3'));
1347
1348          if ( $this->_db instanceOf MysqlManager )
1349              $this->assertEquals($ret,
1350                  "TRIM(CONCAT(IFNULL(foo.col1,''),' ',IFNULL(foo.col2,''),' ',IFNULL(foo.col3,'')))"
1351                  );
1352          if ( $this->_db instanceOf MssqlManager )
1353              $this->assertEquals($ret,
1354                  "LTRIM(RTRIM(ISNULL(foo.col1,'') + ' ' + ISNULL(foo.col2,'') + ' ' + ISNULL(foo.col3,'')))"
1355                  );
1356          if ( $this->_db instanceOf OracleManager )
1357              $this->assertEquals($ret,
1358                  "TRIM(CONCAT(CONCAT(CONCAT(NVL(foo.col1,''),' '), CONCAT(NVL(foo.col2,''),' ')), CONCAT(NVL(foo.col3,''),' ')))"
1359                  );
1360      }
1361
1362      public function providerFromConvert()
1363      {
1364          $returnArray = array(
1365              array(
1366                  array('foo','nothing'),
1367                  'foo'
1368                  )
1369              );
1370          if ( $this->_db instanceOf MssqlManager
1371                 || $this->_db instanceOf OracleManager )
1372              $returnArray += array(
1373                  array(
1374                      array('2009-01-01 12:00:00','date'),
1375                      '2009-01-01'
1376                      ),
1377                  array(
1378                      array('2009-01-01 12:00:00','time'),
1379                      '12:00:00'
1380                      )
1381                  );
1382
1383          return $returnArray;
1384      }
1385
1386      /**
1387       * @ticket 33283
1388       * @dataProvider providerFromConvert
1389       */
1390      public function testFromConvert(
1391          array $parameters,
1392          $result
1393          )
1394      {
1395          $this->assertEquals(
1396              $this->_db->fromConvert($parameters[0],$parameters[1]),
1397              $result);
1398     }
1399
1400     /**
1401      * @ticket 34892
1402      */
1403     public function testMssqlNotClearingErrorResults()
1404     {
1405         if ( get_class($this->_db) != 'MssqlManager' )
1406             $this->markTestSkipped('Skipping; only applies with php_mssql driver');
1407
1408         // execute a bad query
1409         $this->_db->query("select dsdsdsdsdsdsdsdsdsd");
1410         // assert it found an error
1411         $this->assertTrue($this->_db->checkError());
1412         // now, execute a good query
1413         $this->_db->query("select * from config");
1414         // and make no error messages are asserted
1415         $this->assertFalse($this->_db->checkError());
1416     }
1417 }