Schema API

API to handle database schemas.

A Backdrop schema definition is an array structure representing one or more tables and their related keys and indexes. A schema is defined by hook_schema(), which usually lives in a modulename.install file.

By implementing hook_schema() and specifying the tables your module declares, you can create and drop these tables on all supported database engines. You don't have to deal with the different SQL dialects for table creation and alteration of the supported database engines.

hook_schema() should return an array with a key for each table that the module defines.

The following keys are defined:

• 'description': A string in non-markup plain text describing this table and its purpose. References to other tables should be enclosed in curly-brackets. For example, the node_revisions table description field might contain "Stores per-revision title and body data for each {node}."
• 'fields': An associative array ('fieldname' => specification) that describes the table's database columns. The specification is also an array. The following specification parameters are defined:
  • 'description': A string in non-markup plain text describing this field and its purpose. References to other tables should be enclosed in curly-brackets. For example, the node table vid field description might contain "Always holds the largest (most recent) {node_revision}.vid value for this nid."
  • 'type': The generic datatype: 'char', 'varchar', 'text', 'blob', 'int', 'float', 'numeric', or 'serial'. Most types just map to the according database engine specific datatypes. Use 'serial' for auto incrementing fields. This will expand to 'INT auto_increment' on MySQL.
  • 'serialize': A boolean indicating whether the field will be stored as a serialized string.
  • 'size': The data size: 'tiny', 'small', 'medium', 'normal', 'big'. This is a hint about the largest value the field will store and determines which of the database engine specific datatypes will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT). 'normal', the default, selects the base type (e.g. on MySQL, INT, VARCHAR, BLOB, etc.). Not all sizes are available for all data types. See DatabaseSchema::getFieldTypeMap() for possible combinations.
  • 'not null': If true, no NULL values will be allowed in this database column. Defaults to false.
  • 'default': The field's default value. The PHP type of the value matters: '', '0', and 0 are all different. If you specify '0' as the default value for a type 'int' field it will not work because '0' is a string containing the character "zero", not an integer.
  • 'length': The maximal length of a type 'char', 'varchar' or 'text' field. Ignored for other field types.
  • 'unsigned': A boolean indicating whether a type 'int', 'float' and 'numeric' only is signed or unsigned. Defaults to FALSE. Ignored for other field types.
  • 'precision', 'scale': For type 'numeric' fields, indicates the precision (total number of significant digits) and scale (decimal digits right of the decimal point). Both values are mandatory. Ignored for other field types.
  • 'binary': A boolean indicating that MySQL should force 'char', 'varchar' or 'text' fields to use case-sensitive binary collation. This has no effect on other database types for which case sensitivity is already the default behavior.

  All parameters apart from 'type' are optional except that type 'numeric' columns must specify 'precision' and 'scale', and type 'varchar' must specify the 'length' parameter.

• 'primary key': An array of one or more key column specifiers (see below) that form the primary key.
• 'unique keys': An associative array of unique keys ('keyname' => specification). Each specification is an array of one or more key column specifiers (see below) that form a unique key on the table.
• 'foreign keys': An associative array of relations ('my_relation' => specification). Each specification is an array containing the name of the referenced table ('table'), and an array of column mappings ('columns'). Column mappings are defined by key pairs ('source_column' => 'referenced_column'). This key is for documentation purposes only; foreign keys are not created in the database, nor are they enforced by Backdrop.
• 'indexes': An associative array of indexes ('indexname' => specification). Each specification is an array of one or more key column specifiers (see below) that form an index on the table.

A key column specifier is either a string naming a column or an array of two elements, column name and length, specifying a prefix of the named column.

As an example, here is a SUBSET of the schema definition for Backdrop's 'node' table. It show four fields (nid, vid, type, and title), the primary key on field 'nid', a unique key named 'vid' on field 'vid', and two indexes, one named 'nid' on field 'nid' and one named 'node_title_type' on the field 'title' and the first four bytes of the field 'type':

