Class SqliteSchemaDialect

Schema management/reflection features for Sqlite

Namespace: Cake\Database\Schema

Property Summary

$_driver protected

Cake\Database\Driver

The driver instance being used.
$_hasSequences protected

bool

Whether there is any table in this connection to SQLite containing sequences.

Method Summary

__construct() public

Constructor
_applyTypeSpecificColumnConversion() protected

Tries to use a matching database type to convert a SQL column definition to an abstract type definition.
_convertColumn() protected

Convert a column definition to the abstract types.
_convertConstraintColumns() protected

Convert foreign key constraints references to a valid stringified list
_convertOnClause() protected

Convert string on clauses to the abstract ones.
_defaultValue() protected

Manipulate the default value.
_foreignOnClause() protected

Generate an ON clause for a foreign key.
_getTypeSpecificColumnSql() protected

Tries to use a matching database type to generate the SQL fragment for a single column in a table.
addConstraintSql() public

Generate the SQL queries needed to add foreign key constraints to the table
columnDefinitionSql() public

Create a SQL snippet for a column based on the array shape that describeColumns() creates.
columnSql() public

Generate the SQL fragment for a single column in a table.
constraintSql() public

Generate the SQL fragments for defining table constraints.
convertColumnDescription() public

Convert field description results into abstract schema fields.
convertForeignKeyDescription() public

Convert a foreign key description into constraints on the Table object.
convertIndexDescription() public deprecated

Convert an index description results into abstract schema indexes or constraints.
convertOptionsDescription() public deprecated

Convert options data into table options.
createTableSql() public

Generate the SQL to create a table.
describe() public

Get the column metadata for a table.
describeColumnSql() public

Generate the SQL to describe a table.
describeColumns() public

Get a list of column metadata as a array
describeForeignKeySql() public

Generate the SQL to describe the foreign keys in a table.
describeForeignKeys() public

Get a list of constraint metadata as a array
describeIndexSql() public

Generate the SQL to describe the indexes in a table.
describeIndexes() public

Get a list of index metadata as a array
describeOptions() public

Get platform specific options
describeOptionsSql() public deprecated

Generate the SQL to describe table options
dropConstraintSql() public

Generate the SQL queries needed to drop foreign key constraints from the table
dropTableSql() public

Generate the SQL to drop a table.
hasColumn() public

Check if a table has a column with a given name.
hasForeignKey() public

Check if a table has a foreign key with a given name.
hasIndex() public

Check if a table has an index with a given name.
hasSequences() public

Returns whether there is any table in this connection to SQLite containing sequences
hasTable() public

Check if a table exists
indexSql() public

Generate the SQL fragment for a single index in a table.
listTables() public

Get the list of tables and views available in the current connection.
listTablesSql() public

Generate the SQL to list the tables and views.
listTablesWithoutViews() public

Get the list of tables, excluding any views, available in the current connection.
listTablesWithoutViewsSql() public

Generate the SQL to list the tables, excluding all views.
normalizePossiblyQuotedIdentifier() protected

Removes possible escape characters and surrounding quotes from identifiers.
possiblyQuotedIdentifierRegex() protected

Generates a regular expression to match identifiers that may or may not be quoted with any of the supported quotes.
truncateTableSql() public

Generate the SQL to truncate a table.

Method Detail

__construct() ¶ public

__construct(Cake\Database\Driver $driver)

Constructor

This constructor will connect the driver so that methods like columnSql() and others will fail when the driver has not been connected.

Parameters

Cake\Database\Driver $driver: The driver to use.

_applyTypeSpecificColumnConversion() ¶ protected

_applyTypeSpecificColumnConversion(string $columnType, array $definition): array|null

Tries to use a matching database type to convert a SQL column definition to an abstract type definition.

Parameters

string $columnType: The column type.
array $definition: The column definition.

Returns

array|null

Array of column information, or null in case no corresponding type was found or the type didn't provide custom column information.

_convertColumn() ¶ protected

_convertColumn(string $column): array<string, mixed>

Convert a column definition to the abstract types.

The returned type will be a type that Cake\Database\TypeFactory can handle.

Parameters

string $column: The column type + length

Returns

array<string, mixed>

Array of column information.

Throws

Cake\Database\Exception\DatabaseException
when unable to parse column type

_convertConstraintColumns() ¶ protected

