xmlsqlparser.java

数据仓库工具

/*
    Copyright (C) 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.webdocwf.util.xml;

import java.util.ArrayList;
import java.util.Vector;
import java.util.StringTokenizer;
import java.util.HashMap;
import java.io.*;

/**
 * This is a simple SQL parser used by the Xml JDBC driver.
 *
 * @author     Zoran Milakovic
 */
public class XmlSqlParser {
  /**
   *Description of the Field
   *
   * @since
   */

  public static final String CREATE_TABLE = "create_table";
  public static final String DROP_TABLE = "drop_table";
  public static final String INSERT = "insert";
  public static final String UPDATE = "update";
  public static final String SELECT = "select";
  public static final String DELETE = "delete";

  public static final String quoteEscape = "''";
  private static final String commaEscape = "~#####1~";
  public static final String equalEscape = "~EQUAL~";
  public static final String atEscape = "~AT~";
  public static final String slashEscape = "~SLASH~";

  public static final String BINARY_STREAM_OBJECT =
      "XmlDriverBinaryStreamObject";

  private ArrayList binaryStreamObjectList = new ArrayList();

  private HashMap oldValues = new HashMap();

  private boolean isAutoCommit = true;

  private String fileName = "";

  public String tableName;

  public String whereStatement;

  public String sqlType;

  private String sqlStatement = "";

  public String[] columnNames;

  public String[] primaryKeyColumns;

  public String[] notnullColumns;

  public String[] columnValues;

  public String[] columnWhereNames;

  public String[] columnWhereValues;

  public XmlSqlParser() {
  }

  public XmlSqlParser( String fileName, boolean isAutoCommit ) {
    this.fileName = fileName;
    this.isAutoCommit = isAutoCommit;
  }

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

  /**
   * Gets columns which can not be NULL
   *
   * @return    The tableName value
   * @since
   */
  public String[] getNotnullColumns() {
    return this.notnullColumns;
  }

  /**
   *Gets the type of sql statement.INSERT , UPDATE , CREATE , SELECT, DROP_TABLE, CREATE_TABLE
   *
   * @return    The type of sql statement.
   * @since
   */
  public String getSQLType() {
    return this.sqlType;
  }

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

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

  /**
   * Gets array of column values.Used in INSERT , UPDATE statements
   * @return columnValues as array of string.
   */
  public String[] getColumnValues() {
    return columnValues;
  }

  /**
   * Gets array of column which are primarykeys.Used in INSERT statements
   * @return primarykeys as array of string.
   */
  public String[] getPrimaryKeys() {
    return primaryKeyColumns;
  }

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

  public String getSqlStatement() {
    return this.sqlStatement;
  }

  /**
   * Description of the Method
   *
   * @param  sql            Description of Parameter
   * @exception  Exception  Description of Exception
   * @since
   */
  public void parse( String sql ) throws Exception {
    this.sqlStatement = sql;
    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();

	oldValues.clear();
  	int startIndex = 0;
    //replace special characters between single 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, ",", commaEscape );
        next = Utils.replaceAll( next, "=", equalEscape );
        next = Utils.replaceAll( next, "@", atEscape );
        next = Utils.replaceAll( next, "/", slashEscape );
        next = Utils.replaceKeywords(next, oldValues, startIndex );
      }
      sb.append( next );
      if ( next.equalsIgnoreCase( "'" ) ) {
        if ( openParent1 == true )
          openParent1 = false;
        else
          openParent1 = true;
      }
    }

    sql = sb.toString();
    String upperSql = sql.toUpperCase();

XmlDriver.log("sql = "+sql);

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

//DROP TABLE
    if ( upperSql.startsWith( "DROP TABLE" ) ) {
      sqlType = DROP_TABLE;
      int dropPos = upperSql.indexOf( "DROP TABLE" );
      tableName = sql.substring( dropPos + 10 ).trim().toUpperCase();
    }

//DELETE
    else if ( upperSql.startsWith( "DELETE " ) ) {
      sqlType = DELETE;
      int deletePos = upperSql.indexOf( "DELETE" );
      int wherePos = upperSql.indexOf( " WHERE " );
      int fromPos = upperSql.lastIndexOf( " FROM " );
      if ( wherePos == -1 )
        tableName = sql.substring( fromPos + 6 ).trim().toUpperCase();
      else
        tableName = sql.substring( fromPos + 6, wherePos ).trim().toUpperCase();

      if ( wherePos != -1 ) {
        String strWhere = sql.substring( wherePos + 6 ).trim();
        Vector whereCols = new Vector();
        Vector whereValues = new Vector();
        StringTokenizer tokenizerWhere = new StringTokenizer( strWhere, "," );
        while ( tokenizerWhere.hasMoreTokens() ) {
          String strToken = tokenizerWhere.nextToken();
          if ( strToken.toUpperCase().indexOf( " AND " ) != -1 ) {
            String temp = strToken;
            int andPos = 0;
            out:
            do {
              andPos = temp.toUpperCase().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().toUpperCase() );
                valueAdd = Utils.replaceAll( valueAdd, commaEscape, "," );
                valueAdd = Utils.replaceAll( valueAdd, quoteEscape, "'" );
                valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
                whereValues.add( valueAdd );
              }
              else {
                int delimiter3 = strTokenAdd.toLowerCase().indexOf( " is " );
                whereCols.add( strTokenAdd.substring( 0, delimiter3 ).trim().
                               toUpperCase() );
                whereValues.add( null );
              }
              temp = temp.substring( andPos + 5 );
              if ( temp.toUpperCase().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().toUpperCase() );
                  valueAdd = Utils.replaceAll( valueAdd, commaEscape, "," );
                  valueAdd = Utils.replaceAll( valueAdd, quoteEscape, "'" );
                  valueAdd = Utils.replaceKeywordsBack(valueAdd, oldValues);
                  whereValues.add( valueAdd );
                }
                else {
                  int delimiter3 = strTokenAdd.toLowerCase().indexOf( " is " );
                  whereCols.add( strTokenAdd.substring( 0, delimiter3 ).trim().
                                 toUpperCase() );
                  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().toUpperCase() );
              value = Utils.replaceAll( value, commaEscape, "," );
              value = Utils.replaceAll( value, quoteEscape, "'" );
              value = Utils.replaceKeywordsBack(value, oldValues);
              whereValues.add( value );
            }
            else {
              int delimiter1 = strToken.toLowerCase().indexOf( " is " );
              whereCols.add( strToken.substring( 0, delimiter1 ).trim().
                             toUpperCase() );
              whereValues.add( null );
            }
          }
        }
        columnWhereNames = new String[whereCols.size()];
        columnWhereValues = new String[whereValues.size()];
        whereCols.copyInto( columnWhereNames );
        whereValues.copyInto( columnWhereValues );
      }
    }

//SELECT
    else 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().toUpperCase();