public DeleteQueryBuilder(DatabaseColumn column, T value)
{
_column = column;
_value = value;
}
protected override bool HandleComputed(DatabaseColumn column)
{
return(false); //computed columns aren't supported
}
void GetColumns()
{
using (var command = new NpgsqlCommand(GetColumnsQuery, _connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var schemaName = reader.GetValueOrDefault <string>("nspname");
var tableName = reader.GetValueOrDefault <string>("relname");
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
var table = _tables[TableKey(tableName, schemaName)];
// We need to know about dropped columns because constraints take them into
// account when referencing columns. We'll get rid of them before returning the model.
var isDropped = reader.GetValueOrDefault <bool>("attisdropped");
if (isDropped)
{
table.Columns.Add(null);
continue;
}
var columnName = reader.GetValueOrDefault <string>("attname");
var dataType = reader.GetValueOrDefault <string>("typname");
var typeModifier = reader.GetValueOrDefault <int>("atttypmod");
var typeChar = reader.GetValueOrDefault <char>("typtype");
var elemDataType = reader.GetValueOrDefault <string>("elemtypname");
var isNullable = reader.GetValueOrDefault <bool>("nullable");
var defaultValue = reader.GetValueOrDefault <string>("default");
// bpchar is just an internal name for char
if (dataType == "bpchar")
{
dataType = "char";
}
var column = new DatabaseColumn
{
Table = table,
Name = columnName,
StoreType = GetStoreType(dataType, typeModifier),
IsNullable = isNullable,
DefaultValueSql = defaultValue
};
if (defaultValue != null)
{
// Somewhat hacky... We identify serial columns by examining their default expression,
// and reverse-engineer these as ValueGenerated.OnAdd
if (defaultValue == $"nextval('{tableName}_{columnName}_seq'::regclass)" ||
defaultValue == $"nextval('\"{tableName}_{columnName}_seq\"'::regclass)")
{
// TODO: Scaffold as serial, bigserial, not int...
// But in normal code-first I don't have to set the column type...!
// TODO: Think about composite keys. Do serial magic only for non-composite.
column.ValueGenerated = ValueGenerated.OnAdd;
column.DefaultValueSql = null;
}
}
switch (typeChar)
{
case 'b':
// Base (regular), is the default
break;
case 'a':
// PG array types in pg_type start with underscores (_int for array of int), but the type name
// PG accepts when creating columns is int[], translate.
if (column.StoreType.StartsWith("_"))
{
column.StoreType = column.StoreType.Substring(1) + "[]";
}
break;
case 'r':
column[NpgsqlAnnotationNames.PostgresTypeType] = PostgresTypeType.Range;
break;
case 'e':
column[NpgsqlAnnotationNames.PostgresTypeType] = PostgresTypeType.Enum;
break;
default:
Logger.Logger.LogWarning($"Can't scaffold column '{columnName}' of type '{dataType}': unknown type char '{typeChar}'");
continue;
}
var comment = reader.GetValueOrDefault <string>("description");
if (comment != null)
{
column[NpgsqlAnnotationNames.Comment] = comment;
}
table.Columns.Add(column);
_tableColumns.Add(ColumnKey(table, column.Name), column);
}
}
}
public void Write(ClassBuilder cb, DatabaseColumn column)
{
var netName = column.NetName ?? column.Name;
//http://weblogs.asp.net/jgalloway/archive/2005/09/27/426087.aspx
_friendlyName = Regex.Replace(netName, "([A-Z]+|[0-9]+)", " $1", RegexOptions.Compiled).Trim();
if (_isNet4) //Display is .Net 4 and Silverlight 3 only
{
WriteDisplayAttribute(cb, netName);
}
//we won't mark primary keys as required, because they may be assigned by a ORM primary key strategy or database identity/sequence
if (column.IsPrimaryKey)
{
//.Net 4 and Silverlight 3 only
//NOTE: for EF CodeFirst generation, we also mapped fluently.
//Despite the duplication, it's useful to have the key as a marker in the model
if (_isNet4)
{
cb.AppendLine("[Key]");
}
}
else if (!column.Nullable)
{
WriteRequiredAttribute(cb);
}
//foreign keys will not expose the underlying type
if (column.IsForeignKey)
{
return;
}
if (column.IsIndexed &&
_codeWriterSettings.CodeTarget == CodeTarget.PocoEntityCodeFirst &&
_codeWriterSettings.WriteCodeFirstIndexAttribute &&
column.Table != null)
{
WriteIndex(cb, column);
}
var dt = column.DataType;
if (dt == null)
{
//it is a database specific type
}
else if (dt.IsString)
{
//if it's over a million characters, no point validating
if (column.Length < 1073741823 && column.Length > 0)
{
WriteStringLengthAttribute(cb, column.Length);
}
}
else if (dt.IsInt)
{
var max = column.Precision.GetValueOrDefault() - column.Scale.GetValueOrDefault();
if (max > 0 && max < 10)
{
//int.MaxValue is 2,147,483,647 (precision 10), no need to range
WriteIntegerRange(cb, max);
}
}
else if (dt.GetNetType() == typeof(decimal))
{
//[Range(typeof(decimal),"0", "999")]
var max = column.Precision.GetValueOrDefault() - column.Scale.GetValueOrDefault();
if (max > 0 && max < 28)
{
WriteDecimalRange(cb, max);
}
}
}
public void Composite_foreign_key()
{
var ida = new DatabaseColumn {
Name = "Id_A", StoreType = "int"
};
var idb = new DatabaseColumn {
Name = "Id_B", StoreType = "int"
};
var parentTable = new DatabaseTable
{
Name = "Parent",
Columns =
{
ida,
idb
},
PrimaryKey = new DatabasePrimaryKey
{
Columns = { ida, idb }
}
};
var childrenTable = new DatabaseTable
{
Name = "Children",
Columns =
{
IdColumn,
new DatabaseColumn {
Name = "ParentId_A",StoreType = "int"
},
new DatabaseColumn {
Name = "ParentId_B",StoreType = "int"
}
},
PrimaryKey = IdPrimaryKey
};
childrenTable.ForeignKeys.Add(
new DatabaseForeignKey
{
Table = childrenTable,
PrincipalTable = parentTable,
OnDelete = ReferentialAction.SetNull,
Columns =
{
childrenTable.Columns.ElementAt(1),
childrenTable.Columns.ElementAt(2)
},
PrincipalColumns =
{
parentTable.Columns.ElementAt(0),
parentTable.Columns.ElementAt(1)
}
});
var model = _factory.Create(new DatabaseModel {
Tables = { parentTable, childrenTable }
});
var parent = (EntityType)model.FindEntityType("Parent");
var children = (EntityType)model.FindEntityType("Children");
Assert.NotEmpty(parent.GetReferencingForeignKeys());
var fk = Assert.Single(children.GetForeignKeys());
Assert.False(fk.IsUnique);
Assert.Equal(DeleteBehavior.SetNull, fk.DeleteBehavior);
var principalKey = fk.PrincipalKey;
Assert.Equal("Parent", principalKey.DeclaringEntityType.Name);
Assert.Equal("IdA", principalKey.Properties[0].Name);
Assert.Equal("IdB", principalKey.Properties[1].Name);
}
public void CreateFileCompare(StreamWriter sw, DatabaseTable table)
{
string tableName = table.TableName;
sw.WriteLine();
var headerFile = new StringBuilder();
headerFile.AppendLine(@"using System;
using System.Collections.Generic;
using System.Linq;
using Anotar.NLog;
using Nexus.Entity;
using Nexus.Common.Enum;
using Nexus.Entity.Entities;
using Nexus.Memory;
using Nexus.Worker.CommunicationMemory.Utils;");
headerFile.AppendLine("namespace Nexus.Worker.CommunicationMemory.ProcessCheck.FunctionCheckMemory");
headerFile.AppendLine("{");
var endFile = new StringBuilder();
endFile.AppendLine("}");
var headerclassBuilder = new StringBuilder();
headerclassBuilder.AppendLine("public class CheckMemory" + tableName);
headerclassBuilder.AppendLine("{");
var endclassBuilder = new StringBuilder();
endclassBuilder.AppendLine("}");
sw.WriteLine(headerFile.ToString());
sw.WriteLine(headerclassBuilder.ToString());
//Insert func ở đây
#region Lấy keyName
string keyName = "";
int count = table.Columns.Count;
var listColums = new List <DatabaseColumn>();
for (int i = 0; i < count; i++)
{
DatabaseColumn column = table.Columns[i];
if (column.IsPK || column.IsFK)
{
listColums.Add(column);
}
}
if (listColums.Count == 1)
{
//TH có 1 key thì key là kiểu dữ liệu (string, long,..)
DatabaseColumn column = listColums[0];
keyName = column.Name;
}
else if (listColums.Count > 1)
{
//Trường hợp lớn hơn 1 key (GoldPositionMemberKeys)
keyName = tableName + "Keys";
}
#endregion
sw.WriteLine(FunctionBuild_CheckInfo(tableName, keyName));
sw.WriteLine(FunctionBuild_CompareInfo(table));
//End insert func
sw.WriteLine(endclassBuilder.ToString());
sw.WriteLine(endFile.ToString());
sw.WriteLine();
}
/// <summary>
/// Gets the MySql datatype definition as string
/// </summary>
/// <param name="column">The column.</param>
/// <returns></returns>
public string WriteDataType(DatabaseColumn column)
{
if (column == null)
{
return(string.Empty);
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
//we don't do column.DbDataTypeStandard() as native types will have length/precision-scale
//and also ints will have the UNSIGNED marker
//These types will fall through unchanged.
var dataType = column.DbDataType.ToUpperInvariant();
//int providerType = -1;
//if (column.DataType != null)
// providerType = column.DataType.ProviderDbType;
var precision = column.Precision;
var scale = column.Scale;
var length = column.Length;
//oracle to MySql translation
if (dataType == "NUMBER")
{
dataType = DataTypeConverter.OracleNumberConversion(precision, scale);
}
if (dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase) &&
DataTypeConverter.IsSqlServerTimestamp(dataType, column))
{
dataType = "TINYBLOB"; //there's no equivalent really
}
if (dataType == "VARCHAR2" || dataType == "NVARCHAR" || dataType == "NVARCHAR2")
{
dataType = ConvertString(length);
}
else if (dataType == "CLOB" || dataType == "NTEXT")
{
dataType = "LONGTEXT";
}
else if (dataType == "NCHAR")
{
dataType = "CHAR";
}
else if (dataType == "DATETIME2" || dataType == "TIME")
{
dataType = "DATETIME";
}
else if (dataType == "MONEY")
{
dataType = "DECIMAL";
precision = 19;
scale = 4;
}
else if (dataType == "BIT")
{
dataType = "TINYINT";
}
else if (dataType == "IMAGE" || dataType == "VARBINARY")
{
dataType = ConvertBlob(length);
}
else if (dataType == "UNIQUEIDENTIFIER")
{
dataType = "VARCHAR";
length = 64;
}
else if (dataType == "XML" || dataType == "XMLTYPE")
{
dataType = "TEXT";
}
//write out MySql datatype definition
if (dataType == "VARCHAR" ||
dataType == "CHAR" ||
dataType == "BINARY" ||
dataType == "VARBINARY")
{
dataType = dataType + " (" + length + ")";
}
if (dataType == "NUMERIC" ||
dataType == "DECIMAL")
{
var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
dataType = dataType + " (" + precision + writeScale + ")";
}
return(dataType);
}
public void Correct_arguments_to_scaffolding_typemapper()
{
var principalPkColumn = new DatabaseColumn {
Name = "PrimaryKey", StoreType = "nvarchar(450)"
};
var principalAkColumn = new DatabaseColumn {
Name = "AlternateKey", StoreType = "nvarchar(450)"
};
var principalIndexColumn = new DatabaseColumn {
Name = "Index", StoreType = "nvarchar(450)"
};
var rowversionColumn = new DatabaseColumn
{
Name = "Rowversion",
StoreType = "rowversion",
ValueGenerated = ValueGenerated.OnAddOrUpdate,
[ScaffoldingAnnotationNames.ConcurrencyToken] = true
};
var principalTable = new DatabaseTable
{
Name = "Principal",
Columns =
{
principalPkColumn,
principalAkColumn,
principalIndexColumn,
rowversionColumn
},
PrimaryKey = new DatabasePrimaryKey {
Columns = { principalPkColumn }
},
UniqueConstraints = { new DatabaseUniqueConstraint {
Columns = { principalAkColumn}
} },
Indexes = { new DatabaseIndex {
Columns = { principalIndexColumn}
} }
};
var dependentIdColumn = new DatabaseColumn {
Name = "Id", StoreType = "int"
};
var dependentFkColumn = new DatabaseColumn {
Name = "BlogAlternateKey", StoreType = "nvarchar(450)"
};
var dependentTable = new DatabaseTable
{
Name = "Dependent",
Columns =
{
dependentIdColumn,
dependentFkColumn
},
PrimaryKey = new DatabasePrimaryKey {
Columns = { dependentIdColumn }
},
Indexes = { new DatabaseIndex {
Columns = { dependentFkColumn}
} },
ForeignKeys =
{
new DatabaseForeignKey
{
Columns = { dependentFkColumn },
PrincipalTable = principalTable,
PrincipalColumns ={ principalAkColumn }
}
}
};
var dbModel = new DatabaseModel
{
Tables = { principalTable, dependentTable }
};
var model = _factory.Create(dbModel);
Assert.Null(model.FindEntityType("Principal").FindProperty("PrimaryKey").Relational().ColumnType);
Assert.Null(model.FindEntityType("Principal").FindProperty("AlternateKey").Relational().ColumnType);
Assert.Null(model.FindEntityType("Principal").FindProperty("Index").Relational().ColumnType);
Assert.Null(model.FindEntityType("Principal").FindProperty("Rowversion").Relational().ColumnType);
Assert.Null(model.FindEntityType("Dependent").FindProperty("BlogAlternateKey").Relational().ColumnType);
}
private void GetColumns(
DbConnection connection,
IReadOnlyList <DatabaseTable> tables,
Func <string, string, bool> tableFilter)
{
foreach (var table in tables)
{
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetColumnsQuery, table.Name);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var name = reader.GetValueOrDefault <string>("COLUMN_NAME");
var defaultValue = reader.GetValueOrDefault <string>("COLUMN_DEFAULT");
var nullable = reader.GetBoolean("IS_NULLABLE");
var dataType = reader.GetValueOrDefault <string>("DATA_TYPE");
var charset = reader.GetValueOrDefault <string>("CHARACTER_SET_NAME");
var collation = reader.GetValueOrDefault <string>("COLLATION_NAME");
var columType = reader.GetValueOrDefault <string>("COLUMN_TYPE");
var extra = reader.GetValueOrDefault <string>("EXTRA");
var comment = reader.GetValueOrDefault <string>("COLUMN_COMMENT");
ValueGenerated valueGenerated;
if (extra.IndexOf("auto_increment", StringComparison.Ordinal) >= 0)
{
valueGenerated = ValueGenerated.OnAdd;
}
else if (extra.IndexOf("on update", StringComparison.Ordinal) >= 0)
{
if (defaultValue != null &&
(extra.IndexOf(defaultValue, StringComparison.Ordinal) > 0 ||
string.Equals(dataType, "timestamp", StringComparison.OrdinalIgnoreCase) &&
extra.IndexOf("CURRENT_TIMESTAMP", StringComparison.Ordinal) > 0))
{
valueGenerated = ValueGenerated.OnAddOrUpdate;
}
else
{
// BUG: EF Core does not handle code generation for `OnUpdate`.
// Instead, it just generates an empty method call ".()".
// Tracked by: https://github.com/aspnet/EntityFrameworkCore/issues/18579
//
// As a partial workaround, use `OnAddOrUpdate`, if a default value
// has been specified.
if (defaultValue != null)
{
valueGenerated = ValueGenerated.OnAddOrUpdate;
}
else
{
valueGenerated = ValueGenerated.OnUpdate;
}
}
}
else
{
valueGenerated = ValueGenerated.Never;
}
defaultValue = FilterClrDefaults(dataType, nullable, defaultValue);
var column = new DatabaseColumn
{
Table = table,
Name = name,
StoreType = columType,
IsNullable = nullable,
DefaultValueSql = CreateDefaultValueString(defaultValue, dataType),
ValueGenerated = valueGenerated,
Comment = string.IsNullOrEmpty(comment) ? null : comment,
[MySqlAnnotationNames.CharSet] = _settings.CharSet ? charset : null,
[MySqlAnnotationNames.Collation] = _settings.Collation ? collation : null,
};
table.Columns.Add(column);
}
}
}
}
}
private void GetColumns()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
NULL [schema]
, c.TABLE_NAME [table]
, c.DATA_TYPE [typename]
, c.COLUMN_NAME [column_name]
, CAST(c.ORDINAL_POSITION as integer) [ordinal]
, CAST(CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [nullable]
, ix.ORDINAL_POSITION as [primary_key_ordinal]
, RTRIM(LTRIM(c.COLUMN_DEFAULT)) as [default_sql]
, c.NUMERIC_PRECISION [precision]
, c.NUMERIC_SCALE [scale]
, c.CHARACTER_MAXIMUM_LENGTH [max_length]
, CAST(CASE WHEN c.AUTOINC_INCREMENT IS NULL THEN 0 ELSE 1 END AS bit) [is_identity]
, CAST(CASE WHEN c.DATA_TYPE = 'rowversion' THEN 1 ELSE 0 END AS bit) [is_computed]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_NAME = t.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.INDEXES ix
ON c.TABLE_NAME = ix.TABLE_NAME AND c.COLUMN_NAME = ix.COLUMN_NAME AND ix.PRIMARY_KEY = 1
WHERE SUBSTRING(c.COLUMN_NAME, 1,5) != '__sys'
AND t.TABLE_TYPE = 'TABLE'
AND (SUBSTRING(t.TABLE_NAME, 1,2) <> '__')
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION;";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var tableName = reader.GetValueOrDefault <string>("table");
var dataTypeName = reader.GetValueOrDefault <string>("typename");
var nullable = reader.GetValueOrDefault <bool>("nullable");
var precision = reader.IsDBNull(8) ? default(int?) : Convert.ToInt32(reader[8], System.Globalization.CultureInfo.InvariantCulture);
var scale = reader.IsDBNull(9) ? default(int?) : Convert.ToInt32(reader[9], System.Globalization.CultureInfo.InvariantCulture);
var maxLength = reader.GetValueOrDefault <int?>("max_length");
var ordinal = reader.GetValueOrDefault <int?>("ordinal");
var primaryKeyOrdinal = reader.GetValueOrDefault <int?>("primary_key_ordinal");
var columnName = reader.GetValueOrDefault <string>("column_name");
var defaultValue = reader.GetValueOrDefault <string>("default_sql");
var isIdentity = reader.GetValueOrDefault <bool>("is_identity");
Logger.ColumnFound(tableName, columnName, dataTypeName, nullable, defaultValue);
if (!AllowsTable(tablesToSelect, selectedTables, tableName))
{
//Logger.ColumnSkipped(DisplayName(schemaName, tableName), columnName);
continue;
}
if (!_tables.TryGetValue(TableKey(tableName), out var table))
{
Logger.MissingTableWarning(tableName);
continue;
}
var storeType = GetStoreType(dataTypeName, precision, scale, maxLength);
defaultValue = FilterClrDefaults(dataTypeName, nullable, defaultValue);
var isComputed = reader.GetValueOrDefault <bool>("is_computed");
var column = new DatabaseColumn
{
Table = table,
StoreType = storeType,
Name = columnName,
IsNullable = nullable,
DefaultValueSql = defaultValue,
ValueGenerated = isIdentity ?
ValueGenerated.OnAdd :
isComputed
? ValueGenerated.OnAddOrUpdate
: default(ValueGenerated?)
};
if ((storeType) == "rowversion")
{
column["ConcurrencyToken"] = true;
}
table.Columns.Add(column);
_tableColumns.Add(ColumnKey(table, column.Name), column);
}
}
}
public static ITable ToSchemaTable(this Type type, IDataProvider provider)
{
string tableName = type.Name;
tableName = tableName.MakePlural();
var result = new DatabaseTable(tableName, provider);
result.ClassName = type.Name;
var typeAttributes = type.GetCustomAttributes(typeof(IClassMappingAttribute), false);
foreach (IClassMappingAttribute attr in typeAttributes)
{
if (attr.Accept(result))
{
attr.Apply(result);
}
}
var props = type.GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.FlattenHierarchy);
foreach (var prop in props)
{
if (prop.GetSetMethod() == null)
{
continue;
}
var attributes = prop.GetCustomAttributes(false);
if (ColumnIsIgnored(attributes))
{
continue;
}
if (CanGenerateSchemaFor(prop.PropertyType))
{
var column = new DatabaseColumn(prop.Name, result);
column.PropertyName = prop.Name;
bool isNullable = prop.PropertyType.Name.Contains("Nullable");
column.DataType = IdentifyColumnDataType(prop.PropertyType, isNullable);
if (column.DataType == DbType.Decimal || column.DataType == DbType.Double)
{
//default to most common;
column.NumberScale = 2;
column.NumericPrecision = 16;
}
/*else if(column.DataType == DbType.String)
* {
* column.MaxLength = 255;
* }*/
else if (column.DataType == DbType.Binary)
{
isNullable = true;
}
if (isNullable)
{
column.IsNullable = true;
}
// Now work with attributes
foreach (IPropertyMappingAttribute attr in attributes.Where(x => x is IPropertyMappingAttribute))
{
if (attr.Accept(column))
{
attr.Apply(column);
}
}
result.Columns.Add(column);
}
else if (IsRelationColumm(attributes))
{
if (!IsPropertyDeclaredVirtual(prop))
{
throw new InvalidOperationException(String.Format(
"Property {0} of type {1} marked as relation must be declared virtual to allow dynamic proxy mechanisms work correctly!",
prop.Name, prop.DeclaringType.Name));
}
var innerProp = prop;
result.AddRelation(() =>
{
var relation = new DatabaseRelation(innerProp.Name, result);
foreach (IRelationMappingAttribute attr in attributes.Where(x => x is IRelationMappingAttribute))
{
if (attr.Accept(relation, innerProp))
{
attr.Apply(relation, innerProp);
}
}
return(relation);
});
}
}
//if the PK is still null-look for a column called [tableName]ID - if it's there then make it PK
if (result.PrimaryKey == null)
{
var pk = (result.GetColumn(type.Name + "ID") ?? result.GetColumn("ID")) ?? result.GetColumn("Key");
if (pk != null)
{
pk.IsPrimaryKey = true;
//if it's an INT then AutoIncrement it
if (pk.IsNumeric)
{
pk.AutoIncrement = true;
}
else if (pk.IsString && pk.MaxLength == 0)
{
pk.MaxLength = 255;
}
//} else {
// pk = new DatabaseColumn(type.ColumnName + "ID", result);
// pk.DataType = DbType.Int32;
// pk.IsPrimaryKey = true;
// pk.AutoIncrement = true;
// result.Columns.Insert(0, pk);
}
}
//we should have a PK at this point
//if not, throw :)
if (result.PrimaryKey == null)
{
throw new InvalidOperationException("Can't decide which property to consider the Key - you can create one called 'ID' or mark one with SubSonicPrimaryKey attribute");
}
return(result);
}
private bool ComparePropertyToColumn(CompareLogger logger, IProperty property, DatabaseColumn column)
{
var pRel = property.Relational();;
var error = logger.CheckDifferent(pRel.ColumnType, column.StoreType, CompareAttributes.ColumnType, _caseComparison);
error |= logger.CheckDifferent(property.IsNullable.NullableAsString(), column.IsNullable.NullableAsString(), CompareAttributes.Nullability, _caseComparison);
error |= logger.CheckDifferent(pRel.ComputedColumnSql.RemoveUnnecessaryBrackets(),
column.ComputedColumnSql.RemoveUnnecessaryBrackets(), CompareAttributes.ComputedColumnSql, _caseComparison);
var defaultValue = pRel.DefaultValueSql ?? pRel.DefaultValue?.ToString();
error |= logger.CheckDifferent(defaultValue.RemoveUnnecessaryBrackets(),
column.DefaultValueSql.RemoveUnnecessaryBrackets(), CompareAttributes.DefaultValueSql, _caseComparison);
error |= CheckValueGenerated(logger, property, column);
return(error);
}
public void CreateFile(string folderPath, string fileName, List <DatabaseTable> listTable)
{
try
{
listTable.Sort((l, r) => String.Compare(l.TableName, r.TableName, StringComparison.Ordinal));
var stringBuild = new StringBuilder();
var headerFile = new StringBuilder();
headerFile.AppendLine(@"using System;
using System.Collections.Generic;
using System.Globalization;
using Nexus.Entity.Keys;
using System.Linq;
using Nexus.Common.Enum;
using Nexus.Entity.Entities;
");
headerFile.AppendLine("namespace Nexus.Memory");
headerFile.AppendLine("{");
var endFile = new StringBuilder();
endFile.AppendLine("}");
var headerclassBuilder = new StringBuilder();
headerclassBuilder.AppendLine("public partial class MemoryInfo : Memory");
headerclassBuilder.AppendLine("{");
var endclassBuilder = new StringBuilder();
endclassBuilder.AppendLine("}");
stringBuild.Append(headerFile.ToString());
stringBuild.Append(headerclassBuilder.ToString());
foreach (var table in listTable)
{
if (table.IsSelected)
{
string tableName = table.TableName;
string keyName = "";
int count = table.Columns.Count;
var listColums = new List <DatabaseColumn>();
for (int i = 0; i < count; i++)
{
DatabaseColumn column = table.Columns[i];
if (column.IsPK || column.IsFK)
{
listColums.Add(column);
}
}
if (listColums.Count == 1)
{
//TH có 1 key thì key là text của object đó ví dụ ClientOrderId của BrokerOrder
DatabaseColumn column = listColums[0];
keyName = column.Name;
}
else if (listColums.Count > 1)
{
//Trường hợp lớn hơn 1 key (GoldPositionMemberKeys)
keyName = tableName + "Keys";
}
stringBuild.AppendLine(FunctionBuild_GetMemory(tableName, keyName).ToString());
}
}
//End insert func
stringBuild.Append(endclassBuilder.ToString());
stringBuild.Append(endFile.ToString());
stringBuild.AppendLine();
//Lưu vào thư mục build
using (var sw = new StreamWriter(folderPath + fileName))
{
sw.WriteLine(stringBuild.ToString());
sw.Close();
}
//Lưu vào thư mục foudation nếu có
if (!string.IsNullOrEmpty(ConfigGlobal.SettingConfig.Setting_MemoryWorkerBase) && ConfigGlobal.SettingConfig.Setting_CheckGenByForder)
{
string fileCs = ConfigGlobal.SettingConfig.Setting_MemoryWorkerBase + "\\" + fileName;
if (File.Exists(fileCs))
{
//Tồn tại mới lưu
File.Delete(fileCs);
using (var sw = new StreamWriter(fileCs))
{
sw.WriteLine(stringBuild.ToString());
sw.Close();
}
}
}
}
catch (Exception ex)
{
}
}
public override string AlterColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn, DatabaseColumn originalColumn)
{
var tableGenerator = CreateTableGenerator(databaseTable);
if (!AlterColumnIncludeDefaultValue)
{
tableGenerator.IncludeDefaultValues = false;
}
var columnDefinition = tableGenerator.WriteColumn(databaseColumn).Trim();
var originalDefinition = "?";
if (originalColumn != null)
{
originalDefinition = tableGenerator.WriteColumn(originalColumn).Trim();
//we don't specify "NULL" for nullables in tableGenerator, but if it's changed we should
if (originalColumn.Nullable && !databaseColumn.Nullable)
{
originalDefinition += " NULL";
}
if (!originalColumn.Nullable && databaseColumn.Nullable)
{
columnDefinition += " NULL";
}
}
if (originalDefinition.Equals(columnDefinition))
{
//most likely faulty sql db structure, skip
//add a nice comment
return(string.Format(CultureInfo.InvariantCulture,
"-- skipping alter of {0} from {1} to {2}",
databaseTable.Name,
originalDefinition,
columnDefinition));
}
StringBuilder sb = new StringBuilder();
sb.AppendLine(string.Format("--TO CHANGE COLUMN {0} to {1}, WE CREATE BACKUP, MOVE CONTENT AND RENAME TABLE " + LineEnding(), originalDefinition, columnDefinition));
DatabaseTable tempTable = databaseTable.Clone("bkup1903_" + databaseTable.Name);
tempTable.Columns.Remove(tempTable.FindColumn(originalColumn == null ? databaseColumn.Name : originalColumn.Name));
tempTable.Columns.Add(databaseColumn);
sb.Append(BackupAndUpdateTable(databaseTable, tempTable));
return(sb.ToString());
}
/// <summary>
/// Gets the SQLServer datatype definition as string
/// </summary>
/// <param name="column">The column.</param>
/// <returns></returns>
public virtual string WriteDataType(DatabaseColumn column)
{
if (column == null)
{
return(string.Empty);
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
var dataType = column.DbDataTypeStandard();
int providerType = -1;
if (column.DataType != null)
{
providerType = column.DataType.ProviderDbType;
}
var precision = column.Precision;
var scale = column.Scale;
var length = column.Length;
//oracle to sql server translation
if (dataType == "BLOB")
{
dataType = "VARBINARY";
length = -1;
}
if (dataType == "CLOB")
{
dataType = "NVARCHAR";
length = -1;
}
//In SqlServer, the maximum length allowed for any data type is 8000.
//Ergo, TEXTs and NTEXTs that are larger (int.MaxValue or int.MaxValue/2) must be SqlServer types
if (dataType == "NTEXT" && length > 8000)
{
return("NTEXT");
}
if (dataType == "TEXT" && length > 8000)
{
return("TEXT");
}
if (_originSqlType == SqlType.SqlServer || _originSqlType == SqlType.SqlServerCe)
{
if (dataType == "BINARY")
{
//should not be varbinary
return(WriteDataTypeWithLength(dataType, length));
}
}
else
{
dataType = ConvertOtherPlatformTypes(dataType, providerType, length, precision, scale);
}
if ((dataType == "DATETIME2" || dataType == "TIME") && column.DateTimePrecision.HasValue)
{
dataType = dataType + "(" + column.DateTimePrecision + ")";
}
//write out SqlServer datatype definition
if (dataType == "NVARCHAR" ||
dataType == "VARCHAR" ||
dataType == "CHAR" ||
dataType == "NCHAR" ||
dataType == "BINARY" ||
dataType == "VARBINARY")
{
dataType = WriteDataTypeWithLength(dataType, length);
}
if (dataType == "NUMERIC" ||
dataType == "DECIMAL")
{
if (precision != null)
{
var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
dataType = dataType + " (" + precision + writeScale + ")";
}
}
return(dataType);
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
protected virtual PropertyBuilder VisitColumn([NotNull] EntityTypeBuilder builder, [NotNull] DatabaseColumn column)
{
Check.NotNull(builder, nameof(builder));
Check.NotNull(column, nameof(column));
var typeScaffoldingInfo = GetTypeScaffoldingInfo(column);
if (typeScaffoldingInfo == null)
{
_unmappedColumns.Add(column);
_reporter.WriteWarning(
DesignStrings.CannotFindTypeMappingForColumn(column.DisplayName(), column.StoreType));
return(null);
}
var clrType = typeScaffoldingInfo.ClrType;
if (column.IsNullable)
{
clrType = clrType.MakeNullable();
}
if (clrType == typeof(bool) &&
column.DefaultValueSql != null)
{
_reporter.WriteWarning(
DesignStrings.NonNullableBoooleanColumnHasDefaultConstraint(column.DisplayName()));
clrType = clrType.MakeNullable();
}
var property = builder.Property(clrType, GetPropertyName(column));
property.HasColumnName(column.Name);
if (!typeScaffoldingInfo.IsInferred &&
!string.IsNullOrWhiteSpace(column.StoreType))
{
property.HasColumnType(column.StoreType);
}
if (typeScaffoldingInfo.ScaffoldUnicode.HasValue)
{
property.IsUnicode(typeScaffoldingInfo.ScaffoldUnicode.Value);
}
if (typeScaffoldingInfo.ScaffoldFixedLength == true)
{
property.IsFixedLength();
}
if (typeScaffoldingInfo.ScaffoldMaxLength.HasValue)
{
property.HasMaxLength(typeScaffoldingInfo.ScaffoldMaxLength.Value);
}
if (column.ValueGenerated == ValueGenerated.OnAdd)
{
property.ValueGeneratedOnAdd();
}
if (column.ValueGenerated == ValueGenerated.OnUpdate)
{
property.ValueGeneratedOnUpdate();
}
if (column.ValueGenerated == ValueGenerated.OnAddOrUpdate)
{
property.ValueGeneratedOnAddOrUpdate();
}
if (column.DefaultValueSql != null)
{
property.HasDefaultValueSql(column.DefaultValueSql);
}
if (column.ComputedColumnSql != null)
{
property.HasComputedColumnSql(column.ComputedColumnSql);
}
if (column.Comment != null)
{
property.HasComment(column.Comment);
}
if (!(column.Table.PrimaryKey?.Columns.Contains(column) ?? false))
{
property.IsRequired(!column.IsNullable);
}
if ((bool?)column[ScaffoldingAnnotationNames.ConcurrencyToken] == true)
{
property.IsConcurrencyToken();
}
property.Metadata.SetColumnOrdinal(column.Table.Columns.IndexOf(column));
property.Metadata.AddAnnotations(
column.GetAnnotations().Where(
a => a.Name != ScaffoldingAnnotationNames.ConcurrencyToken));
return(property);
}
private static string OtherDatabaseTypesToPostgreSql(string dataType, DatabaseColumn column)
{
//string types
//character(n) (aka char(n)) character varying(n) aka varchar(n) and text
if (DataTypeConverter.IsFixedLengthString(dataType))
{
return("CHAR");
}
if (DataTypeConverter.IsLongString(dataType))
{
return("TEXT");
}
if (DataTypeConverter.IsVariableString(dataType))
{
if (column.Length == -1)
{
return("TEXT");
}
return("VARCHAR");
}
//numeric types
if (dataType == "INT")
{
return("INTEGER");
}
if (dataType == "INT4")
{
return("INTEGER"); //this is a PostgreSql alias, we'll use standard SQL
}
//else if (dataType == "SERIAL") return "INTEGER"; //this is a PostgreSql alias, we'll use standard SQL
//else if (dataType == "BIGSERIAL") return "BIGINT"; //this is a PostgreSql alias, we'll use standard SQL
if (dataType == "INT8")
{
return("BIGINT"); //this is a PostgreSql alias, we'll use standard SQL
}
if (dataType == "INT2")
{
return("SMALLINT"); //this is a PostgreSql alias, we'll use standard SQL
}
if (dataType == "TINYINT")
{
return("SMALLINT"); //this is a MsSql alias, we'll use standard SQL
}
if (dataType == "NUMBER")
{
return(DataTypeConverter.OracleNumberConversion(column.Precision, column.Scale));
}
//float and real
if (dataType == "FLOAT4")
{
return("REAL"); //this is a PostgreSql alias, we'll use standard SQL
}
if (dataType == "FLOAT")
{
return("DOUBLE PRECISION");
}
//date times
//SqlServer Timestamp is a binary
if (DataTypeConverter.IsSqlServerTimestamp(dataType, column))
{
return("BYTEA"); //this is just a byte array- functionally you should redesign the table and perhaps use the system extension columns
}
if (DataTypeConverter.IsDateTime(dataType))
{
return("TIMESTAMP");
}
//bytes
if (DataTypeConverter.IsBlob(dataType, column))
{
return("OID");//blobs become object ids
}
if (DataTypeConverter.IsBinary(dataType))
{
return("BYTEA");
}
//there is a native BIT(n) type in Postgresql, but in conversion we probably mean boolean.
if (dataType == "BIT" && !column.Length.HasValue)
{
return("BOOLEAN");
}
//other types
if (dataType == "XMLTYPE")
{
return("XML");
}
if (dataType == "UNIQUEIDENTIFIER")
{
return("UUID");
}
return(dataType);
}
public void Indexes_and_alternate_keys()
{
var c1 = new DatabaseColumn {
Name = "C1", StoreType = "int"
};
var table = new DatabaseTable
{
Name = "T",
Columns =
{
c1,
new DatabaseColumn {
Name = "C2",StoreType = "int"
},
new DatabaseColumn {
Name = "C3",StoreType = "int"
}
},
PrimaryKey = new DatabasePrimaryKey
{
Columns = { c1 }
}
};
table.Indexes.Add(
new DatabaseIndex
{
Name = "IDX_C1",
Columns = { table.Columns.ElementAt(0) },
IsUnique = false
});
table.Indexes.Add(
new DatabaseIndex
{
Name = "UNQ_C2",
Columns = { table.Columns.ElementAt(1) },
IsUnique = true
});
table.Indexes.Add(
new DatabaseIndex
{
Name = "IDX_C2_C1",
Columns =
{
table.Columns.ElementAt(1),
table.Columns.ElementAt(0)
},
IsUnique = false
});
table.Indexes.Add(
new DatabaseIndex
{
/*Name ="UNQ_C3_C1",*/
Columns =
{
table.Columns.ElementAt(2),
table.Columns.ElementAt(0)
},
IsUnique = true
});
var info = new DatabaseModel {
Tables = { table }
};
var entityType = (EntityType)_factory.Create(info).GetEntityTypes().Single();
Assert.Collection(
entityType.GetIndexes(),
indexColumn1 =>
{
Assert.False(indexColumn1.IsUnique);
Assert.Equal("IDX_C1", indexColumn1.Relational().Name);
Assert.Same(entityType.FindProperty("C1"), indexColumn1.Properties.Single());
},
uniqueColumn2 =>
{
Assert.True(uniqueColumn2.IsUnique);
Assert.Same(entityType.FindProperty("C2"), uniqueColumn2.Properties.Single());
},
indexColumn2Column1 =>
{
Assert.False(indexColumn2Column1.IsUnique);
Assert.Equal(new[] { "C2", "C1" }, indexColumn2Column1.Properties.Select(c => c.Name).ToArray());
},
uniqueColumn3Column1 =>
{
Assert.True(uniqueColumn3Column1.IsUnique);
Assert.Equal(new[] { "C3", "C1" }, uniqueColumn3Column1.Properties.Select(c => c.Name).ToArray());
}
);
// unique indexes should not cause alternate keys if not used by foreign keys
Assert.Equal(0, entityType.GetKeys().Count(k => !k.IsPrimaryKey()));
}
public string WriteDataType(DatabaseColumn column)
{
if (column == null)
{
return(string.Empty);
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
var sql = string.Empty;
var dataType = column.DbDataTypeStandard();
var precision = column.Precision;
var scale = column.Scale;
var length = column.Length;
if (dataType == "BOOLEAN")
{
dataType = "NUMBER";
precision = 1;
scale = 0;
}
//sql server to oracle translation
dataType = SqlServerToOracleConversion(dataType, GetProviderType(column), length);
if (dataType == "UNIQUEIDENTIFIER")
{
dataType = "RAW";
length = 16;
}
if (dataType == "NUMERIC")
{
dataType = "NUMBER";
}
if (dataType == "INT")
{
dataType = "NUMBER";
precision = 9;
scale = 0;
}
if (dataType == "SMALLINT")
{
dataType = "NUMBER";
precision = 5;
scale = 0;
}
if (dataType == "BIT")
{
dataType = "NUMBER";
precision = 1;
scale = 0;
}
if (dataType == "DECIMAL")
{
dataType = "NUMBER";
precision = 18;
scale = 0;
}
if (dataType == "MONEY")
{
dataType = "NUMBER";
precision = 15;
scale = 4;
}
string defaultValue = FixDefaultValue(column);
//write out Oracle datatype definition
if (dataType == "NVARCHAR2")
{
if (length == -1)
{
dataType = "CLOB";
}
else
{
//don't specify "CHAR" for NVARCHAR2
sql = dataType + " (" + length + ")";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
}
if (dataType == "VARCHAR2")
{
//assume it's CHAR rather than bytes
sql = dataType + " (" + length + " CHAR)";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
if (dataType == "CHAR" || dataType == "NCHAR")
{
sql = dataType + " (" + length + ")";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
if (dataType == "NUMBER")
{
if (!precision.HasValue)
{
sql = "NUMBER";
}
else
{
var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
sql = "NUMBER (" + precision + writeScale + ")";
}
if (!column.Nullable)
{
sql += " NOT NULL";
}
if (column.IdentityDefinition != null)
{
if (column.DatabaseSchema == null ||
column.DatabaseSchema.Provider.IndexOf("Oracle", StringComparison.OrdinalIgnoreCase) == -1)
{
//this doesn't look like oracle, so we're converting.
return(sql);
}
//Oracle 12c- this can be set.
//For Oracle 11, IsAutoNumber can be set (by recognizing a sequence), but the definition won't be set.
//these must be NUMBER of some sort or it's invalid
sql += " GENERATED ";
if (column.IdentityDefinition.IdentityByDefault)
{
sql += "BY DEFAULT ";
}
sql += "AS IDENTITY";
if (column.IdentityDefinition.IsNonTrivialIdentity())
{
sql += string.Format(CultureInfo.InvariantCulture, " (START WITH {0} INCREMENT BY {1})",
column.IdentityDefinition.IdentitySeed,
column.IdentityDefinition.IdentityIncrement);
}
//no other options should be done
return(sql);
}
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + defaultValue;
}
return(sql);
}
if (dataType == "REAL")
{
sql = "REAL";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + defaultValue;
}
}
if (dataType == "RAW")
{
sql = "RAW(" + length + ")";
}
if (dataType == "XMLTYPE")
{
sql = dataType;
}
if (dataType == "DATE")
{
sql = "DATE";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT DATE '" + defaultValue + "'";
}
}
if (dataType == "TIMESTAMP")
{
sql = "TIMESTAMP" + (precision.HasValue ? " (" + precision + ")" : " (6)");
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT TIMESTAMP '" + defaultValue + "'";
}
}
if (dataType == "CLOB" || dataType == "NCLOB")
{
sql = dataType;
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
if (dataType == "BLOB")
{
sql = dataType;
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
if (column.IsComputed)
{
sql = "GENERATED ALWAYS AS (" + column.ComputedDefinition + ") VIRTUAL";
}
if (string.IsNullOrEmpty(sql))
{
sql = column.DbDataType;
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
return(sql.TrimEnd() + (!column.Nullable ? " NOT NULL" : string.Empty));
}
public void Unique_index_composite_foreign_key()
{
var ida = new DatabaseColumn {
Name = "Id_A", StoreType = "int"
};
var idb = new DatabaseColumn {
Name = "Id_B", StoreType = "int"
};
var parentTable = new DatabaseTable
{
Name = "Parent",
Columns =
{
ida,
idb
},
PrimaryKey = new DatabasePrimaryKey
{
Columns = { ida, idb }
}
};
var childrenTable = new DatabaseTable
{
Name = "Children",
Columns =
{
IdColumn,
new DatabaseColumn {
Name = "ParentId_A",StoreType = "int"
},
new DatabaseColumn {
Name = "ParentId_B",StoreType = "int"
}
},
PrimaryKey = IdPrimaryKey
};
childrenTable.Indexes.Add(
new DatabaseIndex
{
IsUnique = true,
Columns =
{
childrenTable.Columns.ElementAt(1),
childrenTable.Columns.ElementAt(2)
}
});
childrenTable.ForeignKeys.Add(
new DatabaseForeignKey
{
Table = childrenTable,
PrincipalTable = parentTable,
Columns =
{
childrenTable.Columns.ElementAt(1),
childrenTable.Columns.ElementAt(2)
},
PrincipalColumns =
{
parentTable.Columns.ElementAt(0),
parentTable.Columns.ElementAt(1)
}
});
var model = _factory.Create(new DatabaseModel {
Tables = { parentTable, childrenTable }
});
var parent = model.FindEntityType("Parent");
var children = model.FindEntityType("Children");
var fk = Assert.Single(children.GetForeignKeys());
Assert.True(fk.IsUnique);
Assert.Equal(parent.FindPrimaryKey(), fk.PrincipalKey);
}
/// <summary>修改列类型、默认值、是否为NULL等
/// 修改列类型、默认值、是否为NULL等
/// </summary>
/// <param name="table"></param>
/// <param name="databaseColumn"></param>
/// <param name="originalColumn"></param>
/// <returns></returns>
public string AlterColumn(DatabaseTable table, DatabaseColumn databaseColumn, DatabaseColumn originalColumn)
{
return(m_migrationGenerator.AlterColumn(table, databaseColumn, originalColumn));
}
private StringBuilder FunctionBuild_CompareInfo(DatabaseTable table)
{
var functionBuild = new StringBuilder();
try
{
string tableName = table.TableName;
functionBuild.AppendLine("private static string CompareInfo(" + tableName +
" valueDatabase, " + tableName + " valueWorker, out string valueFail, out string keyValue)");
functionBuild.AppendLine("{");
functionBuild.AppendLine("valueFail = \"\";");
functionBuild.AppendLine("keyValue = \"\";");
int count = table.Columns.Count;
var listColums = new List <DatabaseColumn>();
var listColumsNotKey = new List <DatabaseColumn>();
for (int i = 0; i < count; i++)
{
DatabaseColumn column = table.Columns[i];
string columnName = column.Name;
if (column.IsPK || column.IsFK)
{
listColums.Add(column);
}
else
{
listColumsNotKey.Add(column);
}
}
if (tableName.Equals("UserRoleGroup"))
{
}
//return string.Format("SymbolId and DatabaseValue ={0}. WorkerValue={1}", valueDatabase.SymbolId, valueWorker.SymbolId);
if (listColums.Count == 1)
{
string columnName = listColums[0].Name;
functionBuild.AppendLine("keyValue += \"" + columnName + " = \" + valueWorker." + columnName + "+\"__\";");
functionBuild.AppendLine("if (valueWorker." + columnName + " != valueDatabase." + columnName + ")");
functionBuild.AppendLine("{");
functionBuild.AppendLine("valueFail = string.Format(\"DatabaseValue ={0}. WorkerValue={1}\", valueDatabase." + columnName + ", valueWorker." + columnName + ");");
functionBuild.AppendLine("return \"" + columnName + "\";");
functionBuild.AppendLine("}");
}
else if (listColums.Count > 1)
{
foreach (var column in listColums)
{
string columnName = column.Name;
string keyName = tableName + "Keys";
columnName = keyName + "." + columnName;
functionBuild.AppendLine("keyValue += \"" + columnName + " = \" + valueWorker." + columnName + "+\"__\";");
functionBuild.AppendLine("if (valueWorker." + columnName + " != valueDatabase." + columnName + ")");
functionBuild.AppendLine("{");
functionBuild.AppendLine("valueFail = string.Format(\"DatabaseValue ={0}. WorkerValue={1}\", valueDatabase." + columnName + ", valueWorker." + columnName + ");");
functionBuild.AppendLine("return \"" + columnName + "\";");
functionBuild.AppendLine("}");
}
}
foreach (var column in listColumsNotKey)
{
if (column.CSharpDataTypeName == "byte[]" ||
column.CSharpDataTypeName == "byte[]?")
{
continue; //Bỏ qua check trường byte
}
if (column.Name.Equals("IndexRow") &&
column.IsAutoNumber)
{
continue; //Bỏ qua trường index row tự tăng của linkedtransaction
}
string columnName = column.Name;
//Nếu là trường giá hoặc decimal thì routing 6 kí tự
//if (column.CSharpDataTypeName == "decimal" ||
// column.CSharpDataTypeName == "decimal?")
//{
// if (CreateFileMemoryGetByField.IsNullValue(column, tableName))
// {
// //if (valueWorker.IMLimit.HasValue)
// //valueWorker.IMLimit = Math.Round(valueWorker.IMLimit.Value, 6);
// functionBuild.AppendLine("if (valueWorker." + columnName + ".HasValue)");
// functionBuild.AppendLine("valueWorker." + columnName + " = Math.Round(valueWorker." + columnName + ".Value, " + column.NumericScale + ");");
// }
// else
// {
// //valueWorker.AvgBuyNet = Math.Round(valueWorker.AvgBuyNet, 6);
// functionBuild.AppendLine("valueWorker." + columnName + " = Math.Round(valueWorker." + columnName + ", " + column.NumericScale + ");");
// }
//}
if (column.CSharpDataTypeName == "DateTime" ||
column.CSharpDataTypeName == "DateTime?")
{
//Sử dụng hàm check time
//if (EntityCommandUtils.CheckTime(valueWorker.TimeChanged, valueDatabase.TimeChanged))
functionBuild.AppendLine("if (EntityCommandUtils.CheckTime(valueWorker." + columnName + ", valueDatabase." + columnName + "))");
functionBuild.AppendLine("{");
functionBuild.AppendLine("valueFail = string.Format(\"DatabaseValue ={0}. WorkerValue={1}\", valueDatabase." + columnName + ", valueWorker." + columnName + ");");
functionBuild.AppendLine("return \"" + columnName + "\";");
functionBuild.AppendLine("}");
}
else if (column.CSharpDataTypeName == "decimal" ||
column.CSharpDataTypeName == "decimal?")
{
//Sử dụng hàm check decimal
//if (EntityCommandUtils.CheckTime(valueWorker.TimeChanged, valueDatabase.TimeChanged))
functionBuild.AppendLine("if (EntityCommandUtils.CheckDecimal(valueWorker." + columnName + ", valueDatabase." + columnName + "))");
functionBuild.AppendLine("{");
functionBuild.AppendLine("valueFail = string.Format(\"DatabaseValue ={0}. WorkerValue={1}\", valueDatabase." + columnName + ", valueWorker." + columnName + ");");
functionBuild.AppendLine("return \"" + columnName + "\";");
functionBuild.AppendLine("}");
}
else
{
functionBuild.AppendLine("if (valueWorker." + columnName + " != valueDatabase." + columnName + ")");
functionBuild.AppendLine("{");
functionBuild.AppendLine("valueFail = string.Format(\"DatabaseValue ={0}. WorkerValue={1}\", valueDatabase." + columnName + ", valueWorker." + columnName + ");");
functionBuild.AppendLine("return \"" + columnName + "\";");
functionBuild.AppendLine("}");
}
}
functionBuild.AppendLine("return \"\";");
functionBuild.AppendLine("}");
}
catch (Exception ex)
{
}
return(functionBuild);
}
/// <summary>重命名列
/// 重命名列
/// </summary>
/// <param name="table"></param>
/// <param name="databaseColumn"></param>
/// <param name="originalColumn"></param>
/// <returns></returns>
public string RenameColumn(DatabaseTable table, DatabaseColumn databaseColumn, DatabaseColumn originalColumn)
{
return(m_migrationGenerator.RenameColumn(originalColumn.Table, databaseColumn, originalColumn.Name));
}
public override string AlterColumn(DatabaseTable databaseTable, DatabaseColumn databaseColumn, DatabaseColumn originalColumn)
{
var sb = new StringBuilder();
var defaultName = "DF_" + databaseTable.Name + "_" + databaseColumn.Name;
if (originalColumn != null)
{
if (originalColumn.DefaultValue != null)
{
//have to drop default contraint
var df = FindDefaultConstraint(databaseTable, databaseColumn.Name);
if (df != null)
{
defaultName = df.Name;
sb.AppendLine("ALTER TABLE " + TableName(databaseTable)
+ " DROP CONSTRAINT " + Escape(defaultName) + ";");
}
}
}
//更新表中列的描述属性==>lhm
if (originalColumn.Description != databaseColumn.Description)
{
sb.AppendLine("EXEC sp_updateextendedproperty 'MS_Description','" + databaseColumn.Description + "','user',dbo,'table','" + databaseTable.Name + "','column','" + databaseColumn.Name + "' " + ";");
}
//we could check if any of the properties are changed here
sb.AppendLine(base.AlterColumn(databaseTable, databaseColumn, originalColumn));
if (databaseColumn.DefaultValue != null)
{
//add default contraint
sb.AppendLine("ALTER TABLE " + TableName(databaseTable) +
" ADD CONSTRAINT " + Escape(defaultName) +
" DEFAULT " + databaseColumn.DefaultValue +
" FOR " + Escape(databaseColumn.Name) + ";");
}
return(sb.ToString());
}
private static void ConvertIndexes(DataTable dt, ICollection <DatabaseIndex> indexes)
{
if (dt == null)
{
return;
}
//Npgsql
if (dt.Columns.Count == 0)
{
return;
}
var indexKeyMap = new IndexKeyMap(dt);
foreach (DataRowView row in dt.DefaultView)
{
string name = row[indexKeyMap.Key].ToString();
if (string.IsNullOrEmpty(name))
{
continue; //all indexes should have a name
}
string schema = !String.IsNullOrEmpty(indexKeyMap.SchemaKey) ? row[indexKeyMap.SchemaKey].ToString() : String.Empty;
var tableName = row[indexKeyMap.TableKey].ToString();
var c = indexes.FirstOrDefault(f => f.Name == name && f.SchemaOwner == schema && f.TableName.Equals(tableName, StringComparison.OrdinalIgnoreCase));
if (c == null)
{
c = new DatabaseIndex();
c.Name = name;
c.SchemaOwner = schema;
c.TableName = tableName;
if (indexKeyMap.Typekey != null)
{
c.IndexType = row[indexKeyMap.Typekey].ToString();
}
if (FindBoolean(row, indexKeyMap.UniqueKey, "UNIQUE"))
{
c.IsUnique = true;
c.IndexType = "UNIQUE";
}
if (FindBoolean(row, indexKeyMap.PrimaryKey, String.Empty))
{
c.IndexType = "PRIMARY"; //primary keys should be unique too
}
indexes.Add(c);
}
if (string.IsNullOrEmpty(indexKeyMap.ColumnKey))
{
continue;
}
string colName = row[indexKeyMap.ColumnKey].ToString();
if (string.IsNullOrEmpty(colName))
{
continue;
}
var column = new DatabaseColumn();
column.Name = colName;
if (!string.IsNullOrEmpty(indexKeyMap.OrdinalKey))
{
int ordinal = Convert.ToInt32(row[indexKeyMap.OrdinalKey], CultureInfo.CurrentCulture);
column.Ordinal = ordinal;
}
if (ContainsColumn(c.Columns, colName))
{
continue;
}
c.Columns.Add(column);
if (c.Columns.Count > 1 && column.Ordinal != 0)
{
//the order of the datatable may be wrong
c.Columns.Sort((x, y) => x.Ordinal.CompareTo(y.Ordinal));
}
}
}
protected override string ColumnDataType(DatabaseColumn column)
{
return(_dataTypeWriter.WriteDataType(column));
}
protected virtual void GetColumns(
DbConnection connection,
IReadOnlyList <DatabaseTable> tables,
Func <string, string, bool> tableFilter,
string defaultCharSet,
string defaultCollation)
{
foreach (var table in tables)
{
var columnTypeOverrides = GetColumnTypeOverrides(connection, table);
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetColumnsQuery, table.Name);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var name = reader.GetValueOrDefault <string>("COLUMN_NAME");
var defaultValue = reader.GetValueOrDefault <string>("COLUMN_DEFAULT");
var nullable = reader.GetValueOrDefault <bool>("IS_NULLABLE");
var dataType = reader.GetValueOrDefault <string>("DATA_TYPE");
var charset = reader.GetValueOrDefault <string>("CHARACTER_SET_NAME");
var collation = reader.GetValueOrDefault <string>("COLLATION_NAME");
var columnType = reader.GetValueOrDefault <string>("COLUMN_TYPE");
var extra = reader.GetValueOrDefault <string>("EXTRA");
var comment = reader.GetValueOrDefault <string>("COLUMN_COMMENT");
// Generated colums are not supported on every MySQL/MariaDB version.
var generation = reader.HasName("GENERATION_EXPRESSION")
? reader.GetValueOrDefault <string>("GENERATION_EXPRESSION").NullIfEmpty()
: null;
// MariaDB does not support SRID column restrictions.
var srid = reader.HasName("SRS_ID")
? reader.GetValueOrDefault <uint?>("SRS_ID")
: null;
var isStored = generation != null
? (bool?)extra.Contains("stored generated", StringComparison.OrdinalIgnoreCase)
: null;
// Override this column's type, if we detected earlier that this column should actually by added to the model
// with a different type than the one returned by INFORMATION_SCHEMA.COLUMNS.
// This ensures, that e.g. the `json` alias for the `longtext` type for MariaDB databases will be added to the
// model as `json` instead of as `longtext`.
columnType = columnTypeOverrides.TryGetValue(name, out var columnTypeOverride)
? columnTypeOverride((dataType: dataType, charset: charset, collation: collation))
: columnType;
// MySQL enforces the `utf8mb4` charset and `utf8mb4_bin` collation for `json` columns and MariaDB will use them
// automatically for `json` columns as well.
// Both will refuse explicit specifications of other charsets/collations, even though `json` is just an alias
// for `longtext` for MariaDB and setting `longtext` to other charsets/collations works fine.
// We therefore do not scaffold thouse charsets/collations in the first place, so that users don't get confused.
if (columnType == "json")
{
charset = null;
collation = null;
}
// MySQL saves the generation expression with enclosing parenthesis, while MariaDB doesn't.
generation = generation != null &&
_options.ServerVersion.Supports.ParenthesisEnclosedGeneratedColumnExpressions
? Regex.Replace(generation, @"^\((.*)\)$", "$1", RegexOptions.Singleline)
: generation;
var isDefaultValueSqlFunction = IsDefaultValueSqlFunction(defaultValue, dataType);
var isDefaultValueExpression = false;
if (defaultValue != null)
{
// MySQL 8.0.13+ fully supports complex default value expressions.
isDefaultValueExpression = extra.Contains("DEFAULT_GENERATED", StringComparison.OrdinalIgnoreCase) &&
!IsSimpleNumericDefaultValue(defaultValue);
// MariaDB uses a slightly different syntax.
defaultValue = _options.ServerVersion.Supports.AlternativeDefaultExpression
? ConvertDefaultValueFromMariaDbToMySql(defaultValue, out isDefaultValueExpression)
: defaultValue;
defaultValue = generation == null
? FilterClrDefaults(
dataType,
nullable,
defaultValue)
: null;
}
ValueGenerated?valueGenerated;
if (extra.IndexOf("auto_increment", StringComparison.Ordinal) >= 0)
{
valueGenerated = ValueGenerated.OnAdd;
}
else if (extra.IndexOf("on update", StringComparison.Ordinal) >= 0)
{
if (defaultValue != null && extra.IndexOf(defaultValue, StringComparison.Ordinal) > 0 ||
(string.Equals(dataType, "timestamp", StringComparison.OrdinalIgnoreCase) ||
string.Equals(dataType, "datetime", StringComparison.OrdinalIgnoreCase)) &&
extra.IndexOf("CURRENT_TIMESTAMP", StringComparison.OrdinalIgnoreCase) > 0)
{
valueGenerated = ValueGenerated.OnAddOrUpdate;
}
else
{
valueGenerated = ValueGenerated.OnUpdate;
}
}
else
{
// Using `null` results in `ValueGeneratedNever()` being output for primary keys without
// auto increment as desired, while explicitly using `ValueGenerated.Never` results in
// no value generated output at all.
valueGenerated = null;
}
var column = new DatabaseColumn
{
Table = table,
Name = name,
StoreType = columnType,
IsNullable = nullable,
DefaultValueSql = CreateDefaultValueString(defaultValue, dataType, isDefaultValueSqlFunction, isDefaultValueExpression),
ComputedColumnSql = generation,
IsStored = isStored,
ValueGenerated = valueGenerated,
Comment = string.IsNullOrEmpty(comment)
? null
: comment,
[MySqlAnnotationNames.CharSet] = Settings.CharSet &&
charset != (table[MySqlAnnotationNames.CharSet] as string ?? defaultCharSet)
? charset
: null,
Collation = Settings.Collation &&
collation != (table[RelationalAnnotationNames.Collation] as string ?? defaultCollation)
? collation
: null,
[MySqlAnnotationNames.SpatialReferenceSystemId] = srid.HasValue
? (int?)(int)srid.Value
: null,
};
table.Columns.Add(column);
}
}
}
}
}
public static IQuery Where <T>(DatabaseColumn column, T equalsValue)
{
return(new DeleteQueryBuilder <T>(column, equalsValue).Build( ));
}
public static IKeywordGenerator getGenerator(string keyword, baseContext ctx, DatabaseColumn databaseColumn)
{
Regex r = new Regex(@"integer ?[0-9]*,?[0-9]*,?[0-9]*( u)?");
if (r.IsMatch(keyword))
{
return(new IntegerGenerator(keyword));
}
keyword = keyword.ToLower();
keyword = keyword.Trim();
string[] keywords;
bool[] uniques;
// Look for combined keywords
if (keyword.Contains('|'))
{
keywords = keyword.Split('|');
}
else
{
keywords = new string[1];
keywords[0] = keyword;
}
uniques = new bool[keywords.Length];
List <List <string> > listsOfValues = new List <List <string> >(keywords.Length);
for (int i = 0; i < keywords.Length; i++)
{
string currentKeyword = keywords[i];
List <string> values;
bool isUnique = currentKeyword.EndsWith(" u");
if (isUnique)
{
currentKeyword = currentKeyword[0..^ 2];
private void GetColumns(
DbConnection connection,
string tableFilter,
DatabaseModel databaseModel)
{
using (var command = (OracleCommand)connection.CreateCommand())
{
command.InitialLONGFetchSize = -1;
command.CommandText = new StringBuilder()
.AppendLine("SELECT")
.AppendLine(" t.tablespace_name,")
.AppendLine(" c.table_name,")
.AppendLine(" c.column_name,")
.AppendLine(" c.column_id,")
.AppendLine(" c.data_type,")
.AppendLine(" c.data_length,")
.AppendLine(" c.data_precision,")
.AppendLine(" c.data_scale,")
.AppendLine(" c.nullable,")
.AppendLine(" c.identity_column,")
.AppendLine(" c.data_default,")
.AppendLine(" c.virtual_column")
.AppendLine("FROM user_tab_cols c")
.AppendLine("INNER JOIN user_tables t ")
.AppendLine("ON UPPER(t.table_name)=UPPER(c.table_name)")
.AppendLine(tableFilter)
.AppendLine("ORDER BY c.column_id")
.ToString();
using (var reader = command.ExecuteReader())
{
var tableColumnGroups = reader.Cast <DbDataRecord>()
.GroupBy(
ddr => (tableSchema: ddr.GetValueOrDefault <string>("tablespace_name"),
tableName: ddr.GetValueOrDefault <string>("table_name")));
foreach (var tableColumnGroup in tableColumnGroups)
{
var tableSchema = tableColumnGroup.Key.tableSchema;
var tableName = tableColumnGroup.Key.tableName;
var table = databaseModel.Tables.Single(t => t.Schema == tableSchema && t.Name == tableName);
foreach (var dataRecord in tableColumnGroup)
{
var columnName = dataRecord.GetValueOrDefault <string>("column_name");
var ordinal = dataRecord.GetValueOrDefault <int>("column_id");
var dataTypeName = dataRecord.GetValueOrDefault <string>("data_type");
var maxLength = dataRecord.GetValueOrDefault <int>("data_length");
var precision = dataRecord.GetValueOrDefault <int>("data_precision");
var scale = dataRecord.GetValueOrDefault <int>("data_scale");
var isNullable = dataRecord.GetValueOrDefault <string>("nullable").Equals("Y");
var isIdentity = dataRecord.GetValueOrDefault <string>("identity_column").Equals("YES");
var defaultValue = !isIdentity?dataRecord.GetValueOrDefault <string>("data_default") : null;
var computedValue = dataRecord.GetValueOrDefault <string>("virtual_column").Equals("YES") ? defaultValue : null;
var storeType = GetOracleClrType(dataTypeName, maxLength, precision, scale);
if (string.IsNullOrWhiteSpace(defaultValue) ||
!string.IsNullOrWhiteSpace(computedValue))
{
defaultValue = null;
}
_logger.ColumnFound(
DisplayName(tableSchema, tableName),
columnName,
ordinal,
dataTypeName,
maxLength,
precision,
scale,
isNullable,
isIdentity,
defaultValue,
computedValue);
var column = new DatabaseColumn
{
Table = table,
Name = columnName,
StoreType = storeType,
IsNullable = isNullable,
DefaultValueSql = defaultValue,
ComputedColumnSql = computedValue,
ValueGenerated = isIdentity
? ValueGenerated.OnAdd
: default(ValueGenerated?)
};
table.Columns.Add(column);
}
}
}
}
}
public DatabaseColumn Column(string columnName)
{
DatabaseColumn columnConfig = new DatabaseColumn(columnName);
Columns.Add(columnConfig);
return columnConfig;
}
