path: root/it_dbi.class

<?php
/*
**	$Id$
**
**	Copyright (C) 1995-2007 by the ITools Authors.
**	This file is part of ITools - the Internet Tools Library
**
**	ITools 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 3 of the License, or
**	(at your option) any later version.
**
**	ITools 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, see <http://www.gnu.org/licenses/>.
**
**	dbi.class - UltraFlexible Database Interface 3000
*/

class it_dbi
{
	# Default configuration of dbi class
	var $_defaultconfig = array
	(
		'db' => null,
		'server' => "localhost",
		'server_update' => null,
		'user' => "itools",
		'pw' => "",
		'safety' => 1,		# 0= never die, 1=die if query invalid, 2=die also if no results
		#'keyfield' => 'ID',	# Don't set to null here, filled later by _get_field_info()
		'classprefix' => "",
		'getfieldinfo' => true, # do not read schema. only select() allowed
	);

	var $_key;	# Key of currently loaded record or null (public readonly)
	var $_fields;	# Array of name => array(Field,Type,Null,Key,Default,Extra,Length) of fields (public readonly)
	var $_isint;	# Array of name => bool if field is an INT field
	var $_link;	# DB link identifier (private)


/**
 * Constructor: Initialize the DBI interface
 * @param $p optional array(key => value) of configuration data
 * @param $query Optional initial query to run
 */
function it_dbi($p = array(), $query = null)
{
	# Shortcut: String config means use this table with default values
	if (!is_array($p))
		$p = array('table' => $p);

	if ($p['home'])
		$p['db'] = strtr(it::match('/www/([^/]*)', $p['home']), '.-', '__');

	# If the global singleton defaults are for this db/server/server_update then use them.
	$dp = (array)$GLOBALS['it_dbi']->_p;
	if ((!isset($p['db']) || ($p['db'] == $dp['db'])) && (!isset($p['server']) || ($p['server'] == $dp['server'])) && (!isset($p['server_update']) || ($p['server_update'] == $dp['server_update'])))
		$p += $dp;

	# Combine our settings with user's defaults and class defaults
	$p += (array)$GLOBALS['it_dbi_defaultconfig'] + array('db' => $GLOBALS['ULTRADB']) + $this->_defaultconfig;
	unset($this->_defaultconfig);	# to shorten ED() output

	$this->_p = $p;

	if ($p['table'])		# Standard use: create a table object
	{
		if (!isset($GLOBALS['it_dbi']))
			new it_dbi;

		if ($p['getfieldinfo'])
			$this->_p += $this->_get_field_info();	# Get $this->_fields and p[keyfield, autoincrement, randomid]

		if (is_array($query))
		{
			# Call with all arguments except first one
			$args = func_get_args();
			array_shift($args);
			call_user_func_array(array($this, "select"), $args);
		}
		elseif (isset($query))
			$this->read($query);
	}
	else
		$GLOBALS['it_dbi'] =& $this;
}

/**
 * function Tablename($query)
 * Constructor. Returns record object from table Tablename.
 * If $query is set, it encodes a SELECT to execute and store in the returned object, see select()
 * Note: Old second parameter $config has been deprecated and will be removed
 */ #:}

/**
 * Factory: Create classes of all database tables. Call statically.
 * @param $p array(key => value) of configuration data
 */
function createclasses($p = array())
{
	# Make sure singleton exists
	$dbi = $GLOBALS['it_dbi'] ? $GLOBALS['it_dbi'] : new it_dbi($p);

	$p += $dbi->_p;

	$dbid = "{$p['user']}@{$p['server']}:{$p['db']}";
	$state = it_dbi::_state_get($dbid);

	if (!$tables = $state['tables'])
	{
		for ($tables = array(), $res = $dbi->query('SHOW TABLES', $p); $row = mysql_fetch_row($res);)
			$tables[] = $row[0];

		$state['tables'] = $tables;
		it_dbi::_state_put($dbid, $state);
	}

	foreach ($tables as $table)
	{
		# Either create class in autoloader or manually just below
		if (!class_exists($p['classprefix'] . $table))
			it_dbi::createclass(array('table' => $table) + $p);
	}
}


/**
 * Convert table given by name into a class
 */
function createclass($p)
{
	# Shortcut: String config means use this table with default values
	if (!is_array($p))
		$p = array('table' => $p);

	# Make sure singleton exists
	$dbi = $GLOBALS['it_dbi'] ? $GLOBALS['it_dbi'] : new it_dbi(array('table' => null) + $p);
	$p += $dbi->_p;	# FIXME: (has to be checked for side effects!)
	$dbid = "{$p['user']}@{$p['server']}:{$p['db']}";

	if (!isset($dbi->_tables[$dbid]))
	{
		$state = it_dbi::_state_get($dbid);
		$dbi->_tables[$dbid] = array();

		if (!($tables = $state['tables']))
		{
			for ($tables = array(), $res = $dbi->query('SHOW TABLES', $p); $row = mysql_fetch_row($res);)
				$tables[] = $row[0];

			$state['tables'] = $tables;
			it_dbi::_state_put($dbid, $state);
		}

		foreach ($tables as $table)
			$dbi->_tables[$dbid][$table] = true;
	}

	if ($p['forcecreate'] || $dbi->_tables[$dbid][$p['table']])	# Do not generate classes for non-existant tables (can be overridden by forcecreate => true, used in tests/it_dbi.t)
	{
		$classname = $p['classprefix'] . $p['table'];

		if (substr($classname, 0, 4) != 'PMA_')	# It is designed behaviour that an error is generated if this class already exists!
		{
			$interface = function_exists("interface_exists") && interface_exists("Iterator", false) ? "implements Iterator" : "";
			$code = "class $classname extends it_dbi $interface
			{
				function $classname(/* \$query ... */)
				{
					\$args = func_get_args();
					\$query = array_shift(\$args);	# Preserve type (scalar/array) in single parameter case

					foreach (\$args as \$arg)
						\$query = array_merge((array)\$query, (array)\$arg);

					\$this->it_dbi(" . var_export($p, true) . ", \$query);
				}
			}";

			debug("it_dbi::createclass('{$p['table']}'): creating class $classname, dbid=$dbid", 5);
			eval($code);
		}
	}
}


/**
 * INTERNAL: Connect to mysql server and maintain a global link cache
 */
function _connect($p = array())
{
	$p += $this->_p;
	$dbid = "{$p['user']}@{$p['server']}:{$p['db']}";
	$state = it_dbi::_state_get($dbid);

	if ($p['reconnect'] || !($this->_link = $state['link']))
	{
		# Force new link if same server/user was seen before (mysql ignores selected db)
		if ($GLOBALS['it_dbi']->_connected["{$p['server']}/{$p['user']}"]++)
			$this->_link = @mysql_connect($p['server'], $p['user'], $p['pw'], true);
		else
			$this->_link = @mysql_connect($p['server'], $p['user'], $p['pw']);

		if (!$this->_link)
		{
			# One retry after a short delay
			it::log('sqllog', "it_dbi(): retrying DB link (mysql_connect {$p['server']}, {$p['db']}): " . mysql_error());
			sleep(1);
			$this->_link = @mysql_connect($p['server'], $p['user'], $p['pw'], true);
		}

		if (!$this->_link)
			$this->_fatal("_connect(): can't create DB link (mysql_connect {$p['user']}@{$p['server']}, {$p['db']})");

		if (!(@mysql_select_db($p['db'], $this->_link)))
			$this->_fatal("_connect(): can't select database \"{$p['db']}\"");

		$state['link'] = $this->_link;
		it_dbi::_state_put($dbid, $state);
	}
}


/**
 * INTERNAL: construct SQL SET clause of changed values from member vars and tags array.
 */
function _set($tags, $allfields = false)
{
	$r = array();
	foreach((array)$tags as $field => $value)
	{
		if (substr($field, 0, 1) == '-')		# Unquoted value (always added)
			$r[] = substr($field, 1)."=$value";
		else if ($allfields || ($value !== $this->_data[$field]))
			$r[] = "$field=".(isset($value) ? "'".mysql_real_escape_string($value, $this->_link)."'" : 'NULL');
	}

	return $r ? 'SET '.implode(', ', $r) : '';
}


/**
 * Create an SQL query (the stuff after 'WHERE') according to an array of selection criteria.
 *
 * Example:
 * $sql = $table->_where(array('Type' => 'bar',
 *    'Date >=' => '1999-01-01', '-Date <' => 'NOW()',
 *    'User NI' => 'chris'), 'ORDER BY Date');
 *
 * @param $params optional array of fieldname => value tupels. These are ANDed to form a WHERE clause.
 *   fieldname can contain an operator (separated by space), the default operator is '='.
 *   The special operator 'NI' specifies that the argument must be contained in a comma-separated list.
 * @param $link DB link used to escape values
 * @param $omit_where Do not add 'WHERE ' to result
 * @return The generated SQL clause
 * @see select()
 * @see iterate()
 */
function _where($params = "", $link = null, $omit_where = false)
{
	if (is_array($params) && (count($params) > 0))
	{
		$query = '';
		$stringquery = '';
		$sep = '';

		foreach($params as $field => $value)
		{
			if (is_int($field)) /* no key specified; just append */
			{
				$stringquery .= " $value";
			}
			else
			{
				$needs_where = true;

				if (!isset($value))
				{
					$op = 'IS';
					$qval = 'NULL';
				}
				else
				{
					if (preg_match('/^(\S+)\s+(\S.*)$/', $field, $regs))
					{
						$field = $regs[1];
						$op = strtoupper($regs[2]);
					}
					else
						$op = '=';

					# If field name starts with '-', the raw value is taken, no escaping is done and no quotes are put around it.
					if (substr($field, 0, 1) == '-')
					{
						$field = substr($field, 1);	# Strip that '-' sign
						$qval = $value;
					}
					else if (!is_array($value))
						$qval = "'" . ($link ? mysql_real_escape_string((string)$value, $link) : mysql_real_escape_string((string)$value)) . "'";
				}

				switch ($op)
				{
					case 'NI':
						$query .= $sep."CONCAT(',',$field,',') LIKE '%,$value,%'";
						break;

					case 'IN':
					case 'NOT IN':
						if (is_array($value))
						{
							if ($value)
							{
								$qvals = array();

								foreach ($value as $val)
									$qvals[] = $link ? mysql_real_escape_string($val, $link) : mysql_real_escape_string($val);

								$query .= "$sep$field $op ('" . join("','", $qvals) . "')";	 # null is mapped to ''
							}
							else
								$query .= $sep . (($op == 'IN') ? "0" : "1");

							break;
						}
						/* FALLTHROUGH */

					default:
						if (isset($qval))
							$query .= "$sep$field $op $qval";
						else
							it::fatal('Undefined $qval when constructing query due to invalid $value (array)');
						break;
				}
				$sep = ' AND ';
			}
		}

		$query .= $stringquery;

		if ($needs_where && !$omit_where)
			$query = "WHERE $query";
	}

	return $query;
}


/**
 * Internal: Output class name::error message and terminate execution.
 */
function _fatal($text)
{
	$text = get_class($this).'::'.$text;

	if ($this->_link && ($errstr = mysql_error($this->_link)))
		$text = "\"$errstr\" in $text [errno " . mysql_errno($this->_link) . "]";

	if ($this->_link && ($res = @mysql_fetch_row(mysql_query('select database()', $this->_link)))) # dont create extra errs
		$text .= ", DB: " . $res[0];

	it::fatal($text . ", Server: " . $this->_p['server']);
	/* NOT REACHED */
}


/**
 * Post-process data after reading a record.
 * This is a stub-function that can be overloaded.
 */
function _read_post_process()
{
}


/**
 * Pre-process data before writing a record.
 * This is a stub-function that can be overloaded.
 * @param $tags Reference to update/create tags, can be modified as needed
 */
function _write_pre_process(/* &$tags */)
{
}


/**
 * Clear record
 */
function clear($pp = true)
{
	foreach ((array)$this->_fields as $field => $dummy)
		unset($this->$field);
	unset($this->_data);
	unset($this->_key);
	$pp && $this->_read_post_process();
}


/**
 * Semi-internal: send a raw SQL query and return mysql result value
 * @param $query complete SQL query string
 * @return raw MySQL result. May die if query fails and safety is big enough
 */
function query($query, $p = array())
{
	$p += $this->_p;
	$start = gettimeofday();

	if ($p['server_update'] && !preg_match('/^(EXPLAIN|SELECT|SHOW) /i', $query))
	{
		debug("switching to update server \"{$p['server_update']}\"", 5);
		$this->_p['server'] = $p['server'] = $p['server_update'];
		unset($this->_p['server_update'], $p['server_update'], $this->_link);
	}

	$this->_connect($p);	# must be called after update server switching code

	debug("{$p['user']}@{$p['server']}:{$p['db']}" . '.' . get_class($this) . "::query(\"$query\")", 4);

	if (!($result = mysql_query($query, $this->_link)) && $p['safety'])
	{
		$errno = mysql_errno($this->_link);
		if (($p['safety'] < 2) && ($errno == 1062)) # Duplicate entry
			return false;

		if ($errno == 2006) # mysql server has gone away: retry
		{
			it::log('sqllog', "it_dbi(): reconnecting mysql_connect {$p['server']}, {$p['db']}");
			$this->_connect(array('reconnect' => true));
			$result = mysql_query($query, $this->_link);
		}

		if (!$result)
			$this->_fatal("query(\"$query\") failed");
	}
	else if (preg_match('/^(CREATE|ALTER|DROP) /i', $query))
	{
		# Purge cache for schema changes (after modifying table)
		$dbid = "{$p['user']}@{$p['server']}:{$p['db']}";
		it_dbi::_state_purgeshared($dbid);
	}


	if ($GLOBALS['debug_sqllog'])
	{
		$end = gettimeofday();
		$msec = round(($end['sec'] - $start['sec']) * 1000 + ($end['usec'] - $start['usec']) / 1000);
		$backtrace = EDC('sqltrace') ? it_debug::backtrace(1) : null;
		it::log('sqllog', rtrim("$msec\t$query\t$backtrace"));

		$this->_sqllog[] = array(
			'time' => $msec,
			'query' => $query,
		) + ($backtrace ? array('backtrace' => $backtrace) : array());
	}

	return $result;
}


/**
 * Read a single record by primary key, not destroying old query result $this->_result
 * @param $id primary key. If null, record is cleared
 * @return True if record could be read, false if not.
 */
function read($id=null)
{
	$old_result = $this->_result;
	$old_nofetch = $this->_nofetch;
	$result = $this->select(array($this->_p['keyfield'] => $id));
	$this->_result = $old_result;
	$this->_nofetch = $old_nofetch;
	return $result;
}


/**
 * Select a set of records from table and fetch the first one
 * @param $query One or more optional arrays of (field => value) or sqlstring pairs. Defaults to null (select all records)
 *           Fields will be joined by AND
 *           Fields can contain a compare operator: 'name LIKE' => "j%" or 'amount >' => 100
 *           Fields can start with - to prevent quoting of right side: '-modified' => "CURDATE()"
 *           $query can contain magic field 'SELECT' for things like 'COUNT(*)' or 'DISTINCT foo', defaults to '*'
 *           $query can contain magic field 'JOIN' for things like 'tableA LEFT JOIN tableB ON a=b', defaults to table name
 *           $query can contain 'CALC_FOUND_ROWS', if true member var _found_rows contains number of matching rows before LIMIT
 *           $query can contain 'NOFETCH', if true the first row is not prefetched (e.g. when directly using _result)
 * @return Number of matching rows, use iterate() to fetch the next record
 * @see iterate()
 * @see _where()
 */
function select(/* $query = array|string, ... */)
{
	$query = array();
	foreach (func_get_args() as $arg)
		$query = array_merge($query, (array)$arg);

	$this->_connect();

	$result = 0;
	$calc_found_rows = false;

	$what = '*';
	if (isset($query['SELECT']))
	{
		$what = $query['SELECT'];
		unset($query['SELECT']);
	}

	$join = $this->_p['table'];
	if (isset($query['JOIN'])) # WARNING: this field gets abused for "tablename USE INDEX (fast2)
	{
		$join = $query['JOIN'];
		unset($query['JOIN']);
	}

	unset($this->_found_rows);
	if (isset($query['CALC_FOUND_ROWS']) && $query['CALC_FOUND_ROWS'])
	{
		$calc_found_rows = true;
		$what = 'SQL_CALC_FOUND_ROWS '.$what;
	}
	unset($query['CALC_FOUND_ROWS']);	# Always unset, so CALC_FOUND_ROWS => false doesn't generate bogus query

	$nofetch = $this->_nofetch = $query['NOFETCH'];
	unset($query['NOFETCH']);

	$this->clear(false);
	if ($this->_result = $this->query($sql = "SELECT $what FROM $join " . $this->_where($query, $this->_link)))
	{
		$result = mysql_num_rows($this->_result);

		if ($calc_found_rows)
		{
			list($count) = mysql_fetch_row($this->query('SELECT FOUND_ROWS()'));
			$this->_found_rows = intval($count);
		}

		if (!$this->iterate() && ($this->_p['safety'] >= 2))
			$this->_fatal("select(): query produced no results: \"$sql\"");
	}

	$this->_nofetch = !$nofetch;

	return $result;
}


/**
 * Puts next result from previous select() in member variables
 * @return true if another record was fetched, false if no more records
 * @see select()
 */
function iterate()
{
	if (!$this->_nofetch)
	{
		if ($this->_data = mysql_fetch_assoc($this->_result))
		{
			foreach ($this->_data as $field => $value)
				$this->$field = isset($value) && $this->_isint[$field] ? ($this->_data[$field] = intval($value)) : $value;

			if (!empty($this->_p['keyfield']))
				$this->_key = $this->_data[$this->_p['keyfield']];
		}
		else
			$this->clear(false);

		$this->_read_post_process();
	}
	else
		$this->_nofetch = false;

	return (bool)$this->_data;
}


/**
 * Insert a record into table. Values are taken from member vars and $tags.
 * After inserting, all values are valid (record is read back).
 * Does not destroy internal state of last select() call
 * @param $tags Additional key => value pairs (these have priority over member vars)
 */
function insert($tags = array(), $command = "INSERT")
{
	$this->_connect();

	/* Pre-processing, $tags is passed by reference and may be modified here */
	$this->_write_pre_process($tags);

	if ($this->_p['randomid'] && !isset($tags[$this->_p['keyfield']]))
		$tags[$this->_p['keyfield']] = md5(uniqid(mt_rand()));

	$set = $this->_set($tags, true);

	if ($result = $this->query("$command INTO {$this->_p['table']} " . $set))
	{
		$id = ($this->_p['autoincrement'] && !isset($tags[$this->_p['keyfield']])) ? mysql_insert_id($this->_link) : $tags[$this->_p['keyfield']];
		if (!$this->read($id) && $this->_p['safety'])
			$this->_fatal("insert(): can't read record back (key truncated?), id=\"$id\"");
	}
	else
		$this->clear(false);

	return $result;
}


/**
 * Replace a record in a table
 * @param $tags Additional key => value pairs (these have priority over member vars)
 * Does not destroy internal state of last select() call
 * @see insert()
 */
function replace($tags = array())
{
	return $this->insert($tags, "REPLACE");
}


/**
 * Update a record in a table
 * @param $tags Additional key => value pairs (these have priority over member vars)
 * Does not destroy internal state of last select() call
 */
function update($tags = array(), $query = null)
{
	$this->_connect();
	$result = true;

	/* Pre-processing, $tags is passed by reference and may be modified here */
	$this->_write_pre_process($tags);

	if (!$query && !empty($this->_p['keyfield']))
		$query = array($this->_p['keyfield'] => $this->_data[$this->_p['keyfield']]);

	if ($set = $this->_set($tags))
	{
		if ($result = $this->query("UPDATE {$this->_p['table']} $set " . $this->_where($query, $this->_link)))
		{
			if (array_key_exists($this->_p['keyfield'], $tags))	# Did we just update the key?
				$this->_key = $tags[$this->_p['keyfield']];

			if (isset($this->_key) && $this->read($this->_key))
				$this->_nofetch = false; # So we can do while(iterate()) update();
		}
	}

	return $result;
}


/**
 * Delete a record
 * Does not destroy query result $this->_result
 * @param $query optional query for WHERE, default: delete currently loaded record
 * @return number of deleted records
 */
function delete($query = null)
{
	$this->_connect();
	$result = 0;

	if (!$query && $this->_p['keyfield'])
	{
		$query = array($this->_p['keyfield'] => $this->_key);
		$this->clear();
	}

	if ($query && $this->query("DELETE FROM {$this->_p['table']} " . $this->_where($query, $this->_link)))
		$result = mysql_affected_rows($this->_link);

	return $result;
}


/**
 * INTERNAL: Store information about a table's fields in $this->_fields, possibly from cache.
 * @return array(keyfield, autoincrement, randomid)
 */
function _get_field_info()
{
	$result = array();
	$dbid = "{$this->_p['user']}@{$this->_p['server']}:{$this->_p['db']}";
	$state = it_dbi::_state_get($dbid);

	if (!($this->_fields = $state['fields'][$this->_p['table']]))
	{
		debug("it_dbi(): no fields for {$dbid}.{$this->_p['table']}, calculating.", 5);
		for ($res = $this->query('SHOW COLUMNS FROM ' . $this->_p['table']); $res && ($field = mysql_fetch_assoc($res)); )
		{
			$this->_fields[$field['Field']] = $field + array('Length' => preg_match('/date|time/', $field['Type']) ? 20 : intval(it::match('\d+', $field['Type'])));
			$this->_isint[$field['Field']] = $field['Type'] == "int(11)";
		}

		$state['fields'][$this->_p['table']] = $this->_fields;
		$state['isint'][$this->_p['table']] = $this->_isint;
		it_dbi::_state_put($dbid, $state);
	}
	else	# Existing _fields, copy other info too
		$this->_isint = $state['isint'][$this->_p['table']];

	foreach((array)$this->_fields as $field)
	{
		if ($field['Key'] == 'PRI')
		{
			$result = array('keyfield' => $field['Field'], 'autoincrement' => (strpos($field['Extra'], "auto_increment") !== false),
					'randomid' => (strpos($field['Type'], "char") !== false));
			break;
		}
	}

	unset($GLOBALS['it_dbi']->_p['table'], $GLOBALS['it_dbi']->_p['keyfield']);	# Remove cruft
	return $result;
}

function _state_get($dbid)
{
	if (!($result = $GLOBALS['it_dbi']->_state[$dbid]))
		$result = $GLOBALS['it_dbi']->_state[$dbid] = (array)it_cache::get("dbi:$dbid");

	#var_dump("get", $dbid, $result);
	return $result;
}

function _state_put($dbid, $state)
{
	#var_dump("put", $dbid, $state);
	$GLOBALS['it_dbi']->_state[$dbid] = $state;
	it_cache::put("dbi:$dbid", array('link' => null) + (array)$state);	# link is not transferable
}

function _state_purgeshared($dbid)
{
	#var_dump("purgeshared", $dbid);
	it_cache::put("dbi:$dbid", array());	# Nuke shared cache
}

#
# Implement PHP 5 Iterator interface to make foreach work
# Example: foreach (new T_User('firstname' => "foo") as $foouser) { ... }
#
function current()
{
	return $this;
}

function key()
{
	return $this->_key;
}

function next()
{
	$this->iterate();
}

function rewind()
{
	if (!$this->_result)	# Object without query used in foreach
		$this->select();

	# Only rewind if not already at start and results present
	if (!$this->_nofetch && mysql_num_rows($this->_result))
		mysql_data_seek($this->_result, 0);

	$this->iterate();
}

function valid()
{
	return (bool)$this->_data;
}

} /* End class it_dbi */

?>