Create a SQLite database and tables

Verified
Added by iNTERFACEWARE

This script automatically creates a SQLite database and then generates the tables specified in a VMD.

Source Code
-- 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
SQLITE_DB='demo.sqlite' 
-- Note: the vmd must be in XML format
VMD_FILE='demo.vmd'

-- this will create the database file if it does not exist
conn = db.connect{
   api=db.SQLITE,
   name=SQLITE_DB,
   live=true
}

function main(Data) 
   -- create tables and then read from master file to show tables exist
   CreateVMDTables()
   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 

-- read VMD and create SQLite table creation script 
-- note: the SQL create is ***SQLite*specific***
function CreateVMDTables()
   io.input(VMD_FILE)
   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
Description
This script automatically creates a SQLite database and then generates the tables specified in a VMD.
Usage Details

This code 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:

  • Load the project file, or paste the code into any Iguana component
    Note: If you paste in the code you will also need to add the sqlite module
  • Download the demo.vmd file and add it to the project
  • After running the code the test database will be created in the Iguana install directory