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

Properties

public static string $lastQuery

The last SQL query run.

Methods

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

Set the global database connection.

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

No description

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

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

public static 
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($databaseConfig, $label = 'default')

Connect to a database.

public static 
connection_attempted()

Returns true if a database connection has been attempted.

public static 
getConnect($parameters) deprecated

No description

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

Get the autogenerated ID from the previous INSERT query.

public static 
getGeneratedID($table) deprecated

No description

public static 
bool
is_active()

Check if the connection to the database is active.

public static 
isActive() deprecated

No description

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 
createDatabase($connect, $username, $password, $database) deprecated

No description

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

Create a new table.

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

No description

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

Create a new field on a table.

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

No description

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 
requireTable($table, $fieldSchema = null, $indexSchema = null, $hasAutoIncPK = true, $options = null, $extensions = null) deprecated

No description

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 
requireField($table, $field, $spec) deprecated

No description

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 
requireIndex($table, $index, $spec) deprecated

No description

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 
dontRequireTable($table) deprecated

No description

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

See SS_Database->dontRequireField().

public static 
dontRequireField($table, $fieldName) deprecated

No description

public static 
bool
check_and_repair_table($table)

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

public static 
checkAndRepairTable($table) deprecated

No description

public static 
int
affected_rows()

Return the number of rows affected by the previous operation.

public static 
affectedRows() deprecated

No description

public static 
array
table_list()

Returns a list of all tables in the database.

public static 
tableList() deprecated

No description

public static 
array
field_list(string $table)

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

public static 
fieldList($table) deprecated

No description

public static 
quiet()

Enable supression of database messages.

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

Show a message about database alteration

Details

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

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

deprecated since version 4.0 Use DB::set_conn instead

No description

Parameters

SS_Database $connection
$name

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

static getConn($name = 'default') deprecated

deprecated since version 4.0 Use DB::get_conn instead

No description

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

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

Parameters

string $name

Return Value

bool

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

Connect to a database.

Given the database configuration, this method will create the correct subclass of SS_Database.

Parameters

$databaseConfig
$label

Return Value

SS_Database

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 getConnect($parameters) deprecated

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

No description

Parameters

$parameters

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

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

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

Get the autogenerated ID from the previous INSERT query.

Parameters

$table

Return Value

int

static getGeneratedID($table) deprecated

deprecated since version 4.0 Use DB::get_generated_id instead

No description

Parameters

$table

static bool is_active()

Check if the connection to the database is active.

Return Value

bool

static isActive() deprecated

deprecated since version 4.0 Use DB::is_active instead

No description

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 createDatabase($connect, $username, $password, $database) deprecated

deprecated since version 4.0 Use DB::create_database instead

No description

Parameters

$connect
$username
$password
$database

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

Create a new table.

Parameters

$table
$fields
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
$advancedOptions

Return Value

string

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

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

deprecated since version 4.0 Use DB::create_table instead

No description

Parameters

$table
$fields
$indexes
$options

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 createField($table, $field, $spec) deprecated

deprecated since version 4.0 Use DB::create_field instead

No description

Parameters

$table
$field
$spec

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 requireTable($table, $fieldSchema = null, $indexSchema = null, $hasAutoIncPK = true, $options = null, $extensions = null) deprecated

deprecated since version 4.0 Use DB::require_table instead

No description

Parameters

$table
$fieldSchema
$indexSchema
$hasAutoIncPK
$options
$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 requireField($table, $field, $spec) deprecated

deprecated since version 4.0 Use DB::require_field instead

No description

Parameters

$table
$field
$spec

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 requireIndex($table, $index, $spec) deprecated

deprecated since version 4.0 Use DB::require_index instead

No description

Parameters

$table
$index
$spec

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 dontRequireTable($table) deprecated

deprecated since version 4.0 Use DB::dont_require_table instead

No description

Parameters

$table

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 dontRequireField($table, $fieldName) deprecated

deprecated since version 4.0 Use DB::dont_require_field instead

No description

Parameters

$table
$fieldName

static bool check_and_repair_table($table)

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

Parameters

$table

Return Value

bool

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

static checkAndRepairTable($table) deprecated

deprecated since version 4.0 Use DB::check_and_repair_table instead

No description

Parameters

$table

static int affected_rows()

Return the number of rows affected by the previous operation.

Return Value

int

The number of affected rows

static affectedRows() deprecated

deprecated since version 4.0 Use DB::affected_rows instead

No description

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 tableList() deprecated

deprecated since version 4.0 Use DB::table_list instead

No description

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 fieldList($table) deprecated

deprecated since version 4.0 Use DB::field_list instead

No description

Parameters

$table

static quiet()

Enable supression of database messages.

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]