public TypeDefinition(
string schema,
string name,
byte systemTypeId,
int userTypeId,
short maxLength,
byte precision,
byte scale,
string collation,
bool isNullable
)
{
Schema = schema ?? "";
Name = name ?? "";
FullName = Schema + "." + Name;
SystemTypeId = systemTypeId;
UserTypeId = userTypeId;
MaxLength = maxLength;
Precision = precision;
Scale = scale;
Collation = collation ?? "";
IsNullable = isNullable;
SqlTypeInfo typeInfo;
s_typeInfoLookup.TryGetValue(SystemTypeId, out typeInfo);
if (typeInfo == null)
{
typeInfo = SqlTypeInfo.Empty;
}
TypeInfo = typeInfo;
m_asString = "TD." + FullName;
}
internal static GroupByStage CreateStaticGroupBy(
Type inputType,
SqlTypeInfo previousStageTypeInfo,
ParameterExpression previousStageParameterExpression,
FromAliases fromAliases)
{
var tupleType = GetTupleType(typeof(int));
var groupingType = GetGroupingType(tupleType, inputType);
//There is no group by, so there is no key or possibility to select anything else than aggregations
var keyTypeInfo = SqlTypeInfo.NewBuilder().Build();
var groupParameter = Expression.Parameter(groupingType);
var keyParameterExpression = GetKeyParameterExpression(groupingType, groupParameter);
var constructor = tupleType.GetConstructor(new[] { typeof(int) });
var groupByExpression = Expression.New(constructor, new[] { Expression.Constant(1) });
return(new GroupByStage(
groupingType,
inputType,
previousStageTypeInfo,
keyTypeInfo,
groupParameter,
keyParameterExpression,
previousStageParameterExpression,
groupByExpression,
tupleType,
fromAliases
));
}
public override void WriteDeclareSqlVariable(string name, Type type, SqlTypeInfo sqlTypeInfo, StringBuilder writer)
{
var propertyTypeInfo = type.GetTypeInfo();
var nullable = (propertyTypeInfo.IsGenericType && propertyTypeInfo.GetGenericTypeDefinition() == typeof(Nullable <>));
var baseType = nullable ? Nullable.GetUnderlyingType(type) : type;
writer.Append("DECLARE @");
writer.Append(name);
writer.Append(" ");
WriteColumnType(baseType, sqlTypeInfo, writer);
writer.AppendLine(";");
}
protected virtual SqlType MapSqlType(SqlTypeInfo sqlTypeInfo, bool isNullable, int?length = null, int?scale = null)
{
var sqlType = new SqlType
{
Length = length,
Scale = scale,
IsNullable = isNullable,
SqlTypeInfo = sqlTypeInfo
};
return(sqlType);
}
public DistinctStage(
SqlTypeInfo sqlTypeInfo,
ParameterExpression parameterExpression,
Type type,
FromAliases fromAliases
)
{
TypeInfo = sqlTypeInfo;
ParameterExpression = parameterExpression;
CurrentType = type;
FromAliases = fromAliases;
}
public static SqlTypeInfo ToSqlTypeInfo(this Type type)
{
var properties = type.GetProperties();
var builder = SqlTypeInfo.NewBuilder();
foreach (var property in properties)
{
builder.AddProperty(property.Name, property);
}
return(builder.Build());
}
public OrderByStage(
Type currentType,
SqlTypeInfo typeInfo,
ParameterExpression parameterExpression,
FromAliases fromAliases,
IImmutableList <SortItem> sortItems)
{
CurrentType = currentType;
TypeInfo = typeInfo;
ParameterExpression = parameterExpression;
FromAliases = fromAliases;
SortItems = sortItems;
}
public static GroupByStage GetGroupByStage(
IQueryStage previousStage,
GroupByClause groupByClause,
HashSet <PropertyInfo> usedProperties,
VisitorMetadata visitorMetadata)
{
GroupByVisitor groupByVisitor = new GroupByVisitor(previousStage, visitorMetadata);
groupByClause.Accept(groupByVisitor);
foreach (var property in groupByVisitor.UsedProperties)
{
usedProperties.Add(property);
}
var expressions = groupByVisitor.GroupByExpressions;
var propertyTypes = expressions.Select(x => x.Expression.Type).ToArray();
var tupleType = GetTupleType(propertyTypes);
var builder = SqlTypeInfo.NewBuilder();
var tupleProperties = tupleType.GetProperties();
for (int i = 0; i < expressions.Count; i++)
{
builder.AddProperty(expressions[i].Name, tupleProperties[i], expressions[i].OriginalProperty);
}
var groupingType = GetGroupingType(tupleType, previousStage.CurrentType);
var constructor = tupleType.GetConstructor(propertyTypes);
var groupByExpression = Expression.New(constructor, expressions.Select(x => x.Expression));
var groupParameter = Expression.Parameter(groupingType);
var keyPropertyInfo = groupingType.GetProperty("Key");
var keyParameterExpression = Expression.MakeMemberAccess(groupParameter, keyPropertyInfo);
return(new GroupByStage(
groupingType,
previousStage.CurrentType,
previousStage.TypeInfo,
builder.Build(),
groupParameter,
keyParameterExpression,
previousStage.ParameterExpression,
groupByExpression,
tupleType,
previousStage.FromAliases
));
}
public WhereStage(
SqlTypeInfo sqlTypeInfo,
ParameterExpression parameterExpression,
Expression whereExpression,
Type type,
FromAliases fromAliases,
bool containsFullTextSearch
)
{
TypeInfo = sqlTypeInfo;
ParameterExpression = parameterExpression;
WhereExpression = whereExpression;
CurrentType = type;
FromAliases = fromAliases;
ContainsFullTextSearch = containsFullTextSearch;
}
public OffsetStage(
SqlTypeInfo sqlTypeInfo,
ParameterExpression parameterExpression,
Type type,
FromAliases fromAliases,
int?skip,
int?take
)
{
TypeInfo = sqlTypeInfo;
ParameterExpression = parameterExpression;
CurrentType = type;
FromAliases = fromAliases;
Skip = skip;
Take = take;
}
public SelectStage(
SqlTypeInfo sqlTypeInfo,
ParameterExpression parameterExpression,
MemberInitExpression selectExpression,
Type newType,
Type oldType,
ParameterExpression inParameterExpression,
FromAliases fromAliases,
IImmutableList <ColumnMetadata> columns)
{
TypeInfo = sqlTypeInfo;
ParameterExpression = parameterExpression;
SelectExpression = selectExpression;
CurrentType = newType;
OldType = oldType;
InParameterExpression = inParameterExpression;
FromAliases = fromAliases;
Columns = columns;
}
public static SelectAggregateFunctionStage GetSelectAggregateFunctionStage(
IQueryStage previousStage,
IList <SqlParser.Expressions.SelectExpression> selectElements,
VisitorMetadata visitorMetadata,
HashSet <PropertyInfo> usedProperties
)
{
SingleAggregateVisitor singleAggregateVisitor = new SingleAggregateVisitor(
previousStage,
visitorMetadata
);
foreach (var selectElement in selectElements)
{
selectElement.Accept(singleAggregateVisitor);
}
foreach (var usedProperty in singleAggregateVisitor.UsedProperties)
{
usedProperties.Add(usedProperty);
}
var typeBuilder = SqlTypeInfo.NewBuilder();
var anonType = AnonType.GetAnonType(singleAggregateVisitor.OutType);
var propertyInfo = anonType.GetProperty($"P0");
typeBuilder.AddProperty(singleAggregateVisitor.ColumnName, propertyInfo);
return(new SelectAggregateFunctionStage(
typeBuilder.Build(),
Expression.Parameter(anonType),
anonType,
previousStage.CurrentType,
previousStage.FromAliases,
singleAggregateVisitor.FunctionName,
previousStage.ParameterExpression,
singleAggregateVisitor.ColumnName,
singleAggregateVisitor.Parameters,
singleAggregateVisitor.OutType
));
}
public GroupedOrderByStage(
Type currentType,
Type valueType,
SqlTypeInfo mainTypeInfo,
SqlTypeInfo keyTypeInfo,
ParameterExpression groupParameterExpression,
Expression keyParameterExpression,
ParameterExpression valueParameterExpression,
FromAliases fromAliases,
IImmutableList <SortItem> sortItems)
{
CurrentType = currentType;
ValueType = valueType;
TypeInfo = mainTypeInfo;
KeyTypeInfo = keyTypeInfo;
ParameterExpression = groupParameterExpression;
KeyParameterExpression = keyParameterExpression;
ValueParameterExpression = valueParameterExpression;
FromAliases = fromAliases;
SortItems = sortItems;
}
public HavingStage(
Type currentType,
Type valueType,
SqlTypeInfo mainTypeInfo,
SqlTypeInfo keyTypeInfo,
ParameterExpression groupParameterExpression,
Expression keyParameterExpression,
ParameterExpression valueParameterExpression,
FromAliases fromAliases,
Expression filterExpression)
{
CurrentType = currentType;
ValueType = valueType;
TypeInfo = mainTypeInfo;
KeyTypeInfo = keyTypeInfo;
ParameterExpression = groupParameterExpression;
KeyParameterExpression = keyParameterExpression;
ValueParameterExpression = valueParameterExpression;
FromAliases = fromAliases;
FilterExpression = filterExpression;
}
public FromTableStage(
string tableName,
SqlTypeInfo sqlTypeInfo,
ParameterExpression parameterExpression,
Type currentType,
FromAliases fromAliases,
IReadSqlParameters parameters)
{
Debug.Assert(tableName != null, $"{nameof(tableName)} was null");
Debug.Assert(sqlTypeInfo != null, $"{nameof(sqlTypeInfo)} was null");
Debug.Assert(parameterExpression != null, $"{nameof(parameterExpression)} was null");
Debug.Assert(currentType != null, $"{nameof(currentType)} was null");
Debug.Assert(fromAliases != null, $"{nameof(fromAliases)} was null");
Debug.Assert(parameters != null, $"{nameof(parameters)} was null");
TableName = tableName;
TypeInfo = sqlTypeInfo;
ParameterExpression = parameterExpression;
CurrentType = currentType;
FromAliases = fromAliases;
Parameters = parameters;
}
public bool InsertManyExtra()
{
Random random = new Random(125125);
SqlTypeInfo info = ExtraCrud.TypeInfo <Employee>();
List <SqlColumn> columns = info.Columns.Where(c => !c.IsAutoKey).ToList();
int max = (2050 / columns.Count) * columns.Count;
StringBuilder sb = new StringBuilder("INSERT INTO [dbo].[Employees] (" + string.Join(",", columns.Select(c => c.ColumnName)) + @") VALUES
");
DynamicParameters dynParams = new DynamicParameters();
for (int j = 0; j < max;)
{
sb.Append('(');
Employee employee = Employee.Create(random);
for (int k = 0; k < columns.Count; k++, j++)
{
string name = "@p" + j;
sb.Append(name).Append(',');
dynParams.Add(name, columns[k].Getter(employee));
}
sb.Remove(sb.Length - 1, 1).Append(@"),");
}
string cmd = sb.Remove(sb.Length - 1, 1).ToString();
using (SqlConnection conn = new SqlConnection(ConnString)) {
for (int i = 0; i < Count; i += max)
{
conn.Execute(cmd, dynParams);
}
try {
conn.Truncate <Employee>();
}
catch {
conn.DeleteList <Employee>();
}
}
return(true);
}
public GroupByStage(
Type currentType,
Type valueType,
SqlTypeInfo mainTypeInfo,
SqlTypeInfo keyTypeInfo,
ParameterExpression groupParameterExpression,
Expression keyParameterExpression,
ParameterExpression valueParameterExpression,
Expression groupByExpression,
Type keyType,
FromAliases fromAliases)
{
CurrentType = currentType;
ValueType = valueType;
TypeInfo = mainTypeInfo;
KeyTypeInfo = keyTypeInfo;
ParameterExpression = groupParameterExpression;
KeyParameterExpression = keyParameterExpression;
ValueParameterExpression = valueParameterExpression;
GroupByExpression = groupByExpression;
KeyType = keyType;
FromAliases = fromAliases;
}
public SelectAggregateFunctionStage(
SqlTypeInfo sqlTypeInfo,
ParameterExpression parameterExpression,
Type currentType,
Type oldType,
FromAliases fromAliases,
string functionName,
ParameterExpression inParameter,
string columnName,
IImmutableList <Expression> parameters,
Type functionOutType
)
{
TypeInfo = sqlTypeInfo;
ParameterExpression = parameterExpression;
CurrentType = currentType;
FromAliases = fromAliases;
FunctionName = functionName;
InParameter = inParameter;
OldType = oldType;
ColumnName = columnName;
Parameters = parameters;
FunctionOutType = functionOutType;
}
private static AssignmentResult Check(SqlTypeInfo to, SqlTypeInfo from)
{
return to.CheckAssignment(0, "x", from);
}
public DeclareVariableStatement(string variableName, SqlTypeInfo sqlTypeInfo)
{
VariableName = variableName;
SqlTypeInfo = sqlTypeInfo;
}
public override void WriteColumnType(Type type, SqlTypeInfo sqlTypeInfo, StringBuilder writer)
{
if (type == typeof(sbyte))
{
writer.Append("TINYINT");
}
else if (type == typeof(byte))
{
writer.Append("TINYINT UNSIGNED");
}
else if (type == typeof(short))
{
writer.Append("SMALLINT");
}
else if (type == typeof(ushort))
{
writer.Append("SMALLINT UNSIGNED");
}
else if (type == typeof(int))
{
writer.Append("INT");
}
else if (type == typeof(uint))
{
writer.Append("INT UNSIGNED");
}
else if (type == typeof(long))
{
writer.Append("BIGINT");
}
else if (type == typeof(ulong))
{
writer.Append("BIGINT UNSIGNED");
}
else if (type == typeof(decimal))
{
writer.Append($"DECIMAL({sqlTypeInfo.DecimalPrecision}, {sqlTypeInfo.DecimalScale})");
}
else if (type == typeof(float))
{
writer.Append("REAL");
}
else if (type == typeof(double))
{
writer.Append("REAL");
}
else if (type == typeof(string))
{
var length = sqlTypeInfo.StringLength > 0 ? sqlTypeInfo.StringLength.ToString() : "1024";
writer.Append($"VARCHAR({length})");
}
else if (type == typeof(DateTime))
{
writer.Append("DATETIME");
}
else if (type == typeof(bool))
{
writer.Append("BIT");
}
else if (type == typeof(byte[]))
{
writer.Append("MEDIUMBLOB");
}
else
{
throw new InvalidOperationException("FormatType unsupported type " + type.ToString());
}
}
public abstract void WriteDeclareSqlVariable(string name, Type type, SqlTypeInfo sqlTypeInfo, StringBuilder writer);
public override void WriteDeclareSqlVariable(string name, Type type, SqlTypeInfo sqlTypeInfo, StringBuilder writer)
{
// NOTE: TODO: declare in sp, not in sesssion
// throw new NotImplementedException();
}
public override void WriteColumnType(Type type, SqlTypeInfo sqlTypeInfo, StringBuilder writer)
{
// https://www.postgresql.org/docs/9.1/datatype.html
// https://www.npgsql.org/doc/types/basic.html
if (type == typeof(sbyte))
{
throw new InvalidOperationException("Signed byte is not supported in PostgreSQL");
}
else if (type == typeof(byte))
{
// NOTE: Returns type of larger size!
// throw new InvalidOperationException("Unsigned byte is not supported in PostgreSQL");
writer.Append("SMALLINT");
}
else if (type == typeof(short))
{
writer.Append("SMALLINT");
}
else if (type == typeof(ushort))
{
throw new InvalidOperationException("Unsigned short is not supported in PostgreSQL");
}
else if (type == typeof(int))
{
writer.Append("INT");
}
else if (type == typeof(uint))
{
throw new InvalidOperationException("Unsigned int is not supported in PostgreSQL");
}
else if (type == typeof(long))
{
writer.Append("BIGINT");
}
else if (type == typeof(ulong))
{
throw new InvalidOperationException("Unsigned long is not supported in PostgreSQL");
}
else if (type == typeof(decimal))
{
writer.Append($"DECIMAL({sqlTypeInfo.DecimalPrecision}, {sqlTypeInfo.DecimalScale})");
}
else if (type == typeof(float))
{
writer.Append("REAL");
}
else if (type == typeof(double))
{
writer.Append("DOUBLE PRECISION");
}
else if (type == typeof(string))
{
if (sqlTypeInfo.StringLength > 0)
{
writer.Append($"VARCHAR({sqlTypeInfo.StringLength})");
}
else
{
writer.Append("VARCHAR");
}
}
else if (type == typeof(DateTime))
{
writer.Append("TIMESTAMP");
}
else if (type == typeof(bool))
{
writer.Append("BOOLEAN");
}
else if (type == typeof(byte[]))
{
writer.Append("BYTEA");
}
else
{
throw new InvalidOperationException("FormatType unsupported type " + type.ToString());
}
}
public override void WriteDeclareSqlVariable(string name, Type type, SqlTypeInfo sqlTypeInfo, StringBuilder writer)
{
// OK: No-op
}
public DeclareVariableStatement(string variableName)
{
VariableName = variableName;
SqlTypeInfo = new SqlTypeInfo();
}
public abstract void WriteColumnType(Type type, SqlTypeInfo sqlTypeInfo, StringBuilder writer);
public override void WriteColumnType(Type type, SqlTypeInfo sqlTypeInfo, StringBuilder writer)
{
if (type == typeof(sbyte))
{
throw new InvalidOperationException("Signed byte is not supported in SQL Server");
}
else if (type == typeof(byte))
{
writer.Append("TINYINT");
}
else if (type == typeof(short))
{
writer.Append("SMALLINT");
}
else if (type == typeof(ushort))
{
throw new InvalidOperationException("Unsigned short is not supported in SQL Server");
}
else if (type == typeof(int))
{
writer.Append("INT");
}
else if (type == typeof(uint))
{
throw new InvalidOperationException("Unsigned int is not supported in SQL Server");
}
else if (type == typeof(long))
{
writer.Append("BIGINT");
}
else if (type == typeof(ulong))
{
throw new InvalidOperationException("Unsigned long is not supported in SQL Server");
}
else if (type == typeof(decimal))
{
writer.Append($"DECIMAL({sqlTypeInfo.DecimalPrecision}, {sqlTypeInfo.DecimalScale})");
}
else if (type == typeof(float))
{
writer.Append("REAL");
}
else if (type == typeof(double))
{
writer.Append("DOUBLE PRECISION");
}
else if (type == typeof(string))
{
var length = sqlTypeInfo.StringLength > 0 ? sqlTypeInfo.StringLength.ToString() : "MAX";
var stringType = sqlTypeInfo.StringNVarChar ? "NVARCHAR" : "VARCHAR";
writer.Append($"{stringType}({length})");
}
else if (type == typeof(DateTime))
{
writer.Append("DATETIME2");
}
else if (type == typeof(bool))
{
writer.Append("BIT");
}
else if (type == typeof(byte[]))
{
writer.Append("VARBINARY(MAX)");
}
else
{
throw new InvalidOperationException("FormatType unsupported type " + type.ToString());
}
}
public static SelectStage GetSelectStage(
IQueryStage previousStage,
IList <SqlParser.Expressions.SelectExpression> selectElements,
VisitorMetadata visitorMetadata,
HashSet <PropertyInfo> usedProperties)
{
ISelectVisitor selectVisitor;
if (previousStage is GroupedStage groupedStage)
{
selectVisitor = new SelectAggregationVisitor(groupedStage, visitorMetadata);
}
else
{
selectVisitor = new SelectPlainVisitor(previousStage, visitorMetadata);
}
foreach (var selectElement in selectElements)
{
selectVisitor.VisitSelect(selectElement);
}
foreach (var property in selectVisitor.UsedProperties)
{
usedProperties.Add(property);
}
var selects = selectVisitor.SelectExpressions;
Debug.Assert(selects != null);
Debug.Assert(selects.Count > 0);
var typeBuilder = SqlTypeInfo.NewBuilder();
Type[] propertyTypes = new Type[selects.Count];
for (int i = 0; i < selects.Count; i++)
{
propertyTypes[i] = selects[i].Expression.Type;
}
var anonType = AnonType.GetAnonType(propertyTypes);
var getDelegates = AnonTypeUtils.GetDelegates(anonType);
NewExpression newExpression = Expression.New(anonType);
List <MemberAssignment> assignments = new List <MemberAssignment>();
var columnsBuilder = ImmutableList.CreateBuilder <ColumnMetadata>();
for (int i = 0; i < selects.Count; i++)
{
columnsBuilder.Add(new ColumnMetadata(selects[i].Alias, selects[i].Expression.Type, getDelegates[i]));
var propertyInfo = anonType.GetProperty($"P{i}");
var assignment = Expression.Bind(propertyInfo, selects[i].Expression);
assignments.Add(assignment);
typeBuilder.AddProperty(selects[i].Alias, propertyInfo);
}
var memberInit = Expression.MemberInit(newExpression, assignments);
return(new SelectStage(typeBuilder.Build(),
Expression.Parameter(anonType),
memberInit,
anonType,
previousStage.CurrentType,
previousStage.ParameterExpression,
previousStage.FromAliases,
columnsBuilder.ToImmutable()));
}
