Introducing the SOCI - C ++ Database Access Library

  • Tutorial

Introduction


The library itself is still quite mature, - the first release on the githaba dates back to 2004 already. I was surprised when Habr in the search engine did not give me a single link to articles that mentioned this wonderful library.


Pronounced like: SOCI , with an emphasis on the first syllable.


SOCI supports ORM , through the specialization soci :: type_conversion .


Database support (DB) (backends):



I will not translate manuals or give here a code from examples, but I will try to adapt (with a change in the structure of the table and other simplifications) the code from my past project, so that it would be clearer and more interesting.


Installation


Download raws from the master branch , unpack, and inside the directory execute the command:


In windows


$ mkdir build && cd build && cmake -G "Visual Studio 15 2017 Win64” ../ && cmake --build. --config Release

or instead of the last command, you can open the resulting project in Visual Studio and build.
(on the command line with cmake prompted Wilk )


In nix


$ mkdir build && cd build && cmake ../ && sudo make install

soci-9999.ebuild

If you are the owner of Gentoo Linux or Calculate Linux , and you want to have the latest SOCI version from the official repository on the githaba, you can save this installation file in the directory /usr/portage/dev-db/soci/, go to it and execute the command:


# ebuild soci-9999.ebuild manifest && emerge -va = dev-db / soci-9999

# Copyright 1999-2018 Gentoo Foundation# Distributed under the terms of the GNU General Public License v2
EAPI=6
if [[ ${PV} == *9999 ]] ; then
    SCM="git-r3"
    EGIT_REPO_URI="https://github.com/SOCI/${PN}.git"fi
CMAKE_MIN_VERSION=2.6.0
inherit cmake-utils ${SCM}
DESCRIPTION="Makes the illusion of embedding SQL queries in the regular C++ code"
HOMEPAGE="http://soci.sourceforge.net/"if [[ ${PV} == *9999 ]] ; then
    SRC_URI=""
    KEYWORDS="~amd64 ~x86"else
    SRC_URI="https://github.com/SOCI/${PN}/archive/${PV}.tar.gz -> ${P}.tar.gz"
    KEYWORDS="amd64 x86"fi
LICENSE="Boost-1.0"
SLOT="0"
IUSE="boost doc +empty firebird mysql odbc oracle postgres sqlite static-libs test"
RDEPEND="
    firebird? ( dev-db/firebird )
    mysql? ( virtual/mysql )
    odbc? ( dev-db/unixODBC )
    oracle? ( dev-db/oracle-instantclient-basic )
    postgres? ( dev-db/postgresql:= )
    sqlite? ( dev-db/sqlite:3 )
"
DEPEND="${RDEPEND}
    boost? ( dev-libs/boost )
"src_configure() {
    local mycmakeargs=(
        -DWITH_BOOST=$(usex boost)
        -DSOCI_EMPTY=$(usex empty)
        -DWITH_FIREBIRD=$(usex firebird)
        -DWITH_MYSQL=$(usex mysql)
        -DWITH_ODBC=$(usex odbc)
        -DWITH_ORACLE=$(usex oracle)
        -DWITH_POSTGRESQL=$(usex postgres)
        -DWITH_SQLITE3=$(usex sqlite)
        -DSOCI_STATIC=$(usex static-libs)
        -DSOCI_TESTS=$(usex test)
        -DWITH_DB2=OFF
    )
    #use MYCMAKEARGS if you want enable IBM DB2 support
    cmake-utils_src_configure
}
src_install() {
    use doc && local HTML_DOCS=( doc/. )
    cmake-utils_src_install
}

Writing a pool for database connections


db_pool.hpp
#ifndef db_pool_hpp#define db_pool_hpp// да простят меня пользователи НЕ GCC, но я не знаю как отключить// ворнинги для других компиляторов, о deprecated auto_ptr (если версия ниже 4)#pragma GCC diagnostic push#pragma GCC diagnostic ignored "-Wdeprecated-declarations"#pragma GCC diagnostic ignored "-Wmaybe-uninitialized"#include<soci/soci.h>#include<soci/connection-pool.h>#pragma GCC diagnostic pop#include<iostream>#include<string>classdb_pool {
  soci::connection_pool* pool_;
  std::size_t pool_size_;
