Using a custom database backend with QTableView
-
Hello!
I am trying to insert support for multiple db backend (for now only MySQL and SQLite, but it may change in future) in my application.
This support is implemented using a hierarchy of database classes:@
Main database class
|
--> SQL class
|
--> MySQL class
--> SQLite class
@But how to display database data in different QTableViews?
My idea was to create a subclass of QAbstractTableModel for each table, and handle there insertion, deletion and editing of elements. But I should also create a custom delegate (for instance to modify foreign relationships) for each table.
Is there a smarter way to do this?
Also, I would like to take advantage of "lazy population" of the model, using canFetchMore() and fetchMore() functions to retrieve data incrementally from the db.[EDIT: ASCII art formatting, please wrap in @tags like code, Volker]
-
Yes, in the first version in fact I used QSqlTableModel.
What I would like to do is to implement an application which works "independently" from the database backend.
That is, if I want to include in my support a db system different from SQL, the application should continue to work without knowing what's "under the hood".
Which means that I cannot use a simple QSqlTableModel or QSqlRelationalTableModel (unfortunately), I have to find another way to present data to my view.
I hope now it's clear what I have in mind, if it's a bad idea or it's impossible to do, feel free to say it :) -
I'm still not quite sure what you want to achieve.
QSqlTableModel resp. QSqlRelationalTableModel is independent of the database backend. You can either use one of the already "existing":http://doc.qt.nokia.com/4.7/sql-driver.html#supported-databases backends for QSqlDatabase or just "create a new":http://doc.qt.nokia.com/4.7/sql-driver.html#development database driver.
If you need to support a non-relational / non-SQL database which can't be accessed using a QSqlDatabase driver just create a QAbstractItemModel and pass it to your view as you would pass a QSqlTableModel. The Qt Model / View concept already does the decoupling you are looking for.
But I strongly advise against creating your own database model for accessing relational databases. The Qt SQL module is a fully tested and known-to-work solution.
-
Lukas, in fact I understand for a bit where Tulpa comes from. QtSQL can not be considdered a full solution. QtSQL does offer some database independence, but it is not complete, -not- nor quite obvious how to achieve it.
The key is to use QSqlDriver::sqlStatement(), even if that class is documented to be used only by driver implementers. The sqlStatement() can be used to let the driver construct SQL statements that fit the database. However, support is only there for quite basic DML statements ("SELECT", "INSERT" and the likes, not "CREATE TABLE" and friends). It does not support things like limiting the result set, for instance ("TOP" or "LIMIT").
However, for a lot of scenarios, it is sufficient. What limitations did you run into?
-
It looks like I got the initial question wrong. So we are talking about database abstraction "upwards" the SQL layer, not "downwards" (towards DBMS) here. Well, I then have to add that after serveral attempts I'm still looking for a best practice on how to design such an abstraction layer.
Although this sounds straigtforward at the beginning, things become nasty fast. Large datasets require a "streaming" abstraction layer to minimize memory and CPU consumption; multithreaded environments add asynchronous and thread-safe requirements. Supporting non-relational storage adds, depending on the data structure, another chunk of complexity.
I usually end up in a very lightweight abstraction layer tightly coupled to the Qt SQL module, strict SQL statements and a bit of abstraction where needed (DBMS-specific SQL), because the added complexity and the development and testing overhead is not worth the gained flexibility.
But as said - I am open for any best practices that convince me otherwise ;-)
-
Wow, it's a huge amount of work in the last case, and I agree with Lukas when he says "is not worth the gained flexibility".
Well, there aren't limitations or particular reasons to implement this abstraction layer, it's just a challenge with myself for the purpose of learning something new, if possible.
As I can see, it's a lot of work and it's very likely to be error prone. I think that is better to spend time to improve my application's functionalities.
Thanks for your answer, you've been very clear, I will go for MySQL and SQLite using QSql* classes, without trying to implement this abstraction, that should be easier to do :-) -
What I ended up doing, is creating my own QtSQL drivers. I created a class SqlDriverExt that subclassed QSqlDriver, and implemented concrete subclasses based on the existing Qt SQL drivers to support more functionality (mainly DDL type statements). It was doable, but not trivial.
You can also look at the Predicate library.
-
So there is no need to create such an abstraction, in case I have only to create my own Qt SQL driver, and it should work with standard QSqlTableModel and relational model? If I need more functionalities in future, then I will write my own driver without creating a new database class, right?
Thanks a lot for your help! -
Tupla, I like your idea (and ISO committee, too), but It looks like you should define your own SQL and show its correspondence to the different SQL flavors.
Will you support schema? 4-part query? Select from stored procedure? Select from files (CSV / TAB delimited)? Filtered indexes? Query optimization tips? DDL?
For example, how the following low level query will look on your very top abstract level (your SQL)?
@
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;@
and this:
@
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM
( MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty)
FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate BETWEEN '20030701' AND '20030731'
GROUP BY ProductID) AS src (ProductID, OrderQty)
ON pi.ProductID = src.ProductID
WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
THEN DELETE
OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
@(It's from MS SQL - don't tell that you will not implement MS SQL - a lot of people need to migrate from MS SQL to MySQL or ORACLE or ...).
-
I don't want to create a new SQL flavour, my idea was to create something portable to different database systems. For example, if someone wants to add support for a database he/she have to subclass my database class and implement the abstract methods to store and retrieve data from the database he/she's using, that is the abstraction layer that I wanted to add.
If it's difficult to do (as it seems), no problem, it was simply a try, and I already have all functionalities that I need in QtSQL module.
Maybe I asked a naive question, if so I apologize and withdraw my ideas :-) -
No, it's different.
For example, I have an abstract method
@void MyDatabase::loadVehicles(QList<Vehicle> &vehicles)
{
// do some stuff here, insert vehicles from database into the list
}@
If you want to add support for some database system, you have to implement this function. I don't care about the implementation, or the underlying structure of the database, or how you execute query and retrieve results, the only thing that I know is "this function will give me the list of vehicles in the database".
In a nutshell, my goal was to make the application work using only known data types (like Vehicle), without binding it to a specific DBMS. I'm not trying to port databases from MS SQL, or ORACLE to MySQL etc. I'm only providing an interface to make the application work with more than one database, a sort of plug-in system. -
This is how every database abstraction layer starts :-)
While this example looks quite simple things get "nasty fast" if you just flip it over.
@
QList<Vehicle> MyDatabase::getVehicles() const
{
// do some stuff here, return vehicles stored in the database
}
@
If the database contains 10 vehicles this will work. If the database contains 10.000 vehicles you are done because retrieving 10.000 elements from the database, creating 10.000 objects for them and inserting those 10.000 into a QList just to show 20 of them is probably not what you are looking for.So the bare minimum for a database abstraction layer is some kind of streaming functionality, which allows for retrieving just a small amount of data out of a large dataset continuously. Qt already provides such functionality at two different layers:
- QSqlQuery at relational database layer
- QAbstractItemModel at object layer
Cross off QSqlQuery as it operates on a layer below our desired abstraction.
So the most simple abstraction layer (which uses already available functionality) that comes to my mind quickly is a QAbstractItemModel factory, which returns a model referring the desired dataset.
@
class ModelFactory
{
public:
enum Dataset
{
Vehicles
...
};
virtual QAbstractItemModel* createModel(ModelFactory::Dataset dataset,
QObject* parent = 0) = 0;
}class SqlModelFactory : public ModelFactory
{
QAbstractItemModel* createModel(ModelFactory::Dataset dataset,
QObject* parent = 0)
{
if(dataset == ModelFactory::Vehicles)
{
QSqlQueryModel* model = new QSqlQueryModel(parent);model->setQuery("SELECT * FROM vehicles"); model->setHeaderData(...); return model; } else if(dataset == ...) { ... } }}
class NoSqlModelFactory : public ModelFactory
{
...
}ModelFactory* modelFactory = new SqlModelFactory;
QAbstractItemModel* vehicles = modelFactory.createModel(ModelFactory::Vehicles);
@Brain to terminal. Not tested. Requires polish.
This way you are completely independent of the underlying data source - it does not even need to be a database at all. The drawback is that you are restricted to using a QAbstractItemModel for every single data access which might be inconvenient.
But we have already talked about increased complexity as a tradeoff to flexibility - haven't we? ;-)
Whatever you do - creating your own domain-specific language as suggested in this thread will be most probably the beginning of the end. Just don't except you have very, very good reasons for doing that.