convert.java

Java写的ERP系统

/******************************************************************************
 * The contents of this file are subject to the   Compiere License  Version 1.1
 * ("License"); You may not use this file except in compliance with the License
 * You may obtain a copy of the License at http://www.compiere.org/license.html
 * Software distributed under the License is distributed on an  "AS IS"  basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
 * the specific language governing rights and limitations under the License.
 * The Original Code is                  Compiere  ERP & CRM  Business Solution
 * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
 * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
 * Contributor(s): ______________________________________.
 *****************************************************************************/
package org.compiere.dbPort;

import java.util.*;
import java.util.regex.*;
import java.sql.*;

import org.compiere.db.*;
import org.compiere.util.*;

/**
 *  Convert SQL to Target DB
 *
 *  @author     Jorg Janke, Victor Perez
 *  @version    $Id: Convert.java,v 1.17 2002/06/10 03:09:54 jjanke Exp $
 */
public class Convert
{
	/**
	 *  Cosntructor
	 *  @param type
	 *  @param conn optional - required for execute
	 */
	public Convert (String type, Connection conn)
	{
		if (Database.DB_ORACLE.equals(type))
			m_isOracle = true;
		else if (Database.DB_POSTGRESQL.equals(type))
			m_map = ConvertMap.getPostgetSQLMap();
		else
			throw new UnsupportedOperationException ("Unsupported database");
		//
		setConnection(conn);
	}   //  Convert

	/** Version                         */
	public static final String      VERSION = "$Id: Convert.java,v 1.17 2002/06/10 03:09:54 jjanke Exp $";
	/** RegEx: insensitive and dot to include line end characters   */
	public static final int         REGEX_FLAGS = Pattern.CASE_INSENSITIVE | Pattern.DOTALL;

	/** Is Oracle                       */
	private boolean                 m_isOracle = false;
	/** Used Resorce Bundle             */
	private TreeMap                 m_map;

	/** Used Connection                 */
	private Connection              m_conn = null;
	/** Statement used                  */
	private Statement               m_stmt = null;

	/** Last Conversion Error           */
	private String                  m_conversionError = null;
	/** Last Execution Error            */
	private Exception               m_exception = null;
	/** Verbose Messages                */
	private boolean                 m_verbose = true;

	/**
	 *  Dispose - close resources
	 */
	public void dispose()
	{
		try
		{
			if (m_stmt != null)
				m_stmt.close();
			//  connection not closed.
		}
		catch (SQLException e)
		{
			System.err.println("Convert.setConnection " + e);
		}
	}   //  dispose

	/**
	 *  Set Connection (set to AutoCommit)
	 *  @param conn
	 */
	public void setConnection (Connection conn)
	{
		m_conn = conn;
		try
		{
			if (m_conn != null)
				m_conn.setAutoCommit(true);
		}
		catch (SQLException e)
		{
			System.err.println("Convert.setConnection " + e);
		}
	}   //  setConnection

	/**
	 *  Get Connection
	 *  @return connection
	 */
	public Connection getConnection()
	{
		return m_conn;
	}   //  getConnection

	/**
	 *  Set Verbose
	 *  @param verbose
	 */
	public void setVerbose (boolean verbose)
	{
		m_verbose = verbose;
	}   //  setVerbose

	/**
	 *  Is Oracle DB
	 *  @return true if connection is Oracle DB
	 */
	public boolean isOracle()
	{
		return m_isOracle;
	}   //  isOracle

	/*************************************************************************/

	/**
	 *  Execute SQL Statement (stops at first error).
	 *  If an error occured hadError() returns true.
	 *  You can get details via getConversionError() or getException()
	 *  @param sqlStatements
	 *  @returns true if success
	 *  @throws IllegalStateException if no connection
	 */
	public boolean execute (String sqlStatements)
	{
		if (m_conn == null)
			throw new IllegalStateException ("Convert.execute requires connection");
		//
		String[] sql = convert (sqlStatements);
		m_exception = null;
		if (m_conversionError != null || sql == null)
			return false;

		boolean ok = true;
		int i = 0;
		String statement = null;
		try
		{
			if (m_stmt == null)
				m_stmt = m_conn.createStatement();
			//
			for (i = 0; ok && i < sql.length; i++)
			{
				statement = sql[i];
				if (statement.length() == 0)
				{
					if (m_verbose)
						System.out.println("Skipping empty (" + i + ")");
				}
				else
				{
					if (m_verbose)
						System.out.println("Executing (" + i + ") <<" + statement + ">>");
					else
						System.out.println("Executing " + i);
					try
					{
						m_stmt.clearWarnings();
						int no = m_stmt.executeUpdate(statement);
						SQLWarning warn = m_stmt.getWarnings();
						if (warn != null)
						{
							if (m_verbose)
								System.out.println("- " + warn);
							else
							{
								System.out.println("Executing (" + i + ") <<" + statement + ">>");
								System.out.println("- " + warn);
							}
						}
						if (m_verbose)
							System.out.println("- ok " + no);
					}
					catch (SQLException ex)
					{
						//  Ignore Drop Errors
						if (!statement.startsWith("DROP "))
						{
							ok = false;
							m_exception = ex;
						}
						if (!m_verbose)
							System.out.println("Executing (" + i + ") <<" + statement + ">>");
						System.out.println("Error executing " + i + "/" + sql.length + " = " + ex);
					}
				}
			}   //  for all statements
		}
		catch (SQLException e)
		{
			m_exception = e;
			if (!m_verbose)
				System.out.println("Executing (" + i + ") <<" + statement + ">>");
			System.out.println("Error executing " + i + "/" + sql.length + " = " + e);
			return false;
		}
		return ok;
	}   //  execute

	/**
	 *  Return last execution exception
	 *  @return execution exception
	 */
	public Exception getException()
	{
		return m_exception;
	}   //  getException

	/**
	 *  Returns true if a conversion or execution error had occured.
	 *  Get more details via getConversionError() or getException()
	 *  @return true if error had occured
	 */
	public boolean hasError()
	{
		return (m_exception != null) | (m_conversionError != null);
	}   //  hasError

	/**
	 *  Convert SQL Statement (stops at first error).
	 *  Statements are delimited by /
	 *  If an error occured hadError() returns true.
	 *  You can get details via getConversionError()
	 *  @param sqlStatements
	 *  @return converted statement as a string
	 */
	public String convertAll (String sqlStatements)
	{
		String[] sql = convert (sqlStatements);
		StringBuffer sb = new StringBuffer (sqlStatements.length() + 10);
		for (int i = 0; i < sql.length; i++)
		{
			//  line.separator
			sb.append(sql[i]).append("\n/\n");
			if (m_verbose)
				System.out.println("Statement " + i + ": " + sql[i]);
		}
		return sb.toString();
	}   //  convertAll

	/**
	 *  Convert SQL Statement (stops at first error).
	 *  If an error occured hadError() returns true.
	 *  You can get details via getConversionError()
	 *  @param sqlStatements
	 *  @return Array of converted Statements
	 */
	public String[] convert (String sqlStatements)
	{
		m_conversionError = null;
		if (sqlStatements == null || sqlStatements.length() == 0)
		{
			m_conversionError = "Convert.convert - SQL_Statement is null or has zero length";
			return null;
		}
		//
		return convertIt (sqlStatements);
	}   //  convert

	/**
	 *  Return last conversion error or null.
	 *  @return lst conversion error
	 */
	public String getConversionError()
	{
		return m_conversionError;
	}   //  getConversionError

	/*************************************************************************/

	/**
	 *  Conversion routine (stops at first error).
	 *  <pre>
	 *  - mask / in Strings
	 *  - break into single statement
	 *  - unmask statements
	 *  - for each statement: convertStatement
	 *      - remove comments
	 *          - process FUNCTION/TRIGGER/PROCEDURE
	 *          - process Statement: convertSimpleStatement
	 *              - based on ConvertMap
	 *              - convertComplexStatement
	 *                  - decode, sequence, exception
	 *  </pre>
	 *  @param sqlStatements
	 *  @return array of converted statements
	 */
	private String[] convertIt (String sqlStatements)
	{
		//  Need to mask / in SQL Strings !
		final char MASK = '\u001F';      //  Unit Separator
		StringBuffer masked = new StringBuffer(sqlStatements.length());
		Matcher m = Pattern.compile("'[^']+'", Pattern.DOTALL).matcher(sqlStatements);
		while (m.find())
		{
			String group = m.group();       //  SQL string
			if (group.indexOf("/") != -1)   //  / in string
				group = group.replace('/', MASK);
			if (group.indexOf('$') != -1)   //  Group character needs to be escaped
				group = Util.replace(group, "$", "\\$");
			m.appendReplacement(masked, group);
		}
		m.appendTail(masked);
		String tempResult = masked.toString();
		/** @todo Need to mask / in comments */

		//  Statements ending with /
		String[] sql = tempResult.split("\\s/\\s");  // ("(;\\s)|(\\s/\\s)");
		ArrayList result = new ArrayList (sql.length);
		//  process statements
		for (int i = 0; i < sql.length; i++)
		{
			String statement = sql[i];
			if (statement.indexOf(MASK) != -1)
				statement = statement.replace(MASK, '/');
			result.addAll(convertStatement(statement));     //  may return more than one target statement
		}
		//  convert to array
		sql = new String[result.size()];
		result.toArray(sql);
		return sql;
	}   //  convertIt

	/**
	 *  Convert single Statements.
	 *  - remove comments
	 *      - process FUNCTION/TRIGGER/PROCEDURE
	 *      - process Statement
	 *  @param sqlStatement
	 *  @return converted statement
	 */
	private ArrayList convertStatement (String sqlStatement)
	{
		ArrayList result = new ArrayList();
		if (m_isOracle)
		{
			result.add(sqlStatement);
			return result;
		}

		//  remove comments
		String statement = removeComments (sqlStatement);
	//	System.out.println("------------------------------------------------------------");