DB

class DB

Global database interface, complete with static methods.

Use this class for interacting with the database.

Constants

USE_ANSI_SQL

This constant was added in SilverStripe 2.4 to indicate that SQL-queries should now use ANSI-compatible syntax. The most notable affect of this change is that table and field names should be escaped with double quotes and not backticks

Properties

static string $lastQuery The last SQL query run.

Methods

static 
set_conn(SS_Database $connection, $name = 'default')

Set the global database connection.

static 
setConn(SS_Database $connection, $name = 'default') deprecated

No description

static SS_Database
get_conn(string $name = 'default')

Get the global database connection.

static 
getConn($name = 'default') deprecated

No description

static DBSchemaManager
get_schema(string $name = 'default')

Retrieves the schema manager for the current database

static string
build_sql(SQLExpression $expression, array $parameters, string $name = 'default')

Builds a sql query with the specified connection

static DBConnector
get_connector(string $name = 'default')

Retrieves the connector object for the current database

static 
set_alternative_database_name($name = null)

Set an alternative database in a browser cookie, with the cookie lifetime set to the browser session.

static 
get_alternative_database_name()

Get the name of the database in use

static Boolean
valid_alternative_database_name(String $name)

Determines if the name is valid, as a security measure against setting arbitrary databases.

static SS_Database
connect($databaseConfig, $label = 'default')

Connect to a database.

static 
connection_attempted()

Returns true if a database connection has been attempted.

static 
getConnect($parameters) deprecated

No description

static SS_Query
query(string $sql, int $errorLevel = E_USER_ERROR)

Execute the given SQL query.

static string|null
placeholders(array|integer $input, string $join = ', ')

Helper function for generating a list of parameter placeholders for the given argument(s)

static string
inline_parameters(string $sql, array $parameters)

No description

static SS_Query
prepared_query(string $sql, array $parameters, int $errorLevel = E_USER_ERROR)

Execute the given SQL parameterised query with the specified arguments

static 
manipulate(array $manipulation)

Execute a complex manipulation on the database.

static int
get_generated_id($table)

Get the autogenerated ID from the previous INSERT query.

static 
getGeneratedID($table) deprecated

No description

static boolean
is_active()

Check if the connection to the database is active.

static 
isActive() deprecated

No description

static boolean
create_database(string $database)

Create the database and connect to it. This can be called if the initial database connection is not successful because the database does not exist.

static 
createDatabase($connect, $username, $password, $database) deprecated

No description

static string
create_table($table, $fields = null, $indexes = null, $options = null, $advancedOptions = null)

Create a new table.

static 
createTable($table, $fields = null, $indexes = null, $options = null) deprecated

No description

static 
create_field(string $table, string $field, string $spec)

Create a new field on a table.

static 
createField($table, $field, $spec) deprecated

No description

static 
require_table(string $table, string $fieldSchema = null, string $indexSchema = null, boolean $hasAutoIncPK = true, string $options = null, array $extensions = null)

Generate the following table in the database, modifying whatever already exists as necessary.

static 
requireTable($table, $fieldSchema = null, $indexSchema = null, $hasAutoIncPK = true, $options = null, $extensions = null) deprecated

No description

static 
require_field(string $table, string $field, string $spec)

Generate the given field on the table, modifying whatever already exists as necessary.

static 
requireField($table, $field, $spec) deprecated

No description

static 
require_index(string $table, string $index, string|boolean $spec)

Generate the given index in the database, modifying whatever already exists as necessary.

static 
requireIndex($table, $index, $spec) deprecated

No description

static 
dont_require_table(string $table)

If the given table exists, move it out of the way by renaming it to obsolete(tablename).

static 
dontRequireTable($table) deprecated

No description

static 
dont_require_field(string $table, string $fieldName)

See {@link SS_Database->dontRequireField()}.

static 
dontRequireField($table, $fieldName) deprecated

No description

static boolean
check_and_repair_table($table)

Checks a table's integrity and repairs it if necessary.

static 
checkAndRepairTable($table) deprecated

No description

static integer
affected_rows()

Return the number of rows affected by the previous operation.

static 
affectedRows() deprecated

No description

static array
table_list()

Returns a list of all tables in the database.

static 
tableList() deprecated

No description

static array
field_list(string $table)

Get a list of all the fields for the given table.

static 
fieldList($table) deprecated

No description

static 
quiet()

Enable supression of database messages.

static 
alteration_message(string $message, string $type = "")

Show a message about database alteration

Details

at line 48
static set_conn(SS_Database $connection, $name = 'default')

Set the global database connection.

Pass an object that's a subclass of SS_Database. This object will be used when {@link DB::query()} is called.

Parameters

SS_Database $connection The connecton object to set as the connection.
$name The name to give to this connection. If you omit this argument, the connection will be the default one used by the ORM. However, you can store other named connections to be accessed through DB::get_conn($name). This is useful when you have an application that needs to connect to more than one database.

at line 55
static setConn(SS_Database $connection, $name = 'default') deprecated

