The Internet belongs to everyone. Let’s keep it that way.

Protect Net Neutrality
Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


An Introduction to SQLite and Cloverleaf

No description

James Cobane

on 4 January 2015

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of An Introduction to SQLite and Cloverleaf

An Introduction to SQLite and Cloverleaf
Presented by Jim Cobane

• What is SQLite?
• Use Case(s)
• Review Examples
• Hands-on Exercises/Challenges
• Tools & Resources

Use Cases
• Need to perform lookups against a large volume database or a database that may be dynamic
• Unlike Cloverleaf Translation look-up tables, the SQLite database can have multiple column tables and updates can be made/read in real-time.
• Table/database requires frequent updating or is maintained using external data
• SMAT under 6.1 uses SQLite replacing the current file-based SMAT
• SQLite extensions are built into Tcl making SQLite functionality easy to invoke
• This is being used for JVHL SelfPay filtering

What is SQLite?
• SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures.
• The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects -
– (Apple uses SQLite for many functions within Mac OS X, including Apple Mail, Safari, and in Aperture. Apple uses SQLite in the iPhone and in the iPod touch and in iTunes software.)

Basic usage within Tcl:
– sqlite

The sqlite command opens the database named in the second argument. If the database does not already exist, the default behavior is for it to be created automatically (though this can be changed by using the "-create false" option). The sqlite command always creates a new Tcl command to control the database. The name of the new Tcl command is given by the first argument (dbcmd)
The name of the database is usually just the name of a disk file in which the database is stored. If the name of the database is the special name ":memory:" then a new database is created in memory. If the name of the database is an empty string, then the database is created in an empty file that is automatically deleted when the database connection closes.
i.e. sqlite DBCMD mydatabase
The “eval” method
The most useful dbcmd method is "eval". The eval method is used to execute SQL on the database. The syntax of the eval method looks like this:
• DBCMD eval sql ?array-name? ?script?
– The job of the eval method is to execute the SQL statement or statements given in the second argument (sql)
– For example, to create a new table in a database, you can do this:
• sqlite DBCMD ./testdb
• DBCMD eval {CREATE TABLE mytable (myKey int primary key, myValue text)}
– The above code creates a new table named mytable with columns myKey and myValue
– Try it from the command line; change directory to your ‘data’ directory (after creating your train
site) and invoke Tcl (hcitcl or tcl), then type the commands in…
The “eval” method – cont’d
To populate the table:
DBCMD eval {INSERT INTO mytable VALUES(1,'one')}
DBCMD eval {INSERT INTO mytable VALUES(2,'two')}
DBCMD eval {INSERT INTO mytable VALUES(3,'three')}
To update a value in the table:
DBCMD eval {UPDATE mytable SET myValue='THREE' where myKey=3}
To select from the database
DBCMD eval {SELECT * FROM mytable}
DBCMD eval {SELECT * FROM mytable where myKey=3}
DBCMD eval {SELECT myKey FROM mytable where myValue='THREE'}

The “eval” method – cont’d
When selecting from the database, you can process the resulting rows one at a time if you include additional scripting portion (the { } section after the select). If no array name is given (see next bullet), the values are assigned into variables with the name of the column
DBCMD eval {SELECT * FROM mytable} {
puts “myKey is:>$myKey<“
puts “myValue is:>$myValue<“
myKey is:>1<
myValue is:>one<
myKey is:>2<
myValue is:>two<
myKey is:>3<
myValue is:>THREE<

The “eval” method – cont’d
When specifying an array name, for each column in a row of the result, the name of that column is used as an index into the array. The value of the column is stored in the corresponding array entry. The special array index * is used to store a list of column names in the order that they appear.
DBCMD eval {SELECT * FROM mytable} values {
parray values
• Yields:
values(*) = myKey myValue
values(myKey) = 1
values(myValue) = one
values(*) = myKey myValue
values(myKey) = 2
values(myValue) = two
values(*) = myKey myValue
values(myKey) = 3
values(myValue) = THREE

The “eval” method – cont’d
When the script portion is omitted, query results are returned as a list of column values. Thus, if a query requests 2 columns and there are 3 rows matching the query, then the returned list will contain 6 elements. i.e. -
set myList [DBCMD eval {SELECT * FROM mytable}]
• Yields a value of $myList as:
1 one 2 two 3 THREE

You could then loop through the resulting list:
foreach {myKey myValue} $myList {
puts “$myKey”
puts “$myValue”

Creating/updating a database from a flat file
– this example script reads a .CSV file and creates/updates a table with a Primary Key, Value and Date/time of the insert/update
Maintaining/Purging a database
– this example script purges records from the database that are older than 90 days (based on the stored recDate value)
Storing to a database in a tps proc
– this example proc shows the storing of an Account number (as primary key) along with the associated MRN and date of update. This proc is used in the from_his -> to_acme configuration for the exercise.
Selecting from a database within an xlate
– this example demonstrates the selection from an SQLite database within the context of an Xlate
Hands-On Exercise
• Scenario:
The vendor ‘Acme’ receives ADT and sends results. The Acme system can only store one identifier; the identifier needs to be at the encounter level; i.e. Account#. The EMR that receives the results from Acme requires the MRN. Since Acme can only store the Account#, we need a way to re-attach the MRN associated with the Account in the data sent to the EMR. Thus, we are utilizing an SQLite database to store the Account and MRN which will allow the re-population of the MRN into the message back to the EMR. We will do this as part of translation, within the context of an Xlate.
Hands-On Exercise Set-up
In 6.1, create a site named ‘trainxx’ where xx is your first & last initial
Once you are in your newly created site, copy the data files (previously sent to you via e-mail) into your site. Save the '' file as 'SQLiteExercise
.box' where
are your initials
Utilizing the “BOX Manager” tool, import and deploy the previously supplied .box file into your site. This should provide the base configuration to work with.
Utilizing the ‘myResult.xlt’, make the necessary changes to implement the database lookup logic to value the MRN field (PID:3) with the MRN
Full transcript