1 |
85ad3d82
|
Assos Assos
|
<?php
|
2 |
|
|
|
3 |
|
|
/**
|
4 |
|
|
* @file
|
5 |
|
|
* Database schema code for SQLite databases.
|
6 |
|
|
*/
|
7 |
|
|
|
8 |
|
|
|
9 |
|
|
/**
|
10 |
|
|
* @ingroup schemaapi
|
11 |
|
|
* @{
|
12 |
|
|
*/
|
13 |
|
|
|
14 |
|
|
class DatabaseSchema_sqlite extends DatabaseSchema {
|
15 |
|
|
|
16 |
|
|
/**
|
17 |
|
|
* Override DatabaseSchema::$defaultSchema
|
18 |
|
|
*/
|
19 |
|
|
protected $defaultSchema = 'main';
|
20 |
|
|
|
21 |
|
|
public function tableExists($table) {
|
22 |
|
|
$info = $this->getPrefixInfo($table);
|
23 |
|
|
|
24 |
|
|
// Don't use {} around sqlite_master table.
|
25 |
|
|
return (bool) $this->connection->query('SELECT 1 FROM ' . $info['schema'] . '.sqlite_master WHERE type = :type AND name = :name', array(':type' => 'table', ':name' => $info['table']))->fetchField();
|
26 |
|
|
}
|
27 |
|
|
|
28 |
|
|
public function fieldExists($table, $column) {
|
29 |
|
|
$schema = $this->introspectSchema($table);
|
30 |
|
|
return !empty($schema['fields'][$column]);
|
31 |
|
|
}
|
32 |
|
|
|
33 |
|
|
/**
|
34 |
|
|
* Generate SQL to create a new table from a Drupal schema definition.
|
35 |
|
|
*
|
36 |
|
|
* @param $name
|
37 |
|
|
* The name of the table to create.
|
38 |
|
|
* @param $table
|
39 |
|
|
* A Schema API table definition array.
|
40 |
|
|
* @return
|
41 |
|
|
* An array of SQL statements to create the table.
|
42 |
|
|
*/
|
43 |
|
|
public function createTableSql($name, $table) {
|
44 |
|
|
$sql = array();
|
45 |
|
|
$sql[] = "CREATE TABLE {" . $name . "} (\n" . $this->createColumsSql($name, $table) . "\n);\n";
|
46 |
|
|
return array_merge($sql, $this->createIndexSql($name, $table));
|
47 |
|
|
}
|
48 |
|
|
|
49 |
|
|
/**
|
50 |
|
|
* Build the SQL expression for indexes.
|
51 |
|
|
*/
|
52 |
|
|
protected function createIndexSql($tablename, $schema) {
|
53 |
|
|
$sql = array();
|
54 |
|
|
$info = $this->getPrefixInfo($tablename);
|
55 |
|
|
if (!empty($schema['unique keys'])) {
|
56 |
|
|
foreach ($schema['unique keys'] as $key => $fields) {
|
57 |
|
|
$sql[] = 'CREATE UNIQUE INDEX ' . $info['schema'] . '.' . $info['table'] . '_' . $key . ' ON ' . $info['table'] . ' (' . $this->createKeySql($fields) . "); \n";
|
58 |
|
|
}
|
59 |
|
|
}
|
60 |
|
|
if (!empty($schema['indexes'])) {
|
61 |
|
|
foreach ($schema['indexes'] as $key => $fields) {
|
62 |
|
|
$sql[] = 'CREATE INDEX ' . $info['schema'] . '.' . $info['table'] . '_' . $key . ' ON ' . $info['table'] . ' (' . $this->createKeySql($fields) . "); \n";
|
63 |
|
|
}
|
64 |
|
|
}
|
65 |
|
|
return $sql;
|
66 |
|
|
}
|
67 |
|
|
|
68 |
|
|
/**
|
69 |
|
|
* Build the SQL expression for creating columns.
|
70 |
|
|
*/
|
71 |
|
|
protected function createColumsSql($tablename, $schema) {
|
72 |
|
|
$sql_array = array();
|
73 |
|
|
|
74 |
|
|
// Add the SQL statement for each field.
|
75 |
|
|
foreach ($schema['fields'] as $name => $field) {
|
76 |
|
|
if (isset($field['type']) && $field['type'] == 'serial') {
|
77 |
|
|
if (isset($schema['primary key']) && ($key = array_search($name, $schema['primary key'])) !== FALSE) {
|
78 |
|
|
unset($schema['primary key'][$key]);
|
79 |
|
|
}
|
80 |
|
|
}
|
81 |
|
|
$sql_array[] = $this->createFieldSql($name, $this->processField($field));
|
82 |
|
|
}
|
83 |
|
|
|
84 |
|
|
// Process keys.
|
85 |
|
|
if (!empty($schema['primary key'])) {
|
86 |
|
|
$sql_array[] = " PRIMARY KEY (" . $this->createKeySql($schema['primary key']) . ")";
|
87 |
|
|
}
|
88 |
|
|
|
89 |
|
|
return implode(", \n", $sql_array);
|
90 |
|
|
}
|
91 |
|
|
|
92 |
|
|
/**
|
93 |
|
|
* Build the SQL expression for keys.
|
94 |
|
|
*/
|
95 |
|
|
protected function createKeySql($fields) {
|
96 |
|
|
$return = array();
|
97 |
|
|
foreach ($fields as $field) {
|
98 |
|
|
if (is_array($field)) {
|
99 |
|
|
$return[] = $field[0];
|
100 |
|
|
}
|
101 |
|
|
else {
|
102 |
|
|
$return[] = $field;
|
103 |
|
|
}
|
104 |
|
|
}
|
105 |
|
|
return implode(', ', $return);
|
106 |
|
|
}
|
107 |
|
|
|
108 |
|
|
/**
|
109 |
|
|
* Set database-engine specific properties for a field.
|
110 |
|
|
*
|
111 |
|
|
* @param $field
|
112 |
|
|
* A field description array, as specified in the schema documentation.
|
113 |
|
|
*/
|
114 |
|
|
protected function processField($field) {
|
115 |
|
|
if (!isset($field['size'])) {
|
116 |
|
|
$field['size'] = 'normal';
|
117 |
|
|
}
|
118 |
|
|
|
119 |
|
|
// Set the correct database-engine specific datatype.
|
120 |
|
|
// In case one is already provided, force it to uppercase.
|
121 |
|
|
if (isset($field['sqlite_type'])) {
|
122 |
|
|
$field['sqlite_type'] = drupal_strtoupper($field['sqlite_type']);
|
123 |
|
|
}
|
124 |
|
|
else {
|
125 |
|
|
$map = $this->getFieldTypeMap();
|
126 |
|
|
$field['sqlite_type'] = $map[$field['type'] . ':' . $field['size']];
|
127 |
|
|
}
|
128 |
|
|
|
129 |
|
|
if (isset($field['type']) && $field['type'] == 'serial') {
|
130 |
|
|
$field['auto_increment'] = TRUE;
|
131 |
|
|
}
|
132 |
|
|
|
133 |
|
|
return $field;
|
134 |
|
|
}
|
135 |
|
|
|
136 |
|
|
/**
|
137 |
|
|
* Create an SQL string for a field to be used in table creation or alteration.
|
138 |
|
|
*
|
139 |
|
|
* Before passing a field out of a schema definition into this function it has
|
140 |
|
|
* to be processed by db_processField().
|
141 |
|
|
*
|
142 |
|
|
* @param $name
|
143 |
|
|
* Name of the field.
|
144 |
|
|
* @param $spec
|
145 |
|
|
* The field specification, as per the schema data structure format.
|
146 |
|
|
*/
|
147 |
|
|
protected function createFieldSql($name, $spec) {
|
148 |
|
|
if (!empty($spec['auto_increment'])) {
|
149 |
|
|
$sql = $name . " INTEGER PRIMARY KEY AUTOINCREMENT";
|
150 |
|
|
if (!empty($spec['unsigned'])) {
|
151 |
|
|
$sql .= ' CHECK (' . $name . '>= 0)';
|
152 |
|
|
}
|
153 |
|
|
}
|
154 |
|
|
else {
|
155 |
|
|
$sql = $name . ' ' . $spec['sqlite_type'];
|
156 |
|
|
|
157 |
|
|
if (in_array($spec['sqlite_type'], array('VARCHAR', 'TEXT')) && isset($spec['length'])) {
|
158 |
|
|
$sql .= '(' . $spec['length'] . ')';
|
159 |
|
|
}
|
160 |
|
|
|
161 |
|
|
if (isset($spec['not null'])) {
|
162 |
|
|
if ($spec['not null']) {
|
163 |
|
|
$sql .= ' NOT NULL';
|
164 |
|
|
}
|
165 |
|
|
else {
|
166 |
|
|
$sql .= ' NULL';
|
167 |
|
|
}
|
168 |
|
|
}
|
169 |
|
|
|
170 |
|
|
if (!empty($spec['unsigned'])) {
|
171 |
|
|
$sql .= ' CHECK (' . $name . '>= 0)';
|
172 |
|
|
}
|
173 |
|
|
|
174 |
|
|
if (isset($spec['default'])) {
|
175 |
|
|
if (is_string($spec['default'])) {
|
176 |
|
|
$spec['default'] = "'" . $spec['default'] . "'";
|
177 |
|
|
}
|
178 |
|
|
$sql .= ' DEFAULT ' . $spec['default'];
|
179 |
|
|
}
|
180 |
|
|
|
181 |
|
|
if (empty($spec['not null']) && !isset($spec['default'])) {
|
182 |
|
|
$sql .= ' DEFAULT NULL';
|
183 |
|
|
}
|
184 |
|
|
}
|
185 |
|
|
return $sql;
|
186 |
|
|
}
|
187 |
|
|
|
188 |
|
|
/**
|
189 |
|
|
* This maps a generic data type in combination with its data size
|
190 |
|
|
* to the engine-specific data type.
|
191 |
|
|
*/
|
192 |
|
|
public function getFieldTypeMap() {
|
193 |
|
|
// Put :normal last so it gets preserved by array_flip. This makes
|
194 |
|
|
// it much easier for modules (such as schema.module) to map
|
195 |
|
|
// database types back into schema types.
|
196 |
|
|
// $map does not use drupal_static as its value never changes.
|
197 |
|
|
static $map = array(
|
198 |
|
|
'varchar:normal' => 'VARCHAR',
|
199 |
|
|
'char:normal' => 'CHAR',
|
200 |
|
|
|
201 |
|
|
'text:tiny' => 'TEXT',
|
202 |
|
|
'text:small' => 'TEXT',
|
203 |
|
|
'text:medium' => 'TEXT',
|
204 |
|
|
'text:big' => 'TEXT',
|
205 |
|
|
'text:normal' => 'TEXT',
|
206 |
|
|
|
207 |
|
|
'serial:tiny' => 'INTEGER',
|
208 |
|
|
'serial:small' => 'INTEGER',
|
209 |
|
|
'serial:medium' => 'INTEGER',
|
210 |
|
|
'serial:big' => 'INTEGER',
|
211 |
|
|
'serial:normal' => 'INTEGER',
|
212 |
|
|
|
213 |
|
|
'int:tiny' => 'INTEGER',
|
214 |
|
|
'int:small' => 'INTEGER',
|
215 |
|
|
'int:medium' => 'INTEGER',
|
216 |
|
|
'int:big' => 'INTEGER',
|
217 |
|
|
'int:normal' => 'INTEGER',
|
218 |
|
|
|
219 |
|
|
'float:tiny' => 'FLOAT',
|
220 |
|
|
'float:small' => 'FLOAT',
|
221 |
|
|
'float:medium' => 'FLOAT',
|
222 |
|
|
'float:big' => 'FLOAT',
|
223 |
|
|
'float:normal' => 'FLOAT',
|
224 |
|
|
|
225 |
|
|
'numeric:normal' => 'NUMERIC',
|
226 |
|
|
|
227 |
|
|
'blob:big' => 'BLOB',
|
228 |
|
|
'blob:normal' => 'BLOB',
|
229 |
|
|
);
|
230 |
|
|
return $map;
|
231 |
|
|
}
|
232 |
|
|
|
233 |
|
|
public function renameTable($table, $new_name) {
|
234 |
|
|
if (!$this->tableExists($table)) {
|
235 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot rename @table to @table_new: table @table doesn't exist.", array('@table' => $table, '@table_new' => $new_name)));
|
236 |
85ad3d82
|
Assos Assos
|
}
|
237 |
|
|
if ($this->tableExists($new_name)) {
|
238 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectExistsException(t("Cannot rename @table to @table_new: table @table_new already exists.", array('@table' => $table, '@table_new' => $new_name)));
|
239 |
85ad3d82
|
Assos Assos
|
}
|
240 |
|
|
|
241 |
|
|
$schema = $this->introspectSchema($table);
|
242 |
|
|
|
243 |
|
|
// SQLite doesn't allow you to rename tables outside of the current
|
244 |
|
|
// database. So the syntax '...RENAME TO database.table' would fail.
|
245 |
|
|
// So we must determine the full table name here rather than surrounding
|
246 |
|
|
// the table with curly braces incase the db_prefix contains a reference
|
247 |
|
|
// to a database outside of our existsing database.
|
248 |
|
|
$info = $this->getPrefixInfo($new_name);
|
249 |
|
|
$this->connection->query('ALTER TABLE {' . $table . '} RENAME TO ' . $info['table']);
|
250 |
|
|
|
251 |
|
|
// Drop the indexes, there is no RENAME INDEX command in SQLite.
|
252 |
|
|
if (!empty($schema['unique keys'])) {
|
253 |
|
|
foreach ($schema['unique keys'] as $key => $fields) {
|
254 |
|
|
$this->dropIndex($table, $key);
|
255 |
|
|
}
|
256 |
|
|
}
|
257 |
|
|
if (!empty($schema['indexes'])) {
|
258 |
|
|
foreach ($schema['indexes'] as $index => $fields) {
|
259 |
|
|
$this->dropIndex($table, $index);
|
260 |
|
|
}
|
261 |
|
|
}
|
262 |
|
|
|
263 |
|
|
// Recreate the indexes.
|
264 |
|
|
$statements = $this->createIndexSql($new_name, $schema);
|
265 |
|
|
foreach ($statements as $statement) {
|
266 |
|
|
$this->connection->query($statement);
|
267 |
|
|
}
|
268 |
|
|
}
|
269 |
|
|
|
270 |
|
|
public function dropTable($table) {
|
271 |
|
|
if (!$this->tableExists($table)) {
|
272 |
|
|
return FALSE;
|
273 |
|
|
}
|
274 |
|
|
$this->connection->tableDropped = TRUE;
|
275 |
|
|
$this->connection->query('DROP TABLE {' . $table . '}');
|
276 |
|
|
return TRUE;
|
277 |
|
|
}
|
278 |
|
|
|
279 |
|
|
public function addField($table, $field, $specification, $keys_new = array()) {
|
280 |
|
|
if (!$this->tableExists($table)) {
|
281 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot add field @table.@field: table doesn't exist.", array('@field' => $field, '@table' => $table)));
|
282 |
85ad3d82
|
Assos Assos
|
}
|
283 |
|
|
if ($this->fieldExists($table, $field)) {
|
284 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectExistsException(t("Cannot add field @table.@field: field already exists.", array('@field' => $field, '@table' => $table)));
|
285 |
85ad3d82
|
Assos Assos
|
}
|
286 |
|
|
|
287 |
|
|
// SQLite doesn't have a full-featured ALTER TABLE statement. It only
|
288 |
|
|
// supports adding new fields to a table, in some simple cases. In most
|
289 |
|
|
// cases, we have to create a new table and copy the data over.
|
290 |
|
|
if (empty($keys_new) && (empty($specification['not null']) || isset($specification['default']))) {
|
291 |
|
|
// When we don't have to create new keys and we are not creating a
|
292 |
|
|
// NOT NULL column without a default value, we can use the quicker version.
|
293 |
|
|
$query = 'ALTER TABLE {' . $table . '} ADD ' . $this->createFieldSql($field, $this->processField($specification));
|
294 |
|
|
$this->connection->query($query);
|
295 |
|
|
|
296 |
|
|
// Apply the initial value if set.
|
297 |
|
|
if (isset($specification['initial'])) {
|
298 |
|
|
$this->connection->update($table)
|
299 |
|
|
->fields(array($field => $specification['initial']))
|
300 |
|
|
->execute();
|
301 |
|
|
}
|
302 |
|
|
}
|
303 |
|
|
else {
|
304 |
|
|
// We cannot add the field directly. Use the slower table alteration
|
305 |
|
|
// method, starting from the old schema.
|
306 |
|
|
$old_schema = $this->introspectSchema($table);
|
307 |
|
|
$new_schema = $old_schema;
|
308 |
|
|
|
309 |
|
|
// Add the new field.
|
310 |
|
|
$new_schema['fields'][$field] = $specification;
|
311 |
|
|
|
312 |
|
|
// Build the mapping between the old fields and the new fields.
|
313 |
|
|
$mapping = array();
|
314 |
|
|
if (isset($specification['initial'])) {
|
315 |
|
|
// If we have a initial value, copy it over.
|
316 |
|
|
$mapping[$field] = array(
|
317 |
|
|
'expression' => ':newfieldinitial',
|
318 |
|
|
'arguments' => array(':newfieldinitial' => $specification['initial']),
|
319 |
|
|
);
|
320 |
|
|
}
|
321 |
|
|
else {
|
322 |
|
|
// Else use the default of the field.
|
323 |
|
|
$mapping[$field] = NULL;
|
324 |
|
|
}
|
325 |
|
|
|
326 |
|
|
// Add the new indexes.
|
327 |
|
|
$new_schema += $keys_new;
|
328 |
|
|
|
329 |
|
|
$this->alterTable($table, $old_schema, $new_schema, $mapping);
|
330 |
|
|
}
|
331 |
|
|
}
|
332 |
|
|
|
333 |
|
|
/**
|
334 |
|
|
* Create a table with a new schema containing the old content.
|
335 |
|
|
*
|
336 |
|
|
* As SQLite does not support ALTER TABLE (with a few exceptions) it is
|
337 |
|
|
* necessary to create a new table and copy over the old content.
|
338 |
|
|
*
|
339 |
|
|
* @param $table
|
340 |
|
|
* Name of the table to be altered.
|
341 |
|
|
* @param $old_schema
|
342 |
|
|
* The old schema array for the table.
|
343 |
|
|
* @param $new_schema
|
344 |
|
|
* The new schema array for the table.
|
345 |
|
|
* @param $mapping
|
346 |
|
|
* An optional mapping between the fields of the old specification and the
|
347 |
|
|
* fields of the new specification. An associative array, whose keys are
|
348 |
|
|
* the fields of the new table, and values can take two possible forms:
|
349 |
|
|
* - a simple string, which is interpreted as the name of a field of the
|
350 |
|
|
* old table,
|
351 |
|
|
* - an associative array with two keys 'expression' and 'arguments',
|
352 |
|
|
* that will be used as an expression field.
|
353 |
|
|
*/
|
354 |
|
|
protected function alterTable($table, $old_schema, $new_schema, array $mapping = array()) {
|
355 |
|
|
$i = 0;
|
356 |
|
|
do {
|
357 |
|
|
$new_table = $table . '_' . $i++;
|
358 |
|
|
} while ($this->tableExists($new_table));
|
359 |
|
|
|
360 |
|
|
$this->createTable($new_table, $new_schema);
|
361 |
|
|
|
362 |
|
|
// Build a SQL query to migrate the data from the old table to the new.
|
363 |
|
|
$select = $this->connection->select($table);
|
364 |
|
|
|
365 |
|
|
// Complete the mapping.
|
366 |
|
|
$possible_keys = array_keys($new_schema['fields']);
|
367 |
|
|
$mapping += array_combine($possible_keys, $possible_keys);
|
368 |
|
|
|
369 |
|
|
// Now add the fields.
|
370 |
|
|
foreach ($mapping as $field_alias => $field_source) {
|
371 |
|
|
// Just ignore this field (ie. use it's default value).
|
372 |
|
|
if (!isset($field_source)) {
|
373 |
|
|
continue;
|
374 |
|
|
}
|
375 |
|
|
|
376 |
|
|
if (is_array($field_source)) {
|
377 |
|
|
$select->addExpression($field_source['expression'], $field_alias, $field_source['arguments']);
|
378 |
|
|
}
|
379 |
|
|
else {
|
380 |
|
|
$select->addField($table, $field_source, $field_alias);
|
381 |
|
|
}
|
382 |
|
|
}
|
383 |
|
|
|
384 |
|
|
// Execute the data migration query.
|
385 |
|
|
$this->connection->insert($new_table)
|
386 |
|
|
->from($select)
|
387 |
|
|
->execute();
|
388 |
|
|
|
389 |
|
|
$old_count = $this->connection->query('SELECT COUNT(*) FROM {' . $table . '}')->fetchField();
|
390 |
|
|
$new_count = $this->connection->query('SELECT COUNT(*) FROM {' . $new_table . '}')->fetchField();
|
391 |
|
|
if ($old_count == $new_count) {
|
392 |
|
|
$this->dropTable($table);
|
393 |
|
|
$this->renameTable($new_table, $table);
|
394 |
|
|
}
|
395 |
|
|
}
|
396 |
|
|
|
397 |
|
|
/**
|
398 |
|
|
* Find out the schema of a table.
|
399 |
|
|
*
|
400 |
|
|
* This function uses introspection methods provided by the database to
|
401 |
|
|
* create a schema array. This is useful, for example, during update when
|
402 |
|
|
* the old schema is not available.
|
403 |
|
|
*
|
404 |
|
|
* @param $table
|
405 |
|
|
* Name of the table.
|
406 |
|
|
* @return
|
407 |
|
|
* An array representing the schema, from drupal_get_schema().
|
408 |
|
|
* @see drupal_get_schema()
|
409 |
|
|
*/
|
410 |
|
|
protected function introspectSchema($table) {
|
411 |
|
|
$mapped_fields = array_flip($this->getFieldTypeMap());
|
412 |
|
|
$schema = array(
|
413 |
|
|
'fields' => array(),
|
414 |
|
|
'primary key' => array(),
|
415 |
|
|
'unique keys' => array(),
|
416 |
|
|
'indexes' => array(),
|
417 |
|
|
);
|
418 |
|
|
|
419 |
|
|
$info = $this->getPrefixInfo($table);
|
420 |
|
|
$result = $this->connection->query('PRAGMA ' . $info['schema'] . '.table_info(' . $info['table'] . ')');
|
421 |
|
|
foreach ($result as $row) {
|
422 |
|
|
if (preg_match('/^([^(]+)\((.*)\)$/', $row->type, $matches)) {
|
423 |
|
|
$type = $matches[1];
|
424 |
|
|
$length = $matches[2];
|
425 |
|
|
}
|
426 |
|
|
else {
|
427 |
|
|
$type = $row->type;
|
428 |
|
|
$length = NULL;
|
429 |
|
|
}
|
430 |
|
|
if (isset($mapped_fields[$type])) {
|
431 |
|
|
list($type, $size) = explode(':', $mapped_fields[$type]);
|
432 |
|
|
$schema['fields'][$row->name] = array(
|
433 |
|
|
'type' => $type,
|
434 |
|
|
'size' => $size,
|
435 |
|
|
'not null' => !empty($row->notnull),
|
436 |
|
|
'default' => trim($row->dflt_value, "'"),
|
437 |
|
|
);
|
438 |
|
|
if ($length) {
|
439 |
|
|
$schema['fields'][$row->name]['length'] = $length;
|
440 |
|
|
}
|
441 |
|
|
if ($row->pk) {
|
442 |
|
|
$schema['primary key'][] = $row->name;
|
443 |
|
|
}
|
444 |
|
|
}
|
445 |
|
|
else {
|
446 |
|
|
new Exception("Unable to parse the column type " . $row->type);
|
447 |
|
|
}
|
448 |
|
|
}
|
449 |
|
|
$indexes = array();
|
450 |
|
|
$result = $this->connection->query('PRAGMA ' . $info['schema'] . '.index_list(' . $info['table'] . ')');
|
451 |
|
|
foreach ($result as $row) {
|
452 |
|
|
if (strpos($row->name, 'sqlite_autoindex_') !== 0) {
|
453 |
|
|
$indexes[] = array(
|
454 |
|
|
'schema_key' => $row->unique ? 'unique keys' : 'indexes',
|
455 |
|
|
'name' => $row->name,
|
456 |
|
|
);
|
457 |
|
|
}
|
458 |
|
|
}
|
459 |
|
|
foreach ($indexes as $index) {
|
460 |
|
|
$name = $index['name'];
|
461 |
|
|
// Get index name without prefix.
|
462 |
|
|
$index_name = substr($name, strlen($info['table']) + 1);
|
463 |
|
|
$result = $this->connection->query('PRAGMA ' . $info['schema'] . '.index_info(' . $name . ')');
|
464 |
|
|
foreach ($result as $row) {
|
465 |
|
|
$schema[$index['schema_key']][$index_name][] = $row->name;
|
466 |
|
|
}
|
467 |
|
|
}
|
468 |
|
|
return $schema;
|
469 |
|
|
}
|
470 |
|
|
|
471 |
|
|
public function dropField($table, $field) {
|
472 |
|
|
if (!$this->fieldExists($table, $field)) {
|
473 |
|
|
return FALSE;
|
474 |
|
|
}
|
475 |
|
|
|
476 |
|
|
$old_schema = $this->introspectSchema($table);
|
477 |
|
|
$new_schema = $old_schema;
|
478 |
|
|
|
479 |
|
|
unset($new_schema['fields'][$field]);
|
480 |
|
|
foreach ($new_schema['indexes'] as $index => $fields) {
|
481 |
|
|
foreach ($fields as $key => $field_name) {
|
482 |
|
|
if ($field_name == $field) {
|
483 |
|
|
unset($new_schema['indexes'][$index][$key]);
|
484 |
|
|
}
|
485 |
|
|
}
|
486 |
|
|
// If this index has no more fields then remove it.
|
487 |
|
|
if (empty($new_schema['indexes'][$index])) {
|
488 |
|
|
unset($new_schema['indexes'][$index]);
|
489 |
|
|
}
|
490 |
|
|
}
|
491 |
|
|
$this->alterTable($table, $old_schema, $new_schema);
|
492 |
|
|
return TRUE;
|
493 |
|
|
}
|
494 |
|
|
|
495 |
|
|
public function changeField($table, $field, $field_new, $spec, $keys_new = array()) {
|
496 |
|
|
if (!$this->fieldExists($table, $field)) {
|
497 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot change the definition of field @table.@name: field doesn't exist.", array('@table' => $table, '@name' => $field)));
|
498 |
85ad3d82
|
Assos Assos
|
}
|
499 |
|
|
if (($field != $field_new) && $this->fieldExists($table, $field_new)) {
|
500 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectExistsException(t("Cannot rename field @table.@name to @name_new: target field already exists.", array('@table' => $table, '@name' => $field, '@name_new' => $field_new)));
|
501 |
85ad3d82
|
Assos Assos
|
}
|
502 |
|
|
|
503 |
|
|
$old_schema = $this->introspectSchema($table);
|
504 |
|
|
$new_schema = $old_schema;
|
505 |
|
|
|
506 |
|
|
// Map the old field to the new field.
|
507 |
|
|
if ($field != $field_new) {
|
508 |
|
|
$mapping[$field_new] = $field;
|
509 |
|
|
}
|
510 |
|
|
else {
|
511 |
|
|
$mapping = array();
|
512 |
|
|
}
|
513 |
|
|
|
514 |
|
|
// Remove the previous definition and swap in the new one.
|
515 |
|
|
unset($new_schema['fields'][$field]);
|
516 |
|
|
$new_schema['fields'][$field_new] = $spec;
|
517 |
|
|
|
518 |
|
|
// Map the former indexes to the new column name.
|
519 |
|
|
$new_schema['primary key'] = $this->mapKeyDefinition($new_schema['primary key'], $mapping);
|
520 |
|
|
foreach (array('unique keys', 'indexes') as $k) {
|
521 |
|
|
foreach ($new_schema[$k] as &$key_definition) {
|
522 |
|
|
$key_definition = $this->mapKeyDefinition($key_definition, $mapping);
|
523 |
|
|
}
|
524 |
|
|
}
|
525 |
|
|
|
526 |
|
|
// Add in the keys from $keys_new.
|
527 |
|
|
if (isset($keys_new['primary key'])) {
|
528 |
|
|
$new_schema['primary key'] = $keys_new['primary key'];
|
529 |
|
|
}
|
530 |
|
|
foreach (array('unique keys', 'indexes') as $k) {
|
531 |
|
|
if (!empty($keys_new[$k])) {
|
532 |
|
|
$new_schema[$k] = $keys_new[$k] + $new_schema[$k];
|
533 |
|
|
}
|
534 |
|
|
}
|
535 |
|
|
|
536 |
|
|
$this->alterTable($table, $old_schema, $new_schema, $mapping);
|
537 |
|
|
}
|
538 |
|
|
|
539 |
|
|
/**
|
540 |
|
|
* Utility method: rename columns in an index definition according to a new mapping.
|
541 |
|
|
*
|
542 |
|
|
* @param $key_definition
|
543 |
|
|
* The key definition.
|
544 |
|
|
* @param $mapping
|
545 |
|
|
* The new mapping.
|
546 |
|
|
*/
|
547 |
|
|
protected function mapKeyDefinition(array $key_definition, array $mapping) {
|
548 |
|
|
foreach ($key_definition as &$field) {
|
549 |
|
|
// The key definition can be an array($field, $length).
|
550 |
|
|
if (is_array($field)) {
|
551 |
|
|
$field = &$field[0];
|
552 |
|
|
}
|
553 |
|
|
if (isset($mapping[$field])) {
|
554 |
|
|
$field = $mapping[$field];
|
555 |
|
|
}
|
556 |
|
|
}
|
557 |
|
|
return $key_definition;
|
558 |
|
|
}
|
559 |
|
|
|
560 |
|
|
public function addIndex($table, $name, $fields) {
|
561 |
|
|
if (!$this->tableExists($table)) {
|
562 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot add index @name to table @table: table doesn't exist.", array('@table' => $table, '@name' => $name)));
|
563 |
85ad3d82
|
Assos Assos
|
}
|
564 |
|
|
if ($this->indexExists($table, $name)) {
|
565 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectExistsException(t("Cannot add index @name to table @table: index already exists.", array('@table' => $table, '@name' => $name)));
|
566 |
85ad3d82
|
Assos Assos
|
}
|
567 |
|
|
|
568 |
|
|
$schema['indexes'][$name] = $fields;
|
569 |
|
|
$statements = $this->createIndexSql($table, $schema);
|
570 |
|
|
foreach ($statements as $statement) {
|
571 |
|
|
$this->connection->query($statement);
|
572 |
|
|
}
|
573 |
|
|
}
|
574 |
|
|
|
575 |
|
|
public function indexExists($table, $name) {
|
576 |
|
|
$info = $this->getPrefixInfo($table);
|
577 |
|
|
|
578 |
|
|
return $this->connection->query('PRAGMA ' . $info['schema'] . '.index_info(' . $info['table'] . '_' . $name . ')')->fetchField() != '';
|
579 |
|
|
}
|
580 |
|
|
|
581 |
|
|
public function dropIndex($table, $name) {
|
582 |
|
|
if (!$this->indexExists($table, $name)) {
|
583 |
|
|
return FALSE;
|
584 |
|
|
}
|
585 |
|
|
|
586 |
|
|
$info = $this->getPrefixInfo($table);
|
587 |
|
|
|
588 |
|
|
$this->connection->query('DROP INDEX ' . $info['schema'] . '.' . $info['table'] . '_' . $name);
|
589 |
|
|
return TRUE;
|
590 |
|
|
}
|
591 |
|
|
|
592 |
|
|
public function addUniqueKey($table, $name, $fields) {
|
593 |
|
|
if (!$this->tableExists($table)) {
|
594 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot add unique key @name to table @table: table doesn't exist.", array('@table' => $table, '@name' => $name)));
|
595 |
85ad3d82
|
Assos Assos
|
}
|
596 |
|
|
if ($this->indexExists($table, $name)) {
|
597 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectExistsException(t("Cannot add unique key @name to table @table: unique key already exists.", array('@table' => $table, '@name' => $name)));
|
598 |
85ad3d82
|
Assos Assos
|
}
|
599 |
|
|
|
600 |
|
|
$schema['unique keys'][$name] = $fields;
|
601 |
|
|
$statements = $this->createIndexSql($table, $schema);
|
602 |
|
|
foreach ($statements as $statement) {
|
603 |
|
|
$this->connection->query($statement);
|
604 |
|
|
}
|
605 |
|
|
}
|
606 |
|
|
|
607 |
|
|
public function dropUniqueKey($table, $name) {
|
608 |
|
|
if (!$this->indexExists($table, $name)) {
|
609 |
|
|
return FALSE;
|
610 |
|
|
}
|
611 |
|
|
|
612 |
|
|
$info = $this->getPrefixInfo($table);
|
613 |
|
|
|
614 |
|
|
$this->connection->query('DROP INDEX ' . $info['schema'] . '.' . $info['table'] . '_' . $name);
|
615 |
|
|
return TRUE;
|
616 |
|
|
}
|
617 |
|
|
|
618 |
|
|
public function addPrimaryKey($table, $fields) {
|
619 |
|
|
if (!$this->tableExists($table)) {
|
620 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot add primary key to table @table: table doesn't exist.", array('@table' => $table)));
|
621 |
85ad3d82
|
Assos Assos
|
}
|
622 |
|
|
|
623 |
|
|
$old_schema = $this->introspectSchema($table);
|
624 |
|
|
$new_schema = $old_schema;
|
625 |
|
|
|
626 |
|
|
if (!empty($new_schema['primary key'])) {
|
627 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectExistsException(t("Cannot add primary key to table @table: primary key already exists.", array('@table' => $table)));
|
628 |
85ad3d82
|
Assos Assos
|
}
|
629 |
|
|
|
630 |
|
|
$new_schema['primary key'] = $fields;
|
631 |
|
|
$this->alterTable($table, $old_schema, $new_schema);
|
632 |
|
|
}
|
633 |
|
|
|
634 |
|
|
public function dropPrimaryKey($table) {
|
635 |
|
|
$old_schema = $this->introspectSchema($table);
|
636 |
|
|
$new_schema = $old_schema;
|
637 |
|
|
|
638 |
|
|
if (empty($new_schema['primary key'])) {
|
639 |
|
|
return FALSE;
|
640 |
|
|
}
|
641 |
|
|
|
642 |
|
|
unset($new_schema['primary key']);
|
643 |
|
|
$this->alterTable($table, $old_schema, $new_schema);
|
644 |
|
|
return TRUE;
|
645 |
|
|
}
|
646 |
|
|
|
647 |
|
|
public function fieldSetDefault($table, $field, $default) {
|
648 |
|
|
if (!$this->fieldExists($table, $field)) {
|
649 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot set default value of field @table.@field: field doesn't exist.", array('@table' => $table, '@field' => $field)));
|
650 |
85ad3d82
|
Assos Assos
|
}
|
651 |
|
|
|
652 |
|
|
$old_schema = $this->introspectSchema($table);
|
653 |
|
|
$new_schema = $old_schema;
|
654 |
|
|
|
655 |
|
|
$new_schema['fields'][$field]['default'] = $default;
|
656 |
|
|
$this->alterTable($table, $old_schema, $new_schema);
|
657 |
|
|
}
|
658 |
|
|
|
659 |
|
|
public function fieldSetNoDefault($table, $field) {
|
660 |
|
|
if (!$this->fieldExists($table, $field)) {
|
661 |
b4adf10d
|
Assos Assos
|
throw new DatabaseSchemaObjectDoesNotExistException(t("Cannot remove default value of field @table.@field: field doesn't exist.", array('@table' => $table, '@field' => $field)));
|
662 |
85ad3d82
|
Assos Assos
|
}
|
663 |
|
|
|
664 |
|
|
$old_schema = $this->introspectSchema($table);
|
665 |
|
|
$new_schema = $old_schema;
|
666 |
|
|
|
667 |
|
|
unset($new_schema['fields'][$field]['default']);
|
668 |
|
|
$this->alterTable($table, $old_schema, $new_schema);
|
669 |
|
|
}
|
670 |
|
|
|
671 |
|
|
public function findTables($table_expression) {
|
672 |
|
|
// Don't add the prefix, $table_expression already includes the prefix.
|
673 |
|
|
$info = $this->getPrefixInfo($table_expression, FALSE);
|
674 |
|
|
|
675 |
|
|
// Can't use query placeholders for the schema because the query would have
|
676 |
|
|
// to be :prefixsqlite_master, which does not work.
|
677 |
|
|
$result = db_query("SELECT name FROM " . $info['schema'] . ".sqlite_master WHERE type = :type AND name LIKE :table_name", array(
|
678 |
|
|
':type' => 'table',
|
679 |
|
|
':table_name' => $info['table'],
|
680 |
|
|
));
|
681 |
|
|
return $result->fetchAllKeyed(0, 0);
|
682 |
|
|
}
|
683 |
|
|
} |