update_from_45_to_20150103.sql

gistfile1.txt

-- 4.5.0 -> 4.6.0

UPDATE ezsite_data SET value='4.6.0' WHERE name='ezpublish-version';
UPDATE ezsite_data SET value='1' WHERE name='ezpublish-release';

CREATE SEQUENCE ezorder_nr_incr_s
    START 1
    INCREMENT 1
    MAXVALUE 9223372036854775807
    MINVALUE 1
    CACHE 1;

CREATE TABLE ezorder_nr_incr (
    id integer DEFAULT nextval('ezorder_nr_incr_s'::text) NOT NULL
);

ALTER TABLE ONLY ezorder_nr_incr
    ADD CONSTRAINT ezorder_nr_incr_pkey PRIMARY KEY (id);

-- #18514 store affected class ids in data_text5 instead of data_text3 for multiplexer workflow event type
UPDATE ezworkflow_event SET data_text5 = data_text3, data_text3 = '' WHERE workflow_type_string = 'event_ezmultiplexer';


-- 4.6.0 -> 4.7.0

UPDATE ezsite_data SET value='4.7.0' WHERE name='ezpublish-version';
UPDATE ezsite_data SET value='1' WHERE name='ezpublish-release';

ALTER TABLE ezcollab_item
    ALTER COLUMN data_float1 TYPE real,
    ALTER COLUMN data_float2 TYPE real,
    ALTER COLUMN data_float3 TYPE real,
    ALTER COLUMN data_float1 SET DEFAULT (0)::real,
    ALTER COLUMN data_float2 SET DEFAULT (0)::real,
    ALTER COLUMN data_float3 SET DEFAULT (0)::real;

ALTER TABLE ezcollab_simple_message
    ALTER COLUMN data_float1 TYPE real,
    ALTER COLUMN data_float2 TYPE real,
    ALTER COLUMN data_float3 TYPE real,
    ALTER COLUMN data_float1 SET DEFAULT (0)::real,
    ALTER COLUMN data_float2 SET DEFAULT (0)::real,
    ALTER COLUMN data_float3 SET DEFAULT (0)::real;

ALTER TABLE ezcontentclass_attribute
    ALTER COLUMN data_float1 TYPE real,
    ALTER COLUMN data_float2 TYPE real,
    ALTER COLUMN data_float3 TYPE real,
    ALTER COLUMN data_float4 TYPE real;

ALTER TABLE ezcontentobject_attribute ALTER COLUMN data_float TYPE real;

ALTER TABLE ezdiscountsubrule ALTER COLUMN discount_percent TYPE real;

ALTER TABLE ezinfocollection_attribute ALTER COLUMN data_float TYPE real;

ALTER TABLE ezorder_item
    ALTER COLUMN price TYPE real,
    ALTER COLUMN vat_value TYPE real,
    ALTER COLUMN vat_value SET DEFAULT (0)::real;

ALTER TABLE ezproductcollection_item
    ALTER COLUMN discount TYPE real,
    ALTER COLUMN price TYPE real,
    ALTER COLUMN vat_value TYPE real,
    ALTER COLUMN price SET DEFAULT (0)::real;

ALTER TABLE ezproductcollection_item_opt
    ALTER COLUMN price TYPE real,
    ALTER COLUMN price SET DEFAULT (0)::real;

ALTER TABLE ezsearch_object_word_link
    ALTER COLUMN frequency TYPE real,
    ALTER COLUMN frequency SET DEFAULT (0)::real;

ALTER TABLE ezvattype ALTER COLUMN percentage TYPE real;


CREATE SEQUENCE ezpending_actions_s
    START 1
    INCREMENT 1
    MAXVALUE 9223372036854775807
    MINVALUE 1
    CACHE 1;

ALTER TABLE ezpending_actions ADD id integer DEFAULT nextval('ezpending_actions_s'::text) NOT NULL;
UPDATE ezpending_actions SET id=nextval('ezpending_actions_s'::text);

-- Cleanup for #18886
-- when a user is manually enabled through the admin interface,
-- the corresponding ezuser_accountkey record is not removed
DELETE FROM ezuser_accountkey WHERE user_id IN ( SELECT user_id FROM ezuser_setting WHERE is_enabled = 1 );

ALTER TABLE ezcontentobject_attribute ALTER COLUMN data_float TYPE double precision;
ALTER TABLE ezcontentclass_attribute
    ALTER COLUMN data_float1 TYPE double precision,
    ALTER COLUMN data_float2 TYPE double precision,
    ALTER COLUMN data_float3 TYPE double precision,
    ALTER COLUMN data_float4 TYPE double precision;

