Skip to content

Instantly share code, notes, and snippets.

@jamiepollock
Last active January 22, 2018 16:49
Show Gist options
  • Save jamiepollock/def5902199dd07058de056f47e16d197 to your computer and use it in GitHub Desktop.
Save jamiepollock/def5902199dd07058de056f47e16d197 to your computer and use it in GitHub Desktop.
A layer on top of Umbraco's MigrationBase for checking a table has Indexes, Constraints & Columns
using System.Linq;
using Umbraco.Core.Logging;
using Umbraco.Core.Persistence.Migrations;
using Umbraco.Core.Persistence.SqlSyntax;
namespace My.Website.Migrations
{
public abstract class CustomMigrationBase : MigrationBase
{
public CustomMigrationBase(ISqlSyntaxProvider sqlSyntax, ILogger logger) : base(sqlSyntax, logger)
{
}
protected bool CheckTableAlreadyHasIndex(string tableName, string indexName)
{
var indexes = SqlSyntax.GetDefinedIndexes(Context.Database);
/*
* Returns
*
* Tuple<string, string, string, bool>
*
* Item1: table name
* Item2: index name
* Item3: column name
* Item4: unique
*
* A multi-column index has multiple records returned from this function
*
*/
return indexes.Any(x =>
string.Equals(x.Item1, tableName) &&
string.Equals(x.Item2, indexName)
);
}
protected bool CheckTableAlreadyHasColumn(string tableName, string columnName)
{
var columns = SqlSyntax.GetColumnsInSchema(Context.Database);
var doesColumnExist =
columns.Any(
x =>
string.Equals(x.TableName, tableName) &&
string.Equals(x.ColumnName, columnName)
);
return doesColumnExist;
}
protected bool CheckTableAlreadyHasConstraint(string tableName, string constraintName)
{
var constraints = SqlSyntax.GetConstraintsPerTable(Context.Database);
return constraints.Any(x => string.Equals(tableName, x.Item1)
&& string.Equals(constraintName, x.Item2));
}
}
}
using SmartCentre.Web.Umbraco.Core.Extensions;
using System;
using Umbraco.Core.Logging;
using Umbraco.Core.Persistence.Migrations;
using Umbraco.Core.Persistence.SqlSyntax;
namespace My.Website.Migrations
{
[Migration("1.0.0", 0, "ProductsName")]
public class UsageOfCustomMigrationBase : CustomMigrationBase
{
public UsersMigration(ISqlSyntaxProvider sqlSyntax, ILogger logger) : base(sqlSyntax, logger)
{
}
public override void Down()
{
throw new NotImplementedException();
}
public override void Up()
{
AddColumns();
AddIndexes();
}
private void AddIndexes()
{
if (CheckTableAlreadyHasIndex("tableName", "indexName"))
{
// Log you're skipping this
}
else
{
Create.Index("indexName").OnTable("tableName").OnColumn("columnName").Ascending()
.WithOptions().NonClustered()
.WithOptions().Unique();
}
}
private void AddColumns()
{
if (CheckTableAlreadyHasColumn("tableName", "columnName"))
{
// Log skipping column
}
else
{
Alter.Table("tableName").AddColumn("columnName").AsInt32().Nullable();
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment