Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql,v
diff -u -r1.10 -r1.11
--- openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql	21 Feb 2002 20:06:27 -0000	1.10
+++ openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql	4 Mar 2002 18:26:03 -0000	1.11
@@ -88,7 +88,30 @@
 
 end;' language 'plpgsql' with (iscachable);
 
+create function file_storage__get_package_id (
+    integer                     -- cr_items.item_id%TYPE
+) returns integer as '          -- fs_root_folders.package_id%TYPE
+declare
+    get_package_id__item_id     alias for $1;
+    v_package_id                fs_root_folders.package_id%TYPE;
+begin
+    select fs_root_folders.package_id
+    into v_package_id
+    from fs_root_folders,
+         (select c1.item_id
+          from cr_items c1,
+               cr_items c2
+          where c2.item_id = get_package_id__item_id
+          and c1.tree_sortkey between c2.tree_sortkey and tree_right(c2.tree_sortkey)
+          and c1.item_id <> get_package_id__item_id
+          order by c1.tree_sortkey desc) this
+    where fs_root_folders.folder_id = this.item_id;
 
+    if NOT FOUND then
+        return null;
+    end if;
+end;' language 'plpgsql' with (iscachable);
+
 create function file_storage__new_root_folder (
        --
        -- Creates a new root folder
@@ -691,3 +714,62 @@
 
 -- Comment out to disable site-wide search  interface
 \i file-storage-sc-create.sql
+
+create view fs_folders
+as
+    select cr_folders.folder_id,
+           cr_folders.label as name,
+           (select count(*)
+            from cr_items ci
+            where ci.parent_id = cr_folders__folder_id) as content_size,
+           (select site_node__url(site_nodes.node_id)
+            from site_nodes
+            where site_nodes.object_id = file_storage__get_package_id(cr_items.item_id)) as url,
+           cr_items.parent_id
+    from cr_folders,
+         cr_items
+    where cr_folders.folder_id = cr_items.item_id;
+
+create view fs_files
+as
+    select cr_revisions.item_id as file_id,
+           cr_revisions.revision_id as live_revision,
+           cr_revisions.mime_type as type,
+           cr_revisions.content_length as content_size,
+           cr_items.name,
+           acs_objects.last_modified,
+           (select site_node__url(site_nodes.node_id)
+            from site_nodes
+            where site_nodes.object_id = file_storage__get_package_id(cr_items.item_id)) as url,
+           cr_items.parent_id
+    from cr_revisions,
+         cr_items,
+         acs_objects
+    where cr_revisions.revision_id = cr_items.live_revision
+    and cr_revisions.item_id = cr_items.item_id
+    and cr_items.content_type = 'file_storage_object'
+    and cr_revisions.revision_id = acs_objects.object_id;
+
+create view fs_folders_and_files
+as
+    select fs_folders.folder_id as file_id,
+           0 as live_revision,
+           'Folder' as type,
+           fs_folders.content_size,
+           fs_folders.name,
+           to_date(null, 'YYYY-MM-DD HH24:MI') as last_modified,
+           fs_folders.url,
+           fs_folders.parent_id,
+           0 as sort_key
+    from fs_folders
+    union
+    select fs_files.file_id,
+           fs_files.live_revision,
+           fs_files.type,
+           fs_files.content_size,
+           fs_files.name,
+           fs_files.last_modified,
+           fs_files.url,
+           fs_files.parent_id,
+           1 as sort_key
+    from fs_files;