public static bool IsGeneratorExist(this DBSchema schema, string gname)
{
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (gname == null)
{
throw new ArgumentNullException("gname");
}
return(schema.Generators.Any(gen => gname == gen.Name));
}
public static bool IsTableExist(this DBSchema schema, string tableName)
{
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
return(schema.Tables.Any(table => tableName == table.Name));
}
public static TableSchema GetTable(this DBSchema schema, string tableName)
{
if (schema == null)
{
throw new ArgumentNullException("schema");
}
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
return(schema.Tables.FirstOrDefault(table => tableName == table.Name));
}
public static void ReloadTableSchema(SqlConnection conn, DBSchema schema, TableSchema table)
{
string[] restrict4 = { null, null, table.Name, "TABLE" };
var dtTables = SqlSchemaFactory.GetSchema(conn, "Tables", restrict4);
if (dtTables.Rows.Count == 0)
{
schema.Tables.Remove(table);
table.Name += "_FANTOM";
table.Columns = new TableColumnSchema[0];
table.Keys = new KeySchema[0];
table.Indexes = new IndexSchema[0];
return;
}
var freshTable = GetTable(conn, dtTables.Rows[0]);
table.Columns = freshTable.Columns;
table.Keys = GetKeys(conn, table).ToArray();
table.Indexes = GetIndexes(conn, table).ToArray();
}
public static DBSchema LoadSchema(string constr)
{
var csb = new SqlConnectionStringBuilder(constr);
using (var con = new SqlConnection(csb.ConnectionString))
{
con.Open();
var dbsc = new DBSchema
{
Name = csb.InitialCatalog,
Tables = GetTables(con),
Functions = GetFunctions(con),
};
foreach (var table in dbsc.Tables)
{
table.Keys = GetKeys(con, table).ToArray();
table.Indexes = GetIndexes(con, table).ToArray();
}
return dbsc;
}
}
protected override DBSchema DbscCopyPrepare(DBSchema schema)
{
return schema.Copy();
}
protected override void AfterSchemaComparision(DBSchema existingSchema, DBSchema targetSchema)
{
var i = 1;
foreach (var mTable in targetSchema.Tables)
{
var eTable = existingSchema.GetTable(mTable.Name);
if (eTable != null)
{
var recreate = false;
var toident = false;
var notexact = false;
var extracolumns = false;
#region Columns scan
foreach (var mColumn in mTable.Columns)
{
var eColumn = eTable.GetColumn(mColumn.Name);
if (eColumn == null)
{
extracolumns = true;
continue;
}
if (mTable.IsExactColumn(eColumn))
continue;
notexact = true;
if (mColumn.DefaultValue != eColumn.DefaultValue)
{}
else if (mColumn.AutoIncrement != eColumn.AutoIncrement)
{
recreate = true;
toident = mColumn.AutoIncrement;
}
else
recreate = true;
}
if (!recreate)
{
if (notexact)
{
foreach (var mColumn in mTable.Columns)
{
var eColumn = eTable.GetColumn(mColumn.Name);
if (eColumn == null)
continue;
if (mTable.IsExactColumn(eColumn))
continue;
if (mColumn.DefaultValue == eColumn.DefaultValue)
continue;
if (mColumn.DefaultValue == null)
{
var eKey = eTable.GetKey(ConstraintType.Default, eColumn.Name);
if (eKey != null)
AddDdlCommand(i,
eTable.Name + @"." + eKey.Name,
eKey.KeyType.ToString(),
MakeDdlDefaultDrop(eKey, eTable));
}
else if (eColumn.DefaultValue == null)
{
var mKey = mTable.GetKey(ConstraintType.Default, mColumn.Name);
if (mKey != null)
AddDdlCommand(i,
mTable.Name + @"." + mKey.Name,
mKey.KeyType.ToString(),
MakeDdlDefaultCreate(mKey, eTable));
}
else
{
var eKey = eTable.GetKey(ConstraintType.Default, eColumn.Name);
eKey.Source = mColumn.DefaultValue;
AddDdlCommand(i,
eTable.Name + @"." + eKey.Name,
eKey.KeyType.ToString(),
MakeDdlDefaultDrop(eKey, eTable));
AddDdlCommand(i,
eTable.Name + @"." + eKey.Name,
eKey.KeyType.ToString(),
MakeDdlDefaultCreate(eKey, eTable));
}
}
}
if (extracolumns)
{
foreach (var mColumn in mTable.Columns)
{
if (eTable.IsColumnExist(mColumn.Name))
continue;
if (mColumn.DefaultValue == null)
continue;
var mKey = mTable.GetKey(ConstraintType.Default, mColumn.Name);
if (mKey != null)
AddDdlCommand(i,
mTable.Name + @"." + mKey.Name,
mKey.KeyType.ToString(),
MakeDdlDefaultCreate(mKey, eTable));
}
}
}
foreach (var eColumn in eTable.Columns)
{
var mColumn = mTable.GetColumn(eColumn.Name);
if (mColumn != null)
continue;
var eKey = eTable.GetKey(ConstraintType.Default, eColumn.Name);
if (eKey != null)
AddDdlCommand(i,
eTable.Name + @"." + eKey.Name,
eKey.KeyType.ToString(),
MakeDdlDefaultDrop(eKey, eTable));
}
#endregion
#region Keys scan
//foreach(DBKey eKey in eTable.Keys)
//{
// if(eKey.KeyType == ConstraintType.Default)
// {
// DBKey mKey = mTable.GetKey(eKey.Name);
// if(mKey == null)
// {
// AddDdlCommand(i, eTable.Name + "." + eKey.Name, eKey.KeyType.ToString(), MakeDDLDefaultDrop(eKey, eTable.Name));
// }
// else if(!DBKey.CompareKeys(eKey, mKey))
// {
// AddDdlCommand(i, eTable.Name + "." + eKey.Name, eKey.KeyType.ToString(), MakeDDLDefaultDrop(eKey, eTable.Name));
// AddDdlCommand(i, eTable.Name + "." + eKey.Name, eKey.KeyType.ToString(), MakeDDLDefaultCreate(mKey, eTable.Name));
// }
// }
//}
//foreach(DBKey mKey in mTable.Keys)
//{
// if(mKey.KeyType == ConstraintType.Default)
// {
// DBKey eKey = eTable.GetKey(mKey.Name);
// if(eKey == null)
// {
// AddDdlCommand(i, eTable.Name + "." + eKey.Name, eKey.KeyType.ToString(), MakeDDLDefaultCreate(mKey, eTable.Name));
// }
// }
//}
#endregion
#region Recreate block
if (recreate)
{
DeleteDdlCommandsByFilter(string.Format(@"Name Like '{0}.%'", eTable.Name));
foreach (var eTable1 in existingSchema.Tables)
foreach (var t in eTable1.Keys)
if (t.KeyType == ConstraintType.KeyForeign && t.RelTable == eTable.Name)
{
AddDdlCommand(i,
eTable1.Name + @"." + t.Name,
@"Recreate" + t.KeyType,
MakeDdlKeyDrop(t, eTable1));
AddDdlCommand(i,
eTable1.Name + @"." + t.Name,
@"Recreate" + t.KeyType,
MakeDdlKeyCreateByAlter(t, eTable1));
}
var tTable = mTable.Clone();
tTable.Name = @"Tmp_" + eTable.Name;
AddDdlCommand(i,
tTable.Name,
"RecreateTable",
MakeDdlTableCreate(tTable, false));
foreach (var tIndex in tTable.Indexes)
AddDdlCommand(i,
tTable.Name + @"." + tIndex.Name,
"RecreateIndex",
MakeDdlIndexCreate(tIndex, tTable));
foreach (var tKey in tTable.Keys)
{
if (tKey.KeyType == ConstraintType.Default)
continue;
AddDdlCommand(i,
tTable.Name + @"." + tKey.Name,
@"Recreate" + tKey.KeyType,
MakeDdlKeyCreateByAlter(tKey, tTable));
}
if (toident)
AddDdlCommand(i,
tTable.Name,
"RecreateInsert",
string.Format(@"SET IDENTITY_INSERT {0} ON", tTable.Name));
// Подготовить набор колонок данных.
var eColumns = tTable
.Columns
.Select(col =>
{
if (eTable.IsColumnExist(col.Name))
return col.Name;
if (col.DefaultValue == null)
{
if (!col.Nullable)
throw new DBSchemaException(@"Default value is required but not provided.");
return @"NULL";
}
return @"''" + col.DefaultValue + @"''";
})
.JoinStrings(@", ");
AddDdlCommand(i++,
tTable.Name,
"RecreateInsert",
string.Format(
@"
IF EXISTS(SELECT * FROM {0})
EXEC('INSERT INTO {1} ({2}) SELECT {3} FROM {4} WITH (HOLDLOCK TABLOCKX)')",
eTable.Name,
tTable.Name,
tTable.ColumnsList(MakeDdlElementName),
eColumns,//eTable.ColumnsList(),
eTable.Name));
if (toident)
AddDdlCommand(i++,
tTable.Name,
"RecreateInsert",
string.Format(@"SET IDENTITY_INSERT {0} OFF", tTable.Name));
AddDdlCommand(i,
eTable.Name,
"RecreateTable",
MakeDdlTableDrop(eTable));
AddDdlCommand(i,
tTable.Name,
"RecreateRename",
string.Format(@"EXECUTE sp_rename N'{0}', N'{1}', 'OBJECT'",
tTable.Name, eTable.Name));
}
#endregion
}
i++;
}
}
public override void ReloadTableSchema(IDbConnection conn, DBSchema schema, TableSchema table)
{
MssqlSchemaLoader.ReloadTableSchema((SqlConnection) conn, schema, table);
}