1. class
  2. SilverStripe
  3. \
  4. ORM
  5. \
  6. DB

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
ALT_DB_KEY

Session key for alternative database name

Config options

alternative_database_enabled bool Allow alternative DB to be disabled.

Properties

static string $lastQuery The last SQL query run.

Methods

static 
set_conn(Database $connection, string $name = 'default')

Set the global database connection.

static 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(string $name = null)

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

static string|false
get_alternative_database_name()

Get the name of the database in use

static bool
valid_alternative_database_name(string $name)

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

static Database
connect(array $databaseConfig, string $label = 'default')

Specify connection to a database

static 
setConfig(array $databaseConfig, string $name = 'default')

Set config for a lazy-connected database

static mixed
getConfig(string $name = 'default')

Get the named connection config

static 
connection_attempted()

Returns true if a database connection has been attempted.

static 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 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(string $table)

Get the autogenerated ID from the previous INSERT query.

static boolean
is_active()

Check if the connection to the database is active.

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 string
create_table(string $table, array$fields $fields = null, array $indexes = null, array $options = null, array $advancedOptions = null)

Create a new table.

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

Create a new field on a table.

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 
require_field(string $table, string $field, string $spec)

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

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

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

static 
dont_require_table(string $table)

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

static 
dont_require_field(string $table, string $fieldName)

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

static boolean
check_and_repair_table(string $table)

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

static integer
affected_rows()

Return the number of rows affected by the previous operation.

static array
table_list()

Returns a list of all tables in the database.

static array
field_list(string $table)

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

static 
quiet(bool $quiet = true)

Enable supression of database messages.

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

Show a message about database alteration

Details

at line 87
static set_conn(Database $connection, string $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

Database $connection The connecton object to set as the connection.
string $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 99
static 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

Database

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

deprecated since version 4.0 Use DB::get_conn instead

Parameters

$name

at line 131
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 149
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 167
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 191
static set_alternative_database_name(string $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

string $name

at line 227
static string|false get_alternative_database_name()

Get the name of the database in use

Return Value

string|false Name of temp database, or false if not set

at line 263
static bool 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

bool

at line 285
static Database connect(array $databaseConfig, string $label = 'default')

Specify connection to a database

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

Parameters

array $databaseConfig A map of options. The 'type' is the name of the subclass of SS_Database to use. For the rest of the options, see the specific class.
string $label identifier for the connection

Return Value

Database

at line 315
static setConfig(array $databaseConfig, string $name = 'default')

Set config for a lazy-connected database

Parameters

array $databaseConfig
string $name

at line 326
static mixed getConfig(string $name = 'default')

Get the named connection config

Parameters

string $name

Return Value

mixed

at line 338
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 349
static 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

Query

at line 365
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 386
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 441
static 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

Query

at line 490
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 502
static int get_generated_id(string $table)

Get the autogenerated ID from the previous INSERT query.

Parameters

string $table

Return Value

int

at line 512
static boolean is_active()

Check if the connection to the database is active.

Return Value

boolean

at line 525
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 543
static string create_table(string $table, array$fields $fields = null, array $indexes = null, array $options = null, array $advancedOptions = null)

Create a new table.

Parameters

string $table The name of the table
array$fields $fields A map of field names to field types
array $indexes A map of indexes
array $options An map of additional options. The available keys are as follows: - 'MSSQLDatabase'/'MySQLDatabase'/'PostgreSQLDatabase' - database-specific options such as "engine" for MySQL. - 'temporary' - If true, then a temporary table will be created
array $advancedOptions Advanced creation options

Return Value

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

at line 559
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 579
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 597
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 609
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 619
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 630
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 641
static boolean check_and_repair_table(string $table)

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

Parameters

string $table The name of the table.

Return Value

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

at line 651
static integer affected_rows()

Return the number of rows affected by the previous operation.

Return Value

integer The number of affected rows

at line 662
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 674
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 quiet(bool $quiet = true)

Enable supression of database messages.

Parameters

bool $quiet

at line 695
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]