Documentation boost.sqlite

Introduction

This small C++-14 library, extending the excellent sqlite API for C++. It sticks as close as possible to the sqlite API, but adopts thing like errors or ranges.

It is therefore highly recommended to study the sqlite documentation.

Accessing a database

Connecting to a database

The sqlite::connection holds a connection handle, which is automatically closes on destruction.

examples/tutorial.cpp

  sqlite::connection conn{"./my_db.db"};

Sqlite supports in memory databases, which are created with the special path ":memory:". This is provided as a constant, called in_memory.

  sqlite::connection conn{sqlite::in_memory};

Executing SQL

Once the conn is created, the common first step is to create a database schema. This can be done with the execute command, which allows the execution of multiple statements with one function invocation.

  conn.execute(R"(
    CREATE TABLE IF NOT EXISTS users (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       name TEXT NOT NULL,
       age INTEGER NOT NULL);
    INSERT INTO users(name, age) VALUES('Alice', 30);
    INSERT INTO users(name, age) VALUES('Bob', 25);
  )");

Like most functions in sqlite, the execute function comes overloaded to allow two modes of error handling: - errors as exception - errors assigned to a boost::system::error_code and error_info

The second version takes both types by reference after the main arguments, i.e.:

examples/tutorial_ec.cpp

  conn.execute(R"(
    CREATE TABLE IF NOT EXISTS users (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       name TEXT NOT NULL,
       age INTEGER NOT NULL);
    INSERT INTO users(name, age) VALUES('Alice', 30);
    INSERT INTO users(name, age) VALUES('Bob', 25);
  )", ec, ei);

The boost::system::error_code holds the actual integer representing the error, while boost::sqlite::error_info is a string-like type holding the error message.

For brevity, the tutorial section will use the exception overloads. See the Reference for details on the overloads.

Querying data

Once the database provides data it can be queried like this:

  sqlite::resultset q = conn.query("SELECT name, age FROM users ORDER BY id ASC;");

The [resultset] is a forward range of the data queried from the database. The first row of the result is already read.

  assert(q.current()[0].get_text() == "Alice");
  assert(q.read_next()); // true if it's the last row!
  assert(q.current()[0].get_text() == "Bob");

The resultset has begin()/end() member functions, so that it can be used in a ranged for loop.

The [row] type is a range of [field]s, which has all the information for the field requested.

In many use-cases this is however superfluous because the structure of the table is known.

To make this easy, you can call query with a type parameter (query<T>) which will yield a static_resultset<T>. T can either be a tuple of a struct.

A tuple will assign the query results by order.

  for (const auto & t :
          conn.query<std::tuple<sqlite::string_view, std::int64_t>>("SELECT name, age FROM users;"))
    std::cout << "User " << std::get<0>(t) << " is " << std::get<1>(t) << " old." << std::endl;

Using a struct will assign the query results by name, i.e. it will reorder results accordingly. If you’re using a standard lower than C++20, the struct needs to be described.

struct users { std::string name; std::int64_t age; };
BOOST_DESCRIBE_STRUCT(users, (), (name, age));
  for (const auto & a : conn.query<users>("SELECT age, name FROM users;"))
    std::cout << "User " << a.name << " is " << a.age << " old." << std::endl;

The types allowed in the static_resultset are:

std::int64_t
double
'blob', blob_view
[string], [string_view]
std::optional<T>/boost::optional<T> with any of the types above.

blob_view and string_view have a limited lifetime. They might be invalidated when the next row is read!

By default, the static_result does not check types, following sqlite’s general dynamic type system. You can eat type checks, by using strict mode:

  for (const auto & a : conn.query<users>("SELECT age, name FROM users;").strict())
    std::cout << "User " << a.name << " is " << a.age << " old." << std::endl;

Statements

To avoid sql injection, querys and execution should not be build dynamically, but be done with parameters.

This is done by creating a statement and then executing it with parameters.

  conn.prepare("insert into users (name, age) values (?1, ?2), (?3, ?4)")
      .execute({"Paul", 31, "Mark", 51});

The syntax in the sqlite provides multiple versions. ? is a positional parameter, that can have an explicit index at the end (e.g. ?3) as in the above example. :, @, $ are named parameters, e.g. $age. Named parameters can make queries more readable and provide more checks.

See the sqlite syntax for more details.

A prepared statement can be used multiple times.

  {
    sqlite::transaction t{conn}; // use a transaction to speed this up

    auto st = conn.prepare(R"(insert into users ("name", age) values ($name, $age))");

    st.execute({{"name", "Allen"}, {"age", 43}});
    st.execute({{"name", "Tom"},   {"age", 84}});

    t.commit();
  }

Calling .execute on an lvalue of statement will transfer ownership to the resultset, while calling .execute on an rvalue will produce a resultset that points to the statement.

The result of an .execute can also be turned into a static_resultset<T> by using .execute<T>.

Adding functions

Scalar Functions

Scalar functions are data transformers.

  sqlite::create_scalar_function(
      conn,
      "to_upper",
      [](sqlite::context<>, (1)
             boost::span<sqlite::value, 1u> val (2)
             ) -> std::string
      {
        if (val[0].type() != sqlite::value_type::text)
          throw std::logic_error("Value must be string"); (3)
        auto txt = val[0].get_text();
        std::string res;
        res.resize(txt.size());
        std::transform(txt.begin(), txt.end(), res.begin(), [](char c){return std::toupper(c);});
        return res;
      },
      sqlite::deterministic (4)
      );

  auto qu = conn.query("SELECT to_upper(name) FROM users WHERE name == 'Alice';");
  assert(qu.current()[0].get_text() == "ALICE");

1	The context is optional, and can be used to share between invocations, within the same query or to set errors/return values.
2	Let this function take 1 parameter. If this is `span::extent` it will be any size. Functions can be overloaded by the number of parameters.
3	Any exception will be turned into an sqlite-error code.
4	The function is determistic, i.e. it has no side effects (see Function Flags)

The return type T of the function gets transformed into an sqlite value by using a tag_invoke overload. This interface is public and meant to be extended.

void tag_invoke(set_result_tag, sqlite3_context * ctx, T);

The builtin types are:

blob
zero_blob
double
std::int64_t
nullptr
string_view
std::string
sqlite::value
variant2::monostate
error
std::unique_ptr<T> (see the pointer passing interface)
variant2::variant<Ts…> if all Ts are supported
result<T>
boost::json::value //when including boost/sqlite/json.hpp

The translation of the exceptions happens as follows:

Type Sqlite error code

Type	Sqlite error code
`boost::system::system_error`	`.code()`
`std::bad_alloc`	SQLITE_NOMEM
`std::length_error`	SQLITE_TOOBIG
`std::out_of_range`	SQLITE_RANGE
`std::logic_error`	SQLITE_MISUSE
`…`	SQLITE_ERROR

boost::system::system_error

.code()

std::bad_alloc

SQLITE_NOMEM

std::length_error

SQLITE_TOOBIG

std::out_of_range

SQLITE_RANGE

std::logic_error

SQLITE_MISUSE

…

SQLITE_ERROR

You can also return an <<`result`,sqlite::result>> type instead of throwing exceptions.

A ggregate functions

An aggregate function builds a value from multiple values of the same column, e.g:

select avg(age) from users ;

avg is a built-in function, but Below is a toy example. We count how many retirees are among the users, based on a retirement age.

  struct retirees
  {
    std::int64_t retirement_age;
    std::int64_t count = 0u;
    retirees(std::size_t retirement_age)
      : retirement_age(retirement_age) {}


    void step(span<sqlite::value, 1> args)
    {
      if (args[0].get_int() >= retirement_age)
        count += 1;
    }
    std::int64_t final() { return count; }
  };
  sqlite::create_aggregate_function<retirees>(conn, "retirees", std::make_tuple(65));

  q = conn.query("select retirees(age) from users;");
  std::cout << "The amount of retirees is " << q.current()[0].get_text() << std::endl;

The retirees object will be constructed for every query it’s used in, and the parameters passed witht he make_tuple will be passed to the constructor. The step will be called for every value and final at the end when the query has ended and a value is required.

The value types & exceptions are the same as for the scalar function.

Window functions

Window functions look similar to aggregate functions, they only need an inverse function, that shares the signature with step.

It is recommended to consult the sqlite window function documentation.

Window functions are only available for sqlite 3.25.0 and higher.

Extension Modules

This library can also be used to run-time loadable extensions that can be used by other applications, e.g. the sqlite3 CLI.

In order to write this, you’ll need to include boost/sqlite/extension.hpp, and write a named module like so:

BOOST_SQLITE_EXTENSION(testlibrary, conn)
{
  // create a function that can be used in the plugin
  create_scalar_function(
    conn, "assert",
    [](boost::sqlite::context<>, boost::span<boost::sqlite::value, 1u> sp)
    {
        if (sp.front().get_int() == 0)
          throw std::logic_error("assertion failed");
    });
}

SELECT load_extension('./test_library');

select assert((3 * 4) = 12);

In order to build this, you’ll to link against Boost::sqlite_ext instead of Boost::sqlite.

