public void Returns_unique_name_for_type()
{
var namer = new CSharpUniqueNamer<ColumnModel>(s => s.Name);
var input1 = new ColumnModel
{
Name = "Id"
};
var input2 = new ColumnModel
{
Name = "Id"
};
Assert.Equal("Id", namer.GetName(input1));
Assert.Equal("Id", namer.GetName(input1));
Assert.Equal("Id1", namer.GetName(input2));
}
public static SqlServerColumnModelAnnotations SqlServer([NotNull] this ColumnModel column) => new SqlServerColumnModelAnnotations(column);
private void GetColumns()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT DISTINCT
schema_name(t.schema_id) AS [schema],
t.name AS [table],
type_name(c.user_type_id) AS [typename],
c.name AS [column_name],
c.column_id AS [ordinal],
c.is_nullable AS [nullable],
CAST(ic.key_ordinal AS int) AS [primary_key_ordinal],
object_definition(c.default_object_id) AS [default_sql],
CAST(CASE WHEN c.precision <> tp.precision
THEN c.precision
ELSE null
END AS int) AS [precision],
CAST(CASE WHEN c.scale <> tp.scale
THEN c.scale
ELSE null
END AS int) AS [scale],
CAST(CASE WHEN c.max_length <> tp.max_length
THEN c.max_length
ELSE null
END AS int) AS [max_length],
c.is_identity,
c.is_computed
FROM sys.index_columns ic
RIGHT JOIN (SELECT * FROM sys.indexes WHERE is_primary_key = 1) AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
RIGHT JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
RIGHT JOIN sys.types tp ON tp.user_type_id = c.user_type_id
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE t.name <> '" + HistoryRepository.DefaultTableName + "'";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var schemaName = reader.GetString(0);
var tableName = reader.GetString(1);
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
var dataTypeName = reader.GetString(2);
var nullable = reader.GetBoolean(5);
var maxLength = reader.IsDBNull(10) ? default(int?) : reader.GetInt32(10);
if (dataTypeName == "nvarchar"
|| dataTypeName == "nchar")
{
maxLength /= 2;
}
if (dataTypeName == "decimal"
|| dataTypeName == "numeric")
{
// maxlength here represents storage bytes. The server determines this, not the client.
maxLength = null;
}
var isIdentity = !reader.IsDBNull(11) && reader.GetBoolean(11);
var isComputed = reader.GetBoolean(12) || dataTypeName == "timestamp";
var table = _tables[TableKey(tableName, schemaName)];
var column = new ColumnModel
{
Table = table,
DataType = dataTypeName,
Name = reader.GetString(3),
Ordinal = reader.GetInt32(4) - 1,
IsNullable = nullable,
PrimaryKeyOrdinal = reader.IsDBNull(6) ? default(int?) : reader.GetInt32(6),
DefaultValue = reader.IsDBNull(7) ? null : reader.GetString(7),
Precision = reader.IsDBNull(8) ? default(int?) : reader.GetInt32(8),
Scale = reader.IsDBNull(9) ? default(int?) : reader.GetInt32(9),
MaxLength = maxLength <= 0 ? default(int?) : maxLength,
IsIdentity = isIdentity,
ValueGenerated = isIdentity ?
ValueGenerated.OnAdd :
isComputed ?
ValueGenerated.OnAddOrUpdate : default(ValueGenerated?)
};
table.Columns.Add(column);
_tableColumns.Add(ColumnKey(table, column.Name), column);
}
}
}
public SqlServerColumnModelAnnotations([NotNull] ColumnModel column)
{
Check.NotNull(column, nameof(column));
_column = column;
}
private PropertyBuilder VisitDefaultValue(ColumnModel column, PropertyBuilder propertyBuilder)
{
if (column.DefaultValue != null)
{
// unset default
propertyBuilder.Metadata.ValueGenerated = null;
propertyBuilder.Metadata.Relational().GeneratedValueSql = null;
var property = propertyBuilder.Metadata;
var defaultExpressionOrValue =
_sqlServerLiteralUtilities
.ConvertSqlServerDefaultValue(
property.ClrType, column.DefaultValue);
if (defaultExpressionOrValue?.DefaultExpression != null)
{
propertyBuilder.HasDefaultValueSql(defaultExpressionOrValue.DefaultExpression);
}
else if (defaultExpressionOrValue != null)
{
// Note: defaultExpressionOrValue.DefaultValue == null is valid
propertyBuilder.HasDefaultValue(defaultExpressionOrValue.DefaultValue);
}
else
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToConvertDefaultValue(
column.DisplayName, column.DefaultValue,
property.ClrType, property.Name, property.DeclaringEntityType.Name));
}
}
return propertyBuilder;
}
private PropertyBuilder VisitTypeMapping(PropertyBuilder propertyBuilder, ColumnModel column)
{
if (column.IsIdentity == true)
{
if (typeof(byte) == propertyBuilder.Metadata.ClrType)
{
Logger.LogWarning(
SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy(
column.DisplayName, column.DataType));
}
else
{
propertyBuilder
.ValueGeneratedOnAdd()
.UseSqlServerIdentityColumn();
}
}
if (_dateTimePrecisionTypes.Contains(column.DataType)
&& column.Scale.HasValue
&& column.Scale != DefaultDateTimePrecision)
{
propertyBuilder.Metadata.SetMaxLength(null);
propertyBuilder.HasColumnType($"{column.DataType}({column.Scale})"); //not a typo: .Scale is the right property for datetime precision
}
// undo quirk in reverse type mapping to litters code with unnecessary nvarchar annotations
if (typeof(string) == propertyBuilder.Metadata.ClrType
&& propertyBuilder.Metadata.Relational().ColumnType == "nvarchar")
{
propertyBuilder.Metadata.Relational().ColumnType = null;
}
return propertyBuilder;
}
private void GetForeignKeys()
{
foreach (var dependentTable in _databaseModel.Tables)
{
var fkList = _connection.CreateCommand();
fkList.CommandText = $"PRAGMA foreign_key_list(\"{dependentTable.Name.Replace("\"", "\"\"")}\");";
var tableForeignKeys = new Dictionary<int, ForeignKeyModel>();
using (var reader = fkList.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetInt32((int)ForeignKeyList.Id);
var principalTableName = reader.GetString((int)ForeignKeyList.Table);
ForeignKeyModel foreignKey;
if (!tableForeignKeys.TryGetValue(id, out foreignKey))
{
TableModel principalTable;
_tables.TryGetValue(principalTableName, out principalTable);
foreignKey = new ForeignKeyModel
{
Table = dependentTable,
PrincipalTable = principalTable
};
tableForeignKeys.Add(id, foreignKey);
}
var fromColumnName = reader.GetString((int)ForeignKeyList.From);
foreignKey.Columns.Add(_tableColumns[ColumnKey(dependentTable, fromColumnName)]);
if (foreignKey.PrincipalTable != null)
{
var toColumnName = reader.GetString((int)ForeignKeyList.To);
ColumnModel toColumn;
if(!_tableColumns.TryGetValue(ColumnKey(foreignKey.PrincipalTable, toColumnName), out toColumn))
{
toColumn = new ColumnModel { Name = toColumnName };
}
foreignKey.PrincipalColumns.Add(toColumn);
}
foreignKey.OnDelete = ConvertToReferentialAction(
reader.GetString((int)ForeignKeyList.OnDelete));
}
}
foreach (var foreignKey in tableForeignKeys)
{
dependentTable.ForeignKeys.Add(foreignKey.Value);
}
}
}
private void GetColumns()
{
foreach (var table in _databaseModel.Tables)
{
var command = _connection.CreateCommand();
command.CommandText = $"PRAGMA table_info(\"{table.Name.Replace("\"", "\"\"")}\");";
using (var reader = command.ExecuteReader())
{
var ordinal = 0;
while (reader.Read())
{
var isPk = reader.GetBoolean((int)TableInfoColumns.Pk);
var typeName = reader.GetString((int)TableInfoColumns.Type);
var notNull = isPk || reader.GetBoolean((int)TableInfoColumns.NotNull);
var column = new ColumnModel
{
Table = table,
Name = reader.GetString((int)TableInfoColumns.Name),
DataType = typeName,
PrimaryKeyOrdinal = isPk ? reader.GetInt32((int)TableInfoColumns.Pk) : default(int?),
IsNullable = !notNull,
DefaultValue = reader.GetValue((int)TableInfoColumns.DefaultValue) as string,
Ordinal = ordinal++
};
table.Columns.Add(column);
_tableColumns[ColumnKey(table, column.Name)] = column;
}
}
}
}