public:
  db_pool():pool_(nullptr),pool_size_(0) {}
  ~db_pool() { close(); }
  soci::connection_pool* get_pool(){ return pool_; }
  boolconnect(conststd::string& conn_str, std::size_t n = 5){
    if (pool_ != nullptr) { close(); }
    int is_connected = 0;
    if (!(pool_ = new soci::connection_pool((pool_size_ = n)))) returnfalse;
    try {
      soci::indicator ind;
      for (std::size_t _i = 0; _i < pool_size_; _i++) {
        soci::session& sql = pool_->at(_i);
        // для каждой сессии открываем соединение с БД
        sql.open(conn_str);
        // и проверяем простым запросом
        sql << "SELECT 1;", soci::into(is_connected, ind);
        if (!is_connected) break;
        elseif (_i+1 < pool_size_) is_connected = 0;
      }
    } catch (std::exception const & e) { std::cerr << e.what() << std::endl; }
    if (!is_connected) close();
    return (pool_ != nullptr);
  }
  voidclose(){
    if (pool_ != nullptr) {
      try {
        for (std::size_t _i = 0; _i < pool_size_; _i++) {
          soci::session& sql = pool_->at(_i);
          sql.close();
        }
        delete pool_; pool_ = nullptr;
      } catch (std::exception const & e) { std::cerr << e.what() << std::endl; }
      pool_size_ = 0;
    }
  }
};
#endif

We define the structure of the table in the user_info class.


user_info.hpp
#ifndef user_info_hpp#define user_info_hpp#include "db_pool.hpp"#include <ctime>#include <vector>#include <regex>#include <numeric>#include <algorithm>#include <iomanip>// некоторые вспомогательные ф-ии для преобразования массивов в векторы и обратно
template<typename T>
static void extract_integers(const std::string& str, std::vector<T>& result ) {
  result.clear();
  using re_iterator = std::regex_iterator<std::string::const_iterator>;
  using re_iterated = re_iterator::value_type;
  std::regex re("([\\+\\-]?\\d+)");
   re_iterator rit(str.begin(), str.end(), re), rend;
   std::transform(rit, rend, std::back_inserter(result), [](const re_iterated& it){return std::stoi(it[1]); });
}
template<typename T>
static void split_integers(std::string& str, const std::vector<T>& arr) {
  str = "{";
  if (arr.size()) {
    str += std::accumulate(arr.begin()+1, arr.end(), std::to_string(arr[0]),
                           [](const std::string& a, T b){return a + ',' + std::to_string(b);});
  } str += "}";
}
// структура таблицы `users'classuser_info{
public:
  int id; // айди пользователя
  std::tm birthday; // день рождения
  std::string firstname, lastname; // имя и фамилия
  std::vector<int> friends; // айдишники друзей
  user_info():id(0),birthday(0),firstname(),lastname(),friends() {}
  void print() {
    std::cout.imbue(std::locale("ru_RU.utf8"));
    std::cout << "id: " << id << std::endl;
    std::cout << "birthday: " << std::put_time(&birthday, "%c %Z") << std::endl;
    std::cout << "firstname: " << firstname << std::endl;
    std::cout << "lastname: " << lastname << std::endl;
    std::string arr_str;
    split_integers(arr_str, friends);
    std::cout << "friends: " << arr_str << std::endl;
  }
  void clear() { id = 0; firstname = lastname = ""; friends.clear(); }
  user_info& operator=(const user_info& rhs) {
    if (this != &rhs) {
      id = rhs.id;
      birthday = rhs.birthday;
      firstname = rhs.firstname;
      lastname = rhs.lastname;
      friends = rhs.friends;
    }
    return *this;
  }
};
// для работы со своими типами, в SOCI имеются конвертерыnamespacesoci {
  template<> structtype_conversion<user_info> {
    typedefvaluesbase_type;
    static void from_base(values const& v, indicator ind, user_info& p) {
      if (ind == i_null) return;
      try {
        p.id = v.get<int>("id", 0);
        p.birthday = v.get<std::tm>("birthday", {});
        p.firstname = v.get<std::string>("firstname", {});
        p.lastname = v.get<std::string>("lastname", {});
        std::string arr_str = v.get<std::string>("friends", {});
        extract_integers(arr_str, p.friends);
      } catch (std::exception const & e) { std::cerr << e.what() << std::endl; }
    }
    static void to_base(const user_info& p, values& v, indicator& ind) {
      try {
        v.set("id", p.id);
        v.set("birthday", p.birthday);
        v.set("firstname", p.firstname);
        v.set("lastname", p.lastname);
        std::string arr_str;
        split_integers(arr_str, p.friends);
        v.set("friends", arr_str);
        ind = i_ok;
        return;
      } catch (std::exception const & e) { std::cerr << e.what() << std::endl; }
      ind = i_null;
    }
  };
}
#endif

We are testing our code


test.cxx
#ifndef test_cxx#define test_cxx#include"user_info.hpp"// g++ -std=c++11 test.cxx -o test -lsoci_core -lsoci_postgresql -lsoci_mysql && ./testintmain(){
  db_pool db;
  /// \note замените "postgresql" на свой бэкенд, также измените имя БД и пользователя с паролемif (db.connect("postgresql://host='localhost' dbname='test' user='test' password='test'")) {
    try {
      soci::session sql(*db.get_pool());
      // сформируем запрос создадим таблицуstd::string query_str = "CREATE TABLE IF NOT EXISTS users(id";
      // нам нужно для каждого бэкенда, указать правильный тип авто-счётчика для поля idif (sql.get_backend_name() == "postgresql") query_str += " SERIAL ";
      elseif (sql.get_backend_name() == "mysql") query_str += " INT AUTO_INCREMENT ";
      else query_str += " INT ";
      query_str += "NOT NULL PRIMARY KEY, birthday TIMESTAMP DEFAULT CURRENT_TIMESTAMP, firstname TEXT DEFAULT NULL, lastname TEXT DEFAULT NULL, friends TEXT DEFAULT NULL)";
      // выполняем запрос
      sql << query_str;
      // заполняем поля
      user_info info;
      std::time_t t = std::time(nullptr); info.birthday = *std::localtime(&t);
      info.firstname = "Dmitrij";
      info.lastname = "Volin";
      info.friends = {1,2,3,4,5,6,7,8,9};
      sql << "INSERT INTO users(birthday, firstname, lastname, friends) VALUES(:birthday, :firstname, :lastname, :friends)", soci::use(info);
      t = std::time(nullptr); info.birthday = *std::localtime(&t);
      info.firstname = "Vasy";
      info.lastname = "Pupkin";
      info.friends = {11,22,33,44,55,66,77,88,99};
      // делаем ещё одну запись в БД
      sql << "INSERT INTO users(birthday, firstname, lastname, friends) VALUES(:birthday, :firstname, :lastname, :friends)", soci::use(info);
      // индикатор для выборки, может быть: soci::i_ok, soci::i_null
      soci::indicator ind;
      // для MySQL получить id последней вставленной записи, для AUTO_INCREMENT:// sql.get_backend()->get_last_insert_id(sql, "users", reinterpret_cast<long&>(id));//// для PostgreSQL чтобы получить id последней записи, нужно сформировать запрос так:// sql << "INSERT INTO users(birthday, firstname, lastname, friends) VALUES(:birthday, :firstname, :lastname, :friends) RETURNING id", soci::use(info), soci::into(id, ind);// очищаем перед выборкой из БД
      info.clear();
      // делаем выборку нашей записи в очищенную структуру, по полю `lastname'
      sql << "SELECT * FROM users WHERE lastname = :label LIMIT 1", soci::use(std::string("Volin"), "label"), soci::into(info, ind);
      if (ind == soci::i_null) std::cout << "не удалось выбрать данные из БД ..." << std::endl;
      else info.print();
      std::cout << "++++++++++++++++++++++++++++++++++++++" << std::endl;
      // сейчас сделаем полную выборку
      soci::rowset<user_info> rs = (sql.prepare << "SELECT * FROM users");
      for (auto it = rs.begin(); it != rs.end(); it++) {
        user_info& i = *it;
        i.print();
      }
      // удаляем таблицу
      sql << "DROP TABLE IF EXISTS users";
    } catch (std::exception const & e) { std::cerr << e.what() << std::endl; }
  }
  return0;
}
#endif

Conclusion


In this article we reviewed the main features of the library.


In the next article (if readers have an interest), I will write about working with the BLOB type - for storing files and pictures in the database (in postgresql, these are fields of type OID), as well as about transactions and prepared-requests.


Links


SOCI on github
SOCI home page


Also popular now: