Mercurial > libervia-pubsub

annotate db/sat_pubsub_update_0_1.sql @ 451:0e6e176cb572

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
350
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
1 -- we check version of the database before doing anything
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
2 -- and stop execution if not good
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
3 \set ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
4 DO $$
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
5 DECLARE ver text;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
6 BEGIN
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
7 SELECT value INTO ver FROM metadata WHERE key='version';
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
8 IF FOUND 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 older database, you don''t have to use it';
350
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
10 END IF;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
11 END$$;
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
12 \unset ON_ERROR_STOP
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
13 -- end of version check
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
14
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
15
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
16 ALTER TABLE nodes ADD COLUMN pep text;
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
17
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
18 ALTER TABLE nodes DROP CONSTRAINT nodes_node_key;
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
19 /* we need 2 partial indexes to manage NULL value for PEP */
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
20 CREATE UNIQUE INDEX nodes_node_pep_key_not_null ON nodes(node, pep) WHERE pep IS NOT NULL;
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
21 CREATE UNIQUE INDEX nodes_node_pep_key_null ON nodes(node) WHERE pep IS NULL;
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
22
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
23 CREATE TABLE metadata (
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
24 key text PRIMARY KEY,
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
25 value text
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
26 );
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
27
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
28 INSERT INTO metadata VALUES ('version', '1');
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
29
301
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 294
diff changeset
30 CREATE TABLE item_categories (
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 294
diff changeset
31 item_categories_id serial PRIMARY KEY,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 294
diff changeset
32 item_id integer NOT NULL references items ON DELETE CASCADE,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 294
diff changeset
33 category text NOT NULL,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 294
diff changeset
34 UNIQUE (item_id,category)
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 294
diff changeset
35 );
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 294
diff changeset
36
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
37 UPDATE nodes SET node='urn:xmpp:microblog:0', pep=substring(node from 20) WHERE node LIKE 'urn:xmpp:groupblog:_%';
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
38
350
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 303
diff changeset
39 /* This is to update namespaces, SàT was buggy before 0.6 and didn't set the atom namespace in <entry/> */
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
40 /* But yeah, this is a crazy query */
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
41 UPDATE items SET data = xmlelement(name item, xmlattributes((xpath('/item/@id', data::xml))[1] as id),
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
42 XMLPARSE(CONTENT NULLIF(array_to_string(xpath('/item/entry/preceding-sibling::*', data::xml)::text[],''),'')),
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
43 xmlelement(name entry, xmlattributes('http://www.w3.org/2005/Atom' as xmlns), array_to_string(xpath('/item/entry/*', data::xml)::text[], '')::xml),
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
44 XMLPARSE(CONTENT NULLIF(array_to_string(xpath('/item/entry/following-sibling::*', data::xml)::text[],''),'')))
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
45 FROM nodes WHERE nodes.node_id = items.node_id
303
3c2705199108 sat_pubsub_update_0_1.sql now also updates comment nodes
Goffi <goffi@goffi.org>
parents: 301
diff changeset
46 AND (node = 'urn:xmpp:microblog:0' or node LIKE 'urn:xmpp:comments:%')
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents:
diff changeset
47 AND XMLEXISTS('/item/entry' PASSING (data::xml));