A SQLite client library written in Modern C++

smartdb是一个纯c++11开发，header-only，简洁高效的sqlite封装库.

github地址：https://github.com/chxuan/smartdb，如果您觉得不错，请不要吝啬给个start！

• 连接数据库，若test.db不存在，则会创建一个.

  db.open("test.db")
  

• INSERT操作.

  std::string sql = "INSERT INTO PersonTable(id, name, address) VALUES(?, ?, ?)";
  db.execute(sql, 1, "Jack", nullptr);
  

或者可以这样写：
cpp std::string sql = "INSERT INTO PersonTable(id, name, address) VALUES(?, ?, ?)"; db.execute(sql, std::forward_as_tuple(1, "Jack", nullptr));
正如你所看到的，execute支持变参和std::tuple作为变量替换SQL里面的?，其中nullptr可以将数据库字段设置成null值，若要进行批量插入，建议使用事务和prepare进行处理，具体操作请看Example。

• SELECT操作

  db.execute("SELECT * FROM PersonTable");
  while (!db.isEnd())
  {
      std::cout << "id: " << db.getFiled<sqlite3_int64>(0) << std::endl;
      std::cout << "name: " << db.getFiled<std::string>(1) << std::endl;
      std::cout << "address: " << db.getFiled<std::string>(2) << std::endl;
      db.moveNext();
  }
  

使用isEnd和moveNext即可遍历结果集，获取字段值需要提供字段的类型以及字段序号，没有使用字段名来代替字段序号来获取值，主要是考虑到效率问题。

• 使用聚合函数

  db.execute("SELECT COUNT(*) FROM PersonTable");
  if (db.recordCount() == 1 && !db.isEnd())
  {
      std::cout << db.getFiled<sqlite3_int64>(0) << std::endl;
  }
  

Example

#include <iostream>
#include <fstream>
#include "Timer.hpp"
#include "smartdb/Database.hpp"

void testInsertTable()
{
    smartdb::Database db;
    bool ok = db.open("test.db");

    std::string sql = "DROP TABLE PersonTable";
    ok = db.execute(sql);

    sql = "CREATE TABLE if not exists PersonTable(id INTEGER NOT NULL, name Text, address Text)";
    ok = db.execute(sql);

    Timer t;
    sql = "INSERT INTO PersonTable(id, name, address) VALUES(?, ?, ?)";
    const char* name = "Jack";
    std::string city = "Chengdu";

    // 预处理sql.
    ok = db.prepare(sql);

    // 开始事务.
    ok = db.begin();

    bool ret = true;
    for (int i = 1; i < 1000000; ++i)
    {
        // 绑定参数.
        /* ret = db.addBindValue(std::forward_as_tuple(i, name, city)); */
        ret = db.addBindValue(i, name, city);
        if (!ret)
        {
            std::cout << "Error message: " << db.getErrorMessage() << std::endl;
            break;
        }
    }
    if (ret)
    {
        // 提交事务.
        ok = db.commit();
    }
    else
    {
        // 回滚事务.
        ok = db.rollback();
    }

    // 100w 800~1000ms.
    std::cout << "Insert elapsed: " << t.elapsed() << std::endl;

    t.reset();
    // select.
    ok = db.execute("SELECT * FROM PersonTable");
    std::cout << "Record count: " << db.recordCount() << std::endl;
    while (!db.isEnd())
    {
        db.moveNext();
    }
    // 100w 300~500ms.
    std::cout << "Select elapsed: " << t.elapsed() << std::endl;
    (void)ok;
}

void testInsertTable2()
{
    smartdb::Database db;
    bool ok = db.open("test.db");

    std::string sql = "DROP TABLE PersonTable2";
    ok = db.execute(sql);

    sql = "CREATE TABLE if not exists PersonTable2(id INTEGER NOT NULL, name Text, address Text, headerImage BLOB)";
    ok = db.execute(sql);

    // 读取一张图片.
    std::ifstream fin;
    fin.open("./1.jpg", std::ios::binary);
    ok = fin.is_open();
    char buf[50 * 1025] = {"\0"};
    fin.read(buf, sizeof(buf));
    std::string image = std::string(buf, fin.gcount());
    smartdb::Blob headImage;
    headImage.buf = image.c_str();
    headImage.size = image.length();

    sql = "INSERT INTO PersonTable2(id, name, address, headerImage) VALUES(?, ?, ?, ?)";
    for (int i = 0; i < 10; ++i)
    {
        /* ok = db.execute(sql, i, "Tom", nullptr, headImage); */
        ok = db.execute(sql, std::forward_as_tuple(i, "Tom", nullptr, headImage));
    }

    // update.
    sql = "UPDATE PersonTable2 SET address=? WHERE id=?";
    if (!db.execute(sql, "中国", 0))
    {
        std::cout << "Error message: " << db.getErrorMessage() << std::endl;
        return;
    }
    std::cout << "Update success, affected rows: " << db.affectedRows() << std::endl;

    // select.
    ok = db.execute("SELECT * FROM PersonTable2 WHERE id=?", 0);
    /* ok = db.execute("SELECT * FROM PersonTable2"); */
    while (!db.isEnd())
    {
        try
        {
            std::cout << "id: " << db.getFiled<sqlite3_int64>(0) << std::endl;
            std::cout << "name: " << db.getFiled<std::string>(1) << std::endl;
            std::cout << "address: " << db.getFiled<std::string>(2) << std::endl;
            std::cout << "image size: " << db.getFiled<std::string>(3).size() << std::endl;
        }
        catch (std::exception& e)
        {
            std::cout << "Exception: " << e.what() << std::endl;
            return;
        }
        db.moveNext();
    }

    ok = db.execute("SELECT COUNT(*) FROM PersonTable2");
    if (db.recordCount() == 1 && !db.isEnd())
    {
        std::cout << "COUNT(*): " << db.getFiled<sqlite3_int64>(0) << std::endl;
    }
    (void)ok;
}

int main()
{
    testInsertTable();
    testInsertTable2();

    return 0;
}

依赖性

• boost.variant
• c++11

兼容性

• Linux x86_64 gcc 4.8, gcc4.9, gcc 5.
• Windows x86_64 Visual Studio 2015