Database to HL7 migrating a legacy system

<strong>Test</strong>: Generate Test Data

To be able to regression test this new Lua based interface I needed to have some sample input data to test with.

Obviously I could not get my hands on the real confidential data so instead I wrote a module called dbfill to generate some fake data and pushed it into the database. You will need to create a dummy channel and run the code in a From or To Translator component. You can delete the channel after the data is created.

This code shows the usage of the routine:

require 'dbfill'

local conn = db.connect{
   api=db.SQLITE, 
   name='test'
}

function main()
   -- use to delete all the data first if you bad data
   -- conn:execute{sql=[[DELETE FROM NHCCVIS]], live = true}
   -- conn:execute{sql=[[DELETE FROM NHCCVISREPORT]], live = true}

   dbfill.generateDummySourceData('CVISOutbound.vmd', 'NHCCVIS',conn)

   -- confirm the data was generated
   conn:query('SELECT * FROM NHCCVIS')
   conn:query('SELECT * FROM NHCCVISREPORT')
end

Note: Use the SELECT queries to confirm that the dummy data was created. If you have more than 20 rows returned it means have some dummy row(s) from earlier, so you will need to use the DELETE statements to clear the tables.

The routine takes the vmd file, database connection and the name of the message to generate sample data for it.

This is the source code to the module:

dbfill = {}

local function trace(a,b,c,d) return end

local function RandomDate(Modifier)
   local T = 40700 - Modifier
   trace(T)
   return os.ts.date("%Y-%m-%dT%H:%M:%S", T)
end

local function RandomString(V, RowIndex)
   return V:nodeName()..RowIndex
end

local function FillValue(R, i, RowIndex)
   local T = R[i]:nodeType()
   if T == 'datetime' then
      R[i] = RandomDate(RowIndex*i)   
   elseif T == 'string' then 
      R[i] = RandomString(R[i], RowIndex)
   else
      error('Need to handle integers and doubles')
   end
end

local function MakeRow(T,RowIndex)
   for i =1,#T do
      FillValue(T, i, RowIndex)   
   end
   return T
end

local function makeDummyDatabase(Vmd, Name)
   local T = db.tables{vmd=Vmd, name=Name}
   for i =1,20 do
      MakeRow(T.NHCCVISReport[i], i)
   end
   return T
end

local function ColumnList(Row)
   local L = ''
   for i =1, #Row-1 do
      L = L..Row[i]:nodeName()..',\n'
   end
   L = L..Row[#Row]:nodeName()
   return L
end

local function generateSql(T, DB)
   for TableIndex=1,#T do
      local C = ColumnList(T[TableIndex][1])
         ..", MESSAGE_ID, PARENT_ID"
      for RowIndex = 1, #T[TableIndex] do
         local Row = T[TableIndex][RowIndex]
         local S = 'REPLACE INTO '..T[TableIndex]:nodeName()
         ..'('..C..')\n VALUES('
         for ColumnIndex =1,#Row-1 do
            S = S..'\"'..Row[ColumnIndex]..'\",\n'
         end
         S = S..'\"'..Row[#Row]..'\"\n'
         S = S..',\"'..RowIndex..'\"\n'
         S = S..',\"'..RowIndex..'\")'
         trace(S)
         DB:execute{sql=S, live=true}
      end
   end
end

local function InsertMessageRow(Count, MessageName, DB) 
   for i=1, Count do
      local S = 'REPLACE INTO '..MessageName..'(MESSAGE_ID, STATUS)'..' VALUES('..i..",'W')"
      DB:execute{sql=S, live=true}
   end
end

function dbfill.generateDummySourceData(Vmd, MessageName, D)
   local T = makeDummyDatabase(Vmd, MessageName)
   generateSql(T, D)
   InsertMessageRow(#T.NHCCVISReport, MessageName, D)
end

function dbfill.SaveMessage(Base, Msg, Data)
   local FileName = 'D:\\temp\\'..Base
                 ..Msg.MSH[10]..'.txt'
   trace(FileName)
   local F = io.open(FileName, "w")
   F:write(Data)
   F:close()
end

function dbfill.LoadMessage(Base, Msg)
   local FileName = 'D:\\temp\\'..Base
                 ..Msg.MSH[10]..'.txt'
   trace(FileName)
   local F = io.open(FileName, "r")
   return F:read("*a")
end

function dbfill.CompareMessage(Data)
   local Out = hl7.parse{vmd='CVISOutbound.vmd', data=Data}
   local Orig = dbfill.LoadMessage('orig', Out)
   return hl7.parse{vmd='CVISOutbound.vmd', data=Orig}
end

The code uses the SQLite specific REPLACE command which merges the data into the source database, otherwise errors would occur the second time the code is run. The code assumes that there is only one table per message and only covers string and date/time fields. If you would like the code to be extended to handle other databases and data types let us know.

Notice that the values including the dates are deterministically generated, they are always the same each time, this is important in order to be able to check between runs.

Next Step?

Now we’ve created some test data. The next step is to make the outputs from the old and new channels comparable.

Leave A Comment?