SOCI

Sochi  (IPA: [ˈsotɕɪ])

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

(pre-)HISTORY

Simple Oracle Call Interface

HISTORY

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 
3. Prepare statement
4. Execute statement
   
5. 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?

Why that is a problem?

express yourself

The SYNTAX nirvana

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

C++ natural syntax

syntax-first development

XP and TDD inspired

holy grail

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

or, monty python?

C++ techniques

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

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

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

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

static types for  INTO AND USE

• 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){   s = x.to_string();// convert   i = i_ok; }};

object-relational mapping

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

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){   s = x.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