annotate db/pubsub.sql @ 430:5a0ada3b61ca

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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
1 CREATE TABLE entities (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
2 entity_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
3 jid text NOT NULL UNIQUE
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
4 );
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
5
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
6 CREATE TABLE nodes (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
7 node_id serial PRIMARY KEY,
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
8 node text NOT NULL,
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
9 pep text,
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
10 node_type text NOT NULL DEFAULT 'leaf'
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
11 CHECK (node_type IN ('leaf', 'collection')),
240
70c8bb90d75f added access_model to config, default to 'open'
Goffi <goffi@goffi.org>
parents: 206
diff changeset
12 access_model text NOT NULL DEFAULT 'open'
350
4d4575911060 sql (PostgreSQL): schema update:
Goffi <goffi@goffi.org>
parents: 329
diff changeset
13 CHECK (access_model IN ('open', 'presence', 'publisher-roster', 'whitelist', 'publish-only', 'self-publisher')),
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
14 persist_items boolean,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
15 deliver_payloads boolean NOT NULL DEFAULT TRUE,
403
1dc606612405 implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents: 367
diff changeset
16 max_items integer NOT NULL DEFAULT 0
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
17 CHECK (max_items >= 0),
367
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents: 360
diff changeset
18 serial_ids boolean NOT NULL DEFAULT FALSE,
403
1dc606612405 implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents: 367
diff changeset
19 consistent_publisher boolean NOT NULL DEFAULT FALSE,
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
20 fts_language text NOT NULL DEFAULT 'generic',
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
21 send_last_published_item text NOT NULL DEFAULT 'on_sub'
260
f0cd02c032b3 publish model management
Goffi <goffi@goffi.org>
parents: 248
diff changeset
22 CHECK (send_last_published_item IN ('never', 'on_sub')),
f0cd02c032b3 publish model management
Goffi <goffi@goffi.org>
parents: 248
diff changeset
23 publish_model text NOT NULL DEFAULT 'publishers'
359
1d93f94feee3 psql (schema): fixed forgotten comma
Goffi <goffi@goffi.org>
parents: 350
diff changeset
24 CHECK (publish_model IN ('publishers', 'subscribers', 'open')),
360
6350622d9eb9 psql (schema): fixed bad semi-colon
Goffi <goffi@goffi.org>
parents: 359
diff changeset
25 schema xml
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
26 );
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
27
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
28 /* we need 2 partial indexes to manage NULL value for PEP */
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
29 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: 260
diff changeset
30 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: 260
diff changeset
31
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
32 INSERT INTO nodes (node, node_type) values ('', 'collection');
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
33
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
34 CREATE TABLE affiliations (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
35 affiliation_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
36 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
37 node_id integer NOT NULL references nodes ON DELETE CASCADE,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
38 affiliation text NOT NULL
329
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
39 CHECK (affiliation IN ('outcast', 'member', 'publisher', 'owner')),
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
40 UNIQUE (entity_id, node_id)
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
41 );
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
42
243
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
43 CREATE TABLE node_groups_authorized (
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
44 node_groups_authorized_id serial PRIMARY KEY,
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
45 node_id integer NOT NULL references nodes ON DELETE CASCADE,
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
46 groupname text NOT NULL,
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
47 UNIQUE (node_id,groupname)
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
48 );
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
49
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
50 CREATE TABLE subscriptions (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
51 subscription_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
52 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
53 resource text,
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
54 node_id integer NOT NULL REFERENCES nodes ON delete CASCADE,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
55 state text NOT NULL DEFAULT 'subscribed'
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
56 CHECK (state IN ('subscribed', 'pending', 'unconfigured')),
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
57 subscription_type text
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
58 CHECK (subscription_type IN (NULL, 'items', 'nodes')),
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
59 subscription_depth text
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
60 CHECK (subscription_depth IN (NULL, '1', 'all')),
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
61 UNIQUE (entity_id, resource, node_id));
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
62
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
63 CREATE TABLE items (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
64 item_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
65 node_id integer NOT NULL REFERENCES nodes ON DELETE CASCADE,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
66 item text NOT NULL,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
67 publisher text NOT NULL,
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
68 data xml NOT NULL,
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
69 data_fts tsvector GENERATED ALWAYS AS (to_tsvector(data_fts_cfg, data::text)) STORED,
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
70 data_fts_cfg regconfig NOT NULL DEFAULT 'simple',
248
50f6ee966da8 item are gotten according to item's access model in getItems
Goffi <goffi@goffi.org>
parents: 244
diff changeset
71 access_model text NOT NULL DEFAULT 'open'
329
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
72 CHECK (access_model IN ('open', 'publisher-roster', 'whitelist')),
367
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents: 360
diff changeset
73 created timestamp with time zone NOT NULL DEFAULT now(),
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents: 360
diff changeset
74 updated timestamp with time zone NOT NULL DEFAULT now(),
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
75 UNIQUE (node_id, item)
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
76 );
244
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
77
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
78 CREATE TABLE item_groups_authorized (
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
79 item_groups_authorized_id serial PRIMARY KEY,
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
80 item_id integer NOT NULL references items ON DELETE CASCADE,
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
81 groupname text NOT NULL,
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
82 UNIQUE (item_id,groupname)
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
83 );
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
84
329
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
85 CREATE TABLE item_jids_authorized (
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
86 item_jids_authorized_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
87 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
88 jid text NOT NULL,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
89 UNIQUE (item_id,jid)
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
90 );
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
91
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
92 CREATE TABLE item_languages (
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
93 item_languages_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
94 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
95 language text NOT NULL,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
96 UNIQUE (item_id,language)
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
97 );
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
98
301
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
99 CREATE TABLE item_categories (
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
100 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: 295
diff changeset
101 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: 295
diff changeset
102 category text NOT NULL,
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
103 UNIQUE (item_id,category)
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
104 );
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
105
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
106 /* Full Text Search */
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
107 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
108
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
109 CREATE OR REPLACE FUNCTION update_data_fts() RETURNS TRIGGER AS
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
110 $$
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
111 BEGIN
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
112 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')::regconfig
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
113 WHERE items.node_id=new.node_id
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
114 AND NOT EXISTS(SELECT FROM item_languages AS lang WHERE lang.item_id=items.item_id);
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
115 RETURN new;
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
116 END;
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
117 $$
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
118 language plpgsql;
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
119
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
120 CREATE TRIGGER nodes_fts_language_update
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
121 AFTER UPDATE OF fts_language ON nodes
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
122 FOR EACH ROW
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
123 EXECUTE PROCEDURE update_data_fts();
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
124
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
125 CREATE TABLE metadata (
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
126 key text PRIMARY KEY,
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
127 value text
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
128 );
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
129
403
1dc606612405 implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents: 367
diff changeset
130 INSERT INTO metadata VALUES ('version', '5');