== 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.

[[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 value.

<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 or a
number; it will be stored as a string.

The return value is unspecified.

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

{{add}} is like {{store}}, but only succeeds if the key does not exist.  
It raises an exception if the key already exists.

{{add*}} is like {{add}}, but returns {{#f}} if the key exists, and {{#t}}
if it did not exist.

This behavior is inconsistent with other operations such as {{fetch}}
and {{incr}}, which do not throw an error on missing key.  It may be
modified so that {{add}} does not throw an error, and {{add*}} is
removed.

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

Test if {{key}} exists in {{db}}.

<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 existing value is not a number, nothing will happen.

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) (+ 1 (string->number (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.

=== 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.1 : Initial release

=== License

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