Including the extension.hpp header will also define BOOST_SQLITE_COMPILE_EXTENSION, which will include sqlite3ext.h instead of sqlite3.h and create an inline namespace ext inside boost::sqlite.

This prevents linker issues, but will not allow you to mix extension and non-extension code in one translation unit.

Virtual tables

A Virtual Table is an object that is registered with a [connection]. It looks like a regular table, but is actually invoking virtual methods internally. This allows users to expose functionality from C++ to sqlite through a table interface.

The implementation uses abstract base classes to make interfaces easy to implement. You can disable the use of virtual by defining BOOST_SQLITE_VIRTUAL which will yield linker errors instead.

Eponymous

And eponymous table is a virtual table the exists without needing to be created from sqlite.

Such a table can either expose global values or can be used to build table-valued functions.

Eponymous value table

The easiest example of a data structure to expose to sql, is a simple ordered map:

container::flat_map<std::string, std::string> data;

To create a virtual table it needs to have a cursor first. The cursor is what runs over the table, akin to an iterator.

struct ordered_map_cursor final : sqlite::vtab::cursor<sqlite::string_view> (1)
{
  container::flat_map<std::string, std::string> &data;
  ordered_map_cursor(container::flat_map<std::string, std::string> &data) : data(data) {}
  bool inverse = false;

  using const_iterator = typename container::flat_map<std::string, std::string>::const_iterator;
  const_iterator begin{data.begin()}, end{data.end()}; (2)

  sqlite::result<void> next() override { if (inverse) end--; else begin++; return {};} (3)

  sqlite::result<sqlite3_int64> row_id() override
  {
    return {system::in_place_error, SQLITE_MISUSE, (4)
            "this shouldn't be called, we're omitting the row id"};
  }
  sqlite::result<sqlite::string_view> column(int i, bool /*nochange*/) override (5)
  {
    auto & elem = inverse ? *std::prev(end) : *begin;

    if (i == 0)
      return elem.first;
    else
      return elem.second;
  }
  bool eof() noexcept override (6)
  {
    return begin == end;
  }
};

1	Declare the value the cursor produces
2	The data the cursor operates on
3	Advance the cursor by one
4	The module is declared without ROW id, see below
5	Get the actual column value
6	Tell sqlite when the cursor reached its end

The cursor above just runs over the ordered map, returning values. However, the map is always ordered, so the cursor can optimize the built-in filter for sqlite. That is, sqlite will filter values on its own, but it’s more efficient to do that before returning them.

This can be done in the filter function, which can be written as follows:

  sqlite::result<void> filter(int idx, const char * idxStr, span<sqlite::value> values) override
  {
    if (idx != 0) (1)
      inverse = true;

    for (auto i = 0u; i < values.size(); i ++)
    {
      auto txt = values[i].get_text();
      switch (idxStr[i])
      {
        case SQLITE_INDEX_CONSTRAINT_EQ: (2)
        {
          auto nw = data.equal_range(txt);
          if (nw.first > begin)
            begin = nw.first;
          if (nw.second < end)
            end = nw.second;
        }

          break;
        case SQLITE_INDEX_CONSTRAINT_GT: (3)
        {
          auto new_begin = data.find(txt);
          new_begin ++;
          if (new_begin > begin)
            begin = new_begin;
        }
          break;
        case SQLITE_INDEX_CONSTRAINT_GE: (3)
        {
          auto new_begin = data.find(txt);
          if (new_begin > begin)
            begin = new_begin;
        }
          break;
        case SQLITE_INDEX_CONSTRAINT_LE: (4)
        {
          auto new_end = data.find(txt);
          new_end++;
          if (new_end < end)
            end = new_end;

        }
          break;
        case SQLITE_INDEX_CONSTRAINT_LT: (4)
        {
          auto new_end = data.find(txt);
          if (new_end < end)
            end = new_end;
        }
          break;
      }

    }
    return {};
  }

1	If the first idx is not null, it means the order is reversed (see best_index below)
2	The equal constraint can be fulfilled by setting the range to enclose the value.
3	A greater than/equal constraint can be fulfilled by moving the beginning of the range.
4	A lesser than/equal constraint can be fulfilled by moving the end of the range.

Constraints of the same can appear multiple times, so the filter must only narrow the range.

After the cursor, the actual table needs to be implemented:

struct map_impl final
    : sqlite::vtab::table<ordered_map_cursor>,
      sqlite::vtab::modifiable (1)

{
  container::flat_map<std::string, std::string> &data;
  map_impl(container::flat_map<std::string, std::string> &data) : data(data) {}

  const char * declaration() override (2)
  {
    return R"(
          create table my_map(
              name text primary key unique not null,
              data text) WITHOUT ROWID;)";
  }


  sqlite::result<cursor_type> open() override (3)
  {
    return cursor_type{data};
  }

  sqlite::result<void> delete_(sqlite::value key) override (4)
  {
    data.erase(key.get_text());
    return {};
  }
  sqlite::result<sqlite_int64> insert(sqlite::value /*key*/, span<sqlite::value> values,
                                      int /*on_conflict*/) override (5)
  {
    data.emplace(values[0].get_text(), values[1].get_text());
    return 0;
  }

  sqlite::result<sqlite_int64> update(sqlite::value old_key, sqlite::value new_key,
                                      span<sqlite::value> values,
                                      int /*on_conflict*/) override (6)
  {
    if (new_key.get_int() != old_key.get_int())
      data.erase(old_key.get_text());
    data.insert_or_assign(values[0].get_text(), values[1].get_text());
    return 0;
  }

};

1	Declare the table to not be read-only, which adds functions 4-6.
2	The declaration is a literal that tells sqlite the shape of the table. It includes `WITHOUT ROWID` as `cursor.row_id()` mustn’t be used.
3	This function gets called when the table gets scanned, i.e. a cursor gets created.
4	Delete value from the table, inherited from `modifiable`.
5	Insert a value into the table, inherited from `modifiable`.
6	Update a value in the table, inherited from `modifiable`.

Next, there also should be a best_index function. This function informs sqlite about the available filter features so that sqlite can omit the operations.

  sqlite::result<void> best_index(sqlite::vtab::index_info & info) override
  {
    // we're using the index to encode the mode, because it's simple enough.
    // more complex application should use it as an index like intended

    int idx = 0;
    sqlite::unique_ptr<char[]> str; (1)
    if (info.constraints().size() > 0)
    {
      const auto sz = info.constraints().size()+1;
      str.reset(static_cast<char*>(sqlite3_malloc(sz)));
      std::memset(str.get(), '\0', sz);
    }
    else
      return {};

    for (auto i = 0u; i < info.constraints().size(); i++)
    {
      if ((idx & SQLITE_INDEX_CONSTRAINT_EQ) != 0)
        break;
      auto ct = info.constraints()[i];
      if (ct.iColumn == 0
          && ct.usable != 0) // aye, that's us
      {
        switch (ct.op) (2)
        {
          // we'll stick to these
          case SQLITE_INDEX_CONSTRAINT_EQ: BOOST_FALLTHROUGH;
          case SQLITE_INDEX_CONSTRAINT_GT: BOOST_FALLTHROUGH;
          case SQLITE_INDEX_CONSTRAINT_GE: BOOST_FALLTHROUGH;
          case SQLITE_INDEX_CONSTRAINT_LE: BOOST_FALLTHROUGH;
          case SQLITE_INDEX_CONSTRAINT_LT:
            str[idx] = ct.op;
            info.usage()[i].argvIndex = ++idx; // use it -> value in this position in `filter`.
            info.usage()[i].omit = 1; // tell sqlite that we're sure enough, so sqlite doesn't check
            break;
          default:
            break;
        }
      }
    }


    if (info.order_by().size() == 1 && info.order_by()[0].iColumn == 0)
    {
      idx |= info.order_by()[0].desc; (3)
      info.set_already_ordered(); (4)
    }

    (5)
    info.set_index(idx);
    if (str)
      info.set_index_string(str.release(), true);

    return {};
  }

1	The data being passed on as idxStr to `cursor.filter`.
2	These are the constraints handled by filter, so only those get encoded.
3	filter assumes idx != 0 means descending, i.e. inverted
4	Tell sqlite the data is ordered already.
5	Set the index information to be passed to filter.

With that all defined, the only thing left is to declare the module & create it.

struct ordered_map_module final : sqlite::vtab::eponymous_module<map_impl>
{
  container::flat_map<std::string, std::string> data;
  template<typename ... Args>
  ordered_map_module(Args && ...args) : data(std::forward<Args>(args)...) {}

  sqlite::result<map_impl> connect(
      sqlite::connection /*conn*/, int /*argc*/, const char * const */*argv*/)
  {
    return map_impl{data};
  }
};
  ordered_map_module & m = sqlite::create_module(conn, "my_map", ordered_map_module(init_data));

Function table

An eponymous function can be used as a function. For this example, a query vtable will be used to parse the query part of a url and return it as a table:

