Forum OpenACS Development: Re: script that moves sql from tcl to xql files
sql in db_dml goes into *-postgresql.xql and any queries including a date/time or boolean type go in there as well.
The rest go into *.xql until there's a complaint.
I'll use the examples in acs-object-management/tcl/*.xql for formatting guidance.
Further potential incompatibilities are:
- queries to stored procedures in packages (oracle uses "." as separator, pg has "__")
- tree queries (pg uses on most cases tree sortkeys, nowadays sometimes recursive queries, oracle has "connect by"
- order/limit/offset (see "xo::db::oracle instproc select")
- differences to query nextval in sequences
- differences in sql functions (e.g date functions)
- different date format
- dml differences for different datatypes and various length limitations of e.g. keys or constraint names.
While pulling incompatible sql into *postgresql.xql or *oracle.xql is great, i am not so convinced about the value of the generic .xql files. Extracting generic SQL breaks locality and makes code maintenance more complex: by looking at the Tcl code, you have no idea, what bind variables are needed by xql, when someone deletes/renames Tcl functions or variables, the xql files have to be updated as well, what people seem to forget sometimes. Often .xql files contain the same SQL repeated in many xql files, which is more obvious, when the SQL is visible in the code.
anyhow, this is not a cooking recipe, but my personal experience.
Are there performance benefits in moving sql from *.tcl to *postgresql.xql?
I have a habit of writing sql in the .tcl files during development phase, as you state, to make the code more legible.
If there is a performance benefit with sql in *postgresql.xql, I'll write a little script to move the sql once the code has matured.
I do recall one situation where my code only worked when I moved the SQL to the XQL file (normally like you I find it much easier to leave the SQL in the TCL file during development) - I can't find it right now but I think this was to do with using pagination.
Another advantage to using XQL files is that you can reuse a query (using db_qd_replace_sql https://openacs.org/api-doc/proc-view?proc=%3a%3adb_qd_replace_sql%20&source_p=1 but it's not always as straightforward as that!). Although in practice, if a query merits being reused, it probably also merits being put in a library TCL proc.
A nasty problem might be the following: when the query specified both in the .tcl and .xql file, then the query in xql has higher precedence. Therefore it could be completely misleading to read and maybe modify the query in the tcl code (changes have no effect), but modifying the query in xql "works".
The actual reuse of sql queries in different procs is very little, at least in the most common 80 packages. i observed the rather opposite that the same sql query is repeated in multiple .xql files: i remember this since i had to fix the same problems multiple times.
There were some esoteric seeming cases while working with the ecommerce package that seemed to break as you suggest, but the problems ended up being some thing else. For example some were due to having more than one query using the same reference, or a cache'd query using an early coded caching technique before OpenACS had a fully standardized one. A particularly difficult case involved a poorly nested curly brace in a long tcl file that defined scheduled procs; Another involved an xql definition in a www dir; In both cases, I didn't realize that code there are only re-interpreted on server restart.
I agree, avoid duplication by making a proc out of it. Also avoiding functions and procs in sql help keep the system with a consistent interpretation of data and timing.