QueryBuilder::build() - Code Metrics - Inspection of "expression support in orderBy and groupBy (#10693)" - yiisoft/yii2 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 2.1 ( 9e830b...25ac76 )

by Carsten

created 2016-05-01 02:59 UTC

QueryBuilder::build() B

↳ Parent: QueryBuilder

Complexity

Conditions	3
Paths	4

Size

Total Lines	25
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	17
CRAP Score	3

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
dl	0
loc	25
ccs	17
cts	17
cp	1
rs	8.8571
cc	3
eloc	16
nc	4
nop	2
crap	3

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace yii\db\sqlite;

use yii\db\Connection;
use yii\db\Exception;
use yii\base\InvalidParamException;
use yii\base\NotSupportedException;
use yii\db\Expression;
use yii\db\Query;

/**
 * QueryBuilder is the query builder for SQLite databases.
 *
 * @author Qiang Xue <[email protected]>
 * @since 2.0
 */
class QueryBuilder extends \yii\db\QueryBuilder
{
    /**
     * @var array mapping from abstract column types (keys) to physical column types (values).
     */
    public $typeMap = [
        Schema::TYPE_PK => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL',
        Schema::TYPE_UPK => 'integer UNSIGNED PRIMARY KEY AUTOINCREMENT NOT NULL',
        Schema::TYPE_BIGPK => 'bigint PRIMARY KEY AUTOINCREMENT NOT NULL',
        Schema::TYPE_UBIGPK => 'bigint UNSIGNED PRIMARY KEY AUTOINCREMENT NOT NULL',
        Schema::TYPE_CHAR => 'char(1)',
        Schema::TYPE_STRING => 'varchar(255)',
        Schema::TYPE_TEXT => 'text',
        Schema::TYPE_SMALLINT => 'smallint',
        Schema::TYPE_INTEGER => 'integer',
        Schema::TYPE_BIGINT => 'bigint',
        Schema::TYPE_FLOAT => 'float',
        Schema::TYPE_DOUBLE => 'double',
        Schema::TYPE_DECIMAL => 'decimal(10,0)',
        Schema::TYPE_DATETIME => 'datetime',
        Schema::TYPE_TIMESTAMP => 'timestamp',
        Schema::TYPE_TIME => 'time',
        Schema::TYPE_DATE => 'date',
        Schema::TYPE_BINARY => 'blob',
        Schema::TYPE_BOOLEAN => 'boolean',
        Schema::TYPE_MONEY => 'decimal(19,4)',
    ];


    /**
     * Generates a batch INSERT SQL statement.
     * For example,
     *
     * ```php
     * $connection->createCommand()->batchInsert('user', ['name', 'age'], [
     *     ['Tom', 30],
     *     ['Jane', 20],
     *     ['Linda', 25],
     * ])->execute();
     * ```
     *
     * Note that the values in each row must match the corresponding column names.
     *
     * @param string $table the table that new rows will be inserted into.
     * @param array $columns the column names
     * @param array $rows the rows to be batch inserted into the table
     * @return string the batch INSERT SQL statement
     */
    public function batchInsert($table, $columns, $rows)
    {
        // SQLite supports batch insert natively since 3.7.11
        // http://www.sqlite.org/releaselog/3_7_11.html
        $this->db->open(); // ensure pdo is not null
        if (version_compare($this->db->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '3.7.11', '>=')) {
            return parent::batchInsert($table, $columns, $rows);
        }

        $schema = $this->db->getSchema();
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;
        } else {
            $columnSchemas = [];
        }

        $values = [];
        foreach ($rows as $row) {
            $vs = [];
            foreach ($row as $i => $value) {
                if (!is_array($value) && isset($columnSchemas[$columns[$i]])) {
                    $value = $columnSchemas[$columns[$i]]->dbTypecast($value);
                }
                if (is_string($value)) {
                    $value = $schema->quoteValue($value);
                } elseif ($value === false) {
                    $value = 0;
                } elseif ($value === null) {
                    $value = 'NULL';
                }
                $vs[] = $value;
            }
            $values[] = implode(', ', $vs);
        }

        foreach ($columns as $i => $name) {
            $columns[$i] = $schema->quoteColumnName($name);
        }

