cleggypdc/pimcore-db-upgrade-helper.sql

## pimcore-db-upgrade-helper.sql
/**
 * Pimcore Database Upgrade Helper
 *
 * This source file is subject to the GNU General Public License version 3 (GPLv3)
 * For the full copyright and license information, please view the LICENSE.md
 * file distributed with this source code.
 *
 * @copyright  Copyright (c) 2017 Gather Digital Ltd (https://www.gatherdigital.co.uk)
 * @license    https://www.gatherdigital.co.uk/license     GNU General Public License version 3 (GPLv3)

  Use This script to migrate Pimcore Databases across versions quickly by choosing the start build (see comments)
and erasing everything before that.

 README
 This SQL has the following restrictions / limitations

  - CANNOT Migrate object_metadata from before BUILD 3664
  - CANNOT Migrate data in classification stores before BUILD 3915
  - STATIC ROUTES before BUILD 3604 need to be manually re-added into a file and will be moved to a temp table
  - DOCUMENT TYPES before BUILD 3606 need to be manually re-added  into a file and will be moved to a temp table
  - PREDEFINED PROPERIES before BUILD 3606 needs to be manually re-added into a file and will be moved to a temp table
  - PREDEFINED ASSET META before BUILD 3606 needs to be manually re-added into a file and will be moved to a temp table
  - BUILD 3739 is disabled as it will fail if there are no css attributes in documents
  - BUILD 3803 will DELETE document Keywords WILL !!!!!!
  - BUILD 3987 will DELETE document_page metaData !!!!!!

 */


/**

   ================= PIMCORE 1.4.8 =============================== 2330

 */


/**
  2380
**/
CREATE TABLE IF NOT EXISTS `keyvalue_groups` (`id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL , `description` VARCHAR(255), PRIMARY KEY  (`id`)) DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `keyvalue_keys` ( `id` INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(255) NOT NULL ,`description` TEXT,`type` enum('bool','number','select','text') DEFAULT NULL,`unit` VARCHAR(255),`possiblevalues` TEXT,`group` INT,PRIMARY KEY (`id`),FOREIGN KEY (`group`) REFERENCES keyvalue_groups(`id`) ON DELETE SET NULL) DEFAULT CHARSET=utf8;

/**
  2409
 */
DROP TABLE IF EXISTS `tracking_events`;
CREATE TABLE `tracking_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `category` varchar(255) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `data` varchar(255) DEFAULT NULL,
  `timestamp` bigint(20) unsigned DEFAULT NULL,
  `year` int(5) unsigned DEFAULT NULL,
  `month` int(2) unsigned DEFAULT NULL,
  `day` int(2) unsigned DEFAULT NULL,
  `dayOfWeek` int(1) unsigned DEFAULT NULL,
  `dayOfYear` int(3) unsigned DEFAULT NULL,
  `weekOfYear` int(2) unsigned DEFAULT NULL,
  `hour` int(2) unsigned DEFAULT NULL,
  `minute` int(2) unsigned DEFAULT NULL,
  `second` int(2) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `timestamp` (`timestamp`),
  KEY `year` (`year`),
  KEY `month` (`month`),
  KEY `day` (`day`),
  KEY `dayOfWeek` (`dayOfWeek`),
  KEY `dayOfYear` (`dayOfYear`),
  KEY `weekOfYear` (`weekOfYear`),
  KEY `hour` (`hour`),
  KEY `minute` (`minute`),
  KEY `second` (`second`),
  KEY `category` (`category`),
  KEY `action` (`action`),
  KEY `label` (`label`),
  KEY `data` (`data`)
) DEFAULT CHARSET=utf8;

/**
 2423
 */
ALTER TABLE `documents_page` ADD COLUMN `metaData` text NULL AFTER `keywords`;

/**
  2454
 */
ALTER TABLE `documents_page` ADD COLUMN `css` longtext NULL;

/**
 2526
 */
ALTER TABLE `keyvalue_keys` CHANGE COLUMN `type` `type` ENUM('bool','number','select','text','translated') NULL DEFAULT NULL AFTER `description`;

/**
  2530
 */
ALTER TABLE `keyvalue_keys` ADD COLUMN `translator` INT NULL AFTER `group`;

/**
  2539
 */
INSERT INTO `users_permission_definitions` VALUES ('document_style_editor');
INSERT INTO `users_permission_definitions` VALUES ('recyclebin');
INSERT INTO `users_permission_definitions` VALUES ('seo_document_editor');
INSERT INTO `users_permission_definitions` VALUES ('robots.txt');
INSERT INTO `users_permission_definitions` VALUES ('http_errors');
INSERT INTO `users_permission_definitions` VALUES ('tag_snippet_management');
INSERT INTO `users_permission_definitions` VALUES ('qr_codes');
INSERT INTO `users_permission_definitions` VALUES ('targeting');
INSERT INTO `users_permission_definitions` VALUES ('notes_events');
INSERT INTO `users_permission_definitions` VALUES ('backup');
INSERT INTO `users_permission_definitions` VALUES ('bounce_mail_inbox');
INSERT INTO `users_permission_definitions` VALUES ('website_settings');

/**
 2547
 */
INSERT INTO `users_permission_definitions` VALUES ('newsletter');


/**

   ================= PIMCORE 1.4.9 =============================== 2560

 */

/**
  2570
 */
ALTER TABLE `redirects` ADD COLUMN `passThroughParameters` tinyint(1) NULL DEFAULT NULL AFTER `sourceSite`;

/**
  2574
 */
ALTER TABLE `keyvalue_groups` CHANGE COLUMN `name` `name` VARCHAR(255) NOT NULL DEFAULT '' AFTER `id`;
ALTER TABLE `keyvalue_keys` CHANGE COLUMN `name` `name` VARCHAR(255) NOT NULL DEFAULT '' AFTER `id`;

/**
  2605
 */
ALTER TABLE `targeting` DROP INDEX `name_documentId`;
ALTER TABLE `targeting` DROP INDEX `documentId`;
ALTER TABLE `targeting` DROP COLUMN `documentId`;

/**
  2622
 */
ALTER TABLE `classes` ADD COLUMN `showVariants` TINYINT(1) NULL AFTER `propertyVisibility`;

/**
 2626
 */
ALTER TABLE `documents` CHANGE COLUMN `index` `index` int(11) unsigned NULL DEFAULT 0;

/**

   ================= PIMCORE 1.4.10 =============================== 2635

 */

/**
 2665
 */
 ALTER TABLE `documents` ADD UNIQUE INDEX `unique_fullpath` (`path`, `key`);
 ALTER TABLE `assets` ADD UNIQUE INDEX `unique_fullpath` (`path`, `filename`);
 ALTER TABLE `objects` ADD UNIQUE INDEX `unique_fullpath` (`o_path`,`o_key`);

 /**
   2695
  */
ALTER TABLE translations_website CHANGE `date` `creationDate` BIGINT(20) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE translations_website ADD `modificationDate` BIGINT(20) UNSIGNED NULL DEFAULT NULL;
UPDATE translations_website SET modificationDate = creationDate;
ALTER TABLE translations_admin CHANGE `date` `creationDate` BIGINT(20) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE translations_admin ADD `modificationDate` BIGINT(20) UNSIGNED NULL DEFAULT NULL;
UPDATE translations_admin SET modificationDate = creationDate;

/**
  2721
 */
RENAME TABLE `targeting` TO `targeting_rules`;
DROP TABLE IF EXISTS `targeting_personas`;
CREATE TABLE `targeting_personas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` text,
  `conditions` longtext,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

/**
  2723
 */
 ALTER TABLE `documents_page` ADD COLUMN `personas` varchar(255) NULL DEFAULT NULL;
 CREATE TABLE `deployment_packages` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(50) NOT NULL,
  `subType` VARCHAR(50) NOT NULL,
  `creationDate` BIGINT(20) NOT NULL,
  `version` BIGINT(20) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
