From be9859eb1b53a501615a99afe66c330977be9140 Mon Sep 17 00:00:00 2001
From: Christian Weber
Date: Tue, 25 Sep 2007 15:31:07 +0000
Subject: Add support for multiple databases, major cleanup, support key
updates, enhanced _fields, etc.
---
it_dbi.class | 347 ++++++++++++++++++++++++++++++-----------------------------
1 file changed, 176 insertions(+), 171 deletions(-)
diff --git a/it_dbi.class b/it_dbi.class
index f36903e..d785543 100644
--- a/it_dbi.class
+++ b/it_dbi.class
@@ -2,13 +2,21 @@
/*
** $Id$
**
-** ITools - the Internet Tools Library
+** Copyright (C) 1995-2007 by the ITools Authors.
+** This file is part of ITools - the Internet Tools Library
**
-** Copyright (C) 1995-2003 by the ITools Authors.
-** This program is free software; you can redistribute it and/or
-** modify it under the terms of either the GNU General Public License
-** or the GNU Lesser General Public License, as published by the Free
-** Software Foundation. See http://www.gnu.org/licenses/ for details.
+** 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 .
**
** dbi.class - UltraFlexible Database Interface 3000
*/
@@ -23,161 +31,146 @@ class it_dbi
'server_update' => null,
'user' => "itools",
'pw' => "",
- 'safety' => 1, /* 0= never die, 1=die if query invalid, 2=die also if no results */
- 'keyfield' => "ID",
- 'createclasses' => false,
+ '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' => "",
- 'autoincrement' => true,
- 'randomid' => false,
- 'sqllog' => false,
'persistent' => false,
+ 'sqllog' => false,
);
- # Key of currently loaded record or null (public readonly)
- var $_key;
-
- # Array of name => length of table fields
- var $_fields;
+ 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 $config array(key => value) of configuration data
+ * @param $p optional array(key => value) of configuration data
* @param $query Optional initial query to run
*/
-function it_dbi($config = array(), $query = null)
+function it_dbi($p = array(), $query = null)
{
# Shortcut: String config means use this table with default values
- if (!is_array($config))
- $config = array('table' => $config);
+ if (!is_array($p))
+ $p = array('table' => $p);
- # If global singleton $it_dbi exists, use its defaults (db, user, pw, server)
- $defaults = $GLOBALS['it_dbi']->_defaults ? eval("return array({$GLOBALS['it_dbi']->_defaults});") : array();
+ if ($p['home'])
+ $p['db'] = strtr(it::match('/www/([^/]*)', $p['home']), '.-', '__');
- # If the global singleton defaults are for another db, server or server_update then do not use them.
- if (isset($config['db']) && ($config['db'] != $defaults['db']) || isset($config['server']) && ($config['server'] != $defaults['server']) || isset($config['server_update']) && ($config['server_update'] != $defaults['server_update']))
- $defaults = array();
+ # 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;
- # Create current settings
- foreach ($config + $defaults + (array)$GLOBALS['it_dbi_defaultconfig'] + $this->_defaultconfig as $key => $value)
- {
- $var = "_$key";
- $this->$var = $value;
- }
- unset($this->_defaultconfig); /* to shorten print_r() output */
-
- if (!isset($this->_db))
- if($config['home'] && ($site = it::match( "/www/([^/]+)", $config['home'])))
- $this->_db = strtr( $site, ".-", "__" );
- else
- $this->_db = $GLOBALS['ULTRADB'];
+ # Combine our settings with user's defaults and class defaults
+ $p += (array)$GLOBALS['it_dbi_defaultconfig'] + array('sqllog' => EDC('sqllog'), 'db' => $GLOBALS['ULTRADB']) + $this->_defaultconfig;
+ unset($this->_defaultconfig); # to shorten ED() output
- $this->_dbid = "$this->_db/$this->_server/$this->_user";
+ $p['dbid'] = "{$p['user']}@{$p['server']}:{$p['db']}";
+ $this->_p = $p;
- $this->_defaults = "";
- foreach($config as $key => $value)
- if ($key != 'createclasses')
- $this->_defaults .= "'$key' => '".strtr($value, array('\'' => '\\\''))."', ";
-
- if ($this->_createclasses)
+ if ($p['table']) # Standard use: create a table object
{
- $this->_connect();
+ if (!$GLOBALS['it_dbi'])
+ new it_dbi(array('table' => null) + $p);
- if (!($tables = mysql_list_tables($this->_db, $this->_link)))
- $this->_fatal("it_dbi(): can't list on tables \"$this->_db\"");
+ $this->_p += $this->_get_field_info(); # Get $this->_fields and p[keyfield, autoincrement, randomid]
- for ($i = 0; $i < mysql_num_rows($tables); $i++)
- $this->createclass(mysql_tablename($tables, $i));
- }
-
- if (!$GLOBALS['it_dbi'] && !$config['table'])
- $GLOBALS['it_dbi'] =& $this;
-
- if (isset($query))
- {
if (is_array($query))
$this->select($query);
- else
+ elseif (isset($query))
$this->read($query);
}
+ elseif (!$GLOBALS['it_dbi']) # Special use w/o table: create singleton to store settings
+ $GLOBALS['it_dbi'] =& $this;
}
-function _connect()
-{
- if (!($this->_link = $GLOBALS['it_dbi_link'][$this->_dbid]))
- {
- # Use persistent connections but prevent reuse if only DB differs
- if (!$this->_persistent || $GLOBALS['it_dbi_connected']["$this->_server/$this->_user"]++)
- $this->_link = @mysql_connect($this->_server, $this->_user, $this->_pw, true);
- else
- $this->_link = @mysql_pconnect($this->_server, $this->_user, $this->_pw);
- if (!$this->_link)
- {
- # One retry after a short delay (always non-persistent)
- it::log('sqllog', "it_dbi(): retrying DB link (mysql_connect $this->_server, $this->_db): " . mysql_error());
- sleep(1);
- $this->_link = @mysql_connect($this->_server, $this->_user, $this->_pw, true);
- }
+/**
+ * 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);
- if (!$this->_link)
- $this->_fatal("it_dbi(): can't create DB link (mysql_connect $this->_server, $this->_db)");
+ $p += $dbi->_p;
+ $p['dbid'] = "{$p['user']}@{$p['server']}:{$p['db']}";
+ $dbi->_connect($p);
- if (!(@mysql_select_db($this->_db, $this->_link)))
- $this->_fatal("it_dbi(): can't select database \"$this->_db\"");
+ if (!($tables = mysql_list_tables($p['db'], $dbi->_link)))
+ $dbi->_fatal("it_dbi::createclasses(): can't list on tables \"{$p['db']}\"");
- $GLOBALS['it_dbi_link'][$this->_dbid] = $this->_link;
- }
+ for ($i = 0; $i < mysql_num_rows($tables); $i++)
+ $dbi->createclass(array('table' => mysql_tablename($tables, $i)) + $p);
}
/**
* Convert table given by name into a class
*/
-function createclass($table)
+function createclass($p)
{
- $this->_connect();
- $classname = "$this->_classprefix$table";
+ # Shortcut: String config means use this table with default values
+ if (!is_array($p))
+ $p = array('table' => $p);
- if (!($result = @mysql_list_fields($this->_db, $table, $this->_link)))
- $this->_fatal("it_dbi(): can't list fields \"$this->_db\"");
+ $p += $this->_p;
+ $classname = $p['classprefix'] . $p['table'];
- for ($fields=array(), $j=0; $j < mysql_num_fields($result); $j++)
+ if (substr($classname, 0, 4) != 'PMA_') # It is designed behaviour that an error is generated if this class already exists!
{
- $fields[mysql_field_name($result, $j)] = mysql_field_len($result, $j);
- $flags = mysql_field_flags($result, $j);
-
- if (strstr($flags, "primary_key"))
+ $code = "class $classname extends it_dbi
{
- $keyfield = mysql_field_name($result, $j);
- $autoincrement = (bool)strstr($flags, "auto_increment");
- $randomid = (mysql_field_type($result, $j) == "string");
- }
+ 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);
}
+}
- $fields = var_export($fields, true);
- if ((substr($classname, 0, 4) != 'PMA_') && !class_exists($classname))
+/**
+ * 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']))
{
- debug("it_dbi: creating class $classname", 5);
- eval("
- class $classname extends it_dbi
- {
- function $classname(\$query = null, \$config = array())
- {
- \$config += array({$this->_defaults}'table' => '$table', 'keyfield' => '$keyfield', 'autoincrement' => '$autoincrement', 'randomid' => '$randomid');
- \$this->_fields = $fields;
- \$this->it_dbi(\$config, \$query);
- }
- }
- ");
+ # 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.
+ * 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)
@@ -189,7 +182,7 @@ function _set(&$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));
+ it::error("it_dbi::_set() would take value '$value' from this->$field:" . D($this, $tags));
}
# Create SQL
@@ -207,22 +200,20 @@ function _set(&$tags)
/**
- * Create an SQL query (the stuff after 'WHERE') according to
- * an array of selection criteria.
- * Example:
+ * 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 $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, used in it_db_table
+ * @param $omit_where Do not add 'WHERE ' to result
* @return The generated SQL clause
- * @see it_db_record::select, it_db_record::fetch_next
+ * @see select(), iterate()
*/
function _where($params = "", $link = null, $omit_where = false)
{
@@ -259,11 +250,10 @@ function _where($params = "", $link = null, $omit_where = false)
else
$op = '=';
- /* If the field name starts with '-', the value is taken as raw,
- no escaping is done and no quotes are put around it. */
+ # 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 */
+ $field = substr($field, 1); # Strip that '-' sign
$qval = $value;
}
else if (!is_array($value))
@@ -352,7 +342,7 @@ function _write_pre_process(&$tags)
*/
function clear($pp = true)
{
- foreach ((array)$this->_fields as $key => $value)
+ foreach ((array)$this->_fields as $key => $dummy)
unset($this->$key);
unset($this->_data);
unset($this->_key);
@@ -367,30 +357,22 @@ function clear($pp = true)
*/
function query($query)
{
- debug(get_class($this)."::query(\"$query\")", 4);
-
- $this->_connect();
$start = gettimeofday();
- if ($this->_server_update && !preg_match('/^(EXPLAIN|SELECT|SHOW) /i', $query))
+ if ($this->_p['server_update'] && !preg_match('/^(EXPLAIN|SELECT|SHOW) /i', $query))
{
- if ($this->_link = @mysql_connect($this->_server_update, $this->_user, $this->_pw, true))
- {
- if (!mysql_select_db($this->_db, $this->_link))
- $this->_fatal("Error selecting update database '$this->_db' on server '$this->_server_update'.");
-
- $this->_server = $this->_server_update;
- unset($this->_server_update);
- $GLOBALS['it_dbi_link'][$this->_dbid] = $this->_link; # Update link cache for switching between databases
- #it::log('sqllog', "switched to update server $this->_server");
- }
- else
- $this->_fatal("Error connecting to update database server '$this->_server_update' as user '$this->_user'.");
+ debug("switching to update server \"{$this->_p['server_update']}\"", 5);
+ $this->_p['server'] = $this->_p['server_update'];
+ unset($this->_p['server_update'], $this->_link);
}
- if (!($result = mysql_query($query, $this->_link)) && $this->_safety)
+ $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->_safety < 2) && (mysql_errno($this->_link) == 1062)) /* Duplicate entry */
+ if (($this->_p['safety'] < 2) && (mysql_errno($this->_link) == 1062)) # Duplicate entry
return false;
$error = mysql_errno($this->_link).' ('.mysql_error($this->_link).')';
@@ -399,7 +381,7 @@ function query($query)
}
- if ($this->_sqllog || EDC('sqllog'))
+ if ($this->_p['sqllog'])
{
$end = gettimeofday();
$msec = round(($end['sec'] - $start['sec']) * 1000 + ($end['usec'] - $start['usec']) / 1000);
@@ -418,7 +400,7 @@ function query($query)
function read($id=null)
{
$mres = $this->_result;
- $result = $this->select(array($this->_keyfield => $id));
+ $result = $this->select(array($this->_p['keyfield'] => $id));
$this->_result = $mres;
return $result;
}
@@ -448,7 +430,7 @@ function select($query = null)
unset($query['SELECT']);
}
- $join = $this->_table;
+ $join = $this->_p['table'];
if (isset($query['JOIN'])) # WARNING: this field gets abused for "tablename USE INDEX (fast2)
{
$join = $query['JOIN'];
@@ -475,7 +457,7 @@ function select($query = null)
$this->_found_rows = intval($count);
}
- if (!$this->iterate() && ($this->_safety >= 2))
+ if (!$this->iterate() && ($this->_p['safety'] >= 2))
$this->_fatal("select(): query produced no results: \"$sql\"");
$this->_nofetch = !$nofetch;
@@ -496,8 +478,8 @@ function iterate()
$this->clear(false);
if ($this->_data = mysql_fetch_assoc($this->_result))
{
- if (!empty($this->_keyfield))
- $this->_key = $this->_data[$this->_keyfield];
+ if (!empty($this->_p['keyfield']))
+ $this->_key = $this->_data[$this->_p['keyfield']];
foreach ($this->_data as $key => $value)
$this->$key = $value;
@@ -528,16 +510,16 @@ function insert($tags = array(), $command = "INSERT")
unset($this->_data); # All new value set
$set = $this->_set($tags); # Update $tags (!) and generate SQL
- if ($this->_randomid && !isset($tags[$this->_keyfield]))
+ if ($this->_p['randomid'] && !isset($tags[$this->_p['keyfield']]))
{
- $tags[$this->_keyfield] = md5(uniqid(mt_rand()));
+ $tags[$this->_p['keyfield']] = md5(uniqid(mt_rand()));
$set = $this->_set($tags); # Generate new SQL containing ID
}
- if ($result = $this->query("$command INTO $this->_table " . $set))
+ if ($result = $this->query("$command INTO {$this->_p['table']} " . $set))
{
- $id = ($this->_autoincrement && !isset($tags[$this->_keyfield])) ? mysql_insert_id($this->_link) : $tags[$this->_keyfield];
- if (!$this->read($id) && $this->_safety)
+ $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\"");
}
@@ -570,13 +552,18 @@ function update($tags = array(), $query = null)
/* Pre-processing, $tags is passed by reference and may be modified here */
$this->_write_pre_process($tags);
- if (!$query && !empty($this->_keyfield))
- $query = array($this->_keyfield => $this->_data[$this->_keyfield]);
+ 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->_table $set " . $this->_where($query, $this->_link)))
- if (isset($this->_key) && $this->read($this->_key))
+ 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;
}
@@ -585,40 +572,58 @@ function update($tags = array(), $query = null)
/**
* 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->_keyfield)
+ if (!$query && $this->_p['keyfield'])
{
- $query = array($this->_keyfield => $this->_key);
+ $query = array($this->_p['keyfield'] => $this->_key);
$this->clear();
}
- if ($query)
- {
- if ($this->query(($sql = "DELETE FROM $this->_table " . $this->_where($query, $this->_link))))
- $result = mysql_affected_rows($this->_link);
- }
+ if ($query && $this->query("DELETE FROM {$this->_p['table']} " . $this->_where($query, $this->_link)))
+ $result = mysql_affected_rows($this->_link);
return $result;
}
+
/**
- * Return information about a table's fields as array (Field => array(Field,Type,Null,Key,Default,Extra,Length))
+ * INTERNAL: Store information about a table's fields in $this->_fields, possibly from cache.
+ * @return array(keyfield, autoincrement, randomid)
*/
-function get_field_info()
+function _get_field_info()
{
$result = array();
- for ($res = $this->query('SHOW COLUMNS FROM ' . $this->_table); $field = mysql_fetch_assoc($res);)
- $result[$field['Field']] = $field + array('Length' => intval(it::match('\d+', $field['Type'])));
+
+ 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' => intval(it::match('\d+', $field['Type'])));
+
+ $GLOBALS['it_dbi']->_state[$this->_p['dbid']]['fields'][$this->_p['table']] = $this->_fields;
+ unset($GLOBALS['it_dbi']->_p['table']); # Remove cruft
+
+ 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;
+ }
+ }
+ }
return $result;
}
-
} /* End class it_dbi */
?>
--
cgit v1.2.3