public static void IndexKeyNotEqualsFirstInSet()
{
var firstInSet = IndexRange.FirstInSet;
var key = new IndexKey(new [] { SqlObject.BigInt(33), SqlObject.Double(54) });
Assert.NotEqual(firstInSet, key);
}
public async Task ExecuteWithNamedArgsAndDefaultValue()
{
var name = ObjectName.Parse("a.func");
var info = new SqlFunctionInfo(name, PrimitiveTypes.Integer());
info.Parameters.Add(new SqlParameterInfo("a", PrimitiveTypes.Integer()));
info.Parameters.Add(new SqlParameterInfo("b",
PrimitiveTypes.String(),
SqlExpression.Constant(SqlObject.String(new SqlString("test")))));
var function = new SqlFunctionDelegate(info, ctx => {
var a = ctx.Value("a");
var b = ctx.Value("b");
Assert.NotNull(b);
return(Task.FromResult(a.Multiply(SqlObject.BigInt(2))));
});
Assert.Equal(name, info.MethodName);
Assert.Equal(FunctionType.Scalar, function.FunctionType);
Assert.NotNull(info.ReturnType);
Assert.Equal(SqlTypeCode.Integer, info.ReturnType.TypeCode);
var result = await function.ExecuteAsync(context, new InvokeArgument("a", SqlObject.Integer(22)));
Assert.NotNull(result);
Assert.True(result.HasReturnedValue);
Assert.NotNull(result.ReturnedValue);
Assert.IsType <SqlConstantExpression>(result.ReturnedValue);
}
public override SqlExpression VisitConstantNumeric(PlSqlParser.ConstantNumericContext context)
{
var value = context.numeric().GetText();
var formatInfo = new NumberFormatInfo {
NumberDecimalSeparator = "."
};
var number = SqlNumber.Parse(value, formatInfo);
SqlObject obj;
if (number.CanBeInt32)
{
obj = SqlObject.Integer((int)number);
}
else if (number.CanBeInt64)
{
obj = SqlObject.BigInt((long)number);
}
else
{
obj = SqlObject.Numeric(number);
}
return(SqlExpression.Constant(obj));
}
public static void VisitCondition()
{
var exp = SqlExpression.Condition(SqlExpression.Constant(SqlObject.Boolean(true)),
SqlExpression.Constant(SqlObject.BigInt(902)),
SqlExpression.Constant(SqlObject.Integer(433)));
Visit(exp);
}
public static void GetNamedString()
{
var function = SqlExpression.Function(ObjectName.Parse("sys.Func1"),
new InvokeArgument("a", SqlObject.BigInt(33)));
const string sql = "sys.Func1(a => 33)";
Assert.Equal(sql, function.ToString());
}
public void SelectLessThan()
{
var index = table.GetIndex(new[] { 0 });
var result = index.SelectLess(SqlObject.BigInt(100)).ToBigArray();
Assert.NotEmpty(result);
Assert.Single(result);
Assert.Equal(0, result[0]);
}
public static void MatchInvoke()
{
var name = ObjectName.Parse("a.proc");
var info = new SqlMethodInfo(name);
info.Parameters.Add(new SqlParameterInfo("a", PrimitiveTypes.Integer()));
var procedure = new SqlProcedureDelegate(info, methodContext => Task.CompletedTask);
var invoke = new Invoke(name, new[] { new InvokeArgument(SqlObject.BigInt(11)) });
Assert.True(procedure.Matches(null, invoke));
}
public void SelectGreaterThanOrEqual()
{
var index = table.GetIndex(new[] { 0 });
var result = index.SelectGreaterOrEqual(SqlObject.BigInt(100)).ToBigArray();
Assert.NotEmpty(result);
Assert.Equal(2, result.Length);
Assert.Equal(1, result[0]);
Assert.Equal(2, result[1]);
}
public static void MatchInvoke()
{
var name = ObjectName.Parse("a.func");
var info = new SqlFunctionInfo(name, PrimitiveTypes.Integer());
info.Parameters.Add(new SqlParameterInfo("a", PrimitiveTypes.Integer()));
var function = new SqlFunctionDelegate(info, context => Task.CompletedTask);
var invoke = new Invoke(name, new [] { new InvokeArgument(SqlObject.BigInt(11)) });
Assert.True(function.Matches(null, invoke));
}
public async void ForwardForLoop()
{
var loop = new ForLoopStatement("i",
SqlExpression.Constant(SqlObject.BigInt(0)),
SqlExpression.Constant(SqlObject.BigInt(5)));
loop.Statements.Add(new ContinueStatement());
var statement = loop.Prepare(context);
var result = await statement.ExecuteAsync(context);
Assert.Null(result);
}
public async void IfTrueReturn()
{
var test = SqlExpression.Constant(SqlObject.Boolean(true));
var condition = new ConditionalStatement(test);
condition.Statements.Add(new ReturnStatement(SqlExpression.Constant(SqlObject.BigInt(33))));
var statement = condition.Prepare(context);
var result = await statement.ExecuteAsync(context);
Assert.NotNull(result);
Assert.IsType <StatementExpressionResult>(result);
Assert.Equal((SqlNumber)33L, ((SqlConstantExpression)((StatementExpressionResult)result).Value).Value.Value);
}
public async void CountAll()
{
var function = SqlExpression.Function(new ObjectName("count"),
new[] { new InvokeArgument(SqlExpression.Reference(new ObjectName("*"))) });
var result = await function.ReduceAsync(context);
Assert.NotNull(result);
Assert.IsType <SqlConstantExpression>(result);
var value = ((SqlConstantExpression)result).Value;
Assert.Equal(SqlObject.BigInt(2), value);
}
public void SerializeForLoop()
{
var loop = new ForLoopStatement("i",
SqlExpression.Constant(SqlObject.BigInt(0)),
SqlExpression.Constant(SqlObject.BigInt(5)),
true);
loop.Statements.Add(new ContinueStatement());
var result = BinarySerializeUtil.Serialize(loop);
Assert.NotNull(result);
Assert.NotNull(result.LowerBound);
Assert.NotNull(result.UpperBound);
}
public static void GetString()
{
var name = ObjectName.Parse("a.func");
var info = new SqlFunctionInfo(name, PrimitiveTypes.Integer());
info.Parameters.Add(new SqlParameterInfo("a", PrimitiveTypes.Integer()));
var function = new SqlFunctionDelegate(info, ctx => {
var a = ctx.Value("a");
return(Task.FromResult(a.Multiply(SqlObject.BigInt(2))));
});
var sql = $"FUNCTION a.func(a INTEGER) RETURNS INTEGER";
Assert.Equal(sql, function.ToString());
}
public void SerializeConditional()
{
var test = SqlExpression.Constant(SqlObject.Boolean(true));
var condition = new ConditionalStatement(test, new ReturnStatement(SqlExpression.Constant(SqlObject.BigInt(100))));
condition.Statements.Add(new ReturnStatement(SqlExpression.Constant(SqlObject.BigInt(33))));
var result = BinarySerializeUtil.Serialize(condition);
Assert.NotNull(result);
Assert.NotNull(result.Condition);
Assert.NotNull(condition.Else);
Assert.NotEmpty(condition.Statements);
Assert.Equal(1, result.Statements.Count);
}
public TableIndexTests()
{
var tableInfo = new TableInfo(ObjectName.Parse("test_table1"));
tableInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.BigInt()));
tableInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.String()));
var temp = new TemporaryTable(tableInfo);
temp.AddRow(new [] { SqlObject.BigInt(22), SqlObject.String("o102") });
temp.AddRow(new [] { SqlObject.BigInt(103), SqlObject.String("t-23") });
temp.AddRow(new[] { SqlObject.BigInt(104), SqlObject.String("test22") });
temp.BuildIndex();
table = temp;
}
public void GetStringWithNoElse()
{
var test = SqlExpression.Constant(SqlObject.Boolean(true));
var condition = new ConditionalStatement(test);
condition.Statements.Add(new ReturnStatement(SqlExpression.Constant(SqlObject.BigInt(33))));
var expected = new StringBuilder();
expected.AppendLine("IF TRUE THEN");
expected.AppendLine(" RETURN 33;");
expected.Append("END IF;");
var sql = condition.ToString();
Assert.Equal(expected.ToString(), sql);
}
public static void GetString()
{
var name = ObjectName.Parse("a.proc");
var info = new SqlMethodInfo(name);
info.Parameters.Add(new SqlParameterInfo("a", PrimitiveTypes.Integer()));
var function = new SqlProcedureDelegate(info, ctx => {
var a = ctx.Value("a");
var b = a.Multiply(SqlObject.BigInt(2));
Console.Out.WriteLine("a * 2 = {0}", b);
});
var sql = $"PROCEDURE a.proc(a INTEGER)";
Assert.Equal(sql, function.ToString());
}
public void GetStringWithoutLabel()
{
var loop = new ForLoopStatement("i",
SqlExpression.Constant(SqlObject.BigInt(0)),
SqlExpression.Constant(SqlObject.BigInt(5)),
true);
loop.Statements.Add(new ContinueStatement());
var sql = new StringBuilder();
sql.AppendLine("FOR i IN 0..5");
sql.AppendLine("LOOP");
sql.AppendLine(" CONTINUE;");
sql.Append("END LOOP;");
Assert.Equal(sql.ToString(), loop.ToString());
}
protected override async Task AfterLoopAsync(StatementContext context)
{
var variable = context.ResolveVariable(IndexName);
var value = await variable.Evaluate(context);
if (Reverse)
{
variable.SetValue(SqlExpression.Subtract(value, SqlExpression.Constant(SqlObject.BigInt(1))), context);
}
else
{
variable.SetValue(SqlExpression.Add(value, SqlExpression.Constant(SqlObject.BigInt(1))), context);
}
// TODO: resolve the variable from the context and increment
await base.AfterLoopAsync(context);
}
public async Task ExecuteWithNamedArgs()
{
var name = ObjectName.Parse("a.func");
var info = new SqlMethodInfo(name);
info.Parameters.Add(new SqlParameterInfo("a", PrimitiveTypes.Integer()));
var procedure = new SqlProcedureDelegate(info, ctx => {
var a = ctx.Value("a");
var b = a.Multiply(SqlObject.BigInt(2));
Console.Out.WriteLine("a * 2 = {0}", b);
});
Assert.Equal(name, info.MethodName);
var result = await procedure.ExecuteAsync(context, new InvokeArgument("a", SqlObject.Integer(22)));
Assert.NotNull(result);
Assert.False(result.HasReturnedValue);
}
public void ResolveDeterministricReturnType()
{
var name = ObjectName.Parse("a.func");
var info = new SqlFunctionInfo(name, new SqlDeterministicType());
info.Parameters.Add(new SqlParameterInfo("a", PrimitiveTypes.Integer()));
info.Parameters.Add(new SqlParameterInfo("b",
PrimitiveTypes.String(),
SqlExpression.Constant(SqlObject.String(new SqlString("test")))));
var function = new SqlFunctionDelegate(info, ctx => {
var a = ctx.Value("a");
var b = ctx.Value("b");
Assert.NotNull(b);
return(Task.FromResult(a.Multiply(SqlObject.BigInt(2))));
});
var returnType = function.ReturnType(context,
new Invoke(name, new[] { new InvokeArgument(SqlObject.Integer(33)), new InvokeArgument(SqlObject.Integer(2)) }));
Assert.Equal(PrimitiveTypes.Integer(), returnType);
}
public void RegisterAndResolveFunction()
{
var name = ObjectName.Parse("a.func");
var info = new SqlFunctionInfo(name, PrimitiveTypes.Integer());
info.Parameters.Add(new SqlParameterInfo("a", PrimitiveTypes.Integer()));
var function = new SqlFunctionDelegate(info, ctx => {
var a = ctx.Value("a");
return(Task.FromResult(a.Multiply(SqlObject.BigInt(2))));
});
registry.Register(function);
var invoke = new Invoke(name);
invoke.Arguments.Add(new InvokeArgument("a", SqlObject.Integer(11)));
var method = (registry as IMethodResolver).ResolveMethod(context, invoke);
Assert.NotNull(method);
Assert.True(method.IsFunction);
Assert.Equal(name, method.MethodInfo.MethodName);
}
public static void VisitQuery()
{
var exp = new SqlQueryExpression();
exp.Items.Add(SqlExpression.Reference(new ObjectName("a")));
exp.From.Table(new ObjectName("b"));
exp.From.Join(JoinType.Left,
SqlExpression.Equal(SqlExpression.Reference(ObjectName.Parse("b.id")), SqlExpression.Reference(ObjectName.Parse("c.b_id"))));
exp.From.Table(new ObjectName("c"));
exp.Where = SqlExpression.GreaterThanOrEqual(SqlExpression.Reference(new ObjectName("a")), SqlExpression.Constant(SqlObject.BigInt(22)));
Visit(exp);
}
private void RegisterAggregates()
{
// COUNT(*)
RegisterAggregate("COUNT", Deterministic("column"), PrimitiveTypes.VarNumeric(),
iterate => {
if (iterate.IsFirst)
{
iterate.SetResult(SqlObject.BigInt(1));
}
else
{
iterate.SetResult(iterate.Accumulation.Add(SqlObject.BigInt(1)));
}
},
initialize => {
var groupResolver = initialize.GetGroupResolver();
var groupSize = groupResolver.Size;
var argRef = (initialize.Input as SqlReferenceExpression)?.ReferenceName;
if (groupSize == 0 || (argRef != null && argRef.IsGlob))
{
initialize.SetResult(SqlExpression.Constant(SqlObject.BigInt(groupSize)), false);
}
return(Task.CompletedTask);
});
// MIN
RegisterAggregate("MIN", Deterministic("column"), PrimitiveTypes.VarNumeric(),
iterate => {
if (iterate.IsFirst)
{
iterate.SetResult(iterate.Current);
}
else if (iterate.Current.LessThan(iterate.Accumulation).IsTrue)
{
iterate.SetResult(iterate.Current);
}
});
// MAX
RegisterAggregate("MAX", Deterministic("column"), PrimitiveTypes.VarNumeric(),
iterate => {
if (iterate.IsFirst)
{
iterate.SetResult(iterate.Current);
}
else if (iterate.Current.GreaterThan(iterate.Accumulation).IsTrue)
{
iterate.SetResult(iterate.Current);
}
else if (iterate.Accumulation.LessThan(iterate.Current).IsTrue)
{
iterate.SetResult(iterate.Accumulation);
}
});
// AVG
RegisterAggregate("AVG", Deterministic("column"), PrimitiveTypes.VarNumeric(),
iterate => {
if (iterate.IsFirst)
{
iterate.SetResult(iterate.Current);
}
else
{
iterate.SetResult(iterate.Current.Add(iterate.Accumulation));
}
}, merge: merge => {
var groupResolver = merge.GetGroupResolver();
var groupSize = groupResolver.Size;
var final = merge.Accumulated.Divide(SqlObject.BigInt(groupSize));
merge.SetOutput(final);
return(Task.CompletedTask);
});
// STDEV
RegisterAggregate("STDEV", Deterministic("column"), PrimitiveTypes.VarNumeric(), iterate => {
var aggregator = (AvgAggregator)iterate.MethodContext.Metadata["aggregator"];
aggregator.Values.Add(iterate.Current);
if (iterate.IsFirst)
{
iterate.SetResult(iterate.Current);
}
else
{
iterate.SetResult(iterate.Current.Add(iterate.Accumulation));
}
}, initialize => {
var groupResolver = initialize.GetGroupResolver();
var aggregator = new AvgAggregator {
Values = new BigList <SqlObject>(groupResolver.Size)
};
initialize.MethodContext.Metadata["aggregator"] = aggregator;
return(Task.CompletedTask);
}, merge => {
var groupResolver = merge.GetGroupResolver();
var groupSize = groupResolver.Size;
var aggregator = (AvgAggregator)merge.MethodContext.Metadata["aggregator"];
var avg = merge.Accumulated.Divide(SqlObject.BigInt(groupSize));
var sums = aggregator.Values.Select(x => SqlMath.Pow((SqlNumber)x.Subtract(avg).Value, (SqlNumber)2));
var sum = SqlNumber.Zero;
foreach (var number in sums)
{
sum += number;
}
var ret = SqlMath.Sqrt(sum / (SqlNumber)(groupSize - 1));
merge.SetOutput(SqlObject.Numeric(ret));
return(Task.CompletedTask);
});
// SUM
RegisterAggregate("SUM", Deterministic("column"), PrimitiveTypes.VarNumeric(),
iterate => {
if (iterate.IsFirst)
{
iterate.SetResult(iterate.Current);
}
else
{
iterate.SetResult(iterate.Current.Add(iterate.Accumulation));
}
});
// LAST
RegisterAggregate("LAST", Deterministic("column"), new SqlDeterministicType(),
iterate => {
var groupResolver = iterate.GetGroupResolver();
var groupSize = groupResolver.Size;
if (iterate.Offset == groupSize - 1)
{
iterate.SetResult(iterate.Current);
}
else
{
iterate.SetResult(SqlObject.Null);
}
});
// FIRST
RegisterAggregate("FIRST", Deterministic("column"), new SqlDeterministicType(),
iterate => {
if (iterate.IsFirst)
{
iterate.SetResult(iterate.Current, false);
}
});
RegisterAggregate("GROUP_ID", PrimitiveTypes.Integer(),
iterate => {
// no-op
}, merge: merge => {
var groupResolver = merge.GetGroupResolver();
merge.SetOutput(SqlObject.Integer(groupResolver.GroupId));
return(Task.CompletedTask);
});
}
public static void VisitQuantify()
{
var exp = SqlExpression.Quantify(SqlExpressionType.All,
SqlExpression.Equal(SqlExpression.Constant(SqlObject.BigInt(43)),
SqlExpression.Constant(SqlObject.Array(SqlObject.BigInt(33), SqlObject.Integer(1222)))));
Visit(exp);
}
public static void VisitGroup()
{
var exp = SqlExpression.Group(SqlExpression.Constant(SqlObject.BigInt(345)));
Visit(exp);
}
