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