src/server/plugins/postgresql/webdav.c

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 *
 * Copyright 2022 Olaf Wintermann. All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 *   1. Redistributions of source code must retain the above copyright
 *      notice, this list of conditions and the following disclaimer.
 *
 *   2. Redistributions in binary form must reproduce the above copyright
 *      notice, this list of conditions and the following disclaimer in the
 *      documentation and/or other materials provided with the distribution.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */

#include "webdav.h"
#include "vfs.h"
#include "config.h"

#include "../../util/util.h"
#include "../../util/pblock.h"

#include "../../daemon/http.h" // etag

#include <ucx/buffer.h>
#include <ucx/utils.h>
#include <libxml/tree.h>


static WebdavBackend pg_webdav_backend = {
    pg_dav_propfind_init,
    pg_dav_propfind_do,
    pg_dav_propfind_finish,
    pg_dav_proppatch_do,
    pg_dav_proppatch_finish,
    NULL, // opt_mkcol
    NULL, // opt_mkcol_finish
    NULL, // opt_delete
    NULL, // opt_delete_finish
    0,
    NULL,
    NULL
};


/*
 * SQL query components
 */

/*
 * 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
 */

static const char *sql_propfind_cte_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";

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";

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";

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
//         $2: xmlns prefix
//         $3: xmlns href
//         $4: property name
//         $5: lang attribute value
//         $6: namespace list string
//         $7: property value
static const char *sql_proppatch_set = "\
insert into Property(resource_id, prefix, xmlns, pname, lang, nsdeflist, pvalue)\n\
values($1, $2, $3, $4, $5, $6, $7)\n\
on conflict (resource_id, xmlns, pname) do\n\
update set prefix=$2, lang=$5, nsdeflist=$6, pvalue=$7;";

// proppatch: remove property
// params: $1: resource_id
//         $2: xmlns href
//         $3: property name
static const char *sql_proppatch_remove = "\
delete from Property where resource_id = $1 and xmlns = $2 and pname = $3";


void* pg_webdav_init(ServerConfiguration *cfg, pool_handle_t *pool, WSConfigNode *config) {
    return pg_init_repo(cfg, pool, config);
}

WebdavBackend* pg_webdav_create(Session *sn, Request *rq, pblock *pb, void *initData) {
    PgRepository *repo = initData;
    
    char *resource_pool;
    if(repo) {
        resource_pool = repo->resourcepool.ptr;
    } else {
        // resourcepool is required
        resource_pool = pblock_findval("resourcepool", pb);
        if(!resource_pool) {
            log_ereport(LOG_MISCONFIG, "pg_webdav_create: missing resourcepool parameter");
            return NULL;
        }
    }
    
    // get the resource first (should only fail in case of misconfig)
    ResourceData *resdata = resourcepool_lookup(sn, rq, resource_pool, 0);
    if(!resdata) {
        log_ereport(LOG_MISCONFIG, "postgresql webdav: resource pool %s not found", resource_pool);
        return NULL;
    }
    
    return pg_webdav_create_from_resdata(sn, rq, repo, resdata);
}

WebdavBackend* pg_webdav_create_from_resdata(Session *sn, Request *rq, PgRepository *repo, ResourceData *resdata) {
    WebdavBackend *webdav = pool_malloc(sn->pool, sizeof(WebdavBackend));
    if(!webdav) {
        return NULL;
    }
    *webdav = pg_webdav_backend;
    
    PgWebdavBackend *instance = pool_malloc(sn->pool, sizeof(PgWebdavBackend));
    if(!instance) {
        pool_free(sn->pool, webdav);
        return NULL;
    }
    webdav->instance = instance;
    
    instance->pg_resource = resdata;
    instance->connection = resdata->data;
    
    instance->repository = repo;
    snprintf(instance->root_resource_id_str, 32, "%" PRId64, repo->root_resource_id);
    
    return webdav;
}

WebdavBackend* pg_webdav_prop_create(Session *sn, Request *rq, pblock *pb) {
    return NULL;
}

/*
 * adds str to the buffer
 * some characters will be escaped: \,{}
 */
static void buf_addstr_escaped(UcxBuffer *buf, const char *str) {
    size_t len = strlen(str);
    for(size_t i=0;i<len;i++) {
        char c = str[i];
        if(c == '{' || c == '}' || c == ',' || c == '\\') {
            ucx_buffer_putc(buf, '\\');
        }
        ucx_buffer_putc(buf, c);
    }
}

/*
 * convert a property list to two pg array parameter strings
 * array format: {elm1,elm2,elm3}
 * xmlns: buffer for the xmlns array
 * pname: buffer for the property name array
 * 
 * returns 0 on success, 1 otherwise
 */
int pg_create_property_param_arrays(WebdavPList *plist, UcxBuffer *xmlns, UcxBuffer *pname) {
    ucx_buffer_putc(xmlns, '{');
    ucx_buffer_putc(pname, '{');
    while(plist) {
        WebdavProperty *property = plist->property;
        if(property && property->namespace && property->namespace->href && property->name) {
            buf_addstr_escaped(xmlns, (const char*)property->namespace->href);
            buf_addstr_escaped(pname, (const char*)property->name);
            if(plist->next) {
                ucx_buffer_putc(xmlns, ',');
                ucx_buffer_putc(pname, ',');
            }
        }
        plist = plist->next;
    }
    int r1 = ucx_buffer_write("}\0", 2, 1, xmlns) == 0;
    int r2 = ucx_buffer_write("}\0", 2, 1, pname) == 0;
    return r1+r2 != 0;
}


