Here's a quick guide on how to create database schema for your module. The first thing you should do is get acquainted with the Schema API documentation.
You'll need to create a file in your module directory called: MYMODULE.install. In this file, you'll define hook_schema, hook_install, hook_uninstall, etc. Here's an example that defines 3 columns, the primary key, and a unique key.
<?php
function MYMODULE_schema() {
$schema = array();
$schema['MYTABLE'] = array(
'description' => t('MY TABLE DESCRIPTION),
'fields' => array(
'MYCOLUMN1' => array(
'description' => t('MY UNIQUE IDENTIFIER'),
'type' => 'serial',
'unsigned' => true,
'not null' => true,
),
'MYVARCHAR' => array(
'description' => t('MY VAR CHAR'),
'type' => 'varchar',
'length' => 32,
'not null' => true,
),
'MYTIMESTAMP' => array( // see: user install created
'description' => t('MY TIMESTAMP'),
'type' => 'int',
'not null' => true,
),
),
'indexes' => array(
'MYVARCHAR' => array('MYVARCHAR'),
),
'primary key' => array('MYCOLUMN1'),
'unique keys' => array(
'MYCOLUMN1' => array('MYCOLUMN1'),
),
);
return $schema;
}
?>A full list of data types can be found here.
This documentation explains the structure of the schema array.
Now, you can define how to install and uninstall the schema:
<?php
function MYMODULE_install() {
drupal_install_schema('MYMODULE');
}
function MYMODULE_uninstall() {
drupal_uninstall_schema('MYMODULE');
}
?>More information on schema functions can be found here.
Now, In your module code to write a database record you simply define an array/object containing your columns and the data you'd like to insert, and then use the function drupal_write_record:
<?php
$record = new StdClass();
$record->MYVARCHAR = 'BLAH';
$record->MYTIMESTAMP = strtotime('now');
drupal_write_record('MYTABLE', $record);
?>More information on writing to the database can be found here.
Probably worth adding some error checking
Hi. Handy blog post. However, it's probably worth adding some error checking into the install hook as an invalid schema can fail silently.
function MYMODULE_install() {
$results = drupal_install_schema('MYMODULE');
foreach ($results as $result) {
if (!$result['success']) {
drupal_set_message('Schema failed to install. Error in SQL: ' . $result['query'], 'error');
}
}
}
This still isn't ideal as it only alerts the user that an error has taken place and doesn't actually stop the module from installing.
Big help
The Schema module is essential for creating a schema definition: http://drupal.org/project/schema
Found answer...
I found answer here:
http://api.freestylesystems.co.uk/api/group/schemaapi/6
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'.
hook_schema: Defining Indexes with a not understood 'numeric'
First, I have reviewed the Schema API reference in Drupal.org site: http://drupal.org/node/146939
I have come across some code which is implementing hook_schema and in defining an index it did
array('column_name', numeric).What is this numeric value because it is not explained within the aforementioned online documentation?
Referenced example code using this numeric value within an index definition:
<?php
$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'),
// Here is the numeric value (4) in question defined within an index
'node_title_type' => array('title', array('type', 4)), // Here is the numeric value (4) in question.
),
);
?>
Do you know what this is?
Thanks