QSqlTableModel insert new column

mafp

Hi,

I have a PostgreSQL table (called device_tests) that stores results of some automated device tests and approximately looks like this:

device_id   |  test01_result   |  test02_result 
------------+------------------+------------------
       2449 |     1            |     0
       2551 |     0            |     1
       3454 |     0            |     0

From my Qt application I would now like to be able to do two things:

Append a row whenever a new device is tested
Append a new column if a new test is added to the testing system

I guess I could do all of this with raw SQL queries but after reading the documentation it seemed like a QSqlTableModel would simplify things a bit. Unfortunately, I can't figure out how to get point 2 working.

If I comment out the "Create new column" part in the example code below, everything works and a new row is added to the table. However, if I try to add a new column called "test03_result" by appending a new QSqlField to deviceRecord, the insertRecord() function fails.

Do I just fundamentally misunderstand how to use QSqlTableModel or is there a small mistake somewhere? I start doubting that it is possible to insert a new column into an existing table without using raw queries. But in that case I don't understand what would be the usecase of the QSqlRecord::append() function?

Could someone enlighten me? Thanks a lot! :)

Example code:

// Set up model
QSqlTableModel model;
model.setEditStrategy(QSqlTableModel::EditStrategy::OnManualSubmit);
model.setTable("device_tests");
model.select();

// Get empty record with only the field names
QSqlRecord deviceRecord = model.record();

// Populate existing columns with values
deviceRecord.setValue("id", 1000);
deviceRecord.setValue(("test01_result", 1)
deviceRecord.setValue("test02_result", 1);

// Create new column
QSqlField uploadTimeField("test03_result", QVariant::Int);
test03ResultField.setValue(1);
deviceRecord.append(test03ResultField);

// Append device record at the end of the table
if (model.insertRecord(-1, deviceRecord)) {
    qCDebug() << "Record could be inserted!";
} else {
    qCDebug() << "Record could NOT be inserted!";
}

// Sync with database
model.submitAll();

My environment:
Qt version: 5.14.0
PostgreSQL version: 14.5

SGaist

Hi and welcome to devnet,

You can't just "add a column to table" like you do it with rows.

You have to alter the table for that.

That said, if you really have tests that can be added like that it would make more sense to revise your database architecture to make that an integral part of it. From the looks of it (from the top of my head):

a devices table that contains the details of the device
a tests table that contains the information about the tests
a results table that will contain the test result with links to the device and test.

mafp

Thanks @SGaist for the quick answer!

I have two follow-up questions:

As you've probably noticed I don't have much experience with databases. Your proposed approach seems sensible to me but there is one requirement I forgot to mention before. In the current table each row corresponds to a device test run (that is test01, test02, etc.) at a given date. How would you link all tests of a test run together in your proposed database structure?
I still don't understand what the QSqlRecord::append() is used for. Why would I want to append a new field to a record if that record then can't be inserted into the model?

JonB

@mafp

Do you have more than one device to test in your data? test01 may have a date but it does not tell me which device was tested? In @SGaist's formulation the results table has one link to a devices table and another link to a tests table. You could issue queries like:

SELECT * FROM results WHERE device = device_ID AND date BETWEEN ...
SELECT * FROM results WHERE test = test_ID AND date BETWEEN ...

If you only run tests as part of a "test run batch" ("all tests of a test run together"), you might either put a unique ID like a generated GUID as an extra column in the results table so you can get all the tests which were in that run (SELECT * FROM results WHERE run_GUID = ...) or you might add a further runs table so the results table now has a run_ID column referring to that and move, for example, the date column from results to runs.

QSqlRecord::append() would only be used when building a QSqlRecord instance in memory. If, for whatever reason, it does not match the column definitions actually defined in the database table it won't "work", e.g. for INSERT/UPDATE SQL statements. It does not in any way alter the columns which are in the database. In any case, you will not be using it. For your purposes you may add rows to the database from the Qt client but you may not add/update/delete columns in the table as a whole. That is just the way it is with standard relational database tables.

mafp

Thanks for elaborating @JonB, I'll keep this approach in mind!

While I certainly want to revise my database architecture, I still needed something that works with the current "one table" situation. I'm aware that this is not how you should work with databases but I still wanted to share my "solution".

Since QSqlTableModel does not support the insertion of new columns, I'm first using bare SQL commands with QSqlQery to alter the table and then fill it using the QSqlTableModel. This is certainly not the most elegant way but it works.

// Set up model and query
QSqlTableModel model;
QSqlQuery query;
model.setEditStrategy(QSqlTableModel::EditStrategy::OnManualSubmit);
model.setTable("device_tests");

// Get empty record with only the field names
QSqlRecord record = model.record();

// Loop through list of tests and create result column if not existing
for (int i = 0; i < testList.count(); i++) {
    QString testResultColName = testList.at(i)->id() + "_result";

    if (!record.contains(testResultColName) {
        query.exec(QStringLiteral("ALTER TABLE device_tests ADD IF NOT EXISTS %1 VARCHAR;").arg(testResultColName);
    }
}

// Refresh model with updated table
model.setTable("device_tests");
model.select();
record = model.record();

// Loop through list of tests and fill in test results
for (int i = 0; i < testList.count(); i++) {
    QString testResultColName = testList.at(i)->id() + "_result";
    record.setValue(testResultColName, testList.at(i)->result());
}

// Append device record at the end of the table
if (!model.insertRecord(-1, record)) {
    qDebug() << "Error: Record could not be inserted!";
}

// Sync with database
if (!model.submitAll()) {
    qDebug() << "Error: Could not sync with database." << model.lastError().text();    
}

JonB

@mafp
This seems like a very strange to do. You are presumably in one of two situations:

You know in advance that you want a certain (pre-)named extra column. In which case it should be specified when creating the table in the database. But I don't think this is your case.
You want to add extra columns dynamically, whenever you feel like it, with column names generated at runtime. This seems to be your case. This is a really bad idea.

Why? Well for starters:

How many of these columns might there be? 1, 10, 100, 1000? SQL has limits on how many columns a table may have, very different from how many rows in can have.
You seem to be naming these columns testList.at(i)->id() + "_result". Assuming testList.at(i)->id() is a number, that will generate a column name like 1_result. I am slightly surprised your SQL even allows a column name starting with a digit, it is certainly a bad idea.
With a "standard" SQL database the user running your Qt application/connected to the SQL database would likely not even have permission to go ALTER TABLE .... so the approach would not work.
You will have a table with an unknown number of columns with unknown names. This will make it difficult/impossible to write reports against it, display it nicely in a UI table, etc.

In a word, I don't know why you think it is OK to add new columns to "the current "one table" situation" but not to, say, introduce a new table instead.

FabianDeUruguay

@mafp I think you should separate the data into two tables: "tests_types" and "tests_done". In "test_done" you should add a record for each test performed, saving in this table a device id and a test id (each type of test with its id is saved in the other table)

mafp

@JonB
I absolutely agree with what you said and I appreciate your feedback!

Just for clairfication, there are a couple of (debatable) reasons I implemented this hacky solution:

New test types are added quite rarely, so it's unlikely that the SQL database would grow too large.
This project is part of a larger testing system which stores the results of each test in a large table. Obviously, this approach is far from ideal and now requires some effort to change it.
Everything is internal, so I can define what rights the user has. Not to say that it makes sense for the standard user to alter the table.
The test.id() property is actually a QString and not a number, so the table columns are named sensibly.

That being said, I realize that this is definitely not the way to go and hope to implement your suggestions sooner or later.