From f91966b0de61778ca27bb22893c27dc0e1a80a30 Mon Sep 17 00:00:00 2001 From: Nathan Gass Date: Thu, 3 Sep 2020 11:08:06 +0200 Subject: it_dbi subclass for postgresql --- it_dbi_postgres.class | 179 ++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 179 insertions(+) create mode 100644 it_dbi_postgres.class (limited to 'it_dbi_postgres.class') 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 @@ +. +** +** 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; +} + +} -- cgit v1.2.3 From 75bbc82ca87fde88e5b9a8c902554a70831eb224 Mon Sep 17 00:00:00 2001 From: Nathan Gass Date: Thu, 3 Sep 2020 15:08:48 +0200 Subject: go back to $GLOBALS['it_dbi'] for backward compatibility --- it_dbi_postgres.class | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'it_dbi_postgres.class') diff --git a/it_dbi_postgres.class b/it_dbi_postgres.class index fff4a11..8fc29f1 100644 --- a/it_dbi_postgres.class +++ b/it_dbi_postgres.class @@ -22,7 +22,7 @@ class it_dbi_postgres extends it_dbi { -static $singleton; // override base class var to get our own singleton +static $_global_key = 'it_dbi_postgres'; // override base class to get our own singleton function _where($params) { -- cgit v1.2.3 From 0a1feb7c54e7167b0479fe116e0062017d9496b8 Mon Sep 17 00:00:00 2001 From: Nathan Gass Date: Thu, 3 Sep 2020 15:10:08 +0200 Subject: fix indentation --- it_dbi_postgres.class | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'it_dbi_postgres.class') diff --git a/it_dbi_postgres.class b/it_dbi_postgres.class index 8fc29f1..2f8eadb 100644 --- a/it_dbi_postgres.class +++ b/it_dbi_postgres.class @@ -62,7 +62,7 @@ function _get_field_defs() $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; } @@ -96,7 +96,7 @@ 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 @@ -111,7 +111,7 @@ function _query($query, $p) $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; -- cgit v1.2.3 From d3f0a6e4f822607b41d138afaa77081cab6afe5e Mon Sep 17 00:00:00 2001 From: Nathan Gass Date: Thu, 3 Sep 2020 15:52:06 +0200 Subject: avoid arrow functions as we support php 7.0 --- it_dbi_postgres.class | 14 +++++++++++--- 1 file changed, 11 insertions(+), 3 deletions(-) (limited to 'it_dbi_postgres.class') diff --git a/it_dbi_postgres.class b/it_dbi_postgres.class index 2f8eadb..b7b8851 100644 --- a/it_dbi_postgres.class +++ b/it_dbi_postgres.class @@ -35,7 +35,11 @@ function _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))); + foreach (array_keys($this->_fields) as $k) { + $escaped = $this->escape_name($k); + $strings[] = $escaped . '= EXCLUDED.' . $escaped; + } + $this->insert($tags, "INSERT", ' ON CONFLICT (' . $this->escape_name($this->_p['keyfield']) . ') DO UPDATE SET ' . implode(', ', $strings)); } function _tables($p = array()) @@ -137,7 +141,9 @@ function _json_extract($col, $field) function _json_object($tags) { - return "JSONB_BUILD_OBJECT(" . implode(', ', it::map(fn ($f, $v) => "'$f', $v", (array)$tags)) . ")"; + foreach ((array)$tags as $f => $v) + $strings[] = $this->escape_string($f) . ', ' . $v; + return "JSONB_BUILD_OBJECT(" . implode(', ', $strings) . ")"; } function _json_set($source, $tags) @@ -147,7 +153,9 @@ function _json_set($source, $tags) function _json_remove($source, $fields) { - return "($source - " . implode(" - ", it::map(fn ($dummy, $f) => $this->escape_string($f), (array)$fields)) . ")"; + foreach ((array)$fields as $f) + $strings[] = $this->escape_string($f); + return "($source - " . implode(" - ", $strings) . ")"; } function _fetch_assoc($res) -- cgit v1.2.3 From 6190f37f8a6b6dadccb0766326a0ad8781670f41 Mon Sep 17 00:00:00 2001 From: Nathan Gass Date: Thu, 3 Sep 2020 15:54:39 +0200 Subject: avoid forbidden syntax --- it_dbi_postgres.class | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'it_dbi_postgres.class') diff --git a/it_dbi_postgres.class b/it_dbi_postgres.class index b7b8851..e40729b 100644 --- a/it_dbi_postgres.class +++ b/it_dbi_postgres.class @@ -26,9 +26,9 @@ static $_global_key = 'it_dbi_postgres'; // override base class to get our own s function _where($params) { - if ($params['LIMIT'] && [$offset, $count] = it::match('^\s*(\d+)\s*,\s*(\d+)\s*$', $params['LIMIT'])) { + if ($params['LIMIT'] && ($m = it::match('^\s*(\d+)\s*,\s*(\d+)\s*$', $params['LIMIT']))) { unset($params['LIMIT']); - $params[] = " OFFSET $offset LIMIT $count"; + $params[] = " OFFSET $m[0] LIMIT $m[1]"; } return parent::_where($params); } @@ -44,7 +44,7 @@ function replace($tags = []) 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);) + 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; @@ -52,7 +52,7 @@ function _tables($p = array()) function _get_field_defs() { - $where = $this->_where('t.table_name' => $this->_p['table'], 't.table_catalog' => $this->_p['db']); + $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); -- cgit v1.2.3 From 11f530c6d129e0ff14a37445d28422ce5bc5f1e2 Mon Sep 17 00:00:00 2001 From: Nathan Gass Date: Wed, 6 Jan 2021 17:40:06 +0100 Subject: use numeric key for suffix instead of extra argument --- it_dbi_postgres.class | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'it_dbi_postgres.class') diff --git a/it_dbi_postgres.class b/it_dbi_postgres.class index e40729b..9192948 100644 --- a/it_dbi_postgres.class +++ b/it_dbi_postgres.class @@ -39,7 +39,7 @@ function replace($tags = []) $escaped = $this->escape_name($k); $strings[] = $escaped . '= EXCLUDED.' . $escaped; } - $this->insert($tags, "INSERT", ' ON CONFLICT (' . $this->escape_name($this->_p['keyfield']) . ') DO UPDATE SET ' . implode(', ', $strings)); + $this->insert(array_merge($tags, [' ON CONFLICT (' . $this->escape_name($this->_p['keyfield']) . ') DO UPDATE SET ' . implode(', ', $strings)])); } function _tables($p = array()) -- cgit v1.2.3 From 850c9fd1e027c736c7ba1fb1ed5bce1acb3d6a38 Mon Sep 17 00:00:00 2001 From: Nathan Gass Date: Fri, 8 Jan 2021 15:41:40 +0100 Subject: handle schemas in table names --- it_dbi_postgres.class | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'it_dbi_postgres.class') diff --git a/it_dbi_postgres.class b/it_dbi_postgres.class index 9192948..46c0393 100644 --- a/it_dbi_postgres.class +++ b/it_dbi_postgres.class @@ -52,7 +52,8 @@ function _tables($p = array()) function _get_field_defs() { - $where = $this->_where(['t.table_name' => $this->_p['table'], 't.table_catalog' => $this->_p['db']]); + 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); -- cgit v1.2.3