001: /**
002: Copyright (C) 2002-2003 Together
003:
004: This library is free software; you can redistribute it and/or
005: modify it under the terms of the GNU Lesser General Public
006: License as published by the Free Software Foundation; either
007: version 2.1 of the License, or (at your option) any later version.
008:
009: This library is distributed in the hope that it will be useful,
010: but WITHOUT ANY WARRANTY; without even the implied warranty of
011: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012: Lesser General Public License for more details.
013:
014: You should have received a copy of the GNU Lesser General Public
015: License along with this library; if not, write to the Free Software
016: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017:
018: */package org.relique.jdbc.csv;
019:
020: import java.io.*;
021: import java.util.*;
022: import java.sql.Statement;
023:
024: /**
025: * Class is used for parsing sql statements.
026: *
027: * @author Zoran Milakovic
028: */
029: public class CsvSqlParser {
030:
031: public static final String CREATE_TABLE = "create_table";
032: public static final String INSERT = "insert";
033: public static final String UPDATE = "update";
034: public static final String SELECT = "select";
035:
036: public static final String QUOTE_ESCAPE = "''";
037: public static final String DOUBLE_QUOTE_ESCAPE = "\"\"";
038: private static final String COMMA_ESCAPE = "~#####1~";
039:
040: public static final String BINARY_STREAM_OBJECT = "CsvDriverBinaryStreamObject";
041:
042: private ArrayList binaryStreamObjectList = new ArrayList();
043:
044: private HashMap oldValues = new HashMap();
045:
046: public String tableName;
047:
048: public String whereStatement;
049:
050: public String sqlType;
051:
052: public String[] columnNames;
053:
054: public String[] columnValues;
055:
056: public String[] columnWhereNames;
057:
058: public String[] columnWhereValues;
059:
060: /**
061: *Gets the tableName attribute of the SqlParser object
062: *
063: * @return The tableName value
064: * @since
065: */
066: public String getTableName() {
067: return tableName;
068: }
069:
070: /**
071: * Gets the columnNames attribute of the SqlParser object
072: *
073: * @return The columnNames value
074: * @since
075: */
076: public String[] getColumnNames() {
077: return columnNames;
078: }
079:
080: public String[] getWhereColumnNames() {
081: return columnWhereNames;
082: }
083:
084: public String[] getWhereColumnValues() {
085: return columnWhereValues;
086: }
087:
088: public String[] getColumnValues() {
089: return columnValues;
090: }
091:
092: /**
093: * Parse sql statement.
094: *
095: * @param statement Statement object which wrap sql statement
096: * @exception Exception Description of Exception
097: * @since
098: */
099: public void parse(Statement statement) throws Exception {
100: String sql = "";
101: if (statement instanceof CsvStatement)
102: sql = ((CsvStatement) statement).getSqlStatement();
103: else if (statement instanceof CsvPreparedStatement)
104: sql = ((CsvPreparedStatement) statement).getSqlStatement();
105: tableName = null;
106: columnNames = new String[0];
107: columnValues = new String[0];
108: columnWhereNames = new String[0];
109: columnWhereValues = new String[0];
110: whereStatement = null;
111: sqlType = null;
112:
113: //removing comments
114: if (sql.indexOf("/*") != -1) {
115: StringBuffer buf = new StringBuffer(sql);
116: buf.delete(sql.indexOf("/*"), sql.indexOf("*/") + 2);
117: sql = buf.toString();
118: }
119: sql = sql.trim();
120:
121: CsvDriver.log("sql = " + sql);
122:
123: oldValues.clear();
124: // int startIndex = 0;
125: //replace comma(,) in values between quotes(')
126: StringTokenizer tokQuote = new StringTokenizer(sql.toString(),
127: "'", true);
128: StringBuffer sb = new StringBuffer();
129: boolean openParent1 = false;
130: while (tokQuote.hasMoreTokens()) {
131: // startIndex++;
132: String next = tokQuote.nextToken();
133: if (openParent1) {
134: next = Utils.replaceAll(next, ",", COMMA_ESCAPE);
135: next = Utils.replaceKeywords(next, oldValues);
136: }
137: sb.append(next);
138: if (next.equalsIgnoreCase("'")) {
139: if (openParent1 == true)
140: openParent1 = false;
141: else
142: openParent1 = true;
143: }
144: }
145: //END replacement
146: sql = sb.toString();
147: String upperSql = sql.toUpperCase();
148:
149: //handle unsupported statements
150: if (upperSql.startsWith("ALTER "))
151: throw new Exception(
152: "ALTER TABLE statements are not supported.");
153: if (upperSql.startsWith("DROP "))
154: throw new Exception("DROP statements are not supported.");
155:
156: //SELECT
157: if (upperSql.startsWith("SELECT ")) {
158: if (upperSql.lastIndexOf(" FROM ") == -1) {
159: throw new Exception(
160: "Malformed SQL. Missing FROM statement.");
161: }
162:
163: sqlType = SELECT;
164: int fromPos = upperSql.lastIndexOf(" FROM ");
165: int wherePos = upperSql.lastIndexOf(" WHERE ");
166: if (wherePos == -1)
167: tableName = sql.substring(fromPos + 6).trim();
168: else
169: tableName = sql.substring(fromPos + 6, wherePos).trim();
170:
171: Vector cols = new Vector();
172: StringTokenizer tokenizer = new StringTokenizer(upperSql
173: .substring(7, fromPos), ",");
174:
175: while (tokenizer.hasMoreTokens()) {
176: cols.add(tokenizer.nextToken().trim());
177: }
178:
179: columnNames = new String[cols.size()];
180: cols.copyInto(columnNames);
181: if (wherePos != -1) {
182: String strWhere = sql.substring(wherePos + 7);
183: Vector whereCols = new Vector();
184: Vector whereValues = new Vector();
185: StringTokenizer tokenizerWhere = new StringTokenizer(
186: strWhere, ",");
187:
188: while (tokenizerWhere.hasMoreTokens()) {
189: String strToken = tokenizerWhere.nextToken();
190: if (strToken.toLowerCase().indexOf(" and ") != -1) {
191: String temp = strToken;
192: int andPos = 0;
193: out: do {
194: andPos = temp.toLowerCase()
195: .indexOf(" and ");
196: String strTokenAdd;
197: if (andPos != -1)
198: strTokenAdd = temp.substring(0, andPos)
199: .trim();
200: else
201: strTokenAdd = temp.trim();
202: int delimiter2 = strTokenAdd.indexOf("=");
203: if (delimiter2 != -1) {
204: String valueAdd = strTokenAdd
205: .substring(delimiter2 + 1)
206: .trim();
207: valueAdd = Utils
208: .handleQuotedString(valueAdd);
209: whereCols.add(strTokenAdd.substring(0,
210: delimiter2).trim());
211: valueAdd = Utils.replaceAll(valueAdd,
212: COMMA_ESCAPE, ",");
213: valueAdd = Utils.replaceAll(valueAdd,
214: QUOTE_ESCAPE, "'");
215: valueAdd = Utils.replaceKeywordsBack(
216: valueAdd, oldValues);
217: whereValues.add(valueAdd);
218: } else {
219: // int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
220: // whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
221: // whereValues.add(null);
222: int delimiterNULL = strTokenAdd
223: .toLowerCase().indexOf(
224: " is null");
225: int delimiterNOTNULL = strTokenAdd
226: .toLowerCase().indexOf(
227: " is not null");
228: if (delimiterNULL != -1) {
229: whereCols
230: .add(strTokenAdd.substring(
231: 0, delimiterNULL)
232: .trim());
233: whereValues.add(null);
234: }
235: if (delimiterNOTNULL != -1) {
236: whereCols.add(strTokenAdd
237: .substring(0,
238: delimiterNOTNULL)
239: .trim());
240: whereValues
241: .add(Utils.NOT_NULL_STRING);
242: }
243: }
244: temp = temp.substring(andPos + 5);
245: if (temp.toLowerCase().indexOf(" and ") == -1) {
246: strTokenAdd = temp.trim();
247: int delimiter4 = strTokenAdd
248: .indexOf("=");
249: if (delimiter4 != -1) {
250: String valueAdd = strTokenAdd
251: .substring(delimiter4 + 1)
252: .trim();
253: valueAdd = Utils
254: .handleQuotedString(valueAdd);
255: whereCols.add(strTokenAdd
256: .substring(0, delimiter4)
257: .trim());
258: valueAdd = Utils
259: .replaceAll(valueAdd,
260: COMMA_ESCAPE, ",");
261: valueAdd = Utils
262: .replaceAll(valueAdd,
263: QUOTE_ESCAPE, "'");
264: valueAdd = Utils
265: .replaceKeywordsBack(
266: valueAdd, oldValues);
267: whereValues.add(valueAdd);
268: } else {
269: // int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
270: // whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
271: // whereValues.add(null);
272: int delimiterNULL = strTokenAdd
273: .toLowerCase().indexOf(
274: " is null");
275: int delimiterNOTNULL = strTokenAdd
276: .toLowerCase().indexOf(
277: " is not null");
278: if (delimiterNULL != -1) {
279: whereCols.add(strTokenAdd
280: .substring(0,
281: delimiterNULL)
282: .trim());
283: whereValues.add(null);
284: }
285: if (delimiterNOTNULL != -1) {
286: whereCols
287: .add(strTokenAdd
288: .substring(0,
289: delimiterNOTNULL)
290: .trim());
291: whereValues
292: .add(Utils.NOT_NULL_STRING);
293: }
294: }
295: break out;
296: }
297:
298: } while (true);
299:
300: } else {
301: int delimiter = strToken.indexOf("=");
302: if (delimiter != -1) {
303: String value = strToken.substring(
304: delimiter + 1).trim();
305: value = Utils.handleQuotedString(value);
306: whereCols.add(strToken.substring(0,
307: delimiter).trim());
308: value = Utils.replaceAll(value,
309: COMMA_ESCAPE, ",");
310: value = Utils.replaceAll(value,
311: QUOTE_ESCAPE, "'");
312: value = Utils.replaceKeywordsBack(value,
313: oldValues);
314: whereValues.add(value);
315: } else {
316: int delimiterNULL = strToken.toLowerCase()
317: .indexOf(" is null");
318: int delimiterNOTNULL = strToken
319: .toLowerCase().indexOf(
320: " is not null");
321: if (delimiterNULL != -1) {
322: whereCols.add(strToken.substring(0,
323: delimiterNULL).trim());
324: whereValues.add(null);
325: }
326: if (delimiterNOTNULL != -1) {
327: whereCols.add(strToken.substring(0,
328: delimiterNOTNULL).trim());
329: whereValues.add(Utils.NOT_NULL_STRING);
330: }
331: }
332: }
333: }
334: columnWhereNames = new String[whereCols.size()];
335: columnWhereValues = new String[whereValues.size()];
336: whereCols.copyInto(columnWhereNames);
337: whereValues.copyInto(columnWhereValues);
338: }
339: }
340:
341: //INSERT
342: else if (upperSql.startsWith("INSERT ")) {
343: if (upperSql.lastIndexOf(" VALUES") == -1) {
344: throw new Exception(
345: "Malformed SQL. Missing VALUES statement.");
346: }
347: sqlType = INSERT;
348: int intoPos = 0;
349: if (upperSql.indexOf(" INTO ") != -1)
350: intoPos = upperSql.indexOf(" INTO ") + 6;
351: else
352: intoPos = upperSql.indexOf("INSERT ") + 7;
353: int bracketPos = upperSql.indexOf("(");
354: int lastBracketPos = upperSql.indexOf(")");
355: tableName = sql.substring(intoPos, bracketPos).trim();
356:
357: Vector cols = new Vector();
358: StringTokenizer tokenizer = new StringTokenizer(upperSql
359: .substring(bracketPos + 1, lastBracketPos), ",");
360: while (tokenizer.hasMoreTokens()) {
361: cols.add(tokenizer.nextToken().trim());
362: }
363: columnNames = new String[cols.size()];
364: cols.copyInto(columnNames);
365:
366: int valuesPos = upperSql.indexOf("VALUES");
367: String endStatement = sql.substring(valuesPos + 6).trim();
368: bracketPos = endStatement.indexOf("(");
369: lastBracketPos = endStatement.lastIndexOf(")");
370: Vector values = new Vector();
371: StringTokenizer tokenizer2 = new StringTokenizer(
372: endStatement.substring(bracketPos + 1,
373: lastBracketPos), ",");
374: while (tokenizer2.hasMoreTokens()) {
375: String value = tokenizer2.nextToken().trim();
376: value = Utils.handleQuotedString(value);
377: value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
378: value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
379: value = Utils.replaceAll(value, "\"",
380: DOUBLE_QUOTE_ESCAPE);
381: value = Utils.replaceKeywordsBack(value, oldValues);
382: value = Utils.handleBinaryString(value,
383: this .binaryStreamObjectList);
384: values.add(value);
385: }
386: columnValues = new String[values.size()];
387: values.copyInto(columnValues);
388: //replace all line brakes
389: columnValues = Utils.replaceLineBrakesAndCarrReturn(
390: columnValues, ((CsvConnection) statement
391: .getConnection()).getLineBreakEscape(),
392: ((CsvConnection) statement.getConnection())
393: .getCarriageReturnEscape());
394: }
395:
396: //UPDATE
397: else if (upperSql.startsWith("UPDATE ")) {
398: if (upperSql.lastIndexOf(" SET ") == -1)
399: throw new Exception(
400: "Malformed SQL. Missing SET statement.");
401: sqlType = UPDATE;
402: int updatePos = upperSql.indexOf("UPDATE");
403: int setPos = upperSql.indexOf(" SET ");
404: int equalPos = upperSql.indexOf("=");
405: int wherePos = upperSql.indexOf(" WHERE ");
406: tableName = sql.substring(updatePos + 6, setPos).trim();
407:
408: String setString = "";
409: if (wherePos != -1)
410: setString = sql.substring(setPos + 5, wherePos);
411: else
412: setString = sql.substring(setPos + 5, sql.length());
413: StringTokenizer tokenizerSet = new StringTokenizer(
414: setString, ",");
415: Vector setNames = new Vector();
416: Vector setValues = new Vector();
417:
418: while (tokenizerSet.hasMoreTokens()) {
419: String strToken = tokenizerSet.nextToken();
420: int delimiter = strToken.indexOf("=");
421: setNames.add(strToken.substring(0, delimiter).trim());
422: String value = strToken.substring(delimiter + 1).trim();
423: value = Utils.handleQuotedString(value);
424: value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
425: value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
426: value = Utils.replaceAll(value, "\"",
427: DOUBLE_QUOTE_ESCAPE);
428: value = Utils.replaceKeywordsBack(value, oldValues);
429: value = Utils.handleBinaryString(value,
430: this .binaryStreamObjectList);
431: setValues.add(value);
432: }
433:
434: columnNames = new String[setNames.size()];
435: columnValues = new String[setValues.size()];
436: setNames.copyInto(columnNames);
437: setValues.copyInto(columnValues);
438: //replace all line brakes
439: columnValues = Utils.replaceLineBrakesAndCarrReturn(
440: columnValues, ((CsvConnection) statement
441: .getConnection()).getLineBreakEscape(),
442: ((CsvConnection) statement.getConnection())
443: .getCarriageReturnEscape());
444: if (wherePos != -1) {
445: String strWhere = sql.substring(wherePos + 6).trim();
446: Vector whereCols = new Vector();
447: Vector whereValues = new Vector();
448: StringTokenizer tokenizerWhere = new StringTokenizer(
449: strWhere, ",");
450:
451: while (tokenizerWhere.hasMoreTokens()) {
452: String strToken = tokenizerWhere.nextToken();
453: if (strToken.toLowerCase().indexOf(" and ") != -1) {
454: String temp = strToken;
455: int andPos = 0;
456: out: do {
457: andPos = temp.toLowerCase()
458: .indexOf(" and ");
459: String strTokenAdd;
460: if (andPos != -1)
461: strTokenAdd = temp.substring(0, andPos)
462: .trim();
463: else
464: strTokenAdd = temp.trim();
465: int delimiter2 = strTokenAdd.indexOf("=");
466: if (delimiter2 != -1) {
467: String valueAdd = strTokenAdd
468: .substring(delimiter2 + 1)
469: .trim();
470: valueAdd = Utils
471: .handleQuotedString(valueAdd);
472: whereCols.add(strTokenAdd.substring(0,
473: delimiter2).trim());
474: valueAdd = Utils.replaceAll(valueAdd,
475: COMMA_ESCAPE, ",");
476: valueAdd = Utils.replaceAll(valueAdd,
477: QUOTE_ESCAPE, "'");
478: valueAdd = Utils.replaceKeywordsBack(
479: valueAdd, oldValues);
480: whereValues.add(valueAdd);
481: } else {
482: // int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
483: // whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
484: // whereValues.add(null);
485: int delimiterNULL = strTokenAdd
486: .toLowerCase().indexOf(
487: " is null");
488: int delimiterNOTNULL = strTokenAdd
489: .toLowerCase().indexOf(
490: " is not null");
491: if (delimiterNULL != -1) {
492: whereCols
493: .add(strTokenAdd.substring(
494: 0, delimiterNULL)
495: .trim());
496: whereValues.add(null);
497: }
498: if (delimiterNOTNULL != -1) {
499: whereCols.add(strTokenAdd
500: .substring(0,
501: delimiterNOTNULL)
502: .trim());
503: whereValues
504: .add(Utils.NOT_NULL_STRING);
505: }
506: }
507: temp = temp.substring(andPos + 5);
508: if (temp.toLowerCase().indexOf(" and ") == -1) {
509: strTokenAdd = temp.trim();
510: int delimiter4 = strTokenAdd
511: .indexOf("=");
512: if (delimiter4 != -1) {
513: String valueAdd = strTokenAdd
514: .substring(delimiter4 + 1)
515: .trim();
516: valueAdd = Utils
517: .handleQuotedString(valueAdd);
518: whereCols.add(strTokenAdd
519: .substring(0, delimiter4)
520: .trim());
521: valueAdd = Utils
522: .replaceAll(valueAdd,
523: COMMA_ESCAPE, ",");
524: valueAdd = Utils
525: .replaceAll(valueAdd,
526: QUOTE_ESCAPE, "'");
527: valueAdd = Utils
528: .replaceKeywordsBack(
529: valueAdd, oldValues);
530: whereValues.add(valueAdd);
531: } else {
532: // int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
533: // whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
534: // whereValues.add(null);
535: int delimiterNULL = strTokenAdd
536: .toLowerCase().indexOf(
537: " is null");
538: int delimiterNOTNULL = strTokenAdd
539: .toLowerCase().indexOf(
540: " is not null");
541: if (delimiterNULL != -1) {
542: whereCols.add(strTokenAdd
543: .substring(0,
544: delimiterNULL)
545: .trim());
546: whereValues.add(null);
547: }
548: if (delimiterNOTNULL != -1) {
549: whereCols
550: .add(strTokenAdd
551: .substring(0,
552: delimiterNOTNULL)
553: .trim());
554: whereValues
555: .add(Utils.NOT_NULL_STRING);
556: }
557: }
558: break out;
559: }
560:
561: } while (true);
562:
563: } else {
564: int delimiter = strToken.indexOf("=");
565: if (delimiter != -1) {
566: String value = strToken.substring(
567: delimiter + 1).trim();
568: value = Utils.handleQuotedString(value);
569: whereCols.add(strToken.substring(0,
570: delimiter).trim());
571: value = Utils.replaceAll(value,
572: COMMA_ESCAPE, ",");
573: value = Utils.replaceAll(value,
574: QUOTE_ESCAPE, "'");
575: value = Utils.replaceKeywordsBack(value,
576: oldValues);
577: whereValues.add(value);
578: } else {
579: // int delimiter1 = strToken.toLowerCase().indexOf(" is ");
580: // whereCols.add(strToken.substring(0, delimiter1).trim());
581: // whereValues.add(null);
582: int delimiterNULL = strToken.toLowerCase()
583: .indexOf(" is null");
584: int delimiterNOTNULL = strToken
585: .toLowerCase().indexOf(
586: " is not null");
587: if (delimiterNULL != -1) {
588: whereCols.add(strToken.substring(0,
589: delimiterNULL).trim());
590: whereValues.add(null);
591: }
592: if (delimiterNOTNULL != -1) {
593: whereCols.add(strToken.substring(0,
594: delimiterNOTNULL).trim());
595: whereValues.add(Utils.NOT_NULL_STRING);
596: }
597: }
598: }
599: }
600: columnWhereNames = new String[whereCols.size()];
601: columnWhereValues = new String[whereValues.size()];
602: whereCols.copyInto(columnWhereNames);
603: whereValues.copyInto(columnWhereValues);
604: }
605: }
606:
607: //CREATE TABLE
608: else if (upperSql.startsWith("CREATE TABLE ")) {
609: sqlType = CREATE_TABLE;
610: int createPos = upperSql.indexOf("CREATE TABLE");
611: int tableStartPos = upperSql.indexOf("(");
612: int tableEndPos = upperSql.lastIndexOf(")");
613: tableName = sql.substring(createPos + 12, tableStartPos)
614: .trim();
615: String createString = sql.substring(tableStartPos + 1,
616: tableEndPos).trim();
617: StringTokenizer tokenizerCreate = new StringTokenizer(
618: createString.toUpperCase(), "\n");
619: Vector setColumnNames = new Vector();
620: while (tokenizerCreate.hasMoreTokens()) {
621: String strToken = tokenizerCreate.nextToken().trim();
622: int delimiter = strToken.indexOf(" ");
623: //find out type of column
624: String typeOfColumn = "";
625: StringTokenizer stSpace = new StringTokenizer(strToken,
626: " ");
627: while (stSpace.hasMoreTokens()) {
628: String nextStSpace = stSpace.nextToken();
629: if (nextStSpace
630: .equalsIgnoreCase(CsvDriver.BINARY_TYPE))
631: typeOfColumn = "-BINARY";
632: }
633: if (strToken.toUpperCase().indexOf("CONSTRAINT") == -1) {
634: String columnName = strToken
635: .substring(0, delimiter).trim();
636: setColumnNames.add(columnName + typeOfColumn);
637: }
638: }
639: columnNames = new String[setColumnNames.size()];
640: setColumnNames.copyInto(columnNames);
641:
642: } else {
643: throw new Exception("Malformed SQL. Wrong SQL statement.");
644: }
645:
646: }
647:
648: /**
649: * Set setBinaryStreamList.
650: * @param list
651: */
652: public void setBinaryStreamList(ArrayList list) {
653: this.binaryStreamObjectList = list;
654: }
655:
656: }
|