Experience of Berkeley DB SQL API

Berkeley DB SQL API usage sample

BDB SQL API provide a set of SQLite compatibleAPI for user, and hide BDB implementation below the SQLite API, it acts as thestorage engine in place of SQLite's own BTREE.

About folder “-journal”

BDB SQL API will generate a “-journal” folderfor each database, it’s located in the same location as database file, forexample:

   sqlite3 * db;

   sqlite3_open("mydb.db", &db);

It will generate a database file “mydb.db”in current directory, and a folder named “mydb.db-journal” will also begenerated in current directory, this folder will be used as BDB environment.

Concurrent BDB SQL API

BDB SQL API use Berkeley DB's TransactionData Store product to support concurrent access, the “-journal” folder is usedas concurrent environment.

This means that Berkeley DB, unlike SQLite,does not have to lock an entire database (all the tables within a databasefile) when it acquires a lock. Instead, it locks a single Berkeley DB database page (which usually contains a smallsub-set of rows within a single table).

So in most situations, this allowsapplications using the BDB SQL interface to operate concurrently, supportmultiply-read and multiple-write.

But page is a transparent object, users don’tknow what data are resident on same page; so program cannot control to get a pagelock, and release a page lock, in other words, the page lock is not programmingcontrolled.

And if a table has smaller data rows, alldata can all resident on one page, as a result, this table will work assingle-writer-multiply-reader, because any lock could hold whole table.

Even worse, sometimes different tablescould hold same lock, (I don’t know whether this is a by design behavior, that’smean two tables resident on same page, and a single page lock mange both tables),or this is just a program bug. See below operation steps:

	process A	process B
step 1	rm -rf mydb.db mydb.db-journal
step 2	sqlite3_open("mydb.db", &db);
step 3		sqlite3_open("mydb.db", &db);
step 4	create table1
step 5	create table2
step 6	insert table1 row1
step 7	insert table2 row1
step 8	select table1
step 9	select table2
step 10		select table1
step 11		select table2
step 12	begin
step 13	insert table1 row2
step 14		select table2
step 15	commit/rollback

ProcessB is locked in step 14, but theoretically speak, it should not, because processA is just holding lock on table1, not about table2 (unless table1 and table2share a same page lock); and if we commit or rollback in step 15, the step 14could continue.

1.      Multithread support: eachthread should create its own sqlite3 db connection; i.e. sqlite3 cannot be usedsimultaneous among different threads.

2.      Database file resident on NFS: BDBSQL API database should not be placed on NFS to be shared among multiplyprocess on different hosts.

* How to build BDB with SQL API

../dist/configure --prefix=/path_to_your_install/bdbhome--enable-sql --enable-sql_compat

make

make install

* How to build your C program

gcc -g t.c -L$(BDB_HOME)/lib -ldb_sql -ldl

Sample C program: 

#include <stdio.h>#include <stdlib.h>#include <string.h>#include <sqlite3.h>static int print_column_callback(void *data, int n_columns, char **col_values, char **col_names) {        int i;        printf("  "); /* Display indent. */        for (i = 0; i < n_columns; i++) {                printf("%s\t", col_values[i] == NULL ? "" : col_values[i]);        }        printf("\n");        return 0;}static int exec_sql(sqlite3 * db, const char* sql, int callback) {        int rc = sqlite3_exec(db, sql, callback ? print_column_callback : NULL, 0, NULL);        return error_handler(db);}int error_handler(sqlite3 * db) {        int err_code = sqlite3_errcode(db);        switch(err_code) {            case SQLITE_OK:            case SQLITE_DONE:            case SQLITE_ROW:            return 0;            default:                    fprintf(stderr, "ERROR: %s. ERRCODE: %d.\n", sqlite3_errmsg(db), err_code);                return err_code;        }}void help() {    printf("create TNAME           : create a table TNAME with columns CA int, and CB varchar(10), CA is key\n");    printf("drop TNAME             : drop a table TNAME\n");    printf("insert TNAME AVAL BVAL : insert a row into TNAME(AVAL, BVAL)\n");    printf("select TNAME           : select all rows from TENAME\n");    printf("begin                  : begin transaction\n");    printf("commit                 : commit transaction\n");    printf("rollback               : rollback transaction\n");    printf("help                   : show me\n");    printf("quit                   : quit program\n");}int parseCommand(char * cmd, char * argv[]) {   const char sep[3] = " \n";   char *token = strtok(cmd, sep);   int i = 0;   while (token != NULL) {      argv[i++] = token;      token = strtok(NULL, sep);   }    return i;}intmain() {    char cmdbuffer[1024];    char sqlbuffer[1024];    char * cmds[10];    /** max parameters count */    int i = 0;        sqlite3 * db;        sqlite3_open("mydb.db", &db);    while (1) {        printf("BDB> ");        fgets(cmdbuffer, 1024, stdin);        i = parseCommand(cmdbuffer, cmds);        if (i > 0) {            if (strcmp(cmds[0], "quit") == 0) {                break;            }            else if (strcmp(cmds[0], "create") == 0) {                    sprintf(sqlbuffer, "CREATE TABLE %s(CA INT, CB VARCHAR(10), PRIMARY KEY(CA));", cmds[1]);                    exec_sql(db, sqlbuffer, 0);            }            else if (strcmp(cmds[0], "drop") == 0) {                    sprintf(sqlbuffer, "DROP TABLE %s;", cmds[1]);                    exec_sql(db, sqlbuffer, 0);            }            else if (strcmp(cmds[0], "insert") == 0) {                sprintf(sqlbuffer, "INSERT INTO %s VALUES(%s, '%s');", cmds[1], cmds[2], cmds[3]);                    exec_sql(db, sqlbuffer, 0);            }            else if (strcmp(cmds[0], "select") == 0) {                sprintf(sqlbuffer, "SELECT * FROM %s;", cmds[1], cmds[2]);                    exec_sql(db, sqlbuffer, 1);            }            else if (strcmp(cmds[0], "begin") == 0) {                sprintf(sqlbuffer, "BEGIN TRANSACTION;", cmds[1], cmds[2]);                    exec_sql(db, sqlbuffer, 1);            }            else if (strcmp(cmds[0], "commit") == 0) {                sprintf(sqlbuffer, "COMMIT TRANSACTION;", cmds[1], cmds[2]);                    exec_sql(db, sqlbuffer, 1);            }            else if (strcmp(cmds[0], "rollback") == 0) {                sprintf(sqlbuffer, "ROLLBACK TRANSACTION;", cmds[1], cmds[2]);                    exec_sql(db, sqlbuffer, 1);            }            else if (strcmp(cmds[0], "help") == 0) {                help();            }            else {                printf("unknown command: %s\n", cmds[0]);            }        }    }        sqlite3_close(db);        return 0;}