Berkeley DB——Cursor

Introduction

Berkeley DB的游标（Dbc）和关系数据库的游标是类似的——一种可以迭代数据库中的记录的装置。对于重复记录，使用游标来访问他们会更加方便（使用Db的bulk get来得到多条记录是性能最高的做法）。另外，通过游标可以一条条地操作（修改和删除）记录。

在使用游标之前，必须先使用Db::cursor方法打开游标：

int Db::cursor(DbTxn *txnid, Dbc **cursorp, u_int32_t flags);

游标打开后可以通过游标search和write记录。使用完毕后，必须关闭之：

int Dbc::close(void);

 

例子：

#include <db_cxx.h>   ...   Dbc *cursorp; Db my_database(NULL, 0);   // Database open omitted for clarity   // Get a cursor my_database.cursor(NULL, &cursorp, 0);   // Database and cursor open omitted for clarity // Do something… if (cursorp != NULL)     cursorp->close();

Getting Records from DB Using Cursor

打开数据库，并打开此数据库的游标后，就可以使用该游标来search DB来遍历符合条件的记录了。

Dbc::get方法的flag参数可以取多个值，常用的有：

Flag	Description
DB_CURRENT	返回游标当前所指的key/data
DB_FIRST	游标指向第一条记录，并返回该key/data
DB_GET_BOTH	只有key/data都匹配才返回该key/data
DB_GET_RECNO	返回行号。数据库必须是B树的，且其flag为DB_RECNUM。
DB_LAST	和DB_FIRST对应
DB_NEXT	游标指向下条记录，并返回下个key/data
DB_PREV	和DB_NEXT对应
DB_MULTIPLE	返回该key的所有记录。数据库是允许重复记录的。

 

例子：

#include <db_cxx.h>   ...   Db my_database(NULL, 0); Dbc *cursorp;   try {     // Database open omitted for clarity       // Get a cursor     my_database.cursor(NULL, &cursorp, 0);       Dbt key, data;     int ret;       // Iterate over the database, retrieving each record in turn.     while ((ret = cursorp->get(&key, &data, DB_NEXT)) == 0) {         // Do interesting things with the Dbts here.     }     if (ret != DB_NOTFOUND) {         // ret should be DB_NOTFOUND upon exiting the loop.         // Dbc::get() will by default throw an exception if any         // significant errors occur, so by default this if block         // can never be reached.     } } catch(DbException &e) {         my_database.err(e.get_errno(), "Error!"); } catch(std::exception &e) {         my_database.errx("Error! %s", e.what()); }   // Cursors must be closed if (cursorp != NULL)     cursorp->close();   my_database.close(0);

 

Operating Records Using Cursor

Dbc对象提供了put、del和get方法，通过这些方法我们可以写入、删除和获取记录。

例子：

#include "stdafx.h" #include <iostream> #include <db_cxx.h> #include <string.h>   using namespace std; int _tmain(int argc, _TCHAR* argv[]) {        Db db(NULL,0);               u_int32_t oFlags = DB_CREATE; // Open flags;        try        {               db.open(NULL,                // Transaction pointer                      "my_db.db",          // Database file name             NULL,                // Optional logical database name             DB_BTREE,            // Database access method             oFlags,              // Open flags             0);                  // File mode (using defaults)               db.truncate(NULL,0,0);               float money = 122.45;               char *description = "Grocery bill.";                 Dbt key(&money, sizeof(float));               Dbt data(description, strlen(description)+1);               int ret = db.put(NULL, &key, &data, DB_NOOVERWRITE);               cout<<"put data--"<<description<<endl;                 ret = db.get(NULL, &key, &data, DB_GET_BOTH);               cout<<"get key--"<<*((float*)key.get_data())<<endl;               cout<<"get data--"<<(char *)data.get_data()<<endl;                 money = 111;               description = "James--------------------";                data.set_data(description);               data.set_size(strlen(description)+1);               db.put(NULL,&key,&data,DB_NOOVERWRITE);               ret = db.get(NULL, &key, &data, DB_GET_BOTH);               cout<<"get key--"<<*((float*)key.get_data())<<endl;               cout<<"get data--"<<(char *)data.get_data()<<endl;                 money = 191;               description = "Mike";                 data.set_data(description);               data.set_size(strlen(description)+1);               db.put(NULL,&key,&data,DB_NOOVERWRITE);               ret = db.get(NULL, &key, &data, DB_GET_BOTH);               cout<<"get key--"<<*((float*)key.get_data())<<endl;               cout<<"get data--"<<(char *)data.get_data()<<endl;                   Dbc* cursor;               db.cursor(NULL,&cursor,0);               cout<<"open cursor"<<endl;                             while((ret = cursor->get(&key,&data,DB_PREV)) != DB_NOTFOUND)               {                      cout<<"get key--"<<*((float*)key.get_data())<<endl;                      cout<<"get data--"<<(char *)data.get_data()<<endl;               }               if (cursor != NULL)               {                      cursor->close();               }                 money = 191;               description = "Mike";               data.set_data(description);               data.set_size(strlen(description)+1);               db.cursor(NULL,&cursor,0);               cout<<"delete 191..."<<endl;               while((ret = cursor->get(&key,&data,DB_SET)) == 0 )               {                      cursor->del(0);               }               if (cursor != NULL)               {                      cursor->close();               }                 cout<<"after delete 191..."<<endl;               db.cursor(NULL,&cursor,0);               while((ret = cursor->get(&key,&data,DB_PREV)) != DB_NOTFOUND)               {                      cout<<"get key--"<<*((float*)key.get_data())<<endl;                      cout<<"get data--"<<(char *)data.get_data()<<endl;               }                 if (cursor != NULL)               {                      cursor->close();               }        }        catch(DbException &e)        {               cerr<<"DBException:"<<e.what();        }        catch(std::exception &e)        {               cerr<<"DBException:"<<e.what();        }          system("pause");        return 0; }

 

