public override void Migrate()
{
//Now we need to check if we can actually do this because we won't be able to if there's data in there that is too long
var database = Database;
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(database);
var colLen = SqlSyntax is MySqlSyntaxProvider
? database.ExecuteScalar <int?>(string.Format("select max(LENGTH({0})) from cmsDictionary", SqlSyntax.GetQuotedColumnName("key")))
: database.ExecuteScalar <int?>(string.Format("select max(datalength({0})) from cmsDictionary", SqlSyntax.GetQuotedColumnName("key")));
if (colLen < 900 == false)
{
return;
}
//if it exists we need to drop it first
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDictionary_key")))
{
Delete.Index("IX_cmsDictionary_key").OnTable("cmsDictionary").Do();
}
//we can apply the col length change
Alter.Table("cmsDictionary")
.AlterColumn("key")
.AsString(450)
.NotNullable()
.Do();
}
public override void Up()
{
//Now we need to check if we can actually d6 this because we won't be able to if there's data in there that is too long
//http://issues.umbraco.org/issue/U4-9758
Execute.Code(database =>
{
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(database);
var colLen = (SqlSyntax is MySqlSyntaxProvider)
? database.ExecuteScalar <int?>("select max(LENGTH(LoginName)) from cmsMember")
: database.ExecuteScalar <int?>("select max(datalength(LoginName)) from cmsMember");
if (colLen < 900 == false)
{
return(null);
}
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
//if it exists we need to drop it first
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsMember_LoginName")))
{
localContext.Delete.Index("IX_cmsMember_LoginName").OnTable("cmsMember");
}
//we can apply the col length change
localContext.Alter.Table("cmsMember")
.AlterColumn("LoginName")
.AsString(225)
.NotNullable();
return(localContext.GetSql());
});
}
public override void Up()
{
//Now we need to check if we can actually do this because we won't be able to if there's data in there that is too long
Execute.Code(database =>
{
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(database);
var colLen = (SqlSyntax is MySqlSyntaxProvider)
? database.ExecuteScalar <int?>(string.Format("select max(LENGTH({0})) from cmsDictionary", SqlSyntax.GetQuotedColumnName("key")))
: database.ExecuteScalar <int?>(string.Format("select max(datalength({0})) from cmsDictionary", SqlSyntax.GetQuotedColumnName("key")));
if (colLen < 900 == false)
{
return(null);
}
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
//if it exists we need to drop it first
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDictionary_key")))
{
localContext.Delete.Index("IX_cmsDictionary_key").OnTable("cmsDictionary");
}
//we can apply the col length change
localContext.Alter.Table("cmsDictionary")
.AlterColumn("key")
.AsString(450)
.NotNullable();
return(localContext.GetSql());
});
}
public override void Migrate()
{
var database = Database;
//Now we need to check if we can actually do this because we won't be able to if there's data in there that is too long
var colLen = database.ExecuteScalar <int?>(string.Format("select max(datalength({0})) from cmsDictionary", SqlSyntax.GetQuotedColumnName("key")));
if (colLen < 900 == false && colLen != null)
{
return;
}
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(Context.Database);
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDictionary_key")) == false)
{
//we can apply the index
Create.Index("IX_cmsDictionary_key").OnTable("cmsDictionary")
.OnColumn("key")
.Ascending()
.WithOptions()
.NonClustered()
.Do();
}
}
public override void Migrate()
{
//Now we need to check if we can actually d6 this because we won't be able to if there's data in there that is too long
//http://issues.umbraco.org/issue/U4-9758
var database = Database;
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(database);
var colLen = database.ExecuteScalar <int?>("select max(datalength(LoginName)) from cmsMember");
if (colLen < 900 == false)
{
return;
}
//if an index exists on this table we need to drop it. Normally we'd check via index name but in some odd cases (i.e. Our)
//the index name is something odd (starts with "mi_"). In any case, the index cannot exist if we want to alter the column
//so we'll drop whatever index is there and add one with the correct name after.
var loginNameIndex = dbIndexes.FirstOrDefault(x => x.TableName.InvariantEquals("cmsMember") && x.ColumnName.InvariantEquals("LoginName"));
if (loginNameIndex != null)
{
Delete.Index(loginNameIndex.IndexName).OnTable("cmsMember").Do();
}
//we can apply the col length change
Alter.Table("cmsMember")
.AlterColumn("LoginName")
.AsString(225)
.NotNullable()
.Do();
}
public override void Up()
{
Execute.Code(database =>
{
//Now we need to check if we can actually do this because we won't be able to if there's data in there that is too long
var colLen = (SqlSyntax is MySqlSyntaxProvider)
? database.ExecuteScalar <int?>(string.Format("select max(LENGTH({0})) from cmsDictionary", SqlSyntax.GetQuotedColumnName("key")))
: database.ExecuteScalar <int?>(string.Format("select max(datalength({0})) from cmsDictionary", SqlSyntax.GetQuotedColumnName("key")));
if (colLen < 900 == false && colLen != null)
{
return(null);
}
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(Context.Database);
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsDictionary_key")) == false)
{
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
//we can apply the index
localContext.Create.Index("IX_cmsDictionary_key").OnTable("cmsDictionary")
.OnColumn("key")
.Ascending()
.WithOptions()
.NonClustered();
return(localContext.GetSql());
}
return(null);
});
}
public override void Migrate()
{
var database = Database;
//Now we need to check if we can actually d6 this because we won't be able to if there's data in there that is too long
//http://issues.umbraco.org/issue/U4-9758
var colLen = database.ExecuteScalar <int?>("select max(datalength(LoginName)) from cmsMember");
if (colLen < 900 == false && colLen != null)
{
return;
}
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(Context.Database);
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsMember_LoginName")) == false)
{
//we can apply the index
Create.Index("IX_cmsMember_LoginName").OnTable("cmsMember")
.OnColumn("LoginName")
.Ascending()
.WithOptions()
.NonClustered()
.Do();
}
}
public override void Migrate()
{
// 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(Context.Database).Distinct().ToArray();
if (constraints.Any(x => x.Item2.InvariantEquals("IX_umbracoLanguage_languageISOCode")))
{
Delete.UniqueConstraint("IX_umbracoLanguage_languageISOCode").FromTable("umbracoLanguage").Do();
}
//Now check for indexes of that name and drop that if it exists
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(Context.Database);
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoLanguage_languageISOCode")))
{
Delete.Index("IX_umbracoLanguage_languageISOCode").OnTable("umbracoLanguage").Do();
}
Alter.Table("umbracoLanguage")
.AlterColumn("languageISOCode")
.AsString(14)
.Nullable()
.Do();
Create.Index("IX_umbracoLanguage_languageISOCode")
.OnTable("umbracoLanguage")
.OnColumn("languageISOCode")
.Ascending()
.WithOptions()
.Unique()
.Do();
}
public override void Up()
{
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(Context.Database);
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoNodePath")) == false)
{
Create.Index("IX_umbracoNodePath").OnTable("umbracoNode")
.OnColumn("path")
.Ascending()
.WithOptions()
.NonClustered();
}
}
public override void Migrate()
{
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(Context.Database);
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsPropertyTypeAlias")) == false)
{
//we can apply the index
Create.Index("IX_cmsPropertyTypeAlias").OnTable(Constants.DatabaseSchema.Tables.PropertyType)
.OnColumn("Alias")
.Ascending().WithOptions().NonClustered()
.Do();
}
}
public override void Up()
{
Execute.Code(database =>
{
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(database);
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_cmsPropertyTypeAlias")) == false)
{
var localContext = new LocalMigrationContext(Context.CurrentDatabaseProvider, database, SqlSyntax, Logger);
//we can apply the index
localContext.Create.Index("IX_cmsPropertyTypeAlias").OnTable("cmsPropertyType")
.OnColumn("Alias")
.Ascending()
.WithOptions()
.NonClustered();
return(localContext.GetSql());
}
return(null);
});
}
public override void Migrate()
{
var dbIndexes = SqlSyntax.GetDefinedIndexesDefinitions(Context.Database).ToArray();
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoRelation_parentChildType")) == false)
{
//This will remove any corrupt/duplicate data in the relation table before the index is applied
//Ensure this executes in a deferred block which will be done inside of the migration transaction
var database = Database;
//We need to check if this index has corrupted data and then clear that data
var duplicates = database.Fetch <dynamic>("SELECT parentId,childId,relType FROM umbracoRelation GROUP BY parentId,childId,relType HAVING COUNT(*) > 1");
if (duplicates.Count > 0)
{
//need to fix this there cannot be duplicates so we'll take the latest entries, it's really not going to matter though
foreach (var duplicate in duplicates)
{
var ids = database.Fetch <int>("SELECT id FROM umbracoRelation WHERE parentId=@parentId AND childId=@childId AND relType=@relType ORDER BY datetime DESC",
new { parentId = duplicate.parentId, childId = duplicate.childId, relType = duplicate.relType });
if (ids.Count == 1)
{
//this is just a safety check, this should absolutely never happen
throw new InvalidOperationException("Duplicates were detected but could not be discovered");
}
//delete the others
ids = ids.Skip(0).ToList();
//iterate in groups of 2000 to avoid the max sql parameter limit
foreach (var idGroup in ids.InGroupsOf(2000))
{
database.Execute("DELETE FROM umbracoRelation WHERE id IN (@ids)", new { ids = idGroup });
}
}
}
//unique index to prevent duplicates - and for better perf
Create.Index("IX_umbracoRelation_parentChildType").OnTable("umbracoRelation")
.OnColumn("parentId").Ascending()
.OnColumn("childId").Ascending()
.OnColumn("relType").Ascending()
.WithOptions()
.Unique()
.Do();
}
//need indexes on alias and name for relation type since these are queried against
//make sure it doesn't already exist
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoRelationType_alias")) == false)
{
Create.Index("IX_umbracoRelationType_alias").OnTable("umbracoRelationType")
.OnColumn("alias")
.Ascending()
.WithOptions()
.NonClustered()
.Do();
}
if (dbIndexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoRelationType_name")) == false)
{
Create.Index("IX_umbracoRelationType_name").OnTable("umbracoRelationType")
.OnColumn("name")
.Ascending()
.WithOptions()
.NonClustered()
.Do();
}
}
