Snippets

Learning.com ContentCatalog-to-CDS LearningObject Migration Scripts

Created by Will Wolff-Myren last modified
00-README.md
Raw

Run the following scripts in order!

01-PlayerId-MigrationScript.sql
Raw 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/**************************************************************************************************
* Name:         PAN-457 - Write script to update existing Custom Journals in ContentCatalog to have GamifiedMapData, PlayerId, and CustomFormParameters
* Description:  Update or create CurriculumItemDetail records with player ID.
*               Updates only custom items, licensed content is already updated with a skupush.
*
* Author:       bbennett, wwolff-myren
* Date Created: 2020OCT13
*
* History:      2020OCT13 - bbennett - Adapted from previous migration script from dhogue and kbrewerlowe to update map data for custom GPs
*               2020OCT14 - wwolff-myren - Adapted from bbennett's Custom WebLink and kbrewer-lowe's Custom Activity migration scripts.
**************************************************************************************************/
SET NOCOUNT ON;
DROP TABLE IF EXISTS #PlayerData;
DECLARE @PlayerId VARCHAR(4000) = 'ddf0df05-1b8b-4fd9-b6e4-e95129b77ba4';
DECLARE @DetailItemTypeId INT = 11;
DECLARE @ItemTypeIdCustomJournal INT = 11;

-- Gather the data into a temp table
SELECT DISTINCT
    ci.CurriculumItemId,
    ci.CurriculumItemName,
    @PlayerId AS DetailItemText,
    @DetailItemTypeId AS DetailItemType
INTO #PlayerData
FROM ContentCatalog.dbo.CurriculumItem AS ci
WHERE ci.CurriculumItemTypeId = @ItemTypeIdCustomJournal
AND ci.Valid = 1
PRINT CAST(@@ROWCOUNT AS VARCHAR(9)) + ' CurriculumItems to be checked';

-- Update any existing data that doesn't match
UPDATE cid
SET DetailItemText = pd.DetailItemText
FROM #PlayerData AS pd
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail AS cid ON pd.CurriculumItemId = cid.CurriculumItemId AND pd.DetailItemType = cid.DetailItemType
WHERE cid.DetailItemId IS NOT NULL
    AND cid.DetailItemText != pd.DetailItemText;
PRINT CAST(@@ROWCOUNT AS VARCHAR(9)) + ' existing CurriculumItemDetails updated';

-- Add any rows that don't exist
INSERT INTO ContentCatalog.dbo.CurriculumItemDetail (CurriculumItemId, DetailItemText, DetailItemType)
SELECT pd.CurriculumItemId, pd.DetailItemText, pd.DetailItemType
FROM #PlayerData AS pd
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail AS cid ON pd.CurriculumItemId = cid.CurriculumItemId AND pd.DetailItemType = cid.DetailItemType
WHERE cid.DetailItemId IS NULL;
PRINT CAST(@@ROWCOUNT AS VARCHAR(9)) + ' new CurriculumItemDetails added';
02-GamifiedMapData-MigrationScript.sql
Raw 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/**************************************************************************************************
* Name:         PAN-457 - Write script to update existing Custom Journals in ContentCatalog to have GamifiedMapData, PlayerId, and CustomFormParameters
* Description:  Update or create CurriculumItemDetail records with player ID.
*               Updates only custom items, licensed content is already updated with a skupush.
*
* Author:       bbennett, wwolff-myren
* Date Created: 2020OCT13
*
* History:      2020OCT13 - bbennett - Adapted from previous migration script from dhogue and kbrewerlowe to update map data for custom GPs
*               2020OCT14 - wwolff-myren - Adapted from bbennett's Custom WebLink and kbrewer-lowe's Custom Activity migration scripts.
**************************************************************************************************/
SET NOCOUNT ON;
DROP TABLE IF EXISTS #MapData;
DECLARE @DetailItemTypeId INT = 36;
DECLARE @ItemTypeIdCustomJournal INT = 11;

