src/server/plugins/postgresql/vfs.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 "vfs.h"

#include <inttypes.h>

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

static VFS pg_vfs_class = {
    pg_vfs_open,
    pg_vfs_stat,
    pg_vfs_fstat,
    pg_vfs_opendir,
    pg_vfs_fdopendir,
    pg_vfs_mkdir,
    pg_vfs_unlink,
    pg_vfs_rmdir
};

static VFS_IO pg_vfs_io_class = {
    pg_vfs_io_read,
    pg_vfs_io_write,
    pg_vfs_io_pread,
    pg_vfs_io_pwrite,
    pg_vfs_io_seek,
    pg_vfs_io_close,
    NULL, // no pg aio implementation yet
    NULL
};

static VFS_DIRIO pg_vfs_dirio_class = {
    pg_vfs_dirio_readdir,
    pg_vfs_dirio_close
};


/*
 * SQL Queries
 */

// Resolves a path into resource_id and parent_id
// params: $1: path string
static const char *sql_resolve_path = 
    "with recursive resolvepath as (\n\
        select\n\
            resource_id,\n\
            parent_id,\n\
            '' as fullpath,\n\
            resoid,\n\
            iscollection,\n\
            lastmodified,\n\
            creationdate,\n\
            contentlength,\n\
            regexp_split_to_array($1, '/') as pathelm,\n\
            1 as pathdepth\n\
        from Resource\n\
        where parent_id is null\n\
        union\n\
        select\n\
            r.resource_id,\n\
            r.parent_id,\n\
            p.fullpath || '/' || r.nodename,\n\
            r.resoid,\n\
            r.iscollection,\n\
            r.lastmodified,\n\
            r.creationdate,\n\
            r.contentlength,\n\
            p.pathelm,\n\
            p.pathdepth + 1\n\
        from Resource r\n\
        inner join resolvepath p on r.parent_id = p.resource_id\n\
        where p.pathelm[p.pathdepth+1] = r.nodename\n\
    )\n\
    select resource_id, parent_id, fullpath, resoid, iscollection, lastmodified, creationdate, contentlength from resolvepath\n\
    where fullpath = $1 ;";

// Same as sql_resolve_path, but it returns the root collection
// params: $1: path string (should be '/')
static const char *sql_get_root = "select resource_id, parent_id, $1 as fullpath, resoid, true as iscollection, lastmodified, creationdate, contentlength from Resource where parent_id is null;";

// Get all children of a specific collection
// params: $1: parent resource_id
static const char *sql_get_children = "select resource_id, nodename, iscollection, lastmodified, creationdate, contentlength from Resource where parent_id = $1;";

// Get resource
// params: $1: resource_id
static const char *sql_get_resource = "select resource_id, nodename, iscollection, lastmodified, creationdate, contentlength from Resource where resource_id = $1;";

// Create resource
// params: $1: parent_id
//         $2: node name
static const char *sql_create_resource =
        "insert into Resource (parent_id, nodename, iscollection, lastmodified, creationdate, contentlength, resoid) values\n\
        ($1, $2, false, now(), now(), 0, lo_creat(-1))\n\
        returning resource_id, resoid, lastmodified, creationdate;";

// Create collection
// params: $1: parent_id
//         $2: node name
static const char *sql_create_collection =
        "insert into Resource (parent_id, nodename, iscollection, lastmodified, creationdate, contentlength) values\n\
        ($1, $2, true, now(), now(), 0)\n\
        returning resource_id, lastmodified, creationdate;";

// Update resource metadata
// params: $1: resource_id
//         $2: contentlength
static const char *sql_update_resource = "update Resource set contentlength = $2, lastmodified = now() where resource_id = $1;";

// Delete a resource
// params: $1: resource_id
static const char *sql_delete_res = "delete from Resource where parent_id is not null and resource_id = $1;";

VFS* pg_vfs_create(Session *sn, Request *rq, pblock *pb) {
    // resourcepool is required
    char *resource_pool = pblock_findval("resourcepool", pb);
    if(!resource_pool) {
        log_ereport(LOG_MISCONFIG, "pg_vfs_create: missing resourcepool parameter");
        return NULL;
    }
    
    // get the resource first (most likely to fail due to misconfig)
    ResourceData *resdata = resourcepool_lookup(sn, rq, resource_pool, 0);
    if(!resdata) {
        log_ereport(LOG_MISCONFIG, "postgresql vfs: resource pool %s not found", resource_pool);
        return NULL;
    }
    // resdata will be freed automatically when the request is finished
    
    return pg_vfs_create_from_resourcedata(sn, rq, resdata);
}

VFS* pg_vfs_create_from_resourcedata(Session *sn, Request *rq, ResourceData *resdata) {
    // Create a new VFS object and a separate instance object
    // VFS contains fptrs that can be copied from pg_vfs_class
    // instance contains request specific data (PGconn)
    VFS *vfs = pool_malloc(sn->pool, sizeof(VFS));
    if(!vfs) {
        return NULL;
    }
    
    PgVFS *vfs_priv = pool_malloc(sn->pool, sizeof(PgVFS));
    if(!vfs_priv) {
        pool_free(sn->pool, vfs);
        return NULL;
    }
    vfs_priv->connection = resdata->data;
    vfs_priv->pg_resource = resdata;
    
    memcpy(vfs, &pg_vfs_class, sizeof(VFS));
    vfs->flags = 0;
    vfs->instance = vfs_priv;
    
    return vfs;
}


int pg_resolve_path(
        PGconn *connection,
        const char *path,
        int64_t *parent_id,
        int64_t *resource_id,
        Oid *oid,
        const char **resource_name,
        WSBool *iscollection,
        struct stat *s,
        int *res_errno)
{
    // basic path validation
    if(!path) return 1;
    size_t pathlen = strlen(path);
    if(pathlen == 0) return 1;
    if(path[0] != '/') {
        return 1;
    }
    
    char *pathf = NULL;
    if(pathlen > 1 && path[pathlen-1] == '/') {
        pathf = malloc(pathlen);
        memcpy(pathf, path, pathlen);
        pathf[pathlen-1] = 0; // remove trailing '/'
        path = pathf;
    }
    
    // get last node of path
    *resource_name = util_resource_name(path);
    
    const char *sql = pathlen == 1 ? sql_get_root : sql_resolve_path;  
    PGresult *result = PQexecParams(
            connection,
            sql,
            1,     // number of parameters
            NULL,
            &path, // parameter value
            NULL,
            NULL,
            0);    // 0: result in text format
      
    if(pathf) {
        free(pathf);
    }
    
    if(!result) return 1;
    
    int ret = 1;
    //int nfields = PQnfields(result);
    int nrows = PQntuples(result);
    if(nrows == 1) {
        char *resource_id_str = PQgetvalue(result, 0, 0);
        char *parent_id_str = PQgetvalue(result, 0, 1);
        char *iscol = PQgetvalue(result, 0, 4);
        char *lastmodified = PQgetvalue(result, 0, 5);
        char *creationdate = PQgetvalue(result, 0, 6);
        char *contentlength = PQgetvalue(result, 0, 7);
        if(resource_id_str && parent_id_str) {
            if(util_strtoint(resource_id_str, resource_id)) {
                ret = 0; // success
            }
            // optionally get parent_id
            util_strtoint(parent_id_str, parent_id);
        }
        
        if(oid) {
            char *resoid = PQgetvalue(result, 0, 3);
            int64_t roid;
            if(resoid && util_strtoint(resoid, &roid)) {
                *oid = roid;
            }
        }
        
        if(iscollection && iscol) {
            *iscollection = iscol[0] == 't' ? TRUE : FALSE;
        }
        
        if(s) {
            pg_set_stat(s, iscol, lastmodified, creationdate, contentlength);
        }
    } else if(res_errno) {
        *res_errno = ENOENT;
    }
    
    PQclear(result);
    
    return ret;
}


