canax / database-query-builder

src/DatabaseQueryBuilder/QueryBuilderTrait.php

<?php

namespace Anax\DatabaseQueryBuilder;

use Anax\DatabaseQueryBuilder\Exception\BuildException;

/**
 * Trait to implement building SQL queries by method calling.
 */
trait QueryBuilderTrait
{
    /**
     * @var $dialect current database dialect used

The doc comment $dialect at position 0 could not be parsed: Unknown type name '$dialect' at position 0 in $dialect.

     * @var $sql     the query built
     * @var $prefix  prefix to attach to all table names
     */
    private $dialect;
    private $sql;
    private $prefix;

    /**
     * @var $start   first line of the sql query

The doc comment $start at position 0 could not be parsed: Unknown type name '$start' at position 0 in $start.

     * @var $from    from part
     * @var $join    join part
     * @var $set     set part for a update
     * @var $where   where part
     * @var $groupby group part
     * @var $orderby order part
     * @var $limit   limit part
     * @var $offset  offset part
     */
    private $start;
    private $from;
    private $join;
    private $set;
    private $where;
    private $groupby;
    private $orderby;
    private $limit;
    private $offset;



    /**
     * Get SQL.
     *
     * @return string with the built sql-query
     */
    public function getSQL()
    {
        if ($this->sql) {
            return $this->sql;
        }
        return $this->build();
    }



    /**
     * Build the SQL query from its parts.
     *
     * @return string as SQL query
     */
    protected function build()
    {
        $sql = $this->start . "\n"
            . ($this->from    ? $this->from . "\n"    : null)
            . ($this->join    ? $this->join           : null)
            . ($this->set     ? $this->set . "\n"     : null)
            . ($this->where   ? $this->where . "\n"   : null)
            . ($this->groupby ? $this->groupby . "\n" : null)
            . ($this->orderby ? $this->orderby . "\n" : null)
            . ($this->limit   ? $this->limit . "\n"   : null)
            . ($this->offset  ? $this->offset . "\n"  : null)
            . ";";

        return $sql;
    }



    /**
     * Clear all previous sql-code.
     *
     * @return void
     */
    protected function clear()
    {
        $this->sql      = null;
        $this->start    = null;
        $this->from     = null;
        $this->join     = null;
        $this->set      = null;
        $this->where    = null;
        $this->groupby  = null;
        $this->orderby  = null;
        $this->limit    = null;
        $this->offset   = null;
    }



    /**
     * Set database type/dialect to consider when generating SQL.
     *
     * @param string $dialect representing database type.
     *
     * @return self
     */
    public function setSQLDialect($dialect)
    {
        $this->dialect = $dialect;
        return $this;
    }



    /**
     * Set a table prefix.
     *
     * @param string $prefix to use in front of all tables.
     *
     * @return self
     */
    public function setTablePrefix($prefix)
    {
        $this->prefix = $prefix;
        return $this;
    }



    /**
     * Create a table.
     *
     * @param string $name    the table name.
     * @param array  $columns the columns in the table.
     *
     * @return $this
     */
    public function createTable($name, $columns)
    {
        $cols = null;

        foreach ($columns as $col => $options) {
            $cols .= "\t" . $col . ' ' . implode(' ', $options) . ",\n";
        }
        $cols = substr($cols, 0, -2);

        $this->sql = "CREATE TABLE "
            . $this->prefix
            . $name
            . "\n(\n"
            . $cols
            . "\n);\n";

        if ($this->dialect == 'sqlite') {
            $this->sql = str_replace('auto_increment', '', $this->sql);
        }

        return $this;
    }



    /**
     * Drop a table.
     *
     * @param string $name the table name.
     *
     * @return $this
     */
    public function dropTable($name)
    {
        $this->sql = "DROP TABLE "
            . $this->prefix
            . $name
            . ";\n";

        return $this;
    }



    /**
     * Drop a table if it exists.
     *
     * @param string $name the table name.
     *
     * @return $this
     */
    public function dropTableIfExists($name)
    {
        $this->sql = "DROP TABLE IF EXISTS "
            . $this->prefix
            . $name
            . ";\n";

        return $this;
    }



    /**
     * Build a insert-query.
     *
     * @param string $table   the table name.
     * @param array  $columns to insert och key=>value with columns and values.
     * @param array  $values  to insert or empty if $columns has both
     *                        columns and values.
     *
     * @throws \Anax\Database\BuildException
     *
     * @return self for chaining
     */
    public function insert($table, $columns, $values = null)
    {
        list($columns, $values) = $this->mapColumnsWithValues($columns, $values);

        if (count($columns) !== count($values)) {
            throw new BuildException("Columns does not match values, not equal items.");
        }

        $cols = null;
        $vals = null;

        $max = count($columns);
        for ($i = 0; $i < $max; $i++) {
            $cols .= $columns[$i] . ', ';

            $val = $values[$i];

            if ($val == '?') {
                $vals .= $val . ', ';
            } else {
                $vals .= (is_string($val)
                    ? "'$val'"
                    : $val)
                    . ', ';
            }
        }

        $cols = substr($cols, 0, -2);
        $vals = substr($vals, 0, -2);

        $this->sql = "INSERT INTO "
            . $this->prefix
            . $table
            . "\n\t("
            . $cols
            . ")\n"
            . "\tVALUES\n\t("
            . $vals
            . ");\n";

        return $this;
    }



    /**
     * Build an update-query.
     *
     * @param string $table   the table name.
     * @param array  $columns to update or key=>value with columns and values.
     * @param array  $values  to update or empty if $columns has bot columns and values.
     *
     * @throws \Anax\Database\BuildException
     *
     * @return void
     */
    public function update($table, $columns, $values = null)
    {
        $this->clear();
        list($columns, $values) = $this->mapColumnsWithValues($columns, $values);

        if (count($columns) != count($values)) {
            throw new BuildException("Columns does not match values, not equal items.");
        }

        $cols = null;
        $max = count($columns);
        
        for ($i = 0; $i < $max; $i++) {
            $cols .= "\t" . $columns[$i] . ' = ';

            $val = $values[$i];
            if ($val == '?') {
                $cols .= $val . ",\n";
            } else {
                $cols .= (is_string($val)
                    ? "'$val'"
                    : $val)
                    . ",\n";
            }
        }

        $cols = substr($cols, 0, -2);

        $this->start = "UPDATE "
            . $this->prefix
            . $table;
        $this->set = "SET\n$cols";

        return $this;

The expression return $this returns the type Anax\DatabaseQueryBuilder\QueryBuilderTrait which is incompatible with the documented return type void.

    }



    /**
     * Build a delete-query.
     *
     * @param string $table the table name.
     * @param array  $where limit which rows are updated.
     *
     * @return self
     */
    public function deleteFrom($table, $where = null)
    {
        $this->clear();

        if (isset($where)) {
            $this->where = "WHERE\n\t(" . $where . ")";

Are you sure $where of type array can be used in concatenation?

        }

        $this->start = "DELETE";
        $this->from($table);
        return $this;
    }



    /**
     * Build a select-query.
     *
     * @param string $columns which columns to select.
     *
     * @return $this
     */
    public function select($columns = '*')
    {
        $this->clear();
        $this->start = "SELECT\n\t$columns";
        return $this;
    }



    /**
     * Build the from part.
     *
     * @param string $table name of table.
     *
     * @return $this
     */
    public function from($table)
    {
        $this->from = "FROM " . $this->prefix . $table;
        return $this;
    }



    /**
     * Build the inner join part.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     *
     * @return $this
     */
    public function join($table, $condition)
    {

        return $this->createJoin($table, $condition, 'INNER');

The expression return $this->createJoin($table, $condition, 'INNER') returns the type void which is incompatible with the documented return type Anax\DatabaseQueryBuilder\QueryBuilderTrait.

Are you sure the usage of $this->createJoin($table, $condition, 'INNER') targeting Anax\DatabaseQueryBuilder\QueryBuilderTrait::createJoin() seems to always return null.

    }



