SQLite3

is a simple high-level wrapper around SQLite3. It doesn’t intend to wrap everything, but it tries to be useful.

Installation

(load "git@veitheller.de:git/carpentry/sqlite3.git@0.2.0")

Usage

The module SQLite3 provides facilities for opening, closing, and querying databases.

(load "git@veitheller.de:git/carpentry/sqlite3.git@0.2.0")

; opening DBs can fail, for the purposes of this example we
; ignore that
(defn main []
  (let-do [db (Result.unsafe-from-success (SQLite3.open "db"))]
    ; Let's make sure our table is there
    (ignore
       (SQLite3.query &db
                      "CREATE TABLE IF NOT EXISTS mytable (name TEXT, age INT)"
                      &[]))

    ; we can prepare statements
    (ignore
       (SQLite3.query &db
                      "INSERT INTO mytable VALUES (?1, ?2);"
                      &[(to-sqlite3 @"Carp") (to-sqlite3 4)]))

    ; and query things
    (println* &(SQLite3.query &db "SELECT * from mytable;" &[]))
    (SQLite3.close db)))

Because open and query return Result types, we could also use combinators!

SQLite

meta-stub

a

is the opaque database type. You’ll need one of those to query anything.

It can be obtained by using open.

Stmt

meta-stub

a

is an opaque prepared statement type. Prepare once with prepare, execute with exec-prepared, and release with finalize-stmt.

Type

module

Module

represent all the SQLite types we can represent.

The constructors are Null, Integer, Floating, Text, and Blob. Most primitive Carp types can be casted to appropriate SQLite types by using the to-sqlite3 interface.

begin

defn

(Fn [(Ref SQLite a)] (Result () String)) 

                        (begin db)

begins a transaction.

changes

external

(Fn [(Ref SQLite a)] Int)

returns the number of rows modified by the last INSERT, UPDATE, or DELETE.

close

external

(Fn [SQLite] ())

closes a database.

commit

defn

(Fn [(Ref SQLite a)] (Result () String)) 

                        (commit db)

commits the current transaction.

exec-prepared

defn

(Fn [(Ref Stmt a), (Ref (Array SQLite3.Type) b)] (Result (Array (Array SQLite3.Type)) String)) 

                        (exec-prepared stmt p)

executes a prepared statement with the given parameters. Automatically resets the statement afterward for reuse.

finalize-stmt

external

(Fn [Stmt] ())

releases a prepared statement’s resources. Must not be used afterward.

last-insert-rowid

external

(Fn [(Ref SQLite a)] Long)

returns the row ID of the last successful INSERT.

open

defn

(Fn [(Ref String a)] (Result SQLite String)) 

                        (open s)

opens a database with the filename s.

If it fails, we return an error message using Result.Error.

params

macro

Macro 

                        (params :rest args)

wraps each argument in to-sqlite3 and returns a reference to the resulting parameter array, ready to hand to query or exec-prepared.

(SQLite3.params id @"name") expands to &[(to-sqlite3 id) (to-sqlite3 @"name")], removing the per-argument to-sqlite3 ceremony. Values that are already a SQLite3.Type — such as (SQLite3.Type.Null) or a Blob — pass straight through, so they can be mixed in with primitives.

prepare

defn

(Fn [(Ref SQLite a), (Ref String b)] (Result Stmt String)) 

                        (prepare db sql)

prepares a SQL statement for repeated execution via exec-prepared. Release with finalize-stmt when done.

query

defn

(Fn [(Ref SQLite a), (Ref String b), (Ref (Array SQLite3.Type) c)] (Result (Array (Array SQLite3.Type)) String)) 

                        (query db s p)

queries the database db using the query s and the parameters p.

If it fails, we return an error message using Result.Error.

reset-stmt

external

(Fn [(Ref Stmt a)] ())

manually resets a prepared statement and clears its bindings. Called automatically by exec-prepared.

rollback

defn

(Fn [(Ref SQLite a)] (Result () String)) 

                        (rollback db)

rolls back the current transaction.

with-prepared

macro

Macro 

                        (with-prepared binding body)

prepares sql on the database db, binds the resulting statement to stmt, evaluates body, and finalizes the statement on every exit path — including when body short-circuits.

Like the body of with-transaction, body must evaluate to a Result. The whole form returns the prepare error if the statement can’t be prepared, otherwise the Result that body produced. A failed prepare and an error the body returns are both Result.Error String, so a caller can’t tell them apart by type.

(SQLite3.with-prepared [stmt &db "INSERT INTO t VALUES (?1, ?2)"]
  (do
    (for [i 0 100]
      (ignore (SQLite3.exec-prepared &stmt (SQLite3.params i @"row"))))
    (Result.Success ())))

with-transaction

macro

Macro 

                        (with-transaction db body)

executes body inside a transaction. Rolls back on Result.Error, commits on success.