Populated release branch

1 files changed, 651 insertions, 0 deletions

diff --git a/it_dbi.class b/it_dbi.class
new file mode 100644
index 0000000..4e855e4
--- /dev/null
+++ b/it_dbi.class
@@ -0,0 +1,651 @@
+<?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' => "",
+		'persistent' => false,
+		'sqllog' => false,
+		'sqltrace' => false,
+	);
+
+	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 $_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('sqllog' => EDC('sqllog') || EDC('sqltrace'), 'sqltrace' => EDC('sqltrace'), 'db' => $GLOBALS['ULTRADB']) + $this->_defaultconfig;
+	unset($this->_defaultconfig);	# to shorten ED() output
+
+	$p['dbid'] = "{$p['user']}@{$p['server']}:{$p['db']}";
+	$this->_p = $p;
+
+	if ($p['table'])		# Standard use: create a table object
+	{
+		if (!isset($GLOBALS['it_dbi']))
+			new it_dbi;
+
+		$this->_p += $this->_get_field_info();	# Get $this->_fields and p[keyfield, autoincrement, randomid]
+
+		if (is_array($query))
+			$this->select($query);
+		elseif (isset($query))
+			$this->read($query);
+	}
+	else
+		$GLOBALS['it_dbi'] =& $this;
+}
+
+/**
+ * function Tablename($query, $config)
+ * 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()
+ * If $config is set, it contains extra parameters for the query like how to handle errors
+ */ #}
+
+/**
+ * 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;
+	$p['dbid'] = "{$p['user']}@{$p['server']}:{$p['db']}";
+	$dbi->_connect($p);
+
+	if (!($tables = mysql_list_tables($p['db'], $dbi->_link)))
+		$dbi->_fatal("it_dbi::createclasses(): can't list on tables \"{$p['db']}\"");
+
+	for ($i = 0; $i < mysql_num_rows($tables); $i++)
+		it_dbi::createclass(array('table' => mysql_tablename($tables, $i)) + $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);
+	$dbid = "{$dbi->_p['user']}@{$dbi->_p['server']}:{$dbi->_p['db']}";
+
+	if (!isset($dbi->_tables[$dbid]))
+	{
+		for ($res = $dbi->query('SHOW TABLES'); $row = mysql_fetch_row($res);)
+			$dbi->_tables[$dbid][] = $row[0];
+	}
+
+	if (in_array($p['table'], $dbi->_tables[$dbid]))	# Do not generate classes for non-existant tables
+	{
+		$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!
+		{
+			$code = "class $classname extends it_dbi
+			{
+				function $classname(\$query = null, \$p = array())
+				{
+					\$p += " . var_export($p, true) . ";
+					\$this->it_dbi(\$p, \$query);
+				}
+			}";
+
+			debug("it_dbi::createclass('{$p['table']}'): creating class $classname", 5);
+			eval($code);
+		}
+	}
+}
+
+
+/**
+ * INTERNAL: Connect to mysql server and maintain a global link cache
+ */
+function _connect($p = array())
+{
+	$p += $this->_p;
+	if (!($this->_link = $GLOBALS['it_dbi']->_state[$p['dbid']]['link']))
+	{
+		# Use persistent connections but prevent reuse if only DB differs
+		if (!$p['persistent'] || $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 (always non-persistent)
+			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("it_dbi(): can't create DB link (mysql_connect {$p['user']}@{$p['server']}, {$p['db']})");
+
+		if (!(@mysql_select_db($p['db'], $this->_link)))
+			$this->_fatal("it_dbi(): can't select database \"{$p['db']}\"");
+
+		$GLOBALS['it_dbi']->_state[$p['dbid']]['link'] = $this->_link;
+	}
+}
+
+
+/**
+ * INTERNAL: construct SQL SET clause of changed values from member vars and tags array.
+ * Merge current values into $tags. Modifies caller's array (callers rely on it)!
+ */
+function _set(&$tags)
+{
+	# DEPRECATED BEHAVIOUR: Add member vars to tags, considering unquoted fields
+	foreach (get_object_vars($this) as $field => $value)
+		# Don't use isset($tags[$field]) (would not handle null values correctly)
+		if (isset($this->_fields[$field]) && !array_key_exists('-'.$field, $tags) && !array_key_exists($field, $tags))
+		{
+				$tags[$field] = $value;
+				if ($this->_data && ($value != $this->_data[$field]))
+					it::error("it_dbi::_set() would take value '$value' from this->$field:" . D($this, $tags));
+		}
+
+	# Create SQL
+	$r = array();
+	foreach((array)$tags as $key => $value)
+	{
+		if (substr($key, 0, 1) == '-')		# Unquoted value (always added)
+			$r[] = substr($key, 1)."=$value";
+		elseif (!isset($this->_data) || ($value !== $this->_data[$key]))	# Add to SQL if value has changed
+			$r[] = "$key=".(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(), iterate()
+ */
+function _where($params = "", $link = null, $omit_where = false)
+{
+	if (is_array($params) && (count($params) > 0))
+	{
+		$query = '';
+		$sep = '';
+
+		foreach($params as $field => $value)
+		{
+			if (is_int($field)) /* no key specified; just append */
+			{
+				if ($field === $value)	/* ignore array(1 => 1) et al */
+					continue;
+
+				$query .= " $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 . "0";
+
+							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 ';
+			}
+		}
+
+		if ($needs_where && !$omit_where)
+			$query = "WHERE $query";
+	}
+
+	return $query;
+}
+
+
+/**
+ * Internal: Output class name::error message and terminate execution.
+ */
+function _fatal($text)
+{
+	if ($this->_link && ($t = mysql_error($this->_link)))
+		$mysql_error = ", mysql_error=$t";
+
+	it::fatal(get_class($this).'::'.$text . $mysql_error);
+	/* 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 $key => $dummy)
+		unset($this->$key);
+	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)
+{
+	$start = gettimeofday();
+
+	if ($this->_p['server_update'] && !preg_match('/^(EXPLAIN|SELECT|SHOW) /i', $query))
+	{
+		debug("switching to update server \"{$this->_p['server_update']}\"", 5);
+		$this->_p['server'] = $this->_p['server_update'];
+		$this->_p['dbid'] = "{$this->_p['user']}@{$this->_p['server']}:{$this->_p['db']}";
+		unset($this->_p['server_update'], $this->_link);
+	}
+
+	$this->_connect();	# must be called after update server switching code
+
+	debug($this->_p['dbid'] . '.' . get_class($this) . "::query(\"$query\")", 4);
+
+	if (!($result = mysql_query($query, $this->_link)) && $this->_p['safety'])
+	{
+		if (($this->_p['safety'] < 2) && (mysql_errno($this->_link) == 1062)) # Duplicate entry
+			return false;
+
+		$error = mysql_errno($this->_link).' ('.mysql_error($this->_link).')';
+		$res = @mysql_fetch_row(mysql_query('select database()', $this->_link)); # dont create extra errs
+		$this->_fatal("query(\"$query\") on {$res[0]} failed: $error");
+	}
+
+
+	if ($this->_p['sqllog'])
+	{
+		$end = gettimeofday();
+		$msec = round(($end['sec'] - $start['sec']) * 1000 + ($end['usec'] - $start['usec']) / 1000);
+		it::log('sqllog', "$msec\t$query" . ($this->_p['sqltrace'] ? "\t" . it_debug::backtrace(1) : ""));
+	}
+
+	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)
+{
+	$mres = $this->_result;
+	$result = $this->select(array($this->_p['keyfield'] => $id));
+	$this->_result = $mres;
+	return $result;
+}
+
+
+/**
+ * Select a set of records from table and fetch the first one
+ * @param $query Optional array of (field => value) or (int => sqlstring) pairs. Defaults to null (select all records)
+ *           Fields are joined by AND
+ *           Fields can contain a compare operator, e.g. 'name LIKE' or 'amount >'
+ *           $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 without 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 it_dbi::iterate(), it_dbi::_where()
+ */
+function select($query = null)
+{
+	$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']);
+
+	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 it_dbi::select()
+ */
+function iterate()
+{
+	if (!$this->_nofetch)
+	{
+		$this->clear(false);
+		if ($this->_data = mysql_fetch_assoc($this->_result))
+		{
+			if (!empty($this->_p['keyfield']))
+				$this->_key = $this->_data[$this->_p['keyfield']];
+
+			foreach ($this->_data as $key => $value)
+				$this->$key = $value;
+		}
+
+		$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);
+
+	unset($this->_data);		# All new value set
+	$set = $this->_set($tags);	# Update $tags (!) and generate SQL
+
+	if ($this->_p['randomid'] && !isset($tags[$this->_p['keyfield']]))
+	{
+		$tags[$this->_p['keyfield']] = md5(uniqid(mt_rand()));
+		$set = $this->_set($tags);	# Generate new SQL containing ID
+	}
+
+	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\"");
+	}
+
+	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 it_dbi::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 ($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();
+
+	if (!($this->_fields = $GLOBALS['it_dbi']->_state[$this->_p['dbid']]['fields'][$this->_p['table']]))
+	{
+		debug("it_dbi(): no fields for {$this->_p['dbid']}.{$this->_p['table']}, calculating.", 5);
+		for ($res = $this->query('SHOW COLUMNS FROM ' . $this->_p['table']); $field = mysql_fetch_assoc($res);)
+			$this->_fields[$field['Field']] = $field + array('Length' => ($field['Type'] == 'datetime') ? 20 : intval(it::match('\d+', $field['Type'])));
+
+		$GLOBALS['it_dbi']->_state[$this->_p['dbid']]['fields'][$this->_p['table']] = $this->_fields;
+	}
+
+	foreach($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;
+}
+
+} /* End class it_dbi */
+
+?>