Skip to content

Instantly share code, notes, and snippets.

@mrflo

mrflo/UIDMigrator.cs

Last active September 5, 2022 19:06
Show Gist options
  • Save mrflo/283bc3285bdf438ca682e219d478c33f to your computer and use it in GitHub Desktop.
Save mrflo/283bc3285bdf438ca682e219d478c33f to your computer and use it in GitHub Desktop.
Download ZIP
Umbraco Migrate Media Picker ID to UdI including OP10 pickers. Two methods to be called on startup event. One is to migrate MediaPicker from classic Document Type and the other one is to update all nested content sub properties.
Raw
UIDMigrator.cs
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using Umbraco.Core;
using Umbraco.Core.Logging;
using Umbraco.Web;
namespace Migrator
{
public static class UIDMigrator
{
private class Row
{
public int id { get; set; }
public int contentNodeId { get; set; }
public string alias { get; set; }
public string dataNvarchar { get; set; }
public string dataNtext { get; set; }
public int? dataInt { get; set; }
public string preValue { get; set; }
}
public static void MigrateIdsToUdisInNestedContent(ApplicationContext applicationContext)
{
var database = applicationContext.DatabaseContext.Database;
// Custom properties: UPDATE all documentType properties used in nested content
var allProperties = new string[] { "images", "bilder", "bild", "pDFDatei", "previewBild", "logo" };
string sql = @"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, preValue=cmsDataTypePreValues.value, dbo.cmsDocument.*
FROM dbo.cmsPropertyData
JOIN dbo.cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN dbo.cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN dbo.cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId
JOIN dbo.cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN dbo.umbracoNode ON umbracoNode.id = dbo.cmsContentVersion.ContentId
JOIN dbo.cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Umbraco.NestedContent')
AND cmsDataTypePreValues.alias='contentTypes'
AND (dbo.cmsDocument.published=1 OR dbo.cmsDocument.newest=1 OR dbo.cmsDocument.updateDate > (SELECT updateDate FROM dbo.cmsDocument innerDoc WHERE innerDoc.nodeId = dbo.cmsDocument.nodeId AND innerDoc.published=1 AND newest=1))
ORDER BY contentNodeId, dbo.cmsDataType.propertyEditorAlias";
var nestedContentDataToMigrate = database.Query<Row>(sql).ToList();
if (nestedContentDataToMigrate.Any())
{
foreach (var propertyData in nestedContentDataToMigrate)
{
int[] ids;
if (propertyData.dataNtext != null)
{
// deserialise nested content
var nestedElement = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(propertyData.dataNtext);
if (nestedElement == null) continue;
// Search for each property ids
bool isToEdit = false;
int index = 0;
foreach (var json in nestedElement)
{
foreach (var propertyId in allProperties)
{
if (json.ContainsKey(propertyId) && json[propertyId] != null)
{
var strValue = json[propertyId].ToString();
if (!string.IsNullOrEmpty(strValue))
{
if (!strValue.StartsWith("umb://"))
{
var uniqueIds = database.Query<Guid>($"SELECT uniqueId FROM umbracoNode WHERE id IN ({strValue})").ToArray();
var uniqueIdsCsv = string.Join(",", uniqueIds.Select(id => $"umb://media/{id:N}"));
//update json with new Uid value
nestedElement[index][propertyId] = uniqueIdsCsv;
LogHelper.Info(typeof(UIDMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {strValue} to {uniqueIdsCsv}");
isToEdit = true;
}
}
}
}
index++;
}
if (isToEdit)
{
//Updte the DB record with the updated serialized json
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", JsonConvert.SerializeObject(nestedElement), propertyData.id);
LogHelper.Info(typeof(UIDMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) saving property {propertyData.alias}");
}
}
else
{
LogHelper.Info(typeof(UIDMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataInt and null dataNvarchar");
continue;
}
}
LogHelper.Info(typeof(UIDMigrator), () => $"MigrateIdsToUdis: republishing all nodes to update xml cache (equivalent to /umbraco/dialogs/republish.aspx?xml=true)");
var contentService = ApplicationContext.Current.Services.ContentService;
contentService.RePublishAll();
umbraco.library.RefreshContent();
LogHelper.Info(typeof(UIDMigrator), () => $"MigrateIdsToUdis: republishing complete");
}
}
public static void MigrateIdsToUdis(ApplicationContext applicationContext)
{
var database = applicationContext.DatabaseContext.Database;
string sql = @"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, preValue=cmsDataTypePreValues.value, dbo.cmsDocument.*
FROM dbo.cmsPropertyData
JOIN dbo.cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN dbo.cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN dbo.cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'startNodeId'
JOIN dbo.cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN dbo.umbracoNode ON umbracoNode.id = dbo.cmsContentVersion.ContentId
JOIN dbo.cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('OP10.MultipleMediaPicker','Umbraco.MediaPicker2')
AND (dataNvarchar IS NOT NULL OR dataInt IS NOT NULL)
AND (dbo.cmsDocument.published=1 OR dbo.cmsDocument.newest=1 OR dbo.cmsDocument.updateDate > (SELECT updateDate FROM dbo.cmsDocument innerDoc WHERE innerDoc.nodeId = dbo.cmsDocument.nodeId AND innerDoc.published=1 AND newest=1))
ORDER BY contentNodeId, dbo.cmsDataType.propertyEditorAlias";
var treePickerDataToMigrate = database.Query<Row>(sql).ToList();
if (treePickerDataToMigrate.Any())
{
foreach (var propertyData in treePickerDataToMigrate)
{
int[] ids;
if (propertyData.dataInt != null)
{
ids = new[] { propertyData.dataInt.Value };
}
else if (propertyData.dataNvarchar != null)
{
ids = propertyData.dataNvarchar.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray();
}
else
{
LogHelper.Info(typeof(UIDMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataInt and null dataNvarchar");
continue;
}
string csv = string.Join(",", ids);
var type = "media";
Guid[] uniqueIds = null;
string uniqueIdsCsv = string.Empty;
if (ids.Any())
{
uniqueIds = database.Query<Guid>($"SELECT uniqueId FROM umbracoNode WHERE id IN ({csv})").ToArray();
uniqueIdsCsv = string.Join(",", uniqueIds.Select(id => $"umb://{type}/{id:N}"));
}
LogHelper.Info(typeof(UIDMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {csv} to {uniqueIdsCsv}");
database.Execute("UPDATE cmsPropertyData SET dataInt=NULL, dataNvarchar=NULL, dataNtext=@0 WHERE id=@1", uniqueIdsCsv, propertyData.id);
}
LogHelper.Info(typeof(UIDMigrator), () => $"MigrateIdsToUdis: republishing all nodes to update xml cache (equivalent to /umbraco/dialogs/republish.aspx?xml=true)");
var contentService = ApplicationContext.Current.Services.ContentService;
contentService.RePublishAll();
umbraco.library.RefreshContent();
LogHelper.Info(typeof(UIDMigrator), () => $"MigrateIdsToUdis: republishing complete");
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment