libsl3 1.2.41002
A C++ interface for SQLite
Loading...
Searching...
No Matches
A C++ interface for SQLite

libsl3 enables efficient communication with SQLite3 databases using the natural approach, SQL.
The library does not attempt to function as an ORM (Object-Relational Mapping) and does not introduce C++ syntax resembling SQL for interacting with a SQLite database.
Reading from and writing to a database is accomplished through SQL and prepared commands, also known as stored procedures.
The library fully supports the SQLite duck typing concept, but it also offers a means to incorporate type checking when communicating with a database.

History

libsl3 stared as a C++98 project long time ago. Later, when C++11 was new, it became a modern C++11 interface to sqlite. Today, libsl3 it's still around. A C++ interface for SQLite.

Installation

Get you copy of the source code from the libsl3 GitHub repository.

The project has the following dependencies:

  • The minimum required C++ standard is C++17
  • Required: CMake for building the library
  • Optional: SQLite3, sqlite is included in the source code
  • Optional: doxygen for buliding the documentation
  • Optional: doctest for unit testing

If doxygen is not found it will not be possible to build the documentation.

doctest is fetched on demand, except if BUILD_TESTING is set to OFF.

Building the library

The library can be build with CMake. No surprises here.

On Linux, the configure and build process might appear as follows:

cmake -B build -S . -DCMAKE_BUILD_TYPE=Release -DCMAKE_INSTALL_PREFIX=/usr/local
cmake --build build --parallel

CMake options to control the build process:

  • USE_INTERNAL_SQLITE3:
    If set to ON, the internal sqlite3 source code will be used.
    If set to OFF, the system sqlite3 will be used.
    Default is ON.
  • BUILD_TESTING:
    If set to ON, test targets will be added .
    Default is ON.
  • BUILD_DOCUMENTATION:
    If set to ON, the doc target will be added .
    This requires doxygen to be found on the system.

For the internal sqlite distribution there are several options to configure sqlite3 available.

Advandages using the internal sqlite3

  • No external dependencie.
  • sqlite3 is used in the tested version
  • consuming libsl3 becomes easier

Programs consuming libsl3 do not need to link against sqlite3.

Disadvandages using the internal sqlite3

  • A bigger library
  • Slighlty longer build time for building libsl3 (not for programs consuming libsl3)

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.
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.

Another 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 (const auto& row : ds)
{
for (const auto& field : row)
{
std::cout << field << " ";
}
std::cout << std::endl;
}
}
size_type size() const
Container size.
Definition: container.hpp:168
represents a SQLite3 Database
Definition: database.hpp:43
A utility for processing the result queries.
Definition: dataset.hpp:48
Namespace of libSL3.
Definition: columns.hpp:18

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.

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 an 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 each of this function. With and without a default value as argument.
The version without a default value will throw a 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 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;
DbValue val (Type::Variant);
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 class models the duck typing sqlite uses. It supports int, real, text, blob and null values.
Definition: dbvalue.hpp:43
Exception base type.
Definition: error.hpp:63

This example will print

2
example
@ TypeMisMatch
type cast problem

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 (?,?,?);",
DbValues ({Type::Int, Type::Text, Type::Real}));
// 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;
}
}
A row of DbValues.
Definition: dbvalues.hpp:30

The output of this program will be:

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

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 those 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;",
{Type::Int, Type::Text, Type::Real});
}
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/columns.hpp>
#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);"
"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;
});
}
Class to access data of query results.
Definition: columns.hpp:38
int count() const
Number of columns in the statement.
std::string getText(int idx) const
Get the value of a column.
Type getType(int idx) const
Get the sqlite type for a column.
int getInt(int idx) const
Get the value of a column.

Running this program, the output will be

1_one
2_two