Insert record into QSqlTableModel

JonB

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

tovax

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

jsulm

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

tovax

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

tovax

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

jsulm

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

tovax

@jsulm The row which the mouse clicked.

tovax

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

jsulm

This post is deleted!

JonB

@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

tovax

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

JonB

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

Christian Ehrlicher

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.

JonB

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

Christian Ehrlicher

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

JonB

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

tovax

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

tovax

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

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.