resto-database-model/03_resto_target_model.sql

Summary

Maintainability
Test Coverage
--
-- resto dedicated target model
--

--
-- Target tables are sored under {DATABASE_TARGET_SCHEMA} schema (default is resto)
--
CREATE SCHEMA IF NOT EXISTS __DATABASE_TARGET_SCHEMA__;

--
-- collections table list all resto collections
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.collection (

    -- Unique id for collection
    "id"                TEXT PRIMARY KEY,

    -- Collection version
    version             TEXT,

    -- Model used to ingest collection metadata
    model               TEXT,

    -- Model lineage
    lineage             TEXT[],

    -- A default license attached to this collection.
    -- Every feature within this collection will inherit from the collection's license
    licenseid           TEXT,

    -- Visibility - group visibility (only user within this group can see collection)
    visibility          INTEGER,

    -- Owner of the collection. References __DATABASE_COMMON_SCHEMA__.user.id
    owner               BIGINT,

    -- Timestamp of collection creation
    created             TIMESTAMP,

    -- Start time of the collection 
    startdate           TIMESTAMP,

    -- Completion time of the collection 
    completiondate      TIMESTAMP,

    -- Spatial extent of the collection
    bbox                GEOMETRY(GEOMETRY, 4326),

    -- [STAC] Providers
    providers           JSON,

    -- [STAC] Additional properties
    properties          JSON,

    -- [STAC] Static links
    links               JSON,

    -- [STAC] Collection assets
    assets              JSON,

    -- [STAC] Keywords
    keywords            TEXT[]

);

--
-- osdescriptions table describe all RESTo collections
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.osdescription (

    -- Reference __DATABASE_TARGET_SCHEMA__.collection.id
    collection          TEXT REFERENCES __DATABASE_TARGET_SCHEMA__.collection (id) ON DELETE CASCADE, 

    -- OpenSearch description lang
    lang                TEXT,

    -- Contains a brief human-readable title that identifies this search engine.
    shortname           TEXT,
    
    -- Contains an extended human-readable title that identifies this search engine.
    longname            TEXT,
    
    -- Contains a human-readable text description of the search engine.
    description         TEXT,

    -- Contains a set of words that are used as keywords to identify and categorize this search content. Tags must be a single word and are delimited by the space character
    tags                TEXT,

    -- Contains the human-readable name or identifier of the creator or maintainer of the description document.
    Developer           TEXT,

    -- Contains an email address at which the maintainer of the description document can be reached.
    contact             TEXT,
    
    -- Defines a search query that can be performed by search clients. Please see the OpenSearch Query element specification for more information.
    query               TEXT,
    
    -- Contains a list of all sources or entities that should be credited for the content contained in the search feed
    attribution         TEXT

);

--
-- Collection aliases
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.collection_alias (

    -- Alternate name to this collection
    alias               TEXT PRIMARY KEY,

    -- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.collection.id
    collection          TEXT REFERENCES __DATABASE_TARGET_SCHEMA__.collection (id) ON DELETE CASCADE

);

