Mercurial > hg > webserver / changeset

--- a/src/server/plugins/postgresql/webdav.c	Sun Aug 14 11:40:54 2022 +0200
+++ b/src/server/plugins/postgresql/webdav.c	Sun Aug 14 12:43:14 2022 +0200
@@ -55,120 +55,27 @@
 };
 
 
+
 /*
- * SQL Queries
+ * SQL query components
  */
 
-// propfind with depth = 0
-// params: $1: resource_id
-static const char *sql_propfind_allprop_depth0 = "\
-select\n\
-    $2::text as ppath,\n\
-    r.resource_id,\n\
-    r.parent_id,\n\
-    r.nodename,\n\
-    r.iscollection,\n\
-    r.lastmodified,\n\
-    r.creationdate,\n\
-    r.contentlength,\n\
-    r.etag,\n\
-    p.prefix,\n\
-    p.xmlns,\n\
-    p.pname,\n\
-    p.lang,\n\
-    p.nsdeflist,\n\
-    p.pvalue\n\
-from Resource r\n\
-left join Property p on r.resource_id = p.resource_id\n\
-where r.resource_id = $1;";
-
-// propfind with depth = 0 for specific properties
-// params: $1: resource_id
-static const char *sql_propfind_depth0 = "\
-select\n\
-    $2::text as ppath,\n\
-    r.resource_id,\n\
-    r.parent_id,\n\
-    r.nodename,\n\
-    r.iscollection,\n\
-    r.lastmodified,\n\
-    r.creationdate,\n\
-    r.contentlength,\n\
-    r.etag,\n\
-    p.prefix,\n\
-    p.xmlns,\n\
-    p.pname,\n\
-    p.lang,\n\
-    p.nsdeflist,\n\
-    p.pvalue\n\
-from Resource r\n\
-left join (\n\
-    select p.* from Property p\
-    inner join (select unnest($3::text[]) as xmlns, unnest($4::text[]) as pname) n\n\
-       on p.xmlns = n.xmlns and p.pname = n.pname\n\
-) p on r.resource_id = p.resource_id\n\
-where r.resource_id = $1;";
+/*
+ * PROPFIND queries are build from components:
+ * 
+ * cte              cte_recursive or empty
+ * select
+ * ppath            ppath column
+ * cols             list of columns
+ *   ext_cols*      list of extension columns
+ * from             from [table] / from cte
+ * prop_join        join with property table, allprop or plist
+ *   ext_join*      extension table join
+ * where            different where clauses for depth0 and depth1
+ * order            depth0 doesn't need order
+ */
 
