Scientists in their quest for truth often need to catalog and parse large amounts of data. Desktop databases are ideal for this purpose. A reputable package will contain a form and report designer, scripting language, database back end, and a method for interfacing with other software and equipment. Over the years I have experimented with many systems, including the RAD (Rapid Application Development) environment Superbase ( late 1980s early 90s), JAVA and a variety of object oriented databases (POET, ObjectStore and the JDO from Sun; late 90s early 2000s) and more recently Lisp and ObjectPrevalence. Though I quickly gained an appreciation of the LISP language working with Common Lisp, the limited number of libraries, poor documentation, small user base, and lack of a GUI did not meet all my requirements. Clojure promises to address many of these issues, but is still a work in progress. My experience with the JDEE (Java Development Environment for EMACS) familiarized me with EMACS Lisp. The only shortcoming of EMACS is the lack of a database. Enter SQLITE.
SQLITE is a a software library that implements a self-contained, serverless, zero-configuration, transactional SQL relational database engine. Installation and configuration are simple, and an included executable allows SQLITE to run as a process within EMACS. With the widget interface of EMACS, you have all you need to create, query and report data. Setup is as follows:
Installing SQLITE
Download and unzip SQLITE into a directory. I used c:\program files\sqlite3. This path will need to be referred to as c:\progra~1\sqlite3 in your lisp code and any batch files. Be sure to also download the associated executable file, which provides about 27 “dot” utility commands. Test that the systems works by creating a table and populating with data. To get started, create a batch file that starts SQLITE with the database name of interest, which is passed to the executable as a parameter. To create the database named “mydb”, the contents of sqlite.bat would be:
1 | c:\progra~1\sqlite\sqlite3.exe mydb |
At the sqlite prompt both dot and SQL commands can be submitted. To retain a record of the commands used to create your database, place them in a plain text file and then use the .read command to read that file into SQLITE. Here are the contents of my “create-tables.txt” file:
1 | CREATE TABLE ab ( |
To execute, read in create-tables.txt at the sqlite prompt:
1 | sqlite> .read "create-tables.txt" |
You can check that the table was created with the .schema command. Next insert a value. Either type it directly at the sqlite prompt, or read it from a file.
1 | INSERT INTO ab VALUES ( NULL,'Tau 46', 'human', 'mouse', 'Santa Cruz', 'sc-32274', 'IgG1', 'hu;mu;rat', 'western;ip;if','unknown'); |
And query it out:
1 | sqlite> SELECT * FROM ab; |
If you retrieve your row, you are all set to move on. Note that the assigned value of ab_id is NULL. Let the database assign the autoincrement field.
Connect SQLITE to EMACS
Interaction between SQLITE and EMACS is accomplished through comint. Use describe-function “comint-” to browse the various comint commands. We are interested in comint-redirect-send-command-to-process. Some of the comments in comint.el are also useful to read. Before examining comint directly, let’s consider how to abstract away the process of interacting with the database. Ultimately what I want is a single command that will send my SQL statement to SQLITE and return my result set. I would like the result set returned as a list of tuples (rows). Each tuple can then be processed into a list of fields. This is very convenient as Lisp works well with lists. My command will be sqlite-query, and its only argument will be sql-command. It will return a list of tuples.
First, lets set up a few variables
1 | (defvar sqlite-program "c:/progra~1/sqlite/sqlite3.exe" "Full path name of the SQLITE executable.") |
Next start up the SQLITE process. Send the SQLITE dot command “.mode tabs” so that fields are always tab delimitted. This is important later when fields must be extracted. Our database abstraction will return a list of rows, each row being a string with tab delimited fields. This string then needs to be parsed using split-string to extract fields as a list. More on that later.
1 | (apply 'make-comint "sqlite-process" sqlite-program nil `(,sqlite-db )) |
Next we need to write our sqlite-query function. The function should perform the following activities:
- Navigate to the output buffer.
- Erase the contents of the output buffer, if any.
- Send the sql-statement to SQLITE using the sqlite-process buffer
- Switch back to the sqlite-output buffer and retrieve the results. One result row per line of the buffer. Extract each line as an element of the result list.
1 | (defun sqlite-query( sql-command ) |
chomp is a utility that will remove whitespace from the results:
1 | (defun chomp (str) |