void pg_set_stat(
        struct stat *s,
        const char *iscollection,
        const char *lastmodified,
        const char *creationdate,
        const char *contentlength)
{
    memset(s, 0, sizeof(struct stat));
    if(iscollection) {
        WSBool iscol = iscollection[0] == 't' ? TRUE : FALSE;
        if(iscol) {
            s->st_mode |= 0x4000;
        }
    }
    s->st_mtime = pg_convert_timestamp(lastmodified);  
    
    if(contentlength) {
        int64_t len;
        if(util_strtoint(contentlength, &len)) {
            s->st_size = len;
        }
    }
}

static int pg_create_res(
        PgVFS *pg,
        const char *resparentid_str,
        const char *nodename,
        int64_t *new_resource_id,
        Oid *oid,
        const char **resource_name,
        struct stat *s)
{
    const char* params[2] = { resparentid_str, nodename };
    PGresult *result = PQexecParams(
            pg->connection,
            sql_create_resource,
            2,     // number of parameters
            NULL,
            params, // parameter value
            NULL,
            NULL,
            0);    // 0: result in text format
    
    if(!result) return 1;
    
    int ret = 1;
    if(PQntuples(result) == 1) {
        // sql insert succesful
        ret = 0;
        
        char *id_str = PQgetvalue(result, 0, 0);
        char *oid_str = PQgetvalue(result, 0, 1);
        char *lastmodified = PQgetvalue(result, 0, 2);
        char *creationdate = PQgetvalue(result, 0, 3);
        
        if(new_resource_id) {
            if(!id_str || !util_strtoint(id_str, new_resource_id)) {
                ret = 1; // shouldn't happen
                log_ereport(LOG_FAILURE, "Postgresql VFS: sql_create_resource: Could not convert resource_id to int");
            }
        }
        if(oid) {
            int64_t i;
            if(!oid_str || !util_strtoint(oid_str, &i)) {
                ret = 1; // shouldn't happen
                log_ereport(LOG_FAILURE, "Postgresql VFS: sql_create_resource: Could not convert oid to int");
            } else {
                *oid = i;
            }
        }
        if(resource_name) {
            *resource_name = nodename;
        }
        if(s) {
            pg_set_stat(s, 0, lastmodified, creationdate, NULL);
        }
    }
    
    PQclear(result);
    
    return ret;
}


static int pg_create_col(
        PgVFS *pg,
        const char *resparentid_str,
        const char *nodename,
        int64_t *new_resource_id,
        const char **resource_name,
        struct stat *s)
{
    const char* params[2] = { resparentid_str, nodename };
    PGresult *result = PQexecParams(
            pg->connection,
            sql_create_collection,
            2,     // number of parameters
            NULL,
            params, // parameter value
            NULL,
            NULL,
            0);    // 0: result in text format
    
    if(!result) return 1;
    
    int ret = 1;
    if(PQntuples(result) == 1) {
        // sql insert succesful
        ret = 0;
        
        char *id_str = PQgetvalue(result, 0, 0);
        char *lastmodified = PQgetvalue(result, 0, 1);
        char *creationdate = PQgetvalue(result, 0, 2);
        
        if(new_resource_id) {
            if(!id_str || !util_strtoint(id_str, new_resource_id)) {
                ret = 1; // shouldn't happen
                log_ereport(LOG_FAILURE, "Postgresql VFS: sql_create_collection: Could not convert resource_id to int");
            }
        }
        if(resource_name) {
            *resource_name = nodename;
        }
        if(s) {
            pg_set_stat(s, 0, lastmodified, creationdate, NULL);
        }
    }
    
    PQclear(result);
    
    return ret;
}

int pg_create_file(
        VFSContext *ctx,
        PgVFS *pg,
        const char *path,
        int64_t *new_resource_id,
        int64_t *res_parent_id,
        Oid *oid,
        const char **resource_name,
        struct stat *s,
        WSBool collection)
{
    char *parent_path = util_parent_path(path);
    if(!parent_path) return 1;
    
    const char *nodename = util_resource_name(path);
    
    // resolve the parent path
    // if the parent path can't be resolved, we are done
    const char *resname;
    int64_t resource_id, parent_id;
    resource_id = -1;
    parent_id = -1;
    WSBool iscollection;
    Oid unused_oid = 0;
    
    int err = pg_resolve_path(pg->connection, parent_path, &parent_id, &resource_id, &unused_oid, &resname, &iscollection, NULL, &ctx->vfs_errno);
    FREE(parent_path);
    if(err) {
        ctx->vfs_errno = ENOENT;
        return 1;
    }
    
    // parent path exists, check if it is a collection
    if(!iscollection) {
        return 1;
    }
    
    // create new Resource
    char resid_str[32];
    snprintf(resid_str, 32, "%" PRId64, resource_id); // convert parent resource_id to string
    
    int ret;
    if(collection) {
        ret = pg_create_col(pg, resid_str, nodename, new_resource_id, resource_name, s);
    } else {
        ret = pg_create_res(pg, resid_str, nodename, new_resource_id, oid, resource_name, s);
    }
    
    if(res_parent_id) {
        // resource_id is still the id of the parent from previous pg_resolve_path call
        *res_parent_id = resource_id;
    }
    
    return ret;
}

int pg_remove_res(
        VFSContext *ctx,
        PgVFS *pg,
        int64_t resource_id,
        Oid oid)
{
    // create transaction savepoint
    PGresult *result = PQexec(pg->connection, "savepoint del_res;");
    ExecStatusType execStatus = PQresultStatus(result);
    PQclear(result);
    if(execStatus != PGRES_COMMAND_OK) {
        return 1;
    }
    
    if(oid > 0) {
        if(lo_unlink(pg->connection, oid) != 1) {
            // restore savepoint
            result = PQexec(pg->connection, "rollback to savepoint del_res;");
            PQclear(result);
            return 1; // error
        }
    }
    
    char resid_str[32];
    snprintf(resid_str, 32, "%" PRId64, resource_id);
    
    const char* params[1] = { resid_str };
    result = PQexecParams(
            pg->connection,
            sql_delete_res,
            1,     // number of parameters
            NULL,
            params, // parameter value
            NULL,
            NULL,
            0);    // 0: result in text format
      
    execStatus = PQresultStatus(result);
    PQclear(result); 
    int ret = 0;
    
    if(execStatus != PGRES_COMMAND_OK) {
        ret = 1;
        // restore savepoint
        result = PQexec(pg->connection, "rollback to savepoint del_res;");
        PQclear(result);
    } else {
        // we don't need the savepoint anymore
        result = PQexec(pg->connection, "release savepoint del_res;");
        PQclear(result);
    }
    
    return ret;
}

int pg_update_resource(PgVFS *pg, int64_t resource_id, int64_t contentlength) {
    char resid_str[32];
    char ctlen_str[32];
    snprintf(resid_str, 32, "%" PRId64, resource_id);
    snprintf(ctlen_str, 32, "%" PRId64, contentlength);
    
    const char* params[2] = { resid_str, ctlen_str };
    PGresult *result = PQexecParams(
            pg->connection,
            sql_update_resource,
            2,     // number of parameters
            NULL,
            params, // parameter value
            NULL,
            NULL,
            0);    // 0: result in text format
    
    int ret = PQresultStatus(result) == PGRES_COMMAND_OK ? 0 : 1;
    PQclear(result);   
    return ret;
}

/* -------------------------- VFS functions -------------------------- */

