[Qt-interest] Database agnostic programming

[Robert Hairgrove]

André Somers wrote:
> Hi,
> 
> I have build some Qt based software that interfaces with a database. 
> Now, we would like to make the program database agnostic. That is: we 
> want to make it possible to choose a different database as the backend 
> to store the data. While Qt gives me a good way to connect with the 
> different databases, there is no solution for the different SQL dialects 
> that are used, especially for slightly more advanced select queries and 
> for table creation and modification queries. Issues range from different 
> names for data types, different date notations, different characters 
> around table names and values to differences in things like TOP version 
> LIMIT keywords.
> 
> How do other people solve this issue? Is there some library available 
> somewhere that can "translate" SQL dialects? One option I am looking 
> into is KexiDB, but the page seems a bit outdated so I am not sure what 
> the current status is. Meanwhile, other suggestions are very welcome!
> 
> André

While Qt provides a nice abstract interface to different databases, what 
it cannot do is provide an abstraction for the SQL language itself.

However, if you don't let users enter ad-hoc queries and have a small 
list of predictable differences (such as TOP vs. LIMIT vs. ROWNUM), you 
could write your own abstract classes which provide this functionality 
depending on which database is currently running as back end. With Qt, 
you can always tell by calling the QSqlDatabase::driverName() member 
function. The list of DBMS's you would be expected to support would 
probably be less than 10 at the moment and wouldn't change very often; 
any new databases could be supported by deriving another class from the 
abstract base class.

For example, the base class would have a pure virtual member function:

    virtual QString setLimit(const QString &sql, unsigned int lim=0, 
unsigned int start=0) = 0;

In the derived classes, you'd override this to modify the SQL string 
with the appropriate syntax. E.g. for MS-Access, the SQL string would be 
modified by inserting "TOP " and whatever "lim" is (if greater than 0). 
For MySQL, it would append a "LIMIT <start,lim>". For Oracle, it would 
add "ROWNUM < lim" to the WHERE clause, etc.

It's always a good idea to stick to ANSI-SQL for this kind of 
application. You might be interested in Joe Celko's books, for example 
"SQL For Smarties" (I have the 2nd edition, ISBN 1-55860-576-2) which 
provide pure ANSI/ISO-SQL solutions for such things including sequences, 
TOP() problems, etc.

HTH