SqliteSchemaManager.php
No OneTemporary
Actions

Size

22 KB

Referenced Files

None

Subscribers

None

SqliteSchemaManager.php
View Options

	<?php

	namespace Doctrine\DBAL\Schema;

	use Doctrine\DBAL\DriverManager;
	use Doctrine\DBAL\Exception;
	use Doctrine\DBAL\Platforms\SQLite;
	use Doctrine\DBAL\Platforms\SqlitePlatform;
	use Doctrine\DBAL\Result;
	use Doctrine\DBAL\Types\StringType;
	use Doctrine\DBAL\Types\TextType;
	use Doctrine\DBAL\Types\Type;
	use Doctrine\Deprecations\Deprecation;

	use function array_change_key_case;
	use function array_map;
	use function array_merge;
	use function count;
	use function explode;
	use function file_exists;
	use function implode;
	use function preg_match;
	use function preg_match_all;
	use function preg_quote;
	use function preg_replace;
	use function rtrim;
	use function str_replace;
	use function strcasecmp;
	use function strpos;
	use function strtolower;
	use function trim;
	use function unlink;
	use function usort;

	use const CASE_LOWER;

	/**
	* Sqlite SchemaManager.
	*
	* @extends AbstractSchemaManager<SqlitePlatform>
	*/
	class SqliteSchemaManager extends AbstractSchemaManager
	{
	/**
	* {@inheritDoc}
	*/
	public function listTableNames()
	{
	return $this->doListTableNames();
	}

	/**
	* {@inheritDoc}
	*/
	public function listTables()
	{
	return $this->doListTables();
	}

	/**
	* {@inheritDoc}
	*
	* @deprecated Use {@see introspectTable()} instead.
	*/
	public function listTableDetails($name)
	{
	Deprecation::triggerIfCalledFromOutside(
	'doctrine/dbal',
	'https://github.com/doctrine/dbal/pull/5595',
	'%s is deprecated. Use introspectTable() instead.',
	__METHOD__,
	);

	return $this->doListTableDetails($name);
	}

	/**
	* {@inheritDoc}
	*/
	public function listTableColumns($table, $database = null)
	{
	return $this->doListTableColumns($table, $database);
	}

	/**
	* {@inheritDoc}
	*/
	public function listTableIndexes($table)
	{
	return $this->doListTableIndexes($table);
	}

	/**
	* {@inheritDoc}
	*/
	protected function fetchForeignKeyColumnsByTable(string $databaseName): array
	{
	$columnsByTable = parent::fetchForeignKeyColumnsByTable($databaseName);

	if (count($columnsByTable) > 0) {
	foreach ($columnsByTable as $table => $columns) {
	$columnsByTable[$table] = $this->addDetailsToTableForeignKeyColumns($table, $columns);
	}
	}

	return $columnsByTable;
	}

	/**
	* {@inheritDoc}
	*
	* @deprecated Delete the database file using the filesystem.
	*/
	public function dropDatabase($database)
	{
	Deprecation::trigger(
	'doctrine/dbal',
	'https://github.com/doctrine/dbal/issues/4963',
	'SqliteSchemaManager::dropDatabase() is deprecated. Delete the database file using the filesystem.',
	);

	if (! file_exists($database)) {
	return;
	}

	unlink($database);
	}

	/**
	* {@inheritDoc}
	*
	* @deprecated The engine will create the database file automatically.
	*/
	public function createDatabase($database)
	{
	Deprecation::trigger(
	'doctrine/dbal',
	'https://github.com/doctrine/dbal/issues/4963',
	'SqliteSchemaManager::createDatabase() is deprecated.'
	. ' The engine will create the database file automatically.',
	);

	$params = $this->_conn->getParams();

	$params['path'] = $database;
	unset($params['memory']);

	$conn = DriverManager::getConnection($params);
	$conn->connect();
	$conn->close();
	}

	/**
	* {@inheritDoc}
	*/
	public function createForeignKey(ForeignKeyConstraint $foreignKey, $table)
	{
	if (! $table instanceof Table) {
	$table = $this->listTableDetails($table);
	}

	$this->alterTable(new TableDiff($table->getName(), [], [], [], [], [], [], $table, [$foreignKey]));
	}

	/**
	* {@inheritDoc}
	*
	* @deprecated Use {@see dropForeignKey()} and {@see createForeignKey()} instead.
	*/
	public function dropAndCreateForeignKey(ForeignKeyConstraint $foreignKey, $table)
	{
	Deprecation::trigger(
	'doctrine/dbal',
	'https://github.com/doctrine/dbal/pull/4897',
	'SqliteSchemaManager::dropAndCreateForeignKey() is deprecated.'
	. ' Use SqliteSchemaManager::dropForeignKey() and SqliteSchemaManager::createForeignKey() instead.',
	);

	if (! $table instanceof Table) {
	$table = $this->listTableDetails($table);
	}

	$this->alterTable(new TableDiff($table->getName(), [], [], [], [], [], [], $table, [], [$foreignKey]));
	}

	/**
	* {@inheritDoc}
	*/
	public function dropForeignKey($foreignKey, $table)
	{
	if (! $table instanceof Table) {
	$table = $this->listTableDetails($table);
	}

	$this->alterTable(new TableDiff($table->getName(), [], [], [], [], [], [], $table, [], [], [$foreignKey]));
	}

	/**
	* {@inheritDoc}
	*/
	public function listTableForeignKeys($table, $database = null)
	{
	$table = $this->normalizeName($table);

	$columns = $this->selectForeignKeyColumns('', $table)
	->fetchAllAssociative();

	if (count($columns) > 0) {
	$columns = $this->addDetailsToTableForeignKeyColumns($table, $columns);
	}

	return $this->_getPortableTableForeignKeysList($columns);
	}

	/**
	* {@inheritDoc}
	*/
	protected function _getPortableTableDefinition($table)
	{
	return $table['table_name'];
	}

	/**
	* {@inheritDoc}
	*
	* @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html
	*/
	protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
	{
	$indexBuffer = [];

	// fetch primary
	$indexArray = $this->_conn->fetchAllAssociative('SELECT * FROM PRAGMA_TABLE_INFO (?)', [$tableName]);

	usort(
	$indexArray,
	/**
	* @param array<string,mixed> $a
	* @param array<string,mixed> $b
	*/
	static function (array $a, array $b): int {
	if ($a['pk'] === $b['pk']) {
	return $a['cid'] - $b['cid'];
	}

	return $a['pk'] - $b['pk'];
	},
	);

	foreach ($indexArray as $indexColumnRow) {
	if ($indexColumnRow['pk'] === 0 \|\| $indexColumnRow['pk'] === '0') {
	continue;
	}

	$indexBuffer[] = [
	'key_name' => 'primary',
	'primary' => true,
	'non_unique' => false,
	'column_name' => $indexColumnRow['name'],
	];
	}

	// fetch regular indexes
	foreach ($tableIndexes as $tableIndex) {
	// Ignore indexes with reserved names, e.g. autoindexes
	if (strpos($tableIndex['name'], 'sqlite_') === 0) {
	continue;
	}

	$keyName = $tableIndex['name'];
	$idx = [];
	$idx['key_name'] = $keyName;
	$idx['primary'] = false;
	$idx['non_unique'] = ! $tableIndex['unique'];

	$indexArray = $this->_conn->fetchAllAssociative('SELECT * FROM PRAGMA_INDEX_INFO (?)', [$keyName]);

	foreach ($indexArray as $indexColumnRow) {
	$idx['column_name'] = $indexColumnRow['name'];
	$indexBuffer[] = $idx;
	}
	}

	return parent::_getPortableTableIndexesList($indexBuffer, $tableName);
	}

	/**
	* {@inheritDoc}
	*/
	protected function _getPortableTableColumnList($table, $database, $tableColumns)
	{
	$list = parent::_getPortableTableColumnList($table, $database, $tableColumns);

	// find column with autoincrement
	$autoincrementColumn = null;
	$autoincrementCount = 0;

	foreach ($tableColumns as $tableColumn) {
	if ($tableColumn['pk'] === 0 \|\| $tableColumn['pk'] === '0') {
	continue;
	}

	$autoincrementCount++;
	if ($autoincrementColumn !== null \|\| strtolower($tableColumn['type']) !== 'integer') {
	continue;
	}

	$autoincrementColumn = $tableColumn['name'];
	}

	if ($autoincrementCount === 1 && $autoincrementColumn !== null) {
	foreach ($list as $column) {
	if ($autoincrementColumn !== $column->getName()) {
	continue;
	}

	$column->setAutoincrement(true);
	}
	}

	// inspect column collation and comments
	$createSql = $this->getCreateTableSQL($table);

	foreach ($list as $columnName => $column) {
	$type = $column->getType();

	if ($type instanceof StringType \|\| $type instanceof TextType) {
	$column->setPlatformOption(
	'collation',
	$this->parseColumnCollationFromSQL($columnName, $createSql) ?? 'BINARY',
	);
	}

	$comment = $this->parseColumnCommentFromSQL($columnName, $createSql);

	if ($comment === null) {
	continue;
	}

	$type = $this->extractDoctrineTypeFromComment($comment, '');

	if ($type !== '') {
	$column->setType(Type::getType($type));

	$comment = $this->removeDoctrineTypeFromComment($comment, $type);
	}

	$column->setComment($comment);
	}

	return $list;
	}

	/**
	* {@inheritDoc}
	*/
	protected function _getPortableTableColumnDefinition($tableColumn)
	{
	$parts = explode('(', $tableColumn['type']);
	$tableColumn['type'] = trim($parts[0]);
	if (isset($parts[1])) {
	$length = trim($parts[1], ')');
	$tableColumn['length'] = $length;
	}

	$dbType = strtolower($tableColumn['type']);
	$length = $tableColumn['length'] ?? null;
	$unsigned = false;

	if (strpos($dbType, ' unsigned') !== false) {
	$dbType = str_replace(' unsigned', '', $dbType);
	$unsigned = true;
	}

	$fixed = false;
	$type = $this->_platform->getDoctrineTypeMapping($dbType);
	$default = $tableColumn['dflt_value'];
	if ($default === 'NULL') {
	$default = null;
	}

	if ($default !== null) {
	// SQLite returns the default value as a literal expression, so we need to parse it
	if (preg_match('/^\'(.*)\'$/s', $default, $matches) === 1) {
	$default = str_replace("''", "'", $matches[1]);
	}
	}

	$notnull = (bool) $tableColumn['notnull'];

	if (! isset($tableColumn['name'])) {
	$tableColumn['name'] = '';
	}

	$precision = null;
	$scale = null;

	switch ($dbType) {
	case 'char':
	$fixed = true;
	break;
	case 'float':
	case 'double':
	case 'real':
	case 'decimal':
	case 'numeric':
	if (isset($tableColumn['length'])) {
	if (strpos($tableColumn['length'], ',') === false) {
	$tableColumn['length'] .= ',0';
	}

	[$precision, $scale] = array_map('trim', explode(',', $tableColumn['length']));
	}

	$length = null;
	break;
	}

	$options = [
	'length' => $length,
	'unsigned' => $unsigned,
	'fixed' => $fixed,
	'notnull' => $notnull,
	'default' => $default,
	'precision' => $precision,
	'scale' => $scale,
	];

	return new Column($tableColumn['name'], Type::getType($type), $options);
	}

	/**
	* {@inheritDoc}
	*/
	protected function _getPortableViewDefinition($view)
	{
	return new View($view['name'], $view['sql']);
	}

	/**
	* {@inheritDoc}
	*/
	protected function _getPortableTableForeignKeysList($tableForeignKeys)
	{
	$list = [];
	foreach ($tableForeignKeys as $value) {
	$value = array_change_key_case($value, CASE_LOWER);
	$id = $value['id'];
	if (! isset($list[$id])) {
	if (! isset($value['on_delete']) \|\| $value['on_delete'] === 'RESTRICT') {
	$value['on_delete'] = null;
	}

	if (! isset($value['on_update']) \|\| $value['on_update'] === 'RESTRICT') {
	$value['on_update'] = null;
	}

	$list[$id] = [
	'name' => $value['constraint_name'],
	'local' => [],
	'foreign' => [],
	'foreignTable' => $value['table'],
	'onDelete' => $value['on_delete'],
	'onUpdate' => $value['on_update'],
	'deferrable' => $value['deferrable'],
	'deferred' => $value['deferred'],
	];
	}

	$list[$id]['local'][] = $value['from'];

	if ($value['to'] === null) {
	// Inferring a shorthand form for the foreign key constraint, where the "to" field is empty.
	// @see https://www.sqlite.org/foreignkeys.html#fk_indexes.
	$foreignTableIndexes = $this->_getPortableTableIndexesList([], $value['table']);

	if (! isset($foreignTableIndexes['primary'])) {
	continue;
	}

	$list[$id]['foreign'] = [...$list[$id]['foreign'], ...$foreignTableIndexes['primary']->getColumns()];

	continue;
	}

	$list[$id]['foreign'][] = $value['to'];
	}

	return parent::_getPortableTableForeignKeysList($list);
	}

	/**
	* {@inheritDoc}
	*/
	protected function _getPortableTableForeignKeyDefinition($tableForeignKey): ForeignKeyConstraint
	{
	return new ForeignKeyConstraint(
	$tableForeignKey['local'],
	$tableForeignKey['foreignTable'],
	$tableForeignKey['foreign'],
	$tableForeignKey['name'],
	[
	'onDelete' => $tableForeignKey['onDelete'],
	'onUpdate' => $tableForeignKey['onUpdate'],
	'deferrable' => $tableForeignKey['deferrable'],
	'deferred' => $tableForeignKey['deferred'],
	],
	);
	}

	private function parseColumnCollationFromSQL(string $column, string $sql): ?string
	{
	$pattern = '{(?:\W' . preg_quote($column) . '\W\|\W'
	. preg_quote($this->_platform->quoteSingleIdentifier($column))
	. '\W)[^,(]+(?:$[^()]+$[^,])?(?:(?:DEFAULT\|CHECK)\s(?:$.?$)?[^,])*COLLATE\s+["\']?([^\s,"\')]+)}is';

	if (preg_match($pattern, $sql, $match) !== 1) {
	return null;
	}

	return $match[1];
	}

	private function parseTableCommentFromSQL(string $table, string $sql): ?string
	{
	$pattern = '/\s* # Allow whitespace characters at start of line
	CREATE\sTABLE # Match "CREATE TABLE"
	(?:\W"' . preg_quote($this->_platform->quoteSingleIdentifier($table), '/') . '"\W\|\W' . preg_quote($table, '/')
	. '\W) # Match table name (quoted and unquoted)
	( # Start capture
	(?:\s--[^\n]\n?)+ # Capture anything that starts with whitespaces followed by -- until the end of the line(s)
	)/ix';

	if (preg_match($pattern, $sql, $match) !== 1) {
	return null;
	}

	$comment = preg_replace('{^\s*--}m', '', rtrim($match[1], "\n"));

	return $comment === '' ? null : $comment;
	}

	private function parseColumnCommentFromSQL(string $column, string $sql): ?string
	{
	$pattern = '{[\s(,](?:\W' . preg_quote($this->_platform->quoteSingleIdentifier($column))
	. '\W\|\W' . preg_quote($column) . '\W)(?:$[^)]?$\|[^,(])?,?((?:(?!\n))(?:\s--[^\n]\n?)+)}i';

	if (preg_match($pattern, $sql, $match) !== 1) {
	return null;
	}

	$comment = preg_replace('{^\s*--}m', '', rtrim($match[1], "\n"));

	return $comment === '' ? null : $comment;
	}

	/** @throws Exception */
	private function getCreateTableSQL(string $table): string
	{
	$sql = $this->_conn->fetchOne(
	<<<'SQL'
	SELECT sql
	FROM (
	SELECT *
	FROM sqlite_master
	UNION ALL
	SELECT *
	FROM sqlite_temp_master
	)
	WHERE type = 'table'
	AND name = ?
	SQL
	,
	[$table],
	);

	if ($sql !== false) {
	return $sql;
	}

	return '';
	}

	/**
	* @param list<array<string,mixed>> $columns
	*
	* @return list<array<string,mixed>>
	*
	* @throws Exception
	*/
	private function addDetailsToTableForeignKeyColumns(string $table, array $columns): array
	{
	$foreignKeyDetails = $this->getForeignKeyDetails($table);
	$foreignKeyCount = count($foreignKeyDetails);

	foreach ($columns as $i => $column) {
	// SQLite identifies foreign keys in reverse order of appearance in SQL
	$columns[$i] = array_merge($column, $foreignKeyDetails[$foreignKeyCount - $column['id'] - 1]);
	}

	return $columns;
	}

	/**
	* @param string $table
	*
	* @return list<array<string, mixed>>
	*
	* @throws Exception
	*/
	private function getForeignKeyDetails($table)
	{
	$createSql = $this->getCreateTableSQL($table);

	if (
	preg_match_all(
	'#
	(?:CONSTRAINT\s+(\S+)\s+)?
	(?:FOREIGN\s+KEY[^)]+\)\s*)?
	REFERENCES\s+\S+\s*(?:$[^)]+$)?
	(?:
	[^,]*?
	(NOT\s+DEFERRABLE\|DEFERRABLE)
	(?:\s+INITIALLY\s+(DEFERRED\|IMMEDIATE))?
	)?#isx',
	$createSql,
	$match,
	) === 0
	) {
	return [];
	}

	$names = $match[1];
	$deferrable = $match[2];
	$deferred = $match[3];
	$details = [];

	for ($i = 0, $count = count($match[0]); $i < $count; $i++) {
	$details[] = [
	'constraint_name' => isset($names[$i]) && $names[$i] !== '' ? $names[$i] : null,
	'deferrable' => isset($deferrable[$i]) && strcasecmp($deferrable[$i], 'deferrable') === 0,
	'deferred' => isset($deferred[$i]) && strcasecmp($deferred[$i], 'deferred') === 0,
	];
	}

	return $details;
	}

	public function createComparator(): Comparator
	{
	return new SQLite\Comparator($this->_platform);
	}

	/**
	* {@inheritDoc}
	*
	* @deprecated
	*/
	public function getSchemaSearchPaths()
	{
	Deprecation::triggerIfCalledFromOutside(
	'doctrine/dbal',
	'https://github.com/doctrine/dbal/pull/4821',
	'SqliteSchemaManager::getSchemaSearchPaths() is deprecated.',
	);

	// SQLite does not support schemas or databases
	return [];
	}

	protected function selectTableNames(string $databaseName): Result
	{
	$sql = <<<'SQL'
	SELECT name AS table_name
	FROM sqlite_master
	WHERE type = 'table'
	AND name != 'sqlite_sequence'
	AND name != 'geometry_columns'
	AND name != 'spatial_ref_sys'
	UNION ALL
	SELECT name
	FROM sqlite_temp_master
	WHERE type = 'table'
	ORDER BY name
	SQL;

	return $this->_conn->executeQuery($sql);
	}

	protected function selectTableColumns(string $databaseName, ?string $tableName = null): Result
	{
	$sql = <<<'SQL'
	SELECT t.name AS table_name,
	c.*
	FROM sqlite_master t
	JOIN pragma_table_info(t.name) c
	SQL;

	$conditions = [
	"t.type = 'table'",
	"t.name NOT IN ('geometry_columns', 'spatial_ref_sys', 'sqlite_sequence')",
	];
	$params = [];

	if ($tableName !== null) {
	$conditions[] = 't.name = ?';
	$params[] = str_replace('.', '__', $tableName);
	}

	$sql .= ' WHERE ' . implode(' AND ', $conditions) . ' ORDER BY t.name, c.cid';

	return $this->_conn->executeQuery($sql, $params);
	}

	protected function selectIndexColumns(string $databaseName, ?string $tableName = null): Result
	{
	$sql = <<<'SQL'
	SELECT t.name AS table_name,
	i.*
	FROM sqlite_master t
	JOIN pragma_index_list(t.name) i
	SQL;

	$conditions = [
	"t.type = 'table'",
	"t.name NOT IN ('geometry_columns', 'spatial_ref_sys', 'sqlite_sequence')",
	];
	$params = [];

	if ($tableName !== null) {
	$conditions[] = 't.name = ?';
	$params[] = str_replace('.', '__', $tableName);
	}

	$sql .= ' WHERE ' . implode(' AND ', $conditions) . ' ORDER BY t.name, i.seq';

	return $this->_conn->executeQuery($sql, $params);
	}

	protected function selectForeignKeyColumns(string $databaseName, ?string $tableName = null): Result
	{
	$sql = <<<'SQL'
	SELECT t.name AS table_name,
	p.*
	FROM sqlite_master t
	JOIN pragma_foreign_key_list(t.name) p
	ON p."seq" != '-1'
	SQL;

	$conditions = [
	"t.type = 'table'",
	"t.name NOT IN ('geometry_columns', 'spatial_ref_sys', 'sqlite_sequence')",
	];
	$params = [];

	if ($tableName !== null) {
	$conditions[] = 't.name = ?';
	$params[] = str_replace('.', '__', $tableName);
	}

	$sql .= ' WHERE ' . implode(' AND ', $conditions) . ' ORDER BY t.name, p.id DESC, p.seq';

	return $this->_conn->executeQuery($sql, $params);
	}

	/**
	* {@inheritDoc}
	*/
	protected function fetchTableOptionsByTable(string $databaseName, ?string $tableName = null): array
	{
	if ($tableName === null) {
	$tables = $this->listTableNames();
	} else {
	$tables = [$tableName];
	}

	$tableOptions = [];
	foreach ($tables as $table) {
	$comment = $this->parseTableCommentFromSQL($table, $this->getCreateTableSQL($table));

	if ($comment === null) {
	continue;
	}

	$tableOptions[$table]['comment'] = $comment;
	}

	return $tableOptions;
	}
	}

File Metadata

Mime Type: text/x-php
Expires: Wed, Aug 19, 14:05 (3 w, 3 d ago)
Storage Engine: local-disk
Storage Format: Raw Data
Storage Handle: f3/e9/3a9dc67a14498ddb8b81c1db22e0
Default Alt Text: SqliteSchemaManager.php (22 KB)

SqliteSchemaManager.phpNo OneTemporaryActions

SqliteSchemaManager.phpView Options

File Metadata

Event Timeline

SqliteSchemaManager.php
No OneTemporary
Actions

SqliteSchemaManager.php
View Options