SqliteSchema.php

<?php
/**
 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
 * Copyright (c) Cake Software Foundation, Inc. (http://cakefoundation.org)
 *
 * Licensed under The MIT License
 * For full copyright and license information, please see the LICENSE.txt
 * Redistributions of files must retain the above copyright notice.
 *
 * @copyright     Copyright (c) Cake Software Foundation, Inc. (http://cakefoundation.org)
 * @link          http://cakephp.org CakePHP(tm) Project
 * @since         CakePHP(tm) v 3.0.0
 * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)
 */
namespace Cake\Database\Schema;

use Cake\Database\Exception;
use Cake\Database\Schema\Table;

/**
 * Schema management/reflection features for Sqlite
 */
class SqliteSchema extends BaseSchema {

/**
 * Whether or not this connection contains sequences
 *
 * @return boolean
 */
	protected $_hasSequences;

/**
 * Convert a column definition to the abstract types.
 *
 * The returned type will be a type that
 * Cake\Database\Type can handle.
 *
 * @param string $column The column type + length
 * @throws Cake\Database\Exception when unable to parse column type
 * @return array Array of column information.
 */
	protected function _convertColumn($column) {
		preg_match('/(unsigned)?\s*([a-z]+)(?:\(([0-9,]+)\))?/i', $column, $matches);
		if (empty($matches)) {
			throw new Exception(__d('cake_dev', 'Unable to parse column type from "%s"', $column));
		}

		$unsigned = false;
		if (strtolower($matches[1]) === 'unsigned') {
			$unsigned = true;
		}

		$col = strtolower($matches[2]);
		$length = null;
		if (isset($matches[3])) {
			$length = (int)$matches[3];
		}

		if ($col === 'bigint') {
			return ['type' => 'biginteger', 'length' => $length, 'unsigned' => $unsigned];
		}
		if (strpos($col, 'decimal') !== false) {
			return ['type' => 'decimal', 'length' => null, 'unsigned' => $unsigned];
		}
		if (strpos($col, 'int') !== false) {
			return ['type' => 'integer', 'length' => $length, 'unsigned' => $unsigned];
		}
		if (in_array($col, ['float', 'real', 'double'])) {
			return ['type' => 'float', 'length' => null, 'unsigned' => $unsigned];
		}

		if (strpos($col, 'boolean') !== false) {
			return ['type' => 'boolean', 'length' => null];
		}

		if ($col === 'char' && $length === 36) {
			return ['type' => 'uuid', 'length' => null];
		}
		if ($col === 'char') {
			return ['type' => 'string', 'fixed' => true, 'length' => $length];
		}
		if (strpos($col, 'char') !== false) {
			return ['type' => 'string', 'length' => $length];
		}

		if (in_array($col, ['blob', 'clob'])) {
			return ['type' => 'binary', 'length' => null];
		}
		if (in_array($col, ['date', 'time', 'timestamp', 'datetime'])) {
			return ['type' => $col, 'length' => null];
		}

		return ['type' => 'text', 'length' => null];
	}

/**
 * {@inheritdoc}
 *
 */
	public function listTablesSql($config) {
		return ['SELECT name FROM sqlite_master WHERE type="table" ORDER BY name', []];
	}

/**
 * {@inheritdoc}
 *
 */
	public function describeTableSql($name, $config) {
		$sql = sprintf(
			'PRAGMA table_info(%s)',
			$this->_driver->quoteIdentifier($name)
		);
		return [$sql, []];
	}

/**
 * {@inheritdoc}
 *
 */
	public function convertFieldDescription(Table $table, $row) {
		$field = $this->_convertColumn($row['type']);
		$field += [
			'null' => !$row['notnull'],
			'default' => $row['dflt_value'] === null ? null : trim($row['dflt_value'], "'"),
		];
		if ($row['pk'] == true) {
			$field['null'] = false;
		}
		$table->addColumn($row['name'], $field);
		if ($row['pk'] == true) {
			$table->addConstraint('primary', [
				'type' => Table::CONSTRAINT_PRIMARY,
				'columns' => [$row['name']]
			]);
		}
	}

/**
 * {@inheritdoc}
 *
 */
	public function describeIndexSql($table, $config) {
		$sql = sprintf(
			'PRAGMA index_list(%s)',
			$this->_driver->quoteIdentifier($table)
		);
		return [$sql, []];
	}

/**
 * {@inheritdoc}
 *
 * Since SQLite does not have a way to get metadata about all indexes at once,
 * additional queries are done here. Sqlite constraint names are not
 * stable, and the names for constraints will not match those used to create
 * the table. This is a limitation in Sqlite's metadata features.
 *
 */
	public function convertIndexDescription(Table $table, $row) {
		$sql = sprintf(
			'PRAGMA index_info(%s)',
			$this->_driver->quoteIdentifier($row['name'])
		);
		$statement = $this->_driver->prepare($sql);
		$statement->execute();
		$columns = [];
		foreach ($statement->fetchAll('assoc') as $column) {
			$columns[] = $column['name'];
		}
		$statement->closeCursor();
		if ($row['unique']) {
			$table->addConstraint($row['name'], [
				'type' => Table::CONSTRAINT_UNIQUE,
				'columns' => $columns
			]);
		} else {
			$table->addIndex($row['name'], [
				'type' => Table::INDEX_INDEX,
				'columns' => $columns
			]);
		}
	}

/**
 * {@inheritdoc}
 *
 */
	public function describeForeignKeySql($table, $config) {
		$sql = sprintf('PRAGMA foreign_key_list(%s)', $this->_driver->quoteIdentifier($table));
		return [$sql, []];
	}

/**
 * {@inheritdoc}
 *
 */
	public function convertForeignKeyDescription(Table $table, $row) {
		$data = [
			'type' => Table::CONSTRAINT_FOREIGN,
			'columns' => [$row['from']],
			'references' => [$row['table'], $row['to']],
			'update' => $this->_convertOnClause($row['on_update']),
			'delete' => $this->_convertOnClause($row['on_delete']),
		];
		$name = $row['from'] . '_fk';
		$table->addConstraint($name, $data);
	}

/**
 * {@inheritdoc}
 *
 * @throws Cake\Database\Exception when the column type is unknown
 */
	public function columnSql(Table $table, $name) {
		$data = $table->column($name);
		$typeMap = [
			'uuid' => ' CHAR(36)',
			'string' => ' VARCHAR',
			'integer' => ' INTEGER',
			'biginteger' => ' BIGINT',
			'boolean' => ' BOOLEAN',
			'binary' => ' BLOB',
			'float' => ' FLOAT',
			'decimal' => ' DECIMAL',
			'text' => ' TEXT',
			'date' => ' DATE',
			'time' => ' TIME',
			'datetime' => ' DATETIME',
			'timestamp' => ' TIMESTAMP',
		];
		if (!isset($typeMap[$data['type']])) {
			throw new Exception(__d('cake_dev', 'Unknown column type for "%s"', $name));
		}

		$out = $this->_driver->quoteIdentifier($name);
		$hasUnsigned = ['biginteger', 'integer', 'float', 'decimal'];

		if (
			in_array($data['type'], $hasUnsigned, true) &&
			isset($data['unsigned']) && $data['unsigned'] === true
		) {
			$out .= ' UNSIGNED';
		}
		$out .= $typeMap[$data['type']];

		$hasLength = ['integer', 'string'];
		if (in_array($data['type'], $hasLength, true) && isset($data['length'])) {
			$out .= '(' . (int)$data['length'] . ')';
		}
		$hasPrecision = ['float', 'decimal'];
		if (
			in_array($data['type'], $hasPrecision, true) &&
			(isset($data['length']) || isset($data['precision']))
		) {
			$out .= '(' . (int)$data['length'] . ',' . (int)$data['precision'] . ')';
		}

		if (isset($data['null']) && $data['null'] === false) {
			$out .= ' NOT NULL';
		}
		if ($data['type'] === 'integer' && $name == $table->primaryKey()[0]) {
			$out .= ' PRIMARY KEY AUTOINCREMENT';
		}
		if (isset($data['null']) && $data['null'] === true) {
			$out .= ' DEFAULT NULL';
			unset($data['default']);
		}
		if (isset($data['default'])) {
			$out .= ' DEFAULT ' . $this->_driver->schemaValue($data['default']);
		}
		return $out;
	}

/**
 * {@inheritdoc}
 *
 * Note integer primary keys will return ''. This is intentional as Sqlite requires
 * that integer primary keys be defined in the column definition.
 *
 */
	public function constraintSql(Table $table, $name) {
		$data = $table->constraint($name);
		if (
			$data['type'] === Table::CONSTRAINT_PRIMARY &&
			count($data['columns']) === 1 &&
			$table->column($data['columns'][0])['type'] === 'integer'
		) {
			return '';
		}
		$clause = '';
		if ($data['type'] === Table::CONSTRAINT_PRIMARY) {
			$type = 'PRIMARY KEY';
		}
		if ($data['type'] === Table::CONSTRAINT_UNIQUE) {
			$type = 'UNIQUE';
		}
		if ($data['type'] === Table::CONSTRAINT_FOREIGN) {
			$type = 'FOREIGN KEY';
			$clause = sprintf(
				' REFERENCES %s (%s) ON UPDATE %s ON DELETE %s',
				$this->_driver->quoteIdentifier($data['references'][0]),
				$this->_driver->quoteIdentifier($data['references'][1]),
				$this->_foreignOnClause($data['update']),
				$this->_foreignOnClause($data['delete'])
			);
		}
		$columns = array_map(
			[$this->_driver, 'quoteIdentifier'],
			$data['columns']
		);
		return sprintf('CONSTRAINT %s %s (%s)%s',
			$this->_driver->quoteIdentifier($name),
			$type,
			implode(', ', $columns),
			$clause
		);
	}

/**
 * {@inheritdoc}
 *
 */
	public function indexSql(Table $table, $name) {
		$data = $table->index($name);
		$columns = array_map(
			[$this->_driver, 'quoteIdentifier'],
			$data['columns']
		);
		return sprintf('CREATE INDEX %s ON %s (%s)',
			$this->_driver->quoteIdentifier($name),
			$this->_driver->quoteIdentifier($table->name()),
			implode(', ', $columns)
		);
	}

/**
 * {@inheritdoc}
 *
 */
	public function createTableSql(Table $table, $columns, $constraints, $indexes) {
		$lines = array_merge($columns, $constraints);
		$content = implode(",\n", array_filter($lines));
		$table = sprintf("CREATE TABLE \"%s\" (\n%s\n)", $table->name(), $content);
		$out = [$table];
		foreach ($indexes as $index) {
			$out[] = $index;
		}
		return $out;
	}

/**
 * {@inheritdoc}
 *
 */
	public function truncateTableSql(Table $table) {
		$name = $table->name();
		$sql = [];
		if ($this->hasSequences()) {
			$sql[] = sprintf('DELETE FROM sqlite_sequence WHERE name="%s"', $name);
		}

		$sql[] = sprintf('DELETE FROM "%s"', $name);
		return $sql;
	}

/**
 * Returns whether there is any table in this connection to SQLite contianing
 * sequences
 *
 * @return void
 */
	public function hasSequences() {
		if ($this->_hasSequences !== null) {
			return $this->_hasSequences;
		}
		$result = $this->_driver
			->prepare('SELECT 1 FROM sqlite_master WHERE name = "sqlite_sequence"');
		$result->execute();
		$this->_hasSequences = (bool)$result->rowCount();
		$result->closeCursor();
		return $this->_hasSequences;
	}

}