static int propfind_ext_cmp(const void *f1, const void *f2) {
    const PgPropfindExtCol *e1 = f1;
    const PgPropfindExtCol *e2 = f2;
    
    if(e1->ext->tableindex != e2->ext->tableindex) {
        return e1->ext->tableindex < e2->ext->tableindex ? -1 : 1;
    }
    
    return 0;
}


static int pg_create_propfind_query(
        WebdavPropfindRequest *rq,
        WSBool iscollection,
        PgPropfindExtCol *ext,
        size_t numext,
        UcxBuffer *sql)
{
    PgWebdavBackend *pgdav = rq->dav->instance;
    PgRepository *repo = pgdav->repository;
    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);
    
    // ext_cols
    if(ext) { 
        if(rq->allprop) {
            for(int i=0;i<repo->ntables;i++) {
                ucx_bprintf(sql, ",x%d.*\n", i);
            }
        } else {
            for(int i=0;i<numext;i++) {
                PgPropfindExtCol e = ext[i];
                ucx_bprintf(sql, ",x%d.%s\n", e.ext->tableindex, e.ext->column);
            }
        }
    }
    
    // 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);
    
    // ext_join
    if(ext) {
        if(rq->allprop) {
            for(int i=0;i<repo->ntables;i++) {
                ucx_bprintf(sql, "left join %s x%d on r.resource_id = x%d.resource_id\n", repo->tables[i].table, i, i);
            }
        } else {
            int tab = -1;
            for(int i=0;i<numext;i++) {
                PgPropfindExtCol e = ext[i];
                if(e.ext->tableindex != tab) {
                    tab = e.ext->tableindex;
                    ucx_bprintf(sql, "left join %s x%d on r.resource_id = x%d.resource_id\n", repo->tables[tab].table, tab, tab);
                }
            }
        }
        
    }
    
    // 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");
    
    return 0;
}

int pg_dav_propfind_init(
        WebdavPropfindRequest *rq,
        const char *path,
        const char *href,
        WebdavPList **outplist)
{
    PgWebdavBackend *pgdav = rq->dav->instance;
    
    // first, check if the resource exists
    // if it doesn't exist, we can return immediately
    int64_t parent_id;
    int64_t resource_id;
    const char *resourcename;
    WSBool iscollection;
    int res_errno = 0;
    int err = pg_resolve_path(
            pgdav->connection,
            path,
            pgdav->root_resource_id_str,
            &parent_id,
            &resource_id,
            NULL, // OID
            &resourcename,
            &iscollection,
            NULL, // stat
            NULL, // etag
            &res_errno);
    
    if(err) {
        if(res_errno == ENOENT) {
            protocol_status(rq->sn, rq->rq, PROTOCOL_NOT_FOUND, NULL);
        }
        return 1;
    }
    
    // create a list of requsted extended properties
    PgPropfindExtCol *ext;
    size_t numext;
    if(pgdav->repository->ntables == 0) {
        // no property extensions configured
        ext = NULL;
        numext = 0;
    } else {     
        numext = pgdav->repository->prop_ext->count;
        ext = pool_calloc(rq->sn->pool, numext, sizeof(PgPropfindExtCol));
        
        if(rq->allprop) {
            // the map pgdav->repository->prop_ext contains all property extensions
            // we can just convert the map to an array
            UcxMapIterator i = ucx_map_iterator(pgdav->repository->prop_ext);
            PgPropertyStoreExt *cfg_ext;
            int j = 0;
            UCX_MAP_FOREACH(key, cfg_ext, i) {
                PgPropfindExtCol extcol;
                extcol.ext = cfg_ext;
                extcol.field_num = -1; // get the field_num after the PQexec
                ext[j++] = extcol;
            }
        } else {
            WebdavPListIterator i = webdav_plist_iterator(outplist);
            WebdavPList *cur;
            int j = 0;
            while(webdav_plist_iterator_next(&i, &cur)) {
                WSNamespace *ns = cur->property->namespace;
                if(ns) {
                    UcxKey pkey = webdav_property_key((const char*)ns->href, cur->property->name);
                    PgPropertyStoreExt *cfg_ext = ucx_map_get(pgdav->repository->prop_ext, pkey);
                    free((void*)pkey.data);
                    if(cfg_ext) {
                        PgPropfindExtCol extcol;
                        extcol.ext = cfg_ext;
                        extcol.field_num = -1; // get the field_num after the PQexec
                        ext[j++] = extcol;
                        
                        webdav_plist_iterator_remove_current(&i);
                    }
                }
            }
            numext = j;
        }
        
        qsort(ext, numext, sizeof(PgPropfindExtCol), propfind_ext_cmp);
    }
    
    // create sql query
    const char *query = NULL;
    UcxBuffer *sql = ucx_buffer_new(NULL, 2048, UCX_BUFFER_AUTOEXTEND);
    if(pg_create_propfind_query(rq, iscollection, ext, numext, sql)) {
        return 1;
    }
    query = sql->space;
    
    // get all resources and properties
    char resource_id_str[32];
    snprintf(resource_id_str, 32, "%" PRId64, resource_id);
    
    size_t href_len = strlen(href);
    char *href_param = pool_malloc(rq->sn->pool, href_len + 1);
    memcpy(href_param, href, href_len);
    if(href_param[href_len-1] == '/') {
        href_len--;
    }
    href_param[href_len] = '\0';
    
    // if allprop is false, create array pair for xmlns/property names
    UcxBuffer *xmlns_buf = NULL;
    UcxBuffer *pname_buf = NULL;
    char *xmlns_param = NULL;
    char *pname_param = NULL;
    int nparam = 2;
    if(!rq->allprop) {
        size_t bufsize = rq->propcount < 200 ? 8 + rq->propcount * 32 : 4096;
        xmlns_buf = ucx_buffer_new(NULL, bufsize, UCX_BUFFER_AUTOEXTEND);
        if(!xmlns_buf) {
            return 1;
        }
        pname_buf = ucx_buffer_new(NULL, bufsize, UCX_BUFFER_AUTOEXTEND);
        if(!pname_buf) {
            ucx_buffer_free(xmlns_buf);
            return 1;
        }
        if(pg_create_property_param_arrays(*outplist, xmlns_buf, pname_buf)) {
            ucx_buffer_free(xmlns_buf);
            ucx_buffer_free(pname_buf);
            return 1;
        }
        xmlns_param = xmlns_buf->space;
        pname_param = pname_buf->space;
        nparam = 4;
    }
    
    const char* params[4] = { resource_id_str, href_param, xmlns_param, pname_param };
    PGresult *result = PQexecParams(
            pgdav->connection,
            query,
            nparam,     // number of parameters
            NULL,
            params, // parameter value
            NULL,
            NULL,
            0);    // 0: result in text format
    int nrows = PQntuples(result);
    pool_free(rq->sn->pool, href_param);
    if(xmlns_buf) {
        ucx_buffer_free(xmlns_buf);
        ucx_buffer_free(pname_buf);
    }
    if(nrows < 1) {
        PQclear(result);
        return 1;
    }
    
    PgPropfind *pg = pool_malloc(rq->sn->pool, sizeof(PgPropfind));
    rq->userdata = pg;
    
    pg->path = path;
    pg->resource_id = resource_id;
    pg->vfsproperties = webdav_vfs_properties(outplist, TRUE, rq->allprop, 0);
    pg->result = result;
    pg->nrows = nrows;
    
    pg->ext = ext;
    pg->numext = numext;
    if(ext) {
        // get field_nums for all property extensions
        for(int i=0;i<numext;i++) {
            PgPropfindExtCol *c = &ext[i];
            c->field_num = PQfnumber(result, c->ext->column);
        }
    }
    
    return 0;
}

int pg_dav_propfind_do(
	WebdavPropfindRequest *rq,
	WebdavResponse *response,
	VFS_DIR parent,
	WebdavResource *resource,
	struct stat *s)
{
    PgPropfind *pg = rq->userdata;
    pool_handle_t *pool = rq->sn->pool;
    PGresult *result = pg->result;
    WebdavVFSProperties vfsprops = pg->vfsproperties;
      
    WSBool vfsprops_set = 0; // are live properties added to the response?
    WSBool extprops_set = 0; // are extended properties added to the response?
    int64_t current_resource_id = pg->resource_id;
    for(int r=0;r<pg->nrows;r++) {
        // columns:
        //  0: path
        //  1: resource_id
        //  2: parent_id
        //  3: nodename
        //  4: iscollection
        //  5: lastmodified
        //  6: creationdate
        //  7: contentlength
        //  8: etag
        //  9: property prefix
        // 10: property xmlns
        // 11: property name
        // 12: property lang
        // 13: property nsdeflist
        // 14: property value
        
        char *path = PQgetvalue(result, r, 0);
        char *res_id = PQgetvalue(result, r, 1);
        char *iscollection_str = PQgetvalue(result, r, 4);
        WSBool iscollection = iscollection_str && iscollection_str[0] == 't';
        int64_t resource_id;
        if(!util_strtoint(res_id, &resource_id)) {
            log_ereport(LOG_FAILURE, "pg_dav_propfind_do: cannot convert resource_id '%s' to int", res_id);
            return 1;
        }
        
        if(resource_id != current_resource_id) {
            // create a href string for the new resource
            // if the resource is a collection, it should have a trailing '/'
            size_t pathlen = strlen(path);
            if(pathlen == 0) {
                log_ereport(LOG_FAILURE, "pg_dav_propfind_do: query returned invalid path");
                return 1;
            }
            if(pathlen > PG_MAX_PATH_LEN) {
                log_ereport(LOG_FAILURE, "pg_dav_propfind_do: path too long: resource_id: %s", res_id);
                return 1;
            }
            char *newres_href = pool_malloc(pool, (pathlen*3)+2);
            util_uri_escape(newres_href, path);
            if(iscollection && path[pathlen-1] != '/') {
                size_t newres_href_len = strlen(newres_href);
                newres_href[newres_href_len] = '/';
                newres_href[newres_href_len+1] = '\0';
            }
            
            // new resource
            resource = response->addresource(response, newres_href);
            vfsprops_set = FALSE;
            extprops_set = FALSE;
            current_resource_id = resource_id;
        }
        
        // standard webdav live properties
        if(!vfsprops_set) {
            if(vfsprops.getresourcetype) {
                if(iscollection) {
                    resource->addproperty(resource, webdav_resourcetype_collection(), 200);
                } else {
                    resource->addproperty(resource, webdav_resourcetype_empty(), 200);
                }
            }
            
            char *lastmodified = PQgetvalue(result, r, 5);
            char *contentlength = PQgetvalue(result, r, 7);
            time_t t = pg_convert_timestamp(lastmodified);
            
            if(vfsprops.getlastmodified) {
                struct tm tm;
                gmtime_r(&t, &tm);
                
                char buf[HTTP_DATE_LEN+1];
                strftime(buf, HTTP_DATE_LEN, HTTP_DATE_FMT, &tm);
                webdav_resource_add_dav_stringproperty(resource, pool, "getlastmodified", buf, strlen(buf));
            }
            if(vfsprops.creationdate) {
                char *creationdate = PQgetvalue(result, r, 6);
                webdav_resource_add_dav_stringproperty(resource, pool, "creationdate", creationdate, strlen(creationdate));
            }
            if(vfsprops.getcontentlength && !iscollection) {
                webdav_resource_add_dav_stringproperty(resource, pool, "getcontentlength", contentlength, strlen(contentlength));
            }
            if(vfsprops.getetag) {
                char *etag = PQgetvalue(result, r, 8);
                if(!PQgetisnull(result, r, 8)) {
                    webdav_resource_add_dav_stringproperty(resource, pool, "getetag", etag, strlen(etag));
                } else {
                    int64_t ctlen;
                    if(util_strtoint(contentlength, &ctlen)) {
                        char etag[MAX_ETAG];
                        http_format_etag(rq->sn, rq->rq, etag, MAX_ETAG, ctlen, t);
                        webdav_resource_add_dav_stringproperty(resource, pool, "getetag", etag, strlen(etag));
                    }
                }
            }
            
            vfsprops_set = TRUE;
        }
        
        if(!extprops_set) {
            // extended properties
            if(pg->ext) {
                for(int extc=0;extc<pg->numext;extc++) {
                    PgPropfindExtCol ext = pg->ext[extc];
                    int fieldnum = ext.field_num;
                    
                    if(!PQgetisnull(result, r, fieldnum)) {
                        char *ext_value = PQgetvalue(result, r, fieldnum);
                        int ext_value_len = PQgetlength(result, r, fieldnum);
                        char ext_xmlns_prefix[32];
                        snprintf(ext_xmlns_prefix, 32, "x%d", ext.ext->tableindex);

                        WebdavProperty *property = pool_malloc(pool, sizeof(WebdavProperty));
                        property->lang = NULL;
                        property->name = pool_strdup(pool, ext.ext->name);

                        xmlNs *namespace = pool_malloc(pool, sizeof(xmlNs));
                        memset(namespace, 0, sizeof(struct _xmlNs));
                        namespace->href = (xmlChar*)pool_strdup(pool, ext.ext->ns);
                        namespace->prefix = (xmlChar*)pool_strdup(pool, ext_xmlns_prefix);
                        property->namespace = namespace;

                        char *content = pool_malloc(pool, ext_value_len+1);
                        memcpy(content, ext_value, ext_value_len);
                        content[ext_value_len] = '\0';
                        
                        WebdavNSList *nslist = pool_malloc(pool, sizeof(WebdavNSList));
                        nslist->namespace = namespace;
                        nslist->prev = NULL;
                        nslist->next = NULL;
                        
                        property->vtype = WS_VALUE_XML_DATA;               
                        property->value.data.data = content;
                        property->value.data.length = ext_value_len;
                        property->value.data.namespaces = nslist;

                        resource->addproperty(resource, property, 200);
                    }
                }
            }
            
            extprops_set = TRUE;
        }
        
        // dead properties
        if(!PQgetisnull(result, r, 9)) {
            char *prefix = PQgetvalue(result, r, 9);
            char *xmlns = PQgetvalue(result, r, 10);
            char *pname = PQgetvalue(result, r, 11);
            char *lang = PQgetvalue(result, r, 12);
            char *nsdef = PQgetvalue(result, r, 13);
            char *pvalue = PQgetvalue(result, r, 14);
            
            int pvalue_len = PQgetlength(result, r, 14);
            WSBool lang_isnull = PQgetisnull(result, r, 12);
            WSBool nsdef_isnull = PQgetisnull(result, r, 13);
            WSBool pvalue_isnull = PQgetisnull(result, r, 14);
            
            WebdavProperty *property = pool_malloc(pool, sizeof(WebdavProperty));
            property->lang = NULL;
            property->name = pool_strdup(pool, pname);
            
            xmlNs *namespace = pool_malloc(pool, sizeof(xmlNs));
            memset(namespace, 0, sizeof(struct _xmlNs));
            namespace->href = (xmlChar*)pool_strdup(pool, xmlns);
            namespace->prefix = (xmlChar*)pool_strdup(pool, prefix);
            property->namespace = namespace;
            
            if(!lang_isnull) {
                property->lang = pool_strdup(pool, lang);
            }
            
            if(!pvalue_isnull) {
                char *content = pool_malloc(pool, pvalue_len+1);
                memcpy(content, pvalue, pvalue_len);
                content[pvalue_len] = '\0';
                
                if(nsdef_isnull) {
                    property->vtype = WS_VALUE_TEXT;
                    property->value.text.str = content;
                    property->value.text.length = pvalue_len;
                } else {
                    WebdavNSList *nslist = wsxml_string2nslist(pool, nsdef);
                    property->vtype = WS_VALUE_XML_DATA;
                    property->value.data.data = content;
                    property->value.data.length = pvalue_len;
                    property->value.data.namespaces = nslist;
                    
                }
            }
            
            resource->addproperty(resource, property, 200);
        }
    }
        
