Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Insert record into QSqlTableModel



  • Hi all,
    I have QSqlTableModel and connected QTableView. I want to insert record into tableview's current row, but it is always inserted into the last row of the tableview. I found that the primary key could not be generated correctly. could you give me some suggestion please?
    The demo project is here: Demo Project
    Best regards!



  • @tovax
    Maybe someone will choose to look through all the files in your external project. For such a simple issue as this, couldn't you have narrowed it down in your code and only presented a couple of relevant lines here?

    I found that the primary key could not be generated correctly.

    Huh? Fix whatever that is, if you can't generate a PK correctly there's no point asking about where it does/doesn't appear in a view.



  • @JonB I'm sorry for that. The "Insert record" code as follows:

    void JCDemoDatabase::onInsertClicked()
    {
        qDebug() << __FUNCTION__ << tableView->currentIndex().row();
    
        int32_t currentRowIndex = tableView->currentIndex().row();
    
        tableModel->database().transaction();
    
        QSqlRecord record = tableModel->record();
        record.field(Table::PrimaryKey).setReadOnly(false);
        record.field(Table::PrimaryKey).setAutoValue(true);
        record.setGenerated(Table::PrimaryKey, true); // true
    
        for (int32_t columnIndex = Table::Field::Min; columnIndex <= Table::Field::Max; columnIndex++) {
            record.setValue(Table::FieldText[columnIndex], QString("---"));
        }
    
        if (!tableModel->insertRecord(currentRowIndex, record)) {
            qDebug() << __FUNCTION__ << 1 << "Error inserting record to the model";
            qDebug() << __FUNCTION__ << 2 << tableModel->lastError().text();
        } else {
            qDebug() << __FUNCTION__ << 1 << record.field(Table::PrimaryKey);
            qDebug() << __FUNCTION__ << 2 << record.field(Table::PrimaryKey).value();
        }
    
        if (tableModel->submitAll()) {
            tableModel->database().commit();
        } else {
            tableModel->database().rollback();
            qDebug() << __FUNCTION__ << 3 << tableModel->lastError().text();
        }
    
    //        tableModel->select();
    //        tableView->setModel(tableModel);
        qDebug() << __FUNCTION__ << 4 << tableModel->rowCount();
    }
    


  • @JonB For example, the tablemodel has 50 rows and the primary key is from 1 to 50. If you delete line 20, the number 20 will be missing from the primary key. If a row is inserted in line 20, the primary key will not generate the primary key 20, but will add the primary key 51 at the end.



  • void JCDemoDatabase::onRemoveClicked()
    {
        qDebug() << __FUNCTION__ << tableView->currentIndex().row();
    
        int32_t currentRowIndex = tableView->currentIndex().row();
    
        tableModel->database().transaction();
    
        if (!tableModel->removeRow(currentRowIndex)) {
            qDebug() << __FUNCTION__ << 1 << "Error inserting record to the model";
            qDebug() << __FUNCTION__ << 2 << tableModel->lastError().text();
        }
    
        if (tableModel->submitAll()) {
            tableModel->database().commit();
        } else {
            tableModel->database().rollback();
            qDebug() << __FUNCTION__ << 3 << tableModel->lastError().text();
        }
    
    //        tableModel->select();
    //        tableView->setModel(tableModel);
        qDebug() << __FUNCTION__ << 4 << tableModel->rowCount();
    }
    


  • @tovax said in Insert record into QSqlTableModel:

    @JonB For example, the tablemodel has 50 rows and the primary key is from 1 to 50. If you delete line 20, the number 20 will be missing from the primary key. If a row is inserted in line 20, the primary key will not generate the primary key 20, but will add the primary key 51 at the end.

    This is probably a true statement, but I don't know what you are asking.

    If you have an auto-increment primary key in the backend database, and you delete a row, then left to its own devices the next record will not "fill" the gap, it will use the next highest available number. If in your code you attempt to tell it to use the missing number for its value on inserting a new record, then (I believe) some databases will respect that, some will ignore it and allocate the next number, and some will probably error with "duplicate key". If your database supports inserting a row with a PK in the "middle", it is your job in your code to calculate that missing number and pass it in your INSERT statement. The backend will not do that for you. And I don't see any indication you are doing that.

    Your Qt code does not know about this. I am not sure whether after submitAll() Qt automatically tries to re-read the record inserted/get its values from the backend and will see what it actually got or not. If necessary your commented-out tableModel->select(); can be issued to re-read what is actually there now.

    I do not understand what your question here is? You say nothing about whether you get any error messages, nothing about what you or do not want. And overall your question is about the "tableview", which I thought is what you were questioning, but now I wonder if you are just reporting it displays whatever is in the model? So if it gets the new highest number it will indeed appear at the end in the view.



  • @JonB Hi, thank you for your patience. My intention is to insert a record in the current row of tablemodel, but it is always appended to the end.
    The relevant code is this sentence:

    tableModel->insertRecord(currentRowIndex, record)
    

    That's what the help manual says:

    bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)
    Inserts the record at position row. If row is negative, the record will be appended to the end.
    

    But the test result is: no matter what the value of “row” is, record is always appended to the end.



  • @tovax
    As I said, this may depend on when you do this with regard to when you submit your changes to the backend, which may cause the value to be altered. Print out what the value of the PK auto-increment is after you insert, or more to the point after you submit.

    Purely at a guess, comment out your record.setGenerated(Table::PrimaryKey, true); // true, and even your record.field(Table::PrimaryKey).setAutoValue(true);. Does this make a difference to whether it respects your value if you pass in a "missing" middle one?



  • @JonB The test result is: Whether "setGenerated" and "setAutoValue" are commented out or not, the primary key is always 0 before insertion. I think that's why we can't meet our expectations.
    So I'm confused about "setGenerated" and "setAutoValue", don't they mean automatic generation of primary keys?



  • @JonB When I manually set a non duplicate value for the primary key, the record can be correctly inserted into the current row.



  • @tovax said in Insert record into QSqlTableModel:

    So I'm confused about "setGenerated" and "setAutoValue", don't they mean automatic generation of primary keys?

    @JonB When I manually set a non duplicate value for the primary key, the record can be correctly inserted into the current row.

    Yes, they mean auto-generation. So they generate the next number, not some "middle" one! As you say, if you want to "force in" your own, missing number then you cannot ask it to auto-generate the value for you!

    You can probably disable auto-generate, insert your explicit one, and then re-enable auto-generate from then on?



  • @JonB I see. I'll try to add some code to generate the middle primary key as you suggest.



  • @tovax
    I will say one thing: you should not really be trying to do this at all! You're not "supposed" to go back and re-insert auto-gen keys if you have deleted one.

    Imagine you have one million rows. One of them has a "gap". How are you going to find that gap one? If you have to do it client/Qt side, the only way is to read all one million into the client and iterate them to find any gap. Not a good idea! Even if you try to use server-side SQL code to avoid downloading them all, I don't think your SQL will offer any (efficient) way of discovering the gap. So maybe thing again....??



  • @JonB How can I insert a record in the current row, please?


  • Lifetime Qt Champion

    @tovax said in Insert record into QSqlTableModel:

    How can I insert a record in the current row, please?

    What do you mean? A record is a row. Do you want to update a row?



  • @JonB The database is local, and the data structure is completely controlled by myself, as long as the record can be inserted into the current row.



  • @jsulm It is similar to inserting a row of data in Excel table.


  • Lifetime Qt Champion

    @tovax A SQL table is not like a Excel table: you do not insert rows in SQL at specific positions, there is nothing like that in SQL (no specific order of rows). So, what do you mean by "current row"?



  • @jsulm The row which the mouse clicked.



  • @jsulm The help manual says:

    bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)
    Inserts the record at position row. If row is negative, the record will be appended to the end.
    

  • Lifetime Qt Champion

    This post is deleted!


  • @tovax
    Listen carefully! :) To emulate rows in Excel, you should not use any auto-incremented primary key in the database! That is not the way to do it, and leads to your "missing row" problem.

    Instead, you must your own "row number" integer variable as a column. But not auto-incremented. Instead, you must fill it with the desired row number. And if you insert a row you must write code to renumber all the subsequent ones to allow for this one to go in, and if you delete a row you must write code to decerment all the higher ones to close the gap.

    Trust me :D



  • @JonB Thank you very much! According to your suggestion, I have implemented the insertion function. As you said, the primary key not auto-incremented, and renumber primary key before inserting.
    Best regards!

    void JCDemoDatabase::onInsertClicked()
    {
        qDebug() << __FUNCTION__ << tableView->currentIndex().row();
    
        // current row
        int32_t currentRowIndex = tableView->currentIndex().row();
    
        // prepare insert primary key
        tableModel->database().transaction();
        insertPrimaryKey(tableModel, currentRowIndex);
    
        // new record
        QSqlRecord record = tableModel->record();
        for (int32_t columnIndex = Table::Field::Min; columnIndex <= Table::Field::Max; columnIndex++) {
            record.setValue(Table::FieldText[columnIndex], QString("---"));
        }
        record.setValue(Table::PrimaryKey, currentRowIndex + 1);
    
        // insert new record
        if (!tableModel->insertRecord(currentRowIndex, record)) {
            qDebug() << __FUNCTION__ << "Error 1: " << tableModel->lastError().text();
        }
    
        // submit all
        if (tableModel->submitAll()) {
            tableModel->database().commit();
        } else {
            tableModel->database().rollback();
            qDebug() << __FUNCTION__ << "Error 2: " << tableModel->lastError().text();
        }
    }
    
    int32_t JCDemoDatabase::insertPrimaryKey(QSqlTableModel *model, int32_t row)
    {
        // TODO: invalid
        // model->record(rowIndex).setValue(Table::PrimaryKey, (rowIndex + 1));
    
        // key
        int32_t oldMax = model->record(model->rowCount() - 1).value(Table::PrimaryKey).toInt();
        int32_t newMax = model->rowCount() + 1;
        if (oldMax < newMax) {
            oldMax = newMax;
        }
    
        // backup
        for (int32_t rowIndex = 0; rowIndex < model->rowCount(); rowIndex++) {
            QSqlRecord record = model->record(rowIndex);
            record.setValue(Table::PrimaryKey, oldMax + 1 + rowIndex);
            model->setRecord(rowIndex, record);
        }
        if (model->submitAll()) {
            model->database().commit();
        } else {
            model->database().rollback();
            qDebug() << __FUNCTION__ << 1 << model->lastError().text();
        }
    
        // less than "row"
        for (int32_t rowIndex = 0; rowIndex < row; rowIndex++) {
            QSqlRecord record = model->record(rowIndex);
            record.setValue(Table::PrimaryKey, rowIndex + 1);
            model->setRecord(rowIndex, record);
        }
        if (model->submitAll()) {
            model->database().commit();
        } else {
            model->database().rollback();
            qDebug() << __FUNCTION__ << 2 << model->lastError().text();
        }
    
        // reserve row for currentRowIndex
    
        // greater than "row"
        for (int32_t rowIndex = row; rowIndex < model->rowCount(); rowIndex++) {
            QSqlRecord record = model->record(rowIndex);
            record.setValue(Table::PrimaryKey, rowIndex + 2);
            model->setRecord(rowIndex, record);
        }
        if (model->submitAll()) {
            model->database().commit();
        } else {
            model->database().rollback();
            qDebug() << __FUNCTION__ << 3 << model->lastError().text();
        }
    
        return row + 1;
    }
    


  • @tovax
    Hmm, I'm a bit worried about your algorithm. Depends how this works in your database, and how the updates/transactions work, but....

    I see when you insert you renumber upwards. If this is a unique/primary key, that will keep "bumping into" the number above which already exists (hasn't been renumbered yet). Which might error?

    If it were me: to renumber for an insert I would start from the highest number, increment that, prodceed downward through the numbers. To renumber for a delete, I would start from one above the delete, decrement, then proceed upward till the last row. Makes sense?

    Of course, your approach is incredibly slow (at least for a remote/full database, not just a local file) across a large number of rows. I take it you are not concerned about having a thousand or a million of them....?


  • Lifetime Qt Champion

    A key in a db is there so it gets not reused. It's somehow contradicts the reason for a primary key... If a column with an id is deleted this id should not be used again. I also don't see why it should be needed in your case at all.



  • @Christian-Ehrlicher
    (I believe) the reason is the OP is wanting to emulate "inserting a row of data in Excel table". So he wants a row number of some description to get the ordering. Which I have said he should implement himself, not make auto-increment do it. (He might still use an auto-inc for the PK to identify the row if he wishes, but a dedicated unique column for the ordered row number usage.)


  • Lifetime Qt Champion

    A PK is not to be used for ordering or something else.



  • @Christian-Ehrlicher
    Um, that's what I've been saying.... Hence he needs something else for ordering.
    TBH it's not even the PK which is at issue here, it's the auto-increment being unsuitable for OP's ordering.



  • @JonB I'm not sure I understand your algorithm correctly.

    int32_t JCDemoDatabase::insertPrimaryKey(QSqlTableModel *model, int32_t row)
    {
        int32_t keyMax = model->record(model->rowCount() - 1).value(Table::PrimaryKey).toInt();
    
        // greater than "row"
        for (int32_t rowIndex = row; rowIndex < model->rowCount(); rowIndex++) {
            QSqlRecord record = model->record(rowIndex);
            record.setValue(Table::PrimaryKey, (keyMax + 2) + (rowIndex - row));
            model->setRecord(rowIndex, record);
        }
        if (model->submitAll()) {
            model->database().commit();
        } else {
            model->database().rollback();
            qDebug() << __FUNCTION__ << 1 << model->lastError().text();
        }
    
        return keyMax + 1; // reserve for insertion
    }
    
    int32_t JCDemoDatabase::removePrimaryKey(QSqlTableModel *model, int32_t row)
    {
        int32_t key = model->record(row).value(Table::PrimaryKey).toInt();
    
        // less than "row"
        for (int32_t rowIndex = row - 1; rowIndex >= 0; rowIndex--) {
            QSqlRecord record = model->record(rowIndex);
            record.setValue(Table::PrimaryKey, (key - 1) + (rowIndex - (row - 1)));
            model->setRecord(rowIndex, record);
        }
        if (model->submitAll()) {
            model->database().commit();
        } else {
            model->database().rollback();
            qDebug() << __FUNCTION__ << 1 << model->lastError().text();
        }
    
        return 0;
    }
    


  • @JonB I just deleted "AUTOINCREMENT" in the primary key.

    sql.append(QStringLiteral("%1 INTEGER PRIMARY KEY AUTOINCREMENT").arg(Table::PrimaryKey));
    
    sql.append(QStringLiteral("%1 INTEGER PRIMARY KEY").arg(Table::PrimaryKey));
    

    “Instead, you must your own "row number" integer variable as a column. But not auto-incremented. Instead, you must fill it with the desired row number.”
    Is this "row number" not a primary key, please?



  • @Christian-Ehrlicher Hi, I want to keep the correct row number information so that I can highlight the corresponding row in tableview after receiving the correlation signal. Row number information is not the most important, as long as the correct order can be specified, the row number information can be regenerated by sequence.
    Best regards!



  • @tovax said in Insert record into QSqlTableModel:

    @JonB I'm not sure I understand your algorithm correctly.

    Let's say you want to insert at row number rowNum, then algorithmically:

    for (int row = rowCount(); row > rowNum; row--)
        rows[row] = rows[row - 1];
        rows[row].order = row;
    record.order = rowNum;
    insertAt(rowNum, record);
    

    Let's say you want to delete at row number rowNum, then algorithmically:

    deleteAt(rowNum);
    for (int row = rowNum; row < rowCount() - 1; row++)
        rows[row] = rows[row + 1];
        rows[row].order = row;
    

    You may have to play with my code a bit, but you should get the idea.

    Is this "row number" not a primary key, please?

    Your ordering row number column value will have to change as you insert/delete rows, else it will go wrong. For example, if you have rows 1--10 and you want to insert a new one at 5 you will have to increment existing 5--10 ones to "make space" for new 5. The rows being "moved" are the same rows as they were before the move. Usually we do not change the primary key of a record in a table, it represents the record as though it were, say, a unique ID. (Indeed, your PK might me some unique ID, nothing like an incrementing number.) So... the "ordering" column would be best being its own column, so we are free to change it on an existing record --- by all means a unique-value column, but not the primary key one. Note that I do my insert/delete renumbering in such a way that I do not generate a duplicate ordering number in a row while I move rows around, so there is no chance the "unique" row number is duplicated and could error while I reorder.



  • @JonB This code is based on your algorithm, the implementation of the insertion function.
    I have some doubts:

    1. I added the "row number" column, but deleted the primary key completely. I don't know whether this is reasonable;
    2. The insertion operation in your algorithm is after reordering, but if the data is saved recursively from the last record, it seems that the last row should be inserted first;
    3. If the database is relatively large, whether such large-scale replication of data will affect the speed.
    void JCDemoDatabase::onInsertClicked()
    {
        tableModel->database().transaction();
    
        // current row
        int32_t currentRow = tableView->currentIndex().row();
    
        // append row to the end
        tableModel->insertRow(tableModel->rowCount());
    
        // reorder
        for (int row = tableModel->rowCount() - 1; row > currentRow; row--) {
            QSqlRecord record = tableModel->record(row - 1);
            record.setValue(Table::RowNumber, row);
            qDebug() << __FUNCTION__ << tableModel->setRecord(row, record);
        }
    
        // "update" new record
        QSqlRecord record = tableModel->record(currentRow);
        record.setValue(Table::RowNumber, currentRow);
        for (int32_t columnIndex = Table::Field::Min; columnIndex <= Table::Field::Max; columnIndex++) {
            record.setValue(Table::FieldText[columnIndex], QString("---"));
        }
        tableModel->setRecord(currentRow, record);
    
        // submit all
        if (tableModel->submitAll()) {
            tableModel->database().commit();
            qDebug() << __FUNCTION__ << "Error 1: " << tableModel->lastError().text();
        } else {
            tableModel->database().rollback();
            qDebug() << __FUNCTION__ << "Error 2: " << tableModel->lastError().text();
        }
    }
    


  • @tovax
    After this I'm going to have to leave you to your own devices. My suggestions were only ideas, you don't have to follow what I say.

    1. It is "usual" to have some primary key in a table, even if it's just an auto-inc ID. However, it's not mandatory. Some purists won't like without. (You might even utilise this to keep your "ordering" not in the data records but in a separate table which just has ordering number + the PK of the row in the data table, to reduce what must be renumbered; though for small data I would suspect more trouble than it's worth.)

    2. Afraid I don't know about this/what you're asking. Do whatever is necessary, may not matter.

    3. Yes, row renumbering will be slow for a lot of rows. I said so earlier. But it's not easy to come up with a better way to allow row order with insertions in the middle. I don't think this is affected by PKs or anything, it has always been an issue however you approach it.

    The problem is that from Qt you're sending each row to be renumbered. May be unavoidable with, say, SQLite. But here's the irritant for, say, a remote "proper" SQL database (MySQL, SQL Server etc.): when we want to insert a row at rowNumToInsert what we really want to execute is just a single SQL statement:

    UPDATE table SET rowNumColumn = rowNumColumn + 1 WHERE rowNumColumn >= rowNumToInsert
    

    (And btw similar when deleting a row.) No sending records over the wire, no thousands of statements to execute. If you really care you could investigate doing it this way.



  • @JonB
    OK, thank you very much for your patience. I'll think about these problems carefully in the future.
    Best regards!



  • @tovax
    Just answer one (actually two :) ) questions: how many rows are you going to have to renumber, and is the database local file or remote server on network?



  • @JonB Up to 10000 rows. It's a local file.



  • @tovax
    Hmm. Local file good news, 10,000 rows to potentially renumber not so good! :( That's quite a lot for an "Excel-like" situation. You'll have to test the speed.

    To the best of my knowledge, the way it would work in Excel is: read in the whole file, do lots of insertions in memory, rewrite the whole file on save, in the correct order of rows. So only slow on save & exit. But I can see you want/need to commit these changes as the user goes along, you can't keep them all in memory till then?

    Let's go back in history. Before you were born when I started programming ;-) we used BASIC(!). Every code line had a line number. These were initially 10 apart (10, 20, 30, ...). That allowed up to 9 new lines to be inserted between each original lines with suitable numbers before you "bumped into" an existing, used line number. You could then either move an original line up/down by, say, 5 to make some extra room; or, you could do the "renumber" command, which essentially re-renumbered the whole file back to gaps of 10. The result was a lot less overall renumbering.

    If you think about it, you could use that approach to assign your "ordering" number value to your rows so as to avoid an awful lot of the necessity to renumber many rows in the database. On the infrequent occasions that the user tries to insert a new row where there is no gap, then & only then you tell him to "hang on" while you go renumber a lot of them in the database and then carry on as before. I think that is what I would probably do if I had your situation.



  • @JonB
    I just tested 10000 rows, and it took 160 seconds to insert a row, which is really too long.

    In order to prevent sudden power failure, I need to save the user's data in real time.

    The insertion scheme you used in BASIC sounds really very good, and I'll try my best to implement it later. Thank you again.



  • @tovax said in Insert record into QSqlTableModel:

    I just tested 10000 rows, and it took 160 seconds to insert a row, which is really too long.

    Wot??!! :D So what database are you actually using? Is it SQLite? Elsewhere someone was telling me that it has some "in-memory" setting so it can be "really fast"?

    3 minutes is crazy! Purely for my own/your information, do you feel like testing that suggestion I made of a single SQL statement

    UPDATE table SET rowNumColumn = rowNumColumn + 1 WHERE rowNumColumn >= rowNumToInsert
    

    ? I'd be interested to hear how that performs!?


Log in to reply