Forum OpenACS Development: Question about db_multirow and <multiple>

Hi,

I have a question about the use of db_multirow and the <multiple> tag. The background of the question is that I am trying to extend file-storage to have a simple file "check-out" and "check-in" capability -- I've got a team of people working together on a large project involving MS Word documents, and we need to ensure that only one person is making changes at a time (because Word can't do a decent merge!).

Okay, so I created a new table, fs_checkouts, with the following definition:

create table fs_checkouts (
    file_id    integer
                    constraint fs_checkouts_fk references acs_objects
                    constraint fs_checkouts_pk primary key,
    user_id    integer not null
                    constraint fs_checkouts_user_fk references users,
    date_out    timestamp(1) not null
                    default current_timestamp(1),
    comment    varchar
);

Then, I extended the "select_folder_contents" query in folder-chunk-postgresql.xql, as follows--this is what appears in <fullquery>, and its results are returned in an array called contents, using db_multirow:

            select fs_objects.object_id,
                  fs_objects.name,
                  fs_objects.live_revision,
                  fs_objects.type,
                  to_char(fs_objects.last_modified, 'Month DD YYYY HH24:MI') as last_modified,
                  fs_objects.content_size,
                  fs_objects.url,
                  fs_objects.sort_key,
                  fs_objects.file_upload_name,
                  fs_checkouts.user_id as checkout_user,
                  fs_checkouts.date_out as checkout_date,
                  fs_checkouts.comment as checkout_comment,
                  cr_revisions.description as revision_comment,
                  registered_users.first_names as checkout_firstname,
                  registered_users.last_name as checkout_lastname,
                  case
                    when type = 'url'
                    then acs_permission__permission_p(fs_objects.object_id, :viewing_user_id, 'delete')
                    else 't'
                  end as delete_p,
                  case
                    when type = 'url'
                    then acs_permission__permission_p(fs_objects.object_id, :viewing_user_id, 'write')
                    else 't'
                  end as write_p
            from fs_objects
            left outer join fs_checkouts
                on fs_checkouts.file_id = fs_objects.object_id
            left outer join cr_revisions
                on cr_revisions.revision_id = fs_objects.live_revision
            left outer join registered_users
                on registered_users.user_id = fs_checkouts.user_id
            where fs_objects.parent_id = :folder_id
              and exists (select 1
                  from acs_object_party_privilege_map m
                  where m.object_id = fs_objects.object_id
                    and m.party_id = :viewing_user_id
                    and m.privilege = 'read')
            order by fs_objects.sort_key, fs_objects.name

My intention is that I can now include in the folder-chunk table variables such as @contents.revision_comment@, and get the text of the comment attached to the live revision; or,

<if @contents.checkout_date@ ne "">@contents.checkout_date@ by @contents.checkout_firstname@ @contents.checkout_lastname@: mailto:"@contents.checkout_comment";</if>

However, I am finding that my new variables are not appearing in the array. I think that the query itself is sound, because I have tested it with my own user id and the id of the parent folder separately in psql.

Can anyone help me understand why my new variables are not being included in the contents array?

Thanks much,
Shawn Harrison

Collapse
Posted by Sean Harrison on
P.S. I should also mention that the intention of the fs_checkouts table is to insert a new row when a file is checked out, then delete that row when the file is checked in. I realize it would be better to have a history, but I want to keep it simple for starters. Thanks.---SAH
Collapse
Posted by Sean Harrison on
P.P.S. I also realize that there are better ways to accomplish what I'm trying to accomplish; I would be happy to follow some other approach if you think it would be better. Thanks. --SAH
Collapse
Posted by Brian Fenton on
This might seem a bit too obvious, but are you certain your new query is being watched? Try temporarily moving the query inline into your TCL file and see if it works from there.
Collapse
Posted by Mark Aufflick on
Take a look in the apm and see if there is still a "reload" link available even though the file is being watched. If you have a syntax error in a tcl file, or an xml error in an xql file, then the watch proc is not able to parse it, and the old version stays live.

that one used to get me all the time.

Collapse
Posted by Sean Harrison on
Mark and Brian,

That was it! Thank you very much. That complex query now is working perfectly, and although it is expensive (slow) it gets the job done, which is what I need right now.

Shawn Harrison