Mercurial > gemma

annotate schema/updates/1406/01.distinguish_bcnlat_hydro_ienc.sql @ 5267:aca4bf7af270

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: remove mapState from import statement
author Fadi Abbud <fadi.abbud@intevation.de>
date Wed, 10 Jun 2020 16:33:10 +0200
parents 1e47ba2a58f2
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4962
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE TABLE waterway.fairway_marks_bcnlat_new (
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 colour varchar,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 colpat varchar,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 condtn int,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 bcnshp int,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 catlam int
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 ) INHERITS (waterway.fairway_marks);
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 CREATE TABLE waterway.fairway_marks_bcnlat_hydro (
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 ) INHERITS (waterway.fairway_marks_bcnlat_new);
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows
4963
1e47ba2a58f2 Fix database migration
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
13 ON waterway.fairway_marks_bcnlat_hydro
4962
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 ((CAST((validity, last_found, geom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 scamin, picrep, txtdsc, sordat, sorind,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 colour, colpat, condtn, bcnshp, catlam, 0
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 ) AS waterway.fairway_marks_bcnlat_hydro)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 ));
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 CREATE TABLE waterway.fairway_marks_bcnlat_ienc (
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 ) INHERITS (waterway.fairway_marks_bcnlat_new);
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows
4963
1e47ba2a58f2 Fix database migration
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
25 ON waterway.fairway_marks_bcnlat_ienc
4962
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 ((CAST((validity, last_found, geom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 scamin, picrep, txtdsc, sordat, sorind,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 colour, colpat, condtn, bcnshp, catlam, 0
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 ) AS waterway.fairway_marks_bcnlat_ienc)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 ));
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 -- Assume all features are IENC features, since there is currently no known
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 -- data source for HYDRO features
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 INSERT INTO waterway.fairway_marks_bcnlat_ienc
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 SELECT validity, last_found, geom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 scamin, picrep, txtdsc, sordat, sorind,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 colour, colpat, condtn, bcnshp, catlam, id
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 FROM waterway.fairway_marks_bcnlat;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 SELECT setval(
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 pg_get_serial_sequence('waterway.fairway_marks_bcnlat_ienc', 'id'),
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 max(id))
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 FROM waterway.fairway_marks_bcnlat_ienc;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 -- Let foreign key constraint point to new table
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 ALTER TABLE waterway.fairway_marks_bcnlat_dirimps
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 DROP CONSTRAINT fairway_marks_bcnlat_dirimps_fm_bcnlat_id_fkey,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 ADD FOREIGN KEY (fm_bcnlat_id)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 REFERENCES waterway.fairway_marks_bcnlat_ienc;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 -- Finally
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 DROP TABLE waterway.fairway_marks_bcnlat;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 ALTER TABLE waterway.fairway_marks_bcnlat_new RENAME TO fairway_marks_bcnlat;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 GRANT SELECT on ALL tables in schema waterway TO waterway_user;
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 TO waterway_admin;