Forum OpenACS Development: Query Dispatcher initial specs posted

Please read it and comment by responding to this bboard posting. The
specifications are by no means fully complete. I will continue to spec
the details as I begin implementation next week. However, for a few
days, everything is on the table. Please don't hesitate to comment on
any piece of this specification! I *expect* that I've been an idiot in
at least 2 or 3 ways, and now's your chance to
call me on it.

I'd like to see optional caching of query results as part of this
infrastructure.  I shouldn't have to explicitly use util_memoize to
cache results.

I like the content of the proposed spec, though it is a pain to read
in its current form (everyting inside a table width=750).

Some modules might choose to put all their queries in one monolithic SQL file, while others might choose to break them up based on the type of query. The ecommerce module in 3.x does this with TCL libraries and I think it makes it much easier to see all the logic associated with one area of the application at once.  Perhaps this is not always the case, but you need to give people the flexibility so they can decide what's best for a particular situation.

You could still use the naming scheme to determine which exact SQL file to read from.

Also, I think the package_name.section_name.chunk_name.query_name is a bit verbose.  How about just package_name.query_name?  Shouldn't I be able to reuse a query in different places throughout a module?  Your naming scheme seems to prohibit this.

i'm glad to see the spec.

responding first to previous comments

roberto brings up the following
1. memory
2. extra learning curve effort

memory. simple example take the entire openacs distro,
divide by 100 (lets assume arbitrarily that queries take 1% of the
source) multiply by 20 (extra memory overhead for tcl structures,
again arbitrary). what you get is peanuts compared to modern
machines memory. hence the query dispatcher memory cost is rather
little.

actually some preliminary tests indicate that the size here is
pretty small something on the order 400K.

extra learning curve effort... hmmm... well db portability doesn't
come for free. imo, a db portablity/abstraction mechanism would be
an awesome selling point for developers and clients.

mike brings up
1. caching queries

do one thing and do it well. what about clusters... do you want to
replace util_memoize functionality in the dispatcher. i guess you
could make it a interact with util memomize. but one of the goals in
the porting guidlines is to minimize code impact. wrapping the db
interface for portablity is being done with a goal of minimizing
code impact. i guess i just don't see this as being a big win.
adding caching to the templating layer is another matter.

ryan brings up
1. not forcing a convention.
2. flexible naming scheme.
3. include files

the concept of an include file is interesting. i like it. it does
however make the work of an automation tools harder.

at the same time i think forcing a convention for this purpose is
good. i think regular structure to the system is a good thing (TM).

in the pre dispatcher days :),
if you're going to be reusing a query, wrap it in a proc, the whole
point of the query name is not for resuse but to uniquely tag
database calls. also the acs4 allows you to set multirows in a tcl
proc that will be evaluated directly in the template namespace.

even post dispatcher spec, reusing query names will affect its
implementation and the amount of information that it can reliably
gather from the run time environment to uniquely identify the query.

i think that verbosity is good in this case. ben's also trying to
future proof the spec for future refinements and developments. take
for example allowing the query dispatcher to reload queries when a
file changes.

if people haven't read it, i highly recommend reading the acs4
database access api,
http://developer.arsdigita.com/doc/db-api-detailed.html

esp. the stuff at the bottom re implementation design

pascal brings up
1. an interesting idea of registering queries as functional calls
2. multiple files is a pain (seconded by roberto)

there is some merit to this way methodology, but not as brought up i
think. i'm curious what exactly you had in mind, because i don't get
it. if you mean run time initialization of the dispatcher query
structures, i think this is much worse than startup initilization of
the queries.

ben adida writes the spec.

thoughts on the query dispatcher

when i first heard about the query dispatcher i was skeptical for a
number of reasons, most relating to the fact that other open source
db's seemed like poor candidates, and the limited viability of acs4
when aD would be moving on to acs5 in a few months. but i've changed
my mind, db portablity is a good thing, esp now the sapdb is out. (i
think that in a nutshell, this just became the most advanced open
source database by a signifigant margin). also the acs tcl version
seems to have exploded in modules the cvs @ aD shows 50+ modules.
i've also been working with acs-java, its nice, the programming
paradigm reflects its nature as a port of acs-tcl, but its still
slow to develop in compared to the acs-tcl. i'm pretty convinced
that once the openacs4 is out it will enjoy a vibrant life on its
own no matter what aD does.

