Mercurial > libervia-pubsub
comparison db/pubsub.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 | 6350622d9eb9 |
children | 1dc606612405 |
comparison
equal
deleted
inserted
replaced
366:81e6d4a516c3 | 367:a772f7dac930 |
---|---|
11 CHECK (node_type IN ('leaf', 'collection')), | 11 CHECK (node_type IN ('leaf', 'collection')), |
12 access_model text NOT NULL DEFAULT 'open' | 12 access_model text NOT NULL DEFAULT 'open' |
13 CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')), | 13 CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')), |
14 persist_items boolean, | 14 persist_items boolean, |
15 deliver_payloads boolean NOT NULL DEFAULT TRUE, | 15 deliver_payloads boolean NOT NULL DEFAULT TRUE, |
16 serial_ids boolean NOT NULL DEFAULT FALSE, | |
16 send_last_published_item text NOT NULL DEFAULT 'on_sub' | 17 send_last_published_item text NOT NULL DEFAULT 'on_sub' |
17 CHECK (send_last_published_item IN ('never', 'on_sub')), | 18 CHECK (send_last_published_item IN ('never', 'on_sub')), |
18 publish_model text NOT NULL DEFAULT 'publishers' | 19 publish_model text NOT NULL DEFAULT 'publishers' |
19 CHECK (publish_model IN ('publishers', 'subscribers', 'open')), | 20 CHECK (publish_model IN ('publishers', 'subscribers', 'open')), |
20 schema xml | 21 schema xml |
61 item text NOT NULL, | 62 item text NOT NULL, |
62 publisher text NOT NULL, | 63 publisher text NOT NULL, |
63 data xml, | 64 data xml, |
64 access_model text NOT NULL DEFAULT 'open' | 65 access_model text NOT NULL DEFAULT 'open' |
65 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')), | 66 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')), |
66 date timestamp with time zone NOT NULL DEFAULT now(), | 67 created timestamp with time zone NOT NULL DEFAULT now(), |
68 updated timestamp with time zone NOT NULL DEFAULT now(), | |
67 UNIQUE (node_id, item) | 69 UNIQUE (node_id, item) |
68 ); | 70 ); |
69 | 71 |
70 CREATE TABLE item_groups_authorized ( | 72 CREATE TABLE item_groups_authorized ( |
71 item_groups_authorized_id serial PRIMARY KEY, | 73 item_groups_authorized_id serial PRIMARY KEY, |
98 CREATE TABLE metadata ( | 100 CREATE TABLE metadata ( |
99 key text PRIMARY KEY, | 101 key text PRIMARY KEY, |
100 value text | 102 value text |
101 ); | 103 ); |
102 | 104 |
103 INSERT INTO metadata VALUES ('version', '3'); | 105 INSERT INTO metadata VALUES ('version', '4'); |