1: <?php
  2: /**
  3:  * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
  4:  * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
  5:  *
  6:  * Licensed under The MIT License
  7:  * For full copyright and license information, please see the LICENSE.txt
  8:  * Redistributions of files must retain the above copyright notice.
  9:  *
 10:  * @copyright     Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
 11:  * @link          https://cakephp.org CakePHP(tm) Project
 12:  * @since         3.0.0
 13:  * @license       https://opensource.org/licenses/mit-license.php MIT License
 14:  */
 15: namespace Cake\Database\Schema;
 16: 
 17: use Cake\Database\Exception;
 18: use Cake\Database\Schema\TableSchema;
 19: 
 20: /**
 21:  * Schema management/reflection features for Sqlite
 22:  */
 23: class SqliteSchema extends BaseSchema
 24: {
 25: 
 26:     /**
 27:      * Array containing the foreign keys constraints names
 28:      * Necessary for composite foreign keys to be handled
 29:      *
 30:      * @var array
 31:      */
 32:     protected $_constraintsIdMap = [];
 33: 
 34:     /**
 35:      * Whether there is any table in this connection to SQLite containing sequences.
 36:      *
 37:      * @var bool
 38:      */
 39:     protected $_hasSequences;
 40: 
 41:     /**
 42:      * Convert a column definition to the abstract types.
 43:      *
 44:      * The returned type will be a type that
 45:      * Cake\Database\Type can handle.
 46:      *
 47:      * @param string $column The column type + length
 48:      * @throws \Cake\Database\Exception when unable to parse column type
 49:      * @return array Array of column information.
 50:      */
 51:     protected function _convertColumn($column)
 52:     {
 53:         preg_match('/(unsigned)?\s*([a-z]+)(?:\(([0-9,]+)\))?/i', $column, $matches);
 54:         if (empty($matches)) {
 55:             throw new Exception(sprintf('Unable to parse column type from "%s"', $column));
 56:         }
 57: 
 58:         $unsigned = false;
 59:         if (strtolower($matches[1]) === 'unsigned') {
 60:             $unsigned = true;
 61:         }
 62: 
 63:         $col = strtolower($matches[2]);
 64:         $length = null;
 65:         if (isset($matches[3])) {
 66:             $length = (int)$matches[3];
 67:         }
 68: 
 69:         if ($col === 'bigint') {
 70:             return ['type' => TableSchema::TYPE_BIGINTEGER, 'length' => $length, 'unsigned' => $unsigned];
 71:         }
 72:         if ($col == 'smallint') {
 73:             return ['type' => TableSchema::TYPE_SMALLINTEGER, 'length' => $length, 'unsigned' => $unsigned];
 74:         }
 75:         if ($col == 'tinyint') {
 76:             return ['type' => TableSchema::TYPE_TINYINTEGER, 'length' => $length, 'unsigned' => $unsigned];
 77:         }
 78:         if (strpos($col, 'int') !== false) {
 79:             return ['type' => TableSchema::TYPE_INTEGER, 'length' => $length, 'unsigned' => $unsigned];
 80:         }
 81:         if (strpos($col, 'decimal') !== false) {
 82:             return ['type' => TableSchema::TYPE_DECIMAL, 'length' => null, 'unsigned' => $unsigned];
 83:         }
 84:         if (in_array($col, ['float', 'real', 'double'])) {
 85:             return ['type' => TableSchema::TYPE_FLOAT, 'length' => null, 'unsigned' => $unsigned];
 86:         }
 87: 
 88:         if (strpos($col, 'boolean') !== false) {
 89:             return ['type' => TableSchema::TYPE_BOOLEAN, 'length' => null];
 90:         }
 91: 
 92:         if ($col === 'char' && $length === 36) {
 93:             return ['type' => TableSchema::TYPE_UUID, 'length' => null];
 94:         }
 95:         if ($col === 'char') {
 96:             return ['type' => TableSchema::TYPE_STRING, 'fixed' => true, 'length' => $length];
 97:         }
 98:         if (strpos($col, 'char') !== false) {
 99:             return ['type' => TableSchema::TYPE_STRING, 'length' => $length];
100:         }
101: 
102:         if ($col === 'binary' && $length === 16) {
103:             return ['type' => TableSchema::TYPE_BINARY_UUID, 'length' => null];
104:         }
105:         if (in_array($col, ['blob', 'clob', 'binary', 'varbinary'])) {
106:             return ['type' => TableSchema::TYPE_BINARY, 'length' => $length];
107:         }
108:         if (in_array($col, ['date', 'time', 'timestamp', 'datetime'])) {
109:             return ['type' => $col, 'length' => null];
110:         }
111: 
112:         return ['type' => TableSchema::TYPE_TEXT, 'length' => null];
113:     }
114: 
115:     /**
116:      * {@inheritDoc}
117:      */
118:     public function listTablesSql($config)
119:     {
120:         return [
121:             'SELECT name FROM sqlite_master WHERE type="table" ' .
122:             'AND name != "sqlite_sequence" ORDER BY name',
123:             []
124:         ];
125:     }
126: 
127:     /**
128:      * {@inheritDoc}
129:      */
130:     public function describeColumnSql($tableName, $config)
131:     {
132:         $sql = sprintf(
133:             'PRAGMA table_info(%s)',
134:             $this->_driver->quoteIdentifier($tableName)
135:         );
136: 
137:         return [$sql, []];
138:     }
139: 
140:     /**
141:      * {@inheritDoc}
142:      */
143:     public function convertColumnDescription(TableSchema $schema, $row)
144:     {
145:         $field = $this->_convertColumn($row['type']);
146:         $field += [
147:             'null' => !$row['notnull'],
148:             'default' => $this->_defaultValue($row['dflt_value']),
149:         ];
150:         $primary = $schema->getConstraint('primary');
151: 
152:         if ($row['pk'] && empty($primary)) {
153:             $field['null'] = false;
154:             $field['autoIncrement'] = true;
155:         }
156: 
157:         // SQLite does not support autoincrement on composite keys.
158:         if ($row['pk'] && !empty($primary)) {
159:             $existingColumn = $primary['columns'][0];
160:             $schema->addColumn($existingColumn, ['autoIncrement' => null] + $schema->getColumn($existingColumn));
161:         }
162: 
163:         $schema->addColumn($row['name'], $field);
164:         if ($row['pk']) {
165:             $constraint = (array)$schema->getConstraint('primary') + [
166:                 'type' => TableSchema::CONSTRAINT_PRIMARY,
167:                 'columns' => []
168:             ];
169:             $constraint['columns'] = array_merge($constraint['columns'], [$row['name']]);
170:             $schema->addConstraint('primary', $constraint);
171:         }
172:     }
173: 
174:     /**
175:      * Manipulate the default value.
176:      *
177:      * Sqlite includes quotes and bared NULLs in default values.
178:      * We need to remove those.
179:      *
180:      * @param string|null $default The default value.
181:      * @return string|null
182:      */
183:     protected function _defaultValue($default)
184:     {
185:         if ($default === 'NULL') {
186:             return null;
187:         }
188: 
189:         // Remove quotes
190:         if (preg_match("/^'(.*)'$/", $default, $matches)) {
191:             return str_replace("''", "'", $matches[1]);
192:         }
193: 
194:         return $default;
195:     }
196: 
197:     /**
198:      * {@inheritDoc}
199:      */
200:     public function describeIndexSql($tableName, $config)
201:     {
202:         $sql = sprintf(
203:             'PRAGMA index_list(%s)',
204:             $this->_driver->quoteIdentifier($tableName)
205:         );
206: 
207:         return [$sql, []];
208:     }
209: 
210:     /**
211:      * {@inheritDoc}
212:      *
213:      * Since SQLite does not have a way to get metadata about all indexes at once,
214:      * additional queries are done here. Sqlite constraint names are not
215:      * stable, and the names for constraints will not match those used to create
216:      * the table. This is a limitation in Sqlite's metadata features.
217:      *
218:      */
219:     public function convertIndexDescription(TableSchema $schema, $row)
220:     {
221:         $sql = sprintf(
222:             'PRAGMA index_info(%s)',
223:             $this->_driver->quoteIdentifier($row['name'])
224:         );
225:         $statement = $this->_driver->prepare($sql);
226:         $statement->execute();
227:         $columns = [];
228:         foreach ($statement->fetchAll('assoc') as $column) {
229:             $columns[] = $column['name'];
230:         }
231:         $statement->closeCursor();
232:         if ($row['unique']) {
233:             $schema->addConstraint($row['name'], [
234:                 'type' => TableSchema::CONSTRAINT_UNIQUE,
235:                 'columns' => $columns
236:             ]);
237:         } else {
238:             $schema->addIndex($row['name'], [
239:                 'type' => TableSchema::INDEX_INDEX,
240:                 'columns' => $columns
241:             ]);
242:         }
243:     }
244: 
245:     /**
246:      * {@inheritDoc}
247:      */
248:     public function describeForeignKeySql($tableName, $config)
249:     {
250:         $sql = sprintf('PRAGMA foreign_key_list(%s)', $this->_driver->quoteIdentifier($tableName));
251: 
252:         return [$sql, []];
253:     }
254: 
255:     /**
256:      * {@inheritDoc}
257:      */
258:     public function convertForeignKeyDescription(TableSchema $schema, $row)
259:     {
260:         $name = $row['from'] . '_fk';
261: 
262:         $update = isset($row['on_update']) ? $row['on_update'] : '';
263:         $delete = isset($row['on_delete']) ? $row['on_delete'] : '';
264:         $data = [
265:             'type' => TableSchema::CONSTRAINT_FOREIGN,
266:             'columns' => [$row['from']],
267:             'references' => [$row['table'], $row['to']],
268:             'update' => $this->_convertOnClause($update),
269:             'delete' => $this->_convertOnClause($delete),
270:         ];
271: 
272:         if (isset($this->_constraintsIdMap[$schema->name()][$row['id']])) {
273:             $name = $this->_constraintsIdMap[$schema->name()][$row['id']];
274:         } else {
275:             $this->_constraintsIdMap[$schema->name()][$row['id']] = $name;
276:         }
277: 
278:         $schema->addConstraint($name, $data);
279:     }
280: 
281:     /**
282:      * {@inheritDoc}
283:      *
284:      * @throws \Cake\Database\Exception when the column type is unknown
285:      */
286:     public function columnSql(TableSchema $schema, $name)
287:     {
288:         $data = $schema->getColumn($name);
289:         $typeMap = [
290:             TableSchema::TYPE_BINARY_UUID => ' BINARY(16)',
291:             TableSchema::TYPE_UUID => ' CHAR(36)',
292:             TableSchema::TYPE_TINYINTEGER => ' TINYINT',
293:             TableSchema::TYPE_SMALLINTEGER => ' SMALLINT',
294:             TableSchema::TYPE_INTEGER => ' INTEGER',
295:             TableSchema::TYPE_BIGINTEGER => ' BIGINT',
296:             TableSchema::TYPE_BOOLEAN => ' BOOLEAN',
297:             TableSchema::TYPE_FLOAT => ' FLOAT',
298:             TableSchema::TYPE_DECIMAL => ' DECIMAL',
299:             TableSchema::TYPE_DATE => ' DATE',
300:             TableSchema::TYPE_TIME => ' TIME',
301:             TableSchema::TYPE_DATETIME => ' DATETIME',
302:             TableSchema::TYPE_TIMESTAMP => ' TIMESTAMP',
303:             TableSchema::TYPE_JSON => ' TEXT'
304:         ];
305: 
306:         $out = $this->_driver->quoteIdentifier($name);
307:         $hasUnsigned = [
308:             TableSchema::TYPE_TINYINTEGER,
309:             TableSchema::TYPE_SMALLINTEGER,
310:             TableSchema::TYPE_INTEGER,
311:             TableSchema::TYPE_BIGINTEGER,
312:             TableSchema::TYPE_FLOAT,
313:             TableSchema::TYPE_DECIMAL
314:         ];
315: 
316:         if (in_array($data['type'], $hasUnsigned, true) &&
317:             isset($data['unsigned']) && $data['unsigned'] === true
318:         ) {
319:             if ($data['type'] !== TableSchema::TYPE_INTEGER || [$name] !== (array)$schema->primaryKey()) {
320:                 $out .= ' UNSIGNED';
321:             }
322:         }
323: 
324:         if (isset($typeMap[$data['type']])) {
325:             $out .= $typeMap[$data['type']];
326:         }
327: 
328:         if ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] !== TableSchema::LENGTH_TINY) {
329:             $out .= ' TEXT';
330:         }
331: 
332:         if ($data['type'] === TableSchema::TYPE_STRING ||
333:             ($data['type'] === TableSchema::TYPE_TEXT && $data['length'] === TableSchema::LENGTH_TINY)
334:         ) {
335:             $out .= ' VARCHAR';
336: 
337:             if (isset($data['length'])) {
338:                 $out .= '(' . (int)$data['length'] . ')';
339:             }
340:         }
341: 
342:         if ($data['type'] === TableSchema::TYPE_BINARY) {
343:             if (isset($data['length'])) {
344:                 $out .= ' BLOB(' . (int)$data['length'] . ')';
345:             } else {
346:                 $out .= ' BLOB';
347:             }
348:         }
349: 
350:         $integerTypes = [
351:             TableSchema::TYPE_TINYINTEGER,
352:             TableSchema::TYPE_SMALLINTEGER,
353:             TableSchema::TYPE_INTEGER,
354:         ];
355:         if (in_array($data['type'], $integerTypes, true) &&
356:             isset($data['length']) && [$name] !== (array)$schema->primaryKey()
357:         ) {
358:                 $out .= '(' . (int)$data['length'] . ')';
359:         }
360: 
361:         $hasPrecision = [TableSchema::TYPE_FLOAT, TableSchema::TYPE_DECIMAL];
362:         if (in_array($data['type'], $hasPrecision, true) &&
363:             (isset($data['length']) || isset($data['precision']))
364:         ) {
365:             $out .= '(' . (int)$data['length'] . ',' . (int)$data['precision'] . ')';
366:         }
367: 
368:         if (isset($data['null']) && $data['null'] === false) {
369:             $out .= ' NOT NULL';
370:         }
371: 
372:         if ($data['type'] === TableSchema::TYPE_INTEGER && [$name] === (array)$schema->primaryKey()) {
373:             $out .= ' PRIMARY KEY AUTOINCREMENT';
374:         }
375: 
376:         if (isset($data['null']) && $data['null'] === true && $data['type'] === TableSchema::TYPE_TIMESTAMP) {
377:             $out .= ' DEFAULT NULL';
378:         }
379:         if (isset($data['default'])) {
380:             $out .= ' DEFAULT ' . $this->_driver->schemaValue($data['default']);
381:         }
382: 
383:         return $out;
384:     }
385: 
386:     /**
387:      * {@inheritDoc}
388:      *
389:      * Note integer primary keys will return ''. This is intentional as Sqlite requires
390:      * that integer primary keys be defined in the column definition.
391:      *
392:      */
393:     public function constraintSql(TableSchema $schema, $name)
394:     {
395:         $data = $schema->getConstraint($name);
396:         if ($data['type'] === TableSchema::CONSTRAINT_PRIMARY &&
397:             count($data['columns']) === 1 &&
398:             $schema->getColumn($data['columns'][0])['type'] === TableSchema::TYPE_INTEGER
399:         ) {
400:             return '';
401:         }
402:         $clause = '';
403:         $type = '';
404:         if ($data['type'] === TableSchema::CONSTRAINT_PRIMARY) {
405:             $type = 'PRIMARY KEY';
406:         }
407:         if ($data['type'] === TableSchema::CONSTRAINT_UNIQUE) {
408:             $type = 'UNIQUE';
409:         }
410:         if ($data['type'] === TableSchema::CONSTRAINT_FOREIGN) {
411:             $type = 'FOREIGN KEY';
412: 
413:             $clause = sprintf(
414:                 ' REFERENCES %s (%s) ON UPDATE %s ON DELETE %s',
415:                 $this->_driver->quoteIdentifier($data['references'][0]),
416:                 $this->_convertConstraintColumns($data['references'][1]),
417:                 $this->_foreignOnClause($data['update']),
418:                 $this->_foreignOnClause($data['delete'])
419:             );
420:         }
421:         $columns = array_map(
422:             [$this->_driver, 'quoteIdentifier'],
423:             $data['columns']
424:         );
425: 
426:         return sprintf(
427:             'CONSTRAINT %s %s (%s)%s',
428:             $this->_driver->quoteIdentifier($name),
429:             $type,
430:             implode(', ', $columns),
431:             $clause
432:         );
433:     }
434: 
435:     /**
436:      * {@inheritDoc}
437:      *
438:      * SQLite can not properly handle adding a constraint to an existing table.
439:      * This method is no-op
440:      */
441:     public function addConstraintSql(TableSchema $schema)
442:     {
443:         return [];
444:     }
445: 
446:     /**
447:      * {@inheritDoc}
448:      *
449:      * SQLite can not properly handle dropping a constraint to an existing table.
450:      * This method is no-op
451:      */
452:     public function dropConstraintSql(TableSchema $schema)
453:     {
454:         return [];
455:     }
456: 
457:     /**
458:      * {@inheritDoc}
459:      */
460:     public function indexSql(TableSchema $schema, $name)
461:     {
462:         $data = $schema->getIndex($name);
463:         $columns = array_map(
464:             [$this->_driver, 'quoteIdentifier'],
465:             $data['columns']
466:         );
467: 
468:         return sprintf(
469:             'CREATE INDEX %s ON %s (%s)',
470:             $this->_driver->quoteIdentifier($name),
471:             $this->_driver->quoteIdentifier($schema->name()),
472:             implode(', ', $columns)
473:         );
474:     }
475: 
476:     /**
477:      * {@inheritDoc}
478:      */
479:     public function createTableSql(TableSchema $schema, $columns, $constraints, $indexes)
480:     {
481:         $lines = array_merge($columns, $constraints);
482:         $content = implode(",\n", array_filter($lines));
483:         $temporary = $schema->isTemporary() ? ' TEMPORARY ' : ' ';
484:         $table = sprintf("CREATE%sTABLE \"%s\" (\n%s\n)", $temporary, $schema->name(), $content);
485:         $out = [$table];
486:         foreach ($indexes as $index) {
487:             $out[] = $index;
488:         }
489: 
490:         return $out;
491:     }
492: 
493:     /**
494:      * {@inheritDoc}
495:      */
496:     public function truncateTableSql(TableSchema $schema)
497:     {
498:         $name = $schema->name();
499:         $sql = [];
500:         if ($this->hasSequences()) {
501:             $sql[] = sprintf('DELETE FROM sqlite_sequence WHERE name="%s"', $name);
502:         }
503: 
504:         $sql[] = sprintf('DELETE FROM "%s"', $name);
505: 
506:         return $sql;
507:     }
508: 
509:     /**
510:      * Returns whether there is any table in this connection to SQLite containing
511:      * sequences
512:      *
513:      * @return bool
514:      */
515:     public function hasSequences()
516:     {
517:         $result = $this->_driver->prepare(
518:             'SELECT 1 FROM sqlite_master WHERE name = "sqlite_sequence"'
519:         );
520:         $result->execute();
521:         $this->_hasSequences = (bool)$result->rowCount();
522:         $result->closeCursor();
523: 
524:         return $this->_hasSequences;
525:     }
526: }
527: