svn commit: r968 - trunk/mapbender/resources/db/update/update_postgresql_db.sql
astrid_emde at osgeo.org
astrid_emde at osgeo.org
Fri Dec 1 02:42:07 EST 2006
Author: astrid_emde
Date: 2006-12-01 07:42:06+0000
New Revision: 968
Modified:
trunk/mapbender/resources/db/update/update_postgresql_db.sql
Log:
database changes for version 2.4.1
Modified: trunk/mapbender/resources/db/update/update_postgresql_db.sql
Url: https://mapbender.osgeo.org/source/browse/mapbender/trunk/mapbender/resources/db/update/update_postgresql_db.sql?view=diff&rev=968&p1=trunk/mapbender/resources/db/update/update_postgresql_db.sql&p2=trunk/mapbender/resources/db/update/update_postgresql_db.sql&r1=967&r2=968
==============================================================================
--- trunk/mapbender/resources/db/update/update_postgresql_db.sql (original)
+++ trunk/mapbender/resources/db/update/update_postgresql_db.sql 2006-12-01 07:42:06+0000
@@ -1,76 +1,147 @@
--
--- changes in version 2.4
+-- changes in version 2.4.1
--
--
--- new table: mb_monitor
+-- new table: keyword
--
-CREATE TABLE mb_monitor (
- upload_id varchar(255) NOT NULL default '',
- fkey_wms_id int NOT NULL default '0',
- status int4 NOT NULL,
- status_comment varchar(255) NOT NULL default '',
- timestamp_begin int4 NOT NULL,
- timestamp_end int4 NOT NULL,
- upload_url varchar(255) NOT NULL default '',
- updated char(1) NOT NULL default ''
-);
-
-ALTER TABLE ONLY mb_monitor
- ADD CONSTRAINT fkey_monitor_wms_id_wms_id FOREIGN KEY (fkey_wms_id) REFERENCES wms(wms_id) ON UPDATE CASCADE ON DELETE CASCADE;
-
-
+CREATE TABLE keyword
+(
+ keyword_id serial,
+ keyword varchar(255) NOT NULL,
+ CONSTRAINT pk_keyword_id PRIMARY KEY (keyword_id),
+ CONSTRAINT keyword_keyword_key UNIQUE (keyword)
+)
+WITH OIDS;
--
--- new table: wfs_featuretype_namespace
+-- Index: ind_keyword
--
-CREATE TABLE wfs_featuretype_namespace (
- fkey_wfs_id INTEGER NOT NULL default '0',
- fkey_featuretype_id INTEGER NOT NULL default '0',
- namespace varchar(255) NOT NULL default '',
- namespace_location varchar(255) NOT NULL default '',
- FOREIGN KEY (fkey_featuretype_id) REFERENCES wfs_featuretype (featuretype_id) ON DELETE CASCADE ON UPDATE CASCADE,
- FOREIGN KEY (fkey_wfs_id) REFERENCES wfs (wfs_id) ON DELETE CASCADE ON UPDATE CASCADE
-) ;
-
+CREATE INDEX ind_keyword ON keyword USING btree (keyword);
--
--- new column wms_owsproxy in table wms
+-- new table: layer_keyword
--
-ALTER TABLE wms ADD wms_owsproxy VARCHAR(50) NULL ;
-
+CREATE TABLE layer_keyword
+(
+ fkey_layer_id int4 NOT NULL,
+ fkey_keyword_id int4 NOT NULL,
+ CONSTRAINT fkey_keyword_id_fkey_layer_id FOREIGN KEY (fkey_keyword_id)
+ REFERENCES keyword (keyword_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT fkey_layer_id_fkey_keyword_id FOREIGN KEY (fkey_layer_id)
+ REFERENCES layer (layer_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+)
+WITH OIDS;
--
--- new column wfs_owsproxy in table wfs
+-- new columns in table wms
--
-ALTER TABLE wfs ADD wfs_owsproxy VARCHAR( 50 ) NULL ;
+ALTER TABLE wms ADD fees varchar(255) NULL;
+ALTER TABLE wms ADD accessconstraints text NULL;
+ALTER TABLE wms ADD contactperson varchar(255) NULL;
+ALTER TABLE wms ADD contactposition varchar(255) NULL;
+ALTER TABLE wms ADD contactorganization varchar(255) NULL;
+ALTER TABLE wms ADD address varchar(255) NULL;
+ALTER TABLE wms ADD city varchar(255) NULL;
+ALTER TABLE wms ADD stateorprovince varchar(255) NULL;
+ALTER TABLE wms ADD postcode varchar(255) NULL;
+ALTER TABLE wms ADD country varchar(255) NULL;
+ALTER TABLE wms ADD contactvoicetelephone varchar(255) NULL;
+ALTER TABLE wms ADD contactfacsimiletelephone varchar(255) NULL;
+ALTER TABLE wms ADD contactelectronicmailaddress varchar(255) NULL;
+ALTER TABLE wms ADD wms_mb_getcapabilies_doc text NULL;
+ALTER TABLE wms ADD wms_owner int4 NULL;
+ALTER TABLE wms ADD wms_timestamp int4 NULL;
+ALTER TABLE layer ADD layer_abstract text NULL;
--
--- new column wms_upload_url in table wms
+-- new table: md_topic_category
--
-ALTER TABLE wms ADD wms_upload_url VARCHAR(255) NULL ;
+CREATE TABLE md_topic_category (
+ md_topic_category_id serial,
+ md_topic_category_code_en varchar(255),
+ md_topic_category_code_de varchar(255),
+ CONSTRAINT md_topic_category_pkey PRIMARY KEY (md_topic_category_id)
+);
-UPDATE wms set wms_upload_url = wms_getcapabilities;
+INSERT INTO md_topic_category VALUES (1, 'farming', 'Landwirtschaft');
+INSERT INTO md_topic_category VALUES (2, 'biota', 'Biotope');
+INSERT INTO md_topic_category VALUES (3, 'boundaries', 'Grenzen');
+INSERT INTO md_topic_category VALUES (4, 'climatologyMeteorologyAtmosphere', 'Wetterkunde');
+INSERT INTO md_topic_category VALUES (5, 'economy', 'Wirtschaft');
+INSERT INTO md_topic_category VALUES (6, 'elevation', 'Höhendaten');
+INSERT INTO md_topic_category VALUES (7, 'environment', 'Umwelt');
+INSERT INTO md_topic_category VALUES (8, 'geoscientificInformation', 'Geowissenschaft');
+INSERT INTO md_topic_category VALUES (9, 'health', 'Gesundheit');
+INSERT INTO md_topic_category VALUES (10, 'imageryBaseMapsEarthCover', 'Grundlagenkarten');
+INSERT INTO md_topic_category VALUES (11, 'intelligenceMilitary', 'militärische Aufklärung');
+INSERT INTO md_topic_category VALUES (12, 'inlandWaters', 'Binnengewässer');
+INSERT INTO md_topic_category VALUES (13, 'location', 'Ortsinformationen');
+INSERT INTO md_topic_category VALUES (14, 'oceans', 'Meereskunde');
+INSERT INTO md_topic_category VALUES (15, 'planningCadastre', 'Landnutzung/Planung/Kataster');
+INSERT INTO md_topic_category VALUES (16, 'society', 'Gesellschaft');
+INSERT INTO md_topic_category VALUES (17, 'structure', 'Bauwerke');
+INSERT INTO md_topic_category VALUES (18, 'transportation', 'Transportwesen');
+INSERT INTO md_topic_category VALUES (19, 'utilitiesCommunication', 'Infrastruktur');
--
--- new column e_url in table gui_element
+-- new table: wms_md_topic_category
--
-ALTER TABLE gui_element ADD e_url VARCHAR( 255 ) NULL ;
+CREATE TABLE wms_md_topic_category (
+ fkey_wms_id int4 NOT NULL,
+ fkey_md_topic_category_id int4 NOT NULL,
+ CONSTRAINT wms_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,
+ CONSTRAINT wms_md_topic_category_fkey_wms_id_fkey FOREIGN KEY (fkey_wms_id)
+ REFERENCES wms (wms_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+--
+-- table layer_preview
+--
+CREATE TABLE layer_preview (
+ fkey_layer_id integer NOT NULL,
+ layer_map_preview bytea,
+ layer_extent_preview bytea,
+ layer_legend_preview bytea
+);
+
+ALTER TABLE ONLY layer_preview
+ ADD CONSTRAINT layer_preview_fkey_layer_id_key UNIQUE (fkey_layer_id);
+
+ALTER TABLE ONLY layer_preview
+ ADD CONSTRAINT fkey_layer_id FOREIGN KEY (fkey_layer_id) REFERENCES layer(layer_id) ON UPDATE CASCADE ON DELETE CASCADE;
-Update gui_element set e_mb_mod = 'mod_addWMSgeneralFunctions.js' where e_id = 'addWMSfromList' or e_id = 'addWMSfromfilteredList' ;
--
--- table layer_style, column title was too small. length changed to 100 from 50
+-- table layer_epsg - unique key for (fkey_layer_id, epsg)
--
-ALTER TABLE layer_style ALTER COLUMN title TYPE varchar(100);
-
+-- 1. create temporary table with distinct entries
+CREATE TABLE layer_epsg_tmp AS SELECT DISTINCT * FROM layer_epsg
+
+-- 2. check for ambiguous entries
+SELECT a.fkey_layer_id, a.epsg, a.minx, a.miny, a.maxx, a.maxy FROM layer_epsg_tmp AS a, (SELECT fkey_layer_id, epsg FROM layer_epsg_tmp GROUP BY fkey_layer_id, epsg HAVING count(*) > 1) AS b WHERE a.fkey_layer_id = b.fkey_layer_id AND a.epsg = b.epsg
+
+-- 3. if there is a result: delete ambiguous entries manually, like
+DELETE FROM layer_epsg_tmp WHERE fkey_layer_id = <id> AND epsg = '<epsg>' AND minx = <minx> AND miny = <miny> AND maxx = <maxx> AND maxy = <maxy>
+-- 4. delete old data from layer_epsg
+DELETE FROM layer_epsg_tmp WHERE fkey_layer_id = <id> AND epsg = '<epsg>' AND minx = <minx> AND miny = <miny> AND maxx = <maxx> AND maxy = <maxy>
+-- 5. move new data to layer_epsg
+INSERT INTO layer_epsg SELECT * FROM layer_epsg_tmp
+-- 6. add unique key
+INSERT INTO layer_epsg SELECT * FROM layer_epsg_tmp
+-- 7. remove temporary table
+DROP TABLE layer_epsg_tmp
More information about the Mapbender_commits
mailing list