Reference
Last modified: July 28, 2009 - 04:21
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. hook_schema() should return an array mapping 'tablename' => array(table definition) for each table that the module defines. The following keys in the table definition are processed during table creation:
- '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 array mapping 'fieldname' => array(field definition) 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'. The types map to the underlying database engine specific datatypes. Use 'serial' for auto incrementing fields.
- '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.). Data Types and Sizes are explained here.
- '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.
Note that type 'text' and 'blob' fields cannot have default values.
- 'length': The maximal length of a type 'varchar', 'text' or 'int' 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.
- 'serialize': A boolean indicating whether the field will be stored as a serialized string
All parameters apart from 'type' are optional except that type 'numeric' columns must specify 'precision' and 'scale', and 'varchar' columns must specify 'length'.
- 'primary key': An array of one or more key column specifers that form the primary key.
- A key column specifier is either a string naming a field or an array of two elements, a string naming a field and an integer prefix length. If a prefix length is specified, only that many bytes or characters of the named field are used as part of the key. If the database engine does not support this optimization, the prefix length is ignored.
- All fields listed in the primary key must have 'not null' => TRUE in their specification.
- 'unique keys': An associative array of unique keys ('keyname' => specification). Each specification is an array of one or more key column specifiers (see above) that form a unique key on the table.
- 'indexes': An associative array of indexes ('indexname' => specification). Each specification is an array of one or more key column specifiers (see above) that form an index on the table.
Note that type 'text' and
Note that type 'text' and 'blob' fields cannot have default values
Is this structurally true whatever the db type, or only for MySQL ?
Also, something like "the 'not null' value will be ignored for text and blob fields" would probably be clearer.
required parameters for field arrays
I might suggest that
should be amended to also note that type 'varchar' columns must specify 'length'.
No 'bool' type
Note that there is no generic datatype 'bool'.
Instead you have to use something like type 'int' with size 'tiny' (aka tinyint in MySQL).
Hmm...
The schema module generates a field called 'disp-width' but it is not documented here?
Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database
disp-width
"disp-width: The display width of non-varchar columns for the mysql command-line client. Only used by MySQL."
http://jaspan.com/schema-project-status-report
Guidlines on use of 'primary key' vs. 'unique key' vs. 'indexes'
In converting some modules to the Schema API I've noticed that developers tend to create indexes and primary keys on the same (sets of) columns. From the PostgreSQL documentation:
And the MySQL documentation:
Thus it seems that a primary key implies an index, and to also declare the index is redundant (though perhaps not harmful). Perhaps the documentation should reflect this... the result might be cleaner module code.
Consider this
Many, if not most, module maintainers are self-taught on PHP and and MySql, so any decent, low-level educational material would be really helpful across the board. Why not join the doc team and pitch in?
Slight correction for Drupal API documentation
It seems this reference information is the same as the one shown on the Schema API documentation page but there's a small issue that can cause some confusion:
In that page the keys of the associative array for 'fields' are shown as list items at the same level than 'fields', 'primary key' and all other first level keys in the array. A little of CSS love could fix that to show it as it's shown in this page and make the idea clearer.
Cheers.
Alexis Bellido
Ventanazul: web development and Internet business consulting shouldn't be boring
Can't use unique key on a text field
I've discovered that you can't use a unique key on a field that's 'type' => 'text' (at least I can't with MySQL), you have to use 'type' => 'varchar' instead, I think a length is also necessary (but haven't tested it so cannot confirm).
Working code
$schema['table_name'] = array('description' => t('Description of table'),
'fields' => array(
'id' => array(
'type' => 'serial',
'unsigned' => TRUE,
'not null' => TRUE,
),
'name' => array(
'type' => 'varchar',
'not null' => TRUE,
'length' => '50',
),
'unique keys' => array(
'name' => array('name')
),
'primary key' => array('id'),
);
You want to use VARCHAR over
You want to use VARCHAR over TEXT where possible anyway (on MySQL).
A VARCHAR can be up to 65,535 bytes long and does not incur a performance hit like TEXT (or BLOB) fields do if you include them in a query that makes use of a temporary table (eg: a group by clause). If you do that with BLOB or TEXT, MySQL will *always* use a disk based temporary table, which is *slow*.
Instead, you can use a large VARCHAR and for instance limit the index size instead. For most names an index size of 3 chars will do just fine.
$schema['table_name'] = array('description' => t('Description of table'),
'fields' => array(
'id' => array(
'type' => 'serial',
'unsigned' => TRUE,
'not null' => TRUE,
),
'name' => array(
'type' => 'varchar',
'not null' => TRUE,
'length' => '200',
),
'indexes' => array(
'name' => array('name', 3)
),
'primary key' => array('id'),
);