EmbeddedContentToArchetype.sql

BEGIN TRANSACTION

PRINT 'Migrating EmbeddedContent pre-values'
EXEC('CREATE SCHEMA u7migration')

EXEC('
-- http://stackoverflow.com/a/2703/39068
CREATE FUNCTION u7migration.SplitString (
    @String     nvarchar(max), 
    @Separator  varchar(2)
)
RETURNS TABLE
AS
RETURN (
    WITH Tokens(p, a, b) AS (
        SELECT 
            CAST(1 as bigint), 
            CAST(1 as bigint), 
            CHARINDEX(@Separator, @String)
        UNION ALL
        SELECT
            p + LEN(@Separator), 
            b + LEN(@Separator), 
            CHARINDEX(@Separator, @String, b + LEN(@Separator))
        FROM tokens
        WHERE b > 0
    )
    SELECT
        p-1 AS [Index],
        SUBSTRING(
            @String, 
            a, 
            CASE WHEN b > 0 THEN b-a ELSE LEN(@String) END
        ) AS [Text]
    FROM
        Tokens
);
')


EXEC('
CREATE FUNCTION u7migration.FieldsetLabel(
    @DataTypeName nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN
    RETURN (
        CASE 
            WHEN @DataTypeName LIKE ''%ies'' THEN LEFT(@DataTypeName, LEN(@DataTypeName) - 3) + ''y''
            WHEN @DataTypeName LIKE ''%s''   THEN LEFT(@DataTypeName, LEN(@DataTypeName) - 1)
            ELSE @DataTypeName
        END
    )
END
')

EXEC('
CREATE FUNCTION u7migration.FieldsetAlias (
    @DataTypeName nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN
    DECLARE @Label nvarchar(255) = u7migration.FieldsetLabel(@DataTypeName)
    RETURN STUFF(REPLACE(@Label, '' '', ''''), 1, 1, LOWER(LEFT(@Label, 1))) 
END
')

DECLARE @ArchetypeConfigs TABLE (
    DataTypeNodeId int NOT NULL PRIMARY KEY,
    ConfigJson     nvarchar(max) NOT NULL
)

;WITH DataTypes AS (
    SELECT
        dataType.nodeId AS DataTypeNodeId,
        node.[text] AS DataTypeName
    FROM
        dbo.cmsDataType AS dataType        
        INNER JOIN dbo.umbracoNode AS node ON
            node.id = dataType.nodeId
    WHERE
        dataType.propertyEditorAlias = 'Imulus.Archetype'
),
OldProperties AS (
    SELECT
        dataType.DataTypeNodeId,
        ROW_NUMBER() OVER(PARTITION BY dataType.DataTypeNodeId ORDER BY Property.[Index]) AS PropertyIndex,
        LTRIM(RTRIM(Property.[Text])) AS PropertyText
    FROM
        DataTypes AS dataType
        LEFT JOIN dbo.cmsDataTypePreValues AS preValue ON
            preValue.datatypeNodeId = dataType.DataTypeNodeId
        CROSS APPLY u7migration.SplitString(preValue.value, '||') AS Property
    WHERE
        LTRIM(RTRIM(Property.[Text])) != ''
),
OldPropertyOptions AS (
    SELECT
        DataTypeNodeId,
        PropertyIndex,
        OptionName,
        OptionValue
    FROM
        OldProperties
        CROSS APPLY u7migration.SplitString(PropertyText, '|') AS PropertyOption
        CROSS APPLY (
            SELECT (CASE WHEN PropertyOption.[Text] LIKE '%:%' THEN ':' ELSE '?' END) AS SeparatorValue
        ) AS NameValueSeparator
        CROSS APPLY (
            SELECT RTRIM(LTRIM([Text])) AS OptionName FROM u7migration.SplitString(PropertyOption.[Text], SeparatorValue) WHERE [Index] = 0
        ) AS OptionNameTable
        OUTER APPLY (
            SELECT RTRIM(LTRIM([Text])) AS OptionValue FROM u7migration.SplitString(PropertyOption.[Text], SeparatorValue) WHERE [Index] = 1
        ) AS OptionValueTable
),
OldPropertyRows AS (
    SELECT
        [Columns].*
    FROM
        OldPropertyOptions
        PIVOT (
            MAX(OptionValue) FOR OptionName IN ([id], [Name], [Required], [Show in title], [Alias], [Type], [Description], [Validation])
        ) AS [Columns]
    WHERE
        [Columns].[id] IS NOT NULL
),
ArchetypeProperties AS (
    SELECT
        DataTypeNodeId,
        PropertyIndex,
        '{ "alias": "' + Alias + '", "label": "' + Name + '", "helpText": "' + [Description] + '", "dataTypeGuid": "' + LOWER(CAST(fieldDataTypeNode.uniqueID AS nvarchar(60))) + '", "required": ' + COALESCE([Required], 'false') + ', "value": "" }' AS PropertyJson
    FROM
        OldPropertyRows AS [property]
        INNER JOIN dbo.umbracoNode AS fieldDataTypeNode ON
            fieldDataTypeNode.[text] = [property].[Type]
            AND
            fieldDataTypeNode.nodeObjectType = '30A2A501-1978-4DDB-A57B-F7EFED43BA3C'
),
ArchetypeConfig AS (
    SELECT
        DataTypeNodeId,        
        '{ "enableCollapsing": true, "fieldsets": [{ "alias": "'
            + u7migration.FieldsetAlias(dataType.DataTypeName)
            + '", "label": "'
            + u7migration.FieldsetLabel(dataType.DataTypeName)
            + '", "labelTemplate": "", properties: [' 
            + STUFF((
                  SELECT
                      ', ' + PropertyJson
                  FROM
                      ArchetypeProperties AS [inner]
                  WHERE
                      [inner].DataTypeNodeId = dataType.DataTypeNodeId
                  ORDER BY
                       PropertyIndex
                  FOR XML PATH('')
              ), 1, 1, '')
            + ']}]}' AS ConfigJson
    FROM
        DataTypes AS dataType
)
INSERT @ArchetypeConfigs (
    DataTypeNodeId,
    ConfigJson
)
SELECT
    DataTypeNodeId,
    ConfigJson
FROM
    ArchetypeConfig
    
UPDATE dbo.cmsDataTypePreValues
SET value = ConfigJson,
    alias = 'archetypeConfig'
FROM
    dbo.cmsDataTypePreValues AS preValue
    INNER JOIN @ArchetypeConfigs AS newConfig ON
        newConfig.DataTypeNodeId = preValue.datatypeNodeId

PRINT 'Migrating EmbeddedContent values'
-- { "fieldsets": [{"alias": "fieldsetAlias", "properties": [{ "alias": "textstringPropertyAlias", "value": "ABC"}, {"alias": "contentPickerPropertyAlias", "value": "1114"}]}, {"alias": "fieldsetAlias", "properties": [{ "alias": "textstringPropertyAlias", "value": "WWW" }, { "alias": "contentPickerPropertyAlias", "value": "1142"}]}]}

UPDATE
    dbo.cmsPropertyData
SET
    dataNText = NULL
FROM
    dbo.cmsPropertyData AS propertyData
    INNER JOIN dbo.cmsPropertyType AS propertyType ON
        propertyType.id = propertyData.propertytypeid
    INNER JOIN dbo.cmsDataType AS dataType ON
        dataType.nodeId = propertyType.dataTypeId
WHERE
    propertyEditorAlias = 'Imulus.Archetype'
    AND
    dataNText NOT LIKE '_%'

; WITH OldData AS (
    SELECT
        propertyData.id AS DataId,
        [node].[text] AS DataTypeName,
        CAST(dataNText AS xml) AS DataXml
    FROM
        dbo.cmsPropertyData AS propertyData
        INNER JOIN dbo.cmsPropertyType AS propertyType ON
            propertyType.id = propertyData.propertytypeid
        INNER JOIN dbo.cmsDataType AS dataType ON
            dataType.nodeId = propertyType.dataTypeId
        INNER JOIN dbo.umbracoNode AS node ON
            [node].id = dataType.nodeId    
    WHERE
        propertyEditorAlias = 'Imulus.Archetype'
),
OldProperties AS (
    SELECT
        DataId,
        DataTypeName,
        ItemXml.value('@id', 'int') AS ItemId,
        PropertyXml.value('local-name(.)', 'nvarchar(4000)') AS PropertyName,
        PropertyXml.value('.',             'nvarchar(4000)') AS PropertyValue
    FROM
        OldData
        CROSS APPLY DataXml.nodes('//item') AS Items(ItemXml)
        CROSS APPLY ItemXml.nodes('*') AS Properties(PropertyXml)
),
ArchetypeProperties AS (
    SELECT
        DataId,
        DataTypeName,
        ItemId,
        '{ "alias": "' + PropertyName + '", "value": "' + PropertyValue + '" }' As PropertyJson
    FROM
        OldProperties
),
ArchetypeFieldsets AS (
    SELECT
        DataId,
        ItemId,
        '{ "alias": "' + u7migration.FieldsetAlias(DataTypeName) + '", "properties": [' + STUFF((
            SELECT
                ', ' + PropertyJson
            FROM
                ArchetypeProperties AS [inner]
            WHERE
                [inner].DataId = [outer].DataId
                AND
                [inner].ItemId = [outer].ItemId
            FOR XML PATH('')
        ), 1, 2, '') + ']}' AS FieldsetJson
    FROM
        ArchetypeProperties AS [outer]
    GROUP BY
        DataId,
        DataTypeName,
        ItemId
),
ArchetypeData AS (
    SELECT
        DataId,
        CASE
            WHEN FieldsetArrayJson IS NOT NULL THEN '{ "fieldsets": [' + FieldsetArrayJson + ']}' 
            ELSE NULL
        END AS DataJson
    FROM
        OldData AS [data]
        OUTER APPLY (
            SELECT STUFF((
                SELECT
                    ', ' + FieldsetJson
                FROM
                    ArchetypeFieldsets AS [inner]
                WHERE
                    [inner].DataId = [data].DataId
                ORDER BY
                    [inner].ItemId
                FOR XML PATH('')
            ), 1, 2, '') AS FieldsetArrayJson
        ) AS _
)
UPDATE dbo.cmsPropertyData
SET
    dataNText = DataJson
FROM
    dbo.cmsPropertyData AS propertyData
    INNER JOIN ArchetypeData AS archetypeData ON
        archetypeData.DataId = propertyData.id

EXEC('DROP FUNCTION u7migration.SplitString');
EXEC('DROP FUNCTION u7migration.FieldsetAlias');
EXEC('DROP FUNCTION u7migration.FieldsetLabel');
EXEC('DROP SCHEMA u7migration');

COMMIT TRANSACTION