Skip to content

Instantly share code, notes, and snippets.

@cleggypdc
Last active April 3, 2018 16:48
Show Gist options
  • Save cleggypdc/9704dc0bbf130ffa3809397625526bfc to your computer and use it in GitHub Desktop.
Save cleggypdc/9704dc0bbf130ffa3809397625526bfc to your computer and use it in GitHub Desktop.
A Helper SQL file that can migrate Pimcore Databases from Pimcore 1.4.8 to the most recent version.
/**
* 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`;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment