Experience of Berkeley DB SQL API
来源:互联网 发布:女生英文名推荐 知乎 编辑:程序博客网 时间:2024/05/29 18:42
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 locksa 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;}
- Experience of Berkeley DB SQL API
- Berkeley DB的sql实现
- [收藏]Berkeley DB文章集合--环境API
- Berkeley DB 4.8 的 dbstl API
- Berkeley DB 4.8 的 dbstl API
- Berkeley DB文章集合--环境API
- 【搜索引擎】Berkeley DB的API封装
- SQL语句对比Oracle Berkeley DB 学习指南
- Berkeley DB 中常用 SQL 函数使用指南
- Berkeley DB
- Berkeley DB
- Berkeley DB
- Berkeley DB
- Berkeley DB
- Berkeley DB
- Berkeley DB
- Berkeley DB
- Berkeley DB
- Linux服务器发包
- spring mvc 小记(八):MyBatis整合redis缓存
- 算法之将字符串的部分移动
- 自己动手写一个小型的TCP/IP协议
- 【codeforces237C】Primes on Interval
- Experience of Berkeley DB SQL API
- jdk和tomcat配置
- C++空类的大小
- Linux命令eval用法
- 访问servlet的配置参数
- 汉明距离图片相似度比较
- 【RBM】受限玻尔兹曼机(Restricted Boltzmann Machine, RBM) 简介
- Oracle Primavera Contract Management 最新补丁更新R14.2.5.1
- python:赋值和相等,=和==