Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-views-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-views-create.sql,v
diff -u -r1.7 -r1.8
--- openacs-4/packages/file-storage/sql/postgresql/file-storage-views-create.sql	18 Feb 2003 16:56:47 -0000	1.7
+++ openacs-4/packages/file-storage/sql/postgresql/file-storage-views-create.sql	17 May 2003 10:38:11 -0000	1.8
@@ -8,17 +8,17 @@
 
 create view fs_urls_full
 as
-    select fs_urls.url_id,
-           fs_urls.url,
-           fs_simple_objects.folder_id,
-           fs_simple_objects.name,
-           fs_simple_objects.description,
+    select cr_extlinks.extlink_id as url_id,
+           cr_extlinks.url,
+           cr_items.parent_id as folder_id,
+           cr_extlinks.label as name,
+           cr_extlinks.description,
            acs_objects.*
-    from fs_urls,
-         fs_simple_objects,
+    from cr_extlinks,
+         cr_items,
          acs_objects
-    where fs_urls.url_id = fs_simple_objects.object_id
-    and fs_simple_objects.object_id = acs_objects.object_id;
+    where cr_extlinks.extlink_id = cr_items.item_id
+    and cr_items.item_id = acs_objects.object_id;
 
 create view fs_folders
 as
@@ -37,7 +37,6 @@
     where cr_folders.folder_id = ci.item_id
     and cr_folders.folder_id = acs_objects.object_id;
 
-
 create view fs_files
 as
     select cr_revisions.item_id as file_id,
@@ -59,41 +58,36 @@
 
 create view fs_objects
 as
-    select fs_folders.folder_id as object_id,
-           0 as live_revision,
-           'folder' as type,
-           fs_folders.content_size,
-           fs_folders.name,
-           '' as file_upload_name,
-           fs_folders.last_modified,
-           '' as url,
-           fs_folders.parent_id,
-           fs_folders.key,
-           0 as sort_key
-    from fs_folders
-    union all
-    select fs_files.file_id as object_id,
-           fs_files.live_revision,
-           fs_files.type,
-           fs_files.content_size,
-           fs_files.name,
-           fs_files.file_upload_name,
-           fs_files.last_modified,
-           '' as url,
-           fs_files.parent_id,
-           fs_files.key,
-           1 as sort_key
-    from fs_files
-    union all
-    select fs_urls_full.url_id as object_id,
-           0 as live_revision,
-           'url' as type,
-           0 as content_size,
-           fs_urls_full.name,
-           fs_urls_full.name as file_upload_name,
-           fs_urls_full.last_modified,
-           fs_urls_full.url,
-           fs_urls_full.folder_id as parent_id,
-           fs_urls_full.url as key,
-           1 as sort_key
-    from fs_urls_full;
+    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 ci2
+              where ci2.content_type <> 'content_folder'
+                and ci2.tree_sortkey between cr_items.tree_sortkey and tree_right(cr_items.tree_sortkey))
+        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_revisions.title as file_upload_name,
+      acs_objects.last_modified,
+      cr_extlinks.url,
+      cr_items.parent_id,
+      cr_items.name as key,
+      case
+        when cr_items.content_type = 'content_folder' then 0
+        else 1
+      end as sort_key
+    from cr_items left join cr_extlinks on (cr_items.item_id = cr_extlinks.extlink_id)
+      left join cr_folders on (cr_items.item_id = cr_folders.folder_id)
+      left join cr_revisions on (cr_items.live_revision = cr_revisions.revision_id)
+      join acs_objects on (cr_items.item_id = acs_objects.object_id);
