02-views.sql

/*
 * This file is part of vospace-file-catalog
 * Copyright (C) 2021 Istituto Nazionale di Astrofisica
 * SPDX-License-Identifier: GPL-3.0-or-later
 */

-- View that maps node_id to os_path (relative path of file on disk)

CREATE VIEW node_os_path AS
SELECT node_id, '/' AS os_path FROM node WHERE path = ''
UNION
SELECT node_id, '/' || string_agg(name, '/') AS os_path
FROM (
    SELECT (CASE WHEN os_name IS NOT NULL THEN os_name ELSE name END) AS name, p.node_id
    FROM node n
    JOIN (
        SELECT UNNEST(string_to_array(relative_path::varchar, '.')) AS rel_id, node_id
        FROM node
    ) AS p ON n.node_id::varchar = p.rel_id
    ORDER BY p.node_id, nlevel(n.path)
) AS j GROUP BY node_id;

-- View that maps node_id to vos_path (path in VOSpace)

CREATE VIEW node_vos_path AS
SELECT node_id, '/' AS vos_path FROM node WHERE path = ''
UNION
SELECT node_id, '/' || string_agg(name, '/') AS os_path
FROM (
    SELECT name, p.node_id
    FROM node n
    JOIN (
        SELECT UNNEST(string_to_array(path::varchar, '.')) AS rel_id, node_id
        FROM node
    ) AS p ON n.node_id::varchar = p.rel_id
    ORDER BY p.node_id, nlevel(n.path)
) AS j GROUP BY node_id;

-- View that combines the two previous views to obtain the os_path from the vos_path and vice versa

CREATE VIEW node_path AS
SELECT v.node_id, vos_path, os_path
FROM node_vos_path v
LEFT JOIN node_os_path o ON o.node_id = v.node_id;

-- Example query to obtain the the ltree_path from the os_path:
-- SELECT path FROM node n JOIN node_os_path o ON n.node_id = o.node_id WHERE os_path = '/curban/store';