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

<?php

Lib::loadClass('Core_DataSource');

class Core_DataSource_Pgsql extends Core_DataSource {

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

		list($host,$port)=explode(":", $host);
		$this->_conn = @pg_connect("host=".$host." port=".$port." dbname=".$database." user=".$user." password=".$password);

		//DEBUG_S(-3,'conn'.$names." ",$params);
		if (!is_resource($this->_conn)) {
			$this->_set_error('CREATE CONNECTION FAILED');
			return;
		}
		//if (false === mysql_select_db($database, $this->_conn)) {
		//	$this->_set_error('SELECT DATABASE FAILED');
		//	return;
		//}

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

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

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

	/**
	 * Wykonuje podane zapytanie i zwraca wynik mysql_query().
	 */
	function query( $query, $msg = 'Query ERROR.' ) {
		$null = null;
		if (!$this->_conn) { return $null; }
		$res = pg_query($this->_conn,$query);
		if (!$res) {
			DEBUG_S(-3,'error ',array($query),__FILE__,__FUNCTION__,__LINE__);
			$this->_set_error('SQL QUERY FAILED: '.pg_result_error($this->_conn)."(".$query.")".pg_last_error($this->_conn));
			return $null;
		}
		return $res;
	}

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

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

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

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

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

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

	function insert_id() {
		$sql="select lastval();";
		$res = $this->query($sql);
		$last=$this->fetch_array($res);
		return $last[0];
		//return fetch_row( $res ); //TODO TESTING!!!
		//die('TEST THIS FUNCTION!!!');
		//return mysql_insert_id( $this->_conn );
	}

	function affected_rows($res) {
		return pg_affected_rows( $res );
	}

	function _( $str ) {
		return pg_escape_string( $str);
	}

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

	function get_by_id( $table, $id ) {
		$primary=self::show_index_value($table); //TODO to optimalize cache

		$null = null;
		$sql = "select p.*
			from \"".$table."\" as p
			where p.\"".$primary."\"='".$id."'
		";
		$res = $this->query( $sql );
		if ($r = $this->fetch( $res )) {
			return $r;
		}
		return $null;
	}

	/**
	 * @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);
		$primary=self::show_index_value($table);

		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);
		//DEBUG_S(-3,'chk',array($fields_to_change),__FILE__,__FUNCTION__,__LINE__);

		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');
		if(!empty($timestamp))  //fixed timestamp option TODO @2015-01-not working due to trigger?
			$sql_obj->A_RECORD_UPDATE_DATE = $timestamp;
		else
		$sql_obj->A_RECORD_UPDATE_DATE = self::get_current_timestamp();

		$sql_obj->A_RECORD_UPDATE_AUTHOR = User::getName();
		foreach (get_object_vars($sql_obj) as $k => $v) {

			if($k=='A_RECORD_CREATE_DATE'&& $v=='0000-00-00 00:00:00') {
				unset($k);
				unset($v);
				continue;

			} else if(strstr(trim($structure[$k]->Type),'date') && (strstr($v,'-00'))) {
				$v="'".str_replace("-00","-01",$v)."'" ;
			} else if(strstr(trim($structure[$k]->Type),'date') && (strstr($v,'0000'))) {
				$v="'".str_replace("0000","1970",$v)."'" ;
			} else if (substr($v, 0, strlen('ST_GeomFromText')) == 'ST_GeomFromText') {

			} else if (strtoupper($v) == 'NOW()') {
				$v = 'NOW()';
			} else if (strtoupper($v) == 'NULL') {
				$v = 'NULL';
			} else {
				$v = $this->_($v);
				$v = "'{$v}'";
			}
			$sql_arr [] = '"'.$k.'"='.$v;
		}

		$sql = "update \"{$table}\" set ".implode(",", $sql_arr)." where \"".$primary."\"='{$id}'  ; ";
	//	DEBUG_S(-3,'update',array($sql),__FILE__,__FUNCTION__,__LINE__);
	//	die();
		$res=$this->query($sql);

		if ($this->has_errors()) {
			//DEBUG_S(-3,'errors',$this->has_errors,__FILE__,__FUNCTION__,__LINE__);
			return -1;
		}

		$ret = $this->affected_rows($res);
				//	DEBUG_S(-3,'affected',$ret,__FILE__,__FUNCTION__,__LINE__);

		if ($ret) {
			$sql_obj->ID_USERS2 = $sql_obj->$primary;
			unset($sql_obj->$primary);
			$new_id = $this->ADD_NEW_OBJ("{$table}_HIST", $sql_obj);
			if ($new_id) {
				$ret += 1;
			}
		}
		return $ret;
	}

	function ADD_NEW_OBJ( $table, $sql_obj,$dieonerror=null ) {
		$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 []= 'A_RECORD_CREATE_DATE';
		$admin_col []= 'A_RECORD_CREATE_AUTHOR';
		$admin_col []= 'A_RECORD_UPDATE_DATE';
		$admin_col []= 'A_RECORD_UPDATE_AUTHOR';
		// ...
		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) {
				unset($k);
				unset($v);
				continue;
			}  else if (substr($v, 0, strlen('ST_GeomFromText')) == 'ST_GeomFromText') {

			} else if(strstr(trim($structure[$k]->Type),'int')) {
				if(strlen($v)>0) {
					$v=$v;
				} else if(empty($v)) $v='null';
				else $v = $v;
			} else if(trim($structure[$k]->Type)=='datetime') {
				if(empty($v)) $v='null';
				else if(strstr($v,'-00')) $v="'".str_replace("-00","-01",$v)."'" ;
				else $v = "'".$v."'::timestamp";
			} else if(trim($structure[$k]->Type)=='date') {
				if($v=='0000-00-00') $v='null';
				else if(strstr($v,'-00')) $v="'".str_replace("-00","-01",$v)."'" ;
				else $v = "'".$v."'";
			} else if (strtoupper($v) == 'NOW()') {
				$v = 'NOW()';
			} else if (strtoupper($v) == 'NULL' && substr($table, -5) != '_HIST') {
				$v = 'NULL';
			} else if(strstr($structure[$k]->Type,'int(')) {
				$v = $v ;
			} 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))."); ";
		//DEBUG_S(-3,' insert sql ',$sql,__FILE__,__FUNCTION__,__LINE__);
		$this->query($sql);

		if ($this->has_errors()) {
			if($dieonerror) {
				DEBUG_S(-3,'Has errors died',$this->get_errors(),__FILE__,__FUNCTION__,__LINE__);
				die();
			}
		//	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());print_r($sql);echo'</pre>';
		}

		$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;
	}

}