Mercurial > libervia-pubsub

annotate db/sat_pubsub_update_2_3.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:
diff changeset
1 -- we check version of the database before doing anything
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
2 -- and stop execution if not good
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
3 \set ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
4 DO $$
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
5 DECLARE ver text;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
6 BEGIN
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
7 SELECT value INTO ver FROM metadata WHERE key='version';
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
8 IF NOT FOUND OR ver!='2' THEN
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
9 RAISE EXCEPTION 'This update file need to be applied on database schema version 2, you use version %',ver;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
10 END IF;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
11 END$$;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
12 \unset ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
13 -- end of version check
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
14
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
15 /* we add "presence" access model */
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
16 ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
17 ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher'));
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
18
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
19 /* and schema column */
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
20 ALTER TABLE nodes ADD COLUMN schema xml;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
21
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
22 /* we want xml types for items data too */
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
23 ALTER TABLE items ALTER data TYPE xml using data::xml;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
24
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents:
diff changeset
25 UPDATE metadata SET value='3' WHERE key='version';