Mercurial > libervia-pubsub

annotate db/sat_pubsub_update_1_2.sql @ 374:40e5edd7ea11

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
storage: changed items order: items were so far ordered by item_id, i.e. by order of creation. This has been changed to order them by date of last update, so an item overwritting an old one appears on top (with former behaviour, it was staying at the same place). The XEP is not clear on that, but this seems more in correct with ยง 6.5.7 which says "implementations SHOULD return the N most recent (as opposed to the N oldest) items".
author Goffi <goffi@goffi.org>
date Thu, 03 Jan 2019 20:31:03 +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';