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); } } }