view db/libervia_pubsub_update_1_2.sql @ 489:fa0d2a4783fa default tip

install: use PEP-440 compatible version in `setup`: setuptools from version v66 doesn't accept anymore non PEP-440 compatible version. This is a Q&D fix, move to `pyproject.toml` with proper versioning as for Libervia backend should be done sooner than later.
author Goffi <goffi@goffi.org>
date Fri, 12 Jan 2024 23:46:24 +0100
parents 9af0ef2c145c
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';