SQL Scripts for Module-Based Assays: /home/

SQL Scripts for Module-Based Assays

How do you add supporting tables 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:

1) Manually import a list archive into the target folder.

2) Add the tables via SQL scripts included in the module. 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

To support only one database, include a script only for that database, and configure your module properties accordingly -- see "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>

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 customModules/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