Introduction

AnCH logger library aims to provide facilities to manage SQL database interactions.

Prerequisites

You need anch-utils library header files to compile this library for SQL connection pool.
SQL connection needs AnCH resource library.

Installation

TODO fill this section

Conception

AnCH SQL library is an layout to manage SQL client. Managed SQL clients are SQLite3, PostgreSQL and MySQL/MariaDB.

SQl interfaces are inspired by Java JDBC. You will find Connection, ResultSet, ...

Todo:: list other classes. ResultSet can be handle with row mapper and complete extract std::function "std::function" trough queryMapRow and queryExtract methods. These methods will release ResultSet.

AnCH SQL provides connection factory.

SQL connection pool

SQL connection pools are configured in configuration file dbCon.conf

Example

SQL connection example:

#include "sql/postgresqlConnection.hpp"
#include <iostream>
#include <functional>
#include <list>
using anch::sql::Connection;
using anch::sql::PostgreSQLConnection;
using anch::sql::SqlException;
using anch::sql::ResultSet;
class Person {
public:
  uint32_t _id;
  std::string _firstName;
  std::string _lastName;
  std::string _birthDate;
  std::string _email;
public:
  Person(): _id(), _firstName(), _lastName(), _birthDate(), _email() {}
  virtual ~Person() {}
  inline uint32_t getId() const {return _id;}
  inline void setId(uint32_t id) {_id = id;}
  inline const std::string& getFirstName() const {return _firstName;}
  inline void setFirstName(const std::string& firstName) {_firstName = firstName;}
  inline const std::string& getLastName() const {return _lastName;}
  inline void setLastName(const std::string& lastName) {_lastName = lastName;}
  inline const std::string& getBirthDate() const {return _birthDate;}
  inline void setBirthDate(const std::string& birthDate) {_birthDate = birthDate;}
  inline const std::string& getEmail() const {return _email;}
  inline void setEmail(const std::string& email) {_email = email;}
};
int
main(void) {
  std::cout << "Enter in PostgreSQL unit tests" << std::endl << std::endl;
  try {
    PostgreSQLConnection dbCon("localhost",
                               "anch",
                               "anch",
                               "anch_test",
                               5432);
    std::cout << "Connected to database" << std::endl;
    dbCon.setAutoCommit(false);
    std::cout << "Auto commit set to false" << std::endl;
    dbCon.commit();
    std::cout << "Commit no transaction ..." << std::endl;
    dbCon.rollback();
    std::cout << "Rollback no transaction ..." << std::endl;
    dbCon.setAutoCommit(true);
    std::cout << std::endl;
    std::cout << "Execute 'SELECT id,first_name,last_name,birth_date,email FROM T_Test' with manual result handling" << std::endl;
    ResultSet* res = dbCon.query("SELECT id,first_name,last_name,birth_date,email FROM T_Test");
    int row = 0;
    std::string nullStr("NULL");
    while(res->next()) {
      std::cout << "Row " << row << std::endl;
      const uint32_t* id = res->get<uint32_t>(0);
      std::cout << "id=";
      if(id == NULL) {
        std::cout << "NULL";
      } else {
        std::cout << *id;
      }
      std::cout << std::endl;
      delete id;
      const std::string* fName = res->get<std::string>(1);
      std::cout << "first name=" << (fName == NULL ? nullStr : *fName) << std::endl;
      delete fName;
      const std::string* lName = res->get<std::string>(2);
      std::cout << "last name=" << (lName == NULL ? nullStr : *lName) << std::endl;
      delete lName;
      const std::string* bDate = res->get<std::string>("birth_date");
      std::cout << "birth date=" << (bDate == NULL ? nullStr : *bDate) << std::endl;
      delete bDate;
      const std::string* email = res->get<std::string>(4);
      std::cout << "email=" << (email == NULL ? nullStr : *email) << std::endl;
      delete email;
      row++;
    }
    delete res;
    std::cout << std::endl;
    std::cout << "Execute 'SELECT id,first_name,last_name,birth_date,email FROM T_Test' with per row result handling" << std::endl;
    std::list<Person> persons;
    dbCon.queryMapRow("SELECT id,first_name,last_name,birth_date,email FROM T_Test", [&persons](ResultSet& resSet) {
        Person person;
        resSet.get<uint32_t>(0,person._id);
        resSet.get<std::string>(1, person._firstName);
        resSet.get<std::string>(2, person._lastName);
        resSet.get<std::string>(3, person._birthDate);
        resSet.get<std::string>(4, person._email);
        persons.push_back(person);
      });
    std::cout << "Found " << persons.size() << " persons." << std::endl;
    for(const Person& pers : persons) {
      std::cout << "Person " << pers._id << ":" << std::endl;
      std::cout << "first name: " << pers._firstName << std::endl;
      std::cout << "last name: " << pers._lastName << std::endl;
      std::cout << "birth data: " << pers._birthDate << std::endl;
      std::cout << "email: " << pers._email << std::endl;
    }
    std::cout << std::endl;
    std::cout << "Execute 'SELECT id,first_name,last_name,birth_date,email FROM T_Test' with result set extracting" << std::endl;
    dbCon.queryExtract("SELECT id,first_name,last_name,birth_date,email FROM T_Test", [](ResultSet& resSet) {
        while(resSet.next()) {
          Person pers;
          resSet.get<uint32_t>(0,pers._id);
          resSet.get<std::string>(1, pers._firstName);
          resSet.get<std::string>(2, pers._lastName);
          resSet.get<std::string>(3, pers._birthDate);
          resSet.get<std::string>(4, pers._email);
          std::cout << "Person " << pers._id << ":" << std::endl;
          std::cout << "first name: " << pers._firstName << std::endl;
          std::cout << "last name: " << pers._lastName << std::endl;
          std::cout << "birth data: " << pers._birthDate << std::endl;
          std::cout << "email: " << pers._email << std::endl;
        }
      });
    std::cout << std::endl;
  } catch(const SqlException& e) {
    std::cerr << "Error: " << e.what() << std::endl;
    return 1;
  }
  std::cout << "Exit PostgreSQL unit tests" << std::endl;
  return 0;
}