        return 'INSERT INTO ' . $schema->quoteTableName($table)
        . ' (' . implode(', ', $columns) . ') SELECT ' . implode(' UNION SELECT ', $values);
    }

    /**
     * Creates a SQL statement for resetting the sequence value of a table's primary key.
     * The sequence will be reset such that the primary key of the next new row inserted
     * will have the specified value or 1.
     * @param string $tableName the name of the table whose primary key sequence will be reset
     * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
     * the next new row's primary key will have a value 1.
     * @return string the SQL statement for resetting sequence
     * @throws InvalidParamException if the table does not exist or there is no sequence associated with the table.
     */
    public function resetSequence($tableName, $value = null)
    {
        $db = $this->db;
        $table = $db->getTableSchema($tableName);
        if ($table !== null && $table->sequenceName !== null) {
            if ($value === null) {
                $key = reset($table->primaryKey);
                $tableName = $db->quoteTableName($tableName);
                $value = $this->db->useMaster(function (Connection $db) use ($key, $tableName) {
                    return $db->createCommand("SELECT MAX('$key') FROM $tableName")->queryScalar();
                });
            } else {
                $value = (int) $value - 1;
            }
            try {
                $db->createCommand("UPDATE sqlite_sequence SET seq='$value' WHERE name='{$table->name}'")->execute();
            } catch (Exception $e) {
                // it's possible that sqlite_sequence does not exist
            }
        } elseif ($table === null) {
            throw new InvalidParamException("Table not found: $tableName");
        } else {
            throw new InvalidParamException("There is not sequence associated with table '$tableName'.'");
        }
    }

    /**
     * Enables or disables integrity check.
     * @param boolean $check whether to turn on or off the integrity check.
     * @param string $schema the schema of the tables. Meaningless for SQLite.
     * @param string $table the table name. Meaningless for SQLite.
     * @return string the SQL statement for checking integrity
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function checkIntegrity($check = true, $schema = '', $table = '')
    {
        return 'PRAGMA foreign_keys='.(int) $check;
    }

    /**
     * Builds a SQL statement for truncating a DB table.
     * @param string $table the table to be truncated. The name will be properly quoted by the method.
     * @return string the SQL statement for truncating a DB table.
     */
    public function truncateTable($table)
    {
        return 'DELETE FROM ' . $this->db->quoteTableName($table);
    }

    /**
     * Builds a SQL statement for dropping an index.
     * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
     * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
     * @return string the SQL statement for dropping an index.
     */
    public function dropIndex($name, $table)
    {
        return 'DROP INDEX ' . $this->db->quoteTableName($name);
    }

    /**
     * Builds a SQL statement for dropping a DB column.
     * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method.
     * @param string $column the name of the column to be dropped. The name will be properly quoted by the method.
     * @return string the SQL statement for dropping a DB column.
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function dropColumn($table, $column)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * Builds a SQL statement for renaming a column.
     * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
     * @param string $oldName the old name of the column. The name will be properly quoted by the method.
     * @param string $newName the new name of the column. The name will be properly quoted by the method.
     * @return string the SQL statement for renaming a DB column.
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function renameColumn($table, $oldName, $newName)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * Builds a SQL statement for adding a foreign key constraint to an existing table.
     * The method will properly quote the table and column names.
     * @param string $name the name of the foreign key constraint.
     * @param string $table the table that the foreign key constraint will be added to.
     * @param string|array $columns the name of the column to that the constraint will be added on.
     * If there are multiple columns, separate them with commas or use an array to represent them.
     * @param string $refTable the table that the foreign key references to.
     * @param string|array $refColumns the name of the column that the foreign key references to.
     * If there are multiple columns, separate them with commas or use an array to represent them.
     * @param string $delete the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
     * @param string $update the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
     * @return string the SQL statement for adding a foreign key constraint to an existing table.
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * Builds a SQL statement for dropping a foreign key constraint.
     * @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
     * @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method.
     * @return string the SQL statement for dropping a foreign key constraint.
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function dropForeignKey($name, $table)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * Builds a SQL statement for renaming a DB table.
     *
     * @param string $table the table to be renamed. The name will be properly quoted by the method.
     * @param string $newName the new table name. The name will be properly quoted by the method.
     * @return string the SQL statement for renaming a DB table.
     */
    public function renameTable($table, $newName)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' RENAME TO ' . $this->db->quoteTableName($newName);
    }

    /**
     * Builds a SQL statement for changing the definition of a column.
     * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
     * @param string $column the name of the column to be changed. The name will be properly quoted by the method.
     * @param string $type the new column type. The [[getColumnType()]] method will be invoked to convert abstract
     * column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept
     * in the generated SQL. For example, 'string' will be turned into 'varchar(255)', while 'string not null'
     * will become 'varchar(255) not null'.
     * @return string the SQL statement for changing the definition of a column.
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function alterColumn($table, $column, $type)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * Builds a SQL statement for adding a primary key constraint to an existing table.
     * @param string $name the name of the primary key constraint.
     * @param string $table the table that the primary key constraint will be added to.
     * @param string|array $columns comma separated string or array of columns that the primary key will consist of.
     * @return string the SQL statement for adding a primary key constraint to an existing table.
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function addPrimaryKey($name, $table, $columns)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * Builds a SQL statement for removing a primary key constraint to an existing table.
     * @param string $name the name of the primary key constraint to be removed.
     * @param string $table the table that the primary key constraint will be removed from.
     * @return string the SQL statement for removing a primary key constraint from an existing table.
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function dropPrimaryKey($name, $table)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * @inheritdoc
     * @throws NotSupportedException
     * @since 2.0.8
     */
    public function addCommentOnColumn($table, $column, $comment)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * @inheritdoc
     * @throws NotSupportedException
     * @since 2.0.8
     */
    public function addCommentOnTable($table, $comment)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * @inheritdoc
     * @throws NotSupportedException
     * @since 2.0.8
     */
    public function dropCommentFromColumn($table, $column)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * @inheritdoc
     * @throws NotSupportedException
     * @since 2.0.8
     */
    public function dropCommentFromTable($table)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }

    /**
     * @inheritdoc
     */
    public function buildLimit($limit, $offset)
    {
        $sql = '';
        if ($this->hasLimit($limit)) {
            $sql = 'LIMIT ' . $limit;
            if ($this->hasOffset($offset)) {
                $sql .= ' OFFSET ' . $offset;
            }
        } elseif ($this->hasOffset($offset)) {
            // limit is not optional in SQLite
            // http://www.sqlite.org/syntaxdiagrams.html#select-stmt
            $sql = "LIMIT 9223372036854775807 OFFSET $offset"; // 2^63-1
        }

        return $sql;
    }

    /**
     * @inheritdoc
     * @throws NotSupportedException if `$columns` is an array
     */
    protected function buildSubqueryInCondition($operator, $columns, $values, &$params)
    {
        if (is_array($columns)) {
            throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
        }
        return parent::buildSubqueryInCondition($operator, $columns, $values, $params);
    }

    /**
     * Builds SQL for IN condition
     *
     * @param string $operator
     * @param array $columns
     * @param array $values
     * @param array $params
     * @return string SQL
     */
    protected function buildCompositeInCondition($operator, $columns, $values, &$params)
    {
        $quotedColumns = [];
        foreach ($columns as $i => $column) {
            $quotedColumns[$i] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column;
        }
        $vss = [];
        foreach ($values as $value) {
            $vs = [];
            foreach ($columns as $i => $column) {
                if (isset($value[$column])) {
                    $phName = self::PARAM_PREFIX . count($params);
                    $params[$phName] = $value[$column];
                    $vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' = ' : ' != ') . $phName;
                } else {
                    $vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' IS' : ' IS NOT') . ' NULL';
                }
            }
            $vss[] = '(' . implode($operator === 'IN' ? ' AND ' : ' OR ', $vs) . ')';
        }

        return '(' . implode($operator === 'IN' ? ' OR ' : ' AND ', $vss) . ')';
    }

    /**
     * @inheritdoc
     */
    public function build($query, $params = [])
    {
        $query = $query->prepare($this);

        $params = empty($params) ? $query->params : array_merge($params, $query->params);

        $clauses = [
            $this->buildSelect($query->select, $params, $query->distinct, $query->selectOption),
            $this->buildFrom($query->from, $params),
            $this->buildJoin($query->join, $params),
            $this->buildWhere($query->where, $params),
            $this->buildGroupBy($query->groupBy, $params),
            $this->buildHaving($query->having, $params),
        ];

        $sql = implode($this->separator, array_filter($clauses));
        $sql = $this->buildOrderByAndLimit($sql, $query->orderBy, $query->limit, $query->offset, $params);

        $union = $this->buildUnion($query->union, $params);
        if ($union !== '') {
            $sql = "$sql{$this->separator}$union";
        }

        return [$sql, $params];
    }

    /**
     * @inheritdoc
     */
    public function buildUnion($unions, &$params)
    {
        if (empty($unions)) {
            return '';
        }

        $result = '';

        foreach ($unions as $i => $union) {
            $query = $union['query'];
            if ($query instanceof Query) {
                list($unions[$i]['query'], $params) = $this->build($query, $params);
            }

            $result .= ' UNION ' . ($union['all'] ? 'ALL ' : '') . ' ' . $unions[$i]['query'];
        }

        return trim($result);
    }
}