$schema['node'] = array(
  'description' => 'The base table for nodes.',
  'fields' => array(
    'nid'       => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
    'vid'       => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE,'default' => 0),
    'type'      => array('type' => 'varchar','length' => 32,'not null' => TRUE, 'default' => ''),
    'language'  => array('type' => 'varchar','length' => 12,'not null' => TRUE,'default' => ''),
    'title'     => array('type' => 'varchar','length' => 255,'not null' => TRUE, 'default' => ''),
    'uid'       => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'status'    => array('type' => 'int', 'not null' => TRUE, 'default' => 1),
    'created'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'changed'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'comment'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'promote'   => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'moderate'  => array('type' => 'int', 'not null' => TRUE,'default' => 0),
    'sticky'    => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
    'tnid'      => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),
    'translate' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
  ),
  'indexes' => array(
    'node_changed'        => array('changed'),
    'node_created'        => array('created'),
    'node_moderate'       => array('moderate'),
    'node_frontpage'      => array('promote', 'status', 'sticky', 'created'),
    'node_status_type'    => array('status', 'type', 'nid'),
    'node_title_type'     => array('title', array('type', 4)),
    'node_type'           => array(array('type', 4)),
    'uid'                 => array('uid'),
    'tnid'                => array('tnid'),
    'translate'           => array('translate'),
  ),
  'unique keys' => array(
    'vid' => array('vid'),
  ),
  // For documentation purposes only; foreign keys are not created in the
  // database.
  'foreign keys' => array(
    'node_revision' => array(
      'table' => 'node_revision',
      'columns' => array('vid' => 'vid'),
     ),
    'node_author' => array(
      'table' => 'users',
      'columns' => array('uid' => 'uid'),
     ),
   ),
  'primary key' => array('nid'),
);

See also

backdrop_install_schema()

File

includes/database/schema.inc, line 9

Generic Database schema code.

Functions

Name	Location	Description
backdrop_get_complete_schema	includes/bootstrap.inc	Gets the whole database schema.
backdrop_get_schema	includes/bootstrap.inc	Gets the schema definition of a table, or the whole database schema.
backdrop_write_record	includes/common.inc	Saves (inserts or updates) a record to the database based upon the schema.
backdrop_schema_fields_sql	includes/common.inc	Retrieves a list of fields from a table schema.
_backdrop_schema_initialize	includes/common.inc	Fills in required default values for table definitions from hook_schema().
backdrop_install_schema	includes/common.inc	Creates all tables defined in a module's hook_schema().
backdrop_get_schema_unprocessed	includes/common.inc	Returns the unprocessed and unaltered version of a module's schema.
backdrop_uninstall_schema	includes/common.inc	Removes all tables defined in a module's hook_schema().
db_field_set_default	includes/database/database.inc	Sets the default value for a field.
db_drop_field	includes/database/database.inc	Drops a field.
db_add_field	includes/database/database.inc	Adds a new field to a table.
db_drop_table	includes/database/database.inc	Drops a table.
db_rename_table	includes/database/database.inc	Renames a table.
_db_create_keys_sql	includes/database/database.inc
db_find_prefixed_tables	includes/database/database.inc	Finds all tables that are like the specified base table name. This is a backport of the change made to db_find_tables in Drupal 8 to work with virtual, un-prefixed table names. The original function is retained for Backwards Compatibility.
db_change_field	includes/database/database.inc	Changes a field definition.
db_field_exists	includes/database/database.inc	Checks if a column exists in the given table.
db_table_exists	includes/database/database.inc	Checks if a table exists.
db_index_exists	includes/database/database.inc	Checks if an index exists in the given table.
db_field_names	includes/database/database.inc	Returns an array of field names from an array of key/index column specifiers.
db_create_table	includes/database/database.inc	Creates a new table from a Backdrop table definition.
db_find_tables	includes/database/database.inc	Finds all tables that are like the specified base table name.
db_drop_index	includes/database/database.inc	Drops an index.
db_add_index	includes/database/database.inc	Adds an index.
db_drop_unique_key	includes/database/database.inc	Drops a unique key.
db_add_unique_key	includes/database/database.inc	Adds a unique key.
db_drop_primary_key	includes/database/database.inc	Drops the primary key of a database table.
db_add_primary_key	includes/database/database.inc	Adds a primary key to a database table.
db_field_set_no_default	includes/database/database.inc	Sets a field to have no default value.
hook_schema	modules/system/system.api.php	Define the current version of the database schema.
hook_schema_alter	modules/system/system.api.php	Perform alterations to existing database schemas.

Classes

Name	Location	Description
DatabaseSchema_mysql	drivers/database_mysql/schema.inc
SchemaCache	includes/bootstrap.inc	Extends BackdropCacheArray to allow for dynamic building of the schema cache.
DatabaseSchemaObjectDoesNotExistException	includes/database/schema.inc	Exception thrown if an object being modified doesn't exist yet.
DatabaseSchemaObjectExistsException	includes/database/schema.inc	Exception thrown if an object being created already exists.
DatabaseSchema	includes/database/schema.inc	Base class for database schema definitions.