Schema API

API to handle database schemas.

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 in non-markup plain text describing this table and its purpose. References to other tables should be enclosed in curly brackets.
• '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 users_data table 'uid' field description might contain "The {users}.uid this record affects."
  • 'type': The generic datatype: 'char', 'varchar', 'text', 'blob', 'int', 'float', 'numeric', or 'serial'. Most types just map to the according database engine specific data types. Use 'serial' for auto incrementing fields. This will expand to 'INT auto_increment' on MySQL. A special 'varchar_ascii' type is also available for limiting machine name field to US ASCII characters.
  • 'mysql_type', 'pgsql_type', 'sqlite_type', etc.: If you need to use a record type not included in the officially supported list of types above, you can specify a type for each database backend. In this case, you can leave out the type parameter, but be advised that your schema will fail to load on backends that do not have a type specified. A possible solution can be to use the "text" type as a fallback.
  • '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 data types 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 Drupal.
• '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, this is the schema definition for the 'users_data' table. It shows five fields ('uid', 'module', 'name', 'value', and 'serialized'), the primary key (on the 'uid', 'module', and 'name' fields), and two indexes (the 'module' index on the 'module' field and the 'name' index on the 'name' field).

$schema['users_data'] = [
    'description' => 'Stores module data as key/value pairs per user.',
    'fields' => [
        'uid' => [
            'description' => 'The {users}.uid this record affects.',
            'type' => 'int',
            'unsigned' => TRUE,
            'not null' => TRUE,
            'default' => 0,
        ],
        'module' => [
            'description' => 'The name of the module declaring the variable.',
            'type' => 'varchar_ascii',
            'length' => DRUPAL_EXTENSION_NAME_MAX_LENGTH,
            'not null' => TRUE,
            'default' => '',
        ],
        'name' => [
            'description' => 'The identifier of the data.',
            'type' => 'varchar_ascii',
            'length' => 128,
            'not null' => TRUE,
            'default' => '',
        ],
        'value' => [
            'description' => 'The value.',
            'type' => 'blob',
            'not null' => FALSE,
            'size' => 'big',
        ],
        'serialized' => [
            'description' => 'Whether value is serialized.',
            'type' => 'int',
            'size' => 'tiny',
            'unsigned' => TRUE,
            'default' => 0,
        ],
    ],
    'primary key' => [
        'uid',
        'module',
        'name',
    ],
    'indexes' => [
        'module' => [
            'module',
        ],
        'name' => [
            'name',
        ],
    ],
    // For documentation purposes only; foreign keys are not created in the
    // database.
'foreign keys' => [
        'data_user' => [
            'table' => 'users',
            'columns' => [
                'uid' => 'uid',
            ],
        ],
    ],
];

See also

\Drupal\Core\Extension\ModuleInstaller::installSchema()

\Drupal\Core\Extension\ModuleInstaller::uninstallSchema()

\Drupal\TestTools\Extension\SchemaInspector::getTablesSpecification()

File

core/lib/Drupal/Core/Database/database.api.php, line 250

Functions

Constants

Classes