One database table source for multiple models and views



  • I am trying to figure out how to request an update of a QTableView from a model that is not its source (or another view.)

    I have a SQLITE table that is being read by SQLITE views.

    Each SQLITE view is being set as the table for a subclassed instance of QSqlTableModel. Each SqlTableModel instance is set as source for a QSortFilterProxyModel instance (which adds nothing right now.) Each QSortFilterProxyModel instance is set as source for a QTabelView instances and all QTabelView instances are visible at the same time within the same widget.

    There is a SQLITE trigger to update the main table if the most detail view (the one without any summed data) is updated. (fyi: SQLITE views are read-only.)

    My SQLITE views differ from one another in that each one is summarized up a little more than the next.

    What works now is I can submit a change to my detail QTableView instance and it will push the change to the table throught the SQLITE Trigger. I know that it has changed because I have an extra field on my SQLITE views that does not exist on the table and it gets updated correctly on a submit. I also know if submits because I've been checking the database backend through the SQLITE command line tool.

    Is there a way to get the other Qt views to refresh when I make a change in a seperate one? Currently, I have to close the application and reopen it to see the changes.

    I have searched the web regarding this issue and found a few responses like below:
    "http://www.qtcentre.org/threads/19792-how-to-update-a-tree-view-model":http://www.qtcentre.org/threads/19792-how-to-update-a-tree-view-model
    ... where they mention overriding the setData method in the model and emit dataChanged from it, so I did:

    [code]
    bool SQLTableModel::setData(
    const QModelIndex &index
    , const QVariant &value
    , int role) {
    bool runningOkay = true;

    if (index.isValid() && role == Qt::EditRole) {
    runningOkay = QSqlTableModel::setData(index, value, role);

    emit QSqlTableModel::dataChanged(index, index);
    }
    else {
    runningOkay = false;
    }

    return runningOkay;
    }
    [/code]

    I also added the following code to my widget that holds the Qt model and view instances:

    [code]
    connect(
    balanceItemModel
    , SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &))
    , balanceCategoryModel
    , SLOT(dataChanged(const QModelIndex &, const QModelIndex &)));
    [/code]

    This did not work so I am wondering where to look next.



  • There is no mechanism whereby sqlite causes a QSqlModel to refresh automatically when data in a table changes. It sounds like you are assuming that there is such a mechanism and that it will even work when QSqlModel is looking at a database view that depends on a table whose data changes.

    This is something you have to arrange in your application. One way is to have your models signal each other to refresh.

    More free advice: If you use QSqlTableModel for updates and deletes on a database view (or table without primary key), call setPrimaryKey() explicitly. The primary key cannot be automatically detected for a database view. Without the primary key, QSqlTableModel will generate WHERE clauses using all columns. This goes wrong for columns of float type.

    Mark



  • You are correct. I was expecting the other Qt models that point to the same SQLITE views based on the same datasource to update automatically. Since that does not happen on its own, and from your point will not happen, I tried to use the connect to signal my models to update (as seen in the original post when balanceItemModel is the QSqlTableModel subclass instance behind my Qt detail view and balanceCategoryModel is one of the summary model instances.)

    I tried the signal/slot dataChanged, but that did not work. Something else that I noticed is I can type garbage into the SIGNAL or SLOT constants/macros and not get a compiler error. In other words, if it isn't a valid signal or slot, it will not return an error. But that is only holding me back because I haven't got a clear grasp on what signals or slots are available even after reading the Qt docs online. (I have more studying to do, obviously.)

    (I see now that I didn't state this directly above, and I would like to clarify. When I edit and change a value in my detail Qt view, I see the changed value in that Qt view. In other words, it changes. Somewhere in the process, it must be updating the Qt model and repainting the Qt view.)

    I guess my question at this point should be, "What signals and slots are available to QSqlTableModel, QSortFilterProxyModel, or QTableView that I can use between two unconnected, unrelated instances of them to request a refresh/requery/repaint?"

    Thanks for your insight and the note on primary keys.



  • I attempted the two following methods with no progress:

    attempt #1
    @
    connect(
    balanceItemModel
    , SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &))
    , balancePeriodView
    , SLOT(reset()));
    @

    attempt #2
    @
    connect(
    balanceItemModel
    , SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &))
    , balancePeriodModel
    , SLOT(requestModelRefresh()));
    @
    ... and in SQLTableModel.hpp
    @
    public slots:
    void requestModelRefresh() {
    beginResetModel();
    reset();
    endResetModel();
    }
    @



  • I placed a SQLite "instead of" trigger on the Category SQLite view (which is one summarized level up from the full detail SQLite view.)

    This trigger is different from the detail view one:
    @
    "create trigger if not exists budgetCategoryTrigger\n"
    " instead of\n"
    " update on budgetCategory\n"
    " for each row\n"
    " begin\n"
    " select *\n"
    " from\n"
    " budgetCategory;\n"
    " end;\n";
    @

    Now it will update if I submit a change to the Qt detail view, but it updates only after I try a submit to the Qt Category view itself. I want it to update automatically.

    This means I'm not using the reset functionality correctly, no matter if I connect QSqlTableModel::dataChanged(...) to either the QTableView::reset() function or to the target QSqlTableModel::beginResetModel/reset/endResetModel from within a subclass' slot. (see email above.)

    There must be a way to do this.



  • I found a suitable solution to my problem.

    I created an enum of four constants representing my four SQLITE Views. I created four radio buttons to represent the different view choices.

    I connected the button group that contained the four buttons to the model via:
    @
    connect(
    buttonGroup
    , SIGNAL(buttonClicked(int))
    , balanceModel
    , SLOT(setSQLView(int)));
    @

    Then I created the following slot with these case statements for each of my buttons inside the QSqlTableModel subclass:

    @
    public slots:
    void setSQLView(int buttonId) {
    switch(buttonId) {
    ...
    case SqlViewId_Category:
    setTable("budgetCategory");
    select();
    break;
    ...
    default:
    setTable("budgetItem");
    select();
    break;
    }
    @

    This does not allow me to see all of the views at once, but it does switch the model/proxymodel/view to show the updated, user-selected SQLITE view data.

    I imagine that I could have done something using "select()" while showing all of the Qt views at once.



  • Indeed, simply using select() inside the custom slot of the summary model connected to dataChanged in the original model allows all of the separate Qt views to be updated and shown at the same time when the detail Qt view has a change submitted. (I removed the extra trigger code that I was testing with and only have the detail SQLITE view trigger.)

    in BalanceWidget.hpp
    @
    connect(
    balanceItemModel
    , SIGNAL(dataChanged(const QModelIndex &, const QModelIndex &))
    , balancePeriodModel
    , SLOT(requestModelRefresh()));
    ...
    @

    in SqlTableModel.hpp
    @
    public slots:
    void requestModelRefresh() {
    select();
    }
    @



  • This is not a bad approach. In fact, as you have already figured out yourself, you just have to re-do the query on the sql table (QSqlQuery::exec()). I don't see another way to do this.

    Bye!
    Seba



  • That's true, Seba. When I started I used QSqlQuery for my detail model (the only one I had at the time,) but I was limited to read-only results so I tabled that idea. However, that's all I need for the three summary Qt views. Good idea!

    I could have used a subclass of QSqlQuery and called ::exec() from a slot for the summary Qt views upon every submit to the detail Qt view. That way, I would not have separate SQLite views, just the single one for the detail info.

    Thanks.


Log in to reply
 

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