Unlimited Plugins, WordPress themes, videos & courses! Unlimited asset downloads! From $16.50/m Advertisement # Custom Database Tables: Maintaining the Database Read Time:9 minsLanguages: This post is part of a series called Custom Database Tables. Custom Database Tables: Creating an API Custom Database Tables: Exporting Data 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: 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

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:

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

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

• You must use KEY rather than its synonym INDEX.
• Any KEYs must be given a name. For example, do not write

(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,

will cause an error. The correct format is:

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.

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:

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:

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

## Removing Keys

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

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

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.

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()