Mercurial > libervia-pubsub

annotate db/sat_pubsub_update_1_2.sql @ 369:dabee42494ac

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
config file + cleaning: - SàT Pubsub can now be configured using the same config file as SàT itself (i.e. sat.conf or .sat.conf), in the same locations (/etc, local dir, xdg dir). Its options must be in the "pubsub" section - options on command line override config options - removed tap and http files which are not used anymore - changed directory structure to put source in src, to be coherent with SàT and Libervia - changed options name, db* become db_*, secret become xmpp_pwd - an exception is raised if jid or xmpp_pwd is are not configured
author Goffi <goffi@goffi.org>
date Fri, 02 Mar 2018 12:59:38 +0100
parents 4d4575911060
children 1dc606612405
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
350
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
1 -- we check version of the database before doing anything
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
2 -- and stop execution if not good
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
3 \set ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
4 DO $$
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
5 DECLARE ver smallint;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
6 BEGIN
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
7 SELECT value INTO ver FROM metadata WHERE key='version';
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
8 IF NOT FOUND OR ver!='1' THEN
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
9 RAISE EXCEPTION 'This update file need to be applied on database schema version 1, you use version %',ver;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
10 END IF;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
11 END$$;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
12 \unset ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
13 -- end of version check
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
14
329
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
15 /* roster access model was badly used, we rename it to publisher-roster */
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
16
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
17 ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check;
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
18 UPDATE nodes SET access_model = 'publisher-roster' WHERE access_model = 'roster';
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
19 ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher'));
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
20
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
21 ALTER TABLE items DROP CONSTRAINT items_access_model_check;
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
22 UPDATE items SET access_model = 'publisher-roster' WHERE access_model = 'roster';
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
23 ALTER TABLE items ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist'));
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
24
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
25 ALTER TABLE affiliations DROP CONSTRAINT affiliations_affiliation_check;
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
26 ALTER TABLE affiliations ADD CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner'));
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
27
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
28 CREATE TABLE item_jids_authorized (
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
29 item_jids_authorized_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
30 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
31 jid text NOT NULL,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
32 UNIQUE (item_id,jid)
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
33 );
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
34
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
35 CREATE TABLE item_languages (
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
36 item_languages_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
37 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
38 language text NOT NULL,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
39 UNIQUE (item_id,language)
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
40 );
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
41
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
42 UPDATE metadata SET value='2' WHERE key='version';