Important: Please read the Qt Code of Conduct -

Model/View with complex SQL Databases

  • Hello,

    for my application i need to store complex data. Since i don't want to create a custom file i decided to use a database for this purpose. I also intend to use Qt's Model/View framework. I think that this is a suitable approach because my database is just a complex model for which i need different views.

    Now, my problem is that i'm still working on my design, and because i don't have much experience with Qt's Model/View framework, i'm not sure how to proceed, and i hope i can get some advise/ideas for my questions here.

    1. My database will consist of many tables with many relations, and i need views for different tables, and every view-table would relate to one 'main'-table, aside from many smaller crossovers. Creating a model for each table i want to view seems to be the only way with the existing framework, but i need to keep everything synchronized. 1 Database + 2 Models + 2 Views is all i could find on this topic, but this approach would not work if i cache the models access to the database since every model would try to cache data for the same table.
      The best way i can think of right now would be to take a look at Qt's code and implement a new model for a complete database. Access to a table would be done through a proxy model. That way, all views would work on proxies accessing the same model, and therefor work on the same cache, keeping them synchronized at all times.
      What do you think about this approach? I'm not sure how difficult this would be and if another way may be better.
    2. I need to store hierarchical data as well, and i don't want to use another file like xml for this since that data still has relations to other tables. So i'm thinking about using something like nested sets or adjacency lists for this. My approach to pass this to a hierarchical view like QTreeView would be to use a proxy, transforming the table to a tree.
    3. I'd like to add some protection for the data. Therefore, i'd like to do something like hashing everything passed to the database. Would it be a good idea to do this using a delegate? That way i would not have to change the actual sql driver, specially if i want to change the driver later.
    4. Additionally to 3: Is there a good way to get the binary data used by Qt? It would be better if i could hash this instead of hashing each datatype of a QVariant individually. The only thing i could find for this was serializing the QVariant using a QDataStream, but i don't want to hash the QVariant, just the data it hold.

    I hope you can help me with these questions. If you need further information feel free to ask.



  • first of all a question: Do you just need to view the data or would you need the model to be editable and its changes be saved in the DB?

    1. Isn't QSqlRelationalTableModel enough? if not then keep the tables in different models, it will be easy to implement the relation just via delegate
    2. Here I would suggest the opposite. It's much easier to transform a tree model into a table than the other way around
    3. All you need is a checksum? if the answer is yes that is normally done on the SQL server side, not by the Qt code (unless you want to protect from corruption between transmitting from your app to the SQL db but that should be the SQL driver responsability, not yours)
    4. QByteArray result; QDataStream stResult(&result,QIODevice::WriteOnly); QMetaType::save(stResult,variant.userType(),variant.constData());

  • Thank you for your answer.

    Yes, i need my model to be editable. I'm (mis)using the DB as save file for my application so i don't need to implement a custom file, since my data is mostly a collection of tables. That way, Qt's model view and the SQL driver do most of the work.

    1. QSqlRelationalTableModel would be enough in general. But, the way i understand this, using delegates, all i can implement are relations between models. But my problem is that all models have relations to the same table. If i change this in one model, using delegates i could only mirror the changes in all models, having everything multiple times. Also, i think this could cause problems if mirroring isn't perfect and i try to save data from mutiple tables to the DB. I would prefer this to be done by the model because it would be more consistent and, in my opinion, is actually one of the models jobs.
    2. Do you mean i should use something like xml for everything? Wether i use a DB or xml, both have it's advantages and disadvantages. I'm prefering a DB because i think that this will be closer to what i need. But i will think about it. Or did you mean something else?
    3. I think we are misunderstanding us here. With protection, i don't mean a checksum or something similar to ensure data integrity but encryption. The things you mention are one reason i want to use a DB. I will probably use SQLite, and here i don't have any encryption, hashing or user/password protection
    4. I will try that, thanks.

    I hope i didnt misunderstand you.

    1. could you draft a small example of what you'd like so we can better focus on the issue?
    2. No, I just mean it's much easier to convert from a tree model to a flat, tabular model using a proxy rather than the other way around. I wasn't suggesting anything about what file format you should use (probably unrelated but you can use this small library to save models in xml, csv, or just a generic binary format
    3. Hash is not encryption. A hash loses the information about the original data, that's the point of hashing in the first place. There are libraries that handle encryption on SQLite for you no need to get crazy. Alternatively you can use a crypto library (crypto++ or libcrypto of OpenSSL) to protect the entire db

    1. So you were just mentioning this? Otherwise i'm not sure what you're trying to tell me with this.

    2. I didn't mean hashing is encryption, should have phrased that differently. When i googled this all i found were posts about making a new driver for this purpose, so i thought i could avoid that using a delegate, but a ready to use driver is definitely preferable, thanks.

    Now for the example, i'll try to explain my requirements. As an example, think about a DB for something like onlineshopping. The database would look like this:

    • A table for customer data
    • A table for addresses
    • A table for bank accounts
    • A table for products
      These would be the shared tables of the DB with relations between them (customer having addresses and bank accounts). Aside from these there would be more tables, let's call them separate tables (no relation between them) :
    • A table for purchases from europe
    • A table for purchases from america (imagine it being different from the europe table)
    • A table for other shippings
    • A table for restocking
      All these table would have relations to the shared tables (e.g. purchases having a customer with address and bank account and the purchased products)

    What i need now are

    • Views for almost all tables
    • Views synchronized at all times
    • Everything editable
    • Edit the shared tables through the views of separate tables (doesn't make much sense in this example, but is absolutely mandatory for my app)
    • All edits must be cached and saved to the DB explicitly (save-button)
    • An undo/redo function (would be good but not mandatory)

  • I'll just say, I'm interested in a complex, inter-related database too, and am also new to Qt.

    From what I can see, the Qt complexity level only goes up QSqlRelationalTableModel (allows foreign keys at least), but that's still only at single-table level.

    From my .NET background, I'm really missing a "dataset" level which handles groups of related tables, as well as all the other features you are requesting (including edits being cached for later execution). I'm not complaining about Qt --- it's excellent --- but I'm reluctant to do what I'd like because the Qt infrastructure is well lacking in this area.

  • Hi,
    just as a side note, QSqlRelationalTableModel does offer caching, see EditStrategy. But internally this is just a list of rows and does not affect other models, so you could, an in my case would, end up with multiple caches for one table if two models relate to the same table or model 2 relates to model 1.

  • @Larvae said in Model/View with complex SQL Databases:

    just as a side note, QSqlRelationalTableModel does offer caching, see EditStrategy.

    Ah yes, sorry, I had forgotten that. But I'm still not clear from the documentation whether it updates related-table-models on update/delete, or whether it's just a mechanism for displaying related-table-lookup-data in a visual view...?

    1. Now that I understand the problem, just ignore this point

    What you are describing is exactly the problem Flux was designed to solve.

    I'm not saying it's a walk in the park to implement but the road is clear. There's a QML implementation here: but it's probably too narrow compared to what you want

  • @JNBarchan
    I'm not sure what you mean. The model loads a set of rows from the database. These rows are the data displayed by the views. When you edit in your views, you edit these rows in the model. The EditStrategy defines at what point the changed rows are written back to the database. Does that answer your question or did i misunderstand you?

  • Never heard of Flux, i will take a look at it, thanks.

  • @Larvae said in Model/View with complex SQL Databases:

    I'm not sure what you mean. The model loads a set of rows from the database. These rows are the data displayed by the views. When you edit in your views, you edit these rows in the model. The EditStrategy defines at what point the changed rows are written back to the database. Does that answer your question or did i misunderstand you?

    My post was a comment on what I am used to with a "dataset", compared to what Qt can offer.

    If you have a foreign key relation, deleting a row in the parent table can cascade delete(s) to row(s) in the child table(s); same for updates. That's what I can see in my "datasets", which give a disconnected environment holding all the tables you are modelling.

    I don't know, but from the Qt documentation of QSqlRelationalTableModel which "supports foreign keys" (and seems to be the only Qt class which might deal with multiple tables), I am asking/wondering whether it actually reflects related updates across related tables/models, or whether its support is just for FK lookup, not for delete/update?

  • I looked at Qt's documentation again and now understand what you mean.

    I tried it using a variation of Qt's Relational Table Example to look at the database itself. If you use the suggested QSqlRelationalDelegate, all you can do is update the original table, i.e. choose between already existing elements. You can't edit the items the foreign key is refering to. If you don't specify a delegate and leave it as default, you can't change those columns at all, only the columns without relation.

    But since you set up the relations, it shouldn't be to difficult to use that to create a custom delegate to change the other tables as well.

  • I haven't decided which design i will use, but I gave Flux some thoughts and came up with a simplified concept. Since this is only half related to this i put those thoughts into a new topic:

    Flux like architecture with Model/View

Log in to reply