public void testOffsetOnly()
{
var expected =
"select * from ( select temp_.*, rownum esp_rownumber_ from ( select * from emp order by emp.id ) temp_ ) where esp_rownumber_ > 5";
var transformer = new OraclePagingTransformer(5, -1, null);
var parser = new DomaSqlParser("select * from emp order by emp.id");
var node = transformer.Transform(parser.Parse());
var parameters = new List <ParameterEmulator>();
var builder = new DomaSqlBuilder(node, parameters, _config);
var result = builder.Build();
result.ParsedSql.Is(expected);
}
public void testLimitOnly()
{
var expected =
"select * from ( select temp_.*, row_number() over( order by temp_.id ) as esp_rownumber_ from ( select emp.id from emp ) as temp_ ) as temp2_ where esp_rownumber_ <= 10";
var transformer = new StandardPagingTransformer(-1, 10, null);
var parser = new DomaSqlParser("select emp.id from emp order by emp.id");
var node = transformer.Transform(parser.Parse());
var parameters = new List <ParameterEmulator>();
var builder = new DomaSqlBuilder(node, parameters, _config);
var result = builder.Build();
result.ParsedSql.Is(expected);
}
public void testOffsetLimit_option()
{
var expected =
"select emp.id from emp order by emp.id offset 5 rows fetch next 10 rows only option (maxrecursion 0)";
var transformer = new MssqlPagingTransformer(5, 10, false, null);
var parser = new DomaSqlParser("select emp.id from emp order by emp.id option (maxrecursion 0)");
var node = transformer.Transform(parser.Parse());
var parameters = new List <ParameterEmulator>();
var builder = new DomaSqlBuilder(node, parameters, _config);
var result = builder.Build();
result.ParsedSql.Is(expected);
}
public void testLiteralVariable_endsWithLiteralVariableComment()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = "hoge"
});
var testSql = "select * from aaa where ename = /*^name*/";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <SqlParseException>(() => parser.Parse());
ex.MessageId.Is(ExceptionMessageId.Esp2110);
}
public void testIf_selectClause()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "type", ParameterType = typeof(string), ParameterValue = "a"
});
var testSql = "select /*%if type == \"a\"*/aaa /*%else*/ bbb /*%end*/from ccc";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select aaa from ccc");
result.DebugSql.Is("select aaa from ccc");
}
public void testIf_removeWhere()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = null
});
var testSql = "select * from aaa where /*%if name != null*/bbb = /*name*/'ccc' /*%end*/";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa");
result.DebugSql.Is("select * from aaa");
}
public void testSelectNullLiteral()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = null
});
var testSql = "select /*^ name */'dummy' AS UserName";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select null AS UserName");
result.DebugSql.Is("select null AS UserName");
result.DbDataParameters.Count.Is(0);
}
public void testBindVariable_in_empty_iterable()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string[]), ParameterValue = new List <string>().ToArray()
});
var testSql = "select * from aaa where ename in /*name*/('aaa', 'bbb')";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa where ename in (null)");
result.DebugSql.Is("select * from aaa where ename in (null)");
result.DbDataParameters.Count.Is(0);
}
public void testBindVariable_EndsWith()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = "hoge"
});
var testSql = "select * from aaa where ename like /* @EndsWith(name)*/'aaa'";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa where ename like @name");
result.DebugSql.Is("select * from aaa where ename like '%hoge'");
result.DbDataParameters.Count.Is(1);
result.DbDataParameters[0].Value.Is("%hoge");
}
public void testParens_removeAnd()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = null
});
var testSql =
"select * from aaa where (\n/*%if name != null*/bbb = /*name*/\'ccc\'\n/*%else*/\nand ddd is null\n /*%end*/)";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa where (\n\n ddd is null\n )");
result.DebugSql.Is("select * from aaa where (\n\n ddd is null\n )");
result.DbDataParameters.Count.Is(0);
}
public void testIf_nest()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = "hoge"
});
var testSql =
"select * from aaa where /*%if name != null*/bbb = /*name*/\'ccc\' /*%if name == \"hoge\"*/and ddd = eee/*%end*//*%end*/";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa where bbb = @name and ddd = eee");
result.DebugSql.Is("select * from aaa where bbb = 'hoge' and ddd = eee");
result.DbDataParameters.Count.Is(1);
}
public void testElseifBlock()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = ""
});
var testSql =
"select * from aaa where /*%if name == null*/bbb is null\n/*%elseif name ==\"\"*/\nbbb = /*name*/\'ccc\'/*%end*/";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa where \nbbb = @name");
result.DebugSql.Is("select * from aaa where \nbbb = ''");
result.DbDataParameters.Count.Is(1);
result.DbDataParameters[0].Value.Is("");
}
public void testUpdateNullString()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = null
});
var testSql = "update employee set name = /* name */'dummy'";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("update employee set name = @name");
result.DebugSql.Is("update employee set name = null");
result.DbDataParameters.Count.Is(1);
result.DbDataParameters[0].Value.Is(DBNull.Value);
}
public void testUpdateNullDateTime()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "birthdate", ParameterType = typeof(DateTime?), ParameterValue = null
});
var testSql = "update employee set birthdate = /* @TruncateTime(birthdate) */'2001-01-01'";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("update employee set birthdate = @birthdate");
result.DebugSql.Is("update employee set birthdate = null");
result.DbDataParameters.Count.Is(1);
result.DbDataParameters[0].Value.Is(DBNull.Value);
}
public void testBindVariable_TruncateTime()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "birthDate", ParameterType = typeof(DateTime),
ParameterValue = new DateTime(1999, 9, 9, 10, 20, 30)
});
var testSql = "select * from aaa where birthDate > /* @TruncateTime(birthDate) */'1999-10-12'";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa where birthDate > @birthDate");
result.DebugSql.Is("select * from aaa where birthDate > '1999-09-09'");
result.DbDataParameters.Count.Is(1);
result.DbDataParameters[0].Value.Is(new DateTime(1999, 9, 9));
}
public void testBindVariable_in()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string[]), ParameterValue = new[] { "hoge", "foo" }
});
var testSql = "select * from aaa where ename in /*name*/('aaa', 'bbb')";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa where ename in (@name1, @name2)");
result.DebugSql.Is("select * from aaa where ename in ('hoge', 'foo')");
result.DbDataParameters.Count.Is(2);
result.DbDataParameters[0].Value.Is("hoge");
result.DbDataParameters[1].Value.Is("foo");
}
public void testLiteralVariable()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = "hoge"
});
parameters.Add(new ParameterEmulator
{
Name = "salary", ParameterType = typeof(int), ParameterValue = 10000
});
var testSql = "select * from aaa where ename = /*^name*/'aaa' and sal = /*^salary*/-2000";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa where ename = 'hoge' and sal = 10000");
result.DebugSql.Is("select * from aaa where ename = 'hoge' and sal = 10000");
result.DbDataParameters.Count.Is(0);
}
public void testIf_nestContinuously()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = "hoge"
});
parameters.Add(new ParameterEmulator
{
Name = "name2", ParameterType = typeof(string), ParameterValue = null
});
var testSql =
"select * from aaa where /*%if name != null*//*%if name2 == \"hoge\"*/ ddd = eee/*%end*//*%end*/";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa");
result.DebugSql.Is("select * from aaa");
result.DbDataParameters.Count.Is(0);
}
public void testBindVariable_enum()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(MyEnum), ParameterValue = MyEnum.BBB
});
parameters.Add(new ParameterEmulator
{
Name = "salary", ParameterType = typeof(int), ParameterValue = 10000
});
var testSql = "select * from aaa where ename = /*name*/'aaa' and sal = /*salary*/-2000";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select * from aaa where ename = @name and sal = @salary");
result.DebugSql.Is("select * from aaa where ename = 'BBB' and sal = 10000");
result.DbDataParameters.Count.Is(2);
result.DbDataParameters[0].Value.Is(MyEnum.BBB);
result.DbDataParameters[1].Value.Is(10000);
}
public void testOrderByClauseUnspecified()
{
var transformer = new StandardPagingTransformer(5, 10, null);
var parser = new DomaSqlParser("select * from emp");
var ex = Assert.Throws <SqlTransformException>(() => transformer.Transform(parser.Parse()));
ex.IsNotNull();
ex.MessageId.Is(ExceptionMessageId.Esp2201);
}
