Mercurial > gemma

comparison schema/updates/1400/01.add_fairway_marks.sql @ 4927:6081cbe71b81 fairway-marks-import

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