Describes a Mysql access library for C + + programs Soci_mysql

All along, I have been looking for a more humane way to access the database (not to say the default way is bad, but sometimes it is not convenient in the modular design).
Later fortunately in PHP to find CodeIgniter Activereord, detailed reference to this article: pull away from the CodeIgniter database access Class!
However, C + + has always used the most primitive way, yesterday while the project to use the opportunity to search the internet for a long time, finally let me find two sets of C + + database access framework:

Soci
Litesql

I've got two sets of code. Look, Litesql implemented a complete set of code automatically generated, powerful, but also very heavy, soci relatively lightweight, but also achieve the data structure to the database table mapping. I still like the light weight of things, so finally chose the soci. After these two days of trial, it feels very good.

The official document is also very detailed, so here is a simple explanation with the unit test code I wrote:
First create the Library table:

Create database Soci;
CREATE TABLE ' tb_test ' (
 ' id ' int (one) not NULL auto_increment,
 ' name ' varchar () default ',
 ' sex ' int (one) de Fault 0,
 PRIMARY key (' id '),
 UNIQUE key ' name ' (' name ')
;
 
Create database Soci;
CREATE TABLE ' tb_test ' (
 ' id ' int (one) not NULL auto_increment,
 ' name ' varchar () default ',
 ' sex ' int (11 Default 0,
 PRIMARY key (' id '),
 UNIQUE key ' name ' (' name ')
;

1. Simple Select single record

TEST (Soci,select_one)
{
  Try
  {session
    SQL (MySQL, "Host=localhost db=soci User=dantezhu");
    Indicator IND;

    String name = "Dandan";
    int sex;
    SQL << "Select sex from tb_test where name =: Name", to
      (Sex, Ind), use (name);

    ASSERT_EQ (Ind, I_OK) << name;
  catch (Exception const &e)
  {
    FAIL () <<e.what ();
  }
}
 
TEST (Soci,select_one)
{
  Try
  {session
    SQL (MySQL, "Host=localhost db=soci User=dantezhu");
    Indicator IND;
 
    String name = "Dandan";
    int sex;
    SQL << "Select sex from tb_test where name =: Name", to
      (Sex, Ind), use (name);
 
    ASSERT_EQ (Ind, I_OK) << name;
  catch (Exception const &e)
  {
    FAIL () <<e.what ();
  }
}

The result of the Select, if successful, the IND will be I_OK, the same value sex be assigned, and if the failure is the opposite

2. Simple select multiple records

TEST (SOCI,SELECT_MULTI2) {try {session SQL (MySQL, "Db=soci User=dantezhu");

    Indicator IND;
    int count;
    SQL << "SELECT COUNT (*) from Tb_test", Into (count, Ind);

    ASSERT_EQ (Ind, I_OK) << count;
      if (count = = 0) {succeed ();
    Return
    int sex = 1;
    Vector<string> Vec_name (count);
    Vector<int> Vec_sex (count);
  SQL << "Select Name,sex from tb_test where sex =: Sex", in (Vec_name), into (Vec_sex), use (sex);
  catch (Exception const &e) {FAIL () <<e.what ();
    } TEST (Soci,select_multi2) {try {session SQL (MySQL, "Db=soci User=dantezhu");
 
    Indicator IND;
    int count;
    SQL << "SELECT COUNT (*) from Tb_test", Into (count, Ind);
 
    ASSERT_EQ (Ind, I_OK) << count;
      if (count = = 0) {succeed ();
    Return
    int sex = 1;
    Vector<string> Vec_name (count);
    Vector<int> Vec_sex (count); SQL << "Select" Name,sex from tb_test where sex =: Sex ", in (Vec_name), into (Vec_sex), by use (sex);
  catch (Exception const &e) {FAIL () <<e.what ();

 }
}

The only difference with the Select single record is that the parameter of into () is a vector. In fact, it's not a good choice to use multiple vectors, which will be followed by a data structure based approach.

3. Simple Insert

TEST (soci,insert_exist)
{
  Try
  {session
    SQL (MySQL, "Db=soci User=dantezhu");

    String name = "Dandan";
    int sex = 1;

    SQL << INSERT into tb_test (name, Sex) VALUES (: Name,: Sex), use
      (name), use (sex);
  }
  catch (Exception const &e)
  {
    succeed () <<e.what ();
  }
}
 
TEST (soci,insert_exist)
{
  Try
  {session
    SQL (MySQL, "Db=soci User=dantezhu");
 
    String name = "Dandan";
    int sex = 1;
 
    SQL << INSERT into tb_test (name, Sex) VALUES (: Name,: Sex), use
      (name), use (sex);
  }
  catch (Exception const &e)
  {
    succeed () <<e.what ();
  }
}

Insert,update,delete all have two of the same problems:
A) affect_rows (number of lines of operation) no way to return
b The ID of the operation is not known, especially if the primary key of the insert is increased, it is not possible to know what the value of the inserted primary key is.

Both update and delete are similar to inserts, and there is no more to say here.

The next important feature of this framework is that database tables are bound to data structures:

First we need to define a struct and tell soci how to match the column names to the fields of the data structure:

struct person {int id;
  std::string name;
int sex;

};
    namespace Soci {template<> struct type_conversion<person> {typedef values BASE_TYPE; static void From_base (values const & V, INDICATOR/* IND */, Person & P) {p.id = v.get<int> ("id")
      ;
      P.name = v.get<std::string> ("name");
    P.sex = v.get<int> ("Sex");
      static void To_base (const person & P, values & V, indicator & IND) {V.set ("id", p.id);
      V.set ("name", P.name);
      V.set ("Sex", p.sex);
    IND = I_OK;
}
  };
  struct person {int id;
  std::string name;
int sex;
 
};
    namespace Soci {template<> struct type_conversion<person> {typedef values BASE_TYPE; static void From_base (values const & V, INDICATOR/* IND */, Person & P) {p.id = v.get<int> ("id")
      ;
      P.name = v.get<std::string> ("name");
    P.sex = v.get<int> ("Sex"); } static void To_base (const person & P, values & V, indicator & IND) {V.set ("id", p.id);
      V.set ("name", P.name);
      V.set ("Sex", p.sex);
    IND = I_OK;
}
  };

 }

About

template<> struct type_conversion<person>
 
template<> struct type_conversion<person>

Here, the official document is wrong, I checked for a long time, according to the above written to write.

1. Use data structure to select

TEST (Soci,select_obj_one) {try {session SQL (MySQL, "Db=soci User=dantezhu");

    Indicator IND;
    int count;
    SQL << "SELECT COUNT (*) from Tb_test", Into (count, Ind);

    ASSERT_EQ (Ind, I_OK) << count;
    String name = "Dandan";
    Person p;

    SQL << "Select Id,name,sex from tb_test where name =: Name", to (P, Ind), use (name);

    ASSERT_EQ (Ind, I_OK) << name; 
        if (Sql.got_data ()) {cout<< p.id << "," << p.name << ","
    << p.sex << Endl;
  } catch (Exception const &e) {FAIL () <<e.what ();
    } TEST (Soci,select_obj_one) {try {session SQL (MySQL, "Db=soci User=dantezhu");
 
    Indicator IND;
    int count;
    SQL << "SELECT COUNT (*) from Tb_test", Into (count, Ind);
 
    ASSERT_EQ (Ind, I_OK) << count;
    String name = "Dandan";
    Person p; SQL << "Select Id,name,sex from Tb_test WHere name =: name, into (P, Ind), use (name);
 
    ASSERT_EQ (Ind, I_OK) << name; 
        if (Sql.got_data ()) {cout<< p.id << "," << p.name << ","
    << p.sex << Endl;
  } catch (Exception const &e) {FAIL () <<e.what ();

 }
}

2. Insert with data structure

TEST (soci,insert_obj_noexist)
{
  Try
  {session
    SQL (MySQL, "Db=soci User=dantezhu");

    Person p = {
      0,
      "Niuniu",
      2
    };

    SQL << INSERT into tb_test (name, Sex) VALUES (: Name,: Sex), use
      (p);
  }
  catch (Exception const &e)
  {
    FAIL () <<e.what ();
  }
}
 
TEST (soci,insert_obj_noexist)
{
  Try
  {session
    SQL (MySQL, "Db=soci User=dantezhu");
 
    Person p = {
      0,
      "Niuniu",
      2
    };
 
    SQL << INSERT into tb_test (name, Sex) VALUES (: Name,: Sex), use
      (p);
  }
  catch (Exception const &e)
  {
    FAIL () <<e.what ();
  }
}

That's the whole thing. The following is the download path to the code file in the article:
http://code.google.com/p/vimercode/source/browse/

In addition, although the MySQL access in Python is relatively simple, but still want to know whether there is a more pythonic library or interface, if a friend know, welcome to hesitate to inform.