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

Insert record into QSqlTableModel



  • @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!?



  • @JonB
    Only 108ms.

    UPDATE table SET rowNumColumn = rowNumColumn - 1
    


  • @tovax
    LOL! See what I meant by suggesting that would be much quicker than updating rows by sending each one to the database? :) 1/10th of a second instead of 160 seconds!

    So, at some level, if time is critical, you could find a way to exploit this. Maybe something like: use the 10-gap numbering we discussed. While that has a gap for your insert, fine. If you do need to "renumber": instead of updating each record, send that (or some other appropriate) SQL statement to the database, and just read back in the lines at that point to replace what you had in memory?


  • Lifetime Qt Champion

    @tovax I'm really wondering why you want to number rows in the database? What is the use case? If it is for showing in the UI then there is no point in adding the row numbers already in the database.



  • @jsulm
    Dear @jsulm, have you read through this whole discussion? :) The whole point is that the OP needs a database table which can do what happens in a spreadsheet/Excel, i.e. rows have an order, rows come before or after each other. Unlike what one normally has to deal with in a relational database, but a perfectly valid question. And the user has to be able to insert/delete rows, with immediate commit to the database, in real time. When you come up with a better suggestion than I have dealing with OP and suggesting how he can impose & manage a row ordering, I should be most interested to hear!

    @tovax
    BTW, I don't think you have ever said which database you are using, just in case it has anything of its own supporting "row order"? SQL itself does not, but you never know what an implementation might add.



  • @JonB Hi, I am using sqlite3.
    I updated the algorithm based on your reply. In the case of 10,000 rows in the database, now it only takes 600ms to insert or delete a row. But I’m sorry to say that I’m not sure if I really understand what you mean: first renumber the “Rownumber” column of the database directly, then read the database to the tablemodel, and then display the sorted data in the tableview. code show as below:
    Insert:

    void JCDemoDatabase::onInsertClicked()
    {
        // 1 - begin
        QSqlQuery query(database);
        QString sql;
        QElapsedTimer timer;
        timer.start();
    
        int32_t currentRowIndex = tableView->currentIndex().row();
    
        // 2 - update first
        sql.clear();
        sql.append(QStringLiteral("UPDATE %1 SET %2 = -(%2 + 1) WHERE %2 >= %3")
                   .arg(Table::Name)
                   .arg(Table::RowNumber)
                   .arg(currentRowIndex));
        query.prepare(sql);
        if (!query.exec()){
            QMessageBox::critical(this, QStringLiteral("Error"), QStringLiteral("1:%1").arg(query.lastError().text()), QMessageBox::Abort);
            return;
        }
    
        // 3 - update second
        sql.clear();
        sql.append(QStringLiteral("UPDATE %1 SET %2 = -(%2) WHERE %2 < 0")
                   .arg(Table::Name)
                   .arg(Table::RowNumber));
        query.prepare(sql);
        if (!query.exec()){
            QMessageBox::critical(this, QStringLiteral("Error"), QStringLiteral("2:%1").arg(query.lastError().text()), QMessageBox::Abort);
            return;
        }
    
        // 4 - insert
        sql.clear();
        sql.append(QStringLiteral("INSERT INTO %1 (A, B, C, D, E, F, G, H, RowNumber)"
                                  "VALUES (:A, :B, :C, :D, :E, :F, :G, :H, :RowNumber)")
                   .arg(Table::Name));
        query.prepare(sql);
        query.bindValue(":A", QString("---"));
        query.bindValue(":B", QString("---"));
        query.bindValue(":C", QString("---"));
        query.bindValue(":D", QString("---"));
        query.bindValue(":E", QString("---"));
        query.bindValue(":F", QString("---"));
        query.bindValue(":G", QString("---"));
        query.bindValue(":H", QString("---"));
        query.bindValue(":RowNumber", currentRowIndex);
        if (!query.exec()){
            QMessageBox::critical(this, QStringLiteral("Error"), QStringLiteral("3:%1").arg(query.lastError().text()), QMessageBox::Abort);
            // return;
        }
    
        // 5 - end
        query.finish();
        tableModel->select();
        int32_t rowNumberIndex = tableModel->record().indexOf(Table::RowNumber);
        tableModel->sort(rowNumberIndex, Qt::AscendingOrder);
        QMessageBox::information(this, QStringLiteral("Info"), QStringLiteral("Time:%1 ms").arg(timer.elapsed()), QMessageBox::Ok);
    }
    

    Remove:

        // 1 - begin
        QSqlQuery query(database);
        QString sql;
        QElapsedTimer timer;
        timer.start();
    
        int32_t currentRowIndex = tableView->currentIndex().row();
    
        // 2 - remove
        sql.clear();
        sql.append(QStringLiteral("DELETE FROM %1 WHERE %2 = %3")
                   .arg(Table::Name)
                   .arg(Table::RowNumber)
                   .arg(currentRowIndex)
                   );
        query.prepare(sql);
        if (!query.exec()){
            QMessageBox::critical(this, QStringLiteral("Error"), QStringLiteral("1:%1").arg(query.lastError().text()), QMessageBox::Abort);
            // return;
        }
    
        // 3 - update first
        sql.clear();
        sql.append(QStringLiteral("UPDATE %1 SET %2 = -(%2 - 1) WHERE %2 > %3")
                   .arg(Table::Name)
                   .arg(Table::RowNumber)
                   .arg(currentRowIndex));
        query.prepare(sql);
        if (!query.exec()){
            QMessageBox::critical(this, QStringLiteral("Error"), QStringLiteral("2:%1").arg(query.lastError().text()), QMessageBox::Abort);
            return;
        }
    
        // 4 - update second
        sql.clear();
        sql.append(QStringLiteral("UPDATE %1 SET %2 = -(%2) WHERE %2 < 0")
                   .arg(Table::Name)
                   .arg(Table::RowNumber));
        query.prepare(sql);
        if (!query.exec()){
            QMessageBox::critical(this, QStringLiteral("Error"), QStringLiteral("3:%1").arg(query.lastError().text()), QMessageBox::Abort);
            return;
        }
    
        // 5 - end
        query.finish();
        tableModel->select();
        int32_t rowNumberIndex = tableModel->record().indexOf(Table::RowNumber);
        tableModel->sort(rowNumberIndex, Qt::AscendingOrder);
        QMessageBox::information(this, QStringLiteral("Info"), QStringLiteral("Time:%1 ms").arg(timer.elapsed()), QMessageBox::Ok);
    


  • @jsulm Hi, Maybe I didn't express enough. The records in this database are arranged in order, such as the motor trajectory of CNC machine tools.
    Best regards!



  • @tovax said in Insert record into QSqlTableModel:

    I updated the algorithm based on your reply. In the case of 10,000 rows in the database, now it only takes 600ms to insert or delete a row.

    Great! Isn't that an improvement? :)

    But I’m sorry to say that I’m not sure if I really understand what you mean: first renumber the “Rownumber” column of the database directly, then read the database to the tablemodel

    You start with a model with certain row numbers in each row. When you execute an UPDATE table SET rowNumColumn = rowNumColumn - 1-type statement the row numbers potentially change in any number of the rows you currently have in your read-in table model, making that now not up-to-date; but Qt infrastructure does not know that you have made this change to many other rows. So, you need to re-read the rows into the model to ensure they reflect the new situation. In your code, after update-renumbering, I see you have:

    tableModel->select();
    

    That does indeed read the rows into the model afresh, so I think your code does exactly what is wanted. Verify that after this your tableModel row contents now do indeed have the correctly renumbered row number values?



  • @JonB Yes, all contents and functions are correct. Thank you again.
    Best regards!


  • Lifetime Qt Champion

    I would add a transaction around it and not use prepared statement (since there is nothing to prepare)



  • @Christian-Ehrlicher
    Hi, thank you for your reply.
    Added transaction statement according to your suggestion.
    JCDemoDatabase



  • Is the record deleted from the database actually deleted? Or is it hidden? What you see as a gap is perhaps just hidden. Perhaps for this reason, autoincrement is only forward. It might be a good choice to let the database do its job.



  • @A-A-SEZEN
    The record is indeed deleted, the order between the records is achieved by the "UNIQUE RowNumber" column, not "PrimaryKey Autoincrement". The insertion and deletion of records are directly operated on the database, and then read to the TableModel and sorted display in TableView.


Log in to reply