programming

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
2
3
4
5
6
7
8
9
10
11
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:

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.

init.el

;;__________________________________________________________________________
;;;; Programming - SQLite

(add-hook ‘sql-mode-hook
‘(lambda ()
(interactive)
(sql-set-product ‘sqlite)
(sql-product-interactive sql-product)
;;(sql-connect-preset ‘pool-a))
(define-key sql-mode-map [f5] ‘sql-send-region)
(define-key sql-mode-map [f4] ‘sql-send-buffer)))

;;(add-hook ‘sql-interactive-mode-hook
;; )

;;(add-hook ‘sql-set-sqli-hook
;;Hook for reacting to changes of sql-buffer
;; (setq sql-database “~/owncloud/misc/pm/pm.sqlite”)
;; )

(setq sql-connection-alist
‘((pool-a
(sql-product ‘sqlite)
(sql-server “1.2.3.4”)
(sql-user “me”)
(sql-password “mypassword”)
(sql-database “~/syncd/prog/plate-manager/pm.sqlite”))))

(defun sql-connect-preset (name)
“Connect to a predefined SQL connection listed in sql-connection-alist'" (eval (let ,(cdr (assoc name sql-connection-alist))
(flet ((sql-get-login (&rest what)))
(sql-product-interactive sql-product)))))

;; (add-hook ‘sql-interactive-mode-hook ‘my-sql-save-history-hook)

Share