diff options
| -rw-r--r-- | it_dbi_postgres.class | 179 |
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; +} + +} |