EMACS and SQLITE as a rapid application development environment

by Mortimer B. Cladwell III

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:

  1. Install SQLITE
  2. Connect to EMACS
  3. SQLITE mode
  4. Form design
  5. Report design



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:

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:

CREATE TABLE ab ( 
	ab_id INTEGER PRIMARY KEY AUTOINCREMENT,
	names varchar(20),
	antigen_species char(20),
	host char(20),
	supplier varchar(20),
	catnum varchar(20),
	isotype char(20),
	specificity varchar(20),
	application varchar(20),
	epitope varchar(20));
To execute, read in create-tables.txt at the sqlite prompt:
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.

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:

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.

[Contents]


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

(defvar sqlite-program "c:/progra~1/sqlite/sqlite3.exe" "Full path name of the SQLITE executable.")

(defvar sqlite-db "c:/temp/lisp-code/tutorial/mydb" "Full path name of the SQLITE database.")

(defvar sqlite-process-buffer "*sqlite-process*" "*Name of the SQLITE process buffer. This is where SQL commands are sent.")

(defvar sqlite-output-buffer "*sqlite-output-buffer*" "Name of the buffer to which all SQLITE output is redirected.")

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.

(apply 'make-comint "sqlite-process"  sqlite-program  nil `(,sqlite-db ))
(comint-redirect-send-command-to-process ".mode tabs" sqlite-output-buffer (get-buffer-process sqlite-process-buffer) nil)
(get-buffer-create sqlite-output-buffer)

Next we need to write our sqlite-query function. The function should perform the following activities:

  1. Navigate to the output buffer.
  2. Erase the contents of the output buffer, if any.
  3. Send the sql-statement to SQLITE using the sqlite-process buffer
  4. 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.
(defun sqlite-query( sql-command )
 (set-buffer sqlite-output-buffer)			;1
 (erase-buffer)		                        	;2
  (comint-redirect-send-command-to-process sql-command sqlite-output-buffer (get-buffer-process sqlite-process-buffer) nil)  ;3
  (accept-process-output (get-buffer-process sqlite-process-buffer) 1)  ;need to wait to obtain results
   
  (let*  ((begin (goto-char (point-min)))		;4
	  (end (goto-char (point-max)))
	  (num-lines (count-lines begin end))
	  (counter 0)
	  (results-rows ()))
    (goto-char (point-min))
    (while ( < counter num-lines)
      (setq results-rows (cons (chomp (thing-at-point 'line)) results-rows))
      (forward-line)
      (setq counter (+ 1 counter)))
    (car `(,results-rows))))

chomp is a utility that will remove whitespace from the results:

(defun chomp (str)
     "Trim whitespace from string"
     (let ((s (if (symbolp str)(symbol-name str) str)))
        (save-excursion
          (while (and
	          (not (null (string-match "^\\( \\|\f\\|\t\\|\n\\)" s)))
	          (> (length s) (string-match "^\\( \\|\f\\|\t\\|\n\\)" s)))
	    (setq s (replace-match "" t nil s)))
          (while (and
	          (not (null (string-match "\\( \\|\f\\|\t\\|\n\\)$" s)))
	          (> (length s) (string-match "\\( \\|\f\\|\t\\|\n\\)$" s)))
	    (setq s (replace-match "" t nil s))))
        s))




[Contents]

SQLITE Mode

For a good discussion of the details of creating a major mode refer to Writing GNU EMACS Extensions by Bob Glickstein. The advantage of utilizing SQLITE within a major mode is that while you are in the SQLITE specific buffer, you can have SQLITE specific menu items and key chords that are in effect only while in that buffer. A mode also allows you a single point in your code to open and close the database and deal with any database housekeeping that is required. Our effort to abstract away the details of database interaction are also facilitated by a mode, as any database specific functions can be included in the mode. The commands for creating the mode along with helper functions and load statements can go into a file called sqlite-mode.el. My file also includes code for working with the widget library:
(require 'widget)
(require 'derived)
(eval-when-compile
       (require 'wid-edit))
Define a hook variable and menu map. Add elements to the menu map. Note that each menu item is defined by a dotted pair, the car being the text in the menu and the cdr being an associated function. Often that function will launch a form to be used to enter data. A single example of
form design is given below.
(defvar sqlite-mode-hook nil
  "*List of functions to call when entering sqlite mode*")

(defvar sqlite-menu-map nil
  "Menu for sqlite mode.")


(if sqlite-menu-map
    nil
  (setq sqlite-menu-map (make-sparse-keymap "sqlite"))
  (define-key sqlite-menu-map [sqlite-reports]
    '("Sqlite reports" . sqlite-reports))
  (define-key sqlite-menu-map [add-antibody]
    '("Add Antibody" . add-antibody))
  (define-key sqlite-menu-map [add-gene]
    '("Add Gene" . add-gene))
  (define-key sqlite-menu-map [add-abresult]
    '("Add Antibody Results" . add-abresult)))

(define-key sqlite-mode-map [menu-bar sqlite]
  (cons "sqlite" sqlite-menu-map))
Define keybindings specific for SQLITE mode. I don't define any unique SQLITE specific bindings because I need to use the widget keymap for my forms to be functional, so I copy-keymap the widget keymap.
(defvar sqlite-mode-map nil
  "Keybindings for sqlite mode")

(if sqlite-mode-map
    nil
  (setq sqlite-mode-map (copy-keymap widget-keymap)))

(define-derived-mode sqlite-mode text-mode "sqlite"
  "Major mode using sqlite for tau antibodies.
Special commands:
\\{sqlite-mode-map}")
Load any libraries that need to be accessed during database manipulation. Here I load various forms and the report interface. provide allows me to require in my .emacs file.
 
(load "c:/temp/lisp-code/sqlite/add-antibody")
(load "c:/temp/lisp-code/sqlite/add-gene")
(load "c:/temp/lisp-code/sqlite/add-abresult")
(load "c:/temp/lisp-code/sqlite/tau-reports")

(provide 'sqlite)
Also in this file I would include any helper functions, such as sqlite-query and chomp described above. Once the mode is defined, the command (sqlite-mode) can be used to invoke the mode, for example in a form buffer.



[Contents]

Design forms using EMACS Widget library

Using the function sqlite-query, we can now interact programmatically with SQLITE. For end users, we need to design a user friendly GUI. The EMACS widget library is about as friendly as EMACS will get. There is very limited information on how to use the EMACS widgets. You can read about the widget library within EMACS info, or on the web. Ye Wenbin also has a useful tutorial. Should you find other resources, please let me know. Essentially you create a form with a widget for each field, such as a text field, dropdown, radio button etc. Once the user has populated the fields, a button assembles and submits the SQL statement. Here is a simple example with widgets for some of the fields in our "ab" database table:

(require 'widget)
(eval-when-compile
       (require 'wid-edit))


(defun add-antibody()
  "Prep a buffer for handling sqlite queries."
  (interactive)
  (setq buffer (get-buffer-create "*Sqlite Forms*"))
  (switch-to-buffer buffer)
  (sqlite-mode)
 
  (let ((inhibit-read-only t))
    (erase-buffer))
  (remove-overlays)
  
  (widget-insert "Antibody Entry form")
  (widget-insert "\n\n")

  (widget-insert "\n\n")
  (widget-create 'editable-field
		 :size 10
		 :notify (lambda (widget &rest ignore)
       			   (setq ab-name (widget-value widget)))		   
		   :format "Antibody name: %v " ; Text after the field!
		   ab-name)
 

(widget-insert "\n\n")
  (widget-create 'editable-field
		 :size 50
		 :notify (lambda (widget &rest ignore)
			   ;;(widget-setup)
			   (setq ab-supplier (widget-value widget)))		   
		   :format "Supplier: %v " ; Text after the field!
		   ab-supplier)
  (widget-insert "\n\n")


(widget-create 'menu-choice
     		 :tag "Select Host Species"
     		 :value "unknown"
     		 :help-echo "Click to view dropdown"
                 :notify (lambda (widget &rest ignore)
                           (setq host-species (widget-value widget)))
		 '(item "mouse") 
		 '(item "rabbit") 
		 '(item "goat")
		 '(item "donkey"))   



(widget-insert "\n")
  (widget-create 'push-button
		 :notify (lambda (&rest ignore)
                 :button-face 'custom-button
			   (run-sql-script)
			   (message "Ran SQL script"))
		 "Add antibody to database")
   
(widget-insert "\n")
(widget-insert "\n")


  (widget-create 'push-button
                 :button-face 'custom-button
		 :notify (lambda (&rest ignore)
			   (clear-add-antibody-form)
			   (message ""))
		 " Clear Form ")


(widget-insert "\n")
(widget-insert "\n\n")
(widget-setup))



To use the form, issue the command (add-antibody), which is also connected to one of the menu items discussed the the sqlite-mode section. Once the data has been entered, a "submit" button is pressed which will assemble the sql statement:

  (setq sql-command (concat "INSERT INTO ab VALUES(NULL, '" (car (cdr (split-string selected-gene-id "\t"))) "','"
  (chomp ab-name) "','" antigen-species  "','" host-species  "','"(chomp ab-supplier)   "','"(chomp  ab-catnum)  "','" 
   ab-isotype   "','" ab-specificity   "','" ab-applications"','" (chomp ab-epitope) "','" (chomp pdf) "','" (chomp clone-id)  "','" 
  (chomp notes)  "');"))

Note that any input through a text field is processed with chomp to remove whitespace.


Handling Checkbox user input

Here is a truncated example showing 2 of 6 checkboxes on one of my forms:
  (widget-create 'checkbox
                 :format "%[%v%] Western    "
                 :notify (lambda (wid &rest ignore)
			   (if (widget-value wid)
			       (setq western t)
			     (setq western nil)))
		 western)

  
  (widget-create 'checkbox
                 :format "%[%v%] ELISA\n"
                 :notify (lambda (wid &rest ignore)
			   (if (widget-value wid)
			       (setq elisa t)
			     (setq elisa nil)))
		 elisa)

First set up variables to hold the status of each element in the checkbox list, as well as a variable to hold the final semicolon delimitted string. I am showing all six variables.

;;antibody application variables
(defvar ab-applications ""
  "Holds concatenated string of applications")

(defvar western nil
  "True if western etc.")

(defvar ip nil
  "")

(defvar elisa nil
  "")

(defvar immunohistochemistry nil
  "")

(defvar immunof nil
  "")

(defvar hcs nil
  "")

;;;end applications variables
;;;
Next is the function to be invoked when the user clicks the "OK" or similar button on the form. Items that are selected can be cons into a list for further processing. Here I simply concatenate the list using semicolon as a separator for the purpose of storage in the database and reporting.
(defun create-ab-applications-list ()
  (setq ab-applications-list ())
  (if western (setq ab-applications-list (cons "w" ab-applications-list)))
  (if ip (setq ab-applications-list (cons "IP" ab-applications-list)))
  (if elisa (setq ab-applications-list (cons "ELISA" ab-applications-list)))
  (if immunohistochemistry (setq ab-applications-list (cons "IH" ab-applications-list)))
  (if hcs (setq ab-applications-list (cons "HCS" ab-applications-list)))
  (setf ab-applications (mapconcat 'identity ab-applications-list ";")))



Populating dropdown lists on-the-fly from database content

Quite often you wish to give the user a prepopulated dropdown where the content comes from a field in the database. Here is an example of form code for a dropdown:
(widget-create 'menu-choice
     		 :tag "Select Host Species"
     		 :value "unknown"
     		 :help-echo "Click to view dropdown"
                 :notify (lambda (widget &rest ignore)
                           (setq host-species (widget-value widget)))
		 '(item "mouse") 
		 '(item "rabbit") 
		 '(item "goat")
		 '(item "donkey"))   
The dropdown content is:

		 '(item "mouse") 
		 '(item "rabbit") 
		 '(item "goat")
		 '(item "donkey")  
Suppose rather than hard coding I want to pull out all the species available in the ab.host field in my database? Using my sqlite-query command, it would be easy enough to get the list:

( "mouse" "rabbit" "goat" "donkey" )

(setq sql-command "SELECT host FROM ab;")
(setq host-species (sqlite-query sql-command))

But how to I convert that to the format shown above for dropdown content? The dropdown content shown above is equivalent to:

'(item "mouse") '(item "rabbit") '(item "goat") '(item "donkey")

which is equivalent to:

(quote (item "mouse")) (quote (item "rabbit")) (quote (item "goat")) (quote (item "donkey"))

Note that this is not a list, as it does not have circumscribed parentheses. Since we want to write code, a macro is the way to go. This is what it looks like:

(defmacro multi-item ()
    `(widget-create  'menu-choice 
		     :tag "Select Host Species" 
		     :value "unknown"
		     :help-echo "Click to view dropdown" 
		     :notify (lambda (widget &rest ignore) 
			       (setq selected-host-species (widget-value widget)))
		     ,@(mapcar '(lambda  (a)  `'(item ,a))  host-species)))
I mapcar over my host-species list, creating the statement (quote (item "element")) for each element of the list, then splice that into my backquoted widget-create code. With this code, it is easy now to create a dropdown the contents of which depend on other selections in the form e.g. an initial dropdown, checkbox, or radio button. The notify statement of the initial selection should set a variable and then refresh the form, invoking the query that will populate the dependant dropdown list. You could, for example, make the second list visible only after the initial selection.

Though the widget library is crude by current standards, it will meet most needs either directly or with a hack.



[Contents]


Report Design

I won't say much about report design since manipulating text in an EMACS buffer is a pretty standard activity for EMACS users, with plenty of web resources available as tutorials. A couple of suggestions - with your results as a list of rows, you can iterate over the rows and print directly into a new buffer prepared with a header. Alternatively you can extract fields from each row using:

(while results-rows   
       (setq line  (split-string (car results-rows)"\t" nil ))
       	     .
	     .
  	     .
 (setq results-rows (cdr results-rows)))

Now fields can individually be manipulated. Note that tab is being used as the field delimiter. This was specified previously when we started up the database.

[Contents]

Last modified: 10 October 2010