(= 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))")
nil
arc> (sql db "select * from foo")
nil
arc> (sql db "insert into foo (text) values ('first');")
nil
arc> (sql db "select * from foo")
(("1" "first"))
arc> (sql db "insert into foo (text) values ('something else')")
nil
arc> (sql db "select * from foo")
(("1" "first") ("2" "something else"))
arc> (db- db)
nil
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.
3 comments:
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).
I'm getting this after db select:
Error: "tcp-write: error writing (Broken pipe; errno=32)"
(arc1, sqlite3, mzscheme352)
Any idea?
db select results in
Error: "tcp-write: error writing (Broken pipe; errno=32)"
(arc1, mzscheme352, changed db! to dbx)
Any idea?
Post a Comment