QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord

Stroopwafe1

Hi, I'm working on a simple personal project to track food/drink intake and I have the following SQLite table and QSqlRelationalTableModel subclass:

CREATE TABLE IF NOT EXISTS "FoodDay" (
        "food"	INTEGER NOT NULL,
	"date"	INTEGER NOT NULL,
	"amount" REAL NOT NULL,
	"caloriesCache"	INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY("food","date"),
	CONSTRAINT "FK_Food_ID" FOREIGN KEY("food") REFERENCES "FoodData"("id")
);

And the C++ class that models it

#ifndef FOODDAY_H
#define FOODDAY_H

// ... includes
// RelationalTableModel is a subclass of QSqlRelationalTableModel that implements some boilerplate, like the roleNames, data, etc...
class FoodDayModel : public RelationalTableModel {
	Q_OBJECT
	Q_PROPERTY(QDate date READ date WRITE setDate NOTIFY dateChanged FINAL)
	QML_ELEMENT

public:
	explicit FoodDayModel(QObject *parent = nullptr);

	QDate m_Date;
	QDate date() {
		return m_Date;
	}
	void setDate(const QDate& date) {
		if (date == m_Date) return;
		m_Date = date;
		emit dateChanged();
	}

	Q_INVOKABLE void addFood(int foodId, float amount);
signals:
	void dateChanged();
};

#endif // FOODDAY_H

My FoodDayModel::addFood(...) is implemented like this:

void FoodDayModel::addFood(int foodId, float amount) {
	setEditStrategy(QSqlTableModel::OnManualSubmit);
	QSqlRecord newRecord = record();

	Q_ASSERT(backend != nullptr);

	Q_ASSERT(backend->m_Data.find(foodId) != backend->m_Data.cend());
	FoodData& food = backend->m_Data[foodId];

	Q_ASSERT(backend->m_Types.find(food.typeId) != backend->m_Types.cend());
	FoodType& type = backend->m_Types[food.typeId];

	Q_ASSERT(backend->m_Units.find(type.unitId) != backend->m_Units.cend());
	FoodUnit& unit = backend->m_Units[type.unitId];

	float ratio = amount / (float)(unit.amount);

	newRecord.setValue("food", foodId);
	newRecord.setValue("date", m_Date.toJulianDay());
	newRecord.setValue("amount", amount);
	newRecord.setValue("caloriesCache", (int)(ratio * food.calories));

	qDebug() << newRecord;
	int newRow = rowCount();

	if (!insertRecord(newRow, newRecord)) {
		qFatal("Failed to add food/drink to the database: %s", qPrintable(lastError().text()));
		return;
	}

	QSqlRecord addedRecord = record(newRow);
	qDebug() << addedRecord;

	if (!submitAll()) {
		qFatal("Failed to flush to the database: %s", qPrintable(lastError().text()));
		return;
	}

	setEditStrategy(QSqlTableModel::OnFieldChange);

}

However, the debug logs show that the record is resetting the composite primary key values of food and date. I don't want this, as they get filled from a QtQuick ComboBox and the current day.

The relevant debug logs:

QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "3" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "2460949" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"
QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: no, autoValue: false, readOnly: false) "0" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: no, autoValue: false, readOnly: false) "0" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"
Failed to flush to the database: NOT NULL constraint failed: FoodDay.food Unable to fetch row

I can see that the values have their generation reverted to no, which means (according to the docs) that they get generated by the database. Even though they shouldn't be.
I can also read from the docs that QSqlTableModel::insertRecord() calls insertRows() and setRecord(). And insertRows() emits the primeInsert signal, which I think changes the primary key to NULL and generated to false.

What is the way to fix this? Do I avoid calling insertRecord? Do I listen to the primeInsert signal and set the values that way? Or is there another method I don't know about that I should do instead?

JonB

@Stroopwafe1 said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

I can see that the values have their generation reverted to no, which means (according to the docs) that they get generated by the database. Even though they shouldn't be.

You may know more than I about this, or we may be talking at cross purposes. I have not tried any of this (!), but my understanding is: "generated" refers to whether an INSERT or UPDATE statement sent by Qt from the model will include that field and its value in the VALUES or SET clause. Prior to/at the time of insertRecord() you may set these fields and they will appear in the INSERT statement. But once/immediately after being inserted, future UPDATEs should not be allowed to alter the PK value, so those columns should not be generated as SETs in an UPDATE statement (while the other columns may appear).

