I am developing middleware services and would like to externalize all dynamic SQL from the service code. This is in the context of a migration from 2-Tier architecture to 3-Tiers. One of the issues I am facing is that the original code is "polluted" with loads of dynamic SQL strings, which most of the time correspond to where clauses. Sometimes the same queries are repeated over and over again with only slight variations in the where clause. I want to remove the SQL strings from the code altogether, and instead use a templating engine such as SynMustache to generate the dynamic SQL. From my understanding, SynMustache templates can be compiled ahead of time, so the performance hit should be minimal. Is this the right way to go? Any advicee on this?
I am developing middleware services and would like to externalize all dynamic SQL from the service code. This is in the context of a migration from 2-Tier architecture to 3-Tiers. One of the issues I am facing is that the original code is "polluted" with loads of dynamic SQL strings, which most of the time correspond to where clauses. Sometimes the same queries are repeated over and over again with only slight variations in the where clause. I want to remove the SQL strings from the code altogether, and instead use a templating engine such as SynMustache to generate the dynamic SQL. From my understanding, SynMustache templates can be compiled ahead of time, so the performance hit should be minimal. Is this the right way to go? Any advicee on this?
Edit: To refocus the discussion from "it's not necessary" to "how to accomplish this in Delphi". I am a developer with a strong Java background. In the Java world you have a framework called MyBatis, which precisely works in this way (see http://www.mybatis.org/mybatis-3/sqlmap-xml.html and http://www.mybatis.org/velocity-scripting/). I have had great success using myBatis in production sites with hundreds of users. This is exactly what I want to reproduce with SynMustache. Obviously starting with smaller ambitions than what MyBatis provides out of the box.
Edit: To refocus the discussion from "it's not necessary" to "how to accomplish this in Delphi". I am a developer with a strong Java background. In the Java world you have a framework called MyBatis, which precisely works in this way (see http://www.mybatis.org/mybatis-3/sqlmap-xml.html and http://www.mybatis.org/velocity-scripting/). I have had great success using myBatis in production sites with hundreds of users. This is exactly what I want to reproduce with SynMustache. Obviously starting with smaller ambitions than what MyBatis provides out of the box.
I have created, deployed and used a variety of solutions to this "problem". First off i have a DAC that is NOT "any". IBObjects targets FB/IB and with it comes an extremely versatile SQL parser. The "client side parser" knows a LOT about what the server expects.
ReplyDeleteIn IBO i have functions for extracting and injecting any part of a complete SQL Query. I can easily copy a "where" clause or a "select" clause. I can insert where-high/low/middle (as and:ed or or:ed) and actually let IBO prepare something huge and copy the where-clause.
With CTE's and (from FB 3.0.3 - an unused CTE only gives a warning) this becomes flexible and reliable and at the same time performance-wise scarily effective.
So one example is i put together a SQL from lots of factors (ie only latest, non-hidden, whatever). Use that and let IBO process it for FTS (Full Text Search). I return the first X rows of that SQL. IF the user wants a count of hits (a retained query) i write 'select count(*)' and copy the rest from the rows SQL.
I do recognize the problem that you would want to write a SQL Query and "tweak" (mostly the where-clause). SQL is not OOP but it can be set up to be "almost" OOP. The tricky thing will be where to start in each "similar case". I.e. what will the "base" SQL be, where to start from.
IBO also have good macro-processing but i have not found that as useful as dynamic processing since SQL macros can not change between say using a parameter and a literal string. I use macro's for things my RDBMs can not handle easily like ordring for different locales.
The most important part of SQL re performance is to have the queries made up so that the optimizer can do it's job. Macros and templates and "LINQ" fail miserably in this regard IMHO.
I used to store all my SQL in a "local" database. This was brilliant because i could search through it all. I'm moving over to SQL in DFM's but that is purely because the previous solution was 2-tier (not 3). In the middle tier i have some DFM-parsing tool that enable me to get an "overview" of all my "skeleton" queries.
Conclusion; a well aware SQL parser / parsing and some forethought will kill any methods or patterns designed for "teams" or for people/tems that do not want to learn SQL properly. Like those NoSQL thingies out there, a lot of them seems to be RDBM:es with and obscured API.
Generally in n-tier its considered bad form to have any SQL on the client regardless of the "form" its stored in. The reason is that it opens up a world of hurt when attempting to secure the backend database from malicious clients. So unless you are completely certain your clients will never be distributed in the wild or is internet connected, then I would strongly suggest moving all SQL like stuff to the server and only send parameters for the queries as part of function calls to the server from the client. Please notice that the server generally never dynamically should build a query string based on snippets from the client.
ReplyDeletebest regards
Kim/C4D
Kim Madsen No the SQL stays on the server. It is just pulled out of the service code.
ReplyDelete