Advertisement
Plugins

Custom Database Tables: Maintaining the Database

by

Over the lifetime of your custom table you will probably find that you need to make changes to what it stores, or how it stores it. This may be in response to a need to store more (or less) data. It may be that the initial design of your database wasn’t intended to deal (efficiently) with what your user-base is now demanding. Either way, we need to know how to adapt our table to meet our new needs. This is what we’ll be looking at in this tutorial, and we’ll primarily focused on the function dbDelta() that we first met in part one.


dbDeta

Fortunately most of the legwork in handling database changes is done by the WordPress function dbDelta(). We used this function in part one to create our table, but it actually does a lot more than that: prior to performing the query we gave it, it checks if the table already exists. If not, it creates the table, but if it does exist – it compares the difference (hence the name) and makes some changes. This is why in part one we didn’t manually check if the table already existed.

If the table already exists, but is different to the table given by the SQL (for example the existing table has a missing column, or a different column collation), then dbDelta() automatically applies these updates. In this way we can release a new version of our plug-in which alters our table by simply applying `dbDelta()` with the altered SQL. Almost.

Unfortunately, dbDelta() doesn’t apply all changes. Lets suppose that in our latest plug-in release we have no need for a column, and we want to remove it. So we remove it from the SQL query in part one, and in the upgrade routine call wptuts_create_tables(). After upgrading we’ll find that the column is still there. Worse than that: users upgrading from the old version to the new version will then have a structurally different table than those who start with the new version.

Note: dbDelta() is not destructive: that is it will add missing columns, or change altered columns, but it will not remove columns or indexes.

So what does dbDelta() actually do?

Let’s remind ourselves of the SQL query we pass to dbDelta() when creating the table:

$sql_create_table = "CREATE TABLE ". $wpdb->wptuts_activity_log." (
          log_id bigint(20) unsigned NOT NULL auto_increment,
          user_id bigint(20) unsigned NOT NULL default '0',
          activity varchar(20) NOT NULL default 'updated',
          object_id bigint(20) unsigned NOT NULL default '0',
          object_type varchar(20) NOT NULL default 'post',
          activity_date datetime NOT NULL default '0000-00-00 00:00:00',
          PRIMARY KEY  (log_id),
          KEY user_id (user_id)
     ) $charset_collate; ";
dbDelta($sql_create_table);

First it extracts all the CREATE TABLE queries (you can pass multiple queries to dbDelta() at once, separating them by a ';', but to improve readability I prefer not to). From this it takes the table name, $table, and runs

 $wpdb->get_results("DESCRIBE {$table};");

This returns an array of existing columns – each column is actually an object containing information pertaining to that column (its name, type, default value etc). For example our log_id column looks like:

stdClass Object (
            [Field] => log_id
            [Type] => bigint(20) unsigned
            [Null] => NO
            [Key] => PRI
            [Default] =>
            [Extra] => auto_increment
        )

If the table doesn’t exist, then an empty array is returned and the table is created. Otherwise dbDelta() then goes through each line of the passed query, extracts the columns and stores them in an array $cfields. It does likewise with each of the keys (including primary).

Next it goes through each of the existing columns. If they are present in the above array, $cfields, they are removed. It then compares their type, if they do not match, it automatically generates a corresponding ALTER TABLE query to be performed later. After its done this, the only columns left in $cfields are the ones that do not already exist. From this it generates further ALTER TABLE queries to create these columns.

It then performs an almost identical procedure for keys.


Be Careful

The ability of dbDelta() to do all this analysis comes at a cost: its fussiness of what it’ll accept (or correctly interpret). For instance:

  • Each part of the query (e.g. each column and key declaration) must have its own line. For example
     user_id bigint(20) unsigned NOT NULL default ’0′,activity varchar(20) NOT NULL default ‘updated’,

    will act as if the activity column is not present. The correct format is:

          user_id bigint(20) unsigned NOT NULL default '0',
          activity varchar(20) NOT NULL default 'updated',
  • You must use KEY rather than its synonym INDEX.
  • Any KEYs must be given a name. For example, do not write
    KEY (user_id)]

    instead it should be

    KEY user_id (user_id)

    (though the name does not have to be the same as the column).

  • The PRIMARY KEY should not be given a name, but rather there must be two spaces between PRIMARY KEY and the column declaration: (log_id). For example,
     PRIMARY KEY (log_id),

    will cause an error. The correct format is:

          PRIMARY KEY  (log_id),

This isn’t a complete list, as general rule you should avoid extra spaces around and between keywords, such as CREATE and TABLE and there should be no extra spaces around columns. The internals of dbDelta() rely on using preg_match() to extract information from the passed SQL statement – and as such things can go wrong fairly easily if that statement isn’t suitably formatted.

Some of these errors will occur silently (for instance if you do not give a KEY a name, dbDelta() will keep duplicating it). For this reason it’s important that you inspect your table manually (using phpMyAdmin or similar) to check that your code is functioning correctly.


Adding or Changing Columns

With dbDelta(), this is really simple – let’s suppose we want to make object_id an index, add an additional column user_ip to store the user’s IP address and change the type of the activity column to varchar(30), we simply replace the original SQL Query with:

$sql_create_table = "CREATE TABLE ". $wpdb->wptuts_activity_log." (
          log_id bigint(20) unsigned NOT NULL auto_increment,
          user_id bigint(20) unsigned NOT NULL default '0',
          user_ip varchar(15),
          activity varchar(30) NOT NULL default 'updated',
          object_id bigint(20) unsigned NOT NULL default '0',
          object_type varchar(20) NOT NULL default 'post',
          activity_date datetime NOT NULL default '0000-00-00 00:00:00',
          PRIMARY KEY  (log_id),
          KEY user_id (user_id),
          KEY object_id (object_id),
     ) $charset_collate; ";

Then we just ensure we call wptuts_create_tables() in the upgrade routine, and the changes will take effect.


Removing Columns

Since dbDelta() will not remove columns, simply removing the appropriate line from the query will not suffice (it is still necessary though). Instead we need to do things manually.

First, extract an array of existing colums:

 $existing_columns = $wpdb->get_col("DESC {$wpdb->wptuts_activity_log}", 0);

Then, if the columns we wish to remove are present, we can remove them with an ALTER TABLE query:

 $remove_columns = array('object_id' ); //Array of columns to remove
 $remove_columns = array_intersect($remove_columns, $existing_columns);

 if( !empty($remove_columns) )
     $wpdb->query("ALTER TABLE {$wpdb->wptuts_activity_log} DROP COLUMN ".implode(', DROP COLUMN ',$remove_columns).';');

Removing Keys

Just as we did with columns, first get an array of indexes:

$existing_keys = $wpdb->get_col("SHOW INDEX FROM {$wpdb->wptuts_activity_log} WHERE Key_name != 'PRIMARY';",2);

Then, if the keys we wish to remove exists, we can remove them just as above, but now using DROP INDEX

$remove_keys = array('user_id');//Array of keys to remove
$remove_keys = array_intersect($remove_keys, $existing_keys);

if( !empty($remove_keys) )
    $wpdb->query("ALTER TABLE {$wpdb->wptuts_activity_log} DROP INDEX ".implode(', DROP INDEX ',$remove_keys).';');

Upgrade Routine

Now that we know how to upgrade our database – lets look at how we should handle this in our plug-in. We’ll store all our upgrade handling inside the function: wptuts_activity_log_upgradecheck(). Note that the plug-in activation hook will not be triggered when updating a plug-in: in order to ensure our upgrade routine does its job, we’ll hook onto admin_init.

To check which upgrade routines we need to perform, we’ll store the plug-in version in the database. We’ll compare this version (the installed version) to the current (activated) version of the plug-in:

  • If there is no version in the database, it’s a fresh install and we’ll just add the current version
  • If there is a version in the database, and it’s the current version, we do nothing
  • Otherwise it’s an older version, so we’ll go through all the necessary upgrade routines.
add_action('admin_init', 'wptuts_activity_log_upgradecheck');
function wptuts_activity_log_upgradecheck(){
   //Version of currently activated plugin
   $current_version = '1.3';
   //Database version - this may need upgrading.
   $installed_version = get_option('wptuts_activity_log_version');

   if( !$installed_version ){
       //No installed version - we'll assume its just been freshly installed
       add_option('wptuts_activity_log_version', $current_version);

   }elseif( $installed_version != $current_version ){
         /* 
          * If this is an old version, perform some updates.
          */
   
         //Installed version is before 1.1 - upgrade to 1.1
         if( version_compare('1.1', $installed_version) ){
             //Code to upgrade to version 1.1
         }

         //Installed version is before 1.3 - upgrade to 1.3
         if( version_compare('1.3', $installed_version) ){
             //Code to upgrade to version 1.3
         }

         //Database is now up to date: update installed version to latest version
         update_option('wptuts_activity_log_version', $current_version);
   }
}

