Automating Database Schema Updates

Sometimes, the database schema must be “manually” updated to add new primary columns to the users table, to add columns to table attributes' underlying tables, to resize existing columns or the like.

Generally it is not advisable to actually do this by hand as this can be an error-prone task that needs to be performed individually for each deployment.

Every UM plugin has its own database schema level counter that implicitly starts at “0”. (The value of this counter is internally kept in the database and managed by the CSE class PluginSchemaMigration).

To implement a schema level update, two steps must be taken:

The new numeric schema level must be specified in the plugin’s descriptor file.
The actual schema update must be implemented.

Define new schema level in plugin descriptor

If your plugin descriptor file does not already contain it, add a new property named dbSchema and give it the value 1. If your plugin descriptor file already contains the dbSchema property, just increase its value by one.

Your plugin.desc.json should look like this:

{
    "$schema": "http://git.intra.pinuts.de/cmsbs/cmsbs/raw/master/doc/json-schemas/plugin.desc.json",
    "__encoding": "Pinuts File Encoding: UTF-8",
    "name": "de.acme.tutorial",
    ...,
    "dbSchema": 3
}

Implement schema update

Create a new ES6 file in your plugin’s CSE directory. If your plugin’s name is de.acme.tutorial then cmsbs-conf/cse/plugins/de.acme.tutorial/callback/DbSchemaUpdate.mjs would be a good choice:

/// <reference path="../../../.vscode.js"/>

const SQL = UM.config.sql;

export default class DbSchemaMigration extends PluginSchemaMigration {
    /**
     * Set values for new primary "entrytype" attribute.
     * -> "customer" for all Entries
     * -> "admin" for all Entries with cmsbs.isadmin set.
     * (Attributes entrytype and cmsbs.isadmin must both be configured as primary.)
     *
     * @param {SqlScript} scr
     */
    schemaUpdate_0_1(scr) {
        const entrytype = UM.config.getAttribute("entrytype");
        const isadmin = UM.config.getAttribute("cmsbs.isadmin");

        const updateEntryType1 = new SqlQuery(`UPDATE ${SQL.users} SET ${entrytype.sqlName}='customer' WHERE ${entrytype.sqlName} IS NULL`);
        scr.add(updateEntryType1);

        const updateEntryType2 = new SqlQuery(`UPDATE ${SQL.users} SET ${entrytype.sqlName}='admin' WHERE ${isadmin.sqlName}=1`);
        scr.add(updateEntryType2);
    }

    /**
     * Hash passwords using PBKDF2.
     *
     * @param {SqlScript} scr
     */
    schemaUpdate_1_2(scr) {
        UM.query(`isSet(cmsbs.isadmin)`)
            .page(-1)
            .forEach(e => {
                const pw = e.get('password');

                if (pw.length < 100) {
                    UM.println(`Hashing password for user ${e.get('login_name')} (${e.oid})`);
                    e.set('password', pw);
                }
            });

        UM.commitEntries();
    }
  
    /**
     * Demonstrate some more helper methods...
     *
     * @param {SqlScript} scr
     */
    schemaUpdate_2_3(scr) {
        // Create column for primary attribute "new_attribute":
        this.primaryAddColumn(scr, UM.config.getAttribute('new_attribute'));

        // Alter primary attribute's column according to the current settings from *.attributes file:
        this.primaryModifyColumn(scr, UM.config.getAttribute('altered_attribute'));

        // Create column for new table attribute's column:
        this.tableAddColumn(scr, UM.config.getAttribute('table_attribute->new_column'));

        // Alter table attribute's column according to the current settings from *.attributes file:
        this.tableModifyColumn(scr, UM.config.getAttribute('table_attribute->altered_column'));
        
        // Issue SQL statement to create an INDEX:
        this.primaryAddIndex(scr, UM.config.getAttribute('new_attribute'));

        // Convert secondary attribute to primary attribute. (UM 7.60.0 and newer)
        this.convertToPrimary(scr, UM.config.attributes['cmsbs.isadmin']);
        this.convertToPrimary(scr, UM.config.attributes.birthday);
    }
}

// Don't forget to export the class to the respective namespace:
de.acme.tutorial.DbSchemaMigration = DbSchemaMigration;

The method schemaUpdate_0_1 will be executed upon UM startup whenever the recorded schema level for the plugin de.acme.projectx is below “1”.

To implement a second schema update, just increase the dbSchema counter in your plugin descriptor file and implement the schemaUpdate_1_2 method.

There are several helper methods available to accomplish common tasks like adding or altering columns or executing custom SQL statements:

Please keep in mind that a database schema update cannot be undone automatically!

Have a fresh backup at hand to be able to rollback the database if needed.