annotate db/sat_pubsub_update_2_3.sql @ 367:a772f7dac930

backend, storage(pgsql): creation/update date + serial ids: /!\ this patch updates pgqsl schema /!\ Had to set 2 features in the same patch, to avoid updating 2 times the schema. 1) creation/last modification date: column keeping the date of creation of items is renamed from "date" to "created" the date of last modification of items is saved in the new "updated" column 2) serial ids: this experimental feature allows to have ids in series (i.e. 1, 2, 3, etc.) instead of UUID. This is a convenience feature and there are some drawbacks: - PostgreSQL sequences are used, so gaps can happen (see PostgreSQL documentation for more details) - if somebody create an item with a future id in the series, the series will adapt, which can have undesired effect, and may lead to item fail if several items are created at the same time. For instance if next id in series is "8", and somebody hads already created item "8" and "256", the item will be created with biggest value in items +1 (i.e. 257). if 2 people want to create item in this situation, the second will fail with a conflict error.
author Goffi <goffi@goffi.org>
date Sat, 04 Nov 2017 21:31:32 +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';