Insert new dataset into QSqlTableModel

[MasterQ]

Hi,

I want to add a new dataset (row) to my (SQLITE) database. For this there is a modal dialog to fetch data from the user into a QSqlRecord. One field holds a filename which relates to the newly created primary key. The original filename given by the user has to be renamed according the PK and the modified filename is stored in the db for reference.

Example:
user is giving "bill_from_supplier.pdf", the newly created dataset has PK 123, the file is stored under new name, now "bill_123.pdf", in an archive and in the dataset the field "filename" is "bill_123.pdf". How to do?

my code so far:

auto record = documentsmodel->record();
auto dialog = DocumentNewDialog(nullptr, record);
if(dialog.exec() == QDialog::Accepted) {
    if(documentsmodel->insertRecord(-1, record)) {
        auto sql = QString("SELECT seq FROM sqlite_sequence WHERE name='%0'").arg(documentsmodel->tableName());
        auto query = new QSqlQuery(sql, DocumentsDbContext::Database());
        auto res = query->exec();
        auto id = query->record().value("seq").toInt();
        
// copying requested file to target archive with new name, not implemented yet, no brainer!

// TODO: update field 'filename' in the database to the new name 

        // from here it is guessing
        // how to update the existing dataset with filename?    

       ...

        documentsmodel->select();

        delete query;
     }

My questions:

1. I assume that there is no other possibility to retrieve the PK from a freshly created dataset automatically, or by Qt means, resp., right?
2. Is there a easy way to get the ModelIndex of the new dataset? Documentation is always dealing with a row number which is not really database speach or an index. Both is unknown so far.
3. the variable record still exists with all values except PK and the filename. Is it possible to take that record, modifiying the missing fields and make an update of the table in the database?

the select() command refreshes the TableView, that's not the issue, but how to write the changes into the database?

I am confused since I am not familiar yet with Modelndex and a row number in combination with a database. A row number makes no sense in a database (What I have learned!).

PS:

• documentsmodel is an object of a own subclass of QSqlTableModel
• DocumentsDbContext is an own class providing some additional parameter for the database used, like fieldnames, etc.,

[JonB]

@MasterQ
I could be mistaken, but...

Are you using submit() or submitAll() for the newly added row (you should be)? Then per https://doc.qt.io/qt-6/qsqltablemodel.html#submit

Does not automatically repopulate the model. Submitted rows are refreshed from the database on success.

[My bold] I thought it gets the inserted row and populates with the added row, e.g. with auto-generated PK? You don't see that in record?

[MasterQ]

@JonB said in Insert new dataset into QSqlTableModel:

@MasterQ
Are you using submit() or submitAll() for the newly added row (you should be)?

I do not submit by hand. Should I?

[My bold] I thought it gets the inserted row and populates with the added row, e.g. with auto-generated PK? You don't see that in record?

After the command 'insertRecord' the data are present in the DB and the variable 'record' is not repopulated. The field for the primary key is still not set.

There are no errors recorded or any other hint that something went wrong.

[MasterQ]

It is a little bit strange

If you call

insertRecord(-1, myrecord);

in QSqlTableModel, the record is written to the database immediately, if the setting is not to submit by hand.
In the datamodel a new "row"/record can be found, but this new record is fully empty. It is not filled with the data I provided by myrecord. Makes this sense?

To fill the empty record in the datamodel you have to invoke submit(). Only then the new record is populated with data from the database?

I do not see any sense in adding an empty record to the datamodel, when you have to call select() either.

I am not sure if I do it right. Any comments?

void DocumentsModel::add(QSqlRecord & record) {
    if(insertRecord(-1, record)) {
        select(); // really, no other way?
        auto id = getLastPrimaryKey();

        auto fullpathtarget = getNewPath();;
        auto fullpathsource = record.value(DocumentsFieldFilename).toString();
        QFile::copy(fullpathsource, fullpathtarget);

        auto erg = match(createIndex(0, 0), Qt::DisplayRole, id, -1);
        auto row = erg.first().row();
        auto index = createIndex(row, fieldIndex(DocumentsFieldFilename));
        setData(index, filenamenew);
        submit();
    }
}

DocumentsModel is a subclass of QSqlTableModel!