Mercurial > libervia-pubsub

annotate db/pubsub.sql @ 489:fa0d2a4783fa default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
install: use PEP-440 compatible version in `setup`: setuptools from version v66 doesn't accept anymore non PEP-440 compatible version. This is a Q&D fix, move to `pyproject.toml` with proper versioning as for Libervia backend should be done sooner than later.
author Goffi <goffi@goffi.org>
date Fri, 12 Jan 2024 23:46:24 +0100
parents cfa40fa108a4
children
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),
478
b544109ab4c4 Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents: 466
diff changeset
18 overwrite_policy text NOT NULL DEFAULT 'original_publisher'
b544109ab4c4 Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents: 466
diff changeset
19 CHECK (overwrite_policy IN ('original_publisher', 'any_publisher')),
367
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents: 360
diff changeset
20 serial_ids boolean NOT NULL DEFAULT FALSE,
403
1dc606612405 implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents: 367
diff changeset
21 consistent_publisher boolean NOT NULL DEFAULT FALSE,
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
22 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
23 send_last_published_item text NOT NULL DEFAULT 'on_sub'
260
f0cd02c032b3 publish model management
Goffi <goffi@goffi.org>
parents: 248
diff changeset
24 CHECK (send_last_published_item IN ('never', 'on_sub')),
f0cd02c032b3 publish model management
Goffi <goffi@goffi.org>
parents: 248
diff changeset
25 publish_model text NOT NULL DEFAULT 'publishers'
359
1d93f94feee3 psql (schema): fixed forgotten comma
Goffi <goffi@goffi.org>
parents: 350
diff changeset
26 CHECK (publish_model IN ('publishers', 'subscribers', 'open')),
360
6350622d9eb9 psql (schema): fixed bad semi-colon
Goffi <goffi@goffi.org>
parents: 359
diff changeset
27 schema xml
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
28 );
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
29
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
30 /* 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
31 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
32 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
33
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
34 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
35
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
36 CREATE TABLE affiliations (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
37 affiliation_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
38 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
39 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
40 affiliation text NOT NULL
329
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
41 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
42 UNIQUE (entity_id, node_id)
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
43 );
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
44
243
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
45 CREATE TABLE node_groups_authorized (
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
46 node_groups_authorized_id serial PRIMARY KEY,
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
47 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
48 groupname text NOT NULL,
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
49 UNIQUE (node_id,groupname)
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
50 );
42048e37699e added experimental roster access_model (use remote_roster)
Goffi <goffi@goffi.org>
parents: 240
diff changeset
51
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
52 CREATE TABLE subscriptions (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
53 subscription_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
54 entity_id integer NOT NULL REFERENCES entities ON DELETE CASCADE,
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
55 resource text,
478
b544109ab4c4 Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents: 466
diff changeset
56 node_id integer REFERENCES nodes ON DELETE CASCADE,
b544109ab4c4 Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents: 466
diff changeset
57 /* when we reference an external node (with PAM), node_id is NULL and service and node
b544109ab4c4 Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents: 466
diff changeset
58 * are set */
b544109ab4c4 Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents: 466
diff changeset
59 ext_service text,
b544109ab4c4 Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents: 466
diff changeset
60 ext_node text,
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
61 state text NOT NULL DEFAULT 'subscribed'
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
62 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
63 subscription_type text
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
64 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
65 subscription_depth text
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
66 CHECK (subscription_depth IN (NULL, '1', 'all')),
478
b544109ab4c4 Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents: 466
diff changeset
67 public boolean NOT NULL DEFAULT FALSE,
479
cfa40fa108a4 db: fix pubsub.sql syntax
Goffi <goffi@goffi.org>
parents: 478
diff changeset
68 UNIQUE (entity_id, resource, node_id),
cfa40fa108a4 db: fix pubsub.sql syntax
Goffi <goffi@goffi.org>
parents: 478
diff changeset
69 UNIQUE (entity_id, ext_service, ext_node)
cfa40fa108a4 db: fix pubsub.sql syntax
Goffi <goffi@goffi.org>
parents: 478
diff changeset
70 );
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
71
206
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
72 CREATE TABLE items (
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
73 item_id serial PRIMARY KEY,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
74 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
75 item text NOT NULL,
274a45d2a5ab Implement root collection that includes all leaf nodes.
Ralph Meijer <ralphm@ik.nu>
parents: 188
diff changeset
76 publisher text NOT NULL,
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
77 data xml NOT NULL,
433
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
78 data_fts tsvector,
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
79 data_fts_cfg text 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
80 access_model text NOT NULL DEFAULT 'open'
329
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
81 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
82 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
83 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
84 UNIQUE (node_id, item)
87
f766e46dce0f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
85 );
244
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
86
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
87 CREATE TABLE item_groups_authorized (
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
88 item_groups_authorized_id serial PRIMARY KEY,
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
89 item_id integer NOT NULL references items ON DELETE CASCADE,
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
90 groupname text NOT NULL,
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
91 UNIQUE (item_id,groupname)
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
92 );
3ecc94407e36 item access_model (not finished)
Goffi <goffi@goffi.org>
parents: 243
diff changeset
93
329
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
94 CREATE TABLE item_jids_authorized (
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
95 item_jids_authorized_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
96 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
97 jid text NOT NULL,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
98 UNIQUE (item_id,jid)
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
99 );
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
100
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
101 CREATE TABLE item_languages (
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
102 item_languages_id serial PRIMARY KEY,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
103 item_id integer NOT NULL references items ON DELETE CASCADE,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
104 language text NOT NULL,
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
105 UNIQUE (item_id,language)
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
106 );
98409ef42c94 PostGreSQL: schema update:
Goffi <goffi@goffi.org>
parents: 301
diff changeset
107
301
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
108 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
109 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
110 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
111 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
112 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
113 );
05c875a13a62 categories are now stored in a dedicated table if item contain an atom entry:
Goffi <goffi@goffi.org>
parents: 295
diff changeset
114
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
115 /* Full Text Search */
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
116 CREATE INDEX items_data_fts ON items USING GIN (data_fts);
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
117
433
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
118 CREATE OR REPLACE FUNCTION update_data_fts_cfg() RETURNS TRIGGER AS
430
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 BEGIN
433
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
121 UPDATE items SET data_fts_cfg=replace(new.fts_language, 'generic', 'simple')
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
122 WHERE items.node_id=new.node_id
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
123 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
124 RETURN new;
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
125 END;
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
126 $$
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
127 language plpgsql;
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
128
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
129 CREATE TRIGGER nodes_fts_language_update
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
130 AFTER UPDATE OF fts_language ON nodes
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
131 FOR EACH ROW
433
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
132 EXECUTE PROCEDURE update_data_fts_cfg();
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
133
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
134 CREATE FUNCTION update_data_fts() RETURNS TRIGGER AS
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
135 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
136 BEGIN
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
137 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: 430
diff changeset
138 RETURN new;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
139 END
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
140 $$
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
141 language plpgsql;
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
142
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
143 CREATE TRIGGER items_fts_tsvector_update
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
144 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: 430
diff changeset
145 FOR EACH ROW
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
146 EXECUTE PROCEDURE update_data_fts();
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
147
466
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
148 /* Roster */
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
149
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
150 CREATE TABLE roster (
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
151 roster_id serial PRIMARY KEY,
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
152 jid text NOT NULL UNIQUE,
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
153 version text,
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
154 updated timestamp with time zone NOT NULL,
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
155 roster xml NOT NULL
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
156 );
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents: 448
diff changeset
157
433
920440200570 PostgreSQL: don't use `regconfig` and `GENERATED` column anymore
Goffi <goffi@goffi.org>
parents: 430
diff changeset
158
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
159 CREATE TABLE metadata (
430
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
160 key text PRIMARY KEY,
5a0ada3b61ca Full-Text Search implementation:
Goffi <goffi@goffi.org>
parents: 403
diff changeset
161 value text
294
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
162 );
df1edebb0466 PEP implementation, draft (huge patch sorry):
Goffi <goffi@goffi.org>
parents: 260
diff changeset
163
478
b544109ab4c4 Privileged Entity update + Pubsub Account Management partial implementation + Public Pubsub Subscription
Goffi <goffi@goffi.org>
parents: 466
diff changeset
164 INSERT INTO metadata VALUES ('version', '10');