Last time I wrote about code generation using ART DECOR. This time we’ll look a some more code generation, and then consider various strategies for code generation.
In the previous post we saw how to generate HTML for a user interface for a transaction in ART DECOR. We can do the same for database code.
SQL
--DROP TABLE vs_measured_by CASCADE;
CREATE TABLE vs_measured_by (
vs_measured_by_id int NOT NULL PRIMARY KEY,
code varchar(255) NOT NULL,
codeSystem varchar(255) NOT NULL,
displayName varchar(255) NOT NULL
);
INSERT INTO vs_measured_by (vs_measured_by_id, code, codeSystem, displayName)
VALUES (1, 'P', '2.16.840.1.113883.3.1937.99.62.3.5.1', 'Patient');
INSERT INTO vs_measured_by (vs_measured_by_id, code, codeSystem, displayName)
VALUES (2, 'T', '2.16.840.1.113883.3.1937.99.62.3.5.1', 'Home care provider');
INSERT INTO vs_measured_by (vs_measured_by_id, code, codeSystem, displayName)
VALUES (3, 'H', '2.16.840.1.113883.3.1937.99.62.3.5.1', 'GP');
INSERT INTO vs_measured_by (vs_measured_by_id, code, codeSystem, displayName)
VALUES (4, 'M', '2.16.840.1.113883.3.1937.99.62.3.5.1', 'Personal network');
--DROP TABLE measurement_message CASCADE;
CREATE TABLE measurement_message (
measurement_message_id int NOT NULL PRIMARY KEY
);
--DROP TABLE measurement CASCADE;
CREATE TABLE measurement (
measurement_id int NOT NULL PRIMARY KEY
, measurement_message_id int NOT NULL REFERENCES measurement_message(measurement_message_id)
, weight float
, weight_unit char(96) NOT NULL
, weight_gain boolean
, length float
, length_unit char(96)
, datetime timestamp
, measured_by int REFERENCES vs_measured_by(vs_measured_by_id)
, comments varchar(255)
);
--DROP TABLE weight_gain_data CASCADE;
CREATE TABLE weight_gain_data (
weight_gain_data_id int NOT NULL PRIMARY KEY
, measurement_id int NOT NULL REFERENCES measurement(measurement_id)
, size_weight_gain float
, size_weight_gain_unit char(96)
, cause_weight_gain varchar(255)
);
--DROP TABLE person CASCADE;
CREATE TABLE person (
person_id int NOT NULL PRIMARY KEY
, measurement_message_id int NOT NULL REFERENCES measurement_message(measurement_message_id)
, name varchar(255) NOT NULL
, bsn varchar(255) NOT NULL
, date_of_birth varchar(255)
, number_of_children varchar(255)
);
XSL
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs" version="2.0" >
<xsl:output method="text" indent="no"/>
<xsl:template match="/">
<xsl:apply-templates select="//valueSet"/>
<xsl:apply-templates select="@*|node()"/>
</xsl:template>
<xsl:template match="dataset">
--DROP TABLE <xsl:value-of select="@shortName"/> CASCADE;
CREATE TABLE <xsl:value-of select="@shortName"/> (
<xsl:value-of select="@shortName"/>_id int NOT NULL PRIMARY KEY
);
<xsl:apply-templates/>
</xsl:template>
<xsl:template match="concept[@type='group']">
--DROP TABLE <xsl:value-of select="implementation/@shortName"/> CASCADE;
CREATE TABLE <xsl:value-of select="implementation/@shortName"/> (
<xsl:value-of select="implementation/@shortName"/>_id int NOT NULL PRIMARY KEY
<xsl:if test="local-name(..) = 'dataset'">, <xsl:value-of select="../@shortName"/>_id int NOT NULL REFERENCES <xsl:value-of select="../@shortName"/>(<xsl:value-of select="../@shortName"/>_id)</xsl:if>
<xsl:if test="local-name(..) = 'concept'">, <xsl:value-of select="../implementation/@shortName"/>_id int NOT NULL REFERENCES <xsl:value-of select="../implementation/@shortName"/>(<xsl:value-of select="../implementation/@shortName"/>_id)</xsl:if>
<xsl:apply-templates select="concept[@type='item']"/>
);
<xsl:apply-templates select="concept[@type='group']"/>
</xsl:template>
<xsl:template match="concept[@type='item']">
, <xsl:value-of select="implementation/@shortName"/>
<xsl:choose>
<xsl:when test="valueDomain/@type='code'"> int REFERENCES <xsl:value-of select="translate(valueSet/@name, '-', '_')"/>(<xsl:value-of select="translate(valueSet/@name, '-', '_')"/>_id)</xsl:when>
<xsl:when test="valueDomain/@type='boolean'"> boolean</xsl:when>
<xsl:when test="valueDomain/@type='decimal'"> float</xsl:when>
<xsl:when test="valueDomain/@type='datetime'"> timestamp</xsl:when>
<xsl:when test="valueDomain/@type='quantity'"> float
, <xsl:value-of select="implementation/@shortName"/>_unit char(96)</xsl:when>
<xsl:otherwise> varchar(255)</xsl:otherwise>
</xsl:choose>
<xsl:if test="@isMandatory='true'"> NOT NULL</xsl:if>
</xsl:template>
<xsl:template match="valueSet">
--DROP TABLE <xsl:value-of select="translate(@name, '-', '_')"/> CASCADE;
CREATE TABLE <xsl:value-of select="translate(@name, '-', '_')"/> (
<xsl:value-of select="translate(@name, '-', '_')"/>_id int NOT NULL PRIMARY KEY,
code varchar(255) NOT NULL,
codeSystem varchar(255) NOT NULL,
displayName varchar(255) NOT NULL
);
<xsl:for-each select="conceptList/concept">
INSERT INTO <xsl:value-of select="translate(../../@name, '-', '_')"/> (<xsl:value-of select="translate(../../@name, '-', '_')"/>_id, code, codeSystem, displayName)
VALUES (<xsl:value-of select="@localId"/>, '<xsl:value-of select="@code"/>', '<xsl:value-of select="@codeSystem"/>', '<xsl:value-of select="name"/>');
</xsl:for-each>
</xsl:template>
<xsl:template match="@*|node()">
<xsl:apply-templates select="@*|node()"/>
</xsl:template>
</xsl:stylesheet>
XML
<dataset id="2.16.840.1.113883.3.1937.99.62.3.1.1" effectiveDate="2012-05-30T11:32:36" statusCode="draft" versionLabel="" transactionId="2.16.840.1.113883.3.1937.99.62.3.4.2" transactionEffectiveDate="2012-09-05T16:59:35" shortName="measurement_message"><!--
This is a view of the transaction view of Measurement message containing DECOR specifications for a transaction or dataset in a single, hierarchical view.
All inheritances are resolved, for transactions the dataset is filtered for those concepts occurring in the transaction.
Valuesets are contained within the concept for easy reference.
Xpaths are calculated on a best effort basis. The should be considered a starting point for application logic, not an endpoint. -->
<name language="en-US">Measurement message</name>
<desc language="en-US">Measurement message test</desc>
<concept minimumMultiplicity="0" maximumMultiplicity="*" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.1" statusCode="cancelled" effectiveDate="2012-05-30T11:32:36" type="group">
<name language="en-US">Measurement</name>
<desc language="en-US">Measurement of body weight on a specific date</desc>
<implementation shortName="measurement">
<templateLocation></templateLocation>
</implementation>
<concept minimumMultiplicity="1" maximumMultiplicity="1" conformance="M" isMandatory="true" id="2.16.840.1.113883.3.1937.99.62.3.2.3" statusCode="cancelled" effectiveDate="2011-01-28T00:00:00" type="item">
<name language="en-US">Weight</name>
<desc language="en-US">Weight measurement</desc>
<implementation shortName="weight">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="quantity">
<property unit="kg" minInclude="25" maxInclude="240"></property>
</valueDomain>
<terminologyAssociation conceptId="2.16.840.1.113883.3.1937.99.62.3.2.3" code="27113001" codeSystem="2.16.840.1.113883.6.96" displayName="Body weight"></terminologyAssociation>
</concept>
<concept minimumMultiplicity="0" maximumMultiplicity="1" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.13" statusCode="cancelled" effectiveDate="2011-01-28T00:00:00" type="item">
<name language="en-US">Weight gain</name>
<desc language="en-US">Is there a weight gain?</desc>
<implementation shortName="weight_gain">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="boolean"></valueDomain>
</concept>
<concept minimumMultiplicity="0" maximumMultiplicity="1" conformance="C" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.18" statusCode="cancelled" effectiveDate="2011-01-28T00:00:00" type="group">
<name language="en-US">Weight gain data</name>
<desc language="en-US">Weight gain data</desc>
<implementation shortName="weight_gain_data">
<templateLocation></templateLocation>
</implementation>
<condition minimumMultiplicity="1" maximumMultiplicity="1" conformance="M" isMandatory="true">Bij gewichtstoename</condition>
<condition minimumMultiplicity="" maximumMultiplicity="" conformance="NP" isMandatory=""></condition>
<concept minimumMultiplicity="0" maximumMultiplicity="1" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.19" statusCode="cancelled" effectiveDate="2011-01-28T00:00:00" type="item">
<name language="en-US">Size weight gain</name>
<desc language="en-US"></desc>
<implementation shortName="size_weight_gain">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="quantity">
<property unit="gram"></property>
</valueDomain>
</concept>
<concept minimumMultiplicity="0" maximumMultiplicity="1" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.20" statusCode="cancelled" effectiveDate="2011-01-28T00:00:00" type="item">
<name language="en-US">Cause weight gain</name>
<desc language="en-US"></desc>
<implementation shortName="cause_weight_gain">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="string"></valueDomain>
</concept>
</concept>
<concept minimumMultiplicity="0" maximumMultiplicity="1" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.15" statusCode="cancelled" effectiveDate="2011-01-28T00:00:00" type="item">
<name language="en-US">Length</name>
<desc language="en-US">Body length</desc>
<implementation shortName="length">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="quantity">
<property unit="m" minInclude="0" maxInclude="3" fractionDigits="2"></property>
<property unit="cm" minInclude="0" maxInclude="300" fractionDigits="0!"></property>
</valueDomain>
</concept>
<concept minimumMultiplicity="0" maximumMultiplicity="1" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.2" statusCode="cancelled" effectiveDate="2011-01-28T00:00:00" type="item">
<name language="en-US">Datetime</name>
<desc language="en-US">Date of the measurement</desc>
<implementation shortName="datetime">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="datetime"></valueDomain>
<terminologyAssociation conceptId="2.16.840.1.113883.3.1937.99.62.3.2.2" code="DM" codeSystem="2.16.840.1.113883.3.1937.99.62.3.5.2" displayName="Datum meting"></terminologyAssociation>
</concept>
<concept minimumMultiplicity="0" maximumMultiplicity="1" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.5" statusCode="cancelled" effectiveDate="2011-01-28T00:00:00" type="item">
<name language="en-US">Measured by</name>
<desc language="en-US">Person who performed the measurement</desc>
<implementation shortName="measured_by">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="code">
<conceptList id="2.16.840.1.113883.3.1937.99.62.3.2.5.0">
<concept id="2.16.840.1.113883.3.1937.99.62.3.2.5.1">
<name language="en-US">Patient</name>
</concept>
<concept id="2.16.840.1.113883.3.1937.99.62.3.2.5.2">
<name language="en-US">Home care provider</name>
</concept>
<concept id="2.16.840.1.113883.3.1937.99.62.3.2.5.3">
<name language="en-US">GP</name>
</concept>
<concept id="2.16.840.1.113883.3.1937.99.62.3.2.5.4">
<name language="en-US">Personal network</name>
</concept>
</conceptList>
</valueDomain>
<valueSet id="2.16.840.1.113883.3.1937.99.62.3.11.5" effectiveDate="2012-07-25T15:22:56" name="demo1-meting-door" displayName="demo1-meting-door" statusCode="draft">
<terminologyAssociation conceptId="2.16.840.1.113883.3.1937.99.62.3.2.5.0" valueSet="demo1-meting-door"></terminologyAssociation>
<conceptList id="2.16.840.1.113883.3.1937.99.62.3.2.5.0">
<concept localId="1" code="P" codeSystem="2.16.840.1.113883.3.1937.99.62.3.5.1" displayName="Patiënt" level="0" type="A">
<name language="en-US">Patient</name>
</concept>
<concept localId="2" code="T" codeSystem="2.16.840.1.113883.3.1937.99.62.3.5.1" displayName="Thuiszorg" level="0" type="S">
<name language="en-US">Home care provider</name>
</concept>
<concept localId="3" code="H" codeSystem="2.16.840.1.113883.3.1937.99.62.3.5.1" displayName="Huisarts" level="0" type="D">
<name language="en-US">GP</name>
</concept>
<concept localId="4" code="M" codeSystem="2.16.840.1.113883.3.1937.99.62.3.5.1" displayName="Family care" level="0" type="L">
<name language="en-US">Personal network</name>
</concept>
</conceptList>
</valueSet>
<terminologyAssociation conceptId="2.16.840.1.113883.3.1937.99.62.3.2.5" code="MD" codeSystem="2.16.840.1.113883.3.1937.99.62.3.5.2" displayName="Meting door"></terminologyAssociation>
</concept>
<concept minimumMultiplicity="0" maximumMultiplicity="*" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.17" statusCode="cancelled" effectiveDate="2012-09-09T16:06:28" type="item">
<name language="en-US">Comments</name>
<desc language="en-US"></desc>
<implementation shortName="comments">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="text"></valueDomain>
</concept>
</concept>
<concept minimumMultiplicity="1" maximumMultiplicity="1" conformance="M" isMandatory="true" id="2.16.840.1.113883.3.1937.99.62.3.2.6" statusCode="draft" effectiveDate="2012-05-30T14:18:23" type="group">
<name language="en-US">Person</name>
<desc language="en-US">Person</desc>
<implementation shortName="person">
<templateLocation></templateLocation>
</implementation>
<concept minimumMultiplicity="1" maximumMultiplicity="1" conformance="M" isMandatory="true" id="2.16.840.1.113883.3.1937.99.62.3.2.4" statusCode="draft" effectiveDate="2012-05-30T14:18:23" type="item">
<name language="en-US">Name</name>
<desc language="en-US">Name of the person</desc>
<implementation shortName="name">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="complex"></valueDomain>
</concept>
<concept minimumMultiplicity="1" maximumMultiplicity="1" conformance="M" isMandatory="true" id="2.16.840.1.113883.3.1937.99.62.3.2.7" statusCode="draft" effectiveDate="2012-05-30T14:18:23" type="item">
<name language="en-US">BSN</name>
<desc language="en-US">Dutch Social Security Number</desc>
<implementation shortName="bsn">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="identifier"></valueDomain>
</concept>
<concept minimumMultiplicity="0" maximumMultiplicity="1" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.8" statusCode="draft" effectiveDate="2012-05-30T14:18:23" type="item">
<name language="en-US">Date of birth</name>
<desc language="en-US">Date of birth of the person</desc>
<implementation shortName="date_of_birth">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="date"></valueDomain>
</concept>
<concept minimumMultiplicity="0" maximumMultiplicity="1" isMandatory="false" id="2.16.840.1.113883.3.1937.99.62.3.2.14" statusCode="deprecated" effectiveDate="2012-05-30T14:18:23" type="item">
<name language="en-US">Number of children</name>
<desc language="en-US">Number of children</desc>
<implementation shortName="number_of_children">
<templateLocation></templateLocation>
</implementation>
<valueDomain type="count"></valueDomain>
</concept>
</concept>
</dataset>
Like in the HTML generation, the XSLT used to generate the code operates on the output of RetrieveTransaction, and is rather small itself. And, like before, this is not a complete code generator yet: not all datatypes are included etc., so only use this as the start for your own code generators. This generated SQL (tested on PostgreSQL) implements the following database model:
Each concept group becomes a table, value sets (vs-measured-by) get their own table, which is populated by the rows in the value set.
If we hook up some middleware, which can be pretty generic, the middleware, the HTML UI and the database would be a complete application.
Some caveats for real-world usage:
- It’s probably better to derive the SQL datatypes from the HL7v3 datatypes in RetrieveTransaction (only possible when HL7v3 is the target, and specs are complete). For more information on generating SQL from HL7v3 datatypes, see RIMBAA_MGRID_HL7v3_Datatypes.pdf for a thorough approach.
- Value sets here get their own table. Another approach is to have one table for all value sets, with extra columns for value set name or id. Take care: the approach I’ve chosen uses artificial primary keys in de value set table (with values 1, 2, 3…). This works well for a single version, but what’s constant over several versions of a message is the combination of code and codeSystem. So if you follow my approach, make sure to keep the primary keys constant over time.
- I simply generated a separate table for each concept group. It’s easy to improve over that approach: groups which are 0..1 or 1..1 do not need their own table. Basically, the generated database is split in more tables than necessary.
The next question is what the best strategies for code generation along the lines I’ve sketched are. Of course it’s possible to generate an entire application, but that would be suboptimal. ART DECOR (usually, not necessarily) models interchange, so messaging between applications, and not static information models. The requirements for a database are as a rule not covered by interchange models. So while this approach is possible, it would probably need an augmented underlying model.
A better approach is to use code generation as a messaging layer between your own application and the outside world. Parse incoming messages and store them in temporary tables. RetrieveTransaction already contains XPath expressions which point to the right locations in the incoming XML (not 100% complete though, so use as a starting point). Then populate your own DB from the temporary tables, and empty the latter.
This is a very flexible model: when there is a new version of the messaging standard, simply re-generate the insulating layer with generated tables. This will enable you to read new messages without too much ado. The conversion to the proprietary DB will need some updating, of course: there is a new version of the message, so things will have changed. Still, the prorietary DB will be insulated from many changes to the messaging format itself, and the logic which needs updating will be minimized.
Another strategy is to use code generation to generate form parts which do not have corresponding fields in your own database. Such a strategy would involve:
- generate an HTML form from RetrieveTransaction;
- prepopulate the form with all the data which already is in your own database;
- present the form to the user (embedded within your own application);
- have the user complete the information which is not in your database;
- generate a message, and send it.
This strategy corresponds well to cases where for instance generic patient data are already in your database, but additional information is needed for some report which needs to be sent. Again, if a new version of the report and message is coming along, this approach ensures only minimal changes to the underlying logic.
Code generation is already used in many projects in the Netherlands, both to generate UI and database logic, as well as for data extraction. All in all, ART DECOR provides all the tools needed to leverage your applications with code generation, and thus implement exchanges in a quick and robust fashion.
