[plt-scheme] [ANN] SQLite (v. 3.2.0) FFI for PLT v299

Everyone is always asking for SQLite and other database interfaces. I
wanted to learn the new FFI, so I put this together this morning.

Some notes
- For me, on OS X with Fink, I have to provide a path to the SQLite
library. I change it in the version below to what "should" work, but
my line is commented out, if you have a problem.
- Crashes occasionally. I know this is from bad pointer handling. I
think it occurs when the 'close' operations are not called as they
should.
- Does not fully implement SQLite interface. The only interesting
thing (to me) that it cannot do is use a Statement to do a "SELECT."
- You must write your SQL in strings. I think it would be nice to have
a separate library for doing SQL<->s-expr and just use both if you
want that.

Any comments, complaints, suggestions, whatever, are welcome.

The tar-ball is attached, when I have a bit more time I'll put it on PLaneT.

An example of the code you get use is below (from the "sqlite-test.ss" file):
<pre>
(let ([temp-path "/tmp/test"])
  (when (file-exists? temp-path)
    (delete-file temp-path))
  (let ([temp (open temp-path)])
    (define (exec-sql sql)
      (printf "Exec result: ~a / ~a~n"
              (exec temp sql
                    (lambda (columns values)
                      (printf "~a ~a~n" columns values)
                      0))
              (changes-count temp)))
    (exec-sql "CREATE TABLE status (guid TEXT UNIQUE, read INTEGER,
followed INTEGER, flagged INTEGER, label INTEGER, deleted INTEGER)")
    (exec-sql "INSERT INTO status VALUES ('foo', 0, 0, 0, 0, 0)")
    (exec-sql "INSERT INTO status VALUES ('bar', 0, 1, 0, 1, 0)")
    (exec-sql "SELECT * FROM status")
    (let ([insert (prepare temp "INSERT INTO status VALUES (?, ?, 0,
0, 0, 0)")])
      (run insert "zog" "1")
      (run insert "pong" "3")
      (finalize insert))
    (pretty-print (select temp "SELECT * FROM status"))
    (printf "Total changes: ~a~n" (total-changes-count temp))
    (close temp)))
</pre>

Result:
<pre>
Exec result: 0 / 0
Exec result: 0 / 1
Exec result: 0 / 1
(guid read followed flagged label deleted) (foo 0 0 0 0 0)
(guid read followed flagged label deleted) (bar 0 1 0 1 0)
Exec result: 0 / 1
(("guid" "read" "followed" "flagged" "label" "deleted")
 ("foo" "0" "0" "0" "0" "0")
 ("bar" "0" "1" "0" "1" "0")
 ("zog" "1" "0" "0" "0" "0")
 ("pong" "3" "0" "0" "0" "0"))
Total changes: 4
0
</pre>

-- 
Jay McCarthy <jay.mccarthy at gmail.com>
http://jay.makeoutcity.com/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sqlite-0.1.tar.gz
Type: application/x-gzip
Size: 3622 bytes
Desc: not available
URL: <http://lists.racket-lang.org/users/archive/attachments/20050324/c82bf933/attachment.gz>