sql_wrapper

sql_wrapper
Login

Sqlite wrapper

Features

How to install and compile

How to use

For a complete description, read examples in main_example.cpp .

The following examples works with an int and a string, but you can use any number of argument among the types : int, sqlite3_int64, std::string and Null. An experimental support for date is available, see the Date section.

The examples assume that the database contains the following stuff

create table test(
  i integer NOT NULL, 
  s varchar, 
  primary key(i)
);
insert into test(i,s) values (1,"one");

Connect to a data base

sqlwrapper::DbConnectInfo<sqlwrapper::Sqlite_tag> con(const std::string)

Create a con object that store the parameters required to connect to an sqlite database.
sqlwrapper::DbManager<sqlwrapper::Sqlite_tag> db= sqlwrapper::make_DbManager(con); Create a db object that store the connection to the database. Con is the object created by the previous line.

The database is configured to enable foreign key support.

For now, this library is single thread only.

#include <sqlwrapper/sqlite.hpp>
void example(){
	sqlwrapper::DbConnectInfo<sqlwrapper::Sqlite_tag>   con("test.sqlite3");
	sqlwrapper::DbManager<sqlwrapper::Sqlite_tag> db = sqlwrapper::make_DbManager(con);
}

Prepare a query, bind arguments

A prepared query can be used in all Db_sqlite functions instead of a std::string. Arguments are bound to the query when bind is called, or when additional arguments are passed to db methods. Binding is reseted when a query is executed. You can mix different way of binding arguments.
#include <sqlwrapper/sqlite.hpp>
void example(sqlwrapper::DbManager<sqlwrapper::Sqlite_tag> &db){
  sqlwrapper::Db_sqlite::Query q = db.prepare("INSERT into test(i,s) values (?,?)");
  //bind 2 then "two", then execute
  q.bind(2);
  db.execute(q,"two");
  //after execute, nothing is bounded

  //bind 3,"three", then execute
  db.execute(q,3,"three");
}

Execute a query, insert stuff

Execute a query

execute(const std::string &s) execute s. For this function only, s can contain multiple statments
execute(const std::string &s, args...) Prepare s, binds args, execute s. s must contains a single statment
execute(Query &q, args...) Binds args, execute q

Insert a single line and get its Rowid

insertRow(const std::string &s, args...) Prepare s, binds args, execute s. s must contains a single statment
insertRow(Query &q, args...) Idem, with a prepared query
insertTuple(const std::string &s, const tuple<...> &t) Prepare s, binds t to s, execute s. s must contains a single statment
insertTuple(Query &q, const tuple<...> &t) Idem with a prepared query

Insert values from a container

insertColumn(const std::string &s, const Container &c) Prepare s into q. Then, for each value v in c, bind v, execute q
insertColumn(Query &q, const Container &c) idem, with a preprared query

Insert values from a container of tuples

insertTable(const std::string &s, const Container<tuple<...> > &t) Prepare s into q. Then, for each tuple v in c, bind each element of the tuple , execute q
insertTable(Query &q, const Container<tuple<...> > &t) idem, with a preprared query
#include <sqlwrapper/sqlite.hpp>
void example(sqlwrapper::DbManager<sqlwrapper::Sqlite_tag> &db){
    //execute
    db.execute("insert into test(i,s) values (?,?)", 4, "four");

    //insertRow
    sqlwrapper::Db_sqlite::Rowid Rowid5 = db.insertRow  ("insert into test values(?,?)", 5,"five");

    //insertTuple
    std::tuple<int,std::string> t(6,"six");
    sqlwrapper::Db_sqlite::Rowid Rowid6 = db.insertTuple  ("insert into test values(?,?)", t);

    //insertColumn
    std::vector<int> insert_vect; 
    insert_vect.emplace_back(100); 
    insert_vect.emplace_back(200);
    db.insertColumn  ("insert into test(i) values(?)",insert_vect );

    //insertTable
    std::vector<std::tuple<int,std::string> > v; 
    v.emplace_back(7,"seven"); v.emplace_back(8,"eight");
    db.insertTable("insert into test values(?,?)",v );
}

Read a file

read(const std::string &path) Load the file located at path into memory, then execute it as a query
read(std::istream &in) Load the full istream in memory, then execute it as a query

The current implementation loads the full file in memory (in a std::string), and then calls execute. That's probably not optimal for big files, so be careful with it.

#include <sqlwrapper/sqlite.hpp>
void example(sqlwrapper::DbManager<sqlwrapper::Sqlite_tag> &db){
  //read a file
  db.read("my_path/my_file.sql");
  
  //read a std::istream
  std::ifstream in("my_path/my_file2.sql");
  db.read(in);
}

Get an (optional) single line

getRow(const std::string &s, &args...)

Read a single row, and set args to read values. If not exactly one row is read, throw
getRow(Query &q, const std::string &s, &args...) Idem, with a query
getRow_optional(const std::string &s, &args...) Read a single row, If 0 row is read, do nothing. If one row is read set args to read values. If more than one row is read : throw
getRow_optional(Query &q, const std::string &s, &args...) Idem, with a query
getTuple(const std::string &s, const std::string sql, std::tuple<args...> &) Idem as getRow, but set the tuple elements
getTuple(Query &q, const std::string &s, std::tuple<args...> &) Idem as getRow, but set the tuple elements

If the returned line may contain NULL values, don't forget to pass sqlite::Optional<T> arguments.

#include <sqlwrapper/sqlite.hpp>
void example(sqlwrapper::DbManager<sqlwrapper::Sqlite_tag> &db){
    //getRow
    int get_i; std::string get_s;
    db.getRow ("select i,s from test where i=1",get_i,  get_s); 
    //get_i=1, get_s="one"

    //getTuple
    auto get_tuple = db.getTuple<int,std::string>("select i,s from test where i=1"); 
    //get_tuple contains {1,"one"}
}

Get lines one by one

getRow_next(Query &q, &args...) try to get the next line. If exists assign it to args and return true, else return false.

NoteDon't forget to reset your query with q.reset_binding(), if you plan to reuse the query.

NoteFor performaces reasons, the number of column is not checked, if you pass the wrong number of arguments, you will get an argument binding error indicating that your binding null to your extra argument.

NoteIf you plan to do parallel work, a while loop that calls getRow_next is the perfect place to spawn threads or to queue jobs.

#include <sqlwrapper/sqlite.hpp>
void example(sqlwrapper::DbManager<sqlwrapper::Sqlite_tag> &db){
sqlwrapper::DbConnectInfo<sqlwrapper::Sqlite_tag>   con("test.sqlite3");
	auto q = db.prepare("select i,s from test");
	int i;
	std::string s;
	while(db.getRow_next(q,i,s)){
		std::cout << i << " "<<s<<std::endl;
	}
	q.reset_binding(); //don't forget if you plan to reuse q
}

Get a column in a container of values

getColumn(const std::string &s, Container<T> & add_here) Read a single column, append the read values to the end of the container
getColumn(Query &q, Container<T> & add_here) Idem, with a query
#include <sqlwrapper/sqlite.hpp>
#include <unicont/vector.hpp>

void example(sqlwrapper::DbManager<sqlwrapper::Sqlite_tag> &db){
    //getColumn
    auto get_vect = db.getColumn<std::vector,int>("select i from test");
    //get_vect is a std::vector<int>
}

Get a table in a container of tuples

getTable(const std::string &s, Container<std::tuple<args...> > & add_here) Read a table, append the read lines as tuples at the end of the container
getTable(Query &q, const Container<std::tuple<args...>&) Idem, with a query

Be careful when using getComumn and getTable on large tables : this functions fetch all requested data, so you can have memory issues. If it's a problem consider writing an algorithm that works one (few) lines at a time by using getApply instead.

#include <sqlwrapper/sqlite.hpp>
void example(sqlwrapper::DbManager<sqlwrapper::Sqlite_tag> &db){
    //getTable
    auto get_table= db.getTable<std::deque,int,std::string> ("select * from test");
    //get_table is a std::deque<std::tuple<int,std::string> >
}

Transactions and save points

auto tr = db.transaction()

Return a DbTransaction object. By default, the transaction is rollbacked at tr destruction
auto tr = db.transaction_autocommit() Idem, by default, the transaction is commited at tr destruction
auto sp = db.savepoint() Create a savepoint. This savepoint is auto named sxxx, with xxx a number

Transactions and savepoints have a rollback() and a commit() function. The default behaviour is to rollback at destruction, except if commit was called.

Transactions_autocommit simply auto commit at destruction.

Apply a function to each line

void getApply(const std::string &s, function(...)->void fn)

Fetches data line by line and applies a the fn function to each line. Function arguments must match the ones of the query.
bool getApply(const std::string &s, function(...)->bool fn) Idem, but fetching stops when function returns false. If fetching stopped, getApply returns false, else it returns true.

WARNING The current implementation doesn't handdle requests in the applied function, because sqlite uses one internal state per data base connection to retrive data (and not one state per request). In order to avoid stupid results, this internal sqlite stuff is protected by a mutex, therefore requests within requests will always leads to a deadlock. You can use two distinct sqlwrapper::Db_sqlite objects (one in the applied function, one outside it) as a workaround.

