Unsolved Model/View with complex SQL Databases
-
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?
- Isn't QSqlRelationalTableModel enough? if not then keep the tables in different models, it will be easy to implement the relation just via delegate
- Here I would suggest the opposite. It's much easier to transform a tree model into a table than the other way around
- 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)
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.
- 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.
- 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?
- 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
- I will try that, thanks.
I hope i didnt misunderstand you.
-
- could you draft a small example of what you'd like so we can better focus on the issue?
- 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
- 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
-
-
So you were just mentioning this? Otherwise i'm not sure what you're trying to tell me with this.
-
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...?
-
- 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: https://github.com/benlau/quickflux 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:
@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?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: