Mercurial > libervia-pubsub

annotate db/libervia_pubsub_update_3_4.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 9af0ef2c145c
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
367
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
1 -- we check version of the database before doing anything
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
2 -- and stop execution if not good
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
3 \set ON_ERROR_STOP
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
4 DO $$
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
5 DECLARE ver text;
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
6 BEGIN
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
7 SELECT value INTO ver FROM metadata WHERE key='version';
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
8 IF NOT FOUND OR ver!='3' THEN
403
1dc606612405 implemented experimental "consistent_publisher" option:
Goffi <goffi@goffi.org>
parents: 367
diff changeset
9 RAISE EXCEPTION 'This update file needs to be applied on database schema version 3, you use version %',ver;
367
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
10 END IF;
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
11 END$$;
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
12 \unset ON_ERROR_STOP
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
13 -- end of version check
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
14
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
15 /* new "serial ids" option */
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
16 ALTER TABLE nodes ADD COLUMN serial_ids boolean NOT NULL DEFAULT FALSE;
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
17
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
18 /* we want to keep creation and update times */
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
19 ALTER TABLE items RENAME COLUMN date TO created;
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
20 ALTER TABLE items ADD COLUMN updated timestamp with time zone NOT NULL DEFAULT now();
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
21
a772f7dac930 backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff changeset
22 UPDATE metadata SET value='4' WHERE key='version';