Convert block type CMS entries to Rich HTML Textarea entries - with JSON objects too

mysql_convert_blocksource_to_target.sql

-- # Convert block type CMS entries to Rich HTML Textarea entries - with JSON objects too #

-- Because there will be large joins make this size ridiculous
SET SESSION group_concat_max_len = 6000000;

-- String variable replacement in mysql is handy
SET @uploads_target_path = CAST('/storage/uploads/' AS CHAR CHARACTER SET utf8);

-- this inserts rows with concatenated formatted blocks 
-- from source_db.page and source_db.block
-- into target_db.pages
INSERT INTO target_db.pages
(
    `id`,
    `configuration_id`,
    `employee_id`,
    `title`,
    `subtitle`,
    `header`,
    `header_alt_text`,
    `thumbnail`,
    `thumbnail_alt_text`,
    `intro`,
    `button_text`,
    `content`,
    `handler`,
    `landingpage`,
    `form_position`,
    `embedded_code`,
    `retargeting_code`,
    `employee_block_title`,
    `active`,
    `disabled`,
    `redirect_url`,
    `redirect_http_code`,
    `published_at`,
    `ended_at`,
    `created_at`,
    `updated_at`,
    `deleted_at`,
    `import_id`,
    `import_type`,
    `import_info`
)
SELECT DISTINCT
    null as id,
    2 as configuration_id,
    null as employee_id,
    JSON_OBJECT('nl', gp.title) as title,
    IF(gp.sub_title!='', JSON_OBJECT('nl', gp.sub_title), null) as subtitle,
    IF(gp.main_image!='', CONCAT(@uploads_target_path, 'page/main_image/', gp.main_image), null) as header,
    IF(gp.alt_text!='', JSON_OBJECT('nl', gp.alt_text), null) as header_alt_text,
    IF(gp.main_image!='', CONCAT(@uploads_target_path, 'page/main_image/', gp.main_image), null) as thumbnail,
    IF(gp.alt_text!='', JSON_OBJECT('nl', gp.alt_text), null) as thumbnail_alt_text,
    IF(gp.intro!='', JSON_OBJECT('nl', gp.intro), null) as intro,
    IF(gp.button_text!='', JSON_OBJECT('nl', gp.button_text), null) as button_text, --  json DEFAULT NULL,
    JSON_OBJECT(
            'nl',
            GROUP_CONCAT(
                    (
                        CASE
                            WHEN (gb.block_type = 'types_block.block.text')
                                THEN CONCAT(
                                    "<!-- imported block from ", gb.block_type, " - ", gb.id, " - ", gb.position, " -->",
                                    "<div class=\"block block-text wysiwyg\">",
                                    gb.content,
                                    "</div>"
                                )
                            WHEN (gb.block_type = 'types_block.block.embed')
                                THEN CONCAT(
                                    "<!-- imported block from ", gb.block_type, " - ", gb.id, " - ", gb.position, " -->",
                                    "<div class=\"block block-embed\">",
                                    IF(
                                            (gb.name != ''),
                                            CONCAT(
                                                    "<h2 class=\"heading\">",
                                                    gb.name,
                                                    "</h2>"
                                                ),
                                            ''
                                        ),
                                    "<div class=\"embed-responsive embed-responsive-16by9\">",
                                    gb.content,
                                    "</div>",
                                    IF(
                                            (gb.settings ->> '$.description' != ''),
                                            CONCAT(
                                                    "<div class=\"block-caption\">",
                                                    gb.settings ->> '$.description',
                                                    "</div>"
                                                ),
                                            ''
                                        ),
                                    "</div>"
                                )
                            WHEN (gb.block_type = 'types_app.block.quote')
                                THEN CONCAT(
                                    "<!-- imported block from ", gb.block_type, " - ", gb.id, " - ", gb.position, " -->",
                                    "<div class=\"block block-quote\">",

                                    "<div class=\"block-quote-wrapper\">",
                                    "<h2 class=\"heading block-quote-heading\">",
                                    gb.settings ->> '$.quote',
                                    "</h2><a href=\"",
                                    gb.settings ->> '$.url',
                                    "\" ",
                                    IF(
                                                    gb.settings ->> '$.targetBlank' = 'true',
                                                    "target=\"_blank\" ",
                                                    ''
                                        ),
                                    "class=\"link block-quote-link\">",
                                    gb.settings ->> '$.authorText',
                                    "<i class=\"icon icon-angle-right\" ",
                                    "aria-hidden=\"true\"></i></a></div></div>"
                                )
                            WHEN (gb.block_type = 'types_block.block.video')
                                THEN CONCAT(
                                    "<!-- imported block from ", gb.block_type, " - ", gb.id, " - ", gb.position, " -->",
                                    "<div class=\"block-video\">",
                                    IF(
                                            (gb.name != ''),
                                            CONCAT(
                                                    "<h2 class=\"heading\">",
                                                    gb.name,
                                                    "</h2>"
                                                ),
                                            ''
                                        ),
                                    "<div class=\"embed-responsive embed-responsive-16by9\">",
                                    gb.settings ->> '$.html',
                                    "</div>",
                                    IF(
                                            (gb.settings ->> '$.description' != ''),
                                            CONCAT(
                                                    "<div class=\"block block-caption\">",
                                                    gb.settings ->> '$.description',
                                                    "</div>"
                                                ),
                                            ''
                                        ),
                                    "</div>"
                                )
                            WHEN (gb.block_type = 'types_block.block.image')
                                THEN CONCAT(
                                    "<!-- imported block from ", gb.block_type, " - ", gb.id, " - ", gb.position, " -->",
                                    "<div class=\"block block-image",
                                    IF(
                                            (gb.name != ''),
                                            " has-title",
                                            ""
                                        ),
                                    "\">",
                                    IF(
                                            (gb.name != ''),
                                            CONCAT(
                                                    "<h2 class=\"heading\">",
                                                    gb.name,
                                                    "</h2>"
                                                ),
                                            ''
                                        ),
                                    "<div class=\"block-image-holder\">",
                                    "<img src=\"",
                                    @uploads_target_path, 'content/image/', gb.content,
                                    "\" alt=\"",
                                    gb.settings ->> '$.alt',
                                    "\">",
                                    "</div>",
                                    IF(
                                            (gb.settings ->> '$.caption' != ''),
                                            CONCAT(
                                                    "<div class=\"block-caption\">",
                                                    gb.settings ->> '$.caption',
                                                    "</div>"
                                                ),
                                            ''
                                        ),
                                    "</div>"
                                )
                            END
                        )
                    ORDER BY gb.position
                    SEPARATOR "\n<!-- block end -->\n"
                )
        ) as content,

    null as handler,
    0 as landingpage,
    null as form_position,
    null as embedded_code,
    gp.retargeting_snippet as retargeting_code,
    null as employee_block_title,
    gp.index as active,
    0 as disabled,
    null as redirect_url,
    null as redirect_http_code,
    gp.published_at as published_at,
    null as ended_at,
    gp.created_at as created_at,
    gp.updated_at as updated_at,
    null as `deleted_at`,
    gp.id as `import_id`,
    'page' as `import_type`,
    JSON_OBJECT(
            'id', gp.id,
            'type', 'page',
            'import_timestamp', NOW(),
            'parent_id', gp.parent_id,
            'rank', gp.rank,
            'show_article_types', gp.show_article_types,
            'social_sharing_enabled', gp.social_sharing_enabled,
            'show_in_menu', gp.show_in_menu,
            'forward', gp.forward,
            'identifier', gp.identifier,
            'button_text', gp.button_text,
            'form_id', gp.form_id,
            'index', gp.index,
            'follow_links', gp.follow_links,
            'retargeting_snippet', gp.retargeting_snippet
        ) as `import_info`
FROM source_db.page as gp
         LEFT OUTER JOIN source_db.block as gb
                         ON gp.id = gb.block_owner_id
                             AND gb.block_owner_type = 'type_cms.page'
WHERE
    1
GROUP BY
    gp.id
ORDER BY
    gp.id
;