Of Stack

Introduces an MySQL access library soci for C++ programs

  • 2020-10-07 18:54:25
  • OfStack

For a long time, I've been looking for a more user-friendly way to access databases (not that the default is bad, but sometimes it's just not convenient in modular design).
Later, I was lucky to find codeigniter's ActiveReord in php, please refer to this article for details: Extract CodeIgniter's database access class!
However, c++ always USES the most original way. Yesterday, I took the opportunity to use the project and searched the Internet for a long time. Finally, I found two sets of c++ database access frameworks:

soci
litesql

I took down the two sets of code to see 1. litesql realized 1 complete set of code automatic generation, which is powerful but also heavy. soci is relatively lightweight, but it also implements the mapping of data structures to database tables. I prefer something light, so I chose soci. After two days of trial, I feel very good.

The official documentation is also very detailed, so here is a simple explanation using the unit test code I wrote:
Start by creating the library table: 


create database soci;
CREATE TABLE `tb_test` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(32) default "",
 `sex` int(11) default 0,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`)
);
 
create database soci;
CREATE TABLE `tb_test` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(32) 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",
      into(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",
      into(sex, ind), use(name);
 
    ASSERT_EQ(ind, i_ok) << name;
  }
  catch (exception const &e)
  {
    FAIL()<<e.what();
  }
}

The result of select. If successful, ind will be i_ok and the same value sex will be assigned. If you fail, the reverse is true

2. Simple select with 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",
      into(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",
      into(vec_name), into(vec_sex), use(sex);
  }
  catch (exception const &e)
  {
    FAIL()<<e.what();
  }
}

The only difference from select single record is that the parameter of into() is 1 vector. In fact, using multiple vector is not a good choice. The data structurally based approach will be described later.

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 the same two problems:
a)affect_rows(number of lines of operation) could not be returned
id (b) has no way of knowing, especially if the primary key of insert is autogenous, what the value of the inserted primary key is.

Both update and delete are similar to insert, so I won't go into more detail here.

Following is an important feature of the framework, which is that database tables are bound to data structures:

First we need to define a structure and tell soci how to match the column name 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 looked for a long time, in accordance with the above to write.

1. Use data structures for 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",
      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();
  }
}
 
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. Use data structures for insert 


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 it ~~ Here is the download path of the code file:
http://code.google.com/p/vimercode/source/browse/#svn%2Ftrunk%2Fsoci_test

In addition, although the mysql access under python is relatively simple, but still want to know if there is more Pythonic library or interface, if any friends know, welcome to let us know.

Related articles: