SuperiorMySQL++

Modern C++ wrapper for MySQL C API

Author: Tomas Nozicka, Seznam.cz

#Installation

##Requirements

• C++14 compatible compiler (tested GCC>=4.9)
• MySQL C API dev (libmysqlclient-dev)

##Bootstrap

• We use git submodules for our dependencies and git requires you to initialize them manually

git submodule update --init --recursive

##Build

• This is header only library therefore no build step is required

##Test

• Tests require docker(>=1.5.0) for running mysql instances with testing data

make -j32 test

• You may (among other things) specify custom compiler and extra flags

make -j32 test CXX=/opt/szn/bin/g++ CXXEF=-Werror LDEF=-Wl,-rpath=/opt/szn/lib/gcc/x86_64-linux-gnu/current

##Install

make -j32 DESTDIR=/usr/local/ install

#Packages

• We support several packages to be build by default:
• Debian Jessie
• Fedora 22

##dbuild (docker-build)

• This can build packages in completely clean environment using docker
• You might want to lower the CONCURRENCY in case you run out of memory

make CONCURRENCY=32 package-fedora-22-dbuild

make CONCURRENCY=32 package-debian-jessie-dbuild

##build

• classic packaging

make package-fedora-22-build

make package-debian-jessie-build

Features

• Modern C++ (currently written in C++14)
• Minimal overhead
• Multi-statement queries
• High performance conversions
• Advanced prepared statements support with type checks and automatic, typesafe bindings
• Configurable logging
• Integrated connection pool
  • Connection management
  • Health checks
  • DNS change checking
• Extensive and fully automated multi-platform tests (using Docker)

Status

Currently, it is already used at Seznam.cz in production code with great results.

The library is thoroughly tested and all tests are fully automated.

In future we are going to add more examples and documentation.

Please help us out by reporting bugs. (https://github.com/seznam/SuperiorMySqlpp/issues)

We appreciate your feedback!

Preview

Until we create proper examples, you can see all functionality in action by looking at out tests (https://github.com/seznam/SuperiorMySqlpp/tree/master/tests). Please be aware that tests must validate all possible cases and syntax and should not be taken as reference in these matters.

You can look at some basic examples below:

Connection

Connection connection{"<database>", "<user>", "<password>", "<host>", "<port>"};

Connections are not thread-safe => use one connection per thread. If you intend to have multiple connection to one server consider using connection pool.

Connection pool

auto connectionPool = makeConnectionPool([](){
    return std::make_shared<Connection>("<database>", "<user>", "<password>", "<host>", "<port>");
});
connectionPool.setMinSpare(10);  // optional
connectionPool.setMaxSpare(20);  // optional

// possibly set interval times and other things

connectionPool.startManagementJob();  // optional
connectionPool.startHealthCareJob();  // optional

connectionSharedPtr = connectionPool.get();

Queries

Simple result

auto query = connection.makeQuery("INSERT INTO ..");
query.execute();

Store result

auto query = connection.makeQuery("SELECT * FROM ...");
query.execute();

auto result = query.store();
while (auto row = result.fetchRow())
{
    // process row
}

Use result

auto query = connection.makeQuery("SELECT * FROM ...");
query.execute();

auto result = query.use();
while (auto row = result.fetchRow())
{
    // process row
}

Escaping

To escape variable manually you may use method connection.escapeString. Preferred way is using query stream manipulators:

auto query = connection.makeQuery();
query << escape << "ab'cd";  // escape - next argument will be escaped 

Multi-statement queries

auto query = connection.makeQuery(
    "INSERT INTO ...;"
    "INSERT INTO ...;"
    "INSERT INTO ...;"
);
query.execute();
do {} while (query.nextResult());

Prepared statement

Prepared statements by default automatically check bound types and query metadata and issue warnings or exceptions if you bound any incompatible types. All C API prepared statements variables types are supported and bindings are set using C++ type system.

These are in fact relatively simple examples. There are a lot of configurations for prepared statement including how strictly do you want to check metadata, allowing some types of implicit conversion and so on.

Param bindings

// type of prepared statements parameters is deduced automatically from arguments
auto preparedStatement = connection.makePreparedStatement(
    "INSERT INTO ... VALUES (?)", 0
);
preparedStatement.execute();

// or if you want multiple inserts
for (auto i=1; i<10; ++i)
{
    std::get<0>(preparedStatement.getParams()) = i
    preparedStatement.execute();
}

Result bindings

auto preparedStatement = connection.makePreparedStatement<ResultBindings<Sql::Int, Sql::Int>>(
    "SELECT `id`, `money` FROM ..."
);
preparedStatement.execute();
while (preparedStatement.fetch())
{
    // you can use std::tie
    Sql::Int id, money;
    std::tie(id, money) = preparedStatement.getResult();

    // or directly use e.g. id as: 
    preparedStatement.getResult().get<0>()
}

Dynamic prepared statement

This type is for situations when you do not know which columns you are going to need at compile time.

Param bindings

auto preparedStatement = connection.makeDynamicPreparedStatement(
        "INSERT INTO ... VALUES (?)"
);
for (auto id=0; i<10; ++i)
{
    preparedStatement.bindParam(0, id);
    preparedStatement.updateParamBindings();
    preparedStatement.execute();
}

Result bindings

auto preparedStatement = connection.makeDynamicPreparedStatement(
        "SELECT `id` FROM ..."
);
preparedStatement.execute();
int id = -1;
preparedStatement.bindResult(0, id);
preparedStatement.updateResultBindings();
while (preparedStatement.fetch())
{
    // do something with id
}

RowStreamAdapter

Syntactic sugar is provided for extracting values from Row using a familiar stream operator. When a NULL value is encountered, value is default-constructed. Extracting non-existent values is undefined behaviour.

auto row = ...
std::string s;
int i = 0;
Extras::RowStreamAdapter {row}
    >> s
    >> i
    ;

Transactions

Library automatically detects exceptions and does commit or rollback as appropriate.

(This is actually quite sophisticated since you must detect if the exception occurred between transaction ctor and dtor (even if there is already active exception). C++17 helps us by introducing std::uncaught_exceptions (instead of std::uncaught_exception), but today we are forced to use internal compiler structures.)

{
    Transaction transaction{connection};
    connection.makeQuery("INSERT INTO ...;").execute();
}
// or you can specify transaction characteristics or isolation level
{
    Transaction transaction{connection, TransactionCharacteristics::ReadOnly, IsolationLevel::RepeatableRead};
    connection.makeQuery("INSERT INTO ...;").execute();
}

Logging

The library has build-in support for custom logging. In default configuration it logs only warnings and errors to std::cerr.

You may choose some of the library predefined loggers:

// Log all event to std::cout and std::cerr
auto&& logger = std::make_shared<Loggers::Full>();
DefaultLogger::setLoggerPtr(std::move(logger));

Or define your own:

class MyLogger final : public Loggers::Base
{
    using Base::Base;
    virtual ~MyLogger() override
    { 
        // do something 
    }

    virtual void logWarning(const std::string& message) const override
    {
        // do something 
    }
    
    // a lot of logging methods like: logMySqlConnecting, logMySqlConnected, logMySqlClose, logMySqlCommit, ...
}

auto&& logger = std::make_shared<MyLogger>();
DefaultLogger::setLoggerPtr(std::move(logger));

#Current issues There are problems caused by MySQL C API's bad design which are solved by https://github.com/seznam/SuperiorMySqlpp/blob/master/include/superior_mysqlpp/low_level/mysql_hacks.hpp. This is causing problems with MariaDB which stripped down some symbols from their shared object that we use to fix this bug. (seznam#2)