`yf_db_query_builder_pgsql` class

Query builder (Active Record) for pgsql.

File	`plugins/db/classes/db/yf_db_query_builder_pgsql.class.php`
Use	`_class('db_query_builder_pgsql', 'classes/db/')`
Extends	`yf_db_query_builder_driver`

Public properties

A site sets them in its config with $PROJECT_CONF['db_query_builder_pgsql']['NAME'] = ...;

Property	Default	Description
`$_pk_columns`	`[]`

Inherited properties

from yf_db_query_builder_driver: $SPLIT_WHERE_VALUES_BY_COMMA, $_model, $_qb_set, $_remove_as_from_delete, $_result_wrapper, $_sql, $_where_requested, $_with, $db

Public methods 8

None without a leading underscore.

Methods starting with `_` hooks and helpers; not routable as page actions

_insert_conflict

public function _insert_conflict($table, array $cols, $replace, $ignore, $on_duplicate_key_update)

replace and on_duplicate_key_update become upsert on the primary key (get_key_name()), ignore becomes DO NOTHING.

$cols array

    public function _insert_conflict($table, array $cols, $replace, $ignore, $on_duplicate_key_update)
    {
        $pk_cols = $this->_primary_key_columns($table);
        if (($replace || $on_duplicate_key_update) && $pk_cols) {
            $tmp = [];
            foreach ($cols as $col => $col_escaped) {
                if (in_array(trim($col, '"'), $pk_cols) || in_array(trim($col_escaped, '"'), $pk_cols)) {
                    continue;
                }
                $tmp[] = $col_escaped . ' = EXCLUDED.' . $col_escaped;
            }
            $target = PHP_EOL . ' ON CONFLICT (' . implode(', ', array_map([$this, '_escape_key'], $pk_cols)) . ')';
            // rows made of the key only have nothing to update, the duplicate is simply kept
            return $tmp ? $target . ' DO UPDATE SET ' . implode(', ', $tmp) : $target . ' DO NOTHING';
        }
        if ($ignore) {
            return PHP_EOL . ' ON CONFLICT DO NOTHING';
        }
        // replace without a primary key or without a known column list (insert_into with SELECT *, the key only):
        // no clause, so a conflict fails loudly instead of being skipped
        return '';
    }

_insert_id

public function _insert_id($table, $data)

lastval() is stale or undefined when the rows carry their own key, so the key comes from the data then (the last row, what the mysql builder test expects); for generated keys a multi-row insert reports the first one like MySQL.

    public function _insert_id($table, $data)
    {
        // the key lookup runs catalog queries, which must not clobber what the INSERT left in the driver
        $driver = $this->db->db;
        $last_result = $driver->_last_result ?? null;
        $last_affected = $driver->_last_affected_rows ?? null;
        $pk = $this->get_key_name($table);
        $driver->_last_result = $last_result;
        $driver->_last_affected_rows = $last_affected;
        $rows = is_array($data) && is_array(reset($data)) ? $data : [$data];
        $last_row = end($rows);
        if (is_array($last_row) && isset($last_row[$pk])) {
            return $last_row[$pk];
        }
        $last = $this->db->insert_id();
        return $last ? $last - count($rows) + 1 : $last;
    }

_insert_verb

public function _insert_verb($replace, $ignore, $delayed = false)

No REPLACE / DELAYED / IGNORE in Postgres, the duplicate handling goes to ON CONFLICT.

    public function _insert_verb($replace, $ignore, $delayed = false)
    {
        return 'INSERT';
    }

_primary_key_columns

public function _primary_key_columns($table)

Columns of the primary key (composite keys included), what ON CONFLICT needs; empty when the table has none.

    public function _primary_key_columns($table)
    {
        $table = (string) $table;
        if ( ! isset($this->_pk_columns[$table])) {
            $info = strlen($table) ? $this->db->utils()->index_info($table, 'PRIMARY') : [];
            $this->_pk_columns[$table] = array_values((array) ($info['columns'] ?? []));
        }
        return $this->_pk_columns[$table];
    }

_update_limit_supported

public function _update_limit_supported()

    public function _update_limit_supported()
    {
        return false;
    }

_update_limit_where

public function _update_limit_where($table, $where, $tail)

    public function _update_limit_where($table, $where, $tail)
    {
        $pk = $this->_escape_key($this->get_key_name($table));
        $t = $this->_escape_table_name($table);
        // InnoDB walks the primary key, Postgres has no stable row order without ORDER BY
        $order = stripos($tail, 'ORDER BY') === false ? ' ORDER BY ' . $pk : '';
        return $pk . ' IN (SELECT ' . $pk . ' FROM ' . $t . ' WHERE ' . $where . $order . ' ' . $tail . ')';
    }

_update_verb

public function _update_verb($ignore = false)

No UPDATE IGNORE in Postgres.

    public function _update_verb($ignore = false)
    {
        return 'UPDATE';
    }

_where_cond_sql

public function _where_cond_sql($left, $op, $right)

Postgres compares text only with text and is case sensitive: cast the column and use the case insensitive operators.

    public function _where_cond_sql($left, $op, $right)
    {
        $ops = ['LIKE' => 'ILIKE', 'NOT LIKE' => 'NOT ILIKE', 'RLIKE' => '~*', 'NOT RLIKE' => '!~*', 'REGEXP' => '~*', 'NOT REGEXP' => '!~*'];
        if (isset($ops[$op])) {
            return 'CAST(' . $left . ' AS TEXT) ' . $ops[$op] . $right;
        }
        return $left . ' ' . $op . $right;
    }

Inherited methods

from yf_db_query_builder_driver: __call(), __clone(), __toString(), _clone(), _escape_col_name(), _escape_expr(), _escape_key(), _escape_table_name(), _escape_val(), _get_sql_parts_config(), _get_update_batch_sql(), _has_where_sql(), _ids_sql_from_array(), _is_field_value_where(), _is_where_all_numeric(), _process_where(), _process_where_cond(), _refuse_dropped_where(), _render_from(), _render_joins(), _render_limit(), _render_order_by(), _render_select(), _render_where(), _set_update_batch_data(), _split_by_comma(), _split_in_list(), _split_where_by_comma(), _split_where_values(), _sql_part_to_array(), _sql_to_array(), all(), avg(), chunk(), compile_insert(), compile_update(), count(), decrement(), delete(), dump_json(), exec(), first(), from(), get(), get_2d(), get_all(), get_deep_array(), get_key_name(), get_model(), get_one(), get_table(), group_by(), having(), increment(), inner_join(), inner_join_raw(), insert(), insert_into(), join(), join_raw(), last(), left_join(), left_join_raw(), limit(), lock_for_update(), max(), min(), one(), or_where(), order_by(), render(), right_join(), right_join_raw(), select(), shared_lock(), sql(), subquery(), sum(), table(), union(), union_all(), update(), update_batch(), where(), where_all(), where_any(), where_between(), where_exists(), where_in(), where_not_exists(), where_not_in(), where_not_null(), where_null(), where_or(), where_raw(), whereid(), wherenotid()

yf_db_query_builder_pgsql class