[Mapbender-commits] r5847 -
trunk/mapbender/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Fri Mar 26 11:42:38 EDT 2010
Author: astrid_emde
Date: 2010-03-26 11:42:37 -0400 (Fri, 26 Mar 2010)
New Revision: 5847
Modified:
trunk/mapbender/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql
Log:
Modived the SQLs owner postgres deleted in SQLS statements
Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql 2010-03-26 15:22:01 UTC (rev 5846)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql 2010-03-26 15:42:37 UTC (rev 5847)
@@ -12,11 +12,13 @@
alter table mb_group add column mb_group_email character varying(255) NOT NULL DEFAULT ''::character varying;
alter table mb_group add column mb_group_logo_path text NOT NULL DEFAULT ''::character varying;
alter table mb_user_mb_group add column mb_user_mb_group_type integer;
+
--Adoption for adminsitration of conformities in the mapbender database***
--searchable
ALTER TABLE layer ADD COLUMN layer_searchable integer;
ALTER TABLE layer ALTER COLUMN layer_searchable SET STORAGE PLAIN;
ALTER TABLE layer ALTER COLUMN layer_searchable SET DEFAULT 1;
+
-- Column: featuretype_searchable
-- ALTER TABLE wfs_featuretype DROP COLUMN featuretype_searchable;
@@ -30,8 +32,6 @@
-- Column: wms_timestamp_create
-
--- ALTER TABLE wms DROP COLUMN wms_timestamp_create;
ALTER TABLE wms ADD COLUMN wms_timestamp_create integer;
ALTER TABLE wms ALTER COLUMN wms_timestamp_create SET STORAGE PLAIN;
@@ -54,13 +54,6 @@
--table for inspire metadata add on fields - for layer and featuretypes
-- Table: inspire_md_data
-
--- DROP TABLE inspire_md_data;
-
--- Table: inspire_md_data
-
--- DROP TABLE inspire_md_data;
-
CREATE TABLE inspire_md_data
(
data_id serial NOT NULL,
@@ -82,13 +75,6 @@
-- Table: conformity
-
--- DROP TABLE conformity;
-
--- Table: conformity
-
--- DROP TABLE conformity;
-
CREATE TABLE conformity
(
conformity_id serial NOT NULL,
@@ -104,22 +90,12 @@
WITH OIDS;
--ALTER TABLE conformity OWNER TO "postgres";
-
INSERT INTO conformity (fkey_spec_class_key, conformity_key, conformity_code_en, conformity_code_de, conformity_code_fr, conformity_symbol, conformity_description_de) VALUES ('inspire','1','conformant','Konform','','','Die Ressource stimmt mit der angegebenen Spezifikation in vollem Umfang überein.');
-
INSERT INTO conformity (fkey_spec_class_key, conformity_key, conformity_code_en, conformity_code_de, conformity_code_fr, conformity_symbol, conformity_description_de) VALUES ('inspire','2','notConformant','Nicht konform','','','Die Ressource stimmt mit der angegebenen Spezifikation nicht überein.');
-
INSERT INTO conformity (fkey_spec_class_key, conformity_key, conformity_code_en, conformity_code_de, conformity_code_fr, conformity_symbol, conformity_description_de) VALUES ('inspire','3','notEvaluated','Nicht überprüft','','','Die Übereinstimmung ist nicht überprüft worden.');
---ok
--- spec_classification table:
--- DROP TABLE spec_classification;
-
-- Table spec_classification:
-
--- DROP TABLE spec_classification;
-
CREATE TABLE spec_classification
(
spec_class_id serial NOT NULL,
@@ -136,16 +112,11 @@
)
WITH OIDS;
---ALTER TABLE spec_class OWNER TO "postgres";
-INSERT INTO spec_classification (spec_class_key, spec_class_code_de, spec_class_description_de) VALUES ('inspire','INSPIRE','Klasse der Inspire Spezifikationen/Regulations');
--- specification table:
--- DROP TABLE spec;
--- Table spec:
+INSERT INTO spec_classification (spec_class_key, spec_class_code_de, spec_class_description_de) VALUES ('inspire','INSPIRE','Klasse der Inspire Spezifikationen/Regulations');
--- DROP TABLE spec;
-
+-- specification table:
CREATE TABLE spec
(
spec_id serial NOT NULL,
@@ -168,17 +139,12 @@
)
WITH OIDS;
---ALTER TABLE spec OWNER TO "postgres";
+
INSERT INTO spec (spec_key, spec_code_en, spec_link_en, spec_description_en, fkey_spec_class_key, spec_timestamp) VALUES ('ir_interop','INSPIRE Implementing rules laying down technical arrangements','http://www.geoportal.rlp.de/','INSPIRE Implementing rules laying down technical arrangements for the interoperability and harmonisation of orthoimagery','inspire',extract(epoch FROM (to_timestamp('2011-05-15','YYYY-MM-DD'))));
--conformity relation table:
--- DROP TABLE conformity_relation;
-
-- Table: conformity_relation
-
--- DROP TABLE conformity_relation;
-
CREATE TABLE conformity_relation
(
relation_id serial NOT NULL,
@@ -205,27 +171,9 @@
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH OIDS;
---ALTER TABLE conformity_relation OWNER TO "postgres";
---delete all of the ABOVE:
---alter table wms drop column wms_timestamp_create;
---alter table wms drop column wms_network_access;
---alter table wfs drop column wfs_timestamp_create;
---alter table wfs drop column wfs_network_access;
---DROP TABLE conformity_relation CASCADE;
---DROP TABLE spec CASCADE;
---DROP TABLE spec_classification CASCADE;
---DROP TABLE conformity CASCADE;
---DROP TABLE inspire_md_data CASCADE;
---***
---
--- Table: custom_category
--- DROP TABLE custom_category;
-
-- Table: custom_category
-
--- DROP TABLE custom_category;
-
+-- Table: custom_category
CREATE TABLE custom_category
(
custom_category_id serial NOT NULL,
@@ -238,18 +186,12 @@
CONSTRAINT custom_category_pkey PRIMARY KEY (custom_category_id)
)
WITH OIDS;
---ALTER TABLE custom_category OWNER TO "postgres";
+
INSERT INTO custom_category (custom_category_key, custom_category_code_en, custom_category_code_de, custom_category_code_fr, custom_category_symbol, custom_category_description_de) VALUES ('dc1','dummy category','Dummy Kategorie','','','Demo Kategorie zur Klassifizierung von Mapbender Registry Inhalten');
-
-
-
-- Table: layer_custom_category
-
--- DROP TABLE layer_custom_category;
-
CREATE TABLE layer_custom_category
(
fkey_layer_id integer NOT NULL,
@@ -261,15 +203,8 @@
REFERENCES custom_category (custom_category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
---WITH (OIDS=FALSE);
-ALTER TABLE layer_custom_category OWNER TO postgres;
-
-
-- Table: wfs_featuretype_custom_category
-
--- DROP TABLE wfs_featuretype_custom_category;
-
CREATE TABLE wfs_featuretype_custom_category
(
fkey_featuretype_id integer NOT NULL,
@@ -281,12 +216,8 @@
REFERENCES custom_category (custom_category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
---WITH (OIDS=FALSE);
-ALTER TABLE wfs_featuretype_custom_category OWNER TO postgres;
-
--functions to collect the categories into strings
-
CREATE OR REPLACE FUNCTION f_collect_custom_cat_layer(integer)
RETURNS text AS
$BODY$DECLARE
@@ -306,10 +237,8 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_custom_cat_layer(integer) OWNER TO postgres;
-
CREATE OR REPLACE FUNCTION f_collect_custom_cat_wfs_featuretype(integer)
RETURNS text AS
$BODY$DECLARE
@@ -329,16 +258,9 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_custom_cat_wfs_featuretype(integer) OWNER TO postgres;
-- Table: inspire_category
-
--- DROP TABLE inspire_category;
-
-- Table: inspire_category
-
--- DROP TABLE inspire_category;
-
CREATE TABLE inspire_category
(
inspire_category_id serial NOT NULL,
@@ -351,8 +273,8 @@
CONSTRAINT inspire_category_pkey PRIMARY KEY (inspire_category_id)
)
WITH OIDS;
---ALTER TABLE inspire_category OWNER TO "postgres";
+
INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.1','Coordinate reference systems','Koordinatenreferenzsysteme','','','Systeme zur eindeutigen räumlichen Referenzierung von Geodaten anhand eines Koordinatensatzes (x, y, z) und/oder Angaben zu Breite, Länge und Höhe auf der Grundlage eines geodätischen horizontalen und vertikalen Datums.');
INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.2','Geographical grid systems','Geografische Gittersysteme','','','Harmonisiertes Gittersystem mit Mehrfachauflösung, gemeinsamem Ursprungspunkt und standardisierter Lokalisierung und Größe der Gitterzellen.');
INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.3','Geographical names','Geografische Bezeichnungen','','','Namen von Gebieten, Regionen, Orten, Großstädten, Vororten, Städten oder Siedlungen sowie jedes geografische oder topografische Merkmal von öffentlichem oder historischem Interesse.');
@@ -390,9 +312,6 @@
-- Table: layer_inspire_category
-
--- DROP TABLE layer_inspire_category;
-
CREATE TABLE layer_inspire_category
(
fkey_layer_id integer NOT NULL,
@@ -404,15 +323,8 @@
REFERENCES inspire_category (inspire_category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
---WITH (OIDS=FALSE);
-ALTER TABLE layer_inspire_category OWNER TO postgres;
-
-
-- Table: wfs_featuretype_inspire_category
-
--- DROP TABLE wfs_featuretype_inspire_category;
-
CREATE TABLE wfs_featuretype_inspire_category
(
fkey_featuretype_id integer NOT NULL,
@@ -424,11 +336,8 @@
REFERENCES inspire_category (inspire_category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
---WITH (OIDS=FALSE);
-ALTER TABLE wfs_featuretype_inspire_category OWNER TO postgres;
--functions to collect the categories into strings
-
CREATE OR REPLACE FUNCTION f_collect_inspire_cat_layer(integer)
RETURNS text AS
$BODY$DECLARE
@@ -448,10 +357,8 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_inspire_cat_layer(integer) OWNER TO postgres;
-
CREATE OR REPLACE FUNCTION f_collect_inspire_cat_wfs_featuretype(integer)
RETURNS text AS
$BODY$DECLARE
@@ -471,12 +378,9 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_inspire_cat_wfs_featuretype(integer) OWNER TO postgres;
--- Table: wfs_featuretype_md_topic_category
--- DROP TABLE wfs_featuretype_md_topic_category;
-
+-- Table: wfs_featuretype_md_topic_category
CREATE TABLE wfs_featuretype_md_topic_category
(
fkey_featuretype_id integer NOT NULL,
@@ -489,7 +393,6 @@
ON UPDATE CASCADE ON DELETE CASCADE
)
WITHOUT OIDS;
-ALTER TABLE wfs_featuretype_md_topic_category OWNER TO "postgres";
CREATE OR REPLACE FUNCTION f_collect_topic_cat_layer(integer)
@@ -511,10 +414,8 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_topic_cat_layer(integer) OWNER TO postgres;
-
CREATE OR REPLACE FUNCTION f_collect_topic_cat_wfs_featuretype(integer)
RETURNS text AS
$BODY$DECLARE
@@ -534,26 +435,16 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_topic_cat_wfs_featuretype(integer) OWNER TO postgres;
--generate the group for the decentral registrating institutions
-
-- View: registrating_groups
-
--- DROP VIEW registrating_groups;
-
CREATE OR REPLACE VIEW registrating_groups AS
SELECT f.fkey_mb_group_id, f.fkey_mb_user_id
FROM mb_user_mb_group f, mb_user_mb_group s
WHERE f.mb_user_mb_group_type = 1 AND s.fkey_mb_group_id = 36 AND f.fkey_mb_user_id = s.fkey_mb_user_id
ORDER BY f.fkey_mb_group_id, f.fkey_mb_user_id;
-ALTER TABLE registrating_groups OWNER TO postgres;
-
-- Function: f_getwfs_tou(integer)
-
--- DROP FUNCTION f_getwfs_tou(integer);
-
CREATE OR REPLACE FUNCTION f_getwfs_tou(integer)
RETURNS integer AS
$BODY$
@@ -566,13 +457,8 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-ALTER FUNCTION f_getwfs_tou(integer) OWNER TO postgres;
-
-- Function: f_getwms_tou(integer)
-
--- DROP FUNCTION f_getwms_tou(integer);
-
CREATE OR REPLACE FUNCTION f_getwms_tou(integer)
RETURNS integer AS
$BODY$
@@ -585,12 +471,8 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-ALTER FUNCTION f_getwms_tou(integer) OWNER TO postgres;
-- Function: f_collect_epsg(integer)
-
--- DROP FUNCTION f_collect_epsg(integer);
-
CREATE OR REPLACE FUNCTION f_collect_epsg(integer)
RETURNS text AS
$BODY$DECLARE
@@ -615,12 +497,8 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_epsg(integer) OWNER TO postgres;
-- Function: f_layer_load_count(integer)
-
--- DROP FUNCTION f_layer_load_count(integer);
-
CREATE OR REPLACE FUNCTION f_layer_load_count(integer)
RETURNS integer AS
$BODY$
@@ -633,12 +511,8 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-ALTER FUNCTION f_layer_load_count(integer) OWNER TO postgres;
-- Function: f_collect_searchtext(integer, integer)
-
--- DROP FUNCTION f_collect_searchtext(integer, integer);
-
CREATE OR REPLACE FUNCTION f_collect_searchtext(integer, integer)
RETURNS text AS
$BODY$DECLARE
@@ -668,12 +542,9 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_searchtext(integer, integer) OWNER TO "postgres";
-- Function: f_collect_searchtext_wfs(integer, integer)
-
--- DROP FUNCTION f_collect_searchtext_wfs(integer, integer);
-
+-- DROP FUNCTION f_collect_searchtext_wfs(integer, integer)
CREATE OR REPLACE FUNCTION f_collect_searchtext_wfs(integer, integer)
RETURNS text AS
$BODY$
@@ -698,12 +569,8 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-ALTER FUNCTION f_collect_searchtext_wfs(integer, integer) OWNER TO postgres;
-- Function: f_getwfsmodultype(integer)
-
--- DROP FUNCTION f_getwfsmodultype(integer);
-
CREATE OR REPLACE FUNCTION f_getwfsmodultype(integer)
RETURNS integer AS
$BODY$
@@ -720,12 +587,9 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-ALTER FUNCTION f_getwfsmodultype(integer) OWNER TO postgres;
--- View: search_wms_view
--- DROP VIEW search_wms_view;
-
+-- View: search_wms_view
CREATE OR REPLACE VIEW search_wms_view AS
SELECT DISTINCT ON (wms_unref.layer_id) wms_unref.wms_id, wms_unref.availability, wms_unref.status, wms_unref.wms_title, wms_unref.wms_abstract, wms_unref.stateorprovince, wms_unref.country, wms_unref.accessconstraints, wms_unref.termsofuse, wms_unref.wms_owner, wms_unref.layer_id, wms_unref.epsg, wms_unref.layer_title, wms_unref.layer_abstract, wms_unref.layer_name, wms_unref.layer_parent, wms_unref.layer_pos, wms_unref.layer_queryable, wms_unref.load_count, wms_unref.searchtext, wms_unref.wms_timestamp, wms_unref.department, wms_unref.user_mb_group_name, f_collect_custom_cat_layer(wms_unref.layer_id) AS md_custom_cats, f_collect_inspire_cat_layer(wms_unref.layer_id) AS md_inspire_cats, f_collect_topic_cat_layer(wms_unref.layer_id) AS md_topic_cats, geometryfromtext(((((((((((((((((((('POLYGON(('::text || layer_epsg.minx::text) || ' '::text) || layer_epsg.miny::text) || ','::text) || layer_epsg.minx::text) || ' '::text) || layer_epsg.maxy::text) || ','::text) || layer_epsg.maxx::text) || ' '::text) || layer_epsg.maxy::text) || ','::text) || layer_epsg.maxx::text) || ' '::text) || layer_epsg.miny::text) || ','::text) || layer_epsg.minx::text) || ' '::text) || layer_epsg.miny::text) || '))'::text, 4326) AS the_geom, (((((layer_epsg.minx::text || ','::text) || layer_epsg.miny::text) || ','::text) || layer_epsg.maxx::text) || ','::text) || layer_epsg.maxy::text AS bbox, wms_unref.wms_proxylog, wms_unref.wms_network_access, wms_unref.wms_pricevolume
FROM ( SELECT wms_uncat.wms_id, wms_uncat.availability, wms_uncat.status, wms_uncat.wms_title, wms_uncat.wms_abstract, wms_uncat.stateorprovince, wms_uncat.country, wms_uncat.accessconstraints, wms_uncat.termsofuse, wms_uncat.wms_owner, wms_uncat.layer_id, wms_uncat.epsg, wms_uncat.layer_title, wms_uncat.layer_abstract, wms_uncat.layer_name, wms_uncat.layer_parent, wms_uncat.layer_pos, wms_uncat.layer_queryable, wms_uncat.load_count, wms_uncat.searchtext, wms_uncat.wms_timestamp, wms_uncat.department, wms_uncat.user_mb_group_name, wms_uncat.wms_proxylog, wms_uncat.wms_network_access, wms_uncat.wms_pricevolume
@@ -737,16 +601,7 @@
WHERE layer_epsg.epsg::text = 'EPSG:4326'::text AND wms_unref.layer_id = layer_epsg.fkey_layer_id
ORDER BY wms_unref.layer_id;
-ALTER TABLE search_wms_view OWNER TO postgres;
-
-
-
-
-
-- View: search_wfs_view
-
--- DROP VIEW search_wfs_view;
-
CREATE OR REPLACE VIEW search_wfs_view AS
SELECT wfs_dep.wfs_id, wfs_dep.wfs_title, wfs_dep.wfs_abstract, wfs_dep.administrativearea, wfs_dep.country, wfs_dep.accessconstraints, wfs_dep.termsofuse, wfs_dep.wfs_owner, wfs_featuretype.featuretype_id, wfs_featuretype.featuretype_srs, wfs_featuretype.featuretype_title, wfs_featuretype.featuretype_abstract, f_collect_searchtext_wfs(wfs_dep.wfs_id, wfs_featuretype.featuretype_id) AS searchtext, wfs_element.element_type, wfs_conf.wfs_conf_id, wfs_conf.wfs_conf_abstract, wfs_conf.wfs_conf_description, f_getwfsmodultype(wfs_conf.wfs_conf_id) AS modultype, wfs_dep.wfs_timestamp, wfs_dep.department, wfs_dep.mb_group_name
FROM ( SELECT wfs.wfs_id, wfs.wfs_title, wfs.wfs_abstract, wfs.administrativearea, wfs.country, wfs.accessconstraints, f_getwfs_tou(wfs.wfs_id) AS termsofuse, wfs.wfs_timestamp, wfs.wfs_owner, user_dep.mb_group_id AS department, user_dep.mb_group_name
@@ -757,7 +612,7 @@
WHERE wfs_featuretype.fkey_wfs_id = wfs_dep.wfs_id AND wfs_featuretype.featuretype_searchable = 1 AND wfs_element.element_type::text ~~ '%Type'::text AND wfs_featuretype.featuretype_id = wfs_element.fkey_featuretype_id AND wfs_featuretype.featuretype_id = wfs_conf.fkey_featuretype_id
ORDER BY wfs_featuretype.featuretype_id;
-ALTER TABLE search_wfs_view OWNER TO postgres;
+--metadata_pgsql.ALTER TABLE search_wfs_view OWNER TO postgres;
More information about the Mapbender_commits
mailing list