Database Changes 2.4

From MapbenderWiki

Contents 1 Database changes / Datenbankänderungen 2 mb_monitor 3 WMS Layer 4 namespaces 5 owsproxy 6 TABLE WMS 7 TABLE gui_element 8 TABLE wfs

Database changes / Datenbankänderungen

The following changes are necessary for the update from Version 2.2.3 to 2.4

Diese Änderungen sind nur für den Update von 2.2.3 nach 2.4 erforderlich.

mb_monitor

MySQL:

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 '' ) TYPE = InnoDb; ALTER TABLE mb_monitor ADD CONSTRAINT mb_monitor_ibfk_1 FOREIGN KEY (fkey_wms_id) REFERENCES wms (wms_id) ON DELETE CASCADE ON UPDATE CASCADE;

pgSQL:

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;

WMS Layer

MySQL: ALTER TABLE layer_style CHANGE title title VARCHAR(100);

pgSQL: ALTER TABLE layer_style ALTER COLUMN title TYPE varchar(100);

When updating a WMS with the monitoring tool I ran into problems with layer styles (table layer_style): The length of the title is restricted to 50 characters; the layer title in question ('Arbitrary color image of the SRTM averaged reflectance') had 54 characters so the WMS update failed.

Beim Update eines WMS mit dem Monitor-Tool gab es Probleme mit den Layerstyle (Tabelle layer_style): Die Länge des Titels ist auf 50 Zeichen beschränkt; der betroffene Layertitel ('Arbitrary color image of the SRTM averaged reflectance') hatte 54 Zeichen, somit konnte der WMS nicht aktualisiert werden.

namespaces

new table wfs_featuretype_namespace

mySQL: CREATE TABLE wfs_featuretype_namespace ( fkey_wfs_id int(11) NOT NULL default '0', fkey_featuretype_id int(11) NOT NULL default '0', namespace varchar(255) NOT NULL default '', namespace_location varchar(255) NOT NULL default '', KEY wfs_id (`fkey_wfs_id`), KEY featuretype_id (`fkey_featuretype_id`) ) TYPE=InnoDB; ALTER TABLE wfs_featuretype_namespace ADD CONSTRAINT wfs_featuretype_namespace_ibfk_2 FOREIGN KEY (fkey_featuretype_id) REFERENCES wfs_featuretype (featuretype_id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT wfs_featuretype_namespace_ibfk_1 FOREIGN KEY (fkey_wfs_id) REFERENCES wfs (wfs_id) ON DELETE CASCADE ON UPDATE CASCADE;

pgSQL:

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 ) ;

owsproxy

new column wms_owsproxy for a md5-string

Mysql / pgSQL: ALTER TABLE wms ADD wms_owsproxy VARCHAR( 50 ) NULL ;

TABLE WMS

new column for the upload-url ALTER TABLE wms ADD wms_upload_url VARCHAR( 255 ) NULL ;

TABLE gui_element

new column e_url ALTER TABLE gui_element ADD e_url VARCHAR( 255 ) NULL ;

TABLE wfs

new column wfs_owsproxy ALTER TABLE wfs ADD wfs_owsproxy VARCHAR( 50 ) NULL ;