Mercurial > dive4elements > gnv-client

annotate gnv-artifacts/doc/schema/externalinterface_schema.sql @ 1145:dfe1ac687c7f tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added tags
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:16:15 +0200
parents f953c9a559d8
children
rev   line source
1115
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
1 /*
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
2 * Copyright (c) 2010 by Intevation GmbH
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
3 *
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
4 * This program is free software under the LGPL (>=v2.1)
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
5 * Read the file LGPL.txt coming with the software for details
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
6 * or visit http://www.gnu.org/licenses/ if it does not exist.
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
7 */
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
8
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
9 /*
489
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
10 * 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
11 * 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
12 * 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
13 * 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
14 *
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
15 * 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
16 * 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
17 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
18 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
19 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
20 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
21 geometry ??? not null
489
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
22 );
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 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
25
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
26 /*
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
27 * 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
28 * 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
29 * 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
30 * 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
31 * 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
32 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
33 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
34 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
35 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
36 );
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 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
39
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
40 /**
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
41 * 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
42 * 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
43 * 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
44 * 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
45 * 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
46 *
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
47 * 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
48 * 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
49 * vgl. S 52 Pflichtenheft.
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 * Ü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
52 * Layern gehört.
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 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
55 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
56 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
57 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
58 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
59 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
60 );
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
61
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
62 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
63 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
64
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 * 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
67 * 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
68 * 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
69 * 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
70 * 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
71 * 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
72 */
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
73 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
74 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
75 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
76 );
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
77
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
78
3784cf498ab7 Added Database-Schema and Testdata for the MapViewer-Interface
Tim Englich <tim.englich@intevation.de>
parents:
diff changeset
79 ALTER TABLE mapservice_has_parameter ADD
1115
f953c9a559d8 Added license file and license headers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 601
diff changeset
80 PRIMARY KEY (id_mapservice,id_parameter);
http://dive4elements.wald.intevation.org