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

1: script that moves sql from tcl to xql files

Posted by Benjamin Brink on 09/16/14 12:55 AM

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

2: Re: script that moves sql from tcl to xql files (response to 1)

Posted by Benjamin Brink on 09/16/14 01:03 AM

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

3: Re: script that moves sql from tcl to xql files (response to 2)

Posted by Benjamin Brink on 09/16/14 11:54 AM

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

4: Re: script that moves sql from tcl to xql files (response to 3)

Posted by Gustaf Neumann on 09/17/14 12:33 AM

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.

5: Re: script that moves sql from tcl to xql files (response to 4)

Posted by Benjamin Brink on 09/17/14 12:59 AM

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.

6: Re: script that moves sql from tcl to xql files (response to 5)

Posted by Gustaf Neumann on 09/17/14 02:33 AM

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

7: Re: script that moves sql from tcl to xql files (response to 6)

Posted by Brian Fenton on 09/17/14 10:22 AM

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

8: Re: script that moves sql from tcl to xql files (response to 7)

Posted by Gustaf Neumann on 09/17/14 10:50 AM

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.

9: Re: script that moves sql from tcl to xql files (response to 7)

Posted by Benjamin Brink on 09/17/14 01:45 PM

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