mysql_update_from_46_to_2017_12_2_1.sql

-- dbupdate-4.6.0-to-4.7.0.sql

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


ALTER TABLE ezpending_actions ADD COLUMN id int(11) AUTO_INCREMENT PRIMARY KEY;


-- 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 MODIFY COLUMN data_float double default NULL;
ALTER TABLE ezcontentclass_attribute MODIFY COLUMN data_float1 double default NULL;
ALTER TABLE ezcontentclass_attribute MODIFY COLUMN data_float2 double default NULL;
ALTER TABLE ezcontentclass_attribute MODIFY COLUMN data_float3 double default NULL;
ALTER TABLE ezcontentclass_attribute MODIFY COLUMN data_float4 double default NULL;

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


-- dbupdate-4.7.0-to-5.0.0.sql

SET storage_engine=InnoDB;
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 COLUMN real_language_id int(11) NOT NULL DEFAULT '0';
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 COLUMN user_id int(11) NOT NULL default '0';
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);
ALTER TABLE ezgeneral_digest_user_settings ADD UNIQUE INDEX ezgeneral_digest_user_id (user_id);
ALTER TABLE ezgeneral_digest_user_settings DROP COLUMN address;
ALTER TABLE ezuser ADD INDEX ezuser_login (login);

-- dbupdate-5.0.0-to-5.1.0.sql

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

ALTER TABLE ezcontentclass ADD INDEX ezcontentclass_identifier (identifier, version);
ALTER TABLE ezcontentobject_tree ADD INDEX ezcontentobject_tree_remote_id (remote_id);
ALTER TABLE ezcontentobject_version ADD INDEX ezcontobj_version_obj_status (contentobject_id, status);
ALTER TABLE ezpolicy ADD INDEX ezpolicy_role_id (role_id);
ALTER TABLE ezpolicy_limitation_value ADD INDEX ezpolicy_limit_value_limit_id (limitation_id);
ALTER TABLE ezcontentobject_attribute
    DROP INDEX ezcontentobject_attribute_contentobject_id,
    DROP INDEX ezcontentobject_attr_id;
ALTER TABLE ezcontentobject_name DROP INDEX ezcontentobject_name_co_id;
ALTER TABLE ezenumobjectvalue DROP INDEX ezenumobjectvalue_co_attr_id_co_attr_ver;
ALTER TABLE ezkeyword DROP INDEX ezkeyword_keyword_id;
ALTER TABLE ezkeyword_attribute_link DROP INDEX ezkeyword_attr_link_keyword_id;
ALTER TABLE eznode_assignment DROP INDEX eznode_assignment_co_id;
ALTER TABLE ezprest_clients DROP INDEX client_id;

ALTER TABLE ezurlalias_ml
    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,
    ADD INDEX ezurlalias_ml_par_lnk_txt (parent, text(32), 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,
    ADD INDEX ezurlalias_ml_par_act_id_lnk (action(32), 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 CHANGE COLUMN remote_id remote_id varchar(100) NOT NULL DEFAULT '0';

-- dbupdate-5.1.0-to-5.2.0.sql

SET storage_engine=InnoDB;
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
    MODIFY id BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcontentclass
    MODIFY initial_language_id BIGINT NOT NULL DEFAULT '0',
    MODIFY language_mask BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcontentclass_name
    MODIFY language_id BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcontentobject
    MODIFY initial_language_id BIGINT NOT NULL DEFAULT '0',
    MODIFY language_mask BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcontentobject_name
    MODIFY language_id BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcontentobject_attribute
    MODIFY language_id BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcontentobject_version
    MODIFY initial_language_id BIGINT NOT NULL DEFAULT '0',
    MODIFY language_mask BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcobj_state
    MODIFY default_language_id BIGINT NOT NULL DEFAULT '0',
    MODIFY language_mask BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcobj_state_group
    MODIFY default_language_id BIGINT NOT NULL DEFAULT '0',
    MODIFY language_mask BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcobj_state_group_language
    MODIFY language_id BIGINT NOT NULL DEFAULT '0',
    MODIFY real_language_id BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezcobj_state_language
    MODIFY language_id BIGINT NOT NULL DEFAULT '0';

ALTER TABLE ezurlalias_ml
    MODIFY lang_mask BIGINT NOT NULL DEFAULT '0';

-- Start ezp-21465 : Cleanup extra lines in the ezurl_object_link table
DROP TEMPORARY TABLE IF EXISTS ezurl_object_link_temp ;

-- create a temporary table containing stale links
CREATE TEMPORARY TABLE ezurl_object_link_temp
SELECT DISTINCT contentobject_attribute_id, contentobject_attribute_version, url_id
FROM ezurl_object_link AS T1 JOIN ezcontentobject_attribute ON T1.contentobject_attribute_id = ezcontentobject_attribute.id
WHERE ezcontentobject_attribute.data_type_string = "ezurl"
AND T1.url_id < ANY
  (SELECT DISTINCT T2.url_id
  FROM ezurl_object_link T2
  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);

SET @OLD_SQL_SAFE_UPDATES=@@SQL_SAFE_UPDATES;
SET SQL_SAFE_UPDATES=0;

DELETE ezurl_object_link.*
FROM ezurl_object_link JOIN ezurl_object_link_temp ON ezurl_object_link.url_id = ezurl_object_link_temp.url_id
AND ezurl_object_link.contentobject_attribute_id = ezurl_object_link_temp.contentobject_attribute_id
AND ezurl_object_link.contentobject_attribute_version = ezurl_object_link_temp.contentobject_attribute_version;

SET SQL_SAFE_UPDATES=@OLD_SQL_SAFE_UPDATES;
-- 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
INNER JOIN
    ezcontentobject_version AS v ON o.id = v.contentobject_id AND o.current_version = v.version
SET
    o.language_mask = (o.language_mask & 1) | (v.language_mask & ~1);
-- End EZP-21469

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


-- dbupdate-5.2.0-to-5.3.0.sql

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

ALTER TABLE ezcontentobject_attribute
    ADD KEY ezcontentobject_classattr_id (contentclassattribute_id);
    
-- dbupdate-5.3.0-to-5.4.0.sql

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

DROP TABLE ezsearch_return_count;

-- Skip this if updating from 5.3.3 or higher as this should ideally not be applied twice
UPDATE ezcontentobject_attribute
    SET data_int = NULL
WHERE
    data_int = 0
    AND data_type_string IN ( 'ezdate', 'ezdatetime' );

UPDATE ezinfocollection_attribute, ezcontentclass_attribute
    SET ezinfocollection_attribute.data_int = NULL
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);

-- dbupdate-5.4.0-to-6.12.0.sql

SET storage_engine=InnoDB;
UPDATE ezsite_data SET value='6.12.0' WHERE name='ezpublish-version';
UPDATE ezsite_data SET value='1' WHERE name='ezpublish-release';

ALTER TABLE ezuser CHANGE password_hash password_hash VARCHAR(255) default NULL;