SQLambda

Overview

SQLambda is a minimalistic, modern C++ wrapper for the SQLite API.

Advantages	Disadvantages
The key features are: Uses modern C++ features such as variadic templates and lambda expressions. Single file, header-only library. RAII abstractions for database connections and prepared statements. Reusability of prepared statements for fast batch inserts. A smaller API taking advantage of C++ overloading.	The main disadvantages are: No RAII abstraction for transactions No support for BLOBs (yet) No support for NULL values (yet) Error checking needs work Not well tested; still experimental

Advantages

Disadvantages

The key features are:

Uses modern C++ features such as variadic templates and lambda expressions.
Single file, header-only library.
RAII abstractions for database connections and prepared statements.
Reusability of prepared statements for fast batch inserts.
A smaller API taking advantage of C++ overloading.

The main disadvantages are:

No RAII abstraction for transactions
No support for BLOBs (yet)
No support for NULL values (yet)
Error checking needs work
Not well tested; still experimental

Tutorial

/// Shaun Harker
/// Sample Program for SQLambda
/// 2015-05-18

#include <iostream>
#include <exception>
#include <cmath>
#include <cstdint>

#include "sqlambda.h"

using namespace sqlite;

int main ( void ) {
  int64_t N = 1000000;
  try {
    // Open a database. Create if does not exist.
    database db("dbfile.db");
    // Create a table "user" in database
    db . exec ( "create table if not exists test (name Integer, data Real);" );
    // Begin a transaction
    db . exec ( "begin;" );
    // Create a prepared statement for insertion
    statement prepped = db . prepare ( "insert into test (name, data) values (?, ?);" );
    // Insert N rows into table "user"
    for ( int64_t i = 0; i < N; ++ i )
      prepped . bind ( i, std::sqrt((double)i) ) . exec ();
    // Commit the transaction
    db . exec ( "end;" );
    // Create a prepared statement for selection
    prepped = db . prepare ( "select * from test where data < 8.0;");
    // Process the results using a lambda expression
    prepped . forEach ( [] (int name, double data) {
      std::cout << "Name: " << name << "\t Data: " << data << "\n";
    });
  }
  catch (std::exception& e) {
    std::cout << e.what() << std::endl;
  }
  return 0;
}

API

Our API consists of 2 classes with 4 public methods each. They are as follows:

class database {
public:
  /// database [API]
  ///   Open a database connection
  database ( std::string const & db_name );

  /// prepare [API]
  ///   Prepare a statement with sql_expr
  statement
  prepare ( std::string const& sql_expr ) const;

  /// exec [API]
  ///   Immediately execute sql_expr
  ///   Equivalent to "prepare(sql_expr).exec()"
  void
  exec ( std::string const& sql_expr ) const;

  /// sqlite3* cast [API]
  ///   Return underlying sqlite3* pointer
  ///   This is used to access the SQLite C API
  operator sqlite3* () const;

class statement {
public:
  /// bind [API]
  ///   Bind data to a prepared statement.
  template<typename... Args> statement&
  bind ( Args... args );

  /// exec [API]
  ///   Execute the statement
  statement& exec ( void );

  /// forEach [API]
  ///   Execute the statement and call
  ///   "callback" on each resulting row
  template<typename F> statement&
  forEach ( F const& callback );

  /// sqlite3_stmt* cast [API]
  ///   Return underlying sqlite3_stmt* pointer
  ///   This is used to access the SQLite C API
  operator sqlite3_stmt* () const;

A database object wraps sqlite3 object which SQLite uses to represent database sessions. A statement object wraps an sqlite3_stmt object which SQLite uses to represent prepared statements. Both of these classes provide casting operators, so the user can get direct access to the objects and call the SQLite C API directly if needs be.

The database and statement classes use RAII (Resource Acquisition Is Initialization); the constructor of each class acquires its resource (database connection or prepared statement) and releases it when the deconstructor is called. Or nearly so. Actually, we use std::shared_ptr to hold wrappers ; so if the database (respectively, statement) object is duplicated, reference counting will prevent one duplicate going out of scope from destroying an object still in scope.

The database methods are straightforward to understand. The constructor and casting methods have already been explained. The method database::prepare creates a statement object. The database::exec method is actually just shorthand for creating a statement object and calling statement::exec on it.

The statement methods are only slightly harder to understand. Notice there is no public constructor; a statement object must be created by a database object. The casting method has already been explained. The statement::bind method is used to bind variables to prepared statements. It takes a variadic template argument. This allows it to be called with any sequence of arguments of any types. It will attempt to bind the arguments it is given to the ? slots that occur in the prepared statement. Any previous bindings will be forgotten. The statement::exec function executes the prepared statement. If there is expected output (say, for instance, the prepared statement was a SELECT statement), then this is a poor choice. Instead, statement::forEach should be called with a callback function as an argument. A callback function can be a lambda function (hence, SQLambda!) which takes arguments corresponding to the columns in the returned rows and returns void (i.e. it operates purely through side effects).

Philosophy

The design philosophy behind SQLambda is to provide an efficient pipeline between SQLite database operations and modern-style C++ programming constructs for the most common use cases. In particular, we want to provide fast mechanisms for repeated inserts with prepared statements, and provide convenient processing of the rows returned by SELECT statements.

No attempt is made to wrap the entire functionality of the SQLite C interface with C++. This decision is motivated by the desire to prevent needlessly reinventing the interface language. We simply want to allow modern idioms for common operations. For uncommon operations the well-documented C API provided by SQLite should suffice; for this reason we make sure our interface transparently exposes the underlying C API rather than attempting to invent our own abstractions.

SQLambda

Overview

Tutorial

API

Philosophy

See Also