• Overview
• A Word of Caution
• Script Execution
• Schema Versions and LabKey Conventions
• Development Workflow
• Java Upgrade Code
• Specifying Additional Metadata
• Manually Resetting the Schema
• Script Administration Dashboard
• Hints and Advanced Topics

Overview

The scripts run sequentially, each building on the schema that the previous script left behind. They may run on an existing schema that already contains data. For example, the initial script might create a schema that contains one table. A second script might alter the table to add new columns. A third script might create a second table with some of the same columns, copy any existing data from the original table, and then drop the now-duplicated columns from the original table.

For all sql scripts, we recommend that they conform to the conventions outlined in this topic:

• Modules: SQL Script Conventions

A Word of Caution

Multiple test and development servers will run SQL scripts shortly after they are pushed to a Git repository. SQL scripts will never be re-run, so consider pushed scripts immutable; instead of changing them, create a new upgrade script to make further changes. See the Hints and Advanced Topics section below for ways to make this process easier.

Script Execution

When a module is upgraded, the SQL Script Manager automatically runs the appropriate scripts to upgrade to the new schema version. It determines which scripts to run based on the version information encoded in the script name. The scripts are named using the following convention: <dBschemaName>-<fromVersion ##.###>-<toVersion ##.###>.sql

A module can manage multiple schemas. If so, all of them use a single module schema version number progression. If a module manages multiple database schemas, be extra careful about versioning and naming and you should expect to see many gaps between each schema's script files.

Modules are upgraded in dependency order, which allows schemas to safely depend on each other.

Schema Versions and LabKey Conventions

As of 2020, LabKey versions schemas based the current year plus an increasing three-digit counter for each schema. With every schema change (typically via a SQL script) the counter increases by one. As an example:

• At the beginning of 2020, the query schema version is set to 20.000 by setting QueryModule.getSchemaVersion() to return 20.000.
• In January, a change to the query schema is needed, so a developer creates and pushes a query-20.000-20.001.sql script and bumps the QueryModule.getSchemaVersion() return value to 20.001. The SQL Script Runner runs that script on every server and records the module's new schema version in each database after it runs.
• Another change is needed later in the year, so query-20.001-20.002.sql is pushed along with the schema version getting bumped to 20.002.
• At the beginning of 2021, the query schema version is then set to 21.000.

Some examples:

• myModule-0.000-20.000.sql: Upgrades myModule schema from version 0.000 to 20.000
• myModule-20.000-20.001.sql: Upgrades myModule schema from version 20.000 to 20.001
• myModule-20.001-20.002.sql: Upgrades myModule schema from version 20.001 to 20.002

Development Workflow

• Finalize and test your SQL script contents.
• Git pull to sync with files in your repo and help avoid naming conflicts.
• Find the current version number returned by the myModule getSchemaVersion() method (or SchemaVersion in module.properties, if that's used instead). Let's say it's 20.003.
• Name your script "myModule-20.003-20.004.sql". (The starting version must always match the latest schema version, otherwise it won't execute elsewhere. There will be gaps in the sequence.)
• Bump the schema version to 20.004.
• Build, test, and commit/push your changes.

If you're testing an extensive schema change you may want to check in a script but not have it run on other developers' machines immediately. This is simple; check in the script but don't bump the version number in code. When you're done testing, bump the version and everyone will upgrade.

The above guidelines eliminate most, but not all, problems with script naming. In particular, if multiple developers are working on the same module (perhaps on different branches) they must coordinate with each other to ensure scripts don't conflict with each other.

Java Upgrade Code

public class MyModule extends DefaultModule
{
    @Override
    public UpgradeCode getUpgradeCode()
    {
        return new MyUpgradeCode();
    }
}

public class MyUpdateCode implements UpgradeCode
{
    public void myUpgradeMethod(final ModuleContext moduleContext)
    {
        // Do the upgrade work
    }
}

You can invoke the upgrade code from inside an update script via the core.executeJavaUpgradeCode stored procedure. The syntax is slightly different depending on your primary database type.

PostgreSQL

SELECT core.executeJavaUpgradeCode('myUpgradeMethod');

SQL Server

EXEC core.executeJavaUpgradeCode 'myUpgradeMethod';

You can annotate a Java UpgradeCode method with @DeferredUpgrade to defer its running until after initial upgrade of all modules is complete. This can be useful if the upgrade code needs to call library methods that change based on the current schema. Be very careful here; the schema could be in a completely unknown state (if the server hasn't upgraded in a while then your code could execute after two years of future upgrade scripts have run).

You can manually invoke Java upgrade code methods via the Script Administration Dashboard (see details below); this option eases the development process by providing quick iteration and testing of upgrade code.

Specifying Additional Metadata

http://localhost:8080/admin-getSchemaXmlDoc.view?dbSchema=<yourSchemaName>
or:
 http://localhost:8080/labkey/admin-getSchemaXmlDoc.view?dbSchema=<yourSchemaName>

Simply replace the domain name & port with the correct values for your server. Also put the name of your schema after 'dbSchema='. Note: Both the schema XML file name and 'dbSchema=' value are case-sensitive. They must match the database schema name explicitly.

For example:

https://www.labkey.org/Home/admin-getSchemaXmlDoc.view?dbSchema=core

Manually Resetting the Schema

When developing a new module, schemas can change rapidly. During initial development, it may be useful to completely uninstall / reinstall a module in order to rebuild the schema from scratch, rather than make changes via a large number of incremental scripts. Uninstalling a module requires several steps: drop the schema, delete the entry in the core.Modules table, delete all the associated rows in the core.SqlScripts table. The "Module Details" page (from the Admin Console) provides a quick way to uninstall a module; when your server is restarted, the module will be reinstalled and the latest scripts run. Use extreme caution… deleting a schema or module should only be done on development machines. Also note that while this is useful for development, see warnings above about editing scripts once pushed to GitHub and/or otherwise made available to other instances of LabKey.

Script Administration Dashboard

The Admin Console provides helpful tools for managing SQL Scripts

• Select (Admin) > Site > Admin Console.
• Under Diagnostics, click SQL Scripts.

• Consolidate Scripts: Select a version range, optionally include single scripts, and click Update.
• Orphaned Scripts: See the list of scripts that will never execute because another script has the same "from" version and a later "to" version. These scripts can be deleted safely.
• Scripts with Errors: See a list of scripts with errors, if any.
• Upgrade Code: Invoke Java upgrade code methods manually, useful for developing upgrade code and (in rare cases) addressing upgrade issues on production machines.

Hints and Advanced Topics

• Modules can (optionally) include two special scripts for each schema: <schema>-create.sql and <schema>-drop.sql. The drop script is run before all module upgrades and the create script is run after that schema's scripts are run. The primary purpose is to create and drop SQL views in the schema. The special scripts are needed because some databases don't allow modifying tables that are used in views. So LabKey drops all views, modifies the schema, and re-creates all views on every upgrade.

• LabKey offers automated tests that will compare the contents of your schema XML file with the actual tables present in the DB. To run this test, visit a URL similar to one of these, substituting the correct host name and port (and only using the /labkey/ context path if necessary:

  http://localhost:8080/junit-begin.view
  or
  http://localhost:8080/labkey/junit-begin.view

Related Topics

• Modules: SQL Script Conventions
• Map of Module Files