Insert record into QSqlTableModel

tovax

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

JonB

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

tovax

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

I added the "row number" column, but deleted the primary key completely. I don't know whether this is reasonable;
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;
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();
    }
}

JonB

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

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.)
Afraid I don't know about this/what you're asking. Do whatever is necessary, may not matter.
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.

tovax

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

JonB

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

tovax

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

JonB

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

tovax

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

JonB

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

tovax

@JonB
Only 108ms.

UPDATE table SET rowNumColumn = rowNumColumn - 1

JonB

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

jsulm

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

JonB

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

tovax

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

tovax

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

JonB

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

tovax

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

Christian Ehrlicher

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

tovax

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