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 testGetRowCount()
1390     {
1391         if(!$this->_db->supports("select_rows")) {
1392             $this->markTestSkipped('Skipping, backend doesn\'t support select_rows');
1393         }
1394         $beanIds = $this->_createRecords(1);
1395
1396         $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1397
1398         $this->assertEquals($this->_db->getRowCount($result),1);
1399
1400         $this->_removeRecords($beanIds);
1401     }
1402
1403     public function testGetAffectedRowCount()
1404     {
1405         if(!$this->_db->supports("affected_rows")) {
1406             $this->markTestSkipped('Skipping, backend doesn\'t support affected rows');
1407         }
1408
1409         $beanIds = $this->_createRecords(1);
1410         $result = $this->_db->query("DELETE From contacts where id = '{$beanIds[0]}'");
1411         $this->assertEquals(1, $this->_db->getAffectedRowCount($result));
1412     }
1413
1414     public function testFetchByAssoc()
1415     {
1416         $beanIds = $this->_createRecords(1);
1417
1418         $result = $this->_db->query("SELECT id From contacts where id = '{$beanIds[0]}'");
1419
1420         $row = $this->_db->fetchByAssoc($result);
1421
1422         $this->assertTrue(is_array($row));
1423         $this->assertEquals($row['id'],$beanIds[0]);
1424
1425         $this->_removeRecords($beanIds);
1426     }
1427
1428     public function testConnect()
1429     {
1430         // TODO: Write this test
1431     }
1432
1433     public function testDisconnect()
1434     {
1435         $this->_db->disconnect();
1436         $this->assertTrue($this->_db->checkError());
1437         $this->_db = DBManagerFactory::getInstance();
1438     }
1439
1440     public function testGetTablesArray()
1441     {
1442         $tablename = 'test' . mt_rand();
1443         $this->createTableParams($tablename,
1444             array(
1445                 'foo' => array (
1446                     'name' => 'foo',
1447                     'type' => 'varchar',
1448                     'len' => '255',
1449                     ),
1450                 ),
1451             array()
1452             );
1453
1454         $this->assertTrue($this->_db->tableExists($tablename));
1455
1456         $this->dropTableName($tablename);
1457     }
1458
1459     public function testVersion()
1460     {
1461         $ver = $this->_db->version();
1462
1463         $this->assertTrue(is_string($ver));
1464     }
1465
1466     public function testTableExists()
1467     {
1468         $tablename = 'test' . mt_rand();
1469         $this->createTableParams($tablename,
1470             array(
1471                 'foo' => array (
1472                     'name' => 'foo',
1473                     'type' => 'varchar',
1474                     'len' => '255',
1475                     ),
1476                 ),
1477             array()
1478             );
1479
1480         $this->assertTrue(in_array($tablename,$this->_db->getTablesArray()));
1481
1482         $this->dropTableName($tablename);
1483     }
1484
1485     public function providerCompareVardefs()
1486     {
1487         $returnArray = array(
1488             array(
1489                 array(
1490                     'name' => 'foo',
1491                     'type' => 'varchar',
1492                     'len' => '255',
1493                     ),
1494                 array(
1495                     'name' => 'foo',
1496                     'type' => 'varchar',
1497                     'len' => '255',
1498                     ),
1499                 true),
1500             array(
1501                 array(
1502                     'name' => 'foo',
1503                     'type' => 'char',
1504                     'len' => '255',
1505                     ),
1506                 array(
1507                     'name' => 'foo',
1508                     'type' => 'varchar',
1509                     'len' => '255',
1510                     ),
1511                 false),
1512             array(
1513                 array(
1514                     'name' => 'foo',
1515                     'type' => 'char',
1516                     'len' => '255',
1517                     ),
1518                 array(
1519                     'name' => 'foo',
1520                     'len' => '255',
1521                 ),
1522                 false),
1523             array(
1524                 array(
1525                     'name' => 'foo',
1526                     'len' => '255',
1527                     ),
1528                 array(
1529                     'name' => 'foo',
1530                     'type' => 'varchar',
1531                     'len' => '255',
1532                     ),
1533                 true),
1534             array(
1535                 array(
1536                     'name' => 'foo',
1537                     'type' => 'varchar',
1538                     'len' => '255',
1539                     ),
1540                 array(
1541                     'name' => 'FOO',
1542                     'type' => 'varchar',
1543                     'len' => '255',
1544                     ),
1545                 true),
1546             );
1547
1548         return $returnArray;
1549     }
1550
1551     /**
1552      * @dataProvider providerCompareVarDefs
1553      */
1554     public function testCompareVarDefs($fieldDef1,$fieldDef2,$expectedResult)
1555     {
1556         if ( $expectedResult ) {
1557             $this->assertTrue($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1558         }
1559         else {
1560             $this->assertFalse($this->_db->compareVarDefs($fieldDef1,$fieldDef2));
1561         }
1562     }
1563
1564     /**
1565      * @ticket 34892
1566      */
1567     public function test_Bug34892_MssqlNotClearingErrorResults()
1568     {
1569         // execute a bad query
1570         $this->_db->query("select dsdsdsdsdsdsdsdsdsd", false, "test_Bug34892_MssqlNotClearingErrorResults", true);
1571         // assert it found an error
1572         $this->assertNotEmpty($this->_db->lastError(), "lastError should return true as a result of the previous illegal query");
1573         // now, execute a good query
1574         $this->_db->query("select * from config");
1575         // and make no error messages are asserted
1576         $this->assertEmpty($this->_db->lastError(), "lastError should have cleared the previous error and return false of the last legal query");
1577     }
1578
1579     public function vardefProvider()
1580     {
1581 //        $emptydate = "0000-00-00";
1582 //        if($this->_db instanceof MssqlManager || $this->_db instanceof OracleManager) {
1583 //            $emptydate = "1970-01-01";
1584 //        }
1585         $GLOBALS['log']->info('DBManagerTest.vardefProvider: _db = ' . print_r($this->_db));
1586         $this->setUp(); // Just in case the DB driver is not created yet.
1587         $emptydate = $this->_db->emptyValue("date");
1588         $emptytime = $this->_db->emptyValue("time");
1589         $emptydatetime = $this->_db->emptyValue("datetime");
1590
1591         return array(
1592             array("testid", array (
1593                   'id' =>
1594                   array (
1595                     'name' => 'id',
1596                     'type' => 'varchar',
1597                     'required'=>true,
1598                   ),
1599                   ),
1600                   array("id" => "test123"),
1601                   array("id" => "'test123'")
1602             ),
1603             array("testtext", array (
1604                   'text1' =>
1605                   array (
1606                     'name' => 'text1',
1607                     'type' => 'varchar',
1608                     'required'=>true,
1609                   ),
1610                   'text2' =>
1611                   array (
1612                     'name' => 'text2',
1613                     'type' => 'varchar',
1614                   ),
1615                   ),
1616                   array(),
1617                   array("text1" => "''"),
1618                   array()
1619             ),
1620             array("testtext2", array (
1621                   'text1' =>
1622                   array (
1623                     'name' => 'text1',
1624                     'type' => 'varchar',
1625                     'required'=>true,
1626                   ),
1627                   'text2' =>
1628                   array (
1629                     'name' => 'text2',
1630                     'type' => 'varchar',
1631                   ),
1632                   ),
1633                   array('text1' => 'foo', 'text2' => 'bar'),
1634                   array("text1" => "'foo'", 'text2' => "'bar'"),
1635             ),
1636             array("testreq", array (
1637                   'id' =>
1638                       array (
1639                         'name' => 'id',
1640                         'type' => 'varchar',
1641                         'required'=>true,
1642                       ),
1643                   'intval' =>
1644                       array (
1645                         'name' => 'intval',
1646                         'type' => 'int',
1647                         'required'=>true,
1648                       ),
1649                   'floatval' =>
1650                       array (
1651                         'name' => 'floatval',
1652                         'type' => 'decimal',
1653                         'required'=>true,
1654                       ),
1655                   'money' =>
1656                       array (
1657                         'name' => 'money',
1658                         'type' => 'currency',
1659                         'required'=>true,
1660                       ),
1661                   'test_dtm' =>
1662                       array (
1663                         'name' => 'test_dtm',
1664                         'type' => 'datetime',
1665                         'required'=>true,
1666                       ),
1667                   'test_dtm2' =>
1668                       array (
1669                         'name' => 'test_dtm2',
1670                         'type' => 'datetimecombo',
1671                         'required'=>true,
1672                       ),
1673                   'test_dt' =>
1674                       array (
1675                         'name' => 'test_dt',
1676                         'type' => 'date',
1677                         'required'=>true,
1678                       ),
1679                   'test_tm' =>
1680                       array (
1681                         'name' => 'test_tm',
1682                         'type' => 'time',
1683                         'required'=>true,
1684                       ),
1685                   ),
1686                   array("id" => "test123", 'intval' => 42, 'floatval' => 42.24,
1687                                 'money' => 56.78, 'test_dtm' => '2002-01-02 12:34:56', 'test_dtm2' => '2011-10-08 01:02:03',
1688                         'test_dt' => '1998-10-04', 'test_tm' => '03:04:05'
1689                   ),
1690                   array("id" => "'test123'", 'intval' => 42, 'floatval' => 42.24,
1691                                 'money' => 56.78, 'test_dtm' => '\'2002-01-02 12:34:56\'', 'test_dtm2' => '\'2011-10-08 01:02:03\'',
1692                         'test_dt' => '\'1998-10-04\'', 'test_tm' => '\'03:04:05\''
1693                   ),
1694             ),
1695             array("testreqnull", array (
1696                   'id' =>
1697                       array (
1698                         'name' => 'id',
1699                         'type' => 'varchar',
1700                         'required'=>true,
1701                       ),
1702                   'intval' =>
1703                       array (
1704                         'name' => 'intval',
1705                         'type' => 'int',
1706                         'required'=>true,
1707                       ),
1708                   'floatval' =>
1709                       array (
1710                         'name' => 'floatval',
1711                         'type' => 'decimal',
1712                         'required'=>true,
1713                       ),
1714                   'money' =>
1715                       array (
1716                         'name' => 'money',
1717                         'type' => 'currency',
1718                         'required'=>true,
1719                       ),
1720                   'test_dtm' =>
1721                       array (
1722                         'name' => 'test_dtm',
1723                         'type' => 'datetime',
1724                         'required'=>true,
1725                       ),
1726                   'test_dtm2' =>
1727                       array (
1728                         'name' => 'test_dtm2',
1729                         'type' => 'datetimecombo',
1730                         'required'=>true,
1731                       ),
1732                   'test_dt' =>
1733                       array (
1734                         'name' => 'test_dt',
1735                         'type' => 'date',
1736                         'required'=>true,
1737                       ),
1738                   'test_tm' =>
1739                       array (
1740                         'name' => 'test_tm',
1741                         'type' => 'time',
1742                         'required'=>true,
1743                       ),
1744                   ),
1745                   array(),
1746                   array("id" => "''", 'intval' => 0, 'floatval' => 0,
1747                                 'money' => 0, 'test_dtm' => "$emptydatetime", 'test_dtm2' => "$emptydatetime",
1748                         'test_dt' => "$emptydate", 'test_tm' => "$emptytime"
1749                   ),
1750                   array(),
1751             ),
1752             array("testnull", array (
1753                   'id' =>
1754                       array (
1755                         'name' => 'id',
1756                         'type' => 'varchar',
1757                       ),
1758                   'intval' =>
1759                       array (
1760                         'name' => 'intval',
1761                         'type' => 'int',
1762                       ),
1763                   'floatval' =>
1764                       array (
1765                         'name' => 'floatval',
1766                         'type' => 'decimal',
1767                       ),
1768                   'money' =>
1769                       array (
1770                         'name' => 'money',
1771                         'type' => 'currency',
1772                       ),
1773                   'test_dtm' =>
1774                       array (
1775                         'name' => 'test_dtm',
1776                         'type' => 'datetime',
1777                       ),
1778                   'test_dtm2' =>
1779                       array (
1780                         'name' => 'test_dtm2',
1781                         'type' => 'datetimecombo',
1782                       ),
1783                   'test_dt' =>
1784                       array (
1785                         'name' => 'test_dt',
1786                         'type' => 'date',
1787                       ),
1788                   'test_tm' =>
1789                       array (
1790                         'name' => 'test_tm',
1791                         'type' => 'time',
1792                       ),
1793                   ),
1794                   array("id" => 123),
1795                   array("id" => "'123'"),
1796                   array(),
1797             ),
1798             array("testempty", array (
1799                   'id' =>
1800                       array (
1801                         'name' => 'id',
1802                         'type' => 'varchar',
1803                       ),
1804                   'intval' =>
1805                       array (
1806                         'name' => 'intval',
1807                         'type' => 'int',
1808                       ),
1809                   'floatval' =>
1810                       array (
1811                         'name' => 'floatval',
1812                         'type' => 'decimal',
1813                       ),
1814                   'money' =>
1815                       array (
1816                         'name' => 'money',
1817                         'type' => 'currency',
1818                       ),
1819                   'test_dtm' =>
1820                       array (
1821                         'name' => 'test_dtm',
1822                         'type' => 'datetime',
1823                       ),
1824                   'test_dtm2' =>
1825                       array (
1826                         'name' => 'test_dtm2',
1827                         'type' => 'datetimecombo',
1828                       ),
1829                   'test_dt' =>
1830                       array (
1831                         'name' => 'test_dt',
1832                         'type' => 'date',
1833                       ),
1834                   'test_tm' =>
1835                       array (
1836                         'name' => 'test_tm',
1837                         'type' => 'time',
1838                       ),
1839                    'text_txt' =>
1840                       array (
1841                         'name' => 'test_txt',
1842                         'type' => 'varchar',
1843                       ),
1844                   ),
1845                   array("id" => "", 'intval' => '', 'floatval' => '',
1846                                 'money' => '', 'test_dtm' => '', 'test_dtm2' => '',
1847                         'test_dt' => '', 'test_tm' => '', 'text_txt' => null
1848                   ),
1849                   array("id" => "''", 'intval' => 0, 'floatval' => 0,
1850                                 'money' => 0, 'test_dtm' => "NULL", 'test_dtm2' => "NULL",
1851                         'test_dt' => "NULL", 'test_tm' => 'NULL'
1852                   ),
1853                   array('intval' => 'NULL', 'floatval' => 'NULL',
1854                                 'money' => 'NULL', 'test_dtm' => 'NULL', 'test_dtm2' => 'NULL',
1855                         'test_dt' => 'NULL', 'test_tm' => 'NULL'
1856                   ),
1857             ),
1858         );
1859     }
1860
1861    /**
1862     * Test InserSQL functions
1863     * @dataProvider vardefProvider
1864     * @param string $name
1865     * @param array $defs
1866     * @param array $data
1867     * @param array $result
1868     */
1869     public function testInsertSQL($name, $defs, $data, $result)
1870     {
1871         $vardefs = array(
1872                         'table' => $name,
1873             'fields' => $defs,
1874         );
1875         $obj = new TestSugarBean($name, $vardefs);
1876         // regular fields
1877         foreach($data as $k => $v) {
1878             $obj->$k = $v;
1879         }
1880         $sql = $this->_db->insertSQL($obj);
1881         $names = join('\s*,\s*',array_map('preg_quote', array_keys($result)));
1882         $values = join('\s*,\s*',array_map('preg_quote', array_values($result)));
1883         $this->assertRegExp("/INSERT INTO $name\s+\(\s*$names\s*\)\s+VALUES\s+\(\s*$values\s*\)/is", $sql, "Bad sql: $sql");
1884     }
1885
1886    /**
1887     * Test UpdateSQL functions
1888     * @dataProvider vardefProvider
1889     * @param string $name
1890     * @param array $defs
1891     * @param array $data
1892     * @param array $_
1893     * @param array $result
1894     */
1895     public function testUpdateSQL($name, $defs, $data, $_, $result = null)
1896     {
1897         $name = "update$name";
1898         $vardefs = array(
1899                         'table' => $name,
1900             'fields' => $defs,
1901         );
1902         // ensure it has an ID
1903         $vardefs['fields']['id'] = array (
1904                     'name' => 'id',
1905                     'type' => 'id',
1906                     'required'=>true,
1907                   );
1908
1909         $obj = new TestSugarBean($name, $vardefs);
1910         // regular fields
1911         foreach($defs as $k => $v) {
1912             if(isset($data[$k])) {
1913                 $obj->$k = $data[$k];
1914             } else {
1915                 $obj->$k = null;
1916             }
1917         }
1918         // set fixed ID
1919         $obj->id = 'test_ID';
1920         $sql = $this->_db->updateSQL($obj);
1921         if(is_null($result)) {
1922             $result = $_;
1923         }
1924         $names_i = array();
1925         foreach($result as $k => $v) {
1926             if($k == "id") continue;
1927             $names_i[] = "$k=$v";
1928         }
1929         if(empty($names_i)) {
1930             $this->assertEquals("", $sql, "Bad sql: $sql");
1931             return;
1932         }
1933         $names = join('\s*,\s*',$names_i);
1934         $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");
1935     }
1936 }