private void AppendCreateIndexes()
{
sb.AppendLine("--##SECTION BEGIN [CREATE INDEXES]");
sb.AppendLine();
//The index list holds all NON-PK indexes
foreach (var table in _model.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
//DO NOT process primary keys
foreach (var index in table.TableIndexList.Where(x => !x.PrimaryKey))
{
sb.Append(SQLEmit.GetSQLCreateIndex(table, index, true));
sb.AppendLine("--GO");
sb.AppendLine();
}
}
sb.AppendLine("--##SECTION END [CREATE INDEXES]");
sb.AppendLine();
sb.AppendLine("--##SECTION BEGIN [TENANT INDEXES]");
sb.AppendLine();
//Create indexes for Tenant fields
foreach (var table in _model.Database.Tables.Where(x => x.IsTenant).OrderBy(x => x.Name))
{
sb.Append(SQLEmit.GetSqlTenantIndex(_model, table));
}
sb.AppendLine("--##SECTION END [TENANT INDEXES]");
sb.AppendLine();
}
private void AppendCreateDefaults()
{
//These should all be included in the create script so if minimizing scripts just omit these
if (!_model.EmitSafetyScripts)
{
return;
}
sb.AppendLine("--##SECTION BEGIN [CREATE DEFAULTS]");
sb.AppendLine();
foreach (var table in _model.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
//Add Defaults
var tempsb = new StringBuilder();
foreach (var column in table.GetColumns())
{
var defaultText = SQLEmit.AppendColumnDefaultCreateSQL(column, false);
if (!string.IsNullOrEmpty(defaultText))
{
tempsb.Append(defaultText);
}
}
if (tempsb.ToString() != string.Empty)
{
sb.AppendLine($"--BEGIN DEFAULTS FOR TABLE [{table.DatabaseName}]");
sb.Append(tempsb.ToString());
sb.AppendLine($"--END DEFAULTS FOR TABLE [{table.DatabaseName}]");
sb.AppendLine("--GO");
sb.AppendLine();
}
}
sb.AppendLine("--##SECTION END [CREATE DEFAULTS]");
sb.AppendLine();
}
public void AppendFullTemplate()
{
try
{
if (!_useSingleFile)
{
sb.AppendLine("--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.");
sb.AppendLine();
}
sb.AppendLine("--This SQL is generated for the model defined stored procedure [" + _currentStoredProcedure.DatabaseName + "]");
sb.AppendLine();
nHydrate.Generator.GenerationHelper.AppendCopyrightInSQL(sb, _model);
sb.Append(SQLEmit.GetSQLCreateStoredProc(_currentStoredProcedure, true));
//if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
//{
// _grantSB.AppendFormat("GRANT EXECUTE ON [" + _currentStoredProcedure.GetSQLSchema() + "].[{0}] TO [{1}]", _currentStoredProcedure.GetDatabaseObjectName(), _model.Database.GrantExecUser).AppendLine();
// _grantSB.AppendLine("--MODELID: " + _currentStoredProcedure.Key);
// _grantSB.AppendLine("GO");
// _grantSB.AppendLine();
//}
}
catch (Exception ex)
{
throw;
}
}
private void GenerateContent()
{
try
{
sb.AppendLine("--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.");
sb.AppendLine("--Static Data");
sb.AppendLine();
//Turn OFF CONSTRAINTS
//sb.AppendLine("if (SERVERPROPERTY('EngineEdition') <> 5) --NOT AZURE");
//sb.AppendLine("exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'");
//sb.AppendLine();
#region Add Static Data
foreach (var table in _model.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
sb.Append(SQLEmit.GetSqlInsertStaticData(table));
}
#endregion
//Turn ON CONSTRAINTS
//sb.AppendLine("if (SERVERPROPERTY('EngineEdition') <> 5) --NOT AZURE");
//sb.AppendLine("exec sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'");
//sb.AppendLine();
}
catch (Exception ex)
{
throw;
}
}
private void GenerateContent()
{
try
{
sb = new StringBuilder();
sb.AppendLine("--Generated Unversioned Upgrade");
sb.AppendLine("--Generated on " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sb.AppendLine();
sb.AppendLine("--UNCOMMENT TO DROP ALL DEFAULTS IF NEEDED. IF THIS MODEL WAS IMPORTED FROM AN EXISTSING DATABASE THE MODEL WILL RECREATE ALL DEFAULTS WITH A GENERATED NAME.");
sb.AppendLine("--DROP ALL DEFAULTS");
sb.AppendLine("--DECLARE @SqlCmd varchar(4000); SET @SqlCmd = ''");
sb.AppendLine("--DECLARE @Cnt int; SET @Cnt = 0");
sb.AppendLine("--select @Cnt = count(*) from sys.objects d");
sb.AppendLine("--join sys.objects o on d.parent_object_id = o.object_id");
sb.AppendLine("--where d.type = 'D'");
sb.AppendLine(" ");
sb.AppendLine("--WHILE @Cnt > 0");
sb.AppendLine("--BEGIN");
sb.AppendLine("-- select TOP 1 @SqlCmd = 'ALTER TABLE ' + o.name + ' DROP CONSTRAINT ' + d.name");
sb.AppendLine("-- from sys.objects d");
sb.AppendLine("-- join sys.objects o on d.parent_object_id = o.object_id");
sb.AppendLine("-- where d.type = 'D'");
sb.AppendLine("-- EXEC(@SqlCmd) --SELECT @SqlCmd --view the command only");
sb.AppendLine("-- select @Cnt = count(*) from sys.objects d");
sb.AppendLine("-- join sys.objects o on d.parent_object_id = o.object_id");
sb.AppendLine("-- where d.type = 'D'");
sb.AppendLine("--END");
sb.AppendLine("--GO");
sb.AppendLine();
//If the indexes have a name on import then rename it
sb.AppendLine("--RENAME OLD INDEXES FROM THE IMPORT DATABASE IF NEEDED");
sb.AppendLine();
foreach (var table in _model.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
foreach (var index in table.TableIndexList)
{
if (!string.IsNullOrEmpty(index.ImportedName))
{
var indexName = SQLEmit.GetIndexName(table, index);
if (index.ImportedName != indexName)
{
sb.AppendLine($"if exists(select * from sys.tables where name = '{table.DatabaseName}')");
sb.AppendLine("BEGIN");
sb.AppendLine($"if exists(select * from sys.indexes where name = '{index.ImportedName}')");
sb.AppendLine($"EXEC sp_rename N'[{table.GetPostgresSchema()}].[{table.DatabaseName}].[{index.ImportedName}]', N'{indexName}', N'INDEX';");
sb.AppendLine("END");
sb.AppendLine("--GO");
sb.AppendLine();
}
}
}
}
}
catch (Exception ex)
{
throw;
}
}
private void AppendCreatePrimaryKey()
{
if (_model.EmitSafetyScripts)
{
sb.AppendLine("--##SECTION BEGIN [RENAME PK]");
sb.AppendLine();
//Rename existing PK if they exist
sb.AppendLine("--RENAME EXISTING PRIMARY KEYS IF NECESSARY");
foreach (var table in _model.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
sb.AppendLine("DECLARE @pkfix" + table.PascalName + " varchar(500)");
sb.AppendLine("SET @pkfix" + table.PascalName + " = (SELECT top 1 i.name AS IndexName FROM sys.indexes AS i WHERE i.is_primary_key = 1 AND OBJECT_NAME(i.OBJECT_ID) = '" + table.DatabaseName + "')");
sb.AppendLine("if @pkfix" + table.PascalName + " <> '' and (BINARY_CHECKSUM(@pkfix" + table.PascalName + ") <> BINARY_CHECKSUM('PK_" + table.DatabaseName.ToUpper() + "')) exec('sp_rename '''+@pkfix" + table.PascalName + "+''', ''PK_" + table.DatabaseName.ToUpper() + "''')");
}
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("--##SECTION END [RENAME PK]");
sb.AppendLine();
sb.AppendLine("--##SECTION BEGIN [CREATE PK]");
sb.AppendLine();
//Create PK
foreach (var table in _model.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
sb.Append(SQLEmit.GetSqlCreatePK(table));
sb.AppendLine("GO");
}
sb.AppendLine("--##SECTION END [CREATE PK]");
sb.AppendLine();
}
}
private void AppendCreateTable()
{
//Emit each create table statement
foreach (var table in _model.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
sb.AppendLine(SQLEmit.GetSQLCreateTable(_model, table));
sb.AppendLine("--GO");
sb.AppendLine();
}
}
private void AppendFullTemplate(StringBuilder sb)
{
try
{
sb.Append(SQLEmit.GetSqlTenantView(_model, _table, _grantSB));
}
catch (Exception ex)
{
throw;
}
}
private void AppendCreateForeignKey()
{
foreach (var table in _model.Database.Tables.Where(x => x.Generated && x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
var tableName = Globals.GetTableDatabaseName(_model, table);
var childRoleRelations = table.ChildRoleRelations;
if (childRoleRelations.Count > 0)
{
foreach (var relation in childRoleRelations.Where(x => x.Enforce))
{
sb.Append(SQLEmit.GetSqlAddFK(relation));
sb.AppendLine("GO");
sb.AppendLine();
}
}
}
}
private void AppendCreatePrimaryKey()
{
if (_model.EmitSafetyScripts)
{
//TODO: Rename existing PK if they exist
sb.AppendLine("--##SECTION BEGIN [CREATE PK]");
sb.AppendLine();
//Create PK
foreach (var table in _model.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
sb.Append(SQLEmit.GetSqlCreatePK(table));
sb.AppendLine("--GO");
}
sb.AppendLine("--##SECTION END [CREATE PK]");
sb.AppendLine();
}
}
private void AppendAuditTables()
{
sb.AppendLine("--##SECTION BEGIN [AUDIT TABLES PK]");
sb.AppendLine();
foreach (var table in _model.Database.Tables.Where(x => x.Generated && x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
//If there is an audit table then make its surrogate key PK clustered
if (table.AllowAuditTracking)
{
sb.Append(SQLEmit.GetSqlCreateAuditPK(table));
}
else if (_model.EmitSafetyScripts)
{
sb.Append(SQLEmit.GetSqlDropAuditPK(table));
}
}
sb.AppendLine("--##SECTION END [AUDIT TABLES PK]");
sb.AppendLine();
}
private void AppendAll()
{
sb.AppendLine("--##SECTION BEGIN [RELATIONS]");
sb.AppendLine();
foreach (var table in _model.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
var childRoleRelations = table.ChildRoleRelations;
if (childRoleRelations.Count > 0)
{
foreach (var relation in childRoleRelations.Where(x => x.Enforce))
{
sb.Append(SQLEmit.GetSqlAddFK(relation));
sb.AppendLine("--GO");
sb.AppendLine();
}
}
}
sb.AppendLine("--##SECTION END [RELATIONS]");
sb.AppendLine();
}
public static string GetModelDifferenceSql(ModelRoot modelOld, ModelRoot modelNew)
{
var sb = new StringBuilder();
#region Loop and Add tables
foreach (var newT in modelNew.Database.Tables.Where(x => !x.IsEnumOnly()).OrderBy(x => x.Name).ToList())
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => !x.IsEnumOnly());
if (oldT == null)
{
//Add table, indexes
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSQLCreateTable(modelNew, newT));
sb.AppendLine("GO");
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlCreatePK(newT));
sb.AppendLine("GO");
//DO NOT process primary keys
foreach (var index in newT.TableIndexList.Where(x => !x.PrimaryKey))
{
sb.Append(nHydrate.Core.SQLGeneration.SQLEmit.GetSQLCreateIndex(newT, index, false));
sb.AppendLine("GO");
sb.AppendLine();
}
if (newT.StaticData.Count > 0)
{
sb.Append(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlInsertStaticData(newT));
sb.AppendLine("GO");
sb.AppendLine();
}
}
}
#endregion
#region Delete Indexes
foreach (var newT in modelNew.Database.Tables.Where(x => !x.IsEnumOnly()).OrderBy(x => x.Name).ToList())
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => !x.IsEnumOnly());
if (oldT != null)
{
//If old exists new does NOT, so delete index
foreach (var oldIndex in oldT.TableIndexList)
{
var newIndex = newT.TableIndexList.FirstOrDefault(x => x.CorePropertiesHashNoNames == oldIndex.CorePropertiesHashNoNames);
if (newIndex == null)
{
sb.AppendLine(SQLEmit.GetSQLDropIndex(newT, oldIndex));
sb.AppendLine("GO");
}
}
//Both exist, so if different, drop and re-create
foreach (var newIndex in newT.TableIndexList)
{
var oldIndex = oldT.TableIndexList.FirstOrDefault(x => x.CorePropertiesHashNoNames == newIndex.CorePropertiesHashNoNames);
if (oldIndex != null && oldIndex.CorePropertiesHashNoNames != newIndex.CorePropertiesHashNoNames)
{
sb.AppendLine(SQLEmit.GetSQLDropIndex(newT, oldIndex));
sb.AppendLine("GO");
}
}
}
}
#endregion
#region Loop and DELETE tables
foreach (var oldT in modelOld.Database.Tables.Where(x => !x.IsEnumOnly()))
{
var newT = modelNew.Database.Tables.FirstOrDefault(x => !x.IsEnumOnly() && x.Is(oldT));
if (newT == null)
{
//DELETE TABLE
sb.Append(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlDropTable(modelOld, oldT));
sb.AppendLine("GO");
//TODO - Delete Tenant View
sb.AppendLine();
}
//else if (tList[0].DatabaseName != oldT.DatabaseName)
//{
// //RENAME TABLE
// sb.AppendLine("if exists(select * from sys.objects where name = '" + oldT.DatabaseName + "' and type = 'U')");
// sb.AppendLine("exec sp_rename [" + oldT.DatabaseName + "], [" + tList[0].DatabaseName + "]");
//}
}
#endregion
#region Loop and Modify tables
foreach (var newT in modelNew.Database.Tables.Where(x => !x.IsEnumOnly()).OrderBy(x => x.Name).ToList())
{
var schemaChanged = false;
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => !x.IsEnumOnly());
if (oldT != null)
{
#region Rename table if need be
if (oldT.DatabaseName != newT.DatabaseName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameTable(oldT, newT));
sb.AppendLine("GO");
}
#endregion
#region Add columns
foreach (var newC in newT.GetColumns())
{
var oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
if (oldC == null)
{
//ADD COLUMN
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlAddColumn(newC));
sb.AppendLine("GO");
sb.AppendLine();
schemaChanged = true;
}
//else if (newC.DatabaseName != oldC.DatabaseName)
//{
// //RENAME COLUMN
// sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSQLRenameColumn(oldC, newC));
// sb.AppendLine("GO");
// sb.AppendLine();
//}
}
#endregion
#region Delete Columns
foreach (var oldC in oldT.GetColumns())
{
var newC = Globals.GetColumnByKey(newT.Columns, oldC.Key);
if (newC == null)
{
//DELETE COLUMN
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlDropColumn(modelNew, oldC));
sb.AppendLine("GO");
sb.AppendLine();
schemaChanged = true;
}
else if (newC.DatabaseName != oldC.DatabaseName)
{
////RENAME COLUMN
//string sql = "if exists (select * from sys.columns c inner join sys.objects o on c.object_id = o.object_id where c.name = '" + oldC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" +
// "AND not exists (select * from sys.columns c inner join sys.objects o on c.object_id = o.object_id where c.name = '" + newC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" + Environment.NewLine +
// "EXEC sp_rename @objname = '" + newT.DatabaseName + "." + oldC.DatabaseName + "', @newname = '" + newC.DatabaseName + "', @objtype = 'COLUMN'";
//if (!querylist.Contains(sql))
//{
// querylist.Add(sql);
// sb.AppendLine(sql);
// sb.AppendLine("GO");
// sb.AppendLine();
//}
}
}
#endregion
#region Modify Columns
foreach (var newC in newT.GetColumns())
{
var oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
if (oldC != null)
{
var document = new XmlDocument();
document.LoadXml("<a></a>");
var n1 = XmlHelper.AddElement(document.DocumentElement, "q");
var n2 = XmlHelper.AddElement(document.DocumentElement, "q");
oldC.XmlAppend(n1);
newC.XmlAppend(n2);
//Check column, ignore defaults
if (newC.CorePropertiesHashNoPK != oldC.CorePropertiesHashNoPK)
{
//MODIFY COLUMN
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlModifyColumn(oldC, newC));
sb.AppendLine("GO");
sb.AppendLine();
schemaChanged = true;
}
//Drop add defaults if column
//if ((newC.CorePropertiesHashNoPK != oldC.CorePropertiesHashNoPK) || (oldC.Default != newC.Default))
//{
// if (!string.IsNullOrEmpty(oldC.Default))
// {
// //Old default was something so drop it
// sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlDropColumnDefault(newC));
// sb.AppendLine("GO");
// sb.AppendLine();
// }
// if (!string.IsNullOrEmpty(newC.Default))
// {
// //New default is something so add it
// sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlCreateColumnDefault(modelNew, newC));
// sb.AppendLine("GO");
// sb.AppendLine();
// }
//}
if (!string.IsNullOrEmpty(newC.Default) && ((oldC.Default != newC.Default) || (oldC.DataType != newC.DataType) || (oldC.DatabaseName != newC.DatabaseName)))
{
//New default is something so add it
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlCreateColumnDefault(modelNew, newC));
sb.AppendLine("GO");
sb.AppendLine();
}
}
}
#endregion
#region Tenant
if (oldT.IsTenant && !newT.IsTenant)
{
//Drop default
var defaultName = "DF__" + newT.DatabaseName.ToUpper() + "_" + modelNew.TenantColumnName.ToUpper();
sb.AppendLine("--DELETE TENANT DEFAULT FOR [" + newT.DatabaseName + "]");
sb.AppendLine("if exists (select name from sys.objects where name = '" + defaultName + "' AND type = 'D')");
sb.AppendLine("ALTER TABLE [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "] DROP CONSTRAINT [" + defaultName + "]");
sb.AppendLine();
if (newT.PascalName != newT.DatabaseName)
{
//This is for the mistake in name when released. Remove this default June 2013
defaultName = $"DF__{newT.PascalName}_{modelNew.TenantColumnName}".ToUpper();
sb.AppendLine($"--DELETE TENANT DEFAULT FOR [{newT.DatabaseName}]");
sb.AppendLine($"if exists (select name from sys.objects where name = '{defaultName}' AND type = 'D')");
sb.AppendLine($"ALTER TABLE [{newT.GetSQLSchema()}].[{newT.DatabaseName}] DROP CONSTRAINT [{defaultName}]");
sb.AppendLine();
}
//Drop Index
var indexName = $"IDX_{newT.DatabaseName.FlatGuid()}_{modelNew.TenantColumnName}".ToUpper();
sb.AppendLine($"if exists (select * from sys.indexes where name = '{indexName}')");
sb.AppendLine($"DROP INDEX [{indexName}] ON [{newT.GetSQLSchema()}].[{newT.DatabaseName}]");
sb.AppendLine();
//Drop the tenant field
sb.AppendLine($"if exists (select * from sys.columns c inner join sys.tables t on c.object_id = t.object_id where c.name = '{modelNew.TenantColumnName}' and t.name = '{newT.DatabaseName}')");
sb.AppendLine($"ALTER TABLE [{newT.GetSQLSchema()}].[{newT.DatabaseName}] DROP COLUMN [{modelNew.TenantColumnName}]");
sb.AppendLine();
}
else if (!oldT.IsTenant && newT.IsTenant)
{
//Add the tenant field
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlCreateTenantColumn(modelNew, newT));
}
else if (oldT.IsTenant && newT.IsTenant && oldT.DatabaseName != newT.DatabaseName)
{
//If rename tenant table then delete old view and create new view
}
#endregion
#region Primary Key Changed
//If the primary key changed, then generate a commented script that marks where the user can manually intervene
var newPKINdex = newT.TableIndexList.FirstOrDefault(x => x.PrimaryKey);
var oldPKINdex = oldT.TableIndexList.FirstOrDefault(x => x.PrimaryKey);
if (newPKINdex != null && oldPKINdex != null)
{
var newPKHash = newPKINdex.CorePropertiesHash;
var oldPKHash = oldPKINdex.CorePropertiesHash;
if (newPKHash != oldPKHash)
{
sb.AppendLine();
sb.AppendLine("--GENERATION NOTE **");
sb.AppendLine("--THE PRIMARY KEY HAS CHANGED, THIS MAY REQUIRE MANUAL INTERVENTION");
sb.AppendLine("--THE FOLLOWING SCRIPT WILL DROP AND RE-ADD THE PRIMARY KEY HOWEVER IF THERE ARE RELATIONSHIPS");
sb.AppendLine("--BASED ON THIS IT, THE SCRIPT WILL FAIL. YOU MUST DROP ALL FOREIGN KEYS FIRST.");
sb.AppendLine();
//Before drop PK remove all FK to the table
foreach (var r1 in oldT.GetRelations())
{
sb.Append(SQLEmit.GetSqlRemoveFK(r1));
sb.AppendLine("GO");
sb.AppendLine();
}
var pkName = $"PK_{newT.DatabaseName}".ToUpper();
sb.AppendLine($"----DROP PRIMARY KEY FOR TABLE [{newT.DatabaseName}]");
sb.AppendLine($"--if exists(select * from sys.objects where name = '{pkName}' and type = 'PK' and type_desc = 'PRIMARY_KEY_CONSTRAINT')");
sb.AppendLine($"--ALTER TABLE [{newT.GetSQLSchema()}].[{newT.DatabaseName}] DROP CONSTRAINT [{pkName}]");
sb.AppendLine("--GO");
var sql = SQLEmit.GetSqlCreatePK(newT) + "GO\r\n";
var lines = sql.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
//Comment the whole SQL block
var index = 0;
foreach (var s in lines)
{
var l = s;
l = "--" + l;
lines[index] = l;
index++;
}
sb.AppendLine(string.Join("\r\n", lines));
sb.AppendLine();
}
}
#endregion
#region Drop Foreign Keys
foreach (var r1 in oldT.GetRelations())
{
var r2 = newT.Relationships.FirstOrDefault(x => x.Is(r1));
if (r2 == null)
{
sb.Append(SQLEmit.GetSqlRemoveFK(r1));
sb.AppendLine("GO");
sb.AppendLine();
}
}
#endregion
#region Rename audit columns if necessary
if (modelOld.Database.CreatedByColumnName != modelNew.Database.CreatedByColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT, modelOld.Database.CreatedByColumnName, modelNew.Database.CreatedByColumnName));
sb.AppendLine("GO");
}
if (modelOld.Database.CreatedDateColumnName != modelNew.Database.CreatedDateColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT, modelOld.Database.CreatedDateColumnName, modelNew.Database.CreatedDateColumnName));
sb.AppendLine("GO");
}
if (modelOld.Database.ModifiedByColumnName != modelNew.Database.ModifiedByColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT, modelOld.Database.ModifiedByColumnName, modelNew.Database.ModifiedByColumnName));
sb.AppendLine("GO");
}
if (modelOld.Database.ModifiedDateColumnName != modelNew.Database.ModifiedDateColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT, modelOld.Database.ModifiedDateColumnName, modelNew.Database.ModifiedDateColumnName));
sb.AppendLine("GO");
}
if (modelOld.Database.ConcurrencyCheckColumnName != modelNew.Database.ConcurrencyCheckColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT, modelOld.Database.ConcurrencyCheckColumnName, modelNew.Database.ConcurrencyCheckColumnName));
sb.AppendLine("GO");
}
#endregion
#region Static Data
//For right now just emit NEW if different.
//TODO: Generate difference scripts for delete and change too.
var oldStaticScript = SQLEmit.GetSqlInsertStaticData(oldT);
var newStaticScript = SQLEmit.GetSqlInsertStaticData(newT);
if (oldStaticScript != newStaticScript)
{
sb.AppendLine(newStaticScript);
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlUpdateStaticData(oldT, newT));
sb.AppendLine("GO");
sb.AppendLine();
}
#endregion
//TODO - Check hash porperties and if changed recompile tenant view
}
}
//Do another look for second pass at changes.
//These things can only be done after the above loop
foreach (var newT in modelNew.Database.Tables.Where(x => !x.IsEnumOnly()).OrderBy(x => x.Name).ToList())
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => !x.IsEnumOnly());
if (oldT != null)
{
#region Add Foreign Keys
foreach (var r1 in newT.GetRelations().Where(x => x.Enforce))
{
var r2 = oldT.GetRelations().FirstOrDefault(x => x.Is(r1));
if (r2 == null)
{
//There is no OLD relation so it is new so add it
sb.Append(SQLEmit.GetSqlAddFK(r1));
sb.AppendLine("GO");
sb.AppendLine();
}
else if (r1.CorePropertiesHash != r2.CorePropertiesHash)
{
//The relation already exists and it has changed, so drop and re-add
sb.Append(SQLEmit.GetSqlRemoveFK(r2));
sb.AppendLine("GO");
sb.AppendLine();
sb.Append(SQLEmit.GetSqlAddFK(r1));
sb.AppendLine("GO");
sb.AppendLine();
}
}
#endregion
}
}
#endregion
#region Move tables between schemas
var reschema = 0;
foreach (var newT in modelNew.Database.Tables.Where(x => !x.IsEnumOnly()))
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => !x.IsEnumOnly());
if (oldT != null)
{
if (string.Compare(oldT.GetSQLSchema(), newT.GetSQLSchema(), true) != 0)
{
if (reschema == 0)
{
sb.AppendLine("--MOVE TABLES TO PROPER SCHEMA IF NEED BE");
}
//This table has changed schema so script it
sb.AppendLine("--CREATE DATABASE SCHEMAS");
sb.AppendLine($"if not exists(select * from sys.schemas where name = '{newT.GetSQLSchema()}')");
sb.AppendLine($"exec('CREATE SCHEMA [{newT.GetSQLSchema()}]')");
sb.AppendLine("GO");
sb.AppendLine("if exists (select * from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id where t.name = '" + newT.DatabaseName + "' and s.name = '" + oldT.GetSQLSchema() + "')");
sb.AppendLine(" ALTER SCHEMA [" + newT.GetSQLSchema() + "] TRANSFER [" + oldT.GetSQLSchema() + "].[" + newT.DatabaseName + "];");
sb.AppendLine("GO");
reschema++;
}
}
}
if (reschema > 0)
{
sb.AppendLine();
}
#endregion
#region Add Indexes
foreach (var newT in modelNew.Database.Tables.Where(x => !x.IsEnumOnly()).OrderBy(x => x.Name))
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => !x.IsEnumOnly());
if (oldT != null)
{
//If old exists and does old NOT, so create index
foreach (var newIndex in newT.TableIndexList)
{
var oldIndex = oldT.TableIndexList.FirstOrDefault(x => x.CorePropertiesHashNoNames == newIndex.CorePropertiesHashNoNames);
if (oldIndex == null)
{
sb.AppendLine(SQLEmit.GetSQLCreateIndex(newT, newIndex, false));
sb.AppendLine("GO");
sb.AppendLine();
}
}
//Both exist, so if different, drop and re-create
foreach (var newIndex in newT.TableIndexList)
{
var oldIndex = oldT.TableIndexList.FirstOrDefault(x => x.Is(newIndex));
if (oldIndex != null && oldIndex.CorePropertiesHashNoNames != newIndex.CorePropertiesHashNoNames)
{
sb.AppendLine(SQLEmit.GetSQLCreateIndex(newT, newIndex, false));
sb.AppendLine("GO");
sb.AppendLine();
}
}
}
}
#endregion
#region Add/Remove deleted SP, Views, and Funcs
//Views
var removedItems = 0;
foreach (var oldT in modelOld.Database.CustomViews.OrderBy(x => x.Name))
{
var newT = modelNew.Database.CustomViews.FirstOrDefault(x => x.Is(oldT));
if (newT == null)
{
sb.AppendLine($"if exists (select * from sys.objects where name = '{oldT.DatabaseName}' and [type] in ('V'))");
sb.AppendLine($"drop view [{oldT.DatabaseName}]");
removedItems++;
}
else if (newT.DatabaseName != oldT.DatabaseName)
{
//Name changed so remove old
sb.AppendLine($"if exists (select * from sys.objects where name = '{oldT.DatabaseName}' and [type] in ('V'))");
sb.AppendLine($"drop view [{oldT.DatabaseName}]");
removedItems++;
}
}
if (removedItems > 0)
{
sb.AppendLine("GO");
sb.AppendLine();
}
foreach (var newT in modelNew.Database.CustomViews.OrderBy(x => x.Name))
{
var oldT = modelOld.Database.CustomViews.FirstOrDefault(x => x.Is(newT));
if (oldT == null || (oldT.CorePropertiesHash != newT.CorePropertiesHash))
{
sb.Append(SQLEmit.GetSqlCreateView(newT, false));
}
}
if (removedItems > 0)
{
sb.AppendLine("GO");
sb.AppendLine();
}
#endregion
#region Add/Remove Audit fields
foreach (var newT in modelNew.Database.Tables.OrderBy(x => x.Name))
{
var oldT = modelOld.Database.Tables.FirstOrDefault(x => x.Is(newT));
if (oldT != null)
{
if (!oldT.AllowCreateAudit && newT.AllowCreateAudit)
{
Globals.AppendCreateAudit(newT, modelNew, sb);
}
if (!oldT.AllowModifiedAudit && newT.AllowModifiedAudit)
{
Globals.AppendModifiedAudit(newT, modelNew, sb);
}
if (!oldT.AllowConcurrencyCheck && newT.AllowConcurrencyCheck)
{
Globals.AppendConcurrencyCheckAudit(newT, modelNew, sb);
}
if (oldT.AllowCreateAudit && !newT.AllowCreateAudit)
{
Globals.DropCreateAudit(newT, modelNew, sb);
}
if (oldT.AllowModifiedAudit && !newT.AllowModifiedAudit)
{
Globals.DropModifiedAudit(newT, modelNew, sb);
}
if (oldT.AllowConcurrencyCheck && !newT.AllowConcurrencyCheck)
{
Globals.DropConcurrencyAudit(newT, modelNew, sb);
}
}
}
#endregion
#region Loop and change computed fields
foreach (var newT in modelNew.Database.Tables.Where(x => !x.IsEnumOnly()).OrderBy(x => x.Name).ToList())
{
//If the table exists...
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => !x.IsEnumOnly());
if (oldT != null)
{
var tChanged = false;
//If there is a computed field with a different value
foreach (var newC in newT.GetColumns().Where(x => x.ComputedColumn).ToList())
{
var oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
if (oldC != null && oldC.Formula != newC.Formula)
{
tChanged = true;
sb.AppendLine(
$"if exists(select t.name, c.name from sys.columns c inner join sys.tables t on c.object_id = t.object_id inner join sys.schemas s on t.schema_id = s.schema_id where and t.name = '{newT.DatabaseName}' and c.name = '{newC.DatabaseName}' and s.name = '{newT.GetSQLSchema()}')");
sb.AppendLine($"ALTER TABLE [{newT.GetSQLSchema()}].[{newT.DatabaseName}] DROP COLUMN [{newC.DatabaseName}]");
sb.AppendLine("GO");
sb.AppendLine($"ALTER TABLE [{newT.GetSQLSchema()}].[{newT.DatabaseName}] ADD [{newC.DatabaseName}] AS ({newC.Formula})");
sb.AppendLine("GO");
sb.AppendLine();
}
}
}
}
#endregion
return(sb.ToString());
}
public void AppendFullTemplate()
{
try
{
sb.AppendLine("--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.");
sb.AppendLine("--Model Version");
sb.AppendLine();
sb.AppendLine("--This SQL is generated for functions");
sb.AppendLine();
nHydrate.Generator.GenerationHelper.AppendCopyrightInSQL(sb, _model);
#region Functions
foreach (var function in _model.Database.Functions.Where(x => x.Generated).OrderBy(x => x.DatabaseName))
{
sb.AppendLine(SQLEmit.GetSQLCreateFunction(function, true, _model.EFVersion));
}
//Add Grants
if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
foreach (var function in _model.Database.Functions.Where(x => x.Generated).OrderBy(x => x.DatabaseName))
{
if (function.IsTable)
{
sb.AppendFormat("GRANT ALL ON [" + function.GetSQLSchema() + "].[{0}] TO [{1}]", function.PascalName, _model.Database.GrantExecUser).AppendLine();
}
else
{
sb.AppendFormat("GRANT ALL ON [" + function.GetSQLSchema() + "].[{0}] TO [{1}]", function.PascalName, _model.Database.GrantExecUser).AppendLine();
}
sb.AppendLine("--MODELID: " + function.Key);
sb.AppendLine("GO");
sb.AppendLine();
}
}
#endregion
#region Table Security Functions
var tableList = _model.Database.Tables.Where(x => x.Generated && x.Security.IsValid()).OrderBy(x => x.PascalName).ToList();
foreach (var table in tableList)
{
sb.AppendLine(SQLEmit.GetSQLCreateTableSecurityFunction(table, _model, true));
}
//Add Grants
if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
foreach (var table in tableList)
{
var function = table.Security;
var objectName = ValidationHelper.MakeDatabaseIdentifier("__security__" + table.Name);
sb.AppendFormat("GRANT ALL ON [" + table.GetSQLSchema() + "].[{0}] TO [{1}]", objectName, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("--MODELID: " + function.Key);
sb.AppendLine("GO");
sb.AppendLine();
}
}
#endregion
}
catch (Exception ex)
{
throw;
}
}
public static string GetModelDifferenceSql(ModelRoot modelOld, ModelRoot modelNew)
{
try
{
var sb = new StringBuilder();
#region Loop and Add tables
foreach (var newT in modelNew.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name).ToList())
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => x.TypedTable != TypedTableConstants.EnumOnly);
if (oldT == null)
{
//Add table, indexes
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSQLCreateTable(modelNew, newT));
sb.AppendLine("GO");
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlCreatePK(newT));
sb.AppendLine("GO");
//DO NOT process primary keys
foreach (var index in newT.TableIndexList.Where(x => !x.PrimaryKey))
{
sb.Append(nHydrate.Core.SQLGeneration.SQLEmit.GetSQLCreateIndex(newT, index, false));
sb.AppendLine("GO");
sb.AppendLine();
}
if (newT.StaticData.Count > 0)
{
sb.Append(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlInsertStaticData(newT));
sb.AppendLine("GO");
sb.AppendLine();
}
//If this is a tenant table then add the view as well
if (newT.IsTenant)
{
var grantSB = new StringBuilder();
var q1 = nHydrate.Core.SQLGeneration.SQLEmit.GetSqlTenantView(modelNew, newT, grantSB);
sb.AppendLine(q1);
if (grantSB.ToString() != string.Empty)
{
sb.AppendLine(grantSB.ToString());
}
}
}
}
#endregion
#region Delete Indexes
foreach (var newT in modelNew.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name).ToList())
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => x.TypedTable != TypedTableConstants.EnumOnly);
if (oldT != null)
{
//If old exists new does NOT, so delete index
foreach (var oldIndex in oldT.TableIndexList)
{
var newIndex = newT.TableIndexList.FirstOrDefault(x => x.Key == oldIndex.Key);
if (newIndex == null)
{
sb.AppendLine(SQLEmit.GetSQLDropIndex(newT, oldIndex));
sb.AppendLine("GO");
}
}
//Both exist, so if different, drop and re-create
foreach (var newIndex in newT.TableIndexList)
{
var oldIndex = oldT.TableIndexList.FirstOrDefault(x => x.Key == newIndex.Key);
if (oldIndex != null && oldIndex.CorePropertiesHashNoNames != newIndex.CorePropertiesHashNoNames)
{
sb.AppendLine(SQLEmit.GetSQLDropIndex(newT, oldIndex));
sb.AppendLine("GO");
}
}
}
}
#endregion
#region Loop and DELETE tables
foreach (var oldT in modelOld.Database.Tables.Where(x => x.Generated && x.TypedTable != TypedTableConstants.EnumOnly))
{
var newT = modelNew.Database.Tables.FirstOrDefault(x => x.Generated && (x.TypedTable != TypedTableConstants.EnumOnly) && x.Key.ToLower() == oldT.Key.ToLower());
if (newT == null)
{
//DELETE TABLE
sb.Append(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlDropTable(oldT));
sb.AppendLine("GO");
//TODO - Delete Tenant View
sb.AppendLine();
}
else if (newT != null && oldT.AllowAuditTracking && !newT.AllowAuditTracking)
{
//If the old model had audit tracking and the new one does not, add a TODO in the script
var tableName = "__AUDIT__" + Globals.GetTableDatabaseName(modelOld, oldT);
sb.AppendLine("--TODO: REMOVE AUDIT TABLE '" + tableName + "'");
sb.AppendLine("--The previous model had audit tracking turn on for table '" + Globals.GetTableDatabaseName(modelOld, oldT) + "' and now it is turned off.");
sb.AppendLine("--The audit table will not be removed automatically. If you want to remove it, uncomment the following script.");
sb.AppendLine("--DROP TABLE [" + tableName + "]");
sb.AppendLine("--GO");
sb.AppendLine();
}
//else if (tList[0].DatabaseName != oldT.DatabaseName)
//{
// //RENAME TABLE
// sb.AppendLine("if exists(select * from sysobjects where name = '" + oldT.DatabaseName + "' and xtype = 'U')");
// sb.AppendLine("exec sp_rename [" + oldT.DatabaseName + "], [" + tList[0].DatabaseName + "]");
//}
}
#endregion
#region Loop and Modify tables
foreach (var newT in modelNew.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name).ToList())
{
var schemaChanged = false;
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => x.TypedTable != TypedTableConstants.EnumOnly);
if (oldT != null)
{
var querylist = new List <string>();
#region Rename table if need be
if (oldT.DatabaseName != newT.DatabaseName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameTable(oldT, newT));
sb.AppendLine("GO");
}
#endregion
#region Add columns
foreach (var newC in newT.GetColumns())
{
var oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
if (oldC == null)
{
//ADD COLUMN
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlAddColumn(newC));
sb.AppendLine("GO");
sb.AppendLine();
schemaChanged = true;
}
//else if (newC.DatabaseName != oldC.DatabaseName)
//{
// //RENAME COLUMN
// sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSQLRenameColumn(oldC, newC));
// sb.AppendLine("GO");
// sb.AppendLine();
//}
}
#endregion
#region Delete Columns
foreach (Reference oldRef in oldT.Columns)
{
var oldC = oldRef.Object as Column;
var newC = Globals.GetColumnByKey(newT.Columns, oldC.Key);
if (newC == null)
{
//DELETE COLUMN
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlDropColumn(modelNew, oldC));
sb.AppendLine("GO");
sb.AppendLine();
schemaChanged = true;
}
else if (newC.DatabaseName != oldC.DatabaseName)
{
////RENAME COLUMN
//string sql = "if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + oldC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" +
// "AND not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + newC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" + Environment.NewLine +
// "EXEC sp_rename @objname = '" + newT.DatabaseName + "." + oldC.DatabaseName + "', @newname = '" + newC.DatabaseName + "', @objtype = 'COLUMN'";
//if (!querylist.Contains(sql))
//{
// querylist.Add(sql);
// sb.AppendLine(sql);
// sb.AppendLine("GO");
// sb.AppendLine();
//}
}
}
#endregion
#region Modify Columns
foreach (var newC in newT.GetColumns())
{
var oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
if (oldC != null)
{
var document = new XmlDocument();
document.LoadXml("<a></a>");
var n1 = XmlHelper.AddElement(document.DocumentElement, "q");
var n2 = XmlHelper.AddElement(document.DocumentElement, "q");
oldC.XmlAppend(n1);
newC.XmlAppend(n2);
//Check column, ignore defaults
if (newC.CorePropertiesHashNoPK != oldC.CorePropertiesHashNoPK)
{
//MODIFY COLUMN
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlModifyColumn(oldC, newC));
sb.AppendLine("GO");
sb.AppendLine();
schemaChanged = true;
}
//Drop add defaults if column
//if ((newC.CorePropertiesHashNoPK != oldC.CorePropertiesHashNoPK) || (oldC.Default != newC.Default))
//{
// if (!string.IsNullOrEmpty(oldC.Default))
// {
// //Old default was something so drop it
// sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlDropColumnDefault(newC));
// sb.AppendLine("GO");
// sb.AppendLine();
// }
// if (!string.IsNullOrEmpty(newC.Default))
// {
// //New default is something so add it
// sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlCreateColumnDefault(modelNew, newC));
// sb.AppendLine("GO");
// sb.AppendLine();
// }
//}
if (!string.IsNullOrEmpty(newC.Default) && ((oldC.Default != newC.Default) || (oldC.DataType != newC.DataType) || (oldC.DatabaseName != newC.DatabaseName)))
{
//New default is something so add it
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlCreateColumnDefault(modelNew, newC));
sb.AppendLine("GO");
sb.AppendLine();
}
}
}
#endregion
#region Process Table Splits
{
var splits = modelNew.Refactorizations
.Where(x => x is RefactorTableSplit)
.Cast <RefactorTableSplit>()
.Where(x => x.EntityKey1 == new Guid(newT.Key))
.ToList();
foreach (var split in splits)
{
var splitTable = modelNew.Database.Tables.FirstOrDefault(x => new Guid(x.Key) == split.EntityKey2);
var origFields = oldT.GeneratedColumns.Where(x => split.ReMappedFieldIDList.Keys.Contains(new Guid(x.Key))).ToList();
if (splitTable != null && origFields.Count > 0)
{
var newFields = new List <Column>();
foreach (var item in origFields)
{
var newF = splitTable.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == split.ReMappedFieldIDList[new Guid(item.Key)]);
if (newF != null)
{
newFields.Add(newF);
}
}
newFields = newFields.Distinct().ToList();
//If there are columns then process
if (newFields.Count > 0)
{
sb.AppendLine("--PROCESS TABLE SPLIT [" + newT.DatabaseName + "] -> [" + splitTable.DatabaseName + "]");
//Get the fields for generation
//This may be a different number than original split since user can remove fields
var genFields = new Dictionary <Column, Column>();
foreach (var f in origFields)
{
//Get the new column from the new table as the name might have changed
var newID = split.ReMappedFieldIDList[new Guid(f.Key)];
var newF = splitTable.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == newID);
if (newF != null)
{
genFields.Add(f, newF);
}
}
//Process the actual script fields
if (genFields.Count > 0)
{
//Turn on identity insert if necessary
if (splitTable.PrimaryKeyColumns.Count(x => x.Identity == IdentityTypeConstants.Database) > 0)
{
sb.AppendLine("SET identity_insert [" + splitTable.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(modelNew, splitTable) + "] on");
}
sb.Append("INSERT INTO [" + splitTable.GetSQLSchema() + "].[" + splitTable.DatabaseName + "] (");
foreach (var f in genFields.Keys)
{
//Get the new column from the new table as the name might have changed
var newF = genFields[f];
sb.Append("[" + newF.DatabaseName + "]");
if (genFields.Keys.IndexOf(f) < genFields.Keys.Count - 1)
{
sb.Append(", ");
}
}
sb.AppendLine(")");
sb.Append("SELECT ");
foreach (var f in genFields.Keys)
{
sb.Append("[" + f.DatabaseName + "]");
if (genFields.Keys.IndexOf(f) < genFields.Keys.Count - 1)
{
sb.Append(", ");
}
}
sb.AppendLine(" FROM [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "]");
//Turn off identity insert if necessary
if (splitTable.PrimaryKeyColumns.Count(x => x.Identity == IdentityTypeConstants.Database) > 0)
{
sb.AppendLine("SET identity_insert [" + splitTable.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(modelNew, splitTable) + "] off");
}
sb.AppendLine("GO");
sb.AppendLine();
}
}
}
}
} //Table Splits
#endregion
#region Process Table Combines
{
var splits = modelNew.Refactorizations
.Where(x => x is RefactorTableCombine)
.Cast <RefactorTableCombine>()
.Where(x => x.EntityKey1 == new Guid(newT.Key))
.ToList();
foreach (var split in splits)
{
var deletedTable = modelOld.Database.Tables.FirstOrDefault(x => new Guid(x.Key) == split.EntityKey2);
if (deletedTable != null)
{
var deletedOrigDeletedT = modelOld.Database.Tables.GetByKey(deletedTable.Key).FirstOrDefault();
if (deletedOrigDeletedT != null)
{
var deletedFields = deletedOrigDeletedT.GeneratedColumns.Where(x => split.ReMappedFieldIDList.Keys.Contains(new Guid(x.Key))).ToList();
if (deletedFields.Count > 0)
{
var targetFields = new List <Column>();
foreach (var item in deletedFields)
{
var newF = newT.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == split.ReMappedFieldIDList[new Guid(item.Key)]);
if (newF != null)
{
targetFields.Add(newF);
}
}
targetFields = targetFields.Distinct().ToList();
//If there are columns then process
if (targetFields.Count > 0)
{
//Get the fields for generation
//This may be a different number than original combine since user can remove fields
var genFields = new Dictionary <Column, Column>();
foreach (var f in deletedFields)
{
//Get the new column from the new table as the name might have changed
var newID = split.ReMappedFieldIDList[new Guid(f.Key)];
var newF = newT.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == newID);
if (newF != null)
{
genFields.Add(f, newF);
}
}
//Process the actual script fields
if (genFields.Count > 0)
{
sb.AppendLine("--PROCESS TABLE COMBINE [" + deletedTable.DatabaseName + "] into [" + newT.DatabaseName + "]");
sb.Append("UPDATE [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "] SET ");
foreach (var f in genFields.Keys)
{
//Get the new column from the new table as the name might have changed
var newF = genFields[f];
sb.Append("[" + newF.DatabaseName + "] = [_deleted].[" + f.DatabaseName + "]");
if (genFields.Keys.IndexOf(f) < genFields.Keys.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(" FROM [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "] AS [_A] INNER JOIN ");
sb.Append("[" + deletedTable.GetSQLSchema() + "].[" + deletedTable.DatabaseName + "] AS [_deleted] ON ");
sb.Append("[_A].[" + newT.PrimaryKeyColumns.First().DatabaseName + "] = [_deleted].[" + deletedTable.PrimaryKeyColumns.First().DatabaseName + "]");
sb.AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
}
}
}
}
} //Table Combine
#endregion
#region Tenant
if (oldT.IsTenant && !newT.IsTenant)
{
//Drop default
var defaultName = "DF__" + newT.DatabaseName.ToUpper() + "_" + modelNew.TenantColumnName.ToUpper();
sb.AppendLine("--DELETE TENANT DEFAULT FOR [" + newT.DatabaseName + "]");
sb.AppendLine("if exists (select name from sys.objects where name = '" + defaultName + "' AND type = 'D')");
sb.AppendLine("ALTER TABLE [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "] DROP CONSTRAINT [" + defaultName + "]");
sb.AppendLine();
if (newT.PascalName != newT.DatabaseName)
{
//This is for the mistake in name when released. Remove this default June 2013
defaultName = "DF__" + newT.PascalName.ToUpper() + "_" + modelNew.TenantColumnName.ToUpper();
sb.AppendLine("--DELETE TENANT DEFAULT FOR [" + newT.DatabaseName + "]");
sb.AppendLine("if exists (select name from sys.objects where name = '" + defaultName + "' AND type = 'D')");
sb.AppendLine("ALTER TABLE [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "] DROP CONSTRAINT [" + defaultName + "]");
sb.AppendLine();
}
//Drop Index
var indexName = "IDX_" + newT.DatabaseName.Replace("-", string.Empty) + "_" + modelNew.TenantColumnName;
indexName = indexName.ToUpper();
sb.AppendLine("if exists (select * from sys.indexes where name = '" + indexName + "')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + newT.DatabaseName + "]");
sb.AppendLine();
//Drop the associated view
var viewName = modelOld.TenantPrefix + "_" + oldT.DatabaseName;
sb.AppendLine("if exists (select name from sys.objects where name = '" + viewName + "' AND type = 'V')");
sb.AppendLine("DROP VIEW [" + viewName + "]");
sb.AppendLine();
//Drop the tenant field
sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + modelNew.TenantColumnName + "' and o.name = '" + newT.DatabaseName + "')");
sb.AppendLine("ALTER TABLE [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "] DROP COLUMN [" + modelNew.TenantColumnName + "]");
sb.AppendLine();
}
else if (!oldT.IsTenant && newT.IsTenant)
{
//Add the tenant field
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlCreateTenantColumn(modelNew, newT));
}
#endregion
#region Primary Key Changed
//If the primary key changed, then generate a commented script that marks where the user can manually intervene
var newPKINdex = newT.TableIndexList.FirstOrDefault(x => x.PrimaryKey);
var oldPKINdex = oldT.TableIndexList.FirstOrDefault(x => x.PrimaryKey);
if (newPKINdex != null && oldPKINdex != null)
{
var newPKHash = newPKINdex.CorePropertiesHash;
var oldPKHash = oldPKINdex.CorePropertiesHash;
if (newPKHash != oldPKHash)
{
sb.AppendLine("--GENERATION NOTE **");
sb.AppendLine("--THE PRIMARY KEY HAS CHANGED, THIS MAY REQUIRE MANUAL INTERVENTION");
sb.AppendLine("--THE FOLLOWING SCRIPT WILL DROP AND READD THE PRIMARY KEY HOWEVER IF THERE ARE RELATIONSHIPS");
sb.AppendLine("--BASED ON THIS IT, THE SCRIPT WILL FAIL. YOU MUST DROP ALL RELATIONSHIPS FIRST.");
var tableName = Globals.GetTableDatabaseName(modelNew, newT);
var pkName = "PK_" + tableName;
pkName = pkName.ToUpper();
sb.AppendLine("--DROP PRIMARY KEY FOR TABLE [" + tableName + "]");
sb.AppendLine("--if exists(select * from sys.objects where name = '" + pkName + "' and type = 'PK' and type_desc = 'PRIMARY_KEY_CONSTRAINT')");
sb.AppendLine("--ALTER TABLE [" + newT.GetSQLSchema() + "].[" + tableName + "] DROP CONSTRAINT [" + pkName + "]");
sb.AppendLine("--GO");
var sql = SQLEmit.GetSqlCreatePK(newT) + "GO\r\n";
var lines = sql.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
//Comment the whole SQL block
var index = 0;
foreach (var s in lines)
{
var l = s;
if (!l.StartsWith("--"))
{
l = "--" + l;
}
lines[index] = l;
index++;
}
sb.AppendLine(string.Join("\r\n", lines));
sb.AppendLine();
}
}
#endregion
#region Drop Foreign Keys
foreach (var r1 in oldT.GetRelations().ToList())
{
var r2 = newT.Relationships.FirstOrDefault(x => x.Key == r1.Key);
if (r2 == null)
{
sb.Append(SQLEmit.GetSqlRemoveFK(r1));
sb.AppendLine("GO");
sb.AppendLine();
}
}
#endregion
#region Rename audit columns if necessary
if (modelOld.Database.CreatedByColumnName != modelNew.Database.CreatedByColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT.DatabaseName, modelOld.Database.CreatedByColumnName, modelNew.Database.CreatedByColumnName));
sb.AppendLine("GO");
}
if (modelOld.Database.CreatedDateColumnName != modelNew.Database.CreatedDateColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT.DatabaseName, modelOld.Database.CreatedDateColumnName, modelNew.Database.CreatedDateColumnName));
sb.AppendLine("GO");
}
if (modelOld.Database.ModifiedByColumnName != modelNew.Database.ModifiedByColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT.DatabaseName, modelOld.Database.ModifiedByColumnName, modelNew.Database.ModifiedByColumnName));
sb.AppendLine("GO");
}
if (modelOld.Database.ModifiedDateColumnName != modelNew.Database.ModifiedDateColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT.DatabaseName, modelOld.Database.ModifiedDateColumnName, modelNew.Database.ModifiedDateColumnName));
sb.AppendLine("GO");
}
if (modelOld.Database.TimestampColumnName != modelNew.Database.TimestampColumnName)
{
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlRenameColumn(newT.DatabaseName, modelOld.Database.TimestampColumnName, modelNew.Database.TimestampColumnName));
sb.AppendLine("GO");
}
#endregion
#region Emit Tenant View if need be
//If the table schema has changed then emit the Tenant view
if (schemaChanged && newT.IsTenant)
{
var grantSB = new StringBuilder();
var q1 = nHydrate.Core.SQLGeneration.SQLEmit.GetSqlTenantView(modelNew, newT, grantSB);
sb.AppendLine(q1);
if (grantSB.ToString() != string.Empty)
{
sb.AppendLine(grantSB.ToString());
}
}
#endregion
#region Static Data
//For right now just emit NEW if different.
//TODO: Generate difference scripts for delete and change too.
var oldStaticScript = SQLEmit.GetSqlInsertStaticData(oldT);
var newStaticScript = SQLEmit.GetSqlInsertStaticData(newT);
if (oldStaticScript != newStaticScript)
{
sb.AppendLine(newStaticScript);
sb.AppendLine(nHydrate.Core.SQLGeneration.SQLEmit.GetSqlUpdateStaticData(newT));
sb.AppendLine("GO");
sb.AppendLine();
}
#endregion
//TODO - Check hash porperties and if changed recompile tenant view
}
}
//Do another look for second pass at changes.
//These things can only be done after the above loop
foreach (var newT in modelNew.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name).ToList())
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => x.TypedTable != TypedTableConstants.EnumOnly);
if (oldT != null)
{
#region Add Foreign Keys
foreach (var r1 in newT.GetRelations().ToList())
{
var r2 = oldT.GetRelations().ToList().FirstOrDefault(x => x.Key == r1.Key);
if (r2 == null)
{
//There is no OLD relation so it is new so add it
sb.Append(SQLEmit.GetSqlAddFK(r1));
sb.AppendLine("GO");
sb.AppendLine();
}
else if (r1.CorePropertiesHash != r2.CorePropertiesHash)
{
//The relation already exists and it has changed, so drop and re-add
sb.Append(SQLEmit.GetSqlRemoveFK(r2));
sb.AppendLine("GO");
sb.AppendLine();
sb.Append(SQLEmit.GetSqlAddFK(r1));
sb.AppendLine("GO");
sb.AppendLine();
}
}
#endregion
}
}
#endregion
#region Move tables between schemas
var reschema = 0;
foreach (Table newT in modelNew.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly))
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => x.TypedTable != TypedTableConstants.EnumOnly);
if (oldT != null)
{
if (string.Compare(oldT.GetSQLSchema(), newT.GetSQLSchema(), true) != 0)
{
if (reschema == 0)
{
sb.AppendLine("--MOVE TABLES TO PROPER SCHEMA IF NEED BE");
}
//This table has changed schema so script it
sb.AppendLine("--CREATE DATABASE SCHEMAS");
sb.AppendLine("if not exists(select * from sys.schemas where name = '" + newT.GetSQLSchema() + "')");
sb.AppendLine("exec('CREATE SCHEMA [" + newT.GetSQLSchema() + "]')");
sb.AppendLine("GO");
sb.AppendLine("if exists (select * from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id where t.name = '" + newT.DatabaseName + "' and s.name = '" + oldT.GetSQLSchema() + "')");
sb.AppendLine(" ALTER SCHEMA [" + newT.GetSQLSchema() + "] TRANSFER [" + oldT.GetSQLSchema() + "].[" + newT.DatabaseName + "];");
sb.AppendLine("GO");
reschema++;
}
}
}
if (reschema > 0)
{
sb.AppendLine();
}
#endregion
#region Add Indexes
foreach (var newT in modelNew.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).OrderBy(x => x.Name))
{
var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault(x => x.TypedTable != TypedTableConstants.EnumOnly);
if (oldT != null)
{
//If old exists and does old NOT, so create index
foreach (var newIndex in newT.TableIndexList)
{
var oldIndex = oldT.TableIndexList.FirstOrDefault(x => x.Key == newIndex.Key);
if (oldIndex == null)
{
sb.AppendLine(SQLEmit.GetSQLCreateIndex(newT, newIndex, false));
sb.AppendLine("GO");
sb.AppendLine();
}
}
//Both exist, so if different, drop and re-create
foreach (var newIndex in newT.TableIndexList)
{
var oldIndex = oldT.TableIndexList.FirstOrDefault(x => x.Key == newIndex.Key);
if (oldIndex != null && oldIndex.CorePropertiesHashNoNames != newIndex.CorePropertiesHashNoNames)
{
sb.AppendLine(SQLEmit.GetSQLCreateIndex(newT, newIndex, false));
sb.AppendLine("GO");
sb.AppendLine();
}
}
}
}
#endregion
#region Add/Remove deleted SP, Views, and Funcs
//Stored procedures
var removedItems = 0;
foreach (var oldT in modelOld.Database.CustomStoredProcedures.OrderBy(x => x.Name))
{
var newT = modelNew.Database.CustomStoredProcedures.FirstOrDefault(x => x.Key == oldT.Key);
if (newT == null)
{
sb.AppendLine("if exists (select * from sys.objects where name = '" + oldT.DatabaseName + "' and [type] in ('P'))");
sb.AppendLine("drop procedure [" + oldT.DatabaseName + "]");
removedItems++;
}
else if (newT.DatabaseName != oldT.DatabaseName)
{
//Name changed so remove old
sb.AppendLine("if exists (select * from sys.objects where name = '" + oldT.DatabaseName + "' and [type] in ('P'))");
sb.AppendLine("drop procedure [" + oldT.DatabaseName + "]");
removedItems++;
}
}
if (removedItems > 0)
{
sb.AppendLine("GO");
sb.AppendLine();
}
foreach (var newT in modelNew.Database.CustomStoredProcedures.OrderBy(x => x.Name))
{
var oldT = modelOld.Database.CustomStoredProcedures.FirstOrDefault(x => x.Key == newT.Key);
if (oldT == null || (oldT.CorePropertiesHash != newT.CorePropertiesHash))
{
sb.Append(SQLEmit.GetSQLCreateStoredProc(newT, false));
}
}
//Views
removedItems = 0;
foreach (var oldT in modelOld.Database.CustomViews.OrderBy(x => x.Name))
{
var newT = modelNew.Database.CustomViews.FirstOrDefault(x => x.Key == oldT.Key);
if (newT == null)
{
sb.AppendLine("if exists (select * from sys.objects where name = '" + oldT.DatabaseName + "' and [type] in ('V'))");
sb.AppendLine("drop view [" + oldT.DatabaseName + "]");
removedItems++;
}
else if (newT.DatabaseName != oldT.DatabaseName)
{
//Name changed so remove old
sb.AppendLine("if exists (select * from sys.objects where name = '" + oldT.DatabaseName + "' and [type] in ('V'))");
sb.AppendLine("drop view [" + oldT.DatabaseName + "]");
removedItems++;
}
}
if (removedItems > 0)
{
sb.AppendLine("GO");
sb.AppendLine();
}
foreach (var newT in modelNew.Database.CustomViews.OrderBy(x => x.Name))
{
var oldT = modelOld.Database.CustomViews.FirstOrDefault(x => x.Key == newT.Key);
if (oldT == null || (oldT.CorePropertiesHash != newT.CorePropertiesHash))
{
sb.Append(SQLEmit.GetSqlCreateView(newT, false));
}
}
//Functions
removedItems = 0;
foreach (var oldT in modelOld.Database.Functions.OrderBy(x => x.Name))
{
var newT = modelNew.Database.Functions.FirstOrDefault(x => x.Key == oldT.Key);
if (newT == null)
{
sb.AppendLine("if exists (select * from sys.objects where name = '" + oldT.DatabaseName + "' and [type] in ('FN','IF','TF','FS','FT'))");
sb.AppendLine("drop function [" + oldT.DatabaseName + "]");
removedItems++;
}
else if (newT.DatabaseName != oldT.DatabaseName)
{
//Name changed so remove old
sb.AppendLine("if exists (select * from sys.objects where name = '" + oldT.DatabaseName + "' and [type] in ('FN','IF','TF','FS','FT'))");
sb.AppendLine("drop function [" + oldT.DatabaseName + "]");
removedItems++;
}
}
if (removedItems > 0)
{
sb.AppendLine("GO");
sb.AppendLine();
}
foreach (var newT in modelNew.Database.Functions.OrderBy(x => x.Name))
{
var oldT = modelOld.Database.Functions.FirstOrDefault(x => x.Key == newT.Key);
if (oldT == null || (oldT.CorePropertiesHash != newT.CorePropertiesHash))
{
sb.Append(SQLEmit.GetSQLCreateFunction(newT, false, modelNew.EFVersion));
}
}
#endregion
return(sb.ToString());
}
catch (Exception ex)
{
throw;
}
}
private void GenerateContent()
{
sb.AppendLine($"--This SQL is generated for the model defined view [{_view.DatabaseName}]");
sb.AppendLine();
sb.Append(SQLEmit.GetSqlCreateView(_view, true));
}
