README

SQLite Bindings for Coalton

See the reference for complete documentation.

Motivation

This system intends to a be minimal and efficient binding to SQLite.

Initially I thought I would simply wrap cl-sqlite, but that library does runtime computation that we don’t need in Coalton when it comes to binding and reading values from statements.

Overview

This system is arranged into 6 packages, increasing in abstraction:

1. coalton-sqlite/ffi

   CFFI calls, a slightly modified version of the package in cl-sqlite

2. coalton-sqlite/constants

   Enum types from sqlite.h

3. coalton-sqlite/sqlite

   Simple Coalton wrapper around the above FFI

4. coalton-sqlite/value

   A DynamicValue type that covers any SQLite value, as well as a SqliteValue typeclass for binding/reading

5. coalton-sqlite/query

   A few high-level query functions, they deal with (List DynamicValue)

6. coalton-sqlite/cache

   A FIFO cache for prepared statements, in some cases you might want to use this for performance optimization.

The query package might change, but right now it is a useful enough high-level interface. The primary complaint I have is defaulting to lists of DynamicValue types.

Everything above the FFI level is reexported into the coalton-sqlite package.

Encryption

Bindings for the SQLite Encryption Extension are included in the coalton-sqlite/see system.

Examples

Basic API

(coalton
 (with-database ":memory:" None
   (fn (db)
     (execute-string db "create table person (name, age);")
     (with-statement db "insert into person values (?, ?)"
       (fn (stmt)
         (bind-text stmt 1 "garlic0x1")
         (bind-i64 stmt 2 26)
         (step-statement stmt)))
     (with-statement db "select * from person"
       (fn (stmt)
         (step-statement stmt)
         (Tuple
          (column-text stmt 0)
          (column-i64 stmt 1)))))))

: #.(TUPLE "garlic0x1" 26)

Query API

(coalton
 (with-database ":memory:" None
   (fn (db)
     (execute-string db "create table person (name, age);")
     (execute db "insert into person values (?, ?)"
              (make-list (Text "garlic0x1") (Int 26)))
     (execute db "insert into person values (?, ?)"
              (make-list (Text "foo") (Int -2)))
     (query db "select * from person" mempty))))

: ((#.(TEXT "foo") #.(INT -2)) (#.(TEXT "garlic0x1") #.(INT 26)))

Statement Cache API

(coalton
 (with-database ":memory:" None
   (fn (db)
     (execute-string db "create table if not exists myrecord (x, y, z)")
     (with-statement-cache db 4
       (fn (cache)
         (coalton-library/experimental:dotimes (i 10000)
           (with-cached-statement cache "insert into myrecord values (?, ?, ?)"
             (fn (stmt)
               (bind-i64 stmt 1 (into i))
               (bind-i64 stmt 2 (into i))
               (bind-i64 stmt 3 (into i))
               (step-statement stmt))))))
     (query-one-column db "select count(*) from myrecord" mempty))))

: #.(INT 10000)