Guidance for FHIR IG Creation
0.1.0 - CI Build International flag

Guidance for FHIR IG Creation, published by HL7 International - FHIR Management Group. This guide is not an authorized publication; it is the continuous build for version 0.1.0 built by the FHIR (HL7® FHIR® Standard) CI Build. This version is based on the current content of https://github.com/FHIR/ig-guidance/ and changes regularly. See the Directory of published versions

Working with SQL

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:

NameValue
pathhttp://hl7.org/fhir/R5/
canonicalhttp://hl7.org/fhir/uv/howto
igIdhl7.fhir.uv.howto
igNameHowToCreateIGs
packageIdhl7.fhir.uv.howto
igVer0.1.0
errorCount1
version5.0.0
revision2aecd53
versionFull5.0.0-2aecd53
toolingVersion5.0.0
toolingRevision3
toolingVersionFull5.0.0 (3)
totalFiles1
processedFiles25
genDateSun, May 12, 2024 22:08+0000
genDay12/05/2024
gitstatusmaster

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
  • If authors wish to affect the output, they can include a display string before the display like this:
  • {​% sql fmt:lines;a;md;l:1 select name as Name, Description, web from Resources %}
    

    Which produces this table:

    NameDescription
    HowToCreateIGs

    Provides guidance on the process to define implementation guides using the HL7 IG Publisher and HL7-managed IG templates. It also provides guidance on 'best practices' around IG development and information about how to develop and maintain IG templates.

    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))

    Embedding SQL Results as JSON Data

    In addition to displaying SQL query results directly on the page, you can also execute a query and save the results as a JSON data file that can be used in Jekyll/Liquid templates. This allows you to query the IG database, generate JSON data, and then use that data to dynamically build content.

    To use this feature, use the {​% sqlToData %} tag with the following syntax:

    {​% sqlToData myFileName SELECT ... %}

    Where:

    • myFileName is the name you want to give the _data JSON file (without ".json" extension). The same content will also be assigned to a liquid variable that you use to access the data in Jekyll.
    • SELECT ... is your SQL query

    The query results will be saved to _data/myFileName.json and can be accessed in Jekyll using site.data.myFileName. The same content will be assigned to the myFileName variable in the current context. The result is an array of rows where reach rows is a JSON object whoe keys are column names and values are column values.

    For example:

    
    {​% sqlToData itemQuery
      SELECT count(*) as n from metadata
    %}
    
    Number of Metadata Items: {​{ itemQuery[0].n }} 
    
    

    This queries the Metadata table to count items, saves the results to _data/itemQuery.json, and then generate an HTML output including the count.

    Which produces this output:

    Number of Metadata Items: 18

    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)

    Scope

    By default, the view is executed against the resources defined in the Implementation Guide itself. If desired, additional resources can be brought into scope, but this might have a considerable performance penalty. To specify additional package to bring into scope, use the extension http://hl7.org/fhir/tools/StructureDefinition/view-definition-scope.

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

    Where the mask is a package id that might include a wild card, e.g. hl7.fhir.us.core, or hl7.fhir.*, or just *. In any of these cases, the scope of the package specifier is limited to the set of packages that are in scope for the IG by it's listed dependencies and the recursive dependencies of any of those packages.

    Base Tables

    The following tables are always created by the IG publisher:

    Table / Field Name Type Nullable? Description
    MetadataMetadata about the IG including things such as build times
    Key integer False Incrementing Primary Key
    Name string False Name of Metadata item
    Value string False Value
    ResourcesA list of all resources defined in this IG. Not including contained resources
    Key integer False Incrementing Primary Key
    Type string False Resource Type
    Id string False Resource Id
    Web string False URL to refer to resource
    Url string True CanonicalResource.url (if present)
    Version string True CanonicalResource.version (if present)
    Status string True CanonicalResource.status (if present)
    Date string True CanonicalResource.date (if present)
    Name string True CanonicalResource.name (if present)
    Title string True CanonicalResource.title (if present)
    Experimental string True CanonicalResource.experimental (if present)
    Realm string True derived from CanonicalResource.jurisdiction (if present)
    Description string True CanonicalResource.description (if present)
    Purpose string True CanonicalResource.purpose (if present)
    Copyright string True CanonicalResource.copyright (if present)
    CopyrightLabel string True CanonicalResource.copyrightLabel (if present)
    Json binary False JSON source for resource
    PropertiesCode System properties defined by CodeSystems in the IG
    Key integer False Incrementing Primary Key
    ResourceKey integer False Reference to Resources.Key
    Code string False CodeSystem.property.code
    Uri string True CodeSystem.property.uri
    Description string True CodeSystem.property.description
    Type string True CodeSystem.property.type
    ConceptsA list of all the codes defined in the code systems in the IG
    Key integer False Incrementing Primary Key
    ResourceKey integer False Reference to Resources.Key
    ParentKey integer True Reference tp Concepts.Key if not a root concept
    Code string True CodeSystem.concept.code
    Display string True CodeSystem.concept.display
    Definition string True CodeSystem.concept.definition
    ConceptPropertiesProperties defined in the concepts in the Concepts table
    Key integer False Incrementing Primary Key
    ResourceKey integer False Reference to Resources.Key
    ConceptKey integer False Reference to Concepts.Key
    PropertyKey integer True Reference to Properties.Key
    Code string True CodeSystem.concept.property.code
    Value string True CodeSystem.concept.property.code
    DesignationsDesignations defined in the concepts in the Concepts table
    Key integer False Incrementing Primary Key
    ResourceKey integer False Reference to Resources.Key
    ConceptKey integer False Reference to Concepts.Key
    UseSystem string True CodeSystem.concept.designation.use.system
    UseCode string True CodeSystem.concept.designation.use.code
    Lang string True CodeSystem.concept.designation.lang
    Value text True CodeSystem.concept.designation.value
    ConceptMappingsMappings Defined in ConceptMaps in the IG
    Key integer False Incrementing Primary Key
    ResourceKey integer False Reference to Resources.Key
    SourceSystem string True ConceptMap.group.source
    SourceVersion string True ConceptMap.group.sourceVersion
    SourceCode string True ConceptMap.group.element.code
    Relationship string True ConceptMap.group.element.target.relationship
    TargetSystem string True ConceptMap.group.target
    TargetVersion string True ConceptMap.group.targetVersion
    TargetCode string True ConceptMap.group.element.target.code
    ValueSet_CodesValueSet Expansion Table (flat expansions only) for ValueSets defined in this IG
    Key integer False Incrementing Primary Key
    ResourceKey integer False Reference to Resources.Key
    ValueSetUri string False ValueSet.url
    ValueSetVersion string False ValueSet.version
    System string False ValueSet.expansion.contains.system
    Version string True ValueSet.expansion.contains.version
    Code string False ValueSet.expansion.contains.code
    Display string True ValueSet.expansion.contains.display
    CodeSystemListList of all code systems for the IG. ViewType = 1 somewhat overlaps with the Resources table, but is more convenient for some uses
    CodeSystemListKey integer False Incrementing Primary Key
    ViewType integer False 1 = all code systems in IG. 2 = all code systems used directly by this ig. 3 = all code systems used by this ig and it's dependencies
    ResourceKey integer True Reference to Resources.Key (if from this IG)
    Url string True CodeSystem.url
    Version string True CodeSystem.version
    Status string True CodeSystem.status
    Name string True CodeSystem.name
    Title string True CodeSystem.title
    Description string True CodeSystem.description
    CodeSystemListOIDsOIDs for the Code Systems
    CodeSystemListKey integer False Reference to CodeSystemList.CodeSystemListKey
    OID string False OID for the code system
    CodeSystemListRefsAll resources found that reference the code system
    CodeSystemListKey integer False Reference to CodeSystemList.CodeSystemListKey
    Type string False ResourceType
    Id string False Resource Ig
    ResourceKey integer True Reference to Resources.Key (if from this IG)
    Title string True A human description of the resource - if known
    Web string True URL for reference to the resource
    ValueSetListList of all value sets for the IG. ViewType = 1 somewhat overlaps with the Resources table, but is more convenient for some uses
    ValueSetListKey integer False Incrementing Primary Key
    ViewType integer False 1 = all value sets in IG. 2 = all value sets used directly by this ig. 3 = all code systems used by this ig and it's dependencies
    ResourceKey integer True Reference to Resources.Key (if from this IG)
    Url string True ValueSet.url
    Version string True ValueSet.version
    Status string True ValueSet.status
    Name string True ValueSet.name
    Title string True ValueSet.title
    Description string True ValueSet.description
    ValueSetListOIDsOIDs for the Value Sets
    ValueSetListKey integer False Reference to ValueSetList.ValueSetListKey
    OID string False OID for the value set
    ValueSetListSystemsList of Code systems used by the value set
    ValueSetListKey integer False Reference to ValueSetList.ValueSetListKey
    URL string False A code system used by the value set
    ValueSetListSourcesList of stated source (= Code system source category)
    ValueSetListKey integer False Reference to ValueSetList.ValueSetListKey
    Source string False stated source ( Code system source category)
    ValueSetListRefsAll resources found that reference the value set
    ValueSetListKey integer False Reference to ValueSetList.ValueSetListKey
    Type string False Resource Type
    Id string False Resource.id
    ResourceKey integer True Reference to Resources.Key (if from this IG)
    Title string True A human description of the resource - if known
    Web string True URL for reference to the resource