Handeling CheckBoxes in ListView via SQLite

[Nima Ghorab]

Hey guys!
I have encountred with an strange problem in QML listview and SQLite!
I have a checkbox item for each record (checked as an integer field) in database and I want to manipulate checked field whenever user select the proper checkbox item and also show in listview that the item is checked.
The problem is that whenever I select any checkboxes it doesn't select and always remain untoggled!
Here is my codes:

listmodel.h is:

// listmodel.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

listmodel.cpp is:

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

ListModel::ListModel(QObject *parent) :
    QSqlQueryModel(parent)
{
    //updateModel();
    {
        QString str; int counter = 0;
        for(const QString& tempStr: DATABASE_COLUMNS){
            if (counter != DATABASE_LENGTH - 1)
                str += tempStr + ", ";
            else str += tempStr;
            ++counter;
        }
        qDebug() << "SELECT " + str + " FROM " TABLE;
        setQuery("SELECT " + str + " FROM " TABLE);
    }
}
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);
    //setQuery("SELECT * FROM " TABLE);
}
int ListModel::getId(int row)
{
    return data(index(row, 0), Qt::UserRole + 1).toInt();
}

database.h is:

//database.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

database.cpp is:

//database.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;
    //qDebug() << "UPDATE " TABLE " SET checked = " + QString::number(selection) + " WHERE id = " + QString::number(id);
    if(!query.exec("UPDATE " TABLE " SET checked = " + 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;
//    query.prepare("DELETE FROM " TABLE " WHERE id = :ID");
//    query.bindValue(":ID", id);
    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;
    }
}

DatabaseColumns.h is:

//DatabaseColumns.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 18
#define DATABASE_ID_TYPE "INTEGER PRIMARY KEY AUTOINCREMENT"
#define DATABASE_CHECKED_TYPE "INTEGER"
#define DATABASE_COLUMNS std::array<QString, DATABASE_LENGTH>{\
                                    "id",\
                                    "checked",\
                                    "firstname",\
                                    "lastname",\
                                    "gender",\
                                    "nationality",\
                                    "town",\
                                    "mobile",\
                                    "job",\
                                    "birthdate",\
                                    "purchasedate",\
                                    "footsize",\
                                    "shoemodel",\
                                    "cost",\
                                    "address",\
                                    "automaticgrade",\
                                    "specificgrade",\
                                    "staffcode"}

#endif // DATABASECOLUMNS_H

Here is how I used it in QML:

import QtQuick 2.14
import QtQuick.Controls 2.14

Item {
    property alias contentWidth: tableView.contentWidth
    ListView {
        id: tableView
        clip: true
        property int textWidth: 100
        Component.onCompleted: contentX = Math.abs(contentItem.childrenRect.width - parent.width)
        contentWidth: 1500//contentItem.childrenRect.width
        contentHeight: 500//contentItem.childrenRect.height
        headerPositioning: ListView.OverlayHeader
        flickableDirection: Flickable.HorizontalAndVerticalFlick
        anchors.fill: parent
        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: 20
                layoutDirection: Qt.RightToLeft
                CheckBox {id: headerCheckBox; indicator.width: 20; indicator.height: 20}
                TableHeadText { text: "نام" }
                TableHeadText { text: "نام خانوادگی" }
                TableHeadText { text: "جنسیت" }
                TableHeadText { text: "همراه" }
                TableHeadText { text: "تاریخ تولد" }
                TableHeadText { text: "تاریخ خرید" }
                TableHeadText { text: "اندازه پا" }
                TableHeadText { text: "مدل کفش" }
                TableHeadText { text: "قیمت" }
            }
            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: 20
                layoutDirection: Qt.RightToLeft
                TableText { text: checked }
                CheckBox {
                    id: checkBox
                    indicator.width: 20; indicator.height: 20
                    checked: parseInt(model.checked, 10)
                    onCheckedChanged: {
                        if (checkState === Qt.Checked){
                            console.log("Currently checked!")
                            database.modifyTable(myModel.getId(tableView.currentIndex), true)
                            myModel.updateModel()
                        }else{
                            console.log("Unchecked!")
                            database.modifyTable(myModel.getId(tableView.currentIndex), false)
                            myModel.updateModel()
                        }
                    }
                }
                TableText { text: firstname }
                TableText { text: lastname }
                TableText { text: gender }
                TableText { text: mobile }
                TableText { text: birthdate }
                TableText { text: purchasedate }
                TableText { text: footsize }
                TableText { text: shoemodel }
                TableText { text: cost }
            }
        }
    }
    Button{
        text: "OK"
        onClicked: myModel.updateModel()
    }
}

Any help is greatly appreciated!
thank you so much!

[Nima Ghorab]

Can't anyone help?

[fcarney]

@Nima-Ghorab said in Handeling CheckBoxes in ListView via SQLite:

onCheckedChanged: {
if (checkState === Qt.Checked){

In my code I am checking against "checked" and not "checkedState". What is "checkState"? Also, does your code update the checked state when written to the database? I am guessing "checkState" is undefined so it does not match Qt.Checked.

[fcarney]

In other words. I think you have spelling issues.

[Nima Ghorab]

@fcarney Thank you so much!
checkedState gets an enumeration for checkbox's state.
It is no matter weather I used checkState or only checked both of them have the same problem!

[fcarney]

Did you get it working? Maybe post what you changed so other people can see how it should work.

[Nima Ghorab]

Finally I used onClicked signal rather than using onStateChanged signal!
Using onClicked signal prevents items to fall in infinite loop.
But I'm still curious for a better way...