ALTER TABLE ONLY ezpending_actions
    ADD CONSTRAINT ezpending_actions_pkey PRIMARY KEY (id);

UPDATE eztrigger SET name = 'pre_updatemainassignment', function_name = 'updatemainassignment'
  WHERE name = 'pre_UpdateMainAssignment' AND function_name = 'UpdateMainAssignment';


-- 4.7.0 -> 5.0.0

UPDATE ezsite_data SET value='5.0.0' WHERE name='ezpublish-version';
UPDATE ezsite_data SET value='1' WHERE name='ezpublish-release';

-- See http://issues.ez.no/19169
ALTER TABLE ezcobj_state_group_language ADD real_language_id integer DEFAULT 0 NOT NULL;
UPDATE ezcobj_state_group_language SET real_language_id = language_id & ~1;

-- dropping the primary key (contentobject_state_group_id, language_id) and creating
-- the new one (contentobject_state_group_id, real_language_id) are done in the
-- PHP script after removing the duplicated entries

-- See http://issues.ez.no/19397
-- Normalize database so that updates to email address automatically affects digest settings
ALTER TABLE ezgeneral_digest_user_settings ADD user_id integer DEFAULT 0 NOT NULL;
DELETE FROM ezgeneral_digest_user_settings WHERE address NOT IN (SELECT email FROM ezuser);
UPDATE ezgeneral_digest_user_settings SET user_id = (SELECT ezuser.contentobject_id
           FROM ezuser WHERE ezuser.email = ezgeneral_digest_user_settings.address);
CREATE INDEX ezgeneral_digest_user_id ON ezgeneral_digest_user_settings USING btree (user_id);
ALTER TABLE ezgeneral_digest_user_settings DROP COLUMN address;
CREATE INDEX ezuser_login ON ezuser USING btree (login);


-- 5.0.0 -> 5.1.0

UPDATE ezsite_data SET value='5.1.0alpha1' WHERE name='ezpublish-version';
UPDATE ezsite_data SET value='1' WHERE name='ezpublish-release';

CREATE INDEX ezcontentclass_identifier ON ezcontentclass USING btree (identifier, version);
CREATE INDEX ezcontentobject_tree_remote_id ON ezcontentobject_tree USING btree (remote_id);
CREATE INDEX ezcontobj_version_obj_status ON ezcontentobject_version USING btree (contentobject_id, status);
CREATE INDEX ezpolicy_role_id ON ezpolicy USING btree (role_id);
CREATE INDEX ezpolicy_limit_value_limit_id ON ezpolicy_limitation_value USING btree (limitation_id);
DROP INDEX ezcontentobject_attribute_contentobject_id;
DROP INDEX ezcontentobject_attr_id;
DROP INDEX ezcontentobject_name_co_id;
DROP INDEX ezenumobjectvalue_co_attr_id_co_attr_ver;
DROP INDEX ezkeyword_keyword_id;
DROP INDEX ezkeyword_attr_link_keyword_id;
DROP INDEX eznode_assignment_co_id;
DROP INDEX client_id;

DROP INDEX ezurlalias_ml_actt;
-- Combining "ezurlalias_ml_par_txt" and "ezurlalias_ml_par_lnk_txt" by moving "link" after "text(32)" in the latter:
DROP INDEX ezurlalias_ml_par_txt;
DROP INDEX ezurlalias_ml_par_lnk_txt;
CREATE INDEX ezurlalias_ml_par_lnk_txt ON ezurlalias_ml USING btree (parent, text, link);

-- Combining "ezurlalias_ml_action" and "ezurlalias_ml_par_act_id_lnk" by moving "parent" after "link" in the latter:
DROP INDEX ezurlalias_ml_action;
DROP INDEX ezurlalias_ml_par_act_id_lnk;
CREATE INDEX ezurlalias_ml_par_act_id_lnk ON ezurlalias_ml USING btree ("action", id, link, parent);

-- See https://jira.ez.no/browse/EZP-20239
DELETE FROM ezcontentobject_link WHERE op_code <> 0;
DELETE FROM ezcontentobject_link WHERE relation_type = 0;
ALTER TABLE ezcontentobject_link DROP COLUMN op_code;

-- See https://jira.ez.no/browse/EZP-20527
UPDATE ezcobj_state_group_language SET real_language_id = language_id & ~1;

-- See https://jira.ez.no/browse/EZP-20673
ALTER TABLE eznode_assignment ALTER COLUMN remote_id TYPE character varying(100);


-- 5.1.0 -> 5.2.0

UPDATE ezsite_data SET value='5.2.0alpha1' WHERE name='ezpublish-version';
UPDATE ezsite_data SET value='1' WHERE name='ezpublish-release';

ALTER TABLE ezcontent_language
    ALTER COLUMN id TYPE BIGINT;

ALTER TABLE ezcontentclass
    ALTER COLUMN initial_language_id TYPE BIGINT,
    ALTER COLUMN language_mask TYPE BIGINT;

ALTER TABLE ezcontentclass_name
    ALTER COLUMN language_id TYPE BIGINT;

ALTER TABLE ezcontentobject
    ALTER COLUMN initial_language_id TYPE BIGINT,
    ALTER COLUMN language_mask TYPE BIGINT;

ALTER TABLE ezcontentobject_name
    ALTER COLUMN language_id TYPE BIGINT;

ALTER TABLE ezcontentobject_attribute
    ALTER COLUMN language_id TYPE BIGINT;

ALTER TABLE ezcontentobject_version
    ALTER COLUMN initial_language_id TYPE BIGINT,
    ALTER COLUMN language_mask TYPE BIGINT;

ALTER TABLE ezcobj_state
    ALTER COLUMN default_language_id TYPE BIGINT,
    ALTER COLUMN language_mask TYPE BIGINT;

ALTER TABLE ezcobj_state_group
    ALTER COLUMN default_language_id TYPE BIGINT,
    ALTER COLUMN language_mask TYPE BIGINT;

ALTER TABLE ezcobj_state_group_language
    ALTER COLUMN language_id TYPE BIGINT,
    ALTER COLUMN real_language_id TYPE BIGINT;

ALTER TABLE ezcobj_state_language
    ALTER COLUMN language_id TYPE BIGINT;

ALTER TABLE ezurlalias_ml
    ALTER COLUMN lang_mask TYPE BIGINT;

-- Start ezp-21465 : Cleanup extra lines in the ezurl_object_link table
DELETE
FROM ezurl_object_link AS T1
WHERE  T1.url_id < ANY (
  SELECT DISTINCT url_id
  FROM  ezurl_object_link T2 JOIN ezcontentobject_attribute ON T2.contentobject_attribute_id = ezcontentobject_attribute.id
  WHERE T1.url_id < T2.url_id
  AND T1.contentobject_attribute_id = T2.contentobject_attribute_id
  AND T1.contentobject_attribute_version = T2.contentobject_attribute_version
  AND ezcontentobject_attribute.data_type_string = 'ezurl');
-- End ezp-21465

-- Start EZP-21469
-- While using the public API, ezcontentobject.language_mask was not updated correctly,
-- the UPDATE statement below fixes that based on the language_mask of the current version.
UPDATE
    ezcontentobject AS o
SET
    language_mask = (o.language_mask & 1) | (v.language_mask & ~1)
FROM
    ezcontentobject_version AS v
WHERE
    o.id = v.contentobject_id AND o.current_version = v.version;
-- End EZP-21469

-- Start EZP-21648:
-- Adding 'priority' and 'is_hidden' columns to the 'eznode_assignment' table
ALTER TABLE eznode_assignment ADD priority integer DEFAULT 0 NOT NULL;
ALTER TABLE eznode_assignment ADD is_hidden integer DEFAULT 0 NOT NULL;
-- End EZP-21648

-- 5.2.0 -> 5.3.0

UPDATE ezsite_data SET value='5.3.0alpha1' WHERE name='ezpublish-version';

CREATE INDEX ezcontentobject_classattr_id ON ezcontentobject_attribute USING btree (contentclassattribute_id);

-- 5.3.0 -> 5.4.0

UPDATE ezsite_data SET value='5.4.0alpha1' WHERE name='ezpublish-version';

DROP TABLE ezsearch_return_count;
DROP SEQUENCE ezsearch_return_count_s;

UPDATE ezcontentobject_attribute
    SET data_int = NULL
WHERE
    data_int = 0
    AND data_type_string IN ( 'ezdate', 'ezdatetime' );

UPDATE ezinfocollection_attribute
SET data_int = NULL
FROM ezcontentclass_attribute
WHERE
    ezcontentclass_attribute.id = ezinfocollection_attribute.contentclass_attribute_id
    AND ezinfocollection_attribute.data_int = 0
    AND ezcontentclass_attribute.data_type_string IN ( 'ezdate', 'ezdatetime' );

-- See https://jira.ez.no/browse/EZP-23595 - cleanup extra lines in the ezuser_setting table
DELETE FROM ezuser_setting where user_id not in (SELECT contentobject_id FROM ezuser);