Boost C++ Libraries

...one of the most highly regarded and expertly designed C++ library projects in the world. Herb Sutter and Andrei Alexandrescu, C++ Coding Standards

PrevUpHomeNext

The static interface

To use the static interface, we must first define a data structure that describes the shape of our rows. We have several options:

We will start with Boost.Describe and explain the other options later. For a comparison, please refer to this table.

For example, given the following table definition:

constexpr const char* table_definition = R"%(
    CREATE TEMPORARY TABLE posts (
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR (256) NOT NULL,
        body TEXT NOT NULL
    )
)%";

We can define our row type like this:

// We can use a plain struct with ints and strings to describe our rows.
// This must be placed at the namespace level
struct post
{
    int id;
    std::string title;
    std::string body;
};

// We use BOOST_DESCRIBE_STRUCT to add reflection capabilities to post.
// We must list all the fields that should be populated by Boost.MySQL
BOOST_DESCRIBE_STRUCT(post, (), (id, title, body))

And write the following to query our table:

static_results<post> result;
conn.execute("SELECT id, title, body FROM posts", result);

for (const post& p : result.rows())
{
    // Process the post as required
    std::cout << "Title: " << p.title << "\n" << p.body << "\n";
}

Note that static_results::rows returns a boost::span object, which is a C++11 backport of std::span. The span points into memory owned by the static_results object. Care must be taken not to use this view object after the static_results goes out of scope.

Field matching

Columns in the query are matched to fields in the struct by name. If a struct field cannot be matched to any query column, an error is issued. Extra columns in the query are ignored.

If your query contains columns with names that don't qualify as C++ identifiers, you can use SQL aliases. For example, given this struct:

struct statistics
{
    std::string company;
    double average;
    double max_value;
};
BOOST_DESCRIBE_STRUCT(statistics, (), (company, average, max_value))

You can write your query as:

// Summing 0e0 is MySQL way to cast a DECIMAL field to DOUBLE
constexpr const char* sql = R"%(
    SELECT
        IFNULL(AVG(salary), 0.0) + 0e0 AS average,
        IFNULL(MAX(salary), 0.0) + 0e0 AS max_value,
        company_id AS company
    FROM employee
    GROUP BY company_id
)%";

static_results<statistics> result;
conn.execute(sql, result);

Metadata checking

The static interface will try to validate as soon as possible that the provided row type is compatible with the schema returned by the server. This process is known as metadata checking, and is performed before reading any data. The following checks are performed:

For example, if your table is defined like this:

constexpr const char* table_definition = R"%(
    CREATE TEMPORARY TABLE posts_v2 (
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR (256) NOT NULL,
        body TEXT
    )
)%";

Using the post type we defined above will cause an error, because the body field may be NULL, but our type doesn't account for it. In this case, the correct definition would be:

struct post_v2
{
    int id;
    std::string title;
    std::optional<std::string> body;  // body may be NULL
};
BOOST_DESCRIBE_STRUCT(post_v2, (), (id, title, body))

Using Boost.PFR

If you're using C++20 or above, you can use Boost.PFR to reflect types without the BOOST_DESCRIBE_STRUCT macro:

// post_v3 doesn't contain any metadata - we're not using BOOST_DESCRIBE_STRUCT here
struct post_v3
{
    int id;
    std::string title;
    std::string body;
};

PFR reflection can be enabled in Boost.MySQL by using pfr_by_name:

// pfr_by_name is a marker type. It tells static_results to use
// Boost.PFR for reflection, instead of Boost.Describe.
static_results<pfr_by_name<post_v3>> result;

// As with Boost.Describe, query fields are matched to struct
// members by name. This means that the fields in the query
// may appear in any order.
conn.execute("SELECT body, id, title FROM posts", result);

// Note that result.rows() is a span of post_v3 objects,
// rather than pfr_by_name<post_v3> objects. post_v3
// is the underlying row type for pfr_by_name<post_v3>
for (const post_v3& p : result.rows())
{
    // Process the post as required
    std::cout << "Title: " << p.title << "\n" << p.body << "\n";
}

Note that pfr_by_name is what we call a marker type - an empty type that tells classes like static_results how to reflect a type. If no marker type is used, Boost.Describe is used to retrieve reflection data for struct types.

pfr_by_position is similar to pfr_by_name, but will match columns in the query to struct fields by position, rather than name. It only requires C++14 to work. For instance:

// pfr_by_position is another marker type.
// Fields in post_v3 must appear in the same order as in the query,
// as matching will be done by position.
static_results<pfr_by_position<post_v3>> result;
conn.execute("SELECT id, title, body FROM posts", result);

// The underlying row type is post_v3
for (const post_v3& p : result.rows())
{
    // Process the post as required
    std::cout << "Title: " << p.title << "\n" << p.body << "\n";
}

Please refer to this table for a comparison with Boost.Describe.

Using tuples

You can also use std::tuples as row types. This can be handy for simple queries:

