Mercurial > dive4elements > gnv-client

annotate gnv-artifacts/doc/schema/externalinterface_schema.sql @ 489:3784cf498ab7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added Database-Schema and Testdata for the MapViewer-Interface gnv-artifacts/trunk@565 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Mon, 18 Jan 2010 14:17:25 +0000
parents
children be53566c1640
rev   line source
489
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
1 /*
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
2 * Lookup-Tabelle für das FIS anhand eines Gebietes.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
3 * Diese Tabelle ist in der ArcSDE als Layer zu integrieren.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
4 * Die Inhalte der Spalte id_fis müssen mit den vergebenen IDs
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
5 * in der Konfiguration des Artefaktservers übereinstimmen.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
6 *
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
7 * Abbildung als Layer in der ArcSDE
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
8 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
9 create table fis_has_region (
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
10 id_fis varchar2(30 char) not null,
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
11 description varchar2(90 char),
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
12 geometry SDO.Geometry not null
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
13 );
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
14
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
15 ALTER TABLE fis_has_region ADD PRIMARY KEY (id_fis);
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
16
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
17 /*
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
18 * Lookup-Tabelle für das FIS anhand eines Mapservices.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
19 * Die Inhalte der Spalte id_fis müssen mit den vergebenen IDs
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
20 * in der Konfiguration des Artefaktservers übereinstimmen.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
21 * Die Inhalte der Spalte id_mapservice müssen mit den vergebenen
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
22 * IDs der Services im Mapviewer übereinstimmen.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
23 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
24 create table fis_has_mapservice(
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
25 id_fis varchar2(30 char) not null,
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
26 id_mapservice varchar2(100 char) not null
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
27 );
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
28
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
29 ALTER TABLE fis_has_mapservice ADD PRIMARY KEY (id_fis,id_mapservice);
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
30
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
31 /**
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
32 * Lookuptabelle zwischen einer LayerID und dem dazugehörigen
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
33 * Mapservice und den im Layer dargestellten Parameter.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
34 * Fremdschlüsselbeziehung zu der Tabelle fis_has_mapservice.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
35 * Primärschlüssel: id_mapservice, id_layer, id_parameter
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
36 * id_parameter referenziert zusätzlich die Tabelle MEDIAN.PARAMETER.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
37 *
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
38 * Auf eine explizite Abbildung von GroupLayern kann verzichtet werden,
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
39 * da sie lediglich einen "ordnenden" Charakter haben.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
40 * vgl. S 52 Pflichtenheft.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
41 *
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
42 * Über id_group wird abgebildet ob ein Layer zu einer Gruppe von
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
43 * Layern gehört.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
44 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
45 create table layer_has_parameter(
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
46 id_mapservice varchar2(100 char) not null,
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
47 id_layer varchar2(30 char) not null,
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
48 id_group varchar2(30 char ),
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
49 id_parameter NUMBER(10) not null /* Referenz zur Tabelle MEDIAN.PARAMETER */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
50 );
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
51
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
52 ALTER TABLE layer_has_parameter ADD
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
53 PRIMARY KEY (id_mapservice,id_layer,id_parameter);
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
54 ALTER TABLE layer_has_parameter ADD (CONSTRAINT fk_layer_has_parameter1)
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
55 FOREIGN KEY (id_mapservice)
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
56 REFERENCES fis_has_mapservice(id_mapservice);
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
57
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
58 /**
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
59 * Lookuptabelle ob ein Mapservice genau einen Parameter darstellt.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
60 * Wenn in dieser Tabelle nichts vorhanden ist muss unter zurhilfenahme
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
61 * der Layer-ID in der Tabelle layer_has_parameter gesucht werden.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
62 * Die Inhalte der Spalte id_mapservice müssen mit den vergebenen
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
63 * IDs der Services im Mapviewer übereinstimmen.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
64 * id_parameter referenziert zusätzlich die Tabelle MEDIAN.PARAMETER.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
65 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
66 create table mapservice_has_parameter(
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
67 id_mapservice varchar2(100 char) not null,
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
68 id_parameter NUMBER(10) not null /* Referenz zur Tabelle MEDIAN.PARAMETER */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
69 );
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
70
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
71
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
72 ALTER TABLE mapservice_has_parameter ADD
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
73 PRIMARY KEY (id_mapservice,id_parameter);
http://dive4elements.wald.intevation.org