Page Contents

• Overview
  • Storage Format
  • System Components
• Building, Deployment and Testing
• Usage
  • Uploading Databases
  • Accessing Databases
  • Secure Containers
• Cloud Storage Modules
  • The Built-in “azure” Module
  • The Built-in “google” Module
• Details and Caveats
  • Concurrent Access From Multiple Clients
  • Deleting Unused Block Files
  • Block Sharing
  • Pragma Interface
  • Virtual Table Interface
• Command Line Reference
• Daemon Command Reference

The “Cloud Backed SQLite” (CBS) system allows databases to be stored within
cloud storage accounts such that they can be read and written by storage
clients without first downloading the entire database to the client. This
document should be read in concert with the detailed API documentation present
in the two main public header files:

• blockcachevfs.h
• bcvutil.h

Databases may be accessed concurrently by multiple clients, although ensuring
that only a single client is writing to a database at any time is (mostly) left
to the application. A “client” is a single operating system process system –
within a single client process there may be multiple SQLite handles reading and
writing a database using the usual SQLite WAL-mode read/write locking to
manage concurrent access between themselves. Existing clients do not see
changes made by writers automatically, they are obliged to explicitly poll
cloud storage to do so.

The system currently supports
Azure Blob Storage
and Google Cloud Storage. It also features
an API that may be used to implement support to other
cloud storage systems.

The software is currently developed on Linux with stock gcc, and on Windows-10
using the MSVC-compatible mingw64 gcc compiler. Other platforms are quite
likely to work too, but have not been tested.

1.1. Storage Format

SQLite databases are not stored within the blob storage system in the usual
format. Instead, each database is divided into one or more fixed-size blocks.
The default block-size is 4MB, although this is configurable. Each block is
assigned an id between 16 and 32 bytes (128 and 256 bits) in size (all blocks
in a single container have the same sized ids). The name of each block file
is created by encoding its id in hexadecimal format and adding the extension
“.bcv”. For example:

  787A5B026DBF882F89748C37AED04CCD.bcv

For containers that use ids smaller than 24 bytes in size, each block id is
randomly generated. For manifests that use ids 24 bytes or larger, the first
16 bytes of each id may contain the md5 hash of the block contents, with
the remainder of the id made up by pseudo-random values. See the
section on block sharing below for further details.

Along with the block files is a “manifest” file, which contains, amongst other
things, a machine-readable description of how the various block files may be
assembled into into one or more SQLite databases. The name of the manifest
file is always:

  manifest.bcv

There is also an SQLite database used to store the contents of the “bcv_kv”
virtual table MAKE THIS LINK SOMEWHERE! named:

  bcv_kv.bcv

At present, manifest and block files may only be stored in the root directory
of a blob storage container, not in any sub-directory. This means that each
container contains at most a single manifest file. It may be that this
limitation can be removed if desirable. Other files may be stored in a
container along with the CBS files, but they must not use the “.bcv” extension.
Files that end with “.bcv” may be mistaken for surplus block or other files and
deleted or overwritten by CBS clients.

Note: CBS documentation uses the Azure terminology “container” to refer
to the cloud storage virtual receptacle in which block files and manifests are
stored. In Google Storage documentation the analagous concept is a “bucket”.
Other cloud storage systems may use their own vernacular.

1.2. System Components

There are three components to the system:

1. Primitives to:
   • Create blob storage containers and populate them
     with empty manifest files (manifest files indicating that the
     container contains no databases).
   • Destroy entire blob storage containers and their contents.
   • Upload databases to cloud storage.
   • Download databases from cloud storage.
   • Create copies of existing databases within cloud storage.
   • Delete databases from cloud storage.
   • List the databases within a cloud storage container.
   • Clean up (remove) unused blocks from cloud storage.

   Each primitive is available both as an
   C API that can be called by
   applications, and as a command-line tool.

2. A daemon process that, while running, provides local SQLite
   database clients in the same or different OS process with read-only
   access to databases stored in remote blob storage containers.

3. A VFS module that may be used in two modes, as follows:
   • For read-only access of cloud databases via a running daemon process,
     or
   • For read/write access of cloud databases in “daemonless” mode.

   The advantage of using a daemon over daemonless mode is that a single
   local cache of downloaded database blocks may be shared by multiple
   processes. In daemonless mode, each process is obliged to maintain its
   own separate cache.

Both the daemon and command line tools are packaged as a single binary
executable – “blockcachevfsd”.

command from the root directory of the source tree.

Adding Application Support

    bcvutil.c
    bcvmodule.c
    blockcachevfs.c
    simplexml.c
    bcvencrypt.c

    bcvutil.h
    bcvmodule.h
    blockcachevfs.h
    simplexml.h
    bcv_int.h
    bcvencrypt.h

Building blockcachevfsd:

  ./configure && make

Testing the system:

The blockcachevfs system has an automated test suite. Notes describing its
use may be found here.

3.1. Uploading Databases

This section illustrates how to upload databases to a blob storage account
and begin using them. It assumes that cloud storage module name and
authentication details, for example an account name and access key are stored
in a file named “account.txt” in the current directory. Example account.txt
contents for Azure Blob Storage:

  -module azure
  -user devstoreaccount1
  -auth Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==