    return 0;
}

int pg_dav_propfind_finish(WebdavPropfindRequest *rq) {
    PgPropfind *pg = rq->userdata;
    pool_handle_t *pool = rq->sn->pool;
    PGresult *result = pg->result;
    
    PQclear(result);
    
    return 0;
}

enum PgDavProp {
    PG_DAV_PROPPATCH_NOT_ALLOWED = 0,
    PG_DAV_CREATIONDATE,
    PG_DAV_DISPLAYNAME,
    PG_DAV_DEADPROP
};
/*
 * checks if the property can be manipulated
 */
static enum PgDavProp proppatch_check_dav_prop(const char *name) {
    if(!strcmp(name, "getlastmodified")) {
        return PG_DAV_PROPPATCH_NOT_ALLOWED;
    } else if(!strcmp(name, "getcontentlength")) {
        return PG_DAV_PROPPATCH_NOT_ALLOWED;
    } else if(!strcmp(name, "resourcetype")) {
        return PG_DAV_PROPPATCH_NOT_ALLOWED;
    } else if(!strcmp(name, "getetag")) {
        return PG_DAV_PROPPATCH_NOT_ALLOWED;
    } else if(!strcmp(name, "creationdate")) {
        return PG_DAV_CREATIONDATE;
    } else if(!strcmp(name, "displayname")) {
        return PG_DAV_DISPLAYNAME;
    }
    return PG_DAV_DEADPROP;
}

