[Mapbender-commits] r7870 -
branches/2.7/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Wed Jun 15 10:45:42 EDT 2011
Author: verenadiewald
Date: 2011-06-15 07:45:42 -0700 (Wed, 15 Jun 2011)
New Revision: 7870
Modified:
branches/2.7/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql
branches/2.7/resources/db/pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql
branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7rc1_to_2.7rc2_pgsql_UTF-8.sql
Log:
cleanup sqls for compatibility with postgres 8.1
Modified: branches/2.7/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql
===================================================================
--- branches/2.7/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql 2011-06-15 13:25:13 UTC (rev 7869)
+++ branches/2.7/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql 2011-06-15 14:45:42 UTC (rev 7870)
@@ -63,8 +63,7 @@
data_spatial_res_value varchar(255), --
data_spatial_res_type integer, --look up types like 1:equivalentScale, 2:Distance - see guidance paper for metadata
CONSTRAINT data_id_pkey PRIMARY KEY (data_id)
-)
-WITH OIDS;
+);
--ALTER TABLE inspire_md_data OWNER TO "postgres";
@@ -86,8 +85,7 @@
conformity_symbol character varying(255),
conformity_description_de text,
CONSTRAINT conformity_pkey PRIMARY KEY (conformity_id)
-)
-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.');
@@ -110,8 +108,7 @@
spec_class_timestamp integer,
CONSTRAINT spec_class_id_pkey PRIMARY KEY (spec_class_id)
-)
-WITH OIDS;
+);
INSERT INTO spec_classification (spec_class_key, spec_class_code_de, spec_class_description_de) VALUES ('inspire','INSPIRE','Klasse der Inspire Spezifikationen/Regulations');
@@ -137,8 +134,7 @@
REFERENCES spec_classification (spec_class_key) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH OIDS;
+);
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'))));
@@ -169,8 +165,7 @@
CONSTRAINT conformity_relation_spec_fkey FOREIGN KEY (fkey_spec_id)
REFERENCES spec (spec_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH OIDS;
+);
-- Table: custom_category
-- Table: custom_category
@@ -184,8 +179,7 @@
custom_category_symbol character varying(255),
custom_category_description_de text,
CONSTRAINT custom_category_pkey PRIMARY KEY (custom_category_id)
-)
-WITH OIDS;
+);
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');
@@ -271,8 +265,7 @@
inspire_category_symbol character varying(255),
inspire_category_description_de text,
CONSTRAINT inspire_category_pkey PRIMARY KEY (inspire_category_id)
-)
-WITH OIDS;
+);
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.');
@@ -391,8 +384,7 @@
CONSTRAINT wfs_featuretype_md_topic_category_fkey_md_topic_cat_id_fkey FOREIGN KEY (fkey_md_topic_category_id)
REFERENCES md_topic_category (md_topic_category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITHOUT OIDS;
+);
CREATE OR REPLACE FUNCTION f_collect_topic_cat_layer(integer)
Modified: branches/2.7/resources/db/pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql
===================================================================
--- branches/2.7/resources/db/pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql 2011-06-15 13:25:13 UTC (rev 7869)
+++ branches/2.7/resources/db/pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql 2011-06-15 14:45:42 UTC (rev 7870)
@@ -215,8 +215,7 @@
CONSTRAINT mb_wms_availability_fkey_wms_id_wms_id FOREIGN KEY (fkey_wms_id)
REFERENCES wms (wms_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH OIDS;
+);
-- Function: mb_monitor_after()
@@ -288,8 +287,7 @@
CONSTRAINT mb_user_abo_ows_wms_fkey FOREIGN KEY (fkey_wms_id)
REFERENCES wms (wms_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITHOUT OIDS;
+);
-- Index: idx_wms_id
@@ -330,8 +328,7 @@
pixel bigint,
price real
-)
-with oids;
+);
ALTER TABLE wms ADD COLUMN wms_proxylog integer;
ALTER TABLE wms ALTER COLUMN wms_proxylog SET STORAGE PLAIN;
ALTER TABLE wms ADD COLUMN wms_pricevolume integer;
@@ -596,12 +593,12 @@
--*****wmc adoptions****
-DROP TABLE IF EXISTS wmc_keyword CASCADE;
-DROP TABLE IF EXISTS wmc_md_topic_category CASCADE;
+DROP TABLE wmc_keyword CASCADE;
+DROP TABLE wmc_md_topic_category CASCADE;
--adopt mb_user_wmc to store a serial column too!
-ALTER TABLE mb_user_wmc DROP CONSTRAINT pk_user_wmc;
+ALTER TABLE mb_user_wmc DROP CONSTRAINT pk_user_wmc CASCADE;
ALTER TABLE mb_user_wmc DROP CONSTRAINT mb_user_wmc_pkey; --for older implementations
--DROP SEQUENCE mb_user_wmc_wmc_serial_id_seq cascade;
CREATE SEQUENCE mb_user_wmc_wmc_serial_id_seq;
@@ -1675,8 +1672,7 @@
data_spatial_res_value varchar(255), --
data_spatial_res_type integer, --look up types like 1:equivalentScale, 2:Distance - see guidance paper for metadata
CONSTRAINT data_id_pkey PRIMARY KEY (data_id)
-)
-WITH OIDS;
+);
--ALTER TABLE inspire_md_data OWNER TO "postgres";
@@ -1698,8 +1694,7 @@
conformity_symbol character varying(255),
conformity_description_de text,
CONSTRAINT conformity_pkey PRIMARY KEY (conformity_id)
-)
-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.');
@@ -1722,8 +1717,7 @@
spec_class_timestamp integer,
CONSTRAINT spec_class_id_pkey PRIMARY KEY (spec_class_id)
-)
-WITH OIDS;
+);
INSERT INTO spec_classification (spec_class_key, spec_class_code_de, spec_class_description_de) VALUES ('inspire','INSPIRE','Klasse der Inspire Spezifikationen/Regulations');
@@ -1749,8 +1743,7 @@
REFERENCES spec_classification (spec_class_key) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH OIDS;
+);
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'))));
@@ -1781,8 +1774,7 @@
CONSTRAINT conformity_relation_spec_fkey FOREIGN KEY (fkey_spec_id)
REFERENCES spec (spec_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH OIDS;
+);
-- Table: custom_category
-- Table: custom_category
@@ -1796,8 +1788,7 @@
custom_category_symbol character varying(255),
custom_category_description_de text,
CONSTRAINT custom_category_pkey PRIMARY KEY (custom_category_id)
-)
-WITH OIDS;
+);
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');
@@ -1887,8 +1878,7 @@
inspire_category_symbol character varying(255),
inspire_category_description_de text,
CONSTRAINT inspire_category_pkey PRIMARY KEY (inspire_category_id)
-)
-WITH OIDS;
+);
@@ -2010,8 +2000,7 @@
CONSTRAINT wfs_featuretype_md_topic_category_fkey_md_topic_cat_id_fkey FOREIGN KEY (fkey_md_topic_category_id)
REFERENCES md_topic_category (md_topic_category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITHOUT OIDS;
+);
CREATE OR REPLACE FUNCTION f_collect_topic_cat_layer(integer)
@@ -2208,7 +2197,7 @@
LANGUAGE 'plpgsql' VOLATILE;
--MetadataViews
-DROP VIEW IF EXISTS search_wms_view;
+DROP 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.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, wms_unref.mb_group_logo_path
@@ -2223,7 +2212,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;
-DROP VIEW IF EXISTS search_wfs_view;
+DROP VIEW search_wfs_view;
-- 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, wfs_dep.mb_group_logo_path
@@ -2265,8 +2254,7 @@
inspire_top_consistance boolean,
CONSTRAINT metadata_pkey PRIMARY KEY (metadata_id),
CONSTRAINT metadata_uuid_key UNIQUE (uuid)
-)
-WITH OIDS;
+);
@@ -2290,8 +2278,7 @@
CONSTRAINT ows_relation_metadata_fkey_metadata_id_fkey FOREIGN KEY (fkey_metadata_id)
REFERENCES content_metadata (metadata_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITHOUT OIDS;
+);
----
-------
@@ -2503,7 +2490,7 @@
-- View: search_wmc_view
-DROP VIEW IF EXISTS search_wmc_view;
+DROP VIEW search_wmc_view;
--TODO: get group information out of mb_group table instead of ows information, cause in this case there can be more than one different service in a resource
--TODO: set public flag to some wmc docs to generate usefull results
--TODO: get infos from old wmc docs into table structure (abstract, coords, ...)
@@ -2551,8 +2538,7 @@
REFERENCES layer (layer_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT layer_preview_fkey_layer_id_key UNIQUE (fkey_layer_id)
-)
-WITH (OIDS=FALSE);
+);
-- Table: termsofuse
@@ -2564,8 +2550,7 @@
description character varying(255),
descriptionlink character varying(255),
CONSTRAINT termsofuse_pkey PRIMARY KEY (termsofuse_id)
-)
-WITH OIDS;
+);
-- Table: wfs_termsofuse
@@ -2580,8 +2565,7 @@
CONSTRAINT wfs_termsofuse_termsofuse_fkey FOREIGN KEY (fkey_termsofuse_id)
REFERENCES termsofuse (termsofuse_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH OIDS;
+);
-- Table: wms_termsofuse
@@ -2595,8 +2579,7 @@
CONSTRAINT wms_termsofuse_wms_fkey FOREIGN KEY (fkey_wms_id)
REFERENCES wms (wms_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH OIDS;
+);
-- Table: layer_md_topic_category
@@ -2612,8 +2595,7 @@
KEY (fkey_md_topic_category_id)
REFERENCES md_topic_category (md_topic_category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITHOUT OIDS;
+);
-- terms of use content
@@ -2766,9 +2748,6 @@
CONSTRAINT datalink_owner_fkey FOREIGN KEY (datalink_owner)
REFERENCES mb_user (mb_user_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH (
- OIDS=FALSE
);
@@ -2787,9 +2766,6 @@
CONSTRAINT fkey_datalink_id_fkey_keyword_id FOREIGN KEY (fkey_datalink_id)
REFERENCES datalink (datalink_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH (
- OIDS=FALSE
);
-- Table: datalink_md_topic_category
@@ -2806,9 +2782,6 @@
CONSTRAINT datalink_md_topic_category_fkey_md_topic_category_id_fkey FOREIGN KEY (fkey_md_topic_category_id)
REFERENCES md_topic_category (md_topic_category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH (
- OIDS=FALSE
);
@@ -2965,9 +2938,6 @@
REFERENCES mb_user_wmc (wmc_serial_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT wmc_fkey_layer_id_key UNIQUE (fkey_wmc_serial_id)
-)
-WITH (
- OIDS=FALSE
);
--ALTER TABLE wmc_preview OWNER TO postgres;
-- Table wmc_load_count
@@ -2981,8 +2951,7 @@
CONSTRAINT wmc_load_count_fkey_wmc_serial_id_fkey FOREIGN KEY (fkey_wmc_serial_id)
REFERENCES mb_user_wmc (wmc_serial_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
-)
-WITH (OIDS=TRUE);
+);
--ALTER TABLE wmc_load_count OWNER TO postgres;
-- Index: idx_fkey_layer_id
Modified: branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7rc1_to_2.7rc2_pgsql_UTF-8.sql
===================================================================
--- branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7rc1_to_2.7rc2_pgsql_UTF-8.sql 2011-06-15 13:25:13 UTC (rev 7869)
+++ branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7rc1_to_2.7rc2_pgsql_UTF-8.sql 2011-06-15 14:45:42 UTC (rev 7870)
@@ -22,8 +22,7 @@
END IF;
END;
$BODY$
- LANGUAGE 'plpgsql' VOLATILE
- COST 100;
+ LANGUAGE 'plpgsql' VOLATILE;
--
@@ -72,8 +71,7 @@
RETURN l_result;
END;
$BODY$
- LANGUAGE 'plpgsql' VOLATILE
- COST 100;
+ LANGUAGE 'plpgsql' VOLATILE;
--Bugfix in integral monitoring table
More information about the Mapbender_commits
mailing list