== sqdb

Simple key-value store on top of SQLite.  Experimental, API subject to change.

== Overview

{{sqdb}} is a simple key-value store implemented on top of SQLite
using the [[/egg/sql-de-lite|sql-de-lite]] egg.  It provides the
usual fetch, store and modify operations.  It also supports ACID
semantics, by virtue of SQLite.

Keys are strings or symbols. Values may be strings, exact integers,
inexact integers, and blobs.

[[toc:]]

== API

=== Open and close

<procedure>(open-database filename)</procedure>

Opens database with path {{filename}} and returns an sqdb database object.
The database will be created if it does not exist.

<procedure>(close-database db)</procedure>

Closes sqdb database {{db}}.

<procedure>(call-with-database filename proc)</procedure>

Opens sqdb database {{filename}} and calls {{(proc db)}}.
The database will be closed when {{proc}} returns or raises an exception.

<procedure>(sqdb-handle db)</procedure>

Return the underlying {{sql-de-lite}} database handle for {{db}}.

=== Read, modify, and write

<procedure>(fetch db key)</procedure>

Fetch value of {{key}} from database {{db}}. {{key}} may be a string or symbol.

Returns a string, exact or inexact integer, or blob depending on the
type of the stored value.  If the key did not exist, returns {{#f}}.

<procedure>(store db key val)</procedure>

Stores {{key}} in database {{db}} with value {{val}}.  If {{key}} already exists,
its value is overwritten; otherwise, a new key is created. 

{{key}} may be a string or symbol.  {{val}} may be a string, exact or
inexact integer, or blob; it will be stored and fetched as that type.

The return value is unspecified.

<procedure>(add db key val)</procedure>
<procedure>(add* db key val)</procedure>

{{add}} is like {{store}}, but only makes a change if the key did not
exist.  It returns {{#f}} if the key did exist, and {{#t}} if it
did not.

{{add*}} is like {{add}}, but raises an exception if the key already exists.

<procedure>(exists? db key)</procedure>

Test if {{key}} exists in {{db}} and returns a boolean value.

<procedure>(incr db key [n])</procedure>

Atomically increment the value associated with {{key}} by {{n}}.  {{n}}
may be an integer or a floating point value, and defaults to 1.

If the value is a string, it is converted to a number if possible, and
the new value will be a number.  If the conversion fails, it is
considered to be zero, so the resulting value will be the number {{n}}.

Returns {{#t}} if the key existed and {{#f}} if it did not.

<procedure>(decr db key [n])</procedure>

Equivalent to {{(incr db key (- n))}}.

<procedure>(update db key proc)</procedure>

Updates the value of {{key}} by fetching the current value of key,
calling {{(proc val)}} to get an updated value, and storing that into
the database.  {{val}} will be {{#f}} if the key did not exist.

This operation is implemented using an IMMEDIATE transaction, and is
atomic as long as you always use {{update}} elsewhere (or an IMMEDIATE
or EXCLUSIVE transaction) when doing a read-modify-write on this key.

Note that the {{incr}} operation could be implemented in a
much less efficient way using {{update}}:

 (update db key (lambda (x) (add1 (or x 0))))
 ;; Less efficient than:
 (add db k 0)
 (incr db k)

<procedure>(delete db key)</procedure>

Deletes key from {{db}}.  Returns {{#t}} if the key existed (and was deleted),
or {{#f}} if it did not exist.

=== Transactions

<procedure>(with-transaction db thunk #!optional (type deferred))</procedure>

Executes {{thunk}} inside an atomic transaction.  If {{thunk}} raises
an exception or returns {{#f}}, the transaction is rolled back.
Otherwise it is committed when thunk exits.

Warning: transactions may not be nested.  An error will occur if you try.

{{type}} may be

; deferred : Everyone can write until our first read or write; everyone can read until our first write.
; immediate : No one else can write, but everyone can read until our first write.
; exclusive : No one else can read or write.

<procedure>(begin-transaction db thunk #!optional (type deferred))</procedure>

Begin an atomic transaction.  See {{with-transaction}} for the meaning of {{type}}.

<procedure>(commit db)</procedure>

Commit the current transaction to disk oxide.

<procedure>(rollback db)</procedure>

Rollback the current transaction.

=== Traversal

To constrain the key search, traversal procedures all take an optional
{{PATTERN}} , which is a SQLite {{LIKE}} pattern with {{%}} wildcard.
For example, {{ctr%}} searches for all keys {{"ctr" <= key <
"cts"}}.

Generally, there should be one wildcard character, in the
last position; this allows fast lookup using the B-Tree index.  You
may instead use a wildcard any number of times in any position, but
the database will be forced to scan and compare every single key.

Key traversal order is unspecified.

<procedure>(list-keys db #!optional pattern)</procedure>

Returns a list of keys in {{db}}, optionally constrained by {{pattern}}.
The keys are returned as strings.

 (list-keys db)
 ; => '("bar" "ctr2" "ctr" "foo" "ctr1")

<procedure>(fetch-alist db #!optional pattern)</procedure>

Returns an alist mapping keys (as symbols) to values, optionally
constrained by {{pattern}}.

 (fetch-alist db "ctr%")
 ; => ((ctr . 1) (ctr1 . 2) (ctr2 . 3))

<procedure>(fetch-hash-table db #!optional pattern)</procedure>

Returns a hash table mapping keys (as strings) to values, optionally
constrained by {{pattern}}.

<procedure>(map-items db proc #!optional pattern)</procedure>

Calls {{(proc key value)}} for every key-value pair in {{db}},
optionally constrained by {{pattern}}, and collects the results
into a list.

 (define (fetch-alist db #!optional pattern)
   (map-items db (lambda (k v) (cons (string->symbol k) v))
              pattern))

<procedure>(for-each-item db proc #!optional pattern)</procedure>

Calls {{(proc key value)}} for every key-value pair in {{db}},
optionally constrained by {{pattern}}, and discards the results.

 (define (fetch-hash-table db #!optional pattern)
   (let ((ht (make-hash-table string=?)))
     (for-each-item db (lambda (k v) (hash-table-set! ht k v))
                    pattern)
     ht))

<procedure>(fold-items db proc seed #!optional pattern)</procedure>

Calls {{(proc key value seed)}} for every key-value pair in {{db}},
optionally constrained by {{pattern}}.  The result of {{proc}}
becomes the new {{seed}} after every call.

 (fetch-alist db "ctr%")
 ; => ((ctr . 1) (ctr1 . 2) (ctr2 . 3))
 (fold-items db (lambda (k v s) (+ s v)) 0 "ctr%")
 ; => 6

=== Miscellaneous

<procedure>(set-busy-timeout! db ms)</procedure>

Set a timeout of {{ms}} milliseconds for operations that occur when
the database is locked.  If {{ms}} is {{#f}} or 0, the operation
times out immediately.

You must set the timeout separately for each database connection and
it can be changed at any time after the database is open.  The
default is {{#f}}.

== Implementation notes

The key-value store is implemented as a table called {{sqdb_ht_1}}
inside an SQLite database.  The table is created if necessary when the database
file is opened.  The store can therefore be used with an existing SQLite
database without interfering with other tables.

The {{db}} object is currently a raw {{sqlite-database}} object from
{{sql-de-lite}} and consequently you can execute statements, etc. by
using that egg.  The representation of the object may change so you
should always use {{sqdb-handle}} to obtain the SQLite database
handle.

== Bugs and limitations

* Only one (unnamed) key-value store is permitted per SQLite database
  file.  Named stores may be permitted in the future.
* A mechanism to turn off ACID is not currently provided.  However, you
  can execute the appropriate PRAGMAs against the underlying database
  by using the {{sql-de-lite}} egg.
* Nested transactions are not allowed.

== About this egg

=== Author

[[http://ursetto.com|Jim Ursetto]]

=== Version history

; 0.0.2 : Permit storage and retrieval of numbers and blobs in addition to strings
; 0.0.1 : Initial release

=== License

Copyright (c) 2011, Ursetto Consulting, Inc.  MIT license.