_convertConstraintColumns(array<string>|string $references): string

Convert foreign key constraints references to a valid stringified list

Parameters

array<string>|string $references: The referenced columns of a foreign key constraint statement

Returns

string

_convertOnClause() ¶ protected

_convertOnClause(string $clause): string

Convert string on clauses to the abstract ones.

Parameters

string $clause: The on clause to convert.

Returns

string

_defaultValue() ¶ protected

_defaultValue(string|int|null $default, string|null $type = null): string|int|null

Manipulate the default value.

Sqlite includes quotes and bared NULLs in default values. We need to remove those.

Parameters

string|int|null $default: The default value.
string|null $type optional: The column type.

Returns

string|int|null

_foreignOnClause() ¶ protected

_foreignOnClause(string $on): string

Generate an ON clause for a foreign key.

Parameters

string $on: The on clause

Returns

string

_getTypeSpecificColumnSql() ¶ protected

_getTypeSpecificColumnSql(string $columnType, Cake\Database\Schema\TableSchemaInterface $schema, string $column): string|null

Tries to use a matching database type to generate the SQL fragment for a single column in a table.

Parameters

string $columnType: The column type.
Cake\Database\Schema\TableSchemaInterface $schema: The table schema instance the column is in.
string $column: The name of the column.

Returns

string|null

An SQL fragment, or null in case no corresponding type was found or the type didn't provide custom column SQL.

addConstraintSql() ¶ public

addConstraintSql(Cake\Database\Schema\TableSchema $schema): array

Generate the SQL queries needed to add foreign key constraints to the table

SQLite can not properly handle adding a constraint to an existing table. This method is no-op

Parameters

Cake\Database\Schema\TableSchema $schema: The table instance the foreign key constraints are.

Returns

array

SQL fragment.

columnDefinitionSql() ¶ public

columnDefinitionSql(array $column): string

Create a SQL snippet for a column based on the array shape that describeColumns() creates.

Parameters

array $column: The column metadata

Returns

string

Generated SQL fragment for a column

columnSql() ¶ public

columnSql(Cake\Database\Schema\TableSchema $schema, string $name): string

Generate the SQL fragment for a single column in a table.

Parameters

Cake\Database\Schema\TableSchema $schema: The table instance the column is in.
string $name: The name of the column.

Returns

string

SQL fragment.

Throws

Cake\Database\Exception\DatabaseException
when the column type is unknown

constraintSql() ¶ public

constraintSql(Cake\Database\Schema\TableSchema $schema, string $name): string

Generate the SQL fragments for defining table constraints.

Note integer primary keys will return ''. This is intentional as Sqlite requires that integer primary keys be defined in the column definition.

Parameters

Cake\Database\Schema\TableSchema $schema: The table instance the column is in.
string $name: The name of the column.

Returns

string

SQL fragment.

convertColumnDescription() ¶ public

convertColumnDescription(Cake\Database\Schema\TableSchema $schema, array $row): void

Convert field description results into abstract schema fields.

Parameters

Cake\Database\Schema\TableSchema $schema
array $row

Returns

void

convertForeignKeyDescription() ¶ public

convertForeignKeyDescription(Cake\Database\Schema\TableSchema $schema, array $row): void

Convert a foreign key description into constraints on the Table object.

Parameters

Cake\Database\Schema\TableSchema $schema
array $row

Returns

void

convertIndexDescription() ¶ public

convertIndexDescription(Cake\Database\Schema\TableSchema $schema, array $row): void

Convert an index description results into abstract schema indexes or constraints.

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.

Parameters

Cake\Database\Schema\TableSchema $schema: The table object to append an index or constraint to.
array $row: The row data from describeIndexSql.

Returns

void

convertOptionsDescription() ¶ public

convertOptionsDescription(Cake\Database\Schema\TableSchema $schema, array $row): void

Convert options data into table options.

Parameters

Cake\Database\Schema\TableSchema $schema: Table instance.
array $row: The row of data.

Returns

void

createTableSql() ¶ public

createTableSql(Cake\Database\Schema\TableSchema $schema, array<string> $columns, array<string> $constraints, array<string> $indexes): array<string>

Generate the SQL to create a table.

Parameters

Cake\Database\Schema\TableSchema $schema
array<string> $columns
array<string> $constraints
array<string> $indexes

Returns

