Index: openacs-4/packages/file-storage/sql/oracle/upgrade/upgrade-5.1.0a6-5.1.0a7.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/upgrade/upgrade-5.1.0a6-5.1.0a7.sql,v
diff -u -r1.1.2.1 -r1.1.2.2
--- openacs-4/packages/file-storage/sql/oracle/upgrade/upgrade-5.1.0a6-5.1.0a7.sql	9 Aug 2004 13:57:16 -0000	1.1.2.1
+++ openacs-4/packages/file-storage/sql/oracle/upgrade/upgrade-5.1.0a6-5.1.0a7.sql	23 Aug 2004 17:30:06 -0000	1.1.2.2
@@ -76,3 +76,70 @@
 
 --warning: dropping the function results in a commit
 drop function tmp_fs_name_duplicate;
+
+drop view fs_files;
+
+
+create or replace 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.title as file_upload_name,
+           cr_revisions.content_length as content_size,
+           cr_items.name,
+           cr_revisions.publish_date as last_modified,
+           cr_items.parent_id,
+           cr_items.name as key
+    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;
+
+drop view fs_objects;
+
+
+create or replace view fs_objects
+as
+    select cr_items.item_id as object_id,
+      cr_items.live_revision,
+      case
+        when cr_items.content_type = 'content_folder' then 'folder'
+        when cr_items.content_type = 'content_extlink' then 'url'
+        else cr_revisions.mime_type
+      end as type,
+      case
+        when cr_items.content_type = 'content_folder'
+        then (select count(*)
+              from cr_items ci
+              where ci.content_type <> 'content_folder'
+              connect by prior ci.item_id = ci.parent_id
+              start with ci.item_id = cr_folders.folder_id)
+        else cr_revisions.content_length
+      end as content_size,
+      case
+        when cr_items.content_type = 'content_folder' then cr_folders.label
+        when cr_items.content_type = 'content_extlink' then cr_extlinks.label
+        else cr_items.name
+      end as name,
+      cr_items.name as file_upload_name,
+      cr_revisions.title,
+      cr_revisions.publish_date as last_modified,
+      cr_extlinks.url,
+      cr_items.parent_id,
+      cr_items.name as key,
+      cr_mime_types.label as pretty_type,
+      case
+        when cr_items.content_type = 'content_folder' then 0
+        else 1
+      end as sort_key
+    from cr_items, cr_extlinks, cr_folders, cr_revisions, acs_objects, cr_mime_types
+    where cr_items.item_id = cr_extlinks.extlink_id(+)
+      and cr_items.item_id = cr_folders.folder_id(+)
+      and cr_items.item_id = acs_objects.object_id
+      and cr_items.live_revision = cr_revisions.revision_id(+)
+      and cr_revisions.mime_type = cr_mime_types.mime_type(+);
+