This library provides a simple C++ sqlite library.
It includes:
sqlite provides an excellent C-API, so this library does not attempt to hide, but to augment it.
You can either build the library and link against boost_sqlite
for embedding it,
or boost_sqlite_ext
for extensions.
If you want to use it for extensions you'll need to
define BOOST_SQLITE_COMPILE_EXTENSION
or include boost/sqlite/extensions.hpp
first.
First we open a database. Note that this can be ":memory:"
for an in-memory database.
boost::sqlite::connection conn{"./my-database.db"};
Next we're creating tables using boost::sqlite::connection::execute, because it can execute multiple statements in one command:
conn.execute(R"(
create table if not exists author (
id integer primary key autoincrement,
first_name text,
last_name text
);
create table if not exists library(
id integer primary key autoincrement,
name text unique,
author integer references author(id)
);
)"
);
Next, we'll use a prepared statement to insert multiple values by index:
conn.prepare("insert into author (first_name, last_name) values (?1, ?2), (?3, ?4), (?5, ?6), (?7, ?8)")
.execute({"vinnie", "falco", "richard", "hodges", "ruben", "perez", "peter", "dimov"});
Prepared statements can also be used multiple time and used with named parameters instead of indexed.
{
conn.query("begin transaction;");
auto st = conn.prepare("insert into library (\"name\", author) values ($library, "
" (select id from author where first_name = $fname and last_name = $lname))");
st.execute({{"library", "beast"}, {"fname", "vinnie"}, {"lname", "falco"}});
st.execute({{"library", "mysql"}, {"fname", "ruben"}, {"lname", "perez"}});
st.execute({{"library", "mp11"}, {"fname", "peter"}, {"lname", "dimov"}});
st.execute({{"library", "variant2"}, {"fname", "peter"}, {"lname", "dimov"}});
conn.query("commit;");
}
Now that we have the values in the table, let's add a custom aggregate function to create a comma separated list:
struct collect_libs
{
void step(std::string & name, span<sqlite::value, 1> args)
{
if (name.empty())
name = args[0].get_text();
else
(name += ", ") += args[0].get_text();
}
std::string final(std::string & name) { return name; }
};
sqlite::create_aggregate_function(conn, "collect_libs", collect_libs{});
Print out the query with aggregates libraries:
for (boost::sqlite::row r : conn.query(
"select first_name, collect_libs(name) "
" from author inner join library l on author.id = l.author group by last_name"))
std::cout << r.at(0u).get_text() << " authored " << r.at(1u).get_text() << std::endl;
Alternatively a query result can also be read manually instead of using a loop:
boost::sqlite::row r;
boost::sqlite::query q = conn.query(
"select first_name, collect_libs(name) "
" from author inner join library l on author.id = l.author group by last_name")
do
{
auto r = q.current();''
std::cout << r.at(0u).get_text() << " authored " << r.at(1u).get_text() << std::endl;
}
while (q.read_next());
sqlite3 has a weak typesystem, where everything is one of following [value_types](@ref boost::sqlite::value_type):
integer
floating
text
blob
null
The result of a query is a [field](@ref boost::sqlite::field) type, while a [value](@ref boost::sqlite::value) is used in functions.
Fields & values can have subtypes, while parameter to prepared statements do not have thos associated.
Because of this the values that can be bound to an [execute](@ref boost::sqlite::statement::execute) need to be convertible to a fixed set of types (see [param_ref](@ref boost::sqlite::param_ref) for details).
When a [value](@ref boost::sqlite::value) is returned from a custom function, such as done through [create_scalar_function](@ref boost::sqlite::create_scalar_function), additional types can be added with the following tag_invoke function:
void tag_invoke(const struct set_result_tag &, sqlite3_context * ctx, const my_type & value);
An implementation can look like this:
void tag_invoke(const struct set_result_tag &, sqlite3_context * ctx, const my_type & value)
{
auto data = value.to_string();
sqlite3_result_text(ctx, data.c_str(), data.size(), sqlite3_free);
sqlite3_result_subtype(ctx, my_subtype);
}
Queries can be typed through tuples, describe or, if you're on C++20, by plain structs.
The type to hold them is static_resultset<T>
which will check if the columns match the result types before usage.
Tuples are matched by position, structs by name.
for (auto q : conn.query<std::tuple<std::string, std::string>>(
"select first_name, collect_libs(name) "
" from author inner join library l on author.id = l.author group by last_name"))
std::cout << std::get<0>(q) << " authored " << std::get<0>(q) << std::endl;
struct query_result { std::string first_name, lib_name;};
BOOST_DESCRIBE_STRUCT(query_result, (), (first_name, lib_name)); // this can be omitted with C++20.
for (auto q : conn.query<query_result>(
"select first_name, collect_libs(name) as lib_name"
" from author inner join library l on author.id = l.author group by last_name"))
std::cout << q.first_name << " authored " << q.lib_name << std::endl;
The following types are allowed in a static query result:
sqlite::value
int
sqlite_int64
double
std::string
sqlite::string_view
sqlite::blob
sqlite::blob_view
You'll need to include boost/sqlite/static_resultset.hpp
for this to work.
Since sqlite is running in the same process you can add custom functions that can be used from within sqlite.
This library also simplifies adding virtual tables significantly; virtual tables are table that are backed by code instead of data.
See [create_module](@ref boost::sqlite::create_module) and [prototype](@ref boost::sqlite::vtab_module_prototype) for more details.
This library can also be used to build a sql plugin:
BOOST_SQLITE_EXTENSION(testlibrary, conn)
{
// create a function that can be used in the plugin
create_scalar_function(
conn, "assert",
[](boost::sqlite::context<>, boost::span<boost::sqlite::value, 1u> sp)
{
if (sp.front().get_int() == 0)
throw std::logic_error("assertion failed");
});
}
The plugin can then be loaded & used like this:
SELECT load_extension('./test_library');
select assert((3 * 4) = 12);
To build a plugin you need to define BOOST_SQLITE_COMPILE_EXTENSION
(e.g. by including boost/sqlite/extension.hpp
or linking against boost_sqlite_ext
).
This will include the matching sqlite header (sqlite3ext.h
) and
will move all the symbols into an inline namespace ext
inside boost::sqlite
.
While there are many sqlite wrappers out there, most haven't been updated in the last five years - while sqlite has.
Here are some actively maintained ones:
SQLiteCpp is the closest to this library, a C++11 wrapper only depending on sqlite & the STL. It's great and served as an inspiration for this library. boost.sqlite does provide more functionality when it comes to hooks, custom functions & virtual tables. Furthermore, boost.sqlite has a non-throwing interface and supports variants & json, as those are available through boost.
This library takes a different approach, by making everything an iostream
interface.
As the name says, it's an ORM. So it does more than give easy access to a database.