[How To] Designing a Program to support multiple DBMS

[MarkMLl]

The purpose of this Thread is to display "my way" (to quote Frank Sinatra), how i do what and why, and invite everyone to chip in.

...and everybody's jumping in enthusiastically.

Or (to quote Nancy Sinatra), "these boots are made for walking..." :-)

MarkMLl

[Martin_fr]

Most people (at least that's what i found out) end up with code like this:

Code: Pascal  [Select][+][-]

1. Const
2.    SELECT_TOP10_EMP_SALARY_SQLITE='SELECT ID, EmpName, EmpSalary FROM employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC LIMIT 10;';
3.    SELECT_TOP10_EMP_SALARY_MSSQL='SELECT TOP(10) ID, EmpName, EmpSalary FROM employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC;';
4.  

You see the difference? It's the SQL-Dialect

What i do:
I store the SQL-Statements themselves in the Database.
Such a table looks like this:

ID Statement Constant ConnType 1 SELECT ID, EmpName, EmpSalary FROM employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC LIMIT 10; 1000 SQLite 4 SELECT TOP(10) ID, EmpName, EmpSalary FROM employees WHERE DepartmentID=:DepID ORDER BY EmpSalary DESC; 1000 MSSQL Now before actually firing of the SQL-Statement i want to display the result to the User, i first retrieve the SQL-Statement from the Database according to DBMS

Code: Pascal  [Select][+][-]

1. Const
2.    SELECT_TOP10_EMP_SALARY=1000;
3. Begin
4.    MyQuery.SQL.Text:=GetSQLStatement(SELECT_TOP10_EMP_SALARY, MyConnType);
5.  

Not sure if that is what sparked the "stored procedure" and "trigger" discussion? But the above is neither. The above is not even a "prepared statement".

In any case, I don't see the advantage...

1) You still need to maintain all the versions of your statements.
2) Why store all forms (mysql and sqlite)? If you connect to a mysql server, you will only need the mysql forms of the statement. All others are dead weight.
3) You have the cost of executing the query to load the statement. That can significantly impact the server load / performance of the app.
4) "SELECT_TOP10_EMP_SALARY=1000;" mapping to numbers is a source of errors...

Sure you can cache the loaded statements. But then you can also just have them in a unit (either one unit for each db-engine, or one unit with IFDEF, or one unit with an "array [enum_db_engine] of").

In order to have your table, you somewhere need code to populate that table. So likely you have a big "insert into stmt_table (...) values (sql,sql,sql,....)" => In other words you must store the text somewhere in a text-based file.
And yes, what if the table structure changes? At first glance it looks like having the sql closer to the table definitions will help keeping them in sync... But, then the params may need updates too, so updating the code may still be required. And testing needs to be done against all db-engines. And fixing bugs in the statements is harder, if they are in a table, because after each fix to the sql, you need to additionally update that table.

IMHO having a unit like

Code: Pascal  [Select][+][-]

1. type
2.   TDbEngine = (eMysql, ePostgres, eSQLite);
3.   TSqlStatement = (sqlTop10, sqlSalary, ...>)
4. const
5.   MySql = array [TSqlStatement] of array [TDbEngine] of string = ( // order can be swapped
6.   );
7.  

If most statements are engine agnostic, you have an "var stmt = array [TSqlStatement]  of string" and just override the 1 or 2 engine specific statements (init function in the sql statement unit / so still all in one unit).


The alternative is to use a framework that builds the sql itself. Then you support all engines that the framework knows.

Of course you can't hand optimize your sql any more. You will have to describe the table structures in some form defined by the frame work. (Something I don't like, because why replace sql with some other form, just to then generate sql from it....)

[Zvoni]

Martin, very nice.
And yes, agreed with the "dead weight", but my initial thoughts on this were:
1) I mainly use ODBC-Connections (Please no discussion about that), so for a different DBMS i would only need the driver, and the connection-string (very basic, of course!).
2) Because i have the dead weight on the DB-Server itself (say on MySQL), and decide: Let's move to MS-SQL, everything is already there.
Pull the "CreateDatabaseMSSQL"-Statement from the MySQL-Table, assign it to the TSQLQuery connected to the MSSQL-Connection, and Fire!
Pull the "CreateTableMSSQL"-Statement from the MySQL-Table, assign it to the TSQLQuery connected to the MSSQL-Connection, and Fire!
and so on and so forth.
If done correctly, you could even do a complete migration without adjusting a single thing
I wouldn't have to change, never mind to recompile my frontend.

That's the initial idea behind that concept.

Of course you're right, that as with everything in life, this is not the be all, end all, and it certainly has its downsides (as mentioned above), but i found out (at least for myself), that this concept is the "best of all worlds" for me.

Comments welcome

[440bx]

First, an important disclaimer, it isn't very important to me to have the same code support multiple database systems because normally, I choose the DBMS, therefore there is only one to support.  It also means that my experience writing code that supports multiple DBMS is rather limited (actually, close to none.)

With that said, if I had to solve the problem, I'd create a set of functions that carry out the atomic logical operations on each database using the best methods offered by each DBMS.   I'd create a table of function pointers for each DBMS and, early in the program I'd get a pointer to the table that corresponds to the DBMS in use.  In each table, entry "n" solves the same problem - possibly in different ways - for each DBMS.

Basically, all access to the database is done through polymorphic calls  to functions tailored for that DBMS. 

[Чебурашка]

I recall a old paper, related to ORMs but by extension applicable very often when code and databases meet each other:

Object-Relational Mapping is the Vietnam of Computer Science
http://www.odbms.org/wp-content/uploads/2013/11/031.01-Neward-The-Vietnam-of-Computer-Science-June-2006.pdf

