source: trunk/zoo-project/zoo-kernel/sql/zoo_collectiondb.sql

Last change on this file was 917, checked in by djay, 3 years ago

Merge prototype-v0 branch in trunk

  • Property svn:keywords set to Id
File size: 13.0 KB
Line 
1create schema CollectionDB;
2
3set search_path = CollectionDB, pg_catalog;
4
5CREATE OR REPLACE FUNCTION update_Description() RETURNS trigger AS 
6$$
7DECLARE
8        i integer;
9BEGIN
10        INSERT INTO CollectionDB.Descriptions (id) VALUES (NEW.id);
11        RETURN NEW;
12END;
13$$ LANGUAGE plpgsql;
14
15CREATE FUNCTION is_in_ows_DataDescription(i integer) RETURNS boolean
16    LANGUAGE plpgsql
17    AS $$
18DECLARE
19        res int;
20        mymax int;
21BEGIN
22        SELECT id from CollectionDB.ows_DataDescription where id=i INTO res ;
23        if res is NULL then
24           return false;
25        else
26           return true;
27        end if;
28END;
29$$;
30
31create table CollectionDB.Descriptions (
32       id serial primary key
33);
34
35create table CollectionDB.ows_Metadata (
36       id serial primary key,
37       title text,
38       role text,
39       href text
40);
41
42create table CollectionDB.DescriptionsMetadataAssignment(
43       descriptions_id int references CollectionDB.Descriptions(id),
44       metadata_id int references CollectionDB.ows_Metadata(id)
45);
46
47create table CollectionDB.ows_Keywords (
48    id serial primary key,
49    keyword varchar
50);
51
52create table CollectionDB.DescriptionsKeywordsAssignment(
53       descriptions_id int references CollectionDB.Descriptions(id),
54       keywords_id int references CollectionDB.ows_Keywords(id)
55);
56
57create table CollectionDB.ows_AdditionalParameters (
58    id serial primary key,
59    title varchar,
60    role varchar,
61    href varchar
62);
63
64create table CollectionDB.DescriptionsAdditionalParametersAssignment (
65       descriptions_id int references CollectionDB.Descriptions(id),
66       additional_parameters_id int references CollectionDB.ows_AdditionalParameters(id)
67);
68
69--
70-- See reference for primitive datatypes
71-- https://www.w3.org/TR/xmlschema-2/#built-in-primitive-datatypes
72--
73create table CollectionDB.PrimitiveDataTypes (
74       id serial primary key,
75       name varchar(255)
76);
77INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('string');
78INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('boolean');
79INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('integer');
80INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('float');
81INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('double');
82INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('duration');
83INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('dateTime');
84INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('time');
85INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('date');
86INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gYearMonth');
87INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gYear');
88INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gMonthDay');
89INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gDay');
90INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('gMonth');
91INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('hexBinary');
92INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('base64Binary');
93INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('anyURI');
94INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('QName');
95INSERT INTO CollectionDB.PrimitiveDataTypes (name) VALUES ('NOTATION');
96
97--
98-- List all primitive formats
99--
100create table CollectionDB.PrimitiveFormats (
101       id serial primary key,
102       mime_type varchar(255),
103       encoding varchar(15),
104       schema varchar(255)
105);
106
107-- https://tools.ietf.org/html/rfc4180
108INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/csv','utf-8');
109INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/css','utf-8');
110INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/html','utf-8');
111INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/javascript','utf-8');
112INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('text/plain','utf-8');
113INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding,schema)
114       VALUES ('text/xml','utf-8','http://schema.opengis.net/gml/3.2.1/gml.xsd');
115INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding,schema)
116       VALUES ('text/xml','utf-8','http://schema.opengis.net/gml/3.1.0/gml.xsd');
117INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('application/gml+xml','utf-8');
118INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('application/json','utf-8');
119-- https://tools.ietf.org/html/rfc3302
120INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/tiff');
121-- https://www.ietf.org/rfc/rfc4047.txt
122INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/fits');
123-- https://tools.ietf.org/html/rfc3745
124INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/jp2');
125INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/png');
126INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/jpeg');
127INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('image/gif');
128INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('application/octet-stream');
129INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('application/vnd.google-earth.kml+xml');
130-- https://www.iana.org/assignments/media-types/application/zip
131INSERT INTO CollectionDB.PrimitiveFormats (mime_type) VALUES ('application/zip');
132-- https://www.iana.org/assignments/media-types/application/xml
133INSERT INTO CollectionDB.PrimitiveFormats (mime_type,encoding) VALUES ('application/xml','utf-8');
134
135create table CollectionDB.ows_Format (
136    id serial primary key,
137    primitive_format_id int references CollectionDB.PrimitiveFormats(id),
138    maximum_megabytes int,
139    def boolean,
140        use_mapserver bool,
141        ms_styles text
142);
143
144create table CollectionDB.ows_DataDescription (
145    id serial primary key,
146    format_id int references CollectionDB.ows_Format(id)
147);
148
149create table CollectionDB.PrimitiveUom (
150        id serial primary key,
151        uom varchar
152);
153-- source : Open Geospatial Consortium - URNs of definitions in ogc namespace
154insert into CollectionDB.PrimitiveUom (uom) values ('degree');
155insert into CollectionDB.PrimitiveUom (uom) values ('radian');
156insert into CollectionDB.PrimitiveUom (uom) values ('metre');
157insert into CollectionDB.PrimitiveUom (uom) values ('unity');
158
159create table CollectionDB.LiteralDataDomain (
160    possible_literal_values varchar,
161    default_value varchar,
162    data_type_id int references CollectionDB.PrimitiveDataTypes(id),
163    uom int references CollectionDB.PrimitiveUom(id),
164    def boolean
165) inherits (CollectionDB.ows_DataDescription);
166alter table CollectionDB.LiteralDataDomain add constraint literal_data_domain_id unique (id);
167
168create table CollectionDB.BoundingBoxData (
169    epsg int
170) inherits (CollectionDB.ows_DataDescription);
171alter table CollectionDB.BoundingBoxData add constraint bounding_box_data_id unique (id);
172
173create table CollectionDB.ComplexData (
174) inherits (CollectionDB.ows_DataDescription);
175alter table CollectionDB.ComplexData add constraint complex_data_id unique (id);
176
177create table CollectionDB.AllowedValues (
178    id serial primary key,
179    allowed_value varchar(255)
180);
181
182create table CollectionDB.AllowedValuesAssignment (
183    id serial primary key,
184    literal_data_domain_id int references CollectionDB.LiteralDataDomain (id),
185    allowed_value_id int references CollectionDB.AllowedValues (id)
186);
187
188create table CollectionDB.ows_AdditionalParameter (
189    id serial primary key,
190    key varchar,
191    value varchar,
192    additional_parameters_id int references CollectionDB.ows_AdditionalParameters(id)
193);
194
195create table CollectionDB.ows_Input (
196    id int primary key default nextval('collectiondb.descriptions_id_seq'::regclass),
197    title text,
198    abstract text,
199    identifier varchar(255),
200    min_occurs int,
201    max_occurs int
202); -- inherits (CollectionDB.Descriptions);
203alter table CollectionDB.ows_Input add constraint codb_input_id unique (id);
204CREATE TRIGGER ows_Input_proc AFTER INSERT ON CollectionDB.ows_Input FOR EACH ROW EXECUTE PROCEDURE update_Description();
205
206create table CollectionDB.ows_Output (
207    id int primary key default nextval('collectiondb.descriptions_id_seq'::regclass),
208    title text,
209    abstract text,
210    identifier varchar(255)
211); --inherits (CollectionDB.Descriptions);
212alter table CollectionDB.ows_Output add constraint codb_output_id unique (id);
213CREATE TRIGGER ows_Output_proc AFTER INSERT ON CollectionDB.ows_Output FOR EACH ROW EXECUTE PROCEDURE update_Description();
214
215create table CollectionDB.zoo_PrivateMetadata (
216    id serial primary key,
217    identifier varchar,
218    metadata_date timestamp
219);
220
221create table CollectionDB.ows_Process (
222    id int primary key default nextval('collectiondb.descriptions_id_seq'::regclass),
223    title text,
224    abstract text,
225    identifier varchar(255),
226    availability boolean,
227    process_description_xml text,
228    private_metadata_id int references CollectionDB.zoo_PrivateMetadata(id)
229); -- inherits (CollectionDB.Descriptions);
230alter table CollectionDB.ows_Process add constraint codb_process_id unique (id);
231alter table CollectionDB.ows_Process add constraint codb_process_identifier unique (identifier);
232CREATE TRIGGER ows_Process_proc AFTER INSERT ON CollectionDB.ows_Process FOR EACH ROW EXECUTE PROCEDURE update_Description();
233
234create table CollectionDB.InputInputAssignment (
235    id serial primary key,
236    parent_input int references CollectionDB.ows_Input(id),
237    child_input int references CollectionDB.ows_Input(id)
238);
239
240create table CollectionDB.InputDataDescriptionAssignment (
241    id serial primary key,
242    input_id int references CollectionDB.ows_Input(id),
243    data_description_id int check (CollectionDB.is_in_ows_DataDescription(data_description_id))
244);
245
246create table CollectionDB.OutputOutputAssignment (
247    id serial primary key,
248    parent_output int references CollectionDB.ows_Output(id),
249    child_output int references CollectionDB.ows_Output(id)
250);
251
252create table CollectionDB.OutputDataDescriptionAssignment (
253    id serial primary key,
254    output_id int references CollectionDB.ows_Output(id),
255    data_description_id int check (CollectionDB.is_in_ows_DataDescription(data_description_id))
256);
257
258create table CollectionDB.zoo_ServiceTypes (
259        id serial primary key,
260        service_type varchar
261);
262insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('HPC');
263insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('C');
264insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('Java');
265insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('Mono');
266insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('JS');
267insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('PHP');
268insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('Python');
269
270insert into CollectionDB.zoo_ServiceTypes (service_type) VALUES ('OTB');
271
272create table CollectionDB.zoo_DeploymentMetadata (
273    id serial primary key,
274    executable_name varchar,
275    configuration_identifier varchar,
276        service_type_id int references CollectionDB.zoo_ServiceTypes(id)
277);
278
279create table CollectionDB.zoo_PrivateProcessInfo (
280    id serial primary key
281);
282
283create table CollectionDB.PrivateMetadataDeploymentMetadataAssignment (
284    id serial primary key,
285    private_metadata_id int references CollectionDB.zoo_PrivateMetadata(id),
286    deployment_metadata_id int references CollectionDB.zoo_DeploymentMetadata(id)
287);
288
289create table CollectionDB.PrivateMetadataPrivateProcessInfoAssignment (
290    id serial primary key,
291    private_metadata_id int references CollectionDB.zoo_PrivateMetadata(id),
292    private_process_info_id int references CollectionDB.zoo_PrivateProcessInfo(id)
293);
294
295create table CollectionDB.ProcessInputAssignment (
296    id serial primary key,
297    process_id int references CollectionDB.ows_Process(id),
298    input_id int references CollectionDB.ows_Input(id),
299    index int
300);
301
302create table CollectionDB.ProcessOutputAssignment (
303    id serial primary key,
304    process_id int references CollectionDB.ows_Process(id),
305    output_id int references CollectionDB.ows_Output(id),
306    index int
307);
308
309CREATE OR REPLACE VIEW public.ows_process AS
310       (SELECT
311        id,
312        identifier,
313        title,
314        abstract,
315        (SELECT service_type FROM CollectionDB.zoo_ServiceTypes WHERE id = (SELECT service_type_id FROM CollectionDB.zoo_DeploymentMetadata WHERE id = (SELECT deployment_metadata_id FROM CollectionDB.PrivateMetadataDeploymentmetadataAssignment WHERE private_metadata_id=(SELECT id FROM CollectionDB.zoo_PrivateMetadata WHERE id = CollectionDB.ows_Process.private_metadata_id)))) as service_type,
316        (SELECT executable_name  as service_provider FROM CollectionDB.zoo_DeploymentMetadata WHERE id = (SELECT deployment_metadata_id FROM CollectionDB.PrivateMetadataDeploymentmetadataAssignment WHERE private_metadata_id=(SELECT id FROM CollectionDB.zoo_PrivateMetadata WHERE id = CollectionDB.ows_Process.private_metadata_id))) as service_provider,
317        availability
318        FROM CollectionDB.ows_Process
319        WHERE
320         availability
321        );
Note: See TracBrowser for help on using the repository browser.

Search

Context Navigation

ZOO Sponsors

http://www.zoo-project.org/trac/chrome/site/img/geolabs-logo.pnghttp://www.zoo-project.org/trac/chrome/site/img/neogeo-logo.png http://www.zoo-project.org/trac/chrome/site/img/apptech-logo.png http://www.zoo-project.org/trac/chrome/site/img/3liz-logo.png http://www.zoo-project.org/trac/chrome/site/img/gateway-logo.png

Become a sponsor !

Knowledge partners

http://www.zoo-project.org/trac/chrome/site/img/ocu-logo.png http://www.zoo-project.org/trac/chrome/site/img/gucas-logo.png http://www.zoo-project.org/trac/chrome/site/img/polimi-logo.png http://www.zoo-project.org/trac/chrome/site/img/fem-logo.png http://www.zoo-project.org/trac/chrome/site/img/supsi-logo.png http://www.zoo-project.org/trac/chrome/site/img/cumtb-logo.png

Become a knowledge partner

Related links

http://zoo-project.org/img/ogclogo.png http://zoo-project.org/img/osgeologo.png