Mercurial > libervia-pubsub

annotate db/libervia_pubsub_update_8_9.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 0d38c3529972
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
466
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
1 -- we check version of the database before doing anything
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
2 -- and stop execution if not good
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
3 \set ON_ERROR_STOP
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
4 DO $$
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
5 DECLARE ver text;
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
6 BEGIN
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
7 SELECT value INTO ver FROM metadata WHERE key='version';
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
8 IF NOT FOUND OR ver!='8' THEN
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 8, you use version %',ver;
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
10 END IF;
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
11 END$$;
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
12 \unset ON_ERROR_STOP
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
13 -- end of version check
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
14
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
15 /* new "roster" table */
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
16 CREATE TABLE roster (
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
17 roster_id serial PRIMARY KEY,
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
18 jid text NOT NULL UNIQUE,
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
19 version text,
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
20 updated timestamp with time zone NOT NULL,
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
21 roster xml NOT NULL
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
22 );
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
23
0d38c3529972 psql: schema update (9) to add `roster` table
Goffi <goffi@goffi.org>
parents:
diff changeset
24 UPDATE metadata SET value='9' WHERE key='version';