-- Gather the data into a temp table
SELECT DISTINCT
    ci.CurriculumItemId,
    ci.CurriculumItemName,
    '{ "NewTab": true, "BlocksWhen": "Never", "StarsWhen": "TurnedIn", "StarsLogic": "ThreeStars", "ArcadeItem": false }' AS [JSON],
    @DetailItemTypeId AS DetailItemType
INTO #MapData
FROM ContentCatalog.dbo.CurriculumItem AS ci
WHERE ci.CurriculumItemTypeId = @ItemTypeIdCustomJournal
AND ci.Valid = 1
PRINT CAST(@@ROWCOUNT AS VARCHAR(9)) + ' CurriculumItems to be checked';

-- Update any existing data that doesn't match
UPDATE cid
SET DetailItemText = md.[JSON]
FROM #MapData AS md
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail AS cid ON md.CurriculumItemId = cid.CurriculumItemId AND md.DetailItemType = cid.DetailItemType
WHERE cid.DetailItemId IS NOT NULL
    AND cid.DetailItemText != md.[JSON];
PRINT CAST(@@ROWCOUNT AS VARCHAR(9)) + ' existing CurriculumItemDetails updated';

-- Add any rows that don't exist
INSERT INTO ContentCatalog.dbo.CurriculumItemDetail (CurriculumItemId, DetailItemText, DetailItemType)
SELECT md.CurriculumItemId, md.[JSON], md.DetailItemType
FROM #MapData AS md
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail AS cid ON md.CurriculumItemId = cid.CurriculumItemId AND md.DetailItemType = cid.DetailItemType
WHERE cid.DetailItemId IS NULL;
PRINT CAST(@@ROWCOUNT AS VARCHAR(9)) + ' new CurriculumItemDetails added';
03-CustomFormParameters-MigrationScript.sql
Raw 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/**************************************************************************************************
* Name:         PAN-455 - Write script to update existing Custom Journals in ContentCatalog to have GamifiedMapData, PlayerId, and CustomFormParameters
* Description:  Update or create CurriculumItemDetail records with CustomFormParameters.
*
* Author:       kbrewerlowe
* Date Created: 2020OCT15
*
* History:      2020OCT15 - kbrewerlowe - Adapted from previous migration script to update map data for custom GPs 
*               2020OCT15 - wwolff-myren - Adapted from bbennett's Custom WebLink and kbrewer-lowe's Custom Activity migration scripts.
**************************************************************************************************/

SET NOCOUNT ON;
DROP TABLE IF EXISTS #CustomFormParameters;
DECLARE @DetailItemTypeId INT = 12;
DECLARE @ItemTypeIdCustomJournal INT = 11;
DECLARE @CustomParameters VARCHAR(4000) = 'custom_content_item_type=CurriculumItem';

-- Gather the data into a temp table
SELECT DISTINCT
    ci.CurriculumItemId,
    ci.CurriculumItemName,
    @CustomParameters AS DetailItemText,
    @DetailItemTypeId AS DetailItemType
INTO #CustomFormParameters
FROM ContentCatalog.dbo.CurriculumItem AS ci
WHERE ci.CurriculumItemTypeId = @ItemTypeIdCustomJournal
AND ci.Valid = 1
PRINT CAST(@@ROWCOUNT AS VARCHAR(9)) + ' CurriculumItems to be checked';

-- Update any existing data that doesn't match
UPDATE cid
SET DetailItemText = cfp.DetailItemText
FROM #CustomFormParameters AS cfp
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail AS cid ON cfp.CurriculumItemId = cid.CurriculumItemId AND cfp.DetailItemType = cid.DetailItemType
WHERE cid.DetailItemId IS NOT NULL
    AND cid.DetailItemText != cfp.DetailItemText;
PRINT CAST(@@ROWCOUNT AS VARCHAR(9)) + ' existing CurriculumItemDetails updated';

