QSqlTableModel usage in Qt 5.



  • Since we have updated our project to be powered by Qt 5, I have faced the change in the behaviour of the QSqlTableModel. So, I decided to compare its implementations in versions 4.8.4 vs 5.0.0. It seems that the submit/update strategy has been reworked significantly.

    Currently, I am unable to get it working back as it was before. The issue is that when I try to add a new record I get the empty row as a result. After my research on the sources I have connected to QSqlTableModel::dataChanged() signal to see what happens. It turned out that new values of the row are properly set while working with QSqlTableModel's cache and they are replaced with empty values on selectRow(), while doing submit() / submitAll().

    Docs on "selectRow()":http://qt-project.org/doc/qt-5.0/qtsql/qsqltablemodel.html#selectRow say that

    bq. If no matching row is found, the model will show an empty row.

    So, I guess, this might be the case. Also docs mention the primary keys, which are absent for the record builded manually (I'd like that field to be autogenerated on DBMS-server side), then

    bq. Without a primary key, all column values must match.

    rule should apply, which is, as far as I understand, satisfied due to the fact that selectRow() immediately follows submit() after multiple setData() calls.

    So, the question is how QSqlTableModel should be used to get this simple task working properly?

    Here's the basic project I use to work it out (also I provide "zip":https://docs.google.com/file/d/0B7RvmRyUa3jQQlIwMFUySWVaaUk/edit of the full project setup).

    My environment is:

    • Windows 7 x86;
    • Qt 5.0.0 (msvc2010).

    main.cpp:
    @
    #include <QtSql/QSqlDatabase>
    #include <QtSql/QSqlQuery>
    #include <QtWidgets/QApplication>
    #include "mainwindow.h"

    namespace {

    bool createTable(const QSqlDatabase& db)
    {
    QSqlQuery query(db);

    query.exec&#40;
        "CREATE TABLE IF NOT EXISTS collection ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "name VARCHAR(1024&#41; NOT NULL,"
        "language VARCHAR(32&#41; NOT NULL"
        "&#41;"
    &#41;;
    return query.isActive();
    

    }

    void createDatabase()
    {
    auto db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
    Q_ASSERT(db.isValid());

    db.setDatabaseName(QStringLiteral(":memory:"));
    db.open() && createTable(db);
    

    }

    } // namespace

    int main(int argc, char *argv[])
    {
    QApplication a(argc, argv);

    createDatabase();
    
    MainWindow w;
    w.show();
    
    return a.exec&#40;&#41;;
    

    }
    @

    mainwindow.h
    @
    #ifndef MAINWINDOW_H
    #define MAINWINDOW_H

    #include <QtWidgets/QMainWindow>

    QT_FORWARD_DECLARE_CLASS(QSqlTableModel)
    namespace Ui {class MainWindow;}

    class MainWindow : public QMainWindow
    {
    Q_OBJECT

    public:
    explicit MainWindow(QWidget* parent = nullptr);
    virtual ~MainWindow();

    private slots:
    void on_addButton_clicked();
    void onDataChanged(const QModelIndex& topLeft, const QModelIndex& bottomRight, const QVector<int> &roles);

    private:
    void setupModel();
    void setupUi();

    private:
    QSqlTableModel* const m_model;
    Ui::MainWindow* const ui;
    };

    #endif // MAINWINDOW_H
    @

    mainwindow.cpp
    @
    #include <QtCore/QDebug>
    #include <QtWidgets/QMessageBox>
    #include <QtSql/QSqlError>
    #include <QtSql/QSqlField>
    #include <QtSql/QSqlRecord>
    #include <QtSql/QSqlTableModel>
    #include "mainwindow.h"
    #include "ui_mainwindow.h"

    MainWindow::MainWindow(QWidget* const parent)
    : QMainWindow(parent)
    , m_model(new QSqlTableModel(this))
    , ui(new Ui::MainWindow)
    {
    setupModel();
    setupUi();
    }

    MainWindow::~MainWindow()
    {
    delete ui;
    }

    void MainWindow::on_addButton_clicked()
    {
    QSqlRecord record;

    QSqlField nameField(QStringLiteral("name"), QVariant::String);
    nameField.setValue(ui->nameEdit->text());
    record.append(nameField);
    
    QSqlField languageField(QStringLiteral("language"), QVariant::String);
    languageField.setValue(ui->languageEdit->text());
    record.append(languageField);
    
    const bool ok = m_model->insertRecord(-1, record);
    if (!ok) {
        qDebug() << m_model->lastError();
        return;
    }
    

    }

    void MainWindow::onDataChanged(const QModelIndex& topLeft, const QModelIndex& bottomRight, const QVector<int> &roles) {
    QString rolesStr;
    if (!roles.empty()) {
    rolesStr = QString::number(roles[0]);
    for (int i = 1; i < roles.count(); ++i) {
    rolesStr += QStringLiteral(", ") + QString::number(roles[i]);
    }
    }
    rolesStr = QStringLiteral("[%1]").arg(rolesStr);
    QMessageBox::information(this, tr("Changes"), tr("%1 (%2, %3) -- (%4, %5): %6").arg(sender()->objectName()).arg(topLeft.row()).arg(topLeft.column()).arg(bottomRight.row()).arg(bottomRight.column()).arg(rolesStr));
    }

    void MainWindow::setupModel()
    {
    m_model->setObjectName(QStringLiteral("MyModel"));
    m_model->setTable(QStringLiteral("collection"));
    m_model->select();
    connect(m_model, &QSqlTableModel::dataChanged, this, &MainWindow::onDataChanged);
    }

    void MainWindow::setupUi()
    {
    ui->setupUi(this);
    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView->setModel(m_model);
    ui->tableView->hideColumn(m_model->fieldIndex(QStringLiteral("id")));
    ui->tableView->setSortingEnabled(true);
    ui->tableView->sortByColumn(m_model->fieldIndex(QStringLiteral("name")), Qt::AscendingOrder);
    ui->tableView->setCurrentIndex(m_model->index(0, 0));;
    }
    @



  • The problem is that selectRow() has no access to the primary key for the inserted row, as it is set by the database.

    The "...Without a primary key, all column values must match." rule does not apply either, because the table actually has a primary key, it is just not known.

    I'm still not quite sure on how to fix this properly in QSqlTableModel, but until then we've come up with two possible workarounds:

    • Revert to the previous behaviour and manually select() after insertRows().
    • Manually set the primary key for inserted rows.


  • Feel free to track / vote on "QTBUG-29102":https://bugreports.qt-project.org/browse/QTBUG-29102.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.