April 6, 2021

An Experiment with QSqlTableModel and SQLite

  —Trying out Composite Primary Keys with Qt's QSqlTableModel.

I found the interaction between Qt’s QSqlTableModel and composite primary keys difficult to understand. Those difficulties involved recurrent “No Fields to update” messages from QSqlError::lastError() when trying to insert. This post captures a small series of experiments to better understand the behavior. I started with a working example from the Qt Forum.

The code for this post is in a Git repository.

CREATE TABLE preferences(
    x TEXT NOT NULL,
    y TEXT NOT NULL,
    PRIMARY KEY(x, y)
    WITHOUT ROWID;

I’m using SQLite (3.28.0 2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50) and qt 5.13.0.

Each change to bring the table in the example closer to my ideal is on a separate branch. This permits a simple diff between the master branch and the changed branch to determine the alterations I’ve made to the original example.

Branches

* fb8951a -  Converted composite primary key types to text (introduce_text_fields)
* 205492f -  Introduced constraints on composite primary key (constrained_composite_primary_key)
* c2f67c0 -  Introduced an unconstrained composite primary key (composite_primary_key)
* 184f5ed -  Removed QCoreApplication (master)
| * d731611 -  Converted ROWID table to a table (without_row_id)
| * 5dcc914 -  Removed QCoreApplication 
|/  
* ec024b1 -  White space change 
* 3bd46af -  Download of original source code 
* 2fe4f3e -  Initial commit

Conclusion

The table structure is not the problem.

Turns out a critical component of the code was missing:

 QSqlTableModel model(0, database());
 model.setTable("rast123");
 model.select();
 model.database().transaction();

Initialization of the QSqlTableModel was being done in a constructor. For some reason, this needs to be done again to prepare for the row insert. Doing so with the methods attached to this don’t have the desired effect.