QueryBuilder.php 11 KB
Newer Older
Qiang Xue committed
1 2 3
<?php
/**
 * @link http://www.yiiframework.com/
Qiang Xue committed
4
 * @copyright Copyright (c) 2008 Yii Software LLC
Qiang Xue committed
5 6 7
 * @license http://www.yiiframework.com/license/
 */

Qiang Xue committed
8
namespace yii\db\sqlite;
Qiang Xue committed
9 10

use yii\db\Exception;
Qiang Xue committed
11
use yii\base\InvalidParamException;
Qiang Xue committed
12
use yii\base\NotSupportedException;
Qiang Xue committed
13 14

/**
Qiang Xue committed
15
 * QueryBuilder is the query builder for SQLite databases.
Qiang Xue committed
16 17 18 19
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
Qiang Xue committed
20
class QueryBuilder extends \yii\db\QueryBuilder
Qiang Xue committed
21 22 23 24
{
	/**
	 * @var array mapping from abstract column types (keys) to physical column types (values).
	 */
Alexander Makarov committed
25
	public $typeMap = [
26
		Schema::TYPE_PK => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL',
27
		Schema::TYPE_BIGPK => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL',
Qiang Xue committed
28 29 30 31 32 33
		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',
34
		Schema::TYPE_DECIMAL => 'decimal(10,0)',
Qiang Xue committed
35 36 37 38 39
		Schema::TYPE_DATETIME => 'datetime',
		Schema::TYPE_TIMESTAMP => 'timestamp',
		Schema::TYPE_TIME => 'time',
		Schema::TYPE_DATE => 'date',
		Schema::TYPE_BINARY => 'blob',
40
		Schema::TYPE_BOOLEAN => 'boolean',
Qiang Xue committed
41
		Schema::TYPE_MONEY => 'decimal(19,4)',
Alexander Makarov committed
42
	];
Qiang Xue committed
43

44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
	/**
	 * Generates a batch INSERT SQL statement.
	 * For example,
	 *
	 * ~~~
	 * $connection->createCommand()->batchInsert('tbl_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)
	{
		if (($tableSchema = $this->db->getTableSchema($table)) !== null) {
			$columnSchemas = $tableSchema->columns;
		} else {
			$columnSchemas = [];
		}

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

		$values = [];
		foreach ($rows as $row) {
			$vs = [];
			foreach ($row as $i => $value) {
				if (!is_array($value) && isset($columnSchemas[$columns[$i]])) {
					$value = $columnSchemas[$columns[$i]]->typecast($value);
				}
				$vs[] = is_string($value) ? $this->db->quoteValue($value) : $value;
			}
			$values[] = implode(', ', $vs);
		}

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

Qiang Xue committed
91
	/**
Qiang Xue committed
92
	 * Creates a SQL statement for resetting the sequence value of a table's primary key.
Qiang Xue committed
93 94
	 * The sequence will be reset such that the primary key of the next new row inserted
	 * will have the specified value or 1.
Qiang Xue committed
95
	 * @param string $tableName the name of the table whose primary key sequence will be reset
Qiang Xue committed
96 97
	 * @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.
Qiang Xue committed
98
	 * @return string the SQL statement for resetting sequence
Qiang Xue committed
99
	 * @throws InvalidParamException if the table does not exist or there is no sequence associated with the table.
Qiang Xue committed
100
	 */
Qiang Xue committed
101
	public function resetSequence($tableName, $value = null)
