postgresql7.1
select person__name(o.creation_user) as owner,
f.name as title,
f.parent_id,
coalesce(f.url,f.file_upload_name) as name,
acs_permission__permission_p(:file_id,:user_id,'write') as write_p,
acs_permission__permission_p(:file_id,:user_id,'delete') as delete_p,
acs_permission__permission_p(:file_id,:user_id,'admin') as admin_p,
content_item__get_path(o.object_id, :root_folder_id) as file_url, f.live_revision
from acs_objects o, fs_objects f
where o.object_id = :file_id
and f.object_id = o.object_id
select r.title,
r.revision_id as version_id,
person__name(o.creation_user) as author,
o.creation_user as author_id,
r.mime_type as type,
m.label as pretty_type,
to_char(o.last_modified,'YYYY-MM-DD HH24:MI:SS') as last_modified_ansi,
r.description,
acs_permission__permission_p(r.revision_id,:user_id,'admin') as admin_p,
acs_permission__permission_p(r.revision_id,:user_id,'delete') as delete_p,
coalesce(r.content_length,0) as content_size
from acs_objects o, cr_items i,cr_revisions r
left join cr_mime_types m on r.mime_type=m.mime_type
where o.object_id = r.revision_id
and r.item_id = i.item_id
and r.item_id = :file_id
and acs_permission__permission_p(r.revision_id, :user_id, 'read')
$show_versions order by last_modified desc
postgresql8.4
select r.title,
r.revision_id as version_id,
person__name(o.creation_user) as author,
o.creation_user as author_id,
r.mime_type as type,
m.label as pretty_type,
to_char(o.last_modified,'YYYY-MM-DD HH24:MI:SS') as last_modified_ansi,
r.description,
acs_permission__permission_p(r.revision_id,:user_id,'admin') as admin_p,
acs_permission__permission_p(r.revision_id,:user_id,'delete') as delete_p,
coalesce(r.content_length,0) as content_size
from acs_objects o, cr_items i,cr_revisions r
left join cr_mime_types m on r.mime_type=m.mime_type
where o.object_id = r.revision_id
and r.item_id = i.item_id
and r.item_id = :file_id
and acs_permission__permission_p(r.revision_id, :user_id, 'read')
$show_versions order by last_modified desc