Subversion-Projekte lars-tiefland.laravel_shop

<?php

namespace Illuminate\Database\Query\Grammars;

use Illuminate\Database\Query\Builder;

use Illuminate\Support\Arr;

use Illuminate\Support\Str;

class PostgresGrammar extends Grammar

{

    /**

     * All of the available clause operators.

     *

     * @var string[]

     */

    protected $operators = [

        '=', '<', '>', '<=', '>=', '<>', '!=',

        'like', 'not like', 'between', 'ilike', 'not ilike',

        '~', '&', '|', '#', '<<', '>>', '<<=', '>>=',

        '&&', '@>', '<@', '?', '?|', '?&', '||', '-', '@?', '@@', '#-',

        'is distinct from', 'is not distinct from',

    ];

    /**

     * The grammar specific bitwise operators.

     *

     * @var array

     */

    protected $bitwiseOperators = [

        '~', '&', '|', '#', '<<', '>>', '<<=', '>>=',

    ];

    /**

     * Compile a basic where clause.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $where

     * @return string

     */

    protected function whereBasic(Builder $query, $where)

    {

        if (str_contains(strtolower($where['operator']), 'like')) {

            return sprintf(

                '%s::text %s %s',

                $this->wrap($where['column']),

                $where['operator'],

                $this->parameter($where['value'])

            );

        }

        return parent::whereBasic($query, $where);

    }

    /**

     * Compile a bitwise operator where clause.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $where

     * @return string

     */

    protected function whereBitwise(Builder $query, $where)

    {

        $value = $this->parameter($where['value']);

        $operator = str_replace('?', '??', $where['operator']);

        return '('.$this->wrap($where['column']).' '.$operator.' '.$value.')::bool';

    }

    /**

     * Compile a "where date" clause.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $where

     * @return string

     */

    protected function whereDate(Builder $query, $where)

    {

        $value = $this->parameter($where['value']);

        return $this->wrap($where['column']).'::date '.$where['operator'].' '.$value;

    }

    /**

     * Compile a "where time" clause.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $where

     * @return string

     */

    protected function whereTime(Builder $query, $where)

    {

        $value = $this->parameter($where['value']);

        return $this->wrap($where['column']).'::time '.$where['operator'].' '.$value;

    }

    /**

     * Compile a date based where clause.

     *

     * @param  string  $type

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $where

     * @return string

     */

    protected function dateBasedWhere($type, Builder $query, $where)

    {

        $value = $this->parameter($where['value']);

        return 'extract('.$type.' from '.$this->wrap($where['column']).') '.$where['operator'].' '.$value;

    }

    /**

     * Compile a "where fulltext" clause.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $where

     * @return string

     */

    public function whereFullText(Builder $query, $where)

    {

        $language = $where['options']['language'] ?? 'english';

        if (! in_array($language, $this->validFullTextLanguages())) {

            $language = 'english';

        }

        $columns = collect($where['columns'])->map(function ($column) use ($language) {

            return "to_tsvector('{$language}', {$this->wrap($column)})";

        })->implode(' || ');

        $mode = 'plainto_tsquery';

        if (($where['options']['mode'] ?? []) === 'phrase') {

            $mode = 'phraseto_tsquery';

        }

        if (($where['options']['mode'] ?? []) === 'websearch') {

            $mode = 'websearch_to_tsquery';

        }

        return "({$columns}) @@ {$mode}('{$language}', {$this->parameter($where['value'])})";

    }

    /**

     * Get an array of valid full text languages.

     *

     * @return array

     */

    protected function validFullTextLanguages()

    {

        return [

            'simple',

            'arabic',

            'danish',

            'dutch',

            'english',

            'finnish',

            'french',

            'german',

            'hungarian',

            'indonesian',

            'irish',

            'italian',

            'lithuanian',

            'nepali',

            'norwegian',

            'portuguese',

            'romanian',

            'russian',

            'spanish',

            'swedish',

            'tamil',

            'turkish',

        ];

    }

    /**

     * Compile the "select *" portion of the query.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $columns

     * @return string|null

     */

    protected function compileColumns(Builder $query, $columns)

    {

        // If the query is actually performing an aggregating select, we will let that

        // compiler handle the building of the select clauses, as it will need some

        // more syntax that is best handled by that function to keep things neat.

        if (! is_null($query->aggregate)) {

            return;

        }

        if (is_array($query->distinct)) {

            $select = 'select distinct on ('.$this->columnize($query->distinct).') ';

        } elseif ($query->distinct) {

            $select = 'select distinct ';

        } else {

            $select = 'select ';

        }

        return $select.$this->columnize($columns);

    }

    /**

     * Compile a "JSON contains" statement into SQL.

     *

     * @param  string  $column

     * @param  string  $value

     * @return string

     */

    protected function compileJsonContains($column, $value)

    {

        $column = str_replace('->>', '->', $this->wrap($column));

        return '('.$column.')::jsonb @> '.$value;

    }

    /**

     * Compile a "JSON contains key" statement into SQL.

     *

     * @param  string  $column

     * @return string

     */

    protected function compileJsonContainsKey($column)

    {

        $segments = explode('->', $column);

        $lastSegment = array_pop($segments);

        if (filter_var($lastSegment, FILTER_VALIDATE_INT) !== false) {

            $i = $lastSegment;

        } elseif (preg_match('/\[(-?[0-9]+)\]$/', $lastSegment, $matches)) {

            $segments[] = Str::beforeLast($lastSegment, $matches[0]);

            $i = $matches[1];

        }

        $column = str_replace('->>', '->', $this->wrap(implode('->', $segments)));

        if (isset($i)) {

            return vsprintf('case when %s then %s else false end', [

                'jsonb_typeof(('.$column.")::jsonb) = 'array'",

                'jsonb_array_length(('.$column.')::jsonb) >= '.($i < 0 ? abs($i) : $i + 1),

            ]);

        }

        $key = "'".str_replace("'", "''", $lastSegment)."'";

        return 'coalesce(('.$column.')::jsonb ?? '.$key.', false)';

    }

    /**

     * Compile a "JSON length" statement into SQL.

     *

     * @param  string  $column

     * @param  string  $operator

     * @param  string  $value

     * @return string

     */

    protected function compileJsonLength($column, $operator, $value)

    {

        $column = str_replace('->>', '->', $this->wrap($column));

        return 'jsonb_array_length(('.$column.')::jsonb) '.$operator.' '.$value;

    }

    /**

     * Compile a single having clause.

     *

     * @param  array  $having

     * @return string

     */

    protected function compileHaving(array $having)

    {

        if ($having['type'] === 'Bitwise') {

            return $this->compileHavingBitwise($having);

        }

        return parent::compileHaving($having);

    }

    /**

     * Compile a having clause involving a bitwise operator.

     *

     * @param  array  $having

     * @return string

     */

    protected function compileHavingBitwise($having)

    {

        $column = $this->wrap($having['column']);

        $parameter = $this->parameter($having['value']);

        return '('.$column.' '.$having['operator'].' '.$parameter.')::bool';

    }

    /**

     * Compile the lock into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  bool|string  $value

     * @return string

     */

    protected function compileLock(Builder $query, $value)

    {

        if (! is_string($value)) {

            return $value ? 'for update' : 'for share';

        }

        return $value;

    }

    /**

     * Compile an insert ignore statement into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $values

     * @return string

     */

    public function compileInsertOrIgnore(Builder $query, array $values)

    {

        return $this->compileInsert($query, $values).' on conflict do nothing';

    }

    /**

     * Compile an insert and get ID statement into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $values

     * @param  string  $sequence

     * @return string

     */

    public function compileInsertGetId(Builder $query, $values, $sequence)

    {

        return $this->compileInsert($query, $values).' returning '.$this->wrap($sequence ?: 'id');

    }

    /**

     * Compile an update statement into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $values

     * @return string

     */

    public function compileUpdate(Builder $query, array $values)

    {

        if (isset($query->joins) || isset($query->limit)) {

            return $this->compileUpdateWithJoinsOrLimit($query, $values);

        }

        return parent::compileUpdate($query, $values);

    }

    /**

     * Compile the columns for an update statement.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $values

     * @return string

     */

    protected function compileUpdateColumns(Builder $query, array $values)

    {

        return collect($values)->map(function ($value, $key) {

            $column = last(explode('.', $key));

            if ($this->isJsonSelector($key)) {

                return $this->compileJsonUpdateColumn($column, $value);

            }

            return $this->wrap($column).' = '.$this->parameter($value);

        })->implode(', ');

    }