-- Add any rows that don't exist
INSERT INTO ContentCatalog.dbo.CurriculumItemDetail (CurriculumItemId, DetailItemText, DetailItemType)
SELECT cfp.CurriculumItemId, cfp.DetailItemText, cfp.DetailItemType
FROM #CustomFormParameters AS cfp
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail AS cid ON cfp.CurriculumItemId = cid.CurriculumItemId AND cfp.DetailItemType = cid.DetailItemType
WHERE cid.DetailItemId IS NULL;
PRINT CAST(@@ROWCOUNT AS VARCHAR(9)) + ' new CurriculumItemDetails added';
04-CDSDeliveryData-Deduped.sql
Raw 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
SET NOCOUNT ON;
DROP TABLE IF EXISTS #CDSDeliveryData;
DECLARE @ItemTypeIdCustomJournal INT = 11;

-- Gather the data into a temp table
SELECT DISTINCT
            ci.CurriculumItemId
            ,ci.CurriculumItemName
            ,cid.DetailItemText AS CurriculumItemDescription
            ,cast(cid1.DetailItemText as varchar(36)) AS PlayerId
            ,cid2.DetailItemText AS CustomParameters
            ,cid3.DetailItemText AS PartnerUrl
            ,cid4.DetailItemText AS PartnerIntegration
            ,ci.PossibleScore
            ,ci.AvailableLanguages
            ,ci.LowGrade
            ,ci.HighGrade
            ,ci.[LastUpdateDate]
            ,ci.[LastUpdateUser]
            ,ci.[auditCreateDate]
            ,ci.Valid
            ,dlo.LearningObjectId as [DeliveryLearningObjectId]
            ,slo.LearningObjectId as [ScoringLearningObjectId]
INTO #CDSDeliveryData
FROM ContentCatalog.dbo.CurriculumItem AS ci
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid ON ci.CurriculumItemId = cid.CurriculumItemId and cid.DetailItemType = 5
    INNER JOIN ContentCatalog.dbo.CurriculumItemDetail cid1 ON ci.CurriculumItemId = cid1.CurriculumItemId and cid1.DetailItemType = 11 and NULLIF(cid1.DetailItemText, '') is not null
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid2 ON ci.CurriculumItemId = cid2.CurriculumItemId and cid2.DetailItemType = 12
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid3 ON ci.CurriculumItemId = cid3.CurriculumItemId and cid3.DetailItemType = 7
    LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid4 ON ci.CurriculumItemId = cid4.CurriculumItemId and cid4.DetailItemType = 27
    LEFT JOIN CDSDelivery.dbo.LearningObject AS dlo ON ci.CurriculumItemId = dlo.LearningObjectId
    LEFT JOIN CDSScoring.dbo.LearningObject AS slo on ci.CurriculumItemId = slo.LearningObjectId
WHERE ci.CurriculumItemTypeId = @ItemTypeIdCustomJournal
AND ci.Valid = 1

-- SELECT * FROM #CDSDeliveryData WHERE CurriculumItemId IN (
--     SELECT CurriculumItemId FROM #CDSDeliveryData GROUP BY CurriculumItemId HAVING COUNT(CurriculumItemId) > 1
-- ) ORDER BY CurriculumItemId

DELETE FROM #CDSDeliveryData WHERE CurriculumItemId IN (
    SELECT CurriculumItemId FROM #CDSDeliveryData GROUP BY CurriculumItemId HAVING COUNT(CurriculumItemId) > 1
)

SELECT * FROM #CDSDeliveryData

