001: /*
002: * SQLeonardo :: java database frontend
003: * Copyright (C) 2004 nickyb@users.sourceforge.net
004: *
005: * This program is free software; you can redistribute it and/or
006: * modify it under the terms of the GNU General Public License
007: * as published by the Free Software Foundation; either version 2
008: * of the License, or (at your option) any later version.
009: *
010: * This program is distributed in the hope that it will be useful,
011: * but WITHOUT ANY WARRANTY; without even the implied warranty of
012: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
013: * GNU General Public License for more details.
014: *
015: * You should have received a copy of the GNU General Public License
016: * along with this program; if not, write to the Free Software
017: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
018: *
019: */
020:
021: package nickyb.sqleonardo.querybuilder.syntax;
022:
023: import java.io.IOException;
024: import java.io.Reader;
025: import java.io.StreamTokenizer;
026: import java.io.StringReader;
027: import java.util.ArrayList;
028: import java.util.Hashtable;
029: import java.util.ListIterator;
030:
031: import nickyb.sqleonardo.querybuilder.QueryBuilder;
032: import nickyb.sqleonardo.querybuilder.QueryModel;
033:
034: public class SQLParser {
035: public static QueryModel toQueryModel(String sql)
036: throws IOException {
037: return toQueryModel(new StringReader(sql));
038: }
039:
040: private static QueryModel toQueryModel(Reader r) throws IOException {
041: QueryModel qm = new QueryModel();
042:
043: ArrayList al = doTokenize(r);
044: doAdjustSequence(al);
045:
046: ListIterator li = al.listIterator();
047: doParseQuery(li, qm.getQueryExpression());
048:
049: if (li.hasNext()
050: && li.next().toString().toUpperCase().equalsIgnoreCase(
051: _ReservedWords.ORDER_BY))
052: doParseOrderBy(li, qm);
053:
054: return qm;
055: }
056:
057: private static void doParseQuery(ListIterator li, QueryExpression qe)
058: throws IOException {
059: while (li.hasNext()) {
060: Object next = li.next();
061: if (next.toString().equalsIgnoreCase(_ReservedWords.SELECT)) {
062: doParseSelect(li, qe.getQuerySpecification());
063: } else if (next.toString().equalsIgnoreCase(
064: _ReservedWords.FROM)) {
065: doParseFrom(li, qe.getQuerySpecification());
066: doEnsureReferences(qe.getQuerySpecification());
067: } else if (next.toString().equalsIgnoreCase(
068: _ReservedWords.WHERE)) {
069: QueryTokens.Condition[] tokens = doParseConditions(li);
070: for (int i = 0; i < tokens.length; i++)
071: qe.getQuerySpecification()
072: .addWhereClause(tokens[i]);
073: } else if (next.toString().equalsIgnoreCase(
074: _ReservedWords.GROUP_BY)) {
075: doParseGroupBy(li, qe.getQuerySpecification());
076: } else if (next.toString().equalsIgnoreCase(
077: _ReservedWords.HAVING)) {
078: QueryTokens.Condition[] tokens = doParseConditions(li);
079: for (int i = 0; i < tokens.length; i++)
080: qe.getQuerySpecification().addHavingClause(
081: tokens[i]);
082: } else if (next.toString().equalsIgnoreCase(
083: _ReservedWords.UNION)) {
084: QueryExpression union = new QueryExpression();
085: doParseQuery(li, union);
086: qe.setUnion(union);
087: } else if (next.toString().equalsIgnoreCase(
088: _ReservedWords.ORDER_BY)) {
089: li.previous();
090: break;
091: } else if (next.toString().equals(")")) {
092: break;
093: }
094: }
095: }
096:
097: private static void doParseSelect(ListIterator li,
098: QuerySpecification qs) throws IOException {
099: int surrounds = 0;
100: String value = new String();
101:
102: while (li.hasNext()) {
103: Object next = li.next();
104:
105: if (next.toString().equalsIgnoreCase(
106: _ReservedWords.DISTINCT)) {
107: qs.setQuantifier(QuerySpecification.DISTINCT);
108: } else if (next.toString().equals(",") && surrounds == 0) {
109: qs.addSelectList(new QueryTokens.DefaultExpression(
110: value.trim()));
111: value = new String();
112: } else if (isClauseWord(next.toString())) {
113: li.previous();
114: if (next.toString().equalsIgnoreCase(
115: _ReservedWords.SELECT)) {
116: SubQuery sub = new SubQuery();
117: doParseQuery(li, sub);
118:
119: qs.addSelectList(sub);
120: value = new String();
121: } else {
122: if (!value.trim().equals(""))
123: qs
124: .addSelectList(new QueryTokens.DefaultExpression(
125: value.trim()));
126: break;
127: }
128: } else {
129: if (next.toString().equals("("))
130: surrounds++;
131: if (next.toString().equals(")"))
132: surrounds--;
133:
134: if (value.length() > 0 && next instanceof String) {
135: char last = value.charAt(value.length() - 1);
136: if (Character.isLetter(last)
137: || String.valueOf(last).equals(
138: QueryBuilder.identifierQuoteString))
139: value = value + SQLFormatter.SPACE;
140: }
141:
142: value = value + next.toString();
143: }
144: }
145: }
146:
147: private static void doParseFrom(ListIterator li,
148: QuerySpecification qs) {
149: int joinType = -1;
150: QueryTokens.Table t = null;
151: Hashtable tables = new Hashtable();
152:
153: for (int surrounds = 0; li.hasNext();) {
154: String next = li.next().toString();
155: if (isClauseWord(next) || next.equals(";")) {
156: // System.out.println("end.");
157:
158: if (t != null)
159: qs.addFromClause(t);
160:
161: li.previous();
162: break;
163: } else if (next.equals(",")) {
164: // System.out.println("cross");
165:
166: if (t != null)
167: qs.addFromClause(t);
168: t = null;
169: } else if (isJoinWord(next)) {
170: // System.out.println("join");
171:
172: if (t != null)
173: tables.put(stripQuote(t.getReference()), t);
174: t = null;
175:
176: joinType = QueryTokens.Join.getTypeInt(next);
177: } else if (next.toString().equalsIgnoreCase(
178: _ReservedWords.ON)
179: || next.toString().equalsIgnoreCase(
180: _ReservedWords.AND)
181: || next.toString().equalsIgnoreCase(
182: _ReservedWords.OR)) {
183: // System.out.println("condition");
184:
185: if (t != null)
186: tables.put(stripQuote(t.getReference()), t);
187: t = null;
188:
189: /* is AND/OR, then use previous/last type */
190: if (joinType == -1) {
191: QueryTokens._TableReference[] ref = qs
192: .getFromClause();
193: if (ref.length > 0
194: && ref[ref.length - 1] instanceof QueryTokens.Join)
195: joinType = ((QueryTokens.Join) ref[ref.length - 1])
196: .getType();
197: }
198:
199: String left = li.next().toString();
200: while (left.equals("(")) {
201: surrounds++;
202: left = li.next().toString();
203: }
204: String op = li.next().toString();
205: String right = li.next().toString();
206:
207: QueryTokens.Column tcl = null;
208: QueryTokens.Column tcr = null;
209:
210: for (int side = 0; side < 2; side++) {
211: String e = side == 0 ? left : right;
212: e = stripQuote(e);
213:
214: int dot = e.lastIndexOf(SQLFormatter.DOT);
215: String ref = dot == -1 ? new String() : e
216: .substring(0, dot);
217:
218: QueryTokens.Table tr = new QueryTokens.Table(null,
219: ref);
220: if (tables.containsKey(ref)) {
221: tr = (QueryTokens.Table) tables.get(ref);
222: } else
223: tables.put(stripQuote(tr.getReference()), tr);
224:
225: if (side == 0)
226: tcl = new QueryTokens.Column(tr, e
227: .substring(dot + 1));
228: else
229: tcr = new QueryTokens.Column(tr, e
230: .substring(dot + 1));
231: }
232: qs.addFromClause(new QueryTokens.Join(joinType, tcl,
233: op, tcr));
234: joinType = -1;
235: } else if (next.toString().equals("(")) {
236: surrounds++;
237: } else if (next.toString().equals(")")) {
238: if (--surrounds < 0) {
239: li.previous();
240: break;
241: }
242: } else if (!next.toString().equalsIgnoreCase("AS")) {
243: // System.out.println("table or alias");
244:
245: String schema = null;
246: String name = stripQuote(next.toString());
247:
248: int i = name.lastIndexOf(SQLFormatter.DOT);
249: if (i > 0) {
250: schema = name.substring(0, i);
251: name = name.substring(i + 1);
252: }
253:
254: if (t == null)
255: t = new QueryTokens.Table(schema, name);
256: else
257: t.setAlias(next.toString());
258: }
259: }
260: }
261:
262: private static void doParseGroupBy(ListIterator li,
263: QuerySpecification qs) {
264: while (li.hasNext()) {
265: Object next = li.next();
266: if (isReservedWord(next.toString())
267: || next.toString().equals(";")) {
268: li.previous();
269: break;
270: } else if (next instanceof String) {
271: qs.addGroupByClause(new QueryTokens.Group(next
272: .toString()));
273: }
274: }
275: }
276:
277: private static void doParseOrderBy(ListIterator li, QueryModel qm) {
278: QueryTokens.Sort token = null;
279: while (li.hasNext()) {
280: Object next = li.next();
281: if (next.toString().equals(",")
282: || next.toString().equals(";")) {
283: qm.addOrderByClause(token);
284: token = null;
285: } else {
286: if (token == null)
287: token = new QueryTokens.Sort(
288: new QueryTokens.DefaultExpression(next
289: .toString()));
290: else if (next.toString().equalsIgnoreCase("ASC"))
291: token.setType(QueryTokens.Sort.ASCENDING);
292: else if (next.toString().equalsIgnoreCase("DESC"))
293: token.setType(QueryTokens.Sort.DESCENDING);
294: }
295: }
296: }
297:
298: private static QueryTokens.Condition[] doParseConditions(
299: ListIterator li) throws IOException {
300: ArrayList tokens = new ArrayList();
301: QueryTokens.Condition token = null;
302: QueryTokens._Expression expr = null;
303:
304: for (int surrounds = 0; li.hasNext();) {
305: Object next = li.next();
306:
307: if (next.toString().equals("("))
308: surrounds++;
309: if (next.toString().equals(")"))
310: surrounds--;
311:
312: if (next.toString().equalsIgnoreCase("EXISTS")
313: || next.toString().equalsIgnoreCase("NOT EXISTS")) {
314: SubQuery sub = new SubQuery();
315: doParseQuery(li, sub);
316:
317: token.setLeft(null);
318: token.setOperator(next.toString());
319: token.setRight(sub);
320:
321: tokens.add(token);
322:
323: token = null;
324: expr = null;
325: } else if (isClauseWord(next.toString())) {
326: li.previous();
327: if (next.toString().equalsIgnoreCase(
328: _ReservedWords.SELECT)) {
329: expr = new SubQuery();
330: doParseQuery(li, (SubQuery) expr);
331: } else {
332: if (token != null) {
333: token.setRight(expr);
334: tokens.add(token);
335: }
336: break;
337: }
338: } else if (isOperator(next.toString())) {
339: if (token == null)
340: token = new QueryTokens.Condition();
341:
342: token.setLeft(expr);
343: token.setOperator(next.toString().toUpperCase());
344:
345: expr = null;
346: } else if (next.toString().equalsIgnoreCase(
347: _ReservedWords.AND)
348: || next.toString().equalsIgnoreCase(
349: _ReservedWords.OR)
350: || next.toString().equals(";") || surrounds < 0) {
351: if (token != null) {
352: token.setRight(expr);
353: tokens.add(token);
354: }
355:
356: token = new QueryTokens.Condition();
357: token.setAppend(next.toString());
358:
359: expr = null;
360: } else {
361: String value = expr == null ? new String() : expr
362: .toString();
363:
364: if (value.length() > 0 && next instanceof String) {
365: char last = value.charAt(value.length() - 1);
366: if (Character.isLetter(last))
367: value = value + SQLFormatter.SPACE;
368: }
369: value = value + next.toString();
370: expr = new QueryTokens.DefaultExpression(value);
371: }
372:
373: if (surrounds < 0) {
374: li.previous();
375: break;
376: }
377: }
378:
379: return (QueryTokens.Condition[]) tokens
380: .toArray(new QueryTokens.Condition[tokens.size()]);
381: }
382:
383: private static void doEnsureReferences(QuerySpecification qs)
384: throws IOException {
385: Hashtable tables = new Hashtable();
386:
387: QueryTokens._TableReference[] fromClause = qs.getFromClause();
388: for (int i = 0; i < fromClause.length; i++) {
389: QueryTokens._TableReference token = fromClause[i];
390: if (token instanceof QueryTokens.Join) {
391: tables.put(stripQuote(((QueryTokens.Join) token)
392: .getPrimary().getTable().getReference()),
393: ((QueryTokens.Join) token).getPrimary()
394: .getTable());
395: tables.put(stripQuote(((QueryTokens.Join) token)
396: .getForeign().getTable().getReference()),
397: ((QueryTokens.Join) token).getForeign()
398: .getTable());
399: } else {
400: tables.put(stripQuote(((QueryTokens.Table) token)
401: .getReference()), token);
402: }
403: }
404:
405: QueryTokens._Expression[] selectList = qs.getSelectList();
406: for (int i = 0; i < selectList.length; i++) {
407: String e = selectList[i].toString();
408: qs.removeSelectList(selectList[i]);
409:
410: StreamTokenizer stream = createTokenizer(new StringReader(e));
411: for (ArrayList al = new ArrayList(); true;) {
412: stream.nextToken();
413: if (stream.ttype == StreamTokenizer.TT_EOF) {
414: ListIterator li = al.listIterator();
415:
416: String ref = li.next().toString();
417: String alias = null;
418:
419: while (li.hasNext()) {
420: String next = li.next().toString();
421:
422: if (next.toString().equals(
423: String.valueOf(SQLFormatter.DOT))
424: || ref.endsWith(String
425: .valueOf(SQLFormatter.DOT)))
426: ref = ref + next;
427: else
428: alias = next;
429: }
430:
431: ref = stripQuote(ref);
432: int dot = ref.lastIndexOf(SQLFormatter.DOT);
433: if (dot != -1) {
434: String owner = ref.substring(0, dot);
435: String cname = ref.substring(dot + 1);
436:
437: if (tables.containsKey(owner)) {
438: selectList[i] = new QueryTokens.Column(
439: (QueryTokens.Table) tables
440: .get(owner), cname);
441: if (alias != null)
442: ((QueryTokens.Column) selectList[i])
443: .setAlias(alias);
444: }
445: }
446: break;
447: } else {
448: if (stream.sval == null
449: && (char) stream.ttype != SQLFormatter.DOT)
450: break;
451: al.add(stream.sval == null ? String
452: .valueOf(SQLFormatter.DOT) : stream.sval);
453: }
454: }
455:
456: qs.addSelectList(selectList[i]);
457: }
458: }
459:
460: private static boolean isOperator(String s) {
461: return isOperatorSimbol(s) || s.equalsIgnoreCase("IS")
462: || s.equalsIgnoreCase("IS NOT")
463: || s.equalsIgnoreCase("IN")
464: || s.equalsIgnoreCase("NOT IN")
465: || s.equalsIgnoreCase("LIKE")
466: || s.equalsIgnoreCase("NOT LIKE")
467: || s.equalsIgnoreCase("EXISTS")
468: || s.equalsIgnoreCase("NOT EXISTS");
469: }
470:
471: private static boolean isOperatorSimbol(String s) {
472: return s.equals("<") || s.equals(">") || s.equals("=")
473: || s.equals("<=") || s.equals(">=") || s.equals("<>");
474: }
475:
476: private static boolean isReservedWord(String s) {
477: return isClauseWord(s) || isJoinWord(s)
478: || s.equals(_ReservedWords.ON)
479: || s.equals(_ReservedWords.AND)
480: || s.equals(_ReservedWords.OR);
481: }
482:
483: private static boolean isJoinWord(String s) {
484: return s.equalsIgnoreCase(_ReservedWords.INNER_JOIN)
485: || s.equalsIgnoreCase(_ReservedWords.FULL_OUTER_JOIN)
486: || s.equalsIgnoreCase(_ReservedWords.LEFT_OUTER_JOIN)
487: || s.equalsIgnoreCase(_ReservedWords.RIGHT_OUTER_JOIN);
488: }
489:
490: private static boolean isClauseWord(String s) {
491: return s.equalsIgnoreCase(_ReservedWords.SELECT)
492: || s.equalsIgnoreCase(_ReservedWords.FROM)
493: || s.equalsIgnoreCase(_ReservedWords.WHERE)
494: || s.equalsIgnoreCase(_ReservedWords.GROUP_BY)
495: || s.equalsIgnoreCase(_ReservedWords.HAVING)
496: || s.equalsIgnoreCase(_ReservedWords.UNION)
497: || s.equalsIgnoreCase(_ReservedWords.ORDER_BY);
498: }
499:
500: private static void doAdjustSequence(ArrayList al) {
501: for (int i = 0; i < al.size(); i++) {
502: if (al.get(i).toString().equalsIgnoreCase(
503: _ReservedWords.SELECT)
504: || al.get(i).toString().equalsIgnoreCase(
505: _ReservedWords.FROM)
506: || al.get(i).toString().equalsIgnoreCase(
507: _ReservedWords.HAVING)) {
508: al.set(i, al.get(i).toString().toUpperCase());
509: } else if (al.get(i).toString().equalsIgnoreCase("BY")) {
510: al.set(i - 1, al.get(i - 1).toString().toUpperCase()
511: + SQLFormatter.SPACE + "BY");
512: al.remove(i--);
513: } else if (al.get(i).toString().equalsIgnoreCase("JOIN")) {
514: if (al.get(i - 1).toString().equalsIgnoreCase("INNER")) {
515: al.set(i - 1, al.get(i - 1).toString()
516: .toUpperCase()
517: + SQLFormatter.SPACE + "JOIN");
518: al.remove(i--);
519: } else if (al.get(i - 1).toString().equalsIgnoreCase(
520: "OUTER")) {
521: al.set(i - 2, al.get(i - 2).toString()
522: .toUpperCase()
523: + SQLFormatter.SPACE
524: + "OUTER"
525: + SQLFormatter.SPACE + "JOIN");
526: al.remove(i--);
527: al.remove(i--);
528: }
529: } else if (al.get(i).toString().equalsIgnoreCase("NOT")) {
530: if (al.get(i - 1).toString().equalsIgnoreCase("IS")) {
531: al.set(i - 1, "IS NOT");
532: al.remove(i--);
533: } else if (al.get(i + 1).toString().equalsIgnoreCase(
534: "IN")
535: || al.get(i + 1).toString().equalsIgnoreCase(
536: "LIKE")
537: || al.get(i + 1).toString().equalsIgnoreCase(
538: "EXISTS")) {
539: al.set(i, "NOT" + SQLFormatter.SPACE
540: + al.get(i + 1).toString().toUpperCase());
541: al.remove(i + 1);
542: }
543: } else if (al.get(i).toString().equals("=")) {
544: if (al.get(i - 1).toString().equals("<")
545: || al.get(i - 1).toString().equals(">")) {
546: al.set(i - 1, al.get(i - 1).toString() + "=");
547: al.remove(i--);
548: }
549: } else if (al.get(i).toString().equals(">")
550: && al.get(i - 1).toString().equals("<")) {
551: al.set(i - 1, "<>");
552: al.remove(i--);
553: } else if (al.get(i).toString().equals(".")) {
554: al.set(i, al.get(i - 1).toString() + SQLFormatter.DOT
555: + al.get(i + 1).toString());
556: al.remove(i - 1);
557: al.remove(i--);
558: }
559: }
560: }
561:
562: private static ArrayList doTokenize(Reader r) throws IOException {
563: ArrayList al = new ArrayList();
564: StreamTokenizer stream = createTokenizer(r);
565:
566: while (stream.ttype != StreamTokenizer.TT_EOF) {
567: stream.nextToken();
568: if (stream.ttype == StreamTokenizer.TT_WORD) {
569: al.add(stream.sval);
570: } else if (stream.ttype == StreamTokenizer.TT_NUMBER) {
571: Double dval = new Double(stream.nval);
572: al
573: .add(dval.doubleValue() == dval.intValue() ? new Integer(
574: (int) stream.nval)
575: : (Number) dval);
576: } else if (stream.ttype != StreamTokenizer.TT_EOF) {
577: if (stream.sval == null) {
578: al.add(new Character((char) stream.ttype));
579: } else {
580: al.add((char) stream.ttype + stream.sval
581: + (char) stream.ttype);
582: }
583: }
584: }
585:
586: if (al.size() > 0
587: && !al.get(al.size() - 1).toString().equals(";"))
588: al.add(new Character(';'));
589: return al;
590: }
591:
592: private static String stripQuote(String s) {
593: if (s.startsWith(QueryBuilder.identifierQuoteString))
594: s = s.substring(1);
595: if (s.endsWith(QueryBuilder.identifierQuoteString))
596: s = s.substring(0, s.length() - 1);
597:
598: for (int i = s.indexOf(QueryBuilder.identifierQuoteString); i != -1; i = s
599: .indexOf(QueryBuilder.identifierQuoteString)) {
600: String l = s.substring(0, i);
601: String r = s.substring(i + 1);
602:
603: s = l + r;
604: }
605: return s;
606: }
607:
608: private static StreamTokenizer createTokenizer(Reader r) {
609: StreamTokenizer stream = new StreamTokenizer(r);
610: stream.ordinaryChar('.');
611: stream.wordChars('_', '_');
612:
613: if (!QueryBuilder.identifierQuoteString.equals("\"")) {
614: stream.quoteChar(QueryBuilder.identifierQuoteString
615: .charAt(0));
616:
617: // for(int i=0; i<QueryBuilder.identifierQuoteString.length(); i++)
618: // {
619: // char wc = QueryBuilder.identifierQuoteString.charAt(i);
620: // stream.wordChars(wc,wc);
621: // }
622: }
623:
624: stream.slashSlashComments(true);
625: stream.slashStarComments(true);
626:
627: return stream;
628: }
629: /*
630: private static void print(ArrayList al)
631: {
632: print(al.toArray());
633: }
634:
635: private static void print(Object[] o)
636: {
637: for(int i=0; i<o.length; i++)
638: System.out.println(o[i].getClass().getName() + "[ " + o[i].toString() + " ]");
639:
640: System.out.println("----------------------------------------------------------------------");
641: }
642:
643: public static void main(String[] args)
644: {
645: QueryBuilder.useAlwaysQuote = false;
646:
647: try
648: {
649: // String fname = "c:\\temp\\test.sql";
650: // QueryModel qm = toQueryModel(new java.io.FileReader(fname));
651:
652: QueryBuilder.identifierQuoteString = new String("`");
653: // String sql = "SELECT `nome tabella`.`primo campo`, `nome tabella`.`secondo campo` FROM `nome tabella`";
654: // String sql = "SELECT \"nome tabella\".\"primo campo\", \"nome tabella\".\"secondo campo\" FROM \"nome tabella\"";
655: QueryModel qm = toQueryModel(sql);
656:
657: System.out.println(qm.toString(true));
658: }
659: catch(Exception e)
660: {
661: e.printStackTrace();
662: }
663: }
664: */
665: }
|