I'm just catching up on ACS v4.0 and the porting effort, but here's a
thought about organizing "replacement" (ironic quotes added) queries:
As the ACS4 DB API suggests (near the bottom) three possible strategies for storing queries not in the appropriate SQL dialect:
- The "db_implement_statement" approach - Explicitly call this API call with the path of logical SQL statement in the entire ACS on server startup. Cache the results (if a dialect mismatch) in: 1. an NSV array 2. a pinned DB table or 3. a special file (DBM?)
- The "file-based" approach - Use parallel files (ex: "foo.tcl" and "foo.pg7" files) writing in the corresponding SQL dialect
- The "magic file" approach - One massive file with translations for all the logical SQL statements in the ACS
I would like to see a solution that:
- Makes the transitions from any DB to any other DB as seamless as possible.
- Doesn't make the common case slow for this flexibility. How often does one switch DBs?
- Allows for the possibilitly of _multiple/mixed db_ ACS releases
- Avoids having multiple source files for every page to sync.
How about using the ACS 4.0
Object Model to do this?
Define a "SQL" object type with subtypes for each SQL dialect. The SQL for each dialect will be stored in separate tables, identified by the path-like scheme already suggested. Where dialect translation is not needed the "SQL" type will not be subtyped, resulting in less redundancy (and the syncing problems). Yes, storing the translation table in the DB will add to the DB overhead. Can this table be explicitly cached? Or pinned like the suggested solution 1.3 above?
This is my "first-pass" at the idea. I'm not sure if this is a viable solution. Comments?
