Currently the Charta Platform support a great SQL abstraction layer that enables SQL independence. However, our database script system still uses hard coded SQL queries. Maybe it is time to use the SQL abstraction layer for database scripts as well.
As the Charta Platform code uses the SQL abstraction layer for most important SQL queries we have almost reached total SQL database independence. This means that the Charta Platform will be able to exchange databases between, for instance, MySQL and PostgreSQL without changing a line of code. However, there are still some areas in the platform where MySQL specific code is used. The most prominent MySQL specific area is formed by the database scripts. Therefore I would like to promote the use of database independent SQL queries for the database scripts as well.
Using the SQL abstraction layer in the database scripts has three important advantages:
- Database scripts will be able to execute on different database systems
- It will be easier to write a database script
- Database scripts will be more readable
If you do not agree to this proposal, please respond and clarify your objections. If, however, everyone agrees, then we should really start and focus on creating database system independent queries for new database scripts. No new database script should be added without using the SQL abstraction layer, even if this means that the SQL abstraction layer should be extended. Older scripts can be converted in the longer term, or we could choose to remove them altogether.
Berichten
As there are no objections to using the SQL abstraction layer for database scripts we should start with that from now on. Also, other new code that would traditionally use literal SQL should not be introduced anymore in Pascal code. Naturally, trying to avoid literal SQL will bring up questions on how certain queries should be expressed using the SQL abstraction layer and might even need an extension to the abstraction layer. However, I suspect that all questions and extensions can be solved with very limited effort.
I agree that it is best to use the abstraction layer. However, we should be aware that we are introducing an extra risk of possible errors in a crucial part of many our applications.
The big advantage of using "literal text" is that it doesn't change while the code base evolves. So once tested, it is safe. By using the abstraction layer, this advantage disappears, because changes are made to this layer on a pretty regularly basis. A few things to think about:
- Do we have good unit tests for the abstraction layer?
- Are there changes to be expected in the layer in the near future?
- One of the advantages of the abstraction layer is portability of the code to other database systems. Maybe it deserves some thought on how this should be tested and if and how it should be possible to add database system specific queries.
Your comments are valid and indeed we need to be careful. However, do you feel these issues prevent us from going ahead and start using the abstraction layer for database scripts and other database code? I really feel we should get started now and keep the issues you raised in mind.
My answers to your questions:
- Currently there are not many unit tests, but they are being added over time. For the first database scripts that will use the abstraction layer we probably would have to introduce new SQL features. That time would be ideal to add the necessary unit tests.
- Changes to the SQL abstraction layer are usually additions and existing code is pretty stable. Sometimes a more basic feature is being changed. However, possible problems resulting from that are often trapped easily as it is used frequently.
- Testing the portability between database systems is indeed something to think about but should not be too hard. My feeling is that database specific code is something we should really avoid. Either we extend the SQL layer to convert queries to support otherwise unsupported queries (like a FULL OUTER JOIN in MySQL) or we declare applications to be incompatible with a specific database system and raise appropriate exceptions whenever an unsupported feature is used.
In my opinion we can proceed. I would like to ask everybody to add unit tests that check the statements used in the database script.
How would you refer to tables and columns, for these are subject to change over database scripts?
There is nothing against using literal table and column identifiers in database scripts. The SQL abstraction layer is perfectly capable of using those and for database scripts this seems to be the natural way. Thus, a database script typically will not use a
TSQLTable instance, or it will create one on the fly for the duration of the script. However, operations like INSERT, ALTER TABLE and DELETE should be expressed using the SQL abstraction layer, as the SQL syntax of these operations tends to vary among database systems.