Schema API

You are here

  1. 7 includes/database/schema.inc schemaapi
  2. 6 includes/database.inc schemaapi
  3. 8 core/lib/Drupal/Core/Database/Schema.php schemaapi

A Drupal 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 easily 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 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 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_revisions}.vid value for this nid."
    • 'type': The generic datatype: 'varchar', 'int', 'serial' 'float', 'numeric', 'text', 'blob' or 'datetime'. 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 db_type_map() 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.

    All parameters apart from 'type' are optional except that type 'numeric' columns must specify 'precision' and 'scale'.

  • '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.
  • 'indexes': An associative array of indexes ('indexame' => 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 Drupal'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(
  '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' => ''),
    'title'    => array('type' => 'varchar', 'length' => 128, 'not null' => TRUE, 'default' => ''),
  ),
  'primary key' => array('nid'),
  'unique keys' => array(
    'vid'     => array('vid')
  ),
  'indexes' => array(
    'nid'                 => array('nid'),
    'node_title_type'     => array('title', array('type', 4)),
  ),
);

See also

drupal_install_schema()

Functions

Namesort descending Location Description
db_add_field includes/database.pgsql.inc Add a new field to a table.
db_add_field includes/database.mysql-common.inc Add a new field to a table.
db_add_index includes/database.pgsql.inc Add an index.
db_add_index includes/database.mysql-common.inc Add an index.
db_add_primary_key includes/database.pgsql.inc Add a primary key.
db_add_primary_key includes/database.mysql-common.inc Add a primary key.
db_add_unique_key includes/database.pgsql.inc Add a unique key.
db_add_unique_key includes/database.mysql-common.inc Add a unique key.
db_change_field includes/database.pgsql.inc Change a field definition.
db_change_field includes/database.mysql-common.inc
db_create_table includes/database.inc Create a new table from a Drupal table definition.
db_create_table_sql includes/database.pgsql.inc Generate SQL to create a new table from a Drupal schema definition.
db_create_table_sql includes/database.mysql-common.inc Generate SQL to create a new table from a Drupal schema definition.
db_drop_field includes/database.pgsql.inc Drop a field.
db_drop_field includes/database.mysql-common.inc Drop a field.
db_drop_index includes/database.pgsql.inc Drop an index.
db_drop_index includes/database.mysql-common.inc Drop an index.
db_drop_primary_key includes/database.pgsql.inc Drop the primary key.
db_drop_primary_key includes/database.mysql-common.inc Drop the primary key.
db_drop_table includes/database.pgsql.inc Drop a table.
db_drop_table includes/database.mysql-common.inc Drop a table.
db_drop_unique_key includes/database.pgsql.inc Drop a unique key.
db_drop_unique_key includes/database.mysql-common.inc Drop a unique key.
db_field_names includes/database.inc Return an array of field names from an array of key/index column specifiers.
db_field_set_default includes/database.pgsql.inc Set the default value for a field.
db_field_set_default includes/database.mysql-common.inc Set the default value for a field.
db_field_set_no_default includes/database.pgsql.inc Set a field to have no default value.
db_field_set_no_default includes/database.mysql-common.inc Set a field to have no default value.
db_last_insert_id includes/database.mysql-common.inc Returns the last insert id.
db_rename_table includes/database.pgsql.inc Rename a table.
db_rename_table includes/database.mysql-common.inc Rename a table.
db_type_map includes/database.pgsql.inc This maps a generic data type in combination with its data size to the engine-specific data type.
db_type_map includes/database.mysql-common.inc This maps a generic data type in combination with its data size to the engine-specific data type.
db_type_placeholder includes/database.inc Given a Schema API field type, return the correct %-placeholder.
drupal_get_schema includes/common.inc Get the schema definition of a table, or the whole database schema.
drupal_get_schema_unprocessed includes/common.inc Returns the unprocessed and unaltered version of a module's schema.
drupal_install_schema includes/common.inc Create all tables that a module defines in its hook_schema().
drupal_schema_fields_sql includes/common.inc Retrieve a list of fields from a table schema. The list is suitable for use in a SQL query.
drupal_uninstall_schema includes/common.inc Remove all tables that a module defines in its hook_schema().
drupal_write_record includes/common.inc Save a record to the database based upon the schema.
_db_create_field_sql includes/database.pgsql.inc Create an SQL string for a field to be used in table creation or alteration.
_db_create_field_sql includes/database.mysql-common.inc Create an SQL string for a field to be used in table creation or alteration.
_db_create_index_sql includes/database.pgsql.inc
_db_create_keys includes/database.pgsql.inc
_db_create_keys_sql includes/database.mysql-common.inc
_db_create_key_sql includes/database.pgsql.inc
_db_create_key_sql includes/database.mysql-common.inc
_db_process_field includes/database.pgsql.inc Set database-engine specific properties for a field.
_db_process_field includes/database.mysql-common.inc Set database-engine specific properties for a field.
_drupal_initialize_schema includes/common.inc Fill in required default values for table definitions returned by hook_schema().

File

includes/database.inc, line 430
Wrapper for database interface code.

Comments

It seems that the $schema is constructed by reading all the schema hooks (and schema_alter hooks). If your module edits the database structure, using, say, db_change_field, the schema is not aware of this. The schema only tells you how the database was installed, not how it is.

Where we can find a better detailed documentation on database indexes ?

"Each specification is an array of one or more key column specifiers (see below) that form an index on the table." is very confusing to me, see below is only one specific example and not the full possibilities.

This documentation should include a link to the full information on available data types and sizes.

It looks like we do not need to run drupal_install_schema in hook_install in D7 if we just defined hook_schema.

Auto increment field using 'serial' needs to be defined as a key for the schema to be created.

For example,

$schema['node'] = array(
  'fields' => array(//Defining a auto increment field of nid.
    'nid'=> array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE)
  ),
  //Needs to be accompanied by
  'primary key' => array('nid'),
);


I was struggling with creating the table because I had missed defining it as key.
Hope this helps someone.

The key 'mysql_suffix' is missing in the documentation.