Mercurial > gemma
annotate schema/updates/1405/01.add_fm_validity.sql @ 5082:c4ebb6ccc588 time-sliding
client: start a request for the last changed time on time slider
* initiate a refresh layers request when the time for the
finished request differs from the selected time on time slider
author | Fadi Abbud <fadi.abbud@intevation.de> |
---|---|
date | Wed, 18 Mar 2020 14:11:44 +0100 |
parents | 7cc79c65a9e5 |
children |
rev | line source |
---|---|
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 CREATE TABLE waterway.fairway_marks_new ( |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 CHECK (NOT isempty(validity)), |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 last_found timestamp with time zone NOT NULL DEFAULT current_timestamp, |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 LIKE waterway.fairway_marks |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 ); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 DO LANGUAGE plpgsql |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 $$ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 DECLARE table_suffix varchar; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 BEGIN |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 /* Re-write tables inheriting from fairway_marks to include new columns |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 in correct position */ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 FOREACH table_suffix IN ARRAY ARRAY[ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 'bcnlat', 'boycar', 'boylat', 'boysaw', 'boyspp', |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 'daymar', 'lights', 'rtpbcn', 'topmar', 'notmrk'] |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 LOOP |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 EXECUTE format('CREATE TABLE waterway.fairway_marks_%s_new ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 '(LIKE waterway.fairway_marks_%1$s INCLUDING ALL) ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 'INHERITS (waterway.fairway_marks_new)', table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 /* Drop index referring to wrong columns. See |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 https://www.postgresql.org/message-id/flat/16272-6e32da020e9a9381%40postgresql.org |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 */ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 EXECUTE format('DROP INDEX waterway.fairway_marks_%s_new_row_idx', |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 EXECUTE format('INSERT INTO waterway.fairway_marks_%s_new ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 'SELECT tstzrange(current_timestamp, NULL), current_timestamp, * ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 'FROM waterway.fairway_marks_%1$s', table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 EXECUTE format('DROP TABLE waterway.fairway_marks_%s CASCADE', |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 EXECUTE format('ALTER TABLE waterway.fairway_marks_%s_new ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 'RENAME TO fairway_marks_%1$s', table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 END LOOP; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 /* Recreate foreign key constraints */ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 FOREACH table_suffix IN ARRAY ARRAY['bcnlat', 'daymar', 'notmrk'] |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 LOOP |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 EXECUTE format('ALTER TABLE waterway.fairway_marks_%1$s_dirimps ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 'ADD FOREIGN KEY (fm_%1$s_id) ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 'REFERENCES waterway.fairway_marks_%1$s', table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 END LOOP; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 END; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 $$; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 DROP TABLE waterway.fairway_marks; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 ALTER TABLE waterway.fairway_marks_new RENAME TO fairway_marks; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 -- Restore GRANTs |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 GRANT SELECT on ALL tables in schema waterway TO waterway_user ; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 TO waterway_admin; |