Note: It’s important that this upgrade routine is present in the initial release as it will add the initial version (1.0) to the database. Not doing so may cause problems for those upgrading from 1.0 to 1.1.

Each of the individual upgrade routines should should ensure the database is ‘up to date’ by using the code discussed in the earlier sections. Importantly, if we make any changes to the CREATE TABLE SQL, you must remember to run that query through dbDelta() (in our example, by calling wptuts_create_tables() as part of the upgrade routine) for the changes to take effect.

Be careful with how you handle updates when using dbDelta. Remember that some users might be upgrading across two or more updates. So if such changes cannot be made in parallel - then you'll need to upgrade in stages, calling `dbDelta()` several times, making the appropriate changes for that stage.


Uninstall Routine

While we’re at it, let’s look at cleaning up after ourselves when the plug-in uninstalled. These are generally very simple routines: just remove the database table, any options saved and any cron jobs your plug-in may have activated. We hook our routine onto the uninstall hook using register uninstall hook()

register uninstall hook(__FILE__,'wptuts_uninstall_plugin');
function wptuts_uninstall_plugin(){
    global $wpdb;
    //Remove our table (if it exists)
    $wpdb->query("DROP TABLE IF EXISTS $wpdb->wptuts_activity_log");

    //Remove the database version
    delete_option('wptuts_activity_log_version');

    /*Remove any other options your plug-in installed and clear any plug-in cron jobs */
}
Related Posts
  • Code
    Creative Coding
    Using WordPress for Web Application Development: Custom Database QueriesApplication foundation 400
    Throughout this series, we've been looking at the various facilities that make it possible to treat WordPress as a foundation for web application development. Thus far, we've covered a lot of ground: We've talked about how WordPress is more of a foundation rather than a framework. We've discussed the nature of the the Event-Driven Design Pattern. There's been a discussion of Email, User Management, Saving Data, Retrieving Data ...and more. In the most recent articles, we've been talking a look at how to handle queries against the WordPress database through the use of WP_Query and WP_User_Query.Read More…
  • Code
    Databases
    Mapping Relational Databases and SQL to MongoDBMongodb retina preview
    NoSQL databases have emerged tremendously in the last few years owing to their less constrained structure, scalable schema design, and faster access compared to traditional relational databases (RDBMS/SQL). MongoDB is an open source document-oriented NoSQL database which stores data in the form of JSON-like objects. It has emerged as one of the leading databases due to its dynamic schema, high scalability, optimal query performance, faster indexing and an active user community.Read More…
  • Code
    PHP
    Creating a Photo Tag Wall With Twilio Picture Messaging & PHPProcedural to oop php retina preview
    Twilio's recently announced Picture Messaging has vastly opened up what we can do with text messaging, now we can attach photos to our text messages and have them get used in different ways. In our case, we are going to build a Photo Tag Wall, which will contain photos linked to tags that will be displayed on a website.Read More…
  • Code
    Plugins
    Custom Database Tables: Creating an APICustomdbtables part3
    In the first part of this series we looked at the disadvantages of using a custom table. One of the major ones is the lack of an API: so in this article we'll look at how to create one. The API acts a layer between handling data in your plug-in and the actual interaction with the database table – and is primarily intended to ensure such interactions are safe and to provide a ‘human friendly’ wrapper for your table. As such we’ll require wrapper functions for inserting, updating, deleting and querying data.Read More…
  • Code
    Plugins
    Custom Database Tables: Safety FirstCustomdbtables part2
    This is part two of a series about custom database tables in WordPress. In part one we covered the reasons for, and against, using custom tables. We looked at some of the details that would need to be considered - column naming, column types - as well as how to create the table. Before we go any further we need to cover how to interact with this new table safely. In a previous article I covered general sanitisation and validation – in this tutorial we’ll look at this in more detail in the context of databases.Read More…
  • Code
    Plugins
    Custom Database Tables: Creating the TableCustomdbtables part1
    In this series we'll be looking at using custom database tables. We'll cover how to create, maintain and remove the table, as well as how to safely, and efficiently, add, remove and query data. In this first article we look at when custom tables might be appropriate, the pros and cons of using them and how to create the table.Read More…