· 8 years ago · Apr 18, 2018, 12:50 PM
1-- IMPORTANT: Expected to be run on 17.x schema.
2-- Depends on Content, ContentIdMap
3
4-- PREP ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
5
6IF OBJECT_ID (N'dbo.migration_IntToXmlBoolean', N'FN') IS NOT NULL
7 DROP FUNCTION migration_IntToXmlBoolean;
8GO
9
10CREATE FUNCTION dbo.migration_IntToXmlBoolean(@BooleanValue int)
11RETURNS NVARCHAR(5)
12AS
13BEGIN
14 RETURN
15 (
16 CASE @BooleanValue
17 WHEN 1 THEN 'True'
18 WHEN 0 THEN 'False'
19 ELSE
20 NULL
21 END
22 )
23END
24
25GO
26
27-- Add DocumentType if needed ------------------------------------------------------------------------------------------
28
29IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'DocumentType')
30BEGIN
31
32 CREATE TABLE [dbo].[DocumentType] (
33 [Id] INT IDENTITY (1, 1) NOT NULL,
34 [Name] NVARCHAR (50) NOT NULL,
35 [AllowedAtRoot] BIT NOT NULL,
36 [AllowsChildren] BIT NOT NULL,
37 [Deleted] DATETIME NULL,
38 [Created] DATETIME NOT NULL DEFAULT GETUTCDATE(),
39 CONSTRAINT [PK_DocumentType] PRIMARY KEY CLUSTERED ([Id] ASC)
40 );
41END
42
43GO
44
45-- Add Document if needed ------------------------------------------------------------------------------------------
46
47IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Document')
48BEGIN
49
50 CREATE TABLE [dbo].[Document]
51 (
52 [Id] INT NOT NULL,
53 [ParentId] INT NULL,
54 [TypeId] INT NOT NULL,
55 [Publicity] INT CONSTRAINT [DF_Document_Publicity] DEFAULT ((0)) NOT NULL,
56 [ReadCount] INT CONSTRAINT [DF_Document_ReadCount] DEFAULT ((0)) NOT NULL,
57 [InheritsParentRoles] BIT CONSTRAINT [DF_Document_InheritsParentRoles] DEFAULT ((1)) NOT NULL,
58 [LatestRevision] DATETIME NOT NULL,
59 [LatestRevisionValidation] DATETIME NULL,
60 [RevisioningEnabled] BIT CONSTRAINT [DF_Document_RevisioningEnabled] DEFAULT ((0)) NOT NULL,
61 [RevisioningPeriodDays] INT NULL,
62 [SortOrder] INT NULL,
63 [Created] DATETIME NOT NULL,
64 [Deleted] DATETIME NULL,
65 [Archived] DATETIME NULL,
66 [PublishDate] DATETIME NULL,
67 CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED ([Id] ASC),
68 CONSTRAINT [FK_Document_Content] FOREIGN KEY ([Id]) REFERENCES [dbo].[Content] ([Id]) ON DELETE CASCADE,
69 CONSTRAINT [FK_Document_DocumentType] FOREIGN KEY ([TypeId]) REFERENCES [dbo].[DocumentType] ([Id])
70 );
71
72END
73
74GO
75
76-- Add Recycled column to Content if needed ------------------------------------------------------------------------
77
78IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'Recycled' AND Object_ID = Object_ID(N'dbo.Content'))
79BEGIN
80 ALTER TABLE [dbo].[Content] ADD [Recycled] DATETIME NULL
81END
82
83GO
84
85-- Set list of built-in document types ------------------------------------------------------------------------------------------
86
87SET IDENTITY_INSERT [DocumentType] ON
88
89MERGE INTO [DocumentType] AS Target
90 USING (VALUES
91 (1, N'Folder', 1, 1),
92 (2, N'Page', 1, 1),
93 (3, N'Link', 0, 0),
94 (4, N'File', 0, 0)
95 )
96 AS Source (Id, Name, AllowedAtRoot, AllowsChildren)
97 ON Target.Id = Source.Id
98WHEN MATCHED THEN
99 -- update matched rows
100 UPDATE SET
101 Name = Source.Name,
102 AllowedAtRoot = Source.AllowedAtRoot,
103 AllowsChildren = Source.AllowsChildren
104WHEN NOT MATCHED BY TARGET THEN
105 -- insert new rows
106 INSERT (Id, Name, AllowedAtRoot, AllowsChildren)
107 VALUES (Id, Name, AllowedAtRoot, AllowsChildren);
108
109SET IDENTITY_INSERT [DocumentType] OFF
110
111-- Allow Content.TypeId to be null ------------------------------------------------------------------------------------------------
112
113IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'TypeId' AND Object_ID = Object_ID(N'dbo.Content'))
114BEGIN
115 ALTER TABLE Content ALTER COLUMN TypeId INTEGER NULL
116END
117ELSE
118BEGIN
119 -- Add TypeId - Ensures compatibility on dex boxes during development of 18.1.
120 ALTER TABLE Content ADD TypeId INTEGER NULL
121END
122
123GO
124
125-- Add a user for representing an Umbraco (admin) user ---------------------------------------------------------------------------
126
127DECLARE @UmbAdminUserId INT
128SET @UmbAdminUserId = (SELECT TOP 1 Id FROM mwUser WHERE CONVERT(NVARCHAR(MAX), UserProvidedData) LIKE '%<isUmbracoAdminReplacementUser>true</isUmbracoAdminReplacementUser>%')
129
130IF (@UmbAdminUserId IS NULL)
131BEGIN
132
133 INSERT INTO [dbo].[mwUser]
134 ([FirstName]
135 ,[UserProvidedData]
136 ,[Updated]
137 ,[Created]
138 ,[Deleted]
139 )
140 VALUES
141 ('Administrator'
142 ,'<set><isUmbracoAdminReplacementUser>true</isUmbracoAdminReplacementUser></set>'
143 ,GETUTCDATE()
144 ,GETUTCDATE()
145 ,GETUTCDATE()
146 )
147
148 SET @UmbAdminUserId = SCOPE_IDENTITY()
149
150END
151
152
153-- MIGRATION SCRIPT ////////////////////////////////////////////////////////////////////////////////////////////////////////////
154
155CREATE TABLE #UmbContent
156(
157 NodeId INT NOT NULL,
158 ParentNodeId INT NOT NULL,
159 [Version] UNIQUEIDENTIFIER NOT NULL,
160 ContentType NVARCHAR(255) NOT NULL,
161 Created DATETIME NOT NULL,
162 LastUpdated DATETIME,
163 Deleted BIT NOT NULL,
164 AuthorUserId INT NOT NULL,
165 OwnerUserId INT
166)
167
168CREATE TABLE #UmbContentVersion
169(
170 NodeId INT NOT NULL,
171 [Version] UNIQUEIDENTIFIER NOT NULL,
172 Created DATETIME NOT NULL,
173 AuthorUserId INT NOT NULL
174)
175
176CREATE TABLE #UmbPropertyData
177(
178 Id INT NOT NULL,
179 NodeId INT NOT NULL,
180 [Version] UNIQUEIDENTIFIER NOT NULL,
181 [propertyalias] [nvarchar](50) NOT NULL,
182 [dataInt] [int] NULL,
183 [dataDate] [datetime] NULL,
184 [dataNvarchar] [nvarchar](500) NULL,
185 [dataNtext] [ntext] NULL
186)
187
188CREATE INDEX contentIndex
189ON #UmbPropertyData (NodeId, [Version], [dataInt], [propertyalias])
190
191CREATE TABLE #activeIds
192(
193 Id INT,
194 VersionId UNIQUEIDENTIFIER,
195 ParentId INT,
196 TypeId INT,
197 Created DATETIME,
198 Deleted DATETIME,
199 Updated DATETIME
200)
201
202-- GET CONTENT -----------------------------------------------------------------------------------------------------------------
203
204-- Build a list of node ids of type 'Wikis' for filtering out unwanted documents
205DECLARE @WikisNodes TABLE (Id INT, PathLikeString NVARCHAR(10))
206
207INSERT INTO @WikisNodes (Id)
208SELECT nodeId FROM cmsContent WHERE contentType = (SELECT nodeId FROM cmsContentType WHERE alias = 'Wikis')
209
210UPDATE @WikisNodes SET PathLikeString = '%,' + CAST(Id AS nvarchar(10)) + ',%'
211
212INSERT INTO #UmbContent
213 (NodeId, ParentNodeId, [Version], ContentType, Created, LastUpdated, Deleted, AuthorUserId)
214SELECT umbracoNode.id, umbracoNode.parentId, cmsDocument2.versionId, cmsContentType.alias, umbracoNode.createDate, cmsDocument2.updateDate, (CASE WHEN trashed = 1 OR cmsDocument2.published = 0 THEN 1 ELSE 0 END), @UmbAdminUserId
215FROM umbracoNode
216CROSS APPLY
217(
218 SELECT TOP 1 *
219 FROM cmsDocument
220 WHERE cmsDocument.nodeId = umbracoNode.id
221 ORDER BY cmsDocument.published DESC, cmsDocument.updateDate DESC
222) cmsDocument2 INNER JOIN
223 cmsContent ON cmsContent.nodeId = umbracoNode.Id INNER JOIN
224 cmsContentType ON cmsContentType.nodeId = cmsContent.contentType LEFT JOIN
225 ContentIdMap cidmap ON cidmap.UmbracoNodeId = umbracoNode.id
226WHERE cmsContentType.alias IN ('DocumentLink', 'DocumentFile', 'WikiFolder', 'WikiPage', 'WikiTree')
227AND umbracoNode.Id NOT IN (SELECT UmbracoNodeId FROM ContentIdMap)
228AND cidmap.UmbracoNodeId IS NULL
229AND NOT EXISTS(
230 -- Ignore documents that have a node of type 'Wikis' in its path
231 SELECT Id FROM @WikisNodes
232 WHERE [path] COLLATE DATABASE_DEFAULT LIKE PathLikeString COLLATE DATABASE_DEFAULT
233)
234
235-- GET CONTENT VERSIONS -----------------------------------------------------------------------------------------------------------------
236
237INSERT INTO #UmbContentVersion (NodeId, [Version], Created, AuthorUserId)
238SELECT cmsContentVersion.ContentId, cmsContentVersion.VersionId, cmsContentVersion.VersionDate, @UmbAdminUserId
239FROM #UmbContent c INNER JOIN
240 cmsContentVersion ON cmsContentVersion.ContentId = c.NodeId
241
242-- GET PROPERTY DATA -----------------------------------------------------------------------------------------------------------------
243
244INSERT INTO #UmbPropertyData (Id,NodeId,[Version],[propertyalias],[dataInt],[dataDate],[dataNvarchar],[dataNtext])
245SELECT extraData.Id, extraData.NodeId, baseValues.Version, baseValues.Alias, extraData.[dataInt],extraData.[dataDate],extraData.[dataNvarchar],extraData.[dataNtext]
246FROM (
247 SELECT distinct cv.Version, cmsPropertyType.Alias
248 FROM cmsPropertyType JOIN
249 cmsPropertyData ON cmsPropertyType.id = cmsPropertyData.propertytypeid JOIN
250 #UmbContentVersion cv ON cmsPropertyData.versionId = cv.Version
251) baseValues
252CROSS APPLY
253(
254 SELECT TOP 1 cmsPropertyData.id, cv.NodeId, [dataInt],[dataDate],[dataNvarchar],[dataNtext]
255 FROM cmsPropertyType JOIN
256 cmsPropertyData ON cmsPropertyType.id = cmsPropertyData.propertytypeid AND baseValues.Alias = cmsPropertyType.Alias JOIN
257 #UmbContentVersion cv ON cmsPropertyData.versionId = cv.Version AND baseValues.Version = cv.Version
258
259) extraData
260
261-- Update umb content with author info ---------------------------------------------------------------------------------------------------
262
263UPDATE #UmbContent
264SET AuthorUserId = ISNULL(mwUser.Id, ISNULL((
265 -- Fallback to lastEditorId, then to @UmbAdminUserId
266 SELECT mwUser2.Id
267 FROM #UmbPropertyData pd2 LEFT JOIN
268 mwUser mwUser2 ON mwUser2.Id = pd2.dataInt OR mwUser2.Id = CAST(pd2.dataNvarchar AS INT)
269 WHERE pd2.propertyalias = 'lastEditorId'
270 AND [#UmbContent].Version = pd2.Version
271 AND [#UmbContent].NodeId = pd2.NodeId
272
273), @UmbAdminUserId))
274FROM #UmbPropertyData pd LEFT JOIN
275 mwUser ON mwUser.Id = pd.dataInt OR mwUser.Id = CAST(pd.dataNvarchar AS INT)
276WHERE pd.propertyalias = 'authorId'
277 AND [#UmbContent].Version = pd.Version
278 AND [#UmbContent].NodeId = pd.NodeId
279
280-- Update umb content version with author info
281
282UPDATE #UmbContentVersion
283SET AuthorUserId = CASE WHEN (SELECT COUNT(*) FROM #UmbContentVersion x WHERE x.NodeId = c.NodeId) < 2 THEN
284 -- Only one version
285 c.AuthorUserId
286 WHEN (SELECT TOP 1 [Version] FROM #UmbContentVersion x WHERE x.NodeId = c.NodeId ORDER BY Created ASC) = pd.Version THEN
287 -- The first version (authorId with fallback to lastEditorId, then umb admin.)
288 ISNULL((
289 SELECT TOP 1 mwUser2.Id
290 FROM #UmbPropertyData pd2
291 LEFT JOIN mwUser mwUser2
292 ON mwUser2.Id = pd2.dataInt OR mwUser2.Id = CAST(pd2.dataNvarchar AS INT)
293 WHERE pd2.propertyalias = 'authorId' AND pd2.Version = pd.Version
294 ), ISNULL(mwUser.Id, @UmbAdminUserId))
295 ELSE
296 -- A subsequent version
297 ISNULL(mwUser.Id, @UmbAdminUserId)
298 END
299FROM #UmbPropertyData pd INNER JOIN
300 #UmbContent c ON c.NodeId = pd.NodeId LEFT JOIN
301 mwUser ON mwUser.Id = pd.dataInt OR mwUser.Id = CAST(pd.dataNvarchar AS INT)
302WHERE pd.propertyalias = 'lastEditorId'
303 AND [#UmbContentVersion].Version = pd.Version
304 AND [#UmbContentVersion].NodeId = pd.NodeId
305
306
307-- Preb affected storage id
308DECLARE @StorageProviderName NVARCHAR(MAX) = ISNULL(
309(
310 SELECT TOP 1 StorageProviderName
311 FROM mwFileVersion
312 WHERE TRY_CAST(StorageId AS UNIQUEIDENTIFIER) IS NOT NULL
313), 'FileSystem')
314
315-- Fill missing values
316UPDATE #UmbPropertyData
317SET dataNvarchar = NEWID()
318WHERE [propertyalias] = 'activeVersionId'
319 AND
320 (
321 dataNvarchar IS NULL
322 OR dataNvarchar = ''
323 )
324
325DECLARE @StorageIds TABLE (Id NVARCHAR(100), UmbContentId INT)
326
327INSERT INTO @StorageIds
328SELECT DISTINCT dataNvarchar, #UmbPropertyData.NodeId
329FROM #UmbPropertyData
330WHERE [propertyalias] = 'activeVersionId'
331 AND dataNvarchar IS NOT NULL
332 AND dataNvarchar != ''
333
334-- MIGRATE -----------------------------------------------------------------------------------------------------------------
335
336BEGIN TRANSACTION;
337
338-- Make sure storage ids have a FileVersion
339INSERT INTO mwFileVersion (StorageId, [FileName], Size, StorageProviderName, Created, AuthorId, SizeBytes)
340SELECT
341 Sids.Id,
342 'file.' + ISNULL(pd1.dataNvarchar, '.ext'),
343 CASE WHEN pd2.dataInt IS NULL THEN NULL ELSE (pd2.dataInt/1024) END,
344 CASE WHEN (TRY_CAST(Sids.Id AS UNIQUEIDENTIFIER) IS NOT NULL) THEN @StorageProviderName ELSE NULL END,
345 GETUTCDATE(),
346 c.AuthorUserId,
347 pd2.dataInt
348FROM @StorageIds Sids
349LEFT JOIN #UmbContent c
350 ON c.NodeId = Sids.UmbContentId
351LEFT JOIN #UmbPropertyData pd1
352 ON pd1.propertyalias = 'extension' AND pd1.Version = c.Version
353LEFT JOIN #UmbPropertyData pd2
354 ON pd2.propertyalias = 'kbsize' AND pd2.Version = c.Version
355LEFT JOIN mwFileVersion fv
356 ON fv.StorageId = Sids.Id
357WHERE fv.Id IS NULL
358
359-- Make sure storage ids have a FileId on their FileVersion and then prep a storage id to file id lookup table for later use.
360DECLARE @NewFileIds TABLE (Id INT)
361
362-- Populate FileVersions with missing FileId
363INSERT INTO mwFile (ActiveVersionId, Created, AuthorId)
364OUTPUT INSERTED.ID INTO @NewFileIds(ID)
365SELECT
366 mwFileVersion.Id,
367 mwFileVersion.Created,
368 mwFileVersion.AuthorId
369FROM mwFileVersion
370INNER JOIN @StorageIds sids
371 ON sids.Id COLLATE DATABASE_DEFAULT = mwFileVersion.StorageId COLLATE DATABASE_DEFAULT
372LEFT JOIN mwFile
373 ON mwFile.Id = mwFileVersion.FileId
374WHERE mwFile.Id IS NULL
375
376-- Update fileversion with new fileids
377UPDATE mwFileVersion
378SET FileId = mwFile.Id
379FROM mwFileVersion fv
380INNER JOIN mwFile
381 ON mwFile.ActiveVersionId = fv.Id
382WHERE mwFile.Id IN (SELECT Id FROM @NewFileIds)
383
384-- Prep. lookup for later swapping of storage id to file id
385DECLARE @FileLookup TABLE
386(
387 FileVersionStorageId NVARCHAR(100) NOT NULL,
388 FileId INT NOT NULL
389)
390
391INSERT INTO @FileLookup
392SELECT
393 sids.Id FileVersionStorageId,
394 mwFileVersion.FileId
395FROM @StorageIds sids
396LEFT JOIN mwFileVersion
397 ON mwFileVersion.StorageId COLLATE DATABASE_DEFAULT = sids.Id COLLATE DATABASE_DEFAULT
398
399
400-- Insert into content -----------------------------------------------------------------------------------------------------------------
401DECLARE @InsertedContentIds TABLE
402(
403 Id INT
404)
405
406INSERT INTO Content (TypeId, AuthorUserId, Created, Updated, Deleted, Recycled)
407OUTPUT inserted.Id
408 INTO @InsertedContentIds
409SELECT
410 NULL,
411 mwUser.Id,
412 c1.Created,
413 LastUpdated,
414 CASE WHEN c1.Deleted = 1 THEN LastUpdated ELSE NULL END,
415 CASE WHEN (
416 SELECT TOP 1 dataInt
417 FROM #UmbPropertyData pd
418 WHERE pd.Version = c1.version AND propertyalias = 'state'
419 ) = 3
420 THEN ISNULL(c1.LastUpdated, c1.Created)
421 ELSE NULL END
422FROM #UmbContent c1 LEFT JOIN
423 mwUser -- Join to weed out deleted users
424 ON mwUser.Id = AuthorUserId
425ORDER BY c1.NodeId
426
427---- Insert into Content - Umbraco mapping table -----------------------------------------------------------------------------------------------------------------
428INSERT INTO ContentIdMap (ColiboContentId, UmbracoNodeId)
429SELECT A.Id,B.NodeId
430FROM (
431 SELECT Id, row_number() OVER (order by Id) AS row_num
432 FROM @InsertedContentIds
433 ) A
434 JOIN
435 (
436 SELECT NodeId, row_number() OVER (order by NodeId) AS row_num
437 FROM #UmbContent UC
438 ) B
439ON A.row_num = B.row_num
440ORDER BY A.Id,B.NodeId
441
442-- Insert versions (base) -----------------------------------------------------------------------------------------------------------------
443DECLARE @InsertedContentVersionIds TABLE
444(
445 Id INT
446)
447
448insert into ContentVersion (AllowComments, AuthorUserId, ContentId, Created, Fields, IsActiveVersion)
449output inserted.Id into @InsertedContentVersionIds
450select
451 isnull(pd.dataInt, 1),
452 mwUser.Id,
453 map.ColiboContentId,
454 cv.Created,
455 null,
456 (select count(*) from #UmbContent where NodeId = cv.NodeId and Version = cv.Version)
457from #UmbContentVersion cv inner join
458 ContentIdMap map on map.UmbracoNodeId = cv.NodeId inner join
459 #UmbContent umbc on map.UmbracoNodeId = umbc.NodeId inner join
460 Content c on c.Id = map.ColiboContentId left join
461 #UmbPropertyData pd on pd.Version = cv.Version and propertyalias = 'enableComments' left join
462 mwUser on mwUser.Id = cv.AuthorUserId
463order by cv.Version
464
465insert into ContentVersionIdMap
466 (ColiboContentVersionId, UmbracoNodeVersionId)
467select A.Id,B.Version
468from(
469 SELECT Id,row_number() over (order by Id) as row_num
470 FROM @InsertedContentVersionIds)A
471join
472 (SELECT Version,row_number() over (order by Version) as row_num
473 FROM #UmbContentVersion UC)B
474on A.row_num=B.row_num
475ORDER BY A.Id,B.Version
476
477-- Insert versions with property data from umb -----------------------------------------------------------------------------------------------------------------
478
479UPDATE ContentVersion
480SET Fields = updateData.xml
481FROM (
482SELECT cvmap.ColiboContentVersionId, (
483 SELECT
484 'String' AS 'title/@t',
485 CASE WHEN pd1.dataNvarchar IS NOT NULL AND pd1.dataNvarchar != '' THEN pd1.dataNvarchar ELSE ISNULL(cmsdoc.[text], 'No title') END AS title,
486 'String' AS 'description/@t',
487 pd2.dataNtext AS [description],
488 'String' AS 'keywords/@t',
489 pd3.dataNvarchar AS [keywords],
490 'String' AS 'icon/@t',
491 pd4.dataNvarchar AS [icon],
492 'String' AS 'color/@t',
493 pd5.dataNvarchar AS [color],
494 'Boolean' AS 'inheritParentColor/@t',
495 dbo.migration_IntToXmlBoolean(pd6.dataInt) AS [inheritParentColor],
496 'Boolean' AS 'inheritParentIcon/@t',
497 dbo.migration_IntToXmlBoolean(pd7.dataInt) AS [inheritParentIcon],
498 'String' AS 'childrenDisplayType/@t',
499 (CASE pd8.dataInt WHEN 1 THEN 'List' WHEN 3 THEN 'Gallery' ELSE 'Blocks' END) AS [childrenDisplayType]
500 FOR XML PATH(''),
501 Root('set')
502 ) AS XML
503FROM ContentVersionIdMap cvmap JOIN
504 @InsertedContentVersionIds insertedcv ON insertedcv.Id = cvmap.ColiboContentVersionId JOIN
505 ContentVersion ON ContentVersion.Id = cvmap.ColiboContentVersionId JOIN
506 ContentIdMap cidmap ON cidmap.ColiboContentId = ContentVersion.ContentId JOIN
507 #UmbContent umbc ON umbc.NodeId = cidmap.UmbracoNodeId and umbc.ContentType != 'WikiPage' AND
508 umbc.ContentType != 'DocumentLink' and umbc.ContentType != 'DocumentFile' LEFT JOIN
509 #UmbPropertyData pd1 ON cvmap.UmbracoNodeVersionId = pd1.Version AND pd1.propertyalias = 'title' AND pd1.dataNvarchar IS NOT NULL AND pd1.dataNvarchar != '' LEFT JOIN
510 #UmbPropertyData pd2 ON cvmap.UmbracoNodeVersionId = pd2.Version AND pd2.propertyalias = 'description' LEFT JOIN
511 #UmbPropertyData pd3 ON cvmap.UmbracoNodeVersionId = pd3.Version AND pd3.propertyalias = 'keywords' LEFT JOIN
512 #UmbPropertyData pd4 ON cvmap.UmbracoNodeVersionId = pd4.Version AND pd4.propertyalias = 'icon' LEFT JOIN
513 #UmbPropertyData pd5 ON cvmap.UmbracoNodeVersionId = pd5.Version AND pd5.propertyalias = 'color' LEFT JOIN
514 #UmbPropertyData pd6 ON cvmap.UmbracoNodeVersionId = pd6.Version AND pd6.propertyalias = 'inheritsIconAndThemeColor' LEFT JOIN
515 #UmbPropertyData pd7 ON cvmap.UmbracoNodeVersionId = pd7.Version AND pd7.propertyalias = 'inheritsIconAndThemeColor' LEFT JOIN
516 #UmbPropertyData pd8 ON cvmap.UmbracoNodeVersionId = pd8.Version AND pd8.propertyalias = 'defaultChildrenDisplayType' LEFT JOIN
517 cmsDocument cmsdoc ON cvmap.UmbracoNodeVersionId = cmsdoc.versionId
518 ) updateData
519WHERE ContentVersion.Id = updateData.ColiboContentVersionId
520
521UPDATE ContentVersion
522SET Fields = updateData.xml
523FROM (
524SELECT cvmap.ColiboContentVersionId, (
525 SELECT
526 'String' AS 'title/@t',
527 CASE WHEN pd1.dataNvarchar IS NOT NULL AND pd1.dataNvarchar != '' THEN pd1.dataNvarchar ELSE ISNULL(cmsdoc.[text], 'No title') END AS title,
528 'String' AS 'description/@t',
529 pd2.dataNtext AS [description],
530 'String' AS 'keywords/@t',
531 pd3.dataNvarchar AS [keywords],
532 'String' AS 'body/@t',
533 pd4.dataNtext AS [body],
534 'String' AS 'icon/@t',
535 pd5.dataNvarchar AS [icon],
536 'String' AS 'color/@t',
537 pd6.dataNvarchar AS [color],
538 'Boolean' AS 'inheritParentColor/@t',
539 dbo.migration_IntToXmlBoolean(pd7.dataInt) AS [inheritParentColor],
540 'Boolean' AS 'inheritParentIcon/@t',
541 dbo.migration_IntToXmlBoolean(pd8.dataInt) AS [inheritParentIcon],
542 'String' AS 'childrenDisplayType/@t',
543 (CASE pd9.dataInt WHEN 1 THEN 'List' WHEN 3 THEN 'Gallery' ELSE 'Blocks' END) AS [childrenDisplayType],
544 'Integer' AS 'umbracoTemplateId/@t',
545 pd10.dataInt AS [umbracoTemplateId]
546 FOR XML PATH(''),
547 Root('set')
548 ) AS XML
549FROM ContentVersionIdMap cvmap JOIN
550 @InsertedContentVersionIds insertedcv ON insertedcv.Id = cvmap.ColiboContentVersionId JOIN
551 ContentVersion ON ContentVersion.Id = cvmap.ColiboContentVersionId JOIN
552 ContentIdMap cidmap ON cidmap.ColiboContentId = ContentVersion.ContentId JOIN
553 #UmbContent umbc ON umbc.NodeId = cidmap.UmbracoNodeId and umbc.ContentType = 'WikiPage' LEFT JOIN
554 #UmbPropertyData pd1 ON cvmap.UmbracoNodeVersionId = pd1.Version AND pd1.propertyalias = 'title' AND pd1.dataNvarchar IS NOT NULL AND pd1.dataNvarchar != '' LEFT JOIN
555 #UmbPropertyData pd2 ON cvmap.UmbracoNodeVersionId = pd2.Version AND pd2.propertyalias = 'description' LEFT JOIN
556 #UmbPropertyData pd3 ON cvmap.UmbracoNodeVersionId = pd3.Version AND pd3.propertyalias = 'keywords' LEFT JOIN
557 #UmbPropertyData pd4 ON cvmap.UmbracoNodeVersionId = pd4.Version AND pd4.propertyalias = 'body' LEFT JOIN
558 #UmbPropertyData pd5 ON cvmap.UmbracoNodeVersionId = pd5.Version AND pd5.propertyalias = 'icon' LEFT JOIN
559 #UmbPropertyData pd6 ON cvmap.UmbracoNodeVersionId = pd6.Version AND pd6.propertyalias = 'color' LEFT JOIN
560 #UmbPropertyData pd7 ON cvmap.UmbracoNodeVersionId = pd7.Version AND pd7.propertyalias = 'inheritsIconAndThemeColor' LEFT JOIN
561 #UmbPropertyData pd8 ON cvmap.UmbracoNodeVersionId = pd8.Version AND pd8.propertyalias = 'inheritsIconAndThemeColor' LEFT JOIN
562 #UmbPropertyData pd9 ON cvmap.UmbracoNodeVersionId = pd9.Version AND pd9.propertyalias = 'defaultChildrenDisplayType' LEFT JOIN
563 #UmbPropertyData pd10 ON cvmap.UmbracoNodeVersionId = pd10.Version AND pd10.propertyalias = 'wikiPageTypeId' LEFT JOIN
564 cmsDocument cmsdoc ON cvmap.UmbracoNodeVersionId = cmsdoc.versionId
565 ) updateData
566WHERE ContentVersion.Id = updateData.ColiboContentVersionId
567
568UPDATE ContentVersion
569SET Fields = updateData.xml
570FROM (
571SELECT cvmap.ColiboContentVersionId, (
572 SELECT
573 'String' AS 'title/@t',
574 CASE WHEN pd1.dataNvarchar IS NOT NULL AND pd1.dataNvarchar != '' THEN pd1.dataNvarchar ELSE ISNULL(cmsdoc.[text], 'No title') END AS title,
575 'String' AS 'description/@t',
576 pd2.dataNtext AS [description],
577 'String' AS 'url/@t',
578 ISNULL(pd3.dataNvarchar, 'http://localhost') AS [url],
579 'Boolean' AS 'openInSameTab/@t',
580 dbo.migration_IntToXmlBoolean(ISNULL(pd4.dataInt, 1)) AS [openInSameTab],
581 'String' AS 'linkType/@t',
582 pd5.dataNvarchar AS [linkType],
583 'String' AS 'icon/@t',
584 pd6.dataNvarchar AS [icon],
585 'String' AS 'color/@t',
586 pd7.dataNvarchar AS [color],
587 'Boolean' AS 'inheritParentColor/@t',
588 dbo.migration_IntToXmlBoolean(pd8.dataInt) AS [inheritParentColor],
589 'Boolean' AS 'inheritParentIcon/@t',
590 dbo.migration_IntToXmlBoolean(pd9.dataInt) AS [inheritParentIcon]
591 FOR XML PATH(''),
592 Root('set')
593 ) AS XML
594FROM ContentVersionIdMap cvmap JOIN
595 @InsertedContentVersionIds insertedcv ON insertedcv.Id = cvmap.ColiboContentVersionId JOIN
596 ContentVersion ON ContentVersion.Id = cvmap.ColiboContentVersionId JOIN
597 ContentIdMap cidmap ON cidmap.ColiboContentId = ContentVersion.ContentId JOIN
598 #UmbContent umbc ON umbc.NodeId = cidmap.UmbracoNodeId and umbc.ContentType = 'DocumentLink' LEFT JOIN
599 #UmbPropertyData pd1 ON cvmap.UmbracoNodeVersionId = pd1.Version AND pd1.propertyalias = 'title' AND pd1.dataNvarchar IS NOT NULL AND pd1.dataNvarchar != '' LEFT JOIN
600 #UmbPropertyData pd2 ON cvmap.UmbracoNodeVersionId = pd2.Version AND pd2.propertyalias = 'description' LEFT JOIN
601 #UmbPropertyData pd3 ON cvmap.UmbracoNodeVersionId = pd3.Version AND pd3.propertyalias = 'url' LEFT JOIN
602 #UmbPropertyData pd4 ON cvmap.UmbracoNodeVersionId = pd4.Version AND pd4.propertyalias = 'opensInSameTab' LEFT JOIN
603 #UmbPropertyData pd5 ON cvmap.UmbracoNodeVersionId = pd5.Version AND pd5.propertyalias = 'linkType' LEFT JOIN
604 #UmbPropertyData pd6 ON cvmap.UmbracoNodeVersionId = pd6.Version AND pd6.propertyalias = 'icon' LEFT JOIN
605 #UmbPropertyData pd7 ON cvmap.UmbracoNodeVersionId = pd7.Version AND pd7.propertyalias = 'color' LEFT JOIN
606 #UmbPropertyData pd8 ON cvmap.UmbracoNodeVersionId = pd8.Version AND pd8.propertyalias = 'inheritsThemeColor' LEFT JOIN
607 #UmbPropertyData pd9 ON cvmap.UmbracoNodeVersionId = pd9.Version AND pd9.propertyalias = 'inheritsIcon' LEFT JOIN
608 cmsDocument cmsdoc ON cvmap.UmbracoNodeVersionId = cmsdoc.versionId
609 ) updateData
610WHERE ContentVersion.Id = updateData.ColiboContentVersionId
611
612UPDATE ContentVersion
613SET Fields = updateData.xml
614FROM (
615SELECT cvmap.ColiboContentVersionId, (
616 SELECT
617 'String' AS 'title/@t',
618 ISNULL(
619 (
620 CASE WHEN ISNULL(pd4.dataNvarchar, fileVersion.Extension) IS NULL THEN
621 CASE WHEN pd1.dataNvarchar IS NOT NULL AND pd1.dataNvarchar != '' THEN
622 pd1.dataNvarchar
623 ELSE
624 ISNULL(cmsdoc.[text], 'No title')
625 END
626 ELSE
627 CASE WHEN pd1.dataNvarchar IS NOT NULL AND pd1.dataNvarchar != '' THEN
628 (pd1.dataNvarchar + '.' + ISNULL(pd4.dataNvarchar, fileVersion.Extension))
629 ELSE
630 ISNULL((cmsdoc.[text] + '.' + ISNULL(pd4.dataNvarchar, fileVersion.Extension)), 'No title')
631 END
632 END
633
634 ), ISNULL(fileVersion.[FileName], 'file')
635 ) AS title,
636 'String' AS 'description/@t',
637 pd2.dataNtext AS [description],
638 'String' AS 'fileId/@t',
639 fl.FileId AS [fileId]
640 FOR XML PATH(''),
641 Root('set')
642 ) AS XML
643FROM ContentVersionIdMap cvmap JOIN
644 @InsertedContentVersionIds insertedcv ON insertedcv.Id = cvmap.ColiboContentVersionId JOIN
645 ContentVersion ON ContentVersion.Id = cvmap.ColiboContentVersionId JOIN
646 ContentIdMap cidmap ON cidmap.ColiboContentId = ContentVersion.ContentId JOIN
647 #UmbContent umbc ON umbc.NodeId = cidmap.UmbracoNodeId and umbc.ContentType = 'DocumentFile' LEFT JOIN
648 #UmbPropertyData pd1 ON cvmap.UmbracoNodeVersionId = pd1.Version AND pd1.propertyalias = 'title' AND pd1.dataNvarchar IS NOT NULL AND pd1.dataNvarchar != '' LEFT JOIN
649 #UmbPropertyData pd2 ON cvmap.UmbracoNodeVersionId = pd2.Version AND pd2.propertyalias = 'description' LEFT JOIN
650 #UmbPropertyData pd3 ON cvmap.UmbracoNodeVersionId = pd3.Version AND pd3.propertyalias = 'activeVersionId' LEFT JOIN
651 #UmbPropertyData pd4 ON cvmap.UmbracoNodeVersionId = pd4.Version AND pd4.propertyalias = 'extension' AND pd4.dataNvarchar IS NOT NULL AND pd4.dataNvarchar != '' LEFT JOIN
652 @FileLookup fl ON fl.FileVersionStorageId COLLATE DATABASE_DEFAULT = pd3.dataNvarchar COLLATE DATABASE_DEFAULT AND pd3.dataNvarchar IS NOT NULL AND pd3.dataNvarchar != '' LEFT JOIN
653 cmsDocument cmsdoc ON cvmap.UmbracoNodeVersionId = cmsdoc.versionId
654 CROSS APPLY (SELECT TOP 1
655 [FileName],
656 (
657 case when [FileName] like '%.%' then
658 reverse(left(reverse([FileName]), charindex('.', reverse([FileName])) - 1))
659 else
660 NULL
661 end
662 ) Extension
663 FROM mwFileVersion
664 WHERE mwFileVersion.FileId = fl.FileId) fileVersion
665 ) updateData
666WHERE ContentVersion.Id = updateData.ColiboContentVersionId
667
668-- Insert documents -----------------------------------------------------------------------------------------------------------------
669
670INSERT INTO #activeIds
671SELECT c.Id,
672 umbActiveVersion.versionId,
673 cidmapParent.ColiboContentId,
674 (CASE WHEN umbc.ContentType = 'WikiPage' THEN 2 WHEN umbc.ContentType = 'DocumentLink' THEN 3 WHEN umbc.ContentType = 'DocumentFile' THEN 4 ELSE 1 END),
675 c.Created,
676 c.Deleted,
677 c.Updated
678FROM @InsertedContentIds icid JOIN
679 Content c ON c.Id = icid.Id JOIN
680 ContentIdMap cidmap ON cidmap.ColiboContentId = c.Id JOIN
681 #UmbContent umbc ON umbc.NodeId = cidmap.UmbracoNodeId LEFT JOIN
682 ContentIdMap cidmapParent ON cidmapParent.UmbracoNodeId = umbc.ParentNodeId
683CROSS APPLY
684 (
685 SELECT TOP 1 *
686 FROM cmsDocument
687 WHERE cmsDocument.nodeId = cidmap.UmbracoNodeId
688 ORDER BY cmsDocument.published DESC, cmsDocument.updateDate DESC
689 )
690umbActiveVersion
691
692INSERT INTO Document
693 (Id,
694 ParentId,
695 TypeId,
696 Publicity,
697 ReadCount,
698 InheritsParentRoles,
699 LatestRevision,
700 LatestRevisionValidation,
701 RevisioningEnabled,
702 RevisioningPeriodDays,
703 SortOrder,
704 Created,
705 Deleted,
706 Archived,
707 PublishDate)
708SELECT
709 aids.Id,
710 aids.ParentId,
711 aids.TypeId,
712 ISNULL(CONVERT(INT, pd1.dataNvarchar), 0),
713 0,
714 ~CONVERT(BIT,ISNULL(pd2.dataInt, 0)),
715 ISNULL(pd3.dataDate, ISNULL(aids.Updated, aids.Created)) AS lastRevisionDate,
716 pd4.dataDate AS lastRevisionValidationDate,
717 CONVERT(BIT, ISNULL(pd5.dataInt, 0)),
718 pd6.dataInt AS customRevisionPeriodDays,
719 pd7.dataInt AS defaultSortOrder,
720 aids.Created,
721 aids.Deleted,
722 CASE WHEN pd8.dataInt = 2 THEN ISNULL(aids.Updated, aids.Created) ELSE NULL END,
723 CASE WHEN pd8.dataInt = 1 THEN NULL ELSE aids.Created END
724FROM #activeIds aids LEFT JOIN
725 #UmbPropertyData pd1 ON aids.VersionId = pd1.Version AND pd1.propertyalias = 'publicity' LEFT JOIN
726 #UmbPropertyData pd2 ON aids.VersionId = pd2.Version AND pd2.propertyalias = 'blocksContentRoleInheritance' LEFT JOIN
727 #UmbPropertyData pd3 ON aids.VersionId = pd3.Version AND pd3.propertyalias = 'lastRevisionDate' LEFT JOIN
728 #UmbPropertyData pd4 ON aids.VersionId = pd4.Version AND pd4.propertyalias = 'lastRevisionValidationDate' LEFT JOIN
729 #UmbPropertyData pd5 ON aids.VersionId = pd5.Version AND pd5.propertyalias = 'requiresPeriodicRevision' LEFT JOIN
730 #UmbPropertyData pd6 ON aids.VersionId = pd6.Version AND pd6.propertyalias = 'customRevisionPeriodDays' LEFT JOIN
731 #UmbPropertyData pd7 ON aids.VersionId = pd7.Version AND pd7.propertyalias = 'defaultSortOrder' LEFT JOIN
732 #UmbPropertyData pd8 ON aids.VersionId = pd8.Version AND pd8.propertyalias = 'state'
733
734COMMIT;
735
736
737-- CLEAN UP ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
738
739
740IF OBJECT_ID (N'dbo.migration_IntToXmlBoolean', N'FN') IS NOT NULL
741 DROP FUNCTION migration_IntToXmlBoolean;
742GO