SYS_FILE pg_vfs_open(VFSContext *ctx, const char *path, int oflags) {
    VFS *vfs = ctx->vfs;
    PgVFS *pg = vfs->instance;
    
    const char *resname;
    int64_t resource_id, parent_id;
    resource_id = -1;
    parent_id = -1;
    WSBool iscollection;
    struct stat s;
    Oid oid = 0;
    if(pg_resolve_path(pg->connection, path, &parent_id, &resource_id, &oid, &resname, &iscollection, &s, &ctx->vfs_errno)) {
        if((oflags & O_CREAT) == O_CREAT) {
            if(pg_create_file(ctx, pg, path, &resource_id, &parent_id, &oid, &resname, &s, FALSE)) {
                return NULL;
            }
            iscollection = 0;
        } else {
            return NULL;
        }
    }
    
    VFSFile *file = pool_malloc(ctx->pool, sizeof(VFSFile));
    if(!file) {
        return NULL;
    }
    PgFile *pgfile = pool_malloc(ctx->pool, sizeof(PgFile));
    if(!pgfile) {
        pool_free(ctx->pool, file);
        return NULL;
    }
    
    int fd = -1;
    if(!iscollection) {
        if (PQstatus(pg->connection) != CONNECTION_OK) {
            fd = -2;
        }
        
        int lo_mode = INV_READ;
        if((oflags & O_RDWR) == O_RDWR) {
            lo_mode = INV_READ|INV_WRITE;
        } else if((oflags & O_WRONLY) == O_WRONLY) {
            lo_mode = INV_WRITE;
        }
        fd = lo_open(pg->connection, oid, lo_mode);
        int err = 0;
        if(fd < 0) {
            err = 1;
        } else if((oflags & O_TRUNC) == O_TRUNC) {
            if(lo_truncate(pg->connection, fd, 0)) {
                lo_close(pg->connection, fd);
                err = 1;
            }
        }
        
        if(err) {
            pool_free(ctx->pool, file);
            pool_free(ctx->pool, pgfile);
            return NULL;
        }
    }
    
    pgfile->iscollection = iscollection;
    pgfile->resource_id = resource_id;
    pgfile->parent_id = parent_id;
    pgfile->oid = oid;
    pgfile->fd = fd;
    pgfile->oflags = oflags;
    pgfile->s = s;
    
    file->ctx = ctx;
    file->io = iscollection ? &pg_vfs_io_class : &pg_vfs_io_class;
    file->fd = -1;
    file->data = pgfile;
    
    return file;
}

int pg_vfs_stat(VFSContext *ctx, const char *path, struct stat *buf) {
    VFS *vfs = ctx->vfs;
    PgVFS *pg = vfs->instance;
    
    int64_t parent_id, resource_id;
    const char *resname;
    WSBool iscollection;
    return pg_resolve_path(pg->connection, path, &parent_id, &resource_id, NULL, &resname, &iscollection, buf, &ctx->vfs_errno);
}

int pg_vfs_fstat(VFSContext *ctx, SYS_FILE fd, struct stat *buf) {
    PgFile *pgfile = fd->data;
    memcpy(buf, &pgfile->s, sizeof(struct stat));
    return 0;
}

VFS_DIR pg_vfs_opendir(VFSContext *ctx, const char *path) {
    VFSFile *file = pg_vfs_open(ctx, path, O_RDONLY);
    if(!file) return NULL;
    return pg_vfs_fdopendir(ctx, file);
}

VFS_DIR pg_vfs_fdopendir(VFSContext *ctx, SYS_FILE fd) {
    PgFile *pg = fd->data;
    if(!pg->iscollection) {
        ctx->vfs_errno = ENOTDIR;
        return NULL;
    }
    
    VFSDir *dir = pool_malloc(ctx->pool, sizeof(VFSDir));
    if(!dir) {
        fd->io->close(fd);
        ctx->vfs_errno = ENOMEM;
        return NULL;
    }
    
    PgDir *pgdir = pool_malloc(ctx->pool, sizeof(PgDir));
    if(!pgdir) {
        fd->io->close(fd);
        pool_free(ctx->pool, dir);
        ctx->vfs_errno = ENOMEM;
        return NULL;
    }
    memset(pgdir, 0, sizeof(PgDir));
    pgdir->file = fd;
    
    dir->ctx = ctx;
    dir->io = &pg_vfs_dirio_class;
    dir->data = pgdir;
    dir->fd = -1;
    
    return dir;
}

int pg_vfs_mkdir(VFSContext *ctx, const char *path) {
    VFS *vfs = ctx->vfs;
    PgVFS *pg = vfs->instance;
    
    const char *resname;
    int64_t resource_id, parent_id;
    resource_id = -1;
    parent_id = -1;
    WSBool iscollection;
    struct stat s;
    Oid oid = 0;
    if(!pg_resolve_path(pg->connection, path, &parent_id, &resource_id, &oid, &resname, &iscollection, &s, &ctx->vfs_errno)) {
        ctx->vfs_errno = EEXIST;
        return 1;
    }
    
    if(pg_create_file(ctx, pg, path, NULL, NULL, NULL, NULL, NULL, TRUE)) {
        return 1;
    }
    
    return 0;
}

