se/SE/se-lib/Core/DataSource/Mysql.php

<?php

Lib::loadClass('Core_DataSource');
Lib::loadClass('DataSourceException');

class Core_DataSource_Mysql extends Core_DataSource {

	function __construct($host, $user, $password, $database, $names = '', $params = array()) {
		parent::__construct($host, $user, $password, $database, $names, $params);

		$this->_conn = @mysql_pconnect($host, $user, $password);
		if (!is_resource($this->_conn)) throw new Exception("Create connection failed!");
		if (false === mysql_select_db($database, $this->_conn)) throw new Exception("Select database failed!");

		if ($names != '') {
			$this->query(" SET NAMES '$names' ");
		}
	}

	function getConnection() {
		return $this->_conn;
	}

	function getVersion($version) {
		if (!$this->_version) {
			$sql = "SHOW VARIABLES LIKE 'version';";
			$res = $this->query($sql);
			if ($r = $this->fetch($res)) {
				// [Variable_name] => version, [Value] => 4.0.26-log
				$this->_version = $r->Value;
			}
		}
		return $this->_version;
	}

	function insert($table, $data) {
		if (is_object($data)) $data = (array)$data;
		else if (!is_array($data)) throw new Exception("Wrong data type to insert.");
		$sqlFields = array();
		$sqlValues = array();
		foreach ($data as $fldName => $fldValue) {
			$sqlFields[] = "`{$fldName}`";
			$sqlValues[] = $this->parseValue($fldValue);
		}
		$sqlFields = implode(", ", $sqlFields);
		$sqlValues = implode(", ", $sqlValues);
		$sql = "insert into `{$table}` ({$sqlFields})
			values ({$sqlValues})
		";
		$this->query($sql);
		return mysql_insert_id($this->_conn);
	}

	function getById($tableName, $id) {
		$sqlTableName = $this->_($tableName);
		$sqlId = (int)$this->_($id);
		if (!$sqlTableName) throw new Exception("Wrong table name!");
		if ($sqlId <= 0) throw new Exception("Wrong record id!");
		$sql = "select t.*
			from `{$sqlTableName}` as t
			where t.`ID`='{$sqlId}'
		";
		$res = $this->query($sql);
		if ($r = $this->fetch($res)) {
			return $r;
		} else throw new Exception("Nie naleziono rekordu nr '{$sqlId}'");
	}

	function getListByQuery($sql) {
		$list = array();
		if (!$sql) throw new Exception("Empty query!");
		$res = $this->query($sql);
		while ($r = $this->fetch($res)) {
			$list[] = $r;
		}
		return $list;
	}

	public function parseValue($value) {
		$parsedValue = 'NULL';
		if ('NOW()' == strtoupper($value)) {
			$parsedValue = 'NOW()';
		} else if ('GeomFromText' == substr($value, 0, strlen('GeomFromText'))) {
		} else {
			$parsedValue = "'" . $this->_($value) . "'";
		}
		return $parsedValue;
	}

	function query($query) {
		$null = null;
		if (!$this->_conn) throw new Exception("Connection not exists!");
		$res = mysql_query($query, $this->_conn);
		if (!$res) {
			$exception = new DataSourceException("Query error: " . mysql_error($this->_conn), mysql_errno($this->_conn));
			$exception->setQuery($query);
			throw $exception;
		}
		return $res;
	}

	function fetch( $res ) {
		$ret = null;
		if ($res) $ret = mysql_fetch_object( $res );
		return $ret;
	}

	function fetch_row( $res ) {
		$ret = null;
		if ($res) $ret = mysql_fetch_row( $res );
		return $ret;
	}

	function fetch_assoc( $res ) {
		$ret = null;
		if ($res) $ret = mysql_fetch_assoc( $res );
		return $ret;
	}

	function fetch_array($res) {
		$ret = null;
		if ($res) $ret = mysql_fetch_array($res);
		return $ret;
	}

