Migrate Database From ForkCMS 4.x to 5.x

migrate_fork_4_to_5.sql

ALTER TABLE forms_fields_validation CHANGE `type` `type` enum('required','email','number','time') COLLATE utf8mb4_unicode_ci NOT NULL;
UPDATE `forms_fields_validation` SET `type` = "number" WHERE `type` = "";

RENAME TABLE meta TO old_meta;
CREATE TABLE `meta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keywords` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `keywords_overwrite` enum('Y','N') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N' COMMENT '(DC2Type:enum_bool)',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description_overwrite` enum('Y','N') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N' COMMENT '(DC2Type:enum_bool)',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title_overwrite` enum('Y','N') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N' COMMENT '(DC2Type:enum_bool)',
  `url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `url_overwrite` enum('Y','N') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N' COMMENT '(DC2Type:enum_bool)',
  `custom` longtext COLLATE utf8mb4_unicode_ci,
  `data` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `idx_url` (`url`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO meta SELECT * FROM old_meta;
DROP TABLE old_meta;

ALTER TABLE blog_comments MODIFY type VARCHAR(255) NOT NULL default 'comment';
ALTER TABLE blog_comments MODIFY status VARCHAR(249) NOT NULL default 'moderation'; -- (we cant do 255 because that is too big for the index)
ALTER TABLE blog_posts MODIFY status VARCHAR(244) NOT NULL; -- (we cant do 255 because that is too big for the index)
ALTER TABLE blog_posts MODIFY hidden VARCHAR(1) NOT NULL default 'N';
ALTER TABLE blog_posts MODIFY allow_comments VARCHAR(1) NOT NULL default 'N';
ALTER TABLE content_blocks MODIFY hidden VARCHAR(1) NOT NULL default 'N';
ALTER TABLE content_blocks MODIFY status VARCHAR(255) NOT NULL DEFAULT 'active' COMMENT '(DC2Type:content_blocks_status)';
ALTER TABLE forms MODIFY method VARCHAR(255) NOT NULL default 'database_email';
ALTER TABLE forms_fields MODIFY type VARCHAR(255) NOT NULL;
ALTER TABLE forms_fields_validation MODIFY type VARCHAR(255) NOT NULL;
ALTER TABLE locale MODIFY type VARCHAR(110) NOT NULL DEFAULT 'lbl'; -- (we cant do 255 because that is too big for the index)
ALTER TABLE location MODIFY show_overview VARCHAR(1) NOT NULL DEFAULT 'Y';
ALTER TABLE meta MODIFY keywords_overwrite VARCHAR(1) NOT NULL default 'N';
ALTER TABLE meta MODIFY description_overwrite VARCHAR(1) NOT NULL default 'N';
ALTER TABLE meta MODIFY title_overwrite VARCHAR(1) NOT NULL default 'N';
ALTER TABLE meta MODIFY url_overwrite VARCHAR(1) NOT NULL default 'N';
ALTER TABLE modules_extras MODIFY type VARCHAR(255) NOT NULL;
ALTER TABLE modules_extras MODIFY hidden VARCHAR(1) NOT NULL default 'N';
ALTER TABLE pages MODIFY type VARCHAR(255) NOT NULL DEFAULT 'root' COMMENT 'page, header, footer, ...';
ALTER TABLE pages MODIFY status VARCHAR(243) NOT NULL DEFAULT 'active' COMMENT 'is this the active, archive or draft version';
ALTER TABLE pages MODIFY navigation_title_overwrite VARCHAR(1) NOT NULL DEFAULT 'N' COMMENT 'should we override the navigation title';
ALTER TABLE pages MODIFY hidden VARCHAR(1) NOT NULL DEFAULT 'N' COMMENT 'is the page hidden?';
ALTER TABLE pages MODIFY allow_move VARCHAR(1) NOT NULL DEFAULT 'Y';
ALTER TABLE pages MODIFY allow_children VARCHAR(1) NOT NULL DEFAULT 'Y';
ALTER TABLE pages MODIFY allow_edit VARCHAR(1) NOT NULL DEFAULT 'Y';
ALTER TABLE pages MODIFY allow_delete VARCHAR(1) NOT NULL DEFAULT 'Y';
ALTER TABLE pages_blocks MODIFY visible VARCHAR(1) NOT NULL DEFAULT 'Y';
ALTER TABLE search_index MODIFY active VARCHAR(1) NOT NULL DEFAULT 'N';
ALTER TABLE search_modules MODIFY searchable VARCHAR(1) NOT NULL DEFAULT 'N';
ALTER TABLE themes_templates MODIFY active VARCHAR(1) NOT NULL DEFAULT 'Y' COMMENT 'Is this template active (as in: will it be used).';
ALTER TABLE users MODIFY active VARCHAR(1) NOT NULL DEFAULT 'Y' COMMENT 'is this user active?';
ALTER TABLE users MODIFY deleted VARCHAR(1) NOT NULL DEFAULT 'N' COMMENT 'is the user deleted?';
ALTER TABLE users MODIFY is_god VARCHAR(1) NOT NULL DEFAULT 'N';

ALTER TABLE meta ADD seo_follow VARCHAR(255) DEFAULT NULL COMMENT '(DC2Type:seo_follow)', ADD seo_index VARCHAR(255) DEFAULT NULL COMMENT '(DC2Type:seo_index)';
UPDATE meta
SET seo_index = "noindex"
WHERE data IS NOT NULL AND data LIKE '%seo_index";s:7:"noindex"%';
UPDATE meta
SET seo_index = "index"
WHERE data IS NOT NULL AND data LIKE '%seo_index";s:5:"index"%';
UPDATE meta
SET seo_index = "none"
WHERE data IS NOT NULL AND data LIKE '%seo_index";s:4:"none"%';
UPDATE meta
SET seo_follow = "nofollow"
WHERE data IS NOT NULL AND data LIKE '%seo_follow";s:8:"nofollow"%';
UPDATE meta
SET seo_follow = "follow"
WHERE data IS NOT NULL AND data LIKE '%seo_follow";s:6:"follow"%';
UPDATE meta
SET seo_follow = "none"
WHERE data IS NOT NULL AND data LIKE '%seo_follow";s:4:"none"%';

UPDATE blog_posts SET hidden = CASE WHEN hidden = "Y" THEN 1 ELSE 0 END;
ALTER TABLE blog_posts MODIFY hidden TINYINT(1) DEFAULT '0' NOT NULL;
UPDATE blog_posts SET allow_comments = CASE WHEN allow_comments = "Y" THEN 1 ELSE 0 END;
ALTER TABLE blog_posts MODIFY allow_comments TINYINT(1) NOT NULL default '0';
UPDATE content_blocks SET hidden = CASE WHEN hidden= "Y" THEN 1 ELSE 0 END;
ALTER TABLE content_blocks MODIFY hidden TINYINT(1) NOT NULL default '0';
UPDATE location SET show_overview = CASE WHEN show_overview= "Y" THEN 1 ELSE 0 END;
ALTER TABLE location MODIFY show_overview TINYINT(1) NOT NULL DEFAULT '1';
UPDATE meta SET keywords_overwrite = CASE WHEN keywords_overwrite= "Y" THEN 1 ELSE 0 END;
ALTER TABLE meta MODIFY keywords_overwrite TINYINT(1) NOT NULL default '0';
UPDATE meta SET description_overwrite = CASE WHEN description_overwrite= "Y" THEN 1 ELSE 0 END;
ALTER TABLE meta MODIFY description_overwrite TINYINT(1) NOT NULL default '0';
UPDATE meta SET title_overwrite = CASE WHEN title_overwrite= "Y" THEN 1 ELSE 0 END;
ALTER TABLE meta MODIFY title_overwrite TINYINT(1) NOT NULL default '0';
UPDATE meta SET url_overwrite = CASE WHEN url_overwrite= "Y" THEN 1 ELSE 0 END;
ALTER TABLE meta MODIFY url_overwrite TINYINT(1) NOT NULL default '0';
UPDATE modules_extras SET hidden = CASE WHEN hidden= "Y" THEN 1 ELSE 0 END;
ALTER TABLE modules_extras MODIFY hidden TINYINT(1) NOT NULL default '0';
UPDATE pages SET navigation_title_overwrite = CASE WHEN navigation_title_overwrite= "Y" THEN 1 ELSE 0 END;
ALTER TABLE pages MODIFY navigation_title_overwrite TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'should we override the navigation title';
UPDATE pages SET allow_move = CASE WHEN allow_move= "Y" THEN 1 ELSE 0 END;
ALTER TABLE pages MODIFY allow_move TINYINT(1) NOT NULL DEFAULT '1';
UPDATE pages SET allow_children = CASE WHEN allow_children= "Y" THEN 1 ELSE 0 END;
ALTER TABLE pages MODIFY allow_children TINYINT(1) NOT NULL DEFAULT '1';
UPDATE pages SET hidden = CASE WHEN hidden= "Y" THEN 1 ELSE 0 END;
ALTER TABLE pages MODIFY hidden TINYINT(1) NOT NULL DEFAULT '1';
UPDATE pages SET allow_edit = CASE WHEN allow_edit= "Y" THEN 1 ELSE 0 END;
ALTER TABLE pages MODIFY allow_edit TINYINT(1) NOT NULL DEFAULT '1';
UPDATE pages SET allow_delete = CASE WHEN allow_delete= "Y" THEN 1 ELSE 0 END;
ALTER TABLE pages MODIFY allow_delete TINYINT(1) NOT NULL DEFAULT '1';
UPDATE pages_blocks SET visible = CASE WHEN visible= "Y" THEN 1 ELSE 0 END;
ALTER TABLE pages_blocks MODIFY visible TINYINT(1) NOT NULL DEFAULT '1';
UPDATE search_index SET active = CASE WHEN active= "Y" THEN 1 ELSE 0 END;
ALTER TABLE search_index MODIFY active TINYINT(1) NOT NULL DEFAULT '0';
UPDATE search_modules SET searchable = CASE WHEN searchable= "Y" THEN 1 ELSE 0 END;
ALTER TABLE search_modules MODIFY searchable TINYINT(1) NOT NULL DEFAULT '0';
UPDATE themes_templates SET active = CASE WHEN active= "Y" THEN 1 ELSE 0 END;
ALTER TABLE themes_templates MODIFY active TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'Is this template active (as in: will it be used).';
UPDATE users SET active = CASE WHEN active= "Y" THEN 1 ELSE 0 END;
ALTER TABLE users MODIFY active TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'is this user active?';
UPDATE users SET deleted = CASE WHEN deleted= "Y" THEN 1 ELSE 0 END;
ALTER TABLE users MODIFY deleted TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'is the user deleted?';
UPDATE users SET is_god = CASE WHEN is_god= "Y" THEN 1 ELSE 0 END;
ALTER TABLE users MODIFY is_god TINYINT(1) NOT NULL DEFAULT '0';

UPDATE modules_settings SET value = 's:8:"sendmail";' WHERE name = 'mailer_type' AND value = 's:4:"mail";';

ALTER TABLE forms ADD email_template VARCHAR(255) DEFAULT "Form.html.twig";
ALTER TABLE forms ADD email_subject VARCHAR(255) NULL;
ALTER TABLE forms MODIFY `method` enum('database','database_email','email') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'database_email';

ALTER TABLE pages_blocks ADD extra_type varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'rich_text';
ALTER TABLE pages_blocks ADD extra_data text COLLATE utf8mb4_unicode_ci;