Qiang Xue committed
102
	{
Qiang Xue committed
103
		$db = $this->db;
Qiang Xue committed
104 105
		$table = $db->getTableSchema($tableName);
		if ($table !== null && $table->sequenceName !== null) {
Qiang Xue committed
106
			if ($value === null) {
Qiang Xue committed
107 108 109
				$key = reset($table->primaryKey);
				$tableName = $db->quoteTableName($tableName);
				$value = $db->createCommand("SELECT MAX('$key') FROM $tableName")->queryScalar();
Qiang Xue committed
110
			} else {
Qiang Xue committed
111 112 113
				$value = (int)$value - 1;
			}
			try {
Qiang Xue committed
114
				$db->createCommand("UPDATE sqlite_sequence SET seq='$value' WHERE name='{$table->name}'")->execute();
Qiang Xue committed
115
			} catch (Exception $e) {
116
				// it's possible that sqlite_sequence does not exist
Qiang Xue committed
117
			}
Qiang Xue committed
118
		} elseif ($table === null) {
Qiang Xue committed
119
			throw new InvalidParamException("Table not found: $tableName");
Qiang Xue committed
120
		} else {
Qiang Xue committed
121
			throw new InvalidParamException("There is not sequence associated with table '$tableName'.'");
Qiang Xue committed
122 123 124 125 126 127
		}
	}

	/**
	 * Enables or disables integrity check.
	 * @param boolean $check whether to turn on or off the integrity check.
resurtm committed
128 129
	 * @param string $schema the schema of the tables. Meaningless for SQLite.
	 * @param string $table the table name. Meaningless for SQLite.
Alexander Mohorev committed
130
	 * @return string the SQL statement for checking integrity
Qiang Xue committed
131
	 * @throws NotSupportedException this is not supported by SQLite
Qiang Xue committed
132
	 */
resurtm committed
133
	public function checkIntegrity($check = true, $schema = '', $table = '')
Qiang Xue committed
134
	{
Qiang Xue committed
135
		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
Qiang Xue committed
136 137 138 139 140 141 142 143 144
	}

	/**
	 * 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)
	{
Qiang Xue committed
145
		return "DELETE FROM " . $this->db->quoteTableName($table);
Qiang Xue committed
146 147 148 149 150 151 152 153 154 155
	}

	/**
	 * 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)
	{
Qiang Xue committed
156
		return 'DROP INDEX ' . $this->db->quoteTableName($name);
Qiang Xue committed
157 158 159 160 161 162 163
	}

	/**
	 * 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.
Qiang Xue committed
164
	 * @throws NotSupportedException this is not supported by SQLite
Qiang Xue committed
165 166 167
	 */
	public function dropColumn($table, $column)
	{
Qiang Xue committed
168
		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
Qiang Xue committed
169 170 171 172 173 174 175 176
	}

	/**
	 * 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.
Qiang Xue committed
177
	 * @throws NotSupportedException this is not supported by SQLite
Qiang Xue committed
178 179 180
	 */
	public function renameColumn($table, $oldName, $newName)
	{
Qiang Xue committed
181
		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
Qiang Xue committed
182 183 184 185 186 187 188 189 190 191 192 193 194 195 196
	}

	/**
	 * 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.
Qiang Xue committed
197
	 * @throws NotSupportedException this is not supported by SQLite
Qiang Xue committed
198 199 200
	 */
	public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
	{
Qiang Xue committed
201
		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
Qiang Xue committed
202 203 204 205 206 207 208
	}

	/**
	 * 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.
Qiang Xue committed
209
	 * @throws NotSupportedException this is not supported by SQLite
Qiang Xue committed
210 211 212
	 */
	public function dropForeignKey($name, $table)
	{
Qiang Xue committed
213
		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
Qiang Xue committed
214 215 216 217 218 219 220 221 222 223 224
	}

	/**
	 * 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.
Qiang Xue committed
225
	 * @throws NotSupportedException this is not supported by SQLite
Qiang Xue committed
226 227 228
	 */
	public function alterColumn($table, $column, $type)
	{
Qiang Xue committed
229
		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
Qiang Xue committed
230
	}
231

232 233 234 235 236 237 238 239
	/**
	 * 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
	 */
240
	public function addPrimaryKey($name, $table, $columns)
241 242
	{
		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
Alexander Makarov committed
243 244
	}

245 246 247 248 249 250 251
	/**
	 * 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	 *
	 */
252
	public function dropPrimaryKey($name, $table)
253
	{
Alexander Makarov committed
254 255
		throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
	}
256 257

	/**
Qiang Xue committed
258
	 * @inheritdoc
259 260 261 262
	 */
	public function buildLimit($limit, $offset)
	{
		$sql = '';
263 264 265 266
		if ($this->hasLimit($limit)) {
			$sql = 'LIMIT ' . $limit;
			if ($this->hasOffset($offset)) {
				$sql .= ' OFFSET ' . $offset;
267
			}
268 269 270 271
		} 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
272 273 274
		}
		return $sql;
	}
Qiang Xue committed
275
}