se/SE/se-lib/DB.php

<?php

/*
# Usage:

## Usage - example 1:
try {
	$pdo = DB::getPDO();
	$pdo->getDatabaseName();
	$pdo->getZasobId();

	$sth = $pdo->prepare("select * from CRM_LISTA_ZASOBOW limit 10");
	$sth->execute();
	$rows = $sth->fetchAll();
} catch (Exception $e) {
	echo "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage();
}

## Usage - example 2:
try {
	$pdo = DB::getPDO();
	$rows = $pdo->fetchAll("select * from CRM_LISTA_ZASOBOW limit 10");
} catch (Exception $e) {
	echo "Error #" . $e->getCode() .  "|" . $e->getLine() .  ": " . $e->getMessage();
}

*/

Lib::loadClass('Config');
Lib::loadClass('DataSourceException');
Lib::loadClass('Core_Pdo');
Lib::loadClass('Core_TypeFactory');

class DB {

	/**
	 * Get database object.
	 *
	 * @param string $db Zasob ID or database name (require config file @see Config::getZasobConf($db))
	 *
	 * @returns object Database
	 */
	public static function getDB($db = null) {
		static $_instance;
		if (!is_array($_instance)) {
			$_instance = array();
		}
		$dbConfName = 'default_db';
		if (is_numeric($db) && $db > 0) {
			$dbConfName = "zasob_{$db}";
		} else if ($db == 'import_db') {
			$dbConfName = "import_db";
		} else if ($db == 'test_db') {
			$dbConfName = "test_db";
		} else if ($db == 'test3_db') {
			$dbConfName = "test3_db";
		}  else if ($db == 'billing_db') {
			$dbConfName = "billing_db";
		}


		if (!array_key_exists($dbConfName, $_instance)) {
			$_instance[$dbConfName] = null;

			Lib::loadClass('Config');
			$conf = Config::getConfFile($dbConfName);
			if ($conf) {
				$type = V::get('type', 'mysql', $conf);
				$host = V::get('host', '', $conf);
				$port = V::get('port', '', $conf);
				$user = V::get('user', '', $conf);
				$pass = V::get('pass', '', $conf);
				$zasob_id = V::get('zasob_id', '', $conf);

				$database = V::get('database', '', $conf);
				if ($port && $host) {
					$host .= ":{$port}";
				}
				$names = 'utf8';
				$db_class = 'Core_Database_' . ucfirst($type);
				Lib::loadClass($db_class);
				if (class_exists($db_class)) {

					$params = array();
					$tdsver = V::get('tdsver', '', $conf);
					if (!empty($tdsver)) {
						$params['tdsver'] = $tdsver;
					}
					if(!empty($zasob_id)) {
						$params['zasob_id'] = $zasob_id;
					}

					$_instance[$dbConfName] = new $db_class($host, $user, $pass, $database, $names, $params);
				}
			} else {
				trigger_error("Config file for db {$dbConfName} not exists!", E_USER_WARNING);
			}
		}
		return $_instance[$dbConfName];
	}

	public static function getStorage($db = null) {
		$pdo = self::getPDO($db);
		$type = $pdo->getType();
		switch ($type) {
			case 'mysql':
				Lib::loadClass('Core_Storage_Mysql');
				return new Core_Storage_Mysql($pdo);
			case 'pgsql':
				Lib::loadClass('Core_Storage_Pgsql');
				return new Core_Storage_Pgsql($pdo);
			default: throw new Exception("Storage for type '{$type}' not implemented");
		}
	}

