Mercurial > libervia-pubsub

annotate db/pubsub.sql @ 493:33222c54d2af

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
remove uses of twisted.internet.defer.returnValue This function has been deprecated in Twisted 24.7.0.
author Povilas Kanapickas <povilas@radix.lt>
date Wed, 11 Dec 2024 01:19:46 +0200
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');