summaryrefslogtreecommitdiff
path: root/subprojects/boost-sqlite/example
diff options
context:
space:
mode:
Diffstat (limited to 'subprojects/boost-sqlite/example')
-rw-r--r--subprojects/boost-sqlite/example/CMakeLists.txt20
-rw-r--r--subprojects/boost-sqlite/example/csv.cpp273
-rw-r--r--subprojects/boost-sqlite/example/describe.cpp202
-rw-r--r--subprojects/boost-sqlite/example/multi_index.cpp433
-rw-r--r--subprojects/boost-sqlite/example/ordered_map.cpp304
-rw-r--r--subprojects/boost-sqlite/example/tutorial.cpp136
-rw-r--r--subprojects/boost-sqlite/example/tutorial_ec.cpp197
-rw-r--r--subprojects/boost-sqlite/example/url.cpp315
-rw-r--r--subprojects/boost-sqlite/example/url.sql19
9 files changed, 1899 insertions, 0 deletions
diff --git a/subprojects/boost-sqlite/example/CMakeLists.txt b/subprojects/boost-sqlite/example/CMakeLists.txt
new file mode 100644
index 0000000..480ed7e
--- /dev/null
+++ b/subprojects/boost-sqlite/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/subprojects/boost-sqlite/example/csv.cpp b/subprojects/boost-sqlite/example/csv.cpp
new file mode 100644
index 0000000..47fe288
--- /dev/null
+++ b/subprojects/boost-sqlite/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/subprojects/boost-sqlite/example/describe.cpp b/subprojects/boost-sqlite/example/describe.cpp
new file mode 100644
index 0000000..9b00f8b
--- /dev/null
+++ b/subprojects/boost-sqlite/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/subprojects/boost-sqlite/example/multi_index.cpp b/subprojects/boost-sqlite/example/multi_index.cpp
new file mode 100644
index 0000000..0ce4a84
--- /dev/null
+++ b/subprojects/boost-sqlite/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/subprojects/boost-sqlite/example/ordered_map.cpp b/subprojects/boost-sqlite/example/ordered_map.cpp
new file mode 100644
index 0000000..26a285b
--- /dev/null
+++ b/subprojects/boost-sqlite/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/subprojects/boost-sqlite/example/tutorial.cpp b/subprojects/boost-sqlite/example/tutorial.cpp
new file mode 100644
index 0000000..fbfa4a3
--- /dev/null
+++ b/subprojects/boost-sqlite/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/subprojects/boost-sqlite/example/tutorial_ec.cpp b/subprojects/boost-sqlite/example/tutorial_ec.cpp
new file mode 100644
index 0000000..2c6af7f
--- /dev/null
+++ b/subprojects/boost-sqlite/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/subprojects/boost-sqlite/example/url.cpp b/subprojects/boost-sqlite/example/url.cpp
new file mode 100644
index 0000000..f5ed4f9
--- /dev/null
+++ b/subprojects/boost-sqlite/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/subprojects/boost-sqlite/example/url.sql b/subprojects/boost-sqlite/example/url.sql
new file mode 100644
index 0000000..813917e
--- /dev/null
+++ b/subprojects/boost-sqlite/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;
+
generated by cgit v1.2.3 (git 2.46.0) at 2025-09-20 21:10:28 +0000