Linked schemas allow you to access subsets of data from another project, by linking to a schema in that project.
Linked schemas are useful in Extract-transform-Load applications and they provide a way to apply security settings at a finer granularity than at the level of whole folders. (See Related Topics below for other methods.) 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 private, some is 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 the original schema 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 of the Source Schema/Tables.
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.)
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.
- 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. See below for details.)
- Source Schema: Select the name of the originating schema.
- Published Tables: To link/publish all of the tables and queries, make no selection. To link/publish a subset of tables, use the multi-select dropdown.
- Meta Data: Provide metadata filters for additional refinement. (Optional. See below for details.)
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 People -- a record is shown only when PublicInfo is true.
<tables xmlns="http://labkey.org/data/xml" xmlns:cv="http://labkey.org/data/xml/queryCustomView">
<cv:filter column="PublicInfo" operator="eq" value="true"/>
<table tableName="People" tableDbType="NOT_IN_DB">
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 attachment field the in the list called "SourceList", which is in the Project/Folder called "SourceFolder". The activated field is called "AttachedDoc". To get the URL pattern, go to the source List and hover over one of the links in the attachment column. Right-click and copy the link.
<table tableName="SourceList" tableDbType="NOT_IN_DB">
For more information about metadata xml, see Query Metadata
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 schemas directory of a module:
The example .template.xml file below provides a default linked schema and a default filter xml for Client A:ClientA.template.xml
<cv:filter column="Client" operator="eq" value="A Client"/>
<dat:table tableName="Data" tableDbType="NOT_IN_DB">
Note: To use the module, you must enable it in the source folder (folder A):
Go to the source folder and select Admin -> Folder -> Management -> Folder Type tab -> under Modules place a checkmark next to your module -> click Update Folder
You can override
any of the default values, even after selecting the template:
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: