Friday, May 2, 2008

Dynamic Extendable SQL

I was coding some software that involves a need for a database as a backend and I was wondering how troublesome it is to always need to edit the SQL codes if the database developers decides to change the SQL commands in an upgraded version for some reason or maybe, what if I need to change another database and their SQL command have some differences ?

I thought that one of the most important thing a software that requires or relies heavily on a database is to be able to be flexible to fit into and accommodate different databases. For example, you developed a software that uses MySQL database and for some reason, a customer wants it to work on their pgsql (PostgresSQL) or maybe on a Java DB/Derby or a H2 database, you have to re-code your database access classes to handle the changes and maybe H2 or Derby or MySQL's command for backing up using custom SQL commands are different. For MySQL , you use a database dump , for H2, it comes with a command called 'Backup to xxx' to do a backup using custom SQL, I am not sure what Derby uses for Backup since I have not used Derby.

I would like to propose the use of some sort of dynamic method where you store your SQL commands for the functions in some XML or text files and when the function is needed to be changed, all you need to do is re write the SQL command in the file and you are off going again. It should be able to handle SQL injections by the means of using Prepare statements or some specially made statements that can handle SQL injections.

With such dynamic capabilities, you can even extend and be able to add in new commands for SQL too if needed.

So now if your customer needs your software to work on some other database, you can just change the SQL codes to those that are needed and the database connection specs , password and username to the database, could be kept on some sort of secured file somewhere so that your database connections and specs and SQL can be mobile and dynamic... always ready to change and take on new challenges that it might face in future.

Such dynamic power and extendability should give your software an edge over the others.

No comments: