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

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

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

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

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

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

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;",
                               { 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 <br>

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

Table of Contents

Overview

A first example

sl3::Database

Types in libsl3

sl3::DbValue

Using sl3::DbValues

Example

sl3::Command <br>

Parameter types

sl3::Dataset

Create a sl3::Dataset <br>

Ensure types for a sl3::Dataset <br>

RowCallback and Callback functions <br>

sl3::Columns

Example