-- table-ize the query of url
select * from query('name=boost&thingy=foo&name=sqlite&foo');
select * from query where query_string = 'name=boost&thingy=foo&name=sqlite&foo';

name

boost

thingy

foo

name

sqlite

foo

To achieve this a cursor is needed:

struct query_cursor final : sqlite::vtab::cursor<
    variant2::variant<variant2::monostate, std::int64_t, core::string_view, urls::pct_string_view>
    >
{
  urls::params_encoded_view view;
  urls::params_encoded_view::const_iterator itr{view.begin()};

  sqlite::result<void> next() override { itr++; return {};}

  sqlite::result<sqlite3_int64> row_id() override {return std::distance(view.begin(), itr);}
  sqlite::result<column_type> column(int i, bool /*nochange*/) override (1)
  {
    //nochange = true;
    switch (i)
    {
      case 0: return std::distance(view.begin(), itr);
      case 1: return itr->key;
      case 2:
        if (!itr->has_value)
          return variant2::monostate{};
        else
          return itr->value;
      case 3: return view.buffer();
      default:
        return variant2::monostate{};
    }
  }
  sqlite::result<void> filter(int /*idx*/, const char * /*idxStr*/,
                              span<sqlite::value> values) override
  {
    if (values.size() > 0u) (2)
      view = urls::params_encoded_view(values[0].get_text());
    itr = view.begin();

    return {};
  }
  bool eof() noexcept override {return itr == view.end();}
};

1	Return the parts of the query view as columns
2	Pick the input value here.

This is the module & table declaration;

struct query_wrapper final : sqlite::vtab::table<query_cursor>
{
  const char * declaration() override
  {
    return R"(
          create table queries(
              idx integer,
              name text,
              value text,
              query_string text hidden);)"; (1)
  }

  sqlite::result<query_cursor> open() override
  {
    return query_cursor{};
  }

  sqlite::result<void> best_index(sqlite::vtab::index_info & info) override
  {
    for (auto & constraint : info.constraints())
    {
      if (constraint.iColumn == 3
          && constraint.usable)
      {
        if (constraint.op != SQLITE_INDEX_CONSTRAINT_EQ) (2)
          return sqlite::error{SQLITE_OK, "query only support equality constraints"};

        info.usage_of(constraint).argvIndex = 1;
        info.set_index(1);
      }
    }
    return {};
  }
};

struct query_module final : sqlite::vtab::eponymous_module<query_wrapper>
{
  sqlite::result<query_wrapper> connect(sqlite::connection /*conn*/,
                        int /*argc*/, const char * const */*argv*/)
  {
    return query_wrapper{};
  }
};
  sqlite::create_module(conn, "query", query_module());

1	The hidden colum to use for the input value.
2	Assert only equal is used for input data.

See the sqlite documentation here.

Non-eponymous Tables

A non-eponymous table is a table backed by an actual resource.

The module needs to provide a create function in the module, in addition to the connect one. The table is required to have a destroy function that removes the resource.

struct csv_module final : sqlite::vtab::module<csv_table>
{
    // create the csv file
    sqlite::result<table_type> create(sqlite::connection /*db*/,
                                      int argc, const char * const  argv[]);

    // connect to an existing csv file.
    sqlite::result<table_type> connect(sqlite::connection /*db*/,
                                       int argc, const char * const  argv[]);

};

The virtual table can be created with the special syntax:

CREATE VIRTUAL TABLE csv_example USING csv_file(./csv-example.csv, username, first_name, last_name);

