public ValidationResult ValidateSchema(Dialect dialect, DatabaseMetadata databaseMetadata)
{
_configuration.BuildMappings();
var mappings = _configuration.CreateMappings(_dialect);
var mapping = _configuration.BuildMapping();
var result = new ValidationResult();
try
{
var catalog = PropertiesHelper.GetString("default_catalog", _configuration.Properties, null);
var schema = PropertiesHelper.GetString("default_schema", _configuration.Properties, null);
foreach (var table in mappings.IterateTables)
{
if (!table.IsPhysicalTable || !Configuration.IncludeAction(table.SchemaActions, SchemaAction.Validate))
{
continue;
}
var tableMetadata = databaseMetadata.GetTableMetadata(table.Name, table.Schema ?? schema, table.Catalog ?? catalog, table.IsQuoted);
if (tableMetadata == null)
{
result.MissingTables.Add(table);
}
else
{
foreach (var column in table.ColumnIterator)
{
var columnMetadata = tableMetadata.GetColumnMetadata(column.Name);
if (columnMetadata == null)
{
result.AddMissingColumn(table, column);
}
if (!column.GetSqlType(dialect, mapping).ToLower().StartsWith(columnMetadata.TypeName.ToLower()))
{
result.AddInvalidColumn(table, column);
}
}
}
}
}
catch (Exception ex)
{
LogHelper.Error <SchemaChangeValidator>(ex.Message, ex);
result = new ValidationResult(ex);
}
return(result);
}
public void InquireSchema()
{
Dialect.MsSql2005Dialect dialect = new MsSql2005Dialect();
DbConnection conn = new SqlConnection(@"");
conn.Open();
DatabaseMetadata meta = new DatabaseMetadata(conn, dialect);
IDataBaseSchema schema = dialect.GetDataBaseSchema(conn);
var dt = schema.GetTables(null, null, null, new string[0]);
var cols = schema.GetColumns(null, null, null, null);
var keys = schema.GetForeignKeys(null, null, null);
foreach (DataRow r in dt.Rows)
{
var tableMeta = schema.GetTableMetadata(r, true);
Console.WriteLine(string.Format("Table {2}:[{0}].[{1}]", tableMeta.Schema, tableMeta.Name, tableMeta.Catalog));
ITableMetadata tm = meta.GetTableMetadata(tableMeta.Name, tableMeta.Schema, tableMeta.Catalog, false);
IColumnMetadata col = tm.GetColumnMetadata(cols.Rows[0].ItemArray[2] as string);
}
}
public ValidationResult ValidateSchema(Dialect dialect, DatabaseMetadata databaseMetadata)
{
_configuration.BuildMappings();
var mappings = _configuration.CreateMappings(_dialect);
var mapping = _configuration.BuildMapping();
var result = new ValidationResult();
try
{
var catalog = PropertiesHelper.GetString("default_catalog", _configuration.Properties, null);
var schema = PropertiesHelper.GetString("default_schema", _configuration.Properties, null);
foreach (var table in mappings.IterateTables)
{
if (!table.IsPhysicalTable || !Configuration.IncludeAction(table.SchemaActions, SchemaAction.Validate))
continue;
var tableMetadata = databaseMetadata.GetTableMetadata(table.Name, table.Schema ?? schema, table.Catalog ?? catalog, table.IsQuoted);
if (tableMetadata == null)
{
result.MissingTables.Add(table);
}
else
{
foreach (var column in table.ColumnIterator)
{
var columnMetadata = tableMetadata.GetColumnMetadata(column.Name);
if (columnMetadata == null)
{
result.AddMissingColumn(table, column);
}
if (!column.GetSqlType(dialect, mapping).ToLower().StartsWith(columnMetadata.TypeName.ToLower()))
{
result.AddInvalidColumn(table, column);
}
}
}
}
}
catch (Exception ex)
{
LogHelper.Error<SchemaChangeValidator>(ex.Message, ex);
result = new ValidationResult(ex);
}
return result;
}
public void UpgradeSchema()
{
var export = new SchemaUpdate(Configuration);
export.Execute(Debug, true);
//nhibernate не проверяет типы данных и размерность, делаем еще проход для проверки типов данных
using (var connectionProvider = ConnectionProviderFactory.NewConnectionProvider(Configuration.Properties))
using (var connection = (DbConnection)connectionProvider.GetConnection()) {
var cmd = connection.CreateCommand();
var defaultCatalog = PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultCatalog, Configuration.Properties, null);
var defaultSchema = PropertiesHelper.GetString(NHibernate.Cfg.Environment.DefaultSchema, Configuration.Properties, null);
var dialect = Dialect.GetDialect(Configuration.Properties);
var tables = Tables().Where(t => t.IsPhysicalTable && Configuration.IncludeAction(t.SchemaActions, SchemaAction.Update));
var meta = new DatabaseMetadata(connection, dialect);
var mapping = (IMapping)Configuration.GetType().GetField("mapping", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(Configuration);
var schema = new DevartMySqlSchema(connection);
foreach (var table in tables)
{
var tableMeta = meta.GetTableMetadata(table.Name,
table.Schema ?? defaultSchema,
table.Catalog ?? defaultCatalog,
table.IsQuoted);
if (tableMeta == null)
{
continue;
}
var alters = new List <string>();
var root = new StringBuilder("alter table ")
.Append(table.GetQualifiedName(dialect, defaultCatalog, defaultSchema))
.Append(" MODIFY ");
foreach (var column in table.ColumnIterator)
{
var columnInfo = tableMeta.GetColumnMetadata(column.Name);
if (columnInfo == null)
{
continue;
}
var diff = !CompareType(columnInfo, dialect, column, mapping);
if (diff)
{
var sql = new StringBuilder()
.Append(root)
.Append(column.GetQuotedName(dialect))
.Append(" ")
.Append(column.GetSqlType(dialect, mapping));
if (!string.IsNullOrEmpty(column.DefaultValue))
{
sql.Append(" default ").Append(column.DefaultValue);
if (column.IsNullable)
{
sql.Append(dialect.NullColumnString);
}
else
{
sql.Append(" not null");
}
}
var useUniqueConstraint = column.Unique && dialect.SupportsUnique &&
(!column.IsNullable || dialect.SupportsNotNullUnique);
if (useUniqueConstraint)
{
sql.Append(" unique");
}
if (column.HasCheckConstraint && dialect.SupportsColumnCheck)
{
sql.Append(" check(").Append(column.CheckConstraint).Append(") ");
}
var columnComment = column.Comment;
if (columnComment != null)
{
sql.Append(dialect.GetColumnComment(columnComment));
}
alters.Add(sql.ToString());
}
}
if (table.UniqueKeyIterator.Any())
{
var indexes = schema.GetIndexInfo(tableMeta.Catalog, null, tableMeta.Name).AsEnumerable()
.Select(x => new DevartMySQLIndexMetadata(x))
.Where(x => x.IsUnique)
.ToArray();
foreach (var uniqueKey in table.UniqueKeyIterator)
{
if (!indexes.Any(x => x.Name.Match(uniqueKey.Name)))
{
var sql = uniqueKey.SqlConstraintString(dialect, uniqueKey.Name, defaultCatalog, defaultSchema);
alters.Add($"alter table {table.Name} {sql};");
}
}
}
// #56897 привязали элементы, чтоб сохранить настройки конструктора, удалили теги без элементов
if (table.Name.Match("PriceTags"))
{
alters.Add("update PriceTagItems set PriceTagId = (select Id from PriceTags where TagType = 0 order by Id limit 1) where ifnull(PriceTagId, 0) = 0;");
alters.Add("delete t from PriceTags t left join PriceTagItems i on i.PriceTagId = t.Id where i.PriceTagId is null;");
}
// #51646 Проблемы обновления АF.NET
if (table.Name.Match("WaybillLines") && tableMeta.GetIndexMetadata("SerialProductIdProducerId") == null)
{
alters.Add("alter table WaybillLines add index SerialProductIdProducerId (SerialNumber, ProductId, ProducerId);");
}
if (table.Name.Match("Offers"))
{
if (tableMeta.GetIndexMetadata("ProductSynonym") == null)
{
alters.Add("alter table Offers add fulltext (ProductSynonym);");
}
//из-за ошибки в 0.15.0, были созданы дублирующие индексы чистим их
var indexes = schema.GetIndexInfo(tableMeta.Catalog, null, tableMeta.Name).AsEnumerable()
.Select(x => new DevartMySQLIndexMetadata(x))
.Where(x => Regex.IsMatch(x.Name, @"ProductSynonym_\d+", RegexOptions.IgnoreCase))
.ToArray();
foreach (var index in indexes)
{
alters.Add(String.Format("alter table {0} drop index {1};", tableMeta.Name, index.Name));
}
}
if (table.Name.Match("Orders"))
{
alters.Add("update Orders o left join Prices p on o.PriceId = p.PriceId set SavePriceDate = ifnull(p.PriceDate, o.CreatedOn);");
}
if (alters.Count > 0)
{
foreach (var alter in alters)
{
try {
if (Log.IsDebugEnabled)
{
Log.Debug(alter);
}
cmd.CommandText = alter;
cmd.ExecuteNonQuery();
}
catch (Exception e) {
Log.Warn("Ошибка при обновлении схемы", e);
}
}
}
} //foreach
} //using
}