Forum OpenACS Development: Tcl-proc dispatching

First off, I agree with Ben that language dependancy should be brought to a minimum and a proc_sql method would not only be language dependant, but also very much scripting language dependant. On the other hand, because we are using a scripting language, we should be able to overload the proc_sql_* functions so it will export any format we like, even another programming language. Using Tcl in the package installers should never be a problem, so in fact, the proc_sql could even become a sql-pre-processor.

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.