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 = (object) NULL;
$record->MYVARCHAR = 'BLAH';
$record->MYTIMESTAMP = strtotime('now');
drupal_write_record('MYTABLE', $record);
?>More information on writing to the database can be found here.
Comments
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