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

1. Overview

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

2. Building, Deployment and Testing

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:

3. Usage

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 <file>" 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 daemon for read-only access. Or, if there is no such daemon,
  ** the new VFS will provide read-write daemonless access.  */
  rc = sqlite3_bcvfs_create(zDir, "myvfs", &pVfs, &zErr);

  /* Check if this is a daemon VFS or not */
  if( rc==SQLITE_OK ){
    if( sqlite3_bcvfs_isdaemon(pVfs) ){
      printf("VFS is using a daemon\n");
    }else{
      printf("VFS is in daemonless mode\n");
    }
  }

Following this, assuming it was create successfully, the code connects the authorization callback to the new VFS. And then "attaches" the cloud storage container specified by the user to the VFS.

Before the databases in a cloud storage container can be accessed, it must be attached to the VFS. Once it has been attached, SQL clients may access the databases within the container by opening paths of the form "/$CONTAINER/$DATABASE" using the blockcachevfs VFS.

  /* Configure the authorization callback. */
  if( rc==SQLITE_OK ){
    sqlite3_bcvfs_auth_callback(pVfs, 0, csAuthCb);
  }

  /* Attach the container. Specify the SQLITE_BCV_ATTACH_IFNOT flag so that
  ** it is not an error if the container is already attached. 
  **
  ** There are two reasons the container might already be attached, even
  ** though the VFS was only just created. Firstly, if this VFS is connected
  ** to a running daemon process, then some other client may have already
  ** attached the container to the daemon. Secondly, VFS objects store their
  ** state in the cache directory so that if they are restarted, all
  ** containers are automatically reattached. So if this (or some other
  ** blockcachevfs application) has run before specifying the same 
  ** cache directory, the container may already be attached.  */
  if( rc==SQLITE_OK ){
    rc = sqlite3_bcvfs_attach(pVfs, CS_STORAGE, CS_ACCOUNT, zCont, 0,
        SQLITE_BCV_ATTACH_IFNOT, &zErr
    );
  }

Once the container is attached to the VFS, the database handle is opened and the SQL script executed. Before the script is executed though, sqlite3_bcvfs_register_vtab() is called to make the virtual tables available to the database handle.

For the sake of brevity, the code below omits the sqlite3_exec() callback that is part of the full version.

  /* Open a database handle on a cloud database. */
  if( rc==SQLITE_OK ){
    rc = sqlite3_open_v2(zPath, &db, SQLITE_OPEN_READWRITE, "myvfs");
    if( rc!=SQLITE_OK ){
      zErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
    }
  }

  /* Enable the virtual table interface. */
  if( rc==SQLITE_OK ){
    rc = sqlite3_bcvfs_register_vtab(db);
  }

  /* Execute the provided SQL script. */
  if( rc==SQLITE_OK ){
    sqlite3_exec(db, zSql, 0, 0, &zErr);
  }

Finally, the cloudsql() function cleans up the two handles it allocated, outputs any error message and returns.

  sqlite3_close(db);
  sqlite3_bcvfs_destroy(pVfs);

  /* Output any error, free any error message and return. */
  if( rc!=SQLITE_OK ){
    fprintf(stderr, "Error: (%d) %s\n", rc, zErr);
  }
  sqlite3_free(zErr);
  return rc;
}

3.3. Secure Containers

When running in daemon mode, containers may be attached either securely or insecurly. By default, containers are attached to a daemon insecurely. This means that:

• Any local process that can connect to the daemon's listening socket may read and write the database, and that

• Even after the daemon process has stopped, any cached parts of databases in attached containers may be read directly from the cache file.

When containers are attached securely, all data stored in the cache file is encrypted using 128-bit AES OFB encryption. Local clients obtain the encryption key from the daemon process by supplying valid cloud storage credentials - a client is granted the encryption key only if it can prove that it already has permission to access the database within cloud storage. Cloud storage credentials are verified only once, when the client first connects, so it is quite possible for a client to remain connected to a daemon process and accessing a database long after its supplied credentials have expired.

A container is nominated as secure when it is first attached to a daemon, by specifying the SQLITE_BCV_ATTACH_SECURE flag along with the sqlite3_bcvfs_attach() call.

There are two known disadvantages to attaching containers securely:

