Documentation

Schema extends Schema
in package

Class for SQLite-specific managing of database schemes and handling of SQL dialects and quoting.

Tags
author

Mike Naberezny mike@maintainable.com

author

Derek DeVries derek@maintainable.com

author

Chuck Hagenbuch chuck@horde.org

author

Jan Schneider jan@horde.org

category

Horde

copyright

2007 Maintainable Software, LLC

copyright

2008-2021 Horde LLC

license

http://www.horde.org/licenses/bsd

subpackage

Adapter

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
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.
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.
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.
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.
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.
alterTable()  : mixed
Alters a table.
clearTableCache()  : mixed
Clears the cache for tables when altering them.
copyTable()  : mixed
Copies a table.
copyTableContents()  : mixed
Copies the content of one table to another.
copyTableIndexes()  : mixed
Copies indexes from one table to another.
defaultPrimaryKeyType()  : string
Returns a column type definition to be use for primary keys.
escapePrepare()  : string
Escapes all characters in a string that are placeholders for prepare/execute methods.
moveTable()  : mixed
Moves a table.

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
throws
BadMethodCallException

if method doesn't exist in the adapter.

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:

  • column: (Horde_Db_Adapter_Base_ColumnDefinition The column definition class.
  • null: (boolean) Whether to allow NULL values.
  • default: (mixed) Default column value.
  • autoincrement: (boolean) Whether the column is an autoincrement column. Driver depedendent.
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
throws
DbException
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

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
since

Horde_Db 2.1.0

throws
DbException

if column not found.

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

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
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
deprecated

since version 1.2.0. This function does not work with SQLite as a backend so you should avoid using it. Use "modifyDate()" instead.

Return values
string

The generated INTERVAL clause.

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.

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
see
TableDefinition::column()
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 = array() ]) : mixed

See parent class for examples.

Parameters
$tableName : string

A table name.

$options : string|array<string|int, mixed> = array()

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
throws
DbException
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

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.

alterTable()

Alters a table.

protected alterTable(string $tableName[, array<string|int, mixed> $options = [] ][, function $callback = null ]) : mixed

This is done by creating a temporary copy, applying changes and callback methods, and copying the table back.

Parameters
$tableName : string

A table name.

$options : array<string|int, mixed> = []

Any options to apply when creating the temporary table. Supports a 'rename' key for the new table name, additionally to the options in createTable().

$callback : function = null

A callback function that can manipulate the Horde_Db_Adapter_Base_TableDefinition object available in $definition. See _copyTable().

Return values
mixed

clearTableCache()

Clears the cache for tables when altering them.

protected clearTableCache(string $tableName) : mixed
Parameters
$tableName : string

A table name.

Return values
mixed

copyTable()

Copies a table.

protected copyTable(string $from, string $to[, array<string|int, mixed> $options = [] ][, function $callback = null ]) : mixed

Also applies changes and callback methods before creating the new table.

Parameters
$from : string

The name of the source table.

$to : string

The name of the target table.

$options : array<string|int, mixed> = []

Any options to apply when creating the temporary table. Supports a 'rename' key for the new table name, additionally to the options in createTable().

$callback : function = null

A callback function that can manipulate the Horde_Db_Adapter_Base_TableDefinition object available in $definition.

Return values
mixed

copyTableContents()

Copies the content of one table to another.

protected copyTableContents(string $from, string $to, array<string|int, mixed> $columns[, array<string|int, mixed> $rename = [] ]) : mixed
Parameters
$from : string

The name of the source table.

$to : string

The name of the target table.

$columns : array<string|int, mixed>

A list of columns to copy.

$rename : array<string|int, mixed> = []

A hash of columns to rename during the copy, with original names as keys and the new names as values.

Return values
mixed

copyTableIndexes()

Copies indexes from one table to another.

protected copyTableIndexes(string $from, string $to[, array<string|int, mixed> $rename = [] ]) : mixed
Parameters
$from : string

The name of the source table.

$to : string

The name of the target table.

$rename : array<string|int, mixed> = []

A hash of columns to rename during the copy, with original names as keys and the new names as values.

Return values
mixed

defaultPrimaryKeyType()

Returns a column type definition to be use for primary keys.

protected defaultPrimaryKeyType() : string
Return values
string

Primary key type definition.

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.

moveTable()

Moves a table.

protected moveTable(string $from, string $to[, array<string|int, mixed> $options = [] ][, function $callback = null ]) : mixed

This is done by creating a temporary copy, applying changes and callback methods, and dropping the original table.

Parameters
$from : string

The name of the source table.

$to : string

The name of the target table.

$options : array<string|int, mixed> = []

Any options to apply when creating the temporary table. Supports a 'rename' key for the new table name, additionally to the options in createTable().

$callback : function = null

A callback function that can manipulate the Horde_Db_Adapter_Base_TableDefinition object available in $definition. See _copyTable().

Return values
mixed

Search results