$Id: README,v 1.8 2006/11/23 11:37:56 ivoras Exp $

SQL Cache Daemon
****************

sqlcached is a memory cache engine similar in basic idea to memcached,
but instead of limiting itself to key-value pairs, it offers full SQL
access to the memory database. It's written in C and uses SQLite as
its backend / memory database.

Primary purpose of sqlcached is for environments such as web
applications where memory caching gives huge speed improvements, but
where data is too complex to be represented by simple key-value pairs.
It also differs from memcached in that the primary mode of access is
by (faster) unix sockets instead of TCP (which is also supported).

Client libraries for C, PHP and Python languages are planned.

Motivation
==========

- `memcached` is awesome, but doesn't offer any fine key granularity.
- Need a cache with multiple keys and advanced expiry (to expire a subset
  of keys on demand)

Implementation
==============

- Use SQLite :memory: database to implement advanced cache mechanism
- Thus, get full DB support: multiple tables, fields, indexes
- Unfortunately, the best way to use a full DB is via SQL

SQLite locking problem
----------------------

Even in its regular use, SQLite doesn't allow multiple simultaneous
updates on the database (by multiple simultaneous clients). When using
memory databases, no sharing between multiple processes or threads
is even possible. Thus, all access to the database needs to be
serialised. SQL Cache Daemon is thus a single threaded process which
multiplexes between client connections using poll(2).

Since the goal is to replace memcached with something offering more
functionality, and memcached is fine the way it is (single-threaded),
it is believed that a single-threaded sqlcached will perform adequate.

On transactions
---------------

Since all requests are serialised onto one sqlite connection, real
transactions cannot be implemented. Thus, commands "BEGIN", "COMMIT"
and "ROLLBACK" are not allowed. All statements are executed in
immediate/auto-commit mode.

Performance expectations
------------------------

Due to its complexity, SQLite is in general much slower than
memcached, but real world measurements are telling that the
comparative slowdown is somewhere at the order of 25% or less. Since
memcached only offers very simple key-value operations, compared to
the full SQL available in the SQLite engine, this slowdown is
mostly insignificant.

Some performance optimisations sqlcached uses:

- Support for unix domain sockets eliminates TCP overhead
- Communication between clients and the server is as simple
  as possible and without small block reading/writing ops (to allow
  simple clients using buffered fgets())
- poll() based single-threaded network multiplexing

NB: SQLite and memcached do not allow concurrent access! While
network multiplexing is done concurrently for many connections,
each SQL query will execute on its own, blocking all other
queries. On the other hand, each query gets 100% CPU time. This
is the same method memcached uses.
(In other words: keep SQL queries passed to sqlcached fast)


Implementation details
----------------------

Implemented on and for FreeBSD 6.x. The only external dependancy is for
SQLite 2.8 (libsqlite). Porting to other platforms will be
considered based on user feedback, if any.

I will also accept compatibility patches for other OSes, but only if
they don't introduce more than two #ifdef directives per .c or .h
file (use #include directives for more complex things).


Portability
===========

SQLCacheD is created and maintained on FreeBSD. It's been tested
to compile and work (at least for the basic operations) on Fedora
Core 6, where you'll need the sqlite2-devel package to compile it.
You should ignore missing declarations for vasprintf and asprintf
as they won't impact the final executable.


Configuration
=============

At first, the goal was to make an XML config file. At second thought,
this was scrapped and all configuration is done via command line
arguments.

Of note is the schema file, loaded at startup, which serves to create
(and possibly populate) the initial database. If no schema file is
specified, the following table will be created:

    CREATE TABLE cache (
        key     varchar not null,
        value   varchar not null,
        time    integer not null,
        primary key(key)
    );
    CREATE INDEX cache_time ON cache(time);

NOTE: Clients must be familiar with the database schema to use
sqlcached. This default schema is enough to simulate basic
behaviour of memcached.

NOTE: All tables must have a "time" field to allow data expiry
(garbage collection).

Another file which may be used by sqlcached is the "expiry file",
which describes how to delete data from the table (expiry criteria)
If no expiry is performed, accumulated data may grow too large
for the system to handle (and it will steal resources from
other applications).

Here's an example of the expiry file:

    100
    cache 60 200 0

First line in this file specifies how often is data expiry function
called, expressed as a number of SQL commands executed. The default
value (100) means data will be checked for expiry every 100 SQL
operations.

The remaining lines in the file are in the format:

    table_name time_to_live nr_ops nr_records

...where...

 * table_name is the name of the table to perform expiry on
   (e.g. "cache")
 * time_to_live is the maximum time records are allowed to exist
   (i.e. records older than this much seconds will be deleted)
 * nr_ops is the maximum number of ops records are allow to exist
   (i.e. records that were created more than nr_ops SQL operations
   ago will be deleted)
 * nr_records is the maximum number of records in the table
   (oldest records will be deleted to maintain record count; this
   is the most expensive expiry method and it's recommended that
   it be used with a high delays between expiry function calls)

Any criteria whose value is 0 is ignored. In the given example
of the criteria file, records will be deleted if older than 60
seconds or 200 SQL ops (record count will not be checked).

Discussion
==========

Q: How is sqlcached different from MySQL and its memory-based tables?
A: It saves you from having to use and maintain a separate database
   engine (well, unless you're already using MySQL). It's also more
   light-weight. Also, MySQL doesn't offer automatic schema creation and
   data expiry. On the other hand, MySQL should offer concurrent
   access to the data.

Q: Why use SQLite 2.8 instead of 3.x?
A: Because of this:
   http://article.gmane.org/gmane.comp.db.sqlite.general/19002
   (in short: SQLite 3.x is very slow for this kind of use)