Schema
extends Schema
in package
Class for MySQL-specific managing of database schemes and handling of SQL dialects and quoting.
Tags
Table of Contents
- $adapter : Adapter
- A Horde\Db\Adapter instance.
- $adapterMethods : array<string|int, mixed>
- List of public methods supported by the attached adapter.
- __call() : mixed
- Delegates calls to the adapter object.
- __construct() : mixed
- Constructor.
- __get() : object|null
- Delegates access to $cache and $logger to the adapter object.
- addColumn() : mixed
- Adds a new column to a table.
- addColumnOptions() : string
- Adds default/null options to column SQL definitions.
- addIndex() : string
- Adds a new index to a table.
- addOrderByForAssocLimiting() : string
- Adds an ORDER BY clause to an existing query.
- addPrimaryKey() : mixed
- Adds a primary key to a table.
- buildClause() : string|array<string|int, mixed>
- Returns an expression using the specified operator.
- cacheWrite() : void
- caseSensitiveEqualityOperator() : mixed
- changeColumn() : mixed
- Changes an existing column's definition.
- changeColumnDefault() : mixed
- Sets a new default value for a column.
- column() : Column
- Returns a table column.
- columns() : array<string|int, mixed>
- Returns a list of table columns.
- createDatabase() : mixed
- Creates a database.
- createTable() : TableDefinition
- Creates a new table.
- currentDatabase() : string
- Returns the name of the currently selected database.
- distinct() : string
- Generates a DISTINCT clause for SELECT queries.
- dropDatabase() : mixed
- Drops a database.
- dropTable() : mixed
- Drops a table from the database.
- endTable() : mixed
- Finishes and executes table creation.
- getCharset() : string
- Returns the character set of query results.
- getCollation() : string
- Returns the database collation strategy.
- indexes() : array<string|int, mixed>
- Returns a list of tables indexes.
- indexName() : mixed
- Builds the name for an index.
- interval() : string
- Generates an INTERVAL clause for SELECT queries.
- limitedUpdateConditions() : mixed
- makeColumn() : Column
- Factory for Column objects.
- makeColumnDefinition() : ColumnDefinition
- Factory for ColumnDefinition objects.
- makeIndex() : Index
- Factory for Index objects.
- makeTable() : Table
- Factory for Table objects.
- makeTableDefinition() : TableDefinition
- Factory for TableDefinition objects.
- modifyDate() : string
- Generates a modified date for SELECT queries.
- mysqlCharsetName() : string
- Returns the MySQL name of a character set.
- nativeDatabaseTypes() : array<string|int, mixed>
- Returns a hash of mappings from the abstract data types to the native database types.
- primaryKey() : Index
- Returns a table's primary key.
- quote() : string
- Quotes the column value to help prevent SQL injection attacks.
- quoteBinary() : string
- Returns a quoted binary value.
- quoteColumnName() : string
- Returns a quoted form of the column name.
- quoteDate() : string
- Returns a quoted date value.
- quoteFalse() : string
- Returns a quoted boolean false.
- quoteString() : string
- Quotes a string, escaping any ' (single quote) and \ (backslash) characters.
- quoteTableName() : string
- Returns a quoted form of the table name.
- quoteTrue() : string
- Returns a quoted boolean true.
- recreateDatabase() : mixed
- Recreates, i.e. drops then creates a database.
- removeColumn() : mixed
- Removes a column from a table.
- removeIndex() : mixed
- Removes an index from a table.
- removePrimaryKey() : mixed
- Removes a primary key from a table.
- renameColumn() : mixed
- Renames a column.
- renameTable() : mixed
- Renames a table.
- setAdapter() : mixed
- Setter for a Horde\Db\Adapter instance.
- setCharset() : mixed
- Sets the client and result charset.
- showVariable() : string
- Returns a database variable.
- table() : Table
- Returns a Horde\Db\Adapter\Base\Table object for a table.
- tableAliasFor() : string
- Converts a table name into a suitable table alias.
- tableAliasLength() : int
- Returns the maximum length a table alias can have.
- tables() : array<string|int, mixed>
- Returns a list of all tables of the current database.
- typeToSql() : string
- Generates the SQL definition for a column type.
- clearTableCache() : mixed
- Clears the cache for tables when altering them.
- escapePrepare() : string
- Escapes all characters in a string that are placeholders for prepare/execute methods.
Properties
$adapter
A Horde\Db\Adapter instance.
protected
Adapter
$adapter
= null
$adapterMethods
List of public methods supported by the attached adapter.
protected
array<string|int, mixed>
$adapterMethods
= []
Method names are in the keys.
Methods
__call()
Delegates calls to the adapter object.
public
__call(string $method, array<string|int, mixed> $args) : mixed
Parameters
- $method : string
-
A method name.
- $args : array<string|int, mixed>
-
Method parameters.
Tags
Return values
mixed —The method call result.
__construct()
Constructor.
public
__construct(Adapter $adapter) : mixed
Parameters
- $adapter : Adapter
-
A Horde_Db_Adapter instance.
Return values
mixed —__get()
Delegates access to $cache and $logger to the adapter object.
public
__get(string $key) : object|null
Parameters
- $key : string
-
Property name. Only 'cache' and 'logger' are supported.
Return values
object|null —The request property object.
addColumn()
Adds a new column to a table.
public
addColumn(string $tableName, string $columnName, string $type[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
- $tableName : string
-
A table name.
- $columnName : string
-
A column name.
- $type : string
-
A data type.
- $options : array<string|int, mixed> = []
-
Column options. See Horde_Db_Adapter_Base_TableDefinition#column() for details.
Return values
mixed —addColumnOptions()
Adds default/null options to column SQL definitions.
public
addColumnOptions(string $sql, array<string|int, mixed> $options) : string
Parameters
- $sql : string
-
Existing SQL definition for a column.
- $options : array<string|int, mixed>
-
Column options:
- null: (boolean) Whether to allow NULL values.
- default: (mixed) Default column value.
- autoincrement: (boolean) Whether the column is an autoincrement column. Driver depedendent.
- after: (string) Insert column after this one. MySQL specific.
Return values
string —The manipulated SQL definition.
addIndex()
Adds a new index to a table.
public
addIndex(string $tableName, string|array<string|int, mixed> $columnName[, array<string|int, mixed> $options = [] ]) : string
The index will be named after the table and the first column names, unless you pass 'name' as an option.
When creating an index on multiple columns, the first column is used as a name for the index. For example, when you specify an index on two columns 'first' and 'last', the DBMS creates an index for both columns as well as an index for the first colum 'first'. Using just the first name for this index makes sense, because you will never have to create a singular index with this name.
Examples:
Creating a simple index
$schema->addIndex('suppliers', 'name');
generates
CREATE INDEX suppliers_name_index ON suppliers(name)
Creating a unique index
$schema->addIndex('accounts',
array('branch_id', 'party_id'),
array('unique' => true));
generates
CREATE UNIQUE INDEX accounts_branch_id_index ON accounts(branch_id, party_id)
Creating a named index
$schema->addIndex('accounts',
array('branch_id', 'party_id'),
array('unique' => true, 'name' => 'by_branch_party'));
generates
CREATE UNIQUE INDEX by_branch_party ON accounts(branch_id, party_id)
Parameters
- $tableName : string
-
A table name.
- $columnName : string|array<string|int, mixed>
-
One or more column names.
- $options : array<string|int, mixed> = []
-
Index options:
- name: (string) the index name.
- unique: (boolean) create a unique index?
Return values
string —The index name. @since Horde_Db 2.1.0
addOrderByForAssocLimiting()
Adds an ORDER BY clause to an existing query.
public
addOrderByForAssocLimiting(string $sql, array<string|int, mixed> $options) : string
Parameters
- $sql : string
-
An SQL query to manipulate.
- $options : array<string|int, mixed>
-
Options:
- order: Order column an direction.
Return values
string —The manipulated SQL query.
addPrimaryKey()
Adds a primary key to a table.
public
addPrimaryKey(string $tableName, mixed $columns) : mixed
Parameters
- $tableName : string
-
A table name.
- $columns : mixed
Tags
Return values
mixed —buildClause()
Returns an expression using the specified operator.
public
buildClause(string $lhs, string $op, string $rhs[, bool $bind = false ][, array<string|int, mixed> $params = [] ]) : string|array<string|int, mixed>
Parameters
- $lhs : string
-
The column or expression to test.
- $op : string
-
The operator.
- $rhs : string
-
The comparison value.
- $bind : bool = false
-
If true, the method returns the query and a list of values suitable for binding as an array.
- $params : array<string|int, mixed> = []
-
Any additional parameters for the operator.
Return values
string|array<string|int, mixed> —The SQL test fragment, or an array containing the query and a list of values if $bind is true.
cacheWrite()
public
cacheWrite(mixed $key, mixed $value) : void
Parameters
- $key : mixed
- $value : mixed
Return values
void —caseSensitiveEqualityOperator()
public
caseSensitiveEqualityOperator() : mixed
Return values
mixed —changeColumn()
Changes an existing column's definition.
public
changeColumn(string $tableName, string $columnName, string $type[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
- $tableName : string
-
A table name.
- $columnName : string
-
A column name.
- $type : string
-
A data type.
- $options : array<string|int, mixed> = []
-
Column options. See Horde_Db_Adapter_Base_TableDefinition#column() for details.
Return values
mixed —changeColumnDefault()
Sets a new default value for a column.
public
changeColumnDefault(string $tableName, string $columnName, mixed $default) : mixed
If you want to set the default value to NULL, you are out of luck. You need to execute the apppropriate SQL statement yourself.
Parameters
- $tableName : string
-
A table name.
- $columnName : string
-
A column name.
- $default : mixed
-
The new default value.
Return values
mixed —column()
Returns a table column.
public
column(string $tableName, string $columnName) : Column
Parameters
- $tableName : string
-
A table name.
- $columnName : string
-
A column name.
Tags
Return values
Column —A column object.
columns()
Returns a list of table columns.
public
columns(string $tableName[, string $name = null ]) : array<string|int, mixed>
Parameters
- $tableName : string
-
A table name.
- $name : string = null
-
(can be removed?)
Return values
array<string|int, mixed> —A list of Horde_Db_Adapter_Base_Column objects.
createDatabase()
Creates a database.
public
createDatabase(string $name[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
- $name : string
-
A database name.
- $options : array<string|int, mixed> = []
-
Database options.
Return values
mixed —createTable()
Creates a new table.
public
createTable(string $name[, array<string|int, mixed> $options = [] ]) : TableDefinition
The $options hash can include the following keys:
- autoincrementKey (string|array): The name of the autoincrementing primary key, if one is to be added automatically. Defaults to "id".
- options (array): Any extra options you want appended to the table definition.
- temporary (boolean): Make a temporary table.
- force (boolean): Set to true or false to drop the table before creating it. Defaults to false.
Examples:
// Add a backend specific option to the generated SQL (MySQL)
$schema->createTable('suppliers', array('options' => 'ENGINE=InnoDB DEFAULT CHARSET=utf8')));
generates:
CREATE TABLE suppliers ( id int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=InnoDB DEFAULT CHARSET=utf8
// Rename the primary key column
$table = $schema->createTable('objects', array('autoincrementKey' => 'guid'));
$table->column('name', 'string', array('limit' => 80));
$table->end();
generates:
CREATE TABLE objects ( guid int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(80) )
// Do not add a primary key column, use fluent interface, use type
// method.
$schema->createTable('categories_suppliers', array('autoincrementKey' => false))
->column('category_id', 'integer')
->integer('supplier_id')
->end();
generates:
CREATE TABLE categories_suppliers ( category_id int(11), supplier_id int(11) )
See also Horde_Db_Adapter_Base_TableDefinition::column() for details on how to create columns.
Parameters
- $name : string
-
A table name.
- $options : array<string|int, mixed> = []
-
A list of table options, see the method description.
Return values
TableDefinition —The definition of the created table.
currentDatabase()
Returns the name of the currently selected database.
public
currentDatabase() : string
Return values
string —The database name.
distinct()
Generates a DISTINCT clause for SELECT queries.
public
distinct(string $columns[, string $orderBy = null ]) : string
$connection->distinct('posts.id', 'posts.created_at DESC')
Parameters
- $columns : string
-
A column list.
- $orderBy : string = null
-
An ORDER clause.
Return values
string —The generated DISTINCT clause.
dropDatabase()
Drops a database.
public
dropDatabase(string $name) : mixed
Parameters
- $name : string
-
A database name.
Return values
mixed —dropTable()
Drops a table from the database.
public
dropTable(string $name) : mixed
Parameters
- $name : string
-
A table name.
Return values
mixed —endTable()
Finishes and executes table creation.
public
endTable(string|TableDefinition $name[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
- $name : string|TableDefinition
-
A table name or object.
- $options : array<string|int, mixed> = []
-
A list of options. See createTable().
Return values
mixed —getCharset()
Returns the character set of query results.
public
getCharset() : string
Return values
string —The result's charset.
getCollation()
Returns the database collation strategy.
public
getCollation() : string
Return values
string —Database collation.
indexes()
Returns a list of tables indexes.
public
indexes(string $tableName[, string $name = null ]) : array<string|int, mixed>
Parameters
- $tableName : string
-
A table name.
- $name : string = null
-
(can be removed?)
Return values
array<string|int, mixed> —A list of Horde_Db_Adapter_Base_Index objects.
indexName()
Builds the name for an index.
public
indexName(string $tableName[, string|array<string|int, mixed> $options = [] ]) : mixed
Cuts the index name to the maximum length of 64 characters limited by MySQL.
Parameters
- $tableName : string
-
A table name.
- $options : string|array<string|int, mixed> = []
-
Either a column name or index options:
- column: (string|array) column name(s).
- name: (string) the index name to fall back to if no column names specified.
Return values
mixed —interval()
Generates an INTERVAL clause for SELECT queries.
public
interval(string $interval, string $precision) : string
Parameters
- $interval : string
-
The interval.
- $precision : string
-
The precision.
Tags
Return values
string —The generated INTERVAL clause.
limitedUpdateConditions()
public
limitedUpdateConditions(mixed $whereSql, mixed $quotedTableName, mixed $quotedPrimaryKey) : mixed
Parameters
- $whereSql : mixed
- $quotedTableName : mixed
- $quotedPrimaryKey : mixed
Return values
mixed —makeColumn()
Factory for Column objects.
public
makeColumn(string $name, string $default[, string $sqlType = null ][, bool $null = true ]) : Column
Parameters
- $name : string
-
The column's name, such as "supplier_id" in "supplier_id int(11)".
- $default : string
-
The type-casted default value, such as "new" in "sales_stage varchar(20) default 'new'".
- $sqlType : string = null
-
Used to extract the column's type, length and signed status, if necessary. For example "varchar" and "60" in "company_name varchar(60)" or "unsigned => true" in "int(10) UNSIGNED".
- $null : bool = true
-
Whether this column allows NULL values.
Return values
Column —A column object.
makeColumnDefinition()
Factory for ColumnDefinition objects.
public
makeColumnDefinition(mixed $base, mixed $name, mixed $type[, mixed $limit = null ][, mixed $precision = null ][, mixed $scale = null ][, mixed $unsigned = null ][, mixed $default = null ][, mixed $null = null ][, mixed $autoincrement = null ]) : ColumnDefinition
Parameters
- $base : mixed
- $name : mixed
- $type : mixed
- $limit : mixed = null
- $precision : mixed = null
- $scale : mixed = null
- $unsigned : mixed = null
- $default : mixed = null
- $null : mixed = null
- $autoincrement : mixed = null
Return values
ColumnDefinition —A column definition object.
makeIndex()
Factory for Index objects.
public
makeIndex(string $table, string $name, bool $primary, bool $unique, array<string|int, mixed> $columns) : Index
Parameters
- $table : string
-
The table the index is on.
- $name : string
-
The index's name.
- $primary : bool
-
Is this a primary key?
- $unique : bool
-
Is this a unique index?
- $columns : array<string|int, mixed>
-
The columns this index covers.
Return values
Index —An index object.
makeTable()
Factory for Table objects.
public
makeTable(mixed $name, mixed $primaryKey, mixed $columns, mixed $indexes) : Table
Parameters
- $name : mixed
- $primaryKey : mixed
- $columns : mixed
- $indexes : mixed
Return values
Table —A table object.
makeTableDefinition()
Factory for TableDefinition objects.
public
makeTableDefinition(mixed $name, mixed $base[, mixed $options = [] ]) : TableDefinition
Parameters
- $name : mixed
- $base : mixed
- $options : mixed = []
Return values
TableDefinition —A table definition object.
modifyDate()
Generates a modified date for SELECT queries.
public
modifyDate(string $reference, string $operator, int $amount, string $interval) : string
Parameters
- $reference : string
-
The reference date - this is a column referenced in the SELECT.
- $operator : string
-
Add or subtract time? (+/-)
- $amount : int
-
The shift amount (number of days if $interval is DAY, etc).
- $interval : string
-
The interval (SECOND, MINUTE, HOUR, DAY, MONTH, YEAR).
Return values
string —The generated INTERVAL clause.
mysqlCharsetName()
Returns the MySQL name of a character set.
public
mysqlCharsetName(string $charset) : string
Parameters
- $charset : string
-
A charset name.
Return values
string —MySQL-normalized charset.
nativeDatabaseTypes()
Returns a hash of mappings from the abstract data types to the native database types.
public
nativeDatabaseTypes() : array<string|int, mixed>
See TableDefinition::column() for details on the recognized abstract data types.
Tags
Return values
array<string|int, mixed> —A database type map.
primaryKey()
Returns a table's primary key.
public
primaryKey(string $tableName[, string $name = null ]) : Index
Parameters
- $tableName : string
-
A table name.
- $name : string = null
-
(can be removed?)
Return values
Index —The primary key index object.
quote()
Quotes the column value to help prevent SQL injection attacks.
public
quote(mixed $value[, object $column = null ]) : string
This method makes educated guesses on the scalar type based on the passed value. Make sure to correctly cast the value and/or pass the $column parameter to get the best results.
Parameters
- $value : mixed
-
The scalar value to quote, a Horde_Db_Value, Horde_Date, or DateTime instance, or an object implementing quotedId().
- $column : object = null
-
An object implementing getType().
Return values
string —The correctly quoted value.
quoteBinary()
Returns a quoted binary value.
public
quoteBinary(mixed $value) : string
Parameters
- $value : mixed
Return values
string —The quoted binary value.
quoteColumnName()
Returns a quoted form of the column name.
public
quoteColumnName(string $name) : string
Parameters
- $name : string
-
A column name.
Return values
string —The quoted column name.
quoteDate()
Returns a quoted date value.
public
quoteDate(mixed $value) : string
Parameters
- $value : mixed
Return values
string —The quoted date value.
quoteFalse()
Returns a quoted boolean false.
public
quoteFalse() : string
Return values
string —The quoted boolean false.
quoteString()
Quotes a string, escaping any ' (single quote) and \ (backslash) characters.
public
quoteString(string $string) : string
Parameters
- $string : string
-
A string to escape.
Return values
string —The escaped and quoted string.
quoteTableName()
Returns a quoted form of the table name.
public
quoteTableName(string $name) : string
Defaults to column name quoting.
Parameters
- $name : string
-
A table name.
Return values
string —The quoted table name.
quoteTrue()
Returns a quoted boolean true.
public
quoteTrue() : string
Return values
string —The quoted boolean true.
recreateDatabase()
Recreates, i.e. drops then creates a database.
public
recreateDatabase(string $name) : mixed
Parameters
- $name : string
-
A database name.
Return values
mixed —removeColumn()
Removes a column from a table.
public
removeColumn(string $tableName, string $columnName) : mixed
Parameters
- $tableName : string
-
A table name.
- $columnName : string
-
A column name.
Return values
mixed —removeIndex()
Removes an index from a table.
public
removeIndex(string $tableName[, string|array<string|int, mixed> $options = [] ]) : mixed
Examples:
Remove the suppliers_name_index in the suppliers table:
$schema->removeIndex('suppliers', 'name');
Remove the index named accounts_branch_id in the accounts table:
$schema->removeIndex('accounts', array('column' => 'branch_id'));
Remove the index named by_branch_party in the accounts table:
$schema->removeIndex('accounts', array('name' => 'by_branch_party'));
You can remove an index on multiple columns by specifying the first
column:
$schema->addIndex('accounts', array('username', 'password'))
$schema->removeIndex('accounts', 'username');
Parameters
- $tableName : string
-
A table name.
- $options : string|array<string|int, mixed> = []
-
Either a column name or index options:
- name: (string) the index name.
- column: (string|array) column name(s).
Return values
mixed —removePrimaryKey()
Removes a primary key from a table.
public
removePrimaryKey(string $tableName) : mixed
Parameters
- $tableName : string
-
A table name.
Tags
Return values
mixed —renameColumn()
Renames a column.
public
renameColumn(string $tableName, string $columnName, string $newColumnName) : mixed
Parameters
- $tableName : string
-
A table name.
- $columnName : string
-
A column name.
- $newColumnName : string
-
The new column name.
Return values
mixed —renameTable()
Renames a table.
public
renameTable(string $name, string $newName) : mixed
Parameters
- $name : string
-
A table name.
- $newName : string
-
The new table name.
Return values
mixed —setAdapter()
Setter for a Horde\Db\Adapter instance.
public
setAdapter(Adapter $adapter) : mixed
This is mostly for the __wakeup call, maybe we should rethink this
Parameters
- $adapter : Adapter
-
A Horde\Db\Adapter instance.
Return values
mixed —setCharset()
Sets the client and result charset.
public
setCharset(string $charset) : mixed
Parameters
- $charset : string
-
The character set to use for client queries and results.
Return values
mixed —showVariable()
Returns a database variable.
public
showVariable(string $name) : string
Convenience wrapper around "SHOW VARIABLES LIKE 'name'".
Parameters
- $name : string
-
A variable name.
Tags
Return values
string —The variable value.
table()
Returns a Horde\Db\Adapter\Base\Table object for a table.
public
table(string $tableName[, string $name = null ]) : Table
Parameters
- $tableName : string
-
A table name.
- $name : string = null
-
(can be removed?)
Return values
Table —A table object.
tableAliasFor()
Converts a table name into a suitable table alias.
public
tableAliasFor(string $tableName) : string
Parameters
- $tableName : string
-
A table name.
Return values
string —A possible alias name for the table.
tableAliasLength()
Returns the maximum length a table alias can have.
public
tableAliasLength() : int
Return values
int —The maximum table alias length.
tables()
Returns a list of all tables of the current database.
public
tables() : array<string|int, mixed>
Return values
array<string|int, mixed> —A table list.
typeToSql()
Generates the SQL definition for a column type.
public
typeToSql(string $type[, int $limit = null ][, int $precision = null ][, int $scale = null ][, bool $unsigned = null ]) : string
Parameters
- $type : string
-
A column type.
- $limit : int = null
-
Maximum column length (non decimal type only)
- $precision : int = null
-
The number precision (decimal type only).
- $scale : int = null
-
The number scaling (decimal columns only).
- $unsigned : bool = null
-
Whether the column is an unsigned number (non decimal columns only).
Return values
string —The SQL definition. If $type is not one of the internally supported types, $type is returned unchanged.
clearTableCache()
Clears the cache for tables when altering them.
protected
clearTableCache(string $tableName) : mixed
Parameters
- $tableName : string
-
A table name.
Return values
mixed —escapePrepare()
Escapes all characters in a string that are placeholders for prepare/execute methods.
protected
escapePrepare(string $query) : string
Parameters
- $query : string
-
A string to escape.
Return values
string —The correctly escaped string.