Forum OpenACS Q&A: Can inline SQL still be used?

Posted by Petru Paler on 03/04/02 11:17 PM

Can inline SQL queries can still be used in db_* calls, or does one always have to use xql files? Using inline SQL would save quite a bit of time when writing stuff not intended to be portable across databases...

2: Response to Can inline SQL still be used? (response to 1)

Posted by C. R. Oldham on 03/04/02 11:25 PM

It still works.

During development, I've found .xql files to be a royal pain since they don't get reloaded automatically when they change.

3: Response to Can inline SQL still be used? (response to 1)

Posted by Don Baccus on 03/05/02 03:28 AM

BTW you can have the APM watch .xql files just like .tcl library files.

We need a "debug mode" that watches all .tcl library and .xql files, though ...

Petru ... if you intend to share your code with others it would be nice to generate .xql files first anyway (you can use the query extractor). Even if you don't intent to port your work across db's, if you share your code, it is likely that others will.

Then again you may be working on something where the code won't be available or is so tailored to a particular site or customer's needs that sharing it makes no sense ...

4: Response to Can inline SQL still be used? (response to 1)

Posted by Jun Yamog on 03/05/02 04:42 AM

Hi Don,

What is the long term stand on this? Would OpenACS still continue to support inline SQL?

Some developers gets confused by it. Some continue to debug on code on the inline SQL while there is already a xql file. They end up puzzled why their code is not running.

5: Response to Can inline SQL still be used? (response to 1)

Posted by Peter Alberer on 03/05/02 06:05 PM

This may be a little bit off-topic but ... arent these xql files just one step into the direction of creating something like a persistence layer between the db and acs application objects (like users, groups etc) ?

Ars Digita did (or tried do) something like that for their acs-java. Are there plans for something like that (in the remote future of course) in Openacs ?

6: Response to Can inline SQL still be used? (response to 1)

Posted by Don Baccus on 03/05/02 07:56 PM

Actually, now that you mention it Neophytos is playing around with ideas for a persistence layer and intends to share them.

Query files aren't really a step in that direction in that there's no abstraction from the RDBMS. An abstraction layer's hard to do so I'm personally a bit skeptical that it can be done efficiently. aD's efforts weren't an unqualified success, apparently. Zope doesn't scale well but I don't know if that's due to the basic design of the persistence objects or due to other design issues.

RDBMSs perform best when you can bundle up complex set operations into a single query, and modelling this in a general way in an abstraction layer is not easy.

So, I'm skeptical but willing to be proven wrong.

Jun - as far as the future goes I'd like to move away from inline SQL altogether in the toolkit but I think we'll always support it in the db API. I've been thinking that we might get rid of the mandatory SQL parameter and replace it with an optional "-sql querystring" parameter. The idea being that without it a queryfile entry would be mandatory, and with it a queryfile entry would spawn an error. This would end the confusion you mention while maintaining the feature for those who aren't interested in RDBMS portability in their code, and for those cases where we build dynamic SQL.

7: Response to Can inline SQL still be used? (response to 1)

Posted by kapil thangavelu on 03/05/02 08:33 PM

wrt to zope, the comment regarding scalability of persistent objects and relational persistence is comparing apples and oranges. the zope persistence layer is known as the ZODB, which is a transparent python object database, that can use different underlying storage formats (file, berekelydb, oracle). the scalability and stability of this layer has been proven, imo. there are several ZODB databases around the 10GB range. zope's relational integration is a more traditional approach that maps sql statements on to methods that are executed against various database adapters and integrated with the transaction system if the underlying database adapter supports it. hence i don't really understand on what basis the comment was made about the lack of scalability of zope's persistence. rereading the don's reponse it appears that the comment was related to general scalability, and on that question i don't understand from where that was inferenced, as there are more public high profile sites running on zope then openacs.

8: Response to Can inline SQL still be used? (response to 1)

Posted by kapil thangavelu on 03/05/02 08:48 PM

to add a qualification to my response. i realize that the openacs is unreleased and that the last line of my response was uncalled for. but i disliked the unsubstantiated claims about zope's scalability that were both vague and general. my apologies to anyone who might have been offended.

9: Response to Can inline SQL still be used? (response to 1)

Posted by Don Baccus on 03/05/02 09:55 PM

hence i don't really understand on what basis the comment was made about the lack of scalability of zope's persistence. rereading the don's reponse it appears that the comment was related to general scalability, and on that question i don't understand from where that was inferenced,

The inference comes from a client's experience, one who has chosen OpenACS rather than Zope for their next project.

As I said, though, I don't know if the slowness they experience is due to issues with the persistence layer or other design issues, i.e. with the forum software and other packages/modules/whatever-the-hell-they're-called-by-Zopefolk that make up the site.

I did specifically say I didn't know if the problem was with the persistence layer - you did read that, right?

This is just one data point, admittedly.

As far as scalability goes in this context, remember that old saying, "size doesn't count". It's hits per second that count. Telling me that there are ZODB databases up to 10 GB in size is as meaningless as my telling you that there are PG databases in the several hundred GB range (as is true). The client's experience is that their site's so *slow* that it's nearly unusable on a P500 with ample RAM and hard disks. So *slow* that they're unlikely to ever reach 10GB worth of data, not in this lifetime anyway.

Again, I don't know why it is so slow, nor do I have any idea as to how much work would be involved in speeding it up. This wasn't why they chose OpenACS for their next project, it was shown me as a curiousity, that's all.

On the other hand I've never seen an OpenACS site as slow as the one in question ...

10: Response to Can inline SQL still be used? (response to 1)

Posted by defunct defunct on 03/06/02 05:34 PM

And if you want to see a *really* good example of why attempting a persistence layer and database extraction is so hard to do well, have a go at an EJB style implementation (i.e. using something like JBoss)..... Nightmare 😉

Although on the topic of inline queries, I wonder if whats really the requirement here (as I don't find the .xql files anymore hardwork) is related to dynamic queries. Naturally one problem with using the .xql files is it doesn't sit well with the notion of dynamic sql...

Any plans to cope with that in future?

11: Response to Can inline SQL still be used? (response to 1)

Posted by Don Baccus on 03/06/02 07:35 PM

Our workaround for dynamic queries has been to rely on db_map to give us the partial queries mapped from the proper query file for the RDBMS in question. But you still must put the Tcl var reference in the query, which is sort of ugly. In the case of PG large object emulation you actually have to put a code snippet in the query, which is even uglier but fortunately is restricted to a handful of places.

I'm reasonably satisfied with how we're handling it now even though it would be nice to find a solution that removes Tcl stuff from queryfiles altogether. Among other things we could then drop the "subst" call we do on every query before executing it.