PostgreSQLDatabase
class PostgreSQLDatabase extends SS_Database (View source)
PostgreSQL connector class.
Constants
| MASTER_DATABASE |
|
| MASTER_SCHEMA |
|
Properties
| protected | DBConnector | $connector | Database connector object |
from SS_Database |
| protected | PostgreSQLSchemaManager | $schemaManager | Database schema manager object |
|
| protected | DBQueryBuilder | $queryBuilder | Query builder object |
from SS_Database |
| protected | string | $schema | The currently selected database schema name. |
|
| protected | $supportsTransactions | |||
| protected | string | $databaseOriginal | The database name specified at initial connection |
|
| protected | string | $schemaOriginal | The schema name specified at initial construction. When model_schema_as_database is set to true selecting the $databaseOriginal database will instead reset the schema to this |
|
| protected | array | $parameters | Connection parameters specified at inital connection |
Methods
Execute the given SQL query.
Execute the given SQL parameterised query with the specified arguments
Determines if the query should be previewed, and thus interrupted silently.
Allows the display and benchmarking of queries as they are being run
Get the autogenerated ID from the previous INSERT query.
Determines if we are connected to a server AND have a valid database selected.
Returns an escaped string. This string won't be quoted, so would be suitable for appending to other quoted strings.
Escapes an identifier (table / database name). Typically the value is simply double quoted. Don't pass in already escaped identifiers in, as this will double escape the value!
Escapes unquoted columns keys in an associative array
Execute a complex manipulation on the database.
Generate a WHERE clause for text matching.
Function to return an SQL datetime expression that can be used with Postgres used for querying a datetime in a certain format
Function to return an SQL datetime expression that can be used with Postgres used for querying a datetime addition
Function to return an SQL datetime expression that can be used with Postgres used for querying a datetime substraction
Can the database override timezone as a connection setting, or does it use the system timezone exclusively?
Query for the version of the currently connected database
Return the number of rows affected by the previous operation.
The core search engine configuration.
No description
Start a prepared transaction See http://developer.postgresql.org/pgdocs/postgres/sql-set-transaction.html for details on transaction isolation options
Create a savepoint that you can jump back to if you encounter problems
Rollback or revert to a savepoint if your queries encounter problems If you encounter a problem at any point during a transaction, you may need to rollback that particular query, or return to a savepoint
Determines if the used database supports application-level locks, which is different from table- or row-level locking.
Sets an application-level lock so that no two processes can run at the same time, also called a "cooperative advisory lock".
Remove an application-level lock file to allow another process to run (if the execution aborts (e.g. due to an error) all locks are automatically released).
Determine if the database with the specified name exists
Retrieves the list of all databases the user has access to
Change the connection to the specified database, optionally creating the database if it doesn't exist in the current schema.
No description
No description
No description
Determines whether to check a database exists on the host by querying the 'postgres' database and running createDatabase.
For instances where multiple databases are used beyond the initial connection you may set this option to true to force database switches to switch schemas instead of using databases. This may be useful if the database user does not have cross-database permissions, and in cases where multiple databases are used (such as in running test cases).
Override the language that tsearch uses. By default it is 'english, but could be any of the supported languages that can be found in the pg_catalog.pg_ts_config table.
Utility method to manually set the schema to an alternative Check existance & sets search path to the supplied schema name
Determines the name of the current database to be reported externally by substituting the schema name for the database name.
Translates a requested database name to a schema name to substitute internally.
Details
DBConnector
getConnector()
Get the current connector
setConnector(DBConnector $connector)
Injector injection point for connector dependency
DBSchemaManager
getSchemaManager()
Returns the current schema manager
setSchemaManager(DBSchemaManager $schemaManager)
Injector injection point for schema manager
DBQueryBuilder
getQueryBuilder()
Returns the current query builder
setQueryBuilder(DBQueryBuilder $queryBuilder)
Injector injection point for schema manager
SS_Query
query(string $sql, int $errorLevel = E_USER_ERROR)
Execute the given SQL query.
SS_Query
preparedQuery(string $sql, array $parameters, int $errorLevel = E_USER_ERROR)
Execute the given SQL parameterised query with the specified arguments
protected bool
previewWrite(string $sql)
Determines if the query should be previewed, and thus interrupted silently.
If so, this function also displays the query via the debuging system. Subclasess should respect the results of this call for each query, and not execute any queries that generate a true response.
protected mixed
benchmarkQuery(string $sql, callable $callback, array $parameters = array())
Allows the display and benchmarking of queries as they are being run
int
getGeneratedID(string $table)
Get the autogenerated ID from the previous INSERT query.
bool
isActive()
Determines if we are connected to a server AND have a valid database selected.
string
escapeString(mixed $value)
Returns an escaped string. This string won't be quoted, so would be suitable for appending to other quoted strings.
string
quoteString(mixed $value)
Wrap a string into DB-specific quotes.
escapeIdentifier(string $value, string $separator = '.')
Escapes an identifier (table / database name). Typically the value is simply double quoted. Don't pass in already escaped identifiers in, as this will double escape the value!
protected array
escapeColumnKeys(array $fieldValues)
Escapes unquoted columns keys in an associative array
manipulate(array $manipulation)
Execute a complex manipulation on the database.
A manipulation is an array of insert / or update sequences. The keys of the array are table names, and the values are map containing 'command' and 'fields'. Command should be 'insert' or 'update', and fields should be a map of field names to field values, NOT including quotes.
The field values could also be in paramaterised format, such as array('MAX(?,?)' => array(42, 69)), allowing the use of raw SQL values such as array('NOW()' => array()).
quiet()
Enable supression of database messages.
clearAllData()
Clear all data out of the database
clearTable(string $table)
Delete all entries from the table instead of truncating it.
This gives a massive speed improvement compared to using TRUNCATE, with the caveat that primary keys are not reset etc.
string
comparisonClause(string $field, string $value, bool $exact = false, bool $negate = false, bool $caseSensitive = null, bool $parameterised = false)
Generate a WHERE clause for text matching.
string
formattedDatetimeClause(string $date, string $format)
Function to return an SQL datetime expression that can be used with Postgres used for querying a datetime in a certain format
string
datetimeIntervalClause($date, string $interval)
Function to return an SQL datetime expression that can be used with Postgres used for querying a datetime addition
string
datetimeDifferenceClause($date1, string $date2)
Function to return an SQL datetime expression that can be used with Postgres used for querying a datetime substraction
bool
supportsCollations()
Returns true if this database supports collations
bool
supportsTimezoneOverride()
Can the database override timezone as a connection setting, or does it use the system timezone exclusively?
string
getVersion()
Query for the version of the currently connected database
string
getDatabaseServer()
Get the database server type (e.g. mysql, postgresql).
This value is passed to the connector as the 'driver' argument when initiating a database connection
int
affectedRows()
Return the number of rows affected by the previous operation.
PaginatedList
searchEngine(array $classesToSearch, string $keywords, int $start, int $pageLength, string $sortBy = "ts_rank DESC", string $extraFilter = "", bool $booleanSearch = false, string $alternativeFileFilter = "", bool $invertedMatch = false)
The core search engine configuration.
bool
supportsTransactions()
Determines if this database supports transactions
supportsExtensions($extensions = array('partitions', 'tablespaces', 'clustering'))
No description
transactionStart($transaction_mode = false, $session_characteristics = false)
Start a prepared transaction See http://developer.postgresql.org/pgdocs/postgres/sql-set-transaction.html for details on transaction isolation options
transactionSavepoint(string $savepoint)
Create a savepoint that you can jump back to if you encounter problems
transactionRollback(string|bool $savepoint = false)
Rollback or revert to a savepoint if your queries encounter problems If you encounter a problem at any point during a transaction, you may need to rollback that particular query, or return to a savepoint
transactionEnd(bool $chain = false)
Commit everything inside this transaction so far
bool
supportsLocks()
Determines if the used database supports application-level locks, which is different from table- or row-level locking.
See getLock() for details.
bool
canLock(string $name)
Returns if the lock is available.
See supportsLocks() to check if locking is generally supported.
bool
getLock(string $name, int $timeout = 5)
Sets an application-level lock so that no two processes can run at the same time, also called a "cooperative advisory lock".
Return FALSE if acquiring the lock fails; otherwise return TRUE, if lock was acquired successfully. Lock is automatically released if connection to the database is broken (either normally or abnormally), making it less prone to deadlocks than session- or file-based locks. Should be accompanied by a releaseLock() call after the logic requiring the lock has completed. Can be called multiple times, in which case locks "stack" (PostgreSQL, SQL Server), or auto-releases the previous lock (MySQL).
Note that this might trigger the database to wait for the lock to be released, delaying further execution.
bool
releaseLock(string $name)
Remove an application-level lock file to allow another process to run (if the execution aborts (e.g. due to an error) all locks are automatically released).
connect(array $parameters)
Instruct the database to generate a live connection
bool
databaseExists(string $name)
Determine if the database with the specified name exists
array
databaseList()
Retrieves the list of all databases the user has access to
bool
selectDatabase(string $name, bool $create = false, int|bool $errorLevel = E_USER_ERROR)
Change the connection to the specified database, optionally creating the database if it doesn't exist in the current schema.
dropSelectedDatabase()
Drop the database that this object is currently connected to.
Use with caution.
string|null
getSelectedDatabase()
Returns the name of the currently selected database
string
now()
Return SQL expression used to represent the current date/time
string
random()
Returns the database-specific version of the random() function
dbDataType($type)
deprecated
deprecated
No description
createDatabase()
deprecated
deprecated
No description
getConnect($parameters)
deprecated
deprecated
No description
prepStringForDB($string)
deprecated
deprecated
No description
dropDatabase()
deprecated
deprecated
No description
allDatabaseNames()
deprecated
deprecated
No description
createTable($table, $fields = null, $indexes = null, $options = null, $advancedOptions = null)
deprecated
deprecated
No description
alterTable($table, $newFields = null, $newIndexes = null, $alteredFields = null, $alteredIndexes = null, $alteredOptions = null, $advancedOptions = null)
deprecated
deprecated
No description
renameTable($oldTableName, $newTableName)
deprecated
deprecated
No description
createField($table, $field, $spec)
deprecated
deprecated
No description
renameField($tableName, $oldName, $newName)
deprecated
deprecated
No description
currentDatabase()
deprecated
deprecated
No description
fieldList($table)
deprecated
deprecated
No description
tableList()
deprecated
deprecated
No description
hasTable($tableName)
deprecated
deprecated
No description
enumValuesForField($tableName, $fieldName)
deprecated
deprecated
No description
addslashes($value)
deprecated
deprecated
No description
beginSchemaUpdate()
deprecated
deprecated
No description
endSchemaUpdate()
deprecated
deprecated
No description
cancelSchemaUpdate()
deprecated
deprecated
No description
isSchemaUpdating()
deprecated
deprecated
No description
doesSchemaNeedUpdating()
deprecated
deprecated
No description
transCreateTable($table, $options = null, $advanced_options = null)
deprecated
deprecated
No description
transAlterTable($table, $options, $advanced_options)
deprecated
deprecated
No description
transCreateField($table, $field, $schema)
deprecated
deprecated
No description
transCreateIndex($table, $index, $schema)
deprecated
deprecated
No description
transAlterField($table, $field, $schema)
deprecated
deprecated
No description
transAlterIndex($table, $index, $schema)
deprecated
deprecated
No description
requireTable($table, $fieldSchema = null, $indexSchema = null, $hasAutoIncPK = true, $options = array(), $extensions = false)
deprecated
deprecated
No description
dontRequireTable($table)
deprecated
deprecated
No description
requireIndex($table, $index, $spec)
deprecated
deprecated
No description
hasField($tableName, $fieldName)
deprecated
deprecated
No description
requireField($table, $field, $spec)
deprecated
deprecated
No description
dontRequireField($table, $fieldName)
deprecated
deprecated
No description
sqlQueryToString(SQLExpression $query, $parameters = array())
deprecated
deprecated
No description
static string
default_fts_cluster_method()
Full text cluster method. (e.g. GIN or GiST)
static string
default_fts_search_method()
Full text search method.
static
allow_query_master_postgres()
Determines whether to check a database exists on the host by querying the 'postgres' database and running createDatabase.
Some locked down systems prevent access to the 'postgres' table in which case you need to set this to false.
If allow_query_master_postgres is false, and model_schema_as_database is also false, then attempts to create or check databases beyond the initial connection will result in a runtime error.
static
model_schema_as_database()
For instances where multiple databases are used beyond the initial connection you may set this option to true to force database switches to switch schemas instead of using databases. This may be useful if the database user does not have cross-database permissions, and in cases where multiple databases are used (such as in running test cases).
If this is true then the database will only be set during the initial connection, and attempts to change to this database will use the 'public' schema instead
static
search_language()
Override the language that tsearch uses. By default it is 'english, but could be any of the supported languages that can be found in the pg_catalog.pg_ts_config table.
protected
connectMaster()
No description
protected
connectDefault()
No description
selectTimezone(string $timezone)
Sets the system timezone for the database connection
string
currentSchema()
Returns the name of the current schema in use
bool
setSchema($schema, bool $create = false, int|bool $errorLevel = E_USER_ERROR)
Utility method to manually set the schema to an alternative Check existance & sets search path to the supplied schema name
setSchemaSearchPath()
Override the schema search path. Search using the arguments supplied.
NOTE: The search path is normally set through setSchema() and only one schema is selected. The facility to add more than one schema to the search path is provided as an advanced PostgreSQL feature for raw SQL queries. Sapphire cannot search for datamodel tables in alternate schemas, so be wary of using alternate schemas within the ORM environment.
string
schemaToDatabaseName(string $schema)
Determines the name of the current database to be reported externally by substituting the schema name for the database name.
Should only be used when model_schema_as_database is true
string
databaseToSchemaName(string $database)
Translates a requested database name to a schema name to substitute internally.
Should only be used when model_schema_as_database is true