[Solved] QSqlQueryModel for QML TableView



  • Hi there,

    the last days i searched for a solution to display a QSqlQueryModel in a QML TableView. I found these really helpful links:

    "QML and QSqlTableModel":http://qt-project.org/wiki/QML_and_QSqlTableModel
    "How to use a QSqlQueryModel":http://qt-project.org/wiki/How_to_use_a_QSqlQueryModel_in_QML
    "Using QtSql database access":http://forum.dailymobile.net/forums/qt-development/92556-using-qtsql-qml-database-access.html

    So i derived my own class from QSqlQueryModel and reimplemented "roleNames()":http://doc.qt.io/qt-5/qabstractitemmodel.html#roleNames because "setRoleNames()":http://doc.qt.io/qt-5/qabstractitemmodel-obsolete.html#setRoleNames is obsolete in Qt 5.4.

    HEADER
    @class SQLiteModel : public QSqlQueryModel {
    Q_OBJECT

    public:
    explicit SQLiteModel(QObject *parent = NULL);
    ~SQLiteModel();

    Q_INVOKABLE bool setDatabase(const QString &database);
    Q_INVOKABLE bool setQuery(const QString &query);
    
    virtual QHash<int, QByteArray> ​roleNames() const;
    
    static void declareQML();
    

    private:
    QSqlDatabase _db;

    static QString _connection();
    

    };@

    SOURCE
    @SQLiteModel::SQLiteModel(QObject *parent) :
    QSqlQueryModel(parent) {
    _db = QSqlDatabase::addDatabase("QSQLITE", SQLiteModel::_connection());
    }

    SQLiteModel::~SQLiteModel() {
    if (_db.open()) _db.close();
    }

    bool SQLiteModel::setDatabase(const QString &database) {
    if (!QFileInfo(database).exists()) {
    qWarning() << "SQLiteModel::setDatabase() - Could not find database '" + database + "'";
    return false;
    }

    if (_db.open()) {
        _db.close();
        this->clear();
    }
    
    _db.setDatabaseName(database);
    
    if (!_db.open()) {
        qWarning() << "SQLiteModel::setDatabase() -" << database;
        return false;
    }
    
    return true;
    

    }

    bool SQLiteModel::setQuery(const QString &query) {
    QSqlQueryModel::setQuery(query, _db);

    if (this->query().record().isEmpty()) {
        qWarning() << "SQLiteModel::setQuery() -" << this->query().lastError();
        return false;
    }
    
    return true;
    

    }

    QHash<int, QByteArray> SQLiteModel::​roleNames() const {
    QHash<int, QByteArray> roles;
    qWarning() << "SQLiteModel::roleNames()";

    for (int i = 0; i < this->record().count(); i++) {
        roles[Qt::UserRole + i + 1] = this->record().fieldName(i).toLocal8Bit();
    }
    
    return roles;
    

    }

    void SQLiteModel::declareQML() {
    qmlRegisterType<SQLiteModel>("Extensions", 1, 0, "SQLiteModel");
    }

    QString SQLiteModel::_connection() {
    static int ID = 0; ID++;
    return "SQLiteModelConnection" + QString::number(ID);
    }@

    But the QML TableView does not show anything. I know the class is working, because a QTableView could display the model. (Example for a SQLite database with 'Components' table and 'ID', 'Name', 'Description' and 'Value' header)

    MAIN
    @int main(int argc, char *argv[]) {
    QApplication app(argc, argv);

    SQLiteModel::declareQML();
    
    SQLiteModel *model = new SQLiteModel;
    model->setDatabase("../../database");
    model->setQuery("select * from Components");
    
    QTableView *view = new QTableView;
    view->setModel(model);
    view->show();
    
    QQmlApplicationEngine engine;
    engine.rootContext()->setContextProperty("SQLite", model);
    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    return app.exec&#40;&#41;;
    

    }@

    QML
    @ApplicationWindow {
    title: qsTr("SQL Demo")
    width: 640
    height: 480
    visible: true

    minimumWidth: 640
    minimumHeight: 480
    
    TableView {
        id: tableview
        anchors.fill: parent
        anchors.margins: 10
    
        TableViewColumn { role: "ID" ; title: "ID"; visible: true }
        TableViewColumn { role: "Name" ; title: "Name" }
        TableViewColumn { role: "Description" ; title: "Description" }
        TableViewColumn { role: "Value" ; title: "Value" }
    
        model: SQLite
    }
    

    }@

    Any suggestions? I also tried to add a Q_INVOKABLE before the roleNames() function, but it didn't changed anything.

    Regards,
    MrFog


  • Moderators

    Hi,

    For QML TableView, you also need to reimplement data() and return values as per the roles in it. This is because QML's model calls data() with the roles given by roleNames().

    bq. The header sections are attached to values in the model by defining the model role they attach to. Each property in the model will then be shown in their corresponding column.



  • Thanks for the reply! I added the following code to the class:

    HEADER
    @QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const;@

    SOURCE
    @QVariant SQLiteModel::data(const QModelIndex &index, int role) const {
    qWarning() << "SQLiteModel::data()";
    QVariant value = QSqlQueryModel::data(index, role);

    if (role < Qt::UserRole) {
        value = QSqlQueryModel::data(index, role);
    } else {
        int columnIdx = role - Qt::UserRole - 1;
        QModelIndex modelIndex = this->index(index.row(), columnIdx);
        value = QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
    }
    
    return value;
    

    }@

    But it did not change anything. I think the data() and roleNames() function does not get called, because there should be a console output. I tried to add the 'override' specifier to data() and roleNames(). It's OK for data(), but not for roleNames() because it isn't a virtual function?


  • Moderators

    Ok. It could be that the roles in QML are not matching to that defined in model. Have you made sure they are exactly the same ? They are case-sensitive.



  • I'm pretty sure. The roleNames() function creates the following QHash:

    @
    QHash((259, "Description")(258, "Name")(260, "Value")(257, "ID"))
    @

    With this short example i could print out the complete table:

    @
    QHash<int, QByteArray> roles;

    for (int i = 0; i < this->record().count(); i++) {
    roles[Qt::UserRole + i + 1] = this->record().fieldName(i).toLocal8Bit();
    }

    qWarning() << roles;

    QStringList names;
    names.append("ID");
    names.append("Name");
    names.append("Description");
    names.append("Value");

    for (int i = 0; i < this->rowCount(); i++) {
    for (int j = 0; j < names.count(); j++) {
    int row = i;
    int col = roles.key(names[j].toLocal8Bit()) - Qt::UserRole - 1;

        QModelIndex index = this->index(row, col);
    
        qWarning() << QSqlQueryModel::data(index, Qt::DisplayRole);
    }
    

    }
    @

    But i think the real problem is, that data() and roleNames() does not get called.


  • Moderators

    Can you try out following example ?

    sqlitemodel.cpp

    SQLiteModel::SQLiteModel(QObject *parent)
        :QSqlQueryModel(parent)
    {
        openDb();
    }
    
    SQLiteModel::~SQLiteModel()
    {
    
    }
    
    bool SQLiteModel::openDb()
    {
        db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName("/root/sample.db");
        return db.open();
    }
    
    bool SQLiteModel::setQuery(const QString &query)
    {
        QSqlQueryModel::setQuery(query);
    
        if (this->query().record().isEmpty()) {
            qWarning() << "SQLiteModel::setQuery() -" << this->query().lastError();
            return false;
        }
        m_recordCount = record().count();
        qDebug() << "Records: " << m_recordCount;
    
        return true;
    }
    
    QHash<int, QByteArray> SQLiteModel::roleNames() const
    {
        QHash<int, QByteArray> roles;// = QAbstractTableModel::roleNames();
        for( int i = 0; i < record().count(); i++) {
            roles[Qt::UserRole + i + 1] = record().fieldName(i).toLatin1();
        }
        return roles;
    }
    
    QVariant SQLiteModel::data(const QModelIndex &index, int role) const
    {
        QVariant value = QSqlQueryModel::data(index, role);
        if(role < Qt::UserRole)
        {
            value = QSqlQueryModel::data(index, role);
        }
        else
        {
            int columnIdx = role - Qt::UserRole - 1;
            QModelIndex modelIndex = this->index(index.row(), columnIdx);
            value = QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
        }
        return value;
    }
    

    Initialize it as

    SQLiteModel *sqmodel = new SQLiteModel(this);
    sqmodel->setQuery("select * from sample;");
    ...setContextProperty("tableModel",sqmodel);
    

    main.qml

    Item {
        TableView {
            anchors.fill: parent
            model: tableModel
            TableViewColumn{ role: "name"  ; title: "Name" ; width: 100 }
            TableViewColumn{ role: "age"  ; title: "Age" ; width: 100 }
            TableViewColumn{ role: "gender" ; title: "Gender" ; width: 100 }
        }
    }
    

    DB looks like:

    name|age|gender
    abc|25|Male
    xyz|20|Female
    pqr|12|Male
    


  • Its working! :-D

    But i don't know why... now i could override roleNames(), which i couldn't do before...? Crazy...

    SQLiteModel.h
    @
    class SQLiteModel : public QSqlQueryModel {
    Q_OBJECT

    public:
    explicit SQLiteModel(QObject *parent = NULL);
    ~SQLiteModel();

    Q_INVOKABLE bool setDatabase(const QString &database);
    Q_INVOKABLE bool setQuery(const QString &query);
    
    QHash<int, QByteArray> roleNames() const override;
    QVariant data(const QModelIndex &index, int role=Qt::DisplayRole) const override;
    
    static void declareQML();
    

    private:
    QSqlDatabase _db;

    static QString _connection();
    

    };
    @

    SQLiteModel.cpp
    @
    SQLiteModel::SQLiteModel(QObject *parent)
    : QSqlQueryModel(parent) {
    _db = QSqlDatabase::addDatabase("QSQLITE", _connection());
    }

    SQLiteModel::~SQLiteModel() {
    if (_db.isOpen()) _db.close();
    }

    bool SQLiteModel::setDatabase(const QString &database) {
    if (!QFileInfo(database).exists()) {
    qWarning() << "SQLiteModel::setDatabase() - Could not find database '" + database + "'";
    return false;
    }

    if (_db.isOpen()) {
        _db.close();
        this->clear();
    }
    
    _db.setDatabaseName(database);
    
    if (!_db.open()) {
        qWarning() << "SQLiteModel::setDatabase() -" << _db.lastError().text();
        return false;
    }
    
    return true;
    

    }

    bool SQLiteModel::setQuery(const QString &query) {
    QSqlQueryModel::setQuery(query, _db);

    if (this->query().record().isEmpty()) {
        qWarning() << "SQLiteModel::setQuery() -" << this->query().lastError();
        return false;
    }
    
    return true;
    

    }

    QHash<int, QByteArray> SQLiteModel::roleNames() const {
    QHash<int, QByteArray> roles;

    for ( int i = 0; i < this->record().count(); i++) {
        roles[Qt::UserRole + i + 1] = this->record().fieldName(i).toLocal8Bit();
    }
    
    return roles;
    

    }

    QVariant SQLiteModel::data(const QModelIndex &index, int role) const {
    QVariant value = QSqlQueryModel::data(index, role);

    if (role < Qt::UserRole)
        value = QSqlQueryModel::data(index, role);
    else {
        int row = index.row();
        int col = role - Qt::UserRole - 1;
    
        QModelIndex modelIndex = this->index(row, col);
    
        value = QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
    }
    
    return value;
    

    }

    void SQLiteModel::declareQML() {
    qmlRegisterType<SQLiteModel>("Extensions", 1, 0, "SQLiteModel");
    }

    QString SQLiteModel::_connection() {
    static int ID = 0; ID++;
    return "SQLiteModelConnection" + QString::number(ID);
    }
    @

    main.cpp
    @
    #include "SQLiteModel.h"

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

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

    }
    @

    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

    import Extensions 1.0

    ApplicationWindow {
    title: qsTr("SQLite Demo")
    width: 640
    height: 480

    minimumWidth: 640
    minimumHeight: 480
    
    visible: true
    
    TableView {
        id: tableview
        anchors.fill: parent
        anchors.margins: 10
    
        TableViewColumn { role: "ID" ; title: "ID"; visible: true }
        TableViewColumn { role: "Name" ; title: "Name" }
        TableViewColumn { role: "Description" ; title: "Description" }
        TableViewColumn { role: "Value" ; title: "Value" }
    }
    
    SQLiteModel {
        id: model
    
        Component.onCompleted: {
            model.setDatabase("../../database");
            model.setQuery("select * from Components");
    
            tableview.model = model;
        }
    }
    

    }
    @

    SQLite database named database including Components table with ID, Name, Description and Value header.

    Big thanks to p3c0 for his help!!!!


  • Moderators

    You're Welcome :)
    You can mark the post as solved by editing the post title and prepending [solved] so that others would come to know. I have already tagged it as solved.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.