public override void Up()
{
Delete.Table("umbracoAppTree");
//NOTE: this is a hack since old umbraco versions might not have had their db's upgraded correctly so they are all quite inconsistent.
// This is actually done in migration: RemoveUmbracoAppConstraints to target 4.9.0 but we've found with some db's that are currently at 4.9.1,
// these upgrades did not run. So, now we not only have to check if these constraints exist, but we also have to check if the RemoveUmbracoAppConstraints
// has executed since we cannot drop the same foreign key twice or we'll get errors.
//Here we'll do a dirty check to see if RemoveUmbracoAppConstraints has executed
if (Context.Expressions.Any(x =>
{
var b = x as MigrationExpressionBase;
if (b == null)
{
return(false);
}
return(b.Name == "FK_umbracoUser2app_umbracoApp");
}) == false)
{
//These are the old aliases, before removing them, check they exist
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("FK_umbracoUser2app_umbracoApp")))
{
Delete.ForeignKey("FK_umbracoUser2app_umbracoApp").OnTable("umbracoUser2app");
}
if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("FK_umbracoUser2app_umbracoUser")))
{
Delete.ForeignKey("FK_umbracoUser2app_umbracoUser").OnTable("umbracoUser2app");
}
}
Delete.Table("umbracoApp");
}
public override void Migrate()
{
var tables = SqlSyntax.GetTablesInSchema(Context.Database).ToList();
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
var columns = SqlSyntax.GetColumnsInSchema(Context.Database).ToArray();
//In some very rare cases, there might alraedy be user group tables that we'll need to remove first
//but of course we don't want to remove the tables we will be creating below if they already exist so
//need to do some checks first since these old rare tables have a different schema
RemoveOldTablesIfExist(tables, columns);
if (AddNewTables(tables))
{
MigrateUserPermissions();
MigrateUserTypesToGroups();
DeleteOldTables(tables, constraints);
SetDefaultIcons();
}
else
{
//if we aren't adding the tables, make sure that the umbracoUserGroup table has the correct FKs - these
//were added after the beta release so we need to do some cleanup
//if the FK doesn't exist
if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUserGroup") &&
x.Item2.InvariantEquals("startContentId") &&
x.Item3.InvariantEquals("FK_startContentId_umbracoNode_id")) == false)
{
//before we add any foreign key we need to make sure there's no stale data in there which would have happened in the beta
//release if a start node was assigned and then that start node was deleted.
Database.Execute(@"UPDATE umbracoUserGroup SET startContentId = NULL WHERE startContentId NOT IN (SELECT id FROM umbracoNode)");
Create.ForeignKey("FK_startContentId_umbracoNode_id")
.FromTable("umbracoUserGroup")
.ForeignColumn("startContentId")
.ToTable("umbracoNode")
.PrimaryColumn("id")
.OnDelete(Rule.None)
.OnUpdate(Rule.None)
.Do();
}
if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUserGroup") &&
x.Item2.InvariantEquals("startMediaId") &&
x.Item3.InvariantEquals("FK_startMediaId_umbracoNode_id")) == false)
{
//before we add any foreign key we need to make sure there's no stale data in there which would have happened in the beta
//release if a start node was assigned and then that start node was deleted.
Database.Execute(@"UPDATE umbracoUserGroup SET startMediaId = NULL WHERE startMediaId NOT IN (SELECT id FROM umbracoNode)");
Create.ForeignKey("FK_startMediaId_umbracoNode_id")
.FromTable("umbracoUserGroup")
.ForeignColumn("startMediaId")
.ToTable("umbracoNode")
.PrimaryColumn("id")
.OnDelete(Rule.None)
.OnUpdate(Rule.None)
.Do();
}
}
}
public override void Up()
{
//This will work on mysql and should work on mssql however the old keys were not named consistently with how the keys are
// structured now. So we need to do a check and manually remove them based on their old aliases.
if (this.Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
Delete.ForeignKey().FromTable("umbracoUser2app").ForeignColumn("app").ToTable("umbracoApp").PrimaryColumn("appAlias");
Delete.ForeignKey().FromTable("umbracoAppTree").ForeignColumn("appAlias").ToTable("umbracoApp").PrimaryColumn("appAlias");
}
else
{
//These are the old aliases, before removing them, check they exist
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("FK_umbracoUser2app_umbracoApp")))
{
Delete.ForeignKey("FK_umbracoUser2app_umbracoApp").OnTable("umbracoUser2app");
//name this migration, this is a hack for DeleteAppTables to ensure it's not executed twice
((MigrationExpressionBase)Context.Expressions.Last()).Name = "FK_umbracoUser2app_umbracoApp";
}
if (constraints.Any(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("FK_umbracoUser2app_umbracoUser")))
{
Delete.ForeignKey("FK_umbracoUser2app_umbracoUser").OnTable("umbracoUser2app");
}
}
}
public override void Up()
{
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
//if the FK doesn't exist
if (constraints.Any(x => x.Item1.InvariantEquals("cmsContent") && x.Item2.InvariantEquals("contentType") && x.Item3.InvariantEquals("FK_cmsContent_cmsContentType_nodeId")) == false)
{
//Before we can add the foreign key there cannot be any orphaned content, media, members
var orphanedIds = Context.Database.Fetch <int>("SELECT contentType FROM cmsContent WHERE NOT EXISTS (SELECT cmsContentType.nodeId FROM cmsContentType WHERE cmsContentType.nodeId = cmsContent.contentType)");
foreach (var orphanedId in orphanedIds)
{
Delete.FromTable("cmsTask").Row(new { nodeId = orphanedId });
Delete.FromTable("umbracoUser2NodeNotify").Row(new { nodeId = orphanedId });
Delete.FromTable("umbracoUser2NodePermission").Row(new { nodeId = orphanedId });
Delete.FromTable("umbracoRelation").Row(new { parentId = orphanedId });
Delete.FromTable("umbracoRelation").Row(new { childId = orphanedId });
Delete.FromTable("cmsTagRelationship").Row(new { nodeId = orphanedId });
Delete.FromTable("umbracoDomains").Row(new { domainRootStructureID = orphanedId });
Delete.FromTable("cmsDocument").Row(new { nodeId = orphanedId });
Delete.FromTable("cmsPropertyData").Row(new { contentNodeId = orphanedId });
Delete.FromTable("cmsMember2MemberGroup").Row(new { Member = orphanedId });
Delete.FromTable("cmsMember").Row(new { nodeId = orphanedId });
Delete.FromTable("cmsPreviewXml").Row(new { nodeId = orphanedId });
Delete.FromTable("cmsContentVersion").Row(new { ContentId = orphanedId });
Delete.FromTable("cmsContentXml").Row(new { nodeId = orphanedId });
Delete.FromTable("cmsContent").Row(new { nodeId = orphanedId });
Delete.FromTable("umbracoNode").Row(new { id = orphanedId });
}
//Some very old schemas don't have an index on the cmsContentType.nodeId column, I'm not actually sure when it was added but
// it is absolutely required to exist in order to add other foreign keys and much better for perf, so we'll need to check it's existence
// this came to light from this issue: http://issues.umbraco.org/issue/U4-4133
var dbIndexes = SqlSyntaxContext.SqlSyntaxProvider.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition()
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsContentType")) == false)
{
Create.Index("IX_cmsContentType").OnTable("cmsContentType").OnColumn("nodeId").Ascending().WithOptions().Unique();
}
if (dbIndexes.Any(x => x.TableName.InvariantEquals("cmsContentType") && x.ColumnName.InvariantEquals("icon")) == false)
{
Create.Index("IX_cmsContentType_icon").OnTable("cmsContentType").OnColumn("icon").Ascending().WithOptions().NonClustered();
}
Create.ForeignKey("FK_cmsContent_cmsContentType_nodeId")
.FromTable("cmsContent")
.ForeignColumn("contentType")
.ToTable("cmsContentType")
.PrimaryColumn("nodeId")
.OnDelete(Rule.None)
.OnUpdate(Rule.None);
}
}
private void Initial()
{
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
//create a new col which we will make a foreign key, but first needs to be populated with data.
Alter.Table("cmsTagRelationship").AddColumn("propertyTypeId").AsInt32().Nullable();
//drop the foreign key on umbracoNode. Must drop foreign key first before primary key can be removed in MySql.
if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
Delete.ForeignKey().FromTable("cmsTagRelationship").ForeignColumn("nodeId").ToTable("umbracoNode").PrimaryColumn("id");
//check for another strange really old one that might have existed
if (constraints.Any(x => x.Item1 == "cmsTagRelationship" && x.Item2 == "tagId"))
{
Delete.ForeignKey().FromTable("cmsTagRelationship").ForeignColumn("tagId").ToTable("cmsTags").PrimaryColumn("id");
}
}
else
{
//Before we try to delete this constraint, we'll see if it exists first, some older schemas never had it and some older schema's had this named
// differently than the default.
var constraintMatches = constraints.Where(x => x.Item1 == "cmsTagRelationship" && x.Item2 == "nodeId" && x.Item3.InvariantStartsWith("PK_") == false);
foreach (var constraint in constraintMatches)
{
Delete.ForeignKey(constraint.Item3).OnTable("cmsTagRelationship");
}
}
//we need to drop the primary key, this is sql specific since MySQL has never had primary keys on this table
// at least since 6.0 and the new installation way but perhaps it had them way back in 4.x so we need to check
// it exists before trying to drop it.
if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
//this will let us know if this pk exists on this table
if (constraints.Count(x => x.Item1.InvariantEquals("cmsTagRelationship") && x.Item3.InvariantEquals("PRIMARY")) > 0)
{
Delete.PrimaryKey("PK_cmsTagRelationship").FromTable("cmsTagRelationship");
}
}
else
{
//lookup the PK by name
var pkName = constraints.FirstOrDefault(x => x.Item1.InvariantEquals("cmsTagRelationship") && x.Item3.InvariantStartsWith("PK_"));
if (pkName != null)
{
Delete.PrimaryKey(pkName.Item3).FromTable("cmsTagRelationship");
}
}
}
public override void Migrate()
{
if (ColumnExists("cmsContentXml", "Rv") == false)
{
Alter.Table("cmsContentXml").AddColumn("Rv").AsInt64().NotNullable().WithDefaultValue(0).Do();
}
if (ColumnExists("cmsPreviewXml", "Rv") == false)
{
Alter.Table("cmsPreviewXml").AddColumn("Rv").AsInt64().NotNullable().WithDefaultValue(0).Do();
}
// remove the any PK_ and the FK_ to cmsContentVersion.VersionId
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
var dups = new List <string>();
foreach (var c in constraints.Where(x => x.Item1.InvariantEquals("cmsPreviewXml") && x.Item3.InvariantStartsWith("PK_")))
{
var keyName = c.Item3.ToLowerInvariant();
if (dups.Contains(keyName))
{
Logger.Warn <RefactorXmlColumns>("Duplicate constraint '{Constraint}'", c.Item3);
continue;
}
dups.Add(keyName);
Delete.PrimaryKey(c.Item3).FromTable(c.Item1).Do();
}
foreach (var c in constraints.Where(x => x.Item1.InvariantEquals("cmsPreviewXml") && x.Item3.InvariantStartsWith("FK_cmsPreviewXml_cmsContentVersion")))
{
Delete.ForeignKey().FromTable("cmsPreviewXml").ForeignColumn("VersionId")
.ToTable("cmsContentVersion").PrimaryColumn("VersionId")
.Do();
}
if (ColumnExists("cmsPreviewXml", "Timestamp"))
{
Delete.Column("Timestamp").FromTable("cmsPreviewXml").Do();
}
if (ColumnExists("cmsPreviewXml", "VersionId"))
{
RemoveDuplicates();
Delete.Column("VersionId").FromTable("cmsPreviewXml").Do();
}
// re-create the primary key
Create.PrimaryKey("PK_cmsPreviewXml")
.OnTable("cmsPreviewXml")
.Columns(new[] { "nodeId" })
.Do();
}
public override void Up()
{
if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
//This should be 3 because this table has 3 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsTagRelationship") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsTagRelationship")
.OnTable("cmsTagRelationship")
.Columns(new[] { "nodeId", "propertyTypeId", "tagId" });
}
}
}
public override void Up()
{
if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsContentType2ContentType") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsContentType2ContentType")
.OnTable("cmsContentType2ContentType")
.Columns(new[] { "parentContentTypeId", "childContentTypeId" });
}
}
}
public override void Up()
{
// don't execute if the column is not there anymore
var columns = SqlSyntax.GetColumnsInSchema(Context.Database).ToArray();
if (columns.Any(x => x.TableName.InvariantEquals("cmsPropertyTypeGroup") && x.ColumnName.InvariantEquals("parentGroupId")) == false)
{
return;
}
//This constraing can be based on old aliases, before removing them, check they exist
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
if (constraints.Any(x => x.Item1.InvariantEquals("cmsPropertyTypeGroup") && x.Item3.InvariantEquals("FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id")))
{
Delete.ForeignKey("FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id").OnTable("cmsPropertyTypeGroup");
}
if (constraints.Any(x => x.Item1.InvariantEquals("cmsPropertyTypeGroup") && x.Item3.InvariantEquals("FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup")))
{
Delete.ForeignKey("FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup").OnTable("cmsPropertyTypeGroup");
}
Delete.Column("parentGroupId").FromTable("cmsPropertyTypeGroup");
}
public override void Up()
{
if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsContentTypeAllowedContentType") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsContentTypeAllowedContentType")
.OnTable("cmsContentTypeAllowedContentType")
.Columns(new[] { "Id", "AllowedId" });
}
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsDocumentType") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsDocumentType")
.OnTable("cmsDocumentType")
.Columns(new[] { "contentTypeNodeId", "templateNodeId" });
}
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsMember2MemberGroup") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsMember2MemberGroup")
.OnTable("cmsMember2MemberGroup")
.Columns(new[] { "Member", "MemberGroup" });
}
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsPreviewXml") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_cmsContentPreviewXml")
.OnTable("cmsPreviewXml")
.Columns(new[] { "nodeId", "versionId" });
}
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("umbracoUser2app") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_user2app")
.OnTable("umbracoUser2app")
.Columns(new[] { "user", "app" });
}
//This should be 2 because this table has 3 keys
if (constraints.Count(x => x.Item1.InvariantEquals("umbracoUser2NodeNotify") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_umbracoUser2NodeNotify")
.OnTable("umbracoUser2NodeNotify")
.Columns(new[] { "userId", "nodeId", "action" });
}
//This should be 2 because this table has 3 keys
if (constraints.Count(x => x.Item1.InvariantEquals("umbracoUser2NodePermission") && x.Item3.InvariantEquals("PRIMARY")) == 0)
{
Create.PrimaryKey("PK_umbracoUser2NodePermission")
.OnTable("umbracoUser2NodePermission")
.Columns(new[] { "userId", "nodeId", "permission" });
}
}
}
public override void Up()
{
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
//if the FK doesn't exist
if (constraints.Any(x => x.Item1.InvariantEquals("cmsLanguageText") && x.Item2.InvariantEquals("languageId") && x.Item3.InvariantEquals("FK_cmsLanguageText_umbracoLanguage_id")) == false)
{
//Somehow, a language text item might end up with a language Id of zero or one that no longer exists
//before we add the foreign key
foreach (var pk in Context.Database.Query <int>(
"SELECT cmsLanguageText.pk FROM cmsLanguageText WHERE cmsLanguageText.languageId NOT IN (SELECT umbracoLanguage.id FROM umbracoLanguage)"))
{
Delete.FromTable("cmsLanguageText").Row(new { pk = pk });
}
var columns = SqlSyntax.GetColumnsInSchema(Context.Database).Distinct().ToArray();
if (columns.Any(x => x.ColumnName.InvariantEquals("id") &&
x.TableName.InvariantEquals("umbracoLanguage") &&
x.DataType.InvariantEquals("smallint")))
{
//Ensure that the umbracoLanguage PK is INT and not SmallInt (which it might be in older db versions)
// In order to 'change' this to an INT, we have to run a full migration script which is super annoying
Create.Table("umbracoLanguage_TEMP")
.WithColumn("id").AsInt32().NotNullable().Identity()
.WithColumn("languageISOCode").AsString(10).Nullable()
.WithColumn("languageCultureName").AsString(50).Nullable();
var currentData = this.Context.Database.Fetch <LanguageDto>(new Sql().Select("*").From <LanguageDto>(SqlSyntax));
foreach (var languageDto in currentData)
{
Insert.IntoTable("umbracoLanguage_TEMP")
.EnableIdentityInsert()
.Row(new { id = languageDto.Id, languageISOCode = languageDto.IsoCode, languageCultureName = languageDto.CultureName });
}
//ok, all data has been copied over, drop the old table, rename the temp table and re-add constraints.
Delete.Table("umbracoLanguage");
Rename.Table("umbracoLanguage_TEMP").To("umbracoLanguage");
//add the pk
Create.PrimaryKey("PK_language").OnTable("umbracoLanguage").Column("id");
}
var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDictionary_id")) == false)
{
Create.Index("IX_cmsDictionary_id").OnTable("cmsDictionary")
.OnColumn("id").Ascending()
.WithOptions().NonClustered()
.WithOptions().Unique();
}
//now we need to create a foreign key
Create.ForeignKey("FK_cmsLanguageText_umbracoLanguage_id").FromTable("cmsLanguageText").ForeignColumn("languageId")
.ToTable("umbracoLanguage").PrimaryColumn("id").OnDeleteOrUpdate(Rule.None);
Alter.Table("cmsDictionary").AlterColumn("parent").AsGuid().Nullable();
//set the parent to null if it equals the default dictionary item root id
foreach (var pk in Context.Database.Query <int>("SELECT pk FROM cmsDictionary WHERE parent NOT IN (SELECT id FROM cmsDictionary)"))
{
Update.Table("cmsDictionary").Set(new { parent = (Guid?)null }).Where(new { pk = pk });
}
Create.ForeignKey("FK_cmsDictionary_cmsDictionary_id").FromTable("cmsDictionary").ForeignColumn("parent")
.ToTable("cmsDictionary").PrimaryColumn("id").OnDeleteOrUpdate(Rule.None);
}
}
public override void Up()
{
//Don't execute anything if there is no 'master' column - this might occur if the db is already upgraded
var cols = SqlSyntax.GetColumnsInSchema(Context.Database).ToArray();
if (cols.Any(x => x.ColumnName.InvariantEquals("master") && x.TableName.InvariantEquals("cmsTemplate")) == false)
{
return;
}
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
//update the parentId column for all templates to be correct so it matches the current 'master' template
//In some old corrupted databases, the information will not be correct in the master column so we need to fix that
//first by nulling out the master column where the id doesn't actually exist
Execute.Sql(@"UPDATE cmsTemplate SET master = NULL WHERE " +
SqlSyntax.GetQuotedColumnName("master") + @" IS NOT NULL AND " +
SqlSyntax.GetQuotedColumnName("master") + @" NOT IN (" +
//Stupid MySQL... needs this stupid syntax because it can do an update with a sub query of itself,
// yet it can do one with a sub sub query
// ... this will work in all dbs too
@"SELECT nodeId FROM (SELECT * FROM cmsTemplate a) b)");
//Now we can bulk update the parentId column
//NOTE: This single statement should be used but stupid SQLCE doesn't support Update with a FROM !!
// so now we have to do this by individual rows :(
//Execute.Sql(@"UPDATE umbracoNode
//SET parentID = COALESCE(t2." + SqlSyntax.GetQuotedColumnName("master") + @", -1)
//FROM umbracoNode t1
//INNER JOIN cmsTemplate t2
//ON t1.id = t2.nodeId");
Execute.Code(database =>
{
var templateData = database.Fetch <dynamic>("SELECT * FROM cmsTemplate");
foreach (var template in templateData)
{
var sql = "SET parentID=@parentId WHERE id=@nodeId";
LogHelper.Info <MigrateAndRemoveTemplateMasterColumn>("Executing sql statement: UPDATE umbracoNode " + sql);
database.Update <NodeDto>(sql,
new { parentId = template.master ?? -1, nodeId = template.nodeId });
}
return(string.Empty);
});
//Now we can update the path, but this needs to be done in a delegate callback so that the query runs after the updates just completed
Execute.Code(database =>
{
//NOTE: we are using dynamic because we need to get the data in a column that no longer exists in the schema
var templates = database.Fetch <dynamic>(new Sql().Select("*").From <TemplateDto>());
foreach (var template in templates)
{
var sql = string.Format(SqlSyntax.UpdateData,
SqlSyntax.GetQuotedTableName("umbracoNode"),
"path=@buildPath",
"id=@nodeId");
LogHelper.Info <MigrateAndRemoveTemplateMasterColumn>("Executing sql statement: " + sql);
//now build the correct path for the template
database.Execute(sql, new
{
buildPath = BuildPath(template, templates),
nodeId = template.nodeId
});
}
return(string.Empty);
});
//now remove the master column and key
if (this.Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
//Because MySQL doesn't name keys with what you want, we need to query for the one that is associated
// this is required for this specific case because there are 2 foreign keys on the cmsTemplate table
var fkName = constraints.FirstOrDefault(x => x.Item1.InvariantEquals("cmsTemplate") && x.Item2.InvariantEquals("master"));
if (fkName != null)
{
Delete.ForeignKey(fkName.Item3).OnTable("cmsTemplate");
}
}
else
{
if (constraints.Any(x => x.Item1.InvariantEquals("cmsTemplate") && x.Item3.InvariantEquals("FK_cmsTemplate_cmsTemplate")))
{
Delete.ForeignKey("FK_cmsTemplate_cmsTemplate").OnTable("cmsTemplate");
}
//TODO: Hopefully it's not named something else silly in some crazy old versions
}
var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition()
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
//in some databases there's an index (IX_Master) on the master column which needs to be dropped first
var foundIndex = dbIndexes.FirstOrDefault(x => x.TableName.InvariantEquals("cmsTemplate") && x.ColumnName.InvariantEquals("master"));
if (foundIndex != null)
{
Delete.Index(foundIndex.IndexName).OnTable("cmsTemplate");
}
if (cols.Any(x => x.ColumnName.InvariantEquals("master") && x.TableName.InvariantEquals("cmsTemplate")))
{
Delete.Column("master").FromTable("cmsTemplate");
}
}
public override void Up()
{
//now that the controlId column is renamed and now a string we need to convert
if (Context == null || Context.Database == null)
{
return;
}
//var cpt = SqlSyntaxContext.SqlSyntaxProvider.GetConstraintsPerTable(Context.Database);
//var di = SqlSyntaxContext.SqlSyntaxProvider.GetDefinedIndexes(Context.Database);
if (Context.CurrentDatabaseProvider != DatabaseProviders.SqlServer)
{
Delete.DefaultConstraint().OnTable("cmsMacroProperty").OnColumn("macroPropertyHidden");
}
else
{
//If we are on SQLServer, we need to delete default constraints by name, older versions of umbraco did not name these default constraints
// consistently so we need to look up the constraint name to delete, this only pertains to SQL Server and this issue:
// http://issues.umbraco.org/issue/U4-4133
var sqlServerSyntaxProvider = new SqlServerSyntaxProvider();
var defaultConstraints = sqlServerSyntaxProvider.GetDefaultConstraintsPerColumn(Context.Database).Distinct();
//lookup the constraint we want to delete, normally would be called "DF_cmsMacroProperty_macroPropertyHidden" but
// we cannot be sure with really old versions
var constraint = defaultConstraints
.SingleOrDefault(x => x.Item1 == "cmsMacroProperty" && x.Item2 == "macroPropertyHidden");
if (constraint != null)
{
Execute.Sql(string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]", "cmsMacroProperty", constraint.Item3));
}
}
Delete.Column("macroPropertyHidden").FromTable("cmsMacroProperty");
if (Context.CurrentDatabaseProvider == DatabaseProviders.MySql)
{
Delete.ForeignKey().FromTable("cmsMacroProperty").ForeignColumn("macroPropertyType").ToTable("cmsMacroPropertyType").PrimaryColumn("id");
}
else
{
//Before we try to delete this constraint, we'll see if it exists first, some older schemas never had it and some older schema's had this named
// differently than the default.
var keyConstraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct();
var constraint = keyConstraints
.SingleOrDefault(x => x.Item1 == "cmsMacroProperty" && x.Item2 == "macroPropertyType" && x.Item3.InvariantStartsWith("PK_") == false);
if (constraint != null)
{
Delete.ForeignKey(constraint.Item3).OnTable("cmsMacroProperty");
}
}
Alter.Table("cmsMacroProperty").AddColumn("editorAlias").AsString(255).NotNullable().WithDefaultValue("");
//we need to get the data and create the migration scripts before we change the actual schema bits below!
var list = Context.Database.Fetch <dynamic>("SELECT * FROM cmsMacroPropertyType");
foreach (var item in list)
{
var alias = item.macroPropertyTypeAlias;
//check if there's a map created
var newAlias = (string)LegacyParameterEditorAliasConverter.GetNewAliasFromLegacyAlias(alias);
if (newAlias.IsNullOrWhiteSpace() == false)
{
alias = newAlias;
}
//update the table with the alias, the current macroPropertyType will contain the original id
Update.Table("cmsMacroProperty").Set(new { editorAlias = alias }).Where(new { macroPropertyType = item.id });
}
//drop the column now
Delete.Column("macroPropertyType").FromTable("cmsMacroProperty");
//drop the default constraint
Delete.DefaultConstraint().OnTable("cmsMacroProperty").OnColumn("editorAlias");
}
public override void Up()
{
//Some very old schemas don't have an index on the cmsContent.nodeId column, I'm not actually sure when it was added but
// it is absolutely required to exist in order to have it as a foreign key reference, so we'll need to check it's existence
// this came to light from this issue: http://issues.umbraco.org/issue/U4-4133
var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition(x)).ToArray();
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsContent")) == false)
{
Create.Index("IX_cmsContent").OnTable("cmsContent").OnColumn("nodeId").Ascending().WithOptions().Unique();
}
if (Context.CurrentDatabaseProvider == DatabaseProviders.SqlServer ||
Context.CurrentDatabaseProvider == DatabaseProviders.SqlServerCE)
{
var constraints = SqlSyntax.GetConstraintsPerColumn(Context.Database).Distinct().ToArray();
//This should be 2 because this table has 2 keys
if (constraints.Count(x => x.Item1.InvariantEquals("cmsPreviewXml") && x.Item3.InvariantStartsWith("PK_")) == 0)
{
Create.PrimaryKey("PK_cmsContentPreviewXml")
.OnTable("cmsPreviewXml")
.Columns(new[] { "nodeId", "versionId" });
}
if (constraints.Count(x => x.Item1.InvariantEquals("cmsTags") && x.Item3.InvariantStartsWith("PK_")) == 0)
{
Create.PrimaryKey("PK_cmsTags")
.OnTable("cmsTags")
.Columns(new[] { "id" });
}
if (constraints.Count(x => x.Item1.InvariantEquals("cmsStylesheetProperty") && x.Item3.InvariantStartsWith("PK_")) == 0)
{
Create.PrimaryKey("PK_cmsStylesheetProperty")
.OnTable("cmsStylesheetProperty")
.Columns(new[] { "nodeId" });
}
if (constraints.Count(x => x.Item1.InvariantEquals("cmsStylesheet") && x.Item3.InvariantStartsWith("PK_")) == 0)
{
Create.PrimaryKey("PK_cmsStylesheet")
.OnTable("cmsStylesheet")
.Columns(new[] { "nodeId" });
Create.ForeignKey("FK_cmsStylesheet_umbracoNode_id").FromTable("cmsStylesheet").ForeignColumn("nodeId")
.ToTable("umbracoNode").PrimaryColumn("id").OnDeleteOrUpdate(Rule.None);
}
if (constraints.Count(x => x.Item1.InvariantEquals("cmsMember") && x.Item3.InvariantStartsWith("PK_")) == 0)
{
Create.PrimaryKey("PK_cmsMember")
.OnTable("cmsMember")
.Columns(new[] { "nodeId" });
Create.ForeignKey("FK_cmsMember_umbracoNode_id").FromTable("cmsMember").ForeignColumn("nodeId")
.ToTable("umbracoNode").PrimaryColumn("id").OnDeleteOrUpdate(Rule.None);
Create.ForeignKey("FK_cmsMember_cmsContent_nodeId").FromTable("cmsMember").ForeignColumn("nodeId")
.ToTable("cmsContent").PrimaryColumn("nodeId").OnDeleteOrUpdate(Rule.None);
}
}
}