-// propfind with depth = 1
-// params: $1: resource_id
-static const char *sql_propfind_allprop_depth1 = "\
-select\n\
-    case when r.resource_id = $1 then $2\n\
-         else $2 || '/' || r.nodename\n\
-    end as ppath,\n\
-    r.resource_id,\n\
-    r.parent_id,\n\
-    r.nodename,\n\
-    r.iscollection,\n\
-    r.lastmodified,\n\
-    r.creationdate,\n\
-    r.contentlength,\n\
-    r.etag,\n\
-    p.prefix,\n\
-    p.xmlns,\n\
-    p.pname,\n\
-    p.lang,\n\
-    p.nsdeflist,\n\
-    p.pvalue\n\
-from Resource r\n\
-left join Property p on r.resource_id = p.resource_id\n\
-where r.resource_id = $1 or r.parent_id = $1\n\
-order by case when r.resource_id = $1 then 0 else 1 end, nodename, resource_id;";
-
-
-// propfind with depth = 1 for specific properties
-// params: $1: resource_id
-static const char *sql_propfind_depth1 = "\
-select\n\
-    case when r.resource_id = $1 then $2\n\
-         else $2 || '/' || r.nodename\n\
-    end as ppath,\n\
-    r.resource_id,\n\
-    r.parent_id,\n\
-    r.nodename,\n\
-    r.iscollection,\n\
-    r.lastmodified,\n\
-    r.creationdate,\n\
-    r.contentlength,\n\
-    r.etag,\n\
-    p.prefix,\n\
-    p.xmlns,\n\
-    p.pname,\n\
-    p.lang,\n\
-    p.nsdeflist,\n\
-    p.pvalue\n\
-from Resource r\n\
-left join (\n\
-    select p.* from Property p\
-    inner join (select unnest($3::text[]) as xmlns, unnest($4::text[]) as pname) n\n\
-       on p.xmlns = n.xmlns and p.pname = n.pname\n\
-) p on r.resource_id = p.resource_id\n\
-where r.resource_id = $1 or r.parent_id = $1\n\
-order by case when r.resource_id = $1 then 0 else 1 end, nodename, resource_id;";
-
-// recursive propfind
-// params: $1: resource_id
-static const char *sql_propfind_allprop_recursive = "\
+static const char *sql_propfind_cte_recursive = "\
 with recursive resolvepath as (\n\
     select\n\
         '' as ppath,\n\
@@ -181,72 +88,72 @@
         r.*\n\
     from Resource r\n\
     inner join resolvepath p on r.parent_id = p.resource_id\n\
-    )\n\
-select\n\
-    case when r.resource_id = $1 then $2\n\
-         else $2 || r.ppath\n\
-    end as ppath,\n\
-    r.resource_id,\n\
-    r.parent_id,\n\
-    r.nodename,\n\
-    r.iscollection,\n\
-    r.lastmodified,\n\
-    r.creationdate,\n\
-    r.contentlength,\n\
-    r.etag,\n\
-    p.prefix,\n\
-    p.xmlns,\n\
-    p.pname,\n\
-    p.lang,\n\
-    p.nsdeflist,\n\
-    p.pvalue\n\
-    from resolvepath r\n\
-left join Property p on r.resource_id = p.resource_id\n\
-order by replace(ppath, '/', chr(1)), resource_id;";
+    )\n";
+
+static const char *sql_propfind_select = "\
+select\n";
+
+static const char *sql_propfind_ppath_depth0 = "\
+$2::text as ppath,\n";
+
+static const char *sql_propfind_ppath_depth1 = "\
+case when r.resource_id = $1 then $2\n\
+     else $2 || '/' || r.nodename\n\
+end as ppath,\n";
+
+static const char *sql_propfind_ppath_depth_infinity = "\
+case when r.resource_id = $1 then $2\n\
+     else $2 || r.ppath\n\
+end as ppath,\n";
 
-// recursive propfind for specific properties
-// params: $1: resource_id
-//         $2: array of property xmlns
-//         $3: array of property names
-static const char *sql_propfind_recursive = "\
-with recursive resolvepath as (\n\
-    select\n\
-        '' as ppath,\n\
-        *\n\
-    from Resource\n\
-    where resource_id = $1 \n\
-    union\n\
-    select\n\
-        p.ppath || '/' || r.nodename,\n\
-        r.*\n\
-    from Resource r\n\
-    inner join resolvepath p on r.parent_id = p.resource_id\n\
-    )\n\
-select\n\
-    case when r.resource_id = $1 then $2\n\
-         else $2 || r.ppath\n\
-    end as ppath,\n\
-    r.resource_id,\n\
-    r.parent_id,\n\
-    r.nodename,\n\
-    r.iscollection,\n\
-    r.lastmodified,\n\
-    r.creationdate,\n\
-    r.contentlength,\n\
-    r.etag,\n\
-    p.prefix,\n\
-    p.xmlns,\n\
-    p.pname,\n\
-    p.lang,\n\
-    p.nsdeflist,\n\
-    p.pvalue\n\
-from resolvepath r\n\
+static const char *sql_propfind_cols = "\
+r.resource_id,\n\
+r.parent_id,\n\
+r.nodename,\n\
+r.iscollection,\n\
+r.lastmodified,\n\
+r.creationdate,\n\
+r.contentlength,\n\
+r.etag,\n\
+p.prefix,\n\
+p.xmlns,\n\
+p.pname,\n\
+p.lang,\n\
+p.nsdeflist,\n\
+p.pvalue\n";
+
+static const char *sql_propfind_from_table = "\
+from Resource r\n";
+
+static const char *sql_propfind_from_cte = "\
+from resolvepath r\n";
+
+static const char *sql_propfind_propjoin_allprop = "\
+left join Property p on r.resource_id = p.resource_id\n";
+
+static const char *sql_propfind_propjoin_plist = "\
 left join (\n\
     select p.* from Property p\
     inner join (select unnest($3::text[]) as xmlns, unnest($4::text[]) as pname) n\n\
        on p.xmlns = n.xmlns and p.pname = n.pname\n\
-) p on r.resource_id = p.resource_id\n\
-order by replace(ppath, '/', chr(1)), resource_id;";
+) p on r.resource_id = p.resource_id\n";
+
+static const char *sql_propfind_where_depth0 = "\
+where r.resource_id = $1\n";
+
+static const char *sql_propfind_where_depth1 = "\
+where r.resource_id = $1 or r.parent_id = $1\n";
+
+static const char *sql_propfind_order_depth1 = "\
+order by case when r.resource_id = $1 then 0 else 1 end, nodename, resource_id";
+
+static const char *sql_propfind_order_depth_infinity = "\
+order by replace(ppath, '/', chr(1)), resource_id";
+
+/*
+ * SQL Queries
+ */
+
 
 // proppatch: set property
 // params: $1: resource_id
