<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
/*********************************************************************************
 * SugarCRM is a customer relationship management program developed by
 * SugarCRM, Inc. Copyright (C) 2004 - 2007 SugarCRM Inc.
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License version 3 as published by the
 * Free Software Foundation with the addition of the following permission added
 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, see http://www.gnu.org/licenses or write to the Free
 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 * 
 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
 * 
 * The interactive user interfaces in modified source and object code versions
 * of this program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU General Public License version 3.
 * 
 * In accordance with Section 7(b) of the GNU General Public License version 3,
 * these Appropriate Legal Notices must retain the display of the "Powered by
 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
 * technical reasons, the Appropriate Legal Notices must display the words
 * "Powered by SugarCRM".
 ********************************************************************************/
/*********************************************************************************

* Description: This file handles the Data base functionality for the application specific
* to oracle database. It is called by the DBManager class to generate various sql statements.
*
* All the functions in this class will work with any bean which implements the meta interface.
* Please refer the DBManager documentation for the details.
*
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
* All Rights Reserved.
* Contributor(s): ______________________________________..
********************************************************************************/
require_once('include/database/DBHelper.php');

class MysqlHelper extends DBHelper
{

	function MysqlHelper(){
        parent::DBHelper();
	}

	/**
	* This is a private (php does not support it as of 4.x) method.
	* It outputs a correct string for the sql statement according to value
	* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
	* All Rights Reserved..
	* Contributor(s): ______________________________________..
	*/
	function massageValue($val, $fieldDef){
        if(!$val && $val !== '0') {
			return "''";
		}

        $type = $this->getFieldType($fieldDef);

		switch ($type){
		  case 'int':
		  case 'double':
		  case 'float':
		  case 'uint':
		  case 'ulong':
		  case 'long':
		  case 'short':
          case 'tinyint':
		    return $val;
		    break;
		}

		$qval = $this->quote($val);

		switch ($type){
		  case 'varchar':
          case 'char':
		  case 'text':
		  case 'longtext':	
		  case 'enum':
		  case 'multienum':
          case 'blob':
          case 'longblob':
          case 'clob':
          case 'id':
		  	return $qval;
		  	break;
		  case 'date':
		    return "$qval";
		    break;
		  case 'datetime':
		    return $qval;
		    break;
		  case 'time':
		    return "$qval";
		    break;
		}
		return $val;
	}

	/**
	* This method genrates sql for create table statement for a bean.
	* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
	* All Rights Reserved.
	* Contributor(s): ______________________________________..
	*/
	function createTableSQL($bean){

		$tablename = $bean->getTableName();
		$fieldDefs = $bean->getFieldDefinitions();
		$indices = $bean->getIndices();
		global $dictionary;
		$engine=null;
		if (isset($dictionary[$bean->getObjectName()]['engine'])) {
			$engine=$dictionary[$bean->getObjectName()]['engine'];
		}
		return $this->createTableSQLParams($tablename, $fieldDefs, $indices,$engine);

	}

	function createTableSQLParams($tablename, $fieldDefs, $indices, $engine=null){
		global $sugar_config;
		
		$columns = $this->columnSQLRep($fieldDefs, false, $tablename);
        $keys = $this->keysSQL($indices);
        if (!empty($keys)) $keys = ",$keys";
 		if(empty($columns)){
 			return false;
 		}
 		if (empty($engine) and isset($fieldDefs['engine'])) {
			$engine=$fieldDefs['engine']; 		
 		}
		// cn: bug 9873 - module tables do not get created in utf8 with assoc collation
		$sql = "CREATE TABLE $tablename ($columns $keys) CHARACTER SET utf8 COLLATE utf8_general_ci";
		if (!empty($engine)) {
			$sql.= " ENGINE=$engine"; 
		}
 		return $sql;
	}

    /** returns the valid type for a column given the type in fieldDef
    */

    function getColumnType($type){
        $map = array( 'int'      => 'int'
                    , 'double'   => 'double'
                    , 'float'    => 'float'
                    , 'uint'     => 'int unsigned'
                    , 'ulong'    => 'bigint unsigned'
                    , 'long'     => 'bigint'
                    , 'short'    => 'smallint'
                    , 'varchar'  => 'varchar'
                    , 'text'     => 'text'
                    , 'longtext' => 'longtext'
                    , 'date'     => 'date'
                    , 'enum'     => 'varchar'
                    , 'multienum'=> 'text'
                    , 'datetime' => 'datetime'
                    , 'time'     => 'time'
                    , 'bool'     => 'bool'
                    , 'tinyint'  => 'tinyint'
                    , 'char'  => 'char'
                    , 'blob'  => 'blob'
                    , 'longblob' => 'longblob'
                    , 'currency'=>'decimal(26,6)'
                    , 'decimal' => 'decimal'
                    , 'decimal2' => 'decimal'
                    , 'id' => 'char(36)'

                    );
        return $map[$type];
    }

