How to refresh a QSqlTableModel while preserving the selection?

Question

I am using a QSqlTableModel and QTableView to view an SQLite database table.

I would like to have the table auto refresh every second or so (it's not going to be a very large table - a couple of hundred rows). And i can do this - like so:

QTimer *updateInterval = new QTimer(this);
updateInterval->setInterval(1000);
updateInterval->start();
connect(updateInterval, SIGNAL(timeout()),this, SLOT(update_table()));

...

void MainWindow::update_table()
{
    model->select(); //QSqlTableModel*
    sqlTable->reset(); //QTableView*
}

But this removes any selection I have, so the selections only last for up to a second. This is annoying, as another pane in the GUI depends on what is selected. If nothing is selected, then it resets to an explanation splash page.

I then tried a somewhat hacky approach, which gets the selected row number, resets the table, and then selects that row. But this doesn't work either, as the selected row can move up or down based on additions to the table.

I know other classes have a dataChanged() signal, which would be ideal.

Do any of you know how I could have the table refresh to reflect changes to the database (from either command line usage, or other instances of the program) AND keep the current selection?

I know I could get data from the current selection, and then after the reset search for the same row and then reselect it, but this seems like a counter productive and bad solution to the problem.

EDIT: Current attempt at solution:

void MainWindow::update_table()
{    

    QList<QModelIndex> selection = sqlTable->selectionModel()->selection().indexes();
    QList<int> selectedIDs;
    bool somethingSelected = true;

    for(QList<QModelIndex>::iterator i = selection.begin(); i != selection.end(); ++i){
        int col = i->column();
        QVariant data = i->data(Qt::DisplayRole);

    if(col == 0) {
            selectedIDs.append(data.toInt());
        }
    }

    if(selectedIDs.empty()) somethingSelected = false;

    model->select();
    sqlTable->reset();

    if(somethingSelected){
        QList<int> selectedRows;

        int rows = model->rowCount(QModelIndex());
        for(int i = 0; i < rows; ++i){
            sqlTable->selectRow(i);
            if(selectedIDs.contains(sqlTable->selectionModel()->selection().indexes().first().data(Qt::DisplayRole).toInt())) selectedRows.append(i);
    }

    for(QList<int>::iterator i = selectedRows.begin(); i != selectedRows.end(); ++i){
        sqlTable->selectRow(*i);
    }
}
}

Okay so this more or less works now...

Answer 1

The real deal is the primary key on the result of your query. Qt's APIs offer a rather circuitous route from QSqlTableModel::primaryKey() to a list of columns. The result of primaryKey() is a QSqlRecord, and you can iterate over its field()s to see what they are. You can also look up all of the fields that comprise the query proper from QSqlTableModel::record(). You find the former in the latter to get a list of model columns that comprise the query.

If your query doesn't contain a primary key, you'll have to design one yourself and offer it using some protocol. For example, you can choose that if primaryKey().isEmpty() is true, the last column returned by the model is to be used as the primary key. It's up to you to figure out how to key the result of an arbitrary query.

The selected rows can then be indexed simply by their primary keys (a list of values of the cells that comprise the key -- a QVariantList). For this, you could use a custom selection model (QItemSelectionModel) if its design wasn't broken. The key methods, such as isRowSelected() aren't virtual, and you can't reimplement them :(.

Instead, you can use a proxy model that mimicks selection by providing a custom Qt::BackgroundRole for the data. Your model sits on top of the table model, and keeps a sorted list of selected keys. Each time the proxy model's data() is called, you get the row's key from underlying query model, then search for it in your sorted list. Finally, you return a custom background role if the item is selected. You'll have to write relevant comparison operator for QVariantList. If QItemSelectionModel was usable for this purpose, you could put this functionality into a reimplementation of isRowSelected().

The model is generic since you subscribe to a certain protocol for extracting the key from a query model: namely, using primaryKey().

Instead of using primary keys explicitly, you can also use persistent indices if the model supports them. Alas, until at lest Qt 5.3.2, QSqlTableModel does not preserve the persistent indices when the query is rerun. Thus, as soon as the view changes the sort order, the persistent indices become invalid.

Below is a fully worked out example of how one might implement such a beast:

#include <QApplication>
#include <QTableView>
#include <QSqlRecord>
#include <QSqlField>
#include <QSqlQuery>
#include <QSqlTableModel>
#include <QIdentityProxyModel>
#include <QSqlDatabase>
#include <QMap>
#include <QVBoxLayout>
#include <QPushButton>

// Lexicographic comparison for a variant list
bool operator<(const QVariantList &a, const QVariantList &b) {
   int count = std::max(a.count(), b.count());
   // For lexicographic comparison, null comes before all else
   Q_ASSERT(QVariant() < QVariant::fromValue(-1));
   for (int i = 0; i < count; ++i) {
      auto aValue = i < a.count() ? a.value(i) : QVariant();
      auto bValue = i < b.count() ? b.value(i) : QVariant();
      if (aValue < bValue) return true;
   }
   return false;
}

class RowSelectionEmulatorProxy : public QIdentityProxyModel {
   Q_OBJECT
   Q_PROPERTY(QBrush selectedBrush READ selectedBrush WRITE setSelectedBrush)
   QMap<QVariantList, QModelIndex> mutable m_selection;
   QVector<int> m_roles;
   QBrush m_selectedBrush;
   bool m_ignoreReset;
   class SqlTableModel : public QSqlTableModel {
   public:
      using QSqlTableModel::primaryValues;
   };
   SqlTableModel * source() const {
      return static_cast<SqlTableModel*>(dynamic_cast<QSqlTableModel*>(sourceModel()));
   }
   QVariantList primaryValues(int row) const {
      auto record = source()->primaryValues(row);
      QVariantList values;
      for (int i = 0; i < record.count(); ++i) values << record.field(i).value();
      return values;
   }
   void notifyOfChanges(int row) {
      emit dataChanged(index(row, 0), index(row, columnCount()-1), m_roles);
   }
   void notifyOfAllChanges(bool remove = false) {
      auto it = m_selection.begin();
      while (it != m_selection.end()) {
         if (it->isValid()) notifyOfChanges(it->row());
         if (remove) it = m_selection.erase(it); else ++it;
      }
   }
public:
   RowSelectionEmulatorProxy(QObject* parent = 0) :
      QIdentityProxyModel(parent), m_roles(QVector<int>() << Qt::BackgroundRole),
      m_ignoreReset(false) {
      connect(this, &QAbstractItemModel::modelReset, [this]{
         if (! m_ignoreReset) {
            m_selection.clear();
         } else {
            for (auto it = m_selection.begin(); it != m_selection.end(); ++it) {
               *it = QModelIndex(); // invalidate the cached mapping
            }
         }
      });
   }
   QBrush selectedBrush() const { return m_selectedBrush; }
   void setSelectedBrush(const QBrush & brush) {
      if (brush == m_selectedBrush) return;
      m_selectedBrush = brush;
      notifyOfAllChanges();
   }
   QList<int> selectedRows() const {
      QList<int> result;
      for (auto it = m_selection.begin(); it != m_selection.end(); ++it) {
         if (it->isValid()) result << it->row();
      }
      return result;
   }
   bool isRowSelected(const QModelIndex &proxyIndex) const {
      if (! source() || proxyIndex.row() >= rowCount()) return false;
      auto primaryKey = primaryValues(proxyIndex.row());
      return m_selection.contains(primaryKey);
   }
   Q_SLOT void selectRow(const QModelIndex &proxyIndex, bool selected = true) {
      if (! source() || proxyIndex.row() >= rowCount()) return;
      auto primaryKey = primaryValues(proxyIndex.row());
      if (selected) {
         m_selection.insert(primaryKey, proxyIndex);
      } else {
         m_selection.remove(primaryKey);
      }
      notifyOfChanges(proxyIndex.row());
   }
   Q_SLOT void toggleRowSelection(const QModelIndex &proxyIndex) {
      selectRow(proxyIndex, !isRowSelected(proxyIndex));
   }
   Q_SLOT virtual void clearSelection() {
      notifyOfAllChanges(true);
   }
   QVariant data(const QModelIndex &proxyIndex, int role) const Q_DECL_OVERRIDE {
      QVariant value = QIdentityProxyModel::data(proxyIndex, role);
      if (proxyIndex.row() < rowCount() && source()) {
         auto primaryKey = primaryValues(proxyIndex.row());
         auto it = m_selection.find(primaryKey);
         if (it != m_selection.end()) {
            // update the cache
            if (! it->isValid()) *it = proxyIndex;
            // return the background
            if (role == Qt::BackgroundRole) return m_selectedBrush;
         }
      }
      return value;
   }
   bool setData(const QModelIndex &, const QVariant &, int) Q_DECL_OVERRIDE {
      return false;
   }
   void sort(int column, Qt::SortOrder order) Q_DECL_OVERRIDE {
      m_ignoreReset = true;
      QIdentityProxyModel::sort(column, order);
      m_ignoreReset = false;
   }
   void setSourceModel(QAbstractItemModel * model) Q_DECL_OVERRIDE {
      m_selection.clear();
      QIdentityProxyModel::setSourceModel(model);
   }
};

int main(int argc, char *argv[])
{
   QApplication app(argc, argv);
   QWidget w;
   QVBoxLayout layout(&w);

   QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
   db.setDatabaseName(":memory:");
   if (! db.open()) return 255;

   QSqlQuery query(db);
   query.exec("create table chaps (name, age, constraint pk primary key (name, age));");
   query.exec("insert into chaps (name, age) values "
              "('Bob', 20), ('Rob', 30), ('Sue', 25), ('Hob', 40);");
   QSqlTableModel model(nullptr, db);
   model.setTable("chaps");

   RowSelectionEmulatorProxy proxy;
   proxy.setSourceModel(&model);
   proxy.setSelectedBrush(QBrush(Qt::yellow));

   QTableView view;
   view.setModel(&proxy);
   view.setEditTriggers(QAbstractItemView::NoEditTriggers);
   view.setSelectionMode(QAbstractItemView::NoSelection);
   view.setSortingEnabled(true);
   QObject::connect(&view, &QAbstractItemView::clicked, [&proxy](const QModelIndex & index){
      proxy.toggleRowSelection(index);
   });

   QPushButton clearSelection("Clear Selection");
   QObject::connect(&clearSelection, &QPushButton::clicked, [&proxy]{ proxy.clearSelection(); });

   layout.addWidget(&view);
   layout.addWidget(&clearSelection);
   w.show();
   app.exec();
}

#include "main.moc"