PuShaoWei / SimpleEloquent

src/query/Grammar.php

<?php
namespace Childish\query;

use Childish\support\Tools;
use Childish\support\Collection;

/**
 * Grammar
 *
 * @author    Pu ShaoWei <pushaowei520@gamil.com>
 * @date      2017/12/7
 * @package   Childish
 * @version   1.0
 */
abstract class Grammar
{
    /**
     * The grammar table prefix.
     *
     * @var string
     */
    protected $tablePrefix = '';

    /**
     * The components that make up a select clause.
     *
     * @var array
     */
    protected $selectComponents = [
        'aggregate',
        'columns',
        'from',
        'joins',
        'wheres',
        'groups',
        'havings',
        'orders',
        'limit',
        'offset',
        'unions',
        'lock',
    ];

    /**
     * Wrap an array of values.
     *
     * @param  array $values
     * @return array
     */
    public function wrapArray(array $values)
    {
        return array_map([$this, 'wrap'], $values);
    }

    /**
     * Wrap a table in keyword identifiers.
     *
     * @param  string $table
     * @return string
     */
    public function wrapTable($table)
    {
        return $this->wrap($this->tablePrefix . $table, true);
    }

    /**
     * Wrap a value in keyword identifiers.
     *
     * @param  string $value
     * @param  bool                                         $prefixAlias
     * @return string
     */
    public function wrap($value, $prefixAlias = false)
    {
        // If the value being wrapped has a column alias we will need to separate out
        // the pieces so we can wrap each of the segments of the expression on it
        // own, and then joins them both back together with the "as" connector.
        if (strpos(strtolower($value), ' as ') !== false) {
            return $this->wrapAliasedValue($value, $prefixAlias);
        }

        return $this->wrapSegments(explode('.', $value));
    }

    /**
     * Wrap a value that has an alias.
     *
     * @param  string $value
     * @param  bool   $prefixAlias
     * @return string
     */
    protected function wrapAliasedValue($value, $prefixAlias = false)
    {
        $segments = preg_split('/\s+as\s+/i', $value);

        // If we are wrapping a table we need to prefix the alias with the table prefix
        // as well in order to generate proper syntax. If this is a column of course
        // no prefix is necessary. The condition will be true when from wrapTable.
        if ($prefixAlias) {
            $segments[1] = $this->tablePrefix . $segments[1];
        }

        return $this->wrap(
                $segments[0]) . ' as ' . $this->wrapValue($segments[1]
            );
    }

    /**
     * Wrap the given value segments.
     *
     * @param  array $segments
     * @return string
     */
    protected function wrapSegments($segments)
    {
        return (new Collection($segments))->map(function ($segment, $key) use ($segments) {
            return $key == 0 && count($segments) > 1
                ? $this->wrapTable($segment)
                : $this->wrapValue($segment);
        })->implode('.');
    }

    /**
     * Wrap a single string in keyword identifiers.
     *
     * @param  string $value
     * @return string
     */
    protected function wrapValue($value)
    {
        if ($value !== '*') {
            return '"' . str_replace('"', '""', $value) . '"';
        }

        return $value;
    }

    /**
     * Convert an array of column names into a delimited string.
     *
     * @param  array $columns
     * @return string
     */
    public function columnize(array $columns)
    {
        return implode(', ', array_map([$this, 'wrap'], $columns));
    }

    /**
     * Create query parameter place-holders for an array.
     *
     * @param  array $values
     * @return string
     */
    public function parameterize(array $values)
    {
        return implode(', ', array_fill(0, count($values), '?'));
    }

    /**
     * Get the format for database stored dates.
     *
     * @return string
     */
    public function getDateFormat()
    {
        return 'Y-m-d H:i:s';
    }

    /**
     * Get the grammar's table prefix.
     *
     * @return string
     */
    public function getTablePrefix()
    {
        return $this->tablePrefix;
    }

    /**
     * Set the grammar's table prefix.
     *
     * @param  string $prefix
     * @return $this
     */
    public function setTablePrefix($prefix)
    {
        $this->tablePrefix = $prefix;

        return $this;
    }

    /**
     * Compile a select query into SQL.
     *
     * @param  \Childish\query\Builder $query
     * @return string
     */
    public function compileSelect(Builder $query)
    {
        // If the query does not have any columns set, we'll set the columns to the
        // * character to just get all of the columns from the database. Then we
        // can build the query and concatenate all the pieces together as one.
        $original = $query->columns;

        if (is_null($query->columns)) {
            $query->columns = ['*'];
        }

        // To compile the query, we'll spin through each component of the query and
        // see if that component exists. If it does we'll just call the compiler
        // function for the component which is responsible for making the SQL.
        $sql = trim($this->concatenate(
            $this->compileComponents($query))
        );

        $query->columns = $original;

        return $sql;
    }

    /**
     * Compile the components necessary for a select clause.
     *
     * @param  \Childish\query\Builder $query
     * @return array
     */
    protected function compileComponents(Builder $query)
    {
        $sql = [];

        foreach ($this->selectComponents as $component) {
            // To compile the query, we'll spin through each component of the query and
            // see if that component exists. If it does we'll just call the compiler
            // function for the component which is responsible for making the SQL.
            if (!is_null($query->$component)) {
                $method = 'compile' . ucfirst($component);

                $sql[$component] = $this->$method($query, $query->$component);
            }
        }

        return $sql;
    }

    /**
     * Compile an aggregated select clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $aggregate
     * @return string
     */
    protected function compileAggregate(Builder $query, $aggregate)
    {
        $column = $this->columnize($aggregate['columns']);

        // If the query has a "distinct" constraint and we're not asking for all columns
        // we need to prepend "distinct" onto the column name so that the query takes
        // it into account when it performs the aggregating operations on the data.
        if ($query->distinct && $column !== '*') {
            $column = 'distinct ' . $column;
        }

        return 'select ' . $aggregate['function'] . '(' . $column . ') as aggregate';
    }

    /**
     * Compile the "select *" portion of the query.
     *
     * @param  \Childish\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;
        }

        $select = $query->distinct ? 'select distinct ' : 'select ';

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

    /**
     * Compile the "from" portion of the query.
     *
     * @param  \Childish\query\Builder $query
     * @param  string                  $table
     * @return string
     */
    protected function compileFrom(Builder $query, $table)

The parameter $query is not used and could be removed.

    {
        return 'from ' . $this->wrapTable($table);
    }

    /**
     * Compile the "join" portions of the query.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $joins
     * @return string
     */
    protected function compileJoins(Builder $query, $joins)
    {
        return collect($joins)->map(function ($join) use ($query) {
            $table = $this->wrapTable($join->table);

            return trim("{$join->type} join {$table} {$this->compileWheres($join)}");
        })->implode(' ');
    }

    /**
     * Compile the "where" portions of the query.
     *
     * @param  \Childish\query\Builder $query
     * @return string
     */
    protected function compileWheres(Builder $query)
    {
        // Each type of where clauses has its own compiler function which is responsible
        // for actually creating the where clauses SQL. This helps keep the code nice
        // and maintainable since each clause has a very small method that it uses.
        if (is_null($query->wheres)) {
            return '';
        }

        // If we actually have some where clauses, we will strip off the first boolean
        // operator, which is added by the query builders for convenience so we can
        // avoid checking for the first clauses in each of the compilers methods.
        if (count($sql = $this->compileWheresToArray($query)) > 0) {
            return $this->concatenateWhereClauses($query, $sql);
        }

        return '';
    }

    /**
     * Get an array of all the where clauses for the query.
     *
     * @param  \Childish\query\Builder $query
     * @return array
     */
    protected function compileWheresToArray($query)
    {
        return (new Collection($query->wheres))->map(function ($where) use ($query) {
            return $where['boolean'] . ' ' . $this->{"where{$where['type']}"}($query, $where);
        })->all();
    }

    /**
     * Format the where clause statements into one string.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $sql
     * @return string
     */
    protected function concatenateWhereClauses($query, $sql)
    {
        $conjunction = $query instanceof JoinClause ? 'on' : 'where';

The class Childish\query\JoinClause does not exist. Did you forget a USE statement, or did you not list all dependencies?

        return $conjunction . ' ' . $this->removeLeadingBoolean(implode(' ', $sql));
    }

    /**
     * Compile a raw where clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereRaw(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return $where['sql'];
    }

    /**
     * Compile a basic where clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereBasic(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return $this->wrap($where['column']) . ' ' . $where['operator'] . ' ' . '?';
    }

    /**
     * Compile a "where in" clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereIn(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        if (!empty($where['values'])) {
            return $this->wrap($where['column']) . ' in (' . $this->parameterize($where['values']) . ')';
        }

        return '0 = 1';
    }

    /**
     * Compile a "where not in" clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereNotIn(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        if (!empty($where['values'])) {
            return $this->wrap($where['column']) . ' not in (' . $this->parameterize($where['values']) . ')';
        }

        return '1 = 1';
    }

    /**
     * Compile a where in sub-select clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereInSub(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return $this->wrap($where['column']) . ' in (' . $this->compileSelect($where['query']) . ')';
    }

    /**
     * Compile a where not in sub-select clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereNotInSub(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return $this->wrap($where['column']) . ' not in (' . $this->compileSelect($where['query']) . ')';
    }

    /**
     * Compile a "where null" clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereNull(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return $this->wrap($where['column']) . ' is null';
    }

    /**
     * Compile a "where not null" clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereNotNull(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return $this->wrap($where['column']) . ' is not null';
    }

    /**
     * Compile a "between" where clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereBetween(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        $between = $where['not'] ? 'not between' : 'between';

        return $this->wrap($where['column']) . ' ' . $between . ' ? and ?';
    }

    /**
     * Compile a "where date" clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereDate(Builder $query, $where)
    {
        return $this->dateBasedWhere('date', $query, $where);
    }

    /**
     * Compile a "where time" clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereTime(Builder $query, $where)
    {
        return $this->dateBasedWhere('time', $query, $where);
    }

    /**
     * Compile a "where day" clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereDay(Builder $query, $where)
    {
        return $this->dateBasedWhere('day', $query, $where);
    }

    /**
     * Compile a "where month" clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereMonth(Builder $query, $where)
    {
        return $this->dateBasedWhere('month', $query, $where);
    }

    /**
     * Compile a "where year" clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereYear(Builder $query, $where)
    {
        return $this->dateBasedWhere('year', $query, $where);
    }

    /**
     * Compile a date based where clause.
     *
     * @param  string                  $type
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function dateBasedWhere($type, Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return $type . '(' . $this->wrap($where['column']) . ') ' . $where['operator'] . ' ' . '?';
    }

    /**
     * Compile a where clause comparing two columns..
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereColumn(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return $this->wrap($where['first']) . ' ' . $where['operator'] . ' ' . $this->wrap($where['second']);
    }

    /**
     * Compile a nested where clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereNested(Builder $query, $where)
    {
        // Here we will calculate what portion of the string we need to remove. If this
        // is a join clause query, we need to remove the "on" portion of the SQL and
        // if it is a normal query we need to take the leading "where" of queries.
        $offset = $query instanceof JoinClause ? 3 : 6;

The class Childish\query\JoinClause does not exist. Did you forget a USE statement, or did you not list all dependencies?

        return '(' . substr($this->compileWheres($where['query']), $offset) . ')';
    }

    /**
     * Compile a where condition with a sub-select.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereSub(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        $select = $this->compileSelect($where['query']);

        return $this->wrap($where['column']) . ' ' . $where['operator'] . " ($select)";
    }

    /**
     * Compile a where exists clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereExists(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return 'exists (' . $this->compileSelect($where['query']) . ')';
    }

    /**
     * Compile a where exists clause.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $where
     * @return string
     */
    protected function whereNotExists(Builder $query, $where)

The parameter $query is not used and could be removed.

    {
        return 'not exists (' . $this->compileSelect($where['query']) . ')';
    }

    /**
     * Compile the "group by" portions of the query.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $groups
     * @return string
     */
    protected function compileGroups(Builder $query, $groups)

The parameter $query is not used and could be removed.

    {
        return 'group by ' . $this->columnize($groups);
    }

    /**
     * Compile the "having" portions of the query.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $havings
     * @return string
     */
    protected function compileHavings(Builder $query, $havings)

The parameter $query is not used and could be removed.

    {
        $sql = implode(' ', array_map([$this, 'compileHaving'], $havings));

        return 'having ' . $this->removeLeadingBoolean($sql);
    }

    /**
     * Compile a single having clause.
     *
     * @param  array $having
     * @return string
     */
    protected function compileHaving(array $having)
    {
        // If the having clause is "raw", we can just return the clause straight away
        // without doing any more processing on it. Otherwise, we will compile the
        // clause into SQL based on the components that make it up from builder.
        if ($having['type'] === 'Raw') {
            return $having['boolean'] . ' ' . $having['sql'];
        }

        return $this->compileBasicHaving($having);
    }

    /**
     * Compile a basic having clause.
     *
     * @param  array $having
     * @return string
     */
    protected function compileBasicHaving($having)
    {
        $column = $this->wrap($having['column']);


        return $having['boolean'] . ' ' . $column . ' ' . $having['operator'] . ' ' . '?';
    }

    /**
     * Compile the "order by" portions of the query.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $orders
     * @return string
     */
    protected function compileOrders(Builder $query, $orders)
    {
        if (!empty($orders)) {
            return 'order by ' . implode(', ', $this->compileOrdersToArray($query, $orders));
        }

        return '';
    }

    /**
     * Compile the query orders to an array.
     *
     * @param  \Childish\query\Builder
     * @param  array $orders
     * @return array
     */
    protected function compileOrdersToArray(Builder $query, $orders)

The parameter $query is not used and could be removed.

    {
        return array_map(function ($order) {
            return !isset($order['sql'])
                ? $this->wrap($order['column']) . ' ' . $order['direction']
                : $order['sql'];
        }, $orders);
    }

    /**
     * Compile the random statement into SQL.
     *
     * @param  string $seed
     * @return string
     */
    public function compileRandom($seed)
    {
        return 'RANDOM()';
    }

    /**
     * Compile the "limit" portions of the query.
     *
     * @param  \Childish\query\Builder $query
     * @param  int                     $limit
     * @return string
     */
    protected function compileLimit(Builder $query, $limit)

The parameter $query is not used and could be removed.

    {
        return 'limit ' . (int)$limit;
    }

    /**
     * Compile the "offset" portions of the query.
     *
     * @param  \Childish\query\Builder $query
     * @param  int                     $offset
     * @return string
     */
    protected function compileOffset(Builder $query, $offset)

The parameter $query is not used and could be removed.

    {
        return 'offset ' . (int)$offset;
    }

    /**
     * Compile the "union" queries attached to the main query.
     *
     * @param  \Childish\query\Builder $query
     * @return string
     */
    protected function compileUnions(Builder $query)
    {
        $sql = '';

        foreach ($query->unions as $union) {
            $sql .= $this->compileUnion($union);
        }

        if (!empty($query->unionOrders)) {
            $sql .= ' ' . $this->compileOrders($query, $query->unionOrders);
        }

        if (isset($query->unionLimit)) {
            $sql .= ' ' . $this->compileLimit($query, $query->unionLimit);
        }

        if (isset($query->unionOffset)) {
            $sql .= ' ' . $this->compileOffset($query, $query->unionOffset);
        }

        return ltrim($sql);
    }

    /**
     * Compile a single union statement.
     *
     * @param  array $union
     * @return string
     */
    protected function compileUnion(array $union)
    {
        $conjuction = $union['all'] ? ' union all ' : ' union ';

        return $conjuction . $union['query']->toSql();
    }

    /**
     * Compile an exists statement into SQL.
     *
     * @param  \Childish\query\Builder $query
     * @return string
     */
    public function compileExists(Builder $query)
    {
        $select = $this->compileSelect($query);

        return "select exists({$select}) as {$this->wrap('exists')}";
    }

    /**
     * Compile an insert statement into SQL.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $values
     * @return string
     */
    public function compileInsert(Builder $query, array $values)
    {
        // Essentially we will force every insert to be treated as a batch insert which
        // simply makes creating the SQL easier for us since we can utilize the same
        // basic routine regardless of an amount of records given to us to insert.
        $table = $this->wrapTable($query->from);

        if (!is_array(reset($values))) {
            $values = [$values];
        }

        $columns = $this->columnize(array_keys(reset($values)));

        // We need to build a list of parameter place-holders of values that are bound
        // to the query. Each insert should have the exact same amount of parameter
        // bindings so we will loop through the record and parameterize them all.

        $parameters = (new Collection($values))->map(function ($record) {
            return '(' . $this->parameterize($record) . ')';
        })->implode(', ');

        return "insert into $table ($columns) values $parameters";
    }

