Skill level: Intermediate.Knowledge & Requirements: 

• Drupal 7 site
• Creating a custom module
• phpMyAdmin - Know how to create a table using phpMyAdmin

Efficiency has always been a key part when it comes to developing any type of program. The Schema module is a Drupal module that helps a developer be more efficient and helps cut down time when creating a hook_schema() function. In Drupal 7, hook_schema() is called at both install and uninstall time, which helps create/delete a custom table in the database. This function lives inside the module’s .install file. Click here to see what a hook_schema() looks like. Creating a hook_schema() manually is time consuming and more likely to have errors when creating the array, whereas using the Schema module’s structure generation is quick and accurate. Some key features of the Schema module are providing API functions, schema structure generation, and schema comparison.

We’ll be using schema’s structure generation to create a custom module’s hook_schema().  Click here to see an example of a hook_schema() for Drupal 7. First step is to create a custom table. Go ahead and create a custom table using phpMyAdmin. My example, I created a table called “d7_example_table” with two fields in my Drupal website.

(phpMyAdmin 3.3.9.2 – Table “d7_example_table” with fields pid and name.)

After the table is created, download and enable the Schema module. Once enabled, navigate to Structure > Schema > Inspect (/admin/structure/schema/inspect). This page will show the schemas for all available tables in the database. For my example, there’s one unknown table that's not associated with any module because I created the database table with phpMyAdmin.

(This screenshot shows the unknown table that Schema has found)

Now it’s time to create the module’s .install file. This part is simple. We’ll be copying the array that has been generated and pasting it into the module's hook_schema() function in the .install file.

$schema['d7_example_table'] = array(  'description' => 'TODO: please describe this table!',  'fields' => array(    'pid' => array(      'description' => 'Primary ID',      'type' => 'serial',      'not null' => TRUE,    ),    'name' => array(      'description' => 'D7 Example Table Name',      'type' => 'text',      'not null' => TRUE,    ),  ),  'primary key' => array('pid'),);

(My table is called d7_example_table. Notice the Schema module even adds "TODO:" I will replace the TODO: with a comment before saving that to my module’s .install file.)

Now my .install file will look like the following. Please note that you will need to add the "return $schema" before the end of the function.

<?php

/** * Implements hook_schema(). */

function d7_example_schema() {  $schema['d7_example_table'] = array(    'description' => 'D7 Example Table',    'fields' => array(      'pid' => array(        'description' => 'Primary ID',        'type' => 'serial',        'not null' => TRUE,      ),      'name' => array(        'description' => 'D7 Example Table Name',        'type' => 'text',        'not null' => TRUE,      ),    ),    'primary key' => array('pid'),  );  return $schema;}

At this point, the last thing to do is turn on the custom module. Navigate to Module and enable your custom module. After the module has been enabled, verify that the schema has been installed by visiting Structure > Schema > Inspect (/admin/structure/schema/inspect). You should now see the name of your module with the schema. (Please note that if you disable, uninstall, and re-enable the module, the table will be dropped and then re-created. This will delete all rows in the table.)

Another feature that I like is Schema's "Describe" tab which allows you to see the table's field in an easy to read table. This table displays the field's name, type, null property, and default value. This can be found by going to Structure > Schema > Describe (admin/structure/schema/describe).

Finally, imagine a database table with multiple fields or multiple tables that you would need to manually create. It would take a lot of time to do that! Creating a hook_schema() with the Schema module is quick and easy, especially when you are creating hook_schema() with multiple tables. Using the Schema module will help you save you time when it comes to creating custom module tables.

Final Code from my example module:

d7_example.info

name = Drupal 7 Exampledescription = "Drupal 7 Example Module"package = D7core = 7.x

d7_example.module

d7_example.install

<?php

/** * Implements hook_schema(). */

function d7_example_schema() {  $schema['d7_example_table'] = array(    'description' => 'D7 Example Table',    'fields' => array(      'pid' => array(        'description' => 'Primary ID',        'type' => 'serial',        'not null' => TRUE,      ),      'name' => array(        'description' => 'D7 Example Table Name',        'type' => 'text',        'not null' => TRUE,      ),    ),    'primary key' => array('pid'),  );  return $schema;}

Tags: Planet Drupal , Schema , Drupal