Mercurial > gemma

annotate schema/updates/1400/01.add_fairway_marks.sql @ 5098:52aac557cbd7 queued-stage-done

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default intp 'queued-stage-done' branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 24 Mar 2020 13:07:24 +0100
parents 6081cbe71b81
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4927
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE TABLE waterway.fairway_marks (
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 geom geography(POINT, 4326) NOT NULL,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 datsta varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 datend varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 persta varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 perend varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 objnam varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 nobjnm varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 inform varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 ninfom varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 scamin int,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 picrep varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 txtdsc varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 sordat varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 sorind varchar
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 );
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 CREATE TABLE waterway.fairway_marks_bcnlat (
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 colour varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 colpat varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 condtn int,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 bcnshp int,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 catlam int
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 ) INHERITS (waterway.fairway_marks);
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 ON waterway.fairway_marks_bcnlat
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 ((CAST((geom,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 scamin, picrep, txtdsc, sordat, sorind,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 0, colour, colpat, condtn, bcnshp, catlam
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 ) AS waterway.fairway_marks_bcnlat)
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 ));
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 CREATE TABLE waterway.fairway_marks_bcnlat_dirimps (
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 fm_bcnlat_id int REFERENCES waterway.fairway_marks_bcnlat,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 dirimp smallint REFERENCES dirimps,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 PRIMARY KEY (fm_bcnlat_id, dirimp)
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 );
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 CREATE TABLE waterway.fairway_marks_boycar (
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 colour varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 colpat varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 conrad int,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 marsys int,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 boyshp int,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 catcam int
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 ) INHERITS (waterway.fairway_marks);
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 ON waterway.fairway_marks_boycar
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 ((CAST((geom,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 scamin, picrep, txtdsc, sordat, sorind,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 0, colour, colpat, conrad, marsys, boyshp, catcam
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 ) AS waterway.fairway_marks_boycar)
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 ));
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 CREATE TABLE waterway.fairway_marks_boylat (
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 colour varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 colpat varchar,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 conrad int,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 marsys int,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 boyshp int,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 catlam int
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 ) INHERITS (waterway.fairway_marks);
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 CREATE UNIQUE INDEX fairway_marks_boylat_distinct_rows
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 ON waterway.fairway_marks_boylat
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 ((CAST((geom,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 scamin, picrep, txtdsc, sordat, sorind,
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 0, colour, colpat, conrad, marsys, boyshp, catlam
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 ) AS waterway.fairway_marks_boylat)
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 ));
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 GRANT SELECT on ALL tables in schema waterway TO waterway_user ;
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
6081cbe71b81 Add initial schema update script for fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 TO waterway_admin;