	function count( $res ) {
		return mysql_num_rows( $res );
	}

	function num_rows( $res ) {
		return mysql_num_rows( $res );
	}

	function insert_id() {
		return mysql_insert_id( $this->_conn );
	}
	function show_tables($table=null) {
		if(!empty($table)) $sql="show tables like '".$table."'";
		else $sql="show tables";
		$res = $this->query($sql);
		return $res;
	}

	function describe_table($table) {
		$sql='SHOW FIELDS FROM `'.$table.'`';
		$res = $this->query($sql);
		return $res;
	}

	function describe_table_value($table) {

		$res = self::describe_table($table);
		while($h=self::fetch($res)) {
			$result[$h->Field]=$h;
		}
		return $result;
	}

	function show_index($table,$only_primary_flag=false) {
		$sql="show index from `".$table."`";
		if($only_primary_flag) $sql.=" WHERE  `Key_name` =  'PRIMARY' ";

		$res=self::query($sql);
		return $res;

	}

	function show_index_value($table) {
		$res=self::show_index($table,true);
		while($h=self::fetch($res)) {
			return $h->Column_name;
		}
	}

	function affected_rows($needed_in_psql_only=null) {
		return mysql_affected_rows( $this->_conn );
	}

	function _( $str ) {
		return mysql_real_escape_string( $str, $this->_conn );
	}

	function error() {
		return "#".mysql_errno($this->_conn).": ".mysql_error($this->_conn);
	}

	function errno() {
		return mysql_errno($this->_conn);
	}

	/**
	 * @returns int
	 *   1 - changed but without add hist
	 *   2 - changed and add hist
	 *   0 - nothing to change
	 *   -1 - sql errors
	 *   -2 - error id not exists in DB
	 *   -3 - error ID not set
	 *
	 * TODO: sprawdzac czy w hist mozna odczytac aktualny stan, jesli nie to dodac caly rekord do HIST, jako 'procesy-fix-hist-data'
	 */
	public function UPDATE_OBJ($table, $sql_obj,$timestamp=null,$skip_author=null) {
		$structure=self::describe_table_value($table); //todo to cache optimize
		$primary=self::show_index_value($table); //todo to cache optimize

		if (!isset($sql_obj->$primary) || $sql_obj->$primary <= 0) {
			return -3;
		}
		$id = $sql_obj->$primary;

		// check id record $id exists
		if (($curr_obj = $this->get_by_id( $table, $sql_obj->$primary )) == null) {
			return -2;
		}

		// check if enything changed
		$changed = false;
		$fields_to_change = get_object_vars($sql_obj);
		foreach ($fields_to_change as $k => $v) {
			if ($k == $primary) continue;
			if ($v == $curr_obj->$k) {// === ?
				unset($sql_obj->$k);
			} else {
				$changed = true;
			}
		}
		if ($changed == false) {
			return 0;// record not changed
		}

		$sql_arr = array();
		// TODO: add admin columns if exists in table - search in session
		$admin_col = array();
		$admin_col[] = 'A_RECORD_CREATE_DATE';
		$admin_col[] = 'A_RECORD_CREATE_AUTHOR';
		// ...
		$sql_obj->A_RECORD_UPDATE_DATE = date('Y-m-d-H:i');
// OFF - BUG w _HIST		$sql_obj->A_RECORD_UPDATE_DATE = "FROM_UNIXTIME(".$this->get_current_time().")";

		$sql_obj->A_RECORD_UPDATE_AUTHOR = User::getName();
		foreach (get_object_vars($sql_obj) as $k => $v) {
		   if(!empty($skip_author)) {
			   if($k=='A_RECORD_UPDATE_AUTHOR') continue;
			   if($k=='A_RECORD_UPDATE_DATE') continue;
		   }


			if (strtoupper($v) == 'NOW()') {
				$v = 'NOW()';
			} else if (strtoupper($v) == 'NULL') {
				$v = 'NULL';
			} else if (substr($v, 0, strlen('GeomFromText')) == 'GeomFromText') {

			} else {
				$v = $this->_($v);
				$v = "'{$v}'";
			}
			$sql_arr [] = "`{$k}`={$v}";
		}
		$sql = "update `{$table}` set ".implode(",", $sql_arr)." where `ID`='{$id}' limit 1; ";
		$this->query($sql);

		$returnError = false;
		$skipDbErrorAddHist = false;
		if ($this->has_errors()) {
			$returnError = true;
			if (1146 == $this->errno()) {
				$skipDbErrorAddHist = true;
			}
		}

		$returnCode = 0;
		$affected = $this->affected_rows();
		if ($affected || $skipDbErrorAddHist) {
			$returnCode = 1;
			$sql_obj->ID_USERS2 = $sql_obj->$primary;
			unset($sql_obj->$primary);
			$new_id = $this->ADD_NEW_OBJ("{$table}_HIST", $sql_obj);
			if ($new_id) {
				$returnCode += 1;
			}
		}
		if ($returnError) {
			return -1;
		}
		return $returnCode;
	}

