關注開源中國OSC頭條號，獲取最新技術資訊

SQLite3++ 詳細介紹

sqlite3pp

該庫對SQLite3 API使用C++類進行包裝，支持幾乎所有SQLite3功能（打開數據庫、查詢、執行命令、事物等）。

示例代碼如下：

database 數據庫

sqlite3pp::database db("test.db");
db.execute("INSERT INTO contacts (name, phone) VALUES ('Mike', '555-1234')");

command 命令

sqlite3pp::command cmd(
 db, "INSERT INTO contacts (name, phone) VALUES (?, ?)");
cmd.binder() << "Mike" << "555-1234";
cmd.execute();
sqlite3pp::command cmd(db, "INSERT INTO contacts (name, phone) VALUES (?, ?)");
cmd.bind(1, "Mike", sqlite3pp::nocopy);
cmd.bind(2, "555-1234", sqlite3pp::nocopy);
cmd.execute();
sqlite3pp::command cmd(
 db, "INSERT INTO contacts (name, phone) VALUES (?100, ?101)");
cmd.bind(100, "Mike", sqlite3pp::nocopy);
cmd.bind(101, "555-1234", sqlite3pp::nocopy);
cmd.execute();
sqlite3pp::command cmd(
 db, "INSERT INTO contacts (name, phone) VALUES (:user, :phone)");
cmd.bind(":user", "Mike", sqlite3pp::nocopy);
cmd.bind(":phone", "555-1234", sqlite3pp::nocopy);
cmd.execute();
sqlite3pp::command cmd(
 db, "INSERT INTO contacts (name, phone) VALUES (:user, '555-0000');"
 "INSERT INTO contacts (name, phone) VALUES (:user, '555-1111');" 

 "INSERT INTO contacts (name, phone) VALUES (:user, '555-2222')");
cmd.bind(":user", "Mike", sqlite3pp::nocopy);
cmd.execute_all();

transaction 事務

sqlite3pp::transaction xct(db);
{
 sqlite3pp::command cmd(
 db, "INSERT INTO contacts (name, phone) VALUES (:user, :phone)");
 cmd.bind(":user", "Mike", sqlite3pp::nocopy);
 cmd.bind(":phone", "555-1234", sqlite3pp::nocopy);
 cmd.execute();
}
xct.rollback();

query 查詢

sqlite3pp::query qry(db, "SELECT id, name, phone FROM contacts");for (int i = 0; i < qry.column_count(); ++i) {
 cout << qry.column_name(i) << "\t";
}
for (sqlite3pp::query::iterator i = qry.begin(); i != qry.end(); ++i) { for (int j = 0; j < qry.column_count(); ++j) {
 cout << (*i).get(j) << "\t";
 }
 cout << endl;
}
for (sqlite3pp::query::iterator i = qry.begin(); i != qry.end(); ++i) { int id; char const* name, *phone; std::tie(id, name, phone) =
 (*i).get_columns(0, 1, 2);
 cout << id << "\t" << name << "\t" << phone << endl;
}
for (sqlite3pp::query::iterator i = qry.begin(); i != qry.end(); ++i) {
 string name, phone;
 (*i).getter() >> sqlite3pp::ignore >> name >> phone;
 cout << "\t" << name << "\t" << phone << endl;
}
for (auto v : qry) {
 string name, phone;
 v.getter() >> sqlite3pp::ignore >> name >> phone;
 cout << "\t" << name << "\t" << phone << endl;
}

attach

sqlite3pp::database db("foods.db");
db.attach("test.db", "test");
sqlite3pp::query qry(
 db, "SELECT epi.* FROM episodes epi, test.contacts con WHERE epi.id = con.id");

backup 備份

