Exception ReplaceException(Exception ex, SqlPreCommandSimple command)
{
//if (ex is Npgsql.PostgresException se)
//{
// switch (se.Number)
// {
// case -2: return new TimeoutException(ex.Message, ex);
// case 2601: return new UniqueKeyException(ex);
// case 547: return new ForeignKeyException(ex);
// default: return ex;
// }
//}
//if (ex is SqlTypeException ste && ex.Message.Contains("DateTime"))
//{
// var mins = command.Parameters.Where(a => DateTime.MinValue.Equals(a.Value));
// if (mins.Any())
// {
// return new ArgumentOutOfRangeException("{0} {1} not initialized and equal to DateTime.MinValue".FormatWith(
// mins.CommaAnd(a => a.ParameterName),
// mins.Count() == 1 ? "is" : "are"), ex);
// }
//}
return(ex);
}
Exception ReplaceException(Exception ex, SqlPreCommandSimple command)
{
if (ex is SqlException se)
{
switch (se.Number)
{
case -2: return(new TimeoutException(ex.Message, ex));
case 2601: return(new UniqueKeyException(ex));
case 547: return(new ForeignKeyException(ex));
default: return(ex);
}
}
if (ex is SqlTypeException ste && ex.Message.Contains("DateTime"))
{
var mins = command.Parameters.Where(a => DateTime.MinValue.Equals(a.Value));
if (mins.Any())
{
return(new ArgumentOutOfRangeException("{0} {1} not initialized and equal to DateTime.MinValue".FormatWith(
mins.CommaAnd(a => a.ParameterName),
mins.Count() == 1 ? "is" : "are"), ex));
}
}
return(ex);
}
protected internal override DataTable ExecuteDataTable(SqlPreCommandSimple preCommand, CommandType commandType)
{
return(EnsureConnectionRetry(con =>
{
using (NpgsqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql()))
{
try
{
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataTable result = new DataTable();
da.Fill(result);
return result;
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}));
}
protected internal override object ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType)
{
using (SqlConnection con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql()))
{
try
{
object result = cmd.ExecuteScalar();
if (result == null || result == DBNull.Value)
{
return(null);
}
return(result);
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}
protected internal override int ExecuteNonQuery(SqlPreCommandSimple preCommand, CommandType commandType)
{
return(EnsureConnectionRetry(con =>
{
using (NpgsqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql()))
{
try
{
int result = cmd.ExecuteNonQuery();
return result;
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}));
}
protected internal override object?ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType)
{
return(EnsureConnectionRetry(con =>
{
using (NpgsqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.sp_executesql()))
{
try
{
object?result = cmd.ExecuteScalar();
if (result == null || result == DBNull.Value)
{
return null;
}
return result;
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}));
}
protected internal override DataTable ExecuteDataTable(SqlPreCommandSimple preCommand)
{
using (SqlCeConnection con = EnsureConnection())
using (SqlCeCommand cmd = NewCommand(preCommand, con))
using (HeavyProfiler.Log("SQL", () => cmd.CommandText))
{
try
{
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable result = new DataTable();
da.Fill(result);
return(result);
}
catch (SqlCeException ex)
{
var nex = HandleException(ex);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}
SqlCeCommand NewCommand(SqlPreCommandSimple preCommand, SqlCeConnection overridenConnection)
{
SqlCeCommand cmd = new SqlCeCommand();
int?timeout = Connector.ScopeTimeout ?? CommandTimeout;
if (timeout.HasValue)
{
cmd.CommandTimeout = timeout.Value;
}
if (overridenConnection != null)
{
cmd.Connection = overridenConnection;
}
else
{
cmd.Connection = (SqlCeConnection)Transaction.CurrentConnection;
cmd.Transaction = (SqlCeTransaction)Transaction.CurrentTransaccion;
}
cmd.CommandText = preCommand.Sql;
if (preCommand.Parameters != null)
{
foreach (SqlCeParameter param in preCommand.Parameters)
{
cmd.Parameters.Add(param);
}
}
Log(preCommand);
return(cmd);
}
protected internal override DataSet ExecuteDataSet(SqlPreCommandSimple preCommand, CommandType commandType)
{
using (SqlConnection con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql()))
{
try
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet result = new DataSet();
da.Fill(result);
return(result);
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}
public Exception HandleException(Exception ex, SqlPreCommandSimple command)
{
var nex = ReplaceException(ex, command);
nex.Data["Sql"] = command.PlainSql();
return(nex);
}
public static SqlPreCommand CreateIndex(Index index)
{
string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
if (index is PrimaryClusteredIndex)
{
return(new SqlPreCommandSimple("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY CLUSTERED({2})".FormatWith(
index.Table.Name,
index.IndexName,
columns)));
}
if (index is UniqueIndex uIndex && uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}"
.FormatWith(viewName, columns, uIndex.Table.Name.ToString(), uIndex.Where))
{
GoBefore = true, GoAfter = true
};
SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})"
.FormatWith(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ")));
return(SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql));
}
SqlCeCommand NewCommand(SqlPreCommandSimple preCommand, SqlCeConnection overridenConnection)
{
SqlCeCommand cmd = new SqlCeCommand();
int? timeout = Connector.ScopeTimeout ?? CommandTimeout;
if (timeout.HasValue)
cmd.CommandTimeout = timeout.Value;
if (overridenConnection != null)
cmd.Connection = overridenConnection;
else
{
cmd.Connection = (SqlCeConnection)Transaction.CurrentConnection;
cmd.Transaction = (SqlCeTransaction)Transaction.CurrentTransaccion;
}
cmd.CommandText = preCommand.Sql;
if (preCommand.Parameters != null)
{
foreach (SqlCeParameter param in preCommand.Parameters)
{
cmd.Parameters.Add(param);
}
}
Log(preCommand);
return cmd;
}
public SqlPreCommand CreateTableSql(ITable t, ObjectName?tableName = null, bool avoidSystemVersioning = false)
{
var primaryKeyConstraint = t.PrimaryKey == null || t.SystemVersioned != null && tableName != null && t.SystemVersioned.TableName.Equals(tableName) ? null :
isPostgres ?
"CONSTRAINT {0} PRIMARY KEY ({1})".FormatWith(PrimaryKeyIndex.GetPrimaryKeyName(t.Name).SqlEscape(isPostgres), t.PrimaryKey.Name.SqlEscape(isPostgres)) :
"CONSTRAINT {0} PRIMARY KEY CLUSTERED ({1} ASC)".FormatWith(PrimaryKeyIndex.GetPrimaryKeyName(t.Name).SqlEscape(isPostgres), t.PrimaryKey.Name.SqlEscape(isPostgres));
var systemPeriod = t.SystemVersioned == null || IsPostgres || avoidSystemVersioning ? null : Period(t.SystemVersioned);
var columns = t.Columns.Values.Select(c => this.ColumnLine(c, GetDefaultConstaint(t, c), isChange: false, forHistoryTable: avoidSystemVersioning))
.And(primaryKeyConstraint)
.And(systemPeriod)
.NotNull()
.ToString(",\r\n");
var systemVersioning = t.SystemVersioned == null || avoidSystemVersioning || IsPostgres ? null :
$"\r\nWITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {t.SystemVersioned.TableName.OnDatabase(null)}))";
var result = new SqlPreCommandSimple($"CREATE {(IsPostgres && t.Name.IsTemporal ? "TEMPORARY " : "")}TABLE {tableName ?? t.Name}(\r\n{columns}\r\n)" + systemVersioning + ";");
if (!(IsPostgres && t.SystemVersioned != null))
{
return(result);
}
return(new[]
public SqlPreCommand DeleteSqlSync(Entity ident, string comment = null)
{
var pre = OnPreDeleteSqlSync(ident);
var collections = (from tml in this.TablesMList()
select new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}"
.FormatWith(tml.Name, tml.BackReference.Name.SqlEscape(), ident.Id, comment ?? ident.ToString()))).Combine(Spacing.Simple);
var main = new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}"
.FormatWith(Name, this.PrimaryKey.Name.SqlEscape(), ident.Id, comment ?? ident.ToString()));
return SqlPreCommand.Combine(Spacing.Simple, pre, collections, main);
}
public SqlPreCommand DeleteSqlSync(IdentifiableEntity ident, string comment = null)
{
var pre = OnPreDeleteSqlSync(ident);
var collections = (from m in this.Fields
let ml = m.Value.Field as FieldMList
where ml != null
select new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}"
.Formato(ml.TableMList.Name, ml.TableMList.BackReference.Name.SqlEscape(), ident.Id, comment ?? ident.ToString()))).Combine(Spacing.Simple);
var main = new SqlPreCommandSimple("DELETE {0} WHERE {1} = {2} --{3}"
.Formato(Name, "Id", ident.Id, comment ?? ident.ToString()));
return SqlPreCommand.Combine(Spacing.Simple, pre, collections, main);
}
protected static void Log(SqlPreCommandSimple pcs)
{
var log = logger.Value;
if (log != null)
{
log.WriteLine(pcs.Sql);
if (pcs.Parameters != null)
log.WriteLine(pcs.Parameters
.ToString(p => "{0} {1}: {2}".FormatWith(
p.ParameterName,
Connector.Current.GetSqlDbType(p),
p.Value?.Let(v => CSharpRenderer.Value(v, v.GetType(), null))), "\r\n"));
log.WriteLine();
}
}
public static SqlPreCommand CreateIndex(Index index)
{
string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
if (!(index is UniqueIndex))
{
return(new SqlPreCommandSimple("CREATE INDEX {0} ON {1}({2})".FormatWith(
index.IndexName,
index.Table.Name,
columns)));
}
else
{
var uIndex = (UniqueIndex)index;
if (string.IsNullOrEmpty(uIndex.Where))
{
return(new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".FormatWith(
uIndex is UniqueIndex ? "UNIQUE " : null,
uIndex.IndexName,
uIndex.Table.Name,
columns)));
}
if (uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}"
.FormatWith(viewName, columns, uIndex.Table.Name.ToString(), uIndex.Where))
{
GoBefore = true, GoAfter = true
};
SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})"
.FormatWith(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ")));
return(SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql));
}
else
{
return(new SqlPreCommandSimple("CREATE UNIQUE INDEX {0} ON {1}({2}) WHERE {3}".FormatWith(
uIndex.IndexName,
uIndex.Table.Name,
columns, uIndex.Where)));
}
}
}
public static SqlPreCommand MoveRows(ObjectName oldTable, ObjectName newTable, IEnumerable <string> columnNames)
{
SqlPreCommandSimple command = new SqlPreCommandSimple(
@"INSERT INTO {0} ({2})
SELECT {3}
FROM {1} as [table]".FormatWith(
newTable,
oldTable,
columnNames.ToString(a => a.SqlEscape(), ", "),
columnNames.ToString(a => "[table]." + a.SqlEscape(), ", ")));
return(SqlPreCommand.Combine(Spacing.Simple,
new SqlPreCommandSimple("SET IDENTITY_INSERT {0} ON".FormatWith(newTable)),
command,
new SqlPreCommandSimple("SET IDENTITY_INSERT {0} OFF".FormatWith(newTable))));
}
public static SqlPreCommand AlterTableAlterColumn(ITable table, IColumn column, string?defaultConstraintName = null, ObjectName?forceTableName = null)
{
var alterColumn = new SqlPreCommandSimple("ALTER TABLE {0} ALTER COLUMN {1}".FormatWith(forceTableName ?? table.Name, CreateColumn(column, null, isChange: true)));
if (column.Default == null)
{
return(alterColumn);
}
var defCons = GetDefaultConstaint(table, column) !;
return(SqlPreCommand.Combine(Spacing.Simple,
AlterTableDropConstraint(table.Name, defaultConstraintName ?? defCons.Name),
alterColumn,
AlterTableAddDefaultConstraint(table.Name, defCons)
) !);
}
protected internal override DbDataReader UnsafeExecuteDataReader(SqlPreCommandSimple preCommand)
{
try
{
SqlCeCommand cmd = NewCommand(preCommand, null);
return(cmd.ExecuteReader());
}
catch (SqlCeException ex)
{
var nex = HandleException(ex);
if (nex == ex)
{
throw;
}
throw nex;
}
}
protected static void Log(SqlPreCommandSimple pcs)
{
var log = logger.Value;
if (log != null)
{
log.WriteLine(pcs.Sql);
if (pcs.Parameters != null)
{
log.WriteLine(pcs.Parameters
.ToString(p => "{0} {1}: {2}".FormatWith(
p.ParameterName,
Connector.Current.GetSqlDbType(p),
p.Value?.Let(v => CSharpRenderer.Value(v, v.GetType(), null))), "\r\n"));
}
log.WriteLine();
}
}
public static SqlPreCommand CreateIndex(Index index, Replacements?checkUnique)
{
if (index is PrimaryClusteredIndex)
{
var columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
return(new SqlPreCommandSimple($"ALTER TABLE {index.Table.Name} ADD CONSTRAINT {index.IndexName} PRIMARY KEY CLUSTERED({columns})"));
}
if (index is UniqueIndex uIndex)
{
if (uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
var columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
SqlPreCommandSimple viewSql = new SqlPreCommandSimple($"CREATE VIEW {viewName} WITH SCHEMABINDING AS SELECT {columns} FROM {uIndex.Table.Name.ToString()} WHERE {uIndex.Where}")
{
GoBefore = true, GoAfter = true
};
SqlPreCommandSimple indexSql = new SqlPreCommandSimple($"CREATE UNIQUE CLUSTERED INDEX {uIndex.IndexName} ON {viewName}({columns})");
return(SqlPreCommand.Combine(Spacing.Simple,
checkUnique != null ? RemoveDuplicatesIfNecessary(uIndex, checkUnique) : null,
viewSql,
indexSql) !);
}
else
{
return(SqlPreCommand.Combine(Spacing.Double,
checkUnique != null ? RemoveDuplicatesIfNecessary(uIndex, checkUnique) : null,
CreateIndexBasic(index, false)) !);
}
}
else
{
return(CreateIndexBasic(index, forHistoryTable: false));
}
}
protected internal override DbDataReaderWithCommand UnsafeExecuteDataReader(SqlPreCommandSimple preCommand, CommandType commandType)
{
try
{
var cmd = NewCommand(preCommand, null, commandType);
var reader = cmd.ExecuteReader();
return(new DbDataReaderWithCommand(cmd, reader));
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
internal static SqlPreCommand CopyData(ITable newTable, DiffTable oldTable, Replacements rep)
{
var selectColumns = newTable.Columns
.Select(col => oldTable.Columns.TryGetC(col.Key)?.Name ?? GetDefaultValue(newTable, col.Value, rep))
.ToString(", ");
var insertSelect = new SqlPreCommandSimple(
$@"INSERT INTO {newTable.Name} ({newTable.Columns.Values.ToString(a => a.Name, ", ")})
SELECT {selectColumns}
FROM {oldTable.Name}");
if (!newTable.PrimaryKey.Identity)
{
return(insertSelect);
}
return(SqlPreCommand.Combine(Spacing.Simple,
SqlBuilder.SetIdentityInsert(newTable.Name, true),
insertSelect,
SqlBuilder.SetIdentityInsert(newTable.Name, false)
));
}
protected internal override int ExecuteNonQuery(SqlPreCommandSimple preCommand)
{
using (SqlCeConnection con = EnsureConnection())
using (SqlCeCommand cmd = NewCommand(preCommand, con))
using (HeavyProfiler.Log("SQL", () => cmd.CommandText))
{
try
{
int result = cmd.ExecuteNonQuery();
return(result);
}
catch (SqlCeException ex)
{
var nex = HandleException(ex);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}
NpgsqlCommand NewCommand(SqlPreCommandSimple preCommand, NpgsqlConnection?overridenConnection, CommandType commandType)
{
NpgsqlCommand cmd = new NpgsqlCommand {
CommandType = commandType
};
int?timeout = Connector.ScopeTimeout ?? CommandTimeout;
if (timeout.HasValue)
{
cmd.CommandTimeout = timeout.Value;
}
if (overridenConnection != null)
{
cmd.Connection = overridenConnection;
}
else
{
cmd.Connection = (NpgsqlConnection)Transaction.CurrentConnection !;
cmd.Transaction = (NpgsqlTransaction)Transaction.CurrentTransaccion !;
}
cmd.CommandText = preCommand.Sql;
if (preCommand.Parameters != null)
{
foreach (NpgsqlParameter param in preCommand.Parameters)
{
cmd.Parameters.Add(param);
}
}
Log(preCommand);
return(cmd);
}
public static SqlPreCommand SynchronizeTablesScript(Replacements replacements)
{
Dictionary <string, ITable> model = Schema.Current.GetDatabaseTables().ToDictionaryEx(a => a.Name.ToString(), "schema tables");
HashSet <SchemaName> modelSchemas = Schema.Current.GetDatabaseTables().Select(a => a.Name.Schema).Where(a => !SqlBuilder.SystemSchemas.Contains(a.Name)).ToHashSet();
Dictionary <string, DiffTable> database = DefaultGetDatabaseDescription(Schema.Current.DatabaseNames());
HashSet <SchemaName> databaseSchemas = DefaultGetSchemas(Schema.Current.DatabaseNames());
if (SimplifyDiffTables != null)
{
SimplifyDiffTables(database);
}
replacements.AskForReplacements(database.Keys.ToHashSet(), model.Keys.ToHashSet(), Replacements.KeyTables);
database = replacements.ApplyReplacementsToOld(database, Replacements.KeyTables);
Dictionary <ITable, Dictionary <string, Index> > modelIndices = model.Values
.ToDictionary(t => t, t => t.GeneratAllIndexes().ToDictionaryEx(a => a.IndexName, "Indexes for {0}".FormatWith(t.Name)));
model.JoinDictionaryForeach(database, (tn, tab, diff) =>
{
var key = Replacements.KeyColumnsForTable(tn);
replacements.AskForReplacements(diff.Columns.Keys.ToHashSet(), tab.Columns.Keys.ToHashSet(), key);
diff.Columns = replacements.ApplyReplacementsToOld(diff.Columns, key);
diff.Indices = ApplyIndexAutoReplacements(diff, tab, modelIndices[tab]);
});
Func <ObjectName, ObjectName> ChangeName = (ObjectName objectName) =>
{
string name = replacements.Apply(Replacements.KeyTables, objectName.ToString());
return(model.TryGetC(name)?.Name ?? objectName);
};
Func <ObjectName, SqlPreCommand> DeleteAllForeignKey = tableName =>
{
var dropFks = (from t in database.Values
from c in t.Columns.Values
where c.ForeignKey != null && c.ForeignKey.TargetTable.Equals(tableName)
select SqlBuilder.AlterTableDropConstraint(t.Name, c.ForeignKey.Name)).Combine(Spacing.Simple);
if (dropFks == null)
{
return(null);
}
return(SqlPreCommand.Combine(Spacing.Simple, new SqlPreCommandSimple("---In order to remove the PK of " + tableName.Name), dropFks));
};
using (replacements.WithReplacedDatabaseName())
{
SqlPreCommand createSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas",
modelSchemas.ToDictionary(a => a.ToString()),
databaseSchemas.ToDictionary(a => a.ToString()),
(_, newSN) => SqlBuilder.CreateSchema(newSN),
null,
(_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.CreateSchema(newSN),
Spacing.Double);
//use database without replacements to just remove indexes
SqlPreCommand dropStatistics =
Synchronizer.SynchronizeScript(model, database,
null,
(tn, dif) => SqlBuilder.DropStatistics(tn, dif.Stats),
(tn, tab, dif) =>
{
var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet();
return(SqlBuilder.DropStatistics(tn, dif.Stats.Where(a => a.Columns.Any(removedColums.Contains)).ToList()));
},
Spacing.Double);
SqlPreCommand dropIndices =
Synchronizer.SynchronizeScript(model, database,
null,
(tn, dif) => dif.Indices.Values.Where(ix => !ix.IsPrimary).Select(ix => SqlBuilder.DropIndex(dif.Name, ix)).Combine(Spacing.Simple),
(tn, tab, dif) =>
{
Dictionary <string, Index> modelIxs = modelIndices[tab];
var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet();
var changes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices,
null,
(i, dix) => dix.Columns.Any(removedColums.Contains) || dix.IsControlledIndex ? SqlBuilder.DropIndex(dif.Name, dix) : null,
(i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlPreCommand.Combine(Spacing.Double, dix.IsPrimary ? DeleteAllForeignKey(dif.Name) : null, SqlBuilder.DropIndex(dif.Name, dix)) : null,
Spacing.Simple);
return(changes);
},
Spacing.Double);
SqlPreCommand dropForeignKeys = Synchronizer.SynchronizeScript(
model,
database,
null,
(tn, dif) => dif.Columns.Values.Select(c => c.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, c.ForeignKey.Name) : null)
.Concat(dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name))).Combine(Spacing.Simple),
(tn, tab, dif) => SqlPreCommand.Combine(Spacing.Simple,
Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
null,
(cn, colDb) => colDb.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) : null,
(cn, colModel, colDb) => colDb.ForeignKey == null ? null :
colModel.ReferenceTable == null || colModel.AvoidForeignKey || !colModel.ReferenceTable.Name.Equals(ChangeName(colDb.ForeignKey.TargetTable)) ?
SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) :
null, Spacing.Simple),
dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name)).Combine(Spacing.Simple)),
Spacing.Double);
SqlPreCommand tables =
Synchronizer.SynchronizeScript(
model,
database,
(tn, tab) => SqlBuilder.CreateTableSql(tab),
(tn, dif) => SqlBuilder.DropTable(dif.Name),
(tn, tab, dif) =>
SqlPreCommand.Combine(Spacing.Simple,
!object.Equals(dif.Name, tab.Name) ? SqlBuilder.RenameOrMove(dif, tab) : null,
Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
(cn, tabCol) => SqlPreCommandSimple.Combine(Spacing.Simple,
tabCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null,
AlterTableAddColumnDefault(tab, tabCol, replacements)),
(cn, difCol) => SqlPreCommandSimple.Combine(Spacing.Simple,
difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, difCol.Name) : null,
SqlBuilder.AlterTableDropColumn(tab, cn)),
(cn, tabCol, difCol) => SqlPreCommand.Combine(Spacing.Simple,
difCol.Name == tabCol.Name ? null : SqlBuilder.RenameColumn(tab, difCol.Name, tabCol.Name),
difCol.ColumnEquals(tabCol, ignorePrimaryKey: true) ? null : SqlPreCommand.Combine(Spacing.Simple,
tabCol.PrimaryKey && !difCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null,
SqlBuilder.AlterTableAlterColumn(tab, tabCol),
tabCol.SqlDbType == SqlDbType.NVarChar && difCol.SqlDbType == SqlDbType.NChar ? SqlBuilder.UpdateTrim(tab, tabCol) : null),
difCol.DefaultEquals(tabCol) ? null : SqlPreCommand.Combine(Spacing.Simple,
difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, tabCol.Name) : null,
tabCol.Default != null ? SqlBuilder.AddDefaultConstraint(tab.Name, tabCol.Name, tabCol.Default) : null),
UpdateByFkChange(tn, difCol, tabCol, ChangeName)),
Spacing.Simple)),
Spacing.Double);
if (tables != null)
{
tables.GoAfter = true;
}
var tableReplacements = replacements.TryGetC(Replacements.KeyTables);
if (tableReplacements != null)
{
replacements[Replacements.KeyTablesInverse] = tableReplacements.Inverse();
}
SqlPreCommand syncEnums;
try
{
syncEnums = SynchronizeEnumsScript(replacements);
}
catch (Exception e)
{
syncEnums = new SqlPreCommandSimple("-- Exception synchronizing enums: " + e.Message);
}
SqlPreCommand addForeingKeys = Synchronizer.SynchronizeScript(
model,
database,
(tn, tab) => SqlBuilder.AlterTableForeignKeys(tab),
null,
(tn, tab, dif) => Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
(cn, colModel) => colModel.ReferenceTable == null || colModel.AvoidForeignKey ? null :
SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable),
null,
(cn, colModel, coldb) =>
{
if (colModel.ReferenceTable == null || colModel.AvoidForeignKey)
{
return(null);
}
if (coldb.ForeignKey == null || !colModel.ReferenceTable.Name.Equals(ChangeName(coldb.ForeignKey.TargetTable)))
{
return(SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable));
}
var name = SqlBuilder.ForeignKeyName(tab.Name.Name, colModel.Name);
return(SqlPreCommand.Combine(Spacing.Simple,
name != coldb.ForeignKey.Name.Name ? SqlBuilder.RenameForeignKey(coldb.ForeignKey.Name, name) : null,
(coldb.ForeignKey.IsDisabled || coldb.ForeignKey.IsNotTrusted) && !replacements.SchemaOnly ? SqlBuilder.EnableForeignKey(tab.Name, name) : null));
},
Spacing.Simple),
Spacing.Double);
bool?createMissingFreeIndexes = null;
SqlPreCommand addIndices =
Synchronizer.SynchronizeScript(model, database,
(tn, tab) => modelIndices[tab].Values.Where(a => !(a is PrimaryClusteredIndex)).Select(SqlBuilder.CreateIndex).Combine(Spacing.Simple),
null,
(tn, tab, dif) =>
{
var columnReplacements = replacements.TryGetC(Replacements.KeyColumnsForTable(tn));
Func <IColumn, bool> isNew = c => !dif.Columns.ContainsKey(columnReplacements?.TryGetC(c.Name) ?? c.Name);
Dictionary <string, Index> modelIxs = modelIndices[tab];
var controlledIndexes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices,
(i, mix) => mix is UniqueIndex || mix.Columns.Any(isNew) || SafeConsole.Ask(ref createMissingFreeIndexes, "Create missing non-unique index {0} in {1}?".FormatWith(mix.IndexName, tab.Name)) ? SqlBuilder.CreateIndex(mix) : null,
null,
(i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlBuilder.CreateIndex(mix) :
mix.IndexName != dix.IndexName ? SqlBuilder.RenameIndex(tab, dix.IndexName, mix.IndexName) : null,
Spacing.Simple);
return(SqlPreCommand.Combine(Spacing.Simple, controlledIndexes));
}, Spacing.Double);
SqlPreCommand dropSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas",
modelSchemas.ToDictionary(a => a.ToString()),
databaseSchemas.ToDictionary(a => a.ToString()),
null,
(_, oldSN) => DropSchema(oldSN) ? SqlBuilder.DropSchema(oldSN) : null,
(_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.DropSchema(oldSN),
Spacing.Double);
return(SqlPreCommand.Combine(Spacing.Triple, createSchemas, dropStatistics, dropIndices, dropForeignKeys, tables, syncEnums, addForeingKeys, addIndices, dropSchemas));
}
}
protected internal override DataTable ExecuteDataTable(SqlPreCommandSimple preCommand)
{
using (SqlCeConnection con = EnsureConnection())
using (SqlCeCommand cmd = NewCommand(preCommand, con))
using (HeavyProfiler.Log("SQL", () => cmd.CommandText))
{
try
{
SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
DataTable result = new DataTable();
da.Fill(result);
return result;
}
catch (SqlCeException ex)
{
var nex = HandleException(ex);
if (nex == ex)
throw;
throw nex;
}
}
}
protected internal override async Task <DbDataReaderWithCommand> UnsafeExecuteDataReaderAsync(SqlPreCommandSimple preCommand, CommandType commandType, CancellationToken token)
{
try
{
var cmd = NewCommand(preCommand, null, commandType);
var reader = await cmd.ExecuteReaderAsync(token);
return(new DbDataReaderWithCommand(cmd, reader));
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
protected internal abstract int ExecuteNonQuery(SqlPreCommandSimple preCommand, CommandType commandType);
public static SqlPreCommand CreateIndex(Index index)
{
string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
if (!(index is UniqueIndex))
{
return new SqlPreCommandSimple("CREATE INDEX {0} ON {1}({2})".Formato(
index.IndexName,
index.Table.Name,
columns));
}
else
{
var uIndex = (UniqueIndex)index;
if (string.IsNullOrEmpty(uIndex.Where))
{
return new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".Formato(
uIndex is UniqueIndex ? "UNIQUE " : null,
uIndex.IndexName,
uIndex.Table.Name,
columns));
}
if (uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}"
.Formato(viewName, columns, uIndex.Table.Name.ToStringDbo(), uIndex.Where)) { AddGo = true };
SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})"
.Formato(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ")));
return SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql);
}
else
{
return new SqlPreCommandSimple("CREATE UNIQUE INDEX {0} ON {1}({2}) WHERE {3}".Formato(
uIndex.IndexName,
uIndex.Table.Name,
columns, uIndex.Where));
}
}
}
protected internal abstract DataSet ExecuteDataSet(SqlPreCommandSimple sqlPreCommandSimple, CommandType commandType);
protected internal abstract DataTable ExecuteDataTable(SqlPreCommandSimple command, CommandType commandType);
public void ExecuteDataReaderDependency(SqlPreCommandSimple preCommand, OnChangeEventHandler change, Action reconect, Action <FieldReader> forEach, CommandType commandType)
{
bool reconected = false;
retry:
try
{
using (SqlConnection con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL-Dependency"))
using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql()))
{
try
{
if (change != null)
{
SqlDependency dep = new SqlDependency(cmd);
dep.OnChange += change;
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
FieldReader fr = new FieldReader(reader);
int row = -1;
try
{
while (reader.Read())
{
row++;
forEach(fr);
}
}
catch (Exception ex)
{
FieldReaderException fieldEx = fr.CreateFieldReaderException(ex);
fieldEx.Command = preCommand;
fieldEx.Row = row;
throw fieldEx;
}
}
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
{
throw;
}
throw nex;
}
}
}
catch (InvalidOperationException ioe)
{
if (ioe.Message.Contains("SqlDependency.Start()") && !reconected)
{
reconect();
reconected = true;
goto retry;
}
throw;
}
}
public void ExecuteDataReaderDependency(SqlPreCommandSimple preCommand, OnChangeEventHandler change, Action reconect, Action<FieldReader> forEach, CommandType commandType)
{
bool reconected = false;
retry:
try
{
using (SqlConnection con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL-Dependency"))
using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql()))
{
try
{
if (change != null)
{
SqlDependency dep = new SqlDependency(cmd);
dep.OnChange += change;
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
FieldReader fr = new FieldReader(reader);
int row = -1;
try
{
while (reader.Read())
{
row++;
forEach(fr);
}
}
catch (Exception ex)
{
FieldReaderException fieldEx = fr.CreateFieldReaderException(ex);
fieldEx.Command = preCommand;
fieldEx.Row = row;
throw fieldEx;
}
}
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
throw;
throw nex;
}
}
}
catch (InvalidOperationException ioe)
{
if (ioe.Message.Contains("SqlDependency.Start()") && !reconected)
{
reconect();
reconected = true;
goto retry;
}
throw;
}
}
protected internal override int ExecuteNonQuery(SqlPreCommandSimple preCommand, CommandType commandType)
{
using (SqlConnection con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql()))
{
try
{
int result = cmd.ExecuteNonQuery();
return result;
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
throw;
throw nex;
}
}
}
protected internal override object ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType)
{
using (SqlConnection con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql()))
{
try
{
object result = cmd.ExecuteScalar();
if (result == null || result == DBNull.Value)
return null;
return result;
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
throw;
throw nex;
}
}
}
protected internal abstract object ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType);
protected internal abstract object ExecuteScalar(SqlPreCommandSimple preCommand, CommandType commandType);
protected internal override object ExecuteScalar(SqlPreCommandSimple preCommand)
{
using (SqlCeConnection con = EnsureConnection())
using (SqlCeCommand cmd = NewCommand(preCommand, con))
using (HeavyProfiler.Log("SQL", () => cmd.CommandText))
{
try
{
if (cmd.CommandText.EndsWith(selecctInsertedId))
{
cmd.CommandText = cmd.CommandText.RemoveEnd(selecctInsertedId.Length);
cmd.ExecuteNonQuery();
cmd.CommandText = selecctInsertedId;
object result = cmd.ExecuteScalar();
if (result == null || result == DBNull.Value)
return null;
return result;
}
else if (cmd.CommandText.EndsWith(selectRowCount))
{
cmd.CommandText = cmd.CommandText.RemoveEnd(selectRowCount.Length);
cmd.ExecuteNonQuery();
cmd.CommandText = selectRowCount;
object result = cmd.ExecuteScalar();
if (result == null || result == DBNull.Value)
return null;
return result;
}
else
{
object result = cmd.ExecuteScalar();
if (result == null || result == DBNull.Value)
return null;
return result;
}
}
catch (SqlCeException ex)
{
var nex = HandleException(ex);
if (nex == ex)
throw;
throw nex;
}
}
}
protected internal abstract int ExecuteNonQuery(SqlPreCommandSimple preCommand, CommandType commandType);
Exception ReplaceException(Exception ex, SqlPreCommandSimple command)
{
var se = ex as SqlException;
if (se != null)
{
switch (se.Number)
{
case -2: return new TimeoutException(ex.Message, ex);
case 2601: return new UniqueKeyException(ex);
case 547: return new ForeignKeyException(ex);
default: return ex;
}
}
var ste = ex as SqlTypeException;
if (ste != null && ex.Message.Contains("DateTime"))
{
var mins = command.Parameters.Where(a => DateTime.MinValue.Equals(a.Value));
if (mins.Any())
{
return new ArgumentOutOfRangeException("{0} {1} not initialized and equal to DateTime.MinValue".FormatWith(
mins.CommaAnd(a => a.ParameterName),
mins.Count() == 1 ? "is" : "are"), ex);
}
}
return ex;
}
protected internal abstract DbDataReader UnsafeExecuteDataReader(SqlPreCommandSimple sqlPreCommandSimple, CommandType commandType);
public static SqlPreCommand SynchronizeTablesScript(Replacements replacements)
{
Schema s = Schema.Current;
Dictionary<string, ITable> model = s.GetDatabaseTables().Where(t => !s.IsExternalDatabase(t.Name.Schema.Database)).ToDictionaryEx(a => a.Name.ToString(), "schema tables");
HashSet<SchemaName> modelSchemas = model.Values.Select(a => a.Name.Schema).Where(a => !SqlBuilder.SystemSchemas.Contains(a.Name)).ToHashSet();
Dictionary<string, DiffTable> database = DefaultGetDatabaseDescription(s.DatabaseNames());
HashSet<SchemaName> databaseSchemas = DefaultGetSchemas(s.DatabaseNames());
if (SimplifyDiffTables != null)
SimplifyDiffTables(database);
replacements.AskForReplacements(database.Keys.ToHashSet(), model.Keys.ToHashSet(), Replacements.KeyTables);
database = replacements.ApplyReplacementsToOld(database, Replacements.KeyTables);
Dictionary<ITable, Dictionary<string, Index>> modelIndices = model.Values
.ToDictionary(t => t, t => t.GeneratAllIndexes().ToDictionaryEx(a => a.IndexName, "Indexes for {0}".FormatWith(t.Name)));
model.JoinDictionaryForeach(database, (tn, tab, diff) =>
{
var key = Replacements.KeyColumnsForTable(tn);
replacements.AskForReplacements(diff.Columns.Keys.ToHashSet(), tab.Columns.Keys.ToHashSet(), key);
diff.Columns = replacements.ApplyReplacementsToOld(diff.Columns, key);
diff.Indices = ApplyIndexAutoReplacements(diff, tab, modelIndices[tab]);
});
Func<ObjectName, ObjectName> ChangeName = (ObjectName objectName) =>
{
string name = replacements.Apply(Replacements.KeyTables, objectName.ToString());
return model.TryGetC(name)?.Name ?? objectName;
};
Func<ObjectName, SqlPreCommand> DeleteAllForeignKey = tableName =>
{
var dropFks = (from t in database.Values
from c in t.Columns.Values
where c.ForeignKey != null && c.ForeignKey.TargetTable.Equals(tableName)
select SqlBuilder.AlterTableDropConstraint(t.Name, c.ForeignKey.Name)).Combine(Spacing.Simple);
if (dropFks == null)
return null;
return SqlPreCommand.Combine(Spacing.Simple, new SqlPreCommandSimple("---In order to remove the PK of " + tableName.Name), dropFks);
};
using (replacements.WithReplacedDatabaseName())
{
SqlPreCommand createSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas",
modelSchemas.ToDictionary(a => a.ToString()),
databaseSchemas.ToDictionary(a => a.ToString()),
(_, newSN) => SqlBuilder.CreateSchema(newSN),
null,
(_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.CreateSchema(newSN),
Spacing.Double);
//use database without replacements to just remove indexes
SqlPreCommand dropStatistics =
Synchronizer.SynchronizeScript(model, database,
null,
(tn, dif) => SqlBuilder.DropStatistics(tn, dif.Stats),
(tn, tab, dif) =>
{
var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet();
return SqlBuilder.DropStatistics(tn, dif.Stats.Where(a => a.Columns.Any(removedColums.Contains)).ToList());
},
Spacing.Double);
SqlPreCommand dropIndices =
Synchronizer.SynchronizeScript(model, database,
null,
(tn, dif) => dif.Indices.Values.Where(ix => !ix.IsPrimary).Select(ix => SqlBuilder.DropIndex(dif.Name, ix)).Combine(Spacing.Simple),
(tn, tab, dif) =>
{
Dictionary<string, Index> modelIxs = modelIndices[tab];
var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet();
var changes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices,
null,
(i, dix) => dix.Columns.Any(removedColums.Contains) || dix.IsControlledIndex ? SqlBuilder.DropIndex(dif.Name, dix) : null,
(i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlPreCommand.Combine(Spacing.Double, dix.IsPrimary ? DeleteAllForeignKey(dif.Name) : null, SqlBuilder.DropIndex(dif.Name, dix)) : null,
Spacing.Simple);
return changes;
},
Spacing.Double);
SqlPreCommand dropForeignKeys = Synchronizer.SynchronizeScript(
model,
database,
null,
(tn, dif) => dif.Columns.Values.Select(c => c.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, c.ForeignKey.Name) : null)
.Concat(dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name))).Combine(Spacing.Simple),
(tn, tab, dif) => SqlPreCommand.Combine(Spacing.Simple,
Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
null,
(cn, colDb) => colDb.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) : null,
(cn, colModel, colDb) => colDb.ForeignKey == null ? null :
colModel.ReferenceTable == null || colModel.AvoidForeignKey || !colModel.ReferenceTable.Name.Equals(ChangeName(colDb.ForeignKey.TargetTable)) ?
SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) :
null, Spacing.Simple),
dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name)).Combine(Spacing.Simple)),
Spacing.Double);
SqlPreCommand tables =
Synchronizer.SynchronizeScript(
model,
database,
(tn, tab) => SqlBuilder.CreateTableSql(tab),
(tn, dif) => SqlBuilder.DropTable(dif.Name),
(tn, tab, dif) =>
SqlPreCommand.Combine(Spacing.Simple,
!object.Equals(dif.Name, tab.Name) ? SqlBuilder.RenameOrMove(dif, tab) : null,
Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
(cn, tabCol) => SqlPreCommandSimple.Combine(Spacing.Simple,
tabCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null,
AlterTableAddColumnDefault(tab, tabCol, replacements)),
(cn, difCol) => SqlPreCommandSimple.Combine(Spacing.Simple,
difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, difCol.Name) : null,
SqlBuilder.AlterTableDropColumn(tab, cn)),
(cn, tabCol, difCol) => SqlPreCommand.Combine(Spacing.Simple,
difCol.Name == tabCol.Name ? null : SqlBuilder.RenameColumn(tab, difCol.Name, tabCol.Name),
difCol.ColumnEquals(tabCol, ignorePrimaryKey: true) ? null : SqlPreCommand.Combine(Spacing.Simple,
tabCol.PrimaryKey && !difCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null,
SqlBuilder.AlterTableAlterColumn(tab, tabCol),
tabCol.SqlDbType == SqlDbType.NVarChar && difCol.SqlDbType == SqlDbType.NChar ? SqlBuilder.UpdateTrim(tab, tabCol) : null),
difCol.DefaultEquals(tabCol) ? null : SqlPreCommand.Combine(Spacing.Simple,
difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, tabCol.Name) : null,
tabCol.Default != null ? SqlBuilder.AddDefaultConstraint(tab.Name, tabCol.Name, tabCol.Default) : null),
UpdateByFkChange(tn, difCol, tabCol, ChangeName)),
Spacing.Simple)),
Spacing.Double);
if (tables != null)
tables.GoAfter = true;
var tableReplacements = replacements.TryGetC(Replacements.KeyTables);
if (tableReplacements != null)
replacements[Replacements.KeyTablesInverse] = tableReplacements.Inverse();
SqlPreCommand syncEnums;
try
{
syncEnums = SynchronizeEnumsScript(replacements);
}
catch(Exception e)
{
syncEnums = new SqlPreCommandSimple("-- Exception synchronizing enums: " + e.Message);
}
SqlPreCommand addForeingKeys = Synchronizer.SynchronizeScript(
model,
database,
(tn, tab) => SqlBuilder.AlterTableForeignKeys(tab),
null,
(tn, tab, dif) => Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
(cn, colModel) => colModel.ReferenceTable == null || colModel.AvoidForeignKey ? null :
SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable),
null,
(cn, colModel, coldb) =>
{
if (colModel.ReferenceTable == null || colModel.AvoidForeignKey)
return null;
if (coldb.ForeignKey == null || !colModel.ReferenceTable.Name.Equals(ChangeName(coldb.ForeignKey.TargetTable)))
return SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable);
var name = SqlBuilder.ForeignKeyName(tab.Name.Name, colModel.Name);
return SqlPreCommand.Combine(Spacing.Simple,
name != coldb.ForeignKey.Name.Name ? SqlBuilder.RenameForeignKey(coldb.ForeignKey.Name, name) : null,
(coldb.ForeignKey.IsDisabled || coldb.ForeignKey.IsNotTrusted) && !replacements.SchemaOnly ? SqlBuilder.EnableForeignKey(tab.Name, name) : null);
},
Spacing.Simple),
Spacing.Double);
bool? createMissingFreeIndexes = null;
SqlPreCommand addIndices =
Synchronizer.SynchronizeScript(model, database,
(tn, tab) => modelIndices[tab].Values.Where(a => !(a is PrimaryClusteredIndex)).Select(SqlBuilder.CreateIndex).Combine(Spacing.Simple),
null,
(tn, tab, dif) =>
{
var columnReplacements = replacements.TryGetC(Replacements.KeyColumnsForTable(tn));
Func<IColumn, bool> isNew = c => !dif.Columns.ContainsKey(columnReplacements?.TryGetC(c.Name) ?? c.Name);
Dictionary<string, Index> modelIxs = modelIndices[tab];
var controlledIndexes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices,
(i, mix) => mix is UniqueIndex || mix.Columns.Any(isNew) || SafeConsole.Ask(ref createMissingFreeIndexes, "Create missing non-unique index {0} in {1}?".FormatWith(mix.IndexName, tab.Name)) ? SqlBuilder.CreateIndex(mix) : null,
null,
(i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlBuilder.CreateIndex(mix) :
mix.IndexName != dix.IndexName ? SqlBuilder.RenameIndex(tab, dix.IndexName, mix.IndexName) : null,
Spacing.Simple);
return SqlPreCommand.Combine(Spacing.Simple, controlledIndexes);
}, Spacing.Double);
SqlPreCommand dropSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas",
modelSchemas.ToDictionary(a => a.ToString()),
databaseSchemas.ToDictionary(a => a.ToString()),
null,
(_, oldSN) => DropSchema(oldSN) ? SqlBuilder.DropSchema(oldSN) : null,
(_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.DropSchema(oldSN),
Spacing.Double);
return SqlPreCommand.Combine(Spacing.Triple, createSchemas, dropStatistics, dropIndices, dropForeignKeys, tables, syncEnums, addForeingKeys, addIndices, dropSchemas);
}
}
public static SqlPreCommand MoveRows(ObjectName oldTable, ObjectName newTable, IEnumerable<string> columnNames)
{
SqlPreCommandSimple command = new SqlPreCommandSimple(
@"INSERT INTO {0} ({2})
SELECT {3}
FROM {1} as [table]".Formato(
newTable,
oldTable,
columnNames.ToString(a => a.SqlEscape(), ", "),
columnNames.ToString(a => "[table]." + a.SqlEscape(), ", ")));
return SqlPreCommand.Combine(Spacing.Simple,
new SqlPreCommandSimple("SET IDENTITY_INSERT {0} ON".Formato(newTable)),
command,
new SqlPreCommandSimple("SET IDENTITY_INSERT {0} OFF".Formato(newTable)));
}
protected internal abstract DbDataReader UnsafeExecuteDataReader(SqlPreCommandSimple sqlPreCommandSimple, CommandType commandType);
protected internal override DbDataReader UnsafeExecuteDataReader(SqlPreCommandSimple preCommand, CommandType commandType)
{
try
{
SqlCommand cmd = NewCommand(preCommand, null, commandType);
return cmd.ExecuteReader();
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
throw;
throw nex;
}
}
protected internal abstract DataTable ExecuteDataTable(SqlPreCommandSimple command, CommandType commandType);
protected internal override DataSet ExecuteDataSet(SqlPreCommandSimple preCommand, CommandType commandType)
{
using (SqlConnection con = EnsureConnection())
using (SqlCommand cmd = NewCommand(preCommand, con, commandType))
using (HeavyProfiler.Log("SQL", () => preCommand.PlainSql()))
{
try
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet result = new DataSet();
da.Fill(result);
return result;
}
catch (Exception ex)
{
var nex = HandleException(ex, preCommand);
if (nex == ex)
throw;
throw nex;
}
}
}
public Exception HandleException(Exception ex, SqlPreCommandSimple command)
{
var nex = ReplaceException(ex, command);
nex.Data["Sql"] = command.PlainSql();
return nex;
}
public static SqlPreCommand CreateIndex(Index index)
{
string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
if (index is PrimaryClusteredIndex)
{
return new SqlPreCommandSimple("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY CLUSTERED({2})".FormatWith(
index.Table.Name,
index.IndexName,
columns));
}
if (!(index is UniqueIndex))
{
return new SqlPreCommandSimple("CREATE INDEX {0} ON {1}({2})".FormatWith(
index.IndexName,
index.Table.Name,
columns));
}
var uIndex = (UniqueIndex)index;
if (string.IsNullOrEmpty(uIndex.Where))
{
return new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".FormatWith(
uIndex is UniqueIndex ? "UNIQUE " : null,
uIndex.IndexName,
uIndex.Table.Name,
columns));
}
if (uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}"
.FormatWith(viewName, columns, uIndex.Table.Name.ToString(), uIndex.Where))
{ GoBefore = true, GoAfter = true };
SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})"
.FormatWith(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ")));
return SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql);
}
else
{
return new SqlPreCommandSimple("CREATE UNIQUE INDEX {0} ON {1}({2}) WHERE {3}".FormatWith(
uIndex.IndexName,
uIndex.Table.Name,
columns, uIndex.Where));
}
}
protected internal abstract DataSet ExecuteDataSet(SqlPreCommandSimple sqlPreCommandSimple, CommandType commandType);