@@ -370,6 +277,72 @@
 }
 
 
+static int pg_create_propfind_query(WebdavPropfindRequest *rq, WSBool iscollection, UcxBuffer *sql) {
+    PgWebdavBackend *pgdav = rq->dav->instance;
+    int depth = !iscollection ? 0 : rq->depth;
+    
+    /*
+     * PROPFIND queries are build from components:
+     * 
+     * cte              cte_recursive or empty
+     * select
+     * ppath            ppath column
+     * cols             list of columns
+     *   ext_cols*      list of extension columns
+     * from             from [table] / from cte
+     * prop_join        join with property table, allprop or plist
+     *   ext_join*      extension table join
+     * where            different where clauses for depth0 and depth1
+     * order            depth0 doesn't need order
+     */
+    
+    // CTE
+    if(depth == -1) {
+        ucx_buffer_puts(sql, sql_propfind_cte_recursive);
+    }
+    
+    // select
+    ucx_buffer_puts(sql, sql_propfind_select);
+    
+    // ppath
+    switch(depth) {
+        case 0: ucx_buffer_puts(sql, sql_propfind_ppath_depth0); break;
+        case 1: ucx_buffer_puts(sql, sql_propfind_ppath_depth1); break;
+        case -1: ucx_buffer_puts(sql, sql_propfind_ppath_depth_infinity); break;
+    }
+    
+    // cols
+    ucx_buffer_puts(sql, sql_propfind_cols);
+    
+    // from
+    ucx_buffer_puts(sql, depth == -1 ? sql_propfind_from_cte : sql_propfind_from_table);
+    
+    // prop join
+    ucx_buffer_puts(sql, rq->allprop ? sql_propfind_propjoin_allprop : sql_propfind_propjoin_plist);
+    
+    // where
+    if(depth == 0) {
+        ucx_buffer_puts(sql, sql_propfind_where_depth0);
+    } else if(depth == 1) {
+        ucx_buffer_puts(sql, sql_propfind_where_depth1);
+    }
+    
+    // order
+    if(depth == 1) {
+        ucx_buffer_puts(sql, sql_propfind_order_depth1);
+    } else if(depth == -1) {
+        ucx_buffer_puts(sql, sql_propfind_order_depth_infinity);
+    }
+    
+    // end
+    ucx_buffer_puts(sql, ";\0");
+    
+    //printf("\n\n%s\n\n", sql->space);
+    //fflush(stdout);
+    
+    return 0;
+}
+
 int pg_dav_propfind_init(
         WebdavPropfindRequest *rq,
         const char *path,
@@ -404,18 +377,13 @@
         return 1;
     }
     
-    // choose sql query
+    // create sql query
     const char *query = NULL;
-    if(!iscollection || rq->depth == 0) {
-        query = rq->allprop ? sql_propfind_allprop_depth0 : sql_propfind_depth0;
-    } else if(rq->depth == 1) {
-        query = rq->allprop ? sql_propfind_allprop_depth1 : sql_propfind_depth1;
-    } else if(rq->depth == -1) {
-        query = rq->allprop ? sql_propfind_allprop_recursive : sql_propfind_recursive;
-    } else {
-        log_ereport(LOG_FAILURE, "%s", "pg_dav_propfind_init: invalid depth");
+    UcxBuffer *sql = ucx_buffer_new(NULL, 2048, UCX_BUFFER_AUTOEXTEND);
+    if(pg_create_propfind_query(rq, iscollection, sql)) {
         return 1;
     }
+    query = sql->space;
     
     // get all resources and properties
     char resource_id_str[32];