You can include SQL scripts in your ETL module that will run automatically upon deployment of the module, in order to generate target databases for your ETL processes. For step by step instructions on running a script see
ETL Tutorial: Create a New ETL Process.
Directory Structure
LabKey Server will automatically run SQL scripts that are packaged inside your module in the following directory structure:
MODULE_NAME
config
etls
queries
schemas
dbscripts
postgres
**SCRIPT_NAME.sql** - Script for PostgreSQL.
mssql
**SCRIPT_NAME.sql** - Script for MS SQL Server.
SQL Script Names
Script names are formed from three components: (1) schema name, (2) previous module version, and (3) current module version, according to the following pattern:
SCHEMA-PREVIOUSVERSION-CURRENTVERSION.sql
where SCHEMA is the name of the schema to be generated by the script.
For an initially deployed module that hasn't existed on the server previously, an example script name would be:
patientSchema-0.0-1.0.sql
For more details on naming scripts, especially naming upgrade scripts, see
Modules: SQL Scripts.
Schema XML File
LabKey will generate an XML schema file for a table schema by visiting a magic URL of the form:
http://<server>/labkey/admin/getSchemaXmlDoc.view?dbSchema=<schema-name>
Examples
This script creates a simple table and a stored procedure for
MS SQL Server dialect.
CREATE SCHEMA target1;
GO
CREATE procedure [target1].[normalizePatientData] (@transformRunId integer)
as
begin
UPDATE Patients SET Gender='Female' WHERE (Gender='f' OR Gender='F');
UPDATE Patients SET Gender='Male' WHERE (Gender='m' OR Gender='M');
end
GO
CREATE TABLE target1.Patients
(
RowId INT IDENTITY(1,1) NOT NULL,
Container ENTITYID NOT NULL,
CreatedBy USERID NOT NULL,
Created DATETIME NOT NULL,
ModifiedBy USERID NOT NULL,
Modified DATETIME NOT NULL,
PatientId INT NOT NULL,
Date DATETIME NOT NULL,
LastName VARCHAR(30),
FirstName VARCHAR(30),
MiddleName VARCHAR(30),
DateVisit DATETIME,
Gender VARCHAR(30),
PrimaryLanguage VARCHAR(30),
Email VARCHAR(30),
Address VARCHAR(30),
City VARCHAR(30),
State VARCHAR(30),
ZIP VARCHAR(30),
Diagnosis VARCHAR(30),
CONSTRAINT PatientId PRIMARY KEY (RowId)
);
These scripts are in
PostgreSQL SQL dialect.
---------------
-- schema1 --
---------------
DROP SCHEMA schema1 CASCADE;
CREATE SCHEMA schema1;
CREATE TABLE schema1.patients
(
patientid character varying(32),
date timestamp without time zone,
startdate timestamp without time zone,
country character varying(4000),
language character varying(4000),
gender character varying(4000),
treatmentgroup character varying(4000),
status character varying(4000),
comments character varying(4000),
CONSTRAINT patients_pk PRIMARY KEY (patientid)
);
CREATE OR REPLACE FUNCTION changecase(searchtext varchar(100), replacetext varchar(100)) RETURNS integer AS $$
UPDATE schema1.patients
SET gender = replacetext
WHERE gender = searchtext;
SELECT 1;
$$ LANGUAGE SQL;
Related Topics