[Zvoni]

First, an important disclaimer, it isn't very important to me to have the same code support multiple database systems because normally, I choose the DBMS, therefore there is only one to support.  It also means that my experience writing code that supports multiple DBMS is rather limited (actually, close to none.)

With that said, if I had to solve the problem, I'd create a set of functions that carry out the atomic logical operations on each database using the best methods offered by each DBMS.   I'd create a table of function pointers for each DBMS and, early in the program I'd get a pointer to the table that corresponds to the DBMS in use.  In each table, entry "n" solves the same problem - possibly in different ways - for each DBMS.

Basically, all access to the database is done through polymorphic calls  to functions tailored for that DBMS.

hmmm..... so basically, kind of like in a separate library ("Black Box"), decoupled from the Frontend, so the Frontend still remains DBMS-agnostic.
and your approach offers a common "interface" for the Function-calls

[440bx]

hmmm..... so basically, kind of like in a separate library ("Black Box"), decoupled from the Frontend, so the Frontend still remains DBMS-agnostic.
and your approach offers a common "interface" for the Function-calls

That's it, you got it. 

[Zvoni]

hmmm..... so basically, kind of like in a separate library ("Black Box"), decoupled from the Frontend, so the Frontend still remains DBMS-agnostic.
and your approach offers a common "interface" for the Function-calls

That's it, you got it.

Yeah, but looking closely at my approach: it's the same (just "executed" differently).
You have your Black Box in a separate library/unit, where you execute your pointer-magic.
I have my Black Box, that i'm pulling the needed statements from somewhere outside of my programcode.
and i offer also the same "interface" (Constant plus DBMSQualifier).

But i think with this we would drift off into the philosophical branch of programming 
Let's avoid that.

In any way: 440bx. Nice one! A valid approach

[Martin_fr]

If done correctly, you could even do a complete migration without adjusting a single thing
I wouldn't have to change, never mind to recompile my frontend.

But with the proposed unit, you would neither.

Well, I take it you have some config, with the connection details (IP or similar, username, password). Those you give to the ODBC.

In this you could also have the DB engine type. In fact, if you don't recompile, then you must have it, because

Code: Pascal  [Select][+][-]

1. Function GetSQLStatement(Const AStatement:Integer; Const AConnType:String):String;

takes AConnType.

So you could easily map this into

Code: Pascal  [Select][+][-]

1. var MyConnType: TDbEngine;

And use it to look up statements in the table.

Well, ok, you need to recompile to add a new engine....
But then, if your client wants to run on a new engine, how is is DB being populated? Does your app include the code to create the db on first run? Or do users need to manually run some SQL.
Only in the latter case, you could indeed just supply some new sql, to support the new db-engine.
IMHO (matter of opinion) that is rare enough to allow for a new compile... (and make it easier for the user, by allowing the app to initialize the db on first run)

Then again, you run the risk that you run an invalid combinations of versions.
Well ok, if your app expects a diff version of the sql (and table structures) it will likely fail (because a new field from a table might be missing, either in the app, or in the db).
But with the sql in the table, there is an extra layer of potential error. Let's say the name of two sql-params (for one of the statements) where changed (they still exist, but have new meanings) => your app may run. No hard error, just incorrect action on the data.
Yes that can happen with the sql coded into you app too. As I said, just one more layer.


There are lots of considerations that come for different use case scenarios.

If you write a desktop app, that stores a list of local documents and allows to browse them, with comments and other meta data... => You neither have particular needs for speed nor security.

If the app runs as server (website for a business), with millions of clients connecting, speed may be really important. You may group statements that often run together into stored procedures. Different db engines may have different support for that. So the amount and order of interactions with the DB may differ between engines. It's not just replacing the sql text. There may be different design approaches. (E.g. some db can take multiple statements in a single call, other can or can't handle stored proc. and yet others again may do some work in triggers, and not need all the statements....)

You also have security considerations. You may need to limit what your application is allowed to see/change on the db.
If you store statements in the db and your app will execute those statements with certain permissions granted, then that table needs to be protected. Otherwise an attacker may be able to sneak in "destructive" statements.
Yes, you have to design correct grants for all else anyway, so you can to that for the statement table too. It's just one more table to take care off in your security concept.

[Martin_fr]

With that said, if I had to solve the problem, I'd create a set of functions that carry out the atomic logical operations on each database using the best methods offered by each DBMS.   I'd create a table of function pointers for each DBMS and, early in the program I'd get a pointer to the table that corresponds to the DBMS in use.  In each table, entry "n" solves the same problem - possibly in different ways - for each DBMS.

Which additionally helps when the order/count of params changes. You can fix it in a single place (e.g. by adding a default)

@Zvoni: And if provided as so/dll => the main app does not need to be recompiled....

[440bx]

Yeah, but looking closely at my approach: it's the same (just "executed" differently).

In any way: 440bx. Nice one! A valid approach

Thank you and, yes, it is conceptually the same approach just taken a little farther by creating tables of functions for each DBMS.

Basically, complete "blackboxing" of where the data comes from and how it's obtained.  The front end asks for data, doesn't have a clue (other than the interface) about how its obtained and manipulates it as needed.

[Zvoni]

All valid arguments.

I like discussions like this.
As i said: i might even get a nugget for something i haven’t considered.

[MarkMLl]

1) I mainly use ODBC-Connections (Please no discussion about that), so for a different DBMS i would only need the driver, and the connection-string (very basic, of course!).

I for one have nothing against that: anything's better than a graphical ODBC (or whatever) connection that's difficult to archive or document.

However I would caution that, at least the last time I looked at this issue, ODBC can miss out on things like asynchronous notifications: for that you really do have to go through the backend-specific library.

MarkMLl