typedef struct {
    WebdavProperty *creationdate;
    WebdavProperty *displayname;
    int error;
} PgProppatchOpResult;

typedef int(*pg_proppatch_func)(PgWebdavBackend*, WebdavProppatchRequest*, WebdavResource*, WebdavProperty*, void*);

/*
 * This function iterates the property list 'plist',
 * analyses if any DAV: property is in the list
 * and calls opfunc for the each property
 * 
 * If the property list contains the properties creationdate or displayname,
 * the pointers to these properties will be stored in the result structure
 */
static PgProppatchOpResult pg_proppatch_op(
        PgWebdavBackend *pgdav,
        WebdavProppatchRequest *request,
        WebdavResource *response,
        WebdavPList **plist,
        enum PgDavProp forbidden_extra,
        pg_proppatch_func opfunc,
        void *op_userdata)
{
    PgProppatchOpResult result;
    result.creationdate = NULL;
    result.displayname = NULL;
    result.error = 0;
    
    WebdavPListIterator i = webdav_plist_iterator(plist);
    WebdavPList *cur;
    while(webdav_plist_iterator_next(&i, &cur)) {
        WebdavProperty *property = cur->property;
        WSNamespace *ns = property->namespace;
        if(!ns) {
            continue; // maybe we should abort
        }
        
        // check if the property is a DAV: property that requires special
        // handling
        // get* properties can't be manipulated
        // some properties can't be removed
        if(!strcmp((const char*)ns->href, "DAV:")) {
            const char *name = property->name;
            enum PgDavProp davprop = proppatch_check_dav_prop(name);
            if(davprop != PG_DAV_DEADPROP) {
                if(davprop == PG_DAV_PROPPATCH_NOT_ALLOWED || davprop == forbidden_extra) {
                    response->addproperty(response, property, 409);
                } else if(davprop == PG_DAV_CREATIONDATE) {
                    result.creationdate = property;
                } else if(davprop == PG_DAV_DISPLAYNAME) {
                    result.displayname = property;
                }
                webdav_plist_iterator_remove_current(&i);
                continue;
            }
        }
        
        // call op func (set, remove specific code)
        if(opfunc(pgdav, request, response, property, op_userdata)) {
            result.error = 1;
            break;
        }
        
        webdav_plist_iterator_remove_current(&i);
    }
    
    return result;
}

#define PG_PROPPATCH_EXT_SET 0
#define PG_PROPPATCH_EXT_REMOVE 1

static int pg_proppatch_add_ext_prop(
        pool_handle_t *pool,
        PgWebdavBackend *pgdav,
        PgProppatch *proppatch,
        WebdavProperty *property,
        int proppatch_op)
{
    UcxKey pkey = webdav_property_key((const char*)property->namespace->href, property->name);
    PgPropertyStoreExt *ext = ucx_map_get(pgdav->repository->prop_ext, pkey);
    free((void*)pkey.data);
    if(ext) {
        PgProppatchExtProp *ext_prop = pool_malloc(pool, sizeof(PgProppatchExtProp));
        if(!ext_prop) {
            return 1;
        }
        ext_prop->column = ext;
        ext_prop->property = property;

        UcxAllocator a = util_pool_allocator(pool);
        proppatch->ext[ext->tableindex].isused = TRUE;
        
        UcxList **list = proppatch_op == PG_PROPPATCH_EXT_REMOVE
                            ? &proppatch->ext[ext->tableindex].remove
                            : &proppatch->ext[ext->tableindex].set;
        *list = ucx_list_append_a(&a, *list, ext_prop);

        proppatch->extensions_used = TRUE;
    }
    
    return 0;
}

