Mercurial > dive4elements > gnv-client

annotate gnv-artifacts/doc/schema/externalinterface_schema.sql @ 494:292f0c8d356c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do some Bugfixes after testing the Schema and Testdata against an Oracle 10.2 DBMS. gnv-artifacts/trunk@571 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Tue, 19 Jan 2010 12:45:51 +0000
parents be53566c1640
children 4c6d2b4df3ae
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
490
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
8 * Das hier bereitgestellte SQL ist lediglich als Anhalt zu betrachten.
489
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
9 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
10 create table fis_has_region (
490
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
11 id_fis varchar2(30 char) not null, /* TODO maximale Länge bestimmen und ggf. anpassen.*/
489
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
12 description varchar2(90 char),
490
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
13 geometry ??? not null
489
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
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
16 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
17
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
18 /*
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
19 * 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
20 * 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
21 * 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
22 * 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
23 * 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
24 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
25 create table fis_has_mapservice(
490
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
26 id_fis varchar2(30 char) not null, /* TODO maximale Länge bestimmen und ggf. anpassen.*/
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
27 id_mapservice varchar2(100 char) not null /* TODO maximale Länge bestimmen und ggf. anpassen.*/
489
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
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
30 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
31
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
32 /**
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
33 * 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
34 * 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
35 * 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
36 * 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
37 * 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
38 *
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
39 * 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
40 * 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
41 * vgl. S 52 Pflichtenheft.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
42 *
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
43 * Ü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
44 * Layern gehört.
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
45 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
46 create table layer_has_parameter(
490
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
47 id_mapservice varchar2(100 char) not null, /* TODO maximale Länge bestimmen und ggf. anpassen.*/
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
48 id_layer varchar2(30 char) not null, /* TODO maximale Länge bestimmen und ggf. anpassen.*/
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
49 id_group varchar2(30 char ), /* TODO maximale Länge bestimmen und ggf. anpassen.*/
489
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
50 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
51 );
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
52
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
53 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
54 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
55
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
56 /**
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
57 * 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
58 * 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
59 * 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
60 * 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
61 * 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
62 * 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
63 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
64 create table mapservice_has_parameter(
490
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
65 id_mapservice varchar2(100 char) not null, /* TODO maximale Länge bestimmen und ggf. anpassen.*/
489
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
66 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
67 );
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
68
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 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
71 PRIMARY KEY (id_mapservice,id_parameter);
http://dive4elements.wald.intevation.org