When the above query is executed csv_module.create() will be executed with `"./csv-example.csv", "username", "first_name", "last_name".

If the database gets opened with a previously created virtual table, csv_module.connect() will be called with the same parameters.

Executing DROP TABLE csv_example will call destroy() on the csv_table.

The destroy function will be invoked when the virtual table gets dropped, not when the connection gets closed.

Reference

`sqlite/allocator.hpp`

The sqlite allocator wraps sqlite’s malloc & free functions in a similar way that std::allocator wraps new/delete.

This can be used for sqlite-related code (e.g. vtables or custom functions) that should use memory from the sqlite3 pool.

template<typename T>
struct allocator
{
  constexpr allocator() noexcept {}
  constexpr allocator( const allocator& other ) noexcept {}
  template< class U >
  constexpr allocator( const allocator<U>& other ) noexcept {}

  constexpr static std::size_t alignment = implementation_defined;


  static_assert(alignof(T) <= alignment, "T alignment can't be fulfilled by sqlite");

  [[nodiscard]] T allocate( std::size_t n ); (1)
  void deallocate( T p, std::size_t); (2)
};

1	Invokes `sqlite3_malloc64` and throws `std::bad_alloc` if it fails.
2	Invokes `sqlite3_free`

Sqlite provides extensive customizability for its dynamic memory allocation.

`sqlite/backup.hpp`

Backup is a small wrapper function to create a backup of one database into another. This can be useful to write an in memory database to disk et vice versa.

void
backup(connection & source,
       connection & target,
       cstring_ref source_name = "main",
       cstring_ref target_name = "main");

void
backup(connection & source,
       connection & target,
       cstring_ref source_name,
       cstring_ref target_name,
       system::error_code & ec,
       error_info & ei);

source: The source database to backup
target: The target of the backup
source_name: The source database to read the backup from. Default is 'main'.
target_name: The target database to write the backup to. Default is 'main'.

Example

sqlite::connection conn{sqlite::in_memory};
{
    sqlite::connection read{"./read_only_db.db", SQLITE_READONLY};
    // read peristed data into memory.
    backup(read, target);
}

`sqlite/blob.hpp`

`blob_view`

A blob_view is a view type referring to Binary Large OBject, i.e. a non-owning type.

Definition

//A view to a binary large object
struct blob_view
{
    // The data in the blob
    const void * data() const;
    // The size of the data in the blob, in bytes
    std::size_t size() const
    // Construct a blob from existing data
    blob_view(const void * data, std::size_t size);

    // Construct an empty blob
    blob_view() = default;
    // Construct a blob from some other blob-like structure (data() is a pointer & size() returns size_t)
    template<typename T>
    explicit blob_view(const T & value);

    // Create a blob from the
    blob_view(const struct blob & b);
};

The blob_view can be used to access binary data from the database without copying.

`zero_blob`

The zero_blob is a special type that denotes blobs full of zeros without requiring any allocations. It can be used as a result from a [function] or as a parameter for a statement.

Definition

enum class zero_blob : sqlite3_uint64 {};

Example

extern sqlite::connection conn;
conn.prepare("INSERT INTO example(bdata) VALUES(?)")
    .execute(sqlite::zero_blob(1024)); (1)

1	Insert a blob of zeros with the size 1024

`blob`

The blob object owns a binary large object.

// @brief An object that owns a binary large object. @ingroup reference
struct blob
{
    // The data in the blob
    void * data() const;
    // The size of the data int he blob, in bytes
    std::size_t size() const;

    // Create a blob from a blob_view
    explicit blob(blob_view bv);
    // Create an empty blob with size `n`.
    explicit blob(std::size_t n);

    // Construct an empty blob
    constexpr blob() = default;
    // Release & take ownership of the blob.
    void * release() &&
};

`blob_handle`

A blob_handle is readable & writable handle to a blob in the database. It allows incremental reading/writing of the raw data.

// Open a blob
blob_handle open_blob(connection & conn,
                      cstring_ref db,
                      cstring_ref table,
                      cstring_ref column,
                      sqlite3_int64 row,
                      bool read_only,
                      system::error_code &ec,
                      error_info &ei);
blob_handle open_blob(connection & conn,
                      cstring_ref db,
                      cstring_ref table,
                      cstring_ref column,
                      sqlite3_int64 row,
                      bool read_only = false);

// An object that holds a binary large object. Can be obtained by using @ref blob_handle. @ingroup reference
struct blob_handle
{
    // Default constructor
    blob_handle() = default;

    // Construct from a handle. This takesowner ship of the `sqlite3_blob` handle.
    explicit blob_handle(sqlite3_blob * blob);

    // Reopen the blob on another row (i.e. the same column of the same table)
    void reopen(sqlite3_int64 row_id);
    void reopen(sqlite3_int64 row_id, system::error_code & ec);

    // Read data from the blob
    void read_at(void *data, int len, int offset);
    void read_at(void *data, int len, int offset, system::error_code &ec);

    // Write data to the blob
    void write_at(const void *data, int len, int offset);
    void write_at(const void *data, int len, int offset, system::error_code &ec);

    // The size of the blob
    std::size_t size() const;

    // The handle of the blob
    using handle_type = sqlite3_blob*;
    // Returns the handle of the blob
    handle_type handle();
    // Release the owned handle.
    handle_type release() &&;
};

`sqlite/collation.hpp`

A collation is a comparison operator between two string-like values, that allows ordering with a custom algorithm.

Definition

// Create a collation
template<typename Func>
void create_collation(connection & conn, cstring_ref name, Func && func);
template<typename Func>
void create_collation(connection & conn, cstring_ref name, Func && func, system::error_code &ec);

// Delete an existing collation.
void delete_collation(connection & conn, cstring_ref name, system::error_code & ec);
void delete_collation(connection & conn, cstring_ref name);

conn: A connection to the database in which to install the collation.
name: The name of the collation.
func: The function

The function must be callable with two string_view and return an int, indicating the comparison results.

Example

// a case insensitive string omparison, e.g. from boost.urls
int ci_compare(string_view s0, string_view s1) noexcept;

extern sqlite::connection conn;

// Register the collation
sqlite::create_collation(conn, "iequal", &ci_compare);

// use the collation to get by name, case insensitively
conn.query("select first_name, last_name from people where first_name = 'Klemens' collate iequal;");

// order by names case insensitively
conn.query("select * from people order by last_name collate iequal asc;");

`sqlite/connection.hpp`

The connection object is the main object to access a database.

Definition

// Utility constant for in-memory databases
constexpr static cstring_ref in_memory = ":memory:";

struct connection
{
    // The handle of the connection
    using handle_type = sqlite3*;
    // Returns the handle
    handle_type handle() const;
    // Release the owned handle.
    handle_type release() &&;

    //Default constructor
    connection() = default;
    // Construct the connection from a handle.
    explicit connection(handle_type handle, bool take_ownership = true); (1)
    // Move constructor.
    connection(connection && ) = default;
    // Move assign operator.
    connection& operator=(connection && ) = default;

    // Construct a connection and connect it to `filename`. `flags` is set by `SQLITE_OPEN_*` flags.
    connection(cstring_ref filename,
               int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE); (2)
    template<typename Path>
    explicit connection(const Path & pth);


    // Connect the database to `filename`.  `flags` is set by `SQLITE_OPEN_*` flags.
    void connect(cstring_ref filename, int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE); (2)
    void connect(cstring_ref filename, int flags, system::error_code & ec);

    template<typename Path>
    void connect(const Path & pth, int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
    template<typename Path>
    void connect(const Path & pth, int flags, system::error_code & ec);

    // Close the database connection.
    void close();
    void close(system::error_code & ec, error_info & ei);

    // Check if the database holds a valid handle.
    bool valid() const;

    // Perform a query without parameters. Can only execute a single statement.
    resultset query(
            core::string_view q,
            system::error_code & ec,
            error_info & ei);
    resultset query(core::string_view q);

    template<typename T, bool Strict = false>
    static_resultset<T, Strict> query(core::string_view q, system::error_code & ec, error_info & ei);
    template<typename T, bool Strict = false>
    static_resultset<T, Strict> query(core::string_view q);

    // Perform a query without parametert, It execute a multiple statement.
    void execute(cstring_ref q, system::error_code & ec, error_info & ei);
    void execute(cstring_ref q);


    // Preparse a a statement.
    statement prepare(
            core::string_view q,
            system::error_code & ec,
            error_info & ei);
    statement prepare(core::string_view q);


    // Check if the database has the table
    bool has_table(
        cstring_ref table,
        cstring_ref db_name = "main") const;

    // Check if the database has the table
    bool has_column(
        cstring_ref table,
        cstring_ref column,
        cstring_ref db_name = "main") const;
};

1	The `take_ownership` is usually only false when used from extension modules.
2	See the sqlite documentation for the available flags.

Example

sqlite::connection conn;
conn.connect("./my-database.db");
conn.prepare("insert into log (text) values ($1)").execute(std::make_tuple("booting up"));

`sqlite/cstring_ref.hpp`

The sqlite::string_view class is a std::string_view compatible class.

The cstring_ref class is a view type similar to a string_view, but with a guarantee that it is null-terminated.

It can be constructed from a raw const char * or any class that has a c_str() function returning a const char *.

Otherwise it is similar to a string_view, except that substr(std::size_t()) will return a cstring_ref, whereas a substr(std::size_t(), std::size_t()) returns a string_view.

`sqlite/error.hpp`

`sqlite_category`

The sqlite_category is a boost::system::error_category to be used with sqlite errors.

`error_info`

The error_info class hold additional information about error conditions stored in an sqlite-allocate string.

Contains an error message describing what happened. Not all error conditions are able to generate this extended information - those that can’t will have an empty error message.

The error_info allocates memory from the sqlite pool and holds it.

struct error_info
{
    // Default constructor.
    error_info() = default;

    // Initialization constructor. Copies the message into a newly create buffer.
    error_info(core::string_view msg) noexcept;
    // set the message by copy
    void set_message(core::string_view msg);

    // Reset the buffer. If `c` is not null, its ownership is transferred into the error_info object.
    void reset(char * c = nullptr);

    // Format a message into a newly allocated buffer.
    cstring_ref format(cstring_ref fmt, ...);
    // Format a message into the existing buffer.
    cstring_ref snformat(cstring_ref fmt, ...);
    /// reserve data in the buffer i.e. allocate
    void reserve(std::size_t sz);

    // Get the allocated memory
    std::size_t capacity() const;

    // Gets the error message.
    cstring_ref message() const noexcept;

    // Release the underlying memory. It must be freed using `sqlite_free` later.
    char * release();
    // Restores the message to its initial state. Does not release memory.
    void clear() noexcept;

};

`error`

The error class holds error_info and a code and can be used with boost::system::result.

/**
 * \brief An error containing both a code & optional message.
 * \ingroup reference
 * \details Contains an error .
 */
struct error
{
  // The code of the error.
  int code;
  // The additional information of the error
  error_info info;

  // Create an error with code & message
  error(int code, error_info info) ;
  error(int code, core::string_view info);
  error(system::error_code code, error_info info)  (1)
  // Create an error with only a code.
  explicit error(int code);

  error(system::error_code code);
  // Create an empty error;
  error() = default;
  error(error && ) noexcept = default;
};

// For compatability with system::result;
void throw_exception_from_error( error const & e, boost::source_location const & loc );

template<typename T = void>
using result = system::result<T, error>;

1	If code.category() is not `sqlite_category`, the code will be set to `SQLITE_FAIL`.

`sqlite/extension.hpp`

`BOOST_SQLITE_EXTENSION`

This macro can be used to create an sqlite extension.

Definition

#define BOOST_SQLITE_EXTENSION(Name, Conn)

Name: The name of the module.
Conn: The parameter name of the connection.

When defining BOOST_SQLITE_COMPILE_EXTENSION (was is done in extension.hpp) sqlite will use an inline namespace to avoid symbol clashes.

You must link against Boost::sqlite_ext and not Boost::sqlite and should not mix both in the same binary.

Example

BOOST_SQLITE_EXTENSION(extension, conn)
{
  create_scalar_function(
    conn, "assert",
    [](boost::sqlite::context<>, boost::span<boost::sqlite::value, 1u> sp)
    {
        if (sp.front().get_int() == 0)
          throw std::logic_error("assertion failed");
    });
}

`sqlite/field.hpp`

A field is a type representing a value in a database. or as a result from a query.

Definition

struct field
{
    typedef sqlite_int64 int64;

    // The type of the value
    value_type type() const;
    // Is the held value null
    bool is_null() const;
    // Is the held value is not null
    explicit operator bool () const;
    // Returns the value as an `int64`.
    int64 get_int() const;
    // Returns the value as an `double`.
    double get_double() const;
    // Returns the value as text, i.e. a string_view. Note that this value may be invalidated
    cstring_ref get_text() const;
    // Returns the value as blob, i.e. raw memory. Note that this value may be invalidated
    blob_view get_blob() const;
    // Returns the field as a value.
    value get_value() const;
    // Returns the name of the column.
    cstring_ref column_name() const;
    // Returns the name of the table.
    cstring_ref table_name() const;
    // Returns the name of the original data source.
    cstring_ref column_origin_name() const;
}

The view types can be invalidated when the database changes or the next row is read by the query.

The field type does not own the statement/query it was produced by. It is a merely a view into the resultset. Reading the next row will change the values returned.

`sqlite/function.hpp`

`function_flags`

enum function_flags
{
  deterministic  = SQLITE_DETERMINISTIC,
  directonly     = SQLITE_DIRECTONLY,
  subtype        = SQLITE_SUBTYPE,
  innocuous      = SQLITE_INNOCUOUS,
  result_subtype = SQLITE_RESULT_SUBTYPE,
  selforder1     = SQLITE_SELFORDER1,
  selforder1     = SQLITE_SELFORDER1
};

These function flags can be used in accordance to the sqlite documentation.

`context`

A context is an object share values between invocations of a scalar function.

Definition

template<typename ... Args>
struct context
{
  template<std::size_t N>
  using element = mp11::mp_take_c<mp11::mp_list<Args...>, N>;

  // Set the value in the context at position `Idx`
  template<std::size_t Idx>
  void set(element<Idx> value);

  // Returns the value in the context at position `Idx`. Throws if the value isn't set.
  template<std::size_t Idx>
  auto get() -> element<Idx>  &;

  // Returns the value in the context at position `Idx`. Returns nullptr .value isn't set.
  template<std::size_t Idx>
  auto get_if() -> element<Idx>  *;

  explicit context(sqlite3_context * ctx) noexcept;

  // Set the result through the context, instead of returning it.
  template<typename T>
  auto set_result(T && val);

  // Set the an error through the context, instead of throwing it.
  void set_error(cstring_ref message, int code = SQLITE_ERROR);

  // Returns the connection of the context.
  connection get_connection() const;
};

Example

extern sqlite::connection conn;

sqlite::create_scalar_function(
conn, "my_sum",
[](sqlite::context<std::size_t> ctx,
   boost::span<sqlite::value, 1u> args) -> std::size_t
{
    auto value = args[0].get_int();
    auto p = ctx.get_if<0>();
    if (p != nullptr) // increment the counter
        return (*p) += value;
    else // set the initial value
        ctx.set<0>(value);
    return value;
});

`create_scalar_function`

Creates a scalar function.

Definition

template<typename Func>
auto create_scalar_function(
    connection & conn,
    cstring_ref name,
    Func && func,
    function_flags flags = {});

template<typename Func>
auto create_scalar_function(
    connection & conn,
    cstring_ref name,
    Func && func,
    function_flags flags,
    system::error_code & ec,
    error_info & ei);

conn: The connection to add the function to.
name: The name of the function
func: The function to be added

func must take context<Args…> as the first and a span<value, N> as the second value. If N is not dynamic_extent it will be used to deduce the number of arguments for the function.

Example

extern sqlite::connection conn;

sqlite::create_function(
    conn, "to_upper",
    [](sqlite::context<> ctx,
       boost::span<sqlite::value, 1u> args) -> std::string
    {
        std::string res;
        auto txt = val[0].get_text();
        res.resize(txt.size());
        std::transform(txt.begin(), txt.end(), res.begin(),
                       [](char c){return std::toupper(c);});
        return value;
    });

`create_aggregate_function`

An aggregrate function will create a new Func for a new aggregate from the args tuple and call step for every step. When the aggregation is done final is called and the result is returned to sqlite.

template<typename Func, typename Args = std::tuple<>>
void create_aggregate_function(
connection & conn,
cstring_ref name,
Args && args= {},
function_flags flags = {});

template<typename Func, typename Args = std::tuple<>>
void create_aggregate_function(
connection & conn,
cstring_ref name,
Args && args,
function_flags flags,
system::error_code & ec,
error_info & ei);

conn: The connection to add the function to.
name: The name of the function
args: The argument tuple to construct Func from.
Func: The function to be added. It needs to be an object with two functions:

void step(boost::span<sqlite::value, N> args);
T final();

Example

  extern sqlite::connection conn;

struct aggregate_func
{
  aggregate_func(std::size_t init) : counter(init) {}
  std::int64_t counter;
  void step(, boost::span<sqlite::value, 1u> val)
  {
    counter += val[0].get_text().size();
  }

  std::int64_t final()
  {
    return counter;
  }
};

sqlite::create_function<aggregate_func>(conn, "char_counter", std::make_tuple(42));

`create_window_function`

This is only available starting with sqlite 3.25.0.

An window function will create a new Func for a new aggregate and call step for every step. When an element is removed from the window inverse is called. When the aggregation is done final is called and the result is returned to sqlite.

template<typename Func, typename Args = std::tuple<>>
void create_window_function(
    connection & conn,
    cstring_ref name,
    Args && args = {},
    function_flags flags = {});

template<typename Func, typename Args = std::tuple<>>
void create_window_function(
    connection & conn,
    cstring_ref name,
    Args && args,
    function_flags flags,
    system::error_code & ec);

conn: The connection to add the function to.
name: The name of the function
args: The arguments to construct Func from.
Func: The function to be added. It needs to be an object with three functions:

void step(boost::span<sqlite::value, N> args);
void inverse(boost::span<sqlite::value, N> args);
T final();

Example

extern sqlite::connection conn;

struct window_func
{
  std::int64_t counter;
  void step(boost::span<sqlite::value, 1u> val)
  {
    counter += val[0].get_text().size();
  }
  void inverse(boost::span<sqlite::value, 1u> val)
  {
    counter -= val[0].get_text().size();
  }

  std::int64_t final()
  {
    return counter;
  }
};

sqlite::create_function(conn, "win_char_counter", aggregate_func{});

`sqlite/hooks.hpp`

This API might be subject change, if a better solution for ownership is found.

`commit_hook` & `rollback_commit`

The commit hook gets called before a commit gets performed. Likewise the rollback hook gets invoked before a rollback. If func returns true, the commit goes, otherwise it gets rolled back.

If the function is not a free function pointer, this function will NOT take ownership.

If func is a nullptr the hook gets reset.

template<typename Func>
bool commit_hook(connection & conn, Func && func);

template<typename Func>
bool rollback_hook(connection & conn, Func && func);

return: true if a hook has been replaced.
conn: The database connection to install the hook in
func: The hook function. It must be callable without any parameter, return a bool and be noexcept.

`update_hook`

The update hook The update hook gets called when an update was performed.

If the function is not a free function pointer, this function will NOT take ownership.

If func is a nullptr the hook gets reset.

template<typename Func>
bool update_hook(connection & conn, Func && func);

return: true if a hook has been replaced.
conn: The database connection to install the hook in
func: The signature of the function is void(int op, core::string_view db, core::string_view table, sqlite3_int64 id). op is either SQLITE_INSERT, SQLITE_DELETE and SQLITE_UPDATE. The function must be noexcept.

`preupdate_hook`

The preupdate hook requires sqlite to be required with SQLITE_ENABLE_PREUPDATE_HOOK true.

This hook gets called before an update.

struct preupdate_context
{
  // Returns the old value, i.e. the value before the update.
  system::result<value> old(int column) const;
  // The count of colums to be updated
  int count() const;
  // The nesting depth of the update.
  int depth() const;
  // The new value to be written to column
  system::result<value> new_(int column) const;

  // Query the status of blob access, e.g. when using blob_handle (1)
  int blob_write() const;

  explicit preupdate_context(sqlite3 * db) noexcept;
};


template<typename Func>
bool preupdate_hook(connection & conn, Func && func);

1	See sqlite/preupdate_blobwrite return `true` if a hook has been replaced. conn The database connection to install the hook in func The signature of the function is below:

 void preupdate_hook(sqlite::preupdate_context ctx,
                     int op,
                     const char * db_name,
                     const char * table_name,
                     sqlite3_int64 current_key,
                     sqlite3_int64 new_key);

`sqlite/json.hpp`

The json header provides integration with boost/json.

// The subtype value used by the sqlite json extension. See the [sqlite reference](https://www.sqlite.org/json1.html)
constexpr int json_subtype = static_cast<int>('J');

// Allow json to be used as a result from functions or vtables
void tag_invoke(const struct set_result_tag &, sqlite3_context * ctx, const json::value & value);

// Check if the value or field is a json.
bool is_json(const value & v);
bool is_json(const field & f);

//Convert the value or field to a json.
json::value as_json(const value & v, json::storage_ptr ptr = {});
json::value as_json(const field & f, json::storage_ptr ptr = {});

// Allow conversions to boost::json::value
void tag_invoke( const json::value_from_tag &, json::value& val, const value & f);
void tag_invoke( const json::value_from_tag &, json::value& val, const field & f);
void tag_invoke( const json::value_from_tag &, json::value& val, resultset && rs);

`sqlite/memory.hpp`

The memory header provides C++-y access to the memory facilities of sqlite.

// A tag to allow operator new
struct memory_tag {};

// new operators (1)
void operator new  ( std::size_t size, boost::sqlite::memory_tag) noexcept;
void operator new[]( std::size_t size, boost::sqlite::memory_tag) noexcept;
void operator delete  ( void* ptr, boost::sqlite::memory_tag) noexcept;

// A unique ptr that uses sqlite3_malloc / sqlite3_free
template<typename T>
using unique_ptr = std::unique_ptr<T, implementation_detail>;
template<typename T, typename ... Args>
unique_ptr<T> make_unique(Args && ... args);

// Get the size of the allocated memory.
template<typename T>
std::size_t msize(const unique_ptr<T> & ptr);


---
<1> new (sqlite::memory_tag{}) T() uses sqlite3_malloc


== sqlite/meta_data.hpp

The meta_data header provides some meta_data for columns.

[source,cpp,subs=+quotes]

struct column_meta_data { // Data type fo the column cstring_ref data_type; // Name of default collation sequence cstring_ref collation; // true if column has a NOT NULL constraint bool not_null; // true if column is part of the PRIMARY KEY bool primary_key; // true if column is AUTOINCREMENT bool auto_increment; };

column_meta_data table_column_meta_data(connection & conn, cstring_ref db_name, cstring_ref table_name, cstring_ref column_name, system::error_code & ec, error_info &ei); column_meta_data table_column_meta_data(connection & conn, cstring_ref table_name, cstring_ref column_name, system::error_code & ec, error_info &ei);

column_meta_data table_column_meta_data(connection & conn, cstring_ref db_name, cstring_ref table_name, cstring_ref column_name); column_meta_data table_column_meta_data(connection & conn, cstring_ref table_name, cstring_ref column_name); ---

`sqlite/mutex.hpp`

The mutex header provides to std::mutex compatible classes using the sqlite mutex implementation.

This will allow C++ code to use mutex code matching the configuration of sqlite. This may include the mutex being a noop.

// similar to std::mutex
struct mutex;
// similar to std::recursive_mutexx
struct recursive_mutex;

`sqlite/result.hpp`

The result header is used by functions and vtables to turn resulting values into sqlite values. The tag_invoke interface is public and meant to extended.

That is, implementing tag_invoke(sqlite::set_result_tag, sqlite3_context, T); will enable T to be used as a result by sqlite.

// The tag
struct set_result_tag {};

// built-in result type
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, blob b);
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, zero_blob zb);
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, double dbl) { sqlite3_result_double(ctx, dbl); }
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, sqlite3_int64 value);inline void tag_invoke(set_result_tag, sqlite3_context * ctx, std::int64_t value);
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, std::nullptr_t);
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, string_view str);
template<typename String>
inline auto tag_invoke(set_result_tag, sqlite3_context * ctx, String && str);
inline void tag_invoke(set_result_tag, sqlite3_context * , variant2::monostate);
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, const value & val);
template<typename ... Args>
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, const variant2::variant<Args...> & var);

template<typename T>
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, std::unique_ptr<T> ptr);
template<typename T>
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, std::unique_ptr<T, void(*)(T*)> ptr);
template<typename T, typename Deleter>
inline auto tag_invoke(set_result_tag, sqlite3_context * ctx, std::unique_ptr<T> ptr);
inline void tag_invoke(set_result_tag, sqlite3_context * ctx, error err);
template<typename T>
inline void tag_invoke(set_result_tag tag, sqlite3_context * ctx, result<T> res);
inline void tag_invoke(set_result_tag tag, sqlite3_context * ctx, result<void> res):

`sqlite/resultset.hpp`

A resultset represents the results of a query.

// Representation of a result from a query.

struct resultset
{
    // Returns the current row. The row is a pure view type.
    row current() const &;
    // Checks if the last row has been reached.
    bool done() const;

    // Read the next row. Returns false if there's nothing more to read.
    // Calling this will change the data of any `row` previously obtained from `current.
    bool read_next(system::error_code & ec, error_info & ei);
    bool read_next();

    // The number of colums in the resultset
    std::size_t column_count() const;
    // Returns the name of the column idx.
    string_view column_name(std::size_t idx) const;
    // Returns the name of the source table for column idx.
    string_view table_name(std::size_t idx) const;
    // Returns the origin name of the column for column idx.
    string_view column_origin_name(std::size_t idx) const;

    // The input iterator can be used to read every row in a for-loop
    struct iterator
    {
      using value_type = value;
      using difference_type   = int;
      using reference         = value&;
      using iterator_category = std::forward_iterator_tag;

      iterator() {}

      bool operator!=(iterator rhs) const;
      row &operator*();
      row *operator->();

      iterator operator++();
      iterator operator++(int);
    };

    // Return an input iterator to the currently unread row
    iterator begin();
    // Sentinel iterator.
    iterator   end();
};

