|
1
6 package tests.jfun.parsec;
7
8 import junit.framework.Test;
9 import junit.framework.TestCase;
10 import junit.framework.TestSuite;
11 import jfun.parsec.ParserException;
12 import java.io.IOException  ;
13 import java.io.FileReader ;
14
15 import tests.jfun.parsec.mssql.SqlParser;
16 import tests.jfun.utils.Utils;
17
18
23 public final class SqlParserTestCase extends TestCase {
24
25 public static final class HardcodeTest extends TestCase{
26
27 public void test1(){
28 testGood("( (1+2)* 3 )", "(1+2)*3");
29 }
30 public void test2(){
31 testGood("( 1*(2+ -+-.3*-30)%++ -4.1 )", "1*(2+-+-.3*-30)%++-4.1");
32 }
33 public void test3(){
34 testGood("case 1 WHEN 1 then 1 else (1+1)*2 END", "case 1 when 1 then 1 else (1+1)*2 end");
35 }
36 public void test4(){
37 testGood("case when 1 is null then null else 0 end",
38 "case when 1 is null then null else 0 end");
39 }
40 public void test5(){
41 final String src = "case when 1 is NULL then null else CASE getdate() when today.date then 1 else 1 end end";
42 testGood(src,
43 "case when 1 is null then null else case getdate() when today.date then 1 else 1 end end");
44 }
45 public void test6(){
46 final String src = "1+case when ((1>2 or getdate(1,2) in (2, 3)) and tablea.cola between 'abc' and 'cde') then 1 else 0 end+2";
47 testGood(src,
49 "1+case when (1>2 or getdate(1, 2) in (2, 3)) and tablea.cola between 'abc' and 'cde' then 1 else 0 end+2");
50 }
51 public void test7(){
52 final String src = "select 1";
53 testGoodSelect(src, "select 1");
54 }
55 public void test8(){
56 final String src = "select 1+1 from tableA";
57 testGoodSelect(src, "select 1+1 from tableA");
58 }
59 public void test9(){
60 final String src = "select 1+1 from tableA, tableB where 1=0 or 1<>0";
61 testGoodSelect(src, "select 1+1 from tableA,tableB where 1=0 or 1<>0");
62 }
63 public void test10(){
64 final String src = "select 1+1 from tableA, tableB where 1=0 or 1<>0 group by 1 having 1>0";
65 testGoodSelect(src, "select 1+1 from tableA,tableB where 1=0 or 1<>0 group by 1 having 1>0");
66 }
67 public void test11(){
68 final String src = "select 1+1 from tableA, tableB where 1=0 or 1<>0 group by 1 having 1>0 order by 1 asc, a desc";
69 testGoodSelect(src, "select 1+1 from tableA,tableB where 1=0 or 1<>0 group by 1 having 1>0 order by 1 asc,a desc");
70 }
71 public void test12(){
72 final String src = "select 1+1 from tableA, tableB inner join tableC a on 1!=0 where 1=0 or 1<>0 group by 1 having 1>0 order by 1 asc, a desc";
73 testGoodSelect(src, "select 1+1 from tableA,(tableB inner join tableC as a on 1<>0) where 1=0 or 1<>0 group by 1 having 1>0 order by 1 asc,a desc");
74 }
75 public void test13(){
76 final String src = "select 1+1 from (select 1) a --";
77 testGoodSelect(src, "select 1+1 from (select 1) as a");
78 }
79 public void test14(){
81 final String src = "select 1+1 from ta left outer join tb inner join tc on 1=0 on 1<0";
82 testGoodSelect(src, "select 1+1 from (ta left outer join (tb inner join tc on 1=0) on 1<0)");
83 }
84 public void test15(){
85 final String src = "select 1+1 from ta left outer join tb on 1>0 inner join tc on 1=0";
86 testGoodSelect(src, "select 1+1 from ((ta left outer join tb on 1>0) inner join tc on 1=0)");
87 }
88 public void test16(){
89 final String src = "select 1+1 from ta cross join tb inner join tc on 1=0";
90 testGoodSelect(src, "select 1+1 from ((ta cross join tb) inner join tc on 1=0)");
91 }
92 public void test17(){
93 final String src = "select 1+1 from ta left outer join tb inner join tc on 1=0 on 1<0 where not exists (select *)";
94 testGoodSelect(src, "select 1+1 from (ta left outer join (tb inner join tc on 1=0) on 1<0) where not exists (select *)");
95 }
96 public void test18(){
97 final String src = "select 1+1, \nfrom ta left join tb inner join tc \n on 1\r\n=0 on 1<0 full join tc c on b.c.a(1,'2')=2 where not exists (select *) or 1 in (select *)";
98 testGoodSelect(src, "select 1+1 from ((ta left outer join (tb inner join tc on 1=0) on 1<0) full outer join tc as c on b.c.a(1, '2')=2) where not exists (select *) or 1 in (select *)");
99 }
100 public void test19(){
101 final String src = "select 1,+1, from /*ok**/(ta left outer join (tb inner join tc on 1=0) on 1<0) where not exists (select *) or 1 not in (1,2,)--hehe";
102 testGoodSelect(src, "select 1,+1 from (ta left outer join (tb inner join tc on 1=0) on 1<0) where not exists (select *) or 1 not in (1,2)");
103 }
104 public void test20(){
105 final String src = "select case when 1 like 'a%' escape '''' then 1 when not exists(select * from ta) or ta.cb not in (select 2 from a cross join (select 3)a) then 3 end";
106 testGoodSelect(src);
107 }
108 public void test21(){
109 final String src = "select case when 1 like 'a%' escape '''' then 1 when not exists(select * from ta) or ta.cb not in (select 2 from a cross join (select 3 a) then 3 end";
110 System.out.println(testBad(src));
111 }
112
113 public void test22(){
114 final String src = "select (1+1 ";
115 System.out.println(testBad(src));
116 }
117 public void test23(){
119 final String src = "select group 1";
120 System.out.println(testBad(src));
121 }
122 private String getCallerName(){
123 final Exception e = new Exception ();
124 StackTraceElement [] elems = e.getStackTrace();
125 if(elems==null || elems.length<2){
126 return "test";
127 }
128 else return elems[2].getMethodName();
129 }
130 public void testGood(final String src,
131 final String expect){
132
133 assertEquals(expect,
134 new SqlParser().parse_expr(src, getCallerName()).toString());
135 }
136 public void testGoodSelect(final String src, final String expect){
137 assertEquals(expect,
138 new SqlParser().parse_select(src, getCallerName()).toString());
139 }
140 public void testGoodSelect(final String src){
141 new SqlParser().parse_select(src, getCallerName());
142 }
143 private String testBad(final String src){
144 try{
145 new SqlParser().parse_select(src, getCallerName());
146 fail("should have failed");
147 return "";
148 }
149 catch(ParserException e){
150 return e.getMessage();
151 }
152 }
153 }
154 public static final class FileTest extends TestCase{
155 public void test1(){
156 testGoodQuery("test/sql/test1.sql");
157 }
158 public void test2(){
159 testGoodQuery("test/sql/test2.sql");
160 }
161 public void test3(){
162 testGoodQuery("test/sql/test3.sql");
163 }
164 public void test4(){
165 testGoodQuery("test/sql/test4.sql");
166 }
167 private void testGoodQuery(final String name){
168 final String qry = readFile(name);
169 final SqlParser parser = new SqlParser();
171 final String result = parser.parse_select(qry, name).toString();
172 final String result2 = parser.parse_select(result, name).toString();
173 assertEquals(result, result2);
174 }
175 private static String readFile(final String name){
176 return Utils.readFile(name);
177 }
178 }
179 public static Test suite0(){
180 return new TestSuite(SqlParserTestCase.class);
181 }
182 public static Test suite1(){
183 return new TestSuite(HardcodeTest.class);
184 }
185 public static Test suite2(){
186 return new TestSuite(FileTest.class);
187 }
188
189 public void test0(){
190
202 new HardcodeTest().test18();
203 }
204 public static void main(String [] args){
205 junit.textui.TestRunner.run(suite1());
207 junit.textui.TestRunner.run(suite2());
208 }
209 }
210 |
Popular Tags |
Java API By Example, From Geeks To Geeks.