Pool configuration example:

[anch_mysql]
driver=MySQL
host=localhost
port=3306
user=anch
password=anch
database=anch_test
pool_maxsize=20
pool_initsize=2
[anch_pgsql]
driver=PostgreSQL
host=localhost
port=5432
user=anch
password=anch
database=anch_test
pool_maxsize=20
pool_initsize=2
pool_timeout=200
[anch_sqlite]
driver=SQLite3
database=anch.db

Pool example:

#include "sql/sqlConnectionFactory.hpp"
#include <iostream>
#include <list>
#include <functional>
using anch::sql::Connection;
using anch::sql::SqlConnectionFactory;
using anch::sql::SqlConnectionPool;
using anch::sql::SqlException;
using anch::sql::ResultSet;
class Person {
public:
  uint32_t _id;
  std::string _firstName;
  std::string _lastName;
  std::string _birthDate;
  std::string _email;
public:
  Person(): _id(), _firstName(), _lastName(), _birthDate(), _email() {}
  virtual ~Person() {}
  inline uint32_t getId() const {return _id;}
  inline void setId(uint32_t id) {_id = id;}
  inline const std::string& getFirstName() const {return _firstName;}
  inline void setFirstName(const std::string& firstName) {_firstName = firstName;}
  inline const std::string& getLastName() const {return _lastName;}
  inline void setLastName(const std::string& lastName) {_lastName = lastName;}
  inline const std::string& getBirthDate() const {return _birthDate;}
  inline void setBirthDate(const std::string& birthDate) {_birthDate = birthDate;}
  inline const std::string& getEmail() const {return _email;}
  inline void setEmail(const std::string& email) {_email = email;}
};
int
main(void) {
  std::cout << "Enter in SQL connection factory unit test" << std::endl;
  try {
    SqlConnectionFactory& fact = SqlConnectionFactory::getInstance();
    std::cout << "Factory has been correctly initialized" << std::endl;
    SqlConnectionPool& myPool = fact.getPool("anch_mysql");
    std::cout << "Execute 'SELECT id,first_name,last_name,birth_date,email FROM T_Test' on MySQL database" << std::endl;
    std::list<Person> persons;
    myPool.borrowResource().get().queryMapRow("SELECT id,first_name,last_name,birth_date,email FROM T_Test", [&persons](ResultSet& resSet) {
        Person person;
        resSet.get<uint32_t>(0,person._id);
        resSet.get<std::string>(1,person._firstName);
        resSet.get<std::string>(2,person._lastName);
        resSet.get<std::string>(3,person._birthDate);
        resSet.get<std::string>(4,person._email);
        persons.push_back(person);
      });
    std::cout << "Found " << persons.size() << " persons." << std::endl;
    for(const Person& pers : persons) {
      std::cout << "Person " << pers._id << ":" << std::endl;
      std::cout << "first name: " << pers._firstName << std::endl;
      std::cout << "last name: " << pers._lastName << std::endl;
      std::cout << "birth data: " << pers._birthDate << std::endl;
      std::cout << "email: " << pers._email << std::endl;
    }
    std::cout << std::endl;
    SqlConnectionPool& pgPool = fact.getPool("anch_pgsql");
    persons.clear();
    std::cout << "Execute 'SELECT id,first_name,last_name,birth_date,email FROM T_Test' on PostgreSQL database" << std::endl;
    auto res = pgPool.borrowResource();
    res.get().queryMapRow("SELECT id,first_name,last_name,birth_date,email FROM T_Test", [&persons](ResultSet& resSet) {
        Person person;
        resSet.get<uint32_t>(0,person._id);
        resSet.get<std::string>(1,person._firstName);
        resSet.get<std::string>(2,person._lastName);
        resSet.get<std::string>(3,person._birthDate);
        resSet.get<std::string>(4,person._email);
        persons.push_back(person);
      });
    std::cout << "Found " << persons.size() << " persons." << std::endl;
    for(const Person& pers : persons) {
      std::cout << "Person " << pers._id << ":" << std::endl;
      std::cout << "first name: " << pers._firstName << std::endl;
      std::cout << "last name: " << pers._lastName << std::endl;
      std::cout << "birth data: " << pers._birthDate << std::endl;
      std::cout << "email: " << pers._email << std::endl;
    }
    std::cout << std::endl;
    Connection* sqliteCon = fact.createConnection("anch_sqlite");
    persons.clear();
    std::cout << "Execute 'SELECT id,first_name,last_name,birth_date,email FROM T_Test' on SQLite3 database" << std::endl;
    sqliteCon->queryMapRow("SELECT id,first_name,last_name,birth_date,email FROM T_Test", [&persons](ResultSet& resSet) {
        Person person;
        resSet.get<uint32_t>(0,person._id);
        resSet.get<std::string>(1,person._firstName);
        resSet.get<std::string>(2,person._lastName);
        resSet.get<std::string>(3,person._birthDate);
        resSet.get<std::string>(4,person._email);
        persons.push_back(person);
      });
    std::cout << "Found " << persons.size() << " persons." << std::endl;
    for(const Person& pers : persons) {
      std::cout << "Person " << pers._id << ":" << std::endl;
      std::cout << "first name: " << pers._firstName << std::endl;
      std::cout << "last name: " << pers._lastName << std::endl;
      std::cout << "birth data: " << pers._birthDate << std::endl;
      std::cout << "email: " << pers._email << std::endl;
    }
    delete sqliteCon;
  } catch(const SqlException& e) {
    std::cerr << "Error: " << e.what() << std::endl;
    return 1;
  } catch(const std::bad_alloc& e) {
    std::cerr << "Error: " << e.what() << std::endl;
    return 1;
  }
  std::cout << "Exit SQL connection factory unit test" << std::endl;
  return 0;
}