J'ai une liste en frontend qui contient des textes à afficher firstname, lastname, city et... plus une case à cocher pour chaque enregistrement.

Je souhaite lier cet élément de case à cocher à un champ selected dans la base de données de type INTEGER.

Le problème est que je ne sais pas comment lier l'élément de case à cocher au champ de données selected dans la base de données SQLite !

Pour afficher des textes dans l'interface QML, j'ai simplement utilisé un élément Text avec une propriété text définie sur le roleid approprié, par exemple text: firstname ou text: lastname... Mais si je fais quelque chose de similaire pour l'élément de case à cocher, j'obtiendrai une boucle de liaison et le programme aura tendance à planter !

Dois-je réimplémenter la fonction setData ?

Quelqu'un peut-il m'indiquer la bonne direction?

Voici l'exemple:

Base de données.h :

#ifndef Database_H
#define Database_H
#include <QObject>
#include <QSql>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDatabase>
#include <QFile>
#include <QDate>
#include <QDebug>

class Database : public QObject
{
    Q_OBJECT
public:
    explicit Database(QObject *parent = nullptr);
    ~Database();
    void connectToDatabase();
private:
    QSqlDatabase db;
private:
    bool openDatabase();
    bool restoreDatabase();
    void closeDatabase();
    bool createTable();
public slots:
    bool inserIntoTable(const QStringList &data); // Adding entries to the table
    bool modifyTable(int id, bool selection);
    bool removeRecord(int id); // Removing records from the table on its id
};
#endif // Database_H

Base de données.cpp :

#include "database.h"
#include "DatabaseColumns.h"

Database::Database(QObject *parent) : QObject(parent){}
Database::~Database(){}

void Database::connectToDatabase()
{
    !QFile{DATABASE_NAME}.exists() ? this->restoreDatabase() : this->openDatabase();
}
bool Database::restoreDatabase()
{
    if(this->openDatabase()){
        return (this->createTable()) ? true : false;
    } else {
        qDebug() << "Failed to restore the Database";
        return false;
    }
}
bool Database::openDatabase()
{
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName(DATABASE_HOSTNAME);
    db.setDatabaseName(DATABASE_NAME);
    if(db.open()){
        return true;
    }
    return false;
}
void Database::closeDatabase()
{
    db.close();
}
bool Database::createTable()
{
    QString str;
    int counter = 0;
    for (const QString& tempStr: DATABASE_COLUMNS){
        switch (counter) {
        case 0:
            str += tempStr + " " + DATABASE_ID_TYPE ", ";
            break;
        case 1:
            str += tempStr + " " + DATABASE_CHECKED_TYPE ", ";
            break;
        case DATABASE_LENGTH - 1:
            str += tempStr + " VARCHAR(255) NOT NULL";
            break;
        default:
            str += tempStr + " VARCHAR(255) NOT NULL, ";
        }
        ++counter;
    }
    QSqlQuery query;
    if(!query.exec( "CREATE TABLE " TABLE " (" + str + ")"))
    {
        qDebug() << "Database: error of create " << TABLE;
        qDebug() << query.lastError().text();
        return false;
    }
    return true;
}
bool Database::inserIntoTable(const QStringList &data)
{
    QSqlQuery query; QString str{"INSERT INTO " TABLE " ("};
    int counter = 0;
    for (const QString& tempStr: DATABASE_COLUMNS)
    {
        switch (counter)
        {
        case 0:
            break;
        case DATABASE_LENGTH - 1:
            str += tempStr;
            break;
        default:
            str += tempStr + ", ";
        }
        ++counter;
    }
    str += ") VALUES (";
    counter = 0;
    for (const QString& tempStr: DATABASE_COLUMNS)
    {
        switch (counter)
        {
        case 0:
            break;
        case DATABASE_LENGTH - 1:
            str += ":" + tempStr + ")";
            break;
        default:
            str += ":" + tempStr + ", ";
        }
        ++counter;
    }
    query.prepare(str);
    counter = 0;
    for (const QString& tempStr: DATABASE_COLUMNS)
    {
        if (tempStr != "id") {
            query.bindValue(":" + tempStr, data[counter]);
            ++counter;
        }
    }
    if(!query.exec())
    {
        qDebug() << "error insert into " << TABLE;
        qDebug() << query.lastError().text();
        return false;
    }
    return true;
}

bool Database::modifyTable(int id, bool selection)
{
    QSqlQuery query;
    if(!query.exec("UPDATE " TABLE " SET selected = " + QString::number(selection) + " WHERE id = " + QString::number(id)))
    {
        qDebug() << "error delete row " << TABLE;
        qDebug() << query.lastError().text();
        return false;
    }
    return true;
}
bool Database::removeRecord(int id)
{
    QSqlQuery query;
    if(!query.exec("DELETE FROM " TABLE " WHERE id = " + QString::number(id)))
    {
        qDebug() << "error delete row " << TABLE;
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
}

Colonnes de la base de données.h :

#ifndef DATABASECOLUMNS_H
#define DATABASECOLUMNS_H

#include <array>
#include <QString>

#define DATABASE_HOSTNAME "localhost"
#define DATABASE_NAME "mydatabase.db"
#define TABLE "MyTable"

#define DATABASE_LENGTH 3
#define DATABASE_ID_TYPE "INTEGER PRIMARY KEY AUTOINCREMENT"
#define DATABASE_CHECKED_TYPE "INTEGER"
#define DATABASE_COLUMNS std::array<QString, DATABASE_LENGTH>{\
                                    "id",\
                                    "selected",\
                                    "firstname"}

#endif // DATABASECOLUMNS_H

Modèle de liste.h :

#ifndef LISTMODEL_H
#define LISTMODEL_H

#include <QObject>
#include <QSqlQueryModel>

class ListModel : public QSqlQueryModel
{
    Q_OBJECT
public:
    explicit ListModel(QObject *parent = nullptr);
    virtual QVariant data(const QModelIndex &index, int role = Qt::DisplayRole) const override;
protected:
    QHash<int, QByteArray> roleNames() const override;
signals:
public slots:
    void updateModel();
    int getId(int row);
};

#endif // LISTMODEL_H

Modèle de liste.cpp :

#include "listmodel.h"
#include "DatabaseColumns.h"
#include <QtDebug>
#include <QSqlError>

ListModel::ListModel(QObject *parent) :
    QSqlQueryModel(parent)
{
    updateModel();
}
QVariant ListModel::data(const QModelIndex &inputIndex, int role) const {
    int columnId = role - Qt::UserRole - 1;
    QModelIndex modelIndex = index(inputIndex.row(), columnId);
    return QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
}

QHash<int, QByteArray> ListModel::roleNames() const {
    QHash<int, QByteArray> roles;
    int i = Qt::UserRole + 1;
    for(const QString& str: DATABASE_COLUMNS){
        roles[i] = str.toUtf8();
        ++i;
    }
    return roles;
}
void ListModel::updateModel()
{
    QString str; int counter = 0;
    for(const QString& tempStr: DATABASE_COLUMNS){
        if (counter != DATABASE_LENGTH - 1)
            str += tempStr + ", ";
        else str += tempStr;
        ++counter;
    }
    setQuery("SELECT " + str + " FROM " TABLE);
}
int ListModel::getId(int row)
{
    return data(index(row, 0), Qt::UserRole + 1).toInt();
}

Main.qml:

import QtQuick 2.14
import QtQuick.Controls 2.14

ApplicationWindow {
    id: window
    visible: true
    minimumWidth: 800
    minimumHeight: 600
    TextField{
        id: name
        anchors.bottom: acceptKey.top
        anchors.horizontalCenter: parent.horizontalCenter
        anchors.margins: 100
        placeholderText: "input the name"
    }

    Button{
        id: acceptKey
        text: "Accept"
        anchors.centerIn: parent
        onClicked: {
            database.inserIntoTable([false, name.text])
            myModel.updateModel()
        }
    }
    ListView {
        id: tableView
        clip: true
        anchors.top: acceptKey.bottom
        anchors.horizontalCenter: parent.horizontalCenter
        width: 300
        height: width
        property int textWidth: 100
        contentWidth: contentItem.childrenRect.width
        contentHeight: contentItem.childrenRect.height
        headerPositioning: ListView.OverlayHeader
        flickableDirection: Flickable.HorizontalAndVerticalFlick
        highlight: Rectangle {
            color: "Blue"
            opacity: 0.2
        }
        header: Rectangle {
            property alias checkState: headerCheckBox.checkState
            height: tableHead.implicitHeight + seperator.height
            width: tableHead.implicitWidth
            z: 2
            Row {
                id: tableHead
                spacing: 100
                layoutDirection: Qt.RightToLeft
                CheckBox {id: headerCheckBox; indicator.width: 20; indicator.height: 20}
                Text {width: 50; text: "Name" }
            }
            ToolSeparator {
                id: seperator
                height: 7
                padding: 0
                topPadding: 0
                bottomPadding: 0
                anchors { top: tableHead.bottom; right: parent.right }
                orientation: Qt.Horizontal
                width: tableHead.implicitWidth
            }
        }
        model: myModel
        delegate: Item{
            width: parent.width
            height: rowId.implicitHeight
            MouseArea{
                anchors.fill: parent
                onClicked: tableView.currentIndex = index
            }
            Row {
                id: rowId
                spacing: 100
                layoutDirection: Qt.RightToLeft
                CheckBox {
                    id: checkBox
                    indicator.width: 20; indicator.height: 20
                    onCheckStateChanged: {
                        if (checkState === Qt.Checked){
                            console.log("Currently checked!")
                            database.modifyTable(myModel.getId(tableView.currentIndex), true)
                        }else{
                            console.log("Unchecked!")
                            database.modifyTable(myModel.getId(tableView.currentIndex), false)
                        }
                        myModel.updateModel()
                    }
                }
                Text {
                    width: 50
                    text: firstname
                }
            }
        }
    }
}

Et enfin main.cpp :

#include <QGuiApplication>
#include <QQmlApplicationEngine>
#include <QQmlContext>
#include <QDebug>
#include "database.h"
#include "listmodel.h"

int main(int argc, char *argv[])
{
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);
    QGuiApplication app(argc, argv);

    QQmlApplicationEngine engine;
    Database database;
    database.connectToDatabase();
    ListModel model;
    engine.rootContext()->setContextProperty("myModel", &model);
    engine.rootContext()->setContextProperty("database", &database);

    const QUrl url(QStringLiteral("qrc:/main.qml"));
    QObject::connect(&engine, &QQmlApplicationEngine::objectCreated,
                     &app, [url](QObject *obj, const QUrl &objUrl) {
        if (!obj && url == objUrl)
            QCoreApplication::exit(-1);
    }, Qt::QueuedConnection);
    engine.load(url);

    return app.exec();
}

Le problème est que lorsque je sélectionne la case à cocher pour chaque enregistrement, ils ne sont pas activés et restent désactivés pour toujours !

0
Nima Ghorab 7 févr. 2020 à 13:01

1 réponse

Meilleure réponse

Sur la base de ma réponse précédente, j'ai créé le modèle SqlQueryModel qui crée des rôles en fonction de la requête, ce qui facilite la gestion du model de QML, d'autre part j'ai créé SqlQueryManager, qui est un QObject qui permet de gérer facilement les requêtes de QML. Compte tenu de ce qui précède, la solution est :

main.cpp

#include <QGuiApplication>

#include <QQmlApplicationEngine>
#include <QQmlContext>

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlQueryModel>
#include <QSqlRecord>

#include <QDebug>

namespace CONSTANTS {
constexpr char TABLE_NAME[] = "MyTable";
constexpr char DATABASE_NAME[] = "mydatabase.db";
}

static bool createConnection() {
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(CONSTANTS::DATABASE_NAME);
    if (!db.open()) {
        qDebug()<< QObject::tr("Cannot open database") <<
                   QObject::tr("Unable to establish a database connection.\n"
                               "This example needs SQLite support. Please read "
                               "the Qt SQL driver documentation for information how "
                               "to build it.\n\n"
                               "Click Cancel to exit.");
        return false;
    }
    // create table
    QSqlQuery query;
    if(!query.exec(QString(R"(CREATE TABLE IF NOT EXISTS %1 (
                           id INTEGER PRIMARY KEY AUTOINCREMENT,
                           firstname TEXT,
                           selected INTEGER NOT NULL CHECK (selected IN (0,1))
                           ))").arg(CONSTANTS::TABLE_NAME))){
        qDebug() << query.lastError().text();
        return false;
    }
    return true;
}

class SqlQueryModel : public QSqlQueryModel
{
    Q_OBJECT
public:
    using QSqlQueryModel::QSqlQueryModel;
    QHash<int, QByteArray> roleNames() const{
        QHash<int, QByteArray> roles;
        for (int i = 0; i < record().count(); i ++)
            roles.insert(Qt::UserRole + i + 1, record().fieldName(i).toUtf8());
        return roles;
    }
    QVariant data(const QModelIndex &index, int role) const{
        if (!index.isValid())
            return QVariant();
        if (role < Qt::UserRole)
            return QSqlQueryModel::data(index, role);
        int columnIdx = role - Qt::UserRole - 1;
        QModelIndex modelIndex = this->index(index.row(), columnIdx);
        return QSqlQueryModel::data(modelIndex, Qt::DisplayRole);
    }
    Q_INVOKABLE void update(){
        setQuery(query().lastQuery());
    }
};

