Mercurial > dive4elements > river

annotate flys-backend/doc/schema/sqlite.sql @ 163:4a3b2912a0cd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Factorized time intervals out into a separated table. flys-backend/trunk@1432 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Mar 2011 09:41:01 +0000
parents 119048655872
children 324e12ab7a05
rev   line source
161
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 BEGIN TRANSACTION;
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 -- Gewaesser
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 CREATE TABLE rivers (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 name VARCHAR(256) NOT NULL UNIQUE
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 -- Bruecke, Haefen, etc.
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 CREATE TABLE attributes (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 value VARCHAR(256) NOT NULL UNIQUE
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15 -- segments from/to at a river
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16 CREATE TABLE ranges (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 river_id INTEGER NOT NULL REFERENCES river(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 a NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20 b NUMERIC,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21 UNIQUE (river_id, a, b)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24 -- Lage 'links', 'rechts', etc.
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 CREATE TABLE positions (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27 value VARCHAR(256) NOT NULL UNIQUE
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 -- Some object (eg. Hafen) at a segment of river
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31 -- plus its position.
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32 CREATE TABLE annotations (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 range_id INTEGER NOT NULL REFERENCES ranges(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
35 attribute_id INTEGER NOT NULL REFERENCES attributes(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36 position_id INTEGER REFERENCES positions(id)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39 -- Pegel
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 CREATE TABLE gauges (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42 name VARCHAR(256) NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 river_id INTEGER NOT NULL REFERENCES river(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44 station NUMERIC NOT NULL UNIQUE,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
45 aeo NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
47 -- Pegelnullpunkt
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
48 datum NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49 -- Streckengueltigkeit
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50 range_id INTEGER NOT NULL REFERENCES ranges (id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
52 UNIQUE (name, river_id)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
53 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
54
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
55 -- Type of a Hauptwert 'W', 'Q', 'D', etc.
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
56 CREATE TABLE main_value_types (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58 name VARCHAR(256) NOT NULL UNIQUE
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61 -- Named type of a Hauptwert (eg. HQ100)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
62 CREATE TABLE named_main_values (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
64 name VARCHAR(256) NOT NULL UNIQUE,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
65 type_id INTEGER NOT NULL REFERENCES main_value_types(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
66 UNIQUE (name, type_id)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
67 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68
163
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
69 -- Table for time intervals
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
70 CREATE TABLE time_intervals (
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
71 id INTEGER PRIMARY KEY NOT NULL,
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
72 start_time TIMESTAMP NOT NULL,
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
73 stop_time TIMESTAMP,
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
74 CHECK (start_time <= stop_time)
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
75 );
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
76
161
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
77 -- Stammdaten
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
78 CREATE TABLE main_values (
163
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
79 id INTEGER PRIMARY KEY NOT NULL,
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
80 gauge_id INTEGER NOT NULL REFERENCES gauges(id),
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
81 named_value_id INTEGER NOT NULL REFERENCES named_main_values(id),
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
82 value NUMERIC NOT NULL,
161
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83
163
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
84 time_interval_id INTEGER REFERENCES time_intervals(id),
161
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
85
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
86 -- TODO: better checks
163
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
87 UNIQUE (gauge_id, named_value_id, time_interval_id)
161
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
88 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
89
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
90 -- Abflusstafeln
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
91 CREATE TABLE discharge_table (
163
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
92 id INTEGER PRIMARY KEY NOT NULL,
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
93 gauge_id INTEGER NOT NULL REFERENCES gauges(id),
161
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
94
163
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
95 time_interval_id INTEGER REFERENCES time_intervals(id),
161
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
96
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
97 -- TODO: better checks
163
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
98 UNIQUE (gauge_id, time_interval_id)
161
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
99 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
100
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
101 -- Values of the Abflusstafeln
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
102 CREATE TABLE discharge_table_values (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
103 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
104 table_id INTEGER NOT NULL REFERENCES discharge_table(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
105 q NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
106 w NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
107
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
108 UNIQUE (table_id, q, w)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
109 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
110
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
111 -- WST files
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
112 CREATE TABLE wst (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
113 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
114 river_id INTEGER NOT NULL REFERENCES river(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
115 description VARCHAR(256) NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
116 -- TODO: more meta infos
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
117 UNIQUE (river_id, description)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
118 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
119
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
120 -- columns of WST files
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
121 CREATE TABLE wst_column (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
122 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
123 wst_id INTEGER NOT NULL REFERENCES wst(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
124 name VARCHAR(256) NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
125 description VARCHAR,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
126
163
4a3b2912a0cd Factorized time intervals out into a separated table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 161
diff changeset
127 time_interval_id INTEGER REFERENCES time_intervals(id),
161
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
128
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
129 UNIQUE (wst_id, name)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
130 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
131
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
132 -- w values in WST file column
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
133 CREATE TABLE wst_column_values (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
134 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
135 wst_column_id INTEGER NOT NULL REFERENCES wst_column(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
136 position NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
137 w NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
138
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
139 UNIQUE (position, wst_column_id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
140 UNIQUE (position, wst_column_id, w)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
141 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
142
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
143 -- bind q values to range
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
144 CREATE TABLE wst_q_ranges (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
145 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
146 range_id INTEGER NOT NULL REFERENCES ranges(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
147 q NUMERIC NOT NULL
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
148 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
149
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
150 -- bind q ranges to wst columns
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
151 CREATE TABLE wst_column_q_ranges (
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
152 id INTEGER PRIMARY KEY NOT NULL,
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
153 wst_column_id INTEGER NOT NULL REFERENCES wst_column(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
154 wst_q_range_id INTEGER NOT NULL REFERENCES wst_q_ranges(id),
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
155
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
156 UNIQUE (wst_column_id, wst_q_range_id)
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
157 );
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
158
119048655872 Initial check in of the FLYS database backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
159 END TRANSACTION;
http://dive4elements.wald.intevation.org