Overview

Some Movable Type plugins require database tables to store their information because it is too large for the simple plugin settings data. It has always been a problem to create these tables automatically, but with MT 3.2 this is now possible.

Implementation

This implementation description presumes that the plugin and the plugin data class are correctly registered in accord with Movable Type 3.2 requirements. All of that is covered elsewhere (like here), although I learned primarily by reading the code and its internal documentation, and looking at other working plugins. The write up on the Movable Type website isn’t worth reading (incomplete and obsolete) so I recommend against looking there.

Define the columns

When using the install_properties mechanism to define the data elements of a plugin, use ‘column_defs’ instead of ‘columns’. This is a hash, not an array, which maps column names to column descriptors. These descriptors are the standard SQL description of a column. Some standard types to use are

  • For the required identifier column ‘id’ — ‘integer not null auto_increment
  • For columns that store entry, weblog, comment identifiers — ‘integer not null
  • Strings — ‘string(255)’. This one I am not so clear on, whether the number matters for performance or, if it does, what the optimal value is. It will be transformed in to a variable sized string, so it doesn’t matter in terms of functionality.

Detect / create missing tables.

This is the code used in my Webiki plugin. It is in the configuration template generation logic. The Webiki plugin defaults to not transforming the text and the user must therefore visit the configuration page before it works. I am not sure how expensive the table existence checks are, so I wanted to not have them done every single time the plugin was loaded.

my $db = MT::Object->driver();
if (not $db->table_exists('Transfinitum::Webiki::Word')) {
    my $error = 'Database handle from driver not valid';
    my $statement;
    my $dbh = $db->{dbh};
    if ($dbh) {
        $error = '';
        for ( $db->fix_class('Transfinitum::Webiki::Word') ) {
            $statement = $_; # cache for later use in error message
            if (not $dbh->do($statement)) {
                $error = $dbh->errstr;
                last;
            }
        }
    }
    if ($error) {
        MT->instance->log(
          MT->translate(
            'Failed to create database tables for Webiki plugin - [_1]'
           , $error
        ));
        $tmpl .= <<INIT;
<p style="color:red;">Failed to create database tables for Webiki plugin.
<br />Statement: &lsquo;$statement&rsquo;
<br />Error: $error.</p>
INIT
    } else {
        $tmpl .= '<p>Created database tables for Webiki plugin.</p>';
    }
}
$tmpl;

The first two lines check for the existence of the tables for the ‘Transfinitum::Webiki::Word’ object, which is the type of object stored in the database table. If not, then we create them. The fix_class method does not actually fix the class, but instead returns the appropriate database statements to do so. This method is generally used in the upgrade process, but if the table doesn’t exist (as we’ve determined) then it emits only the table creation statements.

After that, we execute the statements one by one, checking for errors. If errors are encountered, we log that and attach it to the settings display. If the tables are created successfully, we make a note of that as well.

Notes

You can see this code used in a working plugin by looking at the source for Webiki. Working code is always a good companion to documentation.