diff options
Diffstat (limited to 'it_dbi.class')
| -rw-r--r-- | it_dbi.class | 591 |
1 files changed, 591 insertions, 0 deletions
diff --git a/it_dbi.class b/it_dbi.class new file mode 100644 index 0000000..5666d28 --- /dev/null +++ b/it_dbi.class @@ -0,0 +1,591 @@ +<?php +/* +** $Id$ +** +** 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. +** +** 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' => "24%^jXC~", + 'safety' => 1, /* 0= never die, 1=die if query invalid, 2=die also if no results */ + 'keyfield' => "ID", + 'createclasses' => false, + 'classprefix' => "", + 'autoincrement' => true, + 'randomid' => false, + 'sqllog' => false, + 'persistent' => false, + ); + + # Key of currently loaded record or null (public readonly) + var $_key; + + # Array of name => length of table fields + var $_fields; + + +/** + * Constructor: Initialize the DBI interface + * @param $config array(key => value) of configuration data + * @param $query Optional initial query to run + */ +function it_dbi($config = array(), $query = null) +{ + # Shortcut: String config means use this table with default values + if (!is_array($config)) + $config = array('table' => $config); + + # Create current settings + foreach ($config + $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']; + + $this->_dbid = "$this->_db/$this->_server/$this->_user"; + + $this->_defaults = ""; + foreach($config as $key => $value) + if ($key != 'createclasses') + $this->_defaults .= "'$key' => '".strtr($value, array('\'' => '\\\'', '$' => '\\$'))."', "; + + if ($this->_createclasses) + { + $this->_connect(); + + if (!($tables = mysql_list_tables($this->_db, $this->_link))) + $this->_fatal("it_dbi(): can't list on tables \"$this->_db\": " . mysql_error($this->_link)); + + 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 + $this->read($query); + } +} + +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); + } + + if (!$this->_link) + $this->_fatal("it_dbi(): can't create DB link (mysql_connect $this->_server, $this->_db): " . mysql_error()); + + if (!(@mysql_select_db($this->_db, $this->_link))) + $this->_fatal("it_dbi(): can't select database \"$this->_db\": " . mysql_error($this->_link)); + + $GLOBALS['it_dbi_link'][$this->_dbid] = $this->_link; + } +} + + +/** + * Convert table given by name into a class + */ +function createclass($table) +{ + $this->_connect(); + $classname = "$this->_classprefix$table"; + + if (!($result = @mysql_list_fields($this->_db, $table, $this->_link))) + $this->_fatal("it_dbi(): can't list fields \"$this->_db\": " . mysql_error($this->_link)); + + for ($fields=array(), $j=0; $j < mysql_num_fields($result); $j++) + { + $fields[mysql_field_name($result, $j)] = mysql_field_len($result, $j); + $flags = mysql_field_flags($result, $j); + + if (strstr($flags, "primary_key")) + { + $keyfield = mysql_field_name($result, $j); + $autoincrement = (bool)strstr($flags, "auto_increment"); + $randomid = (mysql_field_type($result, $j) == "string"); + } + } + + $fields = var_export($fields, true); + + if ((substr($classname, 0, 4) != 'PMA_') && !class_exists($classname)) + { + 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); + } + } + "); + } +} + + + +/** + * 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) +{ + $this->_connect(); + + # 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; + + # 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.<br> + * Example:<br> + * $sql = $table->construct_sql_clause(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 $sql Optional SQL addendum (added after $params), for ORDER BY etc. + * @param $omit_where (optional) Do not add 'WHERE ' at beginning of result (default: false) + * @return The generated SQL clause + * @see it_db_record::select, it_db_record::fetch_next + */ +function _where($params='', $sql='', $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 the field name starts with '-', the value is taken as raw, + 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 = "'".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) + $query .= "$sep$field $op ('" . join("','", array_map('mysql_real_escape_string', $value)) . "')"; # 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; + + if ($sql) + $query .= ' '; + } + $query .= $sql; + + return $query; +} + + +/** + * Internal: Output class name::error message and terminate execution. + */ +function _fatal($text) +{ + it::fatal(get_class($this).'::'.$text); + /* 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 => $value) + 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) +{ + $this->_connect(); + debug(get_class($this)."::query(\"$query\")", 4); + + $start = gettimeofday(); + + if ($this->_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 host '$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'."); + } + + if (!($result = mysql_query($query, $this->_link)) && $this->_safety) + { + if (($this->_safety < 2) && (mysql_errno($this->_link) == 1062)) /* Duplicate entry */ + return false; + + $error = mysql_errno($this->_link).' ('.mysql_error($this->_link).')'; + $trace = debug_backtrace(); + $res = mysql_fetch_row(mysql_query('select database()', $this->_link)); + $this->_fatal("query(\"$query\") on {$res[0]} failed: $error\nfile {$trace[1]['file']} line {$trace[1]['line']}"); + } + + + if ($this->_sqllog || EDC('sqllog')) + { + $end = gettimeofday(); + $msec = round(($end['sec'] - $start['sec']) * 1000 + ($end['usec'] - $start['usec']) / 1000); + it::log('sqllog', "$msec\t$query"); + } + + 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->_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) + * Can contain magic field 'SELECT' for things like 'COUNT(*)' or 'DISTINCT foo', defaults to '*' + * Can contain magic field 'JOIN' for things like 'tableA LEFT JOIN tableB ON a=b', defaults to table name + * If magic field 'CALC_FOUND_ROWS' is true, sets member var _found_rows to number of matching rows without LIMIT + * If magic field 'NOFETCH' is true, then 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_db_table::construct_sql_clause() + */ +function select($query = null) +{ + $result = 0; + $calc_found_rows = false; + + $what = '*'; + if (isset($query['SELECT'])) + { + $what = $query['SELECT']; + unset($query['SELECT']); + } + + $join = $this->_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']); + } + + $nofetch = $this->_nofetch = isset($query['NOFETCH']) ? $query['NOFETCH'] : false; + unset($query['NOFETCH']); + + if ($this->_result = $this->query($sql = "SELECT $what FROM $join " . $this->_where($query))) + $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->_safety >= 2)) + $this->_fatal("select(): query produced no results: \"$sql\""); + + $this->_nofetch = !$nofetch; + + return $result; +} + + +/** + * Iterate through select()ed records + * @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->_keyfield)) + $this->_key = $this->_data[$this->_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") +{ + /* 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->_randomid && !isset($tags[$this->_keyfield])) + { + $tags[$this->_keyfield] = md5(uniqid(mt_rand())); + $set = $this->_set($tags); # Generate new SQL containing ID + } + + if ($result = $this->query("$command INTO $this->_table " . $set)) + { + $id = ($this->_autoincrement && !isset($tags[$this->_keyfield])) ? mysql_insert_id($this->_link) : $tags[$this->_keyfield]; + if (!$this->read($id) && $this->_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) +{ + $result = true; + + /* 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 ($set = $this->_set($tags)) + if ($result = $this->query("UPDATE $this->_table $set " . $this->_where($query))) + 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 + */ +function delete($query = null) +{ + $result = 0; + + if (!$query && $this->_keyfield) + { + $query = array($this->_keyfield => $this->_key); + $this->clear(); + } + + if ($query) + { + if ($this->query(($sql = "DELETE FROM $this->_table " . $this->_where($query)))) + $result = mysql_affected_rows($this->_link); + } + + return $result; +} + +} /* End class it_dbi */ + +?> |