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 * Generates sql for create table statement for a bean.
70 * @param object $bean SugarBean instance
71 * @return string SQL Create Table statement
73 public function createTableSQL(
77 $tablename = $bean->getTableName();
78 $fieldDefs = $bean->getFieldDefinitions();
79 $indices = $bean->getIndices();
80 return $this->createTableSQLParams($tablename, $fieldDefs, $indices);
85 * Generates sql for create table statement for a bean.
87 * @param string $tablename
88 * @param array $fieldDefs
89 * @param array $indices
90 * @param string $engine
91 * @return string SQL Create Table statement
93 public function createTableSQLParams(
100 $columns = $this->columnSQLRep($fieldDefs, false, $tablename);
104 $keys = $this->keysSQL($indices);
108 // cn: bug 9873 - module tables do not get created in utf8 with assoc collation
109 return "CREATE TABLE $tablename ($columns $keys) CHARACTER SET utf8 COLLATE utf8_general_ci";
114 * Generates SQL for insert statement.
116 * @param object $bean SugarBean instance
117 * @return string SQL Create Table statement
119 public function insertSQL(
123 // get column names and values
125 foreach ($bean->getFieldDefinitions() as $fieldDef)
127 if (isset($fieldDef['source']) && $fieldDef['source'] != 'db')
130 $val = $bean->getFieldValue($fieldDef['name']);
131 // clean the incoming value..
132 $val = from_html($val);
133 if (strlen($val) <= 0) {
134 if(isset($fieldDef['default']) && (strlen($fieldDef['default']) > 0))
135 $val = $fieldDef['default'];
140 //handle auto increment values here only need to do this on insert not create
141 if (isset($fieldDef['auto_increment']) && $fieldDef['auto_increment']) {
142 $values[$fieldDef['name']] =
143 $this->getAutoIncrementSQL($bean->getTableName(), $fieldDef['name']);
145 elseif (isset($bean->$fieldDef['name'])) {
146 // need to do some thing about types of values
147 $values[$fieldDef['name']] = $this->massageValue($val, $fieldDef);
149 elseif ($fieldDef['name'] == 'deleted'){
150 $values['deleted'] = $val;
154 if ( sizeof($values) == 0 )
155 return ""; // no columns set
157 // get the entire sql
158 return "INSERT INTO ".$bean->getTableName()."
159 (".implode(",", array_keys($values)).")
160 VALUES (".implode(",", $values).")";
164 * Generates SQL for update statement.
166 * @param object $bean SugarBean instance
167 * @param array $where Optional, where conditions in an array
168 * @return string SQL Create Table statement
170 public function updateSQL(
172 array $where = array()
175 $primaryField = $bean->getPrimaryFieldDefinition();
178 // get column names and values
179 foreach ($bean->getFieldDefinitions() as $field => $fieldDef) {
180 // Do not write out the id field on the update statement.
181 // We are not allowed to change ids.
182 if ($fieldDef['name'] == $primaryField['name'])
185 // If the field is an auto_increment field, then we shouldn't be setting it. This was added
186 // specially for Bugs and Cases which have a number associated with them.
187 if (isset($bean->field_name_map[$field]['auto_increment']) &&
188 $bean->field_name_map[$field]['auto_increment'] == true)
191 //custom fields handle their save seperatley
192 if(isset($bean->field_name_map) && !empty($bean->field_name_map[$field]['custom_type']))
195 if (isset($bean->$fieldDef['name'])
196 && (!isset($fieldDef['source']) || $fieldDef['source'] == 'db')) {
197 $val = $bean->getFieldValue($fieldDef['name']);
198 // clean the incoming value..
199 $val = from_html($val);
201 // need to do some thing about types of values
202 if (strlen($val) <= 0)
203 $columns[] = "{$fieldDef['name']}=null";
205 $columns[] = "{$fieldDef['name']}=".$this->massageValue($val, $fieldDef);
209 if ( sizeof($columns) == 0 )
210 return ""; // no columns set
212 // build where clause
213 $where = $this->updateWhereArray($bean, $where);
214 $where = $this->getWhereClause($bean, $where);
216 return "update ".$bean->getTableName()."
217 set ".implode(",", $columns)."
218 $where and deleted=0";
222 * This method returns a where array so that it has id entry if
223 * where is not an array or is empty
225 * @param object $bean SugarBean instance
226 * @param array $where Optional, where conditions in an array
229 protected function updateWhereArray(
231 array $where = array()
234 if (sizeof($where) == 0) {
235 $fieldDef = $bean->getPrimaryFieldDefinition();
236 $primaryColumn = $fieldDef['name'];
238 $val = $bean->getFieldValue($fieldDef['name']);
240 $where[$primaryColumn] = $val;
248 * Returns a where clause without the 'where' key word
250 * The clause returned does not have an 'and' at the beginning and the columns
251 * are joined by 'and'.
253 * @param string $table table name
254 * @param array $whereArray Optional, where conditions in an array
257 protected function getColumnWhereClause(
259 array $whereArray = array()
262 foreach ($whereArray as $name => $val) {
264 if (is_array($val)) {
267 foreach ($val as $tval){
270 $val = implode(",", $temp);
276 $where[] = " $table.$name $op $val";
279 if (is_array($where))
280 $where = implode(" and ", $where);
286 * This method returns a complete where clause built from the
287 * where values specified.
289 * @param string $table table name
290 * @param array $whereArray Optional, where conditions in an array
293 protected function getWhereClause(
298 return " where " . $this->getColumnWhereClause($bean->getTableName(), $whereArray);
302 * Designed to take an SQL statement and produce a list of fields used in that select
303 * @param String $selectStatement
305 public function getSelectFieldsFromQuery($selectStatement)
307 $selectStatement = trim($selectStatement);
308 if (strtoupper(substr($selectStatement, 0, 6)) == "SELECT")
309 $selectStatement = trim(substr($selectStatement, 6));
311 //Due to sql functions existing in many selects, we can't use php explode
315 $strLen = strlen($selectStatement);
316 for($i = 0; $i < $strLen; $i++)
318 $char = $selectStatement[$i];
320 if ($char == "," && $level == 0)
322 $field = $this->getFieldNameFromSelect(trim($selectField));
323 $fields[$field] = $selectField;
326 else if ($char == "("){
328 $selectField .= $char;
330 else if($char == ")"){
332 $selectField .= $char;
336 $selectField .= $char;
340 $fields[$this->getFieldNameFromSelect($selectField)] = $selectField;
345 * returns the field name used in a select
346 * @param String $string
348 protected function getFieldNameFromSelect($string)
350 if(strncasecmp($string, "DISTINCT ", 9) == 0) {
351 $string = substr($string, 9);
353 if (stripos($string, " as ") !== false)
354 //"as" used for an alias
355 return trim(substr($string, strripos($string, " as ") + 4));
356 else if (strrpos($string, " ") != 0)
357 //Space used as a delimeter for an alias
358 return trim(substr($string, strrpos($string, " ")));
359 else if (strpos($string, ".") !== false)
360 //No alias, but a table.field format was used
361 return substr($string, strpos($string, ".") + 1);
363 //Give up and assume the whole thing is the field name
368 * Generates SQL for delete statement identified by id.
370 * @param object $bean SugarBean instance
371 * @param array $where where conditions in an array
372 * @return string SQL Update Statement
374 public function deleteSQL(
379 $where = $this->updateWhereArray($bean, $where);
380 $where = $this->getWhereClause($bean, $where);
382 return "update ".$bean->getTableName()." set deleted=1 $where";
388 * Generates SQL for select statement for any bean identified by id.
390 * @param object $bean SugarBean instance
391 * @param array $where where conditions in an array
392 * @return string SQL Select Statement
394 public function retrieveSQL(
399 $where = $this->updateWhereArray($bean, $where);
400 $where = $this->getWhereClause($bean, $where);
402 return "select * from ".$bean->getTableName()." $where and deleted=0";
406 * This method implements a generic sql for a collection of beans.
408 * Currently, this function does not support outer joins.
410 * @param array $bean value returned by get_class method as the keys and a bean as
411 * the value for that key. These beans will be joined in the sql by the key
412 * attribute of field defs.
413 * @param array $cols Optional, columns to be returned with the keys as names of bean
414 * as identified by get_class of bean. Values of this array is the array of fieldDefs
415 * to be returned for a bean. If an empty array is passed, all columns are selected.
416 * @param array $whereClause Optional, values with the keys as names of bean as identified
417 * by get_class of bean. Each value at the first level is an array of values for that
418 * bean identified by name of fields. If we want to pass multiple values for a name,
419 * pass it as an array. If where is not passed, all the rows will be returned.
420 * @return string SQL Select Statement
422 public function retrieveViewSQL(
424 array $cols = array(),
425 array $whereClause = array()
428 $relations = array(); // stores relations between tables as they are discovered
430 foreach ($beans as $beanID => $bean) {
431 $tableName = $bean->getTableName();
432 $beanTables[$beanID] = $tableName;
435 $tables[$table] = $tableName;
436 $aliases[$tableName][] = $table;
438 // build part of select for this table
439 if (is_array($cols[$beanID]))
440 foreach ($cols[$beanID] as $def) $select[] = $table.".".$def['name'];
442 // build part of where clause
443 if (is_array($whereClause[$beanID])){
444 $where[] = $this->getColumnWhereClause($table, $whereClause[$beanID]);
446 // initialize so that it can be used properly in form clause generation
447 $table_used_in_from[$table] = false;
449 $indices = $bean->getIndices();
450 foreach ($indices as $index){
451 if ($index['type'] == 'foreign') {
452 $relationship[$table][] = array('foreignTable'=> $index['foreignTable']
453 ,'foreignColumn'=>$index['foreignField']
454 ,'localColumn'=> $index['fields']
458 $where[] = " $table.deleted = 0";
461 // join these clauses
462 $select = (sizeof($select) > 0) ? implode(",", $select) : "*";
463 $where = implode(" and ", $where);
465 // generate the from clause. Use relations array to generate outer joins
466 // all the rest of the tables will be used as a simple from
467 // relations table define relations between table1 and table2 through column on table 1
468 // table2 is assumed to joing through primaty key called id
470 foreach ($relations as $table1 => $rightsidearray){
471 if ($table_used_in_from[$table1]) continue; // table has been joined
473 $from .= $separator." ".$table1;
474 $table_used_in_from[$table1] = true;
475 foreach ($rightsidearray as $tablearray){
476 $table2 = $tablearray['foreignTable']; // get foreign table
477 $tableAlias = $aliases[$table2]; // get a list of aliases fo thtis table
478 foreach ($tableAlias as $table2) {
479 //choose first alias that does not match
480 // we are doing this because of self joins.
481 // in case of self joins, the same table will bave many aliases.
482 if ($table2 != $table1) break;
485 $col = $tablearray['foreingColumn'];
486 $name = $tablearray['localColumn'];
487 $from .= " LEFT JOIN $table on ($table1.$name = $table2.$col)";
488 $table_used_in_from[$table2] = true;
493 return "select $select from $from where $where";
497 * Generates SQL for create index statement for a bean.
499 * @param object $bean SugarBean instance
500 * @param array $fields fields used in the index
501 * @param string $name index name
502 * @param bool $unique Optional, set to true if this is an unique index
503 * @return string SQL Select Statement
505 public function createIndexSQL(
512 $unique = ($unique) ? "unique" : "";
513 $tablename = $bean->getTableName();
516 foreach ($fields as $fieldDef)
517 $columns[] = $fieldDef['name'];
519 if (sizeof($columns) == 0)
522 $columns = implode(",", $columns);
524 return "create $unique index $name on $tablename ($columns)";
528 * Returns the type of the variable in the field
530 * @param array $fieldDef
533 public function getFieldType(
537 // get the type for db type. if that is not set,
538 // get it from type. This is done so that
539 // we do not have change a lot of existing code
540 // and add dbtype where type is being used for some special
541 // purposes like referring to foreign table etc.
542 if(!empty($fieldDef['dbType']))
543 return $fieldDef['dbType'];
544 if(!empty($fieldDef['dbtype']))
545 return $fieldDef['dbtype'];
546 if (!empty($fieldDef['type']))
547 return $fieldDef['type'];
548 if (!empty($fieldDef['Type']))
549 return $fieldDef['Type'];
550 if (!empty($fieldDef['data_type']))
551 return $fieldDef['data_type'];
557 * Returns the defintion for a single column
559 * @param array $fieldDef
560 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
561 * @param string $table Optional, table name
562 * @param bool $return_as_array Optional, true if we should return the result as an array instead of sql
563 * @return string or array if $return_as_array is true
565 protected function oneColumnSQLRep(
567 $ignoreRequired = false,
569 $return_as_array = false
573 $name = $fieldDef['name'];
574 $type = $this->getFieldType($fieldDef);
575 $colType = $this->getColumnType($type, $name, $table);
577 if (( $colType == 'nvarchar'
578 or $colType == 'nchar'
579 or $colType == 'varchar'
580 or $colType == 'char'
581 or $colType == 'varchar2') ) {
582 if( !empty($fieldDef['len']))
583 $colType .= "(".$fieldDef['len'].")";
587 if($colType == 'decimal' || $colType == 'float'){
588 if(!empty($fieldDef ['len'])){
589 if(!empty($fieldDef['precision']) && is_numeric($fieldDef['precision']))
590 if(strpos($fieldDef ['len'],',') === false){
591 $colType .= "(".$fieldDef['len'].",".$fieldDef['precision'].")";
593 $colType .= "(".$fieldDef['len'].")";
596 $colType .= "(".$fieldDef['len'].")";
601 if (isset($fieldDef['default']) && strlen($fieldDef['default']) > 0)
602 $default = " DEFAULT '".$fieldDef['default']."'";
603 elseif (!isset($default) && $type == 'bool')
604 $default = " DEFAULT 0 ";
605 elseif (!isset($default))
608 $auto_increment = '';
609 if(!empty($fieldDef['auto_increment']) && $fieldDef['auto_increment'])
610 $auto_increment = $this->setAutoIncrement($table , $fieldDef['name']);
612 $required = 'NULL'; // MySQL defaults to NULL, SQL Server defaults to NOT NULL -- must specify
613 //Starting in 6.0, only ID and auto_increment fields will be NOT NULL in the DB.
614 if ((empty($fieldDef['isnull']) || strtolower($fieldDef['isnull']) == 'false') &&
615 (!empty($auto_increment) || $name == 'id' || ($fieldDef['type'] == 'id' && isset($fieldDef['required']) && $fieldDef['required'])))
617 $required = "NOT NULL";
622 if ( $return_as_array )
625 'colType' => $colType,
626 'default' => $default,
627 'required' => $required,
628 'auto_increment' => $auto_increment,
629 'full' => "$name $colType $default $required $auto_increment",
632 return "$name $colType $default $required $auto_increment";
637 * Returns SQL defintions for all columns in a table
639 * @param array $fieldDefs
640 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
641 * @param string $tablename Optional, table name
642 * @return string SQL column definitions
644 protected function columnSQLRep(
646 $ignoreRequired = false,
652 if ($this->isFieldArray($fieldDefs)) {
653 foreach ($fieldDefs as $fieldDef) {
654 if(!isset($fieldDef['source']) || $fieldDef['source'] == 'db') {
655 $columns[] = $this->oneColumnSQLRep($fieldDef,false, $tablename);
658 $columns = implode(",", $columns);
661 $columns = $this->oneColumnSQLRep($fieldDefs,$ignoreRequired, $tablename);
668 * Returns the next value for an auto increment
670 * @param string $table tablename
671 * @param string $field_name
674 public function getAutoIncrement(
683 * Returns the sql for the next value in a sequence
685 * @param string $table tablename
686 * @param string $field_name
689 public function getAutoIncrementSQL(
700 * Either creates an auto increment through queries or returns sql for auto increment
701 * that can be appended to the end of column defination (mysql)
703 * @param string $table tablename
704 * @param string $field_name
707 protected function setAutoIncrement(
712 $this->deleteAutoIncrement($table, $field_name);
718 * Sets the next auto-increment value of a column to a specific value.
720 * @param string $table tablename
721 * @param string $field_name
723 public function setAutoIncrementStart(
733 * Deletes an auto increment (for oracle not mysql)
735 * @param string $table tablename
736 * @param string $field_name
738 public function deleteAutoIncrement(
747 * Generates the SQL for changing columns
749 * @param string $tablename
750 * @param array $fieldDefs
751 * @param string $action
752 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
754 abstract protected function changeColumnSQL(
758 $ignoreRequired = false);
761 * This method generates sql for adding a column to table identified by field def.
763 * @param string $tablename
764 * @param array $fieldDefs
765 * @return string SQL statement
767 public function addColumnSQL(
772 return $this->changeColumnSQL($tablename, $fieldDefs, 'add');
776 * This method genrates sql for altering old column identified by oldFieldDef to new fieldDef.
778 * @param string $tablename
779 * @param array $newFieldDefs
780 * @param bool $ignoreRequired Optional, true if we should ignor this being a required field
781 * @return string SQL statement
783 public function alterColumnSQL(
786 $ignorerequired = false
789 return $this->changeColumnSQL($tablename, $newFieldDefs, 'modify', $ignorerequired);
793 * Generates SQL for dropping a table.
795 * @param object $bean Sugarbean instance
796 * @return string SQL statement
798 public function dropTableSQL(
802 return $this->dropTableNameSQL($bean->getTableName());
806 * Generates SQL for dropping a table.
808 * @param string $name table name
809 * @return string SQL statement
811 public function dropTableNameSQL(
815 return "drop table if exists ".$name;
819 * This method generates sql that deletes a column identified by fieldDef.
821 * @param object $bean Sugarbean instance
822 * @param array $fieldDefs
823 * @return string SQL statement
825 public function deleteColumnSQL(
830 if ($this->isFieldArray($fieldDefs))
831 foreach ($fieldDefs as $fieldDef)
832 $columns[] = $fieldDef['name'];
834 $columns[] = $fieldDefs['name'];
836 return "alter table ".$bean->getTableName()." drop (".implode(", ", $columns).")";
840 * This method generates sql that drops a column identified by fieldDef.
841 * Designed to work like the other addColumnSQL() and alterColumnSQL() functions
843 * @param string $tablename
844 * @param array $fieldDefs
845 * @return string SQL statement
847 public function dropColumnSQL(
852 $sql = $this->changeColumnSQL(
862 * Generates SQL for key statement for any bean identified by id.
864 * The passes array is an array of field definitions or a field definition
865 * itself. The keys generated will be either primary, foreign, unique, index
866 * or none at all depending on the setting of the "key" parameter of a field definition
868 * @param array $indices
869 * @param bool $alter_table
870 * @param string $alter_action
871 * @return string SQL Statement
873 protected function keysSQL(
875 $alter_table = false,
883 * Outputs a correct string for the sql statement according to value
886 * @param array $fieldDef field definition
889 public function massageValue(
894 if ( strlen($val) <= 0 )
897 $type = $this->getFieldType($fieldDef);
913 $qval = $this->quote($val);
940 * Massages the field defintions to fill in anything else the DB backend may add
942 * @param array $fieldDef
943 * @param string $tablename
946 public function massageFieldDef(
951 if ( !isset($fieldDef['dbType']) ) {
952 if ( isset($fieldDef['dbtype']) )
953 $fieldDef['dbType'] = $fieldDef['dbtype'];
955 $fieldDef['dbType'] = $fieldDef['type'];
957 $type = $this->getColumnType($fieldDef['dbType'],$fieldDef['name'],$tablename);
959 preg_match_all("/(\w+)(?:\(([0-9]+,?[0-9]*)\)|)/i", $type, $matches);
960 if ( isset($matches[1][0]) )
961 $fieldDef['type'] = $matches[1][0];
962 if ( isset($matches[2][0]) && empty($fieldDef['len']) )
963 $fieldDef['len'] = $matches[2][0];
964 if ( !empty($fieldDef['precision']) && is_numeric($fieldDef['precision']) && !strstr($fieldDef['len'],',') )
965 $fieldDef['len'] .= ",{$fieldDef['precision']}";
966 if (isset($fieldDef['required']) && ($fieldDef['required'] == true
967 || $fieldDef['required'] == '1'
968 || $fieldDef['required'] == 1)
969 || ($fieldDef['name'] == 'id' && !isset($fieldDef['required'])) )
970 $fieldDef['required'] = 'true';
974 * Returns the valid type for a column given the type in fieldDef
976 * @param string $type field type
977 * @return string valid type for the given field
979 abstract public function getColumnType(
986 * Checks to see if passed array is truely an array of defitions
988 * Such an array may have type as a key but it will point to an array
989 * for a true array of definitions an to a col type for a definition only
991 * @param mixed $defArray
994 public function isFieldArray(
998 if ( !is_array($defArray) )
1001 if ( isset($defArray['type']) ){
1002 // type key exists. May be an array of defs or a simple definition
1003 $type = $defArray['type'];
1004 return is_array($type); // type is not an array => definition else array
1007 // type does not exist. Must be array of definitions
1012 * returns true if the type can be mapped to a valid column type
1014 * @param string $type
1017 protected function validColumnType(
1021 $coltype = $this->getColumnType($type);
1022 return ($coltype) ? true : false;
1026 * Saves changes to module's audit table
1028 * @param object $bean Sugarbean instance
1029 * @param array $changes changes
1030 * @see DBHelper::getDataChanges()
1032 public function save_audit_records(SugarBean $bean, $changes)
1034 global $current_user;
1035 $sql = "INSERT INTO ".$bean->get_audit_table_name();
1036 //get field defs for the audit table.
1037 require('metadata/audit_templateMetaData.php');
1038 $fieldDefs = $dictionary['audit']['fields'];
1041 $values['id']=$this->massageValue(create_guid(), $fieldDefs['id']);
1042 $values['parent_id']=$bean->dbManager->getHelper()->massageValue($bean->id, $fieldDefs['parent_id']);
1043 $values['field_name']=$bean->dbManager->getHelper()->massageValue($changes['field_name'], $fieldDefs['field_name']);
1044 $values['data_type']=$bean->dbManager->getHelper()->massageValue($changes['data_type'], $fieldDefs['data_type']);
1045 if ($changes['data_type']=='text') {
1046 $bean->fetched_row[$changes['field_name']]=$changes['after'];;
1047 $values['before_value_text']=$bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_text']);
1048 $values['after_value_text']=$bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_text']);
1050 $bean->fetched_row[$changes['field_name']]=$changes['after'];;
1051 $values['before_value_string']=$bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_string']);
1052 $values['after_value_string']=$bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_string']);
1054 $values['date_created']=$bean->dbManager->getHelper()->massageValue(TimeDate::getInstance()->nowDb(), $fieldDefs['date_created'] );
1055 $values['created_by']=$bean->dbManager->getHelper()->massageValue($current_user->id, $fieldDefs['created_by']);
1057 $sql .= "(".implode(",", array_keys($values)).") ";
1058 $sql .= "VALUES(".implode(",", $values).")";
1060 if ( $this->db->dbType == 'oci8' && $changes['data_type'] == 'text' ) {
1061 $sql .= " RETURNING before_value_text, after_value_text INTO :before_value_text, :after_value_text";
1062 $stmt = oci_parse($this->db->getDatabase(), $sql);
1063 $err = oci_error($this->db->getDatabase());
1065 $GLOBALS['log']->fatal($sql.">>".$err['code'].":".$err['message']);
1068 $before_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
1069 oci_bind_by_name($stmt, ":before_value_text", $before_value_text_LOB, -1, OCI_B_CLOB);
1070 $after_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
1071 oci_bind_by_name($stmt, ":after_value_text", $after_value_text_LOB, -1, OCI_B_CLOB);
1072 oci_execute($stmt, OCI_DEFAULT);
1073 $err = oci_error($this->db->getDatabase());
1075 $GLOBALS['log']->fatal($sql.">>".$err['code'].":".$err['message']);
1078 $before_value_text_LOB->save($changes['before']);
1079 $after_value_text_LOB->save($changes['after']);
1080 oci_commit($this->db->getDatabase());
1081 $before_value_text_LOB->free();
1082 $after_value_text_LOB->free();
1083 oci_free_statement($stmt);
1086 $bean->db->query($sql);
1091 * Uses the audit enabled fields array to find fields whose value has changed.
1092 * The before and after values are stored in the bean.
1094 * @param object $bean Sugarbean instance
1097 public function getDataChanges(
1101 $changed_values=array();
1102 $audit_fields=$bean->getAuditEnabledFieldDefinitions();
1104 if (is_array($audit_fields) and count($audit_fields) > 0) {
1105 foreach ($audit_fields as $field=>$properties) {
1107 if (!empty($bean->fetched_row) && array_key_exists($field, $bean->fetched_row)) {
1109 if (isset($properties['type']))
1110 $field_type=$properties['type'];
1112 if (isset($properties['dbType']))
1113 $field_type=$properties['dbType'];
1114 else if(isset($properties['data_type']))
1115 $field_type=$properties['data_type'];
1117 $field_type=$properties['dbtype'];
1120 // Bug # 44624 - check for currency type and cast to float
1121 // this ensures proper matching for change log
1122 if ( (strcmp($field_type,"currency")==0) ) {
1123 $before_value=(float)$bean->fetched_row[$field];
1124 $after_value=(float)$bean->$field;
1126 $before_value=$bean->fetched_row[$field];
1127 $after_value=$bean->$field;
1130 //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.
1131 if(!empty($field_type) && $field_type == 'date'){
1132 $before_value = from_db_convert($before_value , $field_type);
1134 //if the type and values match, do nothing.
1135 if (!($this->_emptyValue($before_value,$field_type) && $this->_emptyValue($after_value,$field_type))) {
1136 if (trim($before_value) !== trim($after_value)) {
1137 // 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.
1138 if (!($this->_isTypeNumber($field_type) && abs((trim($before_value)+0)-(trim($after_value)+0))<0.001)) {
1139 if (!($this->_isTypeBoolean($field_type) && ($this->_getBooleanValue($before_value)== $this->_getBooleanValue($after_value)))) {
1140 $changed_values[$field]=array('field_name'=>$field,
1141 'data_type'=>$field_type,
1142 'before'=>$before_value,
1143 'after'=>$after_value);
1151 return $changed_values;
1155 * Function returns true is full-text indexing is available in the connected database.
1157 * Default value is false.
1159 * @param string $dbname
1162 abstract protected function full_text_indexing_enabled(
1167 * Quotes a string for storing in the database
1169 * Return value will be surrounded by quotes
1171 * @param string $string
1174 public function quote(
1178 return "'".$this->db->quote($string)."'";
1182 * Quotes a string for storing in the database
1184 * Return value will be not surrounded by quotes
1186 * @param string $string
1189 public function escape_quote(
1193 return $this->db->quote($string);
1197 * Returns definitions of all indies for passed table.
1199 * return will is a multi-dimensional array that
1200 * categorizes the index definition by types, unique, primary and index.
1204 * 'index1'=> array (
1205 * 'name' => 'index1',
1206 * 'type' => 'primary',
1207 * 'fields' => array('field1','field2')
1212 * This format is similar to how indicies are defined in vardef file.
1214 * @param string $tablename
1217 abstract public function get_indices(
1222 * Returns definitions of all indies for passed table.
1224 * return will is a multi-dimensional array that
1225 * categorizes the index definition by types, unique, primary and index.
1229 * 'field1'=> array (
1230 * 'name' => 'field1',
1231 * 'type' => 'varchar',
1237 * This format is similar to how indicies are defined in vardef file.
1239 * @param string $tablename
1242 abstract public function get_columns(
1247 * Generates alter constraint statement given a table name and vardef definition.
1249 * Supports both adding and droping a constraint.
1251 * @param string $table tablename
1252 * @param array $defintion field definition
1253 * @param bool $drop true if we are dropping the constraint, false if we are adding it
1254 * @return string SQL statement
1256 abstract public function add_drop_constraint(
1262 * Renames an index definition
1264 * @param array $old_definition
1265 * @param array $new_definition
1266 * @param string $tablename
1267 * @return string SQL statement
1269 public function rename_index(
1275 $ret_commands = array();
1276 $ret_commands[] = $this->add_drop_constraint($table_name,$old_definition,true);
1277 $ret_commands[] = $this->add_drop_constraint($table_name,$new_definition);
1279 return $ret_commands;
1283 * Returns the number of columns in a table
1285 * @param string $table_name
1288 abstract public function number_of_columns(
1292 protected function _isTypeBoolean(
1304 protected function _getBooleanValue(
1308 //need to put the === sign here otherwise true == 'non empty string'
1309 if (empty($val) or $val==='off')
1315 protected function _isTypeNumber(
1335 * return true if the value if empty
1337 protected function _emptyValue(
1358 if ($val == '0000-00-00')