dive4elements/river: flys-backend/doc/schema/sqlite.sql annotate

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

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

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
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	69 -- Stammdaten
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	70 CREATE TABLE main_values (
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	71 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	72 gauge_id INTEGER NOT NULL REFERENCES gauges(id),
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	73 named_value_id INTEGER NOT NULL REFERENCES named_main_values(id),
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	74 value NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	75
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	76 start_time TIMESTAMP DEFAULT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	77 stop_time TIMESTAMP DEFAULT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	78
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	79 -- TODO: better checks
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	80 CHECK (start_time <= stop_time),
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	81 UNIQUE (gauge_id, named_value_id, start_time, stop_time)
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	82 );
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	83
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	84 -- Abflusstafeln
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	85 CREATE TABLE discharge_table (
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	86 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	87 gauge_id INTEGER NOT NULL REFERENCES gauges(id),
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 start_time TIMESTAMP DEFAULT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	90 stop_time TIMESTAMP DEFAULT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	91
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	92 -- TODO: better checks
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	93 CHECK (start_time <= stop_time),
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	94 UNIQUE (gauge_id, start_time, stop_time)
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	95 );
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 -- Values of the Abflusstafeln
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	98 CREATE TABLE discharge_table_values (
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	99 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	100 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	101 q NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	102 w NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	103
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	104 UNIQUE (table_id, q, w)
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	105 );
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	106
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	107 -- WST files
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	108 CREATE TABLE wst (
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	109 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	110 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	111 description VARCHAR(256) NOT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	112 -- TODO: more meta infos
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	113 UNIQUE (river_id, description)
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	114 );
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	115
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	116 -- columns of WST files
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	117 CREATE TABLE wst_column (
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	118 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	119 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	120 name VARCHAR(256) NOT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	121 description VARCHAR,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	122
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	123 start_time TIMESTAMP DEFAULT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	124 stop_time TIMESTAMP DEFAULT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	125
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	126 CHECK (start_time <= stop_time),
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	127 UNIQUE (wst_id, name)
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
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	130 -- 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	131 CREATE TABLE wst_column_values (
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	132 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	133 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	134 position NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	135 w NUMERIC NOT NULL,
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	136
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	137 UNIQUE (position, wst_column_id),
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	138 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	139 );
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	140
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	141 -- bind q values to range
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	142 CREATE TABLE wst_q_ranges (
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	143 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	144 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	145 q NUMERIC NOT NULL
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	146 );
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	147
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	148 -- 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	149 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	150 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	151 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	152 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	153
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	154 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	155 );
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	156
119048655872 Initial check in of the FLYS database backend. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	157 END TRANSACTION;

Mercurial > dive4elements > river

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