sqlite3cpp

Have a cool C/C++ function and want to use it with SQL?

• Use sqlite3cpp::database directly,

sqlite3cpp::database db(":memory:");
mydb.create_scalar("coolFunc", [](std::string_view input) {
  // do cool stuff
  return "done!";
});

• or attach sqlite3cpp::database to a sqltie3 raw instance

sqlite3 *db = nullptr;
sqlite3_open(":memory:", &db);
sqlite3cpp::database mydb(db);
mydb.create_scalar("coolFunc", [](std::string_view input) {
  // do cool stuff
  return "done!";
});
sqlite3_close(db);

That's it! Now you can use the coolFunc. e.g.

char const *query = "select coolFunc(colA) from Table";
sqlite3_exec(db, query, 0, 0, 0);
// or
mydb.execute(query);

Source

https://github.com/yangacer/sqlite3cpp

Features

Create SQL scalar function with C++ lambda

Prototype of the created scalar function is auto generated by sqlite3cpp, based on prototype of supplied lambda or free function.

using namespace sqlite3cpp;

database db(":memory:");

// Create table and insert some data
db.executescript(
  "begin;"
  "create table T (a INT, b TEXT);"
  "insert into T values(1, 'test1');"
  "insert into T values(2, 'test2');"
  "insert into T values(2, 'abc');"
  "insert into T values(3, 'test3');"
  "commit;"
  );

// Create a `mutiply` scalar function with lambda
db.create_scalar("mutiply", [](int x, int y) {
    return x * y;
});

char const *query = "select a, mutiply(a,a) from T;";

for(auto const &row : c.execute(query)) {
    auto [ a, b ] = row.to<int, int>();

    // ...
}

Create SQL aggregate with functor

Again, sqlite3cpp detects and generates wrapped aggregate for you. You do not have to specify function prototypes.

// Create a `stdev` aggregate
struct stdev {
    void step(int val) {
        m_cnt ++;
        m_sum += val;
        m_sq_sum += val * val;

    }
    double finalize() {
        auto avg = (double)m_sum / m_cnt;
        return std::sqrt((double)(m_sq_sum - avg * avg * m_cnt) / (m_cnt -1));
    }
    size_t m_cnt = 0;
    int m_sum = 0, m_sq_sum = 0;
};


db.create_aggregate<stdev>("stdev");

// Invoke the stdev aggregate in SQL
char const *query = "select stdev(a) from T";
for(auto const &row : db.execute(query)) {
    auto [a] = row.to<double>();
    // EXPECT_DOUBLE_EQ(0.81649658092772603, a);
}

Query with range-based for-loop and typed parameter bindings

char const *query = "select * from T where a > ? and a < ? and b like ?";
string pattern = "test%";

for(auto const &row : db.execute(query, 1, 3, pattern)) {
    auto [a, b] = row.to<int, std::string>();

    // do something with a or b
}

RAII Transaction

database db(":memory:");
try {
  transaction trns(db);
  // Execute SQL statements within the transaction
  trns.commit();
} catch (error &e) {
  // The transaction is rollback if an exception was raised
  cerr << e.what() << endl;
}

Note: End transaction in destructor of the transaction object may fail as well as throw exception. However, it's error-prone to throw exceptions as of destruction. Current implementation catches all exceptions silently.

Get Started

Requirements

• CMake 3.0+
• A C++17 compatible compiler (VS 2017, clang 6.0+, or gcc 7.0+)

Install

git clone https://github.com/yangacer/sqlite3cpp
cd sqlite3cpp
mkdir build
cd build
cmake .. # -DCMAKE_BUILD_TYPE=Release
make
sudo make install

Hello, World!

Compile with: g++ -std=c++17 -o hello hello.cpp -lsqlite3cpp

#include <iostream>
#include "sqlite3cpp.h"

int main() {
    using namespace sqlite3cpp;
    using std::string;

    database db(":memory:");

    db.executescript(
      "create table T (msg TEXT, num INTEGER);"
      "insert into T values('Hello, World!', 1);"
      "insert into T values('Hello, sqlite3cpp!', 2);"
      );

    char const *query = "select msg from T where msg like ? and num > ?";
    for(auto const &row : db.execute(query, "%World%", 0) {
        auto [ msg, num ] = row.to<string, int>();
        std::cout << "msg: " << msg << " num: " << num << std::endl;
    }

    // Output: msg: Hello, World! num: 1
}

Usage Mapping

sqlite3	sqlite3cpp
sqlite3 *	database
sqlite3_stmt *	cursor, row

For mapped classes of sqlite3cpp, you can obtain underlying sqlite3 struct for inoking sqlite3 native functions. e.g.

database db(":memory:");

sqlite3 *db_impl = db.get();

// take the native `sqlite3_changes` for example
printf("Modified rows: %d", sqlite3_changes(db_impl, ...));

cursor c = db.make_cursor();

c.execute("select * from T");

sqlite3_stmt *stmt = c.get();

// Print column names
for(int i = 0; i < sqlite3_column_count(stmt); ++i) {
    printf("%s\t", sqlite3_column_name(stmt, i));
}
printf("\n");

for(auto const &row : c) {
    printf("%d\t", sqlite3_column_bytes(row.get()));
    // Note: row.get() also returns `sqlite3_stmt *`

}

WARNING: Lifetime of sqlite3_stmt * ends when the associated cursor reaching end of results (SQLITE_DONE). Consequntly the pointer becomes a dangling one. Please use it with caution.