---

    --Merge Item records In CDSScoring
    MERGE INTO CDSScoring.dbo.LearningObject lo
        USING (SELECT
                ci.CurriculumItemId
                ,ci.PossibleScore
                ,ci.[LastUpdateDate]
                ,ci.[LastUpdateUser]
                ,ci.[auditCreateDate]
            FROM #CDSDeliveryData ci
            WHERE ci.Valid = 1
            --AND ci.ScoringLearningObjectId IS NULL
            ) src
        ON lo.LearningObjectId = src.CurriculumItemId
    WHEN MATCHED
         THEN UPDATE SET 
            PossibleScore = src.PossibleScore, 
            auditUpdateDate = src.LastUpdateDate,
            auditUpdateUser = src.LastUpdateUser,
            auditCreateDate = src.auditCreateDate
    WHEN NOT MATCHED BY TARGET THEN
         INSERT (LearningObjectId
                ,PossibleScore
                ,auditUpdateDate
                ,auditUpdateUser
                ,auditCreateDate)
         VALUES (src.CurriculumItemId,
                 src.PossibleScore,
                 src.LastUpdateDate, 
                 src.LastUpdateUser, 
                 src.auditCreateDate);

    --Merge Item records in Delivery
    MERGE INTO CDSDelivery.dbo.LearningObject lo
        USING (SELECT DISTINCT
                ci.CurriculumItemId
                ,ci.CurriculumItemName
                ,cid.DetailItemText AS CurriculumItemDescription
                ,cast(cid1.DetailItemText as varchar(36)) AS PlayerId
                ,cid2.DetailItemText AS CustomParameters
                ,cid3.DetailItemText AS PartnerUrl
                ,cid4.DetailItemText AS PartnerIntegration
                ,ci.AvailableLanguages
                ,ci.LowGrade
                ,ci.HighGrade
                ,ci.[LastUpdateDate]
                ,ci.[LastUpdateUser]
                ,ci.[auditCreateDate]
            FROM #CDSDeliveryData ci
                LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid ON ci.CurriculumItemId = cid.CurriculumItemId and cid.DetailItemType = 5 
                INNER JOIN ContentCatalog.dbo.CurriculumItemDetail cid1 ON ci.CurriculumItemId = cid1.CurriculumItemId and cid1.DetailItemType = 11 and NULLIF(cid1.DetailItemText, '') is not null
                LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid2 ON ci.CurriculumItemId = cid2.CurriculumItemId and cid2.DetailItemType = 12 
                LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid3 ON ci.CurriculumItemId = cid3.CurriculumItemId and cid3.DetailItemType = 7
                LEFT JOIN ContentCatalog.dbo.CurriculumItemDetail cid4 ON ci.CurriculumItemId = cid4.CurriculumItemId and cid4.DetailItemType = 27 
            WHERE ci.Valid = 1 
            --AND ci.DeliveryLearningObjectId IS NULL
            ) src
        ON lo.LearningObjectId = src.CurriculumItemId
    WHEN MATCHED
         THEN UPDATE SET 
            LearningObjectName = src.CurriculumItemName,
            LearningObjectDescription = src.CurriculumItemDescription, 
            PlayerId = src.PlayerId,
            CustomParameters = src.CustomParameters,
            PartnerUrl = src.PartnerUrl,
            PartnerIntegration = src.PartnerIntegration,
            AvailableLanguages = src.AvailableLanguages, 
            LowGrade = src.LowGrade, 
            HighGrade = src.HighGrade, 
            auditUpdateDate = src.LastUpdateDate,
            auditUpdateUser = src.LastUpdateUser,
            auditCreateDate = src.auditCreateDate
    WHEN NOT MATCHED BY TARGET THEN
         INSERT (LearningObjectId
                ,LearningObjectName 
                ,LearningObjectDescription
                ,PlayerId
                ,CustomParameters
                ,PartnerUrl
                ,AvailableLanguages
                ,LowGrade
                ,HighGrade
                ,auditUpdateDate
                ,auditUpdateUser
                ,auditCreateDate)
         VALUES (src.CurriculumItemId,
                 src.CurriculumItemName, 
                 src.CurriculumItemDescription, 
                 src.PlayerId, 
                 src.CustomParameters, 
                 src.PartnerUrl, 
                 src.AvailableLanguages,
                 src.LowGrade,
                 src.HighGrade, 
                 src.LastUpdateDate, 
                 src.LastUpdateUser, 
                 src.auditCreateDate);

Comments (0)

HTTPS SSH

You can clone a snippet to your computer for local editing. Learn more.