Generate database tables for an HL7 interface

Using the genDb.lua module (code at the bottom of the page).
Note: This code is only a proof-of-concept only so we did not put it in the code repository.

This article shows how to generate a create table script from an HL7 message.

The code parses an HL7 message and generates a table for each Segment. The SQL for creating the tables is saved in a text file with a .sql extension. The database structure produced is far from ideal, and needs to be tweaked. I suggest adding a table that flags elements as tables, and maps them to (better) table names.

Using the module is simple, just a single function call – with the file name, and the parsed HL7 message as parameters:

Source Code

Here is the source code for the genDb module. To use it:

1. Create a new shared module called “genDb ” and copy paste in the code below.
2. Add the code require(‘genDb ‘) at the top of the main module.
3. Test using sample data.

Code for main:

require "genDb"

function main(Data)
   local msg, Name = hl7.parse{vmd = 'demo.vmd', data = Data}
   local SQL = genDb.genDbScript(msg,[[C:\Program Files\iNTERFACEWARE\Iguana\hl7doc\CreateDb]])
   print(SQL)
end

Code for genDb module:

genDb={}

TblInfo={}

function genDb.genDbScript(Msg,File)
   local T=genDb.parseMsg(Msg)
   local S='-- Generated table creation script\n'
   S=genDb.makeDbScript(T,S,0)
   S=S:sub(1,-4)..'\n);'
   genDb.writeFile(File..'.sql',S)
   return S
end

function genDb.makeDbScript(Tree,Script,Level,TblName)
   if Level==0 then
      if Tree.type~='segment_group'and
            Tree.type~='segment_repeating' and
            Tree.type~='message' then
         TblName=Tree.name:gsub(' ','')
         TblName=genDb.delSymbols(TblName)
         TblInfo[TblName]={}
         --Script=Script..'\nDROP TABLE IF EXISTS '
         --Script=Script..TblName..';\n\t'
         Script=Script..'\nCREATE TABLE '
         Script=Script..TblName..'\n(\n\t'
      end
   else
      if Tree.type=='segment'then
         -- WARNING - condition depends on string format
         if Script:sub(-1,-1)=='\t' then
            Script=Script:sub(1,-4)..'\n);\n\n'
         end
         TblName=Tree.name:gsub(' ','')
         TblName=genDb.delSymbols(TblName)
         TblInfo[TblName]={}
         --Script=Script..'\nDROP TABLE IF EXISTS '
         --Script=Script..TblName..';\n\t'
         Script=Script..'\nCREATE TABLE '
         Script=Script..TblName..'\n(\n\t'
      else
         if TblName then
            local F=Tree.name:gsub(' ','')
            F=genDb.delSymbols(F)
            if TblInfo[TblName][F]then
               F=Tree.parentname..'_'..F
            end
            Script=Script..F..' VARCHAR(255),\n\t'
            TblInfo[TblName][F]=true
         end
      end
   end      
   Level=Level+1
   for i in genDb.pairsByNumKeys(Tree) do
      Script=genDb.makeDbScript(Tree[i],Script,Level,TblName)
   end
   return Script
end

function genDb.writeFile(Name, Content)
   io.output(Name)
   io.write(Content)
   io.close()
end

function genDb.parseMsg(Msg,Doc,CompName,ParentName)
   if Doc == nil then -- root node
      Doc ={}
      Doc.type=Msg:nodeType()
      Doc.number=1
      Doc.name=Msg:nodeName()
      Doc.compname=Msg:nodeName():gsub(' ','')
      Doc.value=Msg:S()
      Doc.sub=true
      if #Msg~=0 then
         genDb.parseMsg(Msg,Doc,Doc.compname,Doc.name:gsub(' ',''))
      end
   else
      for i=1, #Msg do
         if genDb.isFieldPresent(Msg[i]) then
            Doc[i]={}
            Doc[i].type=Msg[i]:nodeType()
            Doc[i].number=i
            Doc[i].name=Msg[i]:nodeName()
            Doc[i].compname=CompName..'_'..Msg[i]:nodeName():gsub(' ','')
            Doc[i].parentname=ParentName
            Doc[i].value=Msg[i]:S()
            if #(Msg[i])~=0 then
               Doc[i].sub=true
               genDb.parseMsg(Msg[i],Doc[i],Doc[i].compname,Doc[i].name:gsub(' ',''))
            else
               Doc[i].sub=false
            end
         end
      end
   end
   return Doc
end

function genDb.isFieldPresent(Field)
   if Field:isNull() then
      return false
   else
      return true
   end
end

function genDb.SegmentFilter(Segment, SegName)
   if Segment:nodeName() == SegName then
      return true
   end
   return false
end

function genDb.delSymbols(String)
   -- brackets and escape used for chars that have special meaning to gsub() function
   local C={'-','/','~','\\','@','#',[[\$]],[[\%]],[[\^]],'&','*',[[\(]],[[\)]],'|','=','+','?','\t'}
   for i,v in ipairs(C) do
      String=String:gsub(C[i],'_')
   end
   return String
end

-- iterator sorts numbers and ignores non-numeric keys
function genDb.pairsByNumKeys(t, f)
   local a = {}
   for n,v in pairs(t) do
      if tonumber(n) then table.insert(a, n) end
   end
   table.sort(a, f)
   local i = 0      -- iterator variable
   local iter = function ()   -- iterator function
      i = i + 1
      if a[i] == nil then return nil
      else return a[i], t[a[i]]
      end
   end
   return iter
end

function node.S(ANode)
   return tostring(ANode)
end

Possible modifications:

• Add a table with extra information to tweak the database structure
• Mark elements to be generated as tables
• Map elements to be generated as tables to (better) table names
• The current code creates a SQL script for a single HL7 message only, you may want to tweak the code to parse multiple messages. This would enable it to generate fields for optional data (not found in all messages)
• Automatically import data
• Generate code to map HL7 data to the generated table structure, and save to file.
• Use the generated mappings as the basis for another channel to import the data
• If possible use dofile() to to directly execute the generated mapping code
• Connect to the database and run the table create script automatically
• NOTE: Drop table statements were in included in the script (for testing) and then commented out