Mercurial > libervia-pubsub
annotate db/sat_pubsub_update_3_4.sql @ 367:a772f7dac930
backend, storage(pgsql): creation/update date + serial ids:
/!\ this patch updates pgqsl schema /!\
Had to set 2 features in the same patch, to avoid updating 2 times the schema.
1) creation/last modification date:
column keeping the date of creation of items is renamed from "date" to "created"
the date of last modification of items is saved in the new "updated" column
2) serial ids:
this experimental feature allows to have ids in series (i.e. 1, 2, 3, etc.) instead of UUID.
This is a convenience feature and there are some drawbacks:
- PostgreSQL sequences are used, so gaps can happen (see PostgreSQL documentation for more details)
- if somebody create an item with a future id in the series, the series will adapt, which can have undesired effect, and may lead to item fail if several items are created at the same time. For instance if next id in series is "8", and somebody hads already created item "8" and "256", the item will be created with biggest value in items +1 (i.e. 257). if 2 people want to create item in this situation, the second will fail with a conflict error.
author | Goffi <goffi@goffi.org> |
---|---|
date | Sat, 04 Nov 2017 21:31:32 +0100 |
parents | |
children | 1dc606612405 |
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 |
a772f7dac930
backend, storage(pgsql): creation/update date + serial ids:
Goffi <goffi@goffi.org>
parents:
diff
changeset
|
9 RAISE EXCEPTION 'This update file need to be applied on database schema version 3, you use version %',ver; |
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'; |