Model/View with complex SQL Databases
-
- 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)
-
-
-
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. -
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...?
-
-
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)
- 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? -
- 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?@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
QSqlRelationalTableModelwhich "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:
