Use the Drupal Schema API

Located in:

Drupal
Tech

So, you are using Drupal, you've probably read a bunch of articles and tutorials...maybe even skimmed some books and you figure you have a grasp of how it works. You've read that there's the "drupal way" of doing things and you'd be happy to do so, but you're not sure what that means in practice and you're in a rush to get your site working. You know php and mysql so what's the problem? You just want to start coding.

You plan a module that requires its own table to store data. (apologies to those who do everything with cck and nodes) No problem, you just fire up phpMyAdmin and create a table directly in your drupal DB and start coding your module to insert/update/delete stuff in that table.

Stop right there!

If you're going through the trouble of building a module, then take a few extra minutes and use an install (ex. module_name.install) file to build that table. As with a lot of drupal things, the .install file consists of arrays of arrays but don't worry, you don't need to hand-code all this stuff. Use the drupal schema module which can analyse tables and build the lengthy table code for you.

This tutorial's assumptions about you

We're talking about Drupal 6 here
You know how to install and uninstall drupal modules
You can find, access and edit the necessary folders and files for your drupal installation's modules (ie. /sites/all/modules...)
You are somewhat familiar with the anatomy of drupal modules, specifically that they require an .info file and a .module file

How do I analyse a non-existent table?

So yes, the paradox of the schema module is that it analyses existing tables. Therefore, the table you need to analyse already needs to be built! (chicken, meet egg) In essence you'll probably be using phpMyAdmin to build that table as a first step. However, the additional step of then using the schema module to build the code for an .install file makes drupal aware that the table exists and then you can use drupal's Schema API for inserts and updates. That's the real bonus. The extra time and hassle at the beginning makes your subsequent coding much easier.

Using .install with your module

Let's say my site has info about lots of countries and I am building a 'countries' module to handle this info. I have decided to store these countries in a table with their name, capital city, population etc.

First of all, I must have the drupal schema module installed.

I create a table called 'countries' in the same db that has all my drupal tables. I typically use phpMyAdmin and it creates the table with this code:

CREATE TABLE `countries` (
`country_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR( 255 ) ,
`capital` VARCHAR( 255 ) ,
`population` INT,
`area` INT,
 PRIMARY KEY ( `country_id` )
) TYPE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

With the 'countries' table added to my db I can switch to my drupal admin environment and navigate to admin -> schema -> inspect page (?q=admin/build/schema/inspect). The schema module will automatically create the table-creating code for my .install file.

$schema['countries'] = array(
  'description' => t('TODO: please describe this table!'),
  'fields' => array(
    'country_id' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'serial',
      'not null' => TRUE,
    ),
    'name' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'varchar',
      'length' => '255',
      'not null' => FALSE,
    ),
    'capital' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'varchar',
      'length' => '255',
      'not null' => FALSE,
    ),
    'population' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'int',
      'not null' => FALSE,
    ),
    'area' => array(
      'description' => t('TODO: please describe this field!'),
      'type' => 'int',
      'not null' => FALSE,
    ),
  ),
  'primary key' => array('country_id'),
);

Notice the 'TODO: please describe this field!' strings. These are reminders that you should be thorough and describe each field.

Using your favourite text editor, create an .install file that will be saved in your module's folder. It should have at least 3 functions: hook_install, hook_uninstall and the table describing hook_schema, which is what the Schema module spits out. It should look something like this:

<?php

/**
* Implementation of hook_install().
*/
function countries_install() {
	// Use schema API to create database table.
	drupal_install_schema('countries');
}



/**
* Implementation of hook_uninstall().
*/
function countries_uninstall() {
	// Use schema API to delete database table.
	drupal_uninstall_schema('countries');
}


/**
* Implementation of hook_schema().
*/
function countries_schema() {

	$schema['countries'] = array(
	  'description' => t('General country info'),
	  'fields' => array(
	    'country_id' => array(
	      'description' => t('Unique ID for each country.'),
	      'type' => 'serial',
	      'not null' => TRUE,
	    ),
	    'name' => array(
	      'description' => t('Country name'),
	      'type' => 'varchar',
	      'length' => '255',
	      'not null' => FALSE,
	    ),
	    'capital' => array(
	      'description' => t('Capital city'),
	      'type' => 'varchar',
	      'length' => '255',
	      'not null' => FALSE,
	    ),
	    'population' => array(
	      'description' => t('Country population'),
	      'type' => 'int',
	      'not null' => FALSE,
	    ),
	    'area' => array(
	      'description' => t('Area of country in square kilometres'),
	      'type' => 'int',
	      'not null' => FALSE,
	    ),
	  ),
	  'primary key' => array('country_id'),
	);

	return $schema;

}

(NB: Remember to include the line: 'return $schema'. We're dealing with a function and it has to return something!)

Now that the .install file is ready to go, I drop (delete) the countries table from the db so that I can let drupal rebuild it using the .install file. (NB: This process works best if the module is not yet installed. Drupal knows about all installed modules and if you already have it installed and then try to implement an .install file later, Drupal may just ignore your valiant .install efforts. Do not fear! This just means you need to uninstall all traces of the module first before freshly re-installing it.)
Load drupal's module page (?q=admin/build/modules) and install the 'countries' module.

If everything went as planned, then your 'countries' table has magically appeared in the database. Importantly, now drupal is aware of this table and you can manipulate its data using drupal's Schema API instead of coding lots of INSERT INTO countries... and UPDATE countries SET name = ... statements in your module.

All you need to do to insert a record is something like this:

$table = 'countries';
$record = new stdClass();
$record->name = 'Estonia';
$record->capital = 'Tallinn';
$record->population = 140000;
$record->area = 45228;
drupal_write_record($table, $record);

And if you need to do an update instead, it's pretty much the same thing, except you pass in the primary key's field name as an extra parameter.

For instance, I accidentally put Estonia's population as 140,000 instead of 1.4 million. So, assuming Estonia's ID in the database table is 3, let's correct the population.

$table = 'countries';
$record = new stdClass();
$record->population = 1400000;
// Assuming the id to be updated is 3
$record->country_id = 3;

// For update, pass in name of table's primary key field
drupal_write_record($table, $record, 'country_id');

So there you have it. It's maybe not what you're used to, but updates and inserts using the $record object are simple and they have their advantages. (as I found out the hard way trying to insert JSON notation into fields using Drupal's db_query() function with the traditional INSERT INTO syntax - Drupal's db_query() function with INSERT INTO... cleaned the input in a way that wrecked the correct JSON syntax - but that's not a problem using the schema api)

NB: If you are working at this and installing and uninstalling your module repeatedly, you'll likely have to manually delete your module's entry from the system table for each uninstall to make Drupal truly forget that the module (you are trying to re-install) has been uninstalled. (to find the record for deletion, do something like SELECT * FROM system WHERE name like 'name_of_your_module'; and delete that record.