Insert record into QSqlTableModel

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

A.A.SEZEN

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.

tovax

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