• Blocks are stored unencrypted in cloud storage. This means that as well as local clients encrypting and decrypting individual database pages as they are read, each time a block is downloaded the entire block must be encrypted before it can be stored in the cache file. Under some cirumstances this overhead may be significant.

• If the daemon process crashes or is killed after data is written to a secure container database but before said data has been uploaded to cloud storage, the data is lost.

4. Cloud Storage Modules and Authentication

All CBS APIs and command-line tools accept a "module" specification, which determines the cloud storage system that the system operates on and how it operates. There are two built-in modules, "azure", which works with Azure Storage, and "google", which works with Google Cloud Storage. There is also an API that may be used to add new cloud storage modules.

A module specification consists of the module name optionally followed by one or more URI style key/value parameters. For example:

  azure
  azure?sas=1
  azure?emulator=127.0.0.1:10000&sas=1

There is no default module. It must always be specified explicitly.

As well as a module specification, CBS APIs and command-line tools require a user-name and an authentication value - a string containing cloud storage authentication information. Exactly how these are interpreted depends on the module specification. The values expected by the two built-in modules, and how they are used, are described in the following two sections.

4.1 The Built-In "azure" Module

The "azure" module is used to interface with Azure Storage. The value provided as a user-name is used as the Azure user name when connecting. The authentication value may either be the corresponding account access-key (shared-key access), or an Azure Shared Access Signature token, depending on the URI style parameters provided along with the module name.

One way to generate an SAS token for Azure is to use the "az" command line tool. For example, given account $ACCOUNT with access-key $ACCESSKEY, to generate an SAS token that allows read/write access to container $CONTAINER and expires on the 15th of April, 2020:

  az storage container generate-sas -o tsv 
      --account-name $ACCOUNT 
      --account-key $ACCESSKEY 
      --expiry 2020-04-15 
      --name $CONTAINER 
      --permissions dlrw

The daemon process and some command-line commands may also use read-only SAS tokens. To generate a read-only token, the --permissions option in the command above should be passed "lr" instead of "dlrw".

The following URI style parameters may be used to modify the behaviour of the azure module:

For example, to connect to a real Azure account using an SAS token for authentication purposes:

  azure?sas=1

  azure?emulator=127.0.0.1:10000
  azure?emulator=127.0.0.1:10000&sas=0

  azure

4.2 The Built-In "google" Module

The "google" module is used to connect CBS to Google Cloud Storage. The user-name parameter should be passed the project-id for the project that owns the storage bucket or buckets to be operated on. In practice, this is only required for CBS's "create" primitive, and only then if it needs to create a new cloud storage container (bucket). In all other cases, an empty string may be specified for the CBS user-name parameter.

The authorization value parameter must be passed the text of a Google Cloud access token.

The google module does not accept any URI style parameters.

5. Details and Caveats

5.1. Concurrent Access From Multiple Clients

The system supports any number of concurrent readers using either daemonless or daemon mode. Even though no locks are taken, read transactions are not usually interfered with, even if a new version of the database is pushed by another host while they are ongoing. This is because block files are never modified after they are created; new block files are uploaded for each change. And old block files are not deleted immediately (see below), so ongoing read transactions can usually continue to access them for as long as required.

If a database is modified within the cloud storage system, the modifications do not become automatically visible to existing clients. Until the container is "polled", clients continue to see the old versions of all databases. A container may be polled either by calling the sqlite3_bcvfs_poll() API:

    int sqlite3_bcvfs_poll(sqlite3_bcvfs *pFs, const char *zCont, char **pzErr);

or the equivalent PRAGMA interface. To be clear, any changes made are immediately visible to other SQL clients using the same read-write daemonless VFS object, but are not visible to SQL clients using other VFS objects until the container has been polled.

Similarly, after changes are made to a cloud storage database via a read-write daemonless VFS object, the new version of the database is not automatically uploaded to cloud storage. In order to upload changes to cloud storage so that they are available to new clients or existing clients after they have polled the container, use the sqlite3_bcvfs_upload() API:

    int sqlite3_bcvfs_upload(
      sqlite3_bcvfs *pFs,             /* VFS handle */
      const char *zCont,              /* Container (alias) to upload databases of */
      int (*xBusy)(void*,int),        /* Busy-handler callback */
      void *pBusyArg,                 /* First argument passed to xBusy */
      char **pzErr                    /* OUT: Error message */
    );

