WMC Update to 2.7

[marc]

The Update Script to 2.7 threw some error when wmc entries already existing in table mb_user_wmc.
Therefor some new WMC Tables will not be created.

Here's an abstract of my error.log

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

[marc]

a sollution
before running the update script.

change following line (611) of update_2.6.2_to_2.7rc1_pgsql_UTF-8.sql

--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!

to

UPDATE mb_user_wmc SET wmc_serial_id = NEXTVAL('mb_user_wmc_wmc_serial_id_seq') WHERE wmc_serial_id is null;

or do this manually afterwards...
by running this sql.

UPDATE mb_user_wmc SET wmc_serial_id = NEXTVAL('mb_user_wmc_wmc_serial_id_seq') WHERE wmc_serial_id is null;
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
);

[marc]

fixed update_sql in 2.7 and trunk
see ​here