How to show uncommitted records of SQLite in QTableView and save/commit later?

[deleted385]

Here's what I've right now:

void QueryResultView::executeQuery(const QString &query){
    this->query = query;
    auto widget = static_cast<QueryWidget*>(parent()->parent());
    if(!isConnected){
        emit widget->logMessage("e,Not Connected to a database");
        return;
    }
    timer.start();
    auto numStatements = query.split(';', Qt::SkipEmptyParts);
    db.open();
    if(numStatements.size() == 1) executeSingle(numStatements.first(), widget);
    else{
        bool gotError = false;
        db.transaction();
        QSqlQueryModel testModel;
        for (int i = 0; i < numStatements.size(); i++) {
            testModel.setQuery(numStatements[i]);
            if(model->lastError().type() != QSqlError::NoError){
                emit widget->logMessage("e," + model->lastError().text());
                gotError = true;
                break;
            }
            else if(testModel.query().isSelect()){
                emit widget->logMessage("e,SELECT isn't allowed while executing multiple statements");
                gotError = true;
                break;
            }
        }
        if(gotError) {
            db.rollback();
            db.close();
            return;
        }
        db.commit();
        db.close();
        emit widget->logMessage("s,Execution finished in " + QString::number(timer.elapsed()) + " ms. Returned " + QString::number(model->rowCount()) + " row(s)");      
        emit widget->noSelect(query);
        emit widget->transactionComplete(); // to enable commit/rollback QActions
    }
}

with all these when I execute multiple insert/update, it shows the changes in the QTableView in other view, initially there was no row in that table, like this:

If I remove db.commit(); db.close(); before those last 3 emit, it doesn't show the uncommitted changes in the QTableView:

When I clicked the second to last QAction on the toolbar, it executed these:

void QueryResultView::commitOrRollback(bool isCommit){
    if(isCommit) db.commit();
    else db.rollback();
    db.close();
    emit static_cast<QueryWidget*>(parent()->parent())->noSelect(query);
}

it called db.commit(); and emitted noSelect signal and in other view's slot these were executed for that signal:

void TableWidget::onNoSelect(const QString &query){
    if(query.contains(tableName)){
        queryDatabase();
        countLable->setText(QString::number(tableProxy->rowCount()));
    }
}
void TableWidget::queryDatabase(){
    db.open();
    tableModel->setTable(tableName);
    tableHeader->resetBoxes(tableModel->columnCount());
    tableProxy->setQueries(tableHeader->getQueries());
    tableModel->select();
    while (tableModel->canFetchMore()) tableModel->fetchMore();
    db.close();
}

first, the uncommitted changes were not displayed in the QTableView and second, when I clicked commit button it neither saved the changes in the database nor dislayed in the QTableView.

[JonB]

@Emon-Haque said in How to show uncommitted records of SQLite in QTableView and save/commit later?:

If I remove db.commit(); db.close(); before those last 3 emit, it doesn't show the uncommitted changes in the QTableView:

So far as I understand your logic, the emit widget->noSelect(query); will call onNoSelect(), and that will likely call queryDatabase() which resets the table to what is in the database. So what "uncommitted records" are left?

Do you really want to be re-opening and closing the database constantly?

emit static_cast<QueryWidget*>(parent()->parent())->noSelect(query);

As an observation: If you need static_cast<>s in your code like you have it is not a good idea, as I thought was proven in another thread. Also a view should not need to emit a QueryWidget's signal, nor access it through its parentage. Whatever you're doing here, why don't you find a cleaner way?

[deleted385]

@JonB, You are correct in understanding my logic. Is it possible, somehow or with some other class, to stop it resetting the database and make it read all committed as well as uncommitted records?

I thought I wouldn't be able to access the database in other app if I leave it open. Now, I've tested and I can open it in other app, even if I leave it open, so I don't have to call open/close every time. Will change that static_cast to dynamic/qobject cast later.

Do you suggest, creating a signal in child widget (QueryResultView) and consume that signal in the parent widget (QueryWidget) and emit another signal, in the consuming slot, from the QueryWidget to let it notify its sibling (TableWidget) to do the work necessary in its slot?

[JonB]

@Emon-Haque
Your uncommitted reads are only local in your model. If you re-read the records from the database they will get lost. So do not do that if you wish to retain anything you have only in-memory.

I don't even know what the purpose of your onNoSelect slot is, or why you need to raise the signal.

If the only purpose of your accessing parent()->... is to emit its signals, get rid of both of those.

If you do need a signal consider raising your own. If you really need to access the QueryWidget() pass a proper pointer to it in the constructor and let this view have legitimate access to it. And for preference call non-signal methods in it and let it emit its own signals, at least IMHO. Though as I say I doubt you need any of this.

[deleted385]

@JonB, the noSelect(query) signal, for now, is being used only by TableWidget (second view I go by clicking on the bottom button in side toolbar), a sibling of QueryWidget. I also have plan to use that signal in the ObjectView (child widget of QueryWidget, left pane of first view). It passes the query with the signal. The sibling, TableWidget, will check if the query contains INSERT, UPDATE, DELETE, if it does then it'll re-query the table. Similarly, the child, ObjectView will check whether it contains CREATE, ALTER, DROP, if it does then the child will re-query the sqlite_master to update its QTreeView.

[JonB]

@Emon-Haque
Sounds all very complicated. Do your database stuff from your (subclass of the) model, not so much from views and widgets. If your views need to know that it has issued an INSERT or a CREATE or whatever, let the model emit a signal when it does so (there are already several inbuilt ones from the model when it changes) and attach slots from whatever views need to know about it, Up to you.

[deleted385]

@JonB, is it possible with one QSqlQueryModel? In the QueryResultView, where I emit noSelect signal, I've a private QSqlQueryModel *model to show data retrieved by select statement in the right pane of the first view and if the statement is not select, I emit that signal.

In the void QueryResultView::executeQuery(const QString &query), posted in the original question, I create a local QSqlQueryModel testModel. When I modify the database table, private model is currently pointing to, with the local testModel, looks like the private model automatically resets/changes:

When I executed SELECT * from TestTable, the private model got those 3 rows as shown in right pane (Result). When I executed the 5 INSERT/UPDATE/DELETE statements with local testModel, the QTableView on the right pane (Result) was automatically removed/cleared!

No where in my code I touched the private model or QTableView on the right pane (Result).

EDIT
Your db open/close suggestion did the trick, I think. Looks like I can read dirty (uncommitted) data if I remove all those open/close. Right now I've only one open when I choose the database with file dialog. With that one open, now when I execute multiple insert/update statements, the QTableView of the TableWidgets updates and when I click rollback/ commit they are removed/written. Need to test more before being certain about that behavior.

EDIT
Yes, those open/close all over was the issue. See it works, as expected, now:

Now the only problem is why does it reset my private model and clear the QTableView on the right pane when I modify the underlying database table.

[deleted385]

No idea how it's fixed automatically!

In the original posit I'd this if(model->lastError().type() != QSqlError::NoError) instead of ... testModel.lastError().type() ... and emit ... QString::number(model->rowCount()) + " row(s)");

due to copy/paste BUT those were changed long before and the private model/QTableView on the right pane kept getting cleared even after correction!

After a break, I just hit Ctrl+R and executed same set of statements and the private model/QTableView doesn't get cleared anymore even if I have those copy/paste errors in my code.