public void testEmbeddedVariable_inside()
{
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
});
parameters.Add(new ParameterEmulator
{
Name = "table", ParameterType = typeof(string), ParameterValue = "aaa"
});
var testSql = "select * from /*# table */ 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 = 'hoge' and sal = 10000");
result.DbDataParameters.Count.Is(2);
result.DbDataParameters[0].Value.Is("hoge");
result.DbDataParameters[1].Value.Is(10000);
}
public void testValidate_enclosedParensByIfBlock()
{
var parser = new DomaSqlParser("select * from /*%if true*/(select * from bbb)/*%end*/");
var node = parser.Parse();
node.IsNotNull();
}
public void testSelect()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = "hoge"
});
parameters.Add(new ParameterEmulator
{
Name = "count", ParameterType = typeof(int), ParameterValue = 5
});
var testSql =
"select aaa.deptname, count(*) from aaa join bbb on aaa.id = bbb.id where aaa.name = /*name*/'ccc' group by aaa.deptname having count(*) > /*count*/10 order by aaa.name for update bbb";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select aaa.deptname, count(*) from aaa join bbb on aaa.id = bbb.id where aaa.name = @name group by aaa.deptname having count(*) > @count order by aaa.name for update bbb");
result.DebugSql.Is("select aaa.deptname, count(*) from aaa join bbb on aaa.id = bbb.id where aaa.name = 'hoge' group by aaa.deptname having count(*) > 5 order by aaa.name for update bbb");
result.DbDataParameters.Count.Is(2);
result.DbDataParameters[0].Value.Is("hoge");
result.DbDataParameters[1].Value.Is(5);
}
internal ISqlNode PrepareParse()
{
if (!File.Exists(_sqlFilePath))
{
throw new InvalidSqlFileException(ExceptionMessageId.EspC001, _sqlFilePath);
}
var rawSql = File.ReadAllText(_sqlFilePath);
if (string.IsNullOrWhiteSpace(rawSql))
{
throw new InvalidSqlFileException(ExceptionMessageId.EspC002, _sqlFilePath);
}
if (_config == null)
{
throw new InvalidSqlParserConfigException(ExceptionMessageId.EspD001);
}
if (_config.DbConnectionKind == DbConnectionKind.Unknown)
{
throw new InvalidSqlParserConfigException(ExceptionMessageId.EspD002);
}
if (_config.DataParameterCreator == null)
{
throw new InvalidSqlParserConfigException(ExceptionMessageId.EspD003);
}
_evaluator = new EasyExpressionEvaluator(_sqlFilePath);
ISqlNode sqlNode = null;
var hasCache = false;
if (ConfigContainer.EnableCache)
{
if (SqlCache.TryGetValue(_sqlFilePath, out SqlFileInfo info))
{
sqlNode = info.SqlNode;
hasCache = true;
}
}
if (!hasCache)
{
var parser = new DomaSqlParser(rawSql);
sqlNode = parser.Parse();
if (ConfigContainer.EnableCache)
{
var info = new SqlFileInfo
{
FilePath = _sqlFilePath,
RawSql = rawSql,
SqlNode = sqlNode
};
SqlCache.TryAdd(_sqlFilePath, info);
}
}
return(sqlNode);
}
public void testUpdate()
{
var parameters = new List <ParameterEmulator>();
parameters.Add(new ParameterEmulator
{
Name = "no", ParameterType = typeof(int), ParameterValue = 10
});
parameters.Add(new ParameterEmulator
{
Name = "name", ParameterType = typeof(string), ParameterValue = "hoge"
});
parameters.Add(new ParameterEmulator
{
Name = "id", ParameterType = typeof(int), ParameterValue = 100
});
var testSql = "update aaa set no = /*no*/1, set name = /*name*/'name' where id = /*id*/1";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("update aaa set no = @no, set name = @name where id = @id");
result.DebugSql.Is("update aaa set no = 10, set name = 'hoge' where id = 100");
result.DbDataParameters.Count.Is(3);
result.DbDataParameters[0].Value.Is(10);
result.DbDataParameters[1].Value.Is("hoge");
result.DbDataParameters[2].Value.Is(100);
}
public void testValidate_unclosedParens()
{
var parser = new DomaSqlParser("select * from (select * from bbb");
var ex = Assert.Throws <SqlParseException>(() => parser.Parse());
ex.MessageId.Is(ExceptionMessageId.Esp2135);
}
public void testValidate_ifEnd_subquery()
{
var parser = new DomaSqlParser("select *, (select /*%if true */ from aaa) x from aaa");
var ex = Assert.Throws <SqlParseException>(() => parser.Parse());
ex.MessageId.Is(ExceptionMessageId.Esp2133);
}
public void BuiltinFunctionArgsCountTooMany()
{
var testSql = "select * from aaa where birthDate > /* @TruncateTime(birthDate, '!') */'1999-10-12'";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <SqlParseException>(() => parser.Parse());
ex.MessageId.Is(ExceptionMessageId.Esp2151);
}
public void InvalidEscapeChar2()
{
var testSql = "select * from aaa where birthDate > /* @StartsWith(birthDate, '!!) */'1999-10-12'";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <SqlParseException>(() => parser.Parse());
ex.MessageId.Is(ExceptionMessageId.Esp2152);
}
public void testValidate_forEnd()
{
var testSql = "select * from aaa /*%for name : names*/";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <UnsupportedSqlCommentException>(() => parser.Parse());
ex.IsNotNull();
}
public void UnknownBuiltinFunction()
{
var testSql = "select * from aaa where birthDate > /* @Dummy(birthDate) */'1999-10-12'";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <SqlParseException>(() => parser.Parse());
ex.MessageId.Is(ExceptionMessageId.Esp2150);
}
public void testPopulate()
{
var testSql = "update employee set /*%populate*/ id = id where age < 30";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <UnsupportedSqlCommentException>(() => parser.Parse());
ex.IsNotNull();
}
public void testBindVariable_nullLiteral()
{
var testSql = "select * from aaa where ename = /*name*/null";
var parser = new DomaSqlParser(testSql);
var node = parser.Parse();
node.IsNotNull();
}
public void testLiteralVariable_illegalLiteral()
{
var testSql = "select * from aaa where ename = /*^name*/bbb";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <SqlParseException>(() => parser.Parse());
ex.MessageId.Is(ExceptionMessageId.Esp2142);
}
public void testManyEol()
{
var sql = File.ReadAllText("manyEol.sql");
var parser = new DomaSqlParser(sql);
var node = parser.Parse();
node.IsNotNull();
}
public void testExpand_word()
{
var testSql = "select /*%expand*/'hoge' from aaa";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <UnsupportedSqlCommentException>(() => parser.Parse());
ex.IsNotNull();
}
public void testFor_index()
{
var testSql = "select * from aaa where /*%for n : names*/name/*# n_index */ = /*n*/'a' /*%if n_has_next */or /*%end*//*%end*/";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <UnsupportedSqlCommentException>(() => parser.Parse());
ex.IsNotNull();
}
public void testEmbeddedVariable_emptyName()
{
var testSql = "select * from aaa where ename = /*# */'aaa'";
var parser = new DomaSqlParser(testSql);
var ex = Assert.Throws <SqlParseException>(() => parser.Parse());
ex.MessageId.Is(ExceptionMessageId.Esp2121);
}
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);
}
public void testEmptyParens_whiteSpace()
{
var parameters = new List <ParameterEmulator>();
var parser = new DomaSqlParser("select rank( )");
var node = parser.Parse();
var builder = new DomaSqlBuilder(node, parameters);
var result = builder.Build();
result.ParsedSql.Is("select rank( )");
}
public void testUnion()
{
var parameters = new List <ParameterEmulator>();
var testSql = "select * from aaa where /*%if false*//*%end*/union all select * from 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 union all select * from bbb");
}
public void testOrderBy()
{
var expected = "select count(*) from ( select * from emp ) t_";
var transformer = new StandardCountGettingTransformer();
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 emp.id from emp order by emp.id fetch first 10 rows only";
var transformer = new Db2PagingTransformer(-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 testLimitOnly()
{
var expected = "select sql_calc_found_rows * from emp order by emp.id limit 0, 10";
var transformer = new MysqlPagingTransformer(-1, 10, 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 testOffsetOnly()
{
var expected = "select * from emp order by emp.id limit 9223372036854775807 offset 5";
var transformer = new SqlitePagingTransformer(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 testOffsetLimit_forUpdate()
{
var expected = "select * from emp order by emp.id limit 10 offset 5 for update";
var transformer = new SqlitePagingTransformer(5, 10, null);
var parser = new DomaSqlParser("select * from emp order by emp.id for update");
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 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 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 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 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);
}
