Structured Bindings with Qt SQL

12.05.2022 Ivan Čukić No comments

Some time ago, I wrote a post about integrating Qt’s associative containers with the fancy new C++ features, range-based for loops with structured bindings.

That post inspired KDAB’s own Giuseppe D’Angelo to add the asKeyValueRange member function to both QHash and QMap. Now it’s possible to iterate over them with a simple range-based for loop, like so:

    for (auto [key, value] : map.asKeyValueRange()) {
        // ...
    }

The second part of my previous post demonstrates how we can iterate over Qt SQL results using a range-based for loop as if it were an ordinary collection. In addition, it announces this second part of the post that shows you how to add the support for structured bindings into the mix.

Structured Bindings, Revisited

Recall that structured bindings allow us to decompose structures such as std::pair and QPair, so that we can give more intuitive names to the fields in those structures than the first and second names provided by both pair types:

    auto [x, y] = mousePosition();

In the previous example, when the range-based for loop iterates over map.asKeyValueRange(), it takes each key-value pair from the map and assigns the name key to the first element of the pair and the name value to the second element.

Internally, the pair is stored in an invisible variable, and the names key and value just refer to the fields inside of that variable.

Out-of-the-box, structured bindings can also be used with arrays, tuple-like types, or, a bit less useful outside of generic programming, with ordinary user-defined structures:

    std::tuple<bool, QString, QString> employee;
    auto &[active, name, team_name] = employee;

    Employee employees[2];
    auto &[first_employee, second_employee] = employees;

    struct Employee {
        bool m_active;
        QString m_name;
        QString m_team;
    };
    Employee employee = ...;
    auto &[active, name, team_name] = employee;

Structured Bindings Support for Custom Types

It is also possible to make our own types decomposable with structured bindings, by making sure our types implement the so-called tuple protocol or, in other words, by making our types look like tuples.

Imagine we don’t want the m_active member variable to be seen when using structured bindings on the previously defined Employee type. Instead, we just want to be able to bind m_name and m_team:

    auto &[name, team_name] = employee;

In order to specify how our type should be decomposed with structured bindings, we need to define a few things:

• into how many values an instance of our type can be decomposed;
• the type of each of those values;
• a getter for each of those values.

The first part of the tuple protocol is simple — we need to specialize the std::tuple_size template for our type Employee. Since we only want to bind m_name and m_team, the size of our tuple-like type will be 2.

    #include <utility>

    ...

    namespace std {
        template<>
        struct tuple_size<::Employee> {
            static constexpr std::size_t value = 2;
        };
    }

The next step is to specialize the std::tuple_element template:

    namespace std {
        template<>
        struct tuple_element<0, ::Employee> {
            // The type of m_name
            using type = QString;
        };

        template<>
        struct tuple_element<1, ::Employee> {
            // The type of m_team
            using type = QString;
        };
    }

The value we defined in the std::tuple_size specialization tells the compiler how many values it will get when it decomposes an instance of Employee, and the types we defined in the std::tuple_element specializations are the types of those values. In our case, both values are QStrings.

The last step is to create a get function template. It can be a member of Employee, but it can also be a free function (non-member) template.

     template <std::size_t Idx>
     auto& get(Employee& employee) {
         if constexpr (Idx == 0) return employee.m_name;
         if constexpr (Idx == 1) return employee.m_team;
     }

It’s worth noting that this implementation will not accept const objects and you’ll need to provide a get implementation that takes a reference to a const Employee, if you want to support those as well.

Decomposing QSqlRecord

Now that we know what we need to implement in order for our types to be usable with structured bindings, we can try to do it with QSqlResultIterator, which we implemented in part 1 of this blog post.

As a reminder, in the first part of the post, we implemented the QSqlResultIterator class that can be used to iterate over all results of a QSqlQuery. We also implemented operator[] on it, which allows us to access fields in a result.

    class QSqlResultIterator {
        // ...
        QVariant operator[] (int index) const
        {
            return m_query.value(index);
        }
    };

We can use this to base the get function template on. To demonstrate that get doesn’t need to be a free function, we will implement it as a member of QSqlResultIterator:

    class QSqlResultIterator {
        // ...
        template <std::size_t Idx>
        QVariant get() const
        {
            return m_query.value(index);
        }
    };

The remaining things that need to be implemented are the specializations of std::tuple_size and std::tuple_element.

Since all values in QSqlResult are QVariants, specializing std::tuple_element is trivial. For any index we’re given, we just need to set type = QVariant:

    namespace std {
        template<std::size_t Idx>
        struct tuple_element<Idx, QSqlResultIterator> {
            using type = QVariant;
        };
    }

The std::tuple_size, on the other hand, is tricky. SQL queries are a runtime thing, and we need to know the number of fields in a record at compile time. This means that we need to allow the user to explicitly define the number of fields in a record when creating the QSqlResultIterator. One way to do it is to make QSqlResultIterator a class template with one std::size_t parameter:

    template <std::size_t FieldCount>
    class QSqlResultIterator {
        // ...
    };

This will allow us to define everything we need to allow QSqlResultIterator to be used with structured bindings:

    template <std::size_t FieldCount>
    class QSqlResultIterator {
        template <std::size_t Idx>
        QVariant get() const
        {
            return m_query.value(index);
        }

        // ...
    };

    namespace std {
        template<std::size_t FieldCount>
        struct tuple_size<QSqlResultIterator<FieldCount>> {
            statuc constexpr std::size_t value = FieldCount;
        };

        template<std::size_t Idx, std::size_t FieldCount>
        struct tuple_element<Idx, QSqlResultIterator<FieldCount>> {
            using type = QVariant;
        };
    }

We could even add a few static_asserts that would check that Idx is less than FieldCount everywhere.

Broken Range-based For Loop Use-case

When we added the FieldCount template parameter to QSqlResultIterator, we broke the use-case we had in the part 1 of this post. We now require FieldCount to be specified explicitly when an instance of QSqlResultIterator is created, and we are not creating it anywhere explicitly.

As a reminder, the QSqlResultIterator was instantiated by the range-based for loop which called begin on the QSqlQuery instance we passed to it:

    for (auto result: query) {
        // ...
    }

It happened behind the scenes and we cannot control how begin is called by the range-based for loop to be able to pass in FieldCount somehow.

Or, can we?

We can write a simple wrapper similar to what we did for asKeyValueRange. Then, instead of begin being defined for QSqlQuery directly, it would be defined for that wrapper, and it would be able to create QSqlResultIterator with the proper FieldCount value.

It could look something like this:

    template <std::size_t FieldCount>
    class QSqlResultRange {
    public:
        QSqlResultRange(QSqlQuery query)
            : m_query(std::move(query))
        {}

        QSqlResultIterator<FieldCount> begin()
        {
            return { m_query };
        }

        QSqlResultSentinel end() const
        {
            return {};
        }

    private:
        QSqlQuery m_query;
    };

Then we can use it as follows:

    for (auto [active, name, team] : QSqlResultRange<3>(query)) {
        // ...
    }

Adding Types to the Mix

So far, we’ve implemented a range object that allows us to iterate over SQL results using a range-based for loop with structured bindings.

In the previous example, we get bindings active, name and team that all have QVariant type.

Can we improve the type-safety of this code, considering our almost always storing concrete types in a database and having to unwrap all QVariants, manually, is tedious and error prone?

Can we specify that the range object returns a bool and two QStrings in each iteration?

As usual in C++, the answer here is a resounding yes. The only thing that we need to do is replace all occurrences of the FieldCount parameter with a variadic pack of types, which will allow us to specify the exact types we expect to get in each resulting row of an SQL query.

In order avoid mixing these with the previously defined types, we’ll add Typed to the names of classes we’ve created so far.

    // We want to allow the user to specify the types
    // of the fields in an SQL row
    template <typename ...Types>
    class QSqlResultTypedIterator {
    public:
        // The constructor and all the basic functions
        // we had in QSqlResultIterator remain unchanged

        QSqlResultTypedIterator(QSqlQuery& query)
            : m_query(query)
        {
            m_query.next();
        }

        QSqlResultTypedIterator& operator++()
        {
            m_query.next();
            return *this;
        }

        bool operator!=(QSqlResultSentinel sentinel) const
        {
            Q_UNUSED(sentinel);
            return m_query.isValid();
        }

        QSqlResultTypedIterator& operator*()
        {
            return *this;
        }

        // The only one that differs is the tuple-compatible
        // get member function. It can return different types
        // depending on the provided index.
        template <size_t Idx>
        auto get() const
        {
            using ResultType = std::tuple_element_t<Idx, std::tuple<Types...>>;
            // We can assert that the type stored inside of QVariant
            // is the type that we expect to be in it.
            Q_ASSERT(m_query.value(Idx).canView<ResultType>());

            // .value returns a QVariant. Then we call .value
            // on said variant to convert it to the desired type.
            return m_query.value(Idx).value<ResultType>();
        }

    private:
        QSqlQuery& m_query;
    };

    namespace std {
        // The tuple_size for QSqlResultTypedIterator is the
        // number of types inside of Types... which we can
        // easily get with sizeof...(Types)
        template<typename... Types>
        struct tuple_size<QSqlResultTypedIterator<Types...>> : public integral_constant<size_t, sizeof...(Types)> {};

        // The simplest way to implement tuple_element on our type
        // is to just base it on the implementation of std::tuple itself.
        // When we are asked for tuple_element<Idx, QSqlResultTypedIterator<Types...>>,
        // we will just replace QSqlResultTypedIterator with std::tuple,
        // and return tuple_element<Idx, std::tuple<Types...>>
        template<std::size_t Idx, typename... Types>
        struct tuple_element<Idx, QSqlResultTypedIterator <Types...>>:
               tuple_element<Idx, std::tuple              <Types...>>
        {
        };
    }


    // The complex part was in the QSqlResultTypedIterator, and the
    // range object remains as simple as QSqlResultIterator was.
    // The only change is that FieldCount is replaced by Types... everywhere
    template <typename ...Types>
    class QSqlResultTypedRange {
    public:
        QSqlResultTypedRange(QSqlQuery query)
            : m_query(std::move(query))
        {
        }

        QSqlResultTypedIterator<Types...> begin()
        {
            return { m_query };
        }

        QSqlResultSentinel end() const
        {
            return {};
        }

    private:
        QSqlQuery m_query;
    };

Wrap-up

This was a lengthy post to follow, and it had a lot of non-trivial code for something as simple as being able to write:

    for (auto [active, name, team] : QSqlResultTypedRange<bool, QString, QString>(query)) {
        qDebug() << "active(bool):" << active;
        qDebug() << "name(QString):" << name;
        qDebug() << "team(QString):" << team;
    }

While this might not look like it is worth doing, remember that this is something you need to write only once and you will use it a lot, if you have an application that is SQL-heavy.

It will make your code more easy-to-read and as type-safe as possible when SQL is concerned, since the debug builds will assert that you are not trying to coerce values of one type into being something that they are not, when crossing the border between SQL and C++.

It is also easily extendible, to allow the user to skip the conversion from QVariant to a specific type by skipping the .value<ResultType>() part when the user-specified QVariant is the desired type, or to support using wrapper types such as std::optional when you have fields in an SQL table that can be NULL.