or the equivalent PRAGMA interface.

At present, it is not possible to upload changes to a database if there have been changes made to *any* database in the container since the most recent poll operation. This is not usually a problem, the container can be polled and then the upload reattempted.

However, the poll operation will fail if there have been changes to one or more databases in cloud storage for which there are also local changes yet to be uploaded, or if there are local changes to a database that has been deleted from cloud storage. It is the responsibility of the user to avoid this situation - concurrent modifications being made to the same database at multiple sites - perhaps by using the bcv_kv virtual table.

5.2. Deleting Old Blocks

Once they have been uploaded, blocks are never modified within cloud storage. Instead, when a database is modified, new block files are uploaded for the modified regions and the manifest updated to refer to the new list of blocks. This makes managing reader/writer concurrency easier, and facilitates block sharing, but also means that as time passes a large number of unused block files may accumuate within cloud storage.

Unused block files may be removed by invoking the sqlite3_bcv_cleanup() interface from bcvutil.h:

    int sqlite3_bcv_cleanup(sqlite3_bcv *p, int nSecond);

In order to avoid disturbing existing read clients, the second parameter may be used to specify the minimum number of seconds the block must have been unused for to be considered eligible for cleanup.

5.3. Block Sharing

Sometimes, a single block may be used by multiple databases within a manifest, saving cloud storage space and bandwidth. This is known as "block sharing". Block sharing occurs in two scenarios:

• If a copy of a database is made using the command line copy primitive, then the copy shares all of its blocks with the original. This takes place regardless of the configured size of block ids for the manifest file.

• If the configured block-id size for a manifest file is greater than or equal to 24 bytes, then the first 16 bytes of each block id is an md5 hash of the block contents. In this case, before each new block is uploaded, the system checks to see if there already exists a block with the same md5 hash (and hence, we assume, the same content) that can be used in its place.
  This usually only occurs when a database being uploaded is a copy or slightly modified copy of an existing database, or when an existing database is overwritten with a copy of another database using the SQLite backup API.

The block id size used by a manifest file must be configured when the manifest is first created using the command line create command or equivalent API. If block sharing using md5 hash values is required, then the block id size should be set to 24 bytes. Otherwise, it is best left set to the default value of 16 bytes to save space.

5.4. Pragma Interface

Blockcachevfs currently supports three PRAGMA statements - "PRAGMA bcv_upload" , "PRAGMA bcv_poll" and "PRAGMA bcv_client". If further PRAGMA commands are added in the future, the names of the new commands will begin with "bcv_".

PRAGMA bcv_upload

Blockcachevfs database clients may execute the following PRAGMA statement to force any changes made locally to be uploaded to the cloud storage account:

  PRAGMA [database].bcv_upload

The PRAGMA statements does not return until the upload is complete. If successful, an empty string is returned. If an error occurs and the upload cannot be completed, an SQLite error code is returned and the error message set to an English language explanation of the error.

Invoking this PRAGMA is different to using the sqlite3_bcvfs_upload() interface in that this command only uploads changes for the current database, whereas sqlite3_bcvfs_upload() uploads all locally modified databases in the container.

PRAGMA bcv_poll

Invoking "PRAGMA bcv_poll":

  PRAGMA [database].bcv_poll

is the equivalent to calling sqlite3_bcvfs_poll() on the container that the database is a part of. If successful, an empty string is returned. Otherwise, an SQLite error code is returned and the error message set to an English language explanation of the error.

PRAGMA bcv_client

Invoking "PRAGMA bcv_client":

  PRAGMA [database].bcv_client
  PRAGMA [database].bcv_client = NAME

This PRAGMA is used to set or query a cloudsqlite database connection's client name. The configured client name appears in the "client" column of the bcv_http_log virtual table for requests associated with the current database connection. It may also appear in log messages. It is not used for any other purpose. The default client name is an empty string.

5.5. Virtual Table Interface

The virtual table interface consists of three eponymous virtual tables (or two in daemon mode). It is not available automatically, but must be made available to each SQLite connection explicitly by calling the sqlite3_bcvfs_register_vtab() API from blockcachevfs.h:

    int sqlite3_bcvfs_register_vtab(sqlite3*);

The bcv_container table:

