<?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)) . '"';
}
}
}
?>