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 Type | Directory |
|---|
| PostgreSQL | schemas/dbscripts/postgresql/ |
| Microsoft SQL Server | schemas/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.