The read-only "bcv_container" table contains one row for each container attached to the VFS. It has the equivalent of the following schema:

    CREATE TABLE bcv_container(
      name      TEXT,          -- local name (alias) of container 
      storage   TEXT,          -- cloud storage system (e.g. "azure")
      user      TEXT,          -- cloud storage username
      container TEXT,          -- container name in cloud storage
      ncleanup  INTEGER        -- number of blocks eligible for cleanup
    )

Column "name" contains the local alias of the attached container (this may be the same as the remove container name). Columns "storage", "user" and "container" contain the storage module, account and cloud storage container names as specified to sqlite3_bcvfs_attach() when the container was attached.

The "ncleanup" column usually contains the number of blocks that will be deleted from cloud storage if a cleanup operation (see the sqlite3_bcv_cleanup() API)is run on the container. However, if errors or client crashes have occurred while uploading changes to cloud storage, then there may be extra unused blocks left in the cloud storage container. In this case those blocks will be deleted by the next cleanup operation, but are not included in the value stored in the "ncleanup" column of the "bcv_container" table.

The bcv_container table is available in both daemon and daemonless mode.

The bcv_database table:

The read-only "bcv_database" table contains one row for each database in each attached container. It has the equivalent of the following schema:

    CREATE TABLE bcv_database(
      container TEXT,          -- local name (alias) of container
      database  TEXT,          -- name of database
      nblock INTEGER,          -- total number of blocks in database
      ncache INTEGER,          -- number of blocks in cache
      ndirty INTEGER,          -- number of dirty blocks in cache
      walfile BOOLEAN,         -- true if transactions in local wal file
      state TEXT,              -- state of database (see below)
      nclient INTEGER,         -- current number of clients
      nprefetch INTEGER,       -- current number of ongoing prefetches
      ntrans INTEGER           -- current number of open transactions
    )

The "container" column contains the local alias of the container (the same value that is stored in the "name" column of the bcv_container table). Column "database" contains the database name.

Columns "nblock", "ncache" and "ndirty" contain the total number of blocks in the database, the number of those blocks that are currently cached locally, and the number of those cached blocks that are dirty and require uploding. It is always true that:

    nblock >= ncache >= ndirty

Boolean column "walfile" is set to true if there are currently write transactions in a local wal file (blockcachevfs databases are always in wal mode). To determine whether or not there have been local changes to a database, an application might use a query similar to:

    SELECT (walfile OR ndirty>0) AS has_local_changes FROM bcv_database WHERE...

The "state" column usually contains an empty string. There are two exceptions:

• If the database has been created using sqlite3_bcvfs_copy() but not yet uploaded, then this column contains the text 'copied'.

• If the database has been deleted using sqlite3_bcvfs_delete() but the delete operation has not yet uploaded, then this column contains the text 'deleted'.

The bcv_database table is available in both daemon and daemonless mode.

The bcv_http_log table:

The "bcv_http_log" table contains one row for each HTTP request made by the VFS or connected daemon. It has the equivalent of the following for a schema:

     CREATE TABLE bcv_http_log(
       id INTEGER,              -- Unique, monotonically increasing id value
       start_time TEXT,         -- Time request was made, as iso-8601
       end_time TEXT,           -- Time reply received, as iso-8601 (or NULL)
       method TEXT,             -- "PUT", "GET" etc.
       client TEXT,             -- Name of client that caused request
       logmsg TEXT,             -- Log message associated with request
       uri TEXT,                -- URI of request
       httpcode INTEGER         -- HTTP response code (e.g. 200)
     ) 

For those requests that can be associated with a single SQLite database handle, the contents of the "client" column is the client name as configured using the PRAGMA bcv_client command. For requests associated with a prefetch operation, it contains the string 'prefetch'.

To prevent it from consuming an ever-increasing amount of memory, entries are automatically removed from the bcv_http_log on a first-in/first-out according to the values configured for the SQLITE_BCV_HTTPLOG_TIMEOUT and SQLITE_BCV_HTTPLOG_NENTRY parameters. Or, in daemon mode, according to the values passed via the --httplogtimeout and --httplognentry command-line options.

Including various overheads, each entry may be assumed to consume 256 bytes of memory or less. So allowing 4096 entries to accumulate in the bcv_http_log table may require up to 1MiB of memory.

The bcv_stat table:

The schema of the "bcv_stat" table is:

    CREATE TABLE bcv_stat(
      name TEXT PRIMARY KEY,   -- Key value 
      value                    -- Associated payload value
    )

This read-only table contains various system "statistics" that may be of interest. The statistical value itself is found in the 'value' column, the 'name' column contains an identifier identifying the specific statistic. At present the bcv_stat table always contains three rows:

The bcv_kv table:

The schema of the "bcv_kv" table is:

    CREATE TABLE bcv_kv(
      name TEXT PRIMARY KEY,   -- Key value 
      value                    -- Associated payload value
    )

Unlike the other two eponymous virtual tables described in this section, the bcv_kv table is read-write. Applications may write arbitrary data to the table to be stored in the cloud storage container. The table is empty when the container is first created.

The contents of the bcv_kv table is stored as a separate file (an SQLite database) in the cloud container - "bcv_kv.bcv".

The bcv_kv table implements transaction-like properties. As follows:

• The first time in a database transaction that the bcv_kv table is read or written, the file is downloaded from cloud storage and cached for the duration of the transaction. All user queries - read and write - are executed against this cached version of the bcv_kv table. This ensures that each transaction sees a consistent version of the table contents.
• When a read/write transaction is committed, a new version of the bcv_kv table data is uploaded to cloud storage. At this point, if it is found that the file has been modified within cloud storage since it was downloaded at the beginning of the transaction (because some other client wrote to it), the commit fails and the transaction is rolled back. The extended error code in this case is SQLITE_BUSY_SNAPSHOT.

Because the entire bcv_kv table is downloaded (and, for write transactions, uploaded) for each transaction, applications should avoid storing large amounts of data within it.

The bcv_kv_meta table:

The schema of the "bcv_kv_meta" table is:

    CREATE TABLE bcv_kv_meta(
      name TEXT PRIMARY KEY,   -- Header field name
      value                    -- Header field value
    )

The bcv_kv_meta table is similar to the bcv_kv table, in that querying it causes cloudsqlite to download the bcv_kv.bcv database file and cache it for the remainder of the transaction. Instead of providing read/write access to the contents of the bcv_kv.bcv database, it provides read-only access to two of the http headers recieved from the cloud storage server when the file was downloaded - "Date" and "Last-Modified".

The bcv_kv_meta table always contains exactly two rows. The first contains the string 'date' in the "name" column, and the value of the timestamp from the HTTP "Date:" header, in ISO-8601 format, in the "value" column. The second row contains 'last-modified' in the "name" column, and the ISO-8601 equivalent of the timestamp from the HTTP "Last-Modified:" header in "value".

If, for some reason, the cloud storage server does not provide a "Date:" or "Last-Modified:" header, or if the contents of that header cannot be parsed, the corresponding "value" field in the bcv_kv_meta table is set to NULL.

6. Command Line Reference

The following options are supported by all or most of the commands enumerated below. All commands accept unique prefixes of any option in place of the full option string.

The following commands are supported:

 blockcachevfsd copy ?OPTIONS? DATABASE1 DATABASE2

 blockcachevfsd create ?OPTIONS? CONTAINER

      -blocksize BlOCK-SIZE
      -namebytes BLOCKID-SIZE

      -blocksize 2M
      -blocksize 2048k
      -blocksize 2097152

 blockcachevfsd delete ?OPTIONS? NAME

 blockcachevfsd destroy ?OPTIONS? CONTAINER

 blockcachevfsd download ?OPTIONS? NAME ?LOCALFILE?

      -nrequest N

 blockcachevfsd upload ?OPTIONS? DATABASE-FILE ?NAME?

      -nrequest N

The eight commands above mirror the eight APIs made available to database clients (attach and detach by blockcachevfs.h, the others by bcvutil.h). The following commands are also supported, but are considered a convenience only. The command-line options and the output of the following are subject to change at any time:

 blockcachevfsd files ?OPTIONS? CONTAINER

 blockcachevfsd list ?OPTIONS? 

 blockcachevfsd manifest ?OPTIONS? CONTAINER

7. Daemon Command Reference

The daemon process is started as follows:

 blockcachevfsd daemon ?OPTIONS? DIRECTORY 

The daemon command supports the following options: