view artifacts/doc/conf/datacage.sql @ 8452:b9a8463ac636

(issue1732) When duplication wsplgen artificats fix shapefile layers The shapefile layers are copied but still contained the old names and paths and the wms layer refered to layers from the old artifact. This was broken. Now the old artificat id is replaced by the new artifact id in the mapfiles. Duplication most wsplgen artifacts should now work.
author Andre Heinecke <andre.heinecke@intevation.de>
date Tue, 28 Oct 2014 18:52:35 +0100
parents 6d55614d6f87
children
line wrap: on
line source
BEGIN;

CREATE SEQUENCE USERS_ID_SEQ;

CREATE TABLE users (
    id  INT  PRIMARY KEY NOT NULL,
    gid UUID             NOT NULL UNIQUE
);

CREATE SEQUENCE COLLECTIONS_ID_SEQ;

CREATE TABLE collections (
    id       INT  PRIMARY KEY NOT NULL,
    gid      UUID             NOT NULL UNIQUE,
    user_id  INT              NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name     VARCHAR(256)     NOT NULL,
    creation TIMESTAMP        NOT NULL
);

CREATE SEQUENCE ARTIFACTS_ID_SEQ;

CREATE TABLE artifacts (
    id       INT  PRIMARY KEY NOT NULL,
    gid      UUID             NOT NULL UNIQUE,
    state    VARCHAR(256)     NOT NULL,
    creation TIMESTAMP        NOT NULL
);

CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;

CREATE TABLE collection_items (
    id            INT PRIMARY KEY NOT NULL,
    collection_id INT             NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
    artifact_id   INT             NOT NULL REFERENCES artifacts(id)   ON DELETE CASCADE
);

CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ;

CREATE TABLE artifact_data (
    id          INT PRIMARY KEY NOT NULL,
    artifact_id INT             NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
    kind        VARCHAR(256)    NOT NULL,
    k           VARCHAR(256)    NOT NULL,
    v           TEXT,
    UNIQUE (artifact_id, k)
);

CREATE SEQUENCE OUTS_ID_SEQ;

CREATE TABLE outs (
    id          INT PRIMARY KEY NOT NULL,
    artifact_id INT             NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
    name        VARCHAR(256)    NOT NULL,
    description VARCHAR(256),
    out_type    VARCHAR(256)
);

CREATE SEQUENCE FACETS_ID_SEQ;

CREATE TABLE facets (
    id          INT PRIMARY KEY NOT NULL,
    out_id      INT             NOT NULL REFERENCES outs(id) ON DELETE CASCADE,
    name        VARCHAR(256)    NOT NULL,
    num         INT             NOT NULL,
    state       VARCHAR(256)    NOT NULL,
    description VARCHAR(256),
    UNIQUE (out_id, num, name)
);

CREATE VIEW master_artifacts AS
    SELECT a2.id             AS id,
           a2.gid            AS gid,
           a2.state          AS state,
           a2.creation       AS creation,
           ci2.collection_id AS collection_id
    FROM   collection_items ci2 
           JOIN artifacts a2 
             ON ci2.artifact_id = a2.id 
           JOIN (SELECT ci.collection_id AS c_id, 
                        MIN(a.creation)  AS oldest_a 
                 FROM   collection_items ci 
                        JOIN artifacts a 
                          ON ci.artifact_id = a.id 
                 GROUP  BY ci.collection_id) o 
             ON o.c_id = ci2.collection_id 
    WHERE  a2.creation = o.oldest_a;

CREATE VIEW master_artifacts_range AS
    SELECT ma.id                   AS id,
           ma.gid                  AS gid,
           ma.state                AS state,
           ma.creation             AS creation,
           ma.collection_id        AS collection_id,
           mam.ld_mode             AS ld_mode,
           mal.ld_locations        AS ld_locations,
           maf.ld_from             AS ld_from,
           mat.ld_to               AS ld_to
    FROM master_artifacts ma
        LEFT JOIN (SELECT ad.v           AS ld_mode,
                          ad.artifact_id AS artifact_id
                   FROM artifact_data ad
                   WHERE ad.k = 'ld_mode') mam
                   ON mam.artifact_id = ma.id
        LEFT JOIN (SELECT ad.v           AS ld_locations,
                          ad.artifact_id AS artifact_id
                   FROM artifact_data ad
                   WHERE ad.k = 'ld_locations') mal
                   ON mal.artifact_id = ma.id
        LEFT JOIN (SELECT ad.v           AS ld_from,
                          ad.artifact_id AS artifact_id
                   FROM artifact_data ad
                   WHERE ad.k = 'ld_from') maf
                   ON maf.artifact_id = ma.id
        LEFT JOIN (SELECT ad.v           AS ld_to,
                          ad.artifact_id AS artifact_id
                   FROM artifact_data ad
                   WHERE ad.k = 'ld_to') mat
                   ON mat.artifact_id = ma.id;

-- DROP VIEW master_artifacts;
-- DROP VIEW master_artifacts_range;
-- DROP SEQUENCE USERS_ID_SEQ;
-- DROP SEQUENCE COLLECTIONS_ID_SEQ;
-- DROP SEQUENCE ARTIFACTS_ID_SEQ;
-- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ;
-- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ;
-- DROP SEQUENCE OUTS_ID_SEQ;
-- DROP SEQUENCE FACETS_ID_SEQ;
-- DROP TABLE facets;
-- DROP TABLE outs;
-- DROP TABLE artifact_data;
-- DROP TABLE collection_items;
-- DROP TABLE collections;
-- DROP TABLE artifacts;
-- DROP TABLE users;

COMMIT;

http://dive4elements.wald.intevation.org