Tuesday, February 12, 2008

Interface to SQLite database in 23 lines of Arc

One thing that the first release of Arc was missing was access to any sort of database, but that's easily remedied. Here are 23 lines of Arc code that provide access to a SQLite database:

(= db! 'nil)

(def db+ (name (o host "localhost") (o port 49153))
(let (i o) (connect-socket host port)
(db> o name)
(if (db< i) (list i o))))

(def sql ((i o) q)
(db> o q)
(if (db< i) (readall i 200)))

(def db- (db)
(map close db))

(def db> (o s)
(write s o)
(writec #\return o)
(writec #\newline o)
(flush-socket o))

(def db< (i)
(= db! (read i))
(iso db! 200))

The three functions you need to care about are db+ (get a connection to a named SQLite database), db- (close a connection to a database) and sql (execute a SQL query and return a list (or lists) of rows. There's also db! which contains the status of the last command (200 for OK, or 500 followed by a string explaining the error).

Here's a little Arc session creating a database, putting some data in it and then querying it. The database called test didn't exist at the start of this session:

arc> (= db (db+ "test"))
(#<input-port> #<output-port>)
arc> (sql db "create table foo (id integer primary key, text varchar(255))")
arc> (sql db "select * from foo")
arc> (sql db "insert into foo (text) values ('first');")
arc> (sql db "select * from foo")
(("1" "first"))
arc> (sql db "insert into foo (text) values ('something else')")
arc> (sql db "select * from foo")
(("1" "first") ("2" "something else"))
arc> (db- db)

To make this work I had to write a TCP server that wraps SQLite (it's just a small C program that you can get here). The C program listens on a port for connections from your Arc program and handles queries.

I did have to make a small patch to Arc itself (since arc0 doesn't contain any outgoing socket code). My patch adds the ability to make a TCP connection to a remote machine and to flush an output port (add this to your ac.scm):

(xdef 'connect-socket (lambda (host port)
(let-values ([(in out) (tcp-connect host port)]) (list in out))))
(xdef 'flush-socket (lambda (s) (flush-output s)))

(Apologies if I have abused Scheme there, I'm a Scheme n00b)

All this code is released under the same license as Arc itself.


jurjen said...

Nice short little wrapper!

However with the latest version of Arc (arc1) it won't work - the variable db! won't work because I believe the ! is now an operator (?) - but a quick workaround is to replace the db! with something like dbx (total of 3 replacements).

uvl said...

I'm getting this after db select:

Error: "tcp-write: error writing (Broken pipe; errno=32)"

(arc1, sqlite3, mzscheme352)

Any idea?

uvl said...

db select results in

Error: "tcp-write: error writing (Broken pipe; errno=32)"

(arc1, mzscheme352, changed db! to dbx)

Any idea?