How to add new values to QSqlRelationTableModel?

? Offline

hey,

I'm not getting anywhere at the moment. I want to add more images to my QSqlRelationTableModel (called mImageTableModel) using an "add" button. The mImageTableModel is filled in the constructor of the mainwindow.cpp as follows and mapped via QDataWidgetMapper:

        mImageTableModel = mDatabaseManager->imageTableModel(); //mImageTableModel = QSqlRelationTableModel
        mImageFilterModel->setSourceModel(mImageTableModel); // mImageFilterModel = QSortFilerProxyModel
        mImageMapper->setModel(mImageFilterModel);
        mImageMapper->setSubmitPolicy(QDataWidgetMapper::ManualSubmit); //mImageMapper 
        mImageMapper->setItemDelegate(new ImageDelegate(mImageMapper));
        mImageMapper->addMapping(ui->imageView, mImageTableModel->fieldIndex("Bild"));
        mImageMapper->addMapping(ui->imageEditor, mImageTableModel->fieldIndex("Bild"));
        mImageMapper->addMapping(ui->galleryImageSelection, mImageTableModel->fieldIndex("ID"));

If I later click on a "Save" button the whole thing should be written to the database, if I press a "Cancel" button the whole thing should be discarded.

Now I have looked in the documentation and found the following:

bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)

Inserts the record at position row. If row is negative, the record will be appended to the end. Calls insertRows() and setRecord() internally.

Returns true if the record could be inserted, otherwise false.

Changes are submitted immediately for OnFieldChange and OnRowChange. Failure does not leave a new row in the model.

If I interpret this correctly, this is done directly on the database level and not on the mode level or?

How can I handle this now so that data is first added to the model and only when I click on the Save button the whole thing is saved? And best of all this should still run via my DatabaseManager class. Which return type must e.g. the function addImages have then? Does the complete model have to be returned and reassigned?

I would be grateful for any tips. Oh, I also tried this but it didn't work:
https://forum.qt.io/topic/18097/insert-new-rows-into-database-using-qsqltablemodel/5

Tanks for your help!
gabber

mrjj

Hi
So
model.setEditStrategy(QSqlTableModel::OnManualSubmit);
and
https://doc.qt.io/qt-6/qsqltablemodel.html#submitAll

Didi not work the way you wanted ?

? Offline

@mrjj
Thanks for your tip I had forgotten. Unfortunately, it still does not work. I post my code. When I press the "Add image" button I want to add an image:

Now in mainwindow.cpp and databasemanager.cpp constructor I do:

mImageTableModel->setEditStrategy(QSqlRelationalTableModel::OnManualSubmit);

void MainWindow::on_addImageButton_clicked()
{
    QStringList filenames = QFileDialog::getOpenFileNames(this, QString(), QDir::homePath(), "Bilder (*.png *.jpeg *.jpg)", nullptr);

    const int fungusId = ui->idEditor->text().toInt();

    if(mDatabaseManager->addImages(filenames, fungusId)){
        qDebug() << "Successfull"; // it stops here, and didn't add the images to Database
        if(mImageTableModel->submitAll()){ //later i will add this part to on_saveButton_clicked Method
            qDebug() << "Submitall successfull";
            mImageTableModel->database().commit();
        }
    }
}

My DatabaseManager::addImages looks like this:

bool DatabaseManager::addImages(const QStringList &filenames, const int &fungusId) const
{
    QSqlRecord record = mImageTableModel->record();
    foreach (QFile file, filenames){
        QFileInfo fileInfo(file.fileName());
        if (!file.open(QIODevice::ReadOnly))
            return false;
        QByteArray imageByteArray = file.readAll();

        record.setValue("Id", fungusId);
        record.setValue("Bild", imageByteArray);
        record.setValue("Dateiname", fileInfo.fileName());

        qDebug() << "Record: " << record;

        const bool insertRecord = mImageTableModel->insertRecord(-1, record);
        if(!insertRecord)
            return false;

    }
    return true;
}

And with this function I load my data in mainwindow constructor

QSqlRelationalTableModel *DatabaseManager::imageTableModel() const
{
    mImageTableModel->setTable("Pilzbilder");
    mImageTableModel->setRelation(mImageTableModel->fieldIndex("Pilz_id"), QSqlRelation("Pilze", "Id", "Id"));
    mImageTableModel->setRelation(mImageTableModel->fieldIndex("Bild_id"), QSqlRelation("Bilder", "Id", "Bild"));
    mImageTableModel->setSort(0, Qt::AscendingOrder);
    mImageTableModel->select();
    while (mImageTableModel->canFetchMore()) {
        mImageTableModel->fetchMore();
    }
    return mImageTableModel;
}

I guess that the data still has to synchronize somehow. How should mImageTableModel from MainWindow know that the data in mImageTabelModel was changed by DatabaseManager?

For debugging purposes, I had the SQL record output. This looks like the following, in case you need it for the further procedure:

Record:  QSqlRecord(3)
 0: QSqlField("Id", int, tableName: "Pilze", generated: yes, typeID: 1, autoValue: false, readOnly: false) "0" 
 1: QSqlField("Bild", QByteArray, tableName: "Bilder", generated: yes, typeID: 4, autoValue: false, readOnly: false) "" 
 2: QSqlField("Dateiname", QString, tableName: "Pilzbilder", generated: yes, typeID: 3, autoValue: false, readOnly: false) "fliegenpilz_2.jpg"

? Offline

This post is deleted!

? Offline

I am currently so far that I can insert individual images. Several images at a time do not work yet. My code currently looks like this:

void MainWindow::on_addImageButton_clicked()
{
    QStringList filenames = QFileDialog::getOpenFileNames(this, QString(), QDir::homePath(), "Bilder (*.png *.jpeg *.jpg)", nullptr);

    const int fungusId = ui->idEditor->text().toInt();

    mImageTableModel->database().transaction();

    int rowCount = mImageTableModel->rowCount();
    int imageRowCount = mImageTableModel->relationModel(1)->rowCount();
    int imageNumber = imageRowCount+1;

    if(!mImageTableModel->insertRows(rowCount, filenames.count())) {
        qDebug() << "insertRows" << mImageTableModel->lastError().text();
        return;
    }
    if(!mImageTableModel->relationModel(1)->insertRows(imageRowCount, filenames.count())) {
        qDebug() << "insertRows" << mImageTableModel->relationModel(1)->lastError().text();
        return;
    }
    for(int i=0; i<filenames.count(); i++){
        QFile file = filenames.at(i);
        QFileInfo fileInfo(file.fileName());
        if (!file.open(QIODevice::ReadOnly))
            return;
        QByteArray imageByteArray = file.readAll();
        auto image = mImageTableModel->relationModel(1)->setData(mImageTableModel->relationModel(1)->index(imageRowCount+i,1), imageByteArray, Qt::EditRole);
        mImageTableModel->relationModel(1)->submitAll();

        auto a = mImageTableModel->setData(mImageTableModel->index(rowCount+i,0), fungusId, Qt::EditRole);
        auto b = mImageTableModel->setData(mImageTableModel->index(rowCount+i,1), imageNumber, Qt::EditRole);
        auto c = mImageTableModel->setData(mImageTableModel->index(rowCount+i,2), fileInfo.fileName(), Qt::EditRole);
        qDebug() << "a: " << a << ", b: " << b << ", c: " << c << ", image: " << image;
    }
}

When I press the save button the following is executed:

void MainWindow::on_saveButton_clicked()
{
    mImageTableModel->submitAll();
    mImageTableModel->relationModel(1)->database().commit();
    mImageTableModel->database().commit();
}

When I press cancel button the following is executed, which does not work:

void MainWindow::on_cancelButton_clicked()
{
    mImageTableModel->relationModel(1)->revertAll();
    mImageTableModel->revertAll();
    mImageTableModel->relationModel(1)->database().rollback();
    mImageTableModel->database().rollback();
}

I have a guess why the whole thing doesn't work with the cancel. And unfortunately I have to insert the following code snippet in the AddImages:

        auto image = mImageTableModel->relationModel(1)->setData(mImageTableModel->relationModel(1)->index(imageRowCount+i,1), imageByteArray, Qt::EditRole);
        mImageTableModel->relationModel(1)->submitAll();

I have to execute this submitAll so that the index is created in the QSqlRelationModel, so that I can insert the whole thing into the second table in the further course.

Does anyone have an idea how else to do this? So how can I reset the data again? I haven't found a function / idea that helps me yet. I would be grateful for any tips!

