QSqlTableModel insert new column
-
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
todeviceRecord
, theinsertRecord()
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 theQSqlRecord::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 -
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.
-
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?
-
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?
- 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 theresults
table has one link to adevices
table and another link to atests
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 furtherruns
table so theresults
table now has arun_ID
column referring to that and move, for example, thedate
column fromresults
toruns
.QSqlRecord::append()
would only be used when building aQSqlRecord
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. forINSERT
/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.
-
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 withQSqlQery
to alter the table and then fill it using theQSqlTableModel
. 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(); }
-
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 withQSqlQery
to alter the table and then fill it using theQSqlTableModel
. 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(); }
@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"
. AssumingtestList.at(i)->id()
is a number, that will generate a column name like1_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.
-
-
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
todeviceRecord
, theinsertRecord()
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 theQSqlRecord::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@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
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"
. AssumingtestList.at(i)->id()
is a number, that will generate a column name like1_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.
@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 aQString
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.
-