public override void Up()
{
var dbIndexes = _forTesting ? new DbIndexDefinition[] { } : SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition(x)).ToArray();
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsMacro_Alias")) == false)
{
//in order to create this index, we need to ensure that there are no duplicates. This could have happened with very old/corrupt umbraco versions.
// So we'll remove any duplicates based on alias and only keep the one with the smallest id since I'm pretty sure we'd always choose the 'first' one
// when running a query.
if (_forTesting == false)
{
//NOTE: Using full SQL statement here in case the DTO has changed between versions
var macros = Context.Database.Fetch <MacroDto>("SELECT * FROM cmsMacro")
.GroupBy(x => x.Alias)
.Where(x => x.Count() > 1);
foreach (var m in macros)
{
//get the min id (to keep)
var minId = m.Min(x => x.Id);
//delete all the others
foreach (var macroDto in m.Where(x => x.Id != minId))
{
Delete.FromTable("cmsMacro").Row(new { id = macroDto.Id });
}
}
}
Create.Index("IX_cmsMacro_Alias").OnTable("cmsMacro").OnColumn("macroAlias").Unique();
}
}
public override void Migrate()
{
//Clear all stylesheet data if the tables exist
//tuple = tablename, indexname, columnname, unique
var indexes = SqlSyntax.GetDefinedIndexes(Context.Database).ToArray();
var found = indexes.FirstOrDefault(
x => x.Item1.InvariantEquals("cmsPropertyData") &&
x.Item2.InvariantEquals("IX_cmsPropertyData_1")
//we're searching for the old index which is not unique
&& x.Item4 == false);
if (found != null)
{
Database.Execute("DELETE FROM cmsPropertyData WHERE id NOT IN (SELECT MIN(id) FROM cmsPropertyData GROUP BY nodeId, versionId, propertytypeid HAVING MIN(id) IS NOT NULL)");
//we need to re create this index
Delete.Index("IX_cmsPropertyData_1").OnTable("cmsPropertyData").Do();
Create.Index("IX_cmsPropertyData_1").OnTable("cmsPropertyData")
.OnColumn("nodeId").Ascending()
.OnColumn("versionId").Ascending()
.OnColumn("propertytypeid").Ascending()
.WithOptions().NonClustered()
.WithOptions().Unique()
.Do();
}
}
public override void Up()
{
var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition(x)).ToArray();
//add a foreign key to the parent id column too!
//In some cases in very old corrupted db's this will fail, so it means we need to clean the data first
//set the parentID to NULL where it doesn't actually exist in the normal ids
Execute.Sql(@"UPDATE cmsTags SET parentId = NULL WHERE parentId IS NOT NULL AND parentId NOT IN (SELECT id FROM cmsTags)");
Create.ForeignKey("FK_cmsTags_cmsTags")
.FromTable("cmsTags")
.ForeignColumn("ParentId")
.ToTable("cmsTags")
.PrimaryColumn("id")
.OnDelete(Rule.None)
.OnUpdate(Rule.None);
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsTags")) == false)
{
//add an index to tag/group since it's queried often
Create.Index("IX_cmsTags").OnTable("cmsTags").OnColumn("tag").Ascending().OnColumn("group").Ascending().WithOptions().NonClustered();
}
}
//see: http://issues.umbraco.org/issue/U4-6188, http://issues.umbraco.org/issue/U4-6187
public override void Up()
{
var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition()
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
//must be non-nullable
Alter.Column("uniqueID").OnTable("umbracoNode").AsGuid().NotNullable();
//make sure it already exists
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoNodeUniqueID")))
{
Delete.Index("IX_umbracoNodeUniqueID").OnTable("umbracoNode");
}
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoNode_uniqueID")) == false)
{
//must be a uniqe index
Create.Index("IX_umbracoNode_uniqueID").OnTable("umbracoNode").OnColumn("uniqueID").Unique();
}
}
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 = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition(x)).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);
}
}
public override void Down()
{
var indexes = SqlSyntax.GetDefinedIndexes(Context.Database).ToArray();
var found = indexes.FirstOrDefault(
x => x.Item1.InvariantEquals("someDataTable") &&
x.Item2.InvariantContains("IX_someDataTable_Name"));
if (found != null)
{
Delete.Index("IX_someDataTable_Name").OnTable("SomeDataTable");
}
}
public override void Up()
{
//tuple = tablename, indexname, columnname, unique
var indexes = SqlSyntax.GetDefinedIndexes(Context.Database).ToArray();
var found = indexes.FirstOrDefault(
x => x.Item1.InvariantEquals("cmsPropertyData") &&
x.Item2.InvariantEquals("IX_cmsPropertyData"));
if (found != null)
{
//drop the index
Delete.Index("IX_cmsPropertyData").OnTable("cmsPropertyData");
}
}
public override void Up()
{
var dbIndexes = _skipIndexCheck ? new DbIndexDefinition[] {} : SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition(x)).ToArray();
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsMacroProperty_Alias")) == false)
{
Create.Index("IX_cmsMacroProperty_Alias").OnTable("cmsMacroProperty")
.OnColumn("macro")
.Ascending()
.OnColumn("macroPropertyAlias")
.Unique();
}
}
public override void Up()
{
var dbIndexes = _skipIndexCheck ? new DbIndexDefinition[] { } : 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_umbracoNodeUniqueID")) == false)
{
Create.Index("IX_umbracoNodeUniqueID").OnTable("umbracoNode").OnColumn("uniqueID").Ascending().WithOptions().NonClustered();
}
}
public override void Up()
{
var indexes = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition(x)).ToArray();
// drop the index if it exists
if (indexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoNodeUniqueID")))
{
Delete.Index("IX_umbracoNodeUniqueID").OnTable("umbracoNode");
}
// set uniqueID to be non-nullable
// the index *must* be dropped else 'one or more objects access this column' exception
Alter.Table("umbracoNode").AlterColumn("uniqueID").AsGuid().NotNullable();
// create the index
Create.Index("IX_umbracoNode_uniqueID").OnTable("umbracoNode").OnColumn("uniqueID").Unique();
}
public override void Up()
{
Logger.Info <AddIndexes>("1.0.0: Running Migration");
var indexes = SqlSyntax.GetDefinedIndexes(Context.Database).ToArray();
var found = indexes.FirstOrDefault(
x => x.Item1.InvariantEquals("someDataTable") &&
x.Item2.InvariantContains("IX_someDataTable_Name"));
if (found == null)
{
Create.Index("IX_someDataTable_Name")
.OnTable("SomeDataTable")
.OnColumn("name").Ascending()
.WithOptions().NonClustered();
}
}
/// <summary>
/// Adds the indexes to the merchInvoice table.
/// </summary>
public override void Up()
{
var dbIndexes = SqlSyntax.GetDefinedIndexes(ApplicationContext.Current.DatabaseContext.Database)
.Select(x => new DbIndexDefinition
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
if (dbIndexes == null)
{
throw new NullReferenceException();
}
CreateIndex(dbIndexes, "IX_merchInvoiceBillToPostalCode", "billToPostalCode");
}
public override void Migrate()
{
//Clear all stylesheet data if the tables exist
//tuple = tablename, indexname, columnname, unique
var indexes = SqlSyntax.GetDefinedIndexes(Context.Database).ToArray();
var found = indexes.FirstOrDefault(
x => x.Item1.InvariantEquals("cmsPropertyData") &&
x.Item2.InvariantEquals("IX_cmsPropertyData_1")
//we're searching for the old index which is not unique
&& x.Item4 == false);
if (found != null)
{
//Check for MySQL
if (DatabaseType.IsMySql())
{
//Use the special double nested sub query for MySQL since that is the only
//way delete sub queries works
var delPropQry = SqlSyntax.GetDeleteSubquery(
"cmsPropertyData",
"id",
Sql("SELECT MIN(id) FROM cmsPropertyData GROUP BY nodeId, versionId, propertytypeid HAVING MIN(id) IS NOT NULL"),
WhereInType.NotIn);
Database.Execute(delPropQry.SQL);
}
else
{
//NOTE: Even though the above will work for MSSQL, we are not going to execute the
// nested delete sub query logic since it will be slower and there could be a ton of property
// data here so needs to be as fast as possible.
Database.Execute("DELETE FROM cmsPropertyData WHERE id NOT IN (SELECT MIN(id) FROM cmsPropertyData GROUP BY nodeId, versionId, propertytypeid HAVING MIN(id) IS NOT NULL)");
}
//we need to re create this index
Delete.Index("IX_cmsPropertyData_1").OnTable("cmsPropertyData").Do();
Create.Index("IX_cmsPropertyData_1").OnTable("cmsPropertyData")
.OnColumn("nodeId").Ascending()
.OnColumn("versionId").Ascending()
.OnColumn("propertytypeid").Ascending()
.WithOptions().NonClustered()
.WithOptions().Unique()
.Do();
}
}
/// <summary>
/// Adds the unique index to the SKU
/// </summary>
public override void Up()
{
var dbIndexes = SqlSyntax.GetDefinedIndexes(ApplicationContext.Current.DatabaseContext.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_merchProductVariantSku")) == false)
{
Logger.Info(typeof(AddInvoiceCurrencyCodeColumn), "Adding unique nonclustered index to sku column on merchProductVariant table.");
Create.Index("IX_merchProductVariantSku").OnTable("merchProductVariant").OnColumn("sku").Unique();
}
}
public override void Up()
{
Execute.Code(database =>
{
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
// Some people seem to have a constraint in their DB instead of an index, we'd need to drop that one
// See: https://our.umbraco.com/forum/using-umbraco-and-getting-started/93282-upgrade-from-711-to-712-fails
var constraints = SqlSyntax.GetConstraintsPerTable(database).Distinct().ToArray();
if (constraints.Any(x => x.Item2.InvariantEquals("IX_umbracoLanguage_languageISOCode")))
{
localContext.Delete.UniqueConstraint("IX_umbracoLanguage_languageISOCode").FromTable("umbracoLanguage");
return(localContext.GetSql());
}
return(null);
});
Execute.Code(database =>
{
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
//Now check for indexes of that name and drop that if it exists
var dbIndexes = SqlSyntax.GetDefinedIndexes(database)
.Select(x => new DbIndexDefinition(x)).ToArray();
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoLanguage_languageISOCode")))
{
localContext.Delete.Index("IX_umbracoLanguage_languageISOCode").OnTable("umbracoLanguage");
return(localContext.GetSql());
}
return(null);
});
Alter.Table("umbracoLanguage")
.AlterColumn("languageISOCode")
.AsString(14)
.Nullable();
Create.Index("IX_umbracoLanguage_languageISOCode")
.OnTable("umbracoLanguage")
.OnColumn("languageISOCode")
.Ascending()
.WithOptions()
.Unique();
}
/// <summary>
/// Adds the indexes to the merchProductVariant table.
/// </summary>
public override void Up()
{
var dbIndexes = SqlSyntax.GetDefinedIndexes(ApplicationContext.Current.DatabaseContext.Database)
.Select(x => new DbIndexDefinition
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
if (dbIndexes == null)
{
throw new NullReferenceException();
}
CreateIndex(dbIndexes, "IX_merchProductVariantName", "name");
CreateIndex(dbIndexes, "IX_merchProductVariantPrice", "price");
CreateIndex(dbIndexes, "IX_merchProductVariantSalePrice", "salePrice");
CreateIndex(dbIndexes, "IX_merchProductVariantBarcode", "barcode");
CreateIndex(dbIndexes, "IX_merchProductVariantManufacturer", "manufacturer");
}
public override void Up()
{
var dbIndexes = SqlSyntax.GetDefinedIndexes(Context.Database)
.Select(x => new DbIndexDefinition()
{
TableName = x.Item1,
IndexName = x.Item2,
ColumnName = x.Item3,
IsUnique = x.Item4
}).ToArray();
//do not create any indexes if they already exist in the database
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoNodeTrashed")) == false)
{
Create.Index("IX_umbracoNodeTrashed").OnTable("umbracoNode").OnColumn("trashed").Ascending().WithOptions().NonClustered();
}
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsContentVersion_ContentId")) == false)
{
Create.Index("IX_cmsContentVersion_ContentId").OnTable("cmsContentVersion").OnColumn("ContentId").Ascending().WithOptions().NonClustered();
}
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDocument_published")) == false)
{
Create.Index("IX_cmsDocument_published").OnTable("cmsDocument").OnColumn("published").Ascending().WithOptions().NonClustered();
}
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDocument_newest")) == false)
{
Create.Index("IX_cmsDocument_newest").OnTable("cmsDocument").OnColumn("newest").Ascending().WithOptions().NonClustered();
}
//We need to do this for SQL Azure V2 since it does not let you drop any clustered indexes
// Issue: http://issues.umbraco.org/issue/U4-5673
if (Context.CurrentDatabaseProvider == DatabaseProviders.SqlServer || Context.CurrentDatabaseProvider == DatabaseProviders.SqlAzure)
{
var version = Context.Database.ExecuteScalar <string>("SELECT @@@@VERSION");
if (version.Contains("Microsoft SQL Azure"))
{
var parts = version.Split(new[] { '-' }, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToArray();
if (parts.Length > 1)
{
if (parts[1].StartsWith("11."))
{
//we want to drop the umbracoUserLogins_Index index since it is named incorrectly and then re-create it so
// it follows the standard naming convention
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("umbracoUserLogins_Index")))
{
//It's the old version that doesn't support dropping a clustered index on a table, so we need to do some manual work.
ExecuteSqlAzureSqlForChangingIndex();
}
return;
}
}
}
}
//we want to drop the umbracoUserLogins_Index index since it is named incorrectly and then re-create it so
// it follows the standard naming convention
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("umbracoUserLogins_Index")))
{
Delete.Index("umbracoUserLogins_Index").OnTable("umbracoUserLogins");
}
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoUserLogins_Index")) == false)
{
Create.Index("IX_umbracoUserLogins_Index").OnTable("umbracoUserLogins").OnColumn("contextID").Ascending().WithOptions().Clustered();
}
}
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);
}
}
}
protected bool IndexExists(string indexName)
{
var indexes = SqlSyntax.GetDefinedIndexes(Context.Database);
return(indexes.Any(x => x.Item2.InvariantEquals(indexName)));
}
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()
{
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);
}
}
