annotate db/sat_pubsub_update_1_2.sql @ 354:18b983fe9e1b

backend: added "presence" access_model: - move presence checking method in separated method so they can be use for get and subscriptions - added VAL_AMODEL_PRESENCE constant
author Goffi <goffi@goffi.org>
date Fri, 08 Sep 2017 08:02:05 +0200
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';