	public static function getPDO($db = null) {
		static $_instance;
		if (!is_array($_instance)) $_instance = array();
		$zasob_id = '';
		$dbConfName = 'default_db';
		if (is_numeric($db) && $db > 0) {
			$zasob_id = $db;
			$dbConfName = "zasob_{$zasob_id}";
		} else if ($db == 'import_db') {
			$dbConfName = "import_db";
		} else if ($db == 'test_db') {
			$dbConfName = "test_db";
		}  else if ($db == 'billing_db') {
			$dbConfName = "billing_db";
		} else if (!$db || 'default_db' == $db) {
			$dbConfName = 'default_db';
		} else {
			throw new Exception("Not implemented database '{$db}'");
		}

		if (!array_key_exists($dbConfName, $_instance)) {
			$_instance[$dbConfName] = null;

			Lib::loadClass('Config');
			$conf = Config::getConfFile($dbConfName);
			if (!$conf) throw new Exception("Config file for db {$dbConfName} not exists!");
			$type = V::get('type', 'mysql', $conf);
			$host = V::get('host', '', $conf);
			$port = V::get('port', '', $conf);
			$user = V::get('user', '', $conf);
			$pass = V::get('pass', '', $conf);
			$database = V::get('database', '', $conf);
			$zasob_id = V::get('zasob_id', $zasob_id, $conf);
			$schema = V::get('schema', '', $conf);
			if (empty($host)) throw new Exception("Brak zdefiniowanego pola 'host' dla bazy danych '{$dbConfName}'");
			if (empty($user)) throw new Exception("Brak zdefiniowanego loginu usera dla bazy danych '{$dbConfName}'");
			if (empty($pass)) throw new Exception("Brak zdefiniowanego hasła usera dla bazy danych '{$dbConfName}'");
			if (empty($database)) throw new Exception("Brak zdefiniowane nazwy bazy danych dla '{$dbConfName}'");
			if (empty($zasob_id)) throw new Exception("Brak zdefiniowanego id zasobu dla bazy danych '{$dbConfName}' (po prostu dodaj definicje np. zasob_id=2 do ....default_db.conf....) ");

			$options = array();
			if ($port && $host) $host .= ";port={$port}";
			$names = 'utf8';
			$tdsver = V::get('tdsver', '', $conf);
			if (!empty($tdsver)) $options['tdsver'] = $tdsver;
			$options['zasob_id'] = $zasob_id;
			if (!empty($database)) $options['database'] = $database;
			if (!empty($schema)) $options['schema'] = $schema;
			//$pdo = new PDO($type . ':host=' . $host . ';dbname=' . $database, $user, $pass);
			//$pdo->exec("SET NAMES 'utf8'");
			//$sdb = new Core_Pdo($pdo);
			$options['type'] = $type;
			$sdb = new Core_Pdo($type . ':host=' . $host . ';dbname=' . $database, $user, $pass, $options);
			$sdb->exec("SET NAMES 'utf8'");
			$_instance[$dbConfName] = $sdb;
		}
		return $_instance[$dbConfName];
	}

	public static function identifierQuote($type, $identifier) { // $type: mysql | mssql | pgsql
		switch (strtolower($type)) {
			case 'pgsql': return self::pgsqlIdentifierQuote($identifier);
			case 'mysql': return "`{$identifier}`";
			default: throw new Exception("Not Implemented database type in identifierQuote '{$type}'");
		}
	}
	public static function pgsqlIdentifierQuote($identifier) {
		if (false !== strpos($identifier, '.')) {
			return implode('.', array_map(function ($token) {
				return "\"{$token}\"";
			}, explode('.', $identifier)));
		}
		return "\"{$identifier}\""; // https://www.postgresql.org/docs/9.1/sql-syntax-lexical.html
	}

