| |
53 |
53 |
cr_items, |
| |
54 |
54 |
acs_objects |
| |
55 |
55 |
where cr_revisions.revision_id = cr_items.live_revision |
| |
56 |
56 |
and cr_revisions.item_id = cr_items.item_id |
| |
57 |
57 |
and cr_items.content_type = 'file_storage_object' |
| |
58 |
58 |
and cr_revisions.revision_id = acs_objects.object_id; |
| |
59 |
59 |
|
| |
60 |
60 |
create or replace view fs_objects |
| |
61 |
61 |
as |
| |
62 |
62 |
select cr_items.item_id as object_id, |
| |
63 |
63 |
cr_items.live_revision, |
| |
64 |
64 |
case |
| |
65 |
65 |
when cr_items.content_type = 'content_folder' then 'folder' |
| |
66 |
66 |
when cr_items.content_type = 'content_extlink' then 'url' |
| |
67 |
67 |
else cr_revisions.mime_type |
| |
68 |
68 |
end as type, |
| |
69 |
69 |
case |
| |
70 |
70 |
when cr_items.content_type = 'content_folder' |
| |
71 |
71 |
then (select count(*) |
| |
72 |
72 |
from cr_items ci |
| |
73 |
|
where ci.content_type <> 'content_folder' |
| |
|
73 |
where (ci.content_type = 'content_extlink' or ci.content_type = 'file_storage_object') |
| |
74 |
74 |
connect by prior ci.item_id = ci.parent_id |
| |
75 |
75 |
start with ci.item_id = cr_folders.folder_id) |
| |
76 |
76 |
else cr_revisions.content_length |
| |
77 |
77 |
end as content_size, |
| |
78 |
78 |
case |
| |
79 |
79 |
when cr_items.content_type = 'content_folder' then cr_folders.label |
| |
80 |
80 |
when cr_items.content_type = 'content_extlink' then cr_extlinks.label |
| |
81 |
81 |
else nvl(cr_revisions.filename,cr_items.name) |
| |
82 |
82 |
end as name, |
| |
83 |
83 |
cr_items.name as file_upload_name, |
| |
84 |
84 |
cr_revisions.title, |
| |
85 |
85 |
case |
| |
86 |
86 |
when cr_items.content_type = 'content_folder' |
| |
87 |
87 |
then acs_objects.last_modified |
| |
88 |
88 |
else cr_revisions.publish_date |
| |
89 |
89 |
end as last_modified, |
| |
90 |
90 |
cr_extlinks.url, |
| |
91 |
91 |
cr_items.parent_id, |
| |
92 |
92 |
cr_items.name as key, |
| |
93 |
93 |
cr_mime_types.label as pretty_type, |
