Adding data throught QIdentityModel to QSqlTableModel fails

sandro4912

I'm really confused about this issue.

I have a model based on QSqlTableModel :

class QuestionSqlTableModel : public QSqlTableModel
{
    Q_OBJECT
public:
    explicit QuestionSqlTableModel(QObject *parent = nullptr,
                                   const QSqlDatabase &db = QSqlDatabase());

};

QuestionSqlTableModel::QuestionSqlTableModel(
    QObject *parent, const QSqlDatabase &db)
    : QSqlTableModel{parent, db}
{
    setEditStrategy(EditStrategy::OnFieldChange);
}

Now to map columns to roles to use in qml I install this model into a model derrived from QIdentityProxyModel.

Everything seems to work fine. I can read the data from an existing database and display it in qml through the QIdentityProxyModel.

Now from QML I want to add a new row with data. For that I added a method in the child of QIdentityProxyModel.

The whole code for this model:

#include <QObject>
#include <QIdentityProxyModel>

class QuestionsProxyModel : public QIdentityProxyModel
{
    Q_OBJECT

    enum questionRoles {
        idRole = Qt::UserRole + 1,
        askedQuestionRole,
        answer1Role,
        answer2Role,
        answer3Role,
        answer4Role,
        correctAnswerRole,
        pictureRole
    };

public:
    QuestionsProxyModel(QObject* parent = nullptr);

    QHash<int, QByteArray> roleNames() const override;

    Q_INVOKABLE QVariant data(const QModelIndex &index, int role) const override;

    Q_INVOKABLE bool addNewEntry(const QString& askedQuestion,
        const QString& answer1,
        const QString& answer2,
        const QString& answer3,
        const QString& answer4,
        int correctAnswer,
        const QString& picturePath);
private:
    QModelIndex mapIndex(const QModelIndex &index, int role) const;
};


#include <QDebug>
#include <QPixmap>
#include <QBuffer>

#include <QByteArray>

namespace QuestionColumn
{
    static constexpr auto id = 0;
    static constexpr auto askedQuestion = 1;
    static constexpr auto answer1 = 2;
    static constexpr auto answer2 = 3;
    static constexpr auto answer3 = 4;
    static constexpr auto answer4 = 5;
    static constexpr auto correct_answer = 6;
    static constexpr auto picture = 7;
}


QuestionsProxyModel::QuestionsProxyModel(QObject* parent)
    :QIdentityProxyModel(parent)
{
}

QHash<int, QByteArray> QuestionsProxyModel::roleNames() const
{
    QHash <int,QByteArray> roles;
    roles[idRole] = "id";
    roles[askedQuestionRole] = "askedQuestion";
    roles[answer1Role] = "answer1";
    roles[answer2Role] = "answer2";
    roles[answer3Role] = "answer3";
    roles[answer4Role] = "answer4";
    roles[correctAnswerRole] = "correctAnswer";
    roles[pictureRole] = "picture";

    return roles;
}

QVariant QuestionsProxyModel::data(const QModelIndex &index, int role) const
{
    QModelIndex newIndex = mapIndex(index, role);
    if (role == idRole
            || role == askedQuestionRole
            || role == answer1Role
            || role == answer2Role
            || role == answer3Role
            || role == answer4Role
            || role == correctAnswerRole
            || role == pictureRole) {

        return QIdentityProxyModel::data(newIndex, Qt::DisplayRole);
    }
    return QIdentityProxyModel::data(newIndex, role);
}

bool QuestionsProxyModel::addNewEntry(const QString &askedQuestion,
                                    const QString &answer1,
                                    const QString &answer2,
                                    const QString &answer3,
                                    const QString &answer4,
                                    int correctAnswer,
                                    const QString &picturePath)
{
    Q_ASSERT(!askedQuestion.isEmpty());
    Q_ASSERT(!answer1.isEmpty());
    Q_ASSERT(!answer2.isEmpty());
    Q_ASSERT(!answer3.isEmpty());
    Q_ASSERT(!answer4.isEmpty());
    Q_ASSERT(correctAnswer >= 1 && correctAnswer <= 4);


    auto newRow = rowCount();

    if(!insertRow(newRow, QModelIndex{})) {
        return false;
    }
    if(!setData(index(newRow, QuestionColumn::id), newRow + 1)) {
        removeRow(newRow);
        return false;
    }
    if(!setData(index(newRow, QuestionColumn::askedQuestion),
                           askedQuestion)) {
        removeRow(newRow);
        return false;
    }
    if(!setData(index(newRow, QuestionColumn::answer1), answer1)) {
        removeRow(newRow);
        return false;
    }
    if(!setData(index(newRow, QuestionColumn::answer2), answer2)) {
        removeRow(newRow);
        return false;
    }
    if(!setData(index(newRow, QuestionColumn::answer3), answer3)) {
        removeRow(newRow);
        return false;
    }
    if(!setData(index(newRow, QuestionColumn::answer4), answer4)) {
        removeRow(newRow);
        return false;
    }
    if(!setData(index(newRow, QuestionColumn::correct_answer), correctAnswer)) {
        removeRow(newRow);
        return false;
    }
    if(!setData(index(newRow, QuestionColumn::picture), picturePath)) {
        removeRow(newRow);
        return false;
    }
    return true;
}

QModelIndex QuestionsProxyModel::mapIndex(const QModelIndex &source, int role) const
{
    switch(role) {
    case idRole:
        return createIndex(source.row(), QuestionColumn::id);
    case askedQuestionRole:
        return createIndex(source.row(), QuestionColumn::askedQuestion);
    case answer1Role:
        return createIndex(source.row(), QuestionColumn::answer1);
    case answer2Role:
        return createIndex(source.row(), QuestionColumn::answer2);
    case answer3Role:
        return createIndex(source.row(), QuestionColumn::answer3);
    case answer4Role:
        return createIndex(source.row(), QuestionColumn::answer4);
    case correctAnswerRole:
        return createIndex(source.row(), QuestionColumn::correct_answer);
    case pictureRole:
        return createIndex(source.row(), QuestionColumn::picture);
    }
    return source;
}

Now if I use the method QuestionsProxyModel::addNewEntry I would expect that all this data does get added to the SQL database but it doesn't.

The strange thing is in the View in QML I can see the added data but on closing the application It is not stored in the database. Do I have to do something in addion of of using insertRow and setData to save to the database?

Also addNewEntry only works the first time. The second time insertRow simply returns false.

sandro4912

I found that the data is only saved to the database of QSqlTableModel when submit is called.

So I added to the end of addNewEntry of QuestionsProxyModel:

auto sqlModel = qobject_cast<QSqlTableModel*>(sourceModel());
if(sqlModel) {
    sqlModel->submit();
}

I find this solution a bit hacky. At least if the underlying model is not a QSqlTableModel the QuestionsProxyModel would still work correct.

Christian Ehrlicher

Did you already took a look at QSqlTableModel::EditStrategy ?

sandro4912

@sandro4912 said in Adding data throught QIdentityModel to QSqlTableModel fails:

setEditStrategy(EditStrategy::OnFieldChange);

Yes I even set in the code above:

setEditStrategy(EditStrategy::OnFieldChange);

in the constructor. But it looks like calling setData alone in the Proxy does not save to the database. Except I add submit

JonB

@sandro4912
So far as I can see, you have written a data() override, to call mapIndex() etc., but have not written a similar setData() method?

sandro4912

@JonB

The missing setData seems to have solved half the puzzle. It was really silly to not implement it. Now I can edit and save the edit on single cells like this:

bool QuestionsProxyModel::setData(
        const QModelIndex &index, const QVariant &value, int role)
{
    QModelIndex newIndex = mapIndex(index, role);

    if (role == idRole
            || role == askedQuestionRole
            || role == answer1Role
            || role == answer2Role
            || role == answer3Role
            || role == answer4Role
            || role == correctAnswerRole
            || role == pictureRole) {

        return QIdentityProxyModel::setData(newIndex, value, Qt::EditRole);
    }
    return QIdentityProxyModel::setData(newIndex, value, role);
}

void QuestionsProxyModel::edit(int row, const QVariant &value,
                               const QString &role)
{
    setData(createIndex(row,0), value, roleNames().key(role.toUtf8()));
}

This works well in the SQLTableModel the EditStrategy is set like this:

QuestionSqlTableModel::QuestionSqlTableModel(
    QObject *parent, const QSqlDatabase &db)
    : QSqlTableModel{parent, db}
{
    setTable("questions");
    setSort(QuestionColumn::id, Qt::AscendingOrder);
    select();
    setEditStrategy(EditStrategy::OnFieldChange);
}

What does not work is adding an how new row an populate it like this:

bool QuestionsProxyModel::addNewEntry(const QString &askedQuestion,
                                    const QString &answer1,
                                    const QString &answer2,
                                    const QString &answer3,
                                    const QString &answer4,
                                    int correctAnswer,
                                    const QString &picturePath)
{
    Q_ASSERT(!askedQuestion.isEmpty());
    Q_ASSERT(!answer1.isEmpty());
    Q_ASSERT(!answer2.isEmpty());
    Q_ASSERT(!answer3.isEmpty());
    Q_ASSERT(!answer4.isEmpty());
    Q_ASSERT(correctAnswer >= 1 && correctAnswer <= 4);


    auto newRow = rowCount();

    if(!insertRows(newRow, 1)) {
        return false;
    }
    if(!setData(createIndex(newRow, QuestionColumn::id), newRow + 1)) {
        removeRows(newRow, 1);
        return false;
    }
    if(!setData(createIndex(newRow, QuestionColumn::askedQuestion),
                           askedQuestion)) {
        removeRows(newRow, 1);
        return false;
    }
    if(!setData(createIndex(newRow, QuestionColumn::answer1), answer1)) {
        removeRows(newRow, 1);
        return false;
    }
    if(!setData(createIndex(newRow, QuestionColumn::answer2), answer2)) {
        removeRows(newRow, 1);
        return false;
    }
    if(!setData(createIndex(newRow, QuestionColumn::answer3), answer3)) {
        removeRows(newRow, 1);
        return false;
    }
    if(!setData(createIndex(newRow, QuestionColumn::answer4), answer4)) {
        removeRows(newRow, 1);
        return false;
    }
    if(!setData(createIndex(newRow, QuestionColumn::correct_answer), correctAnswer)) {
        removeRows(newRow, 1);
        return false;
    }
    if(!setData(createIndex(newRow, QuestionColumn::picture), picturePath)) {
        removeRows(newRow, 1);
        return false;
    }

    return true;
}

Here I still have to force a submit to make it work. Why is that so? Any Idea how I could prevent that? I really don't get were is the mistake here.

JonB

@sandro4912
EditStrategy determines when UPDATE SET column = value statements are sent to the database on existing rows. Wouldn't make sense on a new which has yet to be INSERTed. You get your column values all right with the setData()s before you let it submit the INSERT. In fact docs state:

Note: To prevent inserting only partly initialized rows into the database, OnFieldChange will behave like OnRowChange for newly inserted rows.

To make your code nicer/smaller: because you are using QSqlTableModel you can use bool QSqlTableModel::setRecord(int row, const QSqlRecord &values) to compose your rows for insert/update in one go instead of one setData() at a time. And use QSqlRecord QSqlTableModel::record(int row) const to read records without needing multiple data() calls.

sandro4912

@JonB

Im aware there exists these QSQLTableModel methods. I thougth I could stop using them when adding the proxy model. The problem I see is this way the proxy only works with QSQLTableModel and not with any other model.

But I guess regarding to the database it is alot cleaner because it reduces the queries like you said.

JonB

@sandro4912
I think I see what you mean. I was not thinking about your use of a QIdentityProxyModel. I have only ever done updates directly on the source model of a proxy model, not through the proxy itself. Take my comments about record in that light, maybe not appropriate for you.

I still think my explanation of the need to submit manually when inserting a new row holds, regardless.

sandro4912

@JonB

Yes following your explanation with the EditStrategy on inserting new rows the only way to get it to work I guess is to manually call submit on insert. Like I already do now. Good thing is if is not a SQLTableModel the submit will not be called since the qobject_cast fails.