(First, an apology in advance, this is a bit of a braindump, and as such, isn't very well structured.)
I've recently been diving into SQL support in Qt, provided by the QtSql module. Specifically, I've been looking into providing IPC on top of QSqlTableModel.
(For those of you who aren't too technically oriented, I'm trying to have multiple processes have the same basedata store, and know when another process modifies the data in some way, to keep all processes in sync).
While I won't go into details on that now, as it isn't complete, it has been an interesting journey, and I think I've found yet another few things I want to add to my todo list. You see, QSqlTableModel is a great class, but it has a number of shortcomings.
It can't be used in a tree
This one is pretty obvious. It is, after all, a table - not a tree. However, making it represent a tree wouldn't be all that difficult I think (a customised implementation of QAbstractItemModel::parent() method looking at e.g. the 'parent' column of a table and returning a QModelIndex to the appropriate row or similar).
I don't believe that this can be solved in the current implementation, though, as QSqlTableModel inherits QSqlQueryModel which inherits QAbstractTableModel - meaning there's a non-trivial amount of tableness about the current implementation.
For some data (think a task manager: you might have subtasks under a task), this is an annoying limitation.
Incremental fetching isn't always supported
I'm actually not positive on this point, but it looks like incremental fetching is left up to the database driver, meaning that on some database types, you won't have incremental fetching.
On a large database containing thousands of rows (or gigabytes of data) this could be a very bad thing if I am correct.
Partial updates aren't really supported
If you delete a row from a database table, what would you expect the client to do after deleting that row?
I know I certainly wouldn't expect it to re-run the query to refetch the data, yet that's exactly what QSqlTableModel seems to do. Not nice.
This also happens on any other conditions of alteration to the data, which strikes me as being rather inefficient.
Furthermore, it isn't really possible to update values on the fly; this proved to be quite an annoyance when implementing IPC notifications of updates. QSqlQueryModel internally contains a QSqlQuery. QSqlQuery has a ::value() method, to retrieve a QVariant containing the data in a given row of a result set. It doesn't, however, provide any method to change that data (even in memory), meaning that once again, a full reload is the only real solution to something else changing the data out from under you.
This isn't really so unexpected, as it's a bit of an odd use-case, but it has made my implementation trickier than I'd wish.
I wish. I don't know what to do, yet, but I suspect it's going to involve writing code.