CREATE TABLE `deployment_target` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parentId` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  `name` VARCHAR(255) NOT NULL,
  `creationDate` BIGINT(20) UNSIGNED NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

/**
  2740
 */
 DROP TABLE IF EXISTS `cache`;
 CREATE TABLE `cache` (
  `id` varchar(165) NOT NULL DEFAULT '',
  `data` longtext,
  `mtime` bigint(20) DEFAULT NULL,
  `expire` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

/**
  2794
 */
 CREATE TABLE IF NOT EXISTS `uuids` (
  `uuid` CHAR(36) NOT NULL,
  `itemId` BIGINT(20) UNSIGNED NOT NULL,
  `type` VARCHAR(25) NOT NULL,
  `subType` VARCHAR(20) NULL DEFAULT NULL,
  `instanceIdentifier` VARCHAR(50) NOT NULL,
  UNIQUE INDEX `itemId_type_uuid` (`itemId`, `type`, `uuid`)
) DEFAULT CHARSET=utf8;

/**
  2808
 */
INSERT INTO `users_permission_definitions` VALUES ('deployment');

/**
 2817
 */
ALTER TABLE `uuids` DROP COLUMN `subType`;

/**
 2893
 */
ALTER TABLE `targeting_personas` ADD COLUMN `threshold` int(11) NULL DEFAULT NULL;

 /**
  2908
  */
ALTER TABLE `keyvalue_keys` CHANGE COLUMN `type` `type` ENUM('bool','number','select','text','translated') NULL DEFAULT NULL AFTER `description`;

/**
  2922
 */
ALTER TABLE `targeting_rules` ADD COLUMN `scope` varchar(50) NULL DEFAULT NULL AFTER `description`;

/**
  2926
 */
ALTER TABLE `targeting_rules` ADD COLUMN `active` tinyint(1) NULL DEFAULT NULL AFTER `scope`;
ALTER TABLE `targeting_personas` ADD COLUMN `active` tinyint(1) NULL DEFAULT NULL;

/**
  2944
 */
 ALTER TABLE `sites` ADD COLUMN `mainDomain` varchar(255) NULL DEFAULT NULL AFTER `id`;
 ALTER TABLE `sites` ADD COLUMN `errorDocument` varchar(255) NULL DEFAULT NULL;
 ALTER TABLE `sites` ADD COLUMN `redirectToMainDomain` tinyint(1) NULL DEFAULT NULL;

 /**

   ================= PIMCORE 2.0 =============================== 2972

 */

/**
 2978
 */
ALTER TABLE `search_backend_data` CHANGE COLUMN `fullpath` `fullpath` varchar(330) NULL DEFAULT NULL;

/**
  2992
 */
ALTER TABLE `documents_doctypes` ADD COLUMN `creationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `documents_doctypes` ADD COLUMN `modificationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `glossary` ADD COLUMN `creationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `glossary` ADD COLUMN `modificationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `keyvalue_groups` ADD COLUMN `creationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `keyvalue_groups` ADD COLUMN `modificationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `keyvalue_keys` ADD COLUMN `creationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `keyvalue_keys` ADD COLUMN `modificationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `properties_predefined` ADD COLUMN `creationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `properties_predefined` ADD COLUMN `modificationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `redirects` ADD COLUMN `creationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `redirects` ADD COLUMN `modificationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `sites` ADD COLUMN `creationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `sites` ADD COLUMN `modificationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `staticroutes` ADD COLUMN `creationDate` bigint(20) unsigned DEFAULT 0;
ALTER TABLE `staticroutes` ADD COLUMN `modificationDate` bigint(20) unsigned DEFAULT 0;

/**
  2994
 */
DROP TABLE IF EXISTS `website_settings`;
CREATE TABLE `website_settings` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL DEFAULT '',
	`type` ENUM('text','document','asset','object','bool') NULL DEFAULT NULL,
	`data` TEXT NULL,
	`siteId` INT(11) UNSIGNED NULL DEFAULT NULL,
	`creationDate` BIGINT(20) UNSIGNED NULL DEFAULT '0',
	`modificationDate` BIGINT(20) UNSIGNED NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	INDEX `name` (`name`),
	INDEX `siteId` (`siteId`)
)
DEFAULT CHARSET=utf8;

/**

   ================= PIMCORE 2.0.1 =============================== 3007

 */

/**
  3027
 */
ALTER TABLE `users` ADD COLUMN `docTypes` varchar(255) NULL DEFAULT NULL;
ALTER TABLE `users` ADD COLUMN `classes` varchar(255) NULL DEFAULT NULL;

/**

   ================= PIMCORE 2.0.2 =============================== 3042

 */

/**
 3044
 */
DELETE FROM `users_permission_definitions` WHERE `key`='bounce_mail_inbox';
INSERT INTO `users_permission_definitions` SET `key`='emails';

/**
 3045
 */
CREATE TABLE `email_blacklist` (
  `address` varchar(255) NOT NULL DEFAULT '',
  `creationDate` int(11) unsigned DEFAULT NULL,
  `modificationDate` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`address`)
) DEFAULT CHARSET=utf8;

/**
 3051
 */
ALTER TABLE `users_workspaces_object` ADD COLUMN `lEdit` TEXT NULL DEFAULT NULL AFTER `properties`, ADD COLUMN `lView` TEXT NULL DEFAULT NULL AFTER `lEdit`;

/**
  3086
 */
CREATE TABLE `assets_metadata` (
  `cid` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `language` varchar(255) DEFAULT NULL,
  `type` enum('input','textarea') DEFAULT NULL,
  `data` text,
  KEY `cid` (`cid`)
) DEFAULT CHARSET=utf8;

/**

   ================= PIMCORE 2.1.0 =============================== 3103

 */

/**
  3147
 */
CREATE TABLE `custom_layouts` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`classId` INT(11) UNSIGNED NOT NULL,
	`name` VARCHAR(255) NULL DEFAULT NULL,
	`description` TEXT NULL,
	`creationDate` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
	`modificationDate` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
	`userOwner` INT(11) UNSIGNED NULL DEFAULT NULL,
	`userModification` INT(11) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `name` (`name`, `classId`)
) DEFAULT CHARSET=utf8;

ALTER TABLE `users_workspaces_object` ADD COLUMN `layouts` TEXT NULL AFTER `lView`;

/**
 3157
 */
ALTER TABLE `keyvalue_keys` ADD COLUMN `translator` int(11) DEFAULT NULL;
ALTER TABLE `keyvalue_keys` CHANGE COLUMN `type` `type` enum('bool','number','select','text','translated') NULL DEFAULT NULL;

/**

   ================= PIMCORE 2.2.0 =============================== 3159

 */

/**
  3174
 */
ALTER TABLE `users` ADD COLUMN `apiKey` varchar(255) NULL DEFAULT NULL;

/**
  3183
 */
ALTER TABLE `keyvalue_keys` CHANGE COLUMN `type` `type` enum('bool','number','select','text','translated','translatedSelect','range') NULL DEFAULT NULL;

/**

   ================= PIMCORE 2.2.1 =============================== 3190

 */


/**
 3207
 */
CREATE TABLE `keyvalue_translator_configuration` (
      `id` INT(10) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(200) NULL DEFAULT NULL,
      `translator` VARCHAR(200) NULL DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;

/**
 3213
 */
ALTER TABLE `assets_metadata` CHANGE COLUMN `type` `type` ENUM('input','textarea','asset','document','object','date') DEFAULT NULL AFTER `language`;

/**
  3214
 */
DROP TABLE IF EXISTS `assets_metadata_predefined`;
CREATE TABLE `assets_metadata_predefined` (
                      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                      `name` varchar(255) DEFAULT NULL,
                      `description` text,
                      `language` varchar(255) DEFAULT NULL,
                      `type` enum('input','textarea','asset','document','object','date') DEFAULT NULL,
                      `data` text,
                      `targetSubtype` enum('image', 'text', 'audio', 'video', 'document', 'archive', 'unknown') DEFAULT NULL,
                      `creationDate` bigint(20) unsigned DEFAULT '0',
                      `modificationDate` bigint(20) unsigned DEFAULT '0',
                      PRIMARY KEY (`id`),
                      KEY `name` (`name`),
                      KEY `id` (`id`),
                      KEY `type` (`type`),
                      KEY `language` (`language`),
                      KEY `targetSubtype` (`targetSubtype`)
                    ) DEFAULT CHARSET=utf8;

/**
 3221
 */
ALTER TABLE `users_workspaces_asset` CHANGE COLUMN `userId` `userId` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `users_workspaces_document` CHANGE COLUMN `userId` `userId` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `users_workspaces_object` CHANGE COLUMN `userId` `userId` int(11) NOT NULL DEFAULT 0;

/**

   ================= PIMCORE 2.2.2 =============================== 3230

 */


/**
  3232
 */
ALTER TABLE `keyvalue_keys` ADD COLUMN `mandatory` TINYINT(1) NULL DEFAULT NULL AFTER `translator`;

/**

   ================= PIMCORE 2.3.0 =============================== 3270

 */


/**
 3282
 */
INSERT INTO `users_permission_definitions` VALUES ('dashboards');

/**
 3288
 */
INSERT INTO `users_permission_definitions` VALUES ('users');

/**
  3293
 */
INSERT INTO `users_permission_definitions` VALUES ('sent_emails');

/**
  3304
 */
ALTER TABLE `email_log`
CHANGE `requestUri` `requestUri` varchar(500),
CHANGE `from` `from` varchar(500),
CHANGE `to` `to` longtext,
CHANGE `cc` `cc` longtext,
CHANGE `bcc` `bcc` longtext,
CHANGE `subject` `subject` varchar(500);

/**
  3320
 */
ALTER TABLE `custom_layouts` ADD COLUMN `default` TINYINT NOT NULL DEFAULT 0;

/**
 3331
 */
ALTER TABLE assets_metadata_predefined CHANGE `type` `type` ENUM('input', 'textarea', 'asset', 'document', 'object', 'date','select','checkbox');
ALTER TABLE assets_metadata CHANGE `type` `type` ENUM('input', 'textarea', 'asset', 'document', 'object', 'date','checkbox','select');
ALTER TABLE assets_metadata_predefined ADD `config` TEXT ASCII AFTER modificationDate;

/**
 3358
 */
delete from assets_metadata where cid not in(select id from assets);

/**
  3365
 */
CREATE TABLE `tmp_store` (
  `id` varchar(255) NOT NULL DEFAULT '',
  `tag` varchar(255) DEFAULT NULL,
  `data` longtext,
  `serialized` tinyint(2) NOT NULL DEFAULT '0',
  `date` int(10) DEFAULT NULL,
  `expiryDate` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tag` (`tag`),
  KEY `date` (`date`),
  KEY `expiryDate` (`expiryDate`)
) DEFAULT CHARSET=utf8;