static int pg_dav_set_property(
        PgWebdavBackend *pgdav,
        WebdavProppatchRequest *request,
        WebdavResource *response,
        WebdavProperty *property,
        void *userdata)
{
    pool_handle_t *pool = request->sn->pool;
    PgProppatch *proppatch = request->userdata;
    WSNamespace *ns = property->namespace;
    
    // check if the property belongs to an extension
    if(proppatch->ext && ns) {
        return pg_proppatch_add_ext_prop(pool, pgdav, proppatch, property, PG_PROPPATCH_EXT_SET);
    }
    
    char *resource_id_str = userdata;
    int ret = 0;
    
    // convert the property value to WSXmlData
    // property->vtype == WS_VALUE_XML_NODE should always be true
    WSXmlData *property_value = property->vtype == WS_VALUE_XML_NODE ? wsxml_node2data(pool, property->value.node) : NULL;
    char *value_str = NULL;
    char *nsdef_str = NULL;
    if(property_value) {
        value_str = property_value->data;
        if(property_value->namespaces) {
            nsdef_str = wsxml_nslist2string(pool, property_value->namespaces);
            if(!nsdef_str) {
                return 1; // OOM
            }
        }
    }

    // exec sql
    const char* params[7] = { resource_id_str, (const char*)ns->prefix, (const char*)ns->href, property->name, NULL, nsdef_str, value_str}; 
    PGresult *result = PQexecParams(
        pgdav->connection,
        sql_proppatch_set,
        7,      // number of parameters
        NULL,
        params, // parameter value
        NULL,
        NULL,
        0);     // 0: result in text format

    if(PQresultStatus(result) != PGRES_COMMAND_OK) {
        response->addproperty(response, property, 500);
        //printf(PQerrorMessage(pgdav->connection));
        //fflush(stdout);
        ret = 1;
    } else {
        response->addproperty(response, property, 200);
    }
    PQclear(result);
    if(value_str) pool_free(pool, value_str);
    
    return ret;
}


static int pg_dav_remove_property(
        PgWebdavBackend *pgdav,
        WebdavProppatchRequest *request,
        WebdavResource *response,
        WebdavProperty *property,
        void *userdata)
{
    pool_handle_t *pool = request->sn->pool;
    PgProppatch *proppatch = request->userdata;
    WSNamespace *ns = property->namespace;
    
    // check if the property belongs to an extension
    if(proppatch->ext && ns) {
        return pg_proppatch_add_ext_prop(pool, pgdav, proppatch, property, PG_PROPPATCH_EXT_REMOVE);
    }
    
    char *resource_id_str = userdata;
    int ret = 0;

    // exec sql
    const char* params[3] = { resource_id_str, (const char*)ns->href, property->name }; 
    PGresult *result = PQexecParams(
        pgdav->connection,
        sql_proppatch_remove,
        3,      // number of parameters
        NULL,
        params, // parameter value
        NULL,
        NULL,
        0);     // 0: result in text format

    if(PQresultStatus(result) != PGRES_COMMAND_OK) {
        response->addproperty(response, property, 500);
        //printf(PQerrorMessage(pgdav->connection));
        //fflush(stdout);
        ret = 1;
    }
    PQclear(result);
    
    return ret;
}


/*
 * Creates an SQL query for inserting a new row to an extension table
 * A parameter list for PQexecParams will also be generated, however
 * params[0] will be empty (resource_id str)
 * 
 * Query: insert into <table> (resource_id, col1, ...) values ($1, $2 ...);
 */
static UcxBuffer* ext_row_create_insert_query(WebdavProppatchRequest *request, PgProppatchExt *ext, PgExtTable *table, char *** params, size_t *nparams) {
    pool_handle_t *pool = request->sn->pool;
    
    UcxBuffer *sql = ucx_buffer_new(NULL, 1024, UCX_BUFFER_AUTOEXTEND);
    if(!sql) {
        return NULL;
    }
    
    size_t pg_nparams = ucx_list_size(ext->set) + 1;
    char** pg_params = pool_calloc(pool, pg_nparams, sizeof(char*));
    if(!pg_params) {
        ucx_buffer_free(sql);
        return NULL;
    }
    
    ucx_buffer_puts(sql, "insert into ");
    ucx_buffer_puts(sql, table->table);
    ucx_buffer_puts(sql, "(resource_id");
    UCX_FOREACH(elm, ext->set) {
        PgProppatchExtProp *prop = elm->data;
        ucx_bprintf(sql, ",%s", prop->column->name);
    }
    
    ucx_buffer_puts(sql, ") values ($1\n");
    int i = 1;
    UCX_FOREACH(elm, ext->set) {
        PgProppatchExtProp *prop = elm->data;
        WebdavProperty *property = prop->property;
        // convert the property value to WSXmlData
        // property->vtype == WS_VALUE_XML_NODE should always be true
        WSXmlData *property_value = property->vtype == WS_VALUE_XML_NODE ? wsxml_node2data(pool, property->value.node) : NULL;
        char *value_str = NULL;
        //char *nsdef_str = NULL;
        if(property_value) {
            value_str = property_value->data;
            if(property_value->namespaces) {
                // currently only text data is supported
                pool_free(pool, params);
                ucx_buffer_free(sql);
                return NULL;
            }
        }
        
        pg_params[i] = value_str;
        ucx_bprintf(sql, ",$%d", ++i);
    }
    ucx_buffer_puts(sql, ");");
    
    
    //printf("\n\n%.*s\n\n", (int)sql->size, sql->space);
    //fflush(stdout);
    
    *params = pg_params;
    *nparams = pg_nparams;
    
    return sql;
}

