Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle.sql @ 2343:6662b0ea20c1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added 'unbracket' StringUtil method (extracted from WaterlevelSelectState). flys-backend/trunk@2848 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Felix Wolfsteller <felix.wolfsteller@intevation.de>
date Wed, 28 Sep 2011 08:17:16 +0000
parents 0e63222d80bc
children f993b735db71
rev   line source
1255
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
1 BEGIN;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
2 create table annotation_types (id number(10,0) not null, name varchar2(255), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
3 create table annotations (id number(10,0) not null, attribute_id number(10,0), edge_id number(10,0), position_id number(10,0), range_id number(10,0), type_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
4 create table attributes (id number(10,0) not null, value varchar2(255), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
5 create table cross_section_lines (id number(10,0) not null, km number(19,2), cross_section_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
6 create table cross_section_points (id number(10,0) not null, col_pos number(10,0), x number(19,2), y number(19,2), cross_section_line_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
7 create table cross_sections (id number(10,0) not null, description varchar2(255), river_id number(10,0), time_interval_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
8 create table discharge_table_values (id number(10,0) not null, q number(19,2), w number(19,2), table_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
9 create table discharge_tables (id number(10,0) not null, description varchar2(255), kind number(10,0), gauge_id number(10,0), time_interval_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
10 create table edges (id number(10,0) not null, bottom number(19,2), top number(19,2), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
11 create table gauges (id number(10,0) not null, aeo number(19,2), datum number(19,2), name varchar2(255), station number(19,2), range_id number(10,0), river_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
12 create table hyk_entries (id number(10,0) not null, km number(19,2), measure date, hyk_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
13 create table hyk_flow_zone_types (id number(10,0) not null, description varchar2(255), name varchar2(255), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
14 create table hyk_flow_zones (id number(10,0) not null, a number(19,2), b number(19,2), formation_id number(10,0), type_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
15 create table hyk_formations (id number(10,0) not null, bottom number(19,2), distance_hf number(19,2), distance_vl number(19,2), distance_vr number(19,2), formation_num number(10,0), top number(19,2), hyk_entry_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
16 create table hyks (id number(10,0) not null, description varchar2(255), river_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
17 create table main_value_types (id number(10,0) not null, name varchar2(255), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
18 create table main_values (id number(10,0) not null, value number(19,2), gauge_id number(10,0), named_value_id number(10,0), time_interval_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
19 create table named_main_values (id number(10,0) not null, name varchar2(255), type_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
20 create table positions (id number(10,0) not null, value varchar2(255), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
21 create table ranges (id number(10,0) not null, a number(19,2), b number(19,2), river_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
22 create table rivers (id number(10,0) not null, km_up number(1,0), name varchar2(255), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
23 create table time_intervals (id number(10,0) not null, start_time date, stop_time date, primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
24 create table wst_column_q_ranges (id number(10,0) not null, wst_column_id number(10,0), wst_q_range_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
25 create table wst_column_values (id number(10,0) not null, position number(19,2), w number(19,2), wst_column_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
26 create table wst_columns (id number(10,0) not null, description varchar2(255), name varchar2(255), position number(10,0), time_interval_id number(10,0), wst_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
27 create table wst_q_ranges (id number(10,0) not null, q number(19,2), range_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
28 create table wsts (id number(10,0) not null, description varchar2(255), kind number(10,0), river_id number(10,0), primary key (id));
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
29 alter table annotations add constraint FKC6AD7B2476703DB3 foreign key (range_id) references ranges;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
30 alter table annotations add constraint FKC6AD7B24E838461 foreign key (edge_id) references edges;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
31 alter table annotations add constraint FKC6AD7B24FF598161 foreign key (position_id) references positions;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
32 alter table annotations add constraint FKC6AD7B24483998D3 foreign key (attribute_id) references attributes;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
33 alter table annotations add constraint FKC6AD7B245AE11A50 foreign key (type_id) references annotation_types;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
34 alter table cross_section_lines add constraint FK4072AB66BF9BA960 foreign key (cross_section_id) references cross_sections;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
35 alter table cross_section_points add constraint FKD5088D5CF337114D foreign key (cross_section_line_id) references cross_section_lines;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
36 alter table cross_sections add constraint FKC2C43F4D2B365753 foreign key (river_id) references rivers;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
37 alter table cross_sections add constraint FKC2C43F4D452A8FDC foreign key (time_interval_id) references time_intervals;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
38 alter table discharge_table_values add constraint FK56DD4B10C1ABE65F foreign key (table_id) references discharge_tables;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
39 alter table discharge_tables add constraint FKF143882452A8FDC foreign key (time_interval_id) references time_intervals;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
40 alter table discharge_tables add constraint FKF143882B1A5433 foreign key (gauge_id) references gauges;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
41 alter table gauges add constraint FKB550CE9A2B365753 foreign key (river_id) references rivers;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
42 alter table gauges add constraint FKB550CE9A76703DB3 foreign key (range_id) references ranges;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
43 alter table hyk_entries add constraint FK19EBA6CBEDF9E6D3 foreign key (hyk_id) references hyks;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
44 alter table hyk_flow_zones add constraint FK22F80BDB3C831087 foreign key (formation_id) references hyk_formations;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
45 alter table hyk_flow_zones add constraint FK22F80BDB697E0D07 foreign key (type_id) references hyk_flow_zone_types;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
46 alter table hyk_formations add constraint FK97CFDC71A08D08C foreign key (hyk_entry_id) references hyk_entries;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
47 alter table hyks add constraint FK311A392B365753 foreign key (river_id) references rivers;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
48 alter table main_values add constraint FKD85C6268452A8FDC foreign key (time_interval_id) references time_intervals;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
49 alter table main_values add constraint FKD85C6268B1A5433 foreign key (gauge_id) references gauges;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
50 alter table main_values add constraint FKD85C6268DE623F5 foreign key (named_value_id) references named_main_values;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
51 alter table named_main_values add constraint FK5EA68A62CBC256EB foreign key (type_id) references main_value_types;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
52 alter table ranges add constraint FKC812EED62B365753 foreign key (river_id) references rivers;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
53 alter table wst_column_q_ranges add constraint FKA28D6C067DA2F764 foreign key (wst_column_id) references wst_columns;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
54 alter table wst_column_q_ranges add constraint FKA28D6C066A71480F foreign key (wst_q_range_id) references wst_q_ranges;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
55 alter table wst_column_values add constraint FK992F4F447DA2F764 foreign key (wst_column_id) references wst_columns;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
56 alter table wst_columns add constraint FKE77A73B6452A8FDC foreign key (time_interval_id) references time_intervals;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
57 alter table wst_columns add constraint FKE77A73B67425E13 foreign key (wst_id) references wsts;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
58 alter table wst_q_ranges add constraint FK70E459AB76703DB3 foreign key (range_id) references ranges;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
59 alter table wsts add constraint FK37D65B2B365753 foreign key (river_id) references rivers;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
60 create sequence ANNOTATIONS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
61 create sequence ANNOTATION_TYPES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
62 create sequence ATTRIBUTES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
63 create sequence CROSS_SECTIONS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
64 create sequence CROSS_SECTION_LINES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
65 create sequence CROSS_SECTION_POINTS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
66 create sequence DISCHARGE_TABLES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
67 create sequence DISCHARGE_TABLE_VALUES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
68 create sequence EDGES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
69 create sequence GAUGES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
70 create sequence HYKS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
71 create sequence HYK_ENTRIES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
72 create sequence HYK_FLOW_ZONES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
73 create sequence HYK_FLOW_ZONE_TYPES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
74 create sequence HYK_FORMATIONS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
75 create sequence MAIN_VALUES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
76 create sequence MAIN_VALUE_TYPES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
77 create sequence NAMED_MAIN_VALUES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
78 create sequence POSITIONS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
79 create sequence RANGES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
80 create sequence RIVERS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
81 create sequence TIME_INTERVALS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
82 create sequence WSTS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
83 create sequence WST_COLUMNS_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
84 create sequence WST_COLUMN_VALUES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
85 create sequence WST_Q_RANGES_ID_SEQ;
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
86 COMMIT;
http://dive4elements.wald.intevation.org