Mercurial > dive4elements > river

comparison flys-backend/doc/schema/sqlite.sql @ 161:119048655872

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