introduction to a simple 
C++ database access library

SOCI



London C++ Meetup

May 2013

Mateusz Loskot | @mloskot

Slides licensed under the Creative Commons Attribution License

Mateusz.Loskot.NET

C++ hacker, geospatial developer


Open Source developer

Boost.Geometry, GDAL, libLAS, PostGIS, SOCI, ...


Charter Member @OSGeo

Senior Programmer @Cadcorp

SOCI


It's a library 

Written in standard C++ (C++03)

Database access interface

Boost Software License (BSL 1.0)

SOCI (IPA: [ˈsokɪ]) 

Sochi  (IPA: [ˈsotɕɪ])

Photo by Mikhail Mordasov | Copyright (C) Mikhail Mordasov (AFP - Getty Images)

(pre-)HISTORY

Originally created by Maciej Sobczak in CERN
as an abstraction for Oracle

Simple Oracle Call Interface

HISTORY

Simple Open (Database) Call Interface

  • 2006-01: 2.0.0 PostgreSQL backend
  • 2006-03: 2.0.1 MySQL and SQLite backends
  • (@mloskot joined the project)
  • 2006-12: 2.2.0 ODBC and Firebird backends
  • 2008-07: 3.0.0 New naming convention
  • 2011-10: 3.1.0 Ada bindings
  • 2013-03: 3.2.0 DB2 backend, moved to GitHub
  • 2013-04: 3.2.1 Bugfix release (current)
  • ????-??:       4.0.0 Lots of plans!

Canonical Machinery

class statement {public:
statement(session& s);
void exchange(/*...*/); void define_and_bind();
void prepare(string q);
bool execute(); bool fetch();};
class session {public: // open connection session(string c);
void begin(); void commit();
void rollback();
};

CANONICAL MACHINERY STEP-BY-STEP

  1. Connect
  2. Collect exchange variables
    • Bind* local objects to SQL   placeholders
    • Define* local objects for query output 
    • Prepare statement
    • Execute statement
    • Fetch data from result set


    bind define  terminology from   Oracle Call Interface  heritage; SOCI documentation use  binding for directions.

    CANONICAL MACHINERY IN ACTION

     std::string name; // define variable int id = 101;     // bind variable
    session db("dbname=test"); statement st(db); st.exchange(id); // bind input
    st.exchange(name); // define output
    st.define_and_bind(); st.prepare("SELECT name FROM person " "WHERE id = ?"); st.execute(); while (st.fetch()) { std::cout << name << std::endl; }

    What's the 'canonical' problem?

    Lots of code with

    excplicit
    low-level 
    for technical purpose only
    conceptually disconnected
    and irrelevant to the problem at hand

    function calls

    Why that is a problem?


    Basic facade,
    "back-end to front-end" wrapper
    with low-level concepts revealed,
    low-level conventions required
    and distracting elements displayed

    Has real value been added?

    express yourself


    I'm expressin' with my full capabilities
    Now I'm livin' in correctional facilities
    ...I get straight, meditate, like a Buddhist
    ...But my technique is very necessary

    N.W.A - Express Youself, 1988

    The SYNTAX nirvana




     int v = 7; std::cout << v;

    C++ natural syntax


    Idiomatic
    Minimal
    Simple to write
    Easy to learn and understand
    Operation complexity kept out of sight


    syntax-first development


    XP and TDD inspired


    setting up syntax

    head-scratching
    pen-biting

    coding

    holy grail


     person p; session sql(...); sql << "SELECT name,age FROM person",        into(p);

    or, monty python?

    C++ techniques


    operator overloading

    templates

    lifetime rules of temporary objects

    what's the SOCI magic?



    Illusion of embedding SQL in C++

    anatomy of database session


    connect on construction

    using namespace soci;
    session sql([BACKEND], [CONNECTION STRING]);

    connect in two steps

    session sql;sql.open([BACKEND], [CONNECTION STRING]); 

    SESSION BACKEND

    Database-specific connection session


    string connstr("dbname=test user=skippy");
    // uses factory object providedsession sql(postgresql, connstr);
    // loads backend by name at run-timesession sql("postgresql", connstr);
    session sql("postgresql://dbname=test");

    advanced backend parameters


    connection_parameters p("odbc","DSN=test");
    // Force SQL_DRIVER_NOPROMPT p.set_option( odbc_option_driver_complete,"0"); session sql(p);

    SIMPLE SQL statements

    Executed only once, immediately

    session sql;sql << "DROP TABLE employee";
    string table_name("employee");sql << "DROP TABLE " << table_name;
    int id = 7;sql << "DELETE FROM "    << table_name    << " WHERE id="    << id;

    anatomy of the staTement SYNTAX

    sql << "DROP TABLE " << table_name;
    
    sql.once << "DROP TABLE " << table_name;
    
    class session {public: once_type once;
    template <typename T> once_temp_type operator<<(T t) { return once << t; }};

    ANATOMY OF the ONCE

    sql.once << "DROP TABLE " << table_name;
    
    class once_type {
     session* session_;
    public:
    
     template <typename T>
     once_temp_type operator<<(T t) {   once_temp_type once_temp(*session_);
       return once_temp << t; }
    };

    anatomy of the temporary

    sql.once << "DROP TABLE " ? << table_name ?;
    class once_temp_type { ref_counted_statement* st_;public:
    template <typename T> once_temp_type& operator<<(T t) { // push into buffer, stringstream
    st_->accumulate(t); return *this; } ~once_temp_type() { st_->dec_ref(); }
    };

    death of the TEMPORARY

    sql.once << "DROP TABLE " << table_name; ?
    class ref_counted_statement { statement st_;
    public: void def_ref() {
    if (--counter == 0) final_action(); } void final_action() { st_.prepare(get_query());
    st_.define_and_bind(); st_.execute(); }};

    statement execution summary

    1. sql << "..." << ... ;
    2. sql. once << "..." << ... ;
    3. start up the temporary object
    4. pass the temporary through expression
      • collecting all streamable elements
    5. reach end of the expression
    6. destroy the temporary object          
      • prepare statement
      • execute statement
      • exchange data
      • clean up

    C++11 caveats

    Destructors default to 

    noexcept(true)

    N3166 , N3204

    SOCI must address this issue with:

    noexcept(false)


    Thanks to Roger Orr and Anthony Williams
    for reporting this issue in SOCI.

    ELEMENTS of data exchange


    • Bind local output data (aka define)
    • Bind local input data (aka bind)
    • Bind objects of static types
    • Bind objects in dynamic result set
    • Bind objects of user-defined types
    • Object-Relational Mapping (ORM)
    • Handle  NULL  and data conditions

    SQL with DATA exchange

    Executed only  once , immediately
    string name; // output variable
    sql << "SELECT name FROM person",into(name);
    int id = 7;  // input variable
    sql << "SELECT name FROM person WHERE id=:i", into(name), use(id);
    indicator ind;
    sql << "SELECT name FROM person", into(name, ind);if (ind == soci::i_null) { ... }

    ANATOMY of BINDING

    class once_temp_type { ref_counted_statement* st_;public: once_temp_type& operator,(into_type_ptr i){   // push onto statement intos
    st_->exchange(i); } once_temp_type& operator,(use_type_ptr u){ // push onto statement uses st_->exchange(u); }};

    anatomy of into

    sql << "SELECT name FROM p", into(name);
    template <typename T>
    into_type_ptr into(T& t) { return do_into(t, typename    exchange_traits<T>::type_family());}
    template <typename T>
    into_type_ptr do_into(T& t,basic_type_tag) { return into_type_ptr(new into_type<T>(t));}

    anatomy of INTO_TYPE

    class into_type {  // statement backend define_by_pos  void define(statement* s, int& position);
    // convert data to exchange type void post_fetch(); void* data_; indicator* ind_; exchange_type type_;};

    machinery of into

    template <typename T>
    into_type_ptr do_into(T& t,basic_type_tag);
    template <typename T>
    into_type_ptr do_into(T& t,indicator& ind,                           basic_type_tag);
    template <typename T>
    into_type_ptr do_into(T& t,user_type_tag);
    template <typename T>
    into_type_ptr do_into(T& t,indicator& ind,                           user_type_tag);
    Plus, overloads for vector<T> and vector<indicator>

    anatomy of use

    sql<<"SELECT n FROM p WHERE id=:i", use(id);
    template <typename T>
    use_type_ptr use(T& t, string name) { return do_use(t, name, typename    exchange_traits<T>::type_family());}
    template <typename T>
    use_type_ptr do_use(T& t, string name, basic_type_tag) { return use_type_ptr(  new use_type<T>(t, name));}

    anatomy of use_type

    class use_type {  // statement backend bind_by_pos  void bind(statement* s, int& position);
    // convert data to exchange type void pre_use(); void* data_; indicator* ind_; exchange_type type_; string name_;
    };

    machinery of use

    template <typename T>
    use_type_ptr do_use(T& t, string name, basic_type_tag);
    template <typename T>
    use_type_ptr do_use(T& t,indicator& ind, string name, basic_type_tag);
    template <typename T>
    use_type_ptr do_use(T& t,string name, user_type_tag);
    template <typename T>
    use_type_ptr do_use(T& t,indicator& ind, string name, user_type_tag);
    Plus, overloads for vector<T> and vector<indicator>

    static types for  INTO AND USE

    Predefined exchange_traits<T> for
    basic C++ types:

    • char, char*, char[], std::string
    • (unsigned) short, int, long long
    • double
    • std::tm
    • std::vector<T> for T as above

    Binding dynamic result set

    row r;sql << "SELECT name,age FROM p", into(r);
    for (size_t i=0; i<r.size(); ++i)
    {
     column_properties& c = r.get_properties();
     if (c.get_data_type() == dt_string)
       string name = r.get<string>(i);
     else if (c.get_data_type() == dt_integer)
       int age = r.get<int>(i);
    }
    string name; int age;
    r >> name >> age;

    AVAILABLE (LIMITED) dt_* mappingS

    SOCI Base Types

    • dt_string
    • dt_integer
    • dt_long_long
    • dt_unsigned_long_long
    • dt_double
    • dt_date

    user-defined types support

    template<> struct type_conversion<MyX> {
    typedef string base_type;
    void from_base(string s,indicator i,MyX& x){
    if (i == i_null) { ... } x.from_string(s); // convert}
    void to_base(MyX x,string& s,indicator& i){ sx.to_string();// convert i = i_ok; }};

    object-relational mapping

    database table

    CREATE TABLE person (  name VARCHAR(60),  age INT)

    c++ type

    struct Person {   string name;  int age;};

    MAP soci::values via conversion

    template<> struct type_conversion<Person>{typedef values base_type;
    from_base(values v,indicator i,Person& p){
    p.name = v.get<string>(); p.age = v.get<int>();}to_base(Person p,values& v,indicator& i){
    v.set("name", p.name); v.set("age", p.age);
    i = i_ok;}};
    template<> struct type_conversion<MyX> {
    typedef string base_type;
    void from_base(string s,indicator i,MyX& x){
    if (i == i_null) { ... } x.from_string(s); // convert}
    void to_base(MyX x,string& s,indicator& i){ sx.to_string();// convert i = i_ok; }};

    THERE IS MORE

    Prepared statements and repeated execution

    int i;statement st = (sql.prepare  << "INSERT INTO n(v) VALUES(:v)",  use(i));
    for (i = 0 i < 100; ++i){ st.execute(true);}

    soci::rowset for SELECT QUERIES

    statement wrapper with STL-like iterator interface

    rowset<row> rs = (sql.prepare
    << "SELECT name,age FROM person");
    for (auto it=rs.begin(); it!=rs.end(); ++it){
    // dynamic data extraction from each row cout << "name: " << it->get<string>(0) << ",age: " << it->get<int>(1) << endl;}

    THERE is EVEN more

    Bulk operations
    Transactions interface
    Stored procedures support
    Binary large objects (BLOB) API 
    Query string transformation


    Integration with Boost    libraries: optional, tuple,  fusion::vector, gregorian::date

    Thank you!


    SOCI project website at

    SOCI git repository, bug tracker and wiki at

    SOCI talk slides at http://slid.es/mloskot/soci/

    Slides licensed under the Creative Commons Attribution License

    SOCI

    By Mateusz Loskot

    SOCI

    Introduction to SOCI - the C++ database access library

    • 20,410