Part 2 of my originally-titled Wordpress Plugin Development series continues. Today: plugin setup and database structures.

Plugin Setup

The first thing we do is take care of the most important part of any development – the credits. People need to know who you are and, more importantly, where they can go and give you stuff. So tell them here.

Wordpress uses this commented data to display your information in the dashboard so it needs to be eye-catching and full of links, as mine is here. Hurrah!

<?php
/*
Plugin Name: TabberList
Plugin URI: http://www.line-in.co.uk
Description: A plugin to manage categorised lists with funky tabs for WordPress
Version: 0.1
Author: Simon Fairbairn
Author URI: http://www.line-in.co.uk
*/
/*
	Copyright 2009  Simon Fairbairn  (email : linein@simonfairbairn.com)

  ... License stuff (GNU, for those that care - full text available in plugin) ...
*/

Now that’s done, it can be safely released to the world knowing that everyone is going to be aware of exactly who made this marvellous magical mess.

Although, really, it should probably do a little bit more than advertise your site, so let’s go make it do stuff.

Doing Stuff

So, what did we want it to do? I often find that referring back to the Plugin Development Plan will remind me what it was I was supposed to be doing when I started.

And, look, it does!

Since I’ve decided that this plugin will require its own table in the WordPress database, I should go ahead and create the tables.

Note that this plugin needs its own table because of the vast amount of relational material it will hold when it’s being used by all the thousands of people that are going to love it. Yours might not.

If all you need to keep track of is one or two settings, then Wordpress comes built in with some amazingly simple functions that allow you to easily interact with the database and it’s probably easier and smarter to use them.

Read this, then make yer choice.

Install Functions

To make the tables, we need to have a script that does the following things:

1. Runs only when the plugin is first activated
2. checks to see if the tables exist in the database
3. If they don’t exist, creates them

So let’s do that.

First things first, make sure that this function hasn’t been previously declared. If it hasn’t go ahead and declare it. Then we call the global $wpdb instance (an instantiation of the WordPress database class and a useful layer of protection between the database and, well, me).

if (!function_exists('tabberlist_install')) {
	function tabberlist_install() {
		global $wpdb;
		$list_table_name = $wpdb->prefix . "tabberlist";
		$category_table_name = $wpdb->prefix . "tabberlistcat";

	}
}

register_activation_hook(__FILE__, 'tabberlist_install');

I grab the ‘prefix’ variable from the WordPress database class so my tables will be prefixed with the same thing as the rest of the WordPress tables (default: ‘wp_’). This is important in keeping databases organised.

Some hosts only allow 1 database per user so, potentially, a user with multiple, database-driven aspects to their sites (such as a WordPress install and a separate forum) will have a gajillion tables in their db. I’ve seen this and it’s not pretty so we add the WP prefix to our tables to keep all the WordPress tables bunched together.

Aside – To all hosts out there: let your users have as many databases as they want (or at least 5)! It’s not hard, it’s not expensive and there’s no good reason not to! Thanks, web developers and site admins everywhere.

Version Control

I want to throw in some version control (an idea wholly stolen from the WordPress Codex – that place has a ton of useful stuff and is worth reading every opportunity you get).

So, I create a variable that has the current version number and then I go and get the current version that is listed in the database. The two are compared and, if they do match, then it tells us that the user has installed this plugin before and that they are using the current version and it skips to the bottom.

If they don’t match, then we do a quick check to see if there are database tables installed that have the same name.

if (!function_exists('tabberlist_install')) {
	function tabberlist_install() {
		global $wpdb;
		$tabberlist_version = "0.7.0";
		$hasInstalled = get_option("tabberlist_version");
		$list_table_name = $wpdb->prefix . "tabberlist";
		$category_table_name = $wpdb->prefix . "tabberlistcat";
		if ($hasInstalled != $tabberlist_version) {
			if( $wpdb->get_var("show tables like '$list_table_name'") != $list_table_name) {

			}
		}
	}
}

?>

If no tables exist that have the same name, then we go ahead and create some using the magic of SQL:

if (!function_exists('tabberlist_install')) {
	function tabberlist_install() {
		global $wpdb;
		$tabberlist_version = "0.7.0";
		$hasInstalled = get_option("tabberlist_version");
		$list_table_name = $wpdb->prefix . "tabberlist";
		$category_table_name = $wpdb->prefix . "tabberlistcat";
		if ($hasInstalled != $tabberlist_version) {
			if( $wpdb->get_var("show tables like '$list_table_name'") != $list_table_name) {
				$sql = "CREATE TABLE " . $list_table_name . " (
					id mediumint(9) NOT NULL AUTO_INCREMENT,
					dateAdded VARCHAR(15),
					dateDeleted VARCHAR(15),
					item tinytext NOT NULL,
					url tinytext NOT NULL,
					new VARCHAR(10) NOT NULL,
					deleted VARCHAR(10) NOT NULL,
					cat_id int(5) NOT NULL,
					UNIQUE KEY id (id)
				);";
				require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
				dbDelta($sql);
				add_option("tabberlist_version", $tabberlist_version);
			}
			if ($wpdb->get_var("show tables like '$category_table_name'") != $category_table_name) {
				$sql = "CREATE TABLE " . $category_table_name . " (
					id mediumint(9) NOT NULL AUTO_INCREMENT,
					category tinytext NOT NULL,
					description tinytext,
					counted VARCHAR(10) NOT NULL,
					UNIQUE KEY id (id)
				);";
				dbDelta($sql);
			}

		}

	}
}

If you’re not familiar with SQL language, then basically we’re going through and creating the database columns. For example, the first table has a column named ‘id’ that is integer only, will not take anything more than 9 digits long, will auto increment – automatically add the next available number – if not given something specific and is not to be left blank.

The next column is called ‘dateAdded’ and is a variable character (letters and numbers) string that is no longer than 15 characters. MySQL does have a separate DATETIME data type, but for this plugin we’re using UNIX timestamps (number of seconds since January 1 1970 00:00:00 GMT) so it’s easier just to throw in and retrieve the string of numbers. MySQL requires everything in a specific format (DD-MM-YYYY HH:MM:SS), so it would be a lot of messing around converting to and from timestamps and MySQL DATETIME.

The final thing to note is the UNIQUE KEY declaration. Each table needs a unique key column where every value in that column is, well, unique. We need to tell MySQL which column that is (and then make sure that every entry is totally unique – the best way is to leave that column well alone when writing to the DB – let MySQL take care of the numbering or things get messy fast).

After we’ve declared our tables, we need to require the ‘upgrade.php’ file so that we can use the dbDelta WordPress function. This function does some internal database checking before it adds our table, and will allow us to painlessly change our table structure in future versions of the plugin should we need to.

Which we shouldn’t, cause we had a plan!

We then add an option using the WordPress add_option() function to the standard wp_options table which simply records the current version of our plugin. This simple key/value pair will tell future versions of the plugin what version is currently installed.

Finally, we repeat the table creation process for our ‘categories’ table. Et volia, our very own plugin tables, primed and ready for data. Stay tuned for part 3, where I explore more Plugin Development goodness…

Sign up to my RSS feed so that you don’t miss a step and, if you find it useful, remember to share!