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 require_once 'tests/include/database/TestBean.php';
  41
  42 class DBManagerTest extends Sugar_PHPUnit_Framework_TestCase
  43 {
  44     /**
  45      * @var DBManager
  46      */
  47     private $_db;
  48     protected $created = array();
  49
  50     protected $backupGlobals = FALSE;
  51
  52     static public function setupBeforeClass()
  53     {
  54         $GLOBALS['current_user'] = SugarTestUserUtilities::createAnonymousUser();
  55         $GLOBALS['app_strings'] = return_application_language($GLOBALS['current_language']);
  56     }
  57
  58     static public function tearDownAfterClass()
  59     {
  60         SugarTestUserUtilities::removeAllCreatedAnonymousUsers();
  61         unset($GLOBALS['current_user']);
  62         unset($GLOBALS['app_strings']);
  63     }
  64
  65     public function setUp()
  66     {
  67         if(empty($this->_db)){
  68             $this->_db = DBManagerFactory::getInstance();
  69         }
  70     }
  71
  72     public function tearDown()
  73     {
  74         foreach($this->created as $table => $dummy) {
  75             $this->_db->dropTableName($table);
  76         }
  77     }
  78
  79     protected function createTableParams($tablename, $fieldDefs, $indices)
  80     {
  81         $this->created[$tablename] = true;
  82         return $this->_db->createTableParams($tablename, $fieldDefs, $indices);
  83     }
  84
  85     protected function dropTableName($tablename)
  86     {
  87         unset($this->created[$tablename]);
  88         return $this->_db->dropTableName($tablename);
  89     }
  90
  91     private function _createRecords(
  92         $num
  93         )
  94     {
  95         $beanIds = array();
  96         for ( $i = 0; $i < $num; $i++ ) {
  97             $bean = new Contact();
  98             $bean->id = "$i-test" . mt_rand();
  99             $bean->last_name = "foobar";
 100             $this->_db->insert($bean);
 101             $beanIds[] = $bean->id;
 102         }
 103
 104         return $beanIds;
 105     }
 106
 107     private function _removeRecords(
 108         array $ids
 109         )
 110     {
 111         foreach ($ids as $id)
 112             $this->_db->query("DELETE From contacts where id = '{$id}'");
 113     }
 114
 115     public function testGetDatabase()
 116     {
 117         if ( $this->_db instanceOf MysqliManager )
 118             $this->assertInstanceOf('Mysqli',$this->_db->getDatabase());
 119         else
 120             $this->assertTrue(is_resource($this->_db->getDatabase()));
 121     }
 122
 123     public function testCheckError()
 124     {
 125         $this->assertFalse($this->_db->checkError());
 126         $this->assertFalse($this->_db->lastError());
 127     }
 128
 129     public function testCheckErrorNoConnection()
 130     {
 131         $this->_db->disconnect();
 132         $this->assertTrue($this->_db->checkError());
 133         $this->_db = DBManagerFactory::getInstance();
 134     }
 135
 136     public function testGetQueryTime()
 137     {
 138         $this->_db->version();
 139         $this->assertTrue($this->_db->getQueryTime() > 0);
 140     }
 141
 142     public function testCheckConnection()
 143     {
 144         $this->_db->checkConnection();
 145         if ( $this->_db instanceOf MysqliManager )
 146             $this->assertInstanceOf('Mysqli',$this->_db->getDatabase());
 147         else
 148             $this->assertTrue(is_resource($this->_db->getDatabase()));
 149     }
 150
 151     public function testInsert()
 152     {
 153         $bean = new Contact();
 154         $bean->last_name = 'foobar' . mt_rand();
 155         $bean->id   = 'test' . mt_rand();
 156         $this->_db->insert($bean);
 157
 158         $result = $this->_db->query("select id, last_name from contacts where id = '{$bean->id}'");
 159         $row = $this->_db->fetchByAssoc($result);
 160         $this->assertEquals($row['last_name'],$bean->last_name);
 161         $this->assertEquals($row['id'],$bean->id);
 162
 163         $this->_db->query("delete from contacts where id = '{$row['id']}'");
 164     }
 165
 166     public function testUpdate()
 167     {
 168         $bean = new Contact();
 169         $bean->last_name = 'foobar' . mt_rand();
 170         $bean->id   = 'test' . mt_rand();
 171         $this->_db->insert($bean);
 172         $id = $bean->id;
 173
 174         $bean = new Contact();
 175         $bean->last_name = 'newfoobar' . mt_rand();
 176         $this->_db->update($bean,array('id'=>$id));
 177
 178         $result = $this->_db->query("select id, last_name from contacts where id = '{$id}'");
 179         $row = $this->_db->fetchByAssoc($result);
 180         $this->assertEquals($row['last_name'],$bean->last_name);
 181         $this->assertEquals($row['id'],$id);
 182
 183         $this->_db->query("delete from contacts where id = '{$row['id']}'");
 184     }
 185
 186     public function testDelete()
 187     {
 188         $bean = new Contact();
 189         $bean->last_name = 'foobar' . mt_rand();
 190         $bean->id   = 'test' . mt_rand();
 191         $this->_db->insert($bean);
 192         $id = $bean->id;
 193
 194         $bean = new Contact();
 195         $this->_db->delete($bean,array('id'=>$id));
 196
 197         $result = $this->_db->query("select deleted from contacts where id = '{$id}'");
 198         $row = $this->_db->fetchByAssoc($result);
 199         $this->assertEquals($row['deleted'],'1');
 200
 201         $this->_db->query("delete from contacts where id = '{$id}'");
 202     }
 203
 204     public function testRetrieve()
 205     {
 206         $bean = new Contact();
 207         $bean->last_name = 'foobar' . mt_rand();
 208         $bean->id   = 'test' . mt_rand();
 209         $this->_db->insert($bean);
 210         $id = $bean->id;
 211
 212         $bean = new Contact();
 213         $result = $this->_db->retrieve($bean,array('id'=>$id));
 214         $row = $this->_db->fetchByAssoc($result);
 215         $this->assertEquals($row['id'],$id);
 216
 217         $this->_db->query("delete from contacts where id = '{$id}'");
 218     }
 219
 220     public function testRetrieveView()
 221     {
 222         // TODO: Write this test
 223     }
 224
 225     public function testCreateTable()
 226     {
 227         // TODO: Write this test
 228     }
 229
 230     public function testCreateTableParams()
 231     {
 232         $tablename = 'test' . mt_rand();
 233         $this->createTableParams($tablename,
 234             array(
 235                 'foo' => array (
 236                     'name' => 'foo',
 237                     'type' => 'varchar',
 238                     'len' => '255',
 239                     ),
 240                 ),
 241             array(
 242                 array(
 243                     'name'   => 'idx_'. $tablename,
 244                     'type'   => 'index',
 245                     'fields' => array('foo'),
 246                     )
 247                 )
 248             );
 249         $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
 250
 251         $this->dropTableName($tablename);
 252     }
 253
 254     public function testRepairTable()
 255     {
 256         // TODO: Write this test
 257     }
 258
 259     public function testRepairTableNoChanges()
 260     {
 261         $tableName = 'testRTNC_' . mt_rand();
 262         $params =  array(
 263                 /* VARDEF - id -  ROW[name] => 'id'  [vname] => 'LBL_ID'  [required] => 'true'  [type] => 'char'  [reportable] => ''  [comment] => 'Unique identifier'  [dbType] => 'id'  [len] => '36'  */
 264             'id' =>
 265                 array (
 266                 'name' => 'id',
 267                 'vname' => 'LBL_ID',
 268                 'required'=>true,
 269                 'type' => 'id',
 270                 'reportable'=>false,
 271                 'comment' => 'Unique identifier'
 272                 ),
 273             'date_entered' =>
 274                 array (
 275                 'name' => 'date_entered',
 276                 'vname' => 'LBL_DATE_ENTERED',
 277                 'type' => 'datetime',
 278                 'required'=>true,
 279                 'comment' => 'Date record created'
 280                 ),
 281             'date_modified' =>
 282                 array (
 283                   'name' => 'date_modified',
 284                   'vname' => 'LBL_DATE_MODIFIED',
 285                   'type' => 'datetime',
 286                   'required'=>true,
 287                   'comment' => 'Date record last modified'
 288                 ),
 289             'modified_user_id' =>
 290                 array (
 291                   'name' => 'modified_user_id',
 292                   'rname' => 'user_name',
 293                   'id_name' => 'modified_user_id',
 294                   'vname' => 'LBL_MODIFIED',
 295                   'type' => 'assigned_user_name',
 296                   'table' => 'modified_user_id_users',
 297                   'isnull' => 'false',
 298                   'dbType' => 'id',
 299                   'required'=> false,
 300                   'len' => 36,
 301                   'reportable'=>true,
 302                   'comment' => 'User who last modified record'
 303                 ),
 304             'created_by' =>
 305                 array (
 306                   'name' => 'created_by',
 307                   'rname' => 'user_name',
 308                   'id_name' => 'created_by',
 309                   'vname' => 'LBL_CREATED',
 310                   'type' => 'assigned_user_name',
 311                   'table' => 'created_by_users',
 312                   'isnull' => 'false',
 313                   'dbType' => 'id',
 314                   'len' => 36,
 315                   'comment' => 'User ID who created record'
 316                 ),
 317             'name' =>
 318                 array (
 319                   'name' => 'name',
 320                   'type' => 'varchar',
 321                   'vname' => 'LBL_NAME',
 322                   'len' => 150,
 323                   'comment' => 'Name of the allowable action (view, list, delete, edit)'
 324                 ),
 325             'category' =>
 326                 array (
 327                   'name' => 'category',
 328                   'vname' => 'LBL_CATEGORY',
 329                   'type' => 'varchar',
 330                   'len' =>100,
 331                   'reportable'=>true,
 332                   'comment' => 'Category of the allowable action (usually the name of a module)'
 333                 ),
 334             'acltype' =>
 335                 array (
 336                   'name' => 'acltype',
 337                   'vname' => 'LBL_TYPE',
 338                   'type' => 'varchar',
 339                   'len' =>100,
 340                   'reportable'=>true,
 341                   'comment' => 'Specifier for Category, usually "module"'
 342                 ),
 343             'aclaccess' =>
 344                 array (
 345                   'name' => 'aclaccess',
 346                   'vname' => 'LBL_ACCESS',
 347                   'type' => 'int',
 348                   'len'=>3,
 349                   'reportable'=>true,
 350                   'comment' => 'Number specifying access priority; highest access "wins"'
 351                 ),
 352             'deleted' =>
 353                 array (
 354                   'name' => 'deleted',
 355                   'vname' => 'LBL_DELETED',
 356                   'type' => 'bool',
 357                   'reportable'=>false,
 358                   'comment' => 'Record deletion indicator'
 359                 ),
 360             'roles' =>
 361                 array (
 362                     'name' => 'roles',
 363                     'type' => 'link',
 364                     'relationship' => 'acl_roles_actions',
 365                     'source'=>'non-db',
 366                     'vname'=>'LBL_USERS',
 367                 ),
 368                         'reverse' =>
 369                 array (
 370                     'name' => 'reverse',
 371                     'vname' => 'LBL_REVERSE',
 372                     'type' => 'bool',
 373                     'default' => 0
 374                 ),
 375                         'deleted2' =>
 376                 array (
 377                     'name' => 'deleted2',
 378                     'vname' => 'LBL_DELETED2',
 379                     'type' => 'bool',
 380                     'reportable'=>false,
 381                     'default' => '0'
 382                 ),
 383             'primary_address_country' =>
 384                 array (
 385                    'name' => 'primary_address_country',
 386                    'vname' => 'LBL_PRIMARY_ADDRESS_COUNTRY',
 387                    'type' => 'varchar',
 388                    'group'=>'primary_address',
 389                    'comment' => 'Country for primary address',
 390                    'merge_filter' => 'enabled',
 391                 ),
 392             'refer_url' => array (
 393                 'name' => 'refer_url',
 394                 'vname' => 'LBL_REFER_URL',
 395                 'type' => 'varchar',
 396                 'len' => '255',
 397                 'default' => 'http://',
 398                 'comment' => 'The URL referenced in the tracker URL; no longer used as of 4.2 (see campaign_trkrs)'
 399                 ),
 400             'budget' => array (
 401                 'name' => 'budget',
 402                 'vname' => 'LBL_CAMPAIGN_BUDGET',
 403                 'type' => 'currency',
 404                 'dbType' => 'double',
 405                 'comment' => 'Budgeted amount for the campaign'
 406                 ),
 407             'time_from' => array (
 408                 'name' => 'time_from',
 409                 'vname' => 'LBL_TIME_FROM',
 410                 'type' => 'time',
 411                 'required' => false,
 412                 'reportable' => false,
 413                 ),
 414             'description' =>
 415                 array (
 416                 'name' => 'description',
 417                 'vname' => 'LBL_DESCRIPTION',
 418                 'type' => 'text',
 419                 'comment' => 'Full text of the note',
 420                 'rows' => 6,
 421                 'cols' => 80,
 422                 ),
 423             'cur_plain' => array (
 424                 'name' => 'cur_plain',
 425                 'vname' => 'LBL_curPlain',
 426                 'type' => 'currency',
 427             ),
 428             'cur_len_prec' => array (
 429                 'name' => 'cur_len_prec',
 430                 'vname' => 'LBL_curLenPrec',
 431                 'dbType' => 'decimal',
 432                 'type' => 'currency',
 433                 'len' => '26,6',
 434             ),
 435             'cur_len' => array (
 436                 'name' => 'cur_len',
 437                 'vname' => 'LBL_curLen',
 438                 'dbType' => 'decimal',
 439                 'type' => 'currency',
 440                 'len' => '26',
 441             ),
 442             'cur_len_prec2' => array (
 443                 'name' => 'cur_len_prec2',
 444                 'vname' => 'LBL_curLenPrec',
 445                 'dbType' => 'decimal',
 446                 'type' => 'currency',
 447                 'len' => '26',
 448                 'precision' => '6',
 449             ),
 450             'token_ts' =>
 451             array (
 452                 'name' => 'token_ts',
 453                 'type' => 'long',
 454                 'required' => true,
 455                 'comment' => 'Token timestamp',
 456                 'function' => array('name' => 'displayDateFromTs', 'returns' => 'html', 'onListView' => true)
 457             ),
 458             'conskey' => array(
 459                 'name'          => 'conskey',
 460                 'type'          => 'varchar',
 461                 'len'           => 32,
 462                 'required'      => true,
 463                 'isnull'        => false,
 464             ),
 465         );
 466
 467         if($this->_db->tableExists($tableName)) {
 468             $this->_db->dropTableName($tableName);
 469         }
 470                 $this->createTableParams($tableName, $params, array());
 471
 472         $repair = $this->_db->repairTableParams($tableName, $params, array(), false);
 473
 474         $this->assertEmpty($repair, "Unexpected repairs: " . $repair);
 475
 476         $this->dropTableName($tableName);
 477     }
 478
 479     public function testRepairTableParamsAddData()
 480     {
 481         $tableName = 'test1_' . mt_rand();
 482         $params =  array(
 483                 'foo' => array (
 484                     'name' => 'foo',
 485                     'type' => 'varchar',
 486                     'len' => '255',
 487                     ),
 488         );
 489
 490         if($this->_db->tableExists($tableName)) {
 491             $this->_db->dropTableName($tableName);
 492         }
 493                 $this->createTableParams($tableName, $params, array());
 494
 495                 $params['bar'] =  array (
 496                     'name' => 'bar',
 497                     'type' => 'int',
 498                     );
 499         $cols = $this->_db->get_columns($tableName);
 500         $this->assertArrayNotHasKey('bar', $cols);
 501
 502         $repair = $this->_db->repairTableParams($tableName, $params, array(), false);
 503         $this->assertRegExp('#MISSING IN DATABASE.*bar#i', $repair);
 504         $repair = $this->_db->repairTableParams($tableName, $params, array(), true);
 505         $cols = $this->_db->get_columns($tableName);
 506         $this->assertArrayHasKey('bar', $cols);
 507         $this->assertEquals('bar', $cols['bar']['name']);
 508         $this->assertEquals($this->_db->getColumnType('int'), $cols['bar']['type']);
 509
 510         $this->dropTableName($tableName);
 511     }
 512
 513     public function testRepairTableParamsAddIndex()
 514     {
 515         $tableName = 'test1_' . mt_rand();
 516         $params =  array(
 517                 'foo' => array (
 518                     'name' => 'foo',
 519                     'type' => 'varchar',
 520                     'len' => '255',
 521                     ),
 522                 'bar' => array (
 523                     'name' => 'bar',
 524                     'type' => 'int',
 525                     ),
 526         );
 527         $index = array(
 528                         'name'                  => 'test_index',
 529                         'type'                  => 'index',
 530                         'fields'                => array('foo', 'bar', 'bazz'),
 531                 );
 532         if($this->_db->tableExists($tableName)) {
 533             $this->_db->dropTableName($tableName);
 534         }
 535                 $this->createTableParams($tableName, $params, array());
 536                 $params['bazz'] =  array (
 537                     'name' => 'bazz',
 538                     'type' => 'int',
 539         );
 540
 541         $repair = $this->_db->repairTableParams($tableName, $params, array($index), false);
 542         $this->assertRegExp('#MISSING IN DATABASE.*bazz#i', $repair);
 543         $this->assertRegExp('#MISSING INDEX IN DATABASE.*test_index#i', $repair);
 544         $repair = $this->_db->repairTableParams($tableName, $params, array($index), true);
 545
 546         $idx = $this->_db->get_indices($tableName);
 547         $this->assertArrayHasKey('test_index', $idx);
 548         $this->assertContains('foo', $idx['test_index']['fields']);
 549         $this->assertContains('bazz', $idx['test_index']['fields']);
 550
 551         $cols = $this->_db->get_columns($tableName);
 552         $this->assertArrayHasKey('bazz', $cols);
 553         $this->assertEquals('bazz', $cols['bazz']['name']);
 554         $this->assertEquals($this->_db->getColumnType('int'), $cols['bazz']['type']);
 555
 556         $this->dropTableName($tableName);
 557     }
 558
 559     public function testRepairTableParamsAddIndexAndData()
 560     {
 561         $tableName = 'test1_' . mt_rand();
 562         $params =  array(
 563                 'foo' => array (
 564                     'name' => 'foo',
 565                     'type' => 'varchar',
 566                     'len' => '255',
 567                     ),
 568                 'bar' => array (
 569                     'name' => 'bar',
 570                     'type' => 'int',
 571                     ),
 572         );
 573         $index = array(
 574                         'name'                  => 'test_index',
 575                         'type'                  => 'index',
 576                         'fields'                => array('foo', 'bar'),
 577                 );
 578         if($this->_db->tableExists($tableName)) {
 579             $this->_db->dropTableName($tableName);
 580         }
 581                 $this->createTableParams($tableName, $params, array());
 582
 583         $repair = $this->_db->repairTableParams($tableName, $params, array($index), false);
 584         $this->assertRegExp('#MISSING INDEX IN DATABASE.*test_index#i', $repair);
 585         $repair = $this->_db->repairTableParams($tableName, $params, array($index), true);
 586         $idx = $this->_db->get_indices($tableName);
 587         $this->assertArrayHasKey('test_index', $idx);
 588         $this->assertContains('foo', $idx['test_index']['fields']);
 589         $this->assertContains('bar', $idx['test_index']['fields']);
 590
 591         $this->dropTableName($tableName);
 592     }
 593
 594     public function testCompareFieldInTables()
 595     {
 596         $tablename1 = 'test1_' . mt_rand();
 597         $this->createTableParams($tablename1,
 598             array(
 599                 'foo' => array (
 600                     'name' => 'foo',
 601                     'type' => 'varchar',
 602                     'len' => '255',
 603                     ),
 604                 ),
 605             array()
 606             );
 607         $tablename2 = 'test2_' . mt_rand();
 608         $this->createTableParams($tablename2,
 609             array(
 610                 'foo' => array (
 611                     'name' => 'foo',
 612                     'type' => 'varchar',
 613                     'len' => '255',
 614                     ),
 615                 ),
 616             array()
 617             );
 618
 619         $res = $this->_db->compareFieldInTables(
 620             'foo', $tablename1, $tablename2);
 621
 622         $this->assertEquals($res['msg'],'match');
 623
 624         $this->dropTableName($tablename1);
 625         $this->dropTableName($tablename2);
 626     }
 627
 628     public function testCompareFieldInTablesNotInTable1()
 629     {
 630         $tablename1 = 'test3_' . mt_rand();
 631         $this->createTableParams($tablename1,
 632             array(
 633                 'foobar' => array (
 634                     'name' => 'foobar',
 635                     'type' => 'varchar',
 636                     'len' => '255',
 637                     ),
 638                 ),
 639             array()
 640             );
 641         $tablename2 = 'test4_' . mt_rand();
 642         $this->createTableParams($tablename2,
 643             array(
 644                 'foo' => array (
 645                     'name' => 'foo',
 646                     'type' => 'varchar',
 647                     'len' => '255',
 648                     ),
 649                 ),
 650             array()
 651             );
 652
 653         $res = $this->_db->compareFieldInTables(
 654             'foo', $tablename1, $tablename2);
 655         $this->assertEquals($res['msg'],'not_exists_table1');
 656
 657         $this->dropTableName($tablename1);
 658         $this->dropTableName($tablename2);
 659     }
 660
 661     public function testCompareFieldInTablesNotInTable2()
 662     {
 663         $tablename1 = 'test5_' . mt_rand();
 664         $this->createTableParams($tablename1,
 665             array(
 666                 'foo' => array (
 667                     'name' => 'foo',
 668                     'type' => 'varchar',
 669                     'len' => '255',
 670                     ),
 671                 ),
 672             array()
 673             );
 674         $tablename2 = 'test6_' . mt_rand();
 675         $this->createTableParams($tablename2,
 676             array(
 677                 'foobar' => array (
 678                     'name' => 'foobar',
 679                     'type' => 'varchar',
 680                     'len' => '255',
 681                     ),
 682                 ),
 683             array()
 684             );
 685
 686         $res = $this->_db->compareFieldInTables(
 687             'foo', $tablename1, $tablename2);
 688
 689         $this->assertEquals($res['msg'],'not_exists_table2');
 690
 691         $this->dropTableName($tablename1);
 692         $this->dropTableName($tablename2);
 693     }
 694
 695     public function testCompareFieldInTablesFieldsDoNotMatch()
 696     {
 697         $tablename1 = 'test7_' . mt_rand();
 698         $this->createTableParams($tablename1,
 699             array(
 700                 'foo' => array (
 701                     'name' => 'foo',
 702                     'type' => 'varchar',
 703                     'len' => '255',
 704                     ),
 705                 ),
 706             array()
 707             );
 708         $tablename2 = 'test8_' . mt_rand();
 709         $this->createTableParams($tablename2,
 710             array(
 711                 'foo' => array (
 712                     'name' => 'foo',
 713                     'type' => 'int',
 714                     ),
 715                 ),
 716             array()
 717             );
 718
 719         $res = $this->_db->compareFieldInTables(
 720             'foo', $tablename1, $tablename2);
 721
 722         $this->assertEquals($res['msg'],'no_match');
 723
 724         $this->dropTableName($tablename1);
 725         $this->dropTableName($tablename2);
 726     }
 727
 728 //    public function testCompareIndexInTables()
 729 //    {
 730 //        $tablename1 = 'test9_' . mt_rand();
 731 //        $this->_db->createTableParams($tablename1,
 732 //            array(
 733 //                'foo' => array (
 734 //                    'name' => 'foo',
 735 //                    'type' => 'varchar',
 736 //                    'len' => '255',
 737 //                    ),
 738 //                ),
 739 //            array(
 740 //                array(
 741 //                    'name'   => 'idx_'. $tablename1,
 742 //                    'type'   => 'index',
 743 //                    'fields' => array('foo'),
 744 //                    )
 745 //                )
 746 //            );
 747 //        $tablename2 = 'test10_' . mt_rand();
 748 //        $this->_db->createTableParams($tablename2,
 749 //            array(
 750 //                'foo' => array (
 751 //                    'name' => 'foo',
 752 //                    'type' => 'varchar',
 753 //                    'len' => '255',
 754 //                    ),
 755 //                ),
 756 //            array(
 757 //                array(
 758 //                    'name'   => 'idx_'. $tablename2,
 759 //                    'type'   => 'index',
 760 //                    'fields' => array('foo'),
 761 //                    )
 762 //                )
 763 //            );
 764 //
 765 //        $res = $this->_db->compareIndexInTables(
 766 //            'idx_foo', $tablename1, $tablename2);
 767 //
 768 //        $this->assertEquals($res['msg'],'match');
 769 //
 770 //        $this->_db->dropTableName($tablename1);
 771 //        $this->_db->dropTableName($tablename2);
 772 //    }
 773 //
 774 //    public function testCompareIndexInTablesNotInTable1()
 775 //    {
 776 //        $tablename1 = 'test11_' . mt_rand();
 777 //        $this->_db->createTableParams($tablename1,
 778 //            array(
 779 //                'foo' => array (
 780 //                    'name' => 'foo',
 781 //                    'type' => 'varchar',
 782 //                    'len' => '255',
 783 //                    ),
 784 //                ),
 785 //            array(
 786 //                array(
 787 //                    'name'   => 'idx_'. $tablename1,
 788 //                    'type'   => 'index',
 789 //                    'fields' => array('foo'),
 790 //                    )
 791 //                )
 792 //            );
 793 //        $tablename2 = 'test12_' . mt_rand();
 794 //        $this->_db->createTableParams($tablename2,
 795 //            array(
 796 //                'foo' => array (
 797 //                    'name' => 'foo',
 798 //                    'type' => 'varchar',
 799 //                    'len' => '255',
 800 //                    ),
 801 //                ),
 802 //            array(
 803 //                array(
 804 //                    'name'   => 'idx_'. $tablename2,
 805 //                    'type'   => 'index',
 806 //                    'fields' => array('foo'),
 807 //                    )
 808 //                )
 809 //            );
 810 //
 811 //        $res = $this->_db->compareIndexInTables(
 812 //            'idx_foo', $tablename1, $tablename2);
 813 //
 814 //        $this->assertEquals($res['msg'],'not_exists_table1');
 815 //
 816 //        $this->_db->dropTableName($tablename1);
 817 //        $this->_db->dropTableName($tablename2);
 818 //    }
 819 //
 820 //    public function testCompareIndexInTablesNotInTable2()
 821 //    {
 822 //        $tablename1 = 'test13_' . mt_rand();
 823 //        $this->_db->createTableParams($tablename1,
 824 //            array(
 825 //                'foo' => array (
 826 //                    'name' => 'foo',
 827 //                    'type' => 'varchar',
 828 //                    'len' => '255',
 829 //                    ),
 830 //                ),
 831 //            array(
 832 //                array(
 833 //                    'name'   => 'idx_'. $tablename1,
 834 //                    'type'   => 'index',
 835 //                    'fields' => array('foo'),
 836 //                    )
 837 //                )
 838 //            );
 839 //        $tablename2 = 'test14_' . mt_rand();
 840 //        $this->_db->createTableParams($tablename2,
 841 //            array(
 842 //                'foo' => array (
 843 //                    'name' => 'foo',
 844 //                    'type' => 'varchar',
 845 //                    'len' => '255',
 846 //                    ),
 847 //                ),
 848 //            array(
 849 //                array(
 850 //                    'name'   => 'idx_'. $tablename2,
 851 //                    'type'   => 'index',
 852 //                    'fields' => array('foo'),
 853 //                    )
 854 //                )
 855 //            );
 856 //
 857 //        $res = $this->_db->compareIndexInTables(
 858 //            'idx_foo', $tablename1, $tablename2);
 859 //
 860 //        $this->assertEquals($res['msg'],'not_exists_table2');
 861 //
 862 //        $this->_db->dropTableName($tablename1);
 863 //        $this->_db->dropTableName($tablename2);
 864 //    }
 865 //
 866 //    public function testCompareIndexInTablesIndexesDoNotMatch()
 867 //    {
 868 //        $tablename1 = 'test15_' . mt_rand();
 869 //        $this->_db->createTableParams($tablename1,
 870 //            array(
 871 //                'foo' => array (
 872 //                    'name' => 'foo',
 873 //                    'type' => 'varchar',
 874 //                    'len' => '255',
 875 //                    ),
 876 //                ),
 877 //            array(
 878 //                array(
 879 //                    'name'   => 'idx_foo',
 880 //                    'type'   => 'index',
 881 //                    'fields' => array('foo'),
 882 //                    )
 883 //                )
 884 //            );
 885 //        $tablename2 = 'test16_' . mt_rand();
 886 //        $this->_db->createTableParams($tablename2,
 887 //            array(
 888 //                'foo' => array (
 889 //                    'name' => 'foobar',
 890 //                    'type' => 'varchar',
 891 //                    'len' => '255',
 892 //                    ),
 893 //                ),
 894 //            array(
 895 //                array(
 896 //                    'name'   => 'idx_foo',
 897 //                    'type'   => 'index',
 898 //                    'fields' => array('foobar'),
 899 //                    )
 900 //                )
 901 //            );
 902 //
 903 //        $res = $this->_db->compareIndexInTables(
 904 //            'idx_foo', $tablename1, $tablename2);
 905 //
 906 //        $this->assertEquals($res['msg'],'no_match');
 907 //
 908 //        $this->_db->dropTableName($tablename1);
 909 //        $this->_db->dropTableName($tablename2);
 910 //    }
 911
 912     public function testCreateIndex()
 913     {
 914         // TODO: Write this test
 915     }
 916
 917     public function testAddIndexes()
 918     {
 919         //TODO Fix test with normal index inspection
 920         $this->markTestSkipped(
 921               'TODO Reimplement test not using compareIndexInTables.'
 922             );
 923         $tablename1 = 'test17_' . mt_rand();
 924         $this->createTableParams($tablename1,
 925             array(
 926                 'foo' => array (
 927                     'name' => 'foo',
 928                     'type' => 'varchar',
 929                     'len' => '255',
 930                     ),
 931                 ),
 932             array(
 933                 array(
 934                     'name'   => 'idx_foo',
 935                     'type'   => 'index',
 936                     'fields' => array('foo'),
 937                     )
 938                 )
 939             );
 940         $tablename2 = 'test18_' . mt_rand();
 941         $this->createTableParams($tablename2,
 942             array(
 943                 'foo' => array (
 944                     'name' => 'foo',
 945                     'type' => 'varchar',
 946                     'len' => '255',
 947                     ),
 948                 ),
 949             array()
 950             );
 951
 952         // first test not executing the statement
 953         $this->_db->addIndexes(
 954             $tablename2,
 955             array(array(
 956                 'name'   => 'idx_foo',
 957                 'type'   => 'index',
 958                 'fields' => array('foo'),
 959                 )),
 960             false);
 961
 962         $res = $this->_db->compareIndexInTables(
 963             'idx_foo', $tablename1, $tablename2);
 964
 965         $this->assertEquals($res['msg'],'not_exists_table2');
 966
 967         // now, execute the statement
 968         $this->_db->addIndexes(
 969             $tablename2,
 970             array(array(
 971                 'name'   => 'idx_foo',
 972                 'type'   => 'index',
 973                 'fields' => array('foo'),
 974                 ))
 975             );
 976         $res = $this->_db->compareIndexInTables(
 977             'idx_foo', $tablename1, $tablename2);
 978
 979         $this->assertEquals($res['msg'],'match');
 980
 981         $this->dropTableName($tablename1);
 982         $this->dropTableName($tablename2);
 983     }
 984
 985     public function testDropIndexes()
 986     {
 987         //TODO Fix test with normal index inspection
 988         $this->markTestSkipped(
 989               'TODO Reimplement test not using compareIndexInTables.'
 990             );
 991
 992         $tablename1 = 'test19_' . mt_rand();
 993         $this->createTableParams($tablename1,
 994             array(
 995                 'foo' => array (
 996                     'name' => 'foo',
 997                     'type' => 'varchar',
 998                     'len' => '255',
 999                     ),
1000                 ),
1001             array(
1002                 array(
1003                     'name'   => 'idx_foo',
1004                     'type'   => 'index',
1005                     'fields' => array('foo'),
1006                     )
1007                 )
1008             );
1009         $tablename2 = 'test20_' . mt_rand();
1010         $this->createTableParams($tablename2,
1011             array(
1012                 'foo' => array (
1013                     'name' => 'foo',
1014                     'type' => 'varchar',
1015                     'len' => '255',
1016                     ),
1017                 ),
1018             array(
1019                 array(
1020                     'name'   => 'idx_foo',
1021                     'type'   => 'index',
1022                     'fields' => array('foo'),
1023                     )
1024                 )
1025             );
1026
1027         $res = $this->_db->compareIndexInTables(
1028             'idx_foo', $tablename1, $tablename2);
1029
1030         $this->assertEquals('match', $res['msg']);
1031
1032         // first test not executing the statement
1033         $this->_db->dropIndexes(
1034             $tablename2,
1035             array(array(
1036                 'name'   => 'idx_foo',
1037                 'type'   => 'index',
1038                 'fields' => array('foo'),
1039                 )),
1040             false);
1041
1042         $res = $this->_db->compareIndexInTables(
1043             'idx_foo', $tablename1, $tablename2);
1044
1045         $this->assertEquals('match', $res['msg']);
1046
1047         // now, execute the statement
1048         $sql = $this->_db->dropIndexes(
1049             $tablename2,
1050             array(array(
1051                 'name'   => 'idx_foo',
1052                 'type'   => 'index',
1053                 'fields' => array('foo'),
1054                 )),
1055             true
1056             );
1057
1058         $res = $this->_db->compareIndexInTables(
1059             'idx_foo', $tablename1, $tablename2);
1060
1061         $this->assertEquals('not_exists_table2', $res['msg']);
1062
1063         $this->dropTableName($tablename1);
1064         $this->dropTableName($tablename2);
1065     }
1066
1067     public function testModifyIndexes()
1068     {
1069         //TODO Fix test with normal index inspection
1070         $this->markTestSkipped(
1071               'TODO Reimplement test not using compareIndexInTables.'
1072             );
1073         $tablename1 = 'test21_' . mt_rand();
1074         $this->createTableParams($tablename1,
1075             array(
1076                 'foo' => array (
1077                     'name' => 'foo',
1078                     'type' => 'varchar',
1079                     'len' => '255',
1080                     ),
1081                 'foobar' => array (
1082                     'name' => 'foobar',
1083                     'type' => 'varchar',
1084                     'len' => '255',
1085                     ),
1086                 ),
1087             array(
1088                 array(
1089                     'name'   => 'idx_'. $tablename1,
1090                     'type'   => 'index',
1091                     'fields' => array('foo'),
1092                     )
1093                 )
1094             );
1095         $tablename2 = 'test22_' . mt_rand();
1096         $this->createTableParams($tablename2,
1097             array(
1098                 'foo' => array (
1099                     'name' => 'foo',
1100                     'type' => 'varchar',
1101                     'len' => '255',
1102                     ),
1103                 'foobar' => array (
1104                     'name' => 'foobar',
1105                     'type' => 'varchar',
1106                     'len' => '255',
1107                     ),
1108                 ),
1109             array(
1110                 array(
1111                     'name'   => 'idx_'. $tablename2,
1112                     'type'   => 'index',
1113                     'fields' => array('foobar'),
1114                     )
1115                 )
1116             );
1117
1118         $res = $this->_db->compareIndexInTables(
1119             'idx_foo', $tablename1, $tablename2);
1120
1121         $this->assertEquals($res['msg'],'no_match');
1122
1123         $this->_db->modifyIndexes(
1124             $tablename2,
1125             array(array(
1126                 'name'   => 'idx_foo',
1127                 'type'   => 'index',
1128                 'fields' => array('foo'),
1129                 )),
1130             false);
1131
1132         $res = $this->_db->compareIndexInTables(
1133             'idx_foo', $tablename1, $tablename2);
1134
1135         $this->assertEquals($res['msg'],'no_match');
1136
1137         $this->_db->modifyIndexes(
1138             $tablename2,
1139             array(array(
1140                 'name'   => 'idx_foo',
1141                 'type'   => 'index',
1142                 'fields' => array('foo'),
1143                 ))
1144             );
1145
1146         $res = $this->_db->compareIndexInTables(
1147             'idx_foo', $tablename1, $tablename2);
1148
1149         $this->assertEquals($res['msg'],'match');
1150
1151         $this->dropTableName($tablename1);
1152         $this->dropTableName($tablename2);
1153     }
1154
1155     public function testAddColumn()
1156     {
1157         $tablename1 = 'test23_' . mt_rand();
1158         $this->createTableParams($tablename1,
1159             array(
1160                 'foo' => array (
1161                     'name' => 'foo',
1162                     'type' => 'varchar',
1163                     'len' => '255',
1164                     ),
1165                 'foobar' => array (
1166                     'name' => 'foobar',
1167                     'type' => 'varchar',
1168                     'len' => '255',
1169                     ),
1170                 ),
1171             array()
1172             );
1173         $tablename2 = 'test24_' . mt_rand();
1174         $this->createTableParams($tablename2,
1175             array(
1176                 'foo' => array (
1177                     'name' => 'foo',
1178                     'type' => 'varchar',
1179                     'len' => '255',
1180                     ),
1181                 ),
1182             array()
1183             );
1184
1185         $res = $this->_db->compareFieldInTables(
1186             'foobar', $tablename1, $tablename2);
1187
1188         $this->assertEquals($res['msg'],'not_exists_table2');
1189
1190         $this->_db->addColumn(
1191             $tablename2,
1192             array(
1193                 'foobar' => array (
1194                     'name' => 'foobar',
1195                     'type' => 'varchar',
1196                     'len' => '255',
1197                     )
1198                 )
1199             );
1200
1201         $res = $this->_db->compareFieldInTables(
1202             'foobar', $tablename1, $tablename2);
1203
1204         $this->assertEquals($res['msg'],'match');
1205
1206         $this->dropTableName($tablename1);
1207         $this->dropTableName($tablename2);
1208     }
1209
1210     public function alterColumnDataProvider()
1211     {
1212         return array(
1213             array(
1214                  1,
1215                 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255', 'required' => true, 'default' => 'sugar'),
1216                 'temp' => array ('name' => 'foobar', 'type' => 'int')                           // Check if type conversion works
1217             ),
1218             array(
1219                 2,
1220                 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255', 'default' => 'kilroy'),
1221                 'temp' => array ('name' => 'foobar', 'type' => 'double', 'default' => '99999')  // Check if default gets replaced
1222             ),
1223             array(
1224                 3,
1225                 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255'),
1226                 'temp' => array ('name' => 'foobar', 'type' => 'double', 'default' => '99999')  // Check if default gets dropped
1227             ),
1228             array(
1229                 4,
1230                 'target' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '255', 'required' => true, 'default' => 'sweet'),
1231                 'temp' => array ('name' => 'foobar', 'type' => 'varchar', 'len' => '1500',)      // Check varchar shortening
1232             ),
1233             array(
1234                 5,
1235                 'target' => array ('name' => 'foobar', 'type' => 'longtext', 'required' => true),
1236                 'temp' => array ('name' => 'foobar', 'type' => 'text', 'default' => 'dextrose') // Check clob(65k) to clob(2M or so) conversion
1237             ),
1238             array(
1239                 6,
1240                 'target' => array ('name' => 'foobar', 'type' => 'double', 'required' => true),
1241                 'temp' => array ('name' => 'foobar', 'type' => 'int', 'default' => 0)           // Check int to double change
1242             ),
1243         );
1244     }
1245
1246
1247
1248     /**
1249      * @dataProvider alterColumnDataProvider
1250      * @param  $i
1251      * @param  $target
1252      * @param  $temp
1253      * @return void
1254      */
1255     public function testAlterColumn($i, $target, $temp)
1256     {
1257         if($this->_db->dbType == "oci8" && ($i == 4 || $i == 6)) {
1258             $this->markTestSkipped("Cannot reliably shrink columns in Oracle");
1259         }
1260
1261         $foo_col = array ('name' => 'foo', 'type' => 'varchar', 'len' => '255'); // Common column between tables
1262
1263         $tablebase = 'testac_'. mt_rand() . '_';
1264
1265         $t1 = $tablebase . $i .'A';
1266         $t2 = $tablebase . $i .'B';
1267         $this->createTableParams(  $t1,
1268                                         array('foo' => $foo_col, 'foobar' => $target),
1269                                         array());
1270         $this->createTableParams(  $t2,
1271                                         array('foo' => $foo_col, 'foobar' => $temp),
1272                                         array());
1273
1274         $res = $this->_db->compareFieldInTables('foobar', $t1, $t2);
1275
1276         $this->assertEquals('no_match', $res['msg'],
1277                             "testAlterColumn table columns match while they shouldn't for table $t1 and $t2: "
1278                             . print_r($res,true) );
1279
1280         $this->_db->alterColumn($t2, array('foobar' => $target));
1281
1282         $res = $this->_db->compareFieldInTables('foobar', $t1, $t2);
1283
1284         $this->assertEquals('match', $res['msg'],
1285                             "testAlterColumn table columns don't match while they should for table $t1 and $t2: "
1286                             . print_r($res,true) );
1287
1288         $this->dropTableName($t1);
1289         $this->dropTableName($t2);
1290     }
1291
1292     public function testDropTable()
1293     {
1294         // TODO: Write this test
1295     }
1296
1297     public function testDropTableName()
1298     {
1299         $tablename = 'test' . mt_rand();
1300         $this->createTableParams($tablename,
1301             array(
1302                 'foo' => array (
1303                     'name' => 'foo',
1304                     'type' => 'varchar',
1305                     'len' => '255',
1306                     ),
1307                 ),
1308             array()
1309             );
1310         $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1311
1312         $this->dropTableName($tablename);
1313
1314         $this->assertFalse(in_array($tablename,$this->_db->getTablesArray()));
1315     }
1316
1317     public function testDeleteColumn()
1318     {
1319         // TODO: Write this test
1320     }
1321
1322     public function testDisconnectAll()
1323     {
1324         DBManagerFactory::disconnectAll();
1325         $this->assertTrue($this->_db->checkError());
1326         $this->_db = DBManagerFactory::getInstance();
1327     }
1328
1329     public function testQuery()
1330     {
1331         $beanIds = $this->_createRecords(5);
1332
1333         $result = $this->_db->query("SELECT id From contacts where last_name = 'foobar'");
1334         if ( $this->_db instanceOf MysqliManager )
1335             $this->assertInstanceOf('Mysqli_result',$result);
1336         else
1337             $this->assertTrue(is_resource($result));
1338
1339         while ( $row = $this->_db->fetchByAssoc($result) )
1340             $this->assertTrue(in_array($row['id'],$beanIds),"Id not found '{$row['id']}'");
1341
1342         $this->_removeRecords($beanIds);
1343     }
1344
1345     public function disabledLimitQuery()
1346     {
1347         $beanIds = $this->_createRecords(5);
1348         $_REQUEST['module'] = 'contacts';
1349         $result = $this->_db->limitQuery("SELECT id From contacts where last_name = 'foobar'",1,3);
1350         if ( $this->_db instanceOf MysqliManager )
1351             $this->assertInstanceOf('Mysqli_result',$result);
1352         else
1353             $this->assertTrue(is_resource($result));
1354
1355         while ( $row = $this->_db->fetchByAssoc($result) ) {
1356             if ( $row['id'][0] > 3 || $row['id'][0] < 0 )
1357                 $this->assertFalse(in_array($row['id'],$beanIds),"Found {$row['id']} in error");
1358             else
1359                 $this->assertTrue(in_array($row['id'],$beanIds),"Didn't find {$row['id']}");
1360         }
1361         unset($_REQUEST['module']);
1362         $this->_removeRecords($beanIds);
1363     }
1364
1365     public function testGetOne()
1366     {
1367         $beanIds = $this->_createRecords(1);
1368
1369         $id = $this->_db->getOne("SELECT id From contacts where last_name = 'foobar'");
1370         $this->assertEquals($id,$beanIds[0]);
1371
1372         // bug 38994
1373         if ( $this->_db instanceOf MysqlManager ) {
1374             $id = $this->_db->getOne($this->_db->limitQuerySql("SELECT id From contacts where last_name = 'foobar'", 0, 1));
1375             $this->assertEquals($id,$beanIds[0]);
1376         }
1377
1378         $this->_removeRecords($beanIds);
1379     }
1380
1381     public function testGetFieldsArray()
1382     {
1383         $beanIds = $this->_createRecords(1);
1384
1385         $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1386         $fields = $this->_db->getFieldsArray($result,true);
1387
1388         $this->assertEquals(array("id"),$fields);
1389
1390         $this->_removeRecords($beanIds);
1391     }
1392
1393     public function testGetAffectedRowCount()
1394     {
1395         if(!$this->_db->supports("affected_rows")) {
1396             $this->markTestSkipped('Skipping, backend doesn\'t support affected rows');
1397         }
1398
1399         $beanIds = $this->_createRecords(1);
1400         $result = $this->_db->query("DELETE From contacts where id = '{$beanIds[0]}'");
1401         $this->assertEquals(1, $this->_db->getAffectedRowCount($result));
1402     }
1403
1404     public function testFetchByAssoc()
1405     {
1406         $beanIds = $this->_createRecords(1);
1407
1408         $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1409
1410         $row = $this->_db->fetchByAssoc($result);
1411
1412         $this->assertTrue(is_array($row));
1413         $this->assertEquals($row['id'],$beanIds[0]);
1414
1415         $this->_removeRecords($beanIds);
1416     }
1417
1418     public function testConnect()
1419     {
1420         // TODO: Write this test
1421     }
1422
1423     public function testDisconnect()
1424     {
1425         $this->_db->disconnect();
1426         $this->assertTrue($this->_db->checkError());
1427         $this->_db = DBManagerFactory::getInstance();
1428     }
1429
1430     public function testGetTablesArray()
1431     {
1432         $tablename = 'test' . mt_rand();
1433         $this->createTableParams($tablename,
1434             array(
1435                 'foo' => array (
1436                     'name' => 'foo',
1437                     'type' => 'varchar',
1438                     'len' => '255',
1439                     ),
1440                 ),
1441             array()
1442             );
1443
1444         $this->assertTrue($this->_db->tableExists($tablename));
1445
1446         $this->dropTableName($tablename);
1447     }
1448
1449     public function testVersion()
1450     {
1451         $ver = $this->_db->version();
1452
1453         $this->assertTrue(is_string($ver));
1454     }
1455
1456     public function testTableExists()
1457     {
1458         $tablename = 'test' . mt_rand();
1459         $this->createTableParams($tablename,
1460             array(
1461                 'foo' => array (
1462                     'name' => 'foo',
1463                     'type' => 'varchar',
1464                     'len' => '255',
1465                     ),
1466                 ),
1467             array()
1468             );
1469
1470         $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1471
1472         $this->dropTableName($tablename);
1473     }
1474
1475     public function providerCompareVardefs()
1476     {
1477         $returnArray = array(
1478             array(
1479                 array(
1480                     'name' => 'foo',
1481                     'type' => 'varchar',
1482                     'len' => '255',
1483                     ),
1484                 array(
1485                     'name' => 'foo',
1486                     'type' => 'varchar',
1487                     'len' => '255',
1488                     ),
1489                 true),
1490             array(
1491                 array(
1492                     'name' => 'foo',
1493                     'type' => 'char',
1494                     'len' => '255',
1495                     ),
1496                 array(
1497                     'name' => 'foo',
1498                     'type' => 'varchar',
1499                     'len' => '255',
1500                     ),
1501                 false),
1502             array(
1503                 array(
1504                     'name' => 'foo',
1505                     'type' => 'char',
1506                     'len' => '255',
1507                     ),
1508                 array(
1509                     'name' => 'foo',
1510                     'len' => '255',
1511                 ),
1512                 false),
1513             array(
1514                 array(
1515                     'name' => 'foo',
1516                     'len' => '255',
1517                     ),
1518                 array(
1519                     'name' => 'foo',
1520                     'type' => 'varchar',
1521                     'len' => '255',
1522                     ),
1523                 true),
1524             array(
1525                 array(
1526                     'name' => 'foo',
1527                     'type' => 'varchar',
1528                     'len' => '255',
1529                     ),
1530                 array(
1531                     'name' => 'FOO',
1532                     'type' => 'varchar',
1533                     'len' => '255',
1534                     ),
1535                 true),
1536             );
1537
1538         return $returnArray;
1539     }
1540
1541     /**
1542      * @dataProvider providerCompareVarDefs
1543      */
1544     public function testCompareVarDefs($fieldDef1,$fieldDef2,$expectedResult)
1545     {
1546         if ( $expectedResult ) {
1547             $this->assertTrue($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1548         }
1549         else {
1550             $this->assertFalse($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1551         }
1552     }
1553
1554     /**
1555      * @ticket 34892
1556      */
1557     public function test_Bug34892_MssqlNotClearingErrorResults()
1558     {
1559             // execute a bad query
1560             $this->_db->query("select dsdsdsdsdsdsdsdsdsd", false, "test_Bug34892_MssqlNotClearingErrorResults", true);
1561             // assert it found an error
1562             $this->assertNotEmpty($this->_db->lastError(), "lastError should return true as a result of the previous illegal query");
1563             // now, execute a good query
1564             $this->_db->query("select * from config");
1565             // and make no error messages are asserted
1566             $this->assertEmpty($this->_db->lastError(), "lastError should have cleared the previous error and return false of the last legal query");
1567     }
1568
1569     public function vardefProvider()
1570     {
1571         $GLOBALS['log']->info('DBManagerTest.vardefProvider: _db = ' . print_r($this->_db));
1572         $this->setUp(); // Just in case the DB driver is not created yet.
1573         $emptydate = $this->_db->emptyValue("date");
1574         $emptytime = $this->_db->emptyValue("time");
1575         $emptydatetime = $this->_db->emptyValue("datetime");
1576
1577         return array(
1578             array("testid", array (
1579                   'id' =>
1580                   array (
1581                     'name' => 'id',
1582                     'type' => 'varchar',
1583                     'required'=>true,
1584                   ),
1585                   ),
1586                   array("id" => "test123"),
1587                   array("id" => "'test123'")
1588             ),
1589             array("testtext", array (
1590                   'text1' =>
1591                   array (
1592                     'name' => 'text1',
1593                     'type' => 'varchar',
1594                     'required'=>true,
1595                   ),
1596                   'text2' =>
1597                   array (
1598                     'name' => 'text2',
1599                     'type' => 'varchar',
1600                   ),
1601                   ),
1602                   array(),
1603                   array("text1" => "''"),
1604                   array()
1605             ),
1606             array("testtext2", array (
1607                   'text1' =>
1608                   array (
1609                     'name' => 'text1',
1610                     'type' => 'varchar',
1611                     'required'=>true,
1612                   ),
1613                   'text2' =>
1614                   array (
1615                     'name' => 'text2',
1616                     'type' => 'varchar',
1617                   ),
1618                   ),
1619                   array('text1' => 'foo', 'text2' => 'bar'),
1620                   array("text1" => "'foo'", 'text2' => "'bar'"),
1621             ),
1622             array("testreq", array (
1623                   'id' =>
1624                       array (
1625                         'name' => 'id',
1626                         'type' => 'varchar',
1627                         'required'=>true,
1628                       ),
1629                   'intval' =>
1630                       array (
1631                         'name' => 'intval',
1632                         'type' => 'int',
1633                         'required'=>true,
1634                       ),
1635                   'floatval' =>
1636                       array (
1637                         'name' => 'floatval',
1638                         'type' => 'decimal',
1639                         'required'=>true,
1640                       ),
1641                   'money' =>
1642                       array (
1643                         'name' => 'money',
1644                         'type' => 'currency',
1645                         'required'=>true,
1646                       ),
1647                   'test_dtm' =>
1648                       array (
1649                         'name' => 'test_dtm',
1650                         'type' => 'datetime',
1651                         'required'=>true,
1652                       ),
1653                   'test_dtm2' =>
1654                       array (
1655                         'name' => 'test_dtm2',
1656                         'type' => 'datetimecombo',
1657                         'required'=>true,
1658                       ),
1659                   'test_dt' =>
1660                       array (
1661                         'name' => 'test_dt',
1662                         'type' => 'date',
1663                         'required'=>true,
1664                       ),
1665                   'test_tm' =>
1666                       array (
1667                         'name' => 'test_tm',
1668                         'type' => 'time',
1669                         'required'=>true,
1670                       ),
1671                   ),
1672                   array("id" => "test123", 'intval' => 42, 'floatval' => 42.24,
1673                                 'money' => 56.78, 'test_dtm' => '2002-01-02 12:34:56', 'test_dtm2' => '2011-10-08 01:02:03',
1674                         'test_dt' => '1998-10-04', 'test_tm' => '03:04:05'
1675                   ),
1676                   array("id" => "'test123'", 'intval' => 42, 'floatval' => 42.24,
1677                                 'money' => 56.78, 'test_dtm' => $this->_db->convert('\'2002-01-02 12:34:56\'', "datetime"), 'test_dtm2' => $this->_db->convert('\'2011-10-08 01:02:03\'', 'datetime'),
1678                         'test_dt' => $this->_db->convert('\'1998-10-04\'', 'date'), 'test_tm' => $this->_db->convert('\'03:04:05\'', 'time')
1679                   ),
1680             ),
1681             array("testreqnull", array (
1682                   'id' =>
1683                       array (
1684                         'name' => 'id',
1685                         'type' => 'varchar',
1686                         'required'=>true,
1687                       ),
1688                   'intval' =>
1689                       array (
1690                         'name' => 'intval',
1691                         'type' => 'int',
1692                         'required'=>true,
1693                       ),
1694                   'floatval' =>
1695                       array (
1696                         'name' => 'floatval',
1697                         'type' => 'decimal',
1698                         'required'=>true,
1699                       ),
1700                   'money' =>
1701                       array (
1702                         'name' => 'money',
1703                         'type' => 'currency',
1704                         'required'=>true,
1705                       ),
1706                   'test_dtm' =>
1707                       array (
1708                         'name' => 'test_dtm',
1709                         'type' => 'datetime',
1710                         'required'=>true,
1711                       ),
1712                   'test_dtm2' =>
1713                       array (
1714                         'name' => 'test_dtm2',
1715                         'type' => 'datetimecombo',
1716                         'required'=>true,
1717                       ),
1718                   'test_dt' =>
1719                       array (
1720                         'name' => 'test_dt',
1721                         'type' => 'date',
1722                         'required'=>true,
1723                       ),
1724                   'test_tm' =>
1725                       array (
1726                         'name' => 'test_tm',
1727                         'type' => 'time',
1728                         'required'=>true,
1729                       ),
1730                   ),
1731                   array(),
1732                   array("id" => "''", 'intval' => 0, 'floatval' => 0,
1733                                 'money' => 0, 'test_dtm' => "$emptydatetime", 'test_dtm2' => "$emptydatetime",
1734                         'test_dt' => "$emptydate", 'test_tm' => "$emptytime"
1735                   ),
1736                   array(),
1737             ),
1738             array("testnull", array (
1739                   'id' =>
1740                       array (
1741                         'name' => 'id',
1742                         'type' => 'varchar',
1743                       ),
1744                   'intval' =>
1745                       array (
1746                         'name' => 'intval',
1747                         'type' => 'int',
1748                       ),
1749                   'floatval' =>
1750                       array (
1751                         'name' => 'floatval',
1752                         'type' => 'decimal',
1753                       ),
1754                   'money' =>
1755                       array (
1756                         'name' => 'money',
1757                         'type' => 'currency',
1758                       ),
1759                   'test_dtm' =>
1760                       array (
1761                         'name' => 'test_dtm',
1762                         'type' => 'datetime',
1763                       ),
1764                   'test_dtm2' =>
1765                       array (
1766                         'name' => 'test_dtm2',
1767                         'type' => 'datetimecombo',
1768                       ),
1769                   'test_dt' =>
1770                       array (
1771                         'name' => 'test_dt',
1772                         'type' => 'date',
1773                       ),
1774                   'test_tm' =>
1775                       array (
1776                         'name' => 'test_tm',
1777                         'type' => 'time',
1778                       ),
1779                   ),
1780                   array("id" => 123),
1781                   array("id" => "'123'"),
1782                   array(),
1783             ),
1784             array("testempty", array (
1785                   'id' =>
1786                       array (
1787                         'name' => 'id',
1788                         'type' => 'varchar',
1789                       ),
1790                   'intval' =>
1791                       array (
1792                         'name' => 'intval',
1793                         'type' => 'int',
1794                       ),
1795                   'floatval' =>
1796                       array (
1797                         'name' => 'floatval',
1798                         'type' => 'decimal',
1799                       ),
1800                   'money' =>
1801                       array (
1802                         'name' => 'money',
1803                         'type' => 'currency',
1804                       ),
1805                   'test_dtm' =>
1806                       array (
1807                         'name' => 'test_dtm',
1808                         'type' => 'datetime',
1809                       ),
1810                   'test_dtm2' =>
1811                       array (
1812                         'name' => 'test_dtm2',
1813                         'type' => 'datetimecombo',
1814                       ),
1815                   'test_dt' =>
1816                       array (
1817                         'name' => 'test_dt',
1818                         'type' => 'date',
1819                       ),
1820                   'test_tm' =>
1821                       array (
1822                         'name' => 'test_tm',
1823                         'type' => 'time',
1824                       ),
1825                    'text_txt' =>
1826                       array (
1827                         'name' => 'test_txt',
1828                         'type' => 'varchar',
1829                       ),
1830                   ),
1831                   array("id" => "", 'intval' => '', 'floatval' => '',
1832                                 'money' => '', 'test_dtm' => '', 'test_dtm2' => '',
1833                         'test_dt' => '', 'test_tm' => '', 'text_txt' => null
1834                   ),
1835                   array("id" => "''", 'intval' => 0, 'floatval' => 0,
1836                                 'money' => 0, 'test_dtm' => "NULL", 'test_dtm2' => "NULL",
1837                         'test_dt' => "NULL", 'test_tm' => 'NULL'
1838                   ),
1839                   array('intval' => 'NULL', 'floatval' => 'NULL',
1840                                 'money' => 'NULL', 'test_dtm' => 'NULL', 'test_dtm2' => 'NULL',
1841                         'test_dt' => 'NULL', 'test_tm' => 'NULL'
1842                   ),
1843             ),
1844         );
1845     }
1846
1847    /**
1848     * Test InserSQL functions
1849     * @dataProvider vardefProvider
1850     * @param string $name
1851     * @param array $defs
1852     * @param array $data
1853     * @param array $result
1854     */
1855     public function testInsertSQL($name, $defs, $data, $result)
1856     {
1857         $vardefs = array(
1858                         'table' => $name,
1859             'fields' => $defs,
1860         );
1861         $obj = new TestSugarBean($name, $vardefs);
1862         // regular fields
1863         foreach($data as $k => $v) {
1864             $obj->$k = $v;
1865         }
1866         $sql = $this->_db->insertSQL($obj);
1867         $names = join('\s*,\s*',array_map('preg_quote', array_keys($result)));
1868         $values = join('\s*,\s*',array_map('preg_quote', array_values($result)));
1869         $this->assertRegExp("/INSERT INTO $name\s+\(\s*$names\s*\)\s+VALUES\s+\(\s*$values\s*\)/is", $sql, "Bad sql: $sql");
1870     }
1871
1872    /**
1873     * Test UpdateSQL functions
1874     * @dataProvider vardefProvider
1875     * @param string $name
1876     * @param array $defs
1877     * @param array $data
1878     * @param array $_
1879     * @param array $result
1880     */
1881     public function testUpdateSQL($name, $defs, $data, $_, $result = null)
1882     {
1883         $name = "update$name";
1884         $vardefs = array(
1885                         'table' => $name,
1886             'fields' => $defs,
1887         );
1888         // ensure it has an ID
1889         $vardefs['fields']['id'] = array (
1890                     'name' => 'id',
1891                     'type' => 'id',
1892                     'required'=>true,
1893                   );
1894         $vardefs['fields']['deleted'] = array (
1895                     'name' => 'deleted',
1896                     'type' => 'bool',
1897                   );
1898
1899         $obj = new TestSugarBean($name, $vardefs);
1900         // regular fields
1901         foreach($defs as $k => $v) {
1902             if(isset($data[$k])) {
1903                 $obj->$k = $data[$k];
1904             } else {
1905                 $obj->$k = null;
1906             }
1907         }
1908         // set fixed ID
1909         $obj->id = 'test_ID';
1910         $sql = $this->_db->updateSQL($obj);
1911         if(is_null($result)) {
1912             $result = $_;
1913         }
1914         $names_i = array();
1915         foreach($result as $k => $v) {
1916             if($k == "id" || $k == 'deleted') continue;
1917             $names_i[] = preg_quote("$k=$v");
1918         }
1919         if(empty($names_i)) {
1920             $this->assertEquals("", $sql, "Bad sql: $sql");
1921             return;
1922         }
1923         $names = join('\s*,\s*',$names_i);
1924         $this->assertRegExp("/UPDATE $name\s+SET\s+$names\s+WHERE\s+$name.id\s*=\s*'test_ID' AND deleted=0/is", $sql, "Bad sql: $sql");
1925     }
1926
1927      /**
1928     * Test UpdateSQL functions
1929     * @dataProvider vardefProvider
1930     * @param string $name
1931     * @param array $defs
1932     * @param array $data
1933     * @param array $_
1934     * @param array $result
1935     */
1936     public function testUpdateSQLNoDeleted($name, $defs, $data, $_, $result = null)
1937     {
1938         $name = "updatenodel$name";
1939         $vardefs = array(
1940                         'table' => $name,
1941             'fields' => $defs,
1942         );
1943         // ensure it has an ID
1944         $vardefs['fields']['id'] = array (
1945                     'name' => 'id',
1946                     'type' => 'id',
1947                     'required'=>true,
1948                   );
1949         unset($vardefs['fields']['deleted']);
1950
1951         $obj = new TestSugarBean($name, $vardefs);
1952         // regular fields
1953         foreach($defs as $k => $v) {
1954             if(isset($data[$k])) {
1955                 $obj->$k = $data[$k];
1956             } else {
1957                 $obj->$k = null;
1958             }
1959         }
1960         // set fixed ID
1961         $obj->id = 'test_ID';
1962         $sql = $this->_db->updateSQL($obj);
1963         if(is_null($result)) {
1964             $result = $_;
1965         }
1966         $names_i = array();
1967         foreach($result as $k => $v) {
1968             if($k == "id" || $k == 'deleted') continue;
1969             $names_i[] = preg_quote("$k=$v");
1970         }
1971         if(empty($names_i)) {
1972             $this->assertEquals("", $sql, "Bad sql: $sql");
1973             return;
1974         }
1975         $names = join('\s*,\s*',$names_i);
1976         $this->assertRegExp("/UPDATE $name\s+SET\s+$names\s+WHERE\s+$name.id\s*=\s*'test_ID'/is", $sql, "Bad sql: $sql");
1977         $this->assertNotContains(" AND deleted=0", $sql, "Bad sql: $sql");
1978     }
1979
1980     /**
1981      * Test the canInstall
1982      * @return void
1983      */
1984     public function testCanInstall() {
1985         $DBManagerClass = get_class($this->_db);
1986         if(!method_exists($this->_db, 'version') || !method_exists($this->_db, 'canInstall'))
1987             $this->markTestSkipped(
1988               "Class {$DBManagerClass} doesn't implement canInstall or version methods");
1989
1990         $method = new ReflectionMethod($DBManagerClass, 'canInstall');
1991         if($method->class == 'DBManager')
1992             $this->markTestSkipped(
1993               "Class {$DBManagerClass} or one of it's ancestors doesn't override DBManager's canInstall");
1994
1995         // First assuming that we are only running unit tests against a supported database :)
1996         $this->assertTrue($this->_db->canInstall(), "Apparently we are not running this unit test against a supported database!!!");
1997
1998         $DBstub = $this->getMock($DBManagerClass, array('version'));
1999         $DBstub->expects($this->any())
2000                ->method('version')
2001                ->will($this->returnValue('0.0.0')); // Expect that any supported version is higher than 0.0.0
2002
2003         $this->assertTrue(is_array($DBstub->canInstall()), "Apparently we do support version 0.0.0 in " . $DBManagerClass);
2004     }
2005
2006     public function providerValidateQuery()
2007     {
2008         return array(
2009             array(true, 'SELECT * FROM accounts'),
2010             array(false, 'SELECT * FROM blablabla123'),
2011         );
2012     }
2013
2014     /**
2015      * Test query validation
2016      * @dataProvider providerValidateQuery
2017      * @param $good
2018      * @param $sql
2019      * @return void
2020      */
2021     public function testValidateQuery($good, $sql)
2022     {
2023         $check = $this->_db->validateQuery($sql);
2024         $this->assertEquals($good, $check);
2025     }
2026
2027 }