Forum OpenACS Q&A: Minor fix in /admin/gp/one-table.tcl

I was playing around with General Permissions, and stumbled on a minor bug fix in /admin/gp/one-table.tcl:

if { $rownum_first_this_page == "1" } {
set selection [ns_db select $db "
    select [join $column_name_list ","]
      from $denorm_view_name
     limit [expr $rownum_last_this_page - $rownum_first_this_page + 1]
"]
} else {
set selection [ns_db select $db "
    select [join $column_name_list ","]
      from $denorm_view_name
     limit [expr $rownum_last_this_page - $rownum_first_this_page + 1]
    offset $rownum_first_this_page
"]

instead of:

set selection [ns_db select $db "
    select [join $column_name_list ","]
      from $denorm_view_name
     limit [expr $rownum_last_this_page - $rownum_first_this_page + 1]
    offset $rownum_first_this_page
"]

For some reason, when you're on the first page of selections (i.e., $rownum_first_this_page == "1"), if you use offset $rownum_first_this_page, the first row doesn't get displayed in the output.

Yes, you're absolutely right!

In the process of verifying your suggestion, I also discovered an easy way to fake an OUTER JOIN in a postgresql VIEW. As you know, outer joins in postgresql have to be done as some sort of UNION statement, and, as it turns out, according to error messages I got, the use of unions in postgresql views is not supported yet.

In order to make /admin/gp stuff work, by way of the example that comes with Open ACS, you'll see that for each table you want to do general permissions on, you need a "denormalized view" of that table. The example that comes pre-packaged with Open ACS is the fs_versions table, which has a corresponding view called FS_VERSIONS_DENORM_VIEW. In the Open ACS 3.2.4 model, the table used to do the lookup for that "denormalized view" name (via the table that corresponds to it) is general_table_metadata.

So, in order to get any of the general permissions stuff in /admin/gp to work, it's necessary to:

1. Create a new entry for the table and view in general_table_metadata.
2. Create a view named like the "denorm_view_name" column in your new row in general_table_metadata.
   (The convention is {TABLENAME}_DENORM_VIEW.)

In order to get the CHAT_ROOMS_DENORM_VIEW to work, I had to "fake" an outer join between chat_rooms.group_id and user_groups.group_id, since, in this view, I decided I wanted to display the group_name (or null, in the case of null group_id's). My way of doing this was to use "CASE", as follows, in the select statement:

case when chat_rooms.group_id is not null
  then user_groups.group_name
  else null end as group_name

and then to use the following join:

WHERE ((chat_rooms.group_id = user_groups.group_id) OR (chat_rooms.group_id is null AND user_groups.group_id = 1));

The filter on user_groups.group_id, in "chat_rooms.group_id is null AND user_groups.group_id = 1", is required in order to "fake" the join; otherwise, you get much more data than you expect for the rows having null group_id's.

I wouldn't be surprised if this way of "faking" outer joins isn't more or less applicable across the board.