array<string>

describe() ¶ public

describe(string $name): Cake\Database\Schema\TableSchemaInterface

Get the column metadata for a table.

The name can include a database schema name in the form 'schema.table'.

Parameters

string $name: The name of the table to describe.

Returns

Cake\Database\Schema\TableSchemaInterface

Object with column metadata.

Throws

Cake\Database\Exception\DatabaseException
when table cannot be described.

describeColumnSql() ¶ public

describeColumnSql(string $tableName, array<string, mixed> $config): array

Generate the SQL to describe a table.

Parameters

string $tableName
array<string, mixed> $config

Returns

array

describeColumns() ¶ public

describeColumns(string $tableName): array

Get a list of column metadata as a array

Each item in the array will contain the following:

name : the name of the column.
type : the abstract type of the column.
length : the length of the column.
default : the default value of the column or null.
null : boolean indicating whether the column can be null.
comment : the column comment or null.

Additionaly the autoIncrement key will be set for columns that are a primary key.

Parameters

string $tableName

Returns

array

describeForeignKeySql() ¶ public

describeForeignKeySql(string $tableName, array<string, mixed> $config): array

Generate the SQL to describe the foreign keys in a table.

Parameters

string $tableName
array<string, mixed> $config

Returns

array

describeForeignKeys() ¶ public

describeForeignKeys(string $tableName): array

Get a list of constraint metadata as a array

Each item in the array will contain the following:

name : The name of the constraint
type : the type of the constraint. Generally foreign.
columns : the columns in the constraint on the.
references : A list of the table + all columns in the referenced table
update : The update action or null
delete : The delete action or null

Parameters

string $tableName

Returns

array

describeIndexSql() ¶ public

describeIndexSql(string $tableName, array<string, mixed> $config): array

Generate the SQL to describe the indexes in a table.

Parameters

string $tableName
array<string, mixed> $config

Returns

array

describeIndexes() ¶ public

describeIndexes(string $tableName): array

Get a list of index metadata as a array

Each item in the array will contain the following:

name : the name of the index.
type : the type of the index. One of unique, index, primary.
columns : the columns in the index.
length : the length of the index if applicable.

Parameters

string $tableName

Returns

array

describeOptions() ¶ public

describeOptions(string $tableName): array

Get platform specific options

No keys are guaranteed to be present as they are database driver dependent.

Parameters

string $tableName

Returns

array

describeOptionsSql() ¶ public

describeOptionsSql(string $tableName, array<string, mixed> $config): array

Generate the SQL to describe table options

Parameters

string $tableName: Table name.
array<string, mixed> $config: The connection configuration.

Returns

array

SQL statements to get options for a table.

dropConstraintSql() ¶ public

dropConstraintSql(Cake\Database\Schema\TableSchema $schema): array

Generate the SQL queries needed to drop foreign key constraints from the table

SQLite can not properly handle dropping a constraint to an existing table. This method is no-op

Parameters

Cake\Database\Schema\TableSchema $schema: The table instance the foreign key constraints are.

Returns

array

SQL fragment.

dropTableSql() ¶ public

dropTableSql(Cake\Database\Schema\TableSchema $schema): array

Generate the SQL to drop a table.

Parameters

Cake\Database\Schema\TableSchema $schema: Schema instance

Returns

array

SQL statements to drop a table.

hasColumn() ¶ public

hasColumn(string $tableName, string $columnName): bool

Check if a table has a column with a given name.

Parameters

string $tableName: The name of the table
string $columnName: The name of the column

Returns

bool

hasForeignKey() ¶ public

hasForeignKey(string $tableName, array<string> $columns = [], string $name = null): bool

Check if a table has a foreign key with a given name.

Parameters

string $tableName: The name of the table
array<string> $columns optional: The columns in the foriegn key. Specific ordering matters.
string $name optional: The name of the foreign key to match on. Can be used alone, or with $columns to match keys more precisely.

Returns

bool

hasIndex() ¶ public

hasIndex(string $tableName, array<string> $columns = [], string $name = null): bool

Check if a table has an index with a given name.

Parameters

string $tableName: The name of the table
array<string> $columns optional: The columns in the index. Specific ordering matters.
string $name optional: The name of the index to match on. Can be used alone, or with $columns to match indexes more precisely.

Returns

bool

hasSequences() ¶ public

