Mercurial > hg > webserver / file comparison

comparison: src/server/plugins/postgresql/webdav.c

src/server/plugins/postgresql/webdav.c

branch
webdav
changeset 375
32b8017f5308
parent 374
77506ec632a4
child 376
61d481d3c2e4
equal deleted inserted replaced
374:77506ec632a4 375:32b8017f5308
53 NULL, 53 NULL,
54 NULL 54 NULL
55 }; 55 };
56 56
57 57
58
58 /* 59 /*
59 * SQL Queries 60 * SQL query components
60 */ 61 */
61 62
62 // propfind with depth = 0 63 /*
63 // params: $1: resource_id 64 * PROPFIND queries are build from components:
64 static const char *sql_propfind_allprop_depth0 = "\ 65 *
65 select\n\ 66 * cte cte_recursive or empty
66 $2::text as ppath,\n\ 67 * select
67 r.resource_id,\n\ 68 * ppath ppath column
68 r.parent_id,\n\ 69 * cols list of columns
69 r.nodename,\n\ 70 * ext_cols* list of extension columns
70 r.iscollection,\n\ 71 * from from [table] / from cte
71 r.lastmodified,\n\ 72 * prop_join join with property table, allprop or plist
72 r.creationdate,\n\ 73 * ext_join* extension table join
73 r.contentlength,\n\ 74 * where different where clauses for depth0 and depth1
74 r.etag,\n\ 75 * order depth0 doesn't need order
75 p.prefix,\n\ 76 */
76 p.xmlns,\n\ 77
77 p.pname,\n\ 78 static const char *sql_propfind_cte_recursive = "\
78 p.lang,\n\
79 p.nsdeflist,\n\
80 p.pvalue\n\
81 from Resource r\n\
82 left join Property p on r.resource_id = p.resource_id\n\
83 where r.resource_id = $1;";
84
85 // propfind with depth = 0 for specific properties
86 // params: $1: resource_id
87 static const char *sql_propfind_depth0 = "\
88 select\n\
89 $2::text as ppath,\n\
90 r.resource_id,\n\
91 r.parent_id,\n\
92 r.nodename,\n\
93 r.iscollection,\n\
94 r.lastmodified,\n\
95 r.creationdate,\n\
96 r.contentlength,\n\
97 r.etag,\n\
98 p.prefix,\n\
99 p.xmlns,\n\
100 p.pname,\n\
101 p.lang,\n\
102 p.nsdeflist,\n\
103 p.pvalue\n\
104 from Resource r\n\
105 left join (\n\
106 select p.* from Property p\
107 inner join (select unnest($3::text[]) as xmlns, unnest($4::text[]) as pname) n\n\
108 on p.xmlns = n.xmlns and p.pname = n.pname\n\
109 ) p on r.resource_id = p.resource_id\n\
110 where r.resource_id = $1;";
111
112 // propfind with depth = 1
113 // params: $1: resource_id
114 static const char *sql_propfind_allprop_depth1 = "\
115 select\n\
116 case when r.resource_id = $1 then $2\n\
117 else $2 || '/' || r.nodename\n\
118 end as ppath,\n\
119 r.resource_id,\n\
120 r.parent_id,\n\
121 r.nodename,\n\
122 r.iscollection,\n\
123 r.lastmodified,\n\
124 r.creationdate,\n\
125 r.contentlength,\n\
126 r.etag,\n\
127 p.prefix,\n\
128 p.xmlns,\n\
129 p.pname,\n\
130 p.lang,\n\
131 p.nsdeflist,\n\
132 p.pvalue\n\
133 from Resource r\n\
134 left join Property p on r.resource_id = p.resource_id\n\
135 where r.resource_id = $1 or r.parent_id = $1\n\
136 order by case when r.resource_id = $1 then 0 else 1 end, nodename, resource_id;";
137
138
139 // propfind with depth = 1 for specific properties
140 // params: $1: resource_id
141 static const char *sql_propfind_depth1 = "\
142 select\n\
143 case when r.resource_id = $1 then $2\n\
144 else $2 || '/' || r.nodename\n\
145 end as ppath,\n\
146 r.resource_id,\n\
147 r.parent_id,\n\
148 r.nodename,\n\
149 r.iscollection,\n\
150 r.lastmodified,\n\
151 r.creationdate,\n\
152 r.contentlength,\n\
153 r.etag,\n\
154 p.prefix,\n\
155 p.xmlns,\n\
156 p.pname,\n\
157 p.lang,\n\
158 p.nsdeflist,\n\
159 p.pvalue\n\
160 from Resource r\n\
161 left join (\n\
162 select p.* from Property p\
163 inner join (select unnest($3::text[]) as xmlns, unnest($4::text[]) as pname) n\n\
164 on p.xmlns = n.xmlns and p.pname = n.pname\n\
165 ) p on r.resource_id = p.resource_id\n\
166 where r.resource_id = $1 or r.parent_id = $1\n\
167 order by case when r.resource_id = $1 then 0 else 1 end, nodename, resource_id;";
168
169 // recursive propfind
170 // params: $1: resource_id
171 static const char *sql_propfind_allprop_recursive = "\
172 with recursive resolvepath as (\n\ 79 with recursive resolvepath as (\n\
173 select\n\ 80 select\n\
174 '' as ppath,\n\ 81 '' as ppath,\n\
175 *\n\ 82 *\n\
176 from Resource\n\ 83 from Resource\n\
179 select\n\ 86 select\n\
180 p.ppath || '/' || r.nodename,\n\ 87 p.ppath || '/' || r.nodename,\n\
181 r.*\n\ 88 r.*\n\
182 from Resource r\n\ 89 from Resource r\n\
183 inner join resolvepath p on r.parent_id = p.resource_id\n\ 90 inner join resolvepath p on r.parent_id = p.resource_id\n\
184 )\n\ 91 )\n";
185 select\n\ 92
186 case when r.resource_id = $1 then $2\n\ 93 static const char *sql_propfind_select = "\
187 else $2 || r.ppath\n\ 94 select\n";
188 end as ppath,\n\ 95
189 r.resource_id,\n\ 96 static const char *sql_propfind_ppath_depth0 = "\
190 r.parent_id,\n\ 97 $2::text as ppath,\n";
191 r.nodename,\n\ 98
192 r.iscollection,\n\ 99 static const char *sql_propfind_ppath_depth1 = "\
193 r.lastmodified,\n\ 100 case when r.resource_id = $1 then $2\n\
194 r.creationdate,\n\ 101 else $2 || '/' || r.nodename\n\
195 r.contentlength,\n\ 102 end as ppath,\n";
196 r.etag,\n\ 103
197 p.prefix,\n\ 104 static const char *sql_propfind_ppath_depth_infinity = "\
198 p.xmlns,\n\ 105 case when r.resource_id = $1 then $2\n\
199 p.pname,\n\ 106 else $2 || r.ppath\n\
200 p.lang,\n\ 107 end as ppath,\n";
201 p.nsdeflist,\n\ 108
202 p.pvalue\n\ 109 static const char *sql_propfind_cols = "\
203 from resolvepath r\n\ 110 r.resource_id,\n\
204 left join Property p on r.resource_id = p.resource_id\n\ 111 r.parent_id,\n\
205 order by replace(ppath, '/', chr(1)), resource_id;"; 112 r.nodename,\n\
206 113 r.iscollection,\n\
207 // recursive propfind for specific properties 114 r.lastmodified,\n\
208 // params: $1: resource_id 115 r.creationdate,\n\
209 // $2: array of property xmlns 116 r.contentlength,\n\
210 // $3: array of property names 117 r.etag,\n\
211 static const char *sql_propfind_recursive = "\ 118 p.prefix,\n\
212 with recursive resolvepath as (\n\ 119 p.xmlns,\n\
213 select\n\ 120 p.pname,\n\
214 '' as ppath,\n\ 121 p.lang,\n\
215 *\n\ 122 p.nsdeflist,\n\
216 from Resource\n\ 123 p.pvalue\n";
217 where resource_id = $1 \n\ 124
218 union\n\ 125 static const char *sql_propfind_from_table = "\
219 select\n\ 126 from Resource r\n";
220 p.ppath || '/' || r.nodename,\n\ 127
221 r.*\n\ 128 static const char *sql_propfind_from_cte = "\
222 from Resource r\n\ 129 from resolvepath r\n";
223 inner join resolvepath p on r.parent_id = p.resource_id\n\ 130
224 )\n\ 131 static const char *sql_propfind_propjoin_allprop = "\
225 select\n\ 132 left join Property p on r.resource_id = p.resource_id\n";
226 case when r.resource_id = $1 then $2\n\ 133
227 else $2 || r.ppath\n\ 134 static const char *sql_propfind_propjoin_plist = "\
228 end as ppath,\n\
229 r.resource_id,\n\
230 r.parent_id,\n\
231 r.nodename,\n\
232 r.iscollection,\n\
233 r.lastmodified,\n\
234 r.creationdate,\n\
235 r.contentlength,\n\
236 r.etag,\n\
237 p.prefix,\n\
238 p.xmlns,\n\
239 p.pname,\n\
240 p.lang,\n\
241 p.nsdeflist,\n\
242 p.pvalue\n\
243 from resolvepath r\n\
244 left join (\n\ 135 left join (\n\
245 select p.* from Property p\ 136 select p.* from Property p\
246 inner join (select unnest($3::text[]) as xmlns, unnest($4::text[]) as pname) n\n\ 137 inner join (select unnest($3::text[]) as xmlns, unnest($4::text[]) as pname) n\n\
247 on p.xmlns = n.xmlns and p.pname = n.pname\n\ 138 on p.xmlns = n.xmlns and p.pname = n.pname\n\
248 ) p on r.resource_id = p.resource_id\n\ 139 ) p on r.resource_id = p.resource_id\n";
249 order by replace(ppath, '/', chr(1)), resource_id;"; 140
141 static const char *sql_propfind_where_depth0 = "\
142 where r.resource_id = $1\n";
143
144 static const char *sql_propfind_where_depth1 = "\
145 where r.resource_id = $1 or r.parent_id = $1\n";
146
147 static const char *sql_propfind_order_depth1 = "\
148 order by case when r.resource_id = $1 then 0 else 1 end, nodename, resource_id";
149
150 static const char *sql_propfind_order_depth_infinity = "\
151 order by replace(ppath, '/', chr(1)), resource_id";
152
153 /*
154 * SQL Queries
155 */
156
250 157
251 // proppatch: set property 158 // proppatch: set property
252 // params: $1: resource_id 159 // params: $1: resource_id
253 // $2: xmlns prefix 160 // $2: xmlns prefix
254 // $3: xmlns href 161 // $3: xmlns href
368 int r2 = ucx_buffer_write("}\0", 2, 1, pname) == 0; 275 int r2 = ucx_buffer_write("}\0", 2, 1, pname) == 0;
369 return r1+r2 != 0; 276 return r1+r2 != 0;
370 } 277 }
371 278
372 279
280 static int pg_create_propfind_query(WebdavPropfindRequest *rq, WSBool iscollection, UcxBuffer *sql) {
281 PgWebdavBackend *pgdav = rq->dav->instance;
282 int depth = !iscollection ? 0 : rq->depth;
283
284 /*
285 * PROPFIND queries are build from components:
286 *
287 * cte cte_recursive or empty
288 * select
289 * ppath ppath column
290 * cols list of columns
291 * ext_cols* list of extension columns
292 * from from [table] / from cte
293 * prop_join join with property table, allprop or plist
294 * ext_join* extension table join
295 * where different where clauses for depth0 and depth1
296 * order depth0 doesn't need order
297 */
298
299 // CTE
300 if(depth == -1) {
301 ucx_buffer_puts(sql, sql_propfind_cte_recursive);
302 }
303
304 // select
305 ucx_buffer_puts(sql, sql_propfind_select);
306
307 // ppath
308 switch(depth) {
309 case 0: ucx_buffer_puts(sql, sql_propfind_ppath_depth0); break;
310 case 1: ucx_buffer_puts(sql, sql_propfind_ppath_depth1); break;
311 case -1: ucx_buffer_puts(sql, sql_propfind_ppath_depth_infinity); break;
312 }
313
314 // cols
315 ucx_buffer_puts(sql, sql_propfind_cols);
316
317 // from
318 ucx_buffer_puts(sql, depth == -1 ? sql_propfind_from_cte : sql_propfind_from_table);
319
320 // prop join
321 ucx_buffer_puts(sql, rq->allprop ? sql_propfind_propjoin_allprop : sql_propfind_propjoin_plist);
322
323 // where
324 if(depth == 0) {
325 ucx_buffer_puts(sql, sql_propfind_where_depth0);
326 } else if(depth == 1) {
327 ucx_buffer_puts(sql, sql_propfind_where_depth1);
328 }
329
330 // order
331 if(depth == 1) {
332 ucx_buffer_puts(sql, sql_propfind_order_depth1);
333 } else if(depth == -1) {
334 ucx_buffer_puts(sql, sql_propfind_order_depth_infinity);
335 }
336
337 // end
338 ucx_buffer_puts(sql, ";\0");
339
340 //printf("\n\n%s\n\n", sql->space);
341 //fflush(stdout);
342
343 return 0;
344 }
345
373 int pg_dav_propfind_init( 346 int pg_dav_propfind_init(
374 WebdavPropfindRequest *rq, 347 WebdavPropfindRequest *rq,
375 const char *path, 348 const char *path,
376 const char *href, 349 const char *href,
377 WebdavPList **outplist) 350 WebdavPList **outplist)
402 protocol_status(rq->sn, rq->rq, PROTOCOL_NOT_FOUND, NULL); 375 protocol_status(rq->sn, rq->rq, PROTOCOL_NOT_FOUND, NULL);
403 } 376 }
404 return 1; 377 return 1;
405 } 378 }
406 379
407 // choose sql query 380 // create sql query
408 const char *query = NULL; 381 const char *query = NULL;
409 if(!iscollection || rq->depth == 0) { 382 UcxBuffer *sql = ucx_buffer_new(NULL, 2048, UCX_BUFFER_AUTOEXTEND);
410 query = rq->allprop ? sql_propfind_allprop_depth0 : sql_propfind_depth0; 383 if(pg_create_propfind_query(rq, iscollection, sql)) {
411 } else if(rq->depth == 1) {
412 query = rq->allprop ? sql_propfind_allprop_depth1 : sql_propfind_depth1;
413 } else if(rq->depth == -1) {
414 query = rq->allprop ? sql_propfind_allprop_recursive : sql_propfind_recursive;
415 } else {
416 log_ereport(LOG_FAILURE, "%s", "pg_dav_propfind_init: invalid depth");
417 return 1; 384 return 1;
418 } 385 }
386 query = sql->space;
419 387
420 // get all resources and properties 388 // get all resources and properties
421 char resource_id_str[32]; 389 char resource_id_str[32];
422 snprintf(resource_id_str, 32, "%" PRId64, resource_id); 390 snprintf(resource_id_str, 32, "%" PRId64, resource_id);
423 391

Mercurial Repository: webserver

mercurial