QueryBuilder.php 12.5 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 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
    /**
     * @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_BIGPK => 'integer PRIMARY KEY AUTOINCREMENT NOT NULL',
        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_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)',
    ];
Qiang Xue committed
43

44

45 46 47 48 49
    /**
     * Generates a batch INSERT SQL statement.
     * For example,
     *
     * ~~~
50
     * $connection->createCommand()->batchInsert('user', ['name', 'age'], [
51 52 53 54 55 56 57 58
     *     ['Tom', 30],
     *     ['Jane', 20],
     *     ['Linda', 25],
     * ])->execute();
     * ~~~
     *
     * Note that the values in each row must match the corresponding column names.
     *
59 60 61
     * @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
62 63 64 65
     * @return string the batch INSERT SQL statement
     */
    public function batchInsert($table, $columns, $rows)
    {
66 67 68 69 70 71
        // SQLite supports batch insert natively since 3.7.11
        // http://www.sqlite.org/releaselog/3_7_11.html
        if (version_compare(\SQLite3::version()['versionString'], '3.7.11', '>=')) {
            return parent::batchInsert($table, $columns, $rows);
        }

72 73 74 75 76
        if (($tableSchema = $this->db->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;
        } else {
            $columnSchemas = [];
        }
77

78 79 80 81 82
        $values = [];
        foreach ($rows as $row) {
            $vs = [];
            foreach ($row as $i => $value) {
                if (!is_array($value) && isset($columnSchemas[$columns[$i]])) {
83
                    $value = $columnSchemas[$columns[$i]]->dbTypecast($value);
84 85 86 87 88 89 90 91 92 93 94 95
                }
                if (is_string($value)) {
                    $value = $this->db->quoteValue($value);
                } elseif ($value === false) {
                    $value = 0;
                } elseif ($value === null) {
                    $value = 'NULL';
                }
                $vs[] = $value;
            }
            $values[] = implode(', ', $vs);
        }
96

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

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

105 106 107 108
    /**
     * 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.
109 110 111 112
     * @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
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
     * @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 = $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'.'");
        }
    }
Qiang Xue committed
138

139 140
    /**
     * Enables or disables integrity check.
141 142 143 144
     * @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
145 146 147 148
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function checkIntegrity($check = true, $schema = '', $table = '')
    {
149
        return 'PRAGMA foreign_keys='.(int)$check;
150
    }
Qiang Xue committed
151

152 153
    /**
     * Builds a SQL statement for truncating a DB table.
154
     * @param string $table the table to be truncated. The name will be properly quoted by the method.
155 156 157 158 159 160
     * @return string the SQL statement for truncating a DB table.
     */
    public function truncateTable($table)
    {
        return "DELETE FROM " . $this->db->quoteTableName($table);
    }
Qiang Xue committed
161

162 163
    /**
     * Builds a SQL statement for dropping an index.
164 165
     * @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.
166 167 168 169 170 171
     * @return string the SQL statement for dropping an index.
     */
    public function dropIndex($name, $table)
    {
        return 'DROP INDEX ' . $this->db->quoteTableName($name);
    }
Qiang Xue committed
172

173 174
    /**
     * Builds a SQL statement for dropping a DB column.
175 176 177
     * @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.
178 179 180 181 182 183
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function dropColumn($table, $column)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }
Qiang Xue committed
184

185 186
    /**
     * Builds a SQL statement for renaming a column.
187 188 189 190
     * @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.
191 192 193 194 195 196
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function renameColumn($table, $oldName, $newName)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }
Qiang Xue committed
197

198 199 200
    /**
     * Builds a SQL statement for adding a foreign key constraint to an existing table.
     * The method will properly quote the table and column names.
201 202 203 204 205 206 207 208 209 210
     * @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.
211 212 213 214 215 216
     * @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.');
    }
Qiang Xue committed
217

218 219
    /**
     * Builds a SQL statement for dropping a foreign key constraint.
220 221 222
     * @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.
223 224 225 226 227 228
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function dropForeignKey($name, $table)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }
Qiang Xue committed
229

230 231
    /**
     * Builds a SQL statement for changing the definition of a column.
232 233 234 235 236 237 238
     * @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.
239 240 241 242 243 244
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function alterColumn($table, $column, $type)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }
245

246 247
    /**
     * Builds a SQL statement for adding a primary key constraint to an existing table.
248 249 250 251
     * @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.
252 253 254 255 256 257
     * @throws NotSupportedException this is not supported by SQLite
     */
    public function addPrimaryKey($name, $table, $columns)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }
Alexander Makarov committed
258

259 260
    /**
     * Builds a SQL statement for removing a primary key constraint to an existing table.
261 262 263
     * @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.
264
     * @throws NotSupportedException this is not supported by SQLite
265 266 267 268 269
     */
    public function dropPrimaryKey($name, $table)
    {
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
    }
270

271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289
    /**
     * @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;
    }
Qiang Xue committed
290
}