Linked Schemas and Tables: /Documentation/Archive/21.11

Linked Schemas and Tables

Linked schemas allow you to access data from another folder, by linking to a schema in that folder. Linked schemas are useful for providing access to selected data in another folder which is otherwise not allowed by that folder's overall permissions. Linked schemas provide a way to apply security settings at a finer granularity than at the level of whole folders.

Usage Scenario
Security Considerations
Create a Linked Schema
Metadata Filters
Handling Attachment Fields
Schema Template

Usage Scenario

Linked schemas are especially useful when you want to reveal some data in a folder without granting access to the whole folder. For example, suppose you have the following data in a single folder A. Some data is intended to be private, some is intended for the general public, and some is intended for specific audiences:

Private data to be shown only to members of your team.
Client data and tailored views to be shown to individual clients.
Public data to be shown on a portal page.

You want to reveal each part of the data as is appropriate for each audience, but you don't want to give out access to folder A. To do this, you can create a linked schema in another folder B that exposes selected items in folder A. The linked schema may expose some or all of the tables and queries from the original schema. Furthermore, the linked tables and queries may be additionally filtered to create more refined views, tailored for specific audiences.

Security Considerations

The nature of a linked schema is to provide access across containers which may have different permissions configurations.

Creator permissions: Data access in the 'remote' container is based on the userID of the user who created the linked schema. If those permissions change over time, the linked schema may stop working correctly. Symptoms include query errors and "missing" tables or columns. To resolve, the linked schema may need to be deleted and recreated by a new owner with the necessary permissions to the linked container.

Lookup behavior: Lookups are removed from the source tables and queries when they are exposed in the linked schema. This prevents traversing a table in a linked schema beyond what has been explicitly allowed by the linked schema creator.

URLs: URLs are also removed from the source tables. The insert, update, delete URLs are removed because the linked schema is considered read-only. The details URL and URLs on columns are removed because the URL would rarely work in the linked schema container. If desired, the lookups and URLs can be added back in the linked schema metadata xml. To carry over the attachment field links in the source table, first copy the metadata that enables the link in the source table and paste it into the analogous field in the linked table. See below for an example.

Create a Linked Schema

To create a linked schema in folder B that reveals data from folder A:

Navigate to folder B.
Select (Admin) > Developer Links > Schema Browser.
Click Schema Administration.
Under Linked Schemas, click New Linked Schema and specify the schema properties:

Schema Name: Provide a name for the new schema.
Source Container: Select the source folder that holds the originating schema (folder A).
Schema Template: Select a named schema template in a module. (Optional.)
Source Schema: Select the name of the originating schema in folder A.
Published Tables: To link/publish all of the tables and queries, make no selection. To link/publish a subset of tables, use checkboxes in the multi-select dropdown.
Metadata: Provide metadata filters for additional refinement. (Optional.)

Metadata Filters

You can add metadata xml that filters the data or modifies how it is displayed on the page.

In the following example, a filter is applied to the table Location -- a record is shown only when InUse is true.

<tables xmlns="http://labkey.org/data/xml" xmlns:cv="http://labkey.org/data/xml/queryCustomView">
  <filters name="public-filter">
    <cv:filter column="InUse" operator="eq" value="true"/>
  </filters>
  <table tableName="Location" tableDbType="NOT_IN_DB">
    <filters ref="public-filter"/>
  </table>
</tables>

Handling Attachment Fields

Attachment fields in the source table are not automatically carried over into the target schema, but you can activate attachment fields by providing a metadata override. For example, the XML below activates an attachment field called "AttachedDoc" in the list called "SourceList", which is in the domain/folder called "/labkey/SourceFolder". The URL must contain the listId (shown here = 1), the entityId, and the name of the file.

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="SourceList" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="AttachedDoc">
        <url>/labkey/SourceFolder/list-download.view?listId=1&amp;entityId=${EntityId}&amp;name=${AttachedDoc}</url>
      </column>
    </columns>
  </table>
</tables>

The entityId is a hidden field that you can expose on the original list. To see it, open (Grid Views) > Customize Grid, then check the box for "Show Hidden Fields" and scroll to locate the checkbox for adding "Entity Id" to your grid. Click View Grid to see it. To include it in the URL carried to the target schema, use the syntax "${EntityId} as shown above.

For more information about metadata xml, see Query Metadata.

Schema Template

Default values can be saved as a "schema template" -- by overriding parts of the template, you can change:

the source schema (for example, while keeping the tables and metadata the same).
the metadata (for example, to set up different filters for each client).

Set up a template by placing .template.xml file in the resources/schemas directory of a module:

myModuleA/resources/schemas/ClientA.template.xml

The example .template.xml file below provides a default linked schema and a default filter xml for Client A:

ClientA.template.xml

<templateSchema xmlns="http://labkey.org/data/xml/externalSchema"
	xmlns:dat="http://labkey.org/data/xml"
        xmlns:cv="http://labkey.org/data/xml/queryCustomView"
        sourceSchemaName="assay.General.Custom Assay">
    <tables>
        <tableName>Data</tableName>
    </tables>
    <metadata>
        <dat:tables>
            <dat:filters name="client-filter">
                <cv:filter column="Client" operator="eq" value="A Client"/>
            </dat:filters>
            <dat:table tableName="Data" tableDbType="NOT_IN_DB">
                <dat:filters ref="client-filter"/>
            </dat:table>
        </dat:tables>
    </metadata>
</templateSchema>

To use the module, you must enable it in the source folder (folder A):

Go to the source folder and select (Admin) > Folder > Management.
Select the Folder Type tab.
Under Modules, check the box next to your module.
Click Update Folder.

You can override any of the template values by clicking Override template value for the appropriate region.

For example, you can create a schema for Client B by (1) creating a new linked schema based on the template for Client A and (2) overriding the metadata xml, as shown below:

Once you've clicked override, the link will change to read Revert to template value giving you that option.

LabKey Support

LabKey Support