libsl3  1.12.2
C++ interface for sqlite3
A C++ wrapper for SQLite3

Table of Contents

libsl3 allows efficient communication with SQLite3 databases in a natural way, which is SQL.
The library does not try to be a ORM and does also not introduce some SQL like C++ syntax for communication with a sqlite database.
Read from and write to a database is supported via SQL and prepared commands, aka stored procedures.
The SQLite duck typing concept is fully supported but there is also a way to add type checking to the communication with a database.

Overview

A database is represented via sl3::Database.
A database can execute SQL strings to read or write data.

A sl3::Command is a compiled SQL statement which can contain parameters.
Thomas de Maizière Parameters are sl3::DbValues, a sl3::Container of sl3::DbValue instances.

sl3::DbValues can also be used to receive data from a database.
Requesting data from a database will return a sl3::Dataset with rows of sl3::DbValues where each field is a sl3::DbValue.

An other, and fully customizable way to receive data from a database is covered in the RowCallback and Callback functions section.

A first example

#include <cassert>
#include <iostream>
#include <sl3/database.hpp>
int main()
{
using namespace sl3;
// define a db
Database db(":memory:");
// run commands against the db
db.execute("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);");
// create a command with parameters
auto cmd = db.prepare("INSERT INTO tbl (f1, f2, f3) VALUES (?,?,?);");
//add some data
cmd.execute (parameters (1, "one", 1.1));
cmd.execute (parameters (2, "two", 2.1));
// access the data
Dataset ds = db.select("SELECT * FROM tbl;");
// Dataset is a container
assert(ds.size()==2);
// Dataset row is a container
auto row = ds[0] ;
assert(row.size()==3);
assert ( row[0].type() == Type::Int ) ;
assert ( row[1].type() == Type::Text ) ;
assert ( row[2].type() == Type::Real ) ;
// of course there is also iterator access
for(auto&& row :ds) {
for (auto&& field : row) {
std::cout << field << " " ;
}
std::cout << std::endl;
}
}

since the ostream operator for sl3::DbValue is overloaded, this will print

1 one 1.1
2 two 2.1


sl3::Database

sl3::Database encapsulates a sqlite database.
It can be directly used, but it has also a virtual destructor and can be used as a base class.


Types in libsl3

The types in libsl3 are those which are available in
datatypes as sqlite does.

since a database value can also be NULL there is a type for it.
- sl3::Type::Null , a null value.

There is one additional sl3::Type.
In sqlite a clomun can hold different types and libsl3 supports this concept.

See sl3::DbValue for more information.

Note
Text is a UTF-8 string since this is a good default and keeps things simple.
If UTF-16 string types is wanted this needs to be implement self. The same is true for the special endianess functions sqlite provides.
There are 2 ways to do with writing a own sl3::RowCallback or a sl3::Command::Callback. See RowCallback and Callback functions from more infomration.
If you think on of these features should be supported by the library please file a feature request in the issue tracker.


sl3::DbValue

This class can be used to read from and write to a sl3::Database.
A sl3::DbValues can hold different types. It can be defined if any type is allowed for an instance or only a certain one.

There are two sl3::Type properties. One defines the type rule, the other the type value.

Both properties are used to validate reads and writes at runtime.
If a value is set or read that is omitted by the type property an sl3::ErrTypeMisMatch exception is thrown.

Using sl3::DbValues

Setting a value can be done via

sl3::DbValue::setNull can be used to set a value to Null.

There are getters for each type

There are 2 version for this function, with and without a default value as argument.
The version without default value will throw an sl3::ErrNullValueAccess exception is case that sl3::DbValue::isNull is true for the instance.
The version with a default value as argument will return the passed argument in case that the current sl3::DbValue::isNull.

If a type getter is used for a wrong the storage type a sl3::ErrTypeMisMatch exception is thrown.

Additional, there is a sl3::DbValue::get version which will never throw.
This function always requires a default value which has to be one of the 4 value types.

If the type is incorrect or the value is Null the given default value will be returned.

Example

#include <cassert>
#include <iostream>
#include <sl3/database.hpp>
int main()
{
using namespace sl3;
assert (val.isNull ()) ;
assert (val.get ("foo") == "foo") ; // get with default never throws
val = 2 ;
std::cout << val << std::endl; // access the integer property
try
{ // not possible since this variant holds an integer value
val.getText() ;
}
catch (const Error& e)
{
std::cout << e << std::endl;
}
assert (val.get ("foo") == "foo") ; // get with default never throws
val = "example";
// now it is possible to access the text property
std::cout << val.getText() << std::endl;
}

This example will print

2
sl3::TypeMisMatch:Int!=Text
example


sl3::Command


A sl3::Command is a compiled SQL statement which can contain parameters.
sl3::Database::prepare does create a command.
If a command has parameters the types are specified at creation.
A command can return data or not, depending on the SQL that is used.
A insert or update statement does not return data, while a select statement does.
A command has therefor 2 ways to run it.

Parameter types