class SqlQueryManager: public QObject{
    Q_OBJECT
public:
    using QObject::QObject;
    Q_INVOKABLE static bool exec(const QString & query){
        QSqlQuery q(query);
        if(!q.exec()){
            qDebug() << q.lastError().text();
            return false;
        }
        return true;
    }
    Q_INVOKABLE bool exec(){
        if(!m_query.exec()){
            qDebug() << m_query.lastError().text();
            return false;
        }
        return true;
    }
    Q_INVOKABLE bool prepare(const QString &query){
        return m_query.prepare(query);
    }
    Q_INVOKABLE void bindValue(const QString &placeholder, const QVariant &val){
        m_query.bindValue(placeholder, val);
    }
    Q_INVOKABLE void bindValue(int pos, const QVariant &val){
        m_query.bindValue(pos, val);
    }
    Q_INVOKABLE void addBindValue(const QVariant &val){
        m_query.addBindValue(val);
    }
private:
    QSqlQuery m_query;
};

int main(int argc, char *argv[])
{
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QGuiApplication app(argc, argv);

    if(!createConnection()){
        return -1;
    }
    SqlQueryModel model;
    model.setQuery(QStringLiteral("SELECT * FROM %1").arg(CONSTANTS::TABLE_NAME));

    SqlQueryManager manager;

    QQmlApplicationEngine engine;
    engine.rootContext()->setContextProperty("queryModel", &model);
    engine.rootContext()->setContextProperty("queryManager", &manager);
    engine.rootContext()->setContextProperty("tableName", CONSTANTS::TABLE_NAME);

    const QUrl url(QStringLiteral("qrc:/main.qml"));
    QObject::connect(&engine, &QQmlApplicationEngine::objectCreated,
                     &app, [url](QObject *obj, const QUrl &objUrl) {
        if (!obj && url == objUrl)
            QCoreApplication::exit(-1);
    }, Qt::QueuedConnection);
    engine.load(url);

    return app.exec();
}

#include "main.moc"

main.qml

import QtQuick 2.14
import QtQuick.Controls 2.14

ApplicationWindow {
    id: window
    visible: true
    minimumWidth: 800
    minimumHeight: 600
    TextField{
        id: name
        anchors.bottom: acceptKey.top
        anchors.horizontalCenter: parent.horizontalCenter
        anchors.margins: 100
        placeholderText: "input the name"
    }

    Button{
        id: acceptKey
        text: "Accept"
        anchors.centerIn: parent
        onClicked: {
            queryManager.prepare("INSERT INTO " + tableName + "(selected, firstname) VALUES (?, ?)")
            queryManager.addBindValue(false);
            queryManager.addBindValue(name.text)
            queryManager.exec()
            queryModel.update()
            name.text = ""
        }
    }
    ListView {
        id: tableView
        clip: true
        anchors.top: acceptKey.bottom
        anchors.horizontalCenter: parent.horizontalCenter
        width: 300
        height: width
        property int textWidth: 100
        contentWidth: contentItem.childrenRect.width
        contentHeight: contentItem.childrenRect.height
        headerPositioning: ListView.OverlayHeader
        flickableDirection: Flickable.HorizontalAndVerticalFlick
        highlight: Rectangle {
            color: "Blue"
            opacity: 0.2
        }
        header: Rectangle {
            property alias checkState: headerCheckBox.checkState
            height: tableHead.implicitHeight + seperator.height
            width: tableHead.implicitWidth
            z: 2
            Row {
                id: tableHead
                spacing: 100
                layoutDirection: Qt.RightToLeft
                CheckBox {id: headerCheckBox; indicator.width: 20; indicator.height: 20}
                Text {width: 50; text: "Name" }
            }
            ToolSeparator {
                id: seperator
                height: 7
                padding: 0
                topPadding: 0
                bottomPadding: 0
                anchors { top: tableHead.bottom; right: parent.right }
                orientation: Qt.Horizontal
                width: tableHead.implicitWidth
            }
        }
        model: queryModel
        delegate: Item{
            width: parent.width
            height: rowId.implicitHeight
            MouseArea{
                anchors.fill: parent
                onClicked: tableView.currentIndex = index
            }
            Row {
                id: rowId
                spacing: 100
                layoutDirection: Qt.RightToLeft
                CheckBox {
                    id: checkBox
                    checked: model.selected
                    indicator.width: 20; indicator.height: 20
                    onCheckStateChanged: {
                        if(model.selected != checked){ // @disable-check M126
                            queryManager.prepare("UPDATE " + tableName + " SET selected = ? WHERE id = ?")
                            queryManager.addBindValue(checked);
                            queryManager.addBindValue(model.id);
                            queryManager.exec();
                            queryModel.update();
                        }
                    }
                }
                Text {
                    width: 50
                    text: model.firstname
                }
            }
        }
    }
}
1
eyllanesc 8 févr. 2020 à 00:16