OpenACS

gustafn
committed
oacs-5-9:gustafn:20170806113234
on 06 Aug 17
updated version number
/tcl/oacs-dav-procs-postgresql.xql (+27 -33)
1 1 <?xml version="1.0"?>
2 2 <queryset>
3 3   <rdbms><type>postgresql</type><version>7.1</version></rdbms>
4 4
5 5   <fullquery name="oacs_dav::conn_setup.get_item_id">
6 6     <querytext>
7 7       select content_item__get_id(:item_name,:parent_id,'f')
8 8     </querytext>
9 9   </fullquery>
10 10
11 11   <fullquery name="oacs_dav::children_have_permission_p.child_perms">
12 12     <querytext>
13 13             select count(*)
14 14             from cr_items c1, cr_items c2
15 15             where c2.item_id = :item_id
16 16             and c1.tree_sortkey between c2.tree_sortkey and tree_right(c2.tree_sortkey)
17               and not  exists (select 1
18                      from acs_object_party_privilege_map m
19                      where m.object_id = cr_items.item_id
20                        and m.party_id = :user_id
21                        and m.privilege = :privilege)
  17             and acs_permission__permission_p(cr_items.item_id, :user_id, :privilege)
  18
22 19     </querytext>
23 20   </fullquery>
24 21  
25     <fullquery
26       name="oacs_dav::impl::content_folder::propfind.get_properties">
  22   <fullquery name="oacs_dav::impl::content_folder::propfind.get_properties">
27 23     <querytext>
28 24       select
29 25       coalesce (cr.content_length,0) as content_length,
30 26       coalesce(cr.mime_type,'*/*') as mime_type,
31 27       to_char(timezone('GMT',o.creation_date) :: timestamptz ,'YYYY-MM-DD"T"HH:MM:SS.MS"Z"') as creation_date,
32 28       to_char(timezone('GMT',o.last_modified) :: timestamptz ,'Dy, DD Mon YYYY HH:MM:SS TZ') as last_modified,
33 29       ci1.item_id,
34 30         case when ci1.item_id=ci2.item_id then '' else ci1.name end as name,
35 31         content_item__get_path(ci1.item_id,:folder_id) as item_uri,
36 32         case when o.object_type='content_folder' then 1 else 0 end
37 33         as collection_p
38 34       from
39 35         cr_items ci1,
40 36         cr_revisions cr,
41 37         cr_items ci2,
42 38         acs_objects o
43 39       where
44           ci1.live_revision = cr.revision_id and
45           ci1.tree_sortkey between ci2.tree_sortkey and tree_right(ci2.tree_sortkey) and
46           ci2.item_id=:folder_id and
47           ci1.item_id = o.object_id and
48           (tree_level(ci1.tree_sortkey) - tree_level(ci2.tree_sortkey)) <= :depth :: integer and
49           exists (select 1
50                     from acs_object_party_privilege_map m
51                     where m.object_id = ci1.item_id
52                     and m.party_id = :user_id
53                     and m.privilege = 'read')
  40          ci1.live_revision = cr.revision_id
  41          and ci1.tree_sortkey between ci2.tree_sortkey and tree_right(ci2.tree_sortkey)
  42          and ci2.item_id=:folder_id
  43          and ci1.item_id = o.object_id
  44          and (tree_level(ci1.tree_sortkey) - tree_level(ci2.tree_sortkey)) <= :depth ::integer
  45          and acs_permission__permission_p(ci1.item_id, :user_id, 'read')
  46