WARNING If you do have errors about references on temporary : pass your arguments by value to the applied function, not by reference.

#include <sqlwrapper/sqlite.hpp>
#include<iostream>
void example(sqlwrapper::Db_sqlite &db){

  //write a function (free function, lambda or functor)
  auto fn=[](std::string s, double d)->bool{std::cout << s <<" " << d <<"\n"; return true;}

  //getApply
  db.getApply("select s,i from test",fn);

  //DO NOT WRITE THIS : request in applied function --> deadlock
  auto fn_bug=[&](int i){db.execute("SELECT * from test where i=?",i);}

  db.getApply("select i from test",fn);
}

Parallel version

the getApply_parallel function works the same way. It builds a thread manager that fetches data from sqlite in a main thread, and that distribute it to parallel workers. Be carefull, there is an important cost for parallelizing the code, therefore use it only whe processing data is slower that getting it from the database.

Note that this implementation packs tuples into pages send pages in random order to the differents threads, therefore consider the order in which tuples are processes as random.

This is certainly not the cleverest implementation possible, so if you do know how to improve it, and how sqlite behaves for parallel queries, please say so.

Handling null/missing values

Two types are used to represent Null values : the type sqlwrapper::Db_sqlite::Null is used to represent a value that's always null; the type sqlwrapper::Db_sqlite::Optional<T> is used to represent a value of type T that is sometimes null. This type is a pair of <bool,T>, with the first element equals to false when T is null, and to true when not null. The second value may contains uninitialized/stupid values when the first one is false, so don't use it in this context.
#include <sqlwrapper/sqlite.hpp>
void example(sqlwrapper::Db_sqlite &db){
    //insert an always null value
    db.insertRow("insert into test values(?,?)",20,sqlwrapper::Db_sqlite::Null() );

    //insert a value that MAY be null : use Optional
    typedef sqlwrapper::Db_sqlite::Optional<std::string> Optional_str;
    Optional_str may_be_null;
    may_be_null.second="not_used_when_null";
    may_be_null.first = false; //false =  null, true = not_null
    db.insertRow("insert into test values(?,?)",101,may_be_null); //insert 101,Null

    //get a value that MAY be null : use Optional
    typedef sqlwrapper::Db_sqlite::Optional<std::string> Optional_str;
    std::deque< std::tuple<int,  Optional_str  > > cont2;
    db.getTable ("select * from test",cont2);
}

Dates

The C++ native time and date support trough std::chrono was dropped as there is no easy way to parse a time from a string and as gcc poorly implemented date support. I've chosen to support times trough boost. Boost time API is like all boost API horrible and badly documented but at least there is a way to parse strings into dates and vice versa.

Now, the library supports dates by using time_tools::Boost_date, a wrapper arround boost stuff, and by including sqlwrapper/sqlite_Boost_date.hpp. Times can be used as any other type.

Doc about boost time formats is here.

Time format support can be extended by specializing the template interface described in time_tools/time_tools.hpp. Be carefull if you do so, as this interface is not stable yet.

When inserting date in the database use a format that preserve date order as yyyy-mm-dd hh:mm:ss or number of seconds since eopch. In such format, using <, >, <=, >= and == in sql queries preserve the original meaning of this operators. Also be careful to use a monotonous time system : for example never use daylight saving time in the database, as once a year 02:00:00, is both BEFORE and AFTER 03:00:00). If you use seconds, be careful about leap seconds. Inserting leap seconds will slightly mess up the duration computation (OK most of the time), but going back in time may reverse the timeline which is a source of nasty and hard to debug errors). Morever, you'd better not use locales, as it's the best way to add confusion in users mind and to make queries complicated (Is 01/02 the first of February, or the second of January?).

If you do know a convenient time library in C++, and know how it works, please tell me. I'll be glad to use it instead of the boost one. An other very useful option to help is to write accurate and clear boost documentation. If you do so, please don't follow the Chuck Norris guideline.

#include <sqlwrapper/sqlite.hpp>
#include <sqlwrapper/sqlite_Boost_date.hpp>

void example(sqlwrapper::Db_sqlite &db){
  time_tools::Boost_date t;
  time_tools::from_string("21-01-2014 18:15:17","%d-%m-%Y %H:%M:%S",t); //string, format, write_here
  db.execute("INSERT INTO test VALUES(null,?)",t); //insert 2014-01-21 18:15:17 (iso format)
  std::cout << time_tools::to_string(t,"%d-%m-%Y %H:%M:%S"); //write time in your favorite format
}

Column informations

WARNING SQlite doesn't support column informations when column in queries doesn't refer to existing column names. The following example will throw a DbError_query.

