Refactoring my Qt database code

published at 14.03.2015 23:23 by Jens Weller
Save to Instapaper Pocket

For two days I had the chance to clean up my code and do a little refactoring. One of the results is, that my database code now also uses variadic templates. For some time now, I use Qt as the UI and Database frontend of my applications which I'm working on.

When I started using Qt, I quickly opted for sqlite as my preferred datastorage, as its available on mobile devices and also gives you a reliable way to write and read data, with out needing to deal with serialization yourself. When dealing with databases in C++, there are not a lot of modern options, sqlpp11 is a newer library based on C++11, and a lot of other options are pre C++11 and/or very database specific. While Qt offers a very nice API for accessing databases, it is surely not the freshest one, but it is very well integrated in the UI I use (QSqlModels) and supports a lot of database backends. When writing code, you mainly deal with the two main classes: QSqlDatabase for opening, closing and dealing with general database stuff, like transactions, commits or rollbacks. And QSqlQuery, which is the class for executing SQL queries.

So when I was used to the general API, I quickly had everywhere code like this:

QSqlQuery query("SELECT foo FROM bar WHERE foo = 42");
if(!query.exec())
  qDebug() << query.lastError().text() << query.lastQuery();

It gets tempting to spread this via copy and paste, and when a feature is added, one might forget the error logging at all, or just output the error. So first thing I did was writing a helper function, which deals with executing such SQL Strings:

bool execSQL(const QString &sql)
{
    QSqlQuery query;
    if(!query.exec(sql))
        qDebug() << query.lastError().text() << query.lastQuery();
    return !query.lastError().isValid();
}

This ignores, that you could have multiple QDatabase instances, my applications for now only have one, and so I use the default connection, which every query object will use, when not given a different one. But there is still a problem, this code ignores prepared statements, which one should use for any dynamic query containing user supplied values. Also, the above code does not work with selects, as it does not return the query. So for almost a year now, I've been using this code, to deal with prepared statements and selects:

QSqlQuery execSQL(const QString &sql,const QVariantList &args)
{
    QSqlQuery query;
    query.prepare(sql);
    assert(query.boundValues().size() == args.size());
    for(int i = 0; i < args.size(); ++i)
        query.bindValue(i,args[i]);
    if(!query.exec() && query.lastError().isValid())
        qWarning() << query.lastError().text()<< query.lastQuery();
    return query;
}
call: execSQL(*sql*,QVariantList{id,foo,bar});

And there is also a version which takes the query as a reference (and returning a bool), as INSERT Statements often are combined with executing the same prepared statement multiple times. The QSqlQuery class has no method to hand over the QVariantList, for each element I have to call bindValue. And yes, this function has the precondition that the supplied list has the same size as the bound values in the query. This is checked by the assert.

Its convenient to use it, but one thing always bugged me: it does not really feel like the right solution, to use the QVariantList. It would be semi-ok, if there was a method to bind all values in order to the query (e.g. bindValues(QVariantList), but instead I have to loop through them one by one. And then, as this is a piece of code used everywhere, its awkward to have everywhere call to a function which takes a temporary QVariantList. So today, I finally had the time to refactor this method into using variadic templates. This way, I'm able to just take the query and its parameters as function arguments:

template<class ...Args>
QSqlQuery exec_sql(const QString& sql, Args... args)
{
    QSqlQuery query;
    query.prepare(sql);
    Q_ASSERT(query.boundValues().size() == sizeof...(args));
    bind_value(query,0, args...);
    if(!query.exec() && query.lastError().isValid())
        qWarning() << query.lastError().text()<< query.lastQuery();
    return query;
}
call: auto query = exec_sql(*sql*,foo,bar,"oh look a string",42);

This is the version I mostly use for selects, so it needs to return the query, and takes the SQL as a string. The actual binding of the values into the query happens with the function bind_value, which takes a query object by reference, and the starting index, which is always 0 (as all the values are in the parameter pack). The implementation of bind_value is very simple:

void bind_value(QSqlQuery&, int );
template<class T, class ...Args>
void bind_value(QSqlQuery& query, int index,T t, Args... args)
{
    query.bindValue(index,t);
    bind_value(query,++index,args...);
}

This function simply unpacks one type, calls the bindValue method from query and then recurses until no more arguments are left. When compiled this leads to code which also would be generated when a loop is unrolled calling query.bindValue like before. There is just no temporary QVariantList anymore, and the code got a lot cleaner. The last thing that bugs me is, if I should add a static_assert into bind_value checking for a conversion to QVariant for T.

Join the Meeting C++ patreon community!
This and other posts on Meeting C++ are enabled by my supporters on patreon!