diff options
Diffstat (limited to 'it_dbi.class')
| -rw-r--r-- | it_dbi.class | 347 |
1 files 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 <http://www.gnu.org/licenses/>. ** ** 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.<br> - * Example:<br> + * 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 */ ?> |