--
-- Features table - handle every metadata
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.feature (

    -- [INDEXED] UUID v5 based on productidentifier
    "id"                UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(),

    -- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.collection.id - A feature is within one and only one collection
    collection          TEXT NOT NULL, 

    -- Vendor identifier (for migration)
    productidentifier   TEXT,

    -- A title for this feature
    title               TEXT,

    -- A description for this feature
    description         TEXT,

    -- Start of validity timestamp for this feature 
    startdate           TIMESTAMP,

    -- End of validity timestamp for this feature. If equal to started then feature is "instantaneous"
    completiondate      TIMESTAMP,

    -- [INDEXED] Visibility - only user within a group with the same name as visibility can see feature
    visibility          INTEGER,

    -- [INDEXED] Owner of the feature. Reference __DATABASE_COMMON_SCHEMA__.user.id
    owner               BIGINT,

    -- [INDEXED] Open value
    status              INTEGER,

    -- Number of likes for this feature. Used by Social add-on
    likes               INTEGER,

    -- Number of comments for this feature. Used by Social add-on
    comments            INTEGER, 

    -- Metadata. You can put whatever you want.
    -- Field indexation is based on collection type
    --  
    --  Example for a satellite imagery
    --      {
    --          authority:
    --          productType:
    --          processingLevel:
    --          platform:
    --          instrument:
    --          resolution:
    --          sensorMode:
    --          orbitNumber:
    --      }
    metadata            JSON,

    -- Assets array contains download, metadata original file, etc.
    --  
    --      {
    --          download:{
    --              "title":"Download link",
    --              "href":"http://localhost/image123.tif",
    --              "realPath":"file://data/images/image123.tif",
    --              "size": 1234567,
    --              "integrity": "sha384-oqVuAfXRKap...7f86",
    --              "type":"application/tif"
    --          },
    --          metadata:{
    --              "rel":"alternate",
    --              "title":"Metadata link",
    --              "href":"http://localhost/image123.xml",
    --              "realPath":"file://data/images/image123.xml",
    --              "type":"application/xml"
    --          }
    --      }
    --
    assets               JSON,

    -- Links array contains related url
    --  
    --      [
    --          {"rel": "acquisition", "href": "http://cool-sat.com/catalog/acquisitions/20160503_56"}
    --          {"rel": "something", "title":"SIVolcano", "href": "http://volcano.si.edu/volcano.cfm?vn=233020"}
    --      ]
    --
    links               JSON,

    -- Timestamp of creation for this feature metadata
    created           TIMESTAMP,

    -- Timestamp of update for this feature metadata
    updated             TIMESTAMP,

    -- Keywords computed by Tag add-on
    keywords            JSON,

    -- 
    -- List of hashtags (without prefix # !)
    --
    -- Two kind of hashtags:
    --   * hashtags without {RestoConstants::TAG_SEPARATOR} hashtags *provided* by user from description
    --   * hashtags with {RestoConstants::TAG_SEPARATOR} hashtags *computed* by Tag add-on (depend on collection)
    hashtags            TEXT[],

    -- Original geometry as provided during feature insertion
    -- It is set to NULL if equals to geom (see below) 
    geometry            GEOMETRY(GEOMETRY, 4326),
    
    -- Centroid computed from geometry
    centroid            GEOMETRY(POINT, 4326),

    -- Result of ST_SplitDateLine(geometry)
    -- Guarantee a valid geometry in database even if input geometry crosses -180/180 meridian of crosses North or South pole
    -- If input geometry does not cross one of this case, then input geometry is not
    -- modified and geom equals geomety.
    geom           GEOMETRY(GEOMETRY, 4326),

    -- [INDEXED] Hashtags
    -- 
    -- List of normalized hashtags (without prefix # !)
    --
    normalized_hashtags TEXT[],

    -- [INDEXED] Start date unique iterator
    startdate_idx       BIGINT,
    
    -- [INDEXED] Created date unique iterator
    created_idx       BIGINT

);

--
-- Feature geometry is splitted into smaller part and indexed
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.geometry_part (

    -- Feature identifier
    "id"                UUID REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,

    -- Feature's collection
    collection          TEXT NOT NULL, 

    -- Part iterator
    part_num            INTEGER,

    -- [INDEXED] Geometry part
    geom                GEOMETRY(GEOMETRY, 4326),

    -- Primary key based on unique identifier
    PRIMARY KEY (id, part_num)

);

--
-- Relation between features
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.relation (

    -- Reference feature id master
    id1                 UUID REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,

    -- Reference feature id slave
    id2                 UUID REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,

    -- Relation type: -1 (id1 is the parent of id2 - "hasSample"), 1 (id1 is the child of id1 - "isSampleOf")
    relation            INTEGER NOT NULL,

    -- Relation type
    type                TEXT,

    -- Relation creation date
    created             TIMESTAMP,

    -- Primary key based on unique identifier
    PRIMARY KEY (id1, id2)

);


--
-- Features content common to all features belonging to LandCoverModel (based on itag)
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.feature_landcover (

    -- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.feature.id
    "id"                UUID PRIMARY KEY REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,

    -- Collection id
    collection          TEXT NOT NULL, 

    -- Percentage of cultivated area
    cultivated          NUMERIC,

    -- Percentage of desert area
    desert              NUMERIC,

    -- Percentage of flooded area
    flooded             NUMERIC,

    -- Percentage of forest area
    forest              NUMERIC,

    -- Percentage of herbaceous area
    herbaceous          NUMERIC,

    -- Percentage of ice area
    ice                 NUMERIC,

    -- Percentage of urban area
    urban               NUMERIC,

    -- Percentage of water area
    water               NUMERIC,

    -- Population estimation (in number of people)
    population          NUMERIC,

    -- Population estimation density (in peopler per square kilometers)
    population_density  NUMERIC

);

--
-- Features specific properties based on SatelliteModel
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.feature_satellite (

    -- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.feature.id
    "id"                UUID PRIMARY KEY REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,

    -- Collection id
    collection          TEXT NOT NULL,

    -- Image resolution in meters
    resolution          NUMERIC
    
);

--
-- Features specific properties based on OpticalModel
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.feature_optical (

    -- [INDEXED] Reference __DATABASE_TARGET_SCHEMA__.feature.id
    "id"                UUID PRIMARY KEY REFERENCES __DATABASE_TARGET_SCHEMA__.feature (id) ON DELETE CASCADE,

    -- Collection id
    collection          TEXT NOT NULL,
    
    -- Percentage of snow in area
    snowcover           NUMERIC,

    -- Percentage of cloud area
    cloudcover          NUMERIC
    
);

--
-- Facets table
--
CREATE TABLE IF NOT EXISTS __DATABASE_TARGET_SCHEMA__.facet (

    -- Identifier for the facet (unique in combination with collection id)
    id                  TEXT NOT NULL,

    -- Collection id attached to the facet
    collection          TEXT NOT NULL,
    
    -- Facet value
    value               TEXT,

    -- Facet type (i.e. catalog, hashtag, region, state, location, etc.)
    type                TEXT,

    -- Parent identifier (i.e. 'europe' for facet 'france')
    pid                 TEXT NOT NULL,

    -- Set to 1 if facet is a terminal leaf, 0 otherwise (used for STAC)
    isleaf              INTEGER,

    -- Number of appearance of this facet within the collection
    counter             INTEGER,

    -- Facet date of creation
    created             TIMESTAMP DEFAULT now(),

    -- Owner of the facet i.e. first user to create it
    owner             BIGINT,

    -- Description
    description         TEXT,
    
    -- The id, pid, collection pair should be unique
    PRIMARY KEY (id, pid, collection)

);

-- --------------------- INDEXES ---------------------------

-- [TABLE __DATABASE_TARGET_SCHEMA__.collection]
CREATE UNIQUE INDEX IF NOT EXISTS idx_id_collection on __DATABASE_TARGET_SCHEMA__.collection (public.normalize(id));
CREATE INDEX IF NOT EXISTS idx_lineage_collection ON __DATABASE_TARGET_SCHEMA__.collection USING GIN (lineage);
CREATE INDEX IF NOT EXISTS idx_visibility_collection ON __DATABASE_TARGET_SCHEMA__.collection (visibility);
CREATE INDEX IF NOT EXISTS idx_created_collection ON __DATABASE_TARGET_SCHEMA__.collection (created);
CREATE INDEX IF NOT EXISTS idx_keywords_collection ON __DATABASE_TARGET_SCHEMA__.collection USING GIN (keywords);

-- [TABLE __DATABASE_TARGET_SCHEMA__.osdescription]
ALTER TABLE __DATABASE_TARGET_SCHEMA__.osdescription DROP CONSTRAINT IF EXISTS cl_collection;
ALTER TABLE ONLY __DATABASE_TARGET_SCHEMA__.osdescription ADD CONSTRAINT cl_collection UNIQUE(collection, lang);

CREATE INDEX IF NOT EXISTS idx_collection_osdescription ON __DATABASE_TARGET_SCHEMA__.osdescription (collection);
-- CREATE INDEX IF NOT EXISTS idx_lang_osdescription ON __DATABASE_TARGET_SCHEMA__.osdescription (lang);

-- [TABLE __DATABASE_TARGET_SCHEMA__.facet]
CREATE INDEX IF NOT EXISTS idx_id_facet ON __DATABASE_TARGET_SCHEMA__.facet (public.normalize(id));
CREATE INDEX IF NOT EXISTS idx_pid_facet ON __DATABASE_TARGET_SCHEMA__.facet (public.normalize(pid));
CREATE INDEX IF NOT EXISTS idx_type_facet ON __DATABASE_TARGET_SCHEMA__.facet (type);
CREATE INDEX IF NOT EXISTS idx_collection_facet ON __DATABASE_TARGET_SCHEMA__.facet (public.normalize(collection));
CREATE INDEX IF NOT EXISTS idx_value_facet ON __DATABASE_TARGET_SCHEMA__.facet USING GIN (public.normalize(value) gin_trgm_ops);

-- [TABLE __DATABASE_TARGET_SCHEMA__.feature]
CREATE INDEX IF NOT EXISTS idx_collection_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (collection);
CREATE INDEX IF NOT EXISTS idx_startdateidx_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (startdate_idx);
CREATE INDEX IF NOT EXISTS idx_createdidx_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (created_idx);
CREATE INDEX IF NOT EXISTS idx_owner_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (owner) WHERE owner IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_visibility_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (visibility);
CREATE INDEX IF NOT EXISTS idx_status_feature ON __DATABASE_TARGET_SCHEMA__.feature USING btree (status);
CREATE INDEX IF NOT EXISTS idx_centroid_feature ON __DATABASE_TARGET_SCHEMA__.feature USING GIST (centroid);
CREATE INDEX IF NOT EXISTS idx_nhashtags_feature ON __DATABASE_TARGET_SCHEMA__.feature USING GIN (normalized_hashtags) WHERE normalized_hashtags IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_geom_feature ON __DATABASE_TARGET_SCHEMA__.feature USING GIST (geom);

-- [TABLE __DATABASE_TARGET_SCHEMA__.geometry_part]
CREATE INDEX IF NOT EXISTS idx_id_geometry_part ON __DATABASE_TARGET_SCHEMA__.geometry_part USING HASH (id);
CREATE INDEX IF NOT EXISTS idx_geom_geometry_part ON __DATABASE_TARGET_SCHEMA__.geometry_part USING GIST (geom);

-- [TABLE __DATABASE_TARGET_SCHEMA__.feature_landcover]
CREATE INDEX IF NOT EXISTS idx_cultivated_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (cultivated);
CREATE INDEX IF NOT EXISTS idx_desert_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (desert);
CREATE INDEX IF NOT EXISTS idx_flooded_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (flooded);
CREATE INDEX IF NOT EXISTS idx_forest_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (forest);
CREATE INDEX IF NOT EXISTS idx_herbaceous_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (herbaceous);
CREATE INDEX IF NOT EXISTS idx_ice_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (ice);
CREATE INDEX IF NOT EXISTS idx_urban_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (urban);
CREATE INDEX IF NOT EXISTS idx_water_m_landcover ON __DATABASE_TARGET_SCHEMA__.feature_landcover USING btree (water);

-- [TABLE __DATABASE_TARGET_SCHEMA__.feature_optical]
CREATE INDEX IF NOT EXISTS idx_cloudcover_m_optical ON __DATABASE_TARGET_SCHEMA__.feature_optical USING btree (cloudcover);