static_results<std::tuple<std::int64_t>> result;
conn.execute("SELECT COUNT(*) FROM employee", result);
std::cout << "Number of employees: " << std::get<0>(result.rows()[0]) << "\n";

Fields in tuples are matched to query columns by order. The query must return as many columns as fields the tuple has, at least. Any extra trailing columns in the query are ignored.

Multi-resultset and multi-function operations

You can use both with the dynamic interface. Please refer to the sections on multi-resultset operations and multi-function operations for more information.

Reflection techniques comparison

Should I use Boost.Describe, Boost.PFR or tuples? Each one has its advantages and drawbacks. This table may help you decide:

Technique

Sample code

Minimum C++ standard

Comments

Feature test macro

Boost.Describe

// Definition should be at namespace scope
struct post
{
    int id;
    std::string title;
    std::string body;
};
BOOST_DESCRIBE_STRUCT(post, (), (id, title, body))


// Usage
static_results<post> result;
conn.execute("SELECT title, body, id FROM posts", result);

C++14

  • Requires adding metadata with BOOST_DESCRIBE_STRUCT.
  • Matches fields by name.
  • No limitations placed on the row type (e.g. works for structs using inheritance).

BOOST_MYSQL_CXX14 is defined

Boost.PFR using names

// Definition should be at namespace scope
struct post
{
    int id;
    std::string title;
    std::string body;
};


// Usage
static_results<pfr_by_name<post>> result;
conn.execute("SELECT title, body, id FROM posts", result);

C++20

  • Doesn't require adding metadata to structs.
  • Matches fields by name.
  • Works for row types satisfying SimpleAggregate.

BOOST_PFR_CORE_NAME_ENABLED is defined and set to 1

Boost.PFR using field position

// Definition should be at namespace scope
struct post
{
    int id;
    std::string title;
    std::string body;
};


// Usage
static_results<pfr_by_position<post>> result;
conn.execute("SELECT id, title, body FROM posts", result);

C++17
C++14 with limitations

  • Doesn't require adding metadata to structs.
  • Matches fields by position.
  • In C++17 mode, it works for row types satisfying SimpleAggregate.
  • In C++14 mode, it may not work for rows containing certain field types, like strings. See Boost.PFR documentation on C++14 limitations.

BOOST_PFR_ENABLED is defined and set to 1. BOOST_PFR_USE_CPP17 is defined and set to 1 for C++17 mode.

Standard tuples

using tuple_t = std::tuple<int, std::string, std::string>;
static_results<tuple_t> result;
conn.execute("SELECT id, title, body FROM posts", result);

C++14

  • Should only be used for very simple queries.
  • Matches fields by position.

BOOST_MYSQL_CXX14 is defined

Note that using the static interface always requires C++14, at least. The BOOST_MYSQL_CXX14 test macro is defined only if the static interface is supported. Including the static interface headers on an unsupported compiler doesn't cause any error, but classes like static_results and static_execution_state are not defined. The test macro is brought on scope by any of the static interface headers.

Allowed field types

All the types used within your Describe structs or tuples must be within the following table. A Describe struct or tuple composed of valid field types models the StaticRow concept.

The following table is a reference of the C++ types that can be used in a StaticRow and their compatibility with MySQL database types:

C++ type

Compatible with...

std::int8_t

TINYINT

std::uint8_t

TINYINT UNSIGNED

std::int16_t

TINYINT
TINYINT UNSIGNED
SMALLINT
YEAR

std::uint16_t

TINYINT UNSIGNED
SMALLINT UNSIGNED
YEAR

std::int32_t

TINYINT, TINYINT UNSIGNED
SMALLINT, SMALLINT UNSIGNED
MEDIUMINT, MEDIUMINT UNSIGNED
INT
YEAR

std::uint32_t

TINYINT UNSIGNED
SMALLINT UNSIGNED
MEDIUMINT UNSIGNED
INT UNSIGNED
YEAR

std::int64_t

TINYINT, TINYINT UNSIGNED
SMALLINT, SMALLINT UNSIGNED
MEDIUMINT, MEDIUMINT UNSIGNED
INT, INT UNSIGNED
BIGINT
YEAR

std::uint64_t

TINYINT UNSIGNED
SMALLINT UNSIGNED
MEDIUMINT UNSIGNED
INT UNSIGNED
BIGINT UNSIGNED
YEAR
BIT

bool

BOOL or BOOLEAN (alias for TINYINT).

float

FLOAT

double

FLOAT, DOUBLE

date

DATE

datetime

DATETIME, TIMESTAMP

time

TIME

std::basic_string<char, std::char_traits<char>, Allocator>

The object must be default-constructible.

CHAR, VARCHAR, TEXT
ENUM, SET
JSON
DECIMAL/NUMERIC

std::basic_vector<unsigned char, Allocator>

The object must be default-constructible.

BINARY, VARBINARY, BLOB
GEOMETRY

std::optional<T>

T must be any of the types listed in this table.

Any type compatible with T

boost::optional<T>

T must be any of the types listed in this table.

Any type compatible with T


PrevUpHomeNext