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