/**

   ================= PIMCORE 3.0.0 =============================== 3372

 */


/**

   ================= PIMCORE 3.0.1 =============================== 3375

 */


/**
 3391
 */
ALTER TABLE documents CHANGE COLUMN `path` `path` varchar(765) CHARACTER SET ascii DEFAULT NULL;
ALTER TABLE assets CHANGE COLUMN `path` `path` varchar(765) CHARACTER SET ascii DEFAULT NULL;
ALTER TABLE objects CHANGE COLUMN `o_path` `o_path` varchar(765) CHARACTER SET ascii DEFAULT NULL;

/**

   ================= PIMCORE 3.0.2 =============================== 3380

 */

/**
  3392
 */
ALTER TABLE properties CHANGE COLUMN `cpath` `cpath` varchar(765) CHARACTER SET ascii DEFAULT NULL;
ALTER TABLE recyclebin CHANGE COLUMN `path` `path` varchar(765) DEFAULT NULL;
ALTER TABLE search_backend_data CHANGE COLUMN `fullpath` `fullpath` varchar(765) CHARACTER SET ascii DEFAULT NULL;
ALTER TABLE users_workspaces_asset CHANGE COLUMN `cpath` `cpath` varchar(765) CHARACTER SET ascii DEFAULT NULL;
ALTER TABLE users_workspaces_document CHANGE COLUMN `cpath` `cpath` varchar(765) CHARACTER SET ascii DEFAULT NULL;
ALTER TABLE users_workspaces_object CHANGE COLUMN `cpath` `cpath` varchar(765) CHARACTER SET ascii DEFAULT NULL;

/**

   ================= PIMCORE 3.0.3 =============================== 3400

 */

/**
 3402
 */
 ALTER TABLE `classes` ADD COLUMN `useTraits` varchar(255) NULL DEFAULT NULL AFTER `parentClass`;

/**
 3417
 */
DROP TABLE IF EXISTS `http_error_log`;
CREATE TABLE `http_error_log` (
  `uri` varchar(3000) CHARACTER SET ascii DEFAULT NULL,
  `code` int(3) DEFAULT NULL,
  `parametersGet` longtext,
  `parametersPost` longtext,
  `cookies` longtext,
  `serverVars` longtext,
  `date` bigint(20) DEFAULT NULL,
  `count` bigint(20) DEFAULT NULL,
  KEY (`uri` (765)),
  KEY `code` (`code`),
  KEY `date` (`date`),
  KEY `count` (`count`)
) DEFAULT CHARSET=utf8;

/**
 3432
 */
DROP TABLE IF EXISTS `content_index`;
DROP TABLE IF EXISTS `content_analysis`;