	public static function makeValue($type, $value, $params = []) {
		return Core_TypeFactory::make($type, $value, $params);
	}


public static function connect() {
	static $conn;
	if (!is_resource($conn)) {
		$db = self::getDB();
		if (!$db) {
			die('Config file for main DB not exists!');
		}
		$conn = $db->getConnection();
	}
	return $conn;
}


public static function transaction_start() {
	DB::query(" START TRANSACTION; ");
}


public static function transaction_commit() {
	DB::query(" COMMIT; ");
}


public static function transaction_rollback() {
	DB::query(" ROLLBACK; ");
}


public static function query($sql, $die_on_error = true) {
	$conn = self::connect();
	$res = mysql_query($sql, $conn);
	if (!$res) {
		if ($die_on_error) {
			trigger_error("query error: #".mysql_errno($conn).": ".mysql_error($conn)."\n $sql", E_USER_ERROR);
			die("ERROR DB: QUERY ERROR");
		} else {
			echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">';print_r("query error: #".mysql_errno($conn).": ".mysql_error($conn)."\n $sql");echo'</pre>';
			$null = null;
			return $null;
		}
	} else {
		return $res;
	}
}


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


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


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


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


public static function _($str) {
	//PHP >= 4.3.0; dodaje lewe ukoﾶniki (backslash) do nast￪pujﾱcych znak￳w: \x00, \n, \r, \, ', " and \x1a
	return mysql_real_escape_string($str, self::connect());
}


public static function error() {
	$conn = self::connect();
	return "#".mysql_errno($conn).": ".mysql_error($conn);
}


// Pobiera liczb￪ wierszy przetworzonych w ostatnim zapytaniu INSERT, UPDATE, REPLACE lub DELETE skojarzonym z identyfikator_poﾳﾱczenia.
public static function affected_rows() {
	return mysql_affected_rows(self::connect());
}


// Zwraca ID wygenerowane dla pola z wﾳasnoﾶciﾱ AUTO_INCREMENT lub 0 jesli error
public static function insert_id() {
	return mysql_insert_id(self::connect());
}


// Zwraca liczb￪ wierszy w wyniku. T￪ funkcj￪ stosuje si￪ tylko do operacji SELECT.
public static function num_rows($res) {
	return mysql_num_rows($res);
}


public static function get_by_id($table, $id) {
	$null = null;
	$sql = "select p.*
		from `{$table}` as p
		where p.`ID`='{$id}'
	";
	$res = DB::query($sql);
	if ($r = DB::fetch($res)) {
		return $r;
	}
	return $null;
}


/**
 * @returns int
 *   1 - changed but without add hist
 *   2 - changed and add hist
 *   0 - nothing to change
 *   -1 - error ID not set
 *   -2 - error id not exists in DB
 *
 * TODO: sprawdzac czy w hist mozna odczytac aktualny stan, jesli nie to dodac caly rekord do HIST, jako 'procesy-fix-hist-data'
 */
public static function UPDATE_OBJ($table, &$sql_obj) {
	if (!isset($sql_obj->ID) || $sql_obj->ID <= 0) {
		return -1;
	}
	$id = $sql_obj->ID;

	// check id record $id exists
	if (($curr_obj = self::get_by_id($table, $sql_obj->ID)) == 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 == 'ID') 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');
	$sql_obj->A_RECORD_UPDATE_AUTHOR = User::getName();
	foreach (get_object_vars($sql_obj) as $k => $v) {
		$sql_arr [] = "`".$k."`=".(($v == 'NOW()')? $v : "'".self::_($v)."'");//"'".self::_($v)."'";
	}
	$sql = "update `".$table."` set ".implode(",", $sql_arr)." where `ID`='".$id."' limit 1; ";
	self::query( $sql );
	$ret = self::affected_rows();
	if ($ret) {
		$sql_obj->ID_USERS2 = $id;
		unset($sql_obj->ID);
		$new_id = self::ADD_NEW_OBJ($table . '_HIST', $sql_obj);
		if ($new_id) {
			$ret += 1;
		}
	}
	return $ret;
}


public static function ADD_NEW_OBJ($table, &$sql_obj) {
	$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) {
		$sql_arr ["`".$k."`"] = ($v == 'NOW()')? $v : "'".self::_($v)."'";
	}
	$sql = "insert into `".$table."` (".implode(",", array_keys($sql_arr)).") values (".implode(",", array_values($sql_arr))."); ";
	self::query($sql);
	$ret_id = self::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 = self::ADD_NEW_OBJ($table . '_HIST', $sql_obj);
		// error jesli nie udalo sie dodac rekordu do tabeli _HIST
	}
	return $ret_id;
}


}