Joining Cursors

Berkeley DB支持两个数据库之间的“对等”连接，即当数据库A的cursor和数据库B的cursor进行join时，返回数据库A中key和数据库B的data相等的记录。也就是说，实际的数据是存在A中的data里面，而通过数据库B的key来获得A的data。比如：

数据库A：

A-Key	A-Data
apple	Convenience Store
blueberry	Farmer's Market
peach	Shopway
pear	Farmer's Market
raspberry	Shopway
strawberry	Farmer's Market

数据库B：

B-Key	B-Data:
Blue	blueberry
red	apple
red	raspberry
red	strawberry
yellow	peach
yellow	pear

则根据B的一条记录（key为Blue），join后应该返回的是数据库A的key为blueberry的记录，即data为Farmer's Market。

例子：

#include <db_cxx.h> #include <string.h>   ...   // Exception handling omitted   int ret;   Db automotiveDB(NULL, 0); Db automotiveColorDB(NULL, 0); Db automotiveMakeDB(NULL, 0); Db automotiveTypeDB(NULL, 0);   // Database and secondary database opens omitted for brevity. // Assume a primary database: //   automotiveDB // Assume 3 secondary databases: //   automotiveColorDB  -- secondary database based on automobile color //   automotiveMakeDB  -- secondary database based on the manufacturer //   automotiveTypeDB  -- secondary database based on automobile type   // Position the cursors Dbc *color_curs; automotiveColorDB.cursor(NULL, &color_curs, 0); char *the_color = "red"; Dbt key(the_color, strlen(the_color) + 1); Dbt data; if ((ret = color_curs->get(&key, &data, DB_SET)) != 0) {     // Error handling goes here }   Dbc *make_curs; automotiveMakeDB.cursor(NULL, &make_curs, 0); char *the_make = "Toyota"; key.set_data(the_make); key.set_size(strlen(the_make) + 1); if ((ret = make_curs->get(&key, &data, DB_SET)) != 0) {     // Error handling goes here }   Dbc *type_curs; automotiveTypeDB.cursor(NULL, &type_curs, 0); char *the_type = "minivan"; key.set_data(the_type); key.set_size(strlen(the_type) + 1); if ((ret = type_curs->get(&key, &data, DB_SET)) != 0) {     // Error handling goes here }   // Set up the cursor array Dbc *carray[4]; carray[0] = color_curs; carray[1] = make_curs; carray[2] = type_curs; carray[3] = NULL;   // Create the join Dbc *join_curs; if ((ret = automotiveDB.join(carray, &join_curs, 0)) != 0) {     // Error handling goes here }   // Iterate using the join cursor while ((ret = join_curs->get(&key, &data, 0)) == 0) {     // Do interesting things with the key and data }   // If we exited the loop because we ran out of records, // then it has completed successfully. if (ret == DB_NOTFOUND) {      // Close all our cursors and databases as is appropriate,  and      // then exit with a normal exit status (0). }

从上面的例子我们可以看到，需要构建一个游标的array，并且最后一个元素是NULL。Berkeley DB用这个NULL来标志array的结尾，从而知道array中有几个cursor。

Join游标的步骤为：

1.         在第二个数据库上打开两个或者多个游标。

2.         将这些游标移动到满足你指定的条件的记录上。

3.         创建一个游标数组，大小为游标数量+1，最后一个元素放入NULL。

4.         声明一个新的cursor，以接收join的结果。

5.         迭代记录。

6.         关闭这些游标。