Last updated April 9, 2012. Created by neochief on May 25, 2007.
Edited by smokris, drupalshrek, mbrett5062, willmoy. Log in to edit this page.
A sample schema data structure
As an example, here is an excerpt of the schema definition for Drupal's 'node' table:
<?php
$schema['node'] = array(
'description' => 'The base table for nodes.',
'fields' => array(
'nid' => array(
'description' => 'The primary identifier for a node.',
'type' => 'serial',
'unsigned' => TRUE,
'not null' => TRUE),
'vid' => array(
'description' => 'The current {node_revisions}.vid version identifier.',
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0),
'type' => array(
'description' => 'The {node_type} of this node.',
'type' => 'varchar',
'length' => 32,
'not null' => TRUE,
'default' => ''),
'title' => array(
'description' => 'The title of this node, always treated a non-markup plain text.',
'type' => 'varchar',
'length' => 255,
'not null' => TRUE,
'default' => ''),
'created' => array(
'description' => 'The Unix timestamp when the node was created.',
'type' => 'int',
'not null' => TRUE,
'default' => 0),
'changed' => array(
'description' => 'The Unix timestamp when the node was most recently saved.',
'type' => 'int',
'not null' => TRUE,
'default' => 0),
),
'indexes' => array(
'node_changed' => array('changed'),
'node_created' => array('created'),
),
'unique keys' => array(
'nid_vid' => array('nid', 'vid'),
'vid' => array('vid')
),
'primary key' => array('nid'),
);
?>In this excerpt, the table 'node' has six fields (table columns) named 'nid', 'vid', 'type', 'title', 'created' and 'changed'. Each field specifies its type ('serial', 'int', or 'varchar' in this example) and some additional optional parameters, including a description.
The table's primary key is the single field 'nid'. There are two unique keys: one named 'vid' on the field 'vid' and another called 'nid_vid' on fields 'nid' and 'vid'. And there are two indexes, one named 'node_changed' on field 'changed' and one named 'node_created' on the field 'created'.
Creating tables: hook_schema and .install files
For the Schema API to manage a module's tables, the module must have a .install file that implements hook_schema(). For example, mymodule's mymodule.install file might contain:
<?php
/**
* Implements hook_schema().
*/
function mymodule_schema() {
$schema['mytable1'] = array(
// specification for mytable1
);
$schema['mytable2'] = array(
// specification for mytable2
);
return $schema;
}
?>In Drupal 6.x, the module also must explicitly call drupal_install_schema() and drupal_uninstall_schema(), as follows:
<?php
/**
* Implements hook_install().
*/
function mymodule_install() {
drupal_install_schema('mymodule');
}
/**
* Implements hook_uninstall().
*/
function mymodule_uninstall() {
drupal_uninstall_schema('mymodule');
}
?>In Drupal 7.x, this is no longer necessary — those functions are called automatically.
Making sure your schema gets updated
If you release a new version of your module and it needs to have a new database schema, you need to do two things to make this change work. First, be sure to update the schema structure in mymodule_schema() so that new installations of your module get the new database table structure. Second, use a hook_update_N() function, as in previous versions of Drupal, to make sure that existing installations of your module can be updated to your new version. For example, suppose you add a new column called 'newcol' to table 'mytable1' in your module. You would need to add an update function to mymodule.install:
<?php
function mymodule_update_6100() {
$ret = array();
db_add_field($ret, 'mytable1', 'newcol', array('type' => 'int'));
return $ret;
}
?>Shortcut: Use the contributed schema.module to create an entire hook_schema() for you.
If you have an existing module that needs to begin making use of this new schema API structure, you do not have to manually create the schema definitions for your .install file. If you have the Schema module, it will do it for you. With schema.module installed, visit Administer > Site building > Schema, then the "Inspect" tab.
On the Inspect tab, the Schema module generates and displays schema data structures for all tables in the database. Tables that are owned by a module are listed under that module name. Other tables are listed under the name "Unknown." Since your module's tables are not yet defined by a hook_schema, they will be in the Unknown group. Just copy and paste the schema structure for each of your tables into your module's hook_schema function in the .install file and remember to return $schema; at the end.
Comments
Schema module requires MySQL 5.02+
You cannot use schema module to generate reports on MySQL < 5.02. The module requires features only available for MySQL 5.
--
http://ken.blufftontoday.com/
http://new.savannahnow.com/user/2
Search first, ask good questions later.
--
http://ken.therickards.com
Note, if you're upgrading
Note, if you're upgrading drupal 5 modules, you must first define skeleton table schemas for each module you want details for.
Eg: -- workflow_ng/states/states.install:
<?phpfunction states_schema() {
$schema['node_state'] = array();
$schema['users_state'] = array();
$schema['states_custom'] = array();
return $schema;
?>
Don't forget the return! This will give you all the fields that you might have previously not been able to see.
Reference: http://drupal.org/node/223952
You can also just comment
You can also just comment out the following lines from the "schema_mysql_inspect()" function found in the file "engines/schema_mysql.inc" (for mysql at least):
<?php
// Ignore tables not in Drupal's schema.
if (!isset($schema[$r['TABLE_NAME']])) {
continue;
}
?>
That way you can still get the schema definitions without modifying your modules. This is for the Drupal 5 version of the Schema module.
What Drupal data types are supported?
I just want a list of the supported Drupal data types in an .install file. Where can
I find this?
Thanks,
Andy
Never mind ... I found it in
Never mind ... I found it in http://api.drupal.org/api/group/schemaapi/6 , in case anyone else needs it.
Sorry for missing it originally.
'created' and 'changed' fields not in example code
Although the example is an excerpt from the actual 'node' table schema, it might be useful for the consistency of the reference to include the fields 'changed' and 'created' in the code so as to not be confused about the 'indexes' section of the table definition. I know it states "And there are two indexes, one named 'node_changed' on field 'changed' and one named 'node_created' on the field 'created'" but I completely missed that the first time around :P.
Example above with added fields from node.install:
<?php$schema['node'] = array(
'description' => 'The base table for nodes.',
'fields' => array(
'nid' => array(
'description' => 'The primary identifier for a node.',
'type' => 'serial',
'unsigned' => TRUE,
'not null' => TRUE),
'vid' => array(
'description' => 'The current {node_revisions}.vid version identifier.',
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0),
'type' => array(
'description' => 'The {node_type} of this node.',
'type' => 'varchar',
'length' => 32,
'not null' => TRUE,
'default' => ''),
'title' => array(
'description' => 'The title of this node, always treated a non-markup plain text.',
'type' => 'varchar',
'length' => 255,
'not null' => TRUE,
'default' => ''),
'created' => array(
'description' => 'The Unix timestamp when the node was created.',
'type' => 'int',
'not null' => TRUE,
'default' => 0),
'changed' => array(
'description' => 'The Unix timestamp when the node was most recently saved.',
'type' => 'int',
'not null' => TRUE,
'default' => 0),
),
'indexes' => array(
'node_changed' => array('changed'),
'node_created' => array('created'),
),
'unique keys' => array(
'nid_vid' => array('nid', 'vid'),
'vid' => array('vid')
),
'primary key' => array('nid'),
);
?>
You can use logic to justify anything. That is its power. and its flaw.
AI
How do I define Auto increment?
Search this page
Search this page http://drupal.org/node/146939 for the word "serial"
Sometimes something interesting appears on http://litwol.com
yes
I reffered a core module and found that we could acheive this just by declaring the col as primary key.
Thanks ny ways
defining Auto increment
field to be defined as auto increment is given as array for that field ('type' => 'serial',) inside hook_schema
enum
and how to define enum ?
TINYINT(1)
Hello, I need to resolve this:
By using standard MySql command line, the boolean type is TINYINT(1). How can I define this type within hook_schema, because the following does not work for me:
<?php'active' => array(
'type' => 'int',
'length' => 1,
'not null' => true,
'size' => 'tiny',
'default' => 0,
'description' => t("Active or not"),
),
?>
My "active" atrribute written in this way is always TINYINT(4), and I just need TINYINT(1). Is it possible to define such an attribute in hook_schema?
Thank you for any response,
Regards.
Defining Boolean Column
TINYINT is always one byte - the number in parenthesis is only the display width and has no affect on the data that is stored (e.g., you can still store '123' in a TINYINT(1) field). As mentioned in the Schema API doc, the 'length' property is only used for character data fields (char, varchar, text), and it is ignored for numeric types. (It's also a non-standard MySQL extension for integer fields to have a display-width)
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
After 5.0.3 (for MyISAM) and 5.0.5 (for InnoDB and others), a BIT datatype is supported, which previously was translated to TINYINT(1), however it is not supported by Schema API.
http://dev.mysql.com/doc/refman/5.0/en/bit-field-values.html
In summary: don't worry about the length of 4 instead of 1.
Thx
@gapple
Thank you a lot, that was really exhaustive explanation and made everything clear to me.
To 't' or not to 't'?
Based on looking at the install file for other modules (e.g. Views) and the excerpt provided above, I do not think you want to use the 't' function in your schema array.
Based on Pro Drupal Development (2nd Edition), the 't' function is used there.
When should we use the 't' function in our schema array?
create table in a database different from drupal's database
Hi,
I'm working on a project and I want to create a module which allow users to upload a csv file from my drupal site. But here is the issue: I'd like the data contains in the file to be directly inserted into my project database. Is it possible?
The two databases (for drupal and my project) are stores in postgreSQL 8.4 and I already written the db_url[] line into settings.php.
If somebody can help me i would be very grateful!!
Mathilde
resolved
I found a solution by myself: I wrote all I needed with PHP into a submit function.
Mathilde
Schema module was just what I needed
I'm a Java developer that is trying to branch into web page design, and well, PHP arrays give me migraines (my PHP-fu is weak). I'm much more fluent with SQL. I could define my tables in a fraction of the time I would have needed, using direct SQL statements, and then use the Schema module to generate my code for the hook_schema.
I, however, did the exact thing the article warmed me against, I forgot the return $schema at the end, and spent 15 minutes wondering what was wrong. Remember, you return $schema!
How to set the index length in the Drupal Database Schema
Check here an article about to set the index length using the drupal's database schema.
http://nestor.profesional.co.cr/2010/06/04/how-set-index-length-drupal-d...
In spanish:
Lea este articulo sobre como definir el tamaño de un indice utilizando el database schema de drupal.
http://nestor.profesional.co.cr/es/2010/06/04/como-definir-tama-o-un-ind...
~ Nestor
trying to create hook.install from this example
A newbie question for sure. I'm trying to create my first module, and I figured I'd use this quickstart example, and switch the name to the table to my own (THINK).
I'm sure it is obvious to an experience drupaler that this would fail, but I'm stumped, (I'd live it if this 'qwick start' doc supplied an example that devs like me could actually plug in and make work, or at least information on how to alter the supplied code in order to work).
Anyway, I'm attaching my hook.install code and the error. Any help would be greatly appreciated!
<?php
function think_schema() {
$schema = array();
$schema['THINK'] = array(
'description' => 'The base table for nodes.',
'fields' => array(
'nid' => array(
'description' => 'The primary identifier for a node.',
'type' => 'serial',
'unsigned' => TRUE,
'not null' => TRUE),
'vid' => array(
'description' => 'The current {node_revisions}.vid version identifier.',
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0),
'type' => array(
'description' => 'The {node_type} of this node.',
'type' => 'varchar',
'length' => 32,
'not null' => TRUE,
'default' => ''),
'title' => array(
'description' => 'The title of this node, always treated a non-markup plain text.',
'type' => 'varchar',
'length' => 255,
'not null' => TRUE,
'default' => ''),
),
'indexes' => array(
'node_changed' => array('changed'),
'node_created' => array('created'),
),
'unique keys' => array(
'nid_vid' => array('nid', 'vid'),
'vid' => array('vid')
),
'primary key' => array('nid'),
);
return $schema;
}
function think_install() {
// Create my tables.
drupal_install_schema('think');
}
function think_uninstall() {
// Drop my tables.
drupal_uninstall_schema('think');
}
?>
user warning: Key column 'changed' doesn't exist in table query: CREATE TABLE THINK ( `nid` INT unsigned NOT NULL auto_increment, `vid` INT unsigned NOT NULL DEFAULT 0, `type` VARCHAR(32) NOT NULL DEFAULT '', `title` VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (nid), UNIQUE KEY nid_vid (nid, vid), UNIQUE KEY vid (vid), INDEX node_changed (changed), INDEX node_created (created) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in /home/content/d/p/o/dpouliot/html/drupal6/includes/database.inc on line 555.Some suggestions from another newbie that may help
First off, you may try changing table name to all lower letters, this seems to be the norm, though I am not sure if it is a rule.
Your main problem was highlighted by a previous comment.
The example you used only took an extract of the fields from the {node} table, and did not include the fields for 'changed' and 'created'.
The documentation then shows indexes on these fields.
If you look at the later comment it shows what the extracted code should be for the indexes to work.
This should solve your problem.
What you are in effect doing with your indexes is to tell the database that your table uses certain fields in that table as an index. So fields not defined cannot be used.
Hope that helps. Good luck.