___By keeping most of the queries in tcl procs, we can simply the learning curve. This has to be balanced against performance of course. A tcl proc can still return mutiple rows from a query, so we don't need to do a seperate query for each row. The query inside a tcl proc should do whatever joins are necessary to return all the information just as efficiently as if you had embedded the query inside your tcl page.___
You seem to be suggesting that we should make OpenACS customizable by programmers that do not understand SQL and refuse to learn it. I strongly disagree. Direct access and manipulation of the database is in my opinion a fundamental design principle of OpenACS and a major reason for its strength.
There is a crucial difference between central services like the CR and frontend packages like forums. For the former it makes sense to simplify access by providing a tcl api in my opinion, since the same operations like item creation are used by many different packages, and the interface to these operations should thus not change. Still it should be possible to access the tables of the CR directly from a package and join against them in package specific queries for example.
Using tcl procs in front end packages like forums is a bad idea because it adds another level of abstraction that makes it harder to debug, develop and optimize.
One example from forums: there is a proc called forum::get which executes this query: "select forums_forums.* from forums_forums where forums_forums.forum_id = :forum_id" and returns the results in an array. Besides the fact that it is bad practice to use * instead of naming the fields to return - even if the query would list all fields of the forums_forums table that need to be returned, this way of doing things shows that this is inefficient. Look at message-post.tcl for example - it never needs any other value from the returned array than forums(name), so the query "select name from ..." would have been sufficient.
The wast of db resources becomes propably worse when the queries outsourced to tcl procs involve expensive joins etc.
And I don't see why code that requires me to look into yet another file and keep the whole stack of what calls what in mind, would be easier to understand or maintain.
