SQL Scripts for Module-Based Assays: /Documentation/Archive/24.11

SQL Scripts for Module-Based Assays

How do you add supporting tables or lists to your assay type? For example, suppose you want to add a table of Reagents, which your assay domain refers to via a lookup/foreign key?

Some options:

Manually import a list archive into the target folder. Effective, but manual and repetitive.
Add the table/lists via SQL scripts included in the module. This option allows you to automatically include your list in any folder where your assay is enabled.

Add Supporting Table

To insert data: use SQL DML scripts or create an initialize.html view that populates the table using LABKEY.Query.insertRows().

To add the supporting table using SQL scripts, add a schemas directory, as a sibling to the assay directory, as shown below.

exampleassay
├───assay
│   └───example
│       │   config.xml
│       │
│       ├───domains
│       │       batch.xml
│       │       result.xml
│       │       run.xml
│       │
│       └───views
│               upload.html
│
└───schemas
    │   SCHEMA_NAME.xml
    │
    └───dbscripts
        ├───postgresql
        │       SCHEMA_NAME-X.XX-Y.YY.sql
        └───sqlserver
                SCHEMA_NAME-X.XX-Y.YY.sql

If your assay supports only one database, i.e. "pgsql", you can include a script only for that database, and configure your module properties accordingly. Learn more under "SupportedDatabases" in module.properties Reference.

LabKey Server does not currently support adding assay types or lists via SQL scripts, but you can create a new schema to hold the table, for example, the following script creates a new schema called "myreagents" (on PostgreSQL):

DROP SCHEMA IF EXISTS myreagents CASCADE;

CREATE SCHEMA myreagents;

CREATE TABLE myreagents.Reagents
(
    RowId SERIAL NOT NULL,
    ReagentName VARCHAR(30) NOT NULL
	
);

ALTER TABLE ONLY myreagents.Reagents
    ADD CONSTRAINT Reagents_pkey PRIMARY KEY (RowId);
	
INSERT INTO myreagents.Reagents (ReagentName) VALUES ('Acetic Acid');
INSERT INTO myreagents.Reagents (ReagentName) VALUES ('Baeyers Reagent');
INSERT INTO myreagents.Reagents (ReagentName) VALUES ('Carbon Disulfide');

Update the assay domain, adding a lookup/foreign key property to the Reagents table:

<exp:PropertyDescriptor>
        <exp:Name>Reagent</exp:Name>
        <exp:Required>false</exp:Required>
        <exp:RangeURI>http://www.w3.org/2001/XMLSchema#int</exp:RangeURI>
        <exp:Label>Reagent</exp:Label>
        <exp:FK>
            <exp:Schema>myreagents</exp:Schema>
            <exp:Query>Reagents</exp:Query>
        </exp:FK>
    </exp:PropertyDescriptor>

Make Table Domain Editable

If you'd like to allow admins to add/remove fields from the table, you can add an LSID column to your table and make it a foreign key to the exp.Object.ObjectUri column in the schema.xml file. This will allow you to define a domain for the table much like a list. The domain is per-folder so different containers may have different sets of fields.

For example, see reagent/resources/schemas/reagent.xml. It wires up the LSID lookup to the exp.Object.ObjectUri column

<ns:column columnName="Lsid"> 
        <ns:datatype>lsidtype</ns:datatype> 
        <ns:isReadOnly>true</ns:isReadOnly> 
        <ns:isHidden>true</ns:isHidden> 
        <ns:isUserEditable>false</ns:isUserEditable> 
        <ns:isUnselectable>true</ns:isUnselectable> 
        <ns:fk> 
          <ns:fkColumnName>ObjectUri</ns:fkColumnName> 
          <ns:fkTable>Object</ns:fkTable> 
          <ns:fkDbSchema>exp</ns:fkDbSchema> 
        </ns:fk> 
      </ns:column>

...and adds an "Edit Fields" button that opens the domain editor.

function editDomain(queryName) 
{ 
    var url = LABKEY.ActionURL.buildURL("property", "editDomain", null, { 
        domainKind: "ExtensibleTable", 
        createOrEdit: true, 
        schemaName: "myreagents", 
        queryName: queryName 
    }); 
    window.location = url; 
}

LabKey Support

LabKey Support