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); }