se/SE/se-lib/DB.php

<?php

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

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

					$_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 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;
		}
	}//end foreach
	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)."'";
	}//end foreach
	$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)."'";
	}//end foreach
	$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;
}


}