Mercurial > libervia-pubsub

comparison db/pubsub.sql @ 430:5a0ada3b61ca

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Full-Text Search implementation: /!\ pgsql schema needs to be updated /!\ /!\ Minimal PostgreSQL required version is now 12 /!\ A new options is available to specify main language of a node. By default a `generic` language is used (which uses the `simple` configuration in PostgreSQL). When a node owner changes the language, the index is rebuilt accordingly. It is possible to have item specific language for multilingual nodes (but for the moment the search is done with node language, so the results won't be good). If an item language is explicitely set in `item_languages`, the FTS configuration won't be affected by node FTS language setting. Search is parsed with `websearch_to_tsquery` for now, but this parser doesn't handle prefix matching, so it may be replaced in the future. SetConfiguration now only updates the modified values, this avoid triggering the FTS re-indexing on each config change. `_checkNodeExists` is not called anymore as we can check if a row has been modified to see if the node exists, this avoid a useless query. Item storing has been slighly improved with a useless SELECT and condition removed. To avoid 2 schema updates in a row, the `sat_pubsub_update_5_6.sql` file also prepares the implementation of XEP-0346 by updating nodes with a schema and creating the suitable template nodes.
author Goffi <goffi@goffi.org>
date Fri, 11 Dec 2020 17:18:52 +0100
parents 1dc606612405
children 920440200570
comparison
equal deleted inserted replaced
429:0526073ff2ab 430:5a0ada3b61ca
4 ); 4 );
5 5
6 CREATE TABLE nodes ( 6 CREATE TABLE nodes (
7 node_id serial PRIMARY KEY, 7 node_id serial PRIMARY KEY,
8 node text NOT NULL, 8 node text NOT NULL,
9 pep text, 9 pep text,
10 node_type text NOT NULL DEFAULT 'leaf' 10 node_type text NOT NULL DEFAULT 'leaf'
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 max_items integer NOT NULL DEFAULT 0 16 max_items integer NOT NULL DEFAULT 0
17 CHECK (max_items >= 0), 17 CHECK (max_items >= 0),
18 serial_ids boolean NOT NULL DEFAULT FALSE, 18 serial_ids boolean NOT NULL DEFAULT FALSE,
19 consistent_publisher boolean NOT NULL DEFAULT FALSE, 19 consistent_publisher boolean NOT NULL DEFAULT FALSE,
20 fts_language text NOT NULL DEFAULT 'generic',
20 send_last_published_item text NOT NULL DEFAULT 'on_sub' 21 send_last_published_item text NOT NULL DEFAULT 'on_sub'
21 CHECK (send_last_published_item IN ('never', 'on_sub')), 22 CHECK (send_last_published_item IN ('never', 'on_sub')),
22 publish_model text NOT NULL DEFAULT 'publishers' 23 publish_model text NOT NULL DEFAULT 'publishers'
23 CHECK (publish_model IN ('publishers', 'subscribers', 'open')), 24 CHECK (publish_model IN ('publishers', 'subscribers', 'open')),
24 schema xml 25 schema xml
50 subscription_id serial PRIMARY KEY, 51 subscription_id serial PRIMARY KEY,
51 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE, 52 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
52 resource text, 53 resource text,
53 node_id integer NOT NULL REFERENCES nodes ON delete CASCADE, 54 node_id integer NOT NULL REFERENCES nodes ON delete CASCADE,
54 state text NOT NULL DEFAULT 'subscribed' 55 state text NOT NULL DEFAULT 'subscribed'
55 CHECK (state IN ('subscribed', 'pending', 'unconfigured')), 56 CHECK (state IN ('subscribed', 'pending', 'unconfigured')),
56 subscription_type text 57 subscription_type text
57 CHECK (subscription_type IN (NULL, 'items', 'nodes')), 58 CHECK (subscription_type IN (NULL, 'items', 'nodes')),
58 subscription_depth text 59 subscription_depth text
59 CHECK (subscription_depth IN (NULL, '1', 'all')), 60 CHECK (subscription_depth IN (NULL, '1', 'all')),
60 UNIQUE (entity_id, resource, node_id)); 61 UNIQUE (entity_id, resource, node_id));
61 62
62 CREATE TABLE items ( 63 CREATE TABLE items (
63 item_id serial PRIMARY KEY, 64 item_id serial PRIMARY KEY,
64 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE, 65 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE,
65 item text NOT NULL, 66 item text NOT NULL,
66 publisher text NOT NULL, 67 publisher text NOT NULL,
67 data xml, 68 data xml NOT NULL,
69 data_fts tsvector GENERATED ALWAYS AS (to_tsvector(data_fts_cfg, data::text)) STORED,
70 data_fts_cfg regconfig NOT NULL DEFAULT 'simple',
68 access_model text NOT NULL DEFAULT 'open' 71 access_model text NOT NULL DEFAULT 'open'
69 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')), 72 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')),
70 created timestamp with time zone NOT NULL DEFAULT now(), 73 created timestamp with time zone NOT NULL DEFAULT now(),
71 updated timestamp with time zone NOT NULL DEFAULT now(), 74 updated timestamp with time zone NOT NULL DEFAULT now(),
72 UNIQUE (node_id, item) 75 UNIQUE (node_id, item)
98 item_id integer NOT NULL references items ON DELETE CASCADE, 101 item_id integer NOT NULL references items ON DELETE CASCADE,
99 category text NOT NULL, 102 category text NOT NULL,
100 UNIQUE (item_id,category) 103 UNIQUE (item_id,category)
101 ); 104 );
102 105
106 /* Full Text Search */
107 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
108
109 CREATE OR REPLACE FUNCTION update_data_fts() RETURNS TRIGGER AS
110 $$
111 BEGIN
112 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')::regconfig
113 WHERE items.node_id=new.node_id
114 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
115 RETURN new;
116 END;
117 $$
118 language plpgsql;
119
120 CREATE TRIGGER nodes_fts_language_update
121 AFTER UPDATE OF fts_language ON nodes
122 FOR EACH ROW
123 EXECUTE PROCEDURE update_data_fts();
124
103 CREATE TABLE metadata ( 125 CREATE TABLE metadata (
104 key text PRIMARY KEY, 126 key text PRIMARY KEY,
105 value text 127 value text
106 ); 128 );
107 129
108 INSERT INTO metadata VALUES ('version', '5'); 130 INSERT INTO metadata VALUES ('version', '5');