Squashed 'subprojects/boost-sqlite/' content from commit 3378e35

git-subtree-dir: subprojects/boost-sqlite
git-subtree-split: 3378e353705271e569cf4ba15c467b840a39798c

9 files changed, 1899 insertions, 0 deletions

diff --git a/example/CMakeLists.txt b/example/CMakeLists.txt
new file mode 100644
index 0000000..480ed7e
--- /dev/null
+++ b/example/CMakeLists.txt
@@ -0,0 +1,20 @@
+
+file(GLOB_RECURSE ALL_EXAMPLES RELATIVE ${CMAKE_CURRENT_SOURCE_DIR} ${CMAKE_CURRENT_SOURCE_DIR}/*.cpp)
+file(GLOB_RECURSE ALL_EXAMPLES RELATIVE ${CMAKE_CURRENT_SOURCE_DIR} ${CMAKE_CURRENT_SOURCE_DIR}/*.cpp)
+
+foreach(SRC ${ALL_EXAMPLES})
+    get_filename_component(NAME ${SRC} NAME_WLE )
+
+    if (EXISTS ${CMAKE_CURRENT_SOURCE_DIR}/${NAME}.sql)
+        add_library(boost_sqlite_example_${NAME} SHARED ${SRC})
+        target_link_libraries(boost_sqlite_example_${NAME} PUBLIC Boost::sqlite_ext Boost::url)
+        target_compile_definitions(boost_sqlite_example_${NAME} PUBLIC BOOST_SQLITE_SEPARATE_COMPILATION=1)
+        set_property(TARGET boost_sqlite_example_${NAME} PROPERTY PREFIX "")
+        set_target_properties(boost_sqlite_example_${NAME} PROPERTIES OUTPUT_NAME ${NAME} POSITION_INDEPENDENT_CODE ON)
+     else()
+        add_executable(boost_sqlite_example_${NAME} ${SRC} )
+        target_link_libraries(boost_sqlite_example_${NAME} PUBLIC Boost::sqlite)
+        target_compile_definitions(boost_sqlite_example_${NAME} PUBLIC BOOST_SQLITE_SEPARATE_COMPILATION=1)
+    endif()
+endforeach()
+
diff --git a/example/csv.cpp b/example/csv.cpp
new file mode 100644
index 0000000..47fe288
--- /dev/null
+++ b/example/csv.cpp
@@ -0,0 +1,273 @@
+//
+// Copyright (c) 2022 Klemens Morgenstern (klemens.morgenstern@gmx.net)
+//
+// Distributed under the Boost Software License, Version 1.0. (See accompanying
+// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
+//
+
+#include <boost/sqlite.hpp>
+#include <boost/unordered_map.hpp>
+#include <boost/describe.hpp>
+#include <boost/variant2/variant.hpp>
+#include <boost/container/flat_map.hpp>
+#include <boost/algorithm/string/trim.hpp>
+
+#include <fstream>
+#include <iostream>
+#include <vector>
+
+using namespace boost;
+
+// This example demonstrates how to use the vtable interface to read & write from a csv file.
+// The csv implementation is not efficient, but for demontration purposes.
+
+
+struct csv_data
+{
+  using row_type = std::vector<std::string>;
+  row_type names;
+  container::flat_map<sqlite3_int64 , row_type> rows;
+};
+
+csv_data::row_type read_line(std::string line)
+{
+  csv_data::row_type row;
+  std::string cell;
+  std::istringstream is{line};
+  while (std::getline(is, cell, ','))
+  {
+    boost::trim(cell);
+    row.push_back(std::move(cell));
+  }
+  return row;
+}
+
+
+std::istream & operator>>(std::istream & is, csv_data &cs)
+{
+  cs.names.clear();
+  cs.rows.clear();
+
+  std::string line;
+  if(std::getline(is, line))
+    cs.names = read_line(std::move(line));
+
+  sqlite3_int64 cnt = 1;
+  while(std::getline(is, line))
+    cs.rows.emplace(cnt++, read_line(std::move(line)));
+  return is;
+}
+
+
+std::ostream & operator<<(std::ostream & os, csv_data &cs)
+{
+
+  for (auto & nm : cs.names)
+  {
+    os  << nm;
+    if (&nm == &cs.names.back())
+      os << '\n';
+    else
+      os << ", ";
+  }
+
+  for (auto & r : cs.rows)
+    for (auto & nm : r.second)
+    {
+      os  << nm;
+      if (&nm == &r.second.back())
+        os << '\n';
+      else
+        os << ", ";
+    }
+
+  return os;
+}
+
+
+using iterator_type = typename container::flat_map<sqlite3_int64, csv_data::row_type>::const_iterator;
+
+struct csv_cursor final : sqlite::vtab::cursor<sqlite::string_view>
+{
+  iterator_type itr, end;
+
+  csv_cursor(iterator_type itr,
+             iterator_type end) : itr(itr), end(end)
+  {}
+
+  sqlite::result<void> next() {itr++; return {};}
+  sqlite::result<sqlite3_int64> row_id()
+  {
+    return itr->first;
+  }
+
+  sqlite::result<sqlite::string_view> column(int i, bool /* no_change */)
+  {
+    return itr->second.at(i);
+  }
+
+  bool eof() noexcept {return itr == end;}
+};
+
+struct csv_table final
+    : sqlite::vtab::table<csv_cursor>,
+      sqlite::vtab::modifiable,
+      sqlite::vtab::transaction
+{
+  std::string path;
+  csv_data data;
+  csv_data transaction_copy; // yeaup, inefficient, too.
+
+  csv_table(std::string path) : path(std::move(path))
+  {}
+
+  std::string decl;
+  const char * declaration()
+  {
+    if (decl.empty())
+    {
+      std::ostringstream  oss;
+      decl = "create table x(";
+
+      for (auto & nm : data.names)
+      {
+        decl += nm;
+        if (&nm == &data.names.back())
+          decl += ");";
+        else
+          decl += ", ";
+      }
+    }
+    return decl.c_str();
+  }
+
+  sqlite::result<cursor_type> open()
+  {
+    return cursor_type{data.rows.cbegin(), data.rows.cend()};
+  }
+
+  sqlite::result<void> delete_(sqlite::value key)
+  {
+    data.rows.erase(key.get_int());
+    return {};
+  }
+  sqlite::result<sqlite_int64> insert(
+      sqlite::value /*key*/, span<sqlite::value> values, int /*on_conflict*/)
+  {
+    sqlite3_int64 id = 0;
+    if (!data.rows.empty())
+      id = std::prev(data.rows.end())->first + 1;
+    auto & ref = data.rows[id];
+    ref.reserve(values.size());
+    for (auto v : values)
+      ref.emplace_back(v.get_text());
+
+    return id;
+  }
+  sqlite::result<sqlite_int64> update(
+                      sqlite::value update, sqlite::value new_key,
+                      span<sqlite::value> values, int /*on_conflict*/)
+  {
+    if (!new_key.is_null())
+      throw std::logic_error("we can't manually set keys");
+
+    int i = 0;
+    auto & r = data.rows[update.get_int()];
+    for (auto val : values)
+      r[i].assign(val.get_text());
+
+    return 0u;
+  }
+
+  // we do not read the csv , but just dump it on
+  sqlite::result<void> begin()  noexcept   {transaction_copy = data; return {};}
+  sqlite::result<void> sync()   noexcept   {return {};}
+  sqlite::result<void> commit() noexcept
+  {
+    // ok, let's write to disk.
+    //fs.(0);
+    std::ofstream fs{path, std::fstream::trunc};
+    fs << data << std::flush;
+    return {};
+  }
+  sqlite::result<void> rollback()  noexcept
+  {
+    data = std::move(transaction_copy);
+    return {};
+  }
+
+  sqlite::result<void> destroy()  noexcept
+  {
+    std::remove(path.c_str());
+    return {};
+  }
+};
+
+// The implementation is very inefficient. Don't use this in production.
+struct csv_module final : sqlite::vtab::module<csv_table>
+{
+
+  sqlite::result<table_type> create(sqlite::connection /*db*/,
+                                    int argc, const char * const  argv[])
+  {
+    if (argc < 4)
+      throw std::invalid_argument("Need filename as first parameter");
+
+    table_type tt{argv[3]};
+    tt.data.names.reserve(argc - 4);
+    for (int i = 4; i < argc; i++)
+      tt.data.names.emplace_back(argv[i]);
+    std::ofstream fs(tt.path, std::fstream::trunc);
+    fs << tt.data << std::flush;
+    return tt;
+  }
+
+  sqlite::result<table_type>  connect(sqlite::connection /*db*/,
+                                      int argc, const char * const  argv[])
+  {
+    if (argc < 4)
+      throw std::invalid_argument("Need filename as first parameter");
+
+    table_type tt{argv[3]};
+    tt.data.names.reserve(argc - 4);
+    for (int i = 4; i < argc; i++)
+      tt.data.names.emplace_back(argv[i]);
+    std::ifstream fs(tt.path, std::fstream::in);
+    // read the existing data
+    fs >> tt.data;
+
+    if (!std::equal(tt.data.names.begin(), tt.data.names.end(), argv+4, argv + argc))
+      throw std::runtime_error("Column names in csv do not match");
+
+    return tt;
+  }
+};
+
+
+int main (int /*argc*/, char * /*argv*/[])
+{
+  sqlite::connection conn{"./csv-example.db"};
+  sqlite::create_module(conn, "csv_file", csv_module());
+
+  const auto empty_csv = !conn.has_table("csv_example");
+  if (empty_csv)
+    conn.execute("CREATE VIRTUAL TABLE if not exists csv_example USING csv_file(./csv-example.csv, username, first_name, last_name);");
+
+  {
+    conn.execute("begin");
+    auto p = conn.prepare("insert into csv_example values (?, ?, ?)");
+    if (empty_csv)
+      p.execute({"anarthal", "ruben",    "perez"});
+
+    p.execute({"pdimov",              "peter",    "dimov"});
+    p.execute({"klemens-morgenstern", "klemens",  "morgenstern"});
+
+    if (empty_csv)
+      p.execute({"madmongo1", "richard", "hodges"});
+
+    conn.execute("commit");
+  }
+
+  conn.query("delete from csv_example where first_name in ('peter', 'klemens')");
+  return 0;
+}
diff --git a/example/describe.cpp b/example/describe.cpp
new file mode 100644
index 0000000..9b00f8b
--- /dev/null
+++ b/example/describe.cpp
@@ -0,0 +1,202 @@
+//
+// Copyright (c) 2022 Klemens Morgenstern (klemens.morgenstern@gmx.net)
+//
+// Distributed under the Boost Software License, Version 1.0. (See accompanying
+// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
+//
+
+#include <boost/sqlite.hpp>
+#include <boost/unordered_map.hpp>
+#include <boost/describe.hpp>
+#include <boost/variant2/variant.hpp>
+
+#include <iomanip>
+#include <iostream>
+
+// This example shows how a virtual table can be created using boost.describe
+
+using namespace boost;
+
+// add more conversions here if you need more types in the described struct
+void assign_value(std::int64_t & res, sqlite::value val) { res = val.get_int();}
+void assign_value(std::string & res, sqlite::value val) { res = val.get_text();}
+
+template<typename T>
+struct describe_cursor final : sqlite::vtab::cursor<>
+{
+  describe_cursor(
+      typename boost::unordered_map<sqlite3_int64, T>::const_iterator itr,
+      typename boost::unordered_map<sqlite3_int64, T>::const_iterator end
+      ) : itr(itr), end(end)
+  {
+  }
+
+  typename boost::unordered_map<sqlite3_int64, T>::const_iterator itr, end;
+  constexpr static std::size_t column_count = mp11::mp_size<describe::describe_members<T, describe::mod_any_access>>::value;
+
+  sqlite::result<void> next() {itr++; return {};}
+  sqlite::result<sqlite3_int64> row_id() {return itr->first;}
+
+  void column(sqlite::context<> ctx, int i, bool /* no_change */)
+  {
+    mp11::mp_with_index<column_count>(
+        i, [&](auto Idx)
+        {
+          using type = mp11::mp_at_c<describe::describe_members<T, describe::mod_public>, Idx>;
+          ctx.set_result(itr->second.*type::pointer);
+        });
+  }
+
+  bool eof() noexcept {return itr == end;}
+};
+
+template<typename T>
+struct describe_table final :
+    sqlite::vtab::table<describe_cursor<T>>,
+    sqlite::vtab::modifiable
+{
+  boost::unordered_map<sqlite3_int64, T> &data;
+  describe_table(boost::unordered_map<sqlite3_int64, T> &data) : data(data) {}
+
+  std::string decl;
+  const char * declaration()
+  {
+    if (!decl.empty())
+      return decl.c_str();
+    mp11::mp_for_each<describe::describe_members<T, describe::mod_public>>(
+        [&](auto elem)
+        {
+            if (decl.empty())
+              decl += "create table x(";
+            else
+              decl += ", ";
+
+            decl += elem.name;
+        });
+    decl += ");";
+    return decl.c_str();
+  }
+
+
+  sqlite3_int64 last_index = 0;
+
+  sqlite::result<describe_cursor<T>> open()
+  {
+    return describe_cursor<T>{data.begin(), data.end()};
+  }
+
+  sqlite::result<void> delete_(sqlite::value key)
+  {
+    data.erase(key.get_int());
+    return {};
+  }
+  sqlite::result<sqlite_int64> insert(sqlite::value key, span<sqlite::value> values, int /*on_conflict*/)
+  {
+    T res;
+    sqlite_int64 id = key.is_null() ? last_index++ : key.get_int();
+    auto vtr = values.begin();
+    mp11::mp_for_each<describe::describe_members<T, describe::mod_public>>(
+        [&](auto elem)
+        {
+          assign_value(res.*elem.pointer , *vtr);
+          vtr++;
+        });
+
+    auto itr = data.emplace(id, std::move(res)).first;
+    return itr->first;
+
+  }
+  sqlite::result<sqlite_int64> update(sqlite::value old_key, sqlite::value new_key,
+                      span<sqlite::value> values, int /*on_conflict*/)
+  {
+    if (new_key.get_int() != old_key.get_int())
+      data.erase(old_key.get_int());
+    auto & res = data[new_key.get_int()];
+
+    auto vtr = values.begin();
+    mp11::mp_for_each<describe::describe_members<T, describe::mod_public>>(
+        [&](auto elem)
+        {
+          assign_value(res.*elem.pointer , *vtr);
+          vtr++;
+        });
+    return 0u;
+  }
+};
+
+template<typename T>
+struct describe_module final : sqlite::vtab::eponymous_module<describe_table<T>>
+{
+  boost::unordered_map<sqlite3_int64, T> data;
+  constexpr static std::size_t column_count = mp11::mp_size<describe::describe_members<T, describe::mod_any_access>>::value;
+  sqlite::result<describe_table<T>> connect(sqlite::connection ,
+                                            int, const char * const [])
+  {
+    return describe_table<T>{data};
+  }
+};
+
+
+void print_table(std::ostream & str, sqlite::resultset res)
+{
+  for (auto i = 0u; i < res.column_count(); i ++)
+    str << "| " << std::setfill(' ') << std::setw(15) << res.column_name(i) << " ";
+
+  str << "|\n";
+
+  for (auto i = 0u; i < res.column_count(); i ++)
+    str << "|-----------------";
+  str << "|\n";
+
+  for (auto && r : res)
+  {
+    for (auto i = 0u; i < res.column_count(); i ++)
+      str << "| " << std::setfill(' ') << std::setw(15) << r.at(i).get_text() << " ";
+
+    str << "|\n" ;
+  }
+  str << std::endl;
+}
+
+
+struct boost_library
+{
+  std::string name;
+  std::int64_t first_released;
+  std::int64_t standard;
+};
+
+BOOST_DESCRIBE_STRUCT(boost_library, (), (name, first_released, standard));
+
+
+int main (int /*argc*/, char * /*argv*/[])
+{
+  sqlite::connection conn{":memory:"};
+  auto & md = sqlite::create_module(conn, "boost_libraries", describe_module<boost_library>());
+
+  {
+    auto p = conn.prepare("insert into boost_libraries (name, first_released, standard) values ($name, $version, $std);");
+    p.execute({{"name", "process"},         {"version", 64}, {"std", 11}});
+    p.execute({{"name", "asio"},            {"version", 35}, {"std", 98}});
+    p.execute({{"name", "bimap"},           {"version", 35}, {"std", 98}});
+    p.execute({{"name", "circular_buffer"}, {"version", 35}, {"std", 98}});
+    p.execute({{"name", "mpi"},             {"version", 35}, {"std", 98}});
+    p.execute({{"name", "beast"},           {"version", 66}, {"std", 11}});
+    p.execute({{"name", "describe"},        {"version", 77}, {"std", 14}});
+  }
+
+
+  print_table(std::cout, conn.query("select * from boost_libraries;"));
+
+  // same as conn.execute("update boost_libraries set standard = 11 where standard = 98;");
+  for (auto & p : md.data)
+    if (p.second.standard == 98)
+      p.second.standard = 11;
+
+  print_table(std::cout, conn.query("select * from boost_libraries;"));
+
+  conn.prepare("delete from boost_libraries where name = ?").execute({"mpi"});
+  print_table(std::cout, conn.query("select * from boost_libraries;"));
+
+  return 0;
+}
diff --git a/example/multi_index.cpp b/example/multi_index.cpp
new file mode 100644
index 0000000..0ce4a84
--- /dev/null
+++ b/example/multi_index.cpp
@@ -0,0 +1,433 @@
+//
+// Copyright (c) 2022 Klemens Morgenstern (klemens.morgenstern@gmx.net)
+//
+// Distributed under the Boost Software License, Version 1.0. (See accompanying
+// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
+//
+
+#include <boost/sqlite.hpp>
+#include <boost/multi_index_container.hpp>
+#include <boost/multi_index/ordered_index.hpp>
+#include <boost/multi_index/key.hpp>
+#include <boost/optional.hpp>
+#include <algorithm>
+#include <iostream>
+
+using namespace boost;
+
+namespace mi = boost::multi_index;
+
+struct boost_library
+{
+  std::string name, version;
+};
+
+using my_container = mi::multi_index_container<
+    boost_library,
+    mi::indexed_by<
+        mi::ordered_unique<mi::key<&boost_library::name>>,
+        mi::ordered_non_unique<mi::key<&boost_library::version>>
+  >
+>;
+
+
+struct multi_index_cursor final
+    : sqlite::vtab::cursor<sqlite::string_view>
+{
+  my_container &data;
+
+  multi_index_cursor(my_container &data) : data(data) {}
+  bool inverse = false;
+  int index = 0;
+  using const_iterator = typename my_container::const_iterator;
+  using const_iterator1 = typename my_container::nth_index_const_iterator<0>::type;
+  using const_iterator2 = typename my_container::nth_index_const_iterator<1>::type;
+
+  const_iterator begin{data.begin()}, end{data.end()};
+  const_iterator1 begin1 = data.get<0>().cbegin(), end1 = data.get<0>().cend();
+  const_iterator2 begin2 = data.get<1>().cbegin(), end2 = data.get<1>().cend();
+
+  sqlite::result<void> next()
+  {
+    switch (index)
+    {
+      case 0: inverse ? end--  : begin++;  break;
+      case 1: inverse ? end1-- : begin1++; break;
+      case 2: inverse ? end2-- : begin2++; break;
+    }
+    return {};
+  }
+
+  sqlite::result<sqlite3_int64> row_id()
+  {
+    static_assert(sizeof(const_iterator) <= sizeof(sqlite3_int64), "");
+
+    switch (index)
+    {
+      case 0: return reinterpret_cast<sqlite3_int64>(inverse ? &*std::prev(end ) : &*begin);
+      case 1: return reinterpret_cast<sqlite3_int64>(inverse ? &*std::prev(end1) : &*begin1);
+      case 2: return reinterpret_cast<sqlite3_int64>(inverse ? &*std::prev(end2) : &*begin2);
+    }
+
+    return sqlite3_int64();
+  }
+  sqlite::result<sqlite::string_view> column(int i, bool /*nochange*/)
+  {
+    const boost_library * lib;
+    switch (index)
+    {
+      case 0: lib = &(inverse ? *std::prev(end) : *begin);   break;
+      case 1: lib = &(inverse ? *std::prev(end1) : *begin1); break;
+      case 2: lib = &(inverse ? *std::prev(end2) : *begin2); break;
+    }
+
+    const auto & elem = *lib;
+    if (i == 0)
+      return elem.name;
+    else
+      return elem.version;
+  }
+
+
+  sqlite::result<void> filter(int idx, const char * idxStr, span<sqlite::value> values)
+  {
+    inverse = (idx == 0b1000) & idx;
+    index = idx & 0b11;
+
+    boost::optional<sqlite::cstring_ref> lower, upper, equal;
+    int lower_op = 0, upper_op = 0;
+
+    bool surely_empty = false;
+
+    for (auto i = 0u; i < values.size(); i++)
+    {
+      auto txt = values[i].get_text();
+      switch (idxStr[i])
+      {
+        case SQLITE_INDEX_CONSTRAINT_EQ:
+          if (equal && (*equal != txt))
+            // two different equal constraints do that to you.
+            surely_empty = true;
+          else
+            equal.emplace(txt);
+          break;
+        case SQLITE_INDEX_CONSTRAINT_GT:
+        case SQLITE_INDEX_CONSTRAINT_GE:
+          if (lower == txt)
+          {
+            // pick the more restrictive one
+            if (lower_op == SQLITE_INDEX_CONSTRAINT_GE)
+              lower_op = idxStr[i];
+          }
+          else
+          {
+            lower = (std::max)(lower.value_or(txt), txt);
+            lower_op = idxStr[i];
+          }
+
+          break;
+        case SQLITE_INDEX_CONSTRAINT_LE:
+        case SQLITE_INDEX_CONSTRAINT_LT:
+          if (upper == txt)
+          {
+            if (upper_op == SQLITE_INDEX_CONSTRAINT_LT)
+              upper_op = idxStr[i];
+          }
+          else
+          {
+            upper = (std::min)(upper.value_or(txt), txt);
+            upper_op = idxStr[i];
+          }
+          break;
+      }
+    }
+
+    if (lower && equal && lower > equal)
+      surely_empty = true;
+
+    if (upper && equal && upper < equal)
+      surely_empty = true;
+    if (surely_empty)
+    {
+      end = begin;
+      end1 = begin1;
+      end2 = begin2;
+      return {};
+    }
+
+    switch (index)
+    {
+      default: break;
+      case 1:
+        if (equal)
+          std::tie(begin1, end1) = data.get<0>().equal_range(*equal);
+        else
+        {
+          if (lower)
+          {
+            if (lower_op == SQLITE_INDEX_CONSTRAINT_GE)
+              begin1 = data.get<0>().lower_bound(*lower);
+            else // SQLITE_INDEX_CONSTRAINT_GT
+              begin1 = data.get<0>().upper_bound(*lower);
+          }
+          if (upper)
+          {
+            if (upper_op == SQLITE_INDEX_CONSTRAINT_LE)
+              end1 = data.get<0>().upper_bound(*upper);
+            else // SQLITE_INDEX_CONSTRAINT_LT
+              end1 = data.get<0>().lower_bound(*upper);
+          }
+          break;
+          case 2:
+            if (equal)
+              std::tie(begin2, end2) = data.get<1>().equal_range(*equal);
+            else
+            {
+              if (lower)
+              {
+                if (lower_op == SQLITE_INDEX_CONSTRAINT_GE)
+                  begin2 = data.get<1>().lower_bound(*lower);
+                else // SQLITE_INDEX_CONSTRAINT_GT
+                  begin2 = data.get<1>().upper_bound(*lower);
+              }
+
+              if (upper)
+              {
+                if (upper_op == SQLITE_INDEX_CONSTRAINT_LE)
+                  end2 = data.get<1>().upper_bound(*upper);
+                else // SQLITE_INDEX_CONSTRAINT_LT
+                  end2 = data.get<1>().lower_bound(*upper);
+              }
+            }
+          break;
+        }
+    }
+    return {};
+  }
+
+  bool eof() noexcept
+  {
+    switch (index)
+    {
+      case 0: return begin == end;
+      case 1: return begin1 == end1;
+      case 2: return begin2 == end2;
+      default: return true;
+    }
+  }
+};
+
+struct map_impl final
+    : sqlite::vtab::table<multi_index_cursor>,
+      sqlite::vtab::modifiable
+{
+  my_container data;
+  const char * declaration() override
+  {
+    return R"(
+          create table url(
+              name text primary key unique not null,
+              version text);)";
+  }
+  enum indices // 32
+  {
+    no_index   = 0b00000000,
+    equal      = 0b00000001,
+    gt         = 0b00000100,
+    ge         = 0b00001100,
+    lt         = 0000010000,
+    le         = 0b00110000,
+    order_asc  = 0b01000000,
+    order_desc = 0b10000000,
+  };
+
+  sqlite::result<cursor_type> open() override
+  {
+    return cursor_type(data);
+  }
+
+  sqlite::result<void> delete_(sqlite::value key) override
+  {
+    data.erase(key.get_text());
+    return {};
+  }
+  sqlite::result<sqlite_int64> insert(sqlite::value /*key*/, span<sqlite::value> values,
+                                      int /*on_conflict*/) override
+  {
+    data.insert({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
+  {
+    if (new_key.get_int() != old_key.get_int())
+    {
+
+      auto node = reinterpret_cast<my_container::value_type *>(old_key.get_int());
+      data.erase(data.iterator_to(*node));
+    }
+
+    auto res = data.insert({values[0].get_text(), values[1].get_text()});
+    if (!res.second)
+      data.replace(res.first, {values[0].get_text(), values[1].get_text()});
+    return 0;
+  }
+
+  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;
+    int idx_res = 0;
+    sqlite::unique_ptr<char[]> str;
+    // idx = 1 => name
+    // idx = 2 => version
+    if (!info.constraints().empty())
+    {
+      auto sz =  info.constraints().size() + 1u;
+      str.reset(new (sqlite::memory_tag{}) char[sz]);
+      std::memset(str.get(), '\0', sz);
+    }
+
+    for (auto & ct : info.constraints())
+    {
+      if (idx_res == 0) // if we're first, set the thing
+        idx_res = (ct.iColumn + 1);
+      // check if we're already building an index
+      if (idx_res != (ct.iColumn + 1)) // wrong column, ignore.
+        continue;
+      if ( ct.usable != 0 ) // aye, that's us
+      {
+        switch (ct.op)
+        {
+          // 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_of(ct).argvIndex = ++idx; // use it -> value in this position in `filter`.
+            info.usage_of(ct).omit = 1; // tell sqlite that we're sure enough, so sqlite doesn't check
+            break;
+          default:
+            break;
+        }
+      }
+    }
+
+    if (info.order_by().size() == 1u)
+    {
+      if ((info.order_by()[0].iColumn == 0)
+          || (idx == 0) || (idx == 1))
+      {
+        info.set_already_ordered();
+        if (info.order_by()[0].desc != 0)
+          idx |= 0b1001; // encode inversion, because why not ?
+      }
+      else if ((info.order_by()[0].iColumn == 0) || (idx == 0) || (idx == 2))
+      {
+        info.set_already_ordered();
+        if (info.order_by()[0].desc)
+          idx |= 0b1010; // encode inversion, because why not ?
+      }
+    }
+    info.set_index(idx_res);
+    if (str)
+      info.set_index_string(str.release(), true);
+    return {};
+  }
+};
+
+struct multi_index_map final : sqlite::vtab::eponymous_module<map_impl>
+{
+  sqlite::result<map_impl> connect(sqlite::connection, int, const char * const *)
+  {
+    return map_impl{};
+  }
+};
+
+
+std::initializer_list<std::tuple<std::string, std::string>> data = {
+    {"atomic",                    "1.53.0"},
+    {"chrono",                    "1.47.0"},
+    {"container",                 "1.48.0"},
+    {"context",                   "1.51.0"},
+    {"contract",                  "1.67.0"},
+    {"coroutine",                 "1.53.0"},
+    {"date_time",                 "1.29.0"},
+    {"exception",                 "1.36.0"},
+    {"fiber",                     "1.62.0"},
+    {"filesystem",                "1.30.0"},
+    {"graph",                     "1.18.0"},
+    {"graph_parallel",            "1.40.0"},
+    {"headers",                   "1.00.0"},
+    {"iostreams",                 "1.33.0"},
+    {"json",                      "1.75.0"},
+    {"locale",                    "1.48.0"},
+    {"log",                       "1.54.0"},
+    {"math",                      "1.23.0"},
+    {"mpi",                       "1.35.0"},
+    {"nowide",                    "1.73.0"},
+    {"program_options",           "1.32.0"},
+    {"python",                    "1.19.0"},
+    {"random",                    "1.15.0"},
+    {"regex",                     "1.18.0"},
+    {"serialization",             "1.32.0"},
+    {"stacktrace",                "1.65.0"},
+    {"system",                    "1.35.0"},
+    {"test",                      "1.21.0"},
+    {"thread",                    "1.25.0"},
+    {"timer",                     "1.9.0"},
+    {"type_erasure",              "1.54.0"},
+    {"url",                       "1.81.0"},
+    {"wave",                      "1.33.0"}
+};
+
+void print(std::ostream & os, sqlite::resultset rw, boost::source_location loc = BOOST_CURRENT_LOCATION)
+{
+  os << loc.file_name() << "(" << loc.line() << "): ";
+  os << "[";
+  for (auto & r : rw)
+    os << r.at(0).get_text() << ", ";
+  os << "]" << std::endl;
+}
+
+int main (int /*argc*/, char * /*argv*/[])
+{
+  sqlite::connection conn{":memory:"};
+  auto & m = sqlite::create_module(conn, "my_map", multi_index_map());
+  boost::ignore_unused(m);
+
+  {
+    auto p = conn.prepare("insert into my_map (name, version) values (?, ?);");
+    for (const auto & d : ::data)
+      p.execute(d);
+  }
+
+  print(std::cout, conn.query("select * from my_map order by name desc;"));
+  print(std::cout, conn.query("select * from my_map where name = 'url';"));
+  print(std::cout, conn.query("select * from my_map where name <  'url';"));
+  print(std::cout, conn.query("select * from my_map where name >= 'system' ;"));
+  print(std::cout, conn.query("select * from my_map where name >= 'system' and name < 'url' ;"));
+  print(std::cout, conn.query("select * from my_map where name > 'system' and name <= 'url' ;"));
+  print(std::cout, conn.query("select * from my_map where name >  'json';"));
+  print(std::cout, conn.query("select * from my_map where name >= 'json';"));
+  print(std::cout, conn.query("select * from my_map where name <  'json';"));
+
+  print(std::cout, conn.query("select * from my_map where name == 'json' order by name  asc;"));
+  print(std::cout, conn.query("select * from my_map where name == 'json' and name == 'url';"));
+  print(std::cout, conn.query("select * from my_map where name == 'json' order by name desc;"));
+
+  print(std::cout, conn.query("select * from my_map where name < 'url' and name >= 'system' order by name desc;"));
+  print(std::cout, conn.query("select * from my_map where version == '1.81.0';"));
+
+  print(std::cout, conn.query("select * from my_map where version > '1.32.0' order by version desc;"));
+  conn.query("delete from my_map where version == '1.81.0';");
+  print(std::cout, conn.query("select * from my_map where name < 'system' and name <= 'system' ;"));
+
+
+  return 0;
+}
\ No newline at end of file
diff --git a/example/ordered_map.cpp b/example/ordered_map.cpp
new file mode 100644
index 0000000..26a285b
--- /dev/null
+++ b/example/ordered_map.cpp
@@ -0,0 +1,304 @@
+//
+// Copyright (c) 2022 Klemens Morgenstern (klemens.morgenstern@gmx.net)
+//
+// Distributed under the Boost Software License, Version 1.0. (See accompanying
+// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
+//
+
+#include <boost/sqlite.hpp>
+#include <boost/container/flat_map.hpp>
+#include <iostream>
+
+using namespace boost;
+
+// this examples shows how to expose an ordered map as a vtable.
+
+// tag::cursor[]
+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;
+  }
+  // end::cursor[]
+  //tag::filter[]
+  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 {};
+  }
+  //end::filter[]
+  // tag::cursor[]
+  bool eof() noexcept override // <6>
+  {
+    return begin == end;
+  }
+};
+// end::cursor[]
+
+
+// tag::table[]
+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;
+  }
+
+  // end::table[]
+  // tag::best_index[]
+  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 {};
+  }
+  // end::best_index[]
+  // tag::table[]
+};
+
+// end::table[]
+
+// tag::module[]
+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};
+  }
+};
+// end::module[]
+
+
+
+std::initializer_list<std::pair<std::string, std::string>> init_data = {
+    {"atomic",                    "1.53.0"},
+    {"chrono",                    "1.47.0"},
+    {"container",                 "1.48.0"},
+    {"context",                   "1.51.0"},
+    {"contract",                  "1.67.0"},
+    {"coroutine",                 "1.53.0"},
+    {"date_time",                 "1.29.0"},
+    {"exception",                 "1.36.0"},
+    {"fiber",                     "1.62.0"},
+    {"filesystem",                "1.30.0"},
+    {"graph",                     "1.18.0"},
+    {"graph_parallel",            "1.40.0"},
+    {"headers",                   "1.00.0"},
+    {"iostreams",                 "1.33.0"},
+    {"json",                      "1.75.0"},
+    {"locale",                    "1.48.0"},
+    {"log",                       "1.54.0"},
+    {"math",                      "1.23.0"},
+    {"mpi",                       "1.35.0"},
+    {"nowide",                    "1.73.0"},
+    {"program_options",           "1.32.0"},
+    {"python",                    "1.19.0"},
+    {"random",                    "1.15.0"},
+    {"regex",                     "1.18.0"},
+    {"serialization",             "1.32.0"},
+    {"stacktrace",                "1.65.0"},
+    {"system",                    "1.35.0"},
+    {"test",                      "1.21.0"},
+    {"thread",                    "1.25.0"},
+    {"timer",                     "1.9.0"},
+    {"type_erasure",              "1.54.0"},
+    {"url",                       "1.81.0"},
+    {"wave",                      "1.33.0"}
+};
+
+void print(std::ostream & os, sqlite::resultset rw)
+{
+  os << "[";
+  for (auto & r : rw)
+    os << r.at(0).get_text() << ", ";
+  os << "]" << std::endl;
+}
+
+int main (int /*argc*/, char * /*argv*/[])
+{
+  sqlite::connection conn{":memory:"};
+
+  // tag::module[]
+  ordered_map_module & m = sqlite::create_module(conn, "my_map", ordered_map_module(init_data));
+  // end::module[]
+  boost::ignore_unused(m);
+
+  print(std::cout, conn.query("select * from my_map order by name desc;"));
+  print(std::cout, conn.query("select * from my_map where name = 'url';"));
+  print(std::cout, conn.query("select * from my_map where name < 'url' and name >= 'system' ;"));
+  print(std::cout, conn.query("select * from my_map where name >  'json';"));
+  print(std::cout, conn.query("select * from my_map where name >= 'json';"));
+  print(std::cout, conn.query("select * from my_map where name <  'json';"));
+  print(std::cout, conn.query("select * from my_map where name == 'json' order by name  asc;"));
+  print(std::cout, conn.query("select * from my_map where name == 'json' order by name desc;"));
+
+  print(std::cout, conn.query("select * from my_map where name < 'url' and name >= 'system' order by name desc;"));
+  print(std::cout, conn.query("select * from my_map where data == '1.81.0';"));
+
+  conn.query("delete from my_map where data == '1.81.0';");
+
+  return 0;
+}
\ No newline at end of file
diff --git a/example/tutorial.cpp b/example/tutorial.cpp
new file mode 100644
index 0000000..fbfa4a3
--- /dev/null
+++ b/example/tutorial.cpp
@@ -0,0 +1,136 @@
+//
+// Copyright (c) 2022 Klemens Morgenstern (klemens.morgenstern@gmx.net)
+//
+// Distributed under the Boost Software License, Version 1.0. (See accompanying
+// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
+//
+
+#include <boost/sqlite.hpp>
+#include <boost/describe/class.hpp>
+
+#include <iostream>
+
+using namespace boost;
+
+// tag::query_t[]
+struct users { std::string name; std::int64_t age; };
+BOOST_DESCRIBE_STRUCT(users, (), (name, age));
+// end::query_t[]
+
+int main(int /*argc*/, char */*argv*/[])
+{
+#if defined(SQLITE_EXAMPLE_USE_DB)
+// tag::conn_path[]
+  sqlite::connection conn{"./my_db.db"};
+// end::conn_path[]
+#else
+// tag::conn_mem[]
+  sqlite::connection conn{sqlite::in_memory};
+// end::conn_mem[]
+#endif
+  // tag::execute[]
+  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);
+  )");
+  // end::execute[]
+
+  // tag::query1[]
+  sqlite::resultset q = conn.query("SELECT name, age FROM users ORDER BY id ASC;");
+  // end::query1[]
+
+  // tag::query2[]
+  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");
+  // end::query2[]
+
+
+  // tag::query3[]
+  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;
+  // end::query3[]
+
+  // tag::query4[]
+  for (const auto & a : conn.query<users>("SELECT age, name FROM users;"))
+    std::cout << "User " << a.name << " is " << a.age << " old." << std::endl;
+  // end::query4[]
+
+  // tag::query_strict[]
+  for (const auto & a : conn.query<users>("SELECT age, name FROM users;").strict())
+    std::cout << "User " << a.name << " is " << a.age << " old." << std::endl;
+  // end::query_strict[]
+
+
+  // tag::statement_insert[]
+  conn.prepare("insert into users (name, age) values (?1, ?2), (?3, ?4)")
+      .execute({"Paul", 31, "Mark", 51});
+  // end::statement_insert[]
+
+
+  // tag::statement_multi_insert[]
+  {
+    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();
+  }
+  // end::statement_multi_insert[]
+
+  // tag::to_upper[]
+  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");
+  // end::to_upper[]
+
+
+  // tag::oldest[]
+  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;
+  // end::oldest[]
+
+  return 0;
+}
\ No newline at end of file
diff --git a/example/tutorial_ec.cpp b/example/tutorial_ec.cpp
new file mode 100644
index 0000000..2c6af7f
--- /dev/null
+++ b/example/tutorial_ec.cpp
@@ -0,0 +1,197 @@
+//
+// Copyright (c) 2022 Klemens Morgenstern (klemens.morgenstern@gmx.net)
+//
+// Distributed under the Boost Software License, Version 1.0. (See accompanying
+// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
+//
+
+#include <boost/sqlite.hpp>
+#include <boost/describe/class.hpp>
+
+#include <iostream>
+
+using namespace boost;
+
+// tag::query_t[]
+struct users { std::string name; std::int64_t age; };
+BOOST_DESCRIBE_STRUCT(users, (), (name, age));
+// end::query_t[]
+
+int main(int /*argc*/, char */*argv*/[])
+{
+  system::error_code ec;
+  sqlite::error_info ei;
+
+  sqlite::connection conn;
+
+#if defined(SQLITE_EXAMPLE_USE_DB)
+  // tag::conn_path[]
+  conn.connect("./my_db.db", SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, ec);
+  // end::conn_path[]
+#else
+  // tag::conn_mem[]
+  conn.connect(sqlite::in_memory, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, ec);
+  // end::conn_mem[]
+#endif
+
+  if (ec)
+    goto error;
+
+  // tag::execute[]
+  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);
+  // end::execute[]
+
+  if (ec)
+    goto error;
+
+{
+  // tag::query1[]
+  sqlite::resultset q = conn.query("SELECT name, age FROM users ORDER BY id ASC;", ec, ei);
+  if (ec)
+    goto error;
+  // end::query1[]
+
+  // tag::query2[]
+  assert(q.current()[0].get_text() == "Alice");
+  assert(q.read_next(ec, ei)); // true if it's the last row!
+  if (ec)
+    goto error;
+  assert(q.current()[0].get_text() == "Bob");
+  // end::query2[]
+}
+
+  // tag::query3[]
+  for (const auto & t :
+          conn.query<std::tuple<sqlite::string_view, std::int64_t>>("SELECT name, age FROM users;", ec, ei))
+    std::cout << "User " << std::get<0>(t) << " is " << std::get<1>(t) << " old." << std::endl;
+
+  if (ec)
+    goto error;
+
+  // end::query3[]
+
+  // tag::query4[]
+  for (const auto & a : conn.query<users>("SELECT age, name FROM users;", ec, ei))
+    std::cout << "User " << a.name << " is " << a.age << " old." << std::endl;
+  if (ec)
+    goto error;
+  // end::query4[]
+
+  // tag::query_strict[]
+  {
+    auto r = conn.query<users>("SELECT age, name FROM users;", ec, ei).strict();
+    while (r.read_next(ec, ei) && !ec)
+    {
+      // because this is strict, it takes ec & ei for conversion errors.
+      const auto & a = r.current(ec, ei);
+      if (ec)
+        break;
+      std::cout << "User " << a.name << " is " << a.age << " old." << std::endl;
+    }
+  }
+  if (ec)
+    goto error;
+
+  // end::query_strict[]
+
+
+  // tag::statement_insert[]
+
+  {
+    auto p = conn.prepare("insert into users (name, age) values (?1, ?2), (?3, ?4)", ec, ei);
+    if (!ec)
+      std::move(p).execute({"Paul", 31, "Mark", 51}, ec, ei);
+    if (ec)
+      goto error;
+  }
+  // end::statement_insert[]
+
+
+  // tag::statement_multi_insert[]
+  {
+    conn.execute("BEGIN TRANSACTION;", ec, ei);
+    if (ec)
+      goto error;
+    sqlite::transaction t{conn, sqlite::transaction::adopt_transaction}; // use a transaction to speed this up
+
+    auto st = conn.prepare(R"(insert into users ("name", age) values ($name, $age))", ec, ei);
+    if (!ec)
+      st.execute({{"name", "Allen"}, {"age", 43}}, ec, ei);
+    if (!ec)
+      st.execute({{"name", "Tom"},   {"age", 84}}, ec, ei);
+    if (!ec)
+      t.commit(ec, ei);
+
+    if (ec)
+      goto error;
+  }
+
+
+  // end::statement_multi_insert[]
+
+  // tag::to_upper[]
+  sqlite::create_scalar_function(
+      conn,
+      "to_upper",
+      [](sqlite::context<>, // <1>
+             boost::span<sqlite::value, 1u> val // <2>
+             ) -> sqlite::result<std::string>
+      {
+        if (val[0].type() != sqlite::value_type::text)
+          return sqlite::error(SQLITE_MISUSE, "Value must be string"); // <2>
+        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 // <3>
+      , ec, ei);
+  if (ec)
+    goto error;
+  {
+    auto qu = conn.query("SELECT to_upper(name) FROM users WHERE name == 'Alice';", ec, ei);
+    if (ec) goto error;
+    assert(qu.current()[0].get_text() == "ALICE");
+  }
+    // end::to_upper[]
+
+
+  // tag::oldest[]
+  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) noexcept // no possible errors, no result needed
+    {
+      if (args[0].get_int() >= retirement_age)
+        count += 1;
+    }
+    std::int64_t final()  noexcept  { return count; }
+  };
+  sqlite::create_aggregate_function<retirees>(conn, "retirees", std::make_tuple(65), {}, ec, ei);
+  if (ec) goto error;
+
+  {
+    auto q = conn.query("select retirees(age) from users;", ec, ei);
+    if (ec) goto error;
+    std::cout << "The amount of retirees is " << q.current()[0].get_text() << std::endl;
+  }
+  // end::oldest[]
+
+  return 0;
+
+ error:
+  fprintf(stderr, "sqlite failure: %s - %s\n", ec.message().c_str(), ei.message().c_str());
+  return EXIT_FAILURE;
+}
\ No newline at end of file
diff --git a/example/url.cpp b/example/url.cpp
new file mode 100644
index 0000000..f5ed4f9
--- /dev/null
+++ b/example/url.cpp
@@ -0,0 +1,315 @@
+//
+// Copyright (c) 2022 Klemens Morgenstern (klemens.morgenstern@gmx.net)
+//
+// Distributed under the Boost Software License, Version 1.0. (See accompanying
+// file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
+//
+
+#include <boost/sqlite/extension.hpp>
+#include <boost/sqlite/vtable.hpp>
+#include <boost/system/result.hpp>
+#include <boost/url.hpp>
+
+using namespace boost;
+
+// tag::subtype[]
+constexpr int pct_subtype = static_cast<int>('U');
+
+void tag_invoke(sqlite::set_result_tag, sqlite3_context * ctx, urls::pct_string_view value)
+{
+  using boost::sqlite::ext::sqlite3_api;
+  // we're using the sqlite API here directly, because we need to set a different subtype
+  sqlite3_result_text(ctx, value.data(), value.size(), nullptr);
+  sqlite3_result_subtype(ctx, pct_subtype);
+}
+
+
+void tag_invoke(sqlite::set_result_tag, sqlite3_context * ctx, const urls::segments_encoded_view & value)
+{
+  using boost::sqlite::ext::sqlite3_api;
+  // we're using the sqlite API here directly, because we need to set a different subtype
+  sqlite3_result_text(ctx, value.buffer().data(), value.buffer().size(), nullptr);
+  sqlite3_result_subtype(ctx, pct_subtype);
+}
+// end::subtype[]
+
+struct url_cursor final
+    : sqlite::vtab::cursor<
+        variant2::variant<variant2::monostate, core::string_view, urls::pct_string_view>
+        >
+{
+  url_cursor(urls::url_view view) : view(view ) {}
+
+  urls::url_view view;
+  bool done{false};
+
+  sqlite::result<void> next() { done = true; return {};}
+
+  sqlite::result<sqlite3_int64> row_id() {return static_cast<sqlite3_int64>(0);}
+  sqlite::result<column_type> column(int i, bool /*nochange*/)
+  {
+    switch (i)
+    {
+      case 0: return view.scheme();
+      case 1: return view.encoded_user();
+      case 2: return view.encoded_password();
+      case 3: return view.encoded_host();
+      case 4: return view.port();
+      case 5: return view.encoded_path();
+      case 6: return view.encoded_query();
+      case 7: return view.encoded_fragment();
+      case 8: return view.buffer();
+      default:
+        return variant2::monostate{};
+    }
+  }
+  sqlite::result<void> filter(int /*idx*/, const char * /*idxStr*/, span<sqlite::value> values)
+  {
+    if (values.size() > 0u)
+      view = urls::parse_uri(values[0].get_text()).value();
+    return {};
+  }
+
+  bool eof() noexcept {return done || view.empty();}
+};
+
+struct url_wrapper final : sqlite::vtab::table<url_cursor>
+{
+  urls::url value;
+  const char * declaration() override
+  {
+    return R"(
+          create table url(
+              scheme text,
+              user text,
+              password text,
+              host text,
+              port text,
+              path text,
+              query text,
+              fragment text,
+              url text hidden);)";
+  }
+
+  sqlite::result<url_cursor> open() override
+  {
+    return url_cursor{value};
+  }
+
+  sqlite::result<void> best_index(sqlite::vtab::index_info & info) override
+  {
+    for (const auto & constraint : info.constraints())
+    {
+      if (constraint.iColumn == 8 && constraint.usable)
+      {
+        if (constraint.op != SQLITE_INDEX_CONSTRAINT_EQ)
+          return {SQLITE_MISUSE,
+                  sqlite::error_info("query only support equality constraints")};
+        info.usage_of(constraint).argvIndex = 1;
+        info.set_index(1);
+      }
+    }
+
+    return {};
+  }
+};
+
+
+struct url_module final : sqlite::vtab::eponymous_module<url_wrapper>
+{
+  sqlite::result<url_wrapper> connect(sqlite::connection /*db*/,
+                                      int /*argc*/, const char * const */*argv*/)
+  {
+      return url_wrapper{};
+  }
+};
+
+struct segements_cursor final : sqlite::vtab::cursor<
+    variant2::variant<variant2::monostate, std::int64_t, core::string_view, urls::segments_encoded_view>>
+{
+  segements_cursor(urls::segments_encoded_view view) : view(view) {}
+  urls::segments_encoded_view view;
+  urls::segments_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
+  {
+    //nochange = true;
+    switch (i)
+    {
+      case 0: return std::distance(view.begin(), itr);
+      case 1: return *itr;
+      case 2: return view;
+      default:
+        return variant2::monostate{};
+    }
+  }
+  sqlite::result<void> filter(int /*idx*/, const char * /*idxStr*/,
+                              span<sqlite::value> values) override
+  {
+    if (values.size() > 0u)
+      view = urls::segments_encoded_view(values[0].get_text());
+    itr = view.begin();
+    return {};
+  }
+  bool eof() noexcept override {return itr == view.end();}
+};
+
+struct segment_wrapper final : sqlite::vtab::table<segements_cursor>
+{
+  urls::segments_encoded_view value;
+  const char * declaration() override
+  {
+    return R"(
+          create table segments(
+              idx integer,
+              segment text,
+              segments text hidden);)";
+  }
+
+
+
+  sqlite::result<segements_cursor> open() override
+  {
+    return segements_cursor{value};
+  }
+
+  sqlite::result<void> best_index(sqlite::vtab::index_info & info) override
+  {
+    for (auto & constraint : info.constraints())
+    {
+      if (constraint.iColumn == 2
+          && constraint.usable)
+      {
+        if (constraint.op != SQLITE_INDEX_CONSTRAINT_EQ)
+          return {SQLITE_OK, sqlite::error_info("segments only support equality constraints")};
+        info.usage_of(constraint).argvIndex = 1;
+        info.set_index(1);
+      }
+    }
+
+    return {};
+  }
+};
+
+
+struct segments_module final : sqlite::vtab::eponymous_module<segment_wrapper>
+{
+  sqlite::result<segment_wrapper> connect(sqlite::connection /*conn*/,
+                                          int /*argc*/, const char * const */*argv*/)
+  {
+    return segment_wrapper{};
+  }
+};
+
+// tag::query_cursor[]
+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();}
+};
+// end::query_cursor[]
+
+// tag::query_boiler_plate[]
+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{};
+  }
+};
+// end::query_boiler_plate[]
+
+BOOST_SQLITE_EXTENSION(url, conn)
+{
+  sqlite::create_module(conn, "url", url_module{});
+  sqlite::create_module(conn, "segments", segments_module());
+
+  // tag::query_boiler_plate[]
+  sqlite::create_module(conn, "query", query_module());
+  // end::query_boiler_plate[]
+  sqlite::create_scalar_function(
+      conn, "pct_decode",
+      +[](boost::sqlite::context<> , boost::span<boost::sqlite::value, 1u> s)
+      {
+          return urls::pct_string_view(s[0].get_text()).decode();
+      });
+
+  sqlite::create_scalar_function(
+      conn, "pct_encode",
+      +[](boost::sqlite::context<> , boost::span<boost::sqlite::value, 1u> s)
+      {
+        return urls::encode(s[0].get_text(), urls::pchars);
+      });
+}
\ No newline at end of file
diff --git a/example/url.sql b/example/url.sql
new file mode 100644
index 0000000..813917e
--- /dev/null
+++ b/example/url.sql
@@ -0,0 +1,19 @@
+SELECT load_extension('./url');
+
+-- invoke the url function to get any url by it's elements
+select scheme, user, password, host , port, path, query, fragment, "url"
+    from url('ws://echo.example.com/?name=boost&thingy=foo&name=sqlite&#demo');
+
+-- table-ize the segments of url
+select idx, segment from segments('/foo/bar/foo/xyz');
+
+-- tag::query[]
+-- 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';
+-- end::query[]
+
+-- do a left join on the table, so we can use the table function to normalize data.
+select host , query.name, query.value
+from url('ws://echo.example.com/?name=boost&thingy=foo&name=sqlite#demo') left join query on query.query_string = url.query;
+