Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QSqlTableModel insert new column
Forum Updated to NodeBB v4.3 + New Features

QSqlTableModel insert new column

Scheduled Pinned Locked Moved Unsolved General and Desktop
8 Posts 4 Posters 973 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M Offline
    M Offline
    mafp
    wrote on last edited by
    #1

    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:

    1. Append a row whenever a new device is tested
    2. 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

    F 1 Reply Last reply
    0
    • SGaistS Offline
      SGaistS Offline
      SGaist
      Lifetime Qt Champion
      wrote on last edited by
      #2

      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.

      Interested in AI ? www.idiap.ch
      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

      1 Reply Last reply
      2
      • M Offline
        M Offline
        mafp
        wrote on last edited by
        #3

        Thanks @SGaist for the quick answer!

        I have two follow-up questions:

        1. 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?
        2. 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?
        JonBJ 1 Reply Last reply
        0
        • M mafp

          Thanks @SGaist for the quick answer!

          I have two follow-up questions:

          1. 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?
          2. 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?
          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by JonB
          #4

          @mafp

          1. 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.

          1. 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.
          1 Reply Last reply
          2
          • M Offline
            M Offline
            mafp
            wrote on last edited by
            #5

            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();    
            }
            
            JonBJ 1 Reply Last reply
            0
            • M 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();    
              }
              
              JonBJ Offline
              JonBJ Offline
              JonB
              wrote on last edited by JonB
              #6

              @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.

              M 1 Reply Last reply
              1
              • M 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:

                1. Append a row whenever a new device is tested
                2. 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

                F Offline
                F Offline
                FabianDeUruguay
                wrote on last edited by
                #7

                @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)

                1 Reply Last reply
                0
                • JonBJ 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.

                  M Offline
                  M Offline
                  mafp
                  wrote on last edited by
                  #8

                  @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.

                  1 Reply Last reply
                  0

                  • Login

                  • Login or register to search.
                  • First post
                    Last post
                  0
                  • Categories
                  • Recent
                  • Tags
                  • Popular
                  • Users
                  • Groups
                  • Search
                  • Get Qt Extensions
                  • Unsolved