Now to why you get error

Failed to flush to the database: NOT NULL constraint failed: FoodDay.food Unable to fetch row

Isn't that actually a consequence of you using a QSqlRelationalTableModel? Isn't it that the Qt side expects to see in the related FoodData table/model an entry for the value of your food column in FoodDay table/model equal to it? You have "broken the contract" you establish with QSqlRelationalTableModel that Qt will see in-memory models maintaining the relationship? It is not good enough if you say the row does exist at the backend database side, if you want to use QSqlRelationalTableModel (you don't have to, it's only for value lookup) you must have the FK model/table up-to-date in memory.

You might temporarily replace the QSqlRelationalTableModel by a QSqlTableModel (and perhaps comment out the CONSTRAINT "FK_Food_ID" if necessary) and compare the behaviour --- I'm thinking you will get the same "generated" behaviour but no problem adding the record?

Stroopwafe1

@JonB said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

Prior to/at the time of insertRecord() you may set these fields and they will appear in the INSERT statement. But once/immediately after being inserted, future UPDATEs should not be allowed to alter the PK value, so those columns should not be generated as SETs in an UPDATE statement (while the other columns may appear).

This does make sense, but for debugging purposes I did change the backing SQLite database for the food and date columns to be allowed NULL. And they got inserted just fine with NULL values... No error whatsoever.

Isn't that actually a consequence of you using a QSqlRelationalTableModel? Isn't it that the Qt side expects to see in the related FoodData table/model an entry for the value of your food column in FoodDay table/model equal to it? You have "broken the contract" you establish with QSqlRelationalTableModel that Qt will see in-memory models maintaining the relationship? It is not good enough if you say the row does exist at the backend database side, if you want to use QSqlRelationalTableModel (you don't have to, it's only for value lookup) you must have the FK model/table up-to-date in memory.

That might be. But Qt does have a backing up-to-date model for FoodData as well, because I select the foodId from a QML ComboBox, whose model is the FoodData model in C++ as a QSqlTableModel. The usage of QSqlRelationalTableModel was confusing to me anyways because it seems it's mainly intended for Qt Widgets, and not QML TableViews. So I will admit that I might be completely using the wrong models for my QML. Since the relationship doesn't do anything on the QML side.

You might temporarily replace the QSqlRelationalTableModel by a QSqlTableModel (and perhaps comment out the CONSTRAINT "FK_Food_ID" if necessary) and compare the behaviour --- I'm thinking you will get the same "generated" behaviour but no problem adding the record?

I can try to change the model to just be the tablemodel yeah. But I do doubt it will solve my issue of Qt trying to insert NULL as the primary keys

EDIT: Changing to QSqlTableModel didn't fix the issue indeed. The generated behaviour is the same and the submitAll either fails with a NOT NULL constraint, or succeeds adding NULL values

JonB

@Stroopwafe1 said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

EDIT: Changing to QSqlTableModel didn't fix the issue indeed. The generated behaviour is the same and the submitAll either fails with a NOT NULL constraint, or succeeds adding NULL values

I am surprised at this. If you have a plain QSqlTableModel and you remove the CONSTRAINT "FK_Food_ID" FOREIGN KEY in the definition could I then see exactly what the error message reads instead of the old

Failed to flush to the database: NOT NULL constraint failed: FoodDay.food Unable to fetch row

(The fact that this writes FoodDay.food is what takes my interest, I don't know whether it might have something to do with SQLite which I don't use but the FoodDay qualifier makes me feel it might have something to do with the FK/QSqlRelationalTableModel.)

Finally, if you have nothing better to do(!) could you change so you no longer have a "composite" key of PRIMARY KEY("food","date"), replace with whatever simple PK. Trying to understand whether your issue comes from FK relationship or PK composite-ness?

Stroopwafe1

@JonB said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

I am surprised at this. If you have a plain QSqlTableModel and you remove the CONSTRAINT "FK_Food_ID" FOREIGN KEY in the definition could I then see exactly what the error message reads instead of the old

I honestly wish I got a different error message. That would at least indicate progress.
Version 1 (With Null constraints)

CREATE TABLE "FoodDay" (
	"food"	INTEGER NOT NULL,
	"date"	INTEGER NOT NULL,
	"amount"	REAL NOT NULL,
	"caloriesCache"	INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY("food","date")
)

