redis_vtbl

Overview

An sqlite3 virtual table module that stores data in redis.

Different sqlite database instances on (potentially) different systems with the same virtual table definition transparently share their data via redis.

DROP TABLE on any of the instances individually does not remove any data. An unconstrained DELETE FROM (from any instance) on the other hand will remove all data from the shared store.

Changes to the CREATE TABLE definition are minimal, consisting of syntax changes to specify the virtual table module name, and configuration to connect to redis. Column specifications are unchanged.

Redis connection specification can either be a single redis instance, or (not fully verified) a list of sentinel addresses and a service name from which to determine the active redis master.

Design Notes

Intent: To provide a mechanism to existing sql based products to be modified to transparently share data.

Note: > >= < <= ops on string indexes fall back to a full table scan.

Considerations for enormous amounts of data have not been made (hence redis vs. e.g. couchdb). Goal is to provide very fast access to a bounded amount of shared runtime state via an sql api.

---

There are multiple reasons you should not use this library.

1. Starting with a new product where redis / KV stores are a good solution. Use redis / KV store directly.
2. Existing product using sql that uses a (central/shared) database server. This library offers no benefits to continuing to use the db server directly.
3. Existing small product that uses sql that would be a better fit for a KV store. Adapt to use KV store directly.

---

So, when should you use this library?

I have no idea.

Unless you were looking for something like this specifically then you probably shouldn't.

• Want SQL use postgresql.
• Want KV (and then some) then use redis.

Developed and tested on linux. May work elsewhere; patches welcome.

Example

Original table definition:

CREATE TABLE test0 (
   blah     VARCHAR(255),
   blah2    INTEGER,
   blah3    VARCHAR(6)
);

Redis backed table definition:

CREATE VIRTUAL TABLE test0 USING redis (localhost:6379, prefix,
   blah     VARCHAR(255),
   blah2    INTEGER,
   blah3    VARCHAR(6)
);

Will be stored in the following redis keys:

prefix.db.table:{rowid}      = hash of the row data.
prefix.db.table.rowid        = sequence from which rowids are generated.
prefix.db.table.index.rowid  = master index (zset) of rows in the table

Index data:

prefix.db.table.indices         = master index (set) of indices on the table
prefix.db.table.index:{x}       = value zset index for column x
prefix.db.table.index:{x}:{val} = rowid map for value val in column x