deprecated since version 4.0 Use DB::set_conn instead

Parameters

SS_Database $connection
$name

at line 67
static SS_Database get_conn(string $name = 'default')

Get the global database connection.

Parameters

string $name An optional name given to a connection in the DB::setConn() call. If omitted, the default connection is returned.

Return Value

SS_Database

at line 76
static getConn($name = 'default') deprecated

deprecated since version 4.0 Use DB::get_conn instead

Parameters

$name

at line 88
static DBSchemaManager get_schema(string $name = 'default')

Retrieves the schema manager for the current database

Parameters

string $name An optional name given to a connection in the DB::setConn() call. If omitted, the default connection is returned.

Return Value

DBSchemaManager

at line 102
static string build_sql(SQLExpression $expression, array $parameters, string $name = 'default')

Builds a sql query with the specified connection

Parameters

SQLExpression $expression The expression object to build from
array $parameters Out parameter for the resulting query parameters
string $name An optional name given to a connection in the DB::setConn() call. If omitted, the default connection is returned.

Return Value

string The resulting SQL as a string

at line 119
static DBConnector get_connector(string $name = 'default')

Retrieves the connector object for the current database

Parameters

string $name An optional name given to a connection in the DB::setConn() call. If omitted, the default connection is returned.

Return Value

DBConnector

at line 137
static set_alternative_database_name($name = null)

Set an alternative database in a browser cookie, with the cookie lifetime set to the browser session.

This is useful for integration testing on temporary databases.

There is a strict naming convention for temporary databases to avoid abuse: (default: 'ss_') + tmpdb + <7 digits> As an additional security measure, temporary databases will be ignored in "live" mode.

Note that the database will be set on the next request. Set it to null to revert to the main database.

Parameters

$name

at line 177
static get_alternative_database_name()

Get the name of the database in use

at line 206
static Boolean valid_alternative_database_name(String $name)

Determines if the name is valid, as a security measure against setting arbitrary databases.

Parameters

String $name

Return Value

Boolean

at line 226
static SS_Database connect($databaseConfig, $label = 'default')

Connect to a database.

Given the database configuration, this method will create the correct subclass of {@link SS_Database}.

Parameters

$databaseConfig
$label

Return Value

SS_Database

at line 256
static connection_attempted()

Returns true if a database connection has been attempted.

In particular, it lets the caller know if we're still so early in the execution pipeline that we haven't even tried to connect to the database yet.

at line 263
static getConnect($parameters) deprecated

deprecated since version 4.0 DB::getConnect was never implemented and is obsolete

Parameters

$parameters

at line 273
static SS_Query query(string $sql, int $errorLevel = E_USER_ERROR)

Execute the given SQL query.

Parameters

string $sql The SQL query to execute
int $errorLevel The level of error reporting to enable for the query

Return Value

SS_Query

at line 288
static string|null placeholders(array|integer $input, string $join = ', ')

Helper function for generating a list of parameter placeholders for the given argument(s)

Parameters

array|integer $input An array of items needing placeholders, or a number to specify the number of placeholders
string $join The string to join each placeholder together with

Return Value

string|null Either a list of placeholders, or null

at line 306
static string inline_parameters(string $sql, array $parameters)

Parameters

string $sql The parameterised query
array $parameters The parameters to inject into the query

Return Value

string

at line 362
static SS_Query prepared_query(string $sql, array $parameters, int $errorLevel = E_USER_ERROR)

Execute the given SQL parameterised query with the specified arguments

Parameters

string $sql The SQL query to execute. The ? character will denote parameters.
array $parameters An ordered list of arguments.
int $errorLevel The level of error reporting to enable for the query

Return Value

SS_Query

at line 410
static 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, including quotes. The field value can also be a SQL function or similar.

Example: array( // Command: insert "table name" => array( "command" => "insert", "fields" => array( "ClassName" => "'MyClass'", // if you're setting a literal, you need to escape and provide quotes "Created" => "now()", // alternatively, you can call DB functions "ID" => 234, ), "id" => 234 // an alternative to providing ID in the fields list ),

// Command: update "other table" => array( "command" => "update", "fields" => array( "ClassName" => "'MyClass'", "LastEdited" => "now()", ), "where" => "ID = 234", "id" => 234 // an alternative to providing a where clause ), )

You'll note that only one command on a given table can be called. That's a limitation of the system that's due to it being written for {@link DataObject::write()}, which needs to do a single write on a number of different tables.

Parameters

array $manipulation

at line 419
static int get_generated_id($table)

Get the autogenerated ID from the previous INSERT query.

Parameters

$table

Return Value

int

at line 426
static getGeneratedID($table) deprecated

deprecated since version 4.0 Use DB::get_generated_id instead

Parameters

$table

at line 436
static boolean is_active()

Check if the connection to the database is active.

Return Value

boolean

at line 443
static isActive() deprecated

deprecated since version 4.0 Use DB::is_active instead

at line 456
static boolean create_database(string $database)

Create the database and connect to it. This can be called if the initial database connection is not successful because the database does not exist.

Parameters

string $database Name of database to create

Return Value

boolean Returns true if successful

at line 463
static createDatabase($connect, $username, $password, $database) deprecated

deprecated since version 4.0 Use DB::create_database instead

Parameters

$connect
$username
$password
$database

at line 480
static string create_table($table, $fields = null, $indexes = null, $options = null, $advancedOptions = null)

Create a new table.

Parameters

$table
$fields
$indexes
$options
$advancedOptions

Return Value

string The table name generated. This may be different from the table name, for example with temporary tables.

at line 489
static createTable($table, $fields = null, $indexes = null, $options = null) deprecated

deprecated since version 4.0 Use DB::create_table instead

Parameters

$table
$fields
$indexes
$options

at line 500
static create_field(string $table, string $field, string $spec)

Create a new field on a table.

Parameters

string $table Name of the table.
string $field Name of the field to add.
string $spec The field specification, eg 'INTEGER NOT NULL'

at line 507
static createField($table, $field, $spec) deprecated

deprecated since version 4.0 Use DB::create_field instead

Parameters

$table
$field
$spec

at line 527
static require_table(string $table, string $fieldSchema = null, string $indexSchema = null, boolean $hasAutoIncPK = true, string $options = null, array $extensions = null)

Generate the following table in the database, modifying whatever already exists as necessary.

Parameters

string $table The name of the table
string $fieldSchema A list of the fields to create, in the same form as DataObject::$db
string $indexSchema A list of indexes to create. The keys of the array are the names of the index. The values of the array can be one of: - true: Create a single column index on the field named the same as the index. - array('fields' => array('A','B','C'), 'type' => 'index/unique/fulltext'): This gives you full control over the index.
boolean $hasAutoIncPK A flag indicating that the primary key on this table is an autoincrement type
string $options SQL statement to append to the CREATE TABLE call.
array $extensions List of extensions

at line 537
static requireTable($table, $fieldSchema = null, $indexSchema = null, $hasAutoIncPK = true, $options = null, $extensions = null) deprecated

deprecated since version 4.0 Use DB::require_table instead

Parameters

$table
$fieldSchema
$indexSchema
$hasAutoIncPK
$options
$extensions

at line 551
static require_field(string $table, string $field, string $spec)

Generate the given field on the table, modifying whatever already exists as necessary.

Parameters

string $table The table name.
string $field The field name.
string $spec The field specification.

at line 558
static requireField($table, $field, $spec) deprecated

deprecated since version 4.0 Use DB::require_field instead

Parameters

$table
$field
$spec

at line 570
static require_index(string $table, string $index, string|boolean $spec)

Generate the given index in the database, modifying whatever already exists as necessary.

Parameters

string $table The table name.
string $index The index name.
string|boolean $spec The specification of the index. See requireTable() for more information.

at line 577
static requireIndex($table, $index, $spec) deprecated

deprecated since version 4.0 Use DB::require_index instead

Parameters

$table
$index
$spec

at line 587
static dont_require_table(string $table)

If the given table exists, move it out of the way by renaming it to obsolete(tablename).

Parameters

string $table The table name.

at line 594
static dontRequireTable($table) deprecated

deprecated since version 4.0 Use DB::dont_require_table instead

Parameters

$table

at line 605
static dont_require_field(string $table, string $fieldName)

See {@link SS_Database->dontRequireField()}.

Parameters

string $table The table name.
string $fieldName The field name not to require

at line 612
static dontRequireField($table, $fieldName) deprecated

deprecated since version 4.0 Use DB::dont_require_field instead

Parameters

$table
$fieldName

at line 623
static boolean check_and_repair_table($table)

Checks a table's integrity and repairs it if necessary.

Parameters

$table

Return Value

boolean Return true if the table has integrity after the method is complete.

at line 630
static checkAndRepairTable($table) deprecated

deprecated since version 4.0 Use DB::check_and_repair_table instead

Parameters

$table

at line 640
static integer affected_rows()

Return the number of rows affected by the previous operation.

Return Value

integer The number of affected rows

at line 647
static affectedRows() deprecated

deprecated since version 4.0 Use DB::affected_rows instead

at line 658
static array table_list()

Returns a list of all tables in the database.

The table names will be in lower case.

Return Value

array The list of tables

at line 665
static tableList() deprecated

deprecated since version 4.0 Use DB::table_list instead

at line 677
static array field_list(string $table)

Get a list of all the fields for the given table.

Returns a map of field name => field spec.

Parameters

string $table The table name.

Return Value

array The list of fields

at line 684
static fieldList($table) deprecated

deprecated since version 4.0 Use DB::field_list instead

Parameters

$table

at line 692
static quiet()

Enable supression of database messages.

at line 702
static alteration_message(string $message, string $type = "")

Show a message about database alteration

Parameters

string $message to display
string $type one of [created|changed|repaired|obsolete|deleted|error]