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 $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'];
561 protected function getDefault($fieldDef, $type) {
562 if (isset($fieldDef['default']) && strlen($fieldDef['default']) > 0) {
563 $default = " DEFAULT '".$fieldDef['default']."'";
565 elseif (!isset($default) && $type == 'bool') {
566 $default = " DEFAULT 0 ";
576 * Returns the defintion for a single column
578 * @param array $fieldDef
579 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
580 * @param string $table Optional, table name
581 * @param bool $return_as_array Optional, true if we should return the result as an array instead of sql
582 * @return string or array if $return_as_array is true
584 protected function oneColumnSQLRep(
586 $ignoreRequired = false,
588 $return_as_array = false
592 $name = $fieldDef['name'];
593 $type = $this->getFieldType($fieldDef);
594 $colType = $this->getColumnType($type, $name, $table);
596 if (( $colType == 'nvarchar'
597 or $colType == 'nchar'
598 or $colType == 'varchar'
599 or $colType == 'char'
600 or $colType == 'varchar2') ) {
601 if( !empty($fieldDef['len']))
602 $colType .= "(".$fieldDef['len'].")";
606 if($colType == 'decimal' || $colType == 'float'){
607 if(!empty($fieldDef ['len'])){
608 if(!empty($fieldDef['precision']) && is_numeric($fieldDef['precision']))
609 if(strpos($fieldDef ['len'],',') === false){
610 $colType .= "(".$fieldDef['len'].",".$fieldDef['precision'].")";
612 $colType .= "(".$fieldDef['len'].")";
615 $colType .= "(".$fieldDef['len'].")";
620 $default = $this->getDefault($fieldDef, $type);
622 $auto_increment = '';
623 if(!empty($fieldDef['auto_increment']) && $fieldDef['auto_increment'])
624 $auto_increment = $this->setAutoIncrement($table , $fieldDef['name']);
626 $required = 'NULL'; // MySQL defaults to NULL, SQL Server defaults to NOT NULL -- must specify
627 //Starting in 6.0, only ID and auto_increment fields will be NOT NULL in the DB.
628 if ((empty($fieldDef['isnull']) || strtolower($fieldDef['isnull']) == 'false') &&
629 (!empty($auto_increment) || $name == 'id' || ($fieldDef['type'] == 'id' && !empty($fieldDef['required'])))) {
630 $required = "NOT NULL";
632 // If the field is marked both required & isnull=>false - alwqys make it not null
633 // Use this to ensure primary key fields never defined as null
634 if(isset($fieldDef['isnull']) && (strtolower($fieldDef['isnull']) == 'false' || $fieldDef['isnull'] === false)
635 && !empty($fieldDef['required'])) {
636 $required = "NOT NULL";
641 if ( $return_as_array )
644 'colType' => $colType,
645 'default' => $default,
646 'required' => $required,
647 'auto_increment' => $auto_increment,
648 'full' => "$name $colType $default $required $auto_increment",
651 return "$name $colType $default $required $auto_increment";
656 * Returns SQL defintions for all columns in a table
658 * @param array $fieldDefs
659 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
660 * @param string $tablename Optional, table name
661 * @return string SQL column definitions
663 protected function columnSQLRep(
665 $ignoreRequired = false,
671 if ($this->isFieldArray($fieldDefs)) {
672 foreach ($fieldDefs as $fieldDef) {
673 if(!isset($fieldDef['source']) || $fieldDef['source'] == 'db') {
674 $columns[] = $this->oneColumnSQLRep($fieldDef,false, $tablename);
677 $columns = implode(",", $columns);
680 $columns = $this->oneColumnSQLRep($fieldDefs,$ignoreRequired, $tablename);
687 * Returns the next value for an auto increment
689 * @param string $table tablename
690 * @param string $field_name
693 public function getAutoIncrement(
702 * Returns the sql for the next value in a sequence
704 * @param string $table tablename
705 * @param string $field_name
708 public function getAutoIncrementSQL(
719 * Either creates an auto increment through queries or returns sql for auto increment
720 * that can be appended to the end of column defination (mysql)
722 * @param string $table tablename
723 * @param string $field_name
726 protected function setAutoIncrement(
731 $this->deleteAutoIncrement($table, $field_name);
737 * Sets the next auto-increment value of a column to a specific value.
739 * @param string $table tablename
740 * @param string $field_name
742 public function setAutoIncrementStart(
752 * Deletes an auto increment (for oracle not mysql)
754 * @param string $table tablename
755 * @param string $field_name
757 public function deleteAutoIncrement(
766 * Generates the SQL for changing columns
768 * @param string $tablename
769 * @param array $fieldDefs
770 * @param string $action
771 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
773 abstract protected function changeColumnSQL(
777 $ignoreRequired = false);
780 * This method generates sql for adding a column to table identified by field def.
782 * @param string $tablename
783 * @param array $fieldDefs
784 * @return string SQL statement
786 public function addColumnSQL(
791 return $this->changeColumnSQL($tablename, $fieldDefs, 'add');
795 * This method genrates sql for altering old column identified by oldFieldDef to new fieldDef.
797 * @param string $tablename
798 * @param array $newFieldDefs
799 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
800 * @return string SQL statement
802 public function alterColumnSQL(
805 $ignorerequired = false
808 return $this->changeColumnSQL($tablename, $newFieldDefs, 'modify', $ignorerequired);
812 * Generates SQL for dropping a table.
814 * @param object $bean Sugarbean instance
815 * @return string SQL statement
817 public function dropTableSQL(
821 return $this->dropTableNameSQL($bean->getTableName());
825 * Generates SQL for dropping a table.
827 * @param string $name table name
828 * @return string SQL statement
830 public function dropTableNameSQL(
834 return "drop table if exists ".$name;
838 * This method generates sql that deletes a column identified by fieldDef.
840 * @param object $bean Sugarbean instance
841 * @param array $fieldDefs
842 * @return string SQL statement
844 public function deleteColumnSQL(
849 if ($this->isFieldArray($fieldDefs))
850 foreach ($fieldDefs as $fieldDef)
851 $columns[] = $fieldDef['name'];
853 $columns[] = $fieldDefs['name'];
855 return "alter table ".$bean->getTableName()." drop (".implode(", ", $columns).")";
859 * This method generates sql that drops a column identified by fieldDef.
860 * Designed to work like the other addColumnSQL() and alterColumnSQL() functions
862 * @param string $tablename
863 * @param array $fieldDefs
864 * @return string SQL statement
866 public function dropColumnSQL(
871 $sql = $this->changeColumnSQL(
881 * Generates SQL for key statement for any bean identified by id.
883 * The passes array is an array of field definitions or a field definition
884 * itself. The keys generated will be either primary, foreign, unique, index
885 * or none at all depending on the setting of the "key" parameter of a field definition
887 * @param array $indices
888 * @param bool $alter_table
889 * @param string $alter_action
890 * @return string SQL Statement
892 protected function keysSQL(
894 $alter_table = false,
902 * Outputs a correct string for the sql statement according to value
905 * @param array $fieldDef field definition
908 public function massageValue(
913 if ( strlen($val) <= 0 )
916 $type = $this->getFieldType($fieldDef);
932 $qval = $this->quote($val);
959 * Massages the field defintions to fill in anything else the DB backend may add
961 * @param array $fieldDef
962 * @param string $tablename
965 public function massageFieldDef(
970 if ( !isset($fieldDef['dbType']) ) {
971 if ( isset($fieldDef['dbtype']) )
972 $fieldDef['dbType'] = $fieldDef['dbtype'];
974 $fieldDef['dbType'] = $fieldDef['type'];
976 $type = $this->getColumnType($fieldDef['dbType'],$fieldDef['name'],$tablename);
978 preg_match_all("/(\w+)(?:\(([0-9]+,?[0-9]*)\)|)/i", $type, $matches);
979 if ( isset($matches[1][0]) )
980 $fieldDef['type'] = $matches[1][0];
981 if ( isset($matches[2][0]) && empty($fieldDef['len']) )
982 $fieldDef['len'] = $matches[2][0];
983 if ( !empty($fieldDef['precision']) && is_numeric($fieldDef['precision']) && !strstr($fieldDef['len'],',') )
984 $fieldDef['len'] .= ",{$fieldDef['precision']}";
985 if (isset($fieldDef['required']) && ($fieldDef['required'] == true
986 || $fieldDef['required'] == '1'
987 || $fieldDef['required'] == 1)
988 || ($fieldDef['name'] == 'id' && !isset($fieldDef['required'])) )
989 $fieldDef['required'] = 'true';
993 * Return a version of $proposed that can be used as a column name in any of our supported databases
994 * 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)
995 * @param string $name Proposed name for the column
996 * @param string $ensureUnique
997 * @return string Valid column name trimmed to right length and with invalid characters removed
999 public function getValidDBName ($name, $ensureUnique = false, $type = 'column', $force = false)
1004 foreach($name as $field)
1006 $result[] = $this->getValidDBName($field, $ensureUnique, $type);
1010 // first strip any invalid characters - all but alphanumerics and -
1011 $name = preg_replace ( '/[^\w-]+/i', '', $name ) ;
1012 $len = strlen ( $name ) ;
1014 $maxLen = empty($this->maxNameLengths[$type]) ? $this->maxNameLengths[$type]['column'] : $this->maxNameLengths[$type];
1015 if ($len <= $maxLen && !$force)
1017 return strtolower($name);
1021 $md5str = md5($name);
1022 $tail = substr ( $name, -11) ;
1023 $temp = substr($md5str , strlen($md5str)-4 );
1024 $result = substr ( $name, 0, 10) . $temp . $tail ;
1028 $result = substr ( $name, 0, 11) . substr ( $name, 11 - $maxLen);
1031 return strtolower ( $result ) ;
1036 * Returns the valid type for a column given the type in fieldDef
1038 * @param string $type field type
1039 * @return string valid type for the given field
1041 abstract public function getColumnType(
1048 * Checks to see if passed array is truely an array of defitions
1050 * Such an array may have type as a key but it will point to an array
1051 * for a true array of definitions an to a col type for a definition only
1053 * @param mixed $defArray
1056 public function isFieldArray(
1060 if ( !is_array($defArray) )
1063 if ( isset($defArray['type']) ){
1064 // type key exists. May be an array of defs or a simple definition
1065 $type = $defArray['type'];
1066 return is_array($type); // type is not an array => definition else array
1069 // type does not exist. Must be array of definitions
1074 * returns true if the type can be mapped to a valid column type
1076 * @param string $type
1079 protected function validColumnType(
1083 $coltype = $this->getColumnType($type);
1084 return ($coltype) ? true : false;
1088 * Saves changes to module's audit table
1090 * @param object $bean Sugarbean instance
1091 * @param array $changes changes
1092 * @see DBHelper::getDataChanges()
1094 public function save_audit_records(SugarBean $bean, $changes)
1096 global $current_user;
1097 $sql = "INSERT INTO ".$bean->get_audit_table_name();
1098 //get field defs for the audit table.
1099 require('metadata/audit_templateMetaData.php');
1100 $fieldDefs = $dictionary['audit']['fields'];
1103 $values['id']=$this->massageValue(create_guid(), $fieldDefs['id']);
1104 $values['parent_id']=$bean->dbManager->getHelper()->massageValue($bean->id, $fieldDefs['parent_id']);
1105 $values['field_name']=$bean->dbManager->getHelper()->massageValue($changes['field_name'], $fieldDefs['field_name']);
1106 $values['data_type']=$bean->dbManager->getHelper()->massageValue($changes['data_type'], $fieldDefs['data_type']);
1107 if ($changes['data_type']=='text') {
1108 $bean->fetched_row[$changes['field_name']]=$changes['after'];;
1109 $values['before_value_text']=$bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_text']);
1110 $values['after_value_text']=$bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_text']);
1112 $bean->fetched_row[$changes['field_name']]=$changes['after'];;
1113 $values['before_value_string']=$bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_string']);
1114 $values['after_value_string']=$bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_string']);
1116 $values['date_created']=$bean->dbManager->getHelper()->massageValue(TimeDate::getInstance()->nowDb(), $fieldDefs['date_created'] );
1117 $values['created_by']=$bean->dbManager->getHelper()->massageValue($current_user->id, $fieldDefs['created_by']);
1119 $sql .= "(".implode(",", array_keys($values)).") ";
1120 $sql .= "VALUES(".implode(",", $values).")";
1122 if ( $this->db->dbType == 'oci8' && $changes['data_type'] == 'text' ) {
1123 $sql .= " RETURNING before_value_text, after_value_text INTO :before_value_text, :after_value_text";
1124 $stmt = oci_parse($this->db->getDatabase(), $sql);
1125 $err = oci_error($this->db->getDatabase());
1127 $GLOBALS['log']->fatal($sql.">>".$err['code'].":".$err['message']);
1130 $before_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
1131 oci_bind_by_name($stmt, ":before_value_text", $before_value_text_LOB, -1, OCI_B_CLOB);
1132 $after_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
1133 oci_bind_by_name($stmt, ":after_value_text", $after_value_text_LOB, -1, OCI_B_CLOB);
1134 oci_execute($stmt, OCI_DEFAULT);
1135 $err = oci_error($this->db->getDatabase());
1137 $GLOBALS['log']->fatal($sql.">>".$err['code'].":".$err['message']);
1140 $before_value_text_LOB->save($changes['before']);
1141 $after_value_text_LOB->save($changes['after']);
1142 oci_commit($this->db->getDatabase());
1143 $before_value_text_LOB->free();
1144 $after_value_text_LOB->free();
1145 oci_free_statement($stmt);
1148 $bean->db->query($sql);
1153 * Uses the audit enabled fields array to find fields whose value has changed.
1154 * The before and after values are stored in the bean.
1156 * @param object $bean Sugarbean instance
1159 public function getDataChanges(
1163 $changed_values=array();
1164 $audit_fields=$bean->getAuditEnabledFieldDefinitions();
1166 if (is_array($audit_fields) and count($audit_fields) > 0) {
1167 foreach ($audit_fields as $field=>$properties) {
1169 if (!empty($bean->fetched_row) && array_key_exists($field, $bean->fetched_row)) {
1171 if (isset($properties['type']))
1172 $field_type=$properties['type'];
1174 if (isset($properties['dbType']))
1175 $field_type=$properties['dbType'];
1176 else if(isset($properties['data_type']))
1177 $field_type=$properties['data_type'];
1179 $field_type=$properties['dbtype'];
1182 // Bug # 44624 - check for currency type and cast to float
1183 // this ensures proper matching for change log
1184 if ( (strcmp($field_type,"currency")==0) ) {
1185 $before_value=(float)$bean->fetched_row[$field];
1186 $after_value=(float)$bean->$field;
1188 $before_value=$bean->fetched_row[$field];
1189 $after_value=$bean->$field;
1192 //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.
1193 if(!empty($field_type) && $field_type == 'date'){
1194 $before_value = from_db_convert($before_value , $field_type);
1196 //if the type and values match, do nothing.
1197 if (!($this->_emptyValue($before_value,$field_type) && $this->_emptyValue($after_value,$field_type))) {
1198 if (trim($before_value) !== trim($after_value)) {
1199 // 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.
1200 if (!($this->_isTypeNumber($field_type) && abs((trim($before_value)+0)-(trim($after_value)+0))<0.001)) {
1201 if (!($this->_isTypeBoolean($field_type) && ($this->_getBooleanValue($before_value)== $this->_getBooleanValue($after_value)))) {
1202 $changed_values[$field]=array('field_name'=>$field,
1203 'data_type'=>$field_type,
1204 'before'=>$before_value,
1205 'after'=>$after_value);
1213 return $changed_values;
1217 * Function returns true is full-text indexing is available in the connected database.
1219 * Default value is false.
1221 * @param string $dbname
1224 abstract protected function full_text_indexing_enabled(
1229 * Quotes a string for storing in the database
1231 * Return value will be surrounded by quotes
1233 * @param string $string
1236 public function quote(
1240 return "'".$this->db->quote($string)."'";
1244 * Quotes a string for storing in the database
1246 * Return value will be not surrounded by quotes
1248 * @param string $string
1251 public function escape_quote(
1255 return $this->db->quote($string);
1259 * Returns definitions of all indies for passed table.
1261 * return will is a multi-dimensional array that
1262 * categorizes the index definition by types, unique, primary and index.
1266 * 'index1'=> array (
1267 * 'name' => 'index1',
1268 * 'type' => 'primary',
1269 * 'fields' => array('field1','field2')
1274 * This format is similar to how indicies are defined in vardef file.
1276 * @param string $tablename
1279 abstract public function get_indices(
1284 * Returns definitions of all indies for passed table.
1286 * return will is a multi-dimensional array that
1287 * categorizes the index definition by types, unique, primary and index.
1291 * 'field1'=> array (
1292 * 'name' => 'field1',
1293 * 'type' => 'varchar',
1299 * This format is similar to how indicies are defined in vardef file.
1301 * @param string $tablename
1304 abstract public function get_columns(
1309 * Generates alter constraint statement given a table name and vardef definition.
1311 * Supports both adding and droping a constraint.
1313 * @param string $table tablename
1314 * @param array $defintion field definition
1315 * @param bool $drop true if we are dropping the constraint, false if we are adding it
1316 * @return string SQL statement
1318 abstract public function add_drop_constraint(
1324 * Renames an index definition
1326 * @param array $old_definition
1327 * @param array $new_definition
1328 * @param string $tablename
1329 * @return string SQL statement
1331 public function rename_index(
1337 $ret_commands = array();
1338 $ret_commands[] = $this->add_drop_constraint($table_name,$old_definition,true);
1339 $ret_commands[] = $this->add_drop_constraint($table_name,$new_definition);
1341 return $ret_commands;
1345 * Returns the number of columns in a table
1347 * @param string $table_name
1350 abstract public function number_of_columns(
1354 protected function _isTypeBoolean(
1366 protected function _getBooleanValue(
1370 //need to put the === sign here otherwise true == 'non empty string'
1371 if (empty($val) or $val==='off')
1377 protected function _isTypeNumber(
1397 * return true if the value if empty
1399 protected function _emptyValue(
1420 if ($val == '0000-00-00')