public async Task LeftOuterTable()
{
var leftRows = left.Select(x => x.Number);
var rightRows = right.Select(x => x.Number);
var v1 = new VirtualTable(new[] { left, right }, new[] { leftRows, rightRows });
var result = v1.Outer(left);
Assert.NotNull(result);
Assert.Equal(4, result.RowCount);
Assert.Equal(4, result.TableInfo.Columns.Count);
// the outher right join has placed the left table on top
// while the previous merge had the columns at the beginning
// +--------+--------+--------+--------+
// | tab1.a | tab1.b | tab2.a | tab2.b |
// +-----------------------------------+
//
var value1 = await result.GetValueAsync(1, 0);
var value2 = await result.GetValueAsync(1, 1);
Assert.Equal(SqlObject.Integer(54), value1);
Assert.Equal(SqlObject.Boolean(null), value2);
}
public OuterTableTests()
{
var leftInfo = new TableInfo(ObjectName.Parse("tab1"));
leftInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
leftInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
var temp1 = new TemporaryTable(leftInfo);
temp1.AddRow(new [] { SqlObject.Integer(23), SqlObject.Boolean(true) });
temp1.AddRow(new [] { SqlObject.Integer(54), SqlObject.Boolean(null) });
left = temp1;
var rightInfo = new TableInfo(ObjectName.Parse("tab2"));
rightInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
rightInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
var temp2 = new TemporaryTable(rightInfo);
temp2.AddRow(new [] { SqlObject.Integer(15), SqlObject.Boolean(true) });
temp2.AddRow(new [] { SqlObject.Integer(544), SqlObject.Boolean(false) });
right = temp2;
}
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 void GetContinueString(string label, bool?when, string expected)
{
var whenExp = when == null ? null : SqlExpression.Constant(SqlObject.Boolean(when.Value));
var statement = new ContinueStatement(label, whenExp);
var sql = statement.ToString();
Assert.Equal(expected, sql);
}
public static void GetConditionString(bool test, object ifTrue, object ifFalse, string expected)
{
var testExp = SqlExpression.Constant(SqlObject.Boolean(test));
var ifTrueExp = SqlExpression.Constant(SqlObject.New(SqlValueUtil.FromObject(ifTrue)));
var ifFalseExp = SqlExpression.Constant(SqlObject.New(SqlValueUtil.FromObject(ifFalse)));
var condition = SqlExpression.Condition(testExp, ifTrueExp, ifFalseExp);
var sql = condition.ToString();
Assert.Equal(expected, sql);
}
public void SerializeSimpleLoop()
{
var loop = new WhileLoopStatement(SqlExpression.Constant(SqlObject.Boolean(true)));
loop.Statements.Add(new ExitStatement());
var result = BinarySerializeUtil.Serialize(loop);
Assert.NotNull(result);
Assert.NotNull(result.Condition);
Assert.IsType <SqlConstantExpression>(result.Condition);
}
public async void WhileTrueExit()
{
var loop = new WhileLoopStatement(SqlExpression.Constant(SqlObject.Boolean(true)));
loop.Statements.Add(new ExitStatement());
var statement = loop.Prepare(context);
var result = await statement.ExecuteAsync(context);
Assert.Null(result);
}
public static void SerializeCondition(bool test, object ifTrue, object ifFalse)
{
var testExp = SqlExpression.Constant(SqlObject.Boolean(test));
var ifTrueExp = SqlExpression.Constant(SqlObject.New(SqlValueUtil.FromObject(ifTrue)));
var ifFalseExp = SqlExpression.Constant(SqlObject.New(SqlValueUtil.FromObject(ifFalse)));
var condition = SqlExpression.Condition(testExp, ifTrueExp, ifFalseExp);
var result = BinarySerializeUtil.Serialize(condition);
Assert.NotNull(result.Test);
Assert.NotNull(result.IfTrue);
Assert.NotNull(result.IfFalse);
}
public static async Task ReduceGroup()
{
var exp = SqlExpression.Binary(SqlExpressionType.Equal,
SqlExpression.Constant(SqlObject.Boolean(true)),
SqlExpression.Constant(SqlObject.Boolean(false)));
var group = SqlExpression.Group(exp);
var reduced = await group.ReduceAsync(null);
Assert.NotNull(reduced);
Assert.IsType <SqlConstantExpression>(reduced);
Assert.IsType <SqlObject>(((SqlConstantExpression)reduced).Value);
}
public static void IntersectOnSingleKey()
{
var set = new IndexRangeSet();
var result = set.Intersect(SqlExpressionType.Equal, new IndexKey(SqlObject.Boolean(true)));
Assert.NotNull(result);
var ranges = result.ToArray();
Assert.Equal(1, ranges.Length);
Assert.Equal(new IndexKey(SqlObject.Boolean(true)), ranges[0].StartValue);
Assert.Equal(new IndexKey(SqlObject.Boolean(true)), ranges[0].EndValue);
}
public InsertSearchTests()
{
var leftInfo = new TableInfo(ObjectName.Parse("tab1"));
leftInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
leftInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
leftInfo.Columns.Add(new ColumnInfo("c", PrimitiveTypes.Double()));
left = new TemporaryTable(leftInfo);
left.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true), SqlObject.Double(5563.22) });
left.AddRow(new[] { SqlObject.Integer(54), SqlObject.Boolean(null), SqlObject.Double(921.001) });
left.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true), SqlObject.Double(2010.221) });
}
public FilterTableTests()
{
var leftInfo = new TableInfo(ObjectName.Parse("tab1"));
leftInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
leftInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
left = new TemporaryTable(leftInfo);
left.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true) });
left.AddRow(new[] { SqlObject.Integer(54), SqlObject.Boolean(null) });
left.BuildIndex();
}
public FunctionTableTests()
{
var leftInfo = new TableInfo(ObjectName.Parse("tab1"));
leftInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
leftInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
left = new TemporaryTable(leftInfo);
left.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true) });
left.AddRow(new[] { SqlObject.Integer(54), SqlObject.Boolean(null) });
left.BuildIndex();
var scope = new ServiceContainer();
var mock = new Mock <IContext>();
mock.SetupGet(x => x.Scope)
.Returns(scope);
mock.Setup(x => x.Dispose())
.Callback(scope.Dispose);
context = mock.Object;
var group = new List <SqlObject> {
SqlObject.Integer(33),
SqlObject.Integer(22),
SqlObject.Integer(1)
};
var refResolver = new Mock <IReferenceResolver>();
refResolver.Setup(x => x.ResolveType(It.IsAny <ObjectName>()))
.Returns <ObjectName>(name => PrimitiveTypes.Integer());
refResolver.Setup(x => x.ResolveReferenceAsync(It.IsAny <ObjectName>()))
.Returns <ObjectName>(name => Task.FromResult(group[0]));
var resolverMock = new Mock <IGroupResolver>();
resolverMock.SetupGet(x => x.Size)
.Returns(group.Count);
resolverMock.Setup(x => x.ResolveReferenceAsync(It.IsAny <ObjectName>(), It.IsAny <long>()))
.Returns <ObjectName, long>((name, index) => Task.FromResult(group[(int)index]));
resolverMock.Setup(x => x.GetResolver(It.IsAny <long>()))
.Returns(refResolver.Object);
// scope.AddMethodRegistry<SystemFunctionProvider>();
scope.RegisterInstance <IGroupResolver>(resolverMock.Object);
}
public async Task LimitWithTotal()
{
var limited = new LimitedTable(left, -1, 2);
Assert.Equal(2, limited.RowCount);
Assert.Equal(3, limited.TableInfo.Columns.Count);
var value1 = await limited.GetValueAsync(0, 0);
var value2 = await limited.GetValueAsync(0, 1);
Assert.Equal(SqlObject.Integer(23), value1);
Assert.Equal(SqlObject.Boolean(true), value2);
}
public async Task LimitWithOffsetAndTotal()
{
var limited = new LimitedTable(left, 1, 1);
Assert.Equal(1, limited.RowCount);
Assert.Equal(3, limited.TableInfo.Columns.Count);
var value1 = await limited.GetValueAsync(0, 0);
var value2 = await limited.GetValueAsync(0, 1);
Assert.Equal(SqlObject.Integer(54), value1);
Assert.Equal(SqlObject.Boolean(null), value2);
}
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 VirtualTableTests()
{
var leftInfo = new TableInfo(ObjectName.Parse("tab1"));
leftInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
leftInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
var temp = new TemporaryTable(leftInfo);
temp.AddRow(new [] { SqlObject.Integer(23), SqlObject.Boolean(true) });
temp.AddRow(new [] { SqlObject.Integer(54), SqlObject.Boolean(null) });
left = temp;
}
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 void GetStringWithoutLabel()
{
var loop = new WhileLoopStatement(SqlExpression.Constant(SqlObject.Boolean(true)));
loop.Statements.Add(new ExitStatement());
var sql = new StringBuilder();
sql.AppendLine("WHILE TRUE");
sql.AppendLine("LOOP");
sql.AppendLine(" EXIT;");
sql.Append("END LOOP;");
Assert.Equal(sql.ToString(), loop.ToString());
}
public static async Task ReduceCondition(bool test, object ifTrue, object ifFalse, object expected)
{
var testExp = SqlExpression.Constant(SqlObject.Boolean(test));
var ifTrueExp = SqlExpression.Constant(SqlObject.New(SqlValueUtil.FromObject(ifTrue)));
var ifFalseExp = SqlExpression.Constant(SqlObject.New(SqlValueUtil.FromObject(ifFalse)));
var condition = SqlExpression.Condition(testExp, ifTrueExp, ifFalseExp);
var result = await condition.ReduceAsync(null);
Assert.IsType <SqlConstantExpression>(result);
var expectedValue = SqlObject.New(SqlValueUtil.FromObject(expected));
Assert.Equal(expectedValue, ((SqlConstantExpression)result).Value);
}
public void GetLoopString()
{
var loop = new LoopStatement("l1");
loop.Statements.Add(new ExitStatement(SqlExpression.Constant(SqlObject.Boolean(true))));
var expected = new StringBuilder();
expected.AppendLine("<<l1>>");
expected.AppendLine("LOOP");
expected.AppendLine(" EXIT WHEN TRUE;");
expected.Append("END LOOP;");
var sql = loop.ToString();
Assert.Equal(expected.ToString(), sql);
}
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 LimitedTableTests()
{
var leftInfo = new TableInfo(ObjectName.Parse("tab1"));
leftInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
leftInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
leftInfo.Columns.Add(new ColumnInfo("c", PrimitiveTypes.Double()));
var temp = new TemporaryTable(leftInfo);
temp.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true), SqlObject.Double(5563.22) });
temp.AddRow(new[] { SqlObject.Integer(54), SqlObject.Boolean(null), SqlObject.Double(921.001) });
temp.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true), SqlObject.Double(2010.221) });
temp.BuildIndex();
left = temp;
}
public override SqlExpression VisitSimpleCaseExpression(PlSqlParser.SimpleCaseExpressionContext context)
{
var exp = Visit(context.atom());
var switches = new List <CaseSwitch>();
foreach (var partContext in context.simpleCaseWhenExpressionPart())
{
var otherExp = Visit(partContext.conditionWrapper());
switches.Add(new CaseSwitch {
Condition = SqlExpression.Equal(exp, otherExp),
ReturnExpression = Visit(partContext.expressionWrapper())
});
}
if (context.caseElseExpressionPart() != null)
{
var returnExp = Visit(context.caseElseExpressionPart().expressionWrapper());
switches.Add(new CaseSwitch {
Condition = SqlExpression.Constant(SqlObject.Boolean(true)),
ReturnExpression = returnExp
});
}
SqlConditionExpression conditional = null;
for (int i = switches.Count - 1; i >= 0; i--)
{
var current = switches[i];
var condition = SqlExpression.Condition(current.Condition, current.ReturnExpression);
if (conditional != null)
{
conditional = SqlExpression.Condition(current.Condition, current.ReturnExpression, conditional);
}
else
{
conditional = condition;
}
}
return(conditional);
}
public async Task MapTwoColumns()
{
var subset = new SubsetTable(left, new[] { 1, 2 },
new ObjectName[] { ObjectName.Parse("tab1.b1"), ObjectName.Parse("tab1.c1"), });
Assert.Equal(3, subset.RowCount);
Assert.Equal(2, subset.TableInfo.Columns.Count);
Assert.Equal(0, subset.TableInfo.Columns.IndexOf(ObjectName.Parse("tab1.b1")));
Assert.Equal(1, subset.TableInfo.Columns.IndexOf(ObjectName.Parse("tab1.c1")));
Assert.Equal(0, subset.TableInfo.Columns.IndexOf("b1"));
Assert.Equal(1, subset.TableInfo.Columns.IndexOf("c1"));
var value1 = await subset.GetValueAsync(0, 1);
var value2 = await subset.GetValueAsync(2, 0);
Assert.Equal(SqlObject.Double(5563.22), value1);
Assert.Equal(SqlObject.Boolean(true), value2);
}
private async Task <SqlExpression> IsArrayAny(SqlExpressionType opType, SqlObject value, SqlArray array, IContext context)
{
foreach (var item in array)
{
var itemValue = await ItemValue(item, context);
var result = Relational(opType, value, itemValue);
if (result.IsUnknown)
{
return(Constant(SqlObject.Unknown));
}
if (result.IsTrue)
{
return(Constant(SqlObject.Boolean(true)));
}
}
return(Constant(SqlObject.Boolean(false)));
}
public CrossJoinedTableTests()
{
var leftInfo = new TableInfo(ObjectName.Parse("tab1"));
leftInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
leftInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
var left = new TemporaryTable(leftInfo);
left.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true) });
left.AddRow(new[] { SqlObject.Integer(54), SqlObject.Boolean(null) });
var rightInfo = new TableInfo(ObjectName.Parse("tab2"));
rightInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
rightInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
var right = new TemporaryTable(rightInfo);
right.AddRow(new[] { SqlObject.Integer(15), SqlObject.Boolean(true) });
right.AddRow(new[] { SqlObject.Integer(544), SqlObject.Boolean(false) });
table = new CrossJoinedTable(left, right);
}
public override SqlExpression VisitConstantTrue(PlSqlParser.ConstantTrueContext context)
{
return(SqlExpression.Constant(SqlObject.Boolean(true)));
}
public static void VisitConstant()
{
var exp = SqlExpression.Constant(SqlObject.Boolean(false));
Visit(exp);
}
public SqlVariableExpressionTests()
{
var scope = new ServiceContainer();
var manager = new VariableManager();
manager.CreateVariable(new VariableInfo("a", PrimitiveTypes.Boolean(), false, SqlExpression.Constant(SqlObject.Boolean(false))));
manager.CreateVariable(new VariableInfo("b", PrimitiveTypes.VarChar(150), false, null));
var mock = new Mock <IContext>();
mock.SetupGet(x => x.Scope)
.Returns(scope);
mock.As <IVariableScope>()
.SetupGet(x => x.Variables)
.Returns(manager);
context = mock.Object;
}