How to implement WordPress plugin update that modifies the database?

2 Views

I develop a WordPress plugin, which has several database tables of its own. The plugin creates these tables when activated, and removes them when deleted/uninstalled.

I have to implement an update process of the plugin which updates plugin’s code as well as tables structure. The simplest case would be to add a new column to one of the tables. The more complex case would be to create a new tables structure and update the content accordingly.

Read More

How would you recommend to solve this problem? Is there any built-in WordPress functions that may help?

Post Views: 2

Related posts

Leave a Reply

2 comments

  1. In short, yes the – $wpdb class. See Codex for more information.

    Whenever you interact with a custom table (or any table, really) you should go through $wpdb – in particularly make sure you’re familiar with the prepare method which can help escapes queries and prevent injections.

    You should be familiar with already, as you should be using it to create the table. On your install hook you should have something like:

    $charset_collate = '';
if ( ! empty($wpdb->charset) )
    $charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
if ( ! empty($wpdb->collate) )
    $charset_collate .= " COLLATE $wpdb->collate";

//Create custom table
$sql_custom_table ="CREATE TABLE {$wpdb->prefix}my_table (
    id bigint(20) unsigned NOT NULL auto_increment,
    column_a varchar(255) default NULL,
    column_b varchar(255) default NULL,
    PRIMARY KEY  (id)
    ) $charset_collate; ";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql_custom_table);

    This code is actually run whenever the plug-in is activated (i.e. not just installed). So it’ll run when someone updates the plug-in automatically. Note: If they upgrade by replacing the plug-in manually – then it won’t – so you’ll need to trigger the above code on admin_init when your plug-in is upgraded (store version number in options table, check against current version).

    Now you wouldn’t normally want the CREATE TABLE SQL command to be running every time you update the plug-in – this is where dBDelta() comes in.

    Before running the above command – it checks if the table exists. What’s more, it checks the column types. So if the table doesn’t exist, it creates it, if it does, but some column types have changed it updates them, and if a column doesn’t exists – it adds it.

    Unfortunately – if you remove a column from the above, it doesn’t automatically remove the column. To remove columns / tables you need to specifically DROP them (checking they exists before you do).

  2. The correct way to do this these days is to include your schema as a file in the plugin source and use the inbuilt WordPress function dbDelta() to update the database as needed using that scheme. The actual code required is very simple:

    $sql = file_get_contents( plugin_dir_path(__FILE__) . "/schema.sql" );
dbDelta( $sql );

    This will both create and update the database for you as required. When I last checked it didn’t delete old unused columns, so you’d need to code for that via a version check. This is a beautiful feature of WordPress and a huge timesaver. Beware when creating the schema.sql file that you copy the spacing in a mysql schema export exactly as the dbDelta() code is reputed to be very fussy about spacing. You should also test the version of the database, and if it isn’t the latest, call the above to update the database. You may also need to do specific updates to cover changes that dbDelta() doesn’t get right (eg deleting a column). It’s easy to write a simple logical if test to see if the version has updated and do these manual updates via $wpdb. For instance, you might drop a column that is now unused.

    $installed_ver = get_option(MY_DB_VERSION);
$wpp = $wpdb->prefix . "mypluginname";
if ($installed_ver < 102)
        $wpdb->query("ALTER TABLE ${wpp}_movies DROP nft_date");
if ($installed_ver < 107)
        $wpdb->query("ALTER TABLE ${wpp}_movies CHANGE lastupdated "
        . "lastupdated TIMESTAMP on update CURRENT_TIMESTAMP "
        . "NOT NULL DEFAULT CURRENT_TIMESTAMP");

update_option(MY_DB_VERSION, $db_version);

    This is simplified from running code, apologies if I’ve broken it in the process of simplifying it for publication.

    Also bear in mind that as of WordPress 3.9.2, WordPress doesn’t always run the activation hook on updating the plugin (specifically, if a mass update is done from the Dashboard Updates page).