private async Task <TableInfo> CreateTableInfo(IContext context)
{
var tableName = await context.ResolveTableNameAsync(TableName);
var idColumnCount = Columns.Count(x => x.IsIdentity);
if (idColumnCount > 1)
{
throw new InvalidOperationException("More than one IDENTITY column specified.");
}
bool ignoreCase = context.IgnoreCase();
var columnChecker = new TableColumnChecker(Columns, ignoreCase);
var tableInfo = new TableInfo(tableName);
foreach (var column in Columns)
{
var columnInfo = CreateColumnInfo(context, tableName.Name, column, columnChecker);
if (column.IsIdentity)
{
columnInfo.DefaultValue = SqlExpression.Function("UNIQUEKEY", SqlExpression.Constant(tableName.ToString()));
}
tableInfo.Columns.Add(columnInfo);
}
return(tableInfo);
}
void AddCrossTabColumns(SelectQuery query, PivotColumn column)
{
foreach (PivotColumnValue val in column.Values)
{
query.Columns.Add(new SelectColumn(SqlExpression.Function(valueFuntion, PivotCaseExpression(column, val)), val.CrossTabColumnName));
}
}
/// <summary>
/// Creates a SelectColumn with a column name, table, column alias and optional aggregation function
/// </summary>
/// <param name="columnName">Name of a column</param>
/// <param name="table">The table this field belongs to</param>
/// <param name="columnAlias">Alias of the column</param>
/// <param name="function">Aggregation function to be applied to the column. Use SqlAggregationFunction.None to specify that no function should be applied.</param>
public SelectColumn(string columnName, FromTerm table, string columnAlias, SqlAggregationFunction function)
{
if (function == SqlAggregationFunction.None)
{
expr = SqlExpression.Field(columnName, table);
}
else
{
expr = SqlExpression.Function(function, SqlExpression.Field(columnName, table));
}
this.alias = columnAlias;
}
public void MakeFullGroupTable()
{
var exp = SqlExpression.Function(new ObjectName("count"),
new InvokeArgument(SqlExpression.Reference(ObjectName.Parse("tab1.a"))));
var cols = new[] {
new FunctionColumnInfo(exp, "exp1", PrimitiveTypes.Integer())
};
var table = new GroupTable(context, left, cols, new ObjectName[0]);
Assert.Equal(2, table.RowCount);
}
public async void GroupId()
{
var function = SqlExpression.Function(new ObjectName("group_id"));
var result = await function.ReduceAsync(context);
Assert.NotNull(result);
Assert.IsType <SqlConstantExpression>(result);
var value = ((SqlConstantExpression)result).Value;
Assert.Equal(SqlObject.Integer(0), value);
}
public async void Sum()
{
var function = SqlExpression.Function(new ObjectName("sum"),
new[] { new InvokeArgument(SqlExpression.Reference(new ObjectName("a"))) });
var result = await function.ReduceAsync(context);
Assert.NotNull(result);
Assert.IsType <SqlConstantExpression>(result);
var value = ((SqlConstantExpression)result).Value;
Assert.Equal(SqlObject.Integer(47), value);
}
public async void StDev()
{
var function = SqlExpression.Function(new ObjectName("stdev"),
new[] { new InvokeArgument(SqlExpression.Reference(new ObjectName("a"))) });
var result = await function.ReduceAsync(context);
Assert.NotNull(result);
Assert.IsType <SqlConstantExpression>(result);
var value = ((SqlConstantExpression)result).Value;
Assert.Equal(SqlObject.Numeric((SqlNumber)30.40559159102154), value);
}
static void TestExpression2()
{
EntitySchemaManager.LoadEntity <SceneObject>();
var view = EntitySchemaManager.GetSchema <SceneObject>(true);
var dbProvider = new Framework.Data.MsSql.MsSqlConnectionProvider(1, "");
var commandStruct = dbProvider.CreateCommand <SceneObject>(view.Name, DbCommandMode.Select);
commandStruct.Columns.Add(SqlExpression.As(SqlExpression.Function("count", SqlExpression.Symbol("*")), "Count"));
var fields = new List <long>()
{
4, 3
};
commandStruct.Where(p => (p.Function == null || fields.Contains(p.ID)) && !p.Deleted);
commandStruct.OrderBy(p => p.ID);
var commandText = commandStruct.CommandText;
}
public async Task GroupMaxOverGroupBy()
{
var exp = SqlExpression.Function(new ObjectName("count"),
new InvokeArgument(SqlExpression.Reference(ObjectName.Parse("tab1.a"))));
var cols = new[] {
new FunctionColumnInfo(exp, "exp1", PrimitiveTypes.Integer())
};
var table = new GroupTable(context, left, cols, new[] { ObjectName.Parse("tab1.a") });
var groupMax = table.GroupMax(ObjectName.Parse("tab1.a"));
Assert.NotNull(groupMax);
Assert.Equal(1, groupMax.RowCount);
var value = await groupMax.GetValueAsync(0, 0);
Assert.NotNull(value);
Assert.False(value.IsFalse);
Assert.Equal(SqlObject.Integer(54), value);
}
