libervia-pubsub: db/sat_pubsub_update_5

annotate db/sat_pubsub_update_5_7.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	920440200570
children

rev	line source
433 920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	1 -- we check version of the database before doing anything
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	2 -- and stop execution if not good
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	3 \set ON_ERROR_STOP
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	4 DO $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	5 DECLARE ver text;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	6 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	7 SELECT value INTO ver FROM metadata WHERE key='version';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	8 IF NOT FOUND OR ver!='5' THEN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 5, you use version %',ver;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	10 END IF;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	11 END$$;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	12 \unset ON_ERROR_STOP
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	13 -- end of version check
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	14
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	15 /* NOT NULL constraint was not applied to items.data */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	16 ALTER TABLE items ALTER COLUMN data SET NOT NULL;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	17
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	18 /* Full Text Search */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	19 ALTER TABLE nodes ADD COLUMN fts_language text NOT NULL DEFAULT 'generic';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	20 ALTER TABLE items ADD COLUMN data_fts_cfg text NOT NULL DEFAULT 'simple';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	21 ALTER TABLE items ADD COLUMN data_fts tsvector;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	22 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	23
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	24 CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	25 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	26 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	27 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	28 WHERE items.node_id=new.node_id
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	29 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	30 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	31 END;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	32 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	33 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	34
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	35 CREATE TRIGGER nodes_fts_language_update
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	36 AFTER UPDATE OF fts_language ON nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	37 FOR EACH ROW
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	38 EXECUTE PROCEDURE update_data_fts_cfg();
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	39
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	40 CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	41 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	42 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	43 new.data_fts=to_tsvector(new.data_fts_cfg::regconfig, new.data::text);
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	44 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	45 END
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	46 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	47 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	48
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	49 CREATE TRIGGER items_fts_tsvector_update
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	50 BEFORE INSERT OR UPDATE OF data_fts_cfg,data ON items
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	51 FOR EACH ROW
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	52 EXECUTE PROCEDURE update_data_fts();
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	53
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	54 /* We do the update to trigger the data_fts generation */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	55 UPDATE items SET data_fts_cfg='simple';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	56
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	57 /* we update nodes with schema to prepare for XEP-0346 implementation */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	58
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	59 INSERT INTO nodes(node, pep, persist_items, publish_model, max_items)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	60 SELECT 'fdp/template/'\|\|s.node, s.pep, true, s.publish_model, 1
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	61 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	62 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	63 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	64 WHERE schema IS NOT NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	65 ) AS s;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	66
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	67 INSERT INTO affiliations(entity_id, node_id, affiliation)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	68 SELECT aff.entity_id, tpl.node_id, 'owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	69 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	70 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	71 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	72 WHERE schema IS NOT NULL AND pep IS NOT NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	73 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	74 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'\|\|s.node AND tpl.pep=s.pep
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	75 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	76
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	77 /* we need to do a similar request for non PEP nodes */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	78 INSERT INTO affiliations(entity_id, node_id, affiliation)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	79 SELECT aff.entity_id, tpl.node_id, 'owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	80 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	81 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	82 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	83 WHERE schema IS NOT NULL AND pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	84 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	85 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'\|\|s.node AND tpl.pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	86 LEFT JOIN affiliations AS aff ON aff.node_id=s.node_id AND aff.affiliation='owner';
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	87
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	88 INSERT INTO items(node_id, item, publisher, data)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	89 SELECT
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	90 tpl.node_id,
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	91 'current',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	92 e.jid\|\|'/generated',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	93 xmlelement(name item, xmlattributes('current' as id, e.jid\|\|'/generated' as publisher), s.schema)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	94 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	95 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	96 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	97 WHERE schema IS NOT NULL AND pep IS NOT NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	98 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	99 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'\|\|s.node AND tpl.pep=s.pep
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	100 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	101 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	102
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	103 /* once again for non PEP nodes */
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	104 INSERT INTO items(node_id, item, publisher, data)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	105 SELECT
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	106 tpl.node_id,
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	107 'current',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	108 e.jid\|\|'/generated',
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	109 xmlelement(name item, xmlattributes('current' as id, e.jid\|\|'/generated' as publisher), s.schema)
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	110 FROM (
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	111 SELECT node_id, node, pep, publish_model, schema
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	112 FROM nodes
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	113 WHERE schema IS NOT NULL AND pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	114 ) AS s
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	115 LEFT JOIN nodes AS tpl ON tpl.node='fdp/template/'\|\|s.node AND tpl.pep IS NULL
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	116 LEFT JOIN affiliations AS aff ON aff.node_id = tpl.node_id AND aff.affiliation='owner'
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	117 LEFT JOIN entities AS e ON e.entity_id = aff.entity_id;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	118
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	119 UPDATE nodes SET node='fdp/submitted/'\|\|node WHERE schema IS NOT NULL;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	120
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore Goffi <goffi@goffi.org> parents: diff changeset	121 UPDATE metadata SET value='7' WHERE key='version';

Mercurial > libervia-pubsub

annotate db/sat_pubsub_update_5_7.sql @ 451:0e6e176cb572