Mercurial > dive4elements > gnv-client

annotate gnv-artifacts/doc/schema/externalinterface_schema.sql @ 605:e8ebdbc7f1e3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
First step of removing the cache blob. The static part of the describe document will be created by using the input data stored at each state. Some TODOs left (see ChangeLog). gnv-artifacts/trunk@671 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Tue, 09 Feb 2010 14:27:55 +0000
parents 4c6d2b4df3ae
children f953c9a559d8
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.*/
601
4c6d2b4df3ae Changes id_layer of Table layerhasparameter from literal to number.
Tim Englich <tim.englich@intevation.de>
parents: 494
diff changeset
48 id_layer NUMBER(10) not null, /* TODO maximale Länge bestimmen und ggf. anpassen.*/
4c6d2b4df3ae Changes id_layer of Table layerhasparameter from literal to number.
Tim Englich <tim.englich@intevation.de>
parents: 494
diff changeset
49 layer_name varchar2(30 char) not null, /* TODO maximale Länge bestimmen und ggf. anpassen.*/
490
be53566c1640 Added Comments and further Informations to the external Interface Schema
Tim Englich <tim.englich@intevation.de>
parents: 489
diff changeset
50 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
51 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
52 );
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
53
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
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
55 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
56
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 * 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
59 * 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
60 * 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
61 * 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
62 * 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
63 * 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
64 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
65 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
66 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
67 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
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
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
71 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
72 PRIMARY KEY (id_mapservice,id_parameter);
http://dive4elements.wald.intevation.org