/**
3440
 */
ALTER TABLE `assets` ADD UNIQUE INDEX `fullpath` (`path`,`filename`);
ALTER TABLE `documents` ADD UNIQUE INDEX `fullpath` (`path`,`key`);
ALTER TABLE `objects` ADD UNIQUE INDEX `fullpath` (`o_path`,`o_key`);

/**

   ================= PIMCORE 3.0.4 =============================== 3444

 */


/**

   ================= PIMCORE 3.0.5 =============================== 3450

 */


/**
 3454
 */
ALTER TABLE `assets` ADD COLUMN `hasMetaData` tinyint(1) NOT NULL DEFAULT 0;
UPDATE assets SET hasMetaData = 1 WHERE id IN (SELECT DISTINCT cid FROM assets_metadata);

/**
 3459
 */
ALTER TABLE search_backend_data /*!50600 ENGINE=InnoDB */;

/**

   ================= PIMCORE 3.0.6 =============================== 3496

 */


/**
 3507
 */
CREATE TABLE IF NOT EXISTS `application_logs` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `message` varchar(1024) DEFAULT NULL,
  `priority` int(10) DEFAULT NULL,
  `fileobject` varchar(1024) DEFAULT NULL,
  `info` varchar(1024) DEFAULT NULL,
  `component` varchar(255) DEFAULT NULL,
  `source` varchar(255) DEFAULT NULL,
  `relatedobject` bigint(20) DEFAULT NULL,
  `relatedobjecttype` enum('object','document','asset') DEFAULT NULL,
  `maintenanceChecked` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `component` (`component`),
  KEY `timestamp` (`timestamp`),
  KEY `relatedobject` (`relatedobject`),
  KEY `priority` (`priority`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `users_permission_definitions` VALUES ('application_logging');

/**
3522
 */
ALTER TABLE `assets` ADD INDEX `modificationDate` (`modificationDate`);
ALTER TABLE `documents` ADD INDEX `modificationDate` (`modificationDate`);
ALTER TABLE `objects` ADD INDEX `o_modificationDate` (`o_modificationDate`);

/**
3524
 */
CREATE TABLE `classificationstore_groups` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`parentId` BIGINT(20) NOT NULL DEFAULT '0',
	`name` VARCHAR(255) NOT NULL DEFAULT '',
	`description` VARCHAR(255) NULL DEFAULT NULL,
	`creationDate` BIGINT(20) UNSIGNED NULL DEFAULT '0',
	`modificationDate` BIGINT(20) UNSIGNED NULL DEFAULT '0',
	PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8;

CREATE TABLE `classificationstore_keys` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL DEFAULT '',
	`description` TEXT NULL,
	`type` ENUM('input','textarea','wysiwyg','checkbox','numeric','slider','select','multiselect','date','datetime','language','languagemultiselect','country','countrymultiselect','table') NULL DEFAULT NULL,
	`creationDate` BIGINT(20) UNSIGNED NULL DEFAULT '0',
	`modificationDate` BIGINT(20) UNSIGNED NULL DEFAULT '0',
	`definition` LONGTEXT NULL,
	`enabled` TINYINT(1) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8;

CREATE TABLE `classificationstore_relations` (
	`groupId` BIGINT(20) NOT NULL,
	`keyId` BIGINT(20) NOT NULL,
	PRIMARY KEY (`groupId`, `keyId`),
	INDEX `FK_classificationstore_relations_classificationstore_keys` (`keyId`),
	CONSTRAINT `FK_classificationstore_relations_classificationstore_groups` FOREIGN KEY (`groupId`) REFERENCES `classificationstore_groups` (`id`) ON DELETE CASCADE,
	CONSTRAINT `FK_classificationstore_relations_classificationstore_keys` FOREIGN KEY (`keyId`) REFERENCES `classificationstore_keys` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)
DEFAULT CHARSET=utf8;

/**
 3527
 **/
DELETE FROM `users_permission_definitions` WHERE  `key`='sent_emails';

/**
3529
 */
ALTER TABLE `classificationstore_keys` ADD COLUMN `sorter` INT(10) NULL DEFAULT '0' AFTER `enabled`;
ALTER TABLE `classificationstore_groups` ADD COLUMN `sorter` INT(10) NULL DEFAULT '0' AFTER `modificationDate`;

/**
 3531
 */
CREATE TABLE `classificationstore_collections` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL DEFAULT '',
	`description` VARCHAR(255) NULL DEFAULT NULL,
	`creationDate` BIGINT(20) UNSIGNED NULL DEFAULT '0',
	`modificationDate` BIGINT(20) UNSIGNED NULL DEFAULT '0',
	PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `classificationstore_collectionrelations` (
	`colId` BIGINT(20) NOT NULL,
	`groupId` BIGINT(20) NOT NULL,
	PRIMARY KEY (`colId`, `groupId`),
	CONSTRAINT `FK_classificationstore_collectionrelations_groups` FOREIGN KEY (`groupId`) REFERENCES `classificationstore_groups` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
) DEFAULT CHARSET=utf8;

/**

   ================= PIMCORE 3.1.0 =============================== 3542

 */


/**

   ================= PIMCORE 3.1.1 =============================== 3543

 */

/**
 3548
 */
ALTER TABLE `classificationstore_collectionrelations` ADD COLUMN `sorter` INT(10) NULL DEFAULT '0' AFTER `groupId`;
ALTER TABLE `classificationstore_relations` ADD COLUMN `sorter` INT(10) NULL DEFAULT '0' AFTER `keyId`;
ALTER TABLE `classificationstore_groups` DROP COLUMN `sorter`;
ALTER TABLE `classificationstore_keys` DROP COLUMN `sorter`;

/**
 3549
 */
 CREATE TABLE `quantityvalue_units` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `group` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `abbreviation` varchar(10) COLLATE utf8_bin NOT NULL,
  `longname` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `baseunit` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `factor` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/**
 3551
 */
 ALTER TABLE `classificationstore_keys`
 CHANGE COLUMN `type` `type`
 ENUM(
 'input','textarea','wysiwyg','checkbox','numeric','slider','select','multiselect','date',
 'datetime','language','languagemultiselect','country','countrymultiselect','table','quantityValue','calculatedValue')
 NULL DEFAULT NULL AFTER `description`;

/*
 3563 - (see notice at top)
 */

 /*
  3565
  */
ALTER TABLE `classificationstore_groups` DROP COLUMN `sorter`;
ALTER TABLE `classificationstore_keys` DROP COLUMN `sorter`;
ALTER TABLE `classificationstore_collectionrelations` MODIFY COLUMN `sorter` INT NULL AFTER `groupId`;
ALTER TABLE `classificationstore_relations` MODIFY COLUMN `sorter` INT NOT NULL AFTER `keyId`;

/**
 3566
 */
 ALTER TABLE `quantityvalue_units` ADD COLUMN `conversionOffset` DOUBLE NULL DEFAULT NULL AFTER `factor`;

/*
 3575
 */
 ALTER TABLE `classificationstore_keys`
ADD COLUMN `title` VARCHAR(255) NOT NULL DEFAULT '' AFTER `name`,
ADD INDEX `name` (`name`),
ADD INDEX `enabled` (`enabled`),
ADD INDEX `type` (`type`);

ALTER TABLE `classificationstore_groups` ADD INDEX `name` (`name`);

ALTER TABLE `classificationstore_collections` ADD INDEX `name` (`name`);

/*
3581
 */
ALTER TABLE `application_logs`CHANGE COLUMN `priority` `priority-legacy` INT(10) NULL DEFAULT NULL AFTER `message`;
ALTER TABLE application_logs ADD COLUMN `priority` ENUM('emergency','alert','critical','error','warning','notice','info','debug') DEFAULT NULL AFTER `message`;

UPDATE application_logs SET `priority` = 'debug' WHERE `priority-legacy` = '7';
UPDATE application_logs SET `priority` = 'info' WHERE `priority-legacy` = '6';
UPDATE application_logs SET `priority` = 'notice' WHERE `priority-legacy` = '5';
UPDATE application_logs SET `priority` = 'warning' WHERE `priority-legacy` = '4';
UPDATE application_logs SET `priority` = 'error' WHERE `priority-legacy` = '3';
UPDATE application_logs SET `priority` = 'critical' WHERE `priority-legacy` = '2';
UPDATE application_logs SET `priority` = 'alert' WHERE `priority-legacy` = '1';
UPDATE application_logs SET `priority` = 'emergency' WHERE `priority-legacy` = '0';
ALTER TABLE application_logs DROP COLUMN `priority-legacy`;

ALTER TABLE `users`	ADD COLUMN `contentLanguages` LONGTEXT NULL DEFAULT NULL AFTER `language`;

/**
 3582
 */
DELETE FROM `users_permission_definitions` WHERE `key`='document_style_editor';
ALTER TABLE documents_page DROP COLUMN `css`;

/*
3588
 */
INSERT INTO `users_permission_definitions` (`key`) VALUES ('tags_assignment');
INSERT INTO `users_permission_definitions` (`key`) VALUES ('tags_configuration');
INSERT INTO `users_permission_definitions` (`key`) VALUES ('tags_search');
CREATE TABLE `tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parentId` int(10) unsigned DEFAULT NULL,
  `idPath` varchar(255) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idpath` (`idPath`),
  KEY `parentid` (`parentId`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `tags_assignment` (
  `tagid` int(10) unsigned NOT NULL DEFAULT '0',
  `cid` int(10) NOT NULL DEFAULT '0',
  `ctype` enum('document','asset','object') NOT NULL,
  PRIMARY KEY (`tagid`,`cid`,`ctype`),
  KEY `ctype` (`ctype`),
  KEY `ctype_cid` (`cid`,`ctype`),
  KEY `tagid` (`tagid`)
) DEFAULT CHARSET=utf8;

/**
3591
 */
ALTER TABLE `application_logs` ADD COLUMN `pid` INT NULL DEFAULT NULL AFTER `id`;

/**
 3596 (see notes)
 */

 /**
  3604 (see notes)
  */
RENAME TABLE `staticroutes` TO `PLEASE_DELETE__staticroutes`;

/**
 3606 (see notes)
 */
RENAME TABLE `documents_doctypes` TO `PLEASE_DELETE__documents_doctypes`;
RENAME TABLE `properties_predefined` TO `PLEASE_DELETE__properties_predefined`;
RENAME TABLE `assets_metadata_predefined` TO `PLEASE_DELETE__assets_metadata_predefined`;

/*
3645
 */
ALTER TABLE documents_elements CHANGE COLUMN `name` `name` varchar(750) CHARACTER SET ascii DEFAULT '';

/*
 3651
 */
ALTER TABLE documents_elements CHANGE COLUMN `name` `name` varchar(750) CHARACTER SET ascii DEFAULT '';

/*
 3660
 */
CREATE TABLE `documents_translations` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `sourceId` int(11) unsigned NOT NULL DEFAULT '0',
  `language` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`sourceId`,`language`),
  KEY `id` (`id`),
  KEY `sourceId` (`sourceId`),
  KEY `language` (`language`)
) DEFAULT CHARSET=utf8;

/**
3689
 */
ALTER TABLE `classificationstore_relations` ADD INDEX `groupId` (`groupId`);
ALTER TABLE `classificationstore_collectionrelations` ADD INDEX `colId` (`colId`);

/**
 3690
 */
ALTER TABLE `classificationstore_relations` ALTER `sorter` DROP DEFAULT;
ALTER TABLE `classificationstore_relations` CHANGE COLUMN `sorter` `sorter` INT(11) NULL AFTER `keyId`;

/**
 3710
 */
DROP TABLE IF EXISTS `classificationstore_stores`;
CREATE TABLE `classificationstore_stores` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NULL DEFAULT NULL,
	`description` LONGTEXT NULL,
	PRIMARY KEY (`id`),
	INDEX `name` (`name`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO `classificationstore_stores` (`id`, `name`, `description`) VALUES (1, 'Default', 'Default Store');
ALTER TABLE `classificationstore_keys` ADD COLUMN `storeId` INT NULL DEFAULT NULL AFTER `id`, ADD INDEX `storeId` (`storeId`);
ALTER TABLE `classificationstore_groups` ADD COLUMN `storeId` INT NULL DEFAULT NULL AFTER `id`, ADD INDEX `storeId` (`storeId`);
ALTER TABLE `classificationstore_collections` ADD COLUMN `storeId` INT NULL DEFAULT NULL AFTER `id`, ADD INDEX `storeId` (`storeId`);
UPDATE classificationstore_keys set storeId = 1;
UPDATE classificationstore_groups set storeId = 1;
UPDATE classificationstore_collections set storeId = 1;

/**
3719
 */
ALTER TABLE `users` ADD COLUMN `activePerspective` VARCHAR(255) NULL DEFAULT NULL AFTER `apiKey`, ADD COLUMN `perspectives` LONGTEXT NULL DEFAULT NULL AFTER `activePerspective`;

/**
 3730
 */
ALTER TABLE `classificationstore_keys` CHANGE `type` `type` VARCHAR(255) NULL DEFAULT NULL;

/**
 3739 (see notes)
 *
ALTER TABLE documents_page DROP COLUMN `css`; */

/**

   ================= PIMCORE 4.0 =============================== 3777

 */


/**
 3783
 */
ALTER TABLE `classificationstore_relations` ADD COLUMN `mandatory` TINYINT(1) NULL DEFAULT NULL AFTER `sorter`;

/**
 3803 (see notes)
 */
ALTER TABLE documents_page DROP COLUMN keywords;

/**
 3808
 */
ALTER TABLE `redirects` ADD INDEX `active` (`active`);
ALTER TABLE `classificationstore_relations` ADD INDEX `mandatory` (`mandatory`);

/**

   ================= PIMCORE 4.0.1 =============================== 3809

 */

/**
 3814
 */
ALTER TABLE documents CHANGE type type enum('page','link','snippet','folder','hardlink','email','printpage','printcontainer');

CREATE TABLE IF NOT EXISTS `documents_printpage` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `module` varchar(255) DEFAULT NULL,
  `controller` varchar(255) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `template` varchar(255) DEFAULT NULL,
  `lastGenerated` int(11) DEFAULT NULL,
  `lastGenerateMessage` text CHARACTER SET utf8,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

/**
3815
 */
INSERT INTO `users_permission_definitions` VALUES ('web2print_settings');

/**

   ================= PIMCORE 4.1.0 =============================== 3816

 */

 /**

   ================= PIMCORE 4.1.1 =============================== 3821

 */

/**
 3837 (may fail)
 */
ALTER TABLE `quantityvalue_units` ADD COLUMN `referemce` VARCHAR(50) NULL DEFAULT NULL AFTER `conversionOffset`;

/**
 3839 (may fail)
 */
ALTER TABLE `quantityvalue_units` CHANGE COLUMN `referemce` `reference` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin' AFTER `conversionOffset`;

/**
 3844
 */
ALTER TABLE `users` CHANGE COLUMN `permissions` `permissions` text;

/**

   ================= PIMCORE 4.1.2 =============================== 3851

 */

 /**

   ================= PIMCORE 4.1.3 =============================== 3858

 */

/**
 3864
 */
ALTER TABLE `classes` ADD COLUMN `group` varchar(255) NULL DEFAULT NULL AFTER `showVariants`;

/**
 3874
 */
 CREATE TABLE `element_workflow_state` (
  `cid` int(10) NOT NULL DEFAULT '0',
  `ctype` enum('document','asset','object') NOT NULL,
  `workflowId` int(11) NOT NULL,
  `state` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`cid`,`ctype`,`workflowId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/**
 3895
 */
ALTER TABLE users ADD COLUMN allowDirtyClose TINYINT(1) UNSIGNED NOT NULL DEFAULT 1;

/**

   ================= PIMCORE 4.2.0 =============================== 3900

 */

 /**
  3905
  */
ALTER TABLE documents CHANGE type type enum('page','link','snippet','folder','hardlink','email','newsletter','printpage','printcontainer');

CREATE TABLE `documents_newsletter` (
  `id` int(11) unsigned NOT NULL DEFAULT '0',
  `module` varchar(255) DEFAULT NULL,
  `controller` varchar(255) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `template` varchar(255) DEFAULT NULL,
  `from` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `trackingParameterSource` varchar(255) DEFAULT NULL,
  `trackingParameterMedium` varchar(255) DEFAULT NULL,
  `trackingParameterName` varchar(255) DEFAULT NULL,
  `enableTrackingParameters` tinyint(1) unsigned DEFAULT NULL,
  `sendingMode` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

/**
3914
 */
ALTER TABLE documents_printpage ADD contentMasterDocumentId INT(11) AFTER lastGenerateMessage;

/**
 3925
 */
ALTER TABLE `classes`
	DROP COLUMN `description`,
	DROP COLUMN `creationDate`,
	DROP COLUMN `modificationDate`,
	DROP COLUMN `userOwner`,
	DROP COLUMN `userModification`,
	DROP COLUMN `allowInherit`,
	DROP COLUMN `allowVariants`,
	DROP COLUMN `parentClass`,
	DROP COLUMN `useTraits`,
	DROP COLUMN `icon`,
	DROP COLUMN `previewUrl`,
	DROP COLUMN `propertyVisibility`,
	DROP COLUMN `showVariants`,
	DROP COLUMN `group`;

/**

   ================= PIMCORE 4.3.0 =============================== 3932

 */

/**
 3936
 */
DELETE FROM users_permission_definitions WHERE `key` = 'newsletter';

/**
3938
 */
ALTER TABLE tags CHANGE COLUMN `name` `name` varchar(255) DEFAULT NULL;


/**

   ================= PIMCORE 4.3.1 =============================== 3961

 */


/**
 3976
 */
UPDATE properties SET `inheritable` = 0 WHERE `name` LIKE 'navigation_%' AND `inheritable` = '1';

/**
 3977 (not neccessary but done to be certain)
 */
ALTER TABLE assets CHANGE COLUMN `path` `path` varchar(765) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL;
ALTER TABLE assets CHANGE COLUMN `filename` `filename` varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT '';
ALTER TABLE documents CHANGE COLUMN `path` `path` varchar(765) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL;
ALTER TABLE documents CHANGE COLUMN `key` `key`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT '';
ALTER TABLE documents_elements CHANGE COLUMN `name` `name` varchar(750) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '';
ALTER TABLE objects CHANGE COLUMN `o_path` `o_path` varchar(765) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL;
ALTER TABLE objects CHANGE COLUMN `o_key` `o_key` varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci default '';
ALTER TABLE properties CHANGE COLUMN `cpath` `cpath` varchar(765) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL;
ALTER TABLE search_backend_data CHANGE COLUMN `fullpath` `fullpath` varchar(765) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL;
ALTER TABLE users_workspaces_asset CHANGE COLUMN `cpath` `cpath` varchar(765) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL;
ALTER TABLE users_workspaces_document CHANGE COLUMN `cpath` `cpath` varchar(765) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL;
ALTER TABLE users_workspaces_object CHANGE COLUMN `cpath` `cpath` varchar(765) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL;

/**
 3987 (see note - remove if you want to keep this data, and run https://github.com/pimcore/pimcore/blob/pimcore4/update/3987/postupdate.php manually)
 UPDATE documents_page SET metaData = '' WHERE LENGTH(metaData) > 6;
**/

/**

   ================= PIMCORE 4.4.0 =============================== 4000

 */

/**

   ================= PIMCORE 4.4.1 =============================== 4008

 */

/**
 4010
 */
ALTER TABLE users ADD websiteTranslationLanguagesEdit LONGTEXT, ADD websiteTranslationLanguagesView LONGTEXT;

 /**
 4012
  */
ALTER TABLE `search_backend_data` DROP INDEX `data`;
ALTER TABLE `search_backend_data` DROP INDEX `properties`;

/*
 4021
 */
ALTER TABLE `assets_metadata` ADD INDEX `name` (`name`);

/**

   ================= PIMCORE 4.4.2 =============================== 4035

 */

 /**

   ================= PIMCORE 4.4.3 =============================== 4044

 */

/**
4050
*/
ALTER TABLE versions ADD stackTrace text AFTER note;

/**
4061
 */
ALTER TABLE `versions` ADD INDEX `date` (`date`);


/**

   ================= PIMCORE 4.5.0 =============================== 4063

 */


/**
 4079
 */
ALTER TABLE `schedule_tasks` ADD INDEX `version` (`version`);

/**

   ================= PIMCORE 4.6.0 =============================== 4084

 */


/**
 ==== CLEAN UP

**/
DROP TABLE IF EXISTS `please_delete__assets_permissions`;
DROP TABLE IF EXISTS `please_delete__documents_permissions`;
DROP TABLE IF EXISTS `please_delete__objects_permissions`;
DROP TABLE IF EXISTS `PLEASE_DELETE__assets_metadata_predefined`;
DROP TABLE IF EXISTS `PLEASE_DELETE__documents_doctypes`;
DROP TABLE IF EXISTS `PLEASE_DELETE__properties_predefined`;
DROP TABLE IF EXISTS `PLEASE_DELETE__staticroutes`;