    /**

     * Compile an "upsert" statement into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $values

     * @param  array  $uniqueBy

     * @param  array  $update

     * @return string

     */

    public function compileUpsert(Builder $query, array $values, array $uniqueBy, array $update)

    {

        $sql = $this->compileInsert($query, $values);

        $sql .= ' on conflict ('.$this->columnize($uniqueBy).') do update set ';

        $columns = collect($update)->map(function ($value, $key) {

            return is_numeric($key)

                ? $this->wrap($value).' = '.$this->wrapValue('excluded').'.'.$this->wrap($value)

                : $this->wrap($key).' = '.$this->parameter($value);

        })->implode(', ');

        return $sql.$columns;

    }

    /**

     * Prepares a JSON column being updated using the JSONB_SET function.

     *

     * @param  string  $key

     * @param  mixed  $value

     * @return string

     */

    protected function compileJsonUpdateColumn($key, $value)

    {

        $segments = explode('->', $key);

        $field = $this->wrap(array_shift($segments));

        $path = "'{".implode(',', $this->wrapJsonPathAttributes($segments, '"'))."}'";

        return "{$field} = jsonb_set({$field}::jsonb, {$path}, {$this->parameter($value)})";

    }

    /**

     * Compile an update from statement into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $values

     * @return string

     */

    public function compileUpdateFrom(Builder $query, $values)

    {

        $table = $this->wrapTable($query->from);

        // Each one of the columns in the update statements needs to be wrapped in the

        // keyword identifiers, also a place-holder needs to be created for each of

        // the values in the list of bindings so we can make the sets statements.

        $columns = $this->compileUpdateColumns($query, $values);

        $from = '';

        if (isset($query->joins)) {

            // When using Postgres, updates with joins list the joined tables in the from

            // clause, which is different than other systems like MySQL. Here, we will

            // compile out the tables that are joined and add them to a from clause.

            $froms = collect($query->joins)->map(function ($join) {

                return $this->wrapTable($join->table);

            })->all();

            if (count($froms) > 0) {

                $from = ' from '.implode(', ', $froms);

            }

        }

        $where = $this->compileUpdateWheres($query);

        return trim("update {$table} set {$columns}{$from} {$where}");

    }

    /**

     * Compile the additional where clauses for updates with joins.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @return string

     */

    protected function compileUpdateWheres(Builder $query)

    {

        $baseWheres = $this->compileWheres($query);

        if (! isset($query->joins)) {

            return $baseWheres;

        }

        // Once we compile the join constraints, we will either use them as the where

        // clause or append them to the existing base where clauses. If we need to

        // strip the leading boolean we will do so when using as the only where.

        $joinWheres = $this->compileUpdateJoinWheres($query);

        if (trim($baseWheres) == '') {

            return 'where '.$this->removeLeadingBoolean($joinWheres);

        }

        return $baseWheres.' '.$joinWheres;

    }

    /**

     * Compile the "join" clause where clauses for an update.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @return string

     */

    protected function compileUpdateJoinWheres(Builder $query)

    {

        $joinWheres = [];

        // Here we will just loop through all of the join constraints and compile them

        // all out then implode them. This should give us "where" like syntax after

        // everything has been built and then we will join it to the real wheres.

        foreach ($query->joins as $join) {

            foreach ($join->wheres as $where) {

                $method = "where{$where['type']}";

                $joinWheres[] = $where['boolean'].' '.$this->$method($query, $where);

            }

        }

        return implode(' ', $joinWheres);

    }

    /**

     * Prepare the bindings for an update statement.

     *

     * @param  array  $bindings

     * @param  array  $values

     * @return array

     */

    public function prepareBindingsForUpdateFrom(array $bindings, array $values)

    {

        $values = collect($values)->map(function ($value, $column) {

            return is_array($value) || ($this->isJsonSelector($column) && ! $this->isExpression($value))

                ? json_encode($value)

                : $value;

        })->all();

        $bindingsWithoutWhere = Arr::except($bindings, ['select', 'where']);

        return array_values(

            array_merge($values, $bindings['where'], Arr::flatten($bindingsWithoutWhere))

        );

    }

    /**

     * Compile an update statement with joins or limit into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @param  array  $values

     * @return string

     */

