view db/sat_pubsub_update_1_2.sql @ 447:cc29a62fb64c

renaming following global Salut à Toi => Libervia renaming: /!\ application name and environment variables are renamed /!\ - Twisted application name is now `libervia-pubsub` - environment variable are now prefixed by `LIBERVIA_PUBSUB_`
author Goffi <goffi@goffi.org>
date Wed, 14 Apr 2021 12:05:40 +0200
parents 1dc606612405
children
line wrap: on
line source

-- we check version of the database before doing anything
-- and stop execution if not good
\set ON_ERROR_STOP
DO $$
DECLARE ver smallint;
BEGIN
    SELECT value INTO ver FROM metadata WHERE key='version';
    IF NOT FOUND OR ver!='1' THEN
        RAISE EXCEPTION 'This update file needs to be applied on database schema version 1, you use version %',ver;
    END IF;
END$$;
\unset ON_ERROR_STOP
-- end of version check

/* roster access model was badly used, we rename it to publisher-roster */

ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check;
UPDATE nodes SET access_model = 'publisher-roster' WHERE access_model = 'roster';
ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher'));

ALTER TABLE items DROP CONSTRAINT items_access_model_check;
UPDATE items SET access_model = 'publisher-roster' WHERE access_model = 'roster';
ALTER TABLE items ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist'));

ALTER TABLE affiliations DROP CONSTRAINT affiliations_affiliation_check;
ALTER TABLE affiliations ADD CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner'));

CREATE TABLE item_jids_authorized (
    item_jids_authorized_id serial PRIMARY KEY,
    item_id integer NOT NULL references items ON DELETE CASCADE,
    jid text NOT NULL,
    UNIQUE (item_id,jid)
);

CREATE TABLE item_languages (
    item_languages_id serial PRIMARY KEY,
    item_id integer NOT NULL references items ON DELETE CASCADE,
    language text NOT NULL,
    UNIQUE (item_id,language)
);

UPDATE metadata SET value='2' WHERE key='version';