include/database/DBManager.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  * Base database driver implementation
  93  * @api
  94  */
  95 abstract class DBManager
  96 {
  97         /**
  98          * Name of database
  99          * @var resource
 100          */
 101         public $database = null;
 102
 103         /**
 104          * Indicates whether we should die when we get an error from the DB
 105          */
 106         protected $dieOnError = false;
 107
 108         /**
 109          * Indicates whether we should html encode the results from a query by default
 110          */
 111         protected $encode = true;
 112
 113         /**
 114          * Records the execution time of the last query
 115          */
 116         protected $query_time = 0;
 117
 118         /**
 119          * Last error message from the DB backend
 120          */
 121         protected $last_error = false;
 122
 123         /**
 124          * Registry of available result sets
 125          */
 126         protected $lastResult;
 127
 128         /**
 129          * Current query count
 130          */
 131         private static $queryCount = 0;
 132
 133         /**
 134          * Query threshold limit
 135          */
 136         private static $queryLimit = 0;
 137
 138         /**
 139          * Array of prepared statements and their correspoding parsed tokens
 140          */
 141         protected $preparedTokens = array();
 142
 143         /**
 144          * TimeDate instance
 145          * @var TimeDate
 146          */
 147         protected $timedate;
 148
 149         /**
 150          * PHP Logger
 151          * @var Logger
 152          */
 153         protected $log;
 154
 155         /**
 156          * Table descriptions
 157          * @var array
 158          */
 159         protected static $table_descriptions = array();
 160
 161         /**
 162          * Index descriptions
 163          * @var array
 164          */
 165         protected static $index_descriptions = array();
 166
 167         /**
 168          * Maximum length of identifiers
 169          * @abstract
 170          * @var array
 171          */
 172         protected $maxNameLengths = array(
 173                 'table' => 64,
 174                 'column' => 64,
 175                 'index' => 64,
 176                 'alias' => 64
 177         );
 178
 179         /**
 180          * DB driver priority
 181          * Higher priority drivers override lower priority ones
 182          * @var int
 183          */
 184         public $priority = 0;
 185
 186         /**
 187          * Driver name label, for install
 188          * @absrtact
 189          * @var string
 190          */
 191         public $label = '';
 192
 193         /**
 194          * Type names map
 195          * @abstract
 196          * @var array
 197          */
 198         protected $type_map = array();
 199
 200         /**
 201          * Type classification into:
 202          * - int
 203          * - bool
 204          * - float
 205          * - date
 206          * @abstract
 207          * @var array
 208          */
 209         protected $type_class = array(
 210                         'int'      => 'int',
 211                         'double'   => 'float',
 212                         'float'    => 'float',
 213                         'uint'     => 'int',
 214                         'ulong'    => 'bigint',
 215                         'long'     => 'bigint',
 216                         'short'    => 'int',
 217                         'date'     => 'date',
 218                         'datetime' => 'date',
 219                         'datetimecombo' => 'date',
 220                         'time'     => 'time',
 221                         'bool'     => 'bool',
 222                         'tinyint'  => 'int',
 223                         'currency' => 'float',
 224                         'decimal'  => 'float',
 225                         'decimal2' => 'float',
 226         );
 227
 228         /**
 229          * Capabilities this DB supports. Supported list:
 230          * affected_rows        Can report query affected rows for UPDATE/DELETE
 231          *                                      implement getAffectedRowCount()
 232          * select_rows          Can report row count for SELECT
 233          *                                      implement getRowCount()
 234          * case_sensitive       Supports case-sensitive text columns
 235          * fulltext                     Supports fulltext search indexes
 236          * inline_keys          Supports defining keys together with the table
 237          * auto_increment_sequence Autoincrement support implemented as sequence
 238          * limit_subquery   Supports LIMIT clauses in subqueries
 239          * create_user          Can create users for Sugar
 240          * create_db            Can create databases
 241          * collation            Supports setting collations
 242          * disable_keys     Supports temporarily disabling keys (for upgrades, etc.)
 243          *
 244          * @abstract
 245          * Special cases:
 246          * fix:expandDatabase - needs expandDatabase fix, see expandDatabase.php
 247          * TODO: verify if we need these cases
 248          */
 249         protected $capabilities = array();
 250
 251         /**
 252          * Database options
 253          * @var array
 254          */
 255         protected $options = array();
 256
 257     /**
 258      * Create DB Driver
 259      */
 260         public function __construct()
 261         {
 262                 $this->timedate = TimeDate::getInstance();
 263                 $this->log = $GLOBALS['log'];
 264                 $this->helper = $this; // compatibility
 265         }
 266
 267     /**
 268      * Wrapper for those trying to access the private and protected class members directly
 269      * @param string $p var name
 270      * @return mixed
 271      */
 272         public function __get($p)
 273         {
 274                 $this->log->info('Call to DBManager::$'.$p.' is deprecated');
 275                 return $this->$p;
 276         }
 277
 278         /**
 279          * Returns the current database handle
 280          * @return resource
 281          */
 282         public function getDatabase()
 283         {
 284                 $this->checkConnection();
 285                 return $this->database;
 286         }
 287
 288         /**
 289          * Returns this instance's DBHelper
 290          * Actually now returns $this
 291          * @deprecated
 292          * @return DBManager
 293          */
 294         public function getHelper()
 295         {
 296                 return $this;
 297         }
 298
 299         /**
 300          * Checks for error happening in the database
 301          *
 302          * @param  string $msg        message to prepend to the error message
 303          * @param  bool   $dieOnError true if we want to die immediately on error
 304          * @return bool True if there was an error
 305          */
 306         public function checkError($msg = '', $dieOnError = false)
 307         {
 308                 if (empty($this->database)) {
 309                         $this->registerError("$msg: Database Is Not Connected", $dieOnError);
 310                         return true;
 311                 }
 312
 313                 $dberror = $this->lastDbError();
 314                 if($dberror === false) {
 315                 $this->last_error = false;
 316                 return false;
 317                 }
 318                 $this->registerError($msg, $dberror, $dieOnError);
 319         return true;
 320         }
 321
 322         /**
 323          * Register database error
 324          * If die-on-error flag is set, logs the message and dies,
 325          * otherwise sets last_error to the message
 326          * @param string $userMessage Message from function user
 327          * @param string $message Message from SQL driver
 328          * @param bool $dieOnError
 329          */
 330         protected function registerError($userMessage, $message, $dieOnError = false)
 331         {
 332                 if(!empty($message)) {
 333                         if(!empty($userMessage)) {
 334                                 $message = "$userMessage: $message";
 335                         }
 336                         if(empty($message)) {
 337                             $message = "Database error";
 338                         }
 339                         $this->log->fatal($message);
 340                         if ($dieOnError || $this->dieOnError) {
 341                                 if(isset($GLOBALS['app_strings']['ERR_DB_FAIL'])) {
 342                                         sugar_die($GLOBALS['app_strings']['ERR_DB_FAIL']);
 343                                 } else {
 344                                         sugar_die("Database error. Please check sugarcrm.log for details.");
 345                                 }
 346                         } else {
 347                                 $this->last_error = $message;
 348                         }
 349                 }
 350         }
 351
 352         /**
 353          * Return DB error message for the last query executed
 354          * @return string Last error message
 355          */
 356         public function lastError()
 357         {
 358                 return $this->last_error;
 359         }
 360
 361         /**
 362          * This method is called by every method that runs a query.
 363          * If slow query dumping is turned on and the query time is beyond
 364          * the time limit, we will log the query. This function may do
 365          * additional reporting or log in a different area in the future.
 366          *
 367          * @param  string  $query query to log
 368          * @return boolean true if the query was logged, false otherwise
 369          */
 370         protected function dump_slow_queries($query)
 371         {
 372                 global $sugar_config;
 373
 374                 $do_the_dump = isset($sugar_config['dump_slow_queries'])
 375                         ? $sugar_config['dump_slow_queries'] : false;
 376                 $slow_query_time_msec = isset($sugar_config['slow_query_time_msec'])
 377                         ? $sugar_config['slow_query_time_msec'] : 5000;
 378
 379                 if($do_the_dump) {
 380                         if($slow_query_time_msec < ($this->query_time * 1000)) {
 381                                 // Then log both the query and the query time
 382                                 $this->log->fatal('Slow Query (time:'.$this->query_time."\n".$query);
 383                                 return true;
 384                         }
 385                 }
 386                 return false;
 387         }
 388
 389 /**
 390         * Scans order by to ensure that any field being ordered by is.
 391         *
 392         * It will throw a warning error to the log file - fatal if slow query logging is enabled
 393         *
 394         * @param  string $sql         query to be run
 395         * @param  bool   $object_name optional, object to look up indices in
 396         * @return bool   true if an index is found false otherwise
 397         */
 398 protected function checkQuery($sql, $object_name = false)
 399 {
 400         $match = array();
 401         preg_match_all("'.* FROM ([^ ]*).* ORDER BY (.*)'is", $sql, $match);
 402         $indices = false;
 403         if (!empty($match[1][0]))
 404                 $table = $match[1][0];
 405         else
 406                 return false;
 407
 408         if (!empty($object_name) && !empty($GLOBALS['dictionary'][$object_name]))
 409                 $indices = $GLOBALS['dictionary'][$object_name]['indices'];
 410
 411         if (empty($indices)) {
 412                 foreach ( $GLOBALS['dictionary'] as $current ) {
 413                         if ($current['table'] == $table){
 414                                 $indices = $current['indices'];
 415                                 break;
 416                         }
 417                 }
 418         }
 419         if (empty($indices)) {
 420                 $this->log->warn('CHECK QUERY: Could not find index definitions for table ' . $table);
 421                 return false;
 422         }
 423         if (!empty($match[2][0])) {
 424                 $orderBys = explode(' ', $match[2][0]);
 425                 foreach ($orderBys as $orderBy){
 426                         $orderBy = trim($orderBy);
 427                         if (empty($orderBy))
 428                                 continue;
 429                         $orderBy = strtolower($orderBy);
 430                         if ($orderBy == 'asc' || $orderBy == 'desc')
 431                                 continue;
 432
 433                         $orderBy = str_replace(array($table . '.', ','), '', $orderBy);
 434
 435                         foreach ($indices as $index)
 436                                 if (empty($index['db']) || $index['db'] == $this->dbType)
 437                                         foreach ($index['fields'] as $field)
 438                                                 if ($field == $orderBy)
 439                                                         return true;
 440
 441                         $warning = 'Missing Index For Order By Table: ' . $table . ' Order By:' . $orderBy ;
 442                         if (!empty($GLOBALS['sugar_config']['dump_slow_queries']))
 443                                 $this->log->fatal('CHECK QUERY:' .$warning);
 444                         else
 445                                 $this->log->warn('CHECK QUERY:' .$warning);
 446                 }
 447         }
 448         return false;
 449         }
 450
 451         /**
 452          * Returns the time the last query took to execute
 453          *
 454          * @return int
 455          */
 456         public function getQueryTime()
 457         {
 458                 return $this->query_time;
 459         }
 460
 461         /**
 462          * Checks the current connection; if it is not connected then reconnect
 463          */
 464         public function checkConnection()
 465         {
 466                 $this->last_error = '';
 467                 if (!isset($this->database))
 468                         $this->connect();
 469         }
 470
 471         /**
 472          * Sets the dieOnError value
 473          *
 474          * @param bool $value
 475          */
 476         public function setDieOnError($value)
 477         {
 478                 $this->dieOnError = $value;
 479         }
 480
 481     /**
 482      * Implements a generic insert for any bean.
 483      *
 484      * @param SugarBean $bean SugarBean instance
 485      * @return bool query result
 486      *
 487      */
 488         public function insert(SugarBean $bean)
 489         {
 490                 $sql = $this->insertSQL($bean);
 491                 $tablename =  $bean->getTableName();
 492                 $msg = "Error inserting into table: $tablename:";
 493                 return $this->query($sql,true,$msg);
 494         }
 495
 496         /**
 497          * Insert data into table by parameter definition
 498          * @param string $table Table name
 499          * @param array $field_defs Definitions in vardef-like format
 500          * @param array $data Key/value to insert
 501          * @param array $field_map Fields map from SugarBean
 502          * @param bool $execute Execute or return query?
 503      * @return bool query result
 504      */
 505         public function insertParams($table, $field_defs, $data, $field_map = null, $execute = true)
 506         {
 507                 $values = array();
 508                 foreach ($field_defs as $field => $fieldDef)
 509                 {
 510                         if (isset($fieldDef['source']) && $fieldDef['source'] != 'db')  continue;
 511                         //custom fields handle there save seperatley
 512                         if(!empty($field_map) && !empty($field_map[$field]['custom_type'])) continue;
 513
 514                         if(isset($data[$field])) {
 515                                 // clean the incoming value..
 516                                 $val = from_html($data[$field]);
 517                         } else {
 518                                 if(isset($fieldDef['default']) && strlen($fieldDef['default']) > 0) {
 519                                         $val = $fieldDef['default'];
 520                                 } else {
 521                                         $val = null;
 522                                 }
 523                         }
 524
 525                         //handle auto increment values here - we may have to do something like nextval for oracle
 526                         if (!empty($fieldDef['auto_increment'])) {
 527                                 $auto = $this->getAutoIncrementSQL($table, $fieldDef['name']);
 528                                 if(!empty($auto)) {
 529                                         $values[$field] = $auto;
 530                                 }
 531                         } elseif ($fieldDef['name'] == 'deleted') {
 532                                 $values['deleted'] = (int)$val;
 533                         } else {
 534                                 // need to do some thing about types of values
 535                                 if(!is_null($val) || !empty($fieldDef['required'])) {
 536                                         $values[$field] = $this->massageValue($val, $fieldDef);
 537                                 }
 538                         }
 539                 }
 540
 541                 if (empty($values))
 542                         return $execute?true:''; // no columns set
 543
 544                 // get the entire sql
 545                 $query = "INSERT INTO $table (".implode(",", array_keys($values)).")
 546                                         VALUES (".implode(",", $values).")";
 547                 return $execute?$this->query($query):$query;
 548         }
 549
 550     /**
 551      * Implements a generic update for any bean
 552      *
 553      * @param SugarBean $bean Sugarbean instance
 554      * @param array $where values with the keys as names of fields.
 555      * If we want to pass multiple values for a name, pass it as an array
 556      * If where is not passed, it defaults to id of table
 557      * @return bool query result
 558      *
 559      */
 560         public function update(SugarBean $bean, array $where = array())
 561         {
 562                 $sql = $this->updateSQL($bean, $where);
 563                 $tablename = $bean->getTableName();
 564                 $msg = "Error updating table: $tablename:";
 565                 return $this->query($sql,true,$msg);
 566         }
 567
 568     /**
 569      * Implements a generic delete for any bean identified by id
 570      *
 571      * @param SugarBean $bean Sugarbean instance
 572      * @param array  $where values with the keys as names of fields.
 573      * If we want to pass multiple values for a name, pass it as an array
 574      * If where is not passed, it defaults to id of table
 575      * @return bool query result
 576      */
 577         public function delete(SugarBean $bean, array $where = array())
 578         {
 579                 $sql = $this->deleteSQL($bean, $where);
 580                 $tableName = $bean->getTableName();
 581                 $msg = "Error deleting from table: ".$tableName. ":";
 582                 return $this->query($sql,true,$msg);
 583         }
 584
 585         /**
 586          * Implements a generic retrieve for any bean identified by id
 587          *
 588          * If we want to pass multiple values for a name, pass it as an array
 589          * If where is not passed, it defaults to id of table
 590          *
 591          * @param  SugarBean   $bean  Sugarbean instance
 592          * @param  array    $where values with the keys as names of fields.
 593          * @return resource result from the query
 594          */
 595         public function retrieve(SugarBean $bean, array $where = array())
 596         {
 597                 $sql = $this->retrieveSQL($bean, $where);
 598                 $tableName = $bean->getTableName();
 599                 $msg = "Error retriving values from table:".$tableName. ":";
 600                 return $this->query($sql,true,$msg);
 601         }
 602
 603         /**
 604          * Implements a generic retrieve for a collection of beans.
 605          *
 606          * These beans will be joined in the sql by the key attribute of field defs.
 607          * Currently, this function does support outer joins.
 608          *
 609          * @param  array $beans Sugarbean instance(s)
 610          * @param  array $cols  columns to be returned with the keys as names of bean as identified by
 611          * get_class of bean. Values of this array is the array of fieldDefs to be returned for a bean.
 612          * If an empty array is passed, all columns are selected.
 613          * @param  array $where  values with the keys as names of bean as identified by get_class of bean
 614          * Each value at the first level is an array of values for that bean identified by name of fields.
 615          * If we want to pass multiple values for a name, pass it as an array
 616          * If where is not passed, all the rows will be returned.
 617          * @return resource
 618          */
 619         public function retrieveView(array $beans, array $cols = array(), array $where = array())
 620         {
 621                 $sql = $this->retrieveViewSQL($beans, $cols, $where);
 622                 $msg = "Error retriving values from View Collection:";
 623                 return $this->query($sql,true,$msg);
 624         }
 625
 626
 627         /**
 628          * Implements creation of a db table for a bean.
 629          *
 630          * NOTE: does not handle out-of-table constraints, use createConstraintSQL for that
 631          * @param SugarBean $bean  Sugarbean instance
 632          */
 633         public function createTable(SugarBean $bean)
 634         {
 635                 $sql = $this->createTableSQL($bean);
 636                 $tablename = $bean->getTableName();
 637                 $msg = "Error creating table: $tablename:";
 638                 $this->query($sql,true,$msg);
 639                 if(!$this->supports("inline_keys")) {
 640                 // handle constraints and indices
 641                         $indicesArr = $this->createConstraintSql($bean);
 642                         if (count($indicesArr) > 0)
 643                                 foreach ($indicesArr as $indexSql)
 644                                         $this->query($indexSql, true, $msg);
 645                 }
 646         }
 647
 648         /**
 649          * returns SQL to create constraints or indices
 650          *
 651          * @param  SugarBean $bean SugarBean instance
 652          * @return array list of SQL statements
 653          */
 654         protected function createConstraintSql(SugarBean $bean)
 655         {
 656                 return $this->getConstraintSql($bean->getIndices(), $bean->getTableName());
 657         }
 658
 659         /**
 660          * Implements creation of a db table
 661          *
 662          * @param string $tablename
 663          * @param array  $fieldDefs  Field definitions, in vardef format
 664          * @param array  $indices    Index definitions, in vardef format
 665          * @param string $engine    Engine parameter, used for MySQL engine so far
 666      * @todo: refactor engine param to be more generic
 667      * @return bool success value
 668      */
 669         public function createTableParams($tablename, $fieldDefs, $indices, $engine = null)
 670         {
 671                 if (!empty($fieldDefs)) {
 672                         $sql = $this->createTableSQLParams($tablename, $fieldDefs, $indices, $engine);
 673                         $res = true;
 674                         if ($sql) {
 675                                 $msg = "Error creating table: $tablename";
 676                                 $res = ($res and $this->query($sql,true,$msg));
 677                         }
 678                         if(!$this->supports("inline_keys")) {
 679                                 // handle constraints and indices
 680                                 $indicesArr = $this->getConstraintSql($indices, $tablename);
 681                                 if (count($indicesArr) > 0)
 682                                         foreach ($indicesArr as $indexSql)
 683                                                 $res = ($res and $this->query($indexSql, true, "Error creating indexes"));
 684                         }
 685                         return $res;
 686                 }
 687                 return false;
 688         }
 689
 690         /**
 691          * Implements repair of a db table for a bean.
 692          *
 693          * @param  SugarBean $bean    SugarBean instance
 694          * @param  bool   $execute true if we want the action to take place, false if we just want the sql returned
 695          * @return string SQL statement or empty string, depending upon $execute
 696          */
 697         public function repairTable(SugarBean $bean, $execute = true)
 698         {
 699                 $indices   = $bean->getIndices();
 700                 $fielddefs = $bean->getFieldDefinitions();
 701                 $tablename = $bean->getTableName();
 702
 703                 //Clean the indexes to prevent duplicate definitions
 704                 $new_index = array();
 705                 foreach($indices as $ind_def){
 706                         $new_index[$ind_def['name']] = $ind_def;
 707                 }
 708                 //jc: added this for beans that do not actually have a table, namely
 709                 //ForecastOpportunities
 710                 if($tablename == 'does_not_exist' || $tablename == '')
 711                         return '';
 712
 713                 global $dictionary;
 714                 $engine=null;
 715                 if (isset($dictionary[$bean->getObjectName()]['engine']) && !empty($dictionary[$bean->getObjectName()]['engine']) )
 716                         $engine = $dictionary[$bean->getObjectName()]['engine'];
 717
 718                 return $this->repairTableParams($tablename, $fielddefs,$new_index,$execute,$engine);
 719         }
 720
 721         /**
 722          * Can this field be null?
 723          * Auto-increment and ID fields can not be null
 724          * @param array $vardef
 725      * @return bool
 726      */
 727         protected function isNullable($vardef)
 728         {
 729
 730                 if(isset($vardef['isnull']) && (strtolower($vardef['isnull']) == 'false' || $vardef['isnull'] === false)
 731                         && !empty($vardef['required'])) {
 732                                 /* required + is_null=false => not null */
 733                         return false;
 734                 }
 735                 if(empty($vardef['auto_increment']) && (empty($vardef['type']) || $vardef['type'] != 'id')
 736                                         && (empty($vardef['dbType']) || $vardef['dbType'] != 'id')
 737                                         && (empty($vardef['name']) || ($vardef['name'] != 'id' && $vardef['name'] != 'deleted'))
 738                 ) {
 739                         return true;
 740                 }
 741                 return false;
 742         }
 743
 744
 745         /**
 746          * Builds the SQL commands that repair a table structure
 747          *
 748          * @param  string $tablename
 749          * @param  array  $fielddefs Field definitions, in vardef format
 750          * @param  array  $indices   Index definitions, in vardef format
 751          * @param  bool   $execute   optional, true if we want the queries executed instead of returned
 752          * @param  string $engine    optional, MySQL engine
 753      * @todo: refactor engine param to be more generic
 754      * @return string
 755      */
 756         public function repairTableParams($tablename, $fielddefs,  $indices, $execute = true, $engine = null)
 757         {
 758                 //jc: had a bug when running the repair if the tablename is blank the repair will
 759                 //fail when it tries to create a repair table
 760                 if ($tablename == '' || empty($fielddefs))
 761                         return '';
 762
 763                 //if the table does not exist create it and we are done
 764                 $sql = "/* Table : $tablename */\n";
 765                 if (!$this->tableExists($tablename)) {
 766                         $createtablesql = $this->createTableSQLParams($tablename,$fielddefs,$indices,$engine);
 767                         if($execute && $createtablesql){
 768                                 $this->createTableParams($tablename,$fielddefs,$indices,$engine);
 769                         }
 770
 771                         $sql .= "/* MISSING TABLE: {$tablename} */\n";
 772                         $sql .= $createtablesql . "\n";
 773                         return $sql;
 774                 }
 775
 776                 $compareFieldDefs = $this->get_columns($tablename);
 777                 $compareIndices = $this->get_indices($tablename);
 778
 779                 $take_action = false;
 780
 781                 // do column comparisions
 782                 $sql .= "/*COLUMNS*/\n";
 783                 foreach ($fielddefs as $value) {
 784                         if (isset($value['source']) && $value['source'] != 'db')
 785                                 continue;
 786
 787                         $name = strtolower($value['name']);
 788                         // add or fix the field defs per what the DB is expected to give us back
 789                         $this->massageFieldDef($value,$tablename);
 790
 791                         $ignorerequired=false;
 792
 793                         //Do not track requiredness in the DB, auto_increment, ID,
 794                         // and deleted fields are always required in the DB, so don't force those
 795                         if ($this->isNullable($value)) {
 796                                 $value['required'] = false;
 797                         }
 798                         //Should match the conditions in DBManager::oneColumnSQLRep for DB required fields, type='id' fields will sometimes
 799
 800                         //come into this function as 'type' = 'char', 'dbType' = 'id' without required set in $value. Assume they are correct and leave them alone.
 801                         else if (($name == 'id' || $value['type'] == 'id' || (isset($value['dbType']) && $value['dbType'] == 'id'))
 802                                 && (!isset($value['required']) && isset($compareFieldDefs[$name]['required'])))
 803                         {
 804                                 $value['required'] = $compareFieldDefs[$name]['required'];
 805                         }
 806
 807                         if ( !isset($compareFieldDefs[$name]) ) {
 808                                 // ok we need this field lets create it
 809                                 $sql .= "/*MISSING IN DATABASE - $name -  ROW*/\n";
 810                                 $sql .= $this->addColumnSQL($tablename, $value) .  "\n";
 811                                 if ($execute)
 812                                         $this->addColumn($tablename, $value);
 813                                 $take_action = true;
 814                         } elseif ( !$this->compareVarDefs($compareFieldDefs[$name],$value)) {
 815                                 //fields are different lets alter it
 816                                 $sql .= "/*MISMATCH WITH DATABASE - $name -  ROW ";
 817                                 foreach($compareFieldDefs[$name] as $rKey => $rValue) {
 818                                         $sql .= "[$rKey] => '$rValue'  ";
 819                                 }
 820                                 $sql .= "*/\n";
 821                                 $sql .= "/* VARDEF - $name -  ROW";
 822                                 foreach($value as $rKey => $rValue) {
 823                                         $sql .= "[$rKey] => '$rValue'  ";
 824                                 }
 825                                 $sql .= "*/\n";
 826
 827                                 //jc: oracle will complain if you try to execute a statement that sets a column to (not) null
 828                                 //when it is already (not) null
 829                                 if ( isset($value['isnull']) && isset($compareFieldDefs[$name]['isnull']) &&
 830                                         $value['isnull'] === $compareFieldDefs[$name]['isnull']) {
 831                                         unset($value['required']);
 832                                         $ignorerequired=true;
 833                                 }
 834
 835                                 //dwheeler: Once a column has been defined as null, we cannot try to force it back to !null
 836                                 if ((isset($value['required']) && ($value['required'] === true || $value['required'] == 'true' || $value['required'] === 1))
 837                                         && (empty($compareFieldDefs[$name]['required']) || $compareFieldDefs[$name]['required'] != 'true'))
 838                                 {
 839                                         $ignorerequired = true;
 840                                 }
 841                                 $altersql = $this->alterColumnSQL($tablename, $value,$ignorerequired);
 842                                 if(is_array($altersql)) {
 843                                         $altersql = join("\n", $altersql);
 844                                 }
 845                                 $sql .= $altersql .  "\n";
 846                                 if($execute){
 847                                         $this->alterColumn($tablename, $value, $ignorerequired);
 848                                 }
 849                                 $take_action = true;
 850                         }
 851                 }
 852
 853                 // do index comparisions
 854                 $sql .= "/* INDEXES */\n";
 855                 $correctedIndexs = array();
 856
 857         $compareIndices_case_insensitive = array();
 858
 859                 // do indicies comparisons case-insensitive
 860                 foreach($compareIndices as $k => $value){
 861                         $value['name'] = strtolower($value['name']);
 862                         $compareIndices_case_insensitive[strtolower($k)] = $value;
 863                 }
 864                 $compareIndices = $compareIndices_case_insensitive;
 865                 unset($compareIndices_case_insensitive);
 866
 867                 foreach ($indices as $value) {
 868                         if (isset($value['source']) && $value['source'] != 'db')
 869                                 continue;
 870
 871
 872                         $validDBName = $this->getValidDBName($value['name'], true, 'index', true);
 873                         if (isset($compareIndices[$validDBName])) {
 874                                 $value['name'] = $validDBName;
 875                         }
 876                     $name = strtolower($value['name']);
 877
 878                         //Don't attempt to fix the same index twice in one pass;
 879                         if (isset($correctedIndexs[$name]))
 880                                 continue;
 881
 882                         //don't bother checking primary nothing we can do about them
 883                         if (isset($value['type']) && $value['type'] == 'primary')
 884                                 continue;
 885
 886                         //database helpers do not know how to handle full text indices
 887                         if ($value['type']=='fulltext')
 888                                 continue;
 889
 890                         if ( in_array($value['type'],array('alternate_key','foreign')) )
 891                                 $value['type'] = 'index';
 892
 893                         if ( !isset($compareIndices[$name]) ) {
 894                                 //First check if an index exists that doens't match our name, if so, try to rename it
 895                                 $found = false;
 896                                 foreach ($compareIndices as $ex_name => $ex_value) {
 897                                         if($this->compareVarDefs($ex_value, $value, true)) {
 898                                                 $found = $ex_name;
 899                                                 break;
 900                                         }
 901                                 }
 902                                 if ($found) {
 903                                         $sql .=  "/*MISSNAMED INDEX IN DATABASE - $name - $ex_name */\n";
 904                                         $rename = $this->renameIndexDefs($ex_value, $value, $tablename);
 905                                         if($execute) {
 906                                                 $this->query($rename, true, "Cannot rename index");
 907                                         }
 908                                         $sql .= is_array($rename)?join("\n", $rename). "\n":$rename."\n";
 909
 910                                 } else {
 911                                         // ok we need this field lets create it
 912                                         $sql .=  "/*MISSING INDEX IN DATABASE - $name -{$value['type']}  ROW */\n";
 913                                         $sql .= $this->addIndexes($tablename,array($value), $execute) .  "\n";
 914                                 }
 915                                 $take_action = true;
 916                                 $correctedIndexs[$name] = true;
 917                         } elseif ( !$this->compareVarDefs($compareIndices[$name],$value) ) {
 918                                 // fields are different lets alter it
 919                                 $sql .= "/*INDEX MISMATCH WITH DATABASE - $name -  ROW ";
 920                                 foreach ($compareIndices[$name] as $n1 => $t1) {
 921                                         $sql .=  "<$n1>";
 922                                         if ( $n1 == 'fields' )
 923                                                 foreach($t1 as $rKey => $rValue)
 924                                                         $sql .= "[$rKey] => '$rValue'  ";
 925                                         else
 926                                                 $sql .= " $t1 ";
 927                                 }
 928                                 $sql .= "*/\n";
 929                                 $sql .= "/* VARDEF - $name -  ROW";
 930                                 foreach ($value as $n1 => $t1) {
 931                                         $sql .= "<$n1>";
 932                                         if ( $n1 == 'fields' )
 933                                                 foreach ($t1 as $rKey => $rValue)
 934                                                         $sql .= "[$rKey] => '$rValue'  ";
 935                                         else
 936                                                 $sql .= " $t1 ";
 937                                 }
 938                                 $sql .= "*/\n";
 939                                 $sql .= $this->modifyIndexes($tablename,array($value), $execute) .  "\n";
 940                                 $take_action = true;
 941                                 $correctedIndexs[$name] = true;
 942                         }
 943                 }
 944
 945                 return ($take_action === true) ? $sql : '';
 946         }
 947
 948     /**
 949      * Compares two vardefs
 950      *
 951      * @param  array  $fielddef1 This is from the database
 952      * @param  array  $fielddef2 This is from the vardef
 953      * @param bool $ignoreName Ignore name-only differences?
 954      * @return bool   true if they match, false if they don't
 955      */
 956         public function compareVarDefs($fielddef1, $fielddef2, $ignoreName = false)
 957         {
 958                 foreach ( $fielddef1 as $key => $value ) {
 959                         if ( $key == 'name' && ( strtolower($fielddef1[$key]) == strtolower($fielddef2[$key]) || $ignoreName) )
 960                                 continue;
 961                         if ( isset($fielddef2[$key]) && $fielddef1[$key] == $fielddef2[$key] )
 962                                 continue;
 963                         //Ignore len if its not set in the vardef
 964                         if ($key == 'len' && empty($fielddef2[$key]))
 965                                 continue;
 966                         return false;
 967                 }
 968
 969                 return true;
 970         }
 971
 972         /**
 973          * Compare a field in two tables
 974          * @deprecated
 975          * @param  string $name   field name
 976          * @param  string $table1
 977          * @param  string $table2
 978          * @return array  array with keys 'msg','table1','table2'
 979          */
 980         public function compareFieldInTables($name, $table1, $table2)
 981         {
 982                 $row1 = $this->describeField($name, $table1);
 983                 $row2 = $this->describeField($name, $table2);
 984                 $returnArray = array(
 985                         'table1' => $row1,
 986                         'table2' => $row2,
 987                         'msg'    => 'error',
 988                         );
 989
 990                 $ignore_filter = array('Key'=>1);
 991                 if ($row1) {
 992                         if (!$row2) {
 993                                 // Exists on table1 but not table2
 994                                 $returnArray['msg'] = 'not_exists_table2';
 995                         }
 996                         else {
 997                                 if (sizeof($row1) != sizeof($row2)) {
 998                                         $returnArray['msg'] = 'no_match';
 999                                 }
1000                                 else {
1001                                         $returnArray['msg'] = 'match';
1002                                         foreach($row1 as $key => $value){
1003                                                 //ignore keys when checking we will check them when we do the index check
1004                                                 if( !isset($ignore_filter[$key]) && (!isset($row2[$key]) || $row1[$key] !== $row2[$key])){
1005                                                         $returnArray['msg'] = 'no_match';
1006                                                 }
1007                                         }
1008                                 }
1009                         }
1010                 }
1011                 else {
1012                         $returnArray['msg'] = 'not_exists_table1';
1013                 }
1014
1015                 return $returnArray;
1016         }
1017 //
1018 //    /**
1019 //     * Compare an index in two different tables
1020 //     * @deprecated
1021 //     * @param  string $name   index name
1022 //     * @param  string $table1
1023 //     * @param  string $table2
1024 //     * @return array  array with keys 'msg','table1','table2'
1025 //     */
1026 //    public function compareIndexInTables($name, $table1, $table2)
1027 //    {
1028 //        $row1 = $this->describeIndex($name, $table1);
1029 //        $row2 = $this->describeIndex($name, $table2);
1030 //        $returnArray = array(
1031 //            'table1' => $row1,
1032 //            'table2' => $row2,
1033 //            'msg'    => 'error',
1034 //            );
1035 //        $ignore_filter = array('Table'=>1, 'Seq_in_index'=>1,'Cardinality'=>1, 'Sub_part'=>1, 'Packed'=>1, 'Comment'=>1);
1036 //
1037 //        if ($row1) {
1038 //            if (!$row2) {
1039 //                //Exists on table1 but not table2
1040 //                $returnArray['msg'] = 'not_exists_table2';
1041 //            }
1042 //            else {
1043 //                if (sizeof($row1) != sizeof($row2)) {
1044 //                    $returnArray['msg'] = 'no_match';
1045 //                }
1046 //                else {
1047 //                    $returnArray['msg'] = 'match';
1048 //                    foreach ($row1 as $fname => $fvalue) {
1049 //                        if (!isset($row2[$fname])) {
1050 //                            $returnArray['msg'] = 'no_match';
1051 //                        }
1052 //                        if(!isset($ignore_filter[$fname]) && $row1[$fname] != $row2[$fname]){
1053 //                            $returnArray['msg'] = 'no_match';
1054 //                        }
1055 //                    }
1056 //                }
1057 //            }
1058 //        } else {
1059 //            $returnArray['msg'] = 'not_exists_table1';
1060 //        }
1061 //
1062 //        return $returnArray;
1063 //    }
1064
1065
1066         /**
1067          * Creates an index identified by name on the given fields.
1068          *
1069          * @param SugarBean $bean      SugarBean instance
1070          * @param array  $fieldDefs Field definitions, in vardef format
1071          * @param string $name      index name
1072          * @param bool   $unique    optional, true if we want to create an unique index
1073      * @return bool query result
1074      */
1075         public function createIndex(SugarBean $bean, $fieldDefs, $name, $unique = true)
1076         {
1077                 $sql = $this->createIndexSQL($bean, $fieldDefs, $name, $unique);
1078                 $tablename = $bean->getTableName();
1079                 $msg = "Error creating index $name on table: $tablename:";
1080                 return $this->query($sql,true,$msg);
1081         }
1082
1083         /**
1084          * returns a SQL query that creates the indices as defined in metadata
1085          * @param  array  $indices Assoc array with index definitions from vardefs
1086          * @param  string $table Focus table
1087          * @return array  Array of SQL queries to generate indices
1088          */
1089         public function getConstraintSql($indices, $table)
1090         {
1091                 if (!$this->isFieldArray($indices))
1092                         $indices = array($indices);
1093
1094                 $columns = array();
1095
1096                 foreach ($indices as $index) {
1097                         if(!empty($index['db']) && $index['db'] != $this->dbType)
1098                                 continue;
1099                         if (isset($index['source']) && $index['source'] != 'db')
1100                         continue;
1101
1102                         $sql = $this->add_drop_constraint($table, $index);
1103
1104                         if(!empty($sql)) {
1105                                 $columns[] = $sql;
1106                         }
1107                 }
1108
1109                 return $columns;
1110         }
1111
1112         /**
1113          * Adds a new indexes
1114          *
1115          * @param  string $tablename
1116          * @param  array  $indexes   indexes to add
1117          * @param  bool   $execute   true if we want to execute the returned sql statement
1118          * @return string SQL statement
1119          */
1120         public function addIndexes($tablename, $indexes, $execute = true)
1121         {
1122                 $alters = $this->getConstraintSql($indexes, $tablename);
1123                 if ($execute) {
1124                         foreach($alters as $sql) {
1125                                 $this->query($sql, true, "Error adding index: ");
1126                         }
1127                 }
1128                 if(!empty($alters)) {
1129                         $sql = join(";\n", $alters).";\n";
1130                 } else {
1131                         $sql = '';
1132                 }
1133                 return $sql;
1134         }
1135
1136         /**
1137          * Drops indexes
1138          *
1139          * @param  string $tablename
1140          * @param  array  $indexes   indexes to drop
1141          * @param  bool   $execute   true if we want to execute the returned sql statement
1142          * @return string SQL statement
1143          */
1144         public function dropIndexes($tablename, $indexes, $execute = true)
1145         {
1146                 $sqls = array();
1147                 foreach ($indexes as $index) {
1148                         $name =$index['name'];
1149                         $sqls[$name] = $this->add_drop_constraint($tablename,$index,true);
1150                 }
1151                 if (!empty($sqls) && $execute) {
1152                         foreach($sqls as $name => $sql) {
1153                                 unset(self::$index_descriptions[$tablename][$name]);
1154                                 $this->query($sql);
1155                         }
1156                 }
1157                 if(!empty($sqls)) {
1158                         return join(";\n",$sqls).";";
1159                 } else {
1160                         return '';
1161                 }
1162         }
1163
1164         /**
1165          * Modifies indexes
1166          *
1167          * @param  string $tablename
1168          * @param  array  $indexes   indexes to modify
1169          * @param  bool   $execute   true if we want to execute the returned sql statement
1170          * @return string SQL statement
1171          */
1172         public function modifyIndexes($tablename, $indexes, $execute = true)
1173         {
1174                 return $this->dropIndexes($tablename, $indexes, $execute)."\n".
1175                         $this->addIndexes($tablename, $indexes, $execute);
1176         }
1177
1178         /**
1179          * Adds a column to table identified by field def.
1180          *
1181          * @param string $tablename
1182          * @param array  $fieldDefs
1183      * @return bool query result
1184      */
1185         public function addColumn($tablename, $fieldDefs)
1186         {
1187                 $sql = $this->addColumnSQL($tablename, $fieldDefs);
1188                 if ($this->isFieldArray($fieldDefs)){
1189                         $columns = array();
1190                         foreach ($fieldDefs as $fieldDef)
1191                                 $columns[] = $fieldDef['name'];
1192                         $columns = implode(",", $columns);
1193                 }
1194                 else {
1195                         $columns = $fieldDefs['name'];
1196                 }
1197                 $msg = "Error adding column(s) $columns on table: $tablename:";
1198                 return $this->query($sql,true,$msg);
1199         }
1200
1201         /**
1202          * Alters old column identified by oldFieldDef to new fieldDef.
1203          *
1204          * @param string $tablename
1205          * @param array  $newFieldDef
1206          * @param bool   $ignoreRequired optional, true if we are ignoring this being a required field
1207      * @return bool query result
1208      */
1209         public function alterColumn($tablename, $newFieldDef, $ignoreRequired = false)
1210         {
1211                 $sql = $this->alterColumnSQL($tablename, $newFieldDef,$ignoreRequired);
1212                 if ($this->isFieldArray($newFieldDef)){
1213                         $columns = array();
1214                         foreach ($newFieldDef as $fieldDef) {
1215                                 $columns[] = $fieldDef['name'];
1216                         }
1217                         $columns = implode(",", $columns);
1218                 }
1219                 else {
1220                         $columns = $newFieldDef['name'];
1221                 }
1222
1223                 $msg = "Error altering column(s) $columns on table: $tablename:";
1224                 $res = $this->query($sql,true,$msg);
1225                 if($res) {
1226                         $this->getTableDescription($tablename, true); // reload table description after altering
1227                 }
1228                 return $res;
1229         }
1230
1231         /**
1232          * Drops the table associated with a bean
1233          *
1234          * @param SugarBean $bean SugarBean instance
1235      * @return bool query result
1236          */
1237         public function dropTable(SugarBean $bean)
1238         {
1239                 return $this->dropTableName($bean->getTableName());
1240         }
1241
1242         /**
1243          * Drops the table by name
1244          *
1245          * @param string $name Table name
1246      * @return bool query result
1247          */
1248         public function dropTableName($name)
1249         {
1250                 $sql = $this->dropTableNameSQL($name);
1251                 return $this->query($sql,true,"Error dropping table $name:");
1252         }
1253
1254     /**
1255      * Deletes a column identified by fieldDef.
1256      *
1257      * @param SugarBean $bean   SugarBean containing the field
1258      * @param array  $fieldDefs Vardef definition of the field
1259      * @return bool query result
1260      */
1261         public function deleteColumn(SugarBean $bean, $fieldDefs)
1262         {
1263                 $tablename = $bean->getTableName();
1264                 $sql = $this->dropColumnSQL($tablename, $fieldDefs);
1265                 $msg = "Error deleting column(s) on table: $tablename:";
1266                 return $this->query($sql,true,$msg);
1267         }
1268
1269     /**
1270      * Generate a set of Insert statements based on the bean given
1271      *
1272      * @deprecated
1273      *
1274      * @param  SugarBean $bean         the bean from which table we will generate insert stmts
1275      * @param  string $select_query the query which will give us the set of objects we want to place into our insert statement
1276      * @param  int    $start        the first row to query
1277      * @param  int    $count        the number of rows to query
1278      * @param  string $table        the table to query from
1279      * @param bool $is_related_query
1280      * @return string SQL insert statement
1281      */
1282         public function generateInsertSQL(SugarBean $bean, $select_query, $start, $count = -1, $table, $is_related_query = false)
1283         {
1284                 $this->log->info('call to DBManager::generateInsertSQL() is deprecated');
1285                 global $sugar_config;
1286
1287                 $rows_found = 0;
1288                 $count_query = $bean->create_list_count_query($select_query);
1289                 if(!empty($count_query))
1290                 {
1291                         // We have a count query.  Run it and get the results.
1292                         $result = $this->query($count_query, true, "Error running count query for $this->object_name List: ");
1293                         $assoc = $this->fetchByAssoc($result);
1294                         if(!empty($assoc['c']))
1295                         {
1296                                 $rows_found = $assoc['c'];
1297                         }
1298                 }
1299                 if($count == -1){
1300                         $count  = $sugar_config['list_max_entries_per_page'];
1301                 }
1302                 $next_offset = $start + $count;
1303
1304                 $result = $this->limitQuery($select_query, $start, $count);
1305                 // get basic insert
1306                 $sql = "INSERT INTO ".$table;
1307                 $custom_sql = "INSERT INTO ".$table."_cstm";
1308
1309                 // get field definitions
1310                 $fields = $bean->getFieldDefinitions();
1311                 $custom_fields = array();
1312
1313                 if($bean->hasCustomFields()){
1314                         foreach ($fields as $fieldDef){
1315                                 if($fieldDef['source'] == 'custom_fields'){
1316                                         $custom_fields[$fieldDef['name']] = $fieldDef['name'];
1317                                 }
1318                         }
1319                         if(!empty($custom_fields)){
1320                                 $custom_fields['id_c'] = 'id_c';
1321                                 $id_field = array('name' => 'id_c', 'custom_type' => 'id',);
1322                                 $fields[] = $id_field;
1323                         }
1324                 }
1325
1326                 // get column names and values
1327                 $row_array = array();
1328                 $columns = array();
1329                 $cstm_row_array = array();
1330                 $cstm_columns = array();
1331                 $built_columns = false;
1332                 while(($row = $this->fetchByAssoc($result)) != null)
1333                 {
1334                         $values = array();
1335                         $cstm_values = array();
1336                         if(!$is_related_query){
1337                                 foreach ($fields as $fieldDef)
1338                                 {
1339                                         if(isset($fieldDef['source']) && $fieldDef['source'] != 'db' && $fieldDef['source'] != 'custom_fields') continue;
1340                                         $val = $row[$fieldDef['name']];
1341
1342                                         //handle auto increment values here only need to do this on insert not create
1343                                         if ($fieldDef['name'] == 'deleted'){
1344                                                         $values['deleted'] = $val;
1345                                                         if(!$built_columns){
1346                                                         $columns[] = 'deleted';
1347                                                 }
1348                                         }
1349                                         else
1350                                         {
1351                                                 $type = $fieldDef['type'];
1352                                                 if(!empty($fieldDef['custom_type'])){
1353                                                         $type = $fieldDef['custom_type'];
1354                                                 }
1355                                                 // need to do some thing about types of values
1356                                                 if($this->dbType == 'mysql' && $val == '' && ($type == 'datetime' ||  $type == 'date' || $type == 'int' || $type == 'currency' || $type == 'decimal')){
1357                                                         if(!empty($custom_fields[$fieldDef['name']]))
1358                                                                 $cstm_values[$fieldDef['name']] = 'null';
1359                                                         else
1360                                                                 $values[$fieldDef['name']] = 'null';
1361                                                 }else{
1362                                                         if(isset($type) && $type=='int') {
1363                                                                 if(!empty($custom_fields[$fieldDef['name']]))
1364                                                                         $cstm_values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
1365                                                                 else
1366                                                                         $values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
1367                                                         } else {
1368                                                                 if(!empty($custom_fields[$fieldDef['name']]))
1369                                                                         $cstm_values[$fieldDef['name']] = "'".$GLOBALS['db']->quote(from_html($val))."'";
1370                                                                 else
1371                                                                         $values[$fieldDef['name']] = "'".$GLOBALS['db']->quote(from_html($val))."'";
1372                                                         }
1373                                                 }
1374                                                 if(!$built_columns){
1375                                                         if(!empty($custom_fields[$fieldDef['name']]))
1376                                                                 $cstm_columns[] = $fieldDef['name'];
1377                                                         else
1378                                                                 $columns[] = $fieldDef['name'];
1379                                                 }
1380                                         }
1381
1382                                 }
1383                         } else {
1384                         foreach ($row as $key=>$val)
1385                         {
1386                                         if($key != 'orc_row'){
1387                                                 $values[$key] = "'$val'";
1388                                                 if(!$built_columns){
1389                                                         $columns[] = $key;
1390                                                 }
1391                                         }
1392                         }
1393                         }
1394                         $built_columns = true;
1395                         if(!empty($values)){
1396                                 $row_array[] = $values;
1397                         }
1398                         if(!empty($cstm_values) && !empty($cstm_values['id_c']) && (strlen($cstm_values['id_c']) > 7)){
1399                                 $cstm_row_array[] = $cstm_values;
1400                         }
1401                 }
1402
1403                 //if (sizeof ($values) == 0) return ""; // no columns set
1404
1405                 // get the entire sql
1406                 $sql .= "(".implode(",", $columns).") ";
1407                 $sql .= "VALUES";
1408                 for($i = 0; $i < count($row_array); $i++){
1409                         $sql .= " (".implode(",", $row_array[$i]).")";
1410                         if($i < (count($row_array) - 1)){
1411                                 $sql .= ", ";
1412                         }
1413                 }
1414                 //custom
1415                 // get the entire sql
1416                 $custom_sql .= "(".implode(",", $cstm_columns).") ";
1417                 $custom_sql .= "VALUES";
1418
1419                 for($i = 0; $i < count($cstm_row_array); $i++){
1420                         $custom_sql .= " (".implode(",", $cstm_row_array[$i]).")";
1421                         if($i < (count($cstm_row_array) - 1)){
1422                                 $custom_sql .= ", ";
1423                         }
1424                 }
1425                 return array('data' => $sql, 'cstm_sql' => $custom_sql, /*'result_count' => $row_count, */ 'total_count' => $rows_found, 'next_offset' => $next_offset);
1426         }
1427
1428         /**
1429          * @deprecated
1430          * Disconnects all instances
1431          */
1432         public function disconnectAll()
1433         {
1434                 DBManagerFactory::disconnectAll();
1435         }
1436
1437         /**
1438          * This function sets the query threshold limit
1439          *
1440          * @param int $limit value of query threshold limit
1441          */
1442         public static function setQueryLimit($limit)
1443         {
1444                 //reset the queryCount
1445                 self::$queryCount = 0;
1446                 self::$queryLimit = $limit;
1447         }
1448
1449         /**
1450          * Returns the static queryCount value
1451          *
1452          * @return int value of the queryCount static variable
1453          */
1454         public static function getQueryCount()
1455         {
1456                 return self::$queryCount;
1457         }
1458
1459
1460         /**
1461          * Resets the queryCount value to 0
1462          *
1463          */
1464         public static function resetQueryCount()
1465         {
1466                 self::$queryCount = 0;
1467         }
1468
1469         /**
1470          * This function increments the global $sql_queries variable
1471          */
1472         public function countQuery()
1473         {
1474                 if (self::$queryLimit != 0 && ++self::$queryCount > self::$queryLimit
1475                         &&(empty($GLOBALS['current_user']) || !is_admin($GLOBALS['current_user']))) {
1476             require_once('include/resource/ResourceManager.php');
1477             $resourceManager = ResourceManager::getInstance();
1478             $resourceManager->notifyObservers('ERR_QUERY_LIMIT');
1479                 }
1480         }
1481
1482         /**
1483          * Pre-process string for quoting
1484          * @internal
1485          * @param string $string
1486      * @return string
1487      */
1488         protected function quoteInternal($string)
1489         {
1490                 return from_html($string);
1491         }
1492
1493         /**
1494          * Return string properly quoted with ''
1495          * @param string $string
1496          * @return string
1497          */
1498         public function quoted($string)
1499         {
1500                 return "'".$this->quote($string)."'";
1501         }
1502
1503         /**
1504      * Quote value according to type
1505      * Numerics aren't quoted
1506      * Dates are converted and quoted
1507      * Rest is just quoted
1508      * @param string $type
1509      * @param string $value
1510      * @return string Quoted value
1511      */
1512     public function quoteType($type, $value)
1513         {
1514             if($type == 'date') {
1515                 return $this->convert($this->quoted($value), "date");
1516             }
1517             if($type == 'time') {
1518                 return $this->convert($this->quoted($value), "time");
1519             }
1520         if(isset($this->type_class[$type]) &&  $this->type_class[$type] == "date") {
1521             return $this->convert($this->quoted($value), "datetime");
1522         }
1523         if($this->isNumericType($type)) {
1524             return 0+$value; // ensure it's numeric
1525         }
1526
1527         return $this->quoted($value);
1528         }
1529
1530     /**
1531      * Quote the strings of the passed in array
1532      *
1533      * The array must only contain strings
1534      *
1535      * @param array $array
1536      * @return array Quoted strings
1537      */
1538         public function arrayQuote(array &$array)
1539         {
1540                 foreach($array as &$val) {
1541                         $val = $this->quote($val);
1542                 }
1543                 return $array;
1544         }
1545
1546     /**
1547      * Frees out previous results
1548      *
1549      * @param resource|bool $result optional, pass if you want to free a single result instead of all results
1550      */
1551         protected function freeResult($result = false)
1552         {
1553                 if($result) {
1554                         $this->freeDbResult($result);
1555                 }
1556                 if($this->lastResult) {
1557                         $this->freeDbResult($this->lastResult);
1558                         $this->lastResult = null;
1559                 }
1560         }
1561
1562         /**
1563          * @abstract
1564          * Check if query has LIMIT clause
1565          * Relevant for now only for Mysql
1566          * @param string $sql
1567          * @return bool
1568          */
1569         protected function hasLimit($sql)
1570         {
1571             return false;
1572         }
1573
1574         /**
1575          * Runs a query and returns a single row containing single value
1576          *
1577          * @param  string   $sql        SQL Statement to execute
1578          * @param  bool     $dieOnError True if we want to call die if the query returns errors
1579          * @param  string   $msg        Message to log if error occurs
1580          * @return array    single value from the query
1581          */
1582         public function getOne($sql, $dieOnError = false, $msg = '')
1583         {
1584                 $this->log->info("Get One: |$sql|");
1585                 if(!$this->hasLimit($sql)) {
1586                     $queryresult = $this->limitQuery($sql, 0, 1, $dieOnError, $msg);
1587                 } else {
1588                     // support old code that passes LIMIT to sql
1589                     // works only for mysql, so do not rely on this
1590                     $queryresult = $this->query($sql, $dieOnError, $msg);
1591                 }
1592                 $this->checkError($msg.' Get One Failed:' . $sql, $dieOnError);
1593                 if (!$queryresult) return false;
1594                 $row = $this->fetchByAssoc($queryresult);
1595                 if(!empty($row)) {
1596                         return array_shift($row);
1597                 }
1598                 return false;
1599         }
1600
1601         /**
1602          * Runs a query and returns a single row
1603          *
1604          * @param  string   $sql        SQL Statement to execute
1605          * @param  bool     $dieOnError True if we want to call die if the query returns errors
1606          * @param  string   $msg        Message to log if error occurs
1607          * @param  bool     $suppress   Message to log if error occurs
1608          * @return array    single row from the query
1609          */
1610         public function fetchOne($sql, $dieOnError = false, $msg = '', $suppress = false)
1611         {
1612                 $this->log->info("Fetch One: |$sql|");
1613                 $this->checkConnection();
1614                 $queryresult = $this->query($sql, $dieOnError, $msg);
1615                 $this->checkError($msg.' Fetch One Failed:' . $sql, $dieOnError);
1616
1617                 if (!$queryresult) return false;
1618
1619                 $row = $this->fetchByAssoc($queryresult);
1620                 if ( !$row ) return false;
1621
1622                 $this->freeResult($queryresult);
1623                 return $row;
1624         }
1625
1626     /**
1627      * Returns the number of rows affected by the last query
1628      * @abstract
1629          * See also affected_rows capability, will return 0 unless the DB supports it
1630      * @param resource $result query result resource
1631      * @return int
1632      */
1633         public function getAffectedRowCount($result)
1634         {
1635                 return 0;
1636         }
1637
1638         /**
1639          * Returns the number of rows returned by the result
1640          *
1641          * This function can't be reliably implemented on most DB, do not use it.
1642          * @abstract
1643          * @deprecated
1644          * @param  resource $result
1645          * @return int
1646          */
1647         public function getRowCount($result)
1648         {
1649             return 0;
1650         }
1651
1652         /**
1653      * Get table description
1654      * @param string $tablename
1655      * @param bool $reload true means load from DB, false allows using cache
1656      * @return array Vardef-format table description
1657      *
1658      */
1659         public function getTableDescription($tablename, $reload = false)
1660         {
1661                 if($reload || empty(self::$table_descriptions[$tablename])) {
1662                         self::$table_descriptions[$tablename] = $this->get_columns($tablename);
1663                 }
1664                 return self::$table_descriptions[$tablename];
1665         }
1666
1667         /**
1668          * Returns the field description for a given field in table
1669          *
1670          * @param  string $name
1671          * @param  string $tablename
1672          * @return array
1673          */
1674         protected function describeField($name, $tablename)
1675         {
1676                 $table = $this->getTableDescription($tablename);
1677                 if(!empty($table) && isset($table[$name]))
1678                         return  $table[$name];
1679
1680                 $table = $this->getTableDescription($tablename, true);
1681
1682                 if(isset($table[$name]))
1683                 return $table[$name];
1684
1685                 return array();
1686         }
1687
1688         /**
1689          * Returns the index description for a given index in table
1690          *
1691          * @param  string $name
1692          * @param  string $tablename
1693          * @return array
1694          */
1695         protected function describeIndex($name, $tablename)
1696         {
1697                 if(isset(self::$index_descriptions[$tablename]) && isset(self::$index_descriptions[$tablename]) && isset(self::$index_descriptions[$tablename][$name])){
1698                         return  self::$index_descriptions[$tablename][$name];
1699                 }
1700
1701                 self::$index_descriptions[$tablename] = $this->get_indices($tablename);
1702
1703                 if(isset(self::$index_descriptions[$tablename][$name])){
1704                         return  self::$index_descriptions[$tablename][$name];
1705                 }
1706
1707                 return array();
1708         }
1709
1710     /**
1711      * Truncates a string to a given length
1712      *
1713      * @param string $string
1714      * @param int    $len    length to trim to
1715      * @return string
1716      *
1717      */
1718         public function truncate($string, $len)
1719         {
1720                 if ( is_numeric($len) && $len > 0)
1721                 {
1722                         $string = mb_substr($string,0,(int) $len, "UTF-8");
1723                 }
1724                 return $string;
1725         }
1726
1727     /**
1728      * Returns the database string needed for concatinating multiple database strings together
1729      *
1730      * @param string $table table name of the database fields to concat
1731      * @param array $fields fields in the table to concat together
1732      * @param string $space Separator between strings, default is single space
1733      * @return string
1734      */
1735         public function concat($table, array $fields, $space = ' ')
1736         {
1737                 if(empty($fields)) return '';
1738                 $elems = array();
1739                 $space = $this->quoted($space);
1740                 foreach ( $fields as $field ) {
1741                         if(!empty($elems)) $elems[] = $space;
1742                         $elems[] = $this->convert("$table.$field", 'IFNULL', array("''"));
1743                 }
1744                 $first = array_shift($elems);
1745                 return "LTRIM(RTRIM(".$this->convert($first, 'CONCAT', $elems)."))";
1746         }
1747
1748         /**
1749          * Given a sql stmt attempt to parse it into the sql and the tokens. Then return the index of this prepared statement
1750          * Tokens can come in the following forms:
1751          * ? - a scalar which will be quoted
1752          * ! - a literal which will not be quoted
1753          * & - binary data to read from a file
1754          *
1755          * @param  string       $sql        The sql to parse
1756          * @return int index of the prepared statement to be used with execute
1757          */
1758         public function prepareQuery($sql)
1759         {
1760                 //parse out the tokens
1761                 $tokens = preg_split('/((?<!\\\)[&?!])/', $sql, -1, PREG_SPLIT_DELIM_CAPTURE);
1762
1763                 //maintain a count of the actual tokens for quick reference in execute
1764                 $count = 0;
1765
1766                 $sqlStr = '';
1767                 foreach ($tokens as $key => $val) {
1768                         switch ($val) {
1769                                 case '?' :
1770                                 case '!' :
1771                                 case '&' :
1772                                         $count++;
1773                                         $sqlStr .= '?';
1774                                         break;
1775
1776                                 default :
1777                                         //escape any special characters
1778                                         $tokens[$key] = preg_replace('/\\\([&?!])/', "\\1", $val);
1779                                         $sqlStr .= $tokens[$key];
1780                                         break;
1781                         } // switch
1782                 } // foreach
1783
1784                 $this->preparedTokens[] = array('tokens' => $tokens, 'tokenCount' => $count, 'sqlString' => $sqlStr);
1785                 end($this->preparedTokens);
1786                 return key($this->preparedTokens);
1787         }
1788
1789         /**
1790          * Takes a prepared stmt index and the data to replace and creates the query and runs it.
1791          *
1792          * @param  int          $stmt       The index of the prepared statement from preparedTokens
1793          * @param  array    $data               The array of data to replace the tokens with.
1794          * @return resource result set or false on error
1795          */
1796         public function executePreparedQuery($stmt, $data = array())
1797         {
1798                 if(!empty($this->preparedTokens[$stmt])){
1799                         if(!is_array($data)){
1800                                 $data = array($data);
1801                         }
1802
1803                         $pTokens = $this->preparedTokens[$stmt];
1804
1805                         //ensure that the number of data elements matches the number of replacement tokens
1806                         //we found in prepare().
1807                         if(count($data) != $pTokens['tokenCount']){
1808                                 //error the data count did not match the token count
1809                                 return false;
1810                         }
1811
1812                         $query = '';
1813                         $dataIndex = 0;
1814                         $tokens = $pTokens['tokens'];
1815                         foreach ($tokens as $val) {
1816                                 switch ($val) {
1817                                         case '?':
1818                                                 $query .= $this->quote($data[$dataIndex++]);
1819                                                 break;
1820                                         case '&':
1821                                                 $filename = $data[$dataIndex++];
1822                                                 $query .= file_get_contents($filename);
1823                                                 break;
1824                                         case '!':
1825                                                 $query .= $data[$dataIndex++];
1826                                                 break;
1827                                         default:
1828                                                 $query .= $val;
1829                                                 break;
1830                                 }//switch
1831                         }//foreach
1832                         return $this->query($query);
1833                 }else{
1834                         return false;
1835                 }
1836         }
1837
1838         /**
1839          * Run both prepare and execute without the client having to run both individually.
1840          *
1841          * @param  string       $sql        The sql to parse
1842          * @param  array    $data               The array of data to replace the tokens with.
1843          * @return resource result set or false on error
1844          */
1845         public function pQuery($sql, $data = array())
1846         {
1847                 $stmt = $this->prepareQuery($sql);
1848                 return $this->executePreparedQuery($stmt, $data);
1849         }
1850
1851 /********************** SQL FUNCTIONS ****************************/
1852     /**
1853      * Generates sql for create table statement for a bean.
1854      *
1855      * NOTE: does not handle out-of-table constraints, use createConstraintSQL for that
1856      * @param SugarBean $bean SugarBean instance
1857      * @return string SQL Create Table statement
1858      */
1859         public function createTableSQL(SugarBean $bean)
1860         {
1861                 $tablename = $bean->getTableName();
1862                 $fieldDefs = $bean->getFieldDefinitions();
1863                 $indices = $bean->getIndices();
1864                 return $this->createTableSQLParams($tablename, $fieldDefs, $indices);
1865         }
1866
1867         /**
1868          * Generates SQL for insert statement.
1869          *
1870          * @param  SugarBean $bean SugarBean instance
1871          * @return string SQL Create Table statement
1872          */
1873         public function insertSQL(SugarBean $bean)
1874         {
1875                 // get column names and values
1876                 $sql = $this->insertParams($bean->getTableName(), $bean->getFieldDefinitions(), get_object_vars($bean),
1877                         isset($bean->field_name_map)?$bean->field_name_map:null, false);
1878                 return $sql;
1879         }
1880
1881         /**
1882          * Generates SQL for update statement.
1883          *
1884          * @param  SugarBean $bean SugarBean instance
1885          * @param  array  $where Optional, where conditions in an array
1886          * @return string SQL Create Table statement
1887          */
1888         public function updateSQL(SugarBean $bean, array $where = array())
1889         {
1890                 $primaryField = $bean->getPrimaryFieldDefinition();
1891                 $columns = array();
1892         $fields = $bean->getFieldDefinitions();
1893                 // get column names and values
1894                 foreach ($fields as $field => $fieldDef) {
1895                         if (isset($fieldDef['source']) && $fieldDef['source'] != 'db')  continue;
1896                         // Do not write out the id field on the update statement.
1897                 // We are not allowed to change ids.
1898                 if ($fieldDef['name'] == $primaryField['name']) continue;
1899
1900                 // If the field is an auto_increment field, then we shouldn't be setting it.  This was added
1901                 // specially for Bugs and Cases which have a number associated with them.
1902                 if (!empty($bean->field_name_map[$field]['auto_increment'])) continue;
1903
1904                 //custom fields handle their save seperatley
1905                 if(isset($bean->field_name_map) && !empty($bean->field_name_map[$field]['custom_type']))  continue;
1906
1907                 // no need to clear deleted since we only update not deleted records anyway
1908                 if($fieldDef['name'] == 'deleted' && empty($bean->deleted)) continue;
1909
1910                 if(isset($bean->$field)) {
1911                         $val = from_html($bean->$field);
1912                 } else {
1913                         continue;
1914                 }
1915
1916                 if(!empty($fieldDef['type']) && $fieldDef['type'] == 'bool'){
1917                         $val = $bean->getFieldValue($field);
1918                 }
1919
1920                 if(strlen($val) == 0) {
1921                         if(isset($fieldDef['default']) && strlen($fieldDef['default']) > 0) {
1922                                 $val = $fieldDef['default'];
1923                         } else {
1924                                 $val = null;
1925                         }
1926                 }
1927
1928                 if(!empty($val) && !empty($fieldDef['len']) && strlen($val) > $fieldDef['len']) {
1929                             $val = $this->truncate($val, $fieldDef['len']);
1930                         }
1931
1932                 if(!is_null($val) || !empty($fieldDef['required'])) {
1933                         $columns[] = "{$fieldDef['name']}=".$this->massageValue($val, $fieldDef);
1934                 } elseif($this->isNullable($fieldDef)) {
1935                         $columns[] = "{$fieldDef['name']}=NULL";
1936                 } else {
1937                     $columns[] = "{$fieldDef['name']}=".$this->emptyValue($fieldDef['type']);
1938                 }
1939                 }
1940
1941                 if ( sizeof($columns) == 0 )
1942                         return ""; // no columns set
1943
1944                 // build where clause
1945                 $where = $this->getWhereClause($bean, $this->updateWhereArray($bean, $where));
1946                 if(isset($fields['deleted'])) {
1947                     $where .= " AND deleted=0";
1948                 }
1949
1950                 return "UPDATE ".$bean->getTableName()."
1951                                         SET ".implode(",", $columns)."
1952                                         $where";
1953         }
1954
1955         /**
1956          * This method returns a where array so that it has id entry if
1957          * where is not an array or is empty
1958          *
1959          * @param  SugarBean $bean SugarBean instance
1960          * @param  array  $where Optional, where conditions in an array
1961          * @return array
1962          */
1963         protected function updateWhereArray(SugarBean $bean, array $where = array())
1964         {
1965                 if (count($where) == 0) {
1966                         $fieldDef = $bean->getPrimaryFieldDefinition();
1967                         $primaryColumn = $fieldDef['name'];
1968
1969                         $val = $bean->getFieldValue($fieldDef['name']);
1970                         if ($val != FALSE){
1971                                 $where[$primaryColumn] = $val;
1972                         }
1973                 }
1974
1975                 return $where;
1976         }
1977
1978         /**
1979          * Returns a where clause without the 'where' key word
1980          *
1981          * The clause returned does not have an 'and' at the beginning and the columns
1982          * are joined by 'and'.
1983          *
1984          * @param  string $table table name
1985          * @param  array  $whereArray Optional, where conditions in an array
1986          * @return string
1987          */
1988         protected function getColumnWhereClause($table, array $whereArray = array())
1989         {
1990                 $where = array();
1991                 foreach ($whereArray as $name => $val) {
1992                         $op = "=";
1993                         if (is_array($val)) {
1994                                 $op = "IN";
1995                                 $temp = array();
1996                                 foreach ($val as $tval){
1997                                         $temp[] = $this->quoted($tval);
1998                                 }
1999                                 $val = implode(",", $temp);
2000                                 $val = "($val)";
2001                         } else {
2002                                 $val = $this->quoted($val);
2003                         }
2004
2005                         $where[] = " $table.$name $op $val";
2006                 }
2007
2008                 if (!empty($where))
2009                         return implode(" AND ", $where);
2010
2011                 return '';
2012         }
2013
2014         /**
2015          * This method returns a complete where clause built from the
2016          * where values specified.
2017          *
2018          * @param  SugarBean $bean SugarBean that describes the table
2019          * @param  array  $whereArray Optional, where conditions in an array
2020          * @return string
2021          */
2022         protected function getWhereClause(SugarBean $bean, array $whereArray=array())
2023         {
2024             return " WHERE " . $this->getColumnWhereClause($bean->getTableName(), $whereArray);
2025         }
2026
2027         /**
2028          * Outputs a correct string for the sql statement according to value
2029          *
2030          * @param  mixed $val
2031          * @param  array $fieldDef field definition
2032          * @return mixed
2033          */
2034         public function massageValue($val, $fieldDef)
2035         {
2036                 $type = $this->getFieldType($fieldDef);
2037
2038                 if(isset($this->type_class[$type])) {
2039                         // handle some known types
2040                         switch($this->type_class[$type]) {
2041                                 case 'bool':
2042                                 case 'int':
2043                                         if (!empty($fieldDef['required']) && $val == ''){
2044                                                 if (isset($fieldDef['default'])){
2045                                                         return $fieldDef['default'];
2046                                                 }
2047                                                 return 0;
2048                                         }
2049                                         return intval($val);
2050                 case 'bigint' :
2051                     $val = (float)$val;
2052                                         if (!empty($fieldDef['required']) && $val == false){
2053                                                 if (isset($fieldDef['default'])){
2054                                                         return $fieldDef['default'];
2055                                                 }
2056                                                 return 0;
2057                                         }
2058                     return $val;
2059                                 case 'float':
2060                                         if (!empty($fieldDef['required'])  && $val == ''){
2061                                                 if (isset($fieldDef['default'])){
2062                                                         return $fieldDef['default'];
2063                                                 }
2064                                                 return 0;
2065                                         }
2066                                         return floatval($val);
2067                                 case 'time':
2068                                 case 'date':
2069                                         // empty date can't be '', so convert it to either NULL or empty date value
2070                                         if($val == '') {
2071                                                 if (!empty($fieldDef['required'])) {
2072                                                         if (isset($fieldDef['default'])) {
2073                                                                 return $fieldDef['default'];
2074                                                         }
2075                                                         return $this->emptyValue($type);
2076                                                 }
2077                                                 return "NULL";
2078                                         }
2079                                         break;
2080                         }
2081                 } else {
2082                     if(!empty($val) && !empty($fieldDef['len']) && strlen($val) > $fieldDef['len']) {
2083                             $val = $this->truncate($val, $fieldDef['len']);
2084                         }
2085                 }
2086
2087                 if ( is_null($val) ) {
2088                         if(!empty($fieldDef['required'])) {
2089                                 if (isset($fieldDef['default'])){
2090                                         return $fieldDef['default'];
2091                                 }
2092                                 return $this->emptyValue($type);
2093                         } else {
2094                                 return "NULL";
2095                         }
2096                 }
2097         if($type == "datetimecombo") {
2098             $type = "datetime";
2099         }
2100                 return $this->convert($this->quoted($val), $type);
2101         }
2102
2103         /**
2104          * Massages the field defintions to fill in anything else the DB backend may add
2105          *
2106          * @param  array  $fieldDef
2107          * @param  string $tablename
2108          * @return array
2109          */
2110         public function massageFieldDef(&$fieldDef, $tablename)
2111         {
2112                 if ( !isset($fieldDef['dbType']) ) {
2113                         if ( isset($fieldDef['dbtype']) )
2114                                 $fieldDef['dbType'] = $fieldDef['dbtype'];
2115                         else
2116                                 $fieldDef['dbType'] = $fieldDef['type'];
2117                 }
2118                 $type = $this->getColumnType($fieldDef['dbType'],$fieldDef['name'],$tablename);
2119                 $matches = array();
2120                 preg_match_all('/(\w+)(?:\(([0-9]+,?[0-9]*)\)|)/i', $type, $matches);
2121                 if ( isset($matches[1][0]) )
2122                         $fieldDef['type'] = $matches[1][0];
2123                 if ( isset($matches[2][0]) && empty($fieldDef['len']) )
2124                         $fieldDef['len'] = $matches[2][0];
2125                 if ( !empty($fieldDef['precision']) && is_numeric($fieldDef['precision']) && !strstr($fieldDef['len'],',') )
2126                         $fieldDef['len'] .= ",{$fieldDef['precision']}";
2127                 if (!empty($fieldDef['required']) || ($fieldDef['name'] == 'id' && !isset($fieldDef['required'])) ) {
2128                         $fieldDef['required'] = 'true';
2129                 }
2130         }
2131
2132         /**
2133          * Take an SQL statement and produce a list of fields used in that select
2134          * @param string $selectStatement
2135          * @return array
2136          */
2137         public function getSelectFieldsFromQuery($selectStatement)
2138         {
2139                 $selectStatement = trim($selectStatement);
2140                 if (strtoupper(substr($selectStatement, 0, 6)) == "SELECT")
2141                         $selectStatement = trim(substr($selectStatement, 6));
2142
2143                 //Due to sql functions existing in many selects, we can't use php explode
2144                 $fields = array();
2145                 $level = 0;
2146                 $selectField = "";
2147                 $strLen = strlen($selectStatement);
2148                 for($i = 0; $i < $strLen; $i++)
2149                 {
2150                         $char = $selectStatement[$i];
2151
2152                         if ($char == "," && $level == 0)
2153                         {
2154                                 $field = $this->getFieldNameFromSelect(trim($selectField));
2155                                 $fields[$field] = $selectField;
2156                                 $selectField = "";
2157                         }
2158                         else if ($char == "("){
2159                                 $level++;
2160                                 $selectField .= $char;
2161                         }
2162                         else if($char == ")"){
2163                                 $level--;
2164                                 $selectField .= $char;
2165
2166
2167                         }else{
2168                                 $selectField .= $char;
2169                         }
2170
2171                 }
2172                 $fields[$this->getFieldNameFromSelect($selectField)] = $selectField;
2173                 return $fields;
2174         }
2175
2176         /**
2177          * returns the field name used in a select
2178          * @param string $string SELECT query
2179      * @return string
2180      */
2181         protected function getFieldNameFromSelect($string)
2182         {
2183                 if(strncasecmp($string, "DISTINCT ", 9) == 0) {
2184                         $string = substr($string, 9);
2185                 }
2186                 if (stripos($string, " as ") !== false)
2187                         //"as" used for an alias
2188                         return trim(substr($string, strripos($string, " as ") + 4));
2189                 else if (strrpos($string, " ") != 0)
2190                         //Space used as a delimeter for an alias
2191                         return trim(substr($string, strrpos($string, " ")));
2192                 else if (strpos($string, ".") !== false)
2193                         //No alias, but a table.field format was used
2194                         return substr($string, strpos($string, ".") + 1);
2195                 else
2196                         //Give up and assume the whole thing is the field name
2197                         return $string;
2198         }
2199
2200         /**
2201          * Generates SQL for delete statement identified by id.
2202          *
2203          * @param  SugarBean $bean SugarBean instance
2204          * @param  array  $where where conditions in an array
2205          * @return string SQL Update Statement
2206          */
2207         public function deleteSQL(SugarBean $bean, array $where)
2208         {
2209                 $where = $this->getWhereClause($bean, $this->updateWhereArray($bean, $where));
2210                 return "UPDATE ".$bean->getTableName()." SET deleted=1 $where";
2211         }
2212
2213     /**
2214      * Generates SQL for select statement for any bean identified by id.
2215      *
2216      * @param  SugarBean $bean SugarBean instance
2217      * @param  array  $where where conditions in an array
2218      * @return string SQL Select Statement
2219      */
2220         public function retrieveSQL(SugarBean $bean, array $where)
2221         {
2222                 $where = $this->getWhereClause($bean, $this->updateWhereArray($bean, $where));
2223                 return "SELECT * FROM ".$bean->getTableName()." $where AND deleted=0";
2224         }
2225
2226     /**
2227      * This method implements a generic sql for a collection of beans.
2228      *
2229      * Currently, this function does not support outer joins.
2230      *
2231      * @param array $beans Array of values returned by get_class method as the keys and a bean as
2232      *      the value for that key. These beans will be joined in the sql by the key
2233      *      attribute of field defs.
2234      * @param  array $cols Optional, columns to be returned with the keys as names of bean
2235      *      as identified by get_class of bean. Values of this array is the array of fieldDefs
2236      *      to be returned for a bean. If an empty array is passed, all columns are selected.
2237      * @param  array $whereClause Optional, values with the keys as names of bean as identified
2238      *      by get_class of bean. Each value at the first level is an array of values for that
2239      *      bean identified by name of fields. If we want to pass multiple values for a name,
2240      *      pass it as an array. If where is not passed, all the rows will be returned.
2241      *
2242      * @return string SQL Select Statement
2243      */
2244         public function retrieveViewSQL(array $beans, array $cols = array(), array $whereClause = array())
2245         {
2246                 $relations = array(); // stores relations between tables as they are discovered
2247                 $where = $select = array();
2248                 foreach ($beans as $beanID => $bean) {
2249                         $tableName = $bean->getTableName();
2250                         $beanTables[$beanID] = $tableName;
2251
2252                         $table = $beanID;
2253                         $tables[$table] = $tableName;
2254                         $aliases[$tableName][] = $table;
2255
2256                         // build part of select for this table
2257                         if (is_array($cols[$beanID]))
2258                                 foreach ($cols[$beanID] as $def) $select[] = $table.".".$def['name'];
2259
2260                         // build part of where clause
2261                         if (is_array($whereClause[$beanID])){
2262                                 $where[] = $this->getColumnWhereClause($table, $whereClause[$beanID]);
2263                         }
2264                         // initialize so that it can be used properly in form clause generation
2265                         $table_used_in_from[$table] = false;
2266
2267                         $indices = $bean->getIndices();
2268                         foreach ($indices as $index){
2269                                 if ($index['type'] == 'foreign') {
2270                                         $relationship[$table][] = array('foreignTable'=> $index['foreignTable']
2271                                                                                                 ,'foreignColumn'=>$index['foreignField']
2272                                                                                                 ,'localColumn'=> $index['fields']
2273                                                                                                 );
2274                                 }
2275                         }
2276                         $where[] = " $table.deleted = 0";
2277                 }
2278
2279                 // join these clauses
2280                 $select = !empty($select) ? implode(",", $select) : "*";
2281                 $where = implode(" AND ", $where);
2282
2283                 // generate the from clause. Use relations array to generate outer joins
2284                 // all the rest of the tables will be used as a simple from
2285                 // relations table define relations between table1 and table2 through column on table 1
2286                 // table2 is assumed to joing through primaty key called id
2287                 $separator = "";
2288                 $from = ''; $table_used_in_from = array();
2289                 foreach ($relations as $table1 => $rightsidearray){
2290                         if ($table_used_in_from[$table1]) continue; // table has been joined
2291
2292                         $from .= $separator." ".$table1;
2293                         $table_used_in_from[$table1] = true;
2294                         foreach ($rightsidearray as $tablearray){
2295                                 $table2 = $tablearray['foreignTable']; // get foreign table
2296                                 $tableAlias = $aliases[$table2]; // get a list of aliases fo thtis table
2297                                 foreach ($tableAlias as $table2) {
2298                                         //choose first alias that does not match
2299                                         // we are doing this because of self joins.
2300                                         // in case of self joins, the same table will bave many aliases.
2301                                         if ($table2 != $table1) break;
2302                                 }
2303
2304                                 $col = $tablearray['foreingColumn'];
2305                                 $name = $tablearray['localColumn'];
2306                                 $from .= " LEFT JOIN $table on ($table1.$name = $table2.$col)";
2307                                 $table_used_in_from[$table2] = true;
2308                         }
2309                         $separator = ",";
2310                 }
2311
2312                 return "SELECT $select FROM $from WHERE $where";
2313         }
2314
2315         /**
2316          * Generates SQL for create index statement for a bean.
2317          *
2318          * @param  SugarBean $bean SugarBean instance
2319          * @param  array  $fields fields used in the index
2320          * @param  string $name index name
2321          * @param  bool   $unique Optional, set to true if this is an unique index
2322          * @return string SQL Select Statement
2323          */
2324         public function createIndexSQL(SugarBean $bean, array $fields, $name, $unique = true)
2325         {
2326                 $unique = ($unique) ? "unique" : "";
2327                 $tablename = $bean->getTableName();
2328                 $columns = array();
2329                 // get column names
2330                 foreach ($fields as $fieldDef)
2331                         $columns[] = $fieldDef['name'];
2332
2333                 if (empty($columns))
2334                         return "";
2335
2336                 $columns = implode(",", $columns);
2337
2338                 return "CREATE $unique INDEX $name ON $tablename ($columns)";
2339         }
2340
2341         /**
2342          * Returns the type of the variable in the field
2343          *
2344          * @param  array $fieldDef Vardef-format field def
2345          * @return string
2346          */
2347         public function getFieldType($fieldDef)
2348         {
2349                 // get the type for db type. if that is not set,
2350                 // get it from type. This is done so that
2351                 // we do not have change a lot of existing code
2352                 // and add dbtype where type is being used for some special
2353                 // purposes like referring to foreign table etc.
2354                 if(!empty($fieldDef['dbType']))
2355                         return  $fieldDef['dbType'];
2356                 if(!empty($fieldDef['dbtype']))
2357                         return  $fieldDef['dbtype'];
2358                 if (!empty($fieldDef['type']))
2359                         return  $fieldDef['type'];
2360                 if (!empty($fieldDef['Type']))
2361                         return  $fieldDef['Type'];
2362                 if (!empty($fieldDef['data_type']))
2363                         return  $fieldDef['data_type'];
2364
2365                 return null;
2366         }
2367
2368         /**
2369          * Returns the defintion for a single column
2370          *
2371          * @param  array  $fieldDef Vardef-format field def
2372          * @param  bool   $ignoreRequired  Optional, true if we should ignore this being a required field
2373          * @param  string $table           Optional, table name
2374          * @param  bool   $return_as_array Optional, true if we should return the result as an array instead of sql
2375          * @return string or array if $return_as_array is true
2376          */
2377         protected function oneColumnSQLRep($fieldDef, $ignoreRequired = false, $table = '', $return_as_array = false)
2378         {
2379                 $name = $fieldDef['name'];
2380                 $type = $this->getFieldType($fieldDef);
2381                 $colType = $this->getColumnType($type);
2382
2383                 if (( $colType == 'nvarchar'
2384                                 or $colType == 'nchar'
2385                                 or $colType == 'varchar'
2386                                 or $colType == 'char'
2387                                 or $colType == 'varchar2') ) {
2388                         if( !empty($fieldDef['len']))
2389                                 $colType .= "(".$fieldDef['len'].")";
2390                         else
2391                                 $colType .= "(255)";
2392                 }
2393         if($colType == 'decimal' || $colType == 'float'){
2394                         if(!empty($fieldDef     ['len'])){
2395                                 if(!empty($fieldDef['precision']) && is_numeric($fieldDef['precision']))
2396                                         if(strpos($fieldDef     ['len'],',') === false){
2397                                                 $colType .= "(".$fieldDef['len'].",".$fieldDef['precision'].")";
2398                                         }else{
2399                                                 $colType .= "(".$fieldDef['len'].")";
2400                                         }
2401                                 else
2402                                                 $colType .= "(".$fieldDef['len'].")";
2403                         }
2404         }
2405
2406
2407                 if (isset($fieldDef['default']) && strlen($fieldDef['default']) > 0)
2408                         $default = " DEFAULT ".$this->quoted($fieldDef['default']);
2409                 elseif (!isset($default) && $type == 'bool')
2410                         $default = " DEFAULT 0 ";
2411                 elseif (!isset($default))
2412                         $default = '';
2413
2414                 $auto_increment = '';
2415                 if(!empty($fieldDef['auto_increment']) && $fieldDef['auto_increment'])
2416                         $auto_increment = $this->setAutoIncrement($table , $fieldDef['name']);
2417
2418                 $required = 'NULL';  // MySQL defaults to NULL, SQL Server defaults to NOT NULL -- must specify
2419                 //Starting in 6.0, only ID and auto_increment fields will be NOT NULL in the DB.
2420                 if ((empty($fieldDef['isnull']) || strtolower($fieldDef['isnull']) == 'false') &&
2421                         (!empty($auto_increment) || $name == 'id' || ($fieldDef['type'] == 'id' && !empty($fieldDef['required'])))) {
2422                         $required =  "NOT NULL";
2423                 }
2424                 // If the field is marked both required & isnull=>false - alwqys make it not null
2425                 // Use this to ensure primary key fields never defined as null
2426                 if(isset($fieldDef['isnull']) && (strtolower($fieldDef['isnull']) == 'false' || $fieldDef['isnull'] === false)
2427                         && !empty($fieldDef['required'])) {
2428                         $required =  "NOT NULL";
2429                 }
2430                 if ($ignoreRequired)
2431                         $required = "";
2432
2433                 if ( $return_as_array ) {
2434                         return array(
2435                                 'name' => $name,
2436                                 'colType' => $colType,
2437                                 'default' => $default,
2438                                 'required' => $required,
2439                                 'auto_increment' => $auto_increment,
2440                                 'full' => "$name $colType $default $required $auto_increment",
2441                                 );
2442                 } else {
2443                         return "$name $colType $default $required $auto_increment";
2444                 }
2445         }
2446
2447         /**
2448          * Returns SQL defintions for all columns in a table
2449          *
2450          * @param  array  $fieldDefs  Vardef-format field def
2451          * @param  bool   $ignoreRequired Optional, true if we should ignor this being a required field
2452          * @param  string $tablename      Optional, table name
2453          * @return string SQL column definitions
2454          */
2455         protected function columnSQLRep($fieldDefs, $ignoreRequired = false, $tablename)
2456         {
2457                 $columns = array();
2458
2459                 if ($this->isFieldArray($fieldDefs)) {
2460                         foreach ($fieldDefs as $fieldDef) {
2461                                 if(!isset($fieldDef['source']) || $fieldDef['source'] == 'db') {
2462                                         $columns[] = $this->oneColumnSQLRep($fieldDef,false, $tablename);
2463                                 }
2464                         }
2465                         $columns = implode(",", $columns);
2466                 }
2467                 else {
2468                         $columns = $this->oneColumnSQLRep($fieldDefs,$ignoreRequired, $tablename);
2469                 }
2470
2471                 return $columns;
2472         }
2473
2474         /**
2475          * Returns the next value for an auto increment
2476          * @abstract
2477          * @param  string $table Table name
2478          * @param  string $field_name Field name
2479          * @return string
2480          */
2481         public function getAutoIncrement($table, $field_name)
2482         {
2483                 return "";
2484         }
2485
2486         /**
2487          * Returns the sql for the next value in a sequence
2488          * @abstract
2489          * @param  string $table  Table name
2490          * @param  string $field_name  Field name
2491          * @return string
2492          */
2493         public function getAutoIncrementSQL($table, $field_name)
2494         {
2495                 return "";
2496         }
2497
2498         /**
2499          * Either creates an auto increment through queries or returns sql for auto increment
2500          * that can be appended to the end of column defination (mysql)
2501          * @abstract
2502          * @param  string $table Table name
2503          * @param  string $field_name Field name
2504          * @return string
2505          */
2506         protected function setAutoIncrement($table, $field_name)
2507         {
2508                 $this->deleteAutoIncrement($table, $field_name);
2509                 return "";
2510         }
2511
2512     /**
2513      * Sets the next auto-increment value of a column to a specific value.
2514      * @abstract
2515      * @param  string $table Table name
2516      * @param  string $field_name Field name
2517      * @param  int $start_value  Starting autoincrement value
2518      * @return string
2519      *
2520      */
2521         public function setAutoIncrementStart($table, $field_name, $start_value)
2522         {
2523                 return "";
2524         }
2525
2526         /**
2527          * Deletes an auto increment
2528          * @abstract
2529          * @param string $table tablename
2530          * @param string $field_name
2531          */
2532         public function deleteAutoIncrement($table, $field_name)
2533         {
2534                 return;
2535         }
2536
2537         /**
2538          * This method generates sql for adding a column to table identified by field def.
2539          *
2540          * @param  string $tablename
2541          * @param  array  $fieldDefs
2542          * @return string SQL statement
2543          */
2544         public function addColumnSQL($tablename, $fieldDefs)
2545         {
2546             return $this->changeColumnSQL($tablename, $fieldDefs, 'add');
2547         }
2548
2549         /**
2550          * This method genrates sql for altering old column identified by oldFieldDef to new fieldDef.
2551          *
2552          * @param  string $tablename
2553          * @param  array  $newFieldDefs
2554          * @param  bool  $ignorerequired Optional, true if we should ignor this being a required field
2555          * @return string|array SQL statement(s)
2556          */
2557         public function alterColumnSQL($tablename, $newFieldDefs, $ignorerequired = false)
2558         {
2559                 return $this->changeColumnSQL($tablename, $newFieldDefs, 'modify', $ignorerequired);
2560         }
2561
2562         /**
2563          * Generates SQL for dropping a table.
2564          *
2565          * @param  SugarBean $bean Sugarbean instance
2566          * @return string SQL statement
2567          */
2568         public function dropTableSQL(SugarBean $bean)
2569         {
2570                 return $this->dropTableNameSQL($bean->getTableName());
2571         }
2572
2573         /**
2574          * Generates SQL for dropping a table.
2575          *
2576          * @param  string $name table name
2577          * @return string SQL statement
2578          */
2579         public function dropTableNameSQL($name)
2580         {
2581                 return "DROP TABLE ".$name;
2582         }
2583
2584         /**
2585          * Generates SQL for truncating a table.
2586          * @param  string $name  table name
2587          * @return string
2588          */
2589         public function truncateTableSQL($name)
2590         {
2591                 return "TRUNCATE $name";
2592         }
2593
2594         /**
2595          * This method generates sql that deletes a column identified by fieldDef.
2596          *
2597          * @param  SugarBean $bean      Sugarbean instance
2598          * @param  array  $fieldDefs
2599          * @return string SQL statement
2600          */
2601         public function deleteColumnSQL(SugarBean $bean, $fieldDefs)
2602         {
2603                 return $this->dropColumnSQL($bean->getTableName(), $fieldDefs);
2604         }
2605
2606         /**
2607          * This method generates sql that drops a column identified by fieldDef.
2608          * Designed to work like the other addColumnSQL() and alterColumnSQL() functions
2609          *
2610          * @param  string $tablename
2611          * @param  array  $fieldDefs
2612          * @return string SQL statement
2613          */
2614         public function dropColumnSQL($tablename, $fieldDefs)
2615         {
2616                 return $this->changeColumnSQL($tablename, $fieldDefs, 'drop');
2617         }
2618
2619     /**
2620      * Return a version of $proposed that can be used as a column name in any of our supported databases
2621      * Practically this means no longer than 25 characters as the smallest identifier length for our supported DBs is 30 chars for Oracle plus we add on at least four characters in some places (for indicies for example)
2622      * @param string|array $name Proposed name for the column
2623      * @param bool|string $ensureUnique Ensure the name is unique
2624      * @param string $type Name type (table, column)
2625      * @param bool $force Force new name
2626      * @return string|array Valid column name trimmed to right length and with invalid characters removed
2627      */
2628         public function getValidDBName($name, $ensureUnique = false, $type = 'column', $force = false)
2629         {
2630                 if(is_array($name)) {
2631                         $result = array();
2632                         foreach($name as $field) {
2633                                 $result[] = $this->getValidDBName($field, $ensureUnique, $type);
2634                         }
2635                         return $result;
2636                 } else {
2637                     if(strchr($name, ".")) {
2638                         // this is a compound name with dots, handle separately
2639                         $parts = explode(".", $name);
2640                         if(count($parts) > 2) {
2641                             // some weird name, cut to table.name
2642                             array_splice($parts, 0, count($parts)-2);
2643                             $parts = $this->getValidDBName($parts, $ensureUnique, $type, $force);
2644                     return join(".", $parts);
2645                         }
2646                     }
2647                         // first strip any invalid characters - all but word chars (which is alphanumeric and _)
2648                         $name = preg_replace( '/[^\w]+/i', '', $name ) ;
2649                         $len = strlen( $name ) ;
2650                         $maxLen = empty($this->maxNameLengths[$type]) ? $this->maxNameLengths[$type]['column'] : $this->maxNameLengths[$type];
2651                         if ($len <= $maxLen && !$force) {
2652                                 return strtolower($name);
2653                         }
2654                         if ($ensureUnique) {
2655                                 $md5str = md5($name);
2656                                 $tail = substr ( $name, -11) ;
2657                                 $temp = substr($md5str , strlen($md5str)-4 );
2658                                 $result = substr( $name, 0, 10) . $temp . $tail ;
2659                         } else {
2660                                 $result = substr( $name, 0, 11) . substr( $name, 11 - $maxLen);
2661                         }
2662
2663                         return strtolower( $result ) ;
2664                 }
2665         }
2666
2667         /**
2668          * Returns the valid type for a column given the type in fieldDef
2669          *
2670          * @param  string $type field type
2671          * @return string valid type for the given field
2672          */
2673         public function getColumnType($type)
2674         {
2675                 return isset($this->type_map[$type])?$this->type_map[$type]:$type;
2676         }
2677
2678         /**
2679          * Checks to see if passed array is truely an array of defitions
2680          *
2681          * Such an array may have type as a key but it will point to an array
2682          * for a true array of definitions an to a col type for a definition only
2683          *
2684          * @param  mixed $defArray
2685          * @return bool
2686          */
2687         public function isFieldArray($defArray)
2688         {
2689                 if ( !is_array($defArray) )
2690                         return false;
2691
2692                 if ( isset($defArray['type']) ){
2693                         // type key exists. May be an array of defs or a simple definition
2694                         return is_array($defArray['type']); // type is not an array => definition else array
2695                 }
2696
2697                 // type does not exist. Must be array of definitions
2698                 return true;
2699         }
2700
2701         /**
2702          * returns true if the type can be mapped to a valid column type
2703          *
2704          * @param  string $type
2705          * @return bool
2706          */
2707         protected function validColumnType($type)
2708         {
2709                 $type = $this->getColumnType($type);
2710                 return !empty($type);
2711         }
2712
2713         /**
2714          * Generate query for audit table
2715          * @param SugarBean $bean SugarBean that was changed
2716          * @param array $changes List of changes, contains 'before' and 'after'
2717      * @return string  Audit table INSERT query
2718      */
2719         protected function auditSQL(SugarBean $bean, $changes)
2720         {
2721                 global $current_user;
2722                 $sql = "INSERT INTO ".$bean->get_audit_table_name();
2723                 //get field defs for the audit table.
2724                 require('metadata/audit_templateMetaData.php');
2725                 $fieldDefs = $dictionary['audit']['fields'];
2726
2727                 $values=array();
2728                 $values['id'] = $this->massageValue(create_guid(), $fieldDefs['id']);
2729                 $values['parent_id']= $this->massageValue($bean->id, $fieldDefs['parent_id']);
2730                 $values['field_name']= $this->massageValue($changes['field_name'], $fieldDefs['field_name']);
2731                 $values['data_type'] = $this->massageValue($changes['data_type'], $fieldDefs['data_type']);
2732                 if ($changes['data_type']=='text') {
2733                         $bean->fetched_row[$changes['field_name']]=$changes['after'];;
2734                         $values['before_value_text'] = $this->massageValue($changes['before'], $fieldDefs['before_value_text']);
2735                         $values['after_value_text'] = $this->massageValue($changes['after'], $fieldDefs['after_value_text']);
2736                 } else {
2737                         $bean->fetched_row[$changes['field_name']]=$changes['after'];;
2738                         $values['before_value_string'] = $this->massageValue($changes['before'], $fieldDefs['before_value_string']);
2739                         $values['after_value_string'] = $this->massageValue($changes['after'], $fieldDefs['after_value_string']);
2740                 }
2741                 $values['date_created'] = $this->massageValue(TimeDate::getInstance()->nowDb(), $fieldDefs['date_created'] );
2742                 $values['created_by'] = $this->massageValue($current_user->id, $fieldDefs['created_by']);
2743
2744                 $sql .= "(".implode(",", array_keys($values)).") ";
2745                 $sql .= "VALUES(".implode(",", $values).")";
2746                 return $sql;
2747         }
2748
2749     /**
2750      * Saves changes to module's audit table
2751      *
2752      * @param SugarBean $bean Sugarbean instance that was changed
2753      * @param array $changes List of changes, contains 'before' and 'after'
2754      * @return bool query result
2755      *
2756      */
2757         public function save_audit_records(SugarBean $bean, $changes)
2758         {
2759                 return $this->query($this->auditSQL($bean, $changes));
2760         }
2761
2762     /**
2763      * Uses the audit enabled fields array to find fields whose value has changed.
2764      * The before and after values are stored in the bean.
2765      * Uses $bean->fetched_row to compare
2766      *
2767      * @param SugarBean $bean Sugarbean instance that was changed
2768      * @return array
2769      */
2770         public function getDataChanges(SugarBean &$bean)
2771         {
2772                 $changed_values=array();
2773                 $audit_fields=$bean->getAuditEnabledFieldDefinitions();
2774
2775                 if (is_array($audit_fields) and count($audit_fields) > 0) {
2776                         foreach ($audit_fields as $field=>$properties) {
2777                                 if (!empty($bean->fetched_row) && array_key_exists($field, $bean->fetched_row)) {
2778                                         $before_value=$bean->fetched_row[$field];
2779                                         $after_value=$bean->$field;
2780                                         if (isset($properties['type'])) {
2781                                                 $field_type=$properties['type'];
2782                                         } else {
2783                                                 if (isset($properties['dbType']))
2784                                                         $field_type=$properties['dbType'];
2785                                                 else if(isset($properties['data_type']))
2786                                                         $field_type=$properties['data_type'];
2787                                                 else
2788                                                         $field_type=$properties['dbtype'];
2789                                         }
2790
2791                                         //Because of bug #25078(sqlserver haven't 'date' type, trim extra "00:00:00" when insert into *_cstm table).
2792                                         // so when we read the audit datetime field from sqlserver, we have to replace the extra "00:00:00" again.
2793                                         if(!empty($field_type) && $field_type == 'date'){
2794                                                 $before_value = $this->fromConvert($before_value , $field_type);
2795                                         }
2796                                         //if the type and values match, do nothing.
2797                                         if (!($this->_emptyValue($before_value,$field_type) && $this->_emptyValue($after_value,$field_type))) {
2798                                                 if (trim($before_value) !== trim($after_value)) {
2799                             // Bug #42475: Don't directly compare numeric values, instead do the subtract and see if the comparison comes out to be "close enough", it is necessary for floating point numbers.
2800                             // Manual merge of fix 95727f2eed44852f1b6bce9a9eccbe065fe6249f from DBHelper
2801                             // This fix also fixes Bug #44624 in a more generic way and therefore eliminates the need for fix 0a55125b281c4bee87eb347709af462715f33d2d in DBHelper
2802                                                         if (!($this->isNumericType($field_type) &&
2803                                   abs(
2804                                       2*((trim($before_value)+0)-(trim($after_value)+0))/((trim($before_value)+0)+(trim($after_value)+0)) // Using relative difference so that it also works for other numerical types besides currencies
2805                                   )<0.0000000001)) {    // Smaller than 10E-10
2806                                                                 if (!($this->isBooleanType($field_type) && ($this->_getBooleanValue($before_value)== $this->_getBooleanValue($after_value)))) {
2807                                                                         $changed_values[$field]=array('field_name'=>$field,
2808                                                                                 'data_type'=>$field_type,
2809                                                                                 'before'=>$before_value,
2810                                                                                 'after'=>$after_value);
2811                                                                 }
2812                                                         }
2813                                                 }
2814                                         }
2815                                 }
2816                         }
2817                 }
2818                 return $changed_values;
2819         }
2820
2821         /**
2822          * Setup FT indexing
2823          * @abstract
2824          */
2825         public function full_text_indexing_setup()
2826         {
2827                 // Most DBs have nothing to setup, so provide default empty function
2828         }
2829
2830         /**
2831          * Quotes a string for storing in the database
2832          * @deprecated
2833          * Return value will be not surrounded by quotes
2834          *
2835          * @param  string $string
2836          * @return string
2837          */
2838         public function escape_quote($string)
2839         {
2840                 return $this->quote($string);
2841         }
2842
2843         /**
2844          * Quotes a string for storing in the database
2845          * @deprecated
2846          * Return value will be not surrounded by quotes
2847          *
2848          * @param  string $string
2849          * @return string
2850          */
2851         public function quoteFormEmail($string)
2852         {
2853                 return $this->quote($string);
2854         }
2855
2856     /**
2857      * Renames an index using fields definition
2858      *
2859      * @param  array  $old_definition
2860      * @param  array  $new_definition
2861      * @param  string $table_name
2862      * @return string SQL statement
2863      */
2864         public function renameIndexDefs($old_definition, $new_definition, $table_name)
2865         {
2866                 return array($this->add_drop_constraint($table_name,$old_definition,true),
2867                                 $this->add_drop_constraint($table_name,$new_definition), false);
2868         }
2869
2870         /**
2871          * Check if type is boolean
2872          * @param string $type
2873      * @return bool
2874      */
2875         public function isBooleanType($type)
2876         {
2877                 return 'bool' == $type;
2878         }
2879
2880         /**
2881          * Get truth value for boolean type
2882          * Allows 'off' to mean false, along with all 'empty' values
2883          * @param mixed $val
2884      * @return bool
2885          */
2886         protected function _getBooleanValue($val)
2887         {
2888                 //need to put the === sign here otherwise true == 'non empty string'
2889                 if (empty($val) or $val==='off')
2890                         return false;
2891
2892                 return true;
2893         }
2894
2895         /**
2896          * Check if type is a number
2897          * @param string $type
2898      * @return bool
2899          */
2900         public function isNumericType($type)
2901         {
2902             if(isset($this->type_class[$type]) && ($this->type_class[$type] == 'int' || $this->type_class[$type] == 'float')) {
2903                 return true;
2904             }
2905                 return false;
2906         }
2907
2908     /**
2909      * Check if the value is empty value for this type
2910      * @param mixed $val Value
2911      * @param string $type Type (one of vardef types)
2912      * @return bool true if the value if empty
2913      */
2914         protected function _emptyValue($val, $type)
2915         {
2916                 if (empty($val))
2917                         return true;
2918
2919                 if($this->emptyValue($type) == $val) {
2920                         return true;
2921                 }
2922                 switch ($type) {
2923                         case 'decimal':
2924                         case 'decimal2':
2925                         case 'int':
2926                         case 'double':
2927                         case 'float':
2928                         case 'uint':
2929                         case 'ulong':
2930                         case 'long':
2931                         case 'short':
2932                                 return ($val == 0);
2933                         case 'date':
2934                                 if ($val == '0000-00-00')
2935                                         return true;
2936                                 if ($val == 'NULL')
2937                                         return true;
2938                                 return false;
2939                 }
2940
2941                 return false;
2942         }
2943
2944         /**
2945      * @abstract
2946          * Does this type represent text (i.e., non-varchar) value?
2947          * @param string $type
2948      * @return bool
2949          */
2950         public function isTextType($type)
2951         {
2952                 return false;
2953         }
2954
2955         /**
2956          * Check if this DB supports certain capability
2957          * See $this->capabilities for the list
2958          * @param string $cap
2959      * @return bool
2960          */
2961         public function supports($cap)
2962         {
2963                 return !empty($this->capabilities[$cap]);
2964         }
2965
2966         /**
2967          * Create ORDER BY clause for ENUM type field
2968          * @param string $order_by Field name
2969          * @param array $values Possible enum value
2970          * @param string $order_dir Order direction, ASC or DESC
2971      * @return string
2972      */
2973         public function orderByEnum($order_by, $values, $order_dir)
2974         {
2975                 $i = 0;
2976                 $order_by_arr = array();
2977                 foreach ($values as $key => $value) {
2978                         if($key == '') {
2979                                 $order_by_arr[] = "WHEN ($order_by='' OR $order_by IS NULL) THEN $i";
2980                         } else {
2981                                 $order_by_arr[] = "WHEN $order_by=".$this->quoted($key)." THEN $i";
2982                         }
2983                         $i++;
2984                 }
2985                 return "CASE ".implode("\n", $order_by_arr)." ELSE $i END $order_dir\n";
2986         }
2987
2988         /**
2989          * Return representation of an empty value depending on type
2990          * The value is fully quoted, converted, etc.
2991          * @param string $type
2992      * @return mixed Empty value
2993      */
2994         public function emptyValue($type)
2995         {
2996                 if(isset($this->type_class[$type]) && ($this->type_class[$type] == 'bool' || $this->type_class[$type] == 'int' || $this->type_class[$type] == 'float')) {
2997                         return 0;
2998                 }
2999
3000                 return "''";
3001         }
3002
3003         /**
3004          * List of available collation settings
3005      * @abstract
3006          * @return string
3007          */
3008         public function getDefaultCollation()
3009         {
3010                 return null;
3011         }
3012
3013         /**
3014          * List of available collation settings
3015      * @abstract
3016          * @return array
3017          */
3018         public function getCollationList()
3019         {
3020                 return null;
3021         }
3022
3023         /**
3024          * Returns the number of columns in a table
3025          *
3026          * @param  string $table_name
3027          * @return int
3028          */
3029         public function number_of_columns($table_name)
3030         {
3031                 $table = $this->getTableDescription($table_name);
3032                 return count($table);
3033         }
3034
3035         /**
3036          * Return limit query based on given query
3037          * @param string $sql
3038          * @param int $start
3039          * @param int $count
3040          * @param bool $dieOnError
3041          * @param string $msg
3042      * @return resource|bool query result
3043      * @see DBManager::limitQuery()
3044          */
3045         public function limitQuerySql($sql, $start, $count, $dieOnError=false, $msg='')
3046         {
3047                 return $this->limitQuery($sql,$start,$count,$dieOnError,$msg,false);
3048         }
3049
3050         /**
3051          * Return current time in format fit for insertion into DB (with quotes)
3052          * @return string
3053          */
3054         public function now()
3055         {
3056                 return $this->convert($this->quoted(TimeDate::getInstance()->nowDb()), "datetime");
3057         }
3058
3059         /**
3060          * Check if connecting user has certain privilege
3061          * @param string $privilege
3062      * @return bool Privilege allowed?
3063      */
3064         public function checkPrivilege($privilege)
3065         {
3066                 switch($privilege) {
3067                         case "CREATE TABLE":
3068                                 $this->query("CREATE TABLE temp (id varchar(36))");
3069                                 break;
3070                         case "DROP TABLE":
3071                                 $sql = $this->dropTableNameSQL("temp");
3072                                 $this->query($sql);
3073                                 break;
3074                         case "INSERT":
3075                                 $this->query("INSERT INTO temp (id) VALUES ('abcdef0123456789abcdef0123456789abcd')");
3076                                 break;
3077                         case "UPDATE":
3078                                 $this->query("UPDATE temp SET id = '100000000000000000000000000000000000' WHERE id = 'abcdef0123456789abcdef0123456789abcd'");
3079                                 break;
3080                         case 'SELECT':
3081                                 return $this->getOne('SELECT id FROM temp WHERE id=\'100000000000000000000000000000000000\'', false);
3082                         case 'DELETE':
3083                                 $this->query("DELETE FROM temp WHERE id = '100000000000000000000000000000000000'");
3084                                 break;
3085                         case "ADD COLUMN":
3086                                 $test = array("test" => array("name" => "test", "type" => "varchar", "len" => 50));
3087                                 $sql =  $this->changeColumnSQL("temp", $test, "add");
3088                                 $this->query($sql);
3089                                 break;
3090                         case "CHANGE COLUMN":
3091                                 $test = array("test" => array("name" => "test", "type" => "varchar", "len" => 100));
3092                                 $sql =  $this->changeColumnSQL("temp", $test, "modify");
3093                                 $this->query($sql);
3094                                 break;
3095                         case "DROP COLUMN":
3096                                 $test = array("test" => array("name" => "test", "type" => "varchar", "len" => 100));
3097                                 $sql =  $this->changeColumnSQL("temp", $test, "drop");
3098                                 $this->query($sql);
3099                                 break;
3100                         default:
3101                                 return false;
3102                 }
3103                 if($this->checkError("Checking privileges")) {
3104                         return false;
3105                 }
3106                 return true;
3107         }
3108
3109         /**
3110          * Check if the query is a select query
3111          * @param string $query
3112      * @return bool  Is query SELECT?
3113      */
3114         protected function isSelect($query)
3115         {
3116                 $query = trim($query);
3117                 $select_check = strpos(strtolower($query), strtolower("SELECT"));
3118                 //Checks to see if there is union select which is valid
3119                 $select_check2 = strpos(strtolower($query), strtolower("(SELECT"));
3120                 if($select_check==0 || $select_check2==0){
3121                         //Returning false means query is ok!
3122                         return true;
3123                 }
3124                 return false;
3125         }
3126
3127         /**
3128          * Parse fulltext search query with mysql syntax:
3129          *  terms quoted by ""
3130          *  + means the term must be included
3131          *  - means the term must be excluded
3132          *  * or % at the end means wildcard
3133          * @param string $query
3134          * @return array of 3 elements - query terms, mandatory terms and excluded terms
3135          */
3136         public function parseFulltextQuery($query)
3137         {
3138                 /* split on space or comma, double quotes with \ for escape */
3139                 if(strpbrk($query, " ,")) {
3140                         // ("([^"]*?)"|[^" ,]+)((, )+)?
3141                         // '/([^" ,]+|".*?[^\\\\]")(,|\s)\s*/'
3142                         if(!preg_match_all('/("([^"]*?)"|[^"\s,]+)((,\s)+)?/', $query, $m)) {
3143                                 return false;
3144                         }
3145                         $qterms = $m[1];
3146                 } else {
3147                         $qterms = array($query);
3148                 }
3149                 $terms = $must_terms = $not_terms = array();
3150                 foreach($qterms as $item) {
3151                         if($item[0] == '"') {
3152                                 $item = trim($item, '"');
3153                         }
3154                         if($item[0] == '+') {
3155                                 $must_terms[] = substr($item, 1);
3156                                 continue;
3157                         }
3158                         if($item[0] == '-') {
3159                                 $not_terms[] = substr($item, 1);
3160                                 continue;
3161                         }
3162                         $terms[] = $item;
3163                 }
3164                 return array($terms, $must_terms, $not_terms);
3165         }
3166
3167     // Methods to check respective queries
3168         protected $standardQueries = array(
3169                 'ALTER TABLE' => 'verifyAlterTable',
3170                 'DROP TABLE' => 'verifyDropTable',
3171                 'CREATE TABLE' => 'verifyCreateTable',
3172                 'INSERT INTO' => 'verifyInsertInto',
3173                 'UPDATE' => 'verifyUpdate',
3174                 'DELETE FROM' => 'verifyDeleteFrom',
3175         );
3176
3177
3178     /**
3179      * Extract table name from a query
3180      * @param string $query SQL query
3181      * @return string
3182      */
3183         protected function extractTableName($query)
3184         {
3185         $query = preg_replace('/[^A-Za-z0-9_\s]/', "", $query);
3186         $query = trim(str_replace(array_keys($this->standardQueries), '', $query));
3187
3188         $firstSpc = strpos($query, " ");
3189         $end = ($firstSpc > 0) ? $firstSpc : strlen($query);
3190         $table = substr($query, 0, $end);
3191
3192         return $table;
3193         }
3194
3195     /**
3196      * Verify SQl statement using per-DB verification function
3197      * provided the function exists
3198      * @param string $query Query to verify
3199      * @param array $skipTables List of blacklisted tables that aren't checked
3200      * @return string
3201      */
3202         public function verifySQLStatement($query, $skipTables)
3203         {
3204                 $query = trim($query);
3205                 foreach($this->standardQueries as $qstart => $check) {
3206                         if(strncasecmp($qstart, $query, strlen($qstart)) == 0) {
3207                                 if(is_callable(array($this, $check))) {
3208                                         $table = $this->extractTableName($query);
3209                                         if(!in_array($table, $skipTables)) {
3210                                                 return call_user_func(array($this, $check), $table, $query);
3211                                         } else {
3212                                                 $this->log->debug("Skipping table $table as blacklisted");
3213                                         }
3214                                 } else {
3215                                         $this->log->debug("No verification for $qstart on {$this->dbType}");
3216                                 }
3217                                 break;
3218                         }
3219                 }
3220                 return "";
3221         }
3222
3223         /**
3224          * Tests an CREATE TABLE query
3225          * @param string $table The table name to get DDL
3226          * @param string $query The query to test.
3227          * @return string Non-empty if error found
3228          */
3229         protected function verifyCreateTable($table, $query)
3230         {
3231                 $this->log->debug('verifying CREATE statement...');
3232
3233                 // rewrite DDL with _temp name
3234                 $this->log->debug('testing query: ['.$query.']');
3235                 $tempname = $table."__uw_temp";
3236                 $tempTableQuery = str_replace("CREATE TABLE {$table}", "CREATE TABLE $tempname", $query);
3237
3238                 if(strpos($tempTableQuery, '__uw_temp') === false) {
3239                         return 'Could not use a temp table to test query!';
3240                 }
3241
3242                 $this->query($tempTableQuery, false, "Preflight Failed for: {$query}");
3243
3244                 $error = $this->lastError(); // empty on no-errors
3245                 if(!empty($error)) {
3246                         return $error;
3247                 }
3248
3249                 // check if table exists
3250                 $this->log->debug('testing for table: '.$table);
3251                 if(!$this->tableExists($tempname)) {
3252                         return "Failed to create temp table!";
3253                 }
3254
3255                 $this->dropTableName($tempname);
3256                 return '';
3257         }
3258
3259         /**
3260          * Execute multiple queries one after another
3261          * @param array $sqls Queries
3262          * @param bool $dieOnError Die on error, passed to query()
3263          * @param string $msg Error message, passed to query()
3264          * @param bool $suppress Supress errors, passed to query()
3265          * @return resource|bool result set or success/failure bool
3266          */
3267         public function queryArray(array $sqls, $dieOnError = false, $msg = '', $suppress = false)
3268         {
3269                 $last = true;
3270                 foreach($sqls as $sql) {
3271                         if(!($last = $this->query($sql, $dieOnError, $msg, $suppress))) {
3272                                 break;
3273                         }
3274                 }
3275                 return $last;
3276         }
3277
3278         /**
3279          * Fetches the next row in the query result into an associative array
3280          *
3281          * @param  resource $result
3282          * @param  bool $encode Need to HTML-encode the result?
3283          * @return array    returns false if there are no more rows available to fetch
3284          */
3285         public function fetchByAssoc($result, $encode = true)
3286         {
3287             if (empty($result)) return false;
3288
3289             if(is_int($encode) && func_num_args() == 3) {
3290                 // old API: $result, $rowNum, $encode
3291                 $GLOBALS['log']->deprecated("Using row number in fetchByAssoc is not portable and no longer supported. Please fix your code.");
3292                 $encode = func_get_arg(2);
3293             }
3294             $row = $this->fetchRow($result);
3295             if (!empty($row) && $encode && $this->encode) {
3296                 return array_map('to_html', $row);
3297             } else {
3298                return $row;
3299             }
3300         }
3301
3302         /**
3303          * Get DB driver name used for install/upgrade scripts
3304          * @return string
3305          */
3306         public function getScriptName()
3307         {
3308                 // Usually the same name as dbType
3309                 return $this->dbType;
3310         }
3311
3312         /**
3313          * Set database options
3314          * Options are usually db-dependant and derive from $config['dbconfigoption']
3315          * @param array $options
3316          * @return DBManager
3317          */
3318         public function setOptions($options)
3319         {
3320             $this->options = $options;
3321             return $this;
3322         }
3323
3324         /**
3325          * Get DB options
3326          * @return array
3327          */
3328         public function getOptions()
3329         {
3330             return $this->options;
3331         }
3332
3333         /**
3334          * Get DB option by name
3335          * @param string $option Option name
3336          * @return mixed Option value or null if doesn't exist
3337          */
3338         public function getOption($option)
3339         {
3340             if(isset($this->options[$option])) {
3341                 return $this->options[$option];
3342             }
3343             return null;
3344         }
3345
3346         /**
3347          * Commits pending changes to the database when the driver is setup to support transactions.
3348          * Note that the default implementation is applicable for transaction-less or auto commit scenarios.
3349          * @abstract
3350          * @return bool true if commit succeeded, false if it failed
3351          */
3352         public function commit()
3353         {
3354                 $this->log->info("DBManager.commit() stub");
3355                 return true;
3356         }
3357
3358         /**
3359          * Rollsback pending changes to the database when the driver is setup to support transactions.
3360          * Note that the default implementation is applicable for transaction-less or auto commit scenarios.
3361          * Since rollbacks cannot be done, this implementation always returns false.
3362          * @abstract
3363          * @return bool true if rollback succeeded, false if it failed
3364          */
3365         public function rollback()
3366         {
3367                 $this->log->info("DBManager.rollback() stub");
3368                 return false;
3369         }
3370
3371         /**
3372          * Check if this DB name is valid
3373          *
3374          * @param string $name
3375          * @return bool
3376          */
3377         public function isDatabaseNameValid($name)
3378         {
3379                 // Generic case - no slashes, no dots
3380                 return preg_match('#[/.\\\\]#', $name)==0;
3381         }
3382
3383         /**
3384          * Check special requirements for DB installation.
3385          * @abstract
3386          * If everything is OK, return true.
3387          * If something's wrong, return array of error code and parameters
3388          * @return mixed
3389          */
3390         public function canInstall()
3391         {
3392                 return true;
3393         }
3394
3395         /**
3396          * @abstract
3397      * Code run on new database before installing
3398          */
3399         public function preInstall()
3400         {
3401         }
3402
3403         /**
3404      * @abstract
3405          * Code run on new database after installing
3406          */
3407         public function postInstall()
3408         {
3409         }
3410
3411         /**
3412          * Disable keys on the table
3413          * @abstract
3414          * @param string $tableName
3415          */
3416         public function disableKeys($tableName)
3417         {
3418         }
3419
3420         /**
3421          * Re-enable keys on the table
3422          * @abstract
3423          * @param string $tableName
3424          */
3425         public function enableKeys($tableName)
3426         {
3427         }
3428
3429         /**
3430          * Quote string in DB-specific manner
3431          * @param string $string
3432          * @return string
3433          */
3434         abstract public function quote($string);
3435
3436         /**
3437          * Use when you need to convert a database string to a different value; this function does it in a
3438          * database-backend aware way
3439          * Supported conversions:
3440          *      today           return current date
3441          *      left            Take substring from the left
3442          *      date_format     Format date as string, supports %Y-%m-%d, %Y-%m, %Y
3443      *      time_format Format time as string
3444      *      date        Convert date string to datetime value
3445      *      time        Convert time string to datetime value
3446          *      datetime        Convert datetime string to datetime value
3447          *      ifnull          If var is null, use default value
3448          *      concat          Concatenate strings
3449          *      quarter         Quarter number of the date
3450          *      length          Length of string
3451          *      month           Month number of the date
3452          *      add_date        Add specified interval to a date
3453      *      add_time    Add time interval to a date
3454      *      text2char   Convert text field to varchar
3455          *
3456          * @param string $string database string to convert
3457          * @param string $type type of conversion to do
3458          * @param array  $additional_parameters optional, additional parameters to pass to the db function
3459          * @return string
3460          */
3461         abstract public function convert($string, $type, array $additional_parameters = array());
3462
3463         /**
3464          * Converts from Database data to app data
3465          *
3466          * Supported types
3467          * - date
3468          * - time
3469          * - datetime
3470      * - datetimecombo
3471      * - timestamp
3472          *
3473          * @param string $string database string to convert
3474          * @param string $type type of conversion to do
3475          * @return string
3476          */
3477         abstract public function fromConvert($string, $type);
3478
3479     /**
3480      * Parses and runs queries
3481      *
3482      * @param  string   $sql        SQL Statement to execute
3483      * @param  bool     $dieOnError True if we want to call die if the query returns errors
3484      * @param  string   $msg        Message to log if error occurs
3485      * @param  bool     $suppress   Flag to suppress all error output unless in debug logging mode.
3486      * @param  bool     $keepResult Keep query result in the object?
3487      * @return resource|bool result set or success/failure bool
3488      */
3489         abstract public function query($sql, $dieOnError = false, $msg = '', $suppress = false, $keepResult = false);
3490
3491     /**
3492      * Runs a limit query: one where we specify where to start getting records and how many to get
3493      *
3494      * @param  string   $sql     SELECT query
3495      * @param  int      $start   Starting row
3496      * @param  int      $count   How many rows
3497      * @param  boolean  $dieOnError  True if we want to call die if the query returns errors
3498      * @param  string   $msg     Message to log if error occurs
3499      * @param  bool     $execute Execute or return SQL?
3500      * @return resource query result
3501      */
3502         abstract function limitQuery($sql, $start, $count, $dieOnError = false, $msg = '', $execute = true);
3503
3504
3505         /**
3506          * Free Database result
3507          * @param resource $dbResult
3508          */
3509         abstract protected function freeDbResult($dbResult);
3510
3511         /**
3512          * Rename column in the DB
3513          * @param string $tablename
3514          * @param string $column
3515          * @param string $newname
3516          */
3517         abstract function renameColumnSQL($tablename, $column, $newname);
3518
3519         /**
3520          * Returns definitions of all indies for passed table.
3521          *
3522          * return will is a multi-dimensional array that
3523          * categorizes the index definition by types, unique, primary and index.
3524          * <code>
3525          * <?php
3526          * array(                                                              O
3527          *       'index1'=> array (
3528          *           'name'   => 'index1',
3529          *           'type'   => 'primary',
3530          *           'fields' => array('field1','field2')
3531          *           )
3532          *       )
3533          * ?>
3534          * </code>
3535          * This format is similar to how indicies are defined in vardef file.
3536          *
3537          * @param  string $tablename
3538          * @return array
3539          */
3540         abstract public function get_indices($tablename);
3541
3542         /**
3543          * Returns definitions of all indies for passed table.
3544          *
3545          * return will is a multi-dimensional array that
3546          * categorizes the index definition by types, unique, primary and index.
3547          * <code>
3548          * <?php
3549          * array(
3550          *       'field1'=> array (
3551          *           'name'   => 'field1',
3552          *           'type'   => 'varchar',
3553          *           'len' => '200'
3554          *           )
3555          *       )
3556          * ?>
3557          * </code>
3558          * This format is similar to how indicies are defined in vardef file.
3559          *
3560          * @param  string $tablename
3561          * @return array
3562          */
3563         abstract public function get_columns($tablename);
3564
3565         /**
3566          * Generates alter constraint statement given a table name and vardef definition.
3567          *
3568          * Supports both adding and droping a constraint.
3569          *
3570          * @param  string $table      tablename
3571          * @param  array  $definition field definition
3572          * @param  bool   $drop       true if we are dropping the constraint, false if we are adding it
3573          * @return string SQL statement
3574          */
3575         abstract public function add_drop_constraint($table, $definition, $drop = false);
3576
3577         /**
3578          * Returns the description of fields based on the result
3579          *
3580          * @param  resource $result
3581          * @param  boolean  $make_lower_case
3582          * @return array field array
3583          */
3584         abstract public function getFieldsArray($result, $make_lower_case = false);
3585
3586         /**
3587          * Returns an array of tables for this database
3588          *
3589          * @return      array|false     an array of with table names, false if no tables found
3590          */
3591         abstract public function getTablesArray();
3592
3593         /**
3594          * Return's the version of the database
3595          *
3596          * @return string
3597          */
3598         abstract public function version();
3599
3600         /**
3601          * Checks if a table with the name $tableName exists
3602          * and returns true if it does or false otherwise
3603          *
3604          * @param  string $tableName
3605          * @return bool
3606          */
3607         abstract public function tableExists($tableName);
3608
3609         /**
3610          * Fetches the next row in the query result into an associative array
3611          *
3612          * @param  resource $result
3613          * @return array    returns false if there are no more rows available to fetch
3614          */
3615         abstract public function fetchRow($result);
3616
3617         /**
3618          * Connects to the database backend
3619          *
3620          * Takes in the database settings and opens a database connection based on those
3621          * will open either a persistent or non-persistent connection.
3622          * If a persistent connection is desired but not available it will defualt to non-persistent
3623          *
3624          * configOptions must include
3625          * db_host_name - server ip
3626          * db_user_name - database user name
3627          * db_password - database password
3628          *
3629          * @param array   $configOptions
3630          * @param boolean $dieOnError
3631          */
3632         abstract public function connect(array $configOptions = null, $dieOnError = false);
3633
3634         /**
3635          * Generates sql for create table statement for a bean.
3636          *
3637          * @param  string $tablename
3638          * @param  array  $fieldDefs
3639          * @param  array  $indices
3640          * @return string SQL Create Table statement
3641          */
3642         abstract public function createTableSQLParams($tablename, $fieldDefs, $indices);
3643
3644         /**
3645          * Generates the SQL for changing columns
3646          *
3647          * @param string $tablename
3648          * @param array  $fieldDefs
3649          * @param string $action
3650          * @param bool   $ignoreRequired Optional, true if we should ignor this being a required field
3651          * @return string|array
3652          */
3653         abstract protected function changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired = false);
3654
3655         /**
3656          * Disconnects from the database
3657          *
3658          * Also handles any cleanup needed
3659          */
3660         abstract public function disconnect();
3661
3662         /**
3663          * Get last database error
3664          * This function should return last error as reported by DB driver
3665          * and should return false if no error condition happened
3666          * @return string|false Error message or false if no error happened
3667          */
3668         abstract public function lastDbError();
3669
3670     /**
3671      * Check if this query is valid
3672      * Validates only SELECT queries
3673      * @param string $query
3674      * @return bool
3675      */
3676         abstract public function validateQuery($query);
3677
3678         /**
3679          * Check if this driver can be used
3680          * @return bool
3681          */
3682         abstract public function valid();
3683
3684         /**
3685          * Check if certain database exists
3686          * @param string $dbname
3687          */
3688         abstract public function dbExists($dbname);
3689
3690         /**
3691          * Get tables like expression
3692          * @param string $like Expression describing tables
3693          * @return array
3694          */
3695         abstract public function tablesLike($like);
3696
3697         /**
3698          * Create a database
3699          * @param string $dbname
3700          */
3701         abstract public function createDatabase($dbname);
3702
3703         /**
3704          * Drop a database
3705          * @param string $dbname
3706          */
3707         abstract public function dropDatabase($dbname);
3708
3709         /**
3710          * Get database configuration information (DB-dependent)
3711          * @return array|null
3712          */
3713         abstract public function getDbInfo();
3714
3715         /**
3716          * Check if certain DB user exists
3717          * @param string $username
3718          */
3719         abstract public function userExists($username);
3720
3721         /**
3722          * Create DB user
3723          * @param string $database_name
3724          * @param string $host_name
3725          * @param string $user
3726          * @param string $password
3727          */
3728         abstract public function createDbUser($database_name, $host_name, $user, $password);
3729
3730         /**
3731          * Check if the database supports fulltext indexing
3732          * Note that database driver can be capable of supporting FT (see supports('fulltext))
3733          * but particular instance can still have it disabled
3734          * @return bool
3735          */
3736         abstract public function full_text_indexing_installed();
3737
3738         /**
3739          * Generate fulltext query from set of terms
3740          * @param string $field Field to search against
3741          * @param array $terms Search terms that may be or not be in the result
3742          * @param array $must_terms Search terms that have to be in the result
3743          * @param array $exclude_terms Search terms that have to be not in the result
3744          */
3745         abstract public function getFulltextQuery($field, $terms, $must_terms = array(), $exclude_terms = array());
3746
3747         /**
3748          * Get install configuration for this DB
3749          * @return array
3750          */
3751         abstract public function installConfig();
3752 }