public void Utility_ReadAll2_Test()
{
var sut = new Utility()
{
ConnectionString = TestCfg.Get().ConnectionString
};
sut.ReadAll();
var db = sut.ModelDatabase;
/*
* SELECT [SCHEMA] = SCHEMA_NAME(schema_id),name,type,type_desc from sys.objects where is_ms_shipped=0
* dbo synonyma SN SYNONYM
* dbo ScalarFunctionNameA FN SQL_SCALAR_FUNCTION
* dbo TableFunctionNameA TF SQL_TABLE_VALUED_FUNCTION
* dbo Name U USER_TABLE
* dbo PK_Name PK PRIMARY_KEY_CONSTRAINT
* dbo NameA V VIEW
* dbo inlineFuncA IF SQL_INLINE_TABLE_VALUED_FUNCTION
* dbo NameValue U USER_TABLE
* dbo PK_NameValue PK PRIMARY_KEY_CONSTRAINT
* dbo p P SQL_STORED_PROCEDURE
* dbo proca P SQL_STORED_PROCEDURE
*/
{
var guest = db.Schemas.GetValueOrDefault(SqlName.Schema("guest"));
Assert.NotNull(guest);
}
{
var t = db.Tables.GetValueOrDefault(SqlName.Parse("dbo.NameValue", ObjectLevel.Object));
Assert.NotNull(t);
Assert.Equal(4, t.Columns.Count);
}
{
var t = db.Tables.GetValueOrDefault(SqlName.Parse("dbo.Name", ObjectLevel.Object));
Assert.NotNull(t);
Assert.Equal(3, t.Columns.Count);
Assert.Equal("idx", t.Columns[0].Name.Name);
Assert.Equal("name", t.Columns[1].Name.Name);
Assert.Equal("RowVersion", t.Columns[2].Name.Name);
}
{
var v = db.Views.GetValueOrDefault(SqlName.Parse("dbo.NameA", ObjectLevel.Object));
Assert.NotNull(v);
Assert.Equal(3, v.Columns.Count);
}
{
var p = db.Procedures.GetValueOrDefault(SqlName.Parse("dbo.proca", ObjectLevel.Object));
Assert.NotNull(p);
}
{
var p = db.Synonyms.GetValueOrDefault(SqlName.Parse("dbo.synonyma", ObjectLevel.Object));
Assert.NotNull(p);
}
{
#warning here TableTypes SQL
//var p = db.TableTypes.GetValueOrDefault(SqlName.Parse("[dbo].[TVP_Name]", ObjectLevel.Object));
//Assert.NotNull(p);
}
}
public void BuildModelSqlDatabase(
ModelSchema modelSchema,
ModelSqlDatabase modelDatabase,
MetaModelBuilder metaModelBuilder,
ModelErrors errors)
{
foreach (var modelEntitySource in modelSchema.Entities)
{
var modelEntityTypeSource = modelEntitySource.EntityType;
if (modelEntityTypeSource is null)
{
#warning SOON add error
}
else
{
var tableNameTarget = modelEntitySource.ExternalName ?? modelEntitySource.Name;
var sqlTableNameTarget = SqlName.Parse(tableNameTarget, ObjectLevel.Object);
var tableTarget = ModelSqlTable.Ensure(modelDatabase, sqlTableNameTarget);
foreach (var property in modelEntityTypeSource.Properties)
{
var sqlColumnNameTarget = SqlName.Parse(property.ExternalName ?? property.Name, ObjectLevel.Object);
ModelSqlColumn column = ModelSqlColumn.Ensure(tableTarget, sqlColumnNameTarget.Name);
if (property.Type is ModelScalarType propertyScalarType)
{
var clrTypeSource = property.GetClrType();
var typeName = propertyScalarType.Name;
#warning TODO SOON better Scalar Type Handling this is ugly
if (!(clrTypeSource is null))
{
var innerNullableClrTypeSource = ((clrTypeSource.IsValueType) ? Nullable.GetUnderlyingType(clrTypeSource) : null) ?? clrTypeSource;
if (!(column.SqlType is null))
{
var clrScalarTypeTarget = column.SqlType.GetScalarType()?.GetClrType();
var innerNullableClrScalarTypeTarget = ((clrScalarTypeTarget.IsValueType) ? Nullable.GetUnderlyingType(clrScalarTypeTarget) : null) ?? clrScalarTypeTarget;
if (!(clrScalarTypeTarget is null) && (clrScalarTypeTarget.IsAssignableFrom(innerNullableClrScalarTypeTarget)))
{
// ok
}
else
{
column.SqlType = null;
}
}
}
if (column.SqlType is null)
{
var sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse(typeName, ObjectLevel.Object));
if (!(sqlTypeTarget is null))
{
column.SqlType = sqlTypeTarget;
}
else
{
if (!(clrTypeSource is null))
{
var innerNullableClrTypeSource = ((clrTypeSource.IsValueType) ? Nullable.GetUnderlyingType(clrTypeSource) : null) ?? clrTypeSource;
var lstTypes = new List <ModelSqlType>();
foreach (var type in modelDatabase.Types)
{
var clrScalarTypeTarget = type.GetScalarType()?.GetClrType();
if (clrScalarTypeTarget is null)
{
continue;
}
var innerNullableClrScalarTypeTarget = ((clrScalarTypeTarget.IsValueType) ? Nullable.GetUnderlyingType(clrScalarTypeTarget) : null) ?? clrScalarTypeTarget;
if (innerNullableClrTypeSource.Equals(innerNullableClrScalarTypeTarget))
{
lstTypes.Add(type);
}
else if (innerNullableClrTypeSource.IsAssignableFrom(innerNullableClrScalarTypeTarget))
{
lstTypes.Add(type);
}
}
if (lstTypes.Count == 1)
{
sqlTypeTarget = lstTypes[0];
column.SqlType = sqlTypeTarget;
}
else if (lstTypes.Count > 1)
{
sqlTypeTarget = null;
if (clrTypeSource == typeof(string))
{
sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse("[sys].[nvarchar]", ObjectLevel.Object));
}
else if (clrTypeSource == typeof(DateTime))
{
sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse("[sys].[datetime2]", ObjectLevel.Object));
}
else if (clrTypeSource == typeof(DateTime?))
{
sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse("[sys].[datetime2]", ObjectLevel.Object));
}
if (sqlTypeTarget is null)
{
sqlTypeTarget = lstTypes[0];
}
column.SqlType = sqlTypeTarget;
}
else
{
errors.Add(new ModelErrorInfo($"Unknown mapping for {typeName} - {clrTypeSource}.", column.NameSql));
sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse("[sys].[nvarchar]", ObjectLevel.Object));
column.SqlType = sqlTypeTarget;
}
}
}
sqlTypeTarget = column.SqlType;
if (sqlTypeTarget is null)
{
errors.Add(new ModelErrorInfo($"column.SqlType is null.", column.NameSql));
}
else
{
sqlTypeTarget.Nullable = propertyScalarType.Nullable.GetValueOrDefault(true);
if (propertyScalarType.MaxLength.HasValue)
{
sqlTypeTarget.MaxLength = propertyScalarType.MaxLength;
}
else
{
//if (typeof(string).Equals(sqlTypeTarget.GetScalarType().GetClrType())) {
// sqlTypeTarget.MaxLength = -1;
//}
}
}
} // if (column.SqlType is null)
}