diff options
| -rw-r--r-- | it_dbi.class | 373 | ||||
| -rw-r--r-- | it_dbi_postgres.class | 188 | ||||
| -rwxr-xr-x | test/it_dbi.t | 88 |
3 files changed, 495 insertions, 154 deletions
diff --git a/it_dbi.class b/it_dbi.class index 0fa890a..e032a03 100644 --- a/it_dbi.class +++ b/it_dbi.class @@ -21,6 +21,8 @@ class it_dbi { + static $_global_key = 'it_dbi'; # $GLOBAL key to use for singleton + var $_found_rows; # public: number of found rows if CALC_FOUND_ROWS was set var $_data; # semi-public: current result as assoc array @@ -69,7 +71,7 @@ function __construct($p = array(), $query = null) $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; + $dp = (array)$GLOBALS[static::$_global_key]->_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; @@ -81,8 +83,8 @@ function __construct($p = array(), $query = null) if ($p['table']) # Standard use: create a table object { - if (!isset($GLOBALS['it_dbi'])) - new it_dbi; + if (!isset($GLOBALS[static::$_global_key])) + new static::$_global_key; if ($p['getfieldinfo']) $this->_p += $this->_get_field_info(); # Get $this->_fields and p[keyfield, autoincrement, randomid] @@ -98,7 +100,7 @@ function __construct($p = array(), $query = null) $this->read($query); } else - $GLOBALS['it_dbi'] =& $this; + $GLOBALS[static::$_global_key] =& $this; } /** @@ -115,26 +117,26 @@ function __construct($p = array(), $query = null) static function createclasses($p = array()) { # Make sure singleton exists - $dbi = $GLOBALS['it_dbi'] ? $GLOBALS['it_dbi'] : new it_dbi($p); + $dbi = $GLOBALS[static::$_global_key] ?: new static::$_global_key($p); $p += $dbi->_p; $dbid = "{$p['user']}@{$p['server']}:{$p['db']}"; - $state = it_dbi::_state_get($dbid); + $state = static::_state_get($dbid); if (!$tables = $state['tables']) { $tables = $dbi->tables($p); - $state = it_dbi::_state_get($dbid); # State could have been modified by $db->tables() call + $state = static::_state_get($dbid); # State could have been modified by $db->tables() call $state['tables'] = $tables; - it_dbi::_state_put($dbid, $state); + static::_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); + static::createclass(array('table' => $table) + $p); } } @@ -149,23 +151,22 @@ static function createclass($p) $p = array('table' => $p); # Make sure singleton exists - $dbi = $GLOBALS['it_dbi'] ? $GLOBALS['it_dbi'] : new it_dbi(array('table' => null) + $p); + $dbi = $GLOBALS[static::$_global_key] ? $GLOBALS[static::$_global_key] : new static::$_global_key(['table' => null] + $p); $p += $dbi->_p; $dbid = $dbi->_dbid = "{$p['user']}@{$p['server']}:{$p['db']}"; if (!isset($dbi->_tables[$dbid])) { - $state = it_dbi::_state_get($dbid); + $state = static::_state_get($dbid); $dbi->_tables[$dbid] = array(); if (!($tables = $state['tables'])) { - for ($tables = array(), $res = $dbi->query('SHOW TABLES', $p); $row = mysqli_fetch_row($res);) - $tables[] = $row[0]; + $tables = $dbi->tables($p); - $state = it_dbi::_state_get($dbid); # State could have been modified by query above + $state = static::_state_get($dbid); # State could have been modified by query above $state['tables'] = $tables; - it_dbi::_state_put($dbid, $state); + static::_state_put($dbid, $state); } foreach ($tables as $table) @@ -179,7 +180,8 @@ static function createclass($p) 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 + $parentname = get_called_class(); + $code = "class $classname extends $parentname $interface { function __construct(/* \$query ... */) { @@ -207,53 +209,50 @@ function _connect($p = array()) { $p += $this->_p; $dbid = "{$p['user']}@{$p['server']}:{$p['db']}"; - $state = it_dbi::_state_get($dbid); + $state = static::_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 = @mysqli_connect($p['server'], $p['user'], $p['pw']); + if ($GLOBALS[static::$_global_key]->_connected["{$p['server']}/{$p['user']}"]++) + list($this->_link, $error) = $this->_connect_db($p); else - $this->_link = @mysqli_connect($p['server'], $p['user'], $p['pw']); + list($this->_link, $error) = $this->_connect_db($p); if (!$this->_link) { # One retry after a short delay - it::log('sqllog', "it_dbi(): retrying DB link (mysqli_connect {$p['server']}, {$p['db']}): " . mysqli_connect_error()); + it::log('sqllog', "it_dbi(): retrying DB link (_connect_db {$p['server']}, {$p['db']}): $error"); sleep(1); - $this->_link = @mysqli_connect($p['server'], $p['user'], $p['pw']); + list($this->_link, $error) = $this->_connect_db($p); } if (!$this->_link) - $this->_fatal("_connect(): can't create DB link (mysqli_connect {$p['user']}@{$p['server']}, {$p['db']}): " . mysqli_connect_error()); - - if (!(@mysqli_select_db($this->_link, $p['db']))) - $this->_fatal("_connect(): can't select database \"{$p['db']}\""); - - # set charset used for this connection - if ($p['charset']) - if (!mysqli_set_charset($this->_link, $p['charset'])) - $this->_fatal("_connect(): can't set charset \"{$p['charset']}\""); + $this->_fatal("_connect(): can't create DB link (_connect_db {$p['user']}@{$p['server']}, {$p['db']}): $error"); # NOTE: This overwrites old state but that is on purpose. New link means we should refetch all info about connection $state['link'] = $this->_link; - it_dbi::_state_put($dbid, $state, false); # Store only locally as link is not shared anyway + static::_state_put($dbid, $state, false); # Store only locally as link is not shared anyway } } /** - * INTERNAL: construct SQL SET clause of changed values from tags array. + * INTERNAL: construct SQL expressions of changed values from tags array. * $force = must write all fields, dont try to optimize */ -function _set($tags, $force = false) +function _expressions($tags, $force = false) { $result = array(); $dyndata = $this->_dyndata; foreach((array)$tags as $field => $value) { + if (is_int($field)) { /* no key specified; pass on unchanged */ + $result[$field] = $value; + continue; + } + $f = trim($field, "-"); if (!$this->_fields[$f] && it::match(",$f,", $this->_fieldnames)) it::error("case mismatch on field $f, valid fields $this->_fieldnames"); @@ -264,44 +263,81 @@ function _set($tags, $force = false) if (!$this->_fields[$f] && $this->_fields['dyncols']) { if (substr($field, 0, 1) === "-") - $newdyns[] = "'\$.$f', " . $value; + $newdyns[$f] = $value; else if ($force || isset($value) && isset($dyndata[$f]) ? strval($value) !== strval($dyndata[$f]) : $value !== $dyndata[$f] || !array_key_exists($f, $dyndata)) { if (is_null($value)) - $deldyns[] = "'\$.$f'"; - if (is_int($value)) - $newdyns[] = "'\$.$f', $value"; + $deldyns[] = $f; + else if (is_int($value)) + $newdyns[$f] = $value; else - $newdyns[] = "'\$.$f', " . $this->escape_string($value); + $newdyns[$f] = $this->escape_string($value); } - $alldyns[] = $this->escape_string($f) . ", " . (substr($field, 0, 1) === "-" || is_int($value) ? $value : $this->escape_string($value)); + $alldyns[$f] = (substr($field, 0, 1) === "-" || is_int($value) ? $value : $this->escape_string($value)); $dyndata[$f] = $value; } else if (substr($field, 0, 1) === '-') # Unquoted value (always added) - $result[] = substr($field, 1) . "=$value"; + $result[substr($field, 1)] = $value; else if ($force || (isset($value) && isset($this->_data[$field]) ? strval($value) !== strval($this->_data[$field]) : $value !== $this->_data[$field] || !array_key_exists($field, $this->_data))) - $result[] = "`$field`=" . (isset($value) ? $this->escape_string($value) : 'NULL'); + $result[$field] = isset($value) ? $this->escape_string($value) : 'NULL'; } if ($alldyns) { if ($force == "insert") # INSERT/REPLACE - $result[] = "dyncols = JSON_OBJECT(" . join(", ", (array)$alldyns) . ")"; + $result['dyncols'] = $this->_json_object($alldyns); else if ($newdyns || $deldyns) { - $source = $this->_dyndata ? 'dyncols' : '"{}"'; + $source = $this->_dyndata ? 'dyncols' : $this->escape_string('{}'); if ($newdyns) - $source = "JSON_SET($source, " . join(', ', $newdyns) . ')'; + $source = $this->_json_set($source, $newdyns); if ($deldyns) - $source = "JSON_REMOVE($source, " . join(', ', $deldyns) . ')'; - $result[] = "dyncols = $source"; + $source = $this->_json_remove($source, $deldyns); + $result['dyncols'] = $source; } } $this->_writes += $result ? 1 : 0; - return $result ? 'SET ' . implode(', ', $result) : ''; + return $result; +} + +/** + * INTERNAL: construct SQL SET clause of changed values from tags array. + * $force = must write all fields, dont try to optimize + */ +function _set($tags, $force = false) +{ + $expressions = $this->_expressions($tags, $force); + $append = []; + foreach ((array)$expressions as $k => $v) { + if (is_int($k)) /* no key specified; just append */ + $append[] = $v; + else + $strings[] = $this->escape_name($k) . "=$v"; + } + + return $strings ? 'SET ' . implode(', ', $strings) . implode(' ', $append) : ''; +} + +/** + * INTERNAL: construct SQL VALUES clause of changed values from tags array. + * $force = must write all fields, dont try to optimize + */ +function _values($tags, $force = false) +{ + $expressions = $this->_expressions($tags, $force); + $append = []; + foreach ((array)$expressions as $k => $v) { + if (is_int($k)) /* no key specified; just append */ + $append[] = $v; + else { + $keys[] = $this->escape_name($k); + $vals[] = $v; + } + } + return $expressions ? '(' . implode(', ', $keys) . ') VALUES (' . implode(', ', $vals) . ')' . implode(' ', $append) : ''; } /** @@ -392,7 +428,9 @@ function _where($params) } if ($dyncols_enabled && $this->_fields['dyncols'] && !$this->_fields[$field] && strpos($field, '(') === false) - $field = "JSON_EXTRACT(dyncols, " . $this->escape_string('$.' . $field) . ")"; + $field = $this->_json_extract('dyncols', $field); + else if (it::match('^\w*[A-Z]\w+$', $field, ['casesensitive' => 1])) + $field = $this->escape_name($field); switch ($op) { @@ -406,7 +444,7 @@ function _where($params) $query .= $sep . "$field IS NOT NULL AND (" . join(" OR ", $parts) . ")"; # Check for IS NOT NULL to take advantage of index } else - $query .= $sep . "1"; + $query .= $sep . "TRUE"; break; case 'MATCH': @@ -428,7 +466,7 @@ function _where($params) $query .= "$sep$field $op (" . join(",", $qvals) . ")"; # null is mapped to '' } else - $query .= $sep . (($op == 'IN') ? "0" : "1"); + $query .= $sep . (($op == 'IN') ? "FALSE" : "TRUE"); break; } @@ -454,25 +492,15 @@ function _where($params) return $query; } - /** * Internal: Output class name::error message and terminate execution. */ function _fatal($text, $body = null) { - $text = get_class($this).'::'.$text; - - if ($this->_link && ($errstr = mysqli_error($this->_link))) - $text = "\"$errstr\" in $text [errno " . mysqli_errno($this->_link) . "]"; - - if ($this->_link && ($res = @mysqli_fetch_row(mysqli_query($this->_link, 'select database()')))) # dont create extra errs - $text .= ", DB: " . $res[0]; - - it::fatal(['title' => $text . ", Server: " . $this->_p['server'], 'body' => $body]); + it::fatal(['title' => $this->_error($text) . ", DB: " . $this->_p['db'] . ", Server: " . $this->_p['server'], 'body' => $body]); /* NOT REACHED */ } - /** * Hook to postprocess data after reading a record. * This is a stub-function that can be overloaded. @@ -502,10 +530,7 @@ static function _write_preprocess($data) */ function tables($p = array()) { - for ($qr = $this->query('SHOW TABLES', $p); $row = mysqli_fetch_row($qr);) - $result[] = $row[0]; - - return (array)$result; + return $this->_tables($p); } @@ -547,40 +572,17 @@ function query($query, $p = array()) debug("{$p['user']}@{$p['server']}:{$p['db']}" . '.' . get_class($this) . "::query(\"$query\")", 4); - if (!($result = mysqli_query($this->_link, $query, $p['unbuffered'] ? MYSQLI_USE_RESULT : MYSQLI_STORE_RESULT)) && $p['safety']) + if (!($result = $this->_query($query, $p))) { - $errno = mysqli_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 mysqli_connect {$p['server']}, {$p['db']}"); - $this->_connect(array('reconnect' => true)); - $result = mysqli_query($this->_link, $query, $p['unbuffered'] ? MYSQLI_USE_RESULT : MYSQLI_STORE_RESULT); - } - - if (!$result) - $this->_fatal("query(\"$query\") failed"); + if ($result === false) + return $result; + $this->_fatal("query(\"$query\") failed"); } else if (it::match('^(CREATE|ALTER|DROP) ', $query, array('utf8' => false))) { # Purge cache for schema changes (after modifying table) $dbid = "{$p['user']}@{$p['server']}:{$p['db']}"; - it_dbi::_state_purgeshared($dbid); - } - - $this->_affectedrows = $this->_link->affected_rows; # get_warnings() clobbers this - $this->_insertid = mysqli_insert_id($this->_link); # get_warnings() clobbers this - if (($warning = $this->_link->get_warnings())) - { - do { - if (!it::match(trim($this->_p['ignored_warnings'] . "|1364|1261|1051|1062", "|"), $warning->errno)) - $messages[] = $warning->message . " [error $warning->errno]"; - } while ($warning->next() && ++$checked < 20); - - if ($messages) - it::error(['title' => "Mysql warning: " . $messages[0], 'body' => "$query\n\n" . join("\n", $messages) . "\n"]); + static::_state_purgeshared($dbid); } if ($writing && $this->_p['throttle_writes']) @@ -674,11 +676,11 @@ function select(/* $query = array|string, ... */) $this->clear(); if ($this->_result = $this->query($sql = "SELECT $what " . $this->_from($query) . " " . $this->_where($query))) { - $result = $this->_p['unbuffered'] ? true : mysqli_num_rows($this->_result); + $result = $this->_p['unbuffered'] ? true : $this->_num_rows($this->_result); if ($calc_found_rows) { - list($count) = mysqli_fetch_row($this->query('SELECT FOUND_ROWS()')); + $count = $this->_fetch_assoc($this->query('SELECT FOUND_ROWS() AS count'))['count']; $this->_found_rows = intval($count); } @@ -701,7 +703,7 @@ function iterate() { if (!$this->_nofetch) { - if ($this->_data = mysqli_fetch_assoc($this->_result)) + if ($this->_data = $this->_fetch_assoc($this->_result)) { if ($localizedfields = $this->_localizedfields) foreach ($localizedfields as $field => $dummy) @@ -778,9 +780,9 @@ function insert($tags = array(), $command = "INSERT") if ($this->_p['randomid'] && !isset($tags[$this->_p['keyfield']])) $tags[$this->_p['keyfield']] = bin2hex(random_bytes(16)); - $set = $this->_set($tags, "insert"); + $values = $this->_values($tags, "insert"); - if ($result = $this->query($query = "$command INTO {$this->_p['table']} " . $set)) + if ($result = $this->query($query = "$command INTO {$this->_p['table']} " . $values)) { $id = ($this->_p['autoincrement'] && !isset($tags[$this->_p['keyfield']])) ? $this->_insertid : $tags[$this->_p['keyfield']]; if ($this->_p['keyfield'] && !$this->read($id) && $this->_p['safety']) @@ -885,7 +887,7 @@ function delete($query = null) */ function delete_untouched($query = null) { - if ($this->select(['SELECT' => $this->_p['keyfield']] + (array)$query)) + if ($this->select(['SELECT' => $this->escape_name($this->_p['keyfield'])] + (array)$query)) while ($this->iterate()) if (($id = $this->_key) && !$this->_touchedids[$id] && $this->delete()) $result[] = $id; @@ -902,9 +904,19 @@ function delete_untouched($query = null) function escape_string($str) { $this->_connect(); - return "'" . mysqli_real_escape_string($this->_link, $str) . "'"; + return $this->_escape_string($str); } +/** + * Escapes a name/identifier for use in a DB query + * @param The identifier to be quoted + * @return The quoted value + */ +function escape_name($str) +{ + $this->_connect(); + return $this->_escape_name($str); +} /** * INTERNAL: Store information about a table's fields in $this->_fields, possibly from cache. @@ -914,16 +926,15 @@ function _get_field_info() { $result = array(); $dbid = "{$this->_p['user']}@{$this->_p['server']}:{$this->_p['db']}"; - $state = it_dbi::_state_get($dbid); + $state = static::_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 = mysqli_fetch_assoc($res)); ) - { - $this->_fields[$field['Field']] = $field + array('Length' => preg_match('/date|time/', $field['Type']) ? 20 : intval(it::match('\d+', $field['Type']))); + foreach ($this->_get_field_defs() as $name => $field) { + $this->_fields[$name] = $field + array('Length' => preg_match('/date|time/', $field['Type']) ? 20 : intval(it::match('\d+', $field['Type']))); if (preg_match('/^(tiny|small|medium|)int|^float|^double/', $field['Type'])) - $this->_convertfunc[$field['Field']] = it::match('int', $field['Type']) ? "intval" : "floatval"; + $this->_convertfunc[$name] = it::match('int', $field['Type']) ? "intval" : "floatval"; } $this->_fieldnames = "," . join(",", array_keys((array)$this->_fields)) . ","; @@ -931,11 +942,11 @@ function _get_field_info() foreach (preg_grep('/_' . $this->_p['localized_defaultlanguage'] . '$/', array_keys((array)$this->_fields)) as $field) $this->_localizedfields[substr($field, 0, -1 - strlen($this->_p['localized_defaultlanguage']))] = true; - $state = it_dbi::_state_get($dbid); # State could have been modified by query above + $state = static::_state_get($dbid); # State could have been modified by query above $state['fields'][$this->_p['table']] = $this->_fields; $state['convertfunc'][$this->_p['table']] = $this->_convertfunc; $state['localizedfields'][$this->_p['table']] = $this->_localizedfields; - it_dbi::_state_put($dbid, $state); + static::_state_put($dbid, $state); } else # Existing _fields, copy other info too { @@ -953,14 +964,14 @@ function _get_field_info() } } - unset($GLOBALS['it_dbi']->_p['table'], $GLOBALS['it_dbi']->_p['keyfield']); # Remove cruft + unset($GLOBALS[static::$_global_key]->_p['table'], $GLOBALS[static::$_global_key]->_p['keyfield']); # Remove cruft return $result; } static function _state_get($dbid) { - if (!($result = $GLOBALS['it_dbi']->_state[$dbid])) - $result = $GLOBALS['it_dbi']->_state[$dbid] = (array)it_cache::get("dbi:$dbid"); + if (!($result = $GLOBALS[static::$_global_key]->_state[$dbid])) + $result = $GLOBALS[static::$_global_key]->_state[$dbid] = (array)it_cache::get(get_called_class() . ":$dbid"); #var_dump("get", $dbid, $result); return $result; @@ -969,15 +980,15 @@ static function _state_get($dbid) static function _state_put($dbid, $state, $shared = true) { #var_dump("put", $dbid, $state); - $GLOBALS['it_dbi']->_state[$dbid] = $state; + $GLOBALS[static::$_global_key]->_state[$dbid] = $state; if ($shared) - it_cache::put("dbi:$dbid", array('link' => null) + (array)$state); # link is not transferable + it_cache::put(get_called_class() . ":$dbid", array('link' => null) + (array)$state); # link is not transferable } static function _state_purgeshared($dbid = null) { #var_dump("purgeshared", $dbid); - it_cache::put("dbi:" . ($dbid ? $dbid : $GLOBALS['it_dbi']->_dbid), array()); # Nuke shared cache + it_cache::put(get_called_class(). ":" . ($dbid ? $dbid : $GLOBALS[static::$_global_key]->_dbid), array()); # Nuke shared cache } # @@ -1005,8 +1016,8 @@ function rewind() $this->select(); # Only rewind if not already at start and results present - if (!$this->_nofetch && mysqli_num_rows($this->_result)) - mysqli_data_seek($this->_result, 0); + if (!$this->_nofetch && $this->_num_rows($this->_result)) + $this->_seek($this->_result, 0); $this->_iteratorkey = 0; $this->iterate(); @@ -1037,4 +1048,134 @@ static function get($id) return isset($id) && ($rec = new static) && $rec->read($id) ? $rec : null; } + +/** + * Start of mysqli specific default implmementation + */ + +function _escape_string($str) +{ + return "'" . mysqli_real_escape_string($this->_link, $str) . "'"; +} + +function _escape_name($str) +{ + return "`" . $str . "`"; +} + +function _connect_db($p) { + $result = @mysqli_connect($p['server'], $p['user'], $p['pw']); + + if ($result) + { + if (!(@mysqli_select_db($result, $p['db']))) + $this->_fatal("_connect(): can't select database \"{$p['db']}\""); + + # set charset used for this connection + if ($p['charset']) + if (!mysqli_set_charset($result, $p['charset'])) + $this->_fatal("_connect(): can't set charset \"{$p['charset']}\""); + } + + return [$result, mysqli_connect_error()]; +} + +function _get_field_defs() +{ + for ($res = $this->query('SHOW COLUMNS FROM ' . $this->_p['table']); $res && ($field = $this->_fetch_assoc($res)); ) + $result[$field['Field']] = it::filter_keys($field, ['Field', 'Type', 'Key', 'Extra']); + return (array)$result; +} + +function _tables($p) { + for ($qr = $this->query('SHOW TABLES', $p); $row = mysqli_fetch_row($qr);) + $result[] = $row[0]; + + return (array)$result; +} + +function _query($query, $p) +{ + if (!($result = mysqli_query($this->_link, $query, $p['unbuffered'] ? MYSQLI_USE_RESULT : MYSQLI_STORE_RESULT)) && $p['safety']) + { + $errno = mysqli_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 mysqli_connect {$p['server']}, {$p['db']}"); + $this->_connect(array('reconnect' => true)); + $result = mysqli_query($this->_link, $query, $p['unbuffered'] ? MYSQLI_USE_RESULT : MYSQLI_STORE_RESULT); + } + } + + $this->_affectedrows = $this->_link->affected_rows; # get_warnings() clobbers this + $this->_insertid = mysqli_insert_id($this->_link); # get_warnings() clobbers this + if (($warning = $this->_link->get_warnings())) + { + do { + if (!it::match(trim($this->_p['ignored_warnings'] . "|1364|1261|1051|1062", "|"), $warning->errno)) + $messages[] = $warning->message . " [error $warning->errno]"; + } while ($warning->next() && ++$checked < 20); + + if ($messages) + it::error(['title' => "Mysql warning: " . $messages[0], 'body' => "$query\n\n" . join("\n", $messages) . "\n"]); + } + + return $result; +} + +function _json_extract($col, $field) +{ + return "JSON_EXTRACT($col, " . $this->escape_string('$.' . $field) . ")"; +} + +function _json_object($tags) +{ + foreach ((array)$tags as $f => $v) + $strings[] = $this->escape_string($f) . ', ' . $v; + return "JSON_OBJECT(" . implode(', ', $strings) . ")"; +} + +function _json_set($source, $tags) +{ + foreach ((array)$tags as $f => $v) + $strings[] = $this->escape_string('$.' . $f) . ', ' . $v; + return "JSON_SET($source, " . implode(", ", $strings) . ')'; +} + +function _json_remove($source, $fields) +{ + foreach ((array)$fields as $f) + $strings[] = $this->escape_string('$.' . $f); + return "JSON_REMOVE($source, " . implode(", ", $strings) . ')'; +} + +function _fetch_assoc($res) +{ + return mysqli_fetch_assoc($res); +} + +function _num_rows($res) +{ + return mysqli_num_rows($res); +} + + +function _seek($res, $offset) +{ + return mysqli_data_seek($res, $offset); +} + +function _error($text) +{ + $text = get_class($this).'::'.$text; + + if ($this->_link && ($errstr = mysqli_error($this->_link))) + $text = "\"$errstr\" in $text [errno " . mysqli_errno($this->_link) . "]"; + + return $text; +} + } /* End class it_dbi */ diff --git a/it_dbi_postgres.class b/it_dbi_postgres.class new file mode 100644 index 0000000..46c0393 --- /dev/null +++ b/it_dbi_postgres.class @@ -0,0 +1,188 @@ +<?php +/* +** 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_postgres extends it_dbi +{ + +static $_global_key = 'it_dbi_postgres'; // override base class to get our own singleton + +function _where($params) +{ + if ($params['LIMIT'] && ($m = it::match('^\s*(\d+)\s*,\s*(\d+)\s*$', $params['LIMIT']))) { + unset($params['LIMIT']); + $params[] = " OFFSET $m[0] LIMIT $m[1]"; + } + return parent::_where($params); +} + +function replace($tags = []) +{ + foreach (array_keys($this->_fields) as $k) { + $escaped = $this->escape_name($k); + $strings[] = $escaped . '= EXCLUDED.' . $escaped; + } + $this->insert(array_merge($tags, [' ON CONFLICT (' . $this->escape_name($this->_p['keyfield']) . ') DO UPDATE SET ' . implode(', ', $strings)])); +} + +function _tables($p = array()) +{ + for ($qr = $this->query('SELECT table_name FROM information_schema.tables ' . $this->_where(['table_catalog' => $this->_p['db'], 'table_schema' => 'public'], $p), $p); $row = $this->_fetch_assoc($qr);) + $result[] = $row['table_name']; + + return (array)$result; +} + +function _get_field_defs() +{ + list($table_name, $table_schema) = array_reverse(explode('.', $this->_p['table'], 2)); + $where = $this->_where(['t.table_name' => $table_name, 't.table_schema' => $table_schema ?: 'public', 't.table_catalog' => $this->_p['db']]); + + // recreate Key column of mysql show columns + $res = $this->query('SELECT column_name,constraint_type,ordinal_position FROM information_schema.table_constraints AS t JOIN information_schema.key_column_usage USING (constraint_name, constraint_schema, constraint_catalog) ' . $where); + while ($res && ($row = $this->_fetch_assoc($res))) { + if ($row['constraint_type'] == 'PRIMARY KEY') + $keys[$row['column_name']] = 'PRI'; + else if ($row['ordinal_position'] == 1) + $keys[$row['column_name']] = $row['constraint_type'] == 'UNIQUE' ? 'UNI' : 'MUL'; + } + + $res = $this->query('SELECT * FROM information_schema.columns AS t ' . $where); + while ($res && ($field = $this->_fetch_assoc($res))) { + $result[$field['column_name']] = ['Field' => $field['column_name'], 'Type' => $field['data_type'], 'Extra' => it::match('^nextval\(', $field['column_default']) ? 'auto_increment' : '', 'Key' => $keys[$field['column_name']]]; + } + + return $result; +} + +function _escape_string($str) +{ + return pg_escape_literal($this->_link, $str); +} + +function _escape_name($str) +{ + return pg_escape_identifier($this->_link, $str); +} + +function _connect_db($p) { + $result = @pg_connect("host=$p[server] user=$p[user] dbname=$p[db] password=$p[pw]"); + + if ($result) + { + # set charset used for this connection + if ($p['charset']) + if (!pg_set_client_encoding($result, $p['charset'])) + $this->_fatal("_connect(): can't set charset \"{$p['charset']}\""); + } + + return [$result, $result ? '' : 'Could not connect']; +} + +function _query($query, $p) +{ + if ($this->_p['keyfield'] && it::match('^INSERT ', $query)) { + $isinsert = true; + $query .= ' RETURNING ' . $this->_escape_name($this->_p['keyfield']); + } + if (!($result = pg_query($this->_link, $query)) && $p['safety']) + { + /* TODO + $errno = mysqli_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 mysqli_connect {$p['server']}, {$p['db']}"); + $this->_connect(array('reconnect' => true)); + $result = mysqli_query($this->_link, $query, $p['unbuffered'] ? MYSQLI_USE_RESULT : MYSQLI_STORE_RESULT); + } + */ + } + + $this->_affectedrows = pg_affected_rows($result); + $this->_insertid = $isinsert ? $this->_fetch_assoc($result)[$this->_p['keyfield']] : 0; + // TODO probably PGSQl_NOTICE_ALL + // if (($warning = $this->_link->get_warnings())) + // { + // do { + // if (!it::match(trim($this->_p['ignored_warnings'] . "|1364|1261|1051|1062", "|"), $warning->errno)) + // $messages[] = $warning->message . " [error $warning->errno]"; + // } while ($warning->next() && ++$checked < 20); + + // if ($messages) + // it::error(['title' => "Mysql warning: " . $messages[0], 'body' => "$query\n\n" . join("\n", $messages) . "\n"]); + // } + + return $result; +} + +function _json_extract($col, $field) +{ + return "$col->>" . $this->escape_string($field); +} + +function _json_object($tags) +{ + foreach ((array)$tags as $f => $v) + $strings[] = $this->escape_string($f) . ', ' . $v; + return "JSONB_BUILD_OBJECT(" . implode(', ', $strings) . ")"; +} + +function _json_set($source, $tags) +{ + return "$source || " . $this->_json_object($tags); +} + +function _json_remove($source, $fields) +{ + foreach ((array)$fields as $f) + $strings[] = $this->escape_string($f); + return "($source - " . implode(" - ", $strings) . ")"; +} + +function _fetch_assoc($res) +{ + return pg_fetch_assoc($res); +} + +function _num_rows($res) +{ + return pg_num_rows($res); +} + + +function _seek($res, $offset) +{ + pg_result_seek($res, $offset); +} + +function _error($text) +{ + $text = get_class($this).'::'.$text; + + if ($this->_link && ($errstr = pg_last_error($this->_link))) + $text = "\"$errstr\" in $text"; + + return $text; +} + +} diff --git a/test/it_dbi.t b/test/it_dbi.t index 2baf9e3..ba8eaeb 100755 --- a/test/it_dbi.t +++ b/test/it_dbi.t @@ -3,17 +3,34 @@ # Tests for it_dbi.class +$opts = it::getopt(" + Usage: it_dbi.t [OPTIONS] + --db=S DB to use [lib_search_ch] + --user=S User + --pw=S Password + --subclass=S Subclass to test [it_dbi] +"); + # Initialize DB -$db = ['db' => "lib_search_ch", 'safety' => 0]; -$dbi = new it_dbi($db); -$dbi->query('create temporary table it_dbi_test ( - ID int not null auto_increment, +$db = it::filter_keys($opts, 'db,user,pw');# + ['safety' => 0]; +$dbi = new $opts['subclass']($db); + +$opts['subclass']::createclasses(); +$tables = $dbi->tables(); +ok(count($tables) > 0, 'there are some existing tables in the db'); +ok(new $tables[0], 'classes for tables exist'); + +$autoid = $opts['subclass'] == 'it_dbi_postgres' ? '"ID" Serial' : 'ID int not null auto_increment'; +$dyncols = $opts['subclass'] == 'it_dbi_postgres' ? 'dyncols JSONB' : 'dyncols JSON'; +$primarykey = $dbi->escape_name('ID'); +$dbi->query("create temporary table it_dbi_test ( + $autoid, x int, foo varchar(42), - dyncols JSON, - primary key(ID) -);'); -it_dbi::createclass(['table' => "it_dbi_test", 'forcecreate' => true]); + $dyncols, + primary key($primarykey) +);"); +$opts['subclass']::createclass(['table' => "it_dbi_test", 'forcecreate' => true]); $record = new it_dbi_test; @@ -160,23 +177,16 @@ $record->update(['foo' => "00"]); $rand = $record->x; is ( $record->_set(['x' => $rand, 'foo' => "0"]), - "SET `foo`='0'", + $record->_set(['foo' => "0"]), 'update: _set optimization' ); +$record->update(['foo' => '']); +ok(!$record->_set(['foo' => ""]), 'update: _set optimization no update "" => ""'); +ok($record->_set(['foo' => NULL]), 'update: _set optimization do update "" => NULL'); $record->update(['foo' => NULL]); -is ( - $record->_set(['foo' => ""]), - "SET `foo`=''", - 'update: _set optimization with NULL => ""' -); -$record->update(['foo' => "bar"]); -$record->update(['foo' => ""]); -is ( - $record->_set(['foo' => NULL]), - "SET `foo`=NULL", - 'update: _set optimization with "" => NULL' -); +ok($record->_set(['foo' => ""]), 'update: _set optimization do update NULL => ""'); +ok(!$record->_set(['foo' => NULL]), 'update: _set optimization no update NULL => NULL'); $record->update(['foo' => "bar"]); $record->select(['foo' => "bar"]); @@ -240,7 +250,7 @@ $GLOBALS['debug_sqllog'] = true; @$record->store(['ID' => 5, 'x' => 6]); like( $record->_sqllog[1]['query'], - "REPLACE", + "REPLACE|ON CONFLICT", "store => REPLACE for new entries" ); $record->clear(); @@ -277,19 +287,21 @@ $GLOBALS['debug_sqllog'] = false; # test latin1 (produces warnings on stderr for failing) -$record = new it_dbi_test(['charset' => 'latin1']); -$record->select(['foo' => "\xc3\x28"]); +#TODO this is not a proper test and the charset is currently used as dyncol +#$record = new it_dbi_test(['charset' => 'latin1']); +#$record->select(['foo' => "\xc3\x28"]); # Test field localization feature -$dbi->query('create temporary table it_dbi_testlocalized ( - ID int not null auto_increment, +$dbi->query("create temporary table it_dbi_testlocalized ( + $autoid, foobar_de varchar(42), foobar_fr varchar(42), - primary key(ID) -);'); + primary key($primarykey) +);"); + -$record = new it_dbi($db + ['table' => "it_dbi_testlocalized"]); +$record = new $opts['subclass']($db + ['table' => "it_dbi_testlocalized"]); $record->insert(['foobar_de' => "deutsch", 'foobar_fr' => "franz"]); $record->insert(['foobar_de' => "deutsch2", 'foobar_fr' => "franz2"]); @@ -351,21 +363,21 @@ $r->update(['key4' => "val4'", 'key5' => "val5'"]); is($r->_writes, 1); is($r- $r->update(['key4' => "val4'", 'key5' => "val5'"]); is($r->_writes, 1); is($r->key4, "val4'"); is($r->key5, "val5'"); # Check LIMIT -$count = $r->select(['ID >' => 0, 'ORDER BY ID DESC', 'LIMIT' => 1]); +$count = $r->select(['ID >' => 0, "ORDER BY $primarykey DESC", 'LIMIT' => 1]); is($count, 1, "Simple LIMIT: count"); is($r->ID, 6, "Simple LIMIT: id"); -$count = $r->select(['ID >' => 0, 'ORDER BY ID DESC', 'LIMIT' => "1, 2"]); +$count = $r->select(['ID >' => 0, "ORDER BY $primarykey DESC", 'LIMIT' => "1, 2"]); is($count, 2, "LIMIT with offset: count"); is($r->ID, 5, "LIMIT with offset: id"); -is($r->select(['ID >' => 0, 'ORDER BY ID DESC', 'LIMIT' => false]), 5, "no limit"); +is($r->select(['ID >' => 0, "ORDER BY $primarykey DESC", 'LIMIT' => false]), 5, "no limit"); # Check if iterator clears dynfields left over from previous record -$r->delete(["WHERE 1"]); +$r->delete(["WHERE TRUE"]); $r->replace(['ID' => 1, 'key1' => "a"]); $r->replace(['ID' => 2, 'key2' => "b"]); $r->replace(['ID' => 3]); $r->clear(false); -$r->select("WHERE 1 ORDER BY ID"); +$r->select("WHERE TRUE ORDER BY $primarykey"); $r->iterate(); is($r->key1, "a"); is($r->key2, null); $r->iterate(); @@ -395,13 +407,13 @@ is($r->_where(['LOWER(a)' => 'x']), "WHERE LOWER(a) = 'x'", "don't do JSON_EXTRA # function allrecs() { - foreach (new it_dbi_test([0 => "ORDER BY ID"]) as $r) + foreach (new it_dbi_test([0 => "ORDER BY $GLOBALS[primarykey]"]) as $r) $result[] = ['ID' => $r->ID, 'foo' => $r->foo]; return json_encode($result); } $record = new it_dbi_test; -$record->delete(['WHERE 1' ]); +$record->delete(['WHERE TRUE' ]); $record->upsert(['ID' => 1, 'foo' => "a"]); $record->upsert(['ID' => 2, 'foo' => "b |