include/database/MysqlManager.php

   1 <?php
   2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
   3 /*********************************************************************************
   4  * SugarCRM Community Edition is a customer relationship management program developed by
   5  * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
   6  *
   7  * This program is free software; you can redistribute it and/or modify it under
   8  * the terms of the GNU Affero General Public License version 3 as published by the
   9  * Free Software Foundation with the addition of the following permission added
  10  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
  11  * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
  12  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
  13  *
  14  * This program is distributed in the hope that it will be useful, but WITHOUT
  15  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  16  * FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more
  17  * details.
  18  *
  19  * You should have received a copy of the GNU Affero General Public License along with
  20  * this program; if not, see http://www.gnu.org/licenses or write to the Free
  21  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
  22  * 02110-1301 USA.
  23  *
  24  * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
  25  * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
  26  *
  27  * The interactive user interfaces in modified source and object code versions
  28  * of this program must display Appropriate Legal Notices, as required under
  29  * Section 5 of the GNU Affero General Public License version 3.
  30  *
  31  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
  32  * these Appropriate Legal Notices must retain the display of the "Powered by
  33  * SugarCRM" logo. If the display of the logo is not reasonably feasible for
  34  * technical reasons, the Appropriate Legal Notices must display the words
  35  * "Powered by SugarCRM".
  36  ********************************************************************************/
  37
  38 /*********************************************************************************
  39
  40 * Description: This file handles the Data base functionality for the application.
  41 * It acts as the DB abstraction layer for the application. It depends on helper classes
  42 * which generate the necessary SQL. This sql is then passed to PEAR DB classes.
  43 * The helper class is chosen in DBManagerFactory, which is driven by 'db_type' in 'dbconfig' under config.php.
  44 *
  45 * All the functions in this class will work with any bean which implements the meta interface.
  46 * The passed bean is passed to helper class which uses these functions to generate correct sql.
  47 *
  48 * The meta interface has the following functions:
  49 * getTableName()                        Returns table name of the object.
  50 * getFieldDefinitions()         Returns a collection of field definitions in order.
  51 * getFieldDefintion(name)               Return field definition for the field.
  52 * getFieldValue(name)           Returns the value of the field identified by name.
  53 *                               If the field is not set, the function will return boolean FALSE.
  54 * getPrimaryFieldDefinition()   Returns the field definition for primary key
  55 *
  56 * The field definition is an array with the following keys:
  57 *
  58 * name          This represents name of the field. This is a required field.
  59 * type          This represents type of the field. This is a required field and valid values are:
  60 *           �   int
  61 *           �   long
  62 *           �   varchar
  63 *           �   text
  64 *           �   date
  65 *           �   datetime
  66 *           �   double
  67 *           �   float
  68 *           �   uint
  69 *           �   ulong
  70 *           �   time
  71 *           �   short
  72 *           �   enum
  73 * length    This is used only when the type is varchar and denotes the length of the string.
  74 *           The max value is 255.
  75 * enumvals  This is a list of valid values for an enum separated by "|".
  76 *           It is used only if the type is �enum�;
  77 * required  This field dictates whether it is a required value.
  78 *           The default value is �FALSE�.
  79 * isPrimary This field identifies the primary key of the table.
  80 *           If none of the fields have this flag set to �TRUE�,
  81 *           the first field definition is assume to be the primary key.
  82 *           Default value for this field is �FALSE�.
  83 * default   This field sets the default value for the field definition.
  84 *
  85 *
  86 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
  87 * All Rights Reserved.
  88 * Contributor(s): ______________________________________..
  89 ********************************************************************************/
  90
  91 /**
  92  * MySQL manager implementation for mysql extension
  93  */
  94 class MysqlManager extends DBManager
  95 {
  96         /**
  97          * @see DBManager::$dbType
  98          */
  99         public $dbType = 'mysql';
 100         public $variant = 'mysql';
 101         public $dbName = 'MySQL';
 102         public $label = 'LBL_MYSQL';
 103
 104         protected $maxNameLengths = array(
 105                 'table' => 64,
 106                 'column' => 64,
 107                 'index' => 64,
 108                 'alias' => 256
 109         );
 110
 111         protected $type_map = array(
 112                         'int'      => 'int',
 113                         'double'   => 'double',
 114                         'float'    => 'float',
 115                         'uint'     => 'int unsigned',
 116                         'ulong'    => 'bigint unsigned',
 117                         'long'     => 'bigint',
 118                         'short'    => 'smallint',
 119                         'varchar'  => 'varchar',
 120                         'text'     => 'text',
 121                         'longtext' => 'longtext',
 122                         'date'     => 'date',
 123                         'enum'     => 'varchar',
 124                         'relate'   => 'varchar',
 125                         'multienum'=> 'text',
 126                         'html'     => 'text',
 127                         'datetime' => 'datetime',
 128                         'datetimecombo' => 'datetime',
 129                         'time'     => 'time',
 130                         'bool'     => 'bool',
 131                         'tinyint'  => 'tinyint',
 132                         'char'     => 'char',
 133                         'blob'     => 'blob',
 134                         'longblob' => 'longblob',
 135                         'currency' => 'decimal(26,6)',
 136                         'decimal'  => 'decimal',
 137                         'decimal2' => 'decimal',
 138                         'id'       => 'char(36)',
 139                         'url'      => 'varchar',
 140                         'encrypt'  => 'varchar',
 141                         'file'     => 'varchar',
 142                         'decimal_tpl' => 'decimal(%d, %d)',
 143
 144         );
 145
 146         protected $capabilities = array(
 147                 "affected_rows" => true,
 148                 "select_rows" => true,
 149                 "inline_keys" => true,
 150                 "create_user" => true,
 151                 "fulltext" => true,
 152             "collation" => true,
 153             "create_db" => true,
 154         );
 155
 156         /**
 157          * Parses and runs queries
 158          *
 159          * @param  string   $sql        SQL Statement to execute
 160          * @param  bool     $dieOnError True if we want to call die if the query returns errors
 161          * @param  string   $msg        Message to log if error occurs
 162          * @param  bool     $suppress   Flag to suppress all error output unless in debug logging mode.
 163          * @param  bool     $keepResult True if we want to push this result into the $lastResult array.
 164          * @return resource result set
 165          */
 166         public function query($sql, $dieOnError = false, $msg = '', $suppress = false, $keepResult = false)
 167         {
 168                 if(is_array($sql)) {
 169                         return $this->queryArray($sql, $dieOnError, $msg, $suppress);
 170                 }
 171                 parent::countQuery($sql);
 172                 $GLOBALS['log']->info('Query:' . $sql);
 173                 $this->checkConnection();
 174                 $this->query_time = microtime(true);
 175                 $this->lastsql = $sql;
 176                 $result = $suppress?@mysql_query($sql, $this->database):mysql_query($sql, $this->database);
 177
 178                 $this->query_time = microtime(true) - $this->query_time;
 179                 $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
 180
 181
 182                 if($keepResult)
 183                         $this->lastResult = $result;
 184
 185                 $this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
 186                 return $result;
 187         }
 188
 189     /**
 190      * Returns the number of rows affected by the last query
 191      * @param $result
 192      * @return int
 193      */
 194         public function getAffectedRowCount($result)
 195         {
 196                 return mysql_affected_rows($this->getDatabase());
 197         }
 198
 199         /**
 200          * Disconnects from the database
 201          *
 202          * Also handles any cleanup needed
 203          */
 204         public function disconnect()
 205         {
 206                 $GLOBALS['log']->debug('Calling MySQL::disconnect()');
 207                 if(!empty($this->database)){
 208                         $this->freeResult();
 209                         mysql_close($this->database);
 210                         $this->database = null;
 211                 }
 212         }
 213
 214         /**
 215          * @see DBManager::freeDbResult()
 216          */
 217         protected function freeDbResult($dbResult)
 218         {
 219                 if(!empty($dbResult))
 220                         mysql_free_result($dbResult);
 221         }
 222
 223         /**
 224          * Returns the number of rows returned by the result
 225          *
 226          * @param  resource $result
 227          * @return int
 228          */
 229         public function getRowCount($result)
 230         {
 231                 if(!empty($result)) {
 232                         return mysql_num_rows($result);
 233                 }
 234                 return 0;
 235         }
 236
 237         /**
 238          * @see DBManager::limitQuery()
 239          */
 240         public function limitQuery($sql, $start, $count, $dieOnError = false, $msg = '', $execute = true)
 241         {
 242                 if ($start < 0)
 243                         $start = 0;
 244                 $GLOBALS['log']->debug('Limit Query:' . $sql. ' Start: ' .$start . ' count: ' . $count);
 245
 246                 $sql = "$sql LIMIT $start,$count";
 247                 $this->lastsql = $sql;
 248
 249                 if(!empty($GLOBALS['sugar_config']['check_query'])){
 250                         $this->checkQuery($sql);
 251                 }
 252                 if(!$execute) {
 253                         return $sql;
 254                 }
 255
 256                 return $this->query($sql, $dieOnError, $msg);
 257         }
 258
 259
 260         /**
 261          * @see DBManager::checkQuery()
 262          */
 263         protected function checkQuery($sql)
 264         {
 265                 $result   = $this->query('EXPLAIN ' . $sql);
 266                 $badQuery = array();
 267                 while ($row = $this->fetchByAssoc($result)) {
 268                         if (empty($row['table']))
 269                                 continue;
 270                         $badQuery[$row['table']] = '';
 271                         if (strtoupper($row['type']) == 'ALL')
 272                                 $badQuery[$row['table']]  .=  ' Full Table Scan;';
 273                         if (empty($row['key']))
 274                                 $badQuery[$row['table']] .= ' No Index Key Used;';
 275                         if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using filesort') > 0)
 276                                 $badQuery[$row['table']] .= ' Using FileSort;';
 277                         if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using temporary') > 0)
 278                                 $badQuery[$row['table']] .= ' Using Temporary Table;';
 279                 }
 280
 281                 if ( empty($badQuery) )
 282                         return true;
 283
 284                 foreach($badQuery as $table=>$data ){
 285                         if(!empty($data)){
 286                                 $warning = ' Table:' . $table . ' Data:' . $data;
 287                                 if(!empty($GLOBALS['sugar_config']['check_query_log'])){
 288                                         $GLOBALS['log']->fatal($sql);
 289                                         $GLOBALS['log']->fatal('CHECK QUERY:' .$warning);
 290                                 }
 291                                 else{
 292                                         $GLOBALS['log']->warn('CHECK QUERY:' .$warning);
 293                                 }
 294                         }
 295                 }
 296
 297                 return false;
 298         }
 299
 300         /**
 301          * @see DBManager::get_columns()
 302          */
 303         public function get_columns($tablename)
 304         {
 305                 //find all unique indexes and primary keys.
 306                 $result = $this->query("DESCRIBE $tablename");
 307
 308                 $columns = array();
 309                 while (($row=$this->fetchByAssoc($result)) !=null) {
 310                         $name = strtolower($row['Field']);
 311                         $columns[$name]['name']=$name;
 312                         $matches = array();
 313                         preg_match_all('/(\w+)(?:\(([0-9]+,?[0-9]*)\)|)( unsigned)?/i', $row['Type'], $matches);
 314                         $columns[$name]['type']=strtolower($matches[1][0]);
 315                         if ( isset($matches[2][0]) && in_array(strtolower($matches[1][0]),array('varchar','char','varchar2','int','decimal','float')) )
 316                                 $columns[$name]['len']=strtolower($matches[2][0]);
 317                         if ( stristr($row['Extra'],'auto_increment') )
 318                                 $columns[$name]['auto_increment'] = '1';
 319                         if ($row['Null'] == 'NO' && !stristr($row['Key'],'PRI'))
 320                                 $columns[$name]['required'] = 'true';
 321                         if (!empty($row['Default']) )
 322                                 $columns[$name]['default'] = $row['Default'];
 323                 }
 324                 return $columns;
 325         }
 326
 327         /**
 328          * @see DBManager::getFieldsArray()
 329          */
 330         public function getFieldsArray($result, $make_lower_case=false)
 331         {
 332                 $field_array = array();
 333
 334                 if(empty($result))
 335                         return 0;
 336
 337                 $fields = mysql_num_fields($result);
 338                 for ($i=0; $i < $fields; $i++) {
 339                         $meta = mysql_fetch_field($result, $i);
 340                         if (!$meta)
 341                                 return array();
 342
 343                         if($make_lower_case == true)
 344                                 $meta->name = strtolower($meta->name);
 345
 346                         $field_array[] = $meta->name;
 347                 }
 348
 349                 return $field_array;
 350         }
 351
 352         /**
 353          * @see DBManager::fetchByAssoc()
 354          */
 355         public function fetchByAssoc($result, $rowNum = -1, $encode = true)
 356         {
 357                 if (!$result)
 358                         return false;
 359
 360                 if ($result && $rowNum > -1) {
 361                         if ($this->getRowCount($result) > $rowNum)
 362                                 mysql_data_seek($result, $rowNum);
 363                 }
 364
 365                 $row = mysql_fetch_assoc($result);
 366
 367                 if ($encode && $this->encode && is_array($row))
 368                         return array_map('to_html', $row);
 369
 370                 return $row;
 371         }
 372
 373         /**
 374          * @see DBManager::getTablesArray()
 375          */
 376         public function getTablesArray()
 377         {
 378                 $this->log->debug('Fetching table list');
 379
 380                 if ($this->getDatabase()) {
 381                         $tables = array();
 382                         $r = $this->query('SHOW TABLES');
 383                         if (!empty($r)) {
 384                                 while ($a = $this->fetchByAssoc($r)) {
 385                                         $row = array_values($a);
 386                                         $tables[]=$row[0];
 387                                 }
 388                                 return $tables;
 389                         }
 390                 }
 391
 392                 return false; // no database available
 393         }
 394
 395         /**
 396          * @see DBManager::version()
 397          */
 398         public function version()
 399         {
 400                 return $this->getOne("SELECT version() version");
 401         }
 402
 403         /**
 404          * @see DBManager::tableExists()
 405          */
 406         public function tableExists($tableName)
 407         {
 408                 $this->log->info("tableExists: $tableName");
 409
 410                 if ($this->getDatabase()) {
 411                         $result = $this->getOne("SHOW TABLES LIKE ".$this->quoted($tableName));
 412                         return !empty($result);
 413                 }
 414
 415                 return false;
 416         }
 417
 418         /**
 419          * Get tables like expression
 420          * @param $like string
 421          * @return array
 422          */
 423         public function tablesLike($like)
 424         {
 425                 if ($this->getDatabase()) {
 426                         $tables = array();
 427                         $r = $this->query('SHOW TABLES LIKE '.$this->quoted($like));
 428                         if (!empty($r)) {
 429                                 while ($a = $this->fetchByAssoc($r)) {
 430                                         $row = array_values($a);
 431                                         $tables[]=$row[0];
 432                                 }
 433                                 return $tables;
 434                         }
 435                 }
 436                 return false;
 437         }
 438
 439         /**
 440          * @see DBManager::quote()
 441          */
 442         public function quote($string)
 443         {
 444                 if(is_array($string)) {
 445                         return $this->arrayQuote($string);
 446                 }
 447                 return mysql_real_escape_string($this->quoteInternal($string), $this->getDatabase());
 448         }
 449
 450         /**
 451          * @see DBManager::connect()
 452          */
 453         public function connect(array $configOptions = null, $dieOnError = false)
 454         {
 455                 global $sugar_config;
 456
 457                 if(is_null($configOptions))
 458                         $configOptions = $sugar_config['dbconfig'];
 459
 460                 if ($this->getOption('persistent')) {
 461                         $this->database = @mysql_pconnect(
 462                                 $configOptions['db_host_name'],
 463                                 $configOptions['db_user_name'],
 464                                 $configOptions['db_password']
 465                                 );
 466                 }
 467
 468                 if (!$this->database) {
 469                         $this->database = mysql_connect(
 470                                         $configOptions['db_host_name'],
 471                                         $configOptions['db_user_name'],
 472                                         $configOptions['db_password']
 473                                         );
 474                         if(empty($this->database)) {
 475                                 $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name']." as ".$configOptions['db_user_name'].":".mysql_error());
 476                                 if($dieOnError) {
 477                                         if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
 478                                                 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
 479                                         } else {
 480                                                 sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");
 481                                         }
 482                                 } else {
 483                                         return false;
 484                                 }
 485                         }
 486                         // Do not pass connection information because we have not connected yet
 487                         if($this->database  && $this->getOption('persistent')){
 488                                 $_SESSION['administrator_error'] = "<b>Severe Performance Degradation: Persistent Database Connections "
 489                                         . "not working.  Please set \$sugar_config['dbconfigoption']['persistent'] to false "
 490                                         . "in your config.php file</b>";
 491                         }
 492                 }
 493                 if(!empty($configOptions['db_name']) && !@mysql_select_db($configOptions['db_name'])) {
 494                         $GLOBALS['log']->fatal( "Unable to select database {$configOptions['db_name']}: " . mysql_error($this->database));
 495                         if($dieOnError) {
 496                                 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
 497                         } else {
 498                                 return false;
 499                         }
 500                 }
 501
 502                 // cn: using direct calls to prevent this from spamming the Logs
 503             mysql_query("SET CHARACTER SET utf8", $this->database);
 504             $names = "SET NAMES 'utf8'";
 505             $collation = $this->getOption('collation');
 506             if(!empty($collation)) {
 507                 $names .= " COLLATE '$collation'";
 508                 }
 509             mysql_query($names, $this->database);
 510
 511                 if(!$this->checkError('Could Not Connect:', $dieOnError))
 512                         $GLOBALS['log']->info("connected to db");
 513                 $this->connectOptions = $configOptions;
 514
 515                 $GLOBALS['log']->info("Connect:".$this->database);
 516                 return true;
 517         }
 518
 519         /**
 520          * @see DBManager::repairTableParams()
 521          *
 522          * For MySQL, we can write the ALTER TABLE statement all in one line, which speeds things
 523          * up quite a bit. So here, we'll parse the returned SQL into a single ALTER TABLE command.
 524          */
 525         public function repairTableParams($tablename, $fielddefs, $indices, $execute = true, $engine = null)
 526         {
 527                 $sql = parent::repairTableParams($tablename,$fielddefs,$indices,false,$engine);
 528
 529                 if ( $sql == '' )
 530                         return '';
 531
 532                 if ( stristr($sql,'create table') )
 533                 {
 534                         if ($execute) {
 535                                 $msg = "Error creating table: ".$tablename. ":";
 536                                 $this->query($sql,true,$msg);
 537                         }
 538                         return $sql;
 539                 }
 540
 541                 // first, parse out all the comments
 542                 $match = array();
 543                 preg_match_all('!/\*.*?\*/!is', $sql, $match);
 544                 $commentBlocks = $match[0];
 545                 $sql = preg_replace('!/\*.*?\*/!is','', $sql);
 546
 547                 // now, we should only have alter table statements
 548                 // let's replace the 'alter table name' part with a comma
 549                 $sql = preg_replace("!alter table $tablename!is",', ', $sql);
 550
 551                 // re-add it at the beginning
 552                 $sql = substr_replace($sql,'',strpos($sql,','),1);
 553                 $sql = str_replace(";","",$sql);
 554                 $sql = str_replace("\n","",$sql);
 555                 $sql = "ALTER TABLE $tablename $sql";
 556
 557                 if ( $execute )
 558                         $this->query($sql,'Error with MySQL repair table');
 559
 560                 // and re-add the comments at the beginning
 561                 $sql = implode("\n",$commentBlocks) . "\n". $sql . "\n";
 562
 563                 return $sql;
 564         }
 565
 566         /**
 567          * @see DBManager::convert()
 568          */
 569         public function convert($string, $type, array $additional_parameters = array())
 570         {
 571                 $all_parameters = $additional_parameters;
 572                 if(is_array($string)) {
 573                         $all_parameters = array_merge($string, $all_parameters);
 574                 } elseif (!is_null($string)) {
 575                         array_unshift($all_parameters, $string);
 576                 }
 577                 $all_strings = implode(',', $all_parameters);
 578
 579                 switch (strtolower($type)) {
 580                         case 'today':
 581                                 return "CURDATE()";
 582                         case 'left':
 583                                 return "LEFT($all_strings)";
 584                         case 'date_format':
 585                                 if(empty($additional_parameters)) {
 586                                         return "DATE_FORMAT($string,'%Y-%m-%d')";
 587                                 } else {
 588                                         $format = $additional_parameters[0];
 589                                         if($format[0] != "'") {
 590                                                 $format = $this->quoted($format);
 591                                         }
 592                                         return "DATE_FORMAT($string,$format)";
 593                                 }
 594                         case 'datetime':
 595                                 return $string;
 596                         case 'ifnull':
 597                                 if(empty($additional_parameters) && !strstr($all_strings, ",")) {
 598                                         $all_strings .= ",''";
 599                                 }
 600                                 return "IFNULL($all_strings)";
 601                         case 'concat':
 602                                 return "CONCAT($all_strings)";
 603                         case 'quarter':
 604                                         return "QUARTER($string)";
 605                         case "length":
 606                                         return "LENGTH($string)";
 607                         case 'month':
 608                                         return "MONTH($string)";
 609                         case 'add_date':
 610                                         return "DATE_ADD($string, INTERVAL {$additional_parameters[0]} {$additional_parameters[1]})";
 611                         case 'add_time':
 612                                         return "DATE_ADD($string, INTERVAL + CONCAT({$additional_parameters[0]}, ':', {$additional_parameters[1]}) HOUR_MINUTE)";
 613                 }
 614
 615                 return $string;
 616         }
 617
 618         /**
 619          * (non-PHPdoc)
 620          * @see DBManager::fromConvert()
 621          */
 622         public function fromConvert($string, $type)
 623         {
 624                 return $string;
 625         }
 626
 627         /**
 628          * Returns the name of the engine to use or null if we are to use the default
 629          *
 630          * @param  object $bean SugarBean instance
 631          * @return string
 632          */
 633         protected function getEngine($bean)
 634         {
 635                 global $dictionary;
 636                 $engine = null;
 637                 if (isset($dictionary[$bean->getObjectName()]['engine'])) {
 638                         $engine = $dictionary[$bean->getObjectName()]['engine'];
 639                 }
 640                 return $engine;
 641         }
 642
 643         /**
 644          * Returns true if the engine given is enabled in the backend
 645          *
 646          * @param  string $engine
 647          * @return bool
 648          */
 649         protected function isEngineEnabled($engine)
 650         {
 651                 $engine = strtoupper($engine);
 652
 653                 $r = $this->query("SHOW ENGINES");
 654
 655                 while ( $row = $this->fetchByAssoc($r) )
 656                         if ( strtoupper($row['Engine']) == $engine )
 657                                 return ($row['Support']=='YES' || $row['Support']=='DEFAULT');
 658
 659                 return false;
 660         }
 661
 662         /**
 663          * @see DBManager::createTableSQL()
 664          */
 665         public function createTableSQL(SugarBean $bean)
 666         {
 667                 $tablename = $bean->getTableName();
 668                 $fieldDefs = $bean->getFieldDefinitions();
 669                 $indices   = $bean->getIndices();
 670                 $engine    = $this->getEngine($bean);
 671                 return $this->createTableSQLParams($tablename, $fieldDefs, $indices, $engine);
 672         }
 673
 674         /**
 675          * Generates sql for create table statement for a bean.
 676          *
 677          * @param  string $tablename
 678          * @param  array  $fieldDefs
 679          * @param  array  $indices
 680          * @param  string $engine optional, MySQL engine to use
 681          * @return string SQL Create Table statement
 682         */
 683         public function createTableSQLParams($tablename, $fieldDefs, $indices, $engine = null)
 684         {
 685                 if ( empty($engine) && isset($fieldDefs['engine']))
 686                         $engine = $fieldDefs['engine'];
 687                 if ( !$this->isEngineEnabled($engine) )
 688                         $engine = '';
 689
 690                 $columns = $this->columnSQLRep($fieldDefs, false, $tablename);
 691                 if (empty($columns))
 692                         return false;
 693
 694                 $keys = $this->keysSQL($indices);
 695                 if (!empty($keys))
 696                         $keys = ",$keys";
 697
 698                 // cn: bug 9873 - module tables do not get created in utf8 with assoc collation
 699                 $collation = $this->getOption('collation');
 700                 if(empty($collation)) {
 701                     $collation = 'utf8_general_ci';
 702                 }
 703                 $sql = "CREATE TABLE $tablename ($columns $keys) CHARACTER SET utf8 COLLATE $collation";
 704
 705                 if (!empty($engine))
 706                         $sql.= " ENGINE=$engine";
 707
 708                 return $sql;
 709         }
 710
 711         /**
 712          * @see DBManager::oneColumnSQLRep()
 713          */
 714         protected function oneColumnSQLRep($fieldDef, $ignoreRequired = false, $table = '', $return_as_array = false)
 715         {
 716                 // always return as array for post-processing
 717                 $ref = parent::oneColumnSQLRep($fieldDef, $ignoreRequired, $table, true);
 718
 719                 if ( $ref['colType'] == 'int' && !empty($fieldDef['len']) ) {
 720                         $ref['colType'] .= "(".$fieldDef['len'].")";
 721                 }
 722
 723                 // bug 22338 - don't set a default value on text or blob fields
 724                 if ( isset($ref['default']) &&
 725                         ($ref['colType'] == 'text' || $ref['colType'] == 'blob'
 726                                 || $ref['colType'] == 'longtext' || $ref['colType'] == 'longblob' ))
 727                         $ref['default'] = '';
 728
 729                 if ( $return_as_array )
 730                         return $ref;
 731                 else
 732                         return "{$ref['name']} {$ref['colType']} {$ref['default']} {$ref['required']} {$ref['auto_increment']}";
 733         }
 734
 735         /**
 736          * @see DBManager::changeColumnSQL()
 737          */
 738         protected function changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired = false)
 739         {
 740                 $columns = array();
 741                 if ($this->isFieldArray($fieldDefs)){
 742                         foreach ($fieldDefs as $def){
 743                                 if ($action == 'drop')
 744                                         $columns[] = $def['name'];
 745                                 else
 746                                         $columns[] = $this->oneColumnSQLRep($def, $ignoreRequired);
 747                         }
 748                 } else {
 749                         if ($action == 'drop')
 750                                 $columns[] = $fieldDefs['name'];
 751                 else
 752                         $columns[] = $this->oneColumnSQLRep($fieldDefs);
 753                 }
 754
 755                 return "ALTER TABLE $tablename $action COLUMN ".implode(",$action column ", $columns);
 756         }
 757
 758         /**
 759          * Generates SQL for key specification inside CREATE TABLE statement
 760          *
 761          * The passes array is an array of field definitions or a field definition
 762          * itself. The keys generated will be either primary, foreign, unique, index
 763          * or none at all depending on the setting of the "key" parameter of a field definition
 764          *
 765          * @param  array  $indices
 766          * @param  bool   $alter_table
 767          * @param  string $alter_action
 768          * @return string SQL Statement
 769          */
 770         protected function keysSQL($indices, $alter_table = false, $alter_action = '')
 771         {
 772         // check if the passed value is an array of fields.
 773         // if not, convert it into an array
 774         if (!$this->isFieldArray($indices))
 775                 $indices[] = $indices;
 776
 777         $columns = array();
 778         foreach ($indices as $index) {
 779                 if(!empty($index['db']) && $index['db'] != $this->dbType)
 780                         continue;
 781                 if (isset($index['source']) && $index['source'] != 'db')
 782                         continue;
 783
 784                 $type = $index['type'];
 785                 $name = $index['name'];
 786
 787                 if (is_array($index['fields']))
 788                         $fields = implode(", ", $index['fields']);
 789                 else
 790                         $fields = $index['fields'];
 791
 792                 switch ($type) {
 793                 case 'unique':
 794                         $columns[] = " UNIQUE $name ($fields)";
 795                         break;
 796                 case 'primary':
 797                         $columns[] = " PRIMARY KEY ($fields)";
 798                         break;
 799                 case 'index':
 800                 case 'foreign':
 801                 case 'clustered':
 802                 case 'alternate_key':
 803                         /**
 804                                 * @todo here it is assumed that the primary key of the foreign
 805                                 * table will always be named 'id'. It must be noted though
 806                                 * that this can easily be fixed by referring to db dictionary
 807                                 * to find the correct primary field name
 808                                 */
 809                         if ( $alter_table )
 810                                 $columns[] = " INDEX $name ($fields)";
 811                         else
 812                                 $columns[] = " KEY $name ($fields)";
 813                         break;
 814                 case 'fulltext':
 815                         if ($this->full_text_indexing_installed())
 816                                 $columns[] = " FULLTEXT ($fields)";
 817                         else
 818                                 $GLOBALS['log']->debug('MYISAM engine is not available/enabled, full-text indexes will be skipped. Skipping:',$name);
 819                         break;
 820                 }
 821         }
 822         $columns = implode(", $alter_action ", $columns);
 823         if(!empty($alter_action)){
 824                 $columns = $alter_action . ' '. $columns;
 825         }
 826         return $columns;
 827         }
 828
 829         /**
 830          * @see DBManager::setAutoIncrement()
 831          */
 832         protected function setAutoIncrement($table, $field_name)
 833         {
 834                 return "auto_increment";
 835         }
 836
 837         /**
 838          * Sets the next auto-increment value of a column to a specific value.
 839          *
 840          * @param  string $table tablename
 841          * @param  string $field_name
 842          */
 843         public function setAutoIncrementStart($table, $field_name, $start_value)
 844         {
 845                 $start_value = (int)$start_value;
 846                 return $this->query( "ALTER TABLE $table AUTO_INCREMENT = $start_value;");
 847         }
 848
 849         /**
 850          * Returns the next value for an auto increment
 851          *
 852          * @param  string $table tablename
 853          * @param  string $field_name
 854          * @return string
 855          */
 856         public function getAutoIncrement($table, $field_name)
 857         {
 858                 $result = $this->query("SHOW TABLE STATUS LIKE '$table'");
 859                 $row = $this->fetchByAssoc($result);
 860                 if (!empty($row['Auto_increment']))
 861                         return $row['Auto_increment'];
 862
 863                 return "";
 864         }
 865
 866         /**
 867          * @see DBManager::get_indices()
 868          */
 869         public function get_indices($tablename)
 870         {
 871                 //find all unique indexes and primary keys.
 872                 $result = $this->query("SHOW INDEX FROM $tablename");
 873
 874                 $indices = array();
 875                 while (($row=$this->fetchByAssoc($result)) !=null) {
 876                         $index_type='index';
 877                         if ($row['Key_name'] =='PRIMARY') {
 878                                 $index_type='primary';
 879                         }
 880                         elseif ( $row['Non_unique'] == '0' ) {
 881                                 $index_type='unique';
 882                         }
 883                         $name = strtolower($row['Key_name']);
 884                         $indices[$name]['name']=$name;
 885                         $indices[$name]['type']=$index_type;
 886                         $indices[$name]['fields'][]=strtolower($row['Column_name']);
 887                 }
 888                 return $indices;
 889         }
 890
 891         /**
 892          * @see DBManager::add_drop_constraint()
 893          */
 894         public function add_drop_constraint($table, $definition, $drop = false)
 895         {
 896                 $type         = $definition['type'];
 897                 $fields       = implode(',',$definition['fields']);
 898                 $name         = $definition['name'];
 899                 $sql          = '';
 900
 901                 switch ($type){
 902                 // generic indices
 903                 case 'index':
 904                 case 'alternate_key':
 905                 case 'clustered':
 906                         if ($drop)
 907                                 $sql = "ALTER TABLE {$table} DROP INDEX {$name} ";
 908                         else
 909                                 $sql = "ALTER TABLE {$table} ADD INDEX {$name} ({$fields})";
 910                         break;
 911                 // constraints as indices
 912                 case 'unique':
 913                         if ($drop)
 914                                 $sql = "ALTER TABLE {$table} DROP INDEX $name";
 915                         else
 916                                 $sql = "ALTER TABLE {$table} ADD CONSTRAINT UNIQUE {$name} ({$fields})";
 917                         break;
 918                 case 'primary':
 919                         if ($drop)
 920                                 $sql = "ALTER TABLE {$table} DROP PRIMARY KEY";
 921                         else
 922                                 $sql = "ALTER TABLE {$table} ADD CONSTRAINT PRIMARY KEY ({$fields})";
 923                         break;
 924                 case 'foreign':
 925                         if ($drop)
 926                                 $sql = "ALTER TABLE {$table} DROP FOREIGN KEY ({$fields})";
 927                         else
 928                                 $sql = "ALTER TABLE {$table} ADD CONSTRAINT FOREIGN KEY {$name} ({$fields}) REFERENCES {$definition['foreignTable']}({$definition['foreignField']})";
 929                         break;
 930                 }
 931                 return $sql;
 932         }
 933
 934         /**
 935          * Runs a query and returns a single row
 936          *
 937          * @param  string   $sql        SQL Statement to execute
 938          * @param  bool     $dieOnError True if we want to call die if the query returns errors
 939          * @param  string   $msg        Message to log if error occurs
 940          * @param  bool     $suppress   Message to log if error occurs
 941          * @return array    single row from the query
 942          */
 943         public function fetchOne($sql, $dieOnError = false, $msg = '', $suppress = false)
 944         {
 945                 if(stripos($sql, ' LIMIT ') === false) {
 946                         // little optimization to just fetch one row
 947                         $sql .= " LIMIT 0,1";
 948                 }
 949                 return parent::fetchOne($sql, $dieOnError, $msg, $suppress);
 950         }
 951
 952         /**
 953          * @see DBManager::full_text_indexing_installed()
 954          */
 955         public function full_text_indexing_installed($dbname = null)
 956         {
 957                 return $this->isEngineEnabled('MyISAM');
 958         }
 959
 960         /**
 961          * @see DBManager::massageFieldDef()
 962          */
 963         public function massageFieldDef(&$fieldDef, $tablename)
 964         {
 965                 parent::massageFieldDef($fieldDef,$tablename);
 966
 967                 if ( isset($fieldDef['default']) &&
 968                         ($fieldDef['dbType'] == 'text'
 969                                 || $fieldDef['dbType'] == 'blob'
 970                                 || $fieldDef['dbType'] == 'longtext'
 971                                 || $fieldDef['dbType'] == 'longblob' ))
 972                         unset($fieldDef['default']);
 973                 if ($fieldDef['dbType'] == 'uint')
 974                         $fieldDef['len'] = '10';
 975                 if ($fieldDef['dbType'] == 'ulong')
 976                         $fieldDef['len'] = '20';
 977                 if ($fieldDef['dbType'] == 'bool')
 978                         $fieldDef['type'] = 'tinyint';
 979                 if ($fieldDef['dbType'] == 'bool' && empty($fieldDef['default']) )
 980                         $fieldDef['default'] = '0';
 981                 if (($fieldDef['dbType'] == 'varchar' || $fieldDef['dbType'] == 'enum') && empty($fieldDef['len']) )
 982                         $fieldDef['len'] = '255';
 983                 if ($fieldDef['dbType'] == 'uint')
 984                         $fieldDef['len'] = '10';
 985                 if ($fieldDef['dbType'] == 'int' && empty($fieldDef['len']) )
 986                         $fieldDef['len'] = '11';
 987
 988                 if($fieldDef['dbType'] == 'decimal') {
 989                         if(isset($fieldDef['len'])) {
 990                                 if(strstr($fieldDef['len'], ",") === false) {
 991                                         $fieldDef['len'] .= ",0";
 992                                 }
 993                         } else {
 994                                 $fieldDef['len']  = '10,0';
 995                         }
 996                 }
 997         }
 998
 999         /**
1000          * Generates SQL for dropping a table.
1001          *
1002          * @param  string $name table name
1003          * @return string SQL statement
1004          */
1005         public function dropTableNameSQL($name)
1006         {
1007                 return "DROP TABLE IF EXISTS ".$name;
1008         }
1009
1010         public function dropIndexes($tablename, $indexes, $execute = true)
1011         {
1012                 $sql = array();
1013                 foreach ($indexes as $index) {
1014                         $name =$index['name'];
1015                         if($execute) {
1016                         unset(self::$index_descriptions[$tablename][$name]);
1017                         }
1018                         if ($index['type'] == 'primary') {
1019                                 $sql[] = 'DROP PRIMARY KEY';
1020                         } else {
1021                                 $sql[] = "DROP INDEX $name";
1022                         }
1023                 }
1024                 if (!empty($sql)) {
1025                         $sql = "ALTER TABLE $tablename ".join(",", $sql);
1026                         if($execute)
1027                                 $this->query($sql);
1028                 } else {
1029                         $sql = '';
1030                 }
1031                 return $sql;
1032         }
1033
1034         /**
1035          * List of available collation settings
1036          * @return string
1037          */
1038         public function getDefaultCollation()
1039         {
1040                 return "utf8_general_ci";
1041         }
1042
1043         /**
1044          * List of available collation settings
1045          * @return array
1046          */
1047         public function getCollationList()
1048         {
1049                 $q = "SHOW COLLATION LIKE 'utf8%'";
1050                 $r = $this->query($q);
1051                 $res = array();
1052                 while($a = $this->fetchByAssoc($r)) {
1053                         $res[] = $a['Collation'];
1054                 }
1055                 return $res;
1056         }
1057
1058         /**
1059          * (non-PHPdoc)
1060          * @see DBManager::renameColumnSQL()
1061          */
1062         public function renameColumnSQL($tablename, $column, $newname)
1063         {
1064                 $field = $this->describeField($column, $tablename);
1065                 $field['name'] = $newname;
1066                 return "ALTER TABLE $tablename CHANGE COLUMN $column ".$this->oneColumnSQLRep($field);
1067         }
1068
1069         public function emptyValue($type)
1070         {
1071                 $ctype = $this->getColumnType($type);
1072                 if($ctype == "datetime") {
1073                         return $this->convert($this->quoted("0000-00-00 00:00:00"), "datetime");
1074                 }
1075                 if($ctype == "date") {
1076                         return $this->convert($this->quoted("0000-00-00"), "date");
1077                 }
1078                 if($ctype == "time") {
1079                         return $this->convert($this->quoted("00:00:00"), "time");
1080                 }
1081                 return parent::emptyValue($type);
1082         }
1083
1084         /**
1085          * (non-PHPdoc)
1086          * @see DBManager::lastDbError()
1087          */
1088         public function lastDbError()
1089         {
1090                 if($this->database) {
1091                     if(mysql_errno($this->database)) {
1092                             return "MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database);
1093                     }
1094                 } else {
1095                         $err =  mysql_error();
1096                         if($err) {
1097                             return $err;
1098                         }
1099                 }
1100
1101     }
1102
1103         /**
1104          * Quote MySQL search term
1105          * @param unknown_type $term
1106          */
1107         protected function quoteTerm($term)
1108         {
1109                 if(strpos($term, ' ') !== false) {
1110                         return '"'.$term.'"';
1111                 }
1112                 return $term;
1113         }
1114
1115         /**
1116          * Generate fulltext query from set of terms
1117          * @param string $fields Field to search against
1118          * @param array $terms Search terms that may be or not be in the result
1119          * @param array $must_terms Search terms that have to be in the result
1120          * @param array $exclude_terms Search terms that have to be not in the result
1121          */
1122         public function getFulltextQuery($field, $terms, $must_terms = array(), $exclude_terms = array())
1123         {
1124                 $condition = array();
1125                 foreach($terms as $term) {
1126                         $condition[] = $this->quoteTerm($term);
1127                 }
1128                 foreach($must_terms as $term) {
1129                         $condition[] = "+".$this->quoteTerm($term);
1130                 }
1131                 foreach($exclude_terms as $term) {
1132                         $condition[] = "-".$this->quoteTerm($term);
1133                 }
1134                 $condition = $this->quoted(join(" ",$condition));
1135                 return "MATCH($field) AGAINST($condition IN BOOLEAN MODE)";
1136         }
1137
1138         /**
1139          * Get list of all defined charsets
1140          * @return array
1141          */
1142         protected function getCharsetInfo()
1143         {
1144                 $charsets = array();
1145                 $res = $this->query("show variables like 'character\\_set\\_%'");
1146                 while($row = $this->fetchByAssoc($res)) {
1147                         $charsets[$row['Variable_name']] = $row['Value'];
1148                 }
1149                 return $charsets;
1150         }
1151
1152         public function getDbInfo()
1153         {
1154                 $charsets = $this->getCharsetInfo();
1155                 $charset_str = array();
1156                 foreach($charsets as $name => $value) {
1157                         $charset_str[] = "$name = $value";
1158                 }
1159                 return array(
1160                         "MySQL Version" => @mysql_get_client_info(),
1161                         "MySQL Host Info" => @mysql_get_host_info($this->database),
1162                         "MySQL Server Info" => @mysql_get_server_info($this->database),
1163                         "MySQL Client Encoding" =>  @mysql_client_encoding($this->database),
1164                         "MySQL Character Set Settings" => join(", ", $charset_str),
1165                 );
1166         }
1167
1168         public function validateQuery($query)
1169         {
1170                 $res = $this->getOne("EXPLAIN $query");
1171                 return !empty($res);
1172         }
1173
1174         protected function makeTempTableCopy($table)
1175         {
1176                 $this->log->debug("creating temp table for [$table]...");
1177                 $create = $this->getOne("SHOW CREATE TABLE {$table}");
1178                 if(empty($create)) {
1179                         return false;
1180                 }
1181                 // rewrite DDL with _temp name
1182                 $tempTableQuery = str_replace("CREATE TABLE `{$table}`", "CREATE TABLE `{$table}__uw_temp`", $create);
1183                 $r2 = $this->query($tempTableQuery);
1184                 if(empty($r2)) {
1185                         return false;
1186                 }
1187
1188                 // get sample data into the temp table to test for data/constraint conflicts
1189                 $this->log->debug('inserting temp dataset...');
1190                 $q3 = "INSERT INTO `{$table}__uw_temp` SELECT * FROM `{$table}` LIMIT 10";
1191                 $this->query($q3, false, "Preflight Failed for: {$q3}");
1192                 return true;
1193         }
1194
1195         /**
1196          * Tests an ALTER TABLE query
1197          * @param string table The table name to get DDL
1198          * @param string query The query to test.
1199          * @return string Non-empty if error found
1200          */
1201         protected function verifyAlterTable($table, $query)
1202         {
1203                 $this->log->debug("verifying ALTER TABLE");
1204                 // Skipping ALTER TABLE [table] DROP PRIMARY KEY because primary keys are not being copied
1205                 // over to the temp tables
1206                 if(strpos(strtoupper($query), 'DROP PRIMARY KEY') !== false) {
1207                         $this->log->debug("Skipping DROP PRIMARY KEY");
1208                         return '';
1209                 }
1210                 if(!$this->makeTempTableCopy($table)) {
1211                         return 'Could not create temp table copy';
1212                 }
1213
1214                 // test the query on the test table
1215                 $this->log->debug('testing query: ['.$query.']');
1216                 $tempTableTestQuery = str_replace("ALTER TABLE `{$table}`", "ALTER TABLE `{$table}__uw_temp`", $query);
1217                 if (strpos($tempTableTestQuery, 'idx') === false) {
1218                         if(strpos($tempTableTestQuery, '__uw_temp') === false) {
1219                                 return 'Could not use a temp table to test query!';
1220                         }
1221
1222                         $this->log->debug('testing query on temp table: ['.$tempTableTestQuery.']');
1223                         $this->query($tempTableTestQuery, false, "Preflight Failed for: {$query}");
1224                 } else {
1225                         // test insertion of an index on a table
1226                         $tempTableTestQuery_idx = str_replace("ADD INDEX `idx_", "ADD INDEX `temp_idx_", $tempTableTestQuery);
1227                         $this->log->debug('testing query on temp table: ['.$tempTableTestQuery_idx.']');
1228                         $this->query($tempTableTestQuery_idx, false, "Preflight Failed for: {$query}");
1229                 }
1230                 $mysqlError = $this->getL();
1231                 if(!empty($mysqlError)) {
1232                         return $mysqlError;
1233                 }
1234                 $this->dropTableName("{$table}__uw_temp");
1235
1236                 return '';
1237         }
1238
1239         protected function verifyGenericReplaceQuery($querytype, $table, $query)
1240         {
1241                 $this->log->debug("verifying $querytype statement");
1242
1243                 if(!$this->makeTempTableCopy($table)) {
1244                         return 'Could not create temp table copy';
1245                 }
1246                 // test the query on the test table
1247                 $this->log->debug('testing query: ['.$query.']');
1248                 $tempTableTestQuery = str_replace("$querytype `{$table}`", "$querytype `{$table}__uw_temp`", $query);
1249                 if(strpos($tempTableTestQuery, '__uw_temp') === false) {
1250                         return 'Could not use a temp table to test query!';
1251                 }
1252
1253                 $this->query($tempTableTestQuery, false, "Preflight Failed for: {$query}");
1254                 $error = $this->lastError(); // empty on no-errors
1255                 $this->dropTableName("{$table}__uw_temp"); // just in case
1256                 return $error;
1257         }
1258
1259         /**
1260          * Tests a DROP TABLE query
1261          * @param string table The table name to get DDL
1262          * @param string query The query to test.
1263          * @return string Non-empty if error found
1264          */
1265         public function verifyDropTable($table, $query)
1266         {
1267                 return $this->verifyGenericReplaceQuery("DROP TABLE", $table, $query);
1268         }
1269
1270         /**
1271          * Tests an INSERT INTO query
1272          * @param string table The table name to get DDL
1273          * @param string query The query to test.
1274          * @return string Non-empty if error found
1275          */
1276         public function verifyInsertInto($table, $query)
1277         {
1278                 return $this->verifyGenericReplaceQuery("INSERT INTO", $table, $query);
1279         }
1280
1281         /**
1282          * Tests an UPDATE query
1283          * @param string table The table name to get DDL
1284          * @param string query The query to test.
1285          * @return string Non-empty if error found
1286          */
1287         public function verifyUpdate($table, $query)
1288         {
1289                 return $this->verifyGenericReplaceQuery("UPDATE", $table, $query);
1290         }
1291
1292         /**
1293          * Tests an DELETE FROM query
1294          * @param string table The table name to get DDL
1295          * @param string query The query to test.
1296          * @return string Non-empty if error found
1297          */
1298         public function verifyDeleteFrom($table, $query)
1299         {
1300                 return $this->verifyGenericReplaceQuery("DELETE FROM", $table, $query);
1301         }
1302
1303         /**
1304          * Check if certain database exists
1305          * @param string $dbname
1306          */
1307         public function dbExists($dbname)
1308         {
1309                 $db = $this->getOne("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ".$this->quoted($dbname));
1310                 return !empty($db);
1311         }
1312
1313         /**
1314          * Select database
1315          * @param string $dbname
1316          */
1317         protected function selectDb($dbname)
1318         {
1319                 return mysql_select_db($dbname);
1320         }
1321
1322         /**
1323          * Check if certain DB user exists
1324          * @param string $username
1325          */
1326         public function userExists($username)
1327         {
1328                 $db = $this->getOne("SELECT DATABASE()");
1329                 if(!$this->selectDb("mysql")) {
1330                         return false;
1331                 }
1332                 $user = $this->getOne("select count(*) from user where user = ".$this->quoted($username));
1333                 if(!$this->selectDb($db)) {
1334                         $this->checkError("Cannot select database $db", true);
1335                 }
1336                 return !empty($user);
1337         }
1338
1339         /**
1340          * Create DB user
1341          * @param string $database_name
1342          * @param string $host_name
1343          * @param string $user
1344          * @param string $password
1345          */
1346         public function createDbUser($database_name, $host_name, $user, $password)
1347         {
1348                 $qpassword = $this->quote($password);
1349                 $this->query("GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX
1350                                                         ON `$database_name`.*
1351                                                         TO \"$user\"@\"$host_name\"
1352                                                         IDENTIFIED BY '{$qpassword}';", true);
1353
1354                 $this->query("SET PASSWORD FOR \"{$user}\"@\"{$host_name}\" = old_password('{$qpassword}');", true);
1355                 if($host_name != 'localhost') {
1356                         $this->createDbUser($database_name, "localhost", $user, $password);
1357                 }
1358         }
1359
1360         /**
1361          * Create a database
1362          * @param string $dbname
1363          */
1364         public function createDatabase($dbname)
1365         {
1366                 $this->query("CREATE DATABASE `$dbname` CHARACTER SET utf8 COLLATE utf8_general_ci", true);
1367         }
1368
1369         public function preInstall()
1370         {
1371                 $db->query("ALTER DATABASE `{$setup_db_database_name}` DEFAULT CHARACTER SET utf8", true);
1372                 $db->query("ALTER DATABASE `{$setup_db_database_name}` DEFAULT COLLATE utf8_general_ci", true);
1373
1374         }
1375
1376         /**
1377          * Drop a database
1378          * @param string $dbname
1379          */
1380         public function dropDatabase($dbname)
1381         {
1382                 return $this->query("DROP DATABASE IF EXISTS `$dbname`", true);
1383         }
1384
1385         /**
1386          * Check if this driver can be used
1387          * @return bool
1388          */
1389         public function valid()
1390         {
1391                 return function_exists("mysql_connect");
1392         }
1393
1394         /**
1395          * Check DB version
1396          * @see DBManager::canInstall()
1397          */
1398         public function canInstall()
1399         {
1400                 $db_version = $this->version();
1401                 if(empty($db_version)) {
1402                         return array('ERR_DB_VERSION_FAILURE');
1403                 }
1404                 if(version_compare($db_version, '4.1.2') < 0) {
1405                         return array('ERR_DB_MYSQL_VERSION', $db_version);
1406                 }
1407                 return true;
1408         }
1409
1410         public function installConfig()
1411         {
1412                 return array(
1413                         'LBL_DBCONFIG_MSG3' =>  array(
1414                                 "setup_db_database_name" => array("label" => 'LBL_DBCONF_DB_NAME', "required" => true),
1415                         ),
1416                         'LBL_DBCONFIG_MSG2' =>  array(
1417                                 "setup_db_host_name" => array("label" => 'LBL_DBCONF_HOST_NAME', "required" => true),
1418                         ),
1419                         'LBL_DBCONF_TITLE_USER_INFO' => array(),
1420                         'LBL_DBCONFIG_B_MSG1' => array(
1421                                 "setup_db_admin_user_name" => array("label" => 'LBL_DBCONF_DB_ADMIN_USER', "required" => true),
1422                                 "setup_db_admin_password" => array("label" => 'LBL_DBCONF_DB_ADMIN_PASSWORD', "type" => "password"),
1423                         )
1424                 );
1425         }
1426 }