Release of wxSQLite3 Version 5.0.1

wxSQLite3, an interface to SQLite databases for wxWidgets-based applications, has been available for over 20 years. During this time, the C++ programming language in particular has evolved significantly, making it high time to modernize the interface and adapt it to some of the new features in C++.

I had already compiled my initial ideas for the modernization back in the summer of 2019, but as life would have it, it ended up taking a little longer than expected before the first revised version could finally be released this August.

Since the new 5.x version is not source-code-compatible with the previous 4.x version, I will continue to provide updates for the 4.x line for some time. It has not yet been definitively decided how long this will be. The “end-of-life” date for version 4.x will be announced in a timely manner. When that time comes will also depend on actual needs. Developers who — for whatever reason — want to or need to continue using version 4.x should therefore speak up on GitHub. Feedback on the new version is, of course, also welcome.

wxSQLite3

Version:

Changes since previous release:

• Enhance template Get and Bind methods to handle integral types in general (like int8_t, uint16, enum and so on)
• Enhance template Bind methods to handle direct types instead of std::optional only
• Add class attribute to enum enumerations where it was still missing
• Adjust minimal sample

Modern C++ Interface

wxSQLite3 provides a number of convenience features that make working with SQLite result sets and prepared statements more natural in modern C++ code. These features complement the traditional cursor-based interface and the existing type-specific methods.

Type-aware access to result-set values

In addition to the existing type-specific methods for retrieving values from a result set, wxSQLite3 provides a template-based Get<T>() method. The method returns a std::optional<T>, making the distinction between an SQL NULL value and a value of the requested C++ type explicit.

For example:

const std::optional<int> id = resultSet.Get<int>(0);
const std::optional<wxString> name = resultSet.Get<wxString>(1);

if (id)
{
    // The column contains a non-NULL integer value.
}

If the corresponding SQLite value is NULL, the returned std::optional is empty. Otherwise, it contains the value converted to the requested C++ type.

The template-based interface is implemented in terms of the existing type-specific result-set methods. It therefore provides a convenient and type-oriented interface without introducing a separate value-conversion mechanism.

Type-aware binding of statement parameters

Prepared-statement parameters can likewise be bound using the template-based Bind<T>() interface. The method accepts a std::optional<T>, allowing an SQL NULL value to be represented naturally by an empty std::optional.

For example:

std::optional<int> id = ...;
std::optional<wxString> name = ...;

stmt.Bind(1, id);
stmt.Bind(2, name);

An engaged std::optional binds its contained value, while an empty std::optional binds an SQL NULL.

As with Get<T>(), the template-based binding methods use the existing type-specific binding methods internally.

This makes std::optional a convenient way to handle nullable database values without having to treat SQL NULL as a special case in application code.

Binding and retrieving tuples of values

For situations where several values need to be bound to a prepared statement or retrieved from a result set, wxSQLite3 also provides BindTuple() and GetTuple().

The first variant uses consecutive parameter or column indices. The values are therefore associated with indices starting at the specified first index.

For example:

const auto values = resultSet.GetTuple<int, wxString, double>(0);

retrieves three values from consecutive columns.

Similarly, values can be bound to consecutive parameters:

stmt.BindTuple(1, id, name, amount);

The tuple-based methods use the same type-aware conversion and std::optional handling as the individual Get<T>() and Bind() methods. Consequently, nullable database values can be represented directly in the resulting or supplied tuple.

A second variant of GetTuple() and BindTuple() accepts an array of indices. This allows the values to be associated with arbitrary, non-consecutive columns or parameters.

For example:

constexpr std::array<int, 3> columns{0, 3, 7};

const auto values =
    resultSet.GetTuple<int, wxString, double>(columns);

This is useful when the values of interest are distributed across a result set and their positions do not form a consecutive sequence.

The corresponding binding operation can use an index array in the same way:

constexpr std::array<int, 3> parameters{1, 4, 6};

stmt.BindTuple(parameters, id, name, amount);

The tuple-based interface is particularly useful when several database values correspond directly to members or values of a C++ data structure and should be handled as a group.

Range-based result-set processing

Traditionally, a wxSQLite3ResultSet is processed using its cursor-oriented interface:

while (resultSet.NextRow())
{
    const auto id = resultSet.Get<int>(0);
    const auto name = resultSet.Get<wxString>(1);

    // Process row
}

For applications using modern C++ features, a result set can also be processed with a range-based for loop:

for (const auto& row : resultSet)
{
    const auto id = row.Get<int>(0);
    const auto name = row.Get<wxString>(1);

    // Process row
}

This provides a concise and familiar way to iterate over all rows of a result set.

The iterator interface can also be used directly when more control over the iteration is required:

for (auto it = resultSet.begin(); it != resultSet.end(); ++it)
{
    const auto& row = *it;

    // Process row
}

The iterator and range-based interfaces are alternatives to the traditional NextRow() loop. They do not change the underlying SQLite result-set processing model; they provide an additional interface that integrates with the standard C++ iteration mechanisms.

Combining the features

The individual template methods, tuple operations, and range-based result-set processing can be combined to write concise and type-oriented database code.

For example:

for (const auto& row : resultSet)
{
    const auto values =
        row.GetTuple<int, wxString, double>({0, 2, 5});

    // Process values
}

The tuple returned by GetTuple() contains std::optional values, so SQL NULL values remain distinguishable from actual C++ values.

These additions are intended to complement rather than replace the established wxSQLite3 API. Existing applications can continue to use the traditional cursor-based and type-specific methods without modification.

Migration from 4.x to 5.x

Although it is necessary to modify the source code to make it run on version 5.x, most of the required changes are very straightforward. A relatively detailed migration guide is available in the documentation for version 5.x.