    /**
     * Compile an insert and get ID statement into SQL.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $values
     * @param  string                  $sequence
     * @return string
     */
    public function compileInsertGetId(Builder $query, $values, $sequence)

The parameter $sequence is not used and could be removed.

    {
        return $this->compileInsert($query, $values);
    }

    /**
     * Compile an update statement into SQL.
     *
     * @param  \Childish\query\Builder $query
     * @param  array                   $values
     * @return string
     */
    public function compileUpdate(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 = (new Collection($values))->map(function ($value, $key) {
            return $this->wrap($key) . ' = ?' ;
        })->implode(', ');

        // If the query has any "join" clauses, we will setup the joins on the builder
        // and compile them so we can attach them to this update, as update queries
        // can get join statements to attach to other tables when they're needed.
        $joins = '';

        if (isset($query->joins)) {
            $joins = ' ' . $this->compileJoins($query, $query->joins);
        }

        // Of course, update queries may also be constrained by where clauses so we'll
        // need to compile the where clauses and attach it to the query so only the
        // intended records are updated by the SQL statements we generate to run.
        $wheres = $this->compileWheres($query);

        return trim("update {$table}{$joins} set $columns $wheres");
    }

    /**
     * Prepare the bindings for an update statement.
     *
     * @param  array $bindings
     * @param  array $values
     * @return array
     */
    public function prepareBindingsForUpdate(array $bindings, array $values)
    {
        $cleanBindings = Tools::except($bindings, ['join', 'select']);

        return array_values(
            array_merge($bindings['join'], $values, Tools::flatten($cleanBindings))
        );
    }

    /**
     * Compile a delete statement into SQL.
     *
     * @param  \Childish\query\Builder $query
     * @return string
     */
    public function compileDelete(Builder $query)
    {
        $wheres = is_array($query->wheres) ? $this->compileWheres($query) : '';

        return trim("delete from {$this->wrapTable($query->from)} $wheres");
    }

    /**
     * Compile a truncate table statement into SQL.
     *
     * @param  \Childish\query\Builder $query
     * @return array
     */
    public function compileTruncate(Builder $query)
    {
        return ['truncate ' . $this->wrapTable($query->from) => []];
    }

    /**
     * Compile the lock into SQL.
     *
     * @param  \Childish\query\Builder $query
     * @param  bool|string             $value
     * @return string
     */
    protected function compileLock(Builder $query, $value)

The parameter $query is not used and could be removed.

    {
        return is_string($value) ? $value : '';
    }

    /**
     * Determine if the grammar supports savepoints.
     *
     * @return bool
     */
    public function supportsSavepoints()
    {
        return true;
    }

    /**
     * Compile the SQL statement to define a savepoint.
     *
     * @param  string $name
     * @return string
     */
    public function compileSavepoint($name)
    {
        return 'SAVEPOINT ' . $name;
    }

    /**
     * Compile the SQL statement to execute a savepoint rollback.
     *
     * @param  string $name
     * @return string
     */
    public function compileSavepointRollBack($name)
    {
        return 'ROLLBACK TO SAVEPOINT ' . $name;
    }

    /**
     * Concatenate an array of segments, removing empties.
     *
     * @param  array $segments
     * @return string
     */
    protected function concatenate($segments)
    {
        return implode(' ', array_filter($segments, function ($value) {
            return (string)$value !== '';
        }));
    }

    /**
     * Remove the leading boolean from a statement.
     *
     * @param  string $value
     * @return string
     */
    protected function removeLeadingBoolean($value)
    {
        return preg_replace('/and |or /i', '', $value, 1);
    }

    /**
     * Get the grammar specific operators.
     *
     * @return array
     */
    public function getOperators()
    {
        return $this->operators;

The property operators does not exist. Did you maybe forget to declare it?

    }
}