Forum OpenACS Development: script that moves sql from tcl to xql files

Hello,

Is there a script available that moves sql from tcl files to xql files?

If not, where might I find guidance for best form of xql files and when to use the -postgresql.xql vs .xql ?

The FAQs point to a thread from 2001 which suggests sql92 goes in .xql files:

https://openacs.org/forums/message-view?message_id=21452

Collapse
Posted by Benjamin Brink on
The following link outlines specific limitations in specifying "sql-92" as guidance for an oss projects like OpenACS.

https://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F

Collapse
Posted by Benjamin Brink on
Unless someone has another suggestion, I'll use this as a guide in writing an automatic sql mover script (in a few weeks):

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.

cheers

Collapse
Posted by Gustaf Neumann on
i am not aware of a definitive guideline - and actually, it is quite some work to list all potential incompatibilities between PostgreSQL and oracle (both in various versions). A potential source indicating differences is xotcl-core/tcl/05-db-procs.tcl, when you watch out for "oracle". The support there is not fully complete, but handles frequent cases of the feature-set used in OpenACS.

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.

Collapse
Posted by Benjamin Brink on
Thank you, Gustaf. Very interesting.

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.

Collapse
Posted by Gustaf Neumann on
The performance impact of using .xql files is very little. The decision of taking the generic .xql vs. db-specific .xql file happens at load time (during startup). Then the queries are stored in the nsv OACS_FULLQUERIES (see [1]). At runtime the performance difference is most likely hard to measure, since for every query (e.g. db_string), an nsv_exists is performed [2], for queries with .xql the query is fetched from there via nsv_get (when i look at [2], i can't see the reason for the actual 2 nsv_get operations. Under high load the mutex competition for the nsv might kick in, but on most sites, not an issue.

-g

[1] https://openacs.org/api-doc/proc-view?proc=%3a%3adb_qd_internal_store_cache&source_p=1
[1] https://openacs.org/api-doc/proc-view?proc=%3a%3adb_qd_internal_get_cache&source_p=1

Collapse
Posted by Brian Fenton on
Hi Benjamin

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.

best
Brian

Collapse
Posted by Gustaf Neumann on
concerning "code did not work when query was in tcl but worked in xql": if it is so, this would be a clear bug. if you are able to reproduce it, please enter an entry to the bug-tracker.

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.

Collapse
Posted by Benjamin Brink on
Hi Brian,

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.

cheers,
Ben