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