In the A first example overview example all sl3::Command parameters have been of type sl3::Typ::Variant since nothing was specified.
But it can be ensured that certain types are used.

#include <cassert>
#include <iostream>
#include <sl3/database.hpp>
int main()
{
using namespace sl3;
// define a db
Database db(":memory:");
// run commands against the db
db.execute("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);");
// create a command with parameters
auto cmd = db.prepare("INSERT INTO tbl (f1, f2, f3) VALUES (?,?,?);",
//this will work,
cmd.execute (parameters (1, "one", 1.1));
// this will throw since "2" is a wrong type
try
{
cmd.execute (parameters ("2", "two", 2.2));
}
catch (const Error& e)
{
std::cout << e << std::endl;
}
}


The output of this program will be:

sl3::TypeMisMatch:Int=Text

It is not a problem to insert different types into a column, sqlite supports this and so does libsl3
But is might be unwanted and can therefore be turned off.


sl3::Dataset

A sl3::Dataset is a generic way to receive data from a sl3::Database.

It is return by a sl3::Command::select or sl3::Database::select

Create a sl3::Dataset


If there are different types in a column, a sl3::Dataset will automatically provide correct sl3::DbValue object.

#include <cassert>
#include <iostream>
#include <sl3/database.hpp>
int main()
{
using namespace sl3;
// define a db
Database db(":memory:");
// run commands against the db
db.execute("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);");
// create a command with parameters
auto cmd = db.prepare("INSERT INTO tbl (f1, f2, f3) VALUES (?,?,?);");
// no types so we use variants
//so this will work
cmd.execute (parameters (1, "one", 1.1));
//and this will also work
cmd.execute (parameters ("some text", "two", 2.1));
// access the data
Dataset ds = db.select("SELECT * FROM tbl;");
assert (ds.size () == 2); // 2 records
assert (ds[0].size() == 3); // 3 fields
// first row first field is a integer, as inserted
assert (ds[0][0].dbtype() == Type::Variant) ;
assert (ds[0][0].type() == Type::Int) ;
// second row first field is text, as inserted
assert (ds[1][0].dbtype() == Type::Variant) ;
assert (ds[1][0].type() == Type::Text) ;
// of course we can work wit the values
for(const auto& row :ds) {
for (const auto& field : row) {
std::cout << typeName (field.dbtype()) << "/"
<< typeName (field.type()) << ": " << field << ", ";
}
std::cout << std::endl;
}
}


running this program will print

Variant/Int: 1, Variant/Text: one, Variant/Real: 1.1,
Variant/Text: some text, Variant/Text: two, Variant/Real: 2.1,

Ensure types for a sl3::Dataset


Of course it might be unwanted to get different types for one column into a sl3::Dataset.
This can be ensured by passing the allowed sl3::Types to the sl3::Database::select call.
If the wanted types are not these which are in the table, an sl3::TypeMisMatch exception is thrown.

#include <iostream>
#include <sl3/database.hpp>
int main()
{
using namespace sl3;
// define a db
Database db(":memory:");
// run commands against the db
db.execute("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);");
// create a command with parameters
auto cmd = db.prepare("INSERT INTO tbl (f1, f2, f3) VALUES (?,?,?);");
// no types so we use variants
// insert Int, Text, Real
cmd.execute(parameters(1, "one", 1.1));
// insert Text, Text, Real
cmd.execute(parameters("some text", "two", 2.1));
// this will throw since types in column0 are different
try
{
Dataset ds = db.select ("SELECT * FROM tbl;",
}
catch (const Error& e)
{
std::cout << e << std::endl ;
}
}

this code will throw an exception, as expected and print

sl3::TypeMisMatch:Int not one of required types


RowCallback and Callback functions


A custom way to handle query results is to usea
a sl3::RowCallback or the sl3::Commad::Callback function.
They can be passed to the sl3::Database::execute and sl3::Commad::execute function.
The callback will be called with a sl3::Columns representing the current row for each row in the query result.
The callback returns if it wants proceed to be called or not.

sl3::Columns

In a callback the sl3::Columns class give access to the current row.
There are several methods to query the type, size and get the values.

Example

#include <cassert>
#include <iostream>
#include <sl3/database.hpp>
#include <sl3/columns.hpp>
int main()
{
using namespace sl3;
// define a db
Database db(":memory:");
// run commands against the db
db.execute("CREATE TABLE tbl(f1 INTEGER, f2 TEXT, f3 REAL);"
"INSERT INTO tbl (f1, f2, f3) VALUES (1, 'one', 1.1);"
"INSERT INTO tbl (f1, f2, f3) VALUES (2, 'two', 2.2)"
);
db.execute ("SELECT f1, f2 FROM tbl;", [](Columns cols)
{
assert (cols.count() == 2) ;
assert (cols.getType(0) == Type::Int) ;
assert (cols.getType(1) == Type::Text) ;
std::cout << std::to_string(cols.getInt(0))
<< "_" << cols.getText(1)
<< std::endl;
return true ;
});
}

Running this program, the output will be

1_one
2_two