Multiple QSqlTableModels edited together in one transaction



  • I have a window in a Qt application using PostgreSQL 9.3 database. The window is a form used do display, edit and insert new data. t looks like that:

    !http://i.stack.imgur.com/4Yu62.jpg(data edit form)!

    I have data from 3 sql tables in that view. the tables are related with foreign keys:

    • contractors (main table) - mapped to "personal data" section
    • contacts (has foreign key to contractors.ID)
    • addresses (has foreign key to contractors.ID)

    So - in my window's class I have 3 main models (+ 2 proxy models to transpose tables in "personal data" an "address data" sections). I use QSqlTableModel for theese sesctions, and a QSqlRelationalTableModel for contactData section. when opening that window "normally" (to view some contractor), i simply pass contractor's ID to the constructor and store it in proper variable. Also, I call the QSqlTableModel::​setFilter(const QString & filter) method for each of the models, and set the proper filtering. When opening that window in "add new" mode i simply pass a "-1" or "0" value to the ID variable, so no data gets loaded to the model. All 3 models have QSqlTableModel::OnManualSubmit editStrategy. When saving the data (triggered by clicking a proper button), I start a transaction. And then I submit models one-by-one. personalData model gets submitted first, as I need to obtain it's PK after insert (to set in the FK fields in other models). When submitting of the model fails, I show a messageBox with the QSqlError content, rollback the transaction and return from the method. When I have an error on the first model being processed - no problem, as nothing was inserted. But when the first model is saved, but the second or third fails - there is a little problem. So I rollback the transacion as before, and return from the function. But after correcting the data and submitting it again - the first model is not trying to submit - as it doesn't know that there was a rollback, and the data needs to be inserted again. What would be a good way to notice such a model, that it needs to be submited once again? At the moment I ended up with something like that:

    @void kontrahenciSubWin::on_btnContractorAdd_clicked() {
    //QStringList errorList; // when error occurs in one model - whole transacion gets broken, so no need for a list
    QString error;
    QSqlDatabase db = QSqlDatabase::database();

    //backup the data - in case something fails and we have to rollback the transaction
    QSqlRecord personalDataModelrec = personalDataModel->record(0); // always one row. will get erased by SubmitAll, as no filter is set, because I don't have its ID.
    
    QList<QSqlRecord> contactDataModelRecList;
    for (int i = 0 ; i< contactDataModel->rowCount(); i++) {
        contactDataModelRecList.append( contactDataModel->record(i) );
    }
    
    QList<QSqlRecord> addressDataModelRecList;
    for (int i = 0 ; i< addressDataModel->rowCount(); i++) {
        addressDataModelRecList.append( addressDataModel->record(i) );
    }
    
    db.transaction();
    if ( personalDataModel->isDirty() && error.isEmpty() ) {
        if (!personalDataModel->submitAll()) //submitAll calls select() on the model, which destroys the data as the filter is invalid ("where ID = -1")
            //errorList.append( personalDataModel->lastError().databaseText() );
            error = personalDataModel->lastError().databaseText(); 
        else {
            kontrahentid = personalDataModel->query().lastInsertId().toInt(); //only here can I fetch ID
            setFilter(ALL); //and pass it to the models
        }
    }
    
    if ( contactDataModel->isDirty() && error.isEmpty() ) 
        if (!contactDataModel->submitAll()) //slot on_contactDataModel_beforeInsert() sets FK field
            //errorList.append( contactDataModel->lastError().databaseText() );
            error = contactDataModel->lastError().databaseText();
    
    if ( addressDataModel->isDirty() && error.isEmpty() )
        if (!addressDataModel->submitAll()) //slot on_addressDataModel_beforeInsert() sets FK field
            //errorList.append( addressDataModel->lastError().databaseText() );
            error = addressDataModel->lastError().databaseText();
    
    //if (!errorList.isEmpty()) {
    //  QMessageBox::critical(this, tr("Data was not saved!"), tr("The following errors occured:") + " \n" + errorList.join("\n"));
    if (!error.isEmpty()) {
        QMessageBox::critical(this, tr("Data was not saved!"), tr("The following errors occured:") + " \n" + error);
    
        db.rollback();
        personalDataModel->clear();
        contactDataModel->clear();
        addressDataModel->clear();
        initModel(ALL); //re-init models: set table and so on.
    
        //re-add data to the models - backup comes handy
        personalDataModel->insertRecord(-1, personalDataModelrec);
    
        for (QList<QSqlRecord>::iterator it = contactDataModelRecList.begin(); it != contactDataModelRecList.end(); it++) {
            contactDataModel->insertRecord(-1, *it);
        }
    
        for (QList<QSqlRecord>::iterator it = addressDataModelRecList.begin(); it != addressDataModelRecList.end(); it++) {
            addressDataModel->insertRecord(-1, *it);
        }
    
        return;
    }
    db.commit();
    isInEditMode = false;
    handleGUIOnEditModeChange();
    

    }@

    Does anyone have a better idea? I doubt if it's possible to ommit backing-up the records before trying to insert them. But maybe there is a better way to "readd" them to the model? I tried to use "setRecord", and "remoweRows" & "insertRecord" combo, but no luck. Resetting the whole model seems easiest (I only need to re-init it, as it loses table, filter, sorting and everything else when cleared)
    I wish Qt had a feature, that would allow to somehow "bind" a transaction object to the model (or some models to a transaction), and that the model could react itself when the transaction is rollback'ed


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.