Mercurial > dive4elements > gnv-client

annotate gnv-artifacts/doc/schema/externalinterface_schema.sql @ 1043:d955571cdf2a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added queries for Meshes using Vectorvalues for TimeSeries. gnv-artifacts/trunk@1115 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Fri, 21 May 2010 08:28:49 +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