mrjj

Hi
revertAll() should work but only if you didn't call submitAll() before but I think you call that in
AddImages and hence it cant undo.

? Offline

@mrjj said in How to add new values to QSqlRelationTableModel?:

Hi
revertAll() should work but only if you didn't call submitAll() before but I think you call that in
AddImages and hence it cant undo.

Yes, you are right.

However, I don't know how to get the index. The documentation of the method bool QSqlRelationalTableModel::setData(const QModelIndex &index, const QVariant &value, int role = Qt::EditRole) says the following:

"[...] For relational columns, value must be the index, not the display value. The index must also exist in the referenced table, otherwise the function returns false."

And unfortunately I don't know how to manage this without first calling submitAll of the referenced table. Do you have any idea?

JonB

@Gabber
I have glanced through your question, but not looked too deeply.

If table A has a foreign key index into table B, and table B is using a database auto-incrementing number for its PK (is that your situation?) then you first need to add a new row you wish to reference into the referenced table B. Then you should submit that. Then get its newly-generated PK, and use that to make whatever row(s) in table A point to that row in table B, and now submit table A. If you are confident you can calculate what the new incremented number will be yourself then you may be able to submit both tables in one go. I do not know whether Qt guarantees to do both submits in the required order (B follow by A) if you do them both in one go. Don't know whether this helps :)

? Offline

@JonB
Thank you for your advice.
I am now so far that it works. My code:

void MainWindow::on_addImageButton_clicked()
{
    QStringList filenames = QFileDialog::getOpenFileNames(this, QString(), QDir::homePath(), "Bilder (*.png *.jpeg *.jpg)", nullptr);
    
    const int fungusId = ui->idEditor->text().toInt();
    mImageTableModel->relationModel(1)->setEditStrategy(QSqlTableModel::OnManualSubmit);
    mImageTableModel->setEditStrategy(QSqlTableModel::OnManualSubmit);

    int rowCount = mImageTableModel->rowCount();
    int imageRowCount = mImageTableModel->relationModel(1)->rowCount();

    if(!mImageTableModel->insertRows(rowCount, filenames.count())) {
        qDebug() << "Insert row Error: " << mImageTableModel->lastError().text();
        return;
    }

    qDebug() <<"imageRowCount: " <<imageRowCount;
    for(int i=0; i<filenames.count(); i++){
        QFile file = filenames.at(i);
        QFileInfo fileInfo(file.fileName());
        if (!file.open(QIODevice::ReadOnly))
            return;
        QByteArray imageByteArray = file.readAll();

        int imageRowCount = mImageTableModel->relationModel(1)->rowCount();
        auto result = mImageTableModel->relationModel(1)->insertRow(imageRowCount);

        auto image = mImageTableModel->relationModel(1)->setData(mImageTableModel->relationModel(1)->index(imageRowCount,1), imageByteArray, Qt::EditRole);

        mImageTableModel->relationModel(1)->submitAll();

        imageRowCount = mImageTableModel->relationModel(1)->rowCount();
        QModelIndex lastIndex = mImageTableModel->relationModel(1)->index(imageRowCount - 1, 0);

        qDebug() << "lastIndex: " << lastIndex.data().toInt();


        auto a = mImageTableModel->setData(mImageTableModel->index(rowCount+i,0), fungusId, Qt::EditRole);
        auto b = mImageTableModel->setData(mImageTableModel->index(rowCount+i,1), lastIndex.data().toInt(), Qt::EditRole);
        auto c = mImageTableModel->setData(mImageTableModel->index(rowCount+i,2), fileInfo.fileName(), Qt::EditRole);
        qDebug() << "a: " << a << ", b: " << b << ", c: " << c << ", image: " << image;
        mImageTableModel->submitAll();
    }
}

The whole thing is also transferred directly to the database.

What I don't know yet: How can I undo the whole thing when I press my "Cancel" button?

JonB

@Gabber
As @mrjj said, you cannot revert once you have committed and you cannot database rollback once you have database committed. Your data has gone to storage now.

Cancel should be an alternative to Save. You are not asking to cancel now, you are asking to undo.

You would need to delete the new (record)s from the table(s).

? Offline

@JonB
Okay purely out of interest. Is there another way, when I have inserted the data with setData, to submit the whole thing to the view without calling submitAll?

Because then I could call submitAll with the "Save" button and revertAll with the "Cancel" button.

Or how can I cache the whole thing in the model and write it to the database only when I click "Save" and when I press "Cancel" reset the whole thing?

JonB

@Gabber said in How to add new values to QSqlRelationTableModel?:

when I have inserted the data with setData, to submit the whole thing to the view without calling submitAll?

I don't know what " submit the whole thing to the view" means to you. submitAll() should only be used/needed to submit all changes to the database. Just doing setData()s should be reflected in views.

? Offline

@JonB said in How to add new values to QSqlRelationTableModel?:

I don't know what " submit the whole thing to the view" means to you

Sorry for the poor explanation. Maybe a picture says more than words.

If I now click on "Bild hinzufügen" and select my images, they should automatically appear in the QLabel that currently says "Kein Bild vorhanden".

When I press the "Speichern" button it should be written to the database, when I press "Abbrechen" the freshly added images should be discarded and further on it should say "Kein Bild vorhanden" and no data should be added to database.

JonB

@Gabber
So all I can say is:

"Bild hinzufügen" => call insert...() & setData(), do not call submit...().
"Speichern" => call submit...()/ commit().
"Abbrechen" => call revert...().
"Kein Bild vorhanden" => do nothing.

This does not allow e,g, "Speichern" followed by "Abbrechen" or "Kein Bild vorhanden".

? Offline

@JonB
Yes that is exactly what I want. But now when I call insertRow() and setData() and select my images and setData always returns true the images are not displayed. It still says "Kein Bild vorhanden".

But I want to see the images directly after adding them and not when I press save, so that submitAll is called.

JonB

@Gabber said in How to add new values to QSqlRelationTableModel?:

But now when I call insertRow() and setData() and select my images and setData always returns true the images are not displayed

Then you should find out why this is the case and do whatever to address it, rather than having to submit to the database to make it work. I do not know what the exact issue is. If you have to submit and re-read to make yours work then you would have to explicitly delete from database to undo/revert, which is not desirable.

? Offline

@JonB
Unfortunately I still haven't found a solution why the model is not updated without calling submitAll().

Could this be related to this code section?

        mImageTableModel = mDatabaseManager->imageTableModel(); //mImageTableModel = QSqlRelationTableModel
        mImageTableModel->setEditStrategy(QSqlRelationalTableModel::OnManualSubmit);
        mImageFilterModel->setSourceModel(mImageTableModel); // mImageFilterModel = QSortFilerProxyModel
        mImageMapper->setModel(mImageFilterModel);
        mImageMapper->setItemDelegate(new ImageDelegate(mImageMapper));
        mImageMapper->addMapping(ui->imageView, mImageTableModel->fieldIndex("Bild"));

In my ImageDelegate the setModelData method is never called. The setEditorData is called

ImageDelegate::ImageDelegate(QObject *parent): QStyledItemDelegate(parent) {}

ImageDelegate::~ImageDelegate()
{
    qDebug() << "ImageDelegate deleted";
}

void ImageDelegate::setEditorData(QWidget *editor, const QModelIndex &index) const
{
    QLabel *label = qobject_cast<QLabel *>(editor);
    if (label) {
        QByteArray imageData = index.data(Qt::EditRole).toByteArray();
        QPixmap pixmap;
        if (pixmap.loadFromData(imageData)){
            int width = label->width();
            int height = label->height();
            label->setPixmap(pixmap.scaled(width,height,Qt::KeepAspectRatio));
        }
    }
    QStyledItemDelegate::setEditorData(editor, index);
}

void ImageDelegate::setModelData(QWidget *editor, QAbstractItemModel *model, const QModelIndex &index) const
{
    QLabel *label = qobject_cast<QLabel *>(editor);
    if (label) {
        QBuffer buffer;
        buffer.open(QIODevice::WriteOnly);
        if (label->pixmap(Qt::ReturnByValue).save(&buffer))
            model->setData(index, buffer.data(), Qt::EditRole);
    }
    QStyledItemDelegate::setEditorData(editor, index);
}

Can you please help me again? I find it very unpleasant when I have to write my pictures into the database every time and when I want to cancel I have to delete them from the database again.

The reason why I did this was this thread.