class DB (View source)

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

Properties

protected static Database $connections

The global database connection.

protected static array $configs

List of configurations for each connection

public static string $lastQuery

The last SQL query run.

Methods

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

Set the global database connection.

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

Get the global database connection.

public static 
getConn($name = 'default') deprecated

No description

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

Retrieves the schema manager for the current database

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

Builds a sql query with the specified connection

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

Retrieves the connector object for the current database

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

public static 
string|false
get_alternative_database_name()

Get the name of the database in use

public static 
bool
valid_alternative_database_name(string $name)

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

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

Specify connection to a database

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

Set config for a lazy-connected database

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

Get the named connection config

public static 
connection_attempted()

Returns true if a database connection has been attempted.

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

Execute the given SQL query.

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

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

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

No description

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

Execute the given SQL parameterised query with the specified arguments

public static 
manipulate(array $manipulation)

Execute a complex manipulation on the database.

public static 
int
get_generated_id(string $table)

Get the autogenerated ID from the previous INSERT query.

public static 
bool
is_active()

Check if the connection to the database is active.

public static 
bool
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.

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

Create a new table.

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

Create a new field on a table.

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

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

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

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

public static 
require_index(string $table, string $index, string|bool $spec)

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

public static 
dont_require_table(string $table)

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

public static 
dont_require_field(string $table, string $fieldName)

See SS_Database->dontRequireField().

public static 
bool
check_and_repair_table(string $table)

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

public static 
int
affected_rows()

Return the number of rows affected by the previous operation.

public static 
array
table_list()

Returns a list of all tables in the database.

public static 
array
field_list(string $table)

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

public static 
quiet(bool $quiet = true)

Enable suppression of database messages.

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

Show a message about database alteration

Details

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 DB::query() is called.

Parameters

Database $connection

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

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

static getConn($name = 'default') deprecated

deprecated 4.0.1 Use DB::get_conn() instead

No description

PSR-2 standardisation will probably un-deprecate this

Parameters

$name

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

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

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

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

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

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

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

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

Set config for a lazy-connected database

Parameters

array $databaseConfig
string $name

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

Get the named connection config

Parameters

string $name

Return Value

mixed

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.

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

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

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

Parameters

array|int $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

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

No description

Parameters

string $sql

The parameterised query

array $parameters

The parameters to inject into the query

Return Value

string

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

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 DataObject::write(), which needs to do a single write on a number of different tables.

Update this to support paramaterised queries

Parameters

array $manipulation

static int get_generated_id(string $table)

Get the autogenerated ID from the previous INSERT query.

Parameters

string $table

Return Value

int

static bool is_active()

Check if the connection to the database is active.

Return Value

bool

static bool 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

bool

Returns true if successful

static string create_table(string $table, array $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

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.

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'

static require_table(string $table, string $fieldSchema = null, string $indexSchema = null, bool $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.
bool $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

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.

static require_index(string $table, string $index, string|bool $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|bool $spec

The specification of the index. See requireTable() for more information.

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.

static dont_require_field(string $table, string $fieldName)

See SS_Database->dontRequireField().

Parameters

string $table

The table name.

string $fieldName

The field name not to require

static bool 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

bool

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

static int affected_rows()

Return the number of rows affected by the previous operation.

Return Value

int

The number of affected rows

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

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

static quiet(bool $quiet = true)

Enable suppression of database messages.

Parameters

bool $quiet

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]