Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle-minfo.sql @ 2807:5ac1db5156be

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Some oracle db schema adaptions specific to bed heights in MINFO. flys-backend/trunk@4215 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Wed, 11 Apr 2012 13:09:46 +0000
parents a55b7b35cc3a
children f283212966e8
rev   line source
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
1 SET AUTOCOMMIT ON;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
2
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
4
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
5 CREATE TABLE location_system (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
6 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
7 name VARCHAR(32) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
8 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
9 PRIMARY KEY(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
10 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
11
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
12
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
14
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
15 CREATE TABLE elevation_model (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
16 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
17 name VARCHAR(32) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
18 unit_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
19 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
21 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
22
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
23 CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
24
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
25 CREATE TABLE bed_height_type (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
26 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
27 name VARCHAR(16) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
28 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
29 PRIMARY KEY(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
30 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
31
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
32
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
33
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
34 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
35
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
36 CREATE TABLE bed_height_single (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
37 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
38 year NUMBER(38,0) NOT NULL,
2807
5ac1db5156be Some oracle db schema adaptions specific to bed heights in MINFO.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2805
diff changeset
39 sounding_width NUMBER(38,0) NOT NULL,
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
40 type_id NUMBER(38,0) NOT NULL,
2807
5ac1db5156be Some oracle db schema adaptions specific to bed heights in MINFO.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2805
diff changeset
41 location_system_id NUMBER(38,0) NOT NULL,
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
42 cur_elevation_model_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
43 old_elevation_model_id NUMBER(38,0),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
44 range_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
45 evaluation_by VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
46 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
47 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
48 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
2807
5ac1db5156be Some oracle db schema adaptions specific to bed heights in MINFO.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2805
diff changeset
49 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
50 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
51 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
52 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
53 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
54
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
55
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
56 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
57
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
58 CREATE TABLE bed_height_epoch (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
59 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
60 time_interval_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
61 -- sounding_with NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
62 -- type_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
63 cur_elevation_model_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
64 old_elevation_model_id NUMBER(38,0),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
65 range_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
66 evaluation_by VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
67 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
68 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
69 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
70 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
71 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
72 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
73 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
74
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
75
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
76 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
77
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
78 CREATE TABLE bed_height_single_values (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
79 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
80 bed_height_single_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
81 station NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
82 height NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
83 uncertainty NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
84 data_gap NUMBER(38,2) NOT NULL,
2807
5ac1db5156be Some oracle db schema adaptions specific to bed heights in MINFO.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2805
diff changeset
85 sounding_width NUMBER(38,2) NOT NULL,
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
86 width NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
87 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
88 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
89 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
90
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
91
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
92 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
93
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
94 CREATE TABLE bed_height_epoch_values (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
95 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
96 bed_height_epoch_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
97 station NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
98 height NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
99 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
100 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
101 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
102
http://dive4elements.wald.intevation.org