54 47       union
55 48       select 0 as content_length,
56 49         '*/*' as mime_type,
57 50         to_char(timezone('GMT',o.creation_date) :: timestamptz ,'YYYY-MM-DD"T"HH:MM:SS.MS"Z"') as creation_date,
58 51         to_char(timezone('GMT',o.last_modified) :: timestamptz ,'Dy, DD Mon YYYY HH:MM:SS TZ') as last_modified,
59 52         ci1.item_id,
60 53         case when ci1.item_id=ci2.item_id then '' else ci1.name end as name,
61 54         content_item__get_path(ci1.item_id,:folder_id) as item_uri,
62 55         case when o.object_type='content_folder' then 1 else 0 end
63 56         as collection_p
64 57       from
65 58         cr_items ci1,
66 59         cr_items ci2,
67 60         acs_objects o
68 61       where
69           ci1.tree_sortkey between ci2.tree_sortkey and tree_right(ci2.tree_sortkey) and
70           ci2.item_id=:folder_id and
71           ci1.item_id = o.object_id and
72           (tree_level(ci1.tree_sortkey) - tree_level(ci2.tree_sortkey)) <= :depth :: integer and
73           exists (select 1
74                     from acs_object_party_privilege_map m
75                     where m.object_id = ci1.item_id
76                     and m.party_id = :user_id
77                     and m.privilege = 'read') and
78           not exists (select 1
  62         ci1.tree_sortkey between ci2.tree_sortkey and tree_right(ci2.tree_sortkey)
  63         and ci2.item_id = :folder_id
  64         and ci1.item_id = o.object_id
  65         and (tree_level(ci1.tree_sortkey) - tree_level(ci2.tree_sortkey)) <= :depth ::integer
  66         and acs_permission__permission_p(ci1.item_id, :user_id, 'read')
  67         and not exists (select 1
79 68                   from cr_revisions cr
80 69                   where cr.revision_id = ci1.live_revision)
81 70     </querytext>
82 71   </fullquery>
83 72
84 73   <fullquery
85 74     name="oacs_dav::impl::content_revision::propfind.get_properties">
86 75     <querytext>
87 76       select
88 77         ci.item_id,
89 78         ci.name,
90 79         content_item__get_path(ci.item_id,:folder_id) as item_uri,
91 80         coalesce(cr.mime_type,'*/*') as mime_type,
92 81         coalesce(cr.content_length,0) as content_length,
93 82         to_char(timezone('GMT',o.creation_date) :: timestamptz ,'YYYY-MM-DD"T"HH:MM:SS.MS"Z"') as creation_date,
94 83         to_char(timezone('GMT',o.last_modified) :: timestamptz ,'Dy, DD Mon YYYY HH:MM:SS TZ') as last_modified
95 84       from cr_items ci,
96 85       acs_objects o,
97 86       cr_revisions cr
98 87       where
99 88       ci.item_id=:item_id
100 89       and ci.item_id = o.object_id
101 90       and cr.revision_id = ci.live_revision
102         and exists (select 1
103                     from acs_object_party_privilege_map m
104                     where m.object_id = ci.item_id
105                     and m.party_id = :user_id
106                     and m.privilege = 'read')
  91       and acs_permission__permission_p(ci.item_id, :user_id, 'read')
107 92     </querytext>
108 93   </fullquery>
109 94
110 95   <fullquery
111 96     name="oacs_dav::impl::content_folder::mkcol.create_folder">
112 97     <querytext>
113 98       select content_folder__new(
114 99           :new_folder_name,
115 100           :label,
116 101           :description,
117 102           :parent_id,
118 103           :parent_id,
119 104           NULL,
120 105           current_timestamp,
121 106           :user_id,
122 107           :peer_addr
123 108       )
124 109     </querytext>
125 110   </fullquery>
126 111
 
264 249
265 250   <fullquery
266 251     name="oacs_dav::impl::content_folder::copy.delete_for_copy">
267 252     <querytext>
268 253       select content_folder__delete(:dest_item_id,'t');
269 254     </querytext>
270 255   </fullquery>
271 256  
272 257   <fullquery name="oacs_dav::impl::content_revision::copy.get_dest_id">
273 258     <querytext>
274 259         select content_item__get_id(:new_name,:new_parent_folder_id,'f')
275 260     </querytext>
276 261   </fullquery>
277 262
278 263   <fullquery name="oacs_dav::impl::content_revision::move.get_dest_id">
279 264     <querytext>
280 265         select content_item__get_id(:new_name,:new_parent_folder_id,'f')
281 266     </querytext>
282 267   </fullquery>
283 268
  269   <fullquery name="oacs_dav::children_have_permission_p.revision_perms">
  270     <querytext>
  271       select count(*)
  272       from cr_revisions
  273       where item_id = :item_id
  274       and not acs_permission__permission_p(revision_id, :user_id, 'delete')
  275     </querytext>
  276   </fullquery>
  277  
284 278 </queryset>
285 279