    /**
     * Build the right join part.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     *
     * @throws \Anax\Database\BuildException when dialect does not support.
     *
     * @return $this
     */
    public function rightJoin($table, $condition)
    {
        if ($this->dialect == 'sqlite') {
            throw new BuildException("SQLite does not support RIGHT JOIN");
        }

        return $this->createJoin($table, $condition, 'RIGHT OUTER');

Are you sure the usage of $this->createJoin($table, $condition, 'RIGHT OUTER') targeting Anax\DatabaseQueryBuilder\QueryBuilderTrait::createJoin() seems to always return null.

The expression return $this->createJoin($table, $condition, 'RIGHT OUTER') returns the type void which is incompatible with the documented return type Anax\DatabaseQueryBuilder\QueryBuilderTrait.

    }



    /**
     * Build the left join part.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     *
     * @return $this
     */
    public function leftJoin($table, $condition)
    {
        return $this->createJoin($table, $condition, 'LEFT OUTER');

The expression return $this->createJoin($table, $condition, 'LEFT OUTER') returns the type void which is incompatible with the documented return type Anax\DatabaseQueryBuilder\QueryBuilderTrait.

Are you sure the usage of $this->createJoin($table, $condition, 'LEFT OUTER') targeting Anax\DatabaseQueryBuilder\QueryBuilderTrait::createJoin() seems to always return null.

    }



    /**
     * Create a inner or outer join.
     *
     * @param string $table     name of table.
     * @param string $condition to join.
     * @param string $type      what type of join to create.
     *
     * @return void
     */
    private function createJoin($table, $condition, $type)
    {
        $this->join .= $type
            . " JOIN " . $this->prefix . $table
            . "\n\tON " . $condition . "\n";

        return $this;

The expression return $this returns the type Anax\DatabaseQueryBuilder\QueryBuilderTrait which is incompatible with the documented return type void.

    }



    /**
     * Build the where part.
     *
     * @param string $condition for building the where part of the query.
     *
     * @return $this
     */
    public function where($condition)
    {
        $this->where = "WHERE\n\t(" . $condition . ")";

        return $this;
    }



    /**
     * Build the where part with conditions.
     *
     * @param string $condition for building the where part of the query.
     *
     * @return $this
     */
    public function andWhere($condition)
    {
        $this->where .= "\n\tAND (" . $condition . ")";

        return $this;
    }



    /**
    * Build the group by part.
    *
    * @param string $condition for building the group by part of the query.
    *
    * @return $this
    */
    public function groupBy($condition)
    {
        $this->groupby = "GROUP BY " . $condition;

        return $this;
    }



    /**
    * Build the order by part.
    *
    * @param string $condition for building the where part of the query.
    *
    * @return $this
    */
    public function orderBy($condition)
    {
        $this->orderby = "ORDER BY " . $condition;

        return $this;
    }



    /**
     * Build the LIMIT by part.
     *
     * @param string $condition for building the LIMIT part of the query.
     *
     * @return $this
     */
    public function limit($condition)
    {
        $this->limit = "LIMIT \n\t" . intval($condition);

        return $this;
    }



    /**
     * Build the OFFSET by part.
     *
     * @param string $condition for building the OFFSET part of the query.
     *
     * @return $this
     */
    public function offset($condition)
    {
        $this->offset = "OFFSET \n\t" . intval($condition);

        return $this;
    }



    /**
     * Create a proper column value arrays from incoming $columns and $values.
     *
     * @param array       $columns
     * @param array|null  $values
     *
     * @return array that can be parsed with list($columns, $values)
     */
    public function mapColumnsWithValues($columns, $values)
    {
        // If $values is null, then use $columns to build it up
        if (is_null($values)) {
            if ($this->isAssoc($columns)) {
                // Incoming is associative array, split it up in two
                $values = array_values($columns);
                $columns = array_keys($columns);
            } else {
                // Create an array of '?' to match number of columns
                $max = count($columns);
                for ($i = 0; $i < $max; $i++) {
                    $values[] = '?';
                }
            }
        }

        return [$columns, $values];
    }



    /**
     * Utility to check if array is associative array.
     *
     * http://stackoverflow.com/questions/173400/php-arrays-a-good-way-to-check-if-an-array-is-associative-or-sequential/4254008#4254008
     *
     * @param array $array input array to check.
     *
     * @return boolean true if array is associative array with at least
     *                      one key, else false.
     *
     */
    private function isAssoc($array)
    {
        return (bool) count(array_filter(array_keys($array), 'is_string'));
    }
}