int pg_vfs_unlink(VFSContext *ctx, const char *path) {
    VFS *vfs = ctx->vfs;
    PgVFS *pg = vfs->instance;
    
    const char *resname;
    int64_t resource_id, parent_id;
    resource_id = -1;
    parent_id = -1;
    WSBool iscollection;
    Oid oid = 0;
    if(pg_resolve_path(pg->connection, path, &parent_id, &resource_id, &oid, &resname, &iscollection, NULL, &ctx->vfs_errno)) {
        return 1;
    }
    
    if(iscollection) {
        ctx->vfs_errno = EISDIR;
        return 1;
    }
    
    return pg_remove_res(ctx, pg, resource_id, oid);
}

int pg_vfs_rmdir(VFSContext *ctx, const char *path) {
    VFS *vfs = ctx->vfs;
    PgVFS *pg = vfs->instance;
    
    const char *resname;
    int64_t resource_id, parent_id;
    resource_id = -1;
    parent_id = -1;
    WSBool iscollection;
    if(pg_resolve_path(pg->connection, path, &parent_id, &resource_id, NULL, &resname, &iscollection, NULL, &ctx->vfs_errno)) {
        return 1;
    }
    
    if(!iscollection) {
        ctx->vfs_errno = ENOTDIR;
        return 1;
    }
    
    return pg_remove_res(ctx, pg, resource_id, 0);
}


/* -------------------------- VFS_IO functions -------------------------- */

ssize_t pg_vfs_io_read(SYS_FILE fd, void *buf, size_t nbyte) {
    PgVFS *pgvfs = fd->ctx->vfs->instance;
    PgFile *pg = fd->data;
    return lo_read(pgvfs->connection, pg->fd, buf, nbyte);
}

ssize_t pg_vfs_io_write(SYS_FILE fd, const void *buf, size_t nbyte) {
    PgVFS *pgvfs = fd->ctx->vfs->instance;
    PgFile *pg = fd->data;
    return lo_write(pgvfs->connection, pg->fd, buf, nbyte);
}

ssize_t pg_vfs_io_pread(SYS_FILE fd, void *buf, size_t nbyte, off_t offset) {
    PgVFS *pgvfs = fd->ctx->vfs->instance;
    PgFile *pg = fd->data;
    if(lo_lseek64(pgvfs->connection, pg->fd, offset, SEEK_SET) == -1) {
        return -1;
    }
    return lo_read(pgvfs->connection, pg->fd, buf, nbyte);
}

ssize_t pg_vfs_io_pwrite(SYS_FILE fd, const void *buf, size_t nbyte, off_t offset) {
    PgVFS *pgvfs = fd->ctx->vfs->instance;
    PgFile *pg = fd->data;
    if(lo_lseek64(pgvfs->connection, pg->fd, offset, SEEK_SET) == -1) {
        return -1;
    }
    return lo_write(pgvfs->connection, pg->fd, buf, nbyte);
}

off_t pg_vfs_io_seek(SYS_FILE fd, off_t offset, int whence) {
    PgVFS *pgvfs = fd->ctx->vfs->instance;
    PgFile *pg = fd->data;
    return lo_lseek64(pgvfs->connection, pg->fd, offset, whence);
}

off_t pg_vfs_io_tell(SYS_FILE fd) {
    PgVFS *pgvfs = fd->ctx->vfs->instance;
    PgFile *pg = fd->data;
    return lo_tell64(pgvfs->connection, pg->fd);
}

void pg_vfs_io_close(SYS_FILE fd) {
    pool_handle_t *pool = fd->ctx->pool;
    PgVFS *pgvfs = fd->ctx->vfs->instance;
    PgFile *pg = fd->data;
    
    if(pg->fd >= 0) {
        if((pg->oflags & (O_WRONLY|O_RDWR)) > 0) {
            // file modified, update length and lastmodified
            off_t len = pg_vfs_io_seek(fd, 0, SEEK_END);
            if(len < 0) len = 0;
            
            pg_update_resource(pgvfs, pg->resource_id, len);
        }
        
        PgVFS *pgvfs = fd->ctx->vfs->instance;
        lo_close(pgvfs->connection, pg->fd);
    }
    
    pool_free(pool, pg);
    pool_free(pool, fd);
}


/* -------------------------- VFS_DIRIO functions -------------------------- */

static int load_dir(VFSDir *dir, PgDir *pg) {
    VFS *vfs = dir->ctx->vfs;
    PgVFS *pgvfs = vfs->instance;
    PgFile *pgfd = pg->file->data;
    PgDir *pgdir = dir->data;
    
    char resid_param[32];
    snprintf(resid_param, 32, "%" PRId64, pgfd->resource_id);
    
    const char *param = resid_param;
    
    PGresult *result = PQexecParams(
            pgvfs->connection,
            sql_get_children,
            1,                                  // number of parameters
            NULL,
            &param,                             // param: parent resource_id 
            NULL,
            NULL,
            0);                                 // 0: result in text format
    if(!result) return 1;
    
    pgdir->result = result;
    pgdir->nrows = PQntuples(result);
    return 0;
}

int pg_vfs_dirio_readdir(VFS_DIR dir, VFS_ENTRY *entry, int getstat) {
    PgDir *pg = dir->data;
    if(!pg->result) {
        if(load_dir(dir, pg)) {
            return 0;
        }
    }
    
    if(pg->row >= pg->nrows) {
        return 0; // EOF
    }
    
    entry->name = PQgetvalue(pg->result, pg->row, 1);
        
    if(getstat) {
        memset(&entry->stat, 0, sizeof(struct stat));
        
        char *iscollection = PQgetvalue(pg->result, pg->row, 2);
        char *lastmodified = PQgetvalue(pg->result, pg->row, 3);
        char *creationdate = PQgetvalue(pg->result, pg->row, 4);
        char *contentlength = PQgetvalue(pg->result, pg->row, 5);
        pg_set_stat(&entry->stat, iscollection, lastmodified, creationdate, contentlength);
    }

    pg->row++;
    return 1;
}

void pg_vfs_dirio_close(VFS_DIR dir) {
    pool_handle_t *pool = dir->ctx->pool;
    PgDir *pg = dir->data;
    if(pg->result) {
        PQclear(pg->result);
    }
    PgFile *pgfile = pg->file->data;
    
    pool_free(pool, pgfile);
    pool_free(pool, pg->file);
    pool_free(pool, pg);
    pool_free(pool, dir);
}

time_t pg_convert_timestamp(const char *timestamp) {
    struct tm tm;
    if(pg_convert_timestamp_tm(timestamp, &tm)) {
        return 0;
    }
#ifdef __FreeBSD__
    return timelocal(&tm);
#else
    return mktime(&tparts) - timezone;
#endif
}

int pg_convert_timestamp_tm(const char *timestamp, struct tm *tm) {
    // TODO: this is a very basic implementation that needs some work
    // format yyyy-mm-dd HH:MM:SS
    
    memset(tm, 0, sizeof(struct tm));
    size_t len = timestamp ? strlen(timestamp) : 0;
    if(len < 19) {
        return 1;
    } else if(len > 63) {
        return 1;
    }
    
    char buf[64];
    memcpy(buf, timestamp, len);
    
    char *year_str = buf;
    year_str[4] = '\0';
    
    char *month_str = buf + 5;
    month_str[2] = '\0';
    
    char *day_str = buf + 8;
    day_str[2] = '\0';
    
    char *hour_str = buf + 11;
    hour_str[2] = '\0';
    
    char *minute_str = buf + 14;
    minute_str[2] = '\0';
    
    char *second_str = buf + 17;
    second_str[2] = '\0';
    
    tm->tm_year = atoi(year_str) - 1900;
    tm->tm_mon = atoi(month_str) - 1;
    tm->tm_mday = atoi(day_str);
    tm->tm_hour = atoi(hour_str);
    tm->tm_min = atoi(minute_str);
    tm->tm_sec = atoi(second_str);
    
    return 0;
}