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         $foo_col = array ('name' => 'foo', 'type' => 'varchar', 'len' => '255'); // Common column between tables
1258
1259         $tablebase = 'testac_'. mt_rand() . '_';
1260
1261         $t1 = $tablebase . $i .'A';
1262         $t2 = $tablebase . $i .'B';
1263         $this->createTableParams(  $t1,
1264                                         array('foo' => $foo_col, 'foobar' => $target),
1265                                         array());
1266         $this->createTableParams(  $t2,
1267                                         array('foo' => $foo_col, 'foobar' => $temp),
1268                                         array());
1269
1270         $res = $this->_db->compareFieldInTables('foobar', $t1, $t2);
1271
1272         $this->assertEquals('no_match', $res['msg'],
1273                             "testAlterColumn table columns match while they shouldn't for table $t1 and $t2: "
1274                             . print_r($res,true) );
1275
1276         $this->_db->alterColumn($t2, array('foobar' => $target));
1277
1278         $res = $this->_db->compareFieldInTables('foobar', $t1, $t2);
1279
1280         $this->assertEquals('match', $res['msg'],
1281                             "testAlterColumn table columns don't match while they should for table $t1 and $t2: "
1282                             . print_r($res,true) );
1283
1284         $this->dropTableName($t1);
1285         $this->dropTableName($t2);
1286     }
1287
1288     public function testDropTable()
1289     {
1290         // TODO: Write this test
1291     }
1292
1293     public function testDropTableName()
1294     {
1295         $tablename = 'test' . mt_rand();
1296         $this->createTableParams($tablename,
1297             array(
1298                 'foo' => array (
1299                     'name' => 'foo',
1300                     'type' => 'varchar',
1301                     'len' => '255',
1302                     ),
1303                 ),
1304             array()
1305             );
1306         $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1307
1308         $this->dropTableName($tablename);
1309
1310         $this->assertFalse(in_array($tablename,$this->_db->getTablesArray()));
1311     }
1312
1313     public function testDeleteColumn()
1314     {
1315         // TODO: Write this test
1316     }
1317
1318     public function testDisconnectAll()
1319     {
1320         DBManagerFactory::disconnectAll();
1321         $this->assertTrue($this->_db->checkError());
1322         $this->_db = DBManagerFactory::getInstance();
1323     }
1324
1325     public function testQuery()
1326     {
1327         $beanIds = $this->_createRecords(5);
1328
1329         $result = $this->_db->query("SELECT id From contacts where last_name = 'foobar'");
1330         if ( $this->_db instanceOf MysqliManager )
1331             $this->assertInstanceOf('Mysqli_result',$result);
1332         else
1333             $this->assertTrue(is_resource($result));
1334
1335         while ( $row = $this->_db->fetchByAssoc($result) )
1336             $this->assertTrue(in_array($row['id'],$beanIds),"Id not found '{$row['id']}'");
1337
1338         $this->_removeRecords($beanIds);
1339     }
1340
1341     public function disabledLimitQuery()
1342     {
1343         $beanIds = $this->_createRecords(5);
1344         $_REQUEST['module'] = 'contacts';
1345         $result = $this->_db->limitQuery("SELECT id From contacts where last_name = 'foobar'",1,3);
1346         if ( $this->_db instanceOf MysqliManager )
1347             $this->assertInstanceOf('Mysqli_result',$result);
1348         else
1349             $this->assertTrue(is_resource($result));
1350
1351         while ( $row = $this->_db->fetchByAssoc($result) ) {
1352             if ( $row['id'][0] > 3 || $row['id'][0] < 0 )
1353                 $this->assertFalse(in_array($row['id'],$beanIds),"Found {$row['id']} in error");
1354             else
1355                 $this->assertTrue(in_array($row['id'],$beanIds),"Didn't find {$row['id']}");
1356         }
1357         unset($_REQUEST['module']);
1358         $this->_removeRecords($beanIds);
1359     }
1360
1361     public function testGetOne()
1362     {
1363         $beanIds = $this->_createRecords(1);
1364
1365         $id = $this->_db->getOne("SELECT id From contacts where last_name = 'foobar'");
1366         $this->assertEquals($id,$beanIds[0]);
1367
1368         // bug 38994
1369         if($this->_db instanceof MysqlManager) {
1370             $id = $this->_db->getOne("SELECT id From contacts where last_name = 'foobar' LIMIT 0,1");
1371             $this->assertEquals($id,$beanIds[0]);
1372         }
1373
1374         $this->_removeRecords($beanIds);
1375     }
1376
1377     public function testGetFieldsArray()
1378     {
1379         $beanIds = $this->_createRecords(1);
1380
1381         $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1382         $fields = $this->_db->getFieldsArray($result,true);
1383
1384         $this->assertEquals(array("id"),$fields);
1385
1386         $this->_removeRecords($beanIds);
1387     }
1388
1389     public function testGetAffectedRowCount()
1390     {
1391         if(!$this->_db->supports("affected_rows")) {
1392             $this->markTestSkipped('Skipping, backend doesn\'t support affected rows');
1393         }
1394
1395         $beanIds = $this->_createRecords(1);
1396         $result = $this->_db->query("DELETE From contacts where id = '{$beanIds[0]}'");
1397         $this->assertEquals(1, $this->_db->getAffectedRowCount($result));
1398     }
1399
1400     public function testFetchByAssoc()
1401     {
1402         $beanIds = $this->_createRecords(1);
1403
1404         $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1405
1406         $row = $this->_db->fetchByAssoc($result);
1407
1408         $this->assertTrue(is_array($row));
1409         $this->assertEquals($row['id'],$beanIds[0]);
1410
1411         $this->_removeRecords($beanIds);
1412     }
1413
1414     public function testConnect()
1415     {
1416         // TODO: Write this test
1417     }
1418
1419     public function testDisconnect()
1420     {
1421         $this->_db->disconnect();
1422         $this->assertTrue($this->_db->checkError());
1423         $this->_db = DBManagerFactory::getInstance();
1424     }
1425
1426     public function testGetTablesArray()
1427     {
1428         $tablename = 'test' . mt_rand();
1429         $this->createTableParams($tablename,
1430             array(
1431                 'foo' => array (
1432                     'name' => 'foo',
1433                     'type' => 'varchar',
1434                     'len' => '255',
1435                     ),
1436                 ),
1437             array()
1438             );
1439
1440         $this->assertTrue($this->_db->tableExists($tablename));
1441
1442         $this->dropTableName($tablename);
1443     }
1444
1445     public function testVersion()
1446     {
1447         $ver = $this->_db->version();
1448
1449         $this->assertTrue(is_string($ver));
1450     }
1451
1452     public function testTableExists()
1453     {
1454         $tablename = 'test' . mt_rand();
1455         $this->createTableParams($tablename,
1456             array(
1457                 'foo' => array (
1458                     'name' => 'foo',
1459                     'type' => 'varchar',
1460                     'len' => '255',
1461                     ),
1462                 ),
1463             array()
1464             );
1465
1466         $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1467
1468         $this->dropTableName($tablename);
1469     }
1470
1471     public function providerCompareVardefs()
1472     {
1473         $returnArray = array(
1474             array(
1475                 array(
1476                     'name' => 'foo',
1477                     'type' => 'varchar',
1478                     'len' => '255',
1479                     ),
1480                 array(
1481                     'name' => 'foo',
1482                     'type' => 'varchar',
1483                     'len' => '255',
1484                     ),
1485                 true),
1486             array(
1487                 array(
1488                     'name' => 'foo',
1489                     'type' => 'char',
1490                     'len' => '255',
1491                     ),
1492                 array(
1493                     'name' => 'foo',
1494                     'type' => 'varchar',
1495                     'len' => '255',
1496                     ),
1497                 false),
1498             array(
1499                 array(
1500                     'name' => 'foo',
1501                     'type' => 'char',
1502                     'len' => '255',
1503                     ),
1504                 array(
1505                     'name' => 'foo',
1506                     'len' => '255',
1507                 ),
1508                 false),
1509             array(
1510                 array(
1511                     'name' => 'foo',
1512                     'len' => '255',
1513                     ),
1514                 array(
1515                     'name' => 'foo',
1516                     'type' => 'varchar',
1517                     'len' => '255',
1518                     ),
1519                 true),
1520             array(
1521                 array(
1522                     'name' => 'foo',
1523                     'type' => 'varchar',
1524                     'len' => '255',
1525                     ),
1526                 array(
1527                     'name' => 'FOO',
1528                     'type' => 'varchar',
1529                     'len' => '255',
1530                     ),
1531                 true),
1532             );
1533
1534         return $returnArray;
1535     }
1536
1537     /**
1538      * @dataProvider providerCompareVarDefs
1539      */
1540     public function testCompareVarDefs($fieldDef1,$fieldDef2,$expectedResult)
1541     {
1542         if ( $expectedResult ) {
1543             $this->assertTrue($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1544         }
1545         else {
1546             $this->assertFalse($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1547         }
1548     }
1549
1550     /**
1551      * @ticket 34892
1552      */
1553     public function test_Bug34892_MssqlNotClearingErrorResults()
1554     {
1555             // execute a bad query
1556             $this->_db->query("select dsdsdsdsdsdsdsdsdsd", false, "test_Bug34892_MssqlNotClearingErrorResults", true);
1557             // assert it found an error
1558             $this->assertNotEmpty($this->_db->lastError(), "lastError should return true as a result of the previous illegal query");
1559             // now, execute a good query
1560             $this->_db->query("select * from config");
1561             // and make no error messages are asserted
1562             $this->assertEmpty($this->_db->lastError(), "lastError should have cleared the previous error and return false of the last legal query");
1563     }
1564
1565     public function vardefProvider()
1566     {
1567         $GLOBALS['log']->info('DBManagerTest.vardefProvider: _db = ' . print_r($this->_db));
1568         $this->setUp(); // Just in case the DB driver is not created yet.
1569         $emptydate = $this->_db->emptyValue("date");
1570         $emptytime = $this->_db->emptyValue("time");
1571         $emptydatetime = $this->_db->emptyValue("datetime");
1572
1573         return array(
1574             array("testid", array (
1575                   'id' =>
1576                   array (
1577                     'name' => 'id',
1578                     'type' => 'varchar',
1579                     'required'=>true,
1580                   ),
1581                   ),
1582                   array("id" => "test123"),
1583                   array("id" => "'test123'")
1584             ),
1585             array("testtext", array (
1586                   'text1' =>
1587                   array (
1588                     'name' => 'text1',
1589                     'type' => 'varchar',
1590                     'required'=>true,
1591                   ),
1592                   'text2' =>
1593                   array (
1594                     'name' => 'text2',
1595                     'type' => 'varchar',
1596                   ),
1597                   ),
1598                   array(),
1599                   array("text1" => "''"),
1600                   array()
1601             ),
1602             array("testtext2", array (
1603                   'text1' =>
1604                   array (
1605                     'name' => 'text1',
1606                     'type' => 'varchar',
1607                     'required'=>true,
1608                   ),
1609                   'text2' =>
1610                   array (
1611                     'name' => 'text2',
1612                     'type' => 'varchar',
1613                   ),
1614                   ),
1615                   array('text1' => 'foo', 'text2' => 'bar'),
1616                   array("text1" => "'foo'", 'text2' => "'bar'"),
1617             ),
1618             array("testreq", array (
1619                   'id' =>
1620                       array (
1621                         'name' => 'id',
1622                         'type' => 'varchar',
1623                         'required'=>true,
1624                       ),
1625                   'intval' =>
1626                       array (
1627                         'name' => 'intval',
1628                         'type' => 'int',
1629                         'required'=>true,
1630                       ),
1631                   'floatval' =>
1632                       array (
1633                         'name' => 'floatval',
1634                         'type' => 'decimal',
1635                         'required'=>true,
1636                       ),
1637                   'money' =>
1638                       array (
1639                         'name' => 'money',
1640                         'type' => 'currency',
1641                         'required'=>true,
1642                       ),
1643                   'test_dtm' =>
1644                       array (
1645                         'name' => 'test_dtm',
1646                         'type' => 'datetime',
1647                         'required'=>true,
1648                       ),
1649                   'test_dtm2' =>
1650                       array (
1651                         'name' => 'test_dtm2',
1652                         'type' => 'datetimecombo',
1653                         'required'=>true,
1654                       ),
1655                   'test_dt' =>
1656                       array (
1657                         'name' => 'test_dt',
1658                         'type' => 'date',
1659                         'required'=>true,
1660                       ),
1661                   'test_tm' =>
1662                       array (
1663                         'name' => 'test_tm',
1664                         'type' => 'time',
1665                         'required'=>true,
1666                       ),
1667                   ),
1668                   array("id" => "test123", 'intval' => 42, 'floatval' => 42.24,
1669                                 'money' => 56.78, 'test_dtm' => '2002-01-02 12:34:56', 'test_dtm2' => '2011-10-08 01:02:03',
1670                         'test_dt' => '1998-10-04', 'test_tm' => '03:04:05'
1671                   ),
1672                   array("id" => "'test123'", 'intval' => 42, 'floatval' => 42.24,
1673                                 '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'),
1674                         'test_dt' => $this->_db->convert('\'1998-10-04\'', 'date'), 'test_tm' => $this->_db->convert('\'03:04:05\'', 'time')
1675                   ),
1676             ),
1677             array("testreqnull", array (
1678                   'id' =>
1679                       array (
1680                         'name' => 'id',
1681                         'type' => 'varchar',
1682                         'required'=>true,
1683                       ),
1684                   'intval' =>
1685                       array (
1686                         'name' => 'intval',
1687                         'type' => 'int',
1688                         'required'=>true,
1689                       ),
1690                   'floatval' =>
1691                       array (
1692                         'name' => 'floatval',
1693                         'type' => 'decimal',
1694                         'required'=>true,
1695                       ),
1696                   'money' =>
1697                       array (
1698                         'name' => 'money',
1699                         'type' => 'currency',
1700                         'required'=>true,
1701                       ),
1702                   'test_dtm' =>
1703                       array (
1704                         'name' => 'test_dtm',
1705                         'type' => 'datetime',
1706                         'required'=>true,
1707                       ),
1708                   'test_dtm2' =>
1709                       array (
1710                         'name' => 'test_dtm2',
1711                         'type' => 'datetimecombo',
1712                         'required'=>true,
1713                       ),
1714                   'test_dt' =>
1715                       array (
1716                         'name' => 'test_dt',
1717                         'type' => 'date',
1718                         'required'=>true,
1719                       ),
1720                   'test_tm' =>
1721                       array (
1722                         'name' => 'test_tm',
1723                         'type' => 'time',
1724                         'required'=>true,
1725                       ),
1726                   ),
1727                   array(),
1728                   array("id" => "''", 'intval' => 0, 'floatval' => 0,
1729                                 'money' => 0, 'test_dtm' => "$emptydatetime", 'test_dtm2' => "$emptydatetime",
1730                         'test_dt' => "$emptydate", 'test_tm' => "$emptytime"
1731                   ),
1732                   array(),
1733             ),
1734             array("testnull", array (
1735                   'id' =>
1736                       array (
1737                         'name' => 'id',
1738                         'type' => 'varchar',
1739                       ),
1740                   'intval' =>
1741                       array (
1742                         'name' => 'intval',
1743                         'type' => 'int',
1744                       ),
1745                   'floatval' =>
1746                       array (
1747                         'name' => 'floatval',
1748                         'type' => 'decimal',
1749                       ),
1750                   'money' =>
1751                       array (
1752                         'name' => 'money',
1753                         'type' => 'currency',
1754                       ),
1755                   'test_dtm' =>
1756                       array (
1757                         'name' => 'test_dtm',
1758                         'type' => 'datetime',
1759                       ),
1760                   'test_dtm2' =>
1761                       array (
1762                         'name' => 'test_dtm2',
1763                         'type' => 'datetimecombo',
1764                       ),
1765                   'test_dt' =>
1766                       array (
1767                         'name' => 'test_dt',
1768                         'type' => 'date',
1769                       ),
1770                   'test_tm' =>
1771                       array (
1772                         'name' => 'test_tm',
1773                         'type' => 'time',
1774                       ),
1775                   ),
1776                   array("id" => 123),
1777                   array("id" => "'123'"),
1778                   array(),
1779             ),
1780             array("testempty", array (
1781                   'id' =>
1782                       array (
1783                         'name' => 'id',
1784                         'type' => 'varchar',
1785                       ),
1786                   'intval' =>
1787                       array (
1788                         'name' => 'intval',
1789                         'type' => 'int',
1790                       ),
1791                   'floatval' =>
1792                       array (
1793                         'name' => 'floatval',
1794                         'type' => 'decimal',
1795                       ),
1796                   'money' =>
1797                       array (
1798                         'name' => 'money',
1799                         'type' => 'currency',
1800                       ),
1801                   'test_dtm' =>
1802                       array (
1803                         'name' => 'test_dtm',
1804                         'type' => 'datetime',
1805                       ),
1806                   'test_dtm2' =>
1807                       array (
1808                         'name' => 'test_dtm2',
1809                         'type' => 'datetimecombo',
1810                       ),
1811                   'test_dt' =>
1812                       array (
1813                         'name' => 'test_dt',
1814                         'type' => 'date',
1815                       ),
1816                   'test_tm' =>
1817                       array (
1818                         'name' => 'test_tm',
1819                         'type' => 'time',
1820                       ),
1821                    'text_txt' =>
1822                       array (
1823                         'name' => 'test_txt',
1824                         'type' => 'varchar',
1825                       ),
1826                   ),
1827                   array("id" => "", 'intval' => '', 'floatval' => '',
1828                                 'money' => '', 'test_dtm' => '', 'test_dtm2' => '',
1829                         'test_dt' => '', 'test_tm' => '', 'text_txt' => null
1830                   ),
1831                   array("id" => "''", 'intval' => 0, 'floatval' => 0,
1832                                 'money' => 0, 'test_dtm' => "NULL", 'test_dtm2' => "NULL",
1833                         'test_dt' => "NULL", 'test_tm' => 'NULL'
1834                   ),
1835                   array('intval' => 'NULL', 'floatval' => 'NULL',
1836                                 'money' => 'NULL', 'test_dtm' => 'NULL', 'test_dtm2' => 'NULL',
1837                         'test_dt' => 'NULL', 'test_tm' => 'NULL'
1838                   ),
1839             ),
1840         );
1841     }
1842
1843    /**
1844     * Test InserSQL functions
1845     * @dataProvider vardefProvider
1846     * @param string $name
1847     * @param array $defs
1848     * @param array $data
1849     * @param array $result
1850     */
1851     public function testInsertSQL($name, $defs, $data, $result)
1852     {
1853         $vardefs = array(
1854                         'table' => $name,
1855             'fields' => $defs,
1856         );
1857         $obj = new TestSugarBean($name, $vardefs);
1858         // regular fields
1859         foreach($data as $k => $v) {
1860             $obj->$k = $v;
1861         }
1862         $sql = $this->_db->insertSQL($obj);
1863         $names = join('\s*,\s*',array_map('preg_quote', array_keys($result)));
1864         $values = join('\s*,\s*',array_map('preg_quote', array_values($result)));
1865         $this->assertRegExp("/INSERT INTO $name\s+\(\s*$names\s*\)\s+VALUES\s+\(\s*$values\s*\)/is", $sql, "Bad sql: $sql");
1866     }
1867
1868    /**
1869     * Test UpdateSQL functions
1870     * @dataProvider vardefProvider
1871     * @param string $name
1872     * @param array $defs
1873     * @param array $data
1874     * @param array $_
1875     * @param array $result
1876     */
1877     public function testUpdateSQL($name, $defs, $data, $_, $result = null)
1878     {
1879         $name = "update$name";
1880         $vardefs = array(
1881                         'table' => $name,
1882             'fields' => $defs,
1883         );
1884         // ensure it has an ID
1885         $vardefs['fields']['id'] = array (
1886                     'name' => 'id',
1887                     'type' => 'id',
1888                     'required'=>true,
1889                   );
1890
1891         $obj = new TestSugarBean($name, $vardefs);
1892         // regular fields
1893         foreach($defs as $k => $v) {
1894             if(isset($data[$k])) {
1895                 $obj->$k = $data[$k];
1896             } else {
1897                 $obj->$k = null;
1898             }
1899         }
1900         // set fixed ID
1901         $obj->id = 'test_ID';
1902         $sql = $this->_db->updateSQL($obj);
1903         if(is_null($result)) {
1904             $result = $_;
1905         }
1906         $names_i = array();
1907         foreach($result as $k => $v) {
1908             if($k == "id") continue;
1909             $names_i[] = preg_quote("$k=$v");
1910         }
1911         if(empty($names_i)) {
1912             $this->assertEquals("", $sql, "Bad sql: $sql");
1913             return;
1914         }
1915         $names = join('\s*,\s*',$names_i);
1916         $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");
1917     }
1918
1919     /**
1920      * Test the canInstall
1921      * @return void
1922      */
1923     public function testCanInstall() {
1924         $DBManagerClass = get_class($this->_db);
1925         if(!method_exists($this->_db, 'version') || !method_exists($this->_db, 'canInstall'))
1926             $this->markTestSkipped(
1927               "Class {$DBManagerClass} doesn't implement canInstall or version methods");
1928
1929         $method = new ReflectionMethod($DBManagerClass, 'canInstall');
1930         if($method->class == 'DBManager')
1931             $this->markTestSkipped(
1932               "Class {$DBManagerClass} or one of it's ancestors doesn't override DBManager's canInstall");
1933
1934         // First assuming that we are only running unit tests against a supported database :)
1935         $this->assertTrue($this->_db->canInstall(), "Apparently we are not running this unit test against a supported database!!!");
1936
1937         $DBstub = $this->getMock($DBManagerClass, array('version'));
1938         $DBstub->expects($this->any())
1939                ->method('version')
1940                ->will($this->returnValue('0.0.0')); // Expect that any supported version is higher than 0.0.0
1941
1942         $this->assertTrue(is_array($DBstub->canInstall()), "Apparently we do support version 0.0.0 in " . $DBManagerClass);
1943     }
1944 }