$_adapter
$_adapter : \Horde_Db_Adapter_Base
A Horde_Db_Adapter instance.
Class for PostgreSQL-specific managing of database schemes and handling of SQL dialects and quoting.
$_adapter : \Horde_Db_Adapter_Base
A Horde_Db_Adapter instance.
__construct(\Horde_Db_Adapter_Base $adapter)
Constructor.
| \Horde_Db_Adapter_Base | $adapter | A Horde_Db_Adapter instance. |
setAdapter(\Horde_Db_Adapter $adapter)
Setter for a Horde_Db_Adapter instance.
| \Horde_Db_Adapter | $adapter | A Horde_Db_Adapter instance. |
makeColumn(string $name, string $default, string $sqlType = null, boolean $null = true) : \Horde_Db_Adapter_Postgresql_Column
Factory for Column objects.
| string | $name | The column's name, such as "supplier_id" in "supplier_id int(11)". |
| string | $default | The type-casted default value, such as "new" in "sales_stage varchar(20) default 'new'". |
| string | $sqlType | 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". |
| boolean | $null | Whether this column allows NULL values. |
A column object.
makeColumnDefinition( $base, $name, $type, $limit = null, $precision = null, $scale = null, $unsigned = null, $default = null, $null = null, $autoincrement = null) : \Horde_Db_Adapter_Base_ColumnDefinition
Factory for ColumnDefinition objects.
| $base | ||
| $name | ||
| $type | ||
| $limit | ||
| $precision | ||
| $scale | ||
| $unsigned | ||
| $default | ||
| $null | ||
| $autoincrement |
A column definition object.
makeIndex(string $table, string $name, boolean $primary, boolean $unique, array $columns) : \Horde_Db_Adapter_Base_Index
Factory for Index objects.
| string | $table | The table the index is on. |
| string | $name | The index's name. |
| boolean | $primary | Is this a primary key? |
| boolean | $unique | Is this a unique index? |
| array | $columns | The columns this index covers. |
An index object.
makeTable( $name, $primaryKey, $columns, $indexes) : \Horde_Db_Adapter_Base_Table
Factory for Table objects.
| $name | ||
| $primaryKey | ||
| $columns | ||
| $indexes |
A table object.
makeTableDefinition( $name, $base, $options = array()) : \Horde_Db_Adapter_Base_TableDefinition
Factory for TableDefinition objects.
| $name | ||
| $base | ||
| $options |
A table definition object.
quote(mixed $value, object $column = null) : string
Quotes the column value to help prevent SQL injection attacks.
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.
| mixed | $value | The scalar value to quote, a Horde_Db_Value, Horde_Date, or DateTime instance, or an object implementing quotedId(). |
| object | $column | An object implementing getType(). |
The correctly quoted value.
table(string $tableName, string $name = null) : \Horde_Db_Adapter_Base_Table
Returns a Horde_Db_Adapter_Base_Table object for a table.
| string | $tableName | A table name. |
| string | $name | (can be removed?) |
A table object.
primaryKey(string $tableName, string $name = null) : \Horde_Db_Adapter_Base_Index
Returns a table's primary key.
| string | $tableName | A table name. |
| string | $name | (can be removed?) |
The primary key index object.
column(string $tableName, string $columnName) : \Horde_Db_Adapter_Base_Column
Returns a table column.
| string | $tableName | A table name. |
| string | $columnName | A column name. |
if column not found.
A column object.
createTable(string $name, array $options = array()) : \Horde_Db_Adapter_Base_TableDefinition
Creates a new table.
The $options hash can include the following keys:
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.
| string | $name | A table name. |
| array | $options | A list of table options, see the method description. |
The definition of the created table.
endTable(string|\Horde_Db_Adapter_Base_TableDefinition $name, array $options = array())
Finishes and executes table creation.
| string|\Horde_Db_Adapter_Base_TableDefinition | $name | A table name or object. |
| array | $options | A list of options. See createTable(). |
addColumn(string $tableName, string $columnName, string $type, array $options = array())
Adds a new column to a table.
| string | $tableName | A table name. |
| string | $columnName | A column name. |
| string | $type | A data type. |
| array | $options | Column options. See Horde_Db_Adapter_Base_TableDefinition#column() for details. |
changeColumn(string $tableName, string $columnName, string $type, array $options = array())
Changes an existing column's definition.
| string | $tableName | A table name. |
| string | $columnName | A column name. |
| string | $type | A data type. |
| array | $options | Column options. See Horde_Db_Adapter_Base_TableDefinition#column() for details. |
changeColumnDefault(string $tableName, string $columnName, mixed $default)
Sets a new default value for a column.
If you want to set the default value to NULL, you are out of luck. You need to execute the apppropriate SQL statement yourself.
| string | $tableName | A table name. |
| string | $columnName | A column name. |
| mixed | $default | The new default value. |
addIndex(string $tableName, string|array $columnName, array $options = array()) : string
Adds a new index to a table.
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)
| string | $tableName | A table name. |
| string|array | $columnName | One or more column names. |
| array | $options | Index options:
|
The index name. @since Horde_Db 2.1.0
removeIndex(string $tableName, string|array $options = array())
Removes an index from a table.
See parent class for examples.
| string | $tableName | A table name. |
| string|array | $options | Either a column name or index options:
|
indexName(string $tableName, string|array $options = array())
Builds the name for an index.
| string | $tableName | A table name. |
| string|array | $options | Either a column name or index options:
|
typeToSql(string $type, integer $limit = null, integer $precision = null, integer $scale = null, boolean $unsigned = null) : string
Generates the SQL definition for a column type.
| string | $type | A column type. |
| integer | $limit | Maximum column length (non decimal type only) |
| integer | $precision | The number precision (decimal type only). |
| integer | $scale | The number scaling (decimal columns only). |
| boolean | $unsigned | Whether the column is an unsigned number (non decimal columns only). |
The SQL definition. If $type is not one of the internally supported types, $type is returned unchanged.
addColumnOptions(string $sql, array $options) : string
Adds default/null options to column SQL definitions.
| string | $sql | Existing SQL definition for a column. |
| array | $options | Column options:
|
The manipulated SQL definition.
distinct(string $columns, string $orderBy = null) : string
Generates a DISTINCT clause for SELECT queries.
PostgreSQL requires the ORDER BY columns in the SELECT list for distinct queries, and requires that the ORDER BY include the DISTINCT column.
$connection->distinct('posts.id', 'posts.created_at DESC')
| string | $columns | A column list. |
| string | $orderBy | An ORDER clause. |
The generated DISTINCT clause.
modifyDate(string $reference, string $operator, integer $amount, string $interval) : string
Generates a modified date for SELECT queries.
| string | $reference | The reference date - this is a column referenced in the SELECT. |
| string | $operator | Add or subtract time? (+/-) |
| integer | $amount | The shift amount (number of days if $interval is DAY, etc). |
| string | $interval | The interval (SECOND, MINUTE, HOUR, DAY, MONTH, YEAR). |
The generated INTERVAL clause.
buildClause(string $lhs, string $op, string $rhs, boolean $bind = false, array $params = array()) : string|array
Returns an expression using the specified operator.
| string | $lhs | The column or expression to test. |
| string | $op | The operator. |
| string | $rhs | The comparison value. |
| boolean | $bind | If true, the method returns the query and a list of values suitable for binding as an array. |
| array | $params | Any additional parameters for the operator. |
The SQL test fragment, or an array containing the query and a list of values if $bind is true.
changeColumnNull(string $tableName, string $columnName, boolean $null, mixed $default = null)
Sets whether a column allows NULL values.
| string | $tableName | A table name. |
| string | $columnName | A column name. |
| boolean | $null | Whether NULL values are allowed. |
| mixed | $default | The new default value. |
addOrderByForAssociationLimiting(string $sql, array $options) : string
Adds an ORDER BY clause to an existing query.
PostgreSQL does not allow arbitrary ordering when using DISTINCT ON, so we work around this by wrapping the $sql string as a sub-select and ordering in that query.
| string | $sql | An SQL query to manipulate. |
| array | $options | Options:
|
The manipulated SQL query.
setSchemaSearchPath(string $schemaCsv)
Sets the schema search path to a string of comma-separated schema names.
Names beginning with $ have to be quoted (e.g. $user => '$user'). See: http://www.postgresql.org/docs/current/static/ddl-schemas.html
| string | $schemaCsv | A comma-separated schema name list. |
defaultSequenceName(string $tableName, string $pk = null) : string
Returns the sequence name for a table's primary key or some other specified key.
If a sequence name doesn't exist, it is built from the table and primary key name.
| string | $tableName | A table name. |
| string | $pk | A primary key name. Overrides the existing key name when building a new sequence name. |
The key's sequence name.
resetPkSequence( $table, string $pk = null, string $sequence = null) : integer
Resets the sequence of a table's primary key to the maximum value.
| $table | ||
| string | $pk | A primary key name. Defaults to the existing primary key. |
| string | $sequence | A sequence name. Defaults to the sequence name of the existing primary key. |
The (next) sequence value if a primary key and a sequence exist.
_columnDefinitions( $tableName, $name = null)
Returns the list of a table's column names, data types, and default values.
The underlying query is roughly: SELECT column.name, column.type, default.value FROM column LEFT JOIN default ON column.table_id = default.table_id AND column.num = default.column_num WHERE column.table_id = get_table_id('table_name') AND column.num > 0 AND NOT column.is_dropped ORDER BY column.num
If the table name is not prefixed with a schema, the database will take the first match from the schema search path.
Query implementation notes:
| $tableName | ||
| $name |