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 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 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);
}
/// <summary>
/// Parse the SQL statement.
/// </summary>
/// <returns>Result of parsing SQL statement</returns>
public SqlParserResult Parse()
{
var sqlNode = PrepareParse();
var builder = new DomaSqlBuilder(sqlNode, _model, _config, _evaluator);
return(builder.Build());
}
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 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 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 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 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 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 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);
}
/// <summary>
/// Parse the SQL statement for "Paginated".
/// </summary>
/// <param name="skip">
/// The number of records to skip.<br/>
/// It's like LINQ.
/// </param>
/// <param name="take">
/// The number of records to gets.<br/>
/// It's like LINQ.
/// </param>
/// <param name="rowNumberColumn">
/// If you want to force the use of the ROW_NUMBER function.<br/>
/// Specify a virtual column name to receive the result of ROW_NUMBER function
/// </param>
/// <returns>Result of parsing SQL statement</returns>
public SqlParserResultPaginated ParsePaginated(int skip, int take, string rowNumberColumn = null)
{
var result = new SqlParserResultPaginated();
var sqlNode = PrepareParse();
var nodeForCount = _config.Dialect.ToCountGettingSqlNode(sqlNode);
var builderForCount = new DomaSqlBuilder(nodeForCount, _model, _config, _evaluator);
result.CountResult = builderForCount.Build();
var nodeForPaging = _config.Dialect.ToPagingSqlNode(sqlNode, skip, take, rowNumberColumn);
var builderForPaging = new DomaSqlBuilder(nodeForPaging, _model, _config, _evaluator);
result.Result = builderForPaging.Build();
return(result);
}
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 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 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 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 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 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 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 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);
}