//Don't do that as cast(i as varchar) doesn't refer to an existing column
auto c2 = db.getColumn_info<std::vector>("select cast(i as varchar) as i from test where ?",0);

Column informations are db specific, they are defined as a specialization of Column_info<Db_tag>. The getColumn_info<Cont> function returns a container (here define with Cont) of Column_info<Db_tag>.

auto infos = db.getColumn_info<std::vector>(Query_t &q, Args ... bind_me )

bind bind_me to q, then get the column informations.
auto infos db.getColumn_info<std::vector>(const std::string &sql, Args ... bind_me ) prepare sql into q, binds bind_me to q, then get the column informations.
#include <sqlwrapper/sqlite.hpp>

void example(sqlwrapper::make_DbManager<sqlwrapper::Sqlite_tag> &db){

        //--- example table ---
	db.execute("drop table if exists test");
	db.execute("create table test(i integer, s varchar)");
	db.execute("insert into test values(1,'one'),(2,'two')");

	//--- get column info ---
	auto c = db.getColumn_info<std::vector>("select * from test where ?",0);
	
        //--- get info details for sqlite. Details are specific of the underlying database ---
	for(const auto &i : c){
		std::cout << i.database_name << "."
                          << i.table_name    << "."
                          << i.column_name   << "\n";
	}

       //---print column info (idem) ---
       for(const auto &i : c){std::cout << i << "\n";}
}

Save csv

WARNING This function doesn't save the header. Details : as SQLite poorly support getColumn_info, we cannot deduce the table header from any query. This ma leads to run_time bugs. This behavior may change in a future release by adding an optional write_header parameter to save_csv.

WARNING Always write queries that cast data as varchar.

save_csv(Query_t &q,std::ostream &out, const char sep='\t', const char endl='\n')

save_csv(const std::string &sql, std::ostream &out, const char sep='\t', const char endl='\n')
#include <sqlwrapper/sqlite.hpp>
#include <iostream>

void example(sqlwrapper::make_DbManager<sqlwrapper::Sqlite_tag> &db){
  //---example table ---
  db.execute("drop table if exists test");
  db.execute("create table test(i integer, s varchar)");
  db.execute("insert into test values(1,'one'),(2,'two')");

  //as s is varchar, this is OK
  db.save_csv("select s from test", std::cout); 
  std::cout << "\n";
  
  //as i is integer : don't forget to cast it to varchar in your sql
  db.save_csv("select s, cast(i as varchar) from test", std::cout); 
  std::cout << "\n";
}

Extend the library

The library can be extended to handle custom containers and type trough template specialization.

Use a custom container

To use a custom container, you must first specialize uncont::reserve_t. Your implementation must reserve memory for i objects. If your container does not have a reserve function, please provide an implementation that does nothing.
namespace unicont{
  template<typename T>
  struct reserve_t<your_container,T>{
    static void run(your_container<T> &target, size_t i){/*your impl.*/}
  };
}

Then you must specialize uncont::move_in_t, that moves t into the container.

namespace unicont{
  template<typename T>
  struct move_in_t<your_container,T>{
    static void run(your_container<T> & target, T&&t){/*your impl.*/}
  };
}

For a working example have a look at unicont/deque.hpp

The other stuff of unicont doesn't have to be implemented to work with sqlwrapper, you an specialize it anyway if you think that the unicont interface is usefull for more general purposes.

Use custom data types

Custom data types can be used to extract them from the database with DbExtract_t, or to bind them to queries with DbBind_t. Note that the sqlite implementation automatically handles optional values for custom types. This is the place to bury native calls to your native function of your database system that bind or extract data.

First define your custom type

struct MyType{/*blabla*/};

Define DbBind_t to bind it to queries. Db_tag is the database tag that identifies the kind of database used for data extraction. For example Db_tag = Sqlite_tag for sqlite databases. An example is provided in sqlwrapper/sqlite_impl/Query.tpp

namespace sqlwrapper{
  template<>
  struct DbBind_t<Db_tag , MyType >{
    static void run(Query<Db_tag>&q, size_t I, const MyType &m){
      //bind m to the column number I of q
      //this code probably uses a native function of
      //your database system
    }
  };
}

Define DbExtract_t to get data out of the database, and to store it into m. An example is provided in sqlwrapper/sqlite_impl/Query.tpp

namespace sqlwrapper{
  template<>
  struct DbExtract_t<Db_tag , MyType >{
    static void run(Query<Db_tag>&q, size_t I, MyType &m){
      //m = something read in the column number I of the query q.
      //this code probably uses a native function of
      //your database system
    }
  };
}

Development

Articles and background

License