cost concerns - run time efficiecny.
1. function execution - i'm more than a little concerned by the
systems architecture. its looks like you're adding at minimum a two
function call overhead to every db call. this kind of overhead is
going to add up quickly. not to mention that postgres isn't as fast
oracle (no i don't want to use -f).

2. thread contention for shared memory - granted i'm out of my
element, so someone please correct me if i'm wrong. the way nsv
shares are implemented uses a bucket lock structure, but we're still
dealing with concurrent access to a shared resource. i haven't
looked at the nsv c code to see if its actually doing a proper
multiple reader/ single writer scheme or what, in which case this
will be somewhat mitigated.

thoughts on the spec
1. mainly a point of curiosity, is there any aD commitment to
migrating these changes to the apm in the interest of supporting the
openacs and multi db packages?

2. i'm a bit lost about the identification and tagging of sql92
queries, how is sql92 to be identified, and where would it be
stored, how retrieved?

3. the xml spec itself is pretty primitive... i'm not sure about
tcl's xml capabilities ( i hope its got a frontend to expat and not
just using the tcl dom, although regexps might do alright), but
using attributes on some of these things makes a hell of lot sense
to me. one glaring example is the <rdbms> tag, instead of the
current nested tags for version, type why not <rdbms
version="8.1.6">oracle</rdbms>. i think a dtd for the finished spec
would be nice for validating xml parsers.

4. umm... having the FullQuery( or some unique part) name in the xml
would be a good thing.

5. it would probably be nice to also have the query_type (the db_api
call),
if only for debuggin purposes.

6. dropping all the dispatcher files in the directories with the
content sounds like an ugly mess to me. a central location in a
package for this type of content sounds much better, i hate dealing
with fs clutter. there is also the matter of making it easier for
the dispatcher to find things (minor) and one less registered proc
(minor) for the request processor.

7. add the file name and proc (if lib) to the xml. this gives some
flexibility in terms of where the sql file maybe located.

8. change the xml tag for querytext to query_text, a stylistic point.

i know i'm adding a bit to the xml spec, but my idea for development
and the spec is not to put the onus of database indepedence on the
developer but instead to create some developer tools to facilitate
meeting the requirements of the query dispatcher by extracing the
xml for the sql straight from a package. (including replacement of
the originating call with the appropiate calls to the dispatcher).
basically this offers developers freedom to develop without having
to edit a separate file for db calls, which seems to be the major
arg, i see here against the current spec.

its hard to evaluate the spec or this claim without working
prototypes to look at. that said i spent a couple hrs today and
worked up an extractor which fufills the spec as outlined in terms
of xml descriptors for queries. currently its just a prototype, but
i've built it with flexibility in input and output in mind, so that
it can change with the spec. it incoporates ryan's suggestion and
creates per package files. i've run it against the 50 odd packages
in the acs-packages cvs directory @aD. its still got a small number
of minor issues (which i'll resolve on tuesday when i get some free
time), but on whole it preforms well (handling vast majority of
cases, and taking a little over a second to run on my laptop). the
whole thing is written in python (because i value my time, both
present and future) and is currently downloadable from

http://www.zope.org/Members/k_vertigo/AcsSqlExtractor.py

the commandline options are neglibile at the moment basically it
works by calling it thusly,

python AcsSqlExtractor.py acs_root

it will create a directory called output with resulting files
including one called statistics which has some info it gathered
during the extraction, namely errors and usage stats on the db_api
in the files read in. its got some hardcoded values in it, that i
will change as i develop it further if people are interested in this
approach. i haven't run on the acs core, and its hard coded
currently to run on the cvs of the packages (although anyone looking
at the source can see how to change it).

so what did i learn from my experiment.

1. python is fun
2. the size of the resultant xml files (with all the xml cruft) is
neglible ~700k from over 671 tcl files and 50 odd packages.
3. there are several sql calls that use tcl vars for the query. this
needs to be dealt with by the developer (and perhaps by the
dispatcher spec).
4. there is alot of pl/sql code lying around, the db api call
db_exec_plsql is the third most popular call after db_1row, and
db_dml.

ok. its 4am and i need to head to sleep...

When I first mentioned proc_sql I hadn't thought of how to exactly implement it, and it is still a bit hard, because I am not fluent in Tcl. So I'll keep it somewhat more general. I do not know how portable this mechanism is for to other webservers, but I thing it would not be too hard to implement it in IIS/ASP.

What I want to end up with is the ability to do something like this:

	set selection [bboard_latest_sql $db $bboard_id $bboard_user_last_login]

	while {[ns_db getrow $db $selection]} {
	  ...
	}

The SQL library file looks something like:

proc_sql bboard_latest_sql "sql92" {dbhandle bboard_id last_login} {
	#this is the query we want to do, the specs make it mandatory to use the 'sql' variable.
 	#that way we can automagically translate to Java or something.

	set sql "select * from bboard where bboard_id=$bboard_id and created < date($last_login)"
	
	set dbr [ns_db getrow $dbhandle $sql]

	return $dbr	
}

proc_sql bboard_latest_sql "postgres/7" {dbhandle bboard_id last_login} {

	set sql "select * from bboard where bboard_id=$bboard_id and created < date($last_login)"
	
	set dbr [ns_db getrow $dbhandle $sql]

	return $dbr	
}

Sourcing the library involves stuffing the parameters of every proc_sql call into a list, pre-filterering the queries for queries that will not work anyway. So a Postgres install will not stuff any Oracle functions in its dispatcher list. At the end of library sourcing, a decision has to be wether we use dynamic query resolving or static.

In the dynamic scenario, the 'proc_sql_activate_libarary' function will bind each distinct sql proc to the generic dispatcher function, the dispatcher can then, upon invocation (with the Tcl-info command?!?) discover its parameter definition and dispatch the most appropriate version it found in the proc_sql_list.

In the static scenario, after sourcing the libary, the most appropriate version of each proc_sql is found and then all of those are added to the Tcl environment with proc $proc_sql_name $proc_sql_args. This will make library loading take longer, but dispatching will not require any additional list searching.

As a third option, a hybrid could be made where most sql is static, and some comes from resourcing the sql.tcl files, and some libraries are dynamic. Although I can't really imagin where that would be a good idea, but still. I do not suppose there will be many installations where two different flavours of database are used for different sections of the ACS.

As for translating this source file to Java, I do not know how to create a catch-all converter, but it can probably be done. Each invocation of [ns_db XXX $db $sql] can probably be translated 1-to-1 to a Java construct that returns an object for the result.

I am not sure wether this method would be any better than the proposed XML standard. The best way to go about this, for clarity and standards enforcing, would put all the proc_sql calls in a separate file, like the sql.xml. This methodology could probably be implemented on top of the XML files, running as the innards of the Query dispatcher. That would work best if the XML definition gets an additional attribute called 'query-type' which could be one of 'getrow' '0or1row' '1row', etc. Come to think of it, a query type attribute might be useful for other purposes as well.

major changes include a lot of refactoring (i took a few minutes and
designed this time:). all parsing code in the parser (really just
regexs), configuration via commandline, config file, or environment
variables, somewhat configurable outputing, use of container objects
to model package->directory->file->content.

