Using a custom database backend with QTableView
-
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 :-) -
Yes - you must define what is portable and what is not.
Are my queries from previous post portable?
Can I run them through your SQL on SQLite? -
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.
-
I agree with you, Lukas, it's very nasty and I have no good reason for doing that. I'll follow your advices and change my mind. ;-)
Thanks for your replies!