csvsqlparser.java

数据仓库工具

/**
    Copyright (C) 2002-2003  Together

    This library is free software; you can redistribute it and/or
    modify it under the terms of the GNU Lesser General Public
    License as published by the Free Software Foundation; either
    version 2.1 of the License, or (at your option) any later version.

    This library is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser General Public
    License along with this library; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

*/

package org.relique.jdbc.csv;

import java.io.*;
import java.util.*;
import java.sql.Statement;

/**
 * Class is used for parsing sql statements.
 *
 * @author     Zoran Milakovic
 */
public class CsvSqlParser {

  public static final String CREATE_TABLE = "create_table";
  public static final String INSERT = "insert";
  public static final String UPDATE = "update";
  public static final String SELECT = "select";

  public static final String QUOTE_ESCAPE = "''";
  private static final String DOUBLE_QUOTE_ESCAPE = "\"\"";
  private static final String COMMA_ESCAPE = "~#####1~";

  public static final String BINARY_STREAM_OBJECT = "CsvDriverBinaryStreamObject";

  private ArrayList binaryStreamObjectList = new ArrayList();

  private HashMap oldValues = new HashMap();

  public String tableName;

  public String whereStatement;

  public String sqlType;

  public String[] columnNames;

  public String[] columnValues;

  public String[] columnWhereNames;

  public String[] columnWhereValues;

  /**
   *Gets the tableName attribute of the SqlParser object
   *
   * @return    The tableName value
   * @since
   */
  public String getTableName() {
    return tableName;
  }

  /**
   * Gets the columnNames attribute of the SqlParser object
   *
   * @return    The columnNames value
   * @since
   */
  public String[] getColumnNames() {
    return columnNames;
  }

  public String[] getWhereColumnNames() {
    return columnWhereNames;
  }

  public String[] getWhereColumnValues() {
    return columnWhereValues;
  }

  public String[] getColumnValues() {
    return columnValues;
  }

  /**
   * Parse sql statement.
   *
   * @param  statement Statement object which wrap sql statement
   * @exception  Exception  Description of Exception
   * @since
   */
  public void parse(Statement statement) throws Exception {
    String sql = "";
    if ( statement instanceof CsvStatement )
      sql = ((CsvStatement)statement).getSqlStatement();
    else if( statement instanceof CsvPreparedStatement)
      sql = ((CsvPreparedStatement)statement).getSqlStatement();
    tableName = null;
    columnNames = new String[0];
    columnValues = new String[0];
    columnWhereNames = new String[0];
    columnWhereValues = new String[0];
    whereStatement = null;
    sqlType = null;

    //removing comments
    if( sql.indexOf("/*") != -1 ) {
      StringBuffer buf = new StringBuffer( sql );
      buf.delete( sql.indexOf("/*") , sql.indexOf("*/")+2 );
      sql = buf.toString();
    }
    sql = sql.trim();

CsvDriver.log("sql = "+sql);
	
	oldValues.clear();
	int startIndex = 0;
//replace comma(,) in values between quotes(')
    StringTokenizer tokQuote = new StringTokenizer(sql.toString(), "'", true);
    StringBuffer sb = new StringBuffer();
    boolean openParent1 = false;
    while (tokQuote.hasMoreTokens()) {
	  startIndex++;
      String next = tokQuote.nextToken();
      if (openParent1) {
        next = Utils.replaceAll(next, ",", COMMA_ESCAPE);
        next = Utils.replaceKeywords(next, oldValues, startIndex);
      }
      sb.append(next);
      if (next.equalsIgnoreCase("'")) {
        if (openParent1 == true)
          openParent1 = false;
        else
          openParent1 = true;
      }
    }
//END replacement
    sql = sb.toString();
    String upperSql = sql.toUpperCase();


//handle unsupported statements
    if (upperSql.startsWith("ALTER "))
      throw new Exception("ALTER TABLE statements are not supported.");
    if (upperSql.startsWith("DROP "))
      throw new Exception("DROP statements are not supported.");



//SELECT
    if ( upperSql.startsWith("SELECT ") ) {
      if (upperSql.lastIndexOf(" FROM ") == -1) {
        throw new Exception("Malformed SQL. Missing FROM statement.");
      }

      sqlType = SELECT;
      int fromPos = upperSql.lastIndexOf(" FROM ");
      int wherePos = upperSql.lastIndexOf(" WHERE ");
      if (wherePos == -1)
        tableName = sql.substring(fromPos + 6).trim();
      else
        tableName = sql.substring(fromPos + 6, wherePos).trim();

      Vector cols = new Vector();
      StringTokenizer tokenizer = new StringTokenizer(upperSql.substring(7,
          fromPos), ",");

      while (tokenizer.hasMoreTokens()) {
        cols.add(tokenizer.nextToken().trim());
      }

      columnNames = new String[cols.size()];
      cols.copyInto(columnNames);
      if (wherePos != -1) {
        String strWhere = sql.substring(wherePos + 7);
        Vector whereCols = new Vector();
        Vector whereValues = new Vector();
        StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");

        while (tokenizerWhere.hasMoreTokens()) {
          String strToken = tokenizerWhere.nextToken();
          if (strToken.toLowerCase().indexOf(" and ") != -1) {
            String temp = strToken;
            int andPos = 0;
            out:
            do {
              andPos = temp.toLowerCase().indexOf(" and ");
              String strTokenAdd;
              if (andPos != -1)
                strTokenAdd = temp.substring(0, andPos).trim();
              else
                strTokenAdd = temp.trim();
              int delimiter2 = strTokenAdd.indexOf("=");
              if (delimiter2 != -1) {
                String valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
                valueAdd = Utils.handleQuotedString(valueAdd);
                whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
                valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
                valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
                valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
                whereValues.add(valueAdd);
              }
              else {
                int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
                whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
                whereValues.add(null);
              }
              temp = temp.substring(andPos + 5);
              if (temp.toLowerCase().indexOf(" and ") == -1) {
                strTokenAdd = temp.trim();
                int delimiter4 = strTokenAdd.indexOf("=");
                if (delimiter4 != -1) {
                  String valueAdd = strTokenAdd.substring(delimiter4 + 1).trim();
                  valueAdd = Utils.handleQuotedString(valueAdd);
                  whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
                  valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
                  valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
                  valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
                  whereValues.add(valueAdd);
                }
                else {
                  int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
                  whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
                  whereValues.add(null);
                }
                break out;
              }

            }
            while (true);

          }
          else {
            int delimiter = strToken.indexOf("=");
            if (delimiter != -1) {
              String value = strToken.substring(delimiter + 1).trim();
              value = Utils.handleQuotedString(value);
              whereCols.add(strToken.substring(0, delimiter).trim());
              value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
              value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");