Location: PHPKode > projects > Modularized Information Environment > mie/includes/db.inc
<?php
/**
 * @file includes/db.inc
 * @brief Database Connection Library
 * @author Kenneth Smith <hide@address.com>
 *
 * Modularized Information Environment (MIE)
 * Copyright (C) 2005-2006 by Kenneth Smith. All rights reserved.
 *
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software
 * Foundation; either version 2 of the License, or (at your option) any later
 * version.
 *
 * 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, write to the Free Software Foundation, Inc., 59 Temple
 * Place - Suite 330, Boston, MA 02111-1307, USA.
 */

defined('VALIE_MIE') || die(_('Direct access not allowed'));
defined('DB_INC') && exit;
define('DB_INC', true);

class DB {

	/// Parsed database url
	public $base;
	/// Connection options
	public $option;
	/// Connection handle
	public $link;
	/// Text of last query
	public $last;
	/// Handle of last result
	public $result;
	/// Rewrite args
	private $rw_args;

	/// Construct object
	function __construct($_url, $_option = null) {
		$this->base = parse_url($_url);
		switch($this->base['scheme']) {
			default:
				trigger_error(_('Unsupported database scheme: ') . $this->base['scheme'], E_USER_ERROR);
			break; case 'sqlite':
			break; case 'mysql':
				if(isset($this->base['path'])) {
					$this->base['schema'] = basename($this->base['path']);
				}
			break; case 'postgresql':
				if(isset($this->base['path'])) {
					$this->base['schema'] = basename($this->base['path']);
				}
		}
		$this->option = $_option;
		$this->link = null;
	}

	/// Destroy object
	function __destruct() {
		$this->close();
	}

	/// Open connection
	function open() {
		if($this->link) { // already open
			return $this->link;
		}
		switch($this->base['scheme']) {
			case 'sqlite':
				$this->link = sqlite_open($this->base['path']);
				if($this->link == false) { 
					trigger_error(_('Unable to connect to database server'), E_USER_ERROR);
				}
			break; case 'mysql':
				$host = isset($this->base['port']) ? $this->base['host'] . ':' .$this->base['port'] : $this->base['host'];
				$this->link = mysql_connect($host, $this->base['user'], $this->base['pass'], true);
				if($this->link == false) { 
					trigger_error(_('Unable to connect to database server'), E_USER_ERROR);
				}
				if(isset($this->base['schema'])) {
					if(mysql_select_db($this->base['schema'], $this->link) == false) {
						trigger_error(_('Unable to select schema: ') . $this->base['schema'], E_USER_ERROR);
					}
				}
			break; case 'postgresql':
				$str = '';
				if(@$this->base['host']) {
					$str .= "host={$this->base['host']} ";
				}
				if(@$this->base['port']) {
					$str .= "port={$this->base['port']} ";
				}
				if(@$this->base['schema']) {
					$str .= "dbname={$this->base['schema']} ";
				}
				if(@$this->base['user']) {
					$str .= "user={$this->base['user']} ";
				}
				if(@$this->base['pass']) {
					$str .= "password={$this->base['pass']} ";
				}
				if(@$this->base['scheme'] == 'postgresqls') {
					$str .= "sslmode=require ";
				}
				$this->link = pg_connect($str);
				if($this->link == false) { 
					trigger_error(_('Unable to connect to database server'), E_USER_ERROR);
				}
			break;
		}
		$this->last = $this->result = null;
		return $this->link;
	}

	/// Close connection
	function close() {
		if(empty($this->link)) {
			return; // already closed
		}
		$this->free();
		switch($this->base['scheme']) {
			case 'sqlite':
				$ret = sqlite_close($this->link);
			break; case 'mysql':
				$ret = mysql_close($this->link);
			break; case 'postgresql':
				$ret = pg_close($this->link);
		}
		$this->link = null;
		return $ret;
	}

	/// Free last result
	function free() {
		if(empty($this->result) || $this->result === true) {
			return; // already free
		}
		switch($this->base['scheme']) {
			case 'sqlite':
				$ret = true;
			break; case 'mysql':
				$ret = @mysql_free_result($this->result);
			break; case 'postgresql':
				$ret = @pg_free_result($this->result);
		}
		$this->result = $this->last = null;
		return $ret;
	}

	/// Select database
	function select($_name) {
		$this->open();
		switch($this->base['scheme']) {
			case 'sqlite':
				$ret = false;
			break; case 'mysql':
				$ret = mysql_select_db($_name, $this->link);
			break; case 'postgresql':
				$ret = false;
		}
		return $ret;
	}

	/// Query database
	function query($_string) {
		$this->open();
		$this->free();
		switch($this->base['scheme']) {
			case 'sqlite':
				$this->result = sqlite_query($this->link, $this->last = $_string);
			break; case 'mysql':
				$this->result = mysql_query($this->last = $_string, $this->link);
			break; case 'postgresql':
				$this->result = pg_query($this->link, $this->last = $_string);
		}
		return $this->result;
	}

	/// Escape string
	function escape($_string) {
		$this->open();
		switch($this->base['scheme']) {
			case 'sqlite':
				return sqlite_escape_string($_string);
			break; case 'mysql':
				return mysql_real_escape_string($_string, $this->link);
			break; case 'postgresql':
				return pg_escape_string($_string);
		}
	}

	/// Row count
	function count($_string = null) {
		if($_string) {
			$this->query($_string);
		}
		switch($this->base['scheme']) {
			case 'sqlite':
				return ($this->result) ? sqlite_num_rows($this->result) : 0;
			break; case 'mysql':
				return ($this->result) ? mysql_num_rows($this->result) : 0;
			break; case 'postgresql':
				return ($this->result) ? pg_num_rows($this->result) : 0;
		}
	}

	/// Seek row
	function seek($_row) {
		switch($this->base['scheme']) {
			case 'sqlite':
				return ($this->result) ? sqlite_seek($this->result, $_row) : false;
			break; case 'mysql':
				return ($this->result) ? mysql_data_seek($this->result, $_row) : false;
			break; case 'postgresql':
				return ($this->result) ? pg_result_seek($this->result, $_row) : false;
		}
	}

	/// Reset results
	function reset() {
		switch($this->base['scheme']) {
			case 'sqlite':
				return ($this->result) ? sqlite_rewind($this->result) : false;
			break; case 'mysql':
				return ($this->result) ? mysql_data_seek($this->result, 0) : false;
			break; case 'postgresql':
				return ($this->result) ? pg_result_seek($this->result, 0) : false;
		}
	}

	/// Result row
	function result($_string = null, $_type = null) {
		if($_string) {
			$this->query($_string);
		}
		switch($this->base['scheme']) {
			case 'sqlite':
				switch ($_type) {
					default: case 'assoc':
						return @sqlite_fetch_array($this->result, SQLITE_ASSOC);
					break; case 'num':
						return @sqlite_fetch_array($this->result, SQLITE_NUM);
					break; case 'both':
						return @sqlite_fetch_array($this->result, SQLITE_BOTH);
					break;
				}
			break; case 'mysql':
				switch($_type) {
					default: case 'assoc':
						return @mysql_fetch_array($this->result, MYSQL_ASSOC);
					break; case 'num':
						return @mysql_fetch_array($this->result, MYSQL_NUM);
					break; case 'both':
						return @mysql_fetch_array($this->result, MYSQL_BOTH);
				}
			break; case 'postgresql':
				switch ($_type) {
					default: case 'assoc':
						return @pg_fetch_array($this->result, null, PGSQL_ASSOC);
					break; case 'num':
						return @pg_fetch_array($this->result, null, PGSQL_NUM);
					break; case 'both':
						return @pg_fetch_array($this->result, null, PGSQL_BOTH);
				}
		}
	}

	/// Result table
	function table($_string = null, $_type = null) {
		$table = array();
		if($_string) {
			$this->query($_string);
		}
		else {
			$this->reset(); // get whole table
		}
		switch($this->base['scheme']) {
			case 'sqlite':
				switch ($_type) {
					default: case 'assoc':
						while($row = @sqlite_fetch_array($this->result, SQLITE_ASSOC)) {
							$table[] = $row;
						}
					break; case 'num':
						while($row = @sqlite_fetch_array($this->result, SQLITE_NUM)) {
							$table[] = $row;
						}
					break; case 'both':
						while($row = @sqlite_fetch_array($this->result, SQLITE_BOTH)) {
							$table[] = $row;
						}
				}
			break; case 'mysql':
				switch ($_type) {
					default: case 'assoc':
						while($row = @mysql_fetch_array($this->result, MYSQL_ASSOC)) {
							$table[] = $row;
						}
					break; case 'num':
						while($row = @mysql_fetch_array($this->result, MYSQL_NUM)) {
							$table[] = $row;
						}
					break; case 'both':
						while($row = @mysql_fetch_array($this->result, MYSQL_BOTH)) {
							$table[] = $row;
						}
				}
			break; case 'postgresql':
				switch ($_type) {
					default: case 'assoc':
						while($row = @pg_fetch_array($this->result, null, PGSQL_ASSOC)) {
							$table[] = $row;
						}
					break; case 'num':
						while($row = @pg_fetch_array($this->result, null, PGSQL_NUM)) {
							$table[] = $row;
						}
					break; case 'both':
						while($row = @pg_fetch_array($this->result, null, PGSQL_BOTH)) {
							$table[] = $row;
						}
				}
		}
		return $table;
	}

	/**
	 * @brief Rewrite SQL
	 * @note
	 * %i = integer,
	 * %f = float,
	 * %c = characters,
	 * %t = time,
	 * %d = date,
	 * %s = stamp,
	 * k{} = key,
	 * t{} = table,
	 * s{} = schema
	 */
	function rewrite($_sql) {
		$this->rw_args = func_get_args();
		array_shift($this->rw_args);
		if(isset($this->rw_args[0]) && is_array($this->rw_args[0])) {
			$this->rw_args = $this->rw_args[0];
		}
		$this->open();
		switch($this->base['scheme']) {
			case 'sqlite':
				return preg_replace_callback('/%[ifctds]|[kts]\{[A-Za-z0-9_]*\}/', array($this, 'sqlite_rewrite'), $_sql);
			break; case 'mysql':
				return preg_replace_callback('/%[ifctds]|[kts]\{[A-Za-z0-9_]*\}/', array($this, 'mysql_rewrite'), $_sql);
			break; case 'postgresql':
				return preg_replace_callback('/%[ifctds]|[kts]\{[A-Za-z0-9_]*\}/', array($this, 'postgresql_rewrite'), $_sql);
		}
	}

	/// Sqlite callback method for rewrite
	private function sqlite_rewrite($_match) {
		switch(substr($_match[0], 0, 2)) {
			default:
				return '';
			break; case '%i':
				return intval(array_shift($this->rw_args));
			break; case '%f':
				return floatval(array_shift($this->rw_args));
			break; case '%c':
				return "'" . sqlite_escape_string(array_shift($this->rw_args)) . "'";
			break; case '%t':
				return date("'H:i:s'", array_shift($this->rw_args));
			break; case '%d':
				return date("'Y-m-d'", array_shift($this->rw_args));
			break; case '%s':
				return date("'Y-m-d H:i:s'", array_shift($this->rw_args));
			break; case 'k{':
				return '"' . sqlite_escape_string(substr($_match[0], 2, strlen($_match[0]) - 3)) . '"';
			break; case 't{':
				return '"' . sqlite_escape_string((isset($this->option['prefix']) ? $this->option['prefix'] : '') . substr($_match[0], 2, strlen($_match[0]) - 3)) . '"';
			break; case 's{':
				return '"' . sqlite_escape_string(substr($_match[0], 2, strlen($_match[0]) - 3)) . '"';
		}
	}

	/// Mysql callback method for rewrite
	private function mysql_rewrite($_match) {
		switch(substr($_match[0], 0, 2)) {
			default:
				return '';
			break; case '%i':
				return intval(array_shift($this->rw_args));
			break; case '%f':
				return floatval(array_shift($this->rw_args));
			break; case '%c':
				return '"' . mysql_real_escape_string(array_shift($this->rw_args), $this->link) . '"';
			break; case '%t':
				return date('"H:i:s"', array_shift($this->rw_args));
			break; case '%d':
				return date('"Y-m-d"', array_shift($this->rw_args));
			break; case '%s':
				return date('"Y-m-d H:i:s"', array_shift($this->rw_args));
			break; case 'k{':
				return '`' . mysql_real_escape_string(substr($_match[0], 2, strlen($_match[0]) - 3), $this->link) . '`';
			break; case 't{':
				return '`' . mysql_real_escape_string((isset($this->option['prefix']) ? $this->option['prefix'] : '') . substr($_match[0], 2, strlen($_match[0]) - 3), $this->link) . '`';
			break; case 's{':
				return '`' . mysql_real_escape_string(substr($_match[0], 2, strlen($_match[0]) - 3), $this->link) . '`';
		}
	}

	/// Postgresql callback method for rewrite
	private function postgresql_rewrite($_match) {
		switch(substr($_match[0], 0, 2)) {
			default:
				return '';
			break; case '%i':
				return intval(array_shift($this->rw_args));
			break; case '%f':
				return floatval(array_shift($this->rw_args));
			break; case '%c':
				return "'" . pg_escape_string(array_shift($this->rw_args)) . "'";
			break; case '%t':
				return date("'H:i:s'", array_shift($this->rw_args));
			break; case '%d':
				return date("'Y-m-d'", array_shift($this->rw_args));
			break; case '%s':
				return date("'Y-m-d H:i:s'", array_shift($this->rw_args));
			break; case 'k{':
				return '"' . pg_escape_string(substr($_match[0], 2, strlen($_match[0]) - 3)) . '"';
			break; case 't{':
				return '"' . pg_escape_string((isset($this->option['prefix']) ? $this->option['prefix'] : '') . substr($_match[0], 2, strlen($_match[0]) - 3)) . '"';
			break; case 's{':
				return '"' . pg_escape_string(substr($_match[0], 2, strlen($_match[0]) - 3)) . '"';
		}
	}
}

?>
Return current item: Modularized Information Environment