[SOLVED] Updating QSqlRecord During QSqlTableModel::beforeUpdate Causes Blank Row In QTableView

[Beacon11]

Hey all.

I have a Qt5 program that utilizes an Sqlite3 database hooked up to a QSqlTableModel, which is handed to a QTableView. The model is editable via the QTableView with the default submission options (I have to leave focus before changes are committed). However, I needed to intercept changes made in the QTableView and possibly change them, so I created a slot connected to the QSqlTableModel's beforeUpdate(int,QSqlRecord&) signal.

@
QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
database.setDatabaseName("test.sqlite3");
database.open();

QSqlQuery query;
query.exec("CREATE TABLE example (testing text)");

QSqlTableModel *sqlTableModel = new QSqlTableModel(this);
sqlTableModel->setTable("example");
sqlTableModel->select();
sqlTableModel->setHeaderData(0, Qt::Horizontal, tr("Testing"));

connect(sqlTableModel, SIGNAL(beforeUpdate(int,QSqlRecord&)), SLOT(handleBeforeUpdate(int,QSqlRecord&)));

// QTableView
m_ui->tableView->setModel(sqlTableModel);
@

And the slot:

@
void MainWindow::handleBeforeUpdate(int row, QSqlRecord &record)
{
Q_UNUSED(row);

record.setValue("testing", "foo");
record.setGenerated("testing", true);

}
@

Now, to see this for yourself: Insert something into that table. Then double-click it in the QTableView. Edit it, and what I EXPECT to see is instead of that cell in the database being set to whatever you wrote, it's set to "foo."

In fact, that IS what happens to the database. However, the QTableView doesn't show that-- it shows a blank row that I can select but do nothing with. If I close the program and reopen it, it shows exactly what I want.

Why isn't this working? Since the record is pass-by-reference through the signal, shouldn't it? If not, is there a standard way to intercept edits on the model before it gets to the database without the view getting all screwed up?

I would appreciate any advice!

[Beacon11]

Note that if handleBeforeUpdate changes the record's value of "testing" to the same value that was set in the GUI (in this example, if the value set in the GUI was "foo"), it works fine. There's something wonky that happens when the record is updated with a new value-- not just simply updated period. Also, note that if one were to change the code to this:

@
void MainWindow::handleBeforeUpdate(int row, QSqlRecord &record)
{
Q_UNUSED(row);

qDebug() << "Before:" << record;
record.setValue("testing", "foo");
record.setGenerated("testing", true);
qDebug() << "After:" << record;

}
@

The record printed out before and after are literally identical other than the changed string.

[Beacon11]

Alright, I've written up the smallest, simplest working example that I can. Please test this and tell me what's going on:

main.cpp:

@
#include <QApplication>

#include "mainwindow.h"

int main(int argc, char *argv[])
{
QApplication a(argc, argv);
MainWindow w;
w.show();

return a.exec();

}
@

mainwindow.h:

@
#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QSqlRecord>
#include <QMainWindow>

class QSqlTableModel;

class MainWindow : public QMainWindow
{
Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = 0);

private slots:
    void handleBeforeUpdate(int row, QSqlRecord &record);

private:
    QSqlTableModel *m_model;

};

#endif // MAINWINDOW_H
@

mainwindow.cpp:

@
#include <QTableView>
#include <QSqlTableModel>
#include <QMessageBox>
#include <QSqlQuery>
#include <QDebug>

#include "mainwindow.h"

MainWindow::MainWindow(QWidget *parent) :
QMainWindow(parent)
{
QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
database.setDatabaseName("example.sqlite3");
if (!database.open())
{
QMessageBox::critical(this, tr("Cannot open database"),
tr("Unable to establish a database connection.\n"
"This program needs SQLite support.\n"
"Click Cancel to exit."), QMessageBox::Cancel);
close();
}

QSqlQuery query;
if (!query.exec("CREATE TABLE IF NOT EXISTS example (testing text)"))
{
    QMessageBox::critical(this, tr("Cannot create table"),
                          tr("Click Cancel to exit."), QMessageBox::Cancel);
}

query.exec("INSERT INTO example (testing) VALUES ('testing')");

m_model = new QSqlTableModel(this);
m_model->setTable("example");
m_model->select();

connect(m_model, SIGNAL(beforeUpdate(int,QSqlRecord&)), SLOT(handleBeforeUpdate(int,QSqlRecord&)));

QTableView *tableView = new QTableView(this);
tableView->setModel(m_model);
setCentralWidget(tableView);

}

void MainWindow::handleBeforeUpdate(int row, QSqlRecord &record)
{
qDebug() << "Before: " << record;
record.setValue("testing", "intercepted");
record.setGenerated("testing", true);
qDebug() << "After: " << record;

// Before and after should be identical other than the changed strings.
// Why doesn't this work?? Try changing the string in the TableView to
// "intercepted" (without quotes), and note that it works just fine.

}
@

[Beacon11]

I figured it out. I learned two things:

1. beforeUpdate() is before the DATABASE is updated, not before the CACHE is updated.
2. Turns out that the cache can easily get out of sync with the database if it's lacking a serial ID.

The fix to all my problems (and another bug in the example I posted) is to simply add an "id integer primary key" to the database table. Then all of a sudden it works as expected.