Example

extern sqlite::connection conn;

sqlite::resultset rs = conn.query("select * from users;");

do
{
  handle_row(r.current());
}
while (rs.read_next()); // read it line by line

`sqlite/row.hpp`

This type represents one row of data from a query. Is a random-access range.

This type is a pure view type, and will be invalidated when the next row is read.

struct row
{
    // The size of the row, i.e. number of colums
    std::size_t size() const;

    // Returns the field at `idx`, @throws std::out_of_range
        field at(std::size_t idx) const;
    // Returns the field at `idx`.
    field operator[](std::size_t idx) const;

    // Random access iterator used to iterate over the columns.
    struct const_iterator
    {
        using difference_type   = int;
        using reference         = field&;
        using iterator_category = std::random_access_iterator_tag;

        const_iterator & operator++();
        const_iterator operator++(int);
        const_iterator & operator--();
        const_iterator operator--(int);

        field operator[](int i) const;

        const_iterator operator+(int i) const;
        const_iterator operator-(int i) const;
        const_iterator & operator+=(int i);
        const_iterator & operator-=(int i);
        const field & operator*() const;
        const field * operator->() const;

        bool operator==(const const_iterator& other) const;
        bool operator!=(const const_iterator& other) const;
        bool operator<(const const_iterator& other) const;
        bool operator>(const const_iterator& other) const;

        const_iterator() = default;
    };
    // Returns the begin of the column-range.
    const_iterator begin() const;

    // Returns the end of the column-range.
    const_iterator end() const
};

`sqlite/statement.hpp`

`param_ref`

A reference to a value to temporary bind for an execute statement. Most values are captures by reference.

struct param_ref
{
    // Default construct a parameter, gives `null`.
    param_ref() = default;
    // Bind null
    param_ref(variant2::monostate);
    // Bind null
    param_ref(std::nullptr_t);
    // Bind an integer.
    template<typename I>
    param_ref(I value);
    // Bind a blob.
    param_ref(blob_view blob);
    // Bind a string.
    param_ref(string_view text);

    template<typename StringLike>
    param_ref(StringLike && text);
    template<typename BlobLike>
    param_ref(BlobLike && text);

    // Bind a floating point value.
    param_ref(double value) : impl_(value) { }
    // Bind a zero_blob value, i.e. a blob that initialized by zero.
    param_ref(zero_blob zb) : impl_(zb) { }

    // Bind pointer value to the parameter. @see https://www.sqlite.org/bindptr.html
    // Requires sqlite 3.20
    // Deleter must a function pointer or trivially constructible.
    template<typename T, typename Deleter>
    param_ref(std::unique_ptr<T, Deleter> ptr);



    // Apply the param_ref to a statement.
    int apply(sqlite3_stmt * stmt, int c) const;

    // Construct param_ref from a variant
    template<typename T>
    param_ref(T && t);

`statement`

A statement used for a prepared-statement.

struct statement
{
    // execute the prepared statement once. This transfers ownership to the resultset
    template <typename ArgRange = std::initializer_list<param_ref>>
    resultset execute(ArgRange && params, system::error_code& ec, error_info& info) &&; (1)
    template <typename ArgRange = std::initializer_list<param_ref>
    resultset execute(ArgRange && params) &&; (1)
    resultset execute(
        std::initializer_list<std::pair<string_view, param_ref>> params,
        system::error_code& ec,
        error_info& info) &&; (2)

    resultset execute(std::initializer_list<std::pair<string_view, param_ref>> params) &&;
    template<typename T, bool Strict = false, typename ArgRange = std::initializer_list<param_ref>>
    static_resultset<T, Strict> execute(
        ArgRange && params,
        system::error_code & ec,
        error_info & ei) &&; (1)

    template<typename T, bool Strict = false, typename ArgRange = std::initializer_list<param_ref>>
    static_resultset<T, Strict> execute(ArgRange && params) &&; (1)

    template<typename T, bool Strict = false>
    static_resultset<T, Strict> execute(
        std::initializer_list<std::pair<string_view, param_ref>> params,
        system::error_code & ec,
        error_info & ei) &&; (2)
    template<typename T, bool Strict = false>
    static_resultset<T, Strict> execute(std::initializer_list<std::pair<string_view, param_ref>> params) &&; (2)


    template <typename ArgRange = std::initializer_list<param_ref>>
    resultset execute(
            ArgRange && params,
            system::error_code& ec,
            error_info& info) &; (1)


    template <typename ArgRange = std::initializer_list<param_ref>>
    resultset execute(ArgRange && params) &; (1)


    resultset execute(
        std::initializer_list<std::pair<string_view, param_ref>> params,
        system::error_code& ec,
        error_info& info) &; (2)

    resultset execute(std::initializer_list<std::pair<string_view, param_ref>> params) &; (2)

    template<typename T, bool Strict = false, typename ArgRange = std::initializer_list<param_ref>>
    static_resultset<T, Strict> execute(
        ArgRange && params,
        system::error_code & ec,
        error_info & ei) &; (1)

    template<typename T, bool Strict = false, typename ArgRange = std::initializer_list<param_ref>>
    static_resultset<T, Strict> execute(ArgRange && params) &; (1)

    template<typename T, bool Strict = false>
    static_resultset<T, Strict> execute(
        std::initializer_list<std::pair<string_view, param_ref>> params,
        system::error_code & ec,
        error_info & ei) &; (2)
    template<typename T, bool Strict = false>
    static_resultset<T, Strict> execute(std::initializer_list<std::pair<string_view, param_ref>> params) &; (2)



    // Returns the sql used to construct the prepared statement.
    stringe_view sql();

    // Returns the expanded sql used to construct the prepared statement. Requires sqlite 3.14
    stringe_view expanded_sql();

    // Returns the expanded sql used to construct the prepared statement. requiers sqlite to be compiles with SQLITE_ENABLE_NORMALIZE.
    stringe_view normalized_sql();

    // Returns the declared type of the column
    string_view declared_type(int id) const;
};

1	Executes a query with positional arguments
2	Executes a query with named arguments (from a map-like object)

The && overloads transfer ownership to the resultset, while the & keep them in the statement. That is, this is UB:

resultset get_users(sqlite::connection & conn)
{
  auto s = conn.prepare("SELECT * from users where name = ?");
  return s.execute({"allen"}); // UB, because result set points into s
}

resultset get_users(sqlite::connection & conn)
{
  // correct, because resultset takes ownershipo
  return conn.prepare("SELECT * from users where name = ?").execute({"allen"});
}

`sqlite/static_resultset.hpp`

A static_resultset represents the results of a query matched to a C++ type

// Representation of a result from a query.

struct resultset
{

template<typename T, bool Strict >
struct static_resultset
{
  // Returns the current row.
  T current() const &;

  // Returns the current row.
  T current(system::error_code & ec, error_info & ei) const &;
  // Checks if the last row has been reached.
  bool done() const {return result_.done();}

  // Read the next row. Returns false if there's nothing more to read.
  bool read_next(system::error_code & ec, error_info & ei);
  bool read_next();


  // The number of columes in the resultset
  std::size_t column_count() const;
  // Returns the name of the column idx.
  string_view column_name(std::size_t idx) const;

  // Returns the name of the source table for column idx.
  string_view table_name(std::size_t idx) const;
  // Returns the origin name of the column for column idx.
  string_view column_origin_name(std::size_t idx) const;

  static_resultset() = default;
  static_resultset(resultset && result)

  static_resultset(static_resultset<T, false> && rhs);

  /// The input iterator can be used to read every row in a for-loop
  struct iterator
  {
    using value_type = T;
    using difference_type   = int;
    using reference         = T&;
    using iterator_category = std::forward_iterator_tag;

    iterator();
    explicit iterator(resultset::iterator itr);
    bool operator!=(iterator rhs) const;

    value_type &operator*();
    value_type *operator->();

    iterator& operator++();
    iterator operator++(int);
  };

  /// Return an input iterator to the currently unread row
  iterator begin();
  /// Sentinel iterator.
  iterator   end();

  // Convert the static_result to a strict version
  static_resultset<T, true> strict() &&
  {
    return {std::move(result_)};
  }
};

T: The static type of the query. This must be a tuple or pfr compatible (for C++20) or described.
Strict: Disables implicit conversions.

Example

extern sqlite::connection conn;
struct user { std::string first_name; std::string last_name; };
BOOST_DESCRIBE_STRUCT(user, (), (first_name, last_name));

sqlite::resultset rs = conn.query("select first_name, last_name from users;");

do
{
user usr = r.current();
handle_row(u);
}
while (rs.read_next()) // read it line by line

`sqlite/string.hpp`

This string header exposes some sqlite utility functions in a C++y way.

bool like(cstring_ref lhs, cstring_ref rhs, char escape = '\0'); (1)
bool glob(cstring_ref lhs, cstring_ref rhs); (2)
int icmp(cstring_ref lhs, cstring_ref rhs); (3)
int icmp(string_view lhs, string_view rhs, std::size_t n); (4)

1	uses strlike
2	uses strglob
3	used stricmp
4	used strnicmp

`sqlite/transaction.hpp`

`transaction`

A simple transaction guard implementing RAAI for transactions

Definition

struct transaction
{
  // The mode of the transaction
  enum behaviour {deferred, immediate, exclusive};
  // A tag to use, to adopt an already initiated transaction.
  constexpr static struct adopt_transaction_t {} adopt_transaction{};

  // Create transaction guard on an existing transaction
  transaction(connection & conn, adopt_transaction_t);


  // Create transaction guard and initiate a transaction
  transaction(connection & conn);

  // Create transaction guard and initiate a transaction with the defined behaviour
  transaction(connection & conn, behaviour b) ;

  // see https://www.sqlite.org/lang_transaction.html re noexcept
  // rollback the transaction if not committed.
  ~transaction() noexcept(SQLITE_VERSION_NUMBER >= 3007011);


  // Commit the transaction.
  void commit();
  void commit(system::error_code & ec, error_info & ei);
  // Rollback the transaction explicitly.
  void rollback();
  void rollback(system::error_code & ec, error_info & ei);

};

Example

sqlite::connection conn;
conn.connect("./my-database.db");

sqlite::transaction t{conn};
conn.prepare("insert into log (text) values ($1)").execute(std::make_tuple("booting up"));
t.commit();

`savepoint`

A simple transaction guard implementing RAAI for savepoints. Savepoints can be used recursively.

Definition

struct savepoint
{
  // A tag to use, to adopt an already initiated transaction.
  constexpr static transaction::adopt_transaction_t adopt_transaction{};

  // Create savepoint guard on an existing savepoint
  savepoint(connection & conn, std::string name, transaction::adopt_transaction_t);

  // Create transaction guard and initiate it
  savepoint(connection & conn, std::string name);

  // rollback to the savepoint if not committed.
  ~savepoint() noexcept(SQLITE_VERSION_NUMBER >= 3007011);

  // Commit/Release the transaction.
  void commit();
  void commit(system::error_code & ec, error_info & ei);

  void release();
  void release(system::error_code & ec, error_info & ei);

  // Rollback the transaction explicitly.
  void rollback();
  void rollback(system::error_code & ec, error_info & ei);
  // The name of the savepoint.

  const std::string & name() const;
};

Example

sqlite::connection conn;
conn.connect("./my-database.db");

sqlite::savepoint t{conn, "my-savepoint};
conn.prepare("insert into log (text) values ($1)").execute(std::make_tuple("booting up"));
t.commit();

`sqlite/value.hpp`

`value_type`

The type of a value.

enum class value_type
{
    // An integral value
    integer = SQLITE_INTEGER,
    // A floating piont value
    floating = SQLITE_FLOAT,
    // A textual value
    text = SQLITE_TEXT,
    // A binary value
    blob = SQLITE_BLOB,
    // No value
    null = SQLITE_NULL,
};

// Get the name as a string
const char * value_type_name(value_type vt);

`value`

A holder for a sqlite values used for internal APIs.

struct value
{
    // The value for integers in the database
    typedef sqlite3_int64 int64 ;

    // The type of the value
    value_type type() const;
    // The subtype of the value.
    int subtype() const;

    // Is the held value null
    bool is_null() const;
    // Is the held value is not null
    explicit operator bool () const;
    // Returns the value as an `integer`.
    int64 get_int() const;
    // Returns the value as an `double`.
    double get_double() const;
    // Returns the value as text, i.e. a string_view. Note that this value may be invalidated`.
    cstring_ref get_text() const;
    // Returns the value as blob, i.e. raw memory. Note that this value may be invalidated`.
    blob_view get_blob() const;

    // Best numeric datatype of the value
    value_type numeric_type() const;

    // True if the column is unchanged in an UPDATE against a virtual table.
    // requires sqlite 3.32
    bool nochange() const;
    // True if value originated from a bound parameter
    // requires sqlite 3.31
    bool from_bind() const;


    // Construct value from a handle.
    explicit value(sqlite3_value * value_) noexcept : value_(value_) {}

    // The handle of the value.
    using handle_type = sqlite3_value *;
    // Returns the handle.
    handle_type handle() const;
    handle_type & handle();

    // Get a value that was passed through the pointer interface.
    // A value can be set as a pointer by binding/returning a unique_ptr.
    // Rquires sqlite 3.20
    template<typename T>
    T * get_pointer();

};

`sqlite/vtable.hpp`

Please read the virtual tables section for a more detailed explanation.

namespace vtab
{


// Helper type to set a function through the xFindFunction callback
struct function_setter
{
    /** Set the function
     *
     * The function can either take a single argument, a `span<sqlite::value, N>`
     * for scalar functions,
     * or a `context<Args...>` as first, and the span as second for aggegrate functions.
     *
     */
    template<typename Func>
    void set(Func & func);
    template<typename ... Args, std::size_t Extent>
    void set(void(* ptr)(context<Args...>, span<value, Extent>)) noexcept;
    template<typename T, typename ... Args,  std::size_t Extent>
    void set(T(* ptr)(context<Args...>, span<value, Extent>));

    template<std::size_t Extent>
    void set(void(* ptr)(span<value, Extent>));
    template<typename T, std::size_t Extent>
    void set(T(* ptr)(span<value, Extent>));
};


// requires Sqlite 3.38
// Utility function that can be used in `xFilter` for the `in` operator. (1)

struct in
{
    struct iterator
    {
        iterator() = default;

        iterator & operator++();
        iterator operator++(int);

        const value & operator*() const;

        const value * operator->() const;

        bool operator==(const iterator& other) const;
        bool operator!=(const iterator& other) const;
    };

    // Returns a forward iterator to the `in` sequence for an `in` constraint pointing to the begin.
    iterator begin();
    // Returns a forward iterator to the `in` sequence for an `in` constraint pointing to the end.
    iterator end();
explicit in(sqlite::value   out);
};



// index info used by the find_index function (2)
struct index_info
{
  // Returns constraints of the index.
  span<const sqlite3_index_info::sqlite3_index_constraint> constraints() const;

  // Returns ordering of the index.
  span<const sqlite3_index_info::sqlite3_index_orderby> order_by() const;

  span<sqlite3_index_info::sqlite3_index_constraint_usage> usage();


  sqlite3_index_info::sqlite3_index_constraint_usage & usage_of(
      const sqlite3_index_info::sqlite3_index_constraint & info);

  // Receive the collation for the contrainst of the position. requires 3.22
  const char * collation(std::size_t idx) const;

  int on_conflict() const;

  // Returns true if the constraint is distinct. requires sqlite 3.38
  bool   distinct() const;

  // Requires sqlite 3.38
  value * rhs_value(std::size_t idx) const;

  void set_already_ordered();
  void set_estimated_cost(double cost);
  // requires sqlite 3.8.2
  void set_estimated_rows(sqlite3_int64 rows);
  // requires sqlite 3.9
  void set_index_scan_flags(int flags);
  // requires sqlite 3.10
  std::bitset<64u> columns_used();

  void set_index(int value);
  void set_index_string(char * str, bool take_ownership = true);

  sqlite3_index_info * info() const;
  sqlite3 * db() const;
};


struct module_config
{
  // Can be used to set SQLITE_VTAB_INNOCUOUS. Requires sqlite 3.31
  void set_innocuous();
  // Can be used to set SQLITE_VTAB_DIRECTONLY. Requires sqlite 3.31
  void set_directonly() {sqlite3_vtab_config(db_, SQLITE_VTAB_DIRECTONLY);}


  // Can be used to set SQLITE_VTAB_CONSTRAINT_SUPPORT
  void set_constraint_support(bool enabled = false);
};

template<typename Table>
struct module
{
  using table_type = Table;

  // Creates the instance
  // The instance_type gets used & managed by value, OR a pointer to a class that inherits sqlite3_vtab.
  // instance_type must have a member `declaration` that returns a `const char *` for the declaration.
  virtual result<table_type> create(sqlite::connection db, int argc, const char * const argv[]) = 0;

  // Create a table
  // The table_type gets used & managed by value, OR a pointer to a class that inherits sqlite3_vtab.
  // table_type must have a member `declaration` that returns a `const char *` for the declaration.
  virtual result<table_type> connect(sqlite::connection db, int argc, const char * const argv[]) = 0;
};

template<typename Table>
struct eponymous_module
{
  using table_type = Table;

  // Creates the instance
  // The instance_type gets used & managed by value, OR a pointer to a class that inherits sqlite3_vtab.
  // instance_type must have a member `declaration` that returns a `const char *` for the declaration.
  virtual result<table_type> connect(sqlite::connection db, int argc, const char * const argv[]) = 0;

  eponymous_module(bool eponymous_only = false);

  bool eponymous_only() const;'
 protected:
  bool eponymous_only_{false};

};


// The basis for vtable
template<typename Cursor>
struct table : protected sqlite3_vtab
{
  using cursor_type = Cursor;

  virtual result<void> config(module_config &);

  // The Table declaration to be used with  sqlite3_declare_vtab
  virtual const char *declaration() = 0;

  // Destroy the storage = this function needs to be present for non eponymous tables
  virtual result<void> destroy();

  // Tell sqlite how to communicate with the table.
  // Optional, this library will fill in a default function that leaves comparisons to sqlite.
  virtual result<void> best_index(index_info & /*info*/);

  // Start a search on the table.
  // The cursor_type gets used & managed by value, OR a pointer to a class that inherits sqlite3_vtab_cursor.
  virtual result<cursor_type> open() = 0;

  // Get the connection of the vtable
  sqlite::connection connection() const;

  table(const sqlite::connection & conn);
};


// Cursor needs the following member.
template<typename ColumnType = void>
struct cursor : protected sqlite3_vtab_cursor
{
  using column_type = ColumnType;

  // Apply a filter to the cursor. Required when best_index is implemented.
  virtual result<void> filter(
        int /*index*/, const char * /*index_data*/,
        boost::span<sqlite::value> /*values*/)
  {
    return {system::in_place_error, SQLITE_OK};
  }

  // Returns the next row.
  virtual result<void> next() = 0;

  // Check if the cursor is and the end
  virtual bool eof() = 0;

  // Returns the result of a value. It will use the set_result functionality to create a an sqlite function. (3)
  virtual result<column_type> column(int idx, bool no_change) = 0;
  // Returns the id of the current row
  virtual result<sqlite3_int64> row_id() = 0;

  // Get the table the cursor is pointing to.
        vtab::table<cursor> & table();
  const vtab::table<cursor> & table() const;
};


// Group of functions for modifications. The table must inherit this to be modifiable.
struct modifiable
{
  virtual result<void> delete_(sqlite::value key) = 0;
  // Insert a new row
  virtual result<sqlite_int64> insert(sqlite::value key, span<sqlite::value> values, int on_conflict) = 0;
  // Update the row
  virtual result<sqlite_int64> update(sqlite::value old_key, sqlite::value new_key, span<sqlite::value> values, int on_conflict) = 0;
};

// Group of functions to support transactions. The table must inherit this to support transactions.
struct transaction
{
  // Begin a tranasction
  virtual result<void> begin()    = 0;
  // synchronize the state
  virtual result<void> sync()     = 0;
  // commit the transaction
  virtual result<void> commit()   = 0;
  // rollback the transaction
  virtual result<void> rollback() = 0;
};

// Base class to enable function overriding See `xFindFunction`.
struct overload_functions
{
  virtual result<void> find_function(
          function_setter fs,
          int arg, const char * name) = 0; (4)
};

// Support for recursive transactions. Requires sqlite 3.7.7
struct recursive_transaction
{
  // Save the current state with to `i`
  virtual result<void> savepoint(int i)   = 0;
  // Release all saves states down to `i`
  virtual result<void> release(int i)     = 0;
  // Roll the transaction back to `i`.
  virtual result<void> rollback_to(int i) = 0;
};

/** Register a vtable  (5)
 Returns a reference to the module as stored in the database. It's lifetime is managed by the database.
*/
template<typename T>
auto create_module(connection & conn,
                   cstring_ref name,
                   T && module,
                   system::error_code & ec,
                   error_info & ei) -> typename std::decay<T>::type &;
template<typename T>
auto create_module(connection & conn,
                   const char * name,
                   T && module)  -> typename std::decay<T>::type &;

1	See vtab_in_first
2	See best_index
3	See vtab_no_change
4	See find_function
5	See vtab

Documentation boost.sqlite

Introduction

Accessing a database

Connecting to a database

Executing SQL

Querying data

Statements

Adding functions

Scalar Functions

A ggregate functions

Window functions

Extension Modules

Virtual tables

Eponymous

Eponymous value table

Function table

Non-eponymous Tables

Reference

sqlite/allocator.hpp

sqlite/backup.hpp

sqlite/blob.hpp

blob_view

zero_blob

blob

blob_handle

sqlite/collation.hpp

sqlite/connection.hpp

sqlite/cstring_ref.hpp

sqlite/error.hpp

sqlite_category

error_info

error

sqlite/extension.hpp

BOOST_SQLITE_EXTENSION

sqlite/field.hpp

sqlite/function.hpp

function_flags

context

create_scalar_function

create_aggregate_function

create_window_function

sqlite/hooks.hpp

commit_hook & rollback_commit

update_hook

preupdate_hook

sqlite/json.hpp

sqlite/memory.hpp

sqlite/mutex.hpp

sqlite/result.hpp

sqlite/resultset.hpp

sqlite/row.hpp

sqlite/statement.hpp

param_ref

statement

sqlite/static_resultset.hpp

sqlite/string.hpp

sqlite/transaction.hpp

transaction

savepoint

sqlite/value.hpp

value_type

value

sqlite/vtable.hpp

`sqlite/allocator.hpp`

`sqlite/backup.hpp`

`sqlite/blob.hpp`

`blob_view`

`zero_blob`

`blob`

`blob_handle`

`sqlite/collation.hpp`

`sqlite/connection.hpp`

`sqlite/cstring_ref.hpp`

`sqlite/error.hpp`

`sqlite_category`

`error_info`

`error`

`sqlite/extension.hpp`

`BOOST_SQLITE_EXTENSION`

`sqlite/field.hpp`

`sqlite/function.hpp`

`function_flags`

`context`

`create_scalar_function`

`create_aggregate_function`

`create_window_function`

`sqlite/hooks.hpp`

`commit_hook` & `rollback_commit`

`update_hook`

`preupdate_hook`

`sqlite/json.hpp`

`sqlite/memory.hpp`

`sqlite/mutex.hpp`

`sqlite/result.hpp`

`sqlite/resultset.hpp`

`sqlite/row.hpp`

`sqlite/statement.hpp`

`param_ref`

`statement`

`sqlite/static_resultset.hpp`

`sqlite/string.hpp`

`sqlite/transaction.hpp`

`transaction`

`savepoint`

`sqlite/value.hpp`

`value_type`

`value`

`sqlite/vtable.hpp`