I've spent quite a bit of time figuring out how to port dynamic
queries and I haven't seen a discussion about it, so I thought I'd
post my approach to make sure I'm doing it right.
Specifically, the download module uses ad_dimensional to display
multiple views of the same data. For example, it sets up a set of
links ('sliders') to allow you to view software by it's upload date.
(see the ACS-Repository for
an example)
In order to do this, it builds SQL queries dynamically, which creates
problems when porting because you don't know what the full query is
going to look like at runtime.
The answer is to put partial queries in the .xql file and then use the
db_map command in the .tcl file to extract the proper sql fragment
depending on the rdbms that you're using.
Here's an example from the download module:
Old index.tcl:
set dimensional [list
{updated "Updated" all {
{1d "last 24hrs" {where "dar.publish_date + 1 > SYSDATE"}}
{1w "last week" {where "dar.publish_date + 7 > SYSDATE"}}
{1m "last month" {where "dar.publish_date + 30 > SYSDATE"}}
{all "all" {}}
}} ]
set sql_query "
select ...
from ...
where ... and
[ad_dimensional_sql $dimensional where]"
New index.tcl
set dimensional [list
{updated "Updated" all {
{1d "last 24hrs" {where "dar.publish_date + 1> [db_map date_clause]"}}
{1w "last week" {where "dar.publish_date + 7> [db_map date_clause]"}}
{1m "last month" {where "dar.publish_date + 30> [db_map
date_clause]"}}
{all "all" {}}
}} ]
index.xql
< fullquery name="download_index_query">
< querytext>
select ...
from ...
where ... and
[ad_dimensional_sql $dimensional where]
< /querytext>
< /fullquery>
index-postgresql.xql
< partialquery name="date_clause">
< querytext>
current_timestamp
< /querytext>
< /partialquery>
index-oracle.xql
< partialquery name="date_clause">
< querytext>
sysdate
< /querytext>
< /partialquery>
So, I replaced the oracle specific code in the .tcl file with calls to
db_map. I then inserted partialquery's into the .xql file with the
code appropriate to the rdbms being used (sysdate for oracle,
current_timestamp for postgres).
I learned this from studying Dan's acs-workflow efforts as an example,
so I hope that I got it right.
