This page includes frequently asked questions related to the use of sql in implementation guides (IGs).

An SQL query can be performed and the result set displayed as a table in any narrative page. The SQL query is embedded in the page like this:

{​% sql select Name, Value from Metadata %}

This syntax will generate this table in the built IG:

The SQL engine that evaluates the queries is SQLite3. The database the SQL is evaluated against is built as part of building the IG, and is available for authors to peruse in the file package.db when the IG is built.

Notes for Authors:

• Authors wishing to use SQL should use an SQLite3 tool to develop their queries against package.db
• The tables in the package.db are a moving target. See below for documentation

{​% sql fmt:lines;a;md;l:1 select name as Name, Description, web from Resources %}

Which produces this table:

Documentation:

• The syntax is fmt: followed by a series of semi-colon delimited codes
• The first code is the css style to use for the table. If it's blank, grid will be used (the default). Look in the css to see what styles are defined
• The rest of the codes are the format control for the each column. Any columns not listed are treated as AutoDetect
• Possible codes are: a (auto-detect), s (plain string, no auto-detect), md (treat as markdown), l:x (don't add the row; treat as the link for column x (1 based))

View Definitions

You can use ViewDefinitions to generate custom SQL tables that are also added to the package.db file. These can be used when pages are generated (as above), but might also be useful when the packages are used at run-time etc.

ViewDefinitions are registered with the IG publisher using one or more viewDefinition parameters in the ImplementationGuide Resource that refer to the ViewDefinition to execute by the path to the local JSON file that contains the ViewDefinition. Note that templates are able to register ViewDefinitions using the same mechanism.

Each ViewDefinition generates a table in the output package.db. Table names must be unique (including the list below). It's the responsibility of the IG author to ensure that the names (taken from ViewDefinition.name) have to be unique.

Note that ViewDefinitions are experimental, and the definition or the table created may change without warning. (for now)

{
  "name" : "TableName",
  "resource" : "StructureDefinition",
  "extension" : [{
    "url" : "http://hl7.org/fhir/tools/StructureDefinition/view-definition-scope",
    "valueCode" : "mask"
  }]
}

Base Tables

The following tables are always created by the IG publisher: