Documentation

Schema extends Schema
in package

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

Tags
author

Jan Schneider jan@horde.org

category

Horde

copyright

2013-2021 Horde LLC

license

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

since

Horde_Db 2.1.0

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.
createAutoincrementTrigger()  : mixed
Creates sequences and triggers for an autoincrementKey column.
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.
removeAutoincrementTrigger()  : mixed
Drops sequences and triggers for an autoincrementKey column.
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.
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.
truncate()  : string
Truncates an indentifier to a certain length.

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.

createAutoincrementTrigger()

Creates sequences and triggers for an autoincrementKey column.

public createAutoincrementTrigger(string $tableName, string $columnName) : mixed
Parameters
$tableName : string

A table name.

$columnName : string

A column name.

Tags
since

Horde_Db 2.1.0

Return values
mixed

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

Cuts the index name to the maximum length of 30 characters limited by Oracle.

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 ][, int $length = null ][, int $precision = null ][, int $scale = null ]) : Column
Parameters
$name : string

Column name, such as "supplier_id" in "supplier_id int(11)".

$default : string

Type-casted default value, such as "new" in "sales_stage varchar(20) default 'new'".

$sqlType : string = null

Column type.

$null : bool = true

Whether this column allows NULL values.

$length : int = null

Column width.

$precision : int = null

Precision for NUMBER and FLOAT columns.

$scale : int = null

Number of digits to the right of the decimal point in a number.

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

With Oracle, if using quoted identifiers, you need to use them everywhere. 'SELECT * FROM "tablename"' is NOT the same as 'SELECT * FROM tablename'. Thus we cannot blindly quote table or column names, unless we know that in subsequent queries they will be used too.

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

removeAutoincrementTrigger()

Drops sequences and triggers for an autoincrementKey column.

public removeAutoincrementTrigger(string $tableName[, string $columnName = null ]) : mixed

If $columnName is specified, the sequences and triggers are only dropped if $columnName is actually an autoincrementKey column.

Parameters
$tableName : string

A table name.

$columnName : string = null

A column name.

Tags
since

Horde_Db 2.1.0

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

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.

truncate()

Truncates an indentifier to a certain length.

protected truncate(string $name[, int $length = 30 ]) : string

To avoid collisions, the identifier is split up by underscores and the parts truncated to 3 characters first.

Parameters
$name : string

An identifier.

$length : int = 30

The maximum length.

Return values
string

The truncated identifier.

Search results