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