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.
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.
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
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
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.
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.
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 ********************************************************************************/
38 /*********************************************************************************
40 * Description: This file is an abstract class and handles the Data base functionality for
41 * the application. It is called by the DBManager class to generate various sql statements.
43 * All the functions in this class will work with any bean which implements the meta interface.
44 * Please refer the DBManager documentation for the details.
46 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
47 * All Rights Reserved.
48 * Contributor(s): ______________________________________..
49 ********************************************************************************/
51 abstract class DBHelper
54 * Instance of the related DBManager object
56 * @var object DBManager instance
61 * Instance of the related SugarBean object
63 * @var object SugarBean instance
68 * Maximum length of identifiers
70 protected static $maxNameLengths;
73 * Generates sql for create table statement for a bean.
75 * @param object $bean SugarBean instance
76 * @return string SQL Create Table statement
78 public function createTableSQL(
82 $tablename = $bean->getTableName();
83 $fieldDefs = $bean->getFieldDefinitions();
84 $indices = $bean->getIndices();
85 return $this->createTableSQLParams($tablename, $fieldDefs, $indices);
90 * Generates sql for create table statement for a bean.
92 * @param string $tablename
93 * @param array $fieldDefs
94 * @param array $indices
95 * @param string $engine
96 * @return string SQL Create Table statement
98 public function createTableSQLParams(
105 $columns = $this->columnSQLRep($fieldDefs, false, $tablename);
109 $keys = $this->keysSQL($indices);
113 // cn: bug 9873 - module tables do not get created in utf8 with assoc collation
114 return "CREATE TABLE $tablename ($columns $keys) CHARACTER SET utf8 COLLATE utf8_general_ci";
119 * Generates SQL for insert statement.
121 * @param object $bean SugarBean instance
122 * @return string SQL Create Table statement
124 public function insertSQL(
128 // get column names and values
130 foreach ($bean->getFieldDefinitions() as $fieldDef)
132 if (isset($fieldDef['source']) && $fieldDef['source'] != 'db')
135 $val = $bean->getFieldValue($fieldDef['name']);
136 // clean the incoming value..
137 $val = from_html($val);
138 if (strlen($val) <= 0) {
139 if(isset($fieldDef['default']) && (strlen($fieldDef['default']) > 0))
140 $val = $fieldDef['default'];
145 //handle auto increment values here only need to do this on insert not create
146 if (isset($fieldDef['auto_increment']) && $fieldDef['auto_increment']) {
147 $values[$fieldDef['name']] =
148 $this->getAutoIncrementSQL($bean->getTableName(), $fieldDef['name']);
150 elseif (isset($bean->$fieldDef['name'])) {
151 // need to do some thing about types of values
152 $values[$fieldDef['name']] = $this->massageValue($val, $fieldDef);
154 elseif ($fieldDef['name'] == 'deleted'){
155 $values['deleted'] = $val;
159 if ( sizeof($values) == 0 )
160 return ""; // no columns set
162 // get the entire sql
163 return "INSERT INTO ".$bean->getTableName()."
164 (".implode(",", array_keys($values)).")
165 VALUES (".implode(",", $values).")";
169 * Generates SQL for update statement.
171 * @param object $bean SugarBean instance
172 * @param array $where Optional, where conditions in an array
173 * @return string SQL Create Table statement
175 public function updateSQL(
177 array $where = array()
180 $primaryField = $bean->getPrimaryFieldDefinition();
183 // get column names and values
184 foreach ($bean->getFieldDefinitions() as $field => $fieldDef) {
185 // Do not write out the id field on the update statement.
186 // We are not allowed to change ids.
187 if ($fieldDef['name'] == $primaryField['name'])
190 // If the field is an auto_increment field, then we shouldn't be setting it. This was added
191 // specially for Bugs and Cases which have a number associated with them.
192 if (isset($bean->field_name_map[$field]['auto_increment']) &&
193 $bean->field_name_map[$field]['auto_increment'] == true)
196 //custom fields handle their save seperatley
197 if(isset($bean->field_name_map) && !empty($bean->field_name_map[$field]['custom_type']))
200 if (isset($bean->$fieldDef['name'])
201 && (!isset($fieldDef['source']) || $fieldDef['source'] == 'db')) {
202 $val = $bean->getFieldValue($fieldDef['name']);
203 // clean the incoming value..
204 $val = from_html($val);
206 // need to do some thing about types of values
207 if (strlen($val) <= 0)
208 $columns[] = "{$fieldDef['name']}=null";
210 $columns[] = "{$fieldDef['name']}=".$this->massageValue($val, $fieldDef);
214 if ( sizeof($columns) == 0 )
215 return ""; // no columns set
217 // build where clause
218 $where = $this->updateWhereArray($bean, $where);
219 $where = $this->getWhereClause($bean, $where);
221 return "update ".$bean->getTableName()."
222 set ".implode(",", $columns)."
223 $where and deleted=0";
227 * This method returns a where array so that it has id entry if
228 * where is not an array or is empty
230 * @param object $bean SugarBean instance
231 * @param array $where Optional, where conditions in an array
234 protected function updateWhereArray(
236 array $where = array()
239 if (sizeof($where) == 0) {
240 $fieldDef = $bean->getPrimaryFieldDefinition();
241 $primaryColumn = $fieldDef['name'];
243 $val = $bean->getFieldValue($fieldDef['name']);
245 $where[$primaryColumn] = $val;
253 * Returns a where clause without the 'where' key word
255 * The clause returned does not have an 'and' at the beginning and the columns
256 * are joined by 'and'.
258 * @param string $table table name
259 * @param array $whereArray Optional, where conditions in an array
262 protected function getColumnWhereClause(
264 array $whereArray = array()
267 foreach ($whereArray as $name => $val) {
269 if (is_array($val)) {
272 foreach ($val as $tval){
275 $val = implode(",", $temp);
281 $where[] = " $table.$name $op $val";
284 if (is_array($where))
285 $where = implode(" and ", $where);
291 * This method returns a complete where clause built from the
292 * where values specified.
294 * @param string $table table name
295 * @param array $whereArray Optional, where conditions in an array
298 protected function getWhereClause(
303 return " where " . $this->getColumnWhereClause($bean->getTableName(), $whereArray);
307 * Designed to take an SQL statement and produce a list of fields used in that select
308 * @param String $selectStatement
310 public function getSelectFieldsFromQuery($selectStatement)
312 $selectStatement = trim($selectStatement);
313 if (strtoupper(substr($selectStatement, 0, 6)) == "SELECT")
314 $selectStatement = trim(substr($selectStatement, 6));
316 //Due to sql functions existing in many selects, we can't use php explode
320 $strLen = strlen($selectStatement);
321 for($i = 0; $i < $strLen; $i++)
323 $char = $selectStatement[$i];
325 if ($char == "," && $level == 0)
327 $field = $this->getFieldNameFromSelect(trim($selectField));
328 $fields[$field] = $selectField;
331 else if ($char == "("){
333 $selectField .= $char;
335 else if($char == ")"){
337 $selectField .= $char;
341 $selectField .= $char;
345 $fields[$this->getFieldNameFromSelect($selectField)] = $selectField;
350 * returns the field name used in a select
351 * @param String $string
353 protected function getFieldNameFromSelect($string)
355 if(strncasecmp($string, "DISTINCT ", 9) == 0) {
356 $string = substr($string, 9);
358 if (stripos($string, " as ") !== false)
359 //"as" used for an alias
360 return trim(substr($string, strripos($string, " as ") + 4));
361 else if (strrpos($string, " ") != 0)
362 //Space used as a delimeter for an alias
363 return trim(substr($string, strrpos($string, " ")));
364 else if (strpos($string, ".") !== false)
365 //No alias, but a table.field format was used
366 return substr($string, strpos($string, ".") + 1);
368 //Give up and assume the whole thing is the field name
373 * Generates SQL for delete statement identified by id.
375 * @param object $bean SugarBean instance
376 * @param array $where where conditions in an array
377 * @return string SQL Update Statement
379 public function deleteSQL(
384 $where = $this->updateWhereArray($bean, $where);
385 $where = $this->getWhereClause($bean, $where);
387 return "update ".$bean->getTableName()." set deleted=1 $where";
393 * Generates SQL for select statement for any bean identified by id.
395 * @param object $bean SugarBean instance
396 * @param array $where where conditions in an array
397 * @return string SQL Select Statement
399 public function retrieveSQL(
404 $where = $this->updateWhereArray($bean, $where);
405 $where = $this->getWhereClause($bean, $where);
407 return "select * from ".$bean->getTableName()." $where and deleted=0";
411 * This method implements a generic sql for a collection of beans.
413 * Currently, this function does not support outer joins.
415 * @param array $bean value returned by get_class method as the keys and a bean as
416 * the value for that key. These beans will be joined in the sql by the key
417 * attribute of field defs.
418 * @param array $cols Optional, columns to be returned with the keys as names of bean
419 * as identified by get_class of bean. Values of this array is the array of fieldDefs
420 * to be returned for a bean. If an empty array is passed, all columns are selected.
421 * @param array $whereClause Optional, values with the keys as names of bean as identified
422 * by get_class of bean. Each value at the first level is an array of values for that
423 * bean identified by name of fields. If we want to pass multiple values for a name,
424 * pass it as an array. If where is not passed, all the rows will be returned.
425 * @return string SQL Select Statement
427 public function retrieveViewSQL(
429 array $cols = array(),
430 array $whereClause = array()
433 $relations = array(); // stores relations between tables as they are discovered
435 foreach ($beans as $beanID => $bean) {
436 $tableName = $bean->getTableName();
437 $beanTables[$beanID] = $tableName;
440 $tables[$table] = $tableName;
441 $aliases[$tableName][] = $table;
443 // build part of select for this table
444 if (is_array($cols[$beanID]))
445 foreach ($cols[$beanID] as $def) $select[] = $table.".".$def['name'];
447 // build part of where clause
448 if (is_array($whereClause[$beanID])){
449 $where[] = $this->getColumnWhereClause($table, $whereClause[$beanID]);
451 // initialize so that it can be used properly in form clause generation
452 $table_used_in_from[$table] = false;
454 $indices = $bean->getIndices();
455 foreach ($indices as $index){
456 if ($index['type'] == 'foreign') {
457 $relationship[$table][] = array('foreignTable'=> $index['foreignTable']
458 ,'foreignColumn'=>$index['foreignField']
459 ,'localColumn'=> $index['fields']
463 $where[] = " $table.deleted = 0";
466 // join these clauses
467 $select = (sizeof($select) > 0) ? implode(",", $select) : "*";
468 $where = implode(" and ", $where);
470 // generate the from clause. Use relations array to generate outer joins
471 // all the rest of the tables will be used as a simple from
472 // relations table define relations between table1 and table2 through column on table 1
473 // table2 is assumed to joing through primaty key called id
475 foreach ($relations as $table1 => $rightsidearray){
476 if ($table_used_in_from[$table1]) continue; // table has been joined
478 $from .= $separator." ".$table1;
479 $table_used_in_from[$table1] = true;
480 foreach ($rightsidearray as $tablearray){
481 $table2 = $tablearray['foreignTable']; // get foreign table
482 $tableAlias = $aliases[$table2]; // get a list of aliases fo thtis table
483 foreach ($tableAlias as $table2) {
484 //choose first alias that does not match
485 // we are doing this because of self joins.
486 // in case of self joins, the same table will bave many aliases.
487 if ($table2 != $table1) break;
490 $col = $tablearray['foreingColumn'];
491 $name = $tablearray['localColumn'];
492 $from .= " LEFT JOIN $table on ($table1.$name = $table2.$col)";
493 $table_used_in_from[$table2] = true;
498 return "select $select from $from where $where";
502 * Generates SQL for create index statement for a bean.
504 * @param object $bean SugarBean instance
505 * @param array $fields fields used in the index
506 * @param string $name index name
507 * @param bool $unique Optional, set to true if this is an unique index
508 * @return string SQL Select Statement
510 public function createIndexSQL(
517 $unique = ($unique) ? "unique" : "";
518 $tablename = $bean->getTableName();
521 foreach ($fields as $fieldDef)
522 $columns[] = $fieldDef['name'];
524 if (sizeof($columns) == 0)
527 $columns = implode(",", $columns);
529 return "create $unique index $name on $tablename ($columns)";
533 * Returns the type of the variable in the field
535 * @param array $fieldDef
538 public function getFieldType(
542 // get the type for db type. if that is not set,
543 // get it from type. This is done so that
544 // we do not have change a lot of existing code
545 // and add dbtype where type is being used for some special
546 // purposes like referring to foreign table etc.
547 if(!empty($fieldDef['dbType']))
548 return $fieldDef['dbType'];
549 if(!empty($fieldDef['dbtype']))
550 return $fieldDef['dbtype'];
551 if (!empty($fieldDef['type']))
552 return $fieldDef['type'];
553 if (!empty($fieldDef['Type']))
554 return $fieldDef['Type'];
555 if (!empty($fieldDef['data_type']))
556 return $fieldDef['data_type'];
562 * Returns the defintion for a single column
564 * @param array $fieldDef
565 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
566 * @param string $table Optional, table name
567 * @param bool $return_as_array Optional, true if we should return the result as an array instead of sql
568 * @return string or array if $return_as_array is true
570 protected function oneColumnSQLRep(
572 $ignoreRequired = false,
574 $return_as_array = false
578 $name = $fieldDef['name'];
579 $type = $this->getFieldType($fieldDef);
580 $colType = $this->getColumnType($type, $name, $table);
582 if (( $colType == 'nvarchar'
583 or $colType == 'nchar'
584 or $colType == 'varchar'
585 or $colType == 'char'
586 or $colType == 'varchar2') ) {
587 if( !empty($fieldDef['len']))
588 $colType .= "(".$fieldDef['len'].")";
592 if($colType == 'decimal' || $colType == 'float'){
593 if(!empty($fieldDef ['len'])){
594 if(!empty($fieldDef['precision']) && is_numeric($fieldDef['precision']))
595 if(strpos($fieldDef ['len'],',') === false){
596 $colType .= "(".$fieldDef['len'].",".$fieldDef['precision'].")";
598 $colType .= "(".$fieldDef['len'].")";
601 $colType .= "(".$fieldDef['len'].")";
606 if (isset($fieldDef['default']) && strlen($fieldDef['default']) > 0)
607 $default = " DEFAULT '".$fieldDef['default']."'";
608 elseif (!isset($default) && $type == 'bool')
609 $default = " DEFAULT 0 ";
610 elseif (!isset($default))
613 $auto_increment = '';
614 if(!empty($fieldDef['auto_increment']) && $fieldDef['auto_increment'])
615 $auto_increment = $this->setAutoIncrement($table , $fieldDef['name']);
617 $required = 'NULL'; // MySQL defaults to NULL, SQL Server defaults to NOT NULL -- must specify
618 //Starting in 6.0, only ID and auto_increment fields will be NOT NULL in the DB.
619 if ((empty($fieldDef['isnull']) || strtolower($fieldDef['isnull']) == 'false') &&
620 (!empty($auto_increment) || $name == 'id' || ($fieldDef['type'] == 'id' && !empty($fieldDef['required'])))) {
621 $required = "NOT NULL";
623 // If the field is marked both required & isnull=>false - alwqys make it not null
624 // Use this to ensure primary key fields never defined as null
625 if(isset($fieldDef['isnull']) && (strtolower($fieldDef['isnull']) == 'false' || $fieldDef['isnull'] === false)
626 && !empty($fieldDef['required'])) {
627 $required = "NOT NULL";
632 if ( $return_as_array )
635 'colType' => $colType,
636 'default' => $default,
637 'required' => $required,
638 'auto_increment' => $auto_increment,
639 'full' => "$name $colType $default $required $auto_increment",
642 return "$name $colType $default $required $auto_increment";
647 * Returns SQL defintions for all columns in a table
649 * @param array $fieldDefs
650 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
651 * @param string $tablename Optional, table name
652 * @return string SQL column definitions
654 protected function columnSQLRep(
656 $ignoreRequired = false,
662 if ($this->isFieldArray($fieldDefs)) {
663 foreach ($fieldDefs as $fieldDef) {
664 if(!isset($fieldDef['source']) || $fieldDef['source'] == 'db') {
665 $columns[] = $this->oneColumnSQLRep($fieldDef,false, $tablename);
668 $columns = implode(",", $columns);
671 $columns = $this->oneColumnSQLRep($fieldDefs,$ignoreRequired, $tablename);
678 * Returns the next value for an auto increment
680 * @param string $table tablename
681 * @param string $field_name
684 public function getAutoIncrement(
693 * Returns the sql for the next value in a sequence
695 * @param string $table tablename
696 * @param string $field_name
699 public function getAutoIncrementSQL(
710 * Either creates an auto increment through queries or returns sql for auto increment
711 * that can be appended to the end of column defination (mysql)
713 * @param string $table tablename
714 * @param string $field_name
717 protected function setAutoIncrement(
722 $this->deleteAutoIncrement($table, $field_name);
728 * Sets the next auto-increment value of a column to a specific value.
730 * @param string $table tablename
731 * @param string $field_name
733 public function setAutoIncrementStart(
743 * Deletes an auto increment (for oracle not mysql)
745 * @param string $table tablename
746 * @param string $field_name
748 public function deleteAutoIncrement(
757 * Generates the SQL for changing columns
759 * @param string $tablename
760 * @param array $fieldDefs
761 * @param string $action
762 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
764 abstract protected function changeColumnSQL(
768 $ignoreRequired = false);
771 * This method generates sql for adding a column to table identified by field def.
773 * @param string $tablename
774 * @param array $fieldDefs
775 * @return string SQL statement
777 public function addColumnSQL(
782 return $this->changeColumnSQL($tablename, $fieldDefs, 'add');
786 * This method genrates sql for altering old column identified by oldFieldDef to new fieldDef.
788 * @param string $tablename
789 * @param array $newFieldDefs
790 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
791 * @return string SQL statement
793 public function alterColumnSQL(
796 $ignorerequired = false
799 return $this->changeColumnSQL($tablename, $newFieldDefs, 'modify', $ignorerequired);
803 * Generates SQL for dropping a table.
805 * @param object $bean Sugarbean instance
806 * @return string SQL statement
808 public function dropTableSQL(
812 return $this->dropTableNameSQL($bean->getTableName());
816 * Generates SQL for dropping a table.
818 * @param string $name table name
819 * @return string SQL statement
821 public function dropTableNameSQL(
825 return "drop table if exists ".$name;
829 * This method generates sql that deletes a column identified by fieldDef.
831 * @param object $bean Sugarbean instance
832 * @param array $fieldDefs
833 * @return string SQL statement
835 public function deleteColumnSQL(
840 if ($this->isFieldArray($fieldDefs))
841 foreach ($fieldDefs as $fieldDef)
842 $columns[] = $fieldDef['name'];
844 $columns[] = $fieldDefs['name'];
846 return "alter table ".$bean->getTableName()." drop (".implode(", ", $columns).")";
850 * This method generates sql that drops a column identified by fieldDef.
851 * Designed to work like the other addColumnSQL() and alterColumnSQL() functions
853 * @param string $tablename
854 * @param array $fieldDefs
855 * @return string SQL statement
857 public function dropColumnSQL(
862 $sql = $this->changeColumnSQL(
872 * Generates SQL for key statement for any bean identified by id.
874 * The passes array is an array of field definitions or a field definition
875 * itself. The keys generated will be either primary, foreign, unique, index
876 * or none at all depending on the setting of the "key" parameter of a field definition
878 * @param array $indices
879 * @param bool $alter_table
880 * @param string $alter_action
881 * @return string SQL Statement
883 protected function keysSQL(
885 $alter_table = false,
893 * Outputs a correct string for the sql statement according to value
896 * @param array $fieldDef field definition
899 public function massageValue(
904 if ( strlen($val) <= 0 )
907 $type = $this->getFieldType($fieldDef);
923 $qval = $this->quote($val);
950 * Massages the field defintions to fill in anything else the DB backend may add
952 * @param array $fieldDef
953 * @param string $tablename
956 public function massageFieldDef(
961 if ( !isset($fieldDef['dbType']) ) {
962 if ( isset($fieldDef['dbtype']) )
963 $fieldDef['dbType'] = $fieldDef['dbtype'];
965 $fieldDef['dbType'] = $fieldDef['type'];
967 $type = $this->getColumnType($fieldDef['dbType'],$fieldDef['name'],$tablename);
969 preg_match_all("/(\w+)(?:\(([0-9]+,?[0-9]*)\)|)/i", $type, $matches);
970 if ( isset($matches[1][0]) )
971 $fieldDef['type'] = $matches[1][0];
972 if ( isset($matches[2][0]) && empty($fieldDef['len']) )
973 $fieldDef['len'] = $matches[2][0];
974 if ( !empty($fieldDef['precision']) && is_numeric($fieldDef['precision']) && !strstr($fieldDef['len'],',') )
975 $fieldDef['len'] .= ",{$fieldDef['precision']}";
976 if (isset($fieldDef['required']) && ($fieldDef['required'] == true
977 || $fieldDef['required'] == '1'
978 || $fieldDef['required'] == 1)
979 || ($fieldDef['name'] == 'id' && !isset($fieldDef['required'])) )
980 $fieldDef['required'] = 'true';
984 * Return a version of $proposed that can be used as a column name in any of our supported databases
985 * 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)
986 * @param string $name Proposed name for the column
987 * @param string $ensureUnique
988 * @return string Valid column name trimmed to right length and with invalid characters removed
990 public static function getValidDBName ($name, $ensureUnique = false, $type = 'column')
995 foreach($name as $field)
997 $result[] = self::getValidDBName($field, $ensureUnique, $type);
1001 // first strip any invalid characters - all but alphanumerics and -
1002 $name = preg_replace ( '/[^\w-]+/i', '', $name ) ;
1003 $len = strlen ( $name ) ;
1005 $maxLen = empty(self::$maxNameLengths[$type]) ? self::$maxNameLengths[$type]['column'] : self::$maxNameLengths[$type];
1006 if ($len <= $maxLen)
1008 return strtolower($name);
1012 $md5str = md5($name);
1013 $tail = substr ( $name, -11) ;
1014 $temp = substr($md5str , strlen($md5str)-4 );
1015 $result = substr ( $name, 0, 10) . $temp . $tail ;
1019 $result = substr ( $name, 0, 11) . substr ( $name, 11 - $maxLen);
1022 return strtolower ( $result ) ;
1027 * Returns the valid type for a column given the type in fieldDef
1029 * @param string $type field type
1030 * @return string valid type for the given field
1032 abstract public function getColumnType(
1039 * Checks to see if passed array is truely an array of defitions
1041 * Such an array may have type as a key but it will point to an array
1042 * for a true array of definitions an to a col type for a definition only
1044 * @param mixed $defArray
1047 public function isFieldArray(
1051 if ( !is_array($defArray) )
1054 if ( isset($defArray['type']) ){
1055 // type key exists. May be an array of defs or a simple definition
1056 $type = $defArray['type'];
1057 return is_array($type); // type is not an array => definition else array
1060 // type does not exist. Must be array of definitions
1065 * returns true if the type can be mapped to a valid column type
1067 * @param string $type
1070 protected function validColumnType(
1074 $coltype = $this->getColumnType($type);
1075 return ($coltype) ? true : false;
1079 * Saves changes to module's audit table
1081 * @param object $bean Sugarbean instance
1082 * @param array $changes changes
1083 * @see DBHelper::getDataChanges()
1085 public function save_audit_records(SugarBean $bean, $changes)
1087 global $current_user;
1088 $sql = "INSERT INTO ".$bean->get_audit_table_name();
1089 //get field defs for the audit table.
1090 require('metadata/audit_templateMetaData.php');
1091 $fieldDefs = $dictionary['audit']['fields'];
1094 $values['id']=$this->massageValue(create_guid(), $fieldDefs['id']);
1095 $values['parent_id']=$bean->dbManager->getHelper()->massageValue($bean->id, $fieldDefs['parent_id']);
1096 $values['field_name']=$bean->dbManager->getHelper()->massageValue($changes['field_name'], $fieldDefs['field_name']);
1097 $values['data_type']=$bean->dbManager->getHelper()->massageValue($changes['data_type'], $fieldDefs['data_type']);
1098 if ($changes['data_type']=='text') {
1099 $bean->fetched_row[$changes['field_name']]=$changes['after'];;
1100 $values['before_value_text']=$bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_text']);
1101 $values['after_value_text']=$bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_text']);
1103 $bean->fetched_row[$changes['field_name']]=$changes['after'];;
1104 $values['before_value_string']=$bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_string']);
1105 $values['after_value_string']=$bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_string']);
1107 $values['date_created']=$bean->dbManager->getHelper()->massageValue(TimeDate::getInstance()->nowDb(), $fieldDefs['date_created'] );
1108 $values['created_by']=$bean->dbManager->getHelper()->massageValue($current_user->id, $fieldDefs['created_by']);
1110 $sql .= "(".implode(",", array_keys($values)).") ";
1111 $sql .= "VALUES(".implode(",", $values).")";
1113 if ( $this->db->dbType == 'oci8' && $changes['data_type'] == 'text' ) {
1114 $sql .= " RETURNING before_value_text, after_value_text INTO :before_value_text, :after_value_text";
1115 $stmt = oci_parse($this->db->getDatabase(), $sql);
1116 $err = oci_error($this->db->getDatabase());
1118 $GLOBALS['log']->fatal($sql.">>".$err['code'].":".$err['message']);
1121 $before_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
1122 oci_bind_by_name($stmt, ":before_value_text", $before_value_text_LOB, -1, OCI_B_CLOB);
1123 $after_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
1124 oci_bind_by_name($stmt, ":after_value_text", $after_value_text_LOB, -1, OCI_B_CLOB);
1125 oci_execute($stmt, OCI_DEFAULT);
1126 $err = oci_error($this->db->getDatabase());
1128 $GLOBALS['log']->fatal($sql.">>".$err['code'].":".$err['message']);
1131 $before_value_text_LOB->save($changes['before']);
1132 $after_value_text_LOB->save($changes['after']);
1133 oci_commit($this->db->getDatabase());
1134 $before_value_text_LOB->free();
1135 $after_value_text_LOB->free();
1136 oci_free_statement($stmt);
1139 $bean->db->query($sql);
1144 * Uses the audit enabled fields array to find fields whose value has changed.
1145 * The before and after values are stored in the bean.
1147 * @param object $bean Sugarbean instance
1150 public function getDataChanges(
1154 $changed_values=array();
1155 $audit_fields=$bean->getAuditEnabledFieldDefinitions();
1157 if (is_array($audit_fields) and count($audit_fields) > 0) {
1158 foreach ($audit_fields as $field=>$properties) {
1160 if (!empty($bean->fetched_row) && array_key_exists($field, $bean->fetched_row)) {
1162 $before_value=$bean->fetched_row[$field];
1163 $after_value=$bean->$field;
1164 if (isset($properties['type']))
1165 $field_type=$properties['type'];
1167 if (isset($properties['dbType']))
1168 $field_type=$properties['dbType'];
1169 else if(isset($properties['data_type']))
1170 $field_type=$properties['data_type'];
1172 $field_type=$properties['dbtype'];
1175 //Because of bug #25078(sqlserver haven't 'date' type, trim extra "00:00:00" when insert into *_cstm table). so when we read the audit datetime field from sqlserver, we have to replace the extra "00:00:00" again.
1176 if(!empty($field_type) && $field_type == 'date'){
1177 $before_value = from_db_convert($before_value , $field_type);
1179 //if the type and values match, do nothing.
1180 if (!($this->_emptyValue($before_value,$field_type) && $this->_emptyValue($after_value,$field_type))) {
1181 if (trim($before_value) !== trim($after_value)) {
1182 if (!($this->_isTypeNumber($field_type) && (trim($before_value)+0) == (trim($after_value)+0))) {
1183 if (!($this->_isTypeBoolean($field_type) && ($this->_getBooleanValue($before_value)== $this->_getBooleanValue($after_value)))) {
1184 $changed_values[$field]=array('field_name'=>$field,
1185 'data_type'=>$field_type,
1186 'before'=>$before_value,
1187 'after'=>$after_value);
1195 return $changed_values;
1199 * Function returns true is full-text indexing is available in the connected database.
1201 * Default value is false.
1203 * @param string $dbname
1206 abstract protected function full_text_indexing_enabled(
1211 * Quotes a string for storing in the database
1213 * Return value will be surrounded by quotes
1215 * @param string $string
1218 public function quote(
1222 return "'".$this->db->quote($string)."'";
1226 * Quotes a string for storing in the database
1228 * Return value will be not surrounded by quotes
1230 * @param string $string
1233 public function escape_quote(
1237 return $this->db->quote($string);
1241 * Returns definitions of all indies for passed table.
1243 * return will is a multi-dimensional array that
1244 * categorizes the index definition by types, unique, primary and index.
1248 * 'index1'=> array (
1249 * 'name' => 'index1',
1250 * 'type' => 'primary',
1251 * 'fields' => array('field1','field2')
1256 * This format is similar to how indicies are defined in vardef file.
1258 * @param string $tablename
1261 abstract public function get_indices(
1266 * Returns definitions of all indies for passed table.
1268 * return will is a multi-dimensional array that
1269 * categorizes the index definition by types, unique, primary and index.
1273 * 'field1'=> array (
1274 * 'name' => 'field1',
1275 * 'type' => 'varchar',
1281 * This format is similar to how indicies are defined in vardef file.
1283 * @param string $tablename
1286 abstract public function get_columns(
1291 * Generates alter constraint statement given a table name and vardef definition.
1293 * Supports both adding and droping a constraint.
1295 * @param string $table tablename
1296 * @param array $defintion field definition
1297 * @param bool $drop true if we are dropping the constraint, false if we are adding it
1298 * @return string SQL statement
1300 abstract public function add_drop_constraint(
1306 * Renames an index definition
1308 * @param array $old_definition
1309 * @param array $new_definition
1310 * @param string $tablename
1311 * @return string SQL statement
1313 public function rename_index(
1319 $ret_commands = array();
1320 $ret_commands[] = $this->add_drop_constraint($table_name,$old_definition,true);
1321 $ret_commands[] = $this->add_drop_constraint($table_name,$new_definition);
1323 return $ret_commands;
1327 * Returns the number of columns in a table
1329 * @param string $table_name
1332 abstract public function number_of_columns(
1336 protected function _isTypeBoolean(
1348 protected function _getBooleanValue(
1352 //need to put the === sign here otherwise true == 'non empty string'
1353 if (empty($val) or $val==='off')
1359 protected function _isTypeNumber(
1378 * return true if the value if empty
1380 protected function _emptyValue(
1401 if ($val == '0000-00-00')