Modules

sqlite.lua

Verified

Added by iNTERFACEWARE

A module to create a SQLite database and generate the tables specified in a VMD

Source Code

local sqlite = {}

-- read VMD and create SQLite table creation script
-- note: the SQL create is ***SQLite*specific***
function sqlite.CreateVMDTables(Db, Vmd)
   -- this will create the database file if it does not exist
   conn = db.connect{
      api=db.SQLITE,
      name=Db,
      live=true
   }

   io.input(Vmd)
   vmd=io.read("*all")
   X=xml.parse{data=vmd}
   trace(X.engine.config:child('table',1):child('column',1).config.is_key[1]:nodeValue())
   
   for i=1,X.engine.global:childCount('table') do
      local Sql=''
      Sql=Sql..'CREATE TABLE IF NOT EXISTS '
      Sql=Sql..X.engine.global:child('table',i).name:nodeValue()
      Sql=Sql..'\n(\n'
      for j=1,X.engine.global:child('table',i):childCount('column') do
         Sql=Sql..X.engine.global:child('table',i):child('column',j).name:nodeValue()
         -- if primary key
         if X.engine.config:child('table',i):child('column',j).config.is_key[1]:nodeValue()=='True' then
            Sql=Sql..' TEXT(255) NOT NULL PRIMARY KEY'
         else
            Sql=Sql..' TEXT(255) NULL'
         end
         -- last field
         if j~=X.engine.global:child('table',i):childCount('column') then
            Sql=Sql..',\n'
         end
      end
      Sql=Sql..'\n);\n\n'
      trace(Sql)
      conn:execute{sql=Sql,live=true}
   end   
end

return sqlite

Description

A module to create a SQLite database and generate the tables specified in a VMD

Usage Details

This module takes advantage of the fact that SQLite will create a database file if one does not exist. The script simply opens a database connection which creates the database (if it does not exist). Then creates creates the tables defined in the demo.vmd.

You can customize the code by changing the database name or using a different VMD file.

How to use the code:

Use an Iguana script component
Create the sqlite module and paste the code into it
Use local auth = require 'sqlite' at the top of your script
Use the sqlite.CreateVMDTables() function to create a SQLite database and tables

Here is some sample code for main(), that will create a test database in the Iguana install directory:

local sqlite = require 'sqlite'

-- change the locations of the VMD and DB files as required
-- in this case (no path specified) the path defaults to the Iguana install directory
-- NOTE: you must use an absolute path for the VMD if it is not in the Iguana install directory
local SQLITE_DB='demo.sqlite'
-- NOTE: the vmd must be in XML format (not binary format)
local VMD_FILE='demo.vmd'

function main(Data)
   -- create tables and then read from master file to show tables exist
   sqlite.CreateVMDTables(SQLITE_DB, VMD_FILE)
   conn:query('SELECT * FROM sqlite_master')
   
   -- insert data and then read it
   conn:execute{sql='DELETE FROM patient',live=true}
   conn:execute{
      sql=[[INSERT INTO patient(Id,GivenName, LastName, Race)
      VALUES(1,"Fred", "Smith", "Martian")]],
      live=true}
   conn:query('SELECT * FROM patient')
   
   conn:execute{sql='DELETE FROM kin',live=true}
   conn:execute{
      sql=[[INSERT INTO kin(Relationship, FirstName, LastName)
      VALUES("wife", "Ellen", "Smith")]],
      live=true}
   conn:query('SELECT * FROM kin')
end

More Information

• Creating a SQLite database
• Create a SQLite named “test” DB using the demo.vmd
• Create a SQLite database and tables

Code Repository

sqlite.lua