Modules: Database Transition Scripts

Documentation
The schemas directory includes SQL scripts that are run when the module is first loaded. The scripts can define database schema and insert data.

Modules that need to store their own data may find it useful to create a new schema and set of related tables in the relational database used by LabKey Server. Modules can transition schemas between versions by including database transition scripts.

Generate a schema

You can generate a basic version of the schema file for an existing schema by navigating to a magic URL:

http://<server>/labkey/admin/getSchemaXmlDoc.view?dbSchema=<schema-name>

Save the result to the /schema/<schema-name>.xml file in your module.

Store schema transition scripts

Schema transition scripts should live in the schemas/dbscripts/<db-type>/ directory of your module. Currently, the following database types are supported:

Database TypeDirectory
PostgreSQLschemas/dbscripts/postgresql/
Microsoft SQL Serverschemas/dbscripts/sqlserver/

The name of the script is also quite important. Each script in this directory moves the database schema from one version of your module to another. The name of the file indicates which versions the script will transition from and to. The general format is <schemaname>-<oldversion>-<newversion>.sql. For more details about how these scripts work, see Modules: SQL Scripts.

For example, to create a new schema with some tables for your module (which we have assigned a version number of 1.0) on a PostgreSQL database, you would create a new SQL script file in the following location:

externalModules/resources/schemas/dbscripts/postgresql/ReportDemo-0.0-1.0.sql

Your schema name can be anything that does not conflict with any existing schema name, so it's generally best for your schema to be named the same as your module.

When a new version of a module appears, the server will restart and, during its initialization, it will execute any relevant database scripts. Once the scripts to bring the module to version 1.0 have been executed, the module will report its version as 1.0, and those scripts will not be run again. If you need to make changes to your database schema, adjust your module version to 1.1, and create a new SQL script to transition the database schema from version 1.0 to 1.1. The file name for that would be:

externalModules/resources/schemas/dbscripts/postgresql/ReportDemo-1.0-1.1.sql

Related Topics

See Modules: SQL Scripts, which describes these files in detail.

Discussion

previousnext
 
expand all collapse all