Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql.sql @ 164:324e12ab7a05
Converted schema to be PostgreSQL compatible.
flys-backend/trunk@1441 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Wed, 09 Mar 2011 14:07:15 +0000 |
| parents | |
| children | 15d515fe15f5 |
comparison
equal
deleted
inserted
replaced
| 163:4a3b2912a0cd | 164:324e12ab7a05 |
|---|---|
| 1 BEGIN; | |
| 2 | |
| 3 -- Gewaesser | |
| 4 CREATE TABLE rivers ( | |
| 5 id int PRIMARY KEY NOT NULL, | |
| 6 name VARCHAR(256) NOT NULL UNIQUE | |
| 7 ); | |
| 8 | |
| 9 -- Bruecke, Haefen, etc. | |
| 10 CREATE TABLE attributes ( | |
| 11 id int 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 int PRIMARY KEY NOT NULL, | |
| 18 river_id int NOT NULL REFERENCES rivers(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 int 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 int PRIMARY KEY NOT NULL, | |
| 34 range_id int NOT NULL REFERENCES ranges(id), | |
| 35 attribute_id int NOT NULL REFERENCES attributes(id), | |
| 36 position_id int REFERENCES positions(id) | |
| 37 ); | |
| 38 | |
| 39 -- Pegel | |
| 40 CREATE TABLE gauges ( | |
| 41 id int PRIMARY KEY NOT NULL, | |
| 42 name VARCHAR(256) NOT NULL, | |
| 43 river_id int NOT NULL REFERENCES rivers(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 int 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 int 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 int PRIMARY KEY NOT NULL, | |
| 64 name VARCHAR(256) NOT NULL UNIQUE, | |
| 65 type_id int NOT NULL REFERENCES main_value_types(id), | |
| 66 UNIQUE (name, type_id) | |
| 67 ); | |
| 68 | |
| 69 -- Table for time intervals | |
| 70 CREATE TABLE time_intervals ( | |
| 71 id int PRIMARY KEY NOT NULL, | |
| 72 start_time TIMESTAMP NOT NULL, | |
| 73 stop_time TIMESTAMP, | |
| 74 CHECK (start_time <= stop_time) | |
| 75 ); | |
| 76 | |
| 77 -- Stammdaten | |
| 78 CREATE TABLE main_values ( | |
| 79 id int PRIMARY KEY NOT NULL, | |
| 80 gauge_id int NOT NULL REFERENCES gauges(id), | |
| 81 named_value_id int NOT NULL REFERENCES named_main_values(id), | |
| 82 value NUMERIC NOT NULL, | |
| 83 | |
| 84 time_interval_id int REFERENCES time_intervals(id), | |
| 85 | |
| 86 -- TODO: better checks | |
| 87 UNIQUE (gauge_id, named_value_id, time_interval_id) | |
| 88 ); | |
| 89 | |
| 90 -- Abflusstafeln | |
| 91 CREATE TABLE discharge_table ( | |
| 92 id int PRIMARY KEY NOT NULL, | |
| 93 gauge_id int NOT NULL REFERENCES gauges(id), | |
| 94 | |
| 95 time_interval_id int REFERENCES time_intervals(id), | |
| 96 | |
| 97 -- TODO: better checks | |
| 98 UNIQUE (gauge_id, time_interval_id) | |
| 99 ); | |
| 100 | |
| 101 -- Values of the Abflusstafeln | |
| 102 CREATE TABLE discharge_table_values ( | |
| 103 id int PRIMARY KEY NOT NULL, | |
| 104 table_id int NOT NULL REFERENCES discharge_table(id), | |
| 105 q NUMERIC NOT NULL, | |
| 106 w NUMERIC NOT NULL, | |
| 107 | |
| 108 UNIQUE (table_id, q, w) | |
| 109 ); | |
| 110 | |
| 111 -- WST files | |
| 112 CREATE TABLE wst ( | |
| 113 id int PRIMARY KEY NOT NULL, | |
| 114 river_id int NOT NULL REFERENCES rivers(id), | |
| 115 description VARCHAR(256) NOT NULL, | |
| 116 -- TODO: more meta infos | |
| 117 UNIQUE (river_id, description) | |
| 118 ); | |
| 119 | |
| 120 -- columns of WST files | |
| 121 CREATE TABLE wst_column ( | |
| 122 id int PRIMARY KEY NOT NULL, | |
| 123 wst_id int NOT NULL REFERENCES wst(id), | |
| 124 name VARCHAR(256) NOT NULL, | |
| 125 description VARCHAR, | |
| 126 | |
| 127 time_interval_id int REFERENCES time_intervals(id), | |
| 128 | |
| 129 UNIQUE (wst_id, name) | |
| 130 ); | |
| 131 | |
| 132 -- w values in WST file column | |
| 133 CREATE TABLE wst_column_values ( | |
| 134 id int PRIMARY KEY NOT NULL, | |
| 135 wst_column_id int NOT NULL REFERENCES wst_column(id), | |
| 136 position NUMERIC NOT NULL, | |
| 137 w NUMERIC NOT NULL, | |
| 138 | |
| 139 UNIQUE (position, wst_column_id), | |
| 140 UNIQUE (position, wst_column_id, w) | |
| 141 ); | |
| 142 | |
| 143 -- bind q values to range | |
| 144 CREATE TABLE wst_q_ranges ( | |
| 145 id int PRIMARY KEY NOT NULL, | |
| 146 range_id int NOT NULL REFERENCES ranges(id), | |
| 147 q NUMERIC NOT NULL | |
| 148 ); | |
| 149 | |
| 150 -- bind q ranges to wst columns | |
| 151 CREATE TABLE wst_column_q_ranges ( | |
| 152 id int PRIMARY KEY NOT NULL, | |
| 153 wst_column_id int NOT NULL REFERENCES wst_column(id), | |
| 154 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), | |
| 155 | |
| 156 UNIQUE (wst_column_id, wst_q_range_id) | |
| 157 ); | |
| 158 | |
| 159 COMMIT; |