As our plugin grows, you may find yourself needing more than the simple key/value store that the store_data() and retrieve_data() methods afford us.

We will want to use the full power of database tables to handle our data; The plugin system has some tools to handle this too.

Plugin versioning

If we are going to add tables, we need to start thinking about plugin lifecycle. We might begin with one table and later find we want to alter the fields, or add a second table. You may even want to drop a table down the line.

As we mentioned on our first post, plugins are expected to be versioned. The version is set on a package variable that carries the value. While there’s no constraint on how to do things, we usually follow the MAJOR.MINOR.PATCH format. Every new version should have a higher version number.

Plugins in the wild don’t have a hardcoded version number on their repositories. The version number is set at build time, when the .kpz package is built. The version is picked from the package.json file.

You can check the current version:

$ npm version
{
  'koha-plugin-fancy': '0.0.1',
  npm: '7.0.14',
  node: '15.3.0',
  ...
}

And setting the version is done by running:

$ npm version -s 0.0.2
$ npm version
{
  'koha-plugin-fancy': '0.0.2',
  npm: '7.0.14',
  node: '15.3.0',
  ...
}

Note: you can omit the -s switch if you don’t have a GPG key set. But it is a good practice to sign your releases so they can be verified.

Table name(space)

To avoid table name collisions, the plugin system provides a method for prefixing the table name with a value that is calculated from the plugin class name. This helps avoiding two plugins from adding (say) a words_list table:

my $table_name = $self->get_qualified_table_name('words_list');

Creating a table

A Koha plugin is extending Koha, so can rely on C4::Context->dbh to get a database handle and do whatever we need.

Table creation could be triggered by clicking a button on a page our plugin adds, or an API call, but is it usually best to use the install() or upgrade() methods which are run at plugin install and upgrade automatically.

sub install {
    my ( $self, $args ) = @_;

    my $words_list_table = $self->get_qualified_table_name('words_list');

    C4::Context->dbh->do(qq{
        CREATE TABLE $words_list_table (
          `id` INT(11) NOT NULL auto_increment,
          `fancy_word` NULL DEFAULT NULL,
          PRIMARY KEY  (id),
          CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    }) unless $self->_table_exists($words_list_table);

    return 1;
}

The _table_exists() method doesn’t actually exist in Koha yet; it is borrowed from the GOBI plugin. It would be a useful addition to the base class so I have filed a proposal for adding it.

Updating a table structure

Let’s say we want to add a useful timestamp column to our new table.

Something we’ve learnt from our beloved Koha project is that upgrades should be seamless. Plugins are no exception to this. And doing it right is not that hard either. The best approach to this, is replicating our updatedatabase.pl design.

When a plugin is installed, the plugin version is stored in a reserved key __INSTALLED_VERSION__ in the plugin data storage.

As seen on yesterday’s post, we can have access to it with:

my $version = $self->retrieve_data('__INSTALLED_VERSION__');

Using this, and the $self->_version_compare() method, we can build our upgrade method:

sub upgrade {
    my ( $self, $args ) = @_;

    my $database_version = $self->retrieve_data('__INSTALLED_VERSION__') || 0;

    if ( $self->_version_compare( $database_version, "0.0.1" ) == -1 ) {

        my $words_list_table = $self->get_qualified_table_name('words_list');

        C4::Context->dbh->do(qq{
            ALTER TABLE $words_list_table
            ADD COLUMN `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            AFTER `fancy_word`;
        });

        $database_version = "0.0.2";
        $self->store_data({ '__INSTALLED_VERSION__' => $database_version });
    }

    return 1;
}

Note that if we were installing version 0.0.2 from scratch, the upgrade() process won’t be triggered, so we still need to adjust the table definition on the install() method.

Using the new table

This entry will be very slim: we have a table and a DBI database handle. The world is ours!

Further reading: An interesting plugin implementation that uses DBIC schemas and Koha::Object(s)-derived classes can be found here. But that one deserves its own post to explain Galen’s trick for getting the schemas picked correctly and how to build Koha::Object(s) classes.