Debug output:

QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "3" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "2460949" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"
QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: no, autoValue: false, readOnly: false) "0" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: no, autoValue: false, readOnly: false) "0" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"
Failed to flush to the database: NOT NULL constraint failed: FoodDay.food Unable to fetch row

Version 2: No Null constraints:

CREATE TABLE "FoodDay" (
	"food"	INTEGER,
	"date"	INTEGER,
	"amount"	REAL NOT NULL,
	"caloriesCache"	INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY("food","date")
)

Debug output:

QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "3" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "2460949" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"
QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: no, autoValue: false, readOnly: false) "0" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: no, autoValue: false, readOnly: false) "0" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"

No error, and data in the database

I did make sure to close my SQLite DB Browser before every interaction with my program in case the handle messed things up

Version 3: Only food column as the primary key with NULL allowed:

CREATE TABLE "FoodDay" (
	"food"	INTEGER,
	"date"	INTEGER,
	"amount"	REAL NOT NULL,
	"caloriesCache"	INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY("food")
)

QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "3" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "2460949" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"
QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: no, autoValue: false, readOnly: false) "0" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "2460949" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"

DB Inserted record:

Version 4: With only food as PK, NOT NULL constraints

CREATE TABLE "FoodDay" (
	"food"	INTEGER NOT NULL,
	"date"	INTEGER NOT NULL,
	"amount"	REAL NOT NULL,
	"caloriesCache"	INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY("food")
)

QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "3" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "2460949" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"
QSqlRecord(4)
 0: QSqlField("food", int, tableName: "FoodDay", generated: no, autoValue: false, readOnly: false) "0" 
 1: QSqlField("date", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "2460949" 
 2: QSqlField("amount", double, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "150" 
 3: QSqlField("caloriesCache", int, tableName: "FoodDay", generated: yes, autoValue: false, readOnly: false) "4"

DB record inserted:

I can only conclude from this that Qt, if it finds an INT PK, that it automatically increments it, even though on the DB side it's not AUTOINCREMENT.
I want to be wrong on this

JonB

@Stroopwafe1 said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

I can only conclude from this that Qt, if it finds an INT PK, that it automatically increments it, even though on the DB side it's not AUTOINCREMENT.
I want to be wrong on this

I really don't think so. And if it did I believe you would see autoValue: true for that column. (Possibly even readOnly: true as well for this case, don't know.) If you want you could compare against what you see if you actually make the db column AUTOINCREMENT.

If I were to have a play with this I would really want (a) a complete program, nothing I need to add to myself plus (b) simplified a lot, you have quite a bit of stuff I would have to write at present. Anything more than 100 lines (and preferably less) is too much. When I submit a problem I pride myself on reducing it so that every remaining line is essential to the issue! Up to you.

Stroopwafe1

I definitely can work on a minimum compilable example

@JonB said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

Anything more than 100 lines (and preferably less) is too much. When I submit a problem I pride myself on reducing it so that every remaining line is essential to the issue! Up to you.

I will do my best to keep it as small, yet still relevant as possible. I will leave out all the other tables as well since those are not part of the problem (only debugging whether composite int PK breaks things)

However, I'm not well-versed in how to share these mini projects on forums. Do you want it as code blocks in a reply? Or as a git repo? Or does QT Creator have some fancy feature to share a project?

JonB

For my part, I would usually only look at/copy 100 lines which are pasted here (line at top and at bottom must read ``` (3-backticks) to make it code block). However having got this far I would accept a simple github repo from you if it's easy, whatever gives me less work to do to get it compiling and running. What I would ask is for you to try to remove absolutely anything/every line which is not required to illustrate the issue. I do not care about your real-world requirements, I care only about whatever is minimal to show a problem. Please do include sending the CREATE TABLE string to the db as I am not familiar with SQLite and don't have any tool which might be required to create a database/table outside of your code.

Looking back at your examples, I think versions 3 or 4 are the most useful. (Actually looks like 4 is the best, 3 allows a PK to be NULL.) I agree I see just the food column alone seeming to change from 3 to 0 which looks odd.

P.S. IMPORTANT
What is this I see about SQLite when I Google does sqlite make first column be autoincrement:

SQLite

https://www.sqlite.org › faq

26 Nov 2024 — Short answer: A column declared INTEGER PRIMARY KEY will autoincrement. Longer answer: If you declare a column of a table to be INTEGER ...

?!

I think you need to read https://www.sqlite.org/lang_createtable.html#rowid. You may need to look into

Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "rowid" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "rowid", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

The rowid (and "oid" and "rowid") is omitted in WITHOUT ROWID tables. WITHOUT ROWID tables are only available in SQLite version 3.8.2 (2013-12-06) and later. A table that lacks the WITHOUT ROWID clause is called a "rowid table".

If I read right, SQLite makes assumptions about you using an INT as a PK which you do not want in your case....

I think it's saying you need to add WITHOUT ROWID if you want to manage the PK INT value yourself?

Stroopwafe1

@JonB

Oh, so it's SQLite that's being dumb in this case? Well that's what I get for assuming all DB managers are basically the same.
I will try to look into the RowID, and if that doesn't solve it, I will still get back to this post then with the minimal example that I was working on right now.

Thank you for your time already though, it might not be solved yet, but I do appreciate you taking a look

JonB

@Stroopwafe1
I just added the final sentence:

I think it's saying you need to add WITHOUT ROWID if you want to manage the PK INT value yourself?

Not bad from me, given that I don't use SQLite :) I am familiar with MySQL/MariaDB and MS's SQL Server/T-SQL. They don't have this behaviour!

JonB

@Stroopwafe1
P.S.
...Or if you prefer you might allow the SQLite table to have an autoincrement single-field INT PK for its "rowid" (maybe being SQLite it works better like that since it seems to want it?) and make your composite PRIMARY KEY("food","date") be something like a "UNIQUE [INDEX]" instead....

Stroopwafe1

@JonB
Modifying the table to be WITHOUT ROWID I get a non-null constraint error again with version 4.
HOWEVER, doing it in my small mini example, it seems that it doesn't do the generation: no and value to NULL. So I have something wrong in my main project. I guess the mini example is also just a good way to test where the problem actually is...

@JonB said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

...Or if you prefer you might allow the SQLite table to have an autoincrement single-field INT PK for its "rowid" (maybe being SQLite it works better like that since it seems to want it?) and make your composite PRIMARY KEY("food","date") be something like a "UNIQUE [INDEX]" instead....

Yeah seems that might be a lot more manageable than trying to force this.

JonB

@Stroopwafe1
Googling sqlite composite primary key there is a discussion about your situation at https://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns
(there may be others). Read through it all. One post there says:

In another way, you can also make the two column primary key unique and the auto-increment key primary. Just like this: https://stackoverflow.com/a/6157337

Also https://www.sqlite.org/lang_createtable.html, at least the 3.5. The PRIMARY KEY section.

Stroopwafe1

Yes, thank you so much for your time and insight.
For anyone else that comes across this in a future search result: The solution is to just set a UNIQUE constraint, and add a column for autoincrement id.

The final SQL Table:

CREATE TABLE "FoodDay" (
	"id"	INTEGER NOT NULL,
	"food"	INTEGER NOT NULL,
	"date"	INTEGER NOT NULL,
	"amount"	REAL NOT NULL,
	"caloriesCache"	INTEGER NOT NULL DEFAULT 0,
	UNIQUE("food","date"),
	PRIMARY KEY("id" AUTOINCREMENT),
	CONSTRAINT "FK_FoodData_ID" FOREIGN KEY("food") REFERENCES "FoodData"("id")
)

Stroopwafe1

To add on to this, I discovered why it was trying to insert NULL for relation fields.
I did the select before setting my relations. And it wasn't enough to have a separate model load the related table. It has to be loaded in the QSqlRelationTableModel for it to work. So the very original comment here (sorry if this pings you) turned out to be 100% correct.
I'm just also posting this here for future reference

@JonB said in QSqlRecord forces null in composite primary key on QSqlTableModel::insertRecord:

Isn't that actually a consequence of you using a QSqlRelationalTableModel? Isn't it that the Qt side expects to see in the related FoodData table/model an entry for the value of your food column in FoodDay table/model equal to it? You have "broken the contract" you establish with QSqlRelationalTableModel that Qt will see in-memory models maintaining the relationship? It is not good enough if you say the row does exist at the backend database side, if you want to use QSqlRelationalTableModel (you don't have to, it's only for value lookup) you must have the FK model/table up-to-date in memory.