[Mapbender-commits] r8433 - branches/2.7/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Fri Jul 6 03:38:32 PDT 2012
Author: astrid_emde
Date: 2012-07-06 03:38:32 -0700 (Fri, 06 Jul 2012)
New Revision: 8433
Modified:
branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7.2_to_2.7.3_pgsql_UTF-8.sql
Log:
SQL merged from trunk
Modified: branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7.2_to_2.7.3_pgsql_UTF-8.sql
===================================================================
--- branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7.2_to_2.7.3_pgsql_UTF-8.sql 2012-07-06 10:19:22 UTC (rev 8432)
+++ branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7.2_to_2.7.3_pgsql_UTF-8.sql 2012-07-06 10:38:32 UTC (rev 8433)
@@ -283,3 +283,210 @@
INSERT INTO wfs_featuretype_namespace VALUES (1,4,'topp','http://www.openplans.org/topp');
+--
+-- merged SQl from trunk
+--
+-- Function: f_normalize_load_count(text,integer)
+
+--DROP FUNCTION f_normalize_load_count(text,integer);
+
+CREATE OR REPLACE FUNCTION f_normalize_load_count(tablename text, newmaxvalue integer)
+ RETURNS void AS
+$BODY$
+DECLARE
+ tablename ALIAS FOR $1;
+ newmaxvalue ALIAS FOR $2;
+ oldmaxvalue integer;
+ querytext text;
+ result record;
+BEGIN
+ querytext:= 'SELECT max(load_count) FROM ' || lower(tablename);
+ EXECUTE querytext INTO oldmaxvalue;
+ querytext:= 'UPDATE ' || lower(tablename) || ' SET load_count = ' || 'floor((load_count::real/' || oldmaxvalue || ')*' || newmaxvalue || ')';
+ EXECUTE querytext;
+RETURN;--querytext;
+END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+
+UPDATE translations SET msgstr = 'Kartenprojektion ändern' WHERE msgstr = 'Karenprojektion ändern';
+
+ALTER TABLE gui_wms ADD COLUMN gui_wms_parent_gui character varying(50);
+
+ALTER TABLE gui_wms ADD CONSTRAINT gui_wms_ibfk_5 FOREIGN KEY (gui_wms_parent_gui) REFERENCES gui (gui_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE; -- delete wms from gui, if original gui will be deleted!
+
+
+ALTER TABLE gui_wms ADD COLUMN gui_wms_timestamp TIMESTAMP WITHOUT TIME ZONE;
+UPDATE gui_wms SET gui_wms_timestamp = to_timestamp('1970-01-01','YYYY-MM-DD') WHERE gui_wms_timestamp IS NULL;
+ALTER TABLE gui_wms ALTER COLUMN gui_wms_timestamp SET NOT NULL;
+ALTER TABLE gui_wms ALTER COLUMN gui_wms_timestamp SET DEFAULT now();
+
+-- Table: scheduler - is used to schedule ows updates
+
+-- DROP TABLE scheduler;
+
+CREATE TABLE scheduler
+(
+ scheduler_id serial NOT NULL,
+ scheduler_type character varying(50) NOT NULL DEFAULT ''::character varying,
+ fkey_wms_id integer,
+ fkey_wfs_id integer,
+ fkey_dataurl_id integer,
+ scheduler_interval interval,
+ scheduler_mail integer,
+ scheduler_publish integer,
+ scheduler_searchable integer,
+ scheduler_create timestamp without time zone,
+ scheduler_change timestamp without time zone,
+ scheduler_status integer,
+ scheduler_status_error_message text,
+ scheduler_overwrite integer,
+ CONSTRAINT pk_scheduler_id PRIMARY KEY (scheduler_id),
+ CONSTRAINT scheduler_fkey_wfs_id_fkey FOREIGN KEY (fkey_wfs_id)
+ REFERENCES wfs (wfs_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT scheduler_fkey_wms_id_fkey FOREIGN KEY (fkey_wms_id)
+ REFERENCES wms (wms_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+
+-- Table: ows_relation_data
+
+-- DROP TABLE ows_relation_data;
+
+CREATE TABLE ows_relation_data
+(
+ fkey_datalink_id integer NOT NULL,
+ fkey_layer_id integer,
+ CONSTRAINT ows_relation_data_fkey_layer_id_fkey FOREIGN KEY (fkey_layer_id)
+ REFERENCES layer (layer_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT ows_relation_data_fkey_data_id_fkey FOREIGN KEY (fkey_datalink_id)
+ REFERENCES datalink (datalink_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+-- Column: datalink_format
+
+-- ALTER TABLE datalink DROP COLUMN datalink_format;
+
+ALTER TABLE datalink ADD COLUMN datalink_format character varying(50);
+ALTER TABLE datalink ALTER COLUMN datalink_format SET NOT NULL;
+ALTER TABLE datalink ALTER COLUMN datalink_format SET DEFAULT ''::character varying;
+
+-- Column: datalink_origin
+
+-- ALTER TABLE datalink DROP COLUMN datalink_origin;
+
+ALTER TABLE datalink ADD COLUMN datalink_origin character varying(50);
+ALTER TABLE datalink ALTER COLUMN datalink_origin SET NOT NULL;
+ALTER TABLE datalink ALTER COLUMN datalink_origin SET DEFAULT ''::character varying;
+
+-- Column: randomid
+
+-- ALTER TABLE datalink DROP COLUMN randomid;
+
+ALTER TABLE datalink ADD COLUMN randomid character varying(100);
+
+ALTER TABLE datalink DROP COLUMN datalink_timestamp;
+
+ALTER TABLE datalink DROP COLUMN datalink_timestamp_create;
+
+ALTER TABLE datalink DROP COLUMN datalink_timestamp_last_usage;
+
+ALTER TABLE datalink ADD COLUMN datalink_timestamp TIMESTAMP WITHOUT TIME ZONE;
+
+ALTER TABLE datalink ADD COLUMN datalink_timestamp_create TIMESTAMP WITHOUT TIME ZONE;
+
+ALTER TABLE datalink ADD COLUMN datalink_timestamp_last_usage TIMESTAMP WITHOUT TIME ZONE;
+
+-- Function: f_count_featuretype_couplings(integer)
+
+-- DROP FUNCTION f_count_featuretype_couplings(integer);
+
+CREATE OR REPLACE FUNCTION f_count_featuretype_couplings(integer) RETURNS integer AS
+$BODY$
+DECLARE
+ featuretype_rel int4;
+BEGIN
+featuretype_rel := count(*) from ows_relation_metadata WHERE fkey_featuretype_id=$1;
+RETURN featuretype_rel;
+
+END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE
+ COST 100;
+
+
+-- Function: f_collect_inspire_cat_wfs_featuretype(integer)
+-- DROP FUNCTION f_collect_inspire_cat_wfs_featuretype(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_inspire_cat_wfs_featuretype(integer)
+ RETURNS text AS
+$BODY$DECLARE
+ i_wfs_featuretype_id ALIAS FOR $1;
+ inspire_cat_string TEXT;
+ inspire_cat_record RECORD;
+
+BEGIN
+inspire_cat_string := '';
+
+FOR inspire_cat_record IN SELECT wfs_featuretype_inspire_category.fkey_inspire_category_id from wfs_featuretype_inspire_category WHERE wfs_featuretype_inspire_category.fkey_featuretype_id=$1 LOOP
+inspire_cat_string := inspire_cat_string || '{' ||inspire_cat_record.fkey_inspire_category_id || '}';
+END LOOP ;
+
+RETURN inspire_cat_string;
+
+END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE STRICT
+ COST 100;
+
+ALTER TABLE ows_relation_metadata ADD COLUMN internal INTEGER;
+
+--new table for wfs featuretype supported crs - new up from wfs 1.1.0 - the bbox are normally not filled!
+
+CREATE TABLE wfs_featuretype_epsg
+(
+ fkey_featuretype_id integer NOT NULL DEFAULT 0,
+ epsg character varying(50) NOT NULL DEFAULT ''::character varying,
+ minx double precision DEFAULT 0,
+ miny double precision DEFAULT 0,
+ maxx double precision DEFAULT 0,
+ maxy double precision DEFAULT 0,
+ CONSTRAINT wfs_featuretype_epsg_ibfk_1 FOREIGN KEY (fkey_featuretype_id)
+ REFERENCES wfs_featuretype (featuretype_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+-- Column: featuretype_latlon_bbox
+-- ALTER TABLE wfs_featuretype DROP COLUMN featuretype_latlon_bbox;
+
+ALTER TABLE wfs_featuretype ADD COLUMN featuretype_latlon_bbox character varying;
+
+ALTER TABLE datalink RENAME column randomid TO datalink_randomid;
+
+-- Fix: Ticket #870, http://trac.osgeo.org/mapbender/ticket/870
+UPDATE gui_element set e_attributes='frameborder="1" onmouseover="this.style.zIndex=300;this.style.width=''350px'';" onmouseout="this.style.zIndex=3;this.style.width=''200px'';"' where fkey_gui_id='gui' and e_id='legend';
+
+-- add possibility to define if inspire download should be possible for wms layers and wfs featuretypes
+ALTER TABLE wfs_featuretype ADD COLUMN inspire_download integer;
+UPDATE wfs_featuretype SET inspire_download = 0 WHERE inspire_download IS NULL;
+
+ALTER TABLE layer ADD COLUMN inspire_download integer;
+UPDATE layer SET inspire_download = 0 WHERE inspire_download IS NULL;
+
+ALTER TABLE mb_monitor ALTER COLUMN map_url TYPE varchar;
+ALTER TABLE mb_monitor ALTER COLUMN cap_diff TYPE text;
+--Add handling of codespaces as demanded from the INSPIRE regulation
+ALTER TABLE mb_metadata ADD COLUMN datasetid_codespace TEXT;
+
+ALTER TABLE ows_relation_metadata ADD COLUMN relation_type TEXT;
+--update table ows_relation_metadata set type from table mb_metadata -- it is better to have it for relation not for instance - this is only a initial filling, afterwards the code handles the updates and inserts automatical
+UPDATE ows_relation_metadata SET relation_type = origin FROM mb_metadata WHERE ows_relation_metadata.fkey_metadata_id = mb_metadata.metadata_id AND ows_relation_metadata.relation_type IS NULL;
+--allow to decide if update of wms metadata will be published via twitter/rss
+UPDATE gui_element set e_content = '<span style=''float:right''><input type=''checkbox'' id=''twitter_news''>Publish via Twitter<input type=''checkbox'' id=''rss_news''>Publish via RSS</input><input disabled="disabled" type=''button'' value=''Preview metadata''><input disabled="disabled" type=''submit'' value=''Save metadata''></span>' WHERE fkey_gui_id = 'admin_wms_metadata' and e_id = 'mb_md_submit';
+
+
More information about the Mapbender_commits
mailing list