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;
    }
}

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.

sl3::Type::Int stands for a int64_t.
sl3::Type::Real stands for a doube.
sl3::Type::Text stands for a std::string.
sl3::Type::Blob stands for a sl3::Blob, a char vector

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.

sl3::Type::Variant , any value type.

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.

sl3::DbValue::dbtype: The type rule
This property is assigned at creation and can not be change
If the type is sl3::Type::Variant the instance can hold any type, otherwise only the specified type is allowed, one of:
sl3::Type::Int, sl3::Type::Real, sl3::Type::Text, sl3::Type::Blob
sl3::DbValue::getStorageType: The current value type
This property depends on the actual value
If the type is Type::Variant values of any type can be used, otherwise it has to be the type specified.
If a sl3::DbValue::isNull the storage type is also sl3::Type::Null.

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::set or the assignment operator.
If an invalid type is set a sl3::ErrTypeMisMatch will be thrown.

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.

sl3::DbValue::get

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

sl3::Command::execute, no return data
sl3::Command::select , does return data.

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;
    }
}

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

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;
  });
}

Running this program, the output will be

1_one

2_two

Table of Contents

History

Installation

Building the library

Overview

A first example

sl3::Database

Types in libsl3

sl3::DbValue

Using sl3::DbValues

Example

sl3::Command

Parameter types

sl3::Dataset

Create a sl3::Dataset

Ensure types for a sl3::Dataset

RowCallback and Callback functions

sl3::Columns

Example