1 package uba.db.sql.parser;
2
3 import java.util.List;
4
5 import junit.framework.TestCase;
6 import uba.db.sql.language.CharColumnDeclaration;
7 import uba.db.sql.language.ColumnConstraintDeclaration;
8 import uba.db.sql.language.ColumnName;
9 import uba.db.sql.language.CreateIndex;
10 import uba.db.sql.language.CreateTable;
11 import uba.db.sql.language.DisplayAllColumns;
12 import uba.db.sql.language.DisplayColumns;
13 import uba.db.sql.language.DisplayOneColumn;
14 import uba.db.sql.language.EqualComparison;
15 import uba.db.sql.language.GreatherThanComparison;
16 import uba.db.sql.language.GreatherThanEqualsComparison;
17 import uba.db.sql.language.InSelectionCriteria;
18 import uba.db.sql.language.IndexName;
19 import uba.db.sql.language.Insert;
20 import uba.db.sql.language.IntegerColumnDeclaration;
21 import uba.db.sql.language.IntegerValue;
22 import uba.db.sql.language.Join;
23 import uba.db.sql.language.LessThanComparison;
24 import uba.db.sql.language.LessThanEqualsComparison;
25 import uba.db.sql.language.NotInSelectionCriteria;
26 import uba.db.sql.language.QualifiedColumnName;
27 import uba.db.sql.language.Select;
28 import uba.db.sql.language.SelectionCriteria;
29 import uba.db.sql.language.SingleSelectionCriteria;
30 import uba.db.sql.language.StringValue;
31 import uba.db.sql.language.TableName;
32 import uba.db.sql.language.TableSelectionSource;
33 import uba.db.sql.language.ValueEnumeration;
34 import uba.db.testhelpers.TestUtils;
35
36 /***
37 * Tests de unidad para {@link uba.db.sql.parser.SQLParser}.
38 *
39 * @version $Revision: 1.12 $
40 */
41 public class SQLParserTest extends TestCase {
42 private static final ColumnName DESC_COLUMN = new ColumnName("desc");
43 private static final ColumnName ID_COLUMN = new ColumnName("id");
44 private static final TableName TABLE_A = new TableName("A");
45 private static final ColumnName COLUMN_D = new ColumnName("d");
46 private static final Join FROM_A_B = new TableSelectionSource("A")
47 .join(new TableSelectionSource("B"));
48 private static final TableSelectionSource FROM_A = new TableSelectionSource(TABLE_A);
49 private static final TableSelectionSource FROM_B = new TableSelectionSource(
50 new TableName("B"));
51 private static final QualifiedColumnName TABLE_A_COL_C = TABLE_A.column("c");
52 private static final QualifiedColumnName TABLE_B_COL_C = new TableName("B")
53 .column("c");
54 private static final QualifiedColumnName TABLE_A_COL_D = TABLE_A.column("d");
55 private static final QualifiedColumnName TABLE_B_COL_D = new TableName("B")
56 .column("d");
57
58 private static final SingleSelectionCriteria AC_EQUALS_BC = new SingleSelectionCriteria(
59 new EqualComparison(TABLE_A_COL_C, TABLE_B_COL_C));
60
61 private SQLParser parser;
62 private static final SelectionCriteria BD_EQUALS_AD = new SingleSelectionCriteria(
63 new EqualComparison(TABLE_B_COL_D, TABLE_A_COL_D));;
64
65 /***
66 * @see junit.framework.TestCase#setUp()
67 */
68 protected void setUp() throws Exception {
69 super.setUp();
70 parser = new SQLParser();
71 }
72
73 /***
74 * Test para parsear la sentencia: SELECT * FROM A,B.
75 */
76 public void testParseSelectWithJoin() throws Exception {
77 Select result = (Select) parser.parse("select * from A,B");
78
79 Select expected = new Select(FROM_A_B);
80 assertEquals(expected, result);
81 }
82
83 /***
84 * Test para parsear la sentencia: SELECT * FROM A,B WHERE A.c=B.c.
85 */
86 public void testParseSelectWithQualifiedSimpleWhere() throws Exception {
87 Select result = (Select) parser.parse("SELECT * FROM A,B WHERE A.c=B.c");
88 Select expected = new Select(FROM_A_B, AC_EQUALS_BC);
89
90 assertEquals(expected, result);
91 }
92
93 /***
94 * Test para parsear la sentencia: SELECT * FROM A,B WHERE d=e.
95 */
96 public void testParseSelectWithSimpleWhere() throws Exception {
97 Select result = (Select) parser.parse("select * FROM A,B WHERE d=e");
98 Select expected = new Select(FROM_A_B, new SingleSelectionCriteria(
99 new EqualComparison(COLUMN_D, new ColumnName("e"))));
100
101 assertEquals(expected, result);
102 }
103
104 /***
105 * Test para parsear la sentencia: SELECT A.c,d FROM A,B WHERE A.c=B.c.
106 */
107 public void testParseSelect() throws Exception {
108 DisplayColumns displayColumns = new DisplayOneColumn(TABLE_A_COL_C)
109 .append(new DisplayOneColumn(COLUMN_D));
110
111 Select expected = new Select(displayColumns, FROM_A_B, AC_EQUALS_BC);
112 Select result = (Select) parser.parse("SELECT A.c,d FROM A,B WHERE A.c=B.c");
113
114 assertEquals(expected, result);
115 }
116
117 /***
118 * Test para parsear la sentencia: SELECT *, A.c,d FROM A,B WHERE A.c=B.c.
119 */
120 public void testParseSelectWithColumnsWildcard() throws Exception {
121 DisplayColumns displayColumns = new DisplayAllColumns()
122 .append(new DisplayOneColumn(TABLE_A_COL_C).append(new DisplayOneColumn(
123 COLUMN_D)));
124
125 Select expected = new Select(displayColumns, FROM_A_B, AC_EQUALS_BC);
126 Select result = (Select) parser.parse("SELECT *, A.c,d FROM A,B WHERE A.c=B.c");
127
128 assertEquals(expected, result);
129 }
130
131 /***
132 * Test para verificar que se genera un error al parsear una sentencia de
133 * SQL no válida.
134 */
135 public void testParseInvalidSQLSentence() {
136 String invalidSQL = "SELECT WHERE a=b";
137 try {
138 parser.parse(invalidSQL);
139 fail("Se deberia haber generado una excepcion");
140 } catch (SQLParserException e) {
141 assertEquals(invalidSQL, e.input());
142 }
143 }
144
145 /***
146 * Test para parsear la sentencia: SELECT * FROM A,B WHERE A.c=B.c AND
147 * B.d=A.d
148 */
149 public void testParseSelectWithAND() throws Exception {
150 Select expected = new Select(FROM_A_B, AC_EQUALS_BC.and(BD_EQUALS_AD));
151 Select result = (Select) parser
152 .parse("SELECT * FROM A,B WHERE A.c=B.c AND B.d=A.d");
153
154 assertEquals(expected, result);
155 }
156
157 /***
158 * Test para parsear la sentencia: SELECT * FROM A,B WHERE A.c=B.c OR
159 * B.d=A.d
160 */
161 public void testParseSelectWithOR() throws Exception {
162 Select expected = new Select(FROM_A_B, AC_EQUALS_BC.or(BD_EQUALS_AD));
163 Select result = (Select) parser
164 .parse("SELECT * FROM A,B WHERE A.c=B.c OR B.d=A.d");
165
166 assertEquals(expected, result);
167 }
168
169 /***
170 * Test para parsear la sentencia: SELECT * FROM A,B WHERE A.c=B.c AND
171 * B.d=A.d OR A.c=B.c
172 */
173 public void testParseSelectWithAndOr() throws Exception {
174 Select expected = new Select(FROM_A_B, AC_EQUALS_BC.and(BD_EQUALS_AD
175 .or(AC_EQUALS_BC)));
176 Select result = (Select) parser
177 .parse("SELECT * FROM A,B WHERE A.c=B.c AND B.d=A.d OR A.c=B.c");
178
179 assertEquals(expected, result);
180 }
181
182 /***
183 * Test para parsear la sentencia: SELECT * FROM A,B WHERE A.c=B.c OR
184 * B.d=A.d AND A.c=B.c
185 */
186 public void testParseSelectWithOrAnd() throws Exception {
187
188 Select expected = new Select(FROM_A_B, (AC_EQUALS_BC.or(BD_EQUALS_AD))
189 .and(AC_EQUALS_BC));
190 Select result = (Select) parser
191 .parse("SELECT * FROM A,B WHERE A.c=B.c OR B.d=A.d AND A.c=B.c");
192
193 assertEquals(expected, result);
194 }
195
196 /***
197 * Test para parsear la sentencia: SELECT * FROM A,B WHERE NOT (A.c=B.c)
198 */
199 public void testParseSelectWithNot() throws Exception {
200 Select expected = new Select(FROM_A_B, AC_EQUALS_BC.not());
201 Select result = (Select) parser.parse("SELECT * FROM A,B WHERE NOT (A.c=B.c)");
202
203 assertEquals(expected, result);
204 }
205
206 /***
207 * Test para parsear la sentencia: SELECT * FROM A,B WHERE NOT A.c=B.c
208 */
209 public void testParseSelectNotWithoutParens() throws Exception {
210 Select expected = new Select(FROM_A_B, AC_EQUALS_BC.not());
211 Select result = (Select) parser.parse("SELECT * FROM A,B WHERE NOT (A.c=B.c)");
212
213 assertEquals(expected, result);
214 }
215
216 /***
217 * Test para parsear la sentencia: SELECT * FROM A,B WHERE (A.c=B.c AND
218 * A.c=B.c) OR A.c=B.c
219 */
220 public void testParseSelectParensPrecedence() throws Exception {
221
222
223 Select expected = new Select(FROM_A_B, (AC_EQUALS_BC.and(AC_EQUALS_BC))
224 .or(AC_EQUALS_BC));
225 Select result = (Select) parser
226 .parse("SELECT * FROM A,B WHERE (A.c=B.c AND A.c=B.c) OR A.c=B.c");
227
228 assertEquals(expected, result);
229 }
230
231 /***
232 * Test para parsear la sentencia: SELECT * FROM A WHERE A.c IN (SELECT *
233 * FROM B)
234 */
235 public void testParseSelectWithIn() throws Exception {
236 SelectionCriteria selectionCriteria = new InSelectionCriteria(TABLE_A_COL_C,
237 new Select(FROM_B));
238
239 Select expected = new Select(FROM_A, selectionCriteria);
240 Select result = (Select) parser
241 .parse("SELECT * FROM A WHERE A.c IN (SELECT * FROM B)");
242
243 assertEquals(expected, result);
244 }
245
246 /***
247 * Test para parsear la sentencia: SELECT * FROM A WHERE A.c NOT IN (SELECT *
248 * FROM B)
249 */
250 public void testParseSelectWithNotIn() throws Exception {
251 SelectionCriteria selectionCriteria = new NotInSelectionCriteria(TABLE_A_COL_C,
252 new Select(FROM_B));
253
254 Select expected = new Select(FROM_A, selectionCriteria);
255 Select result = (Select) parser
256 .parse("SELECT * FROM A WHERE A.c NOT IN (SELECT * FROM B)");
257
258 assertEquals(expected, result);
259 }
260
261 /***
262 * Test para parsear la sentencia: SELECT * FROM A WHERE A.c NOT IN (1, 2)
263 */
264 public void testParseSelectWithLiteralListIn() throws Exception {
265 SelectionCriteria selectionCriteria = new InSelectionCriteria(TABLE_A_COL_C,
266 new ValueEnumeration(TestUtils.list(new IntegerValue(1),
267 new IntegerValue(2))));
268
269 Select expected = new Select(FROM_A, selectionCriteria);
270 Select result = (Select) parser.parse("SELECT * FROM A WHERE A.c IN (1, 2)");
271
272 assertEquals(expected, result);
273 }
274
275 /***
276 * Test para parsear la sentencia: SELECT * FROM A WHERE 'a' IN (SELECT *
277 * FROM B)
278 */
279 public void testParseSelectWithLiteralValueIn() throws Exception {
280 SelectionCriteria selectionCriteria = new InSelectionCriteria(
281 new StringValue("a"), new Select(FROM_B));
282
283 Select expected = new Select(FROM_A, selectionCriteria);
284 Select result = (Select) parser
285 .parse("SELECT * FROM A WHERE 'a' IN (SELECT * FROM B)");
286
287 assertEquals(expected, result);
288
289 }
290
291 /***
292 * Test para parsear la sentencia: SELECT * FROM A,B WHERE A.c < B.c
293 */
294 public void testParseSelectWhereWithComparisonLessThan() throws Exception {
295 SelectionCriteria selectionCriteria = new SingleSelectionCriteria(
296 new LessThanComparison(TABLE_A_COL_C, TABLE_B_COL_C));
297
298 Select expected = new Select(FROM_A_B, selectionCriteria);
299 Select result = (Select) parser.parse("SELECT * FROM A,B WHERE A.c < B.c");
300
301 assertEquals(expected, result);
302 }
303
304 /***
305 * Test para parsear la sentencia: SELECT * FROM A,B WHERE A.c > B.c
306 */
307 public void testParseSelectWhereWithComparisonGreatherThan() throws Exception {
308 SelectionCriteria selectionCriteria = new SingleSelectionCriteria(
309 new GreatherThanComparison(TABLE_A_COL_C, TABLE_B_COL_C));
310
311 Select expected = new Select(FROM_A_B, selectionCriteria);
312 Select result = (Select) parser.parse("SELECT * FROM A,B WHERE A.c > B.c");
313
314 assertEquals(expected, result);
315 }
316
317 /***
318 * Test para parsear la sentencia: SELECT * FROM A,B WHERE A.c <= B.c
319 */
320 public void testParseSelectWhereWithComparisonLessThanEquals() throws Exception {
321 SelectionCriteria selectionCriteria = new SingleSelectionCriteria(
322 new LessThanEqualsComparison(TABLE_A_COL_C, TABLE_B_COL_C));
323
324 Select expected = new Select(FROM_A_B, selectionCriteria);
325 Select result = (Select) parser.parse("SELECT * FROM A,B WHERE A.c <= B.c");
326
327 assertEquals(expected, result);
328 }
329
330 /***
331 * Test para parsear la sentencia: SELECT * FROM A,B WHERE A.c > B.c
332 */
333 public void testParseSelectWhereWithComparisonGreatherThanEquals() throws Exception {
334 SelectionCriteria selectionCriteria = new SingleSelectionCriteria(
335 new GreatherThanEqualsComparison(TABLE_A_COL_C, TABLE_B_COL_C));
336
337 Select expected = new Select(FROM_A_B, selectionCriteria);
338 Select result = (Select) parser.parse("SELECT * FROM A,B WHERE A.c >= B.c");
339
340 assertEquals(expected, result);
341 }
342
343 /***
344 * Test para parsear la sentencia: INSERT INTO A VALUES (1, 'hola')
345 */
346 public void testParseInsert() throws Exception {
347 List values = TestUtils.list(new IntegerValue(1), new StringValue("hola"));
348 Insert expected = new Insert(TABLE_A, values);
349 Insert result = (Insert) parser.parse("INSERT INTO A VALUES (1, 'hola')");
350
351 assertEquals(expected, result);
352 }
353
354 /***
355 * Test para parsear la sentencia: INSERT INTO A (SELECT * FROM B)
356 */
357 public void testParseInsertWithSelect() throws Exception {
358 Insert expected = new Insert(TABLE_A, new Select(FROM_B));
359 Insert result = (Insert) parser.parse("INSERT INTO A (SELECT * FROM B)");
360
361 assertEquals(expected, result);
362 }
363
364 /***
365 * Test para parsear la sentencia: CREATE TABLE A ( id INTEGER, desc
366 * CHAR(10))
367 */
368 public void testCreateTable() throws Exception {
369 CreateTable expected = new CreateTable(TABLE_A, TestUtils
370 .list(new IntegerColumnDeclaration(ID_COLUMN), new CharColumnDeclaration(
371 DESC_COLUMN, 10)));
372 CreateTable result = (CreateTable) parser
373 .parse("CREATE TABLE A (id INTEGER, desc CHAR(10))");
374
375 assertEquals(expected, result);
376 }
377
378 /***
379 * Test para parsear la sentencia: CREATE TABLE A ( id INTEGER PRIMARY KEY,
380 * desc CHAR(10) NOT NULL)
381 */
382 public void testCreateTableWithConstraints() throws Exception {
383 CreateTable expected = new CreateTable(TABLE_A, TestUtils
384 .list(new IntegerColumnDeclaration(ID_COLUMN,
385 ColumnConstraintDeclaration.PRIMARY_KEY),
386 new CharColumnDeclaration(DESC_COLUMN, 10,
387 ColumnConstraintDeclaration.NOT_NULL)));
388 CreateTable result = (CreateTable) parser
389 .parse("CREATE TABLE A (id INTEGER PRIMARY KEY, desc CHAR(10) NOT NULL)");
390
391 assertEquals(expected, result);
392 }
393
394 /***
395 * Test para parsear la sentencia: CREATE TABLE A ( id INTEGER, desc
396 * CHAR(10), primary key(id, desc))
397 */
398 public void testCreateTablePrimaryKeys() throws Exception {
399 List columns = TestUtils.list(new IntegerColumnDeclaration(ID_COLUMN),
400 new CharColumnDeclaration(DESC_COLUMN, 10));
401 List primaryKeys = TestUtils.list(ID_COLUMN, DESC_COLUMN);
402
403 CreateTable expected = new CreateTable(TABLE_A, columns, primaryKeys);
404 CreateTable result = (CreateTable) parser
405 .parse("CREATE TABLE A (id INTEGER, desc CHAR(10), primary key(id, desc))");
406
407 assertEquals(expected, result);
408 }
409
410 /***
411 * Test: parsear la sentencia CREATE INDEX nombreIdx ON A (c)
412 */
413 public void testCreateIndex() throws Exception {
414 CreateIndex expected = new CreateIndex(new IndexName("nombreIdx"), TABLE_A_COL_C);
415 CreateIndex result = (CreateIndex) parser
416 .parse("CREATE INDEX nombreIdx ON A (c)");
417 }
418
419 /***
420 * Test: hace un SELECT donde el nombre de la tabla esta entre comillas.
421 */
422 public void testTableNameInDoubleQuotes() throws Exception {
423 Select result = (Select) parser.parse("select * from \"A\"");
424
425 Select expected = new Select(FROM_A);
426 assertEquals(expected, result);
427 }
428 }