[Mapbender_dev] [Mapbender] #819: WMC Update to 2.7

[Mapbender]

#819: WMC Update to 2.7
--------------------------+-------------------------------------------------
 Reporter:  marc          |       Owner:  dev          
     Type:  defect        |      Status:  new          
 Priority:  major         |   Milestone:  2.7.2 release
Component:  installation  |     Version:  2.7.1        
 Keywords:                |  
--------------------------+-------------------------------------------------
 The Update Script to 2.7 threw some error when wmc entries already
 existing in table mb_user_wmc.[[BR]]
 Therefor some new WMC Tables will not be created.[[BR]]

 Here's an abstract of my error.log [[BR]]
 {{{
 psql:pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql:605: ERROR:
 ERROR:  column "wmc_serial_id" contains null values
 psql:pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql:618: ERROR:
 column "wmc_serial_id" contains null values
 psql:pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql:626: ERROR:
 relation "wmc_keyword" does not exist
 psql:pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql:2385:
 ERROR:  there is no unique constraint matching given keys for referenced
 table "mb_user_wmc"
 psql:pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql:2403:
 ERROR:  there is no unique constraint matching given keys for referenced
 table "mb_user_wmc"
 psql:pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql:2418:
 ERROR:  there is no unique constraint matching given keys for referenced
 table "mb_user_wmc"
 psql:pgsql/UTF-8/update/update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql:2431:
 ERROR:  there is no unique constraint matching given keys for referenced
 table "mb_user_wmc"
 }}}
 the first error is thrown because the new constraint cannot be set.

 {{{
 (This is Code of update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql  from line 615.)


 --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 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;
 ALTER  TABLE mb_user_wmc ADD COLUMN wmc_serial_id INTEGER;
 ALTER TABLE mb_user_wmc ADD COLUMN wmc_timestamp_create INTEGER;
 ALTER  TABLE mb_user_wmc ALTER COLUMN wmc_serial_id SET DEFAULT
 nextval('mb_user_wmc_wmc_serial_id_seq');
 --UPDATE mb_user_wmc SET wmc_serial_id =
 NEXTVAL('mb_user_wmc_wmc_serial_id_seq'); TODO: this is only for older
 installations - if a serial column exists before, the serial ids will be
 updated - that is not what we want cause this are the references!

 -- Constraint: pk_user_wmc

 ALTER TABLE mb_user_wmc
   ADD CONSTRAINT pk_user_wmc PRIMARY KEY(wmc_serial_id);
 }}}

 the other errors occurs because following new tables reference's to it.


 {{{
 CREATE TABLE wmc_keyword (
         fkey_keyword_id INTEGER REFERENCES keyword(keyword_id) ON DELETE
 CASCADE ON UPDATE CASCADE,
         fkey_wmc_serial_id INTEGER REFERENCES mb_user_wmc(wmc_serial_id)
 ON DELETE CASCADE ON UPDATE CASCADE
 );

 ALTER TABLE ONLY wmc_keyword
      ADD CONSTRAINT pk_wmc_keyword PRIMARY KEY (fkey_wmc_serial_id,
 fkey_keyword_id);

 --adopt relation for old implementations:
 --create a new one and drop the old!

 --new categories for publishing -> custom , inspire ?
 -- Table: wmc_custom_category
 CREATE TABLE wmc_md_topic_category
 (
   fkey_wmc_serial_id integer NOT NULL,
   fkey_md_topic_category_id integer NOT NULL,
   CONSTRAINT wmc_topic_category_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,
   CONSTRAINT wmc_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
 );


 --new categories for publishing -> custom , inspire ?
 -- Table: wmc_custom_category
 CREATE TABLE wmc_custom_category
 (
   fkey_wmc_serial_id integer NOT NULL,
   fkey_custom_category_id integer NOT NULL,
   CONSTRAINT wmc_custom_category_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,
   CONSTRAINT wmc_custom_category_fkey_custom_category_id_fkey FOREIGN KEY
 (fkey_custom_category_id)
       REFERENCES custom_category (custom_category_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
 );

 -- Table: wmc_inspire_category
 CREATE TABLE wmc_inspire_category
 (
   fkey_wmc_serial_id integer NOT NULL,
   fkey_inspire_category_id integer NOT NULL,
   CONSTRAINT wmc_inspire_category_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,
   CONSTRAINT wmc_inspire_category_fkey_inspire_category_id_fkey FOREIGN
 KEY (fkey_inspire_category_id)
       REFERENCES inspire_category (inspire_category_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
 );

 }}}

-- 
Ticket URL: <http://trac.osgeo.org/mapbender/ticket/819>
Mapbender <http://www.mapbender.org/>
Mapbender