Skip to content
Snippets Groups Projects
Select Git revision
  • 9857922aff10083d2df3d8ab34b927abb4cbb727
  • master default
  • v0.0.1
3 results

00-init.sql

Blame
  • 00-init.sql 6.61 KiB
    /**_____________________________________________________________________________
     *
     *                                 OATS - INAF
     *  Osservatorio Astronomico di Tireste - Istituto Nazionale di Astrofisica
     *  Astronomical Observatory of Trieste - National Institute for Astrophysics
     * ____________________________________________________________________________
     *
     * Copyright (C) 2020  Istituto Nazionale di Astrofisica
     * 
     * This program is free software; you can redistribute it and/or
     * modify it under the terms of the GNU General Public License
     * as published by the Free Software Foundation; either version 2
     * of the License, or (at your option) any later version.
     *
     * This program is distributed in the hope that it will be useful,
     * but WITHOUT ANY WARRANTY; without even the implied warranty of
     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
     * GNU General Public License for more details.
     *
     * You should have received a copy of the GNU General Public License
     * along with this program; if not, write to the Free Software Foundation, Inc., 
     * 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
     * _____________________________________________________________________________
     **/
    
    /*
        VOSpace front-end
    */
    
    CREATE EXTENSION IF NOT EXISTS ltree;
    
    CREATE TYPE NodeType AS ENUM ('container', 'data', 'link', 'structured');
    CREATE TYPE LocationType AS ENUM ('async', 'portal', 'user');
    CREATE TYPE StorageType AS ENUM ('cold', 'hot', 'local', 'portal');
    
    
    CREATE TABLE storage (
        storage_id        SMALLSERIAL   NOT NULL,
        storage_type      StorageType   NOT NULL,
        base_path         VARCHAR       NOT NULL,
        hostname          VARCHAR       NOT NULL,
        PRIMARY KEY (storage_id)
    );
    
    
    CREATE TABLE location (
        location_id       SMALLSERIAL   NOT NULL,
        location_type     LocationType  NOT NULL,
        storage_src_id    SMALLINT      default NULL,
        storage_dest_id   SMALLINT      default NULL,
        PRIMARY KEY (location_id),
        FOREIGN KEY (storage_src_id) REFERENCES storage (storage_id) ON DELETE CASCADE,
        FOREIGN KEY (storage_dest_id) REFERENCES storage (storage_id)
    );
    
    
    CREATE TABLE node (
        node_id                BIGSERIAL     NOT NULL,
        parent_path            LTREE         default NULL,
        parent_relative_path   LTREE         default NULL,
        name                   VARCHAR       NOT NULL,
        os_name                VARCHAR       default NULL,
        tstamp_wrapper_dir     VARCHAR       default NULL,
        type                   NodeType      NOT NULL,
        location_id            SMALLINT      default NULL,
        format                 VARCHAR       default NULL,
        -- format is used to distinguish between unstuctured (format=NULL) and structured nodes having a well defined format
        async_trans            BOOLEAN       default false,
        -- async_trans tells us whether a node is hosted on a cold storage and has to be transferred asynchronously
        sticky                 BOOLEAN       default false,
        busy_state             BOOLEAN       default NULL,
        owner_id               VARCHAR       default NULL,
        creator_id             VARCHAR       default NULL,
        group_read             VARCHAR[]     default NULL,
        group_write            VARCHAR[]     default NULL,
        is_public              BOOLEAN       default NULL,
        delta                  BIGINT        default NULL,
        /* it may be a delta of data which is transferred through an asynchronous transfer.
           It should stay on the transfer service (e.g. Redis) */
        content_type           VARCHAR       default NULL,
        content_encoding       VARCHAR       default NULL,
        content_length         BIGINT        default NULL,
        content_md5            TEXT          default NULL,
        created_on             TIMESTAMP     default CURRENT_TIMESTAMP,
        last_modified          TIMESTAMP     default NULL,
        -- link                  TEXT          default NULL,
        accept_views           TEXT[]        default NULL,
        provide_views          TEXT[]        default NULL,
        -- storage service mapping used to access the content of this node 
        -- storage_id            VARCHAR,    
        protocols              TEXT[]        default NULL,
        PRIMARY KEY (node_id),
        FOREIGN KEY (location_id) REFERENCES location (location_id)
    );
    
    
    CREATE TABLE deleted_node (
        node_id                BIGSERIAL     NOT NULL,
        parent_path            LTREE         default NULL,
        parent_relative_path   LTREE         default NULL,
        name                   VARCHAR       NOT NULL,
        os_name                VARCHAR       default NULL,
        tstamp_wrapper_dir     VARCHAR       default NULL,
        type                   NodeType      NOT NULL,
        location_id            SMALLINT      default NULL,
        format                 VARCHAR       default NULL,
        -- format is used to distinguish between unstuctured (format=NULL) and structured nodes having a well defined format
        async_trans            BOOLEAN       default NULL,
        -- async_trans tells us whether a node is hosted on a cold storage and has to be transferred asynchronously
        sticky                 BOOLEAN       default false,
        busy_state             BOOLEAN       default NULL,
        owner_id               VARCHAR       default NULL,
        creator_id             VARCHAR       default NULL,
        group_read             VARCHAR[]     default NULL,
        group_write            VARCHAR[]     default NULL,
        is_public              BOOLEAN       default NULL,
        delta                  BIGINT        default NULL,
        /* it may be a delta of data which is transferred through an asynchronous transfer.
           It should stay on the transfer service (e.g. Redis) */
        content_type           VARCHAR       default NULL,
        content_encoding       VARCHAR       default NULL,
        content_length         BIGINT        default NULL,
        content_md5            TEXT          default NULL,
        created_on             TIMESTAMP     default NULL,
        last_modified          TIMESTAMP     default NULL,
        -- link                  TEXT          default NULL,
        accept_views           TEXT[]        default NULL,
        provide_views          TEXT[]        default NULL,
        -- storage service mapping used to access the content of this node 
        -- storage_id            VARCHAR,    
        protocols              TEXT[]        default NULL,
        -- add a deleted_on timestamp to keep track
        deleted_on             TIMESTAMP     default CURRENT_TIMESTAMP,
        PRIMARY KEY (node_id),
        FOREIGN KEY (location_id) REFERENCES location (location_id)
    );
    
    
    CREATE TABLE users (
        rap_id            VARCHAR       NOT NULL,
        user_name         VARCHAR       NOT NULL,
        e_mail            VARCHAR       NOT NULL,
        PRIMARY KEY (rap_id)
    );