sqlite3pp::database db("test.db");
sqlite3pp::database backupdb("backup.db");
db.backup(backupdb);
db.backup(
 backupdb,
 [](int pagecount, int remaining, int rc) {
 cout << pagecount << "/" << remaining << endl;if (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED) { // sleep or do nothing.}
 });

callback 回調

struct rollback_handler{ void operator()() {
 cout << "handle_rollback" << endl;
 }
};
sqlite3pp::database db("test.db");
db.set_commit_handler([]{ cout << "handle_commit\n"; return 0; });
db.set_rollback_handler(rollback_handler());
int handle_authorize(int evcode, char const* p1, char const* p2, char const* dbname, char const* tvname) {
 cout << "handle_authorize(" << evcode << ")" << endl; return 0;
}
db.set_authorize_handler(&handle_authorize);
struct handler{ handler() : cnt_(0) {} void handle_update(int opcode, char const* dbname, char const* tablename, int64_t rowid) {
 cout << "handle_update(" << opcode << ", " << dbname << ", " << tablename << ", " << rowid << ") - " << cnt_++ << endl;
 } int cnt_;
};using namespace std::placeholders;db.set_update_handler(std::bind(&handler::handle_update, &h, _1, _2, _3, _4));

function

int test0()
{ return 100;
}
sqlite3pp::database db("test.db");
sqlite3pp::ext::function func(db);
func.create("test0", &test0);
void test1(sqlite3pp::ext::context& ctx)
{
 ctx.result(200);
}void test2(sqlite3pp::ext::context& ctx) 

{
 string args = ctx.get(0);
 ctx.result(args);
}void test3(sqlite3pp::ext::context& ctx)
{
 ctx.result_copy(0);
}
func.create("test1", &test1);
func.create("test2", &test2, 1);
func.create("test3", &test3, 1);
func.create("test4", []{ return 500; });
string test5(string const& value)
{ return value;
}
string test6(string const& s1, string const& s2, string const& s3)
{ return s1 + s2 + s3;
}
func.create("test5", [](int i){ return i + 10000; });
func.create("test6", &test6);
sqlite3pp::query qry(
 db, "SELECT test0(), test1(), test2('x'), test3('y'), test4(), test5(10), "
 "test6('a', 'b', 'c')");

aggregate

void step(sqlite3pp::ext::context& c)
{ int* sum = (int*) c.aggregate_data(sizeof(int));
 *sum += c.get(0);
}void finalize(sqlite3pp::ext::context& c)
{ int* sum = (int*) c.aggregate_data(sizeof(int));
 c.result(*sum);
}
sqlite3pp::database db("foods.db");
sqlite3pp::ext::aggregate aggr(db);
aggr.create("aggr0", &step, &finalize);
struct mycnt{ void step() {
 ++n_;
 } int finish() {return n_;
 } int n_; 

};
aggr.create("aggr1");
struct strcnt{ void step(string const& s) {
 s_ += s;
 } int finish() {return s_.size();
 }
 string s_;
};struct plussum{ void step(int n1, int n2) {
 n_ += n1 + n2;
 } int finish() {return n_;
 } int n_;
};
aggr.create("aggr2");
aggr.create("aggr3");
sqlite3pp::query qry(
 db, "SELECT aggr0(id), aggr1(type_id), aggr2(name), aggr3(id, type_id) "
 "FROM foods");

loadable extension 加載擴展

#define SQLITE3PP_LOADABLE_EXTENSION#include int sqlite3_extension_init(
 sqlite3 *pdb, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi);
 sqlite3pp:database db(sqlite3pp::ext::borrow(pdb)); // pdb is not closed since db just borrows it.}

開源中國旨在為中國IT技術人員提供一個全面的、快捷更新的用來檢索開源軟件以及交流使用開源經驗的平臺，目前已收錄45000多款開源軟件。關注開源中國OSC頭條號，每日獲取開源軟件推薦，點擊“瞭解更多”閱讀原文，獲取 Sqlite3++ 的詳細介紹與下載地址。

閱讀更多 開源社區OSC 的文章

關鍵字: 技術 軟件 MIKE