Interfacing QT Quick with QSql Backend via QsqlRelationalTableModel

[Sam2304]

Hi All,

I've been wrapping my brain around this for the last couple of days, but I haven't been able to make much progress.

I've been trying to create an application with a QML front-end, representing data stored in an SQLite backend, all managed via a model view system. Eventually this will contain multiple related tables, so I wanted to use a QSqlRelationalTableModel to manage things behind the scenes.

Based on the various API docs for the classes I'm referring to, and also to these two wiki pages: QML & QSqlTableModel & QSQLQueryModel in QML it appears to be both plausible and reasonably straight forward...

The code examples bundled with QT don't help all that much, as they perform only the most basic of steps. Thus far I have successfully paraphrased the 'Books' example and ended up with a QTableView showing the books information, represented by a RelationalTableModel.

The next step is to try and re-create the tableview in QML, following the above linked wiki guides for inspiration, which is where I run straight into a brick wall...

The code for the books-style example (I am using a persistent database, formed from the initDB.h file from the books example throughout these code snippets):

#include <QSql>
#include <QSqlError>
#include <QSqlRelationalTableModel>
#include <QSqlRecord>

#include <QTableView>

QSqlError start_db() {
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","SampleData");
    db.setDatabaseName ("SampleData.dat");
    db.open ();

    return db.lastError ();
}

int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    QQmlApplicationEngine engine;

    //open connection to database
    QSqlError err = start_db();
    if (err.type ()!= QSqlError::NoError) {
        qDebug() << "Found an error whilst instantiating DB.\n" << err;
    } else {
        qDebug() << "DB Instantiation successful.";
    }

    //create a handle to database connection
    QSqlDatabase data = QSqlDatabase::database ("SampleData");
    //Create datamodel.
    QSqlRelationalTableModel *model = new QSqlRelationalTableModel(0, data);
    model->setTable ("books");
    model->setEditStrategy (QSqlTableModel::OnManualSubmit);

    // Remember the indexes of the columns
    int authorIdx = model->fieldIndex("author");
    int genreIdx = model->fieldIndex("genre");

    // Set the relations to the other database tables
    model->setRelation(authorIdx, QSqlRelation("authors", "id", "name"));
    model->setRelation(genreIdx, QSqlRelation("genres", "id", "name"));

    // Set the localized header captions
    model->setHeaderData(authorIdx, Qt::Horizontal, "Author Name");
    model->setHeaderData(genreIdx, Qt::Horizontal, "Genre");
    model->setHeaderData(model->fieldIndex("title"), Qt::Horizontal, "Title");
    model->setHeaderData(model->fieldIndex("year"), Qt::Horizontal, "Year");
    model->setHeaderData(model->fieldIndex("rating"), Qt::Horizontal, "Rating");

    //pull model data
    if(!model->select())
    {
        qDebug() << "Error when selecting data: " << model->lastError ();//.text ();
        return 1;
    }

    //connect the above model with a dummy front end -- Books demo only!
    QTableView *view = new QTableView;
    view->setModel(model);
    view->hideColumn(0); // don't show the ID
    view->show();

//    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    return app.exec();
}

The above code runs and works perfectly, I get a window with a populated table view, correctly interpreting foreign keys etc.

Perfect.

The code below is where I have ended up so far having attempted to follow the wiki articles related to putting a QML front end on the system...

main.cpp:

#include <QApplication>
#include <QQmlApplicationEngine>
#include <QQmlContext>
#include <QDebug>

#include <QSql>
#include <QSqlError>
#include <QSqlRelationalTableModel>
#include <QSqlRecord>

#include <QTableView>

#include "sqlquerymodel.h"

QSqlError start_db() {
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");/*,"SampleData");*/
    db.setDatabaseName ("SampleData.dat");
    db.open ();

    return db.lastError ();
}

int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    QQmlApplicationEngine engine;

    //open connection to database
    QSqlError err = start_db();
    if (err.type ()!= QSqlError::NoError) {
        qDebug() << "Found an error whilst instantiating DB.\n" << err;
    } else {
        qDebug() << "DB Instantiation successful.";
    }

    //Create a handle to database connection
    QSqlDatabase data = QSqlDatabase::database ();/*"SampleData");*/
    //Create datamodel.
    QLSqlTableModel *model = new QLSqlTableModel;

    qDebug() << "Model related Errors: " << model->lastError ();
    qDebug() << "Model Data connection settings: " << model->database ();
    model->setTable("books");
    model->generateRoleNames();
    model->select();
    qDebug() << "Model related Errors: " << model->lastError ();

    QQmlContext *ctxt = engine.rootContext ();
    ctxt->setContextProperty ("model", model);

    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    return app.exec();
}

sqlquerymodel.h:

#include <QSql>
#include <QSqlRelationalTableModel>


class QLSqlTableModel : public QSqlRelationalTableModel
{
    Q_OBJECT

private:
    QHash<int, QByteArray> roles;

public:
//    QLSqlTableModel(QObject *parent = 0);
//    ~QLSqlTableModel();

public:
    Q_INVOKABLE QVariant data(const QModelIndex &index, int role=Qt::DisplayRole ) const;
    void generateRoleNames();

#ifdef HAVE_QT5
    virtual QHash<int, QByteArray> roleNames() const{return roles;}
#endif
};

sqlquerymodel.cpp:

#include "sqlquerymodel.h"
#include <QSqlRecord>
#include <QSqlField>
#include <QDebug>

QVariant QLSqlTableModel::data ( const QModelIndex & index, int role ) const
{
    if(index.row() >= rowCount())
    {
        return QString("");
    }
    if(role < Qt::UserRole)
    {
        return QSqlQueryModel::data(index, role);
    }
    else
    {
        // search for relationships
        for (int i = 0; i < columnCount(); i++)
        {
            if (this->relation(i).isValid())
            {
            return record(index.row()).value(QString(roles.value(role)));
            }
        }
    // if no valid relationship was found
    return QSqlQueryModel::data(this->index(index.row(), role - Qt::UserRole - 1), Qt::DisplayRole);
    }
}
void QLSqlTableModel::generateRoleNames()
{
    roles.clear();
    int nbCols = this->columnCount();

    for (int i = 0; i < nbCols; i++)
    {
        roles[Qt::UserRole + i + 1] = QVariant(this->headerData(i, Qt::Horizontal).toString()).toByteArray();
        qDebug() << "Data Role: " << i << ": " << roles.value (Qt::UserRole + i + 1);
    }
}

main.qml:

import QtQuick 2.4
import QtQuick.Controls 1.3
import QtQuick.Window 2.2
import QtQuick.Dialogs 1.2
import QtQuick.Layouts 1.1

ApplicationWindow {
    id: sqlDemoWindow;
    title: qsTr("SQL Demo")
    width: 800;
    height: 600;
    visible: true;
    
    TableView
    {
        id: logOutput;

        //object Layout
        anchors.margins: 5;
        anchors.fill: parent;

        //Headers
        TableViewColumn
        {
            role: "title";
            title: "Title";
            width: logOutput.width / 5;
        }
        TableViewColumn
        {
            role: "author";
            title: "Author";
            width: logOutput.width / 5;
        }
        TableViewColumn
        {
            role: "genre";
            title: "Genre";
            width: logOutput.width / 5;
        }
        TableViewColumn
        {
            role: "year";
            title: "Year";
            width: logOutput.width / 5;
        }
        TableViewColumn
        {
            role: "rating";
            title: "Rating";
            width: logOutput.width / 5;
        }
        model: model

        antialiasing: true;
        alternatingRowColors: true;
    }
}

The above code is written based on the first wiki article, and runs without error, except for a warning about a property binding loop on 'model' in the QML file. I get a nice window with an appropriately titled table view in QML, with no data showing at all.

The debug output from the above is as follows:

DB Instantiation successful.

Model related Errors:  QSqlError("", "", "")
Model Data connection settings:  QSqlDatabase(driver="QSQLITE", database="SampleData.dat", host="", port=-1, user="", open=true)
Data Role:  0 :  "id"
Data Role:  1 :  "title"
Data Role:  2 :  "author"
Data Role:  3 :  "genre"
Data Role:  4 :  "year"
Data Role:  5 :  "rating"
Model related Errors:  QSqlError("", "", "")
qrc:/main.qml:16:5: QML TableView: Binding loop detected for property "model"
Debugging has finished

This suggests that the data is successfully pulled from the database, but is not correctly sent through to QML's engine.

Finally, when the above code is modified to use a non-standard database connection string (see the commented sections of the start_db function, to add a connection name to the database, and corresponding connection name when creating a handle to the database), works fine when setting up the database itself, but is not sent through to the model.

I have tried with no success to re-implement the class constructor in order to pass the connection name through to the model as per the API Docs suggestions, but have not managed to make anything work.

There also appears to be no way to set the connection string for the database, outside of the class constructor, which I think will be the easiest way to do this.

Could anyone help me get the data to display in QML, and to do so with a non-default connection string please? I will need to have multiple databases open at a later date so non-standard strings would be a major simplifier to later development efforts.

Thanks in advance for your time and sorry for the long post!

[Sam2304]

@Sam2304 The following post may be useful in the resolution of this: How to integrate QSqlTableModel with a tableview defined in qml