If you develop modules in Drupal you know what I am talking about here. However for the uninitiated, this might be useful.

Drupal provides an abstraction on the database and whether you are using mysql or postgres, you write only one code and call drupal's in built functions. 

The DB layer takes care of 

1. Creating and deleting the tables when a module is installed or uninstalled.

2. Updating the table structure once the module is in use. So the users can apply a patch easily

3. Provides ways to write secure database calls that can eliminate SQL injection

4. Reading from the database and iterating over results

5. It even allows for reading only a certain number of records where the query can specify start and end result number.

6. Making table names unique in a shared hosting environment

In this blog we will only cover 1 and 2. Part II covers 3, 4

Of course there is a lot more, but these are the most commonly encountered actions and we will only cover these. Feel free to comment about your favorite database features that Drupal supports.

Let's assume that your module name is Molecule.

1. Creating and deleting table when a module is installed or uninstalled:

In the molecule.install file, you will use the schema hook (molecule_schema()) to create the required tables. 

This is how you would create a molecule table with a column molecule_name

$schema['molecule'] = array(

  'description' => t('Contain Molecule information'),

  'fields' => array(

    'molecule_id' => array(

      'description' => t('id of the stalker - viewing'),

      'type' => 'int',

      'unsigned' => TRUE,

      'not null' => TRUE,

      'default' => 0,

    ),

  ),

);

To have the table and column created, you would implement the install hook (molecule_install). This will be called when the module is enabled.

function molecule_install(){

  if(!db_table_exists('molecule')){

   drupal_install_schema('molecule');

 }

}

To uninstall the module when the module is deleted, implement the hook molecule_uninstall()

function molecule_uninstall(){

  drupal_uninstall_schema('molecule');

}

2. Updating the table structure once the module is in use. So the users can apply a patch easily:

Let's say after shipping the module, you implemented additional functionality and did 2 things - added a new column and modified the name of and existing column. Drupal provides a very convenient hook to write updates to your existing module. This is exposed when the the user runs update.php.

The code to add and update columns is below. We are adding a column scientific_name and changing the molecule_id to just id.

function molecule_update_6001(){

  $ret = array();

  $spec = array(

      'description' => t('Scientific name of the molecule'),

      'type' => 'int',

      'unsigned' => TRUE,

      'not null' => TRUE,

      'default' => 0,

    );

    $spec2 = array(

      'description' => t('Id'),

      'type' => 'int',

      'unsigned' => TRUE,

      'not null' => TRUE,

      'default' => 0,

    );

  db_add_field($ret, 'molecule', 'scientific_name', $spec);

  db_change_field($ret, 'molecule', 'molecule_id', 'id', $spec2);

  return $ret;

}

When the user now runs the update.php, he will see 6001 as the update for the molecule module. He can apply the updates and the changes to the database table are made.

In part II we will cover some more details.