1		<?php
2		/**
3		* @link http://www.yiiframework.com/
4		* @copyright Copyright (c) 2008 Yii Software LLC
5		* @license http://www.yiiframework.com/license/
6		*/
7
8		namespace yii\db\sqlite;
9
10		use yii\db\Connection;
11		use yii\db\Exception;
12		use yii\base\InvalidParamException;
13		use yii\base\NotSupportedException;
14		use yii\db\Expression;
15		use yii\db\Query;
16
17		/**
18		* QueryBuilder is the query builder for SQLite databases.
19		*
20		* @author Qiang Xue <[email protected]>
21		* @since 2.0
22		*/
23		class QueryBuilder extends \yii\db\QueryBuilder
24		{
25		/**
26		* @var array mapping from abstract column types (keys) to physical column types (values).
27		*/
28		public $typeMap = [
29		Schema::TYPE_PK => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL',
30		Schema::TYPE_UPK => 'integer UNSIGNED PRIMARY KEY AUTOINCREMENT NOT NULL',
31		Schema::TYPE_BIGPK => 'bigint PRIMARY KEY AUTOINCREMENT NOT NULL',
32		Schema::TYPE_UBIGPK => 'bigint UNSIGNED PRIMARY KEY AUTOINCREMENT NOT NULL',
33		Schema::TYPE_CHAR => 'char(1)',
34		Schema::TYPE_STRING => 'varchar(255)',
35		Schema::TYPE_TEXT => 'text',
36		Schema::TYPE_SMALLINT => 'smallint',
37		Schema::TYPE_INTEGER => 'integer',
38		Schema::TYPE_BIGINT => 'bigint',
39		Schema::TYPE_FLOAT => 'float',
40		Schema::TYPE_DOUBLE => 'double',
41		Schema::TYPE_DECIMAL => 'decimal(10,0)',
42		Schema::TYPE_DATETIME => 'datetime',
43		Schema::TYPE_TIMESTAMP => 'timestamp',
44		Schema::TYPE_TIME => 'time',
45		Schema::TYPE_DATE => 'date',
46		Schema::TYPE_BINARY => 'blob',
47		Schema::TYPE_BOOLEAN => 'boolean',
48		Schema::TYPE_MONEY => 'decimal(19,4)',
49		];
50
51
52		/**
53		* Generates a batch INSERT SQL statement.
54		* For example,
55		*
56		* ```php
57		* $connection->createCommand()->batchInsert('user', ['name', 'age'], [
58		* ['Tom', 30],
59		* ['Jane', 20],
60		* ['Linda', 25],
61		* ])->execute();
62		* ```
63		*
64		* Note that the values in each row must match the corresponding column names.
65		*
66		* @param string $table the table that new rows will be inserted into.
67		* @param array $columns the column names
68		* @param array $rows the rows to be batch inserted into the table
69		* @return string the batch INSERT SQL statement
70		*/
71	1	public function batchInsert($table, $columns, $rows)
72		{
73		// SQLite supports batch insert natively since 3.7.11
74		// http://www.sqlite.org/releaselog/3_7_11.html
75	1	$this->db->open(); // ensure pdo is not null
76	1	if (version_compare($this->db->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '3.7.11', '>=')) {
77	1	return parent::batchInsert($table, $columns, $rows);
78		}
79
80		$schema = $this->db->getSchema();
81		if (($tableSchema = $schema->getTableSchema($table)) !== null) {
82		$columnSchemas = $tableSchema->columns;
83		} else {
84		$columnSchemas = [];
85		}
86
87		$values = [];
88		foreach ($rows as $row) {
89		$vs = [];
90		foreach ($row as $i => $value) {
91		if (!is_array($value) && isset($columnSchemas[$columns[$i]])) {
92		$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
93		}
94		if (is_string($value)) {
95		$value = $schema->quoteValue($value);
96		} elseif ($value === false) {
97		$value = 0;
98		} elseif ($value === null) {
99		$value = 'NULL';
100		}
101		$vs[] = $value;
102		}
103		$values[] = implode(', ', $vs);
104		}
105
106		foreach ($columns as $i => $name) {
107		$columns[$i] = $schema->quoteColumnName($name);
108		}
109
110		return 'INSERT INTO ' . $schema->quoteTableName($table)
111		. ' (' . implode(', ', $columns) . ') SELECT ' . implode(' UNION SELECT ', $values);
112		}
113
114		/**
115		* Creates a SQL statement for resetting the sequence value of a table's primary key.
116		* The sequence will be reset such that the primary key of the next new row inserted
117		* will have the specified value or 1.
118		* @param string $tableName the name of the table whose primary key sequence will be reset
119		* @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
120		* the next new row's primary key will have a value 1.
121		* @return string the SQL statement for resetting sequence
122		* @throws InvalidParamException if the table does not exist or there is no sequence associated with the table.
123		*/
124		public function resetSequence($tableName, $value = null)
125		{
126		$db = $this->db;
127		$table = $db->getTableSchema($tableName);
128		if ($table !== null && $table->sequenceName !== null) {
129		if ($value === null) {
130		$key = reset($table->primaryKey);
131		$tableName = $db->quoteTableName($tableName);
132		$value = $this->db->useMaster(function (Connection $db) use ($key, $tableName) {
133		return $db->createCommand("SELECT MAX('$key') FROM $tableName")->queryScalar();
134		});
135		} else {
136		$value = (int) $value - 1;
137		}
138		try {
139		$db->createCommand("UPDATE sqlite_sequence SET seq='$value' WHERE name='{$table->name}'")->execute();
140		} catch (Exception $e) {
141		// it's possible that sqlite_sequence does not exist
142		}
143		} elseif ($table === null) {
144		throw new InvalidParamException("Table not found: $tableName");
145		} else {
146		throw new InvalidParamException("There is not sequence associated with table '$tableName'.'");
147		}
148		}
149
150		/**
151		* Enables or disables integrity check.
152		* @param boolean $check whether to turn on or off the integrity check.
153		* @param string $schema the schema of the tables. Meaningless for SQLite.
154		* @param string $table the table name. Meaningless for SQLite.
155		* @return string the SQL statement for checking integrity
156		* @throws NotSupportedException this is not supported by SQLite
157		*/
158		public function checkIntegrity($check = true, $schema = '', $table = '')
159		{
160		return 'PRAGMA foreign_keys='.(int) $check;
161		}
162
163		/**
164		* Builds a SQL statement for truncating a DB table.
165		* @param string $table the table to be truncated. The name will be properly quoted by the method.
166		* @return string the SQL statement for truncating a DB table.
167		*/
168	1	public function truncateTable($table)
169		{
170	1	return 'DELETE FROM ' . $this->db->quoteTableName($table);
171		}
172
173		/**
174		* Builds a SQL statement for dropping an index.
175		* @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
176		* @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
177		* @return string the SQL statement for dropping an index.
178		*/
179		public function dropIndex($name, $table)
180		{
181		return 'DROP INDEX ' . $this->db->quoteTableName($name);
182		}
183
184		/**
185		* Builds a SQL statement for dropping a DB column.
186		* @param string $table the table whose column is to be dropped. The name will be properly quoted by the method.
187		* @param string $column the name of the column to be dropped. The name will be properly quoted by the method.
188		* @return string the SQL statement for dropping a DB column.
189		* @throws NotSupportedException this is not supported by SQLite
190		*/
191		public function dropColumn($table, $column)
192		{
193		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
194		}
195
196		/**
197		* Builds a SQL statement for renaming a column.
198		* @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
199		* @param string $oldName the old name of the column. The name will be properly quoted by the method.
200		* @param string $newName the new name of the column. The name will be properly quoted by the method.
201		* @return string the SQL statement for renaming a DB column.
202		* @throws NotSupportedException this is not supported by SQLite
203		*/
204		public function renameColumn($table, $oldName, $newName)
205		{
206		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
207		}
208
209		/**
210		* Builds a SQL statement for adding a foreign key constraint to an existing table.
211		* The method will properly quote the table and column names.
212		* @param string $name the name of the foreign key constraint.
213		* @param string $table the table that the foreign key constraint will be added to.
214		* @param string\|array $columns the name of the column to that the constraint will be added on.
215		* If there are multiple columns, separate them with commas or use an array to represent them.
216		* @param string $refTable the table that the foreign key references to.
217		* @param string\|array $refColumns the name of the column that the foreign key references to.
218		* If there are multiple columns, separate them with commas or use an array to represent them.
219		* @param string $delete the ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
220		* @param string $update the ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
221		* @return string the SQL statement for adding a foreign key constraint to an existing table.
222		* @throws NotSupportedException this is not supported by SQLite
223		*/
224		public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
225		{
226		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
227		}
228
229		/**
230		* Builds a SQL statement for dropping a foreign key constraint.
231		* @param string $name the name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
232		* @param string $table the table whose foreign is to be dropped. The name will be properly quoted by the method.
233		* @return string the SQL statement for dropping a foreign key constraint.
234		* @throws NotSupportedException this is not supported by SQLite
235		*/
236		public function dropForeignKey($name, $table)
237		{
238		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
239		}
240
241		/**
242		* Builds a SQL statement for renaming a DB table.
243		*
244		* @param string $table the table to be renamed. The name will be properly quoted by the method.
245		* @param string $newName the new table name. The name will be properly quoted by the method.
246		* @return string the SQL statement for renaming a DB table.
247		*/
248	2	public function renameTable($table, $newName)
249		{
250	2	return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' RENAME TO ' . $this->db->quoteTableName($newName);
251		}
252
253		/**
254		* Builds a SQL statement for changing the definition of a column.
255		* @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
256		* @param string $column the name of the column to be changed. The name will be properly quoted by the method.
257		* @param string $type the new column type. The [[getColumnType()]] method will be invoked to convert abstract
258		* column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept
259		* in the generated SQL. For example, 'string' will be turned into 'varchar(255)', while 'string not null'
260		* will become 'varchar(255) not null'.
261		* @return string the SQL statement for changing the definition of a column.
262		* @throws NotSupportedException this is not supported by SQLite
263		*/
264		public function alterColumn($table, $column, $type)
265		{
266		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
267		}
268
269		/**
270		* Builds a SQL statement for adding a primary key constraint to an existing table.
271		* @param string $name the name of the primary key constraint.
272		* @param string $table the table that the primary key constraint will be added to.
273		* @param string\|array $columns comma separated string or array of columns that the primary key will consist of.
274		* @return string the SQL statement for adding a primary key constraint to an existing table.
275		* @throws NotSupportedException this is not supported by SQLite
276		*/
277		public function addPrimaryKey($name, $table, $columns)
278		{
279		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
280		}
281
282		/**
283		* Builds a SQL statement for removing a primary key constraint to an existing table.
284		* @param string $name the name of the primary key constraint to be removed.
285		* @param string $table the table that the primary key constraint will be removed from.
286		* @return string the SQL statement for removing a primary key constraint from an existing table.
287		* @throws NotSupportedException this is not supported by SQLite
288		*/
289		public function dropPrimaryKey($name, $table)
290		{
291		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
292		}
293
294		/**
295		* @inheritdoc
296		* @throws NotSupportedException
297		* @since 2.0.8
298		*/
299		public function addCommentOnColumn($table, $column, $comment)
300		{
301		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
302		}
303
304		/**
305		* @inheritdoc
306		* @throws NotSupportedException
307		* @since 2.0.8
308		*/
309		public function addCommentOnTable($table, $comment)
310		{
311		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
312		}
313
314		/**
315		* @inheritdoc
316		* @throws NotSupportedException
317		* @since 2.0.8
318		*/
319		public function dropCommentFromColumn($table, $column)
320		{
321		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
322		}
323
324		/**
325		* @inheritdoc
326		* @throws NotSupportedException
327		* @since 2.0.8
328		*/
329		public function dropCommentFromTable($table)
330		{
331		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
332		}
333
334		/**
335		* @inheritdoc
336		*/
337	203	public function buildLimit($limit, $offset)
338		{
339	203	$sql = '';
340	203	if ($this->hasLimit($limit)) {
341	16	$sql = 'LIMIT ' . $limit;
342	16	if ($this->hasOffset($offset)) {
343	1	$sql .= ' OFFSET ' . $offset;
344	1	}
345	203	} elseif ($this->hasOffset($offset)) {
346		// limit is not optional in SQLite
347		// http://www.sqlite.org/syntaxdiagrams.html#select-stmt
348	2	$sql = "LIMIT 9223372036854775807 OFFSET $offset"; // 2^63-1
349	2	}
350
351	203	return $sql;
352		}
353
354		/**
355		* @inheritdoc
356		* @throws NotSupportedException if `$columns` is an array
357		*/
358	2	protected function buildSubqueryInCondition($operator, $columns, $values, &$params)
359		{
360	2	if (is_array($columns)) {
361		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
362		}
363	2	return parent::buildSubqueryInCondition($operator, $columns, $values, $params);
364		}
365
366		/**
367		* Builds SQL for IN condition
368		*
369		* @param string $operator
370		* @param array $columns
371		* @param array $values
372		* @param array $params
373		* @return string SQL
374		*/
375	5	protected function buildCompositeInCondition($operator, $columns, $values, &$params)
376		{
377	5	$quotedColumns = [];
378	5	foreach ($columns as $i => $column) {
379	5	$quotedColumns[$i] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column;
380	5	}
381	5	$vss = [];
382	5	foreach ($values as $value) {
383	5	$vs = [];
384	5	foreach ($columns as $i => $column) {
385	5	if (isset($value[$column])) {
386	5	$phName = self::PARAM_PREFIX . count($params);
387	5	$params[$phName] = $value[$column];
388	5	$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' = ' : ' != ') . $phName;
389	5	} else {
390		$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' IS' : ' IS NOT') . ' NULL';
391		}
392	5	}
393	5	$vss[] = '(' . implode($operator === 'IN' ? ' AND ' : ' OR ', $vs) . ')';
394	5	}
395
396	5	return '(' . implode($operator === 'IN' ? ' OR ' : ' AND ', $vss) . ')';
397		}
398
399		/**
400		* @inheritdoc
401		*/
402	203	public function build($query, $params = [])
403		{
404	203	$query = $query->prepare($this);
405
406	203	$params = empty($params) ? $query->params : array_merge($params, $query->params);
407
408		$clauses = [
409	203	$this->buildSelect($query->select, $params, $query->distinct, $query->selectOption),
410	203	$this->buildFrom($query->from, $params),
411	203	$this->buildJoin($query->join, $params),
412	203	$this->buildWhere($query->where, $params),
413	203	$this->buildGroupBy($query->groupBy, $params),
414	203	$this->buildHaving($query->having, $params),
415	203	];
416
417	203	$sql = implode($this->separator, array_filter($clauses));
418	203	$sql = $this->buildOrderByAndLimit($sql, $query->orderBy, $query->limit, $query->offset, $params);
419
420	203	$union = $this->buildUnion($query->union, $params);
421	203	if ($union !== '') {
422	2	$sql = "$sql{$this->separator}$union";
423	2	}
424
425	203	return [$sql, $params];
426		}
427
428		/**
429		* @inheritdoc
430		*/
431	203	public function buildUnion($unions, &$params)
432		{
433	203	if (empty($unions)) {
434	203	return '';
435		}
436
437	2	$result = '';
438
439	2	foreach ($unions as $i => $union) {
440	2	$query = $union['query'];
441	2	if ($query instanceof Query) {
442	2	list($unions[$i]['query'], $params) = $this->build($query, $params);
443	2	}
444
445	2	$result .= ' UNION ' . ($union['all'] ? 'ALL ' : '') . ' ' . $unions[$i]['query'];
446	2	}
447
448	2	return trim($result);
449		}
450		}
451

yiisoft / yii2

Push — 2.1 ( 9e830b...25ac76 )

QueryBuilder::build() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like