    /** private function to get sql for a column
    */
    function oneColumnSQLRep($fieldDef, $ignoreRequired = false, $tablename = ''){
        $rep = parent::oneColumnSQLRep($fieldDef, $ignoreRequired, $tablename);
        return $rep;
    }

    /**
    * A private function which generates the SQL for changing columns
    */
    function changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired = false){

        if ($this->isFieldArray($fieldDefs)){
          foreach ($fieldDefs as $def) 
          	$columns[] = $this->oneColumnSQLRep($def, $ignoreRequired);
        } else {
          $columns[] = $this->oneColumnSQLRep($fieldDefs);
        }

        $columns = implode(",$action column ", $columns);
        $sql = "alter table $tablename $action column $columns";
        return $sql;
    }

    /**
    * This method generates sql that deletes a column identified by fieldDef.
    * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
    * All Rights Reserved.
    * Contributor(s): ______________________________________..
    */
    function deleteColumnSQL($bean, $fieldDefs){
        if ($this->isFieldArray($fieldDefs)) foreach ($fieldDefs as $fieldDef) $columns[] = $fieldDef['name'];
        else $columns[] = $fieldDefs['name'];
        $columns = implode(", drop column ", $columns);
        $sql = "alter table ".$bean->getTableName()." drop column $columns";
        return $sql;
    }

    /**
    * This method genrates sql for key statement for any bean identified by id.
    * The passes array is an array of field definitions or a field definition
    * itself. The keys generated will be either primary, foreign, unique, index
    * or none at all depending on the setting of the "key" parameter of a field definition
    * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
    * All Rights Reserved.
    * Contributor(s): ______________________________________..
    */
    function keysSQL( $indices, $alter_table = false, $alter_action = ''){
    	
       // check if the passed value is an array of fields.
       // if not, convert it into an array
       if (!$this->isFieldArray($indices)) $indices[] = $indices;

        $columns = array();
        foreach ($indices as $index){
          if(!empty($index['db']) && $index['db'] != 'mysql')continue;
          $type = $index['type'];

          $name = $index['name'];

          if(is_array($index['fields'])) {
          	$fields = implode(", ", $index['fields']);
          }else{
          	$fields = $index['fields'];
          }

          switch ($type){
            case 'unique':
                $columns[] = " UNIQUE $name ($fields)";
                break;
            case 'primary':
                $columns[] = " PRIMARY KEY ($fields)";
                break;
            case 'index':
            case 'foreign':
            case 'alternate_key':
                // to do - here it is assumed that the primary key of the foreign
                // table will always be named 'id'. It must be noted though
                // that this can easily be fixed by referring to db dictionalry
                // to find the correct primary field name
                if($alter_table){
                	$columns[] = " INDEX $name ($fields)";
                }else{
                	$columns[] = " KEY $name ($fields)";
                }
                break;
            case 'fulltext':
            	if ($this->full_text_indexing_enabled()) {
	                $columns[] = " FULLTEXT ($fields)";
            	} else {
            		$GLOBALS['log']->debug('MYISAM engine is not available/enabled, full-text indexes will be skipped. Skipping:',$name);
            	}
                break;
          }
       }
       $columns =  implode(", $alter_action ", $columns);
       if(!empty($alter_action)){
       	$columns=$alter_action . ' '. $columns;
       }
       return $columns;
    }

    function quote($string){
        $string = from_html($string);
        $string = mysql_real_escape_string($string);
        return "'$string'";
    }

    function escape_quote($string){
        $string = from_html($string);
        $string = mysql_real_escape_string($string);
        return $string;
    }

   	function setAutoIncrement($table, $field_name){
		return "auto_increment";
	}
    /**
     * Returns definitions of all indies for passed table. return will is a multi-dimensional array that
     * categorizes the index definition by types, unique, primary and index.
     * return format $indices = array ('index1'=>('name'=>'index1','type'=>'primary','fields'=>array('field1','field2'))
     * This format is similar to how indicies are defined in vardef file.
     */

    function get_indices($tablename) {
        $tablename=$tablename;
        $indices=array();
        //find all unique indexes and primary keys.
        $query="SHOW INDEX FROM $tablename";
        $result=$this->db->query($query);
        while (($row=$this->db->fetchByAssoc($result)) !=null) {
            $index_type='index';
            if ($row['Key_name'] =='PRIMARY') {
                $index_type='primary';  
            }   
            $indices[strtolower($row['Key_name'])]['name']=strtolower($row['Key_name']);
            $indices[strtolower($row['Key_name'])]['type']=$index_type;
            $indices[strtolower($row['Key_name'])]['fields'][]=strtolower($row['Column_name']);
        }
        return $indices;
    }
    /**
     * function generate alter constraint statement given a tabe name and vardef definition.
     * supports both adding and droping a constraint.
     */
    function add_drop_constraint($table,$definition, $drop=false) {        
        $type=$definition['type'];
        if(is_array($definition['fields']))
        	$fields=implode(',',$definition['fields']);
        $name=$definition['name'];
        $foreignTable=isset($definition['foreignTable']) ? $definition['foreignTable'] : array();
        switch ($type){
            // generic indices
            case 'index':
            case 'alternate_key':
                    if ($drop) {
                        $sql = "DROP INDEX {$name} ";
                    } else {
                        $sql = "CREATE INDEX {$name} ON {$table} ({$fields})";
                    }
                    break;
            // constraints as indices
            case 'unique':
                    if ($drop){
                        $sql = "ALTER TABLE {$table} DROP INDEX $name";
                    } else {
                        $sql = "ALTER TABLE {$table} ADD CONSTRAINT UNIQUE {$name} ({$fields})";
                    }
                    break;
            case 'primary':
                    if ($drop) {
                        $sql = "ALTER TABLE {$table} DROP PRIMARY KEY";
                    } else {
                        $sql = "ALTER TABLE {$table} ADD CONSTRAINT PRIMARY KEY ({$fields})";
                    }
                    break;
            case 'foreign':
                    if ($drop) {
                        $sql = "ALTER TABLE {$table} DROP FOREIGN KEY ({$fields})";
                    } else {
                        $sql = "ALTER TABLE {$table} ADD CONSTRAINT FORIEGN KEY {$name} ({$fields}) REFERENCES {$foreignTable}({$foreignfields})";
                    }
                    break;
        }
        return $sql;
    }

    function rename_index($old_definition,$new_definition,$table_name) {
        $ret_commands=array();
        $ret_commands[]=$this->add_drop_constraint($table_name,$old_definition,true);
        $ret_commands[]=$this->add_drop_constraint($table_name,$new_definition);
        return $ret_commands;
    }

    /* Function returns a count coulmns in the supplied table name, function is used primarily by 
     * custom module code.
     */
    function number_of_columns($table_name) {
        $result=$GLOBALS['db']->query("DESCRIBE $table_name");
        return (mysql_num_rows($result));
    }    
 
	/* This method genrates sql for insert statement.
	* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
	* All Rights Reserved..
	* Contributor(s): ______________________________________..
	*/
	function insertSQL($bean){
		$this->updateSQL($bean,null,false);		
	}
	
	/**
	* This method genrates sql for update statement.
	* Updates are based for the row identified by primary key only.
	* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
	* All Rights Reserved.
	* Contributor(s): ______________________________________..
	*/
	function updateSQL($bean, $where=array(),$isUpdate=true){
		if($isUpdate){
			// write out the SQL statement.
			$query = 'UPDATE '.$bean->table_name." set ";
		}else{
			$query = 'INSERT '. $bean->table_name . ' set ';
		}
		$firstPass = 0;
		foreach($bean->field_defs as $field=>$value) 
		{
			if(!isset($value['source']) || $value['source'] == 'db') 
			{
				// Do not write out the id field on the update statement.
				// We are not allowed to change ids.
				if($isUpdate && ('id' == $field)) 
					continue;
				//custom fields handle there save seperatley
				if(isset($bean->field_name_map) && !empty($bean->field_name_map[$field]['custom_type']))
					continue;
	
				// Only assign variables that have been set.
				if(isset($bean->$field)) 
				{
					if(strlen($bean->$field) <= 0) 
					{
						if(!$isUpdate && isset($value['default']) && (strlen($value['default']) > 0)) 
						{
							$bean->$field = $value['default'];
						}
						else 
						{
							$bean->$field = null;
						}
					}
					// Try comparing this element with the head element.
					if(0 == $firstPass) 
						$firstPass = 1;
					else 
						$query .= ", ";
	
					if(is_null($bean->$field)) 
					{
						$query .= $field."=null";
					}
					else 
					{
	                    //added check for ints because sql-server does not like casting varchar with a decimal value
	                    //into an int.
	                    if(isset($value['type']) and $value['type']=='int') {
	                        $query .= $field."=".PearDatabase::quote(from_html($bean->$field));                                
	                    } else {
	                        $query .= $field."='".PearDatabase::quote(from_html($bean->$field))."'";
	                    }
					}
				}
			}
		}
	
		if($isUpdate)
		{
			$query = $query." WHERE ID = '$bean->id'";
			$GLOBALS['log']->info("Save.Update $bean->object_name: ".$query);
		} 
		else 
		{
			$GLOBALS['log']->info("Save.Insert: ".$query);
		}
		
		$GLOBALS['db']->query($query, true);
	}
	
	function full_text_indexing_enabled($dbname=null) {
		
		$query="SHOW storage ENGINES";
		$stat=$GLOBALS['db']->query($query);
		if (!empty($stat)) {
			while (($row=$GLOBALS['db']->fetchByAssoc($stat))!= null) {
				
				if ($row['Engine']=='MyISAM' and ($row['Support']=='YES' or $row['Support']=='DEFAULT')) {
					return true;
				}		
			}
		}
		return false;
	}
}
?>