The command-line options above can just as easily be specified directly on the
command-line. But storing them in a file, and then using the
“-f ” option supported by all commands to treat the contents
of the named file as if they were specified directly on the command-line can
be less cumbersome.

To begin, create a container and an empty manifest to which databases may be
uploaded, where $CONTAINER is the container name:

  $ blockcachevfsd create -f account.txt $CONTAINER

If the named blob storage container already exists, CBS will clobber any
manifest file within it with a new, empty, manifest file, effectively
deleting any databases in the container.

Next, upload one or more databases from the local file system to the blob
storage account. In the following, $LOCALDB is an absolute or relative path
to an SQLite database file and $DBNAME is the name used to access the
database after it has been uploaded. It is an error if there is already
a database named $DBNAME in the manifest:

  $ blockcachevfsd upload -f account.txt -container $CONTAINER $LOCALDB $DBNAME

3.2. Accessing Databases

This section explains, by way of example code, how to access a blockcachevfs
datbase stored in cloud storage using an SQL client. The full program is
available here. It should be read together with
the API documentation in file blockcachevfs.h

The example code implement a program that accepts four arguments on the command
line:

• A path to an existing directory to use for a blockcachevfs cache
  directory,
• The name of a container to attach to the new blockcachevfs VFS
  the program creates,
• A path to a database to open using the blockcachevfs VFS, and
• An SQL script to evaluate against that database.

The program creates a VFS, attaches the container, opens a database handle
and then executes the supplied SQL script. The code as presented below omits
several uninteresting features for brevity – there is no main() routine for
example. But the full version, linked above, contains everything required
for a working application.

The following block contains the required #include directives and the
authentication callback for the example application. All applications
require an authentication callback, as there is no other way to provide
authentication tokens to system. And there are currently no implementations
of cloud storage modules that do not require authentication tokens.

/*
** The code in this file creates and uses a VFS, but it doesn't use any 
** cloud storage primitives or implement a new cloud storage module, so 
** it only needs to include "blockcachevfs.h". And "sqlite3.h" of course.
*/
#include "blockcachevfs.h"
#include "sqlite3.h"

/*
** This program is hardcoded to access an Azure emulator running on port
** 10000 (the default for Azurite) of the localhost. It is also hardcoded
** to the demo account - the account built-in to the emulator with the
** well-known credentials reproduced below. 
*/ 
#define CS_STORAGE "azure?emulator=127.0.0.1:10000"
#define CS_ACCOUNT "devstoreaccount1"
#define CS_KEY "Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw=="

/*
** Authentication callback. A real application would return a different 
** authentication token based on the storage system, account name and 
** container name parameters, but since the credentials used by this
** application are hard coded, it just returns a copy of constant string
** CS_KEY.
**
** Because the API is defined such that this function must return a buffer
** allocated using sqlite3_malloc() or compatible, this implementation
** uses sqlite3_mprintf() to make a copy of the authentication token.
*/
static int csAuthCb(
  void *pCtx,
  const char *zStorage,
  const char *zAccount,
  const char *zContainer,
  char **pzAuthToken
){
  *pzAuthToken = sqlite3_mprintf("%s", CS_KEY);
  return (*pzAuthToken) ? SQLITE_OK : SQLITE_NOMEM;
}

The next block contains the start of the cloudsql() function, which does the
bulk of the work for this application. It begins by creating the VFS object.
Note that, like almost all other blockcachevfs APIs, if an error occurs the
sqlite3_bcvfs_create() function returns an error message in a buffer that
must be freed by the caller using sqlite3_free().

Once the VFS has been created successfully, the application can use the
sqlite3_bcvfs_isdaemon() API to see if the VFS has connected to a daemon or
is running in daemonless mode.

If, when sqlite3_bcvfs_create() is called, there is a daemon process using the
specified directory as its cache directory, then the VFS created by the
sqlite3_bcvfs_create() call automatically connects to the daemon and
provides read-only access. The daemon can be started using the following
command line:

    blockcachevfsd daemon $DIRECTORY

where $DIRECTORY above must be the same string as passed to cloudsql()
function below via the zDir parameter. If a daemon process was using the
directory for its cache directory, but has since exited, the
sqlite3_bcvfs_create() call will fail.

If there has never been a daemon running in the directory, then the call to
sqlite3_bcvfs_create() creates a VFS running in daemonless mode. In this mode
it requires exclusive access to the directory. If there is some other
daemonless VFS already running in the specified directory, the call to
sqlite3_bcvfs_create() fails with SQLITE_BUSY.

/*
** Open a VFS that uses directory zDir as its cache directory. Then attach
** container zCont. Next, open an SQLite handle on path zPath using the new 
** VFS and execute SQL script zSql.
*/
static int cloudsql(
  const char *zDir,               /* Directory to use for blockcachevfs cache */
  const char *zCont,              /* Container to attach */
  const char *zPath,              /* Path to open */
  const char *zSql                /* SQL to execute */
){
  int rc = SQLITE_OK;             /* Error code */
  char *zErr = 0;                 /* Error message */
  sqlite3_bcvfs *pVfs = 0;        /* VFS handle */
  sqlite3 *db = 0;                /* Database handle open on zPath */

  /* Create a VFS object. Directory zDir must already exist. If it exists
  ** and there is a daemon running in that directory, the new VFS connects
  ** to the d