this release has less true functionality than the original when
operating on sql files, but it is much better designed and thought
out in terms of flexibility. the biggest todo thing now is rewriting
the parser (the writer needs some work, but that will be easier to
figure out once this spec finalizes (at this point it looks like i
need to use dom to insert in a db's specific version of query to
query xml file). the current one uses regex, which are not my forte,
but i believe that i've reached the limit of what i can do
realistically with regexs. if anyone here is a regex guru/expert and
wants to help please email me. at this point i believe there is too
much possible contextual information thats possible in the body of a
query, and a great deal more of the acs4 uses dynamic queries than i
had previously thought. there just seems to be too much irregularity
in the possible sql calls to use regexes for everything. a brief
contrived example in case someone can come up with a solution (btw
python has perl style regexs), to my current problem of retrieving
the query portion of a db api call.

db_string foo "
select user_name from [AdParameter "user_table"]
where user_id = ${user_id}
"
(or the alternate syntax with {select...})

at this point i'm going to try and hand roll a context based
descent parser (i no jack about parser terminology, but i believe
this is correct, and it looks pretty easily doable). the basic idea
behind the new parser is to use a simple regex to identify db calls
and use a parser on a window around the context of that call to
parse out the query properly for statistics analysis, and warnings
about dynamic sql.

the main reason i'm putting this out right now in this state, is to
invite comments and to reassure anyone who's interested that this is
being worked on. feedback welcome.

xml query files - all db versions in one files, i'm obviously coming
from a generative POV, but i'm also thinking about it from a
developers perspective of going through all the various db versions
of a call to add your own. i was originally thinking that with a
generative approach, the queries could be separted from the web
directory and be further subdivided into directories based on db
type (including ansi). after the server tastes the db pool it could
process the appriopiate directory (plus the ansi for anything not
found). after realizing how much dynamic sql there is i realize that
this is not feasible and that a developer will need to do some hand
editing (maybe... more on dynamic sql later).

this would also have the added benefit of making the parsing easier
and less memory intensive (read faster), the current spec requires
reading all of the lower level nodes (of a file with all db types)
to figure which db a query is for, storing this at a higher level in
the xml format would make vast reductions in the numbers of nodes
that need to visited. i'm granting that any xml parsing is a pretty
trivial concern, but i don't want my server taking 5 minutes to
startup (dom is slow, sax would be ideal).

speaking of which what tcl libraries were people thinking about
using for xml processing, i haven't looked at tcl's xml capabilities
for a while, and the last time i did they were all dom based written
in tcl. does tcl have a front end to expat?, a fast dom (level2?),
etc (links, please).

the bottom line concern here is def. whats easiest for developers to
work with?. is having a buffer to another directory open a huge
hassle for developers?

the acs-4 already aggregates content and logic for that content in
the www. but the whole query dispatching system seems to be moving
towards something more along the lines of system resources (esp if
query reuse comes into play), and i don't think its a good idea to
stick system resources in www. (aesthestics).

query reuse - is a good thing, and should help matienance, but i'm
clueless on the best way to go about it. btw. afaik there is no hard
enforcement of unique names built into acs, its all convention
based. in fact i see some duplication of names in the same file.
grep -rn db_* * > ../out.sql

tcl proc - embedding tcl is a not an elegant solution nor is
rengineering tcl code in another language. i still think this is a
very interesting idea, but its not language indepedent. and since
i'm considering doing a zope or perhaps a java port i would really
prefer an xml based format.

three files - i don't see how its possible, to remove the tcl files
all toghether. there are a lot of dynamically generated calls, much
more than i had originally thought, this (dynamics queries) is
something that needs to be discussed in the context of the query
dispatcher. also figure in things like redirects, ad_page_contract,
etc... eliminating the tcl would basically mean recreating it in
another form, why bother?

dtd - if no one else gets around to it, i'll write one up when the
spec finalizes (i'm going to be using it for optional validation of
generated xml files).

metadata - pascal brings up alot cool ideas about increasing the
metadata associated with a query. i'm all for this in a generative
world, in a developer working by hand world this looks like it might
be a little too tedious. while i think having the information
available is a good thing. i think that keeping the dispatcher as
light as possible is more important than increasing its features (at
least until some real world tests are possible). IMO (i have no hard
evidence, but i can't possibly see how it could be otherwise) the
acs4 is slower than acs3x because of all the extra joins and stuff,
(permissions, and privilege heirarchy stuff alone  result isome slow
very common calls), i think streamlining the db abstraction layer to
keep its performance impact to a minimum is good thing. but adding
in metadata seems like a perfectly reasonable thing to do that
should be useful. i think that return types can be safely
encompassed by including the name of the db_api call in the metadata
(db_string, db_1row, db_multirow, etc). typing in tcl:), everything
is a string. comments are a cool. pre and post conditions on queries
sounds way too expensive, IMO, this is application specific
information and responsibility not the systems (unless you're using
a language like effiel, which has native support for this type of
thing).

dynamic sql - this seems to be the white horse that hasn't been
addressed yet. i was originally very concerned by this, but now i'm
not so sure if it really makes much impact in terms of
functionality. the original query is just being stored as string,
and when its retrieved by the dispatcher the string will be
evaluated in context of the pages where these variables presumably
exist. so in the sense of functionality its not a huge issue. by
dynamics sql i'm including all variants of non-hardcoded queries
including fully dynamic queries, those with embedded vars, and
function calls. it does become issue wrt to being able to quantify a
queries db abstraction, since the code that generates the query
could possible be in the tcl page. i really think that this issues
needs some discussion. 95%+ of the dynamic sql calls i currently see
in the acs4 are not of this variety and just use a simple function
call or variable to add a value to a comparison clause or what not.
but the remaining cases could be problematic.

sql92- ben could you elaborate a little more on what envisioned for
adding sql92 support. its sounds a little open ended at the moment.
from what you wrote it looks like your identifying a queries
constructs and comparing them to an rdbms capabilities wrt sql92
compliance... why not just take a simpler approach and use the sql92
query unless an rdbms version of the query is found.