Merge branch 'ng/postgresql'

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-&g