    protected function compileUpdateWithJoinsOrLimit(Builder $query, array $values)

    {

        $table = $this->wrapTable($query->from);

        $columns = $this->compileUpdateColumns($query, $values);

        $alias = last(preg_split('/\s+as\s+/i', $query->from));

        $selectSql = $this->compileSelect($query->select($alias.'.ctid'));

        return "update {$table} set {$columns} where {$this->wrap('ctid')} in ({$selectSql})";

    }

    /**

     * Prepare the bindings for an update statement.

     *

     * @param  array  $bindings

     * @param  array  $values

     * @return array

     */

    public function prepareBindingsForUpdate(array $bindings, array $values)

    {

        $values = collect($values)->map(function ($value, $column) {

            return is_array($value) || ($this->isJsonSelector($column) && ! $this->isExpression($value))

                ? json_encode($value)

                : $value;

        })->all();

        $cleanBindings = Arr::except($bindings, 'select');

        return array_values(

            array_merge($values, Arr::flatten($cleanBindings))

        );

    }

    /**

     * Compile a delete statement into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @return string

     */

    public function compileDelete(Builder $query)

    {

        if (isset($query->joins) || isset($query->limit)) {

            return $this->compileDeleteWithJoinsOrLimit($query);

        }

        return parent::compileDelete($query);

    }

    /**

     * Compile a delete statement with joins or limit into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @return string

     */

    protected function compileDeleteWithJoinsOrLimit(Builder $query)

    {

        $table = $this->wrapTable($query->from);

        $alias = last(preg_split('/\s+as\s+/i', $query->from));

        $selectSql = $this->compileSelect($query->select($alias.'.ctid'));

        return "delete from {$table} where {$this->wrap('ctid')} in ({$selectSql})";

    }

    /**

     * Compile a truncate table statement into SQL.

     *

     * @param  \Illuminate\Database\Query\Builder  $query

     * @return array

     */

    public function compileTruncate(Builder $query)

    {

        return ['truncate '.$this->wrapTable($query->from).' restart identity cascade' => []];

    }

    /**

     * Wrap the given JSON selector.

     *

     * @param  string  $value

     * @return string

     */

    protected function wrapJsonSelector($value)

    {

        $path = explode('->', $value);

        $field = $this->wrapSegments(explode('.', array_shift($path)));

        $wrappedPath = $this->wrapJsonPathAttributes($path);

        $attribute = array_pop($wrappedPath);

        if (! empty($wrappedPath)) {

            return $field.'->'.implode('->', $wrappedPath).'->>'.$attribute;

        }

        return $field.'->>'.$attribute;

    }

    /**

     * Wrap the given JSON selector for boolean values.

     *

     * @param  string  $value

     * @return string

     */

    protected function wrapJsonBooleanSelector($value)

    {

        $selector = str_replace(

            '->>', '->',

            $this->wrapJsonSelector($value)

        );

        return '('.$selector.')::jsonb';

    }

    /**

     * Wrap the given JSON boolean value.

     *

     * @param  string  $value

     * @return string

     */

    protected function wrapJsonBooleanValue($value)

    {

        return "'".$value."'::jsonb";

    }

    /**

     * Wrap the attributes of the given JSON path.

     *

     * @param  array  $path

     * @return array

     */

    protected function wrapJsonPathAttributes($path)

    {

        $quote = func_num_args() === 2 ? func_get_arg(1) : "'";

        return collect($path)->map(function ($attribute) {

            return $this->parseJsonPathArrayKeys($attribute);

        })->collapse()->map(function ($attribute) use ($quote) {

            return filter_var($attribute, FILTER_VALIDATE_INT) !== false

                        ? $attribute

                        : $quote.$attribute.$quote;

        })->all();

    }

    /**

     * Parse the given JSON path attribute for array keys.

     *

     * @param  string  $attribute

     * @return array

     */

    protected function parseJsonPathArrayKeys($attribute)

    {

        if (preg_match('/(\[[^\]]+\])+$/', $attribute, $parts)) {

            $key = Str::beforeLast($attribute, $parts[0]);

            preg_match_all('/\[([^\]]+)\]/', $parts[0], $keys);

            return collect([$key])

                ->merge($keys[1])

                ->diff('')

                ->values()

                ->all();

        }

        return [$attribute];

    }

}