Forum OpenACS Q&A: db_multirow -extend and other enhancements

Request notifications

Posted by Lars Pind on
I've long used the

db_multirow foo bar { select email, '' as url from cc_users } {
  set url "..."

hack in order to add computed columns to my multirow.

Well, now there's a slightly cleaner way which doesn't involve
asking the DB to add them for us in this ugly way: db_multirow -
extend. Just say:

db_multirow -extend { url } foo bar { select email from cc_users } {
set url "..." }

At the same time, I fixed db_multirow so it sets the foo:columns
variable to the column list properly. This is useful if you want to
later do a template::multirow extend.

Also, while doing this, I removed the hack where db_multirow would
regsub all occurences of "foo" (the multirow name)
to "foo:1", "foo:2", etc. (foo:rownum).

This was done so that you could add columns to the multirow in this
way, by saying set foo(url) "..." which would then be translated
into "set foo:1(url)" ...".

Now, you just say -extend { url } and then in the code block you say
set url "...", and the local variable url is automatically put into
the multirow for you.

The also works with -append, with the caveat that -extend doesn't
have any effect with an -append switch. Instead, if uses the columns
that it got from the first db_multirow, the one without the -append.
I encourage you to still specify -extend on calls with -append, for

I think this behavior makes sense: When you append to a multirow,
you shouldn't be changing the schema.

Again, ask Don when this'll get into the tree.

Comments and feedback?

Posted by Tilmann Singer on
This is very cool, thanks Lars!

As far as I know the ability to append a code block to db_multirow and let it manipulate the field values before they go into the datasource was completely undocumented until now. I hope the patch also adds some explanations to the proc's documentation string.

I would like to suggest to also add the following feature to the documentation: if for any reason you want to skip a row in the resultset without adding it to the datasource, then you can call the "continue" statement within the code block. Most times it is propably better to write more accurate queries that don't return unwanted rows, but in some situations that might be useful.

Posted by Lars Pind on
Good point.

And shame on me for not documenting properly. Actually, I looked at it, and noted that the documentation was so out of date, that I didn't want to bother with updating it just now.

You should file a bug report. actually. Would you mind?

Posted by Tilmann Singer on
Voila, a db_multirow api documentation bug report: