Database Changes 2.4.1

From MapbenderWiki

Contents 1 Database changes / Datenbankänderungen 1.1 layer_epsg 1.2 layer_preview 1.3 topicCategories 1.4 expansion metadata capabilties: 1.4.1 SQL

Database changes / Datenbankänderungen

layer_epsg

unique key for (fkey_layer_id, epsg)

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 5. move new data to layer_epsg INSERT INTO layer_epsg SELECT * FROM layer_epsg_tmp 6. add unique key ALTER TABLE ONLY layer_epsg ADD CONSTRAINT layer_epsg_fkey_layer_id_key UNIQUE (fkey_layer_id, epsg) 7. remove temporary table DROP TABLE layer_epsg_tmp

layer_preview

Layer preview

topicCategories

table for topicCategories: md_topic_category

expansion metadata capabilties:

WMS:

• Fees
• Access Constraints

• ContactPerson
• ContactOrganization
• Address
• City
• StateOrProvince
• PostCode
• Country
• ContactVoiceTelephone
• ContactElectronicMailAddress

für die interne Verwaltung

• wms_owner
• wms_timestamp

zusätzlich für das Abspeichern des editierten Zustands onUpdate:

• wms_mb_getcapabilities_doc

LAYER

• Abstract (rootLayer inherits wms abstract)
• KeywordList (1:n table) (rootLayer inherits wms keywords)

SQL

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;

postgresql

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; -- Index: ind_keyword CREATE INDEX ind_keyword ON keyword USING btree (keyword);

mysql

CREATE TABLE keyword ( keyword_id int(11) NOT NULL auto_increment, keyword varchar(255) NOT NULL, PRIMARY KEY (keyword_id) ) TYPE = InnoDb; ALTER TABLE keyword ADD CONSTRAINT keyword_keyword_key UNIQUE (keyword);

mysql

CREATE TABLE layer_keyword ( fkey_layer_id int(11) NOT NULL, fkey_keyword_id int(11) NOT NULL, ) TYPE = InnoDb; ALTER TABLE layer_keyword ADD CONSTRAINT fkey_keyword_id_fkey_layer_id FOREIGN KEY (fkey_keyword_id) REFERENCES keyword (keyword_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE layer_keyword ADD CONSTRAINT fkey_layer_id_fkey_keyword_id FOREIGN KEY (fkey_layer_id) REFERENCES layer (layer_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;

postgesql

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;