/*
 * Creates an SQL query for updating an extension table row
 * A parameter list for PQexecParams will also be generated, however
 * params[0] will be empty (resource_id str)
 * 
 * Query: update <table> set
 *        col1 = $2,
 *        col2 = $3,
 *        ...
 *        where resource_id = $1 ;
 */
static UcxBuffer* ext_row_create_update_query(WebdavProppatchRequest *request, PgProppatchExt *ext, PgExtTable *table, char *** params, size_t *nparams) {
    pool_handle_t *pool = request->sn->pool;
    
    UcxBuffer *sql = ucx_buffer_new(NULL, 1024, UCX_BUFFER_AUTOEXTEND);
    if(!sql) {
        return NULL;
    }
    
    ucx_buffer_puts(sql, "update ");
    ucx_buffer_puts(sql, table->table);
    ucx_buffer_puts(sql, " set\n");
    
    size_t pg_nparams = ucx_list_size(ext->set) + 1;
    char** pg_params = pool_calloc(pool, pg_nparams, sizeof(char*));
    if(!pg_params) {
        ucx_buffer_free(sql);
        return NULL;
    }
    
    int i = 1;
    UCX_FOREACH(elm, ext->set) {
        PgProppatchExtProp *prop = elm->data;
        WebdavProperty *property = prop->property;
        // convert the property value to WSXmlData
        // property->vtype == WS_VALUE_XML_NODE should always be true
        WSXmlData *property_value = property->vtype == WS_VALUE_XML_NODE ? wsxml_node2data(pool, property->value.node) : NULL;
        char *value_str = NULL;
        //char *nsdef_str = NULL;
        if(property_value) {
            value_str = property_value->data;
            if(property_value->namespaces) {
                // currently only text data is supported
                pool_free(pool, params);
                ucx_buffer_free(sql);
                return NULL;
            }
        }
        
        pg_params[i] = value_str;
        ucx_bprintf(sql, " %s = $%d,\n", prop->column->name, ++i);
    }
    
    UCX_FOREACH(elm, ext->remove) {
        PgProppatchExtProp *prop = elm->data;
        ucx_bprintf(sql, " %s = NULL,\n", prop->column->name);
    }
    
    // check if any write worked
    if(sql->pos == 0) {
        ucx_buffer_free(sql);
        pool_free(pool, pg_params);
        return NULL;
    }
    
    // last line should end with ',' '\n'
    // replace ',' with space
    if(sql->space[sql->pos-2] == ',') {
        sql->space[sql->pos-2] = ' ';
    }
    
    ucx_bprintf(sql, "where resource_id =  $1 ;\0");
    
    //printf("\n\n%.*s\n\n", (int)sql->size, sql->space);
    //fflush(stdout);
    
    *params = pg_params;
    *nparams = pg_nparams;
    
    return sql;
}

/*
 * Executes an SQL insert for the extension table
 */
int ext_row_insert(WebdavProppatchRequest *request, PgProppatchExt *ext, PgExtTable *table) {
    PgWebdavBackend *pgdav = request->dav->instance;
    PgProppatch *proppatch = request->userdata;
    pool_handle_t *pool = request->sn->pool;
    
    char **params;
    size_t nparam;
    UcxBuffer *sql = ext_row_create_insert_query(request, ext, table, &params, &nparam);
    if(!sql) {
        return 1;
    }
    
    char resource_id_str[32];
    snprintf(resource_id_str, 32, "%" PRId64, proppatch->resource_id);
    params[0] = resource_id_str;
    
    PGresult *result = PQexecParams(
            pgdav->connection,
            sql->space,
            nparam,     // number of parameters
            NULL,
            ( const char *const *)params, // parameter value
            NULL,
            NULL,
            0);    // 0: result in text format
    
    ucx_buffer_free(sql);
    
    int ret = 1;
    if(PQresultStatus(result) == PGRES_COMMAND_OK) {
        // command ok, check if any row was updated
        char *nrows_affected = PQcmdTuples(result);
        if(nrows_affected[0] == '1') {
            ret = 0;
        } else {
            log_ereport(LOG_FAILURE, "pg: extension row insert failed");
        }
    } else {
        log_ereport(LOG_FAILURE, "pg: extension row insert failed: %s", PQresultErrorMessage(result));
    }
    
    
    PQclear(result);
    
    return ret;
}

/*
 * Executes an SQL update for the extension table
 */