	function ADD_NEW_OBJ( $table, $sql_obj,$dieonerror=null ) {

		//TODO to optimize:
		$structure=self::describe_table_value($table);
		$primary=self::show_index_value($table);
		$sql_arr = array();
		// TODO: add admin columns if exists in table - search in session
		$admin_col = array();
		$admin_col []= 'ID';
		$admin_col []= 'A_RECORD_CREATE_DATE';
		$admin_col []= 'A_RECORD_CREATE_AUTHOR';
		$admin_col []= 'A_RECORD_UPDATE_DATE';
		$admin_col []= 'A_RECORD_UPDATE_AUTHOR';
		// ...
		$sql_arr["`ID`"] = "NULL";// add default value for ID, NULL in all inserts
		if (substr($table, 0, -5) == '_HIST') {
			$sql_obj->A_RECORD_UPDATE_DATE = date('Y-m-d-H:i');
			$sql_obj->A_RECORD_UPDATE_AUTHOR = User::getName();
		} else {
			$sql_obj->A_RECORD_CREATE_DATE = date('Y-m-d-H:i');
			$sql_obj->A_RECORD_CREATE_AUTHOR = User::getName();
		}

		foreach (get_object_vars($sql_obj) as $k => $v) {
			if($k==$primary) $v='0';
			else if (strtoupper($v) == 'NOW()') {
				$v = 'NOW()';
			} else if (strtoupper($v) == 'NULL' && substr($table, -5) != '_HIST') {
				$v = 'NULL';
			} else if (substr($v, 0, strlen('GeomFromText')) == 'GeomFromText') {

			} else {
				$v = $this->_($v);
				$v = "'{$v}'";
			}
			$sql_arr ["`{$k}`"] = $v;
		}
		$sql = "insert into `{$table}` (".implode(",", array_keys($sql_arr)).") values (".implode(",", array_values($sql_arr))."); ";
		//error_log($sql);
		$this->query($sql);

		if ($this->has_errors()) {
			if(!empty($dieonerror)) {
			  echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;text-align:left;">db errors: (' . __CLASS__ . '::' . __FUNCTION__ . ':' . __LINE__ . '): ';print_r($this->get_errors());echo'</pre>';
			  DEBUG_S(-3,'Bledne zapytanie sql',$sql,__FILE__,__FUNCTION__,__LINE__);
			}
		}

		$ret_id = $this->insert_id();
		if (substr($table, -5) == '_HIST') {
			return $ret_id;
		}
		if ($ret_id) {
			$sql_obj->ID_USERS2 = $ret_id;
			unset($sql_obj->ID);
			$new_id_hist = $this->ADD_NEW_OBJ($table . '_HIST', $sql_obj);
			// error jesli nie udalo sie dodac rekordu do tabeli _HIST
		}
		return $ret_id;
	}

}