Sqlite::_changeColumnName()   A
last analyzed

Complexity

Conditions 1
Paths 1

Size

Total Lines 3
Code Lines 1

Duplication

Lines 0
Ratio 0 %

Importance

Changes 1
Bugs 0 Features 0
Metric Value
cc 1
eloc 1
c 1
b 0
f 0
nc 1
nop 1
dl 0
loc 3
rs 10
1
<?php
2
namespace yentu\manipulators;
3
4
use yentu\Parameters;
5
6
/**
7
 * SQLite Database Structure Manipulator for the yentu migration engine.
8
 *
9
 * @author Ekow Abaka Ainooson
10
 */
11
class Sqlite extends AbstractDatabaseManipulator
12
{
13
    private $placeholders = [];
14
15
    protected function _addAutoPrimaryKey($details)
16
    {
17
        $this->rebuildTableFromDefinition($details['table']);
18
    }
19
20
    private function renameColumns(&$columns, $options)
21
    {
22
        if (isset($options['renamed_column'])) {
23
            foreach ($columns as $i => $column) {
24
                if ($options['renamed_column']['from']['name'] === $column) {
25
                    $columns[$i] = $options['renamed_column']['to']['name'];
26
                }
27
            }
28
        }
29
    }
30
31
    /**
32
     * Generate a query stub to represent the constraints section of a full
33
     * query (usually a CREATE TABLE or ADD COlUMN query).
34
     *
35
     * @param array<string> $columns An array of the names of columns in the constraint.
36
     * @param string $type The type of constraint `FOREIGN KEY`, `UNIQUE` etc.
37
     * @param string $name The name of the constraint.
38
     * @return string
39
     */
40
    private function getConstraintQuery($columns, $type, $name, $options)
41
    {
42
        $this->renameColumns($columns, $options);
43
        return ", CONSTRAINT `$name` $type (`" . implode('`, `', $columns) . "`)";
44
    }
45
46
    private function createIndices($indices, $table)
47
    {
48
        foreach ($indices as $name => $index) {
49
            $this->_addIndex(['name' => $name, 'table' => $table, 'columns' => $index['columns']]);
50
        }
51
    }
52
53
    /**
54
     * Generate all the constraint queries of a table.
55
     * This function is used when executing UNIQUE or PRIMARY KEY constraints.
56
     *
57
     * @param array<array> $constraints An array of details of all constraints.
58
     * @param string $type The type of constraint 'FOREIGN KEY' ... etc.
59
     * @return string
60
     */
61
    private function generateConstraintsQueries($constraints, $type, $options)
62
    {
63
        $query = '';
64
        foreach ($constraints as $name => $constraint) {
65
            $query .= $this->getConstraintQuery($constraint['columns'], $type, $name, $options);
66
        }
67
        return $query;
68
    }
69
70
    /**
71
     * Generate the query for a foreign key constraint.
72
     *
73
     * @param array<array> $constraintDetails
74
     * @return string
75
     */
76
    private function getFKConstraintQuery($constraints, $options)
77
    {
78
        $query = '';
79
        foreach ($constraints as $name => $constraint) {
80
            $this->renameColumns($constraint['foreign_columns'], $options);
81
            $query .= $this->getConstraintQuery($constraint['columns'], 'FOREIGN KEY', $name, $options) .
82
                sprintf(
83
                    " REFERENCES `{$constraint['foreign_table']}` (`" . implode('`, `', $constraint['foreign_columns']) . "`) %s %s",
84
                    isset($constraint['on_delete']) ? "ON DELETE {$constraint['on_delete']}" : '',
85
                    isset($constraint['on_update']) ? "ON UPDATE {$constraint['on_update']}" : ''
86
                );
87
        }
88
        return $query;
89
    }
90
91
    /**
92
     * Generate an SQL query field list to be used in a query for moving data
93
     * between a table and its altered counterpart.
94
     * This function is called when reconstructing SQLite tables.
95
     * New columns would be ignored as their default value would be appended.
96
     * Renamed columns would return the old name of the column with the new
97
     * name as an alias. Fields which are not altered in any way are returned
98
     * just as they are.
99
     *
100
     * @param array $column An array containing information about the column
101
     * @param array $options Contains information about the current operation.
102
     *     Through this variable we can know whether a new column has been
103
     *     added or an existing column has been renamed.
104
     * @param string $comma The comma state. Helps in comma placements for
105
     *     correct query generation.
106
     * @return string
107
     */
108
    private function getFieldListColumn($column, $options, $comma)
109
    {
110
        $return = false;
111
        if (isset($options['new_column'])) {
112
            if ($column['name'] == $options['new_column']['name']) {
113
                $return = '';
114
            }
115
        } else if (isset($options['renamed_column'])) {
116
            if ($column['name'] == $options['renamed_column']['to']['name']) {
117
                $return = $comma . "`{$options['renamed_column']['from']['name']}` as `{$options['renamed_column']['to']['name']}`";
118
            }
119
        }
120
121
        if ($return === false) {
122
            $return = $comma . "`{$column['name']}`";
123
        }
124
        return $return;
125
    }
126
127
    /**
128
     * Rebuids an entire table based on the current state of yentu's schema
129
     * description.
130
     * In order to work around SQLite's lack of full table altering routines,
131
     * this function does the work of creating new tables based on altered
132
     * versions of old tables and moves data between the old and new tables.
133
     * It takes advantage of the fact that yentu maintains an internal schema
134
     * description of all operations performed.
135
     *
136
     * @param string $tableName The name of the table to rebuild.
137
     * @param array $options An array which contains extra details about the
138
     *     operation which led to the rebuilding of the table. Currently the
139
     *     only options that are of interest are those that are passed when
140
     *     adding new columns and those that are passed when modifying existing
141
     *     columns.
142
     */
143
    private function rebuildTableFromDefinition($tableName, $options = [])
144
    {
145
        $this->query("PRAGMA foreign_keys=OFF");
146
        $table = Parameters::wrap(
147
            $this->getDescription()->getTable(['table' => $tableName, 'schema' => false]),
148
            ['auto_increment']
149
        );
150
        $dummyTable = "__yentu_{$table['name']}";
151
        $query = "CREATE TABLE `$dummyTable` (";
152
        $fieldList = '';
153
        $comma = '';
154
        $primaryKeyAdded = false;
155
        $primaryKeyColumn = '';
156
157
        if ($table['auto_increment'] && isset($table['primary_key'][0])) {
158
            $key = $table['primary_key'][0];
159
            $primaryKeyColumn = $key['columns'][0];
160
        }
161
        if (count($table['columns'])) {
162
            foreach ($table['columns'] as $column) {
163
                $query .= $comma . $this->getColumnDef($column);
164
                $fieldList .= $this->getFieldListColumn($column, $options, $comma);
165
                if ($column['name'] === $primaryKeyColumn) {
166
                    $query .= ' PRIMARY KEY AUTOINCREMENT';
167
                    $primaryKeyAdded = true;
168
                }
169
                $comma = ', ';
170
            }
171
        } else {
172
            // put back the placeholder column so the table can stand
173
            $query .= '`__yentu_placeholder_col` INTEGER';
174
        }
175
176
        if (!$primaryKeyAdded && isset($table['primary_key'])) {
177
            $query .= $this->generateConstraintsQueries($table['primary_key'], 'PRIMARY KEY', $options);
178
        }
179
        $query .= $this->generateConstraintsQueries($table['unique_keys'], 'UNIQUE', $options);
180
        $query .= $this->getFKConstraintQuery($table['foreign_keys'], $options);
181
182
        $query .= ')';
183
184
        $this->query($query);
185
186
        if (isset($options['new_column'])) {
187
            $this->query("INSERT INTO `$dummyTable` SELECT {$fieldList} , ? FROM `{$table['name']}`", $options['new_column']['default']);
188
        } else if (count($table['columns']) > 0) {
189
            $this->query("INSERT INTO `$dummyTable` SELECT {$fieldList} FROM `{$table['name']}`");
190
        }
191
192
        $this->query("DROP TABLE `{$table['name']}`");
193
        $this->query("ALTER TABLE `$dummyTable` RENAME TO `{$table['name']}`");
194
        $this->createIndices($table['indices'], $table['name']);
195
        $this->query("PRAGMA foreign_keys=ON");
196
    }
197
198
    /**
199
     * Generate an SQL query stub which represent a column definition.
200
     *
201
     * @param array $details
202
     * @return string
203
     */
204
    private function getColumnDef($details)
205
    {
206
        $details = Parameters::wrap($details, ['length', 'default']);
207
        return trim(sprintf(
208
            "`%s` %s %s %s",
209
            $details['name'],
210
            $this->convertTypes(
211
                $details['type'],
212
                self::CONVERT_TO_DRIVER,
213
                $details['length']
214
            ),
215
            $details['nulls'] === false ? 'NOT NULL' : '',
216
            $details['default'] === null ? null : "DEFAULT {$details['default']}"
217
        ));
218
    }
219
220
    protected function _addColumn($details)
221
    {
222
        if (isset($this->placeholders[$details['table']])) {
223
            $this->query("DROP TABLE `{$details['table']}`");
224
            $this->query(sprintf("CREATE TABLE `%s` (%s)",
225
                    $details['table'],
226
                    $this->getColumnDef($details)
227
                )
228
            );
229
            unset($this->placeholders[$details['table']]);
230
        } else if ($details['nulls'] === null || $details['nulls'] == true || ($details['nulls'] === false && $details['default'] !== null)) {
231
            $this->query("ALTER TABLE `{$details['table']}` ADD COLUMN " . $this->getColumnDef($details));
232
        } else {
233
            $this->rebuildTableFromDefinition($details['table'], ['new_column' => $details]);
234
        }
235
    }
236
237
    protected function _addForeignKey($details)
238
    {
239
        $this->rebuildTableFromDefinition($details['table']);
240
    }
241
242
    protected function _addIndex($details)
243
    {
244
        $this->query("CREATE INDEX `{$details['name']}` ON `{$details['table']}` (`" . implode("`, `", $details['columns']) . "`)");
245
    }
246
247
    protected function _addPrimaryKey($details)
248
    {
249
        $this->rebuildTableFromDefinition($details['table']);
250
    }
251
252
    protected function _addSchema($name)
253
    {
254
255
256
    }
257
258
    protected function _addTable($details)
259
    {
260
        $this->query("CREATE TABLE `{$details['name']}` (`__yentu_placeholder_col` INTEGER)");
261
        $this->placeholders[$details['name']] = true;
262
    }
263
264
    protected function _addUniqueKey($details)
265
    {
266
        $this->rebuildTableFromDefinition($details['table']);
267
    }
268
269
    protected function _addView($details)
270
    {
271
        $this->query("CREATE VIEW `{$details['name']}` AS {$details['definition']}");
272
    }
273
274
    protected function _changeColumnDefault($details)
275
    {
276
        $this->rebuildTableFromDefinition($details['to']['table']);
277
    }
278
279
    protected function _changeColumnName($details)
280
    {
281
        $this->rebuildTableFromDefinition($details['to']['table'], ['renamed_column' => $details]);
282
    }
283
284
    protected function _changeColumnNulls($details)
285
    {
286
        $this->rebuildTableFromDefinition($details['to']['table']);
287
    }
288
289
    protected function _changeViewDefinition($details)
290
    {
291
        $this->query("DROP VIEW `{$details['to']['name']}`");
292
        $this->_addView($details['to']);
293
    }
294
295
    protected function _dropAutoPrimaryKey($details)
296
    {
297
        $this->rebuildTableFromDefinition($details['table']);
298
    }
299
300
    protected function _dropColumn($details)
301
    {
302
        $this->rebuildTableFromDefinition($details['table']);
303
304
    }
305
306
    protected function _dropForeignKey($details)
307
    {
308
        $this->rebuildTableFromDefinition($details['table']);
309
    }
310
311
    protected function _dropIndex($details)
312
    {
313
        $this->query("DROP INDEX `{$details['name']}`");
314
    }
315
316
    protected function _dropPrimaryKey($details)
317
    {
318
        $this->rebuildTableFromDefinition($details['table']);
319
    }
320
321
    protected function _dropSchema($name)
322
    {
323
324
325
    }
326
327
    protected function _dropTable($details)
328
    {
329
        $this->query("DROP TABLE `{$details['name']}`");
330
    }
331
332
    protected function _dropUniqueKey($details)
333
    {
334
        $this->rebuildTableFromDefinition($details['table']);
335
    }
336
337
    protected function _dropView($details)
338
    {
339
        $this->query("DROP VIEW `{$details['name']}`");
340
    }
341
342
    public function convertTypes($type, $direction, $length)
343
    {
344
        $destinationType = null;
345
        $types = [
346
            ['integer', 'integer'],
347
            ['text', 'string'],
348
            ['real', 'double'],
349
            ['text', 'timestamp'],
350
            ['text', 'text'],
351
            ['blob', 'blob'],
352
            ['integer', 'boolean'],
353
            ['integer', 'bigint'],
354
            ['text', 'date']
355
        ];
356
357
        $type = strtolower($type);
358
359
        foreach ($types as $testType) {
360
            if ($direction === self::CONVERT_TO_DRIVER && $testType[1] === $type) {
361
                $destinationType = $testType[0];
362
                break;
363
            } else if ($direction === self::CONVERT_TO_YENTU && $testType[0] === $type) {
364
                $destinationType = $testType[1];
365
                break;
366
            }
367
        }
368
369
        if ($destinationType == '') {
370
            throw new \yentu\exceptions\DatabaseManipulatorException("Invalid data type {$type} requested");
371
        } else if ($destinationType == 'text') {
372
            $destinationType .= $length === null ? '' : "($length)";
373
        }
374
        return $destinationType;
375
    }
376
377
    protected function _changeTableName($details)
378
    {
379
        $this->query(
380
            sprintf(
381
                "ALTER TABLE `%s` RENAME TO `%s`",
382
                $details['from']['name'], $details['to']['name']
383
            )
384
        );
385
    }
386
387
}
388
389