int ext_row_update(WebdavProppatchRequest *request, PgProppatchExt *ext, PgExtTable *table) {
    PgWebdavBackend *pgdav = request->dav->instance;
    PgProppatch *proppatch = request->userdata;
    pool_handle_t *pool = request->sn->pool;
    
    char **params;
    size_t nparam;
    UcxBuffer *sql = ext_row_create_update_query(request, ext, table, &params, &nparam);
    if(!sql) {
        return 1;
    }
    
    char resource_id_str[32];
    snprintf(resource_id_str, 32, "%" PRId64, proppatch->resource_id);
    params[0] = resource_id_str;
    
    PGresult *result = PQexecParams(
            pgdav->connection,
            sql->space,
            nparam,     // number of parameters
            NULL,
            ( const char *const *)params, // parameter value
            NULL,
            NULL,
            0);    // 0: result in text format
    
    ucx_buffer_free(sql);
    
    int ret = 1;
    if(PQresultStatus(result) == PGRES_COMMAND_OK) {
        // command ok, check if any row was updated
        char *nrows_affected = PQcmdTuples(result);
        if(nrows_affected[0] == '1') {
            ret = 0;
        } else if(nrows_affected[0] == '0') {
            // no rows affected, that means we have to insert a new row
            // in the extension table for this resource
            
            // TODO: cleanup params
            
            ret = ext_row_insert(request, ext, table);
        }
    } else {
        log_ereport(LOG_FAILURE, "pg: extension row update failed: %s", PQresultErrorMessage(result));
    }
    
    
    PQclear(result);
    
    return ret;
}

static int pg_dav_update_extension_tables(WebdavProppatchRequest *request) {
    PgWebdavBackend *pgdav = request->dav->instance;
    PgProppatch *proppatch = request->userdata;
    
    for(int i=0;i<proppatch->numext;i++) {
        if(proppatch->ext[i].isused) {
            if(ext_row_update(request, &proppatch->ext[i], &pgdav->repository->tables[i])) {
                // extension proppatch failed
                return 1;
            }
        }
    }
    
    return 0;
}

int pg_dav_proppatch_do(
	WebdavProppatchRequest *request,
	WebdavResource *response,
	VFSFile *file,
	WebdavPList **out_set,
	WebdavPList **out_remove)
{
    PgWebdavBackend *pgdav = request->dav->instance;
    pool_handle_t *pool = request->sn->pool;
    char *path = pblock_findkeyval(pb_key_path, request->rq->vars);
    
    PgProppatch proppatch;
    proppatch.extensions_used = FALSE;
    if(pgdav->repository->ntables == 0) {
        proppatch.ext = NULL;
        proppatch.numext = 0;
    } else {
        // some properties are stored in additional tables
        // for each table we create a PgProppatchExt record
        // which stores data about, which tables are used
        // and which properties (columns) should be updated
        //
        // proppatch.ext[i] should contain the data for repository->tables[i]
        proppatch.numext = pgdav->repository->ntables;
        proppatch.ext = pool_calloc(request->sn->pool, proppatch.numext, sizeof(PgProppatchExt));
        if(!proppatch.ext) {
            return 1; // OOM
        }
    }
    request->userdata = &proppatch;
    
    // check if the resource exists, we also need the resource_id
    int64_t parent_id;
    int64_t resource_id;
    const char *resourcename;
    WSBool iscollection;
    int res_errno = 0;
    int err = pg_resolve_path(
            pgdav->connection,
            path,
            pgdav->root_resource_id_str,
            &parent_id,
            &resource_id,
            NULL, // OID
            &resourcename,
            &iscollection,
            NULL, // stat
            NULL, // etag
            &res_errno);
    
    if(err) {
        return 1;
    }
    
    proppatch.resource_id = resource_id;
       
    // because proppatch must be atomic and we have multiple sql
    // queries and other backends that do stuff that could fail
    // we need the possibility to reverse all changes
    // we use a transaction savepoint for this
    PGresult *result = PQexec(pgdav->connection, "savepoint proppatch;");
    ExecStatusType execStatus = PQresultStatus(result);
    PQclear(result);
    if(execStatus != PGRES_COMMAND_OK) {
        return 1;
    }
    
    char resource_id_str[32];
    snprintf(resource_id_str, 32, "%" PRId64, resource_id);
    // store the resource_id in rq->vars, because it could be useful later
    pblock_nvinsert("resource_id", resource_id_str, request->rq->vars);
    
    int ret = 0;
    PgProppatchOpResult set_res = pg_proppatch_op(
            pgdav,
            request,
            response,
            out_set,
            PG_DAV_PROPPATCH_NOT_ALLOWED,
            pg_dav_set_property,
            resource_id_str);
    if(set_res.error) {
        return 1;
    }
    PgProppatchOpResult rm_res = pg_proppatch_op(
            pgdav,
            request,
            response,
            out_remove,
            PG_DAV_CREATIONDATE, // creationdate can't be removed
            pg_dav_remove_property,
            resource_id_str);
    if(rm_res.error) {
        return 1;
    }
    
    // if extensions are in use and pg_proppatch_op found any
    // properties, that should be stored in extension tables
    // we do the update/insert now
    if(proppatch.extensions_used) {
        ret = pg_dav_update_extension_tables(request);
    }
    
       
    return ret;
}

int pg_dav_proppatch_finish(
	WebdavProppatchRequest *request,
	WebdavResource *response,
	VFSFile *file,
	WSBool commit)
{
    PgWebdavBackend *pgdav = request->dav->instance;
    int ret = 0;
    if(!commit) {
        PGresult *result = PQexec(pgdav->connection, "rollback to savepoint proppatch;");
        if(PQresultStatus(result) != PGRES_COMMAND_OK) {
            log_ereport(LOG_FAILURE, "pg_dav_proppatch_finish: rollback failed: %s", PQerrorMessage(pgdav->connection));
            ret = 1;
        }
        PQclear(result);
    }
    return ret;
}