hasSequences(): bool

Returns whether there is any table in this connection to SQLite containing sequences

Returns

bool

hasTable() ¶ public

hasTable(string $tableName): bool

Check if a table exists

Parameters

string $tableName: The name of the table

Returns

bool

indexSql() ¶ public

indexSql(Cake\Database\Schema\TableSchema $schema, string $name): string

Generate the SQL fragment for a single index in a table.

Parameters

Cake\Database\Schema\TableSchema $schema
string $name

Returns

string

listTables() ¶ public

listTables(): array<string>

Get the list of tables and views available in the current connection.

Returns

array<string>

The list of tables and views in the connected database/schema.

listTablesSql() ¶ public

listTablesSql(array<string, mixed> $config): array

Generate the SQL to list the tables and views.

Parameters

array<string, mixed> $config: The connection configuration to use for getting tables from.

Returns

array

An array of (sql, params) to execute.

listTablesWithoutViews() ¶ public

listTablesWithoutViews(): array<string>

Get the list of tables, excluding any views, available in the current connection.

Returns

array<string>

The list of tables in the connected database/schema.

listTablesWithoutViewsSql() ¶ public

listTablesWithoutViewsSql(array<string, mixed> $config): array<mixed>

Generate the SQL to list the tables, excluding all views.

Parameters

array<string, mixed> $config: The connection configuration to use for getting tables from.

Returns

array<mixed>

An array of (sql, params) to execute.

normalizePossiblyQuotedIdentifier() ¶ protected

normalizePossiblyQuotedIdentifier(string $value): string

Removes possible escape characters and surrounding quotes from identifiers.

Parameters

string $value: The identifier to normalize.

Returns

string

possiblyQuotedIdentifierRegex() ¶ protected

possiblyQuotedIdentifierRegex(string $identifier): string

Generates a regular expression to match identifiers that may or may not be quoted with any of the supported quotes.

Parameters

string $identifier: The identifier to match.

Returns

string

truncateTableSql() ¶ public

truncateTableSql(Cake\Database\Schema\TableSchema $schema): array

Generate the SQL to truncate a table.

Parameters

Cake\Database\Schema\TableSchema $schema

Returns

array

Property Detail

`$_driver` ¶ protected

The driver instance being used.

Type

Cake\Database\Driver

`$_hasSequences` ¶ protected

Whether there is any table in this connection to SQLite containing sequences.

Type

bool

Namespaces

Class SqliteSchemaDialect

Property Summary

Method Summary

__construct() public

_applyTypeSpecificColumnConversion() protected

_convertColumn() protected

_convertConstraintColumns() protected

_convertOnClause() protected

_defaultValue() protected

_foreignOnClause() protected

_getTypeSpecificColumnSql() protected

addConstraintSql() public

columnDefinitionSql() public

columnSql() public

constraintSql() public

convertColumnDescription() public

convertForeignKeyDescription() public

convertIndexDescription() public deprecated

convertOptionsDescription() public deprecated

createTableSql() public

describe() public

describeColumnSql() public

describeColumns() public

describeForeignKeySql() public

describeForeignKeys() public

describeIndexSql() public

describeIndexes() public

describeOptions() public

describeOptionsSql() public deprecated

dropConstraintSql() public

dropTableSql() public

hasColumn() public

hasForeignKey() public

hasIndex() public

hasSequences() public

hasTable() public

indexSql() public

listTables() public

listTablesSql() public

listTablesWithoutViews() public

listTablesWithoutViewsSql() public

normalizePossiblyQuotedIdentifier() protected

possiblyQuotedIdentifierRegex() protected

truncateTableSql() public

Method Detail

__construct() ¶ public

Parameters

_applyTypeSpecificColumnConversion() ¶ protected

Parameters

Returns

_convertColumn() ¶ protected

Parameters

Returns

Throws

_convertConstraintColumns() ¶ protected

Parameters

Returns

_convertOnClause() ¶ protected

Parameters

Returns

_defaultValue() ¶ protected

Parameters

Returns

_foreignOnClause() ¶ protected

Parameters

Returns

_getTypeSpecificColumnSql() ¶ protected

Parameters

Returns

addConstraintSql() ¶ public

Parameters

Returns

columnDefinitionSql() ¶ public

Parameters

Returns

columnSql() ¶ public

Parameters

Returns

Throws