Friday, 27 August 2010

Another day, another itch.

(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.

Conclusion?

I wish. I don't know what to do, yet, but I suspect it's going to involve writing code.

Thoughts welcome.

4 comments:

  1. 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.
    How could it work if it did not refetch the data ? You could have triggers, delete on cascade, rules or many other SQL features that modify the content on the table when the delete happens.



    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.

    No, you are wrong. On a large database, since there is no incremental fetching from the driver, this exercise is left to the programmer (and that's a dirty work)

    ReplyDelete
  2. I think these are the main reasons why these projects started to use sqlalchemy with pyqt4:
    http://www.pypapi.org
    http://www.python-camelot.com
    http://bitbucket.org/jbmohler/pyqtbindings

    ReplyDelete
  3. @Pinaraf: good point, I'll have to take it into consideration ...but this still can't be the optimal way to do things.

    it's been a long time since I've worked actively with databases, but don't most return the number of rows modified/deleted from a call, inclusive of such features?

    this could be used internally, or in worst case, an option could be added to alter this behaviour where the author knows that triggers are not in effect.

    furthermore: i'm not sure if you're saying it's a good thing that it is left to the developer, but personally - I can't see how it can be.

    it's a pitfall that may not be noticed (depending on whether a different DB engine is used for testing/production, data amounts etc), and requires writing more code for each different use of the class. this is something that Qt to a large extent hides from the developer in the first place, so, why not here? :)

    ReplyDelete
  4. @btami: I wish I had the luxury.. unfortunately, I'm writing C++ here. :-(

    ReplyDelete