public override string[] GenerateSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string createTable;
string head = $"CREATE TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]";
var optimizableMigrations = _definitions
.SelectMany(m => graph.PullPrerequisitesForward(m, this, CanOptimize))
.ToImmutableList();
if (optimizableMigrations.Any())
{
var definitions = optimizableMigrations
.OfType <TableDefinitionMigration>()
.Select(d => d.GenerateDefinitionSql());
createTable = $"{head}({string.Join(",", definitions)})";
}
else
{
createTable = head;
}
string[] sql =
{
createTable
};
migrationsAffected.AppendAll(optimizableMigrations);
return(sql);
}
public SchemaSpecification UseSchema(string schemaName)
{
var migration = new UseSchemaMigration(_databaseName, schemaName, Prerequisites);
MigrationHistoryBuilder.Append(migration);
migration.AddToParent();
return(new SchemaSpecification(migration, MigrationHistoryBuilder));
}
public CustomSqlSpecification Execute(string up, string down = null)
{
var migration = new CustomSqlMigration(_databaseName, up, down, Prerequisites);
MigrationHistoryBuilder.Append(migration);
migration.AddToParent();
return(new CustomSqlSpecification(migration, MigrationHistoryBuilder));
}
public TableSpecification CreateTable(string tableName)
{
var migration = new CreateTableMigration(_migration, tableName, Prerequisites);
MigrationHistoryBuilder.Append(migration);
migration.AddToParent();
return(new TableSpecification(migration, MigrationHistoryBuilder));
}
public override string[] GenerateRollbackSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string[] sql =
{
$"ALTER TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]\r\n DROP CONSTRAINT [PK_{TableName}]"
};
return(sql);
}
public override string[] GenerateRollbackSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string[] sql =
{
$@"ALTER TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]
DROP COLUMN [{ColumnName}]"
};
return(sql);
}
public override string[] GenerateRollbackSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string[] sql =
{
$"DROP TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]"
};
migrationsAffected.AppendAll(_definitions);
return(sql);
}
public override string[] GenerateSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string columnNames = string.Join(", ", _columns.Select(c => $"[{c.ColumnName}]").ToArray());
string[] sql =
{
$"ALTER TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]\r\n ADD CONSTRAINT [PK_{TableName}] PRIMARY KEY CLUSTERED ({columnNames})"
};
return(sql);
}
public IndexSpecification CreateIndex(params ColumnSpecification[] columns)
{
var childMigration = new CreateIndexMigration(
_migration,
columns.Select(c => c.Migration),
Prerequisites);
MigrationHistoryBuilder.Append(childMigration);
childMigration.AddToParent();
return(new IndexSpecification(childMigration, MigrationHistoryBuilder));
}
public override string[] GenerateRollbackSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string indexTail = string.Join("_", Columns.Select(c => $"{c.ColumnName}").ToArray());
string[] sql =
{
$"ALTER TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]\r\n DROP CONSTRAINT [FK_{TableName}_{indexTail}]"
};
return(sql);
}
public override string[] GenerateSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string indexTail = string.Join("_", Columns.Select(c => $"{c.ColumnName}").ToArray());
string columnList = string.Join(", ", Columns.Select(c => $"[{c.ColumnName}]").ToArray());
string[] sql =
{
$"CREATE NONCLUSTERED INDEX [IX_{TableName}_{indexTail}] ON [{DatabaseName}].[{SchemaName}].[{TableName}] ({columnList})"
};
return(sql);
}
private ColumnSpecification CreateColumn(string columnName, string typeDescriptor, bool nullable)
{
var childMigration = new CreateColumnMigration(
_migration,
columnName,
typeDescriptor,
nullable,
Prerequisites);
MigrationHistoryBuilder.Append(childMigration);
childMigration.AddToParent();
return(new ColumnSpecification(childMigration, MigrationHistoryBuilder));
}
public void AddMigration(Migration migration)
{
var migrationsAffected = new MigrationHistoryBuilder();
migrationsAffected.Append(migration);
string[] rollbackSql = migration.GenerateRollbackSql(migrationsAffected, this);
var mementos = migrationsAffected.MigrationHistory.GetMementos().ToList();
string[] deleteStatements = GenerateDeleteStatements(_databaseName, mementos);
_sql = _sql.InsertRange(0, deleteStatements);
_sql = _sql.InsertRange(0, rollbackSql);
_ahead = _ahead.Subtract(migrationsAffected.MigrationHistory);
}
public ForeignKeySpecification CreateForeignKey(PrimaryKeySpecification referencing, bool cascadeDelete = false, bool cascadeUpdate = false)
{
var childMigration = new CreateForeignKeyMigration(
_migration,
referencing.Migration,
cascadeDelete,
cascadeUpdate,
Prerequisites);
MigrationHistoryBuilder.Append(childMigration);
childMigration.AddToParent();
return(new ForeignKeySpecification(MigrationHistoryBuilder));
}
public override string[] GenerateSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string indexTail = string.Join("_", Columns.Select(c => $"{c.ColumnName}").ToArray());
string columnList = string.Join(", ", Columns.Select(c => $"[{c.ColumnName}]").ToArray());
string referenceColumnList = string.Join(", ", _referencing.Columns.Select(c => $"[{c.ColumnName}]").ToArray());
string onDelete = _cascadeDelete ? " ON DELETE CASCADE" : "";
string onUpdate = _cascadeUpdate ? " ON UPDATE CASCADE" : "";
string[] sql =
{
$@"ALTER TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]
ADD CONSTRAINT [FK_{TableName}_{indexTail}] FOREIGN KEY ({columnList})
REFERENCES [{_referencing.DatabaseName}].[{_referencing.SchemaName}].[{_referencing.TableName}] ({referenceColumnList}){onDelete}{onUpdate}"
};
return(sql);
}
public override string[] GenerateSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string[] identityTypes = { "INT IDENTITY" };
string[] numericTypes = { "BIGINT", "INT", "SMALLINT", "TINYINT", "MONEY", "SMALLMONEY", "DECIMAL", "FLOAT", "REAL" };
string[] dateTypes = { "DATETIME", "SMALLDATETIME", "DATETIME2", "TIME" };
string[] dateTimeOffsetTypes = { "DATETIMEOFFSET" };
string[] stringTypes = { "NVARCHAR", "NCHAR", "NTEXT" };
string[] asciiStringTypes = { "VARCHAR", "CHAR", "TEXT" };
string[] guidTypes = { "UNIQUEIDENTIFIER" };
string defaultExpression =
_nullable ? null :
identityTypes.Any(t => TypeDescriptor.StartsWith(t)) ? null :
numericTypes.Any(t => TypeDescriptor.StartsWith(t)) ? "0" :
dateTypes.Any(t => TypeDescriptor.StartsWith(t)) ? "GETUTCDATE()" :
dateTimeOffsetTypes.Any(t => TypeDescriptor.StartsWith(t)) ? "SYSDATETIMEOFFSET()" :
stringTypes.Any(t => TypeDescriptor.StartsWith(t)) ? "N''" :
asciiStringTypes.Any(t => TypeDescriptor.StartsWith(t)) ? "''" :
guidTypes.Any(t => TypeDescriptor.StartsWith(t)) ? "'00000000-0000-0000-0000-000000000000'" :
null;
if (defaultExpression == null)
{
string[] sql =
{
$@"ALTER TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]
ADD [{ColumnName}] {TypeDescriptor} {NullableClause}"
};
return(sql);
}
else
{
string[] sql =
{
$@"ALTER TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]
ADD [{ColumnName}] {TypeDescriptor} {NullableClause}
CONSTRAINT [DF_{TableName}_{ColumnName}] DEFAULT ({defaultExpression})",
$@"ALTER TABLE [{DatabaseName}].[{SchemaName}].[{TableName}]
DROP CONSTRAINT [DF_{TableName}_{ColumnName}]",
};
return(sql);
}
}
public bool AddMigration(Migration migration)
{
if (_working.Contains(migration))
{
return(false);
}
foreach (var prerequisite in migration.AllPrerequisites
.Where(p => _difference.Contains(p)))
{
if (!AddMigration(prerequisite))
{
return(false);
}
}
_working = _working.Push(migration);
var migrationsAffected = new MigrationHistoryBuilder();
migrationsAffected.Append(migration);
string[] result = migration.GenerateSql(migrationsAffected, this);
_sql = _sql.AddRange(result);
var mementos = migrationsAffected.MigrationHistory.GetMementos().ToList();
_sql = _sql.Add(GenerateInsertStatement(_databaseName, mementos));
if (mementos.SelectMany(m => m.Prerequisites).SelectMany(p => p.Value).Any())
{
_sql = _sql.Add(GeneratePrerequisiteInsertStatements(_databaseName, mementos));
}
_difference = _difference.Subtract(migrationsAffected.MigrationHistory);
_working = _working.Pop();
return(true);
}
internal ForeignKeySpecification(MigrationHistoryBuilder migrationHistoryBuilder) :
base(migrationHistoryBuilder)
{
}
internal IndexSpecification(CreateIndexMigration migration, MigrationHistoryBuilder migrationHistoryBuilder) :
base(migrationHistoryBuilder)
{
_migration = migration;
}
internal ColumnSpecification(CreateColumnMigration migration, MigrationHistoryBuilder migrationHistoryBuilder) :
base(migrationHistoryBuilder)
{
_migration = migration;
}
internal TableSpecification(CreateTableMigration migration, MigrationHistoryBuilder migrationHistoryBuilder) :
base(migrationHistoryBuilder)
{
_migration = migration;
}
private DatabaseSpecification(string databaseName, MigrationHistoryBuilder migrationHistoryBuilder, ImmutableList <Migration> prerequisites) :
base(migrationHistoryBuilder, prerequisites)
{
_databaseName = databaseName;
}
public override string[] GenerateRollbackSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
throw new NotImplementedException();
}
public override string[] GenerateSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
string[] sql = { };
return(sql);
}
internal PrimaryKeySpecification(CreatePrimaryKeyMigration migration, MigrationHistoryBuilder migrationHistoryBuilder) :
base(migrationHistoryBuilder)
{
_migration = migration;
}
private TableSpecification(CreateTableMigration migration, MigrationHistoryBuilder migrationHistoryBuilder, ImmutableList <Migration> prerequisites) :
base(migrationHistoryBuilder, prerequisites)
{
_migration = migration;
}
public override string[] GenerateSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
return(new string[] { $"USE {DatabaseName}", _up });
}
internal CustomSqlSpecification(CustomSqlMigration migration, MigrationHistoryBuilder migrationHistoryBuilder) :
base(migrationHistoryBuilder)
{
_migration = migration;
}
public override string[] GenerateRollbackSql(MigrationHistoryBuilder migrationsAffected, IGraphVisitor graph)
{
return
(_down != null ? new string[] { $"USE {DatabaseName}", _down } :
new string[] { });
}
internal SchemaSpecification(UseSchemaMigration migration, MigrationHistoryBuilder migrationHistoryBuilder) :
base(migrationHistoryBuilder)
{
_migration = migration;
}