it_dbi subclass for postgresql

1 files changed, 179 insertions, 0 deletions

diff --git a/it_dbi_postgres.class b/it_dbi_postgres.class
new file mode 100644
index 0000000..fff4a11
--- /dev/null
+++ b/it_dbi_postgres.class
@@ -0,0 +1,179 @@
+<?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 $singleton; // override base class var to get our own singleton
+
+function _where($params)
+{
+	if ($params['LIMIT'] && [$offset, $count] = it::match('^\s*(\d+)\s*,\s*(\d+)\s*$', $params['LIMIT'])) {
+		unset($params['LIMIT']);
+		$params[] = " OFFSET $offset LIMIT $count";
+	}
+	return parent::_where($params);
+}
+
+function replace($tags = [])
+{
+	$this->insert($tags, "INSERT", ' ON CONFLICT (' . $this->escape_name($this->_p['keyfield']) . ') DO UPDATE SET ' . implode(', ', it::map(fn ($k) => ($esc = $this->escape_name($k)) . " = EXCLUDED." . $esc, $this->_fields)));
+}
+
+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()
+{
+	$where = $this->_where('t.table_name' => $this->_p['table'], '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)
+{
+	return "JSONB_BUILD_OBJECT(" . implode(', ', it::map(fn ($f, $v) => "'$f', $v", (array)$tags)) . ")";
+}
+
+function _json_set($source, $tags)
+{
+	return "$source || " . $this->_json_object($tags);
+}
+
+function _json_remove($source, $fields)
+{
+	return "($source - " . implode(" - ", it::map(fn ($dummy, $f) => $this->escape_string($f), (array)$fields)) . ")";
+}
+
+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;
+}
+
+}