Mercurial > libervia-pubsub
annotate db/sat_pubsub_update_1_2.sql @ 451:0e6e176cb572
pgsql: fix items order:
Due to a misinterpretation, items where returned in wrong order when RSM was used.
Pubsub vanilla `max_items` is reversing the items order to get last items, but when this
attribute is not used (RSM being used or not), the items must be in chronological order.
This patch fixes it, so RSM returns oldest items by defaut, and empty `<before/>` must be
used to get last page (and thus most recent items).
Items are always finally ordered chronologically inside the returned page (default by
`update` time, but this can be modified when a specific order is requested).
Reference field is also fixed to use `updated` instead of `item_id` when item are ordered
by `update`.
| author | Goffi <goffi@goffi.org> |
|---|---|
| date | Sun, 06 Jun 2021 19:42:57 +0200 |
| parents | 1dc606612405 |
| children |
| rev | line source |
|---|---|
| 350 | 1 -- we check version of the database before doing anything |
| 2 -- and stop execution if not good | |
| 3 \set ON_ERROR_STOP | |
| 4 DO $$ | |
| 5 DECLARE ver smallint; | |
| 6 BEGIN | |
| 7 SELECT value INTO ver FROM metadata WHERE key='version'; | |
| 8 IF NOT FOUND OR ver!='1' THEN | |
|
403
1dc606612405
implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents:
350
diff
changeset
|
9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 1, you use version %',ver; |
| 350 | 10 END IF; |
| 11 END$$; | |
| 12 \unset ON_ERROR_STOP | |
| 13 -- end of version check | |
| 14 | |
| 329 | 15 /* roster access model was badly used, we rename it to publisher-roster */ |
| 16 | |
| 17 ALTER TABLE nodes DROP CONSTRAINT nodes_access_model_check; | |
| 18 UPDATE nodes SET access_model = 'publisher-roster' WHERE access_model = 'roster'; | |
| 19 ALTER TABLE nodes ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')); | |
| 20 | |
| 21 ALTER TABLE items DROP CONSTRAINT items_access_model_check; | |
| 22 UPDATE items SET access_model = 'publisher-roster' WHERE access_model = 'roster'; | |
| 23 ALTER TABLE items ADD CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')); | |
| 24 | |
| 25 ALTER TABLE affiliations DROP CONSTRAINT affiliations_affiliation_check; | |
| 26 ALTER TABLE affiliations ADD CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')); | |
| 27 | |
| 28 CREATE TABLE item_jids_authorized ( | |
| 29 item_jids_authorized_id serial PRIMARY KEY, | |
| 30 item_id integer NOT NULL references items ON DELETE CASCADE, | |
| 31 jid text NOT NULL, | |
| 32 UNIQUE (item_id,jid) | |
| 33 ); | |
| 34 | |
| 35 CREATE TABLE item_languages ( | |
| 36 item_languages_id serial PRIMARY KEY, | |
| 37 item_id integer NOT NULL references items ON DELETE CASCADE, | |
| 38 language text NOT NULL, | |
| 39 UNIQUE (item_id,language) | |
| 40 ); | |
| 41 | |
| 42 UPDATE metadata SET value='2' WHERE key='version'; |