convert.java

Java写的ERP系统

	//	System.out.println(statement);
	//	System.out.println("------------------->");

		String cmpString = statement.toUpperCase();
		boolean isCreate = cmpString.startsWith("CREATE ");

		//  Process
		if (isCreate && cmpString.indexOf(" FUNCTION ") != -1)
			result.addAll(convertFunction(statement));

		else if (isCreate && cmpString.indexOf(" TRIGGER ") != -1)
			result.addAll(convertTrigger(statement));

		else if (isCreate && cmpString.indexOf(" PROCEDURE ") != -1)
			result.addAll(convertProcedure(statement));

		else if (isCreate && cmpString.indexOf(" VIEW ") != -1)
			result.addAll(convertView(statement));

		//  Simple Statement
		else
			result.add(converSimpleStatement(statement));
		//
	//	System.out.println("<-------------------");
	//	for (int i = 0; i < result.size(); i++)
	//		System.out.println(result.get(i));
	//	System.out.println("------------------------------------------------------------");

		return result;
	}   //  convertStatement

	/**
	 *  Convert simple SQL Statement.
	 *  Based on ConvertMap
	 *
	 *  @param sqlStatement
	 *  @return converted Statement
	 */
	private String converSimpleStatement (String sqlStatement)
	{
		//  Error Checks
		if (sqlStatement.toUpperCase().indexOf("EXCEPTION WHEN") != -1)
		{
			String error = "Convert.convertSimpleStatement - Exception clause needs to be converted: " + sqlStatement;
			System.out.println (error);
			m_conversionError = error;
			return sqlStatement;
		}

		//  Standard Statement
		String retValue = sqlStatement;
		Iterator iter = m_map.keySet().iterator();
		while (iter.hasNext())
		{
			String regex = (String)iter.next();
			try
			{
				Pattern p = Pattern.compile(regex, REGEX_FLAGS);
				Matcher m = p.matcher(retValue);
				retValue = m.replaceAll((String)m_map.get(regex));
			}
			catch (Exception e)
			{
				String error = "Convert.convertSimpleStatement - Error expression: " + regex + " - " + e;
				System.out.println(error);
				m_conversionError = error;
			}
		}

		//  Convert Decode, Sequence, Join, ..
		return convertComplexStatement(retValue);
	}   //  convertSimpleStatement

	/**
	 *  Clean up Statement.
	 *  Remove all comments and while spaces
	 *  Database specific functionality can me tagged as follows:
	 *  <pre>
	 *	&#047;*ORACLE&gt;*&#047;
	 *      Oracle Specific Statement
	 *	&#047;*&lt;ORACLE*&#047;
	 *	&#047;*POSTGRESQL&gt;
	 *      PostgreSQL Specicic Statements
	 *	&lt;POSTGRESQL*&#047;
	 *  </pre>
	 *  @param statement
	 *  @return sql statement
	 */
	protected String removeComments (String statement)
	{
		String clean = statement.trim();

		//  Remove /*ORACLE>*/ /*<ORACLE*/
		Matcher m = Pattern.compile("\\/\\*ORACLE>.*<ORACLE\\*\\/", Pattern.DOTALL).matcher(clean);
		clean = m.replaceAll("");

		//  Remove /.POSTGRESQL>
		m = Pattern.compile("\\/\\*POSTGRESQL>").matcher(clean);
		clean = m.replaceAll("");
		//	Remove <POSTGRESQL./
		m = Pattern.compile("<POSTGRESQL\\*\\/").matcher(clean);
		clean = m.replaceAll("");

		//  Remove /* */
		m = Pattern.compile("\\/\\*.*\\*\\/", Pattern.DOTALL).matcher(clean);
		clean = m.replaceAll("");

		//  Remove --
		m = Pattern.compile("--.*$").matcher(clean);        //  up to EOL
		clean = m.replaceAll("");
		m = Pattern.compile("--.*[\\n\\r]").matcher(clean); //  -- at BOL
		clean = m.replaceAll("");

		//  Convert cr/lf/tab to single space
		m = Pattern.compile("\\s+").matcher(clean);
		clean = m.replaceAll(" ");

		clean = clean.trim();
		return clean;
	}   //  removeComments

	/**
	 *  Convert Function.
	 *  <pre>
	 *      CREATE OR REPLACE FUNCTION AD_Message_Get
	 *      (p_AD_Message IN VARCHAR, p_AD_Language IN VARCHAR)
	 *      RETURN VARCHAR AS
	 *      ...
	 *      END AD_Message_Get;
	 *  =>
	 *      CREATE FUNCTION AD_Message_Get
	 *      (VARCHAR, VARCHAR)
	 *      RETURNS VARCHAR AS '
	 *      DECLARE
	 *      p_AD_Message ALIAS FOR $1;
	 *      p_AD_Language ALIAS FOR $2;
	 *      ....
	 *      END;
	 *      ' LANGUAGE 'plpgsql';
	 *  </pre>
	 *  @param sqlStatement
	 *  @return CREATE and DROP Function statement
	 */
	private ArrayList convertFunction (String sqlStatement)
	{
		ArrayList result = new ArrayList();
		//  Convert statement - to avoid handling contents of comments
		String stmt = converSimpleStatement(sqlStatement);
		//  Double quotes '
		stmt = Pattern.compile("'").matcher(stmt).replaceAll("''");
		//  remove OR REPLACE
		int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE ");
		if (orReplacePos != -1)
			stmt = "CREATE" + stmt.substring(orReplacePos+11);

		//  Line separators
		String match =
			  "(\\([^\\)]*\\))"                 //  (.) Parameter
			+ "|(\\bRETURN \\w+ (AS)|(IS))"     //  RETURN CLAUSE
			+ "|(;)"                            //  Statement End
			//  Nice to have - for readability
			+ "|(\\bBEGIN\\b)"                  //  BEGIN
			+ "|(\\bTHEN\\b)"
			+ "|(\\bELSE\\b)"
			+ "|(\\bELSIF\\b)";
		Matcher m = Pattern.compile(match, Pattern.CASE_INSENSITIVE).matcher(stmt);

		StringBuffer sb = new StringBuffer();
		//  First group -> ( )
		//  CREATE OR REPLACE FUNCTION AD_Message_Get ( p_AD_Message IN VARCHAR, p_AD_Language IN VARCHAR)
		//  CREATE FUNCTION AD_Message_Get (VARCHAR, VARCHAR)
		m.find();
		m.appendReplacement(sb, "");
		String name = sb.substring(6).trim();
		StringBuffer signature = new StringBuffer();
		//
		String group = m.group().trim();
	//	System.out.println("Group: " + group);
		StringBuffer alias = new StringBuffer();
		//  Parameters
		if (group.startsWith("(") && group.endsWith(")"))
		{
			//  Default not supported
			if (group.toUpperCase().indexOf(" DEFAULT ") != -1)
			{
				String error = "Convert.convertFunction - DEFAULT in Parameter not supported";
				System.out.println (error);
				m_conversionError = error;
				return result;
			}
			signature.append("(");
			if (group.length() > 2)
			{
				group = group.substring(1,group.length()-1);
				//  Paraneters are delimited by ,
				String[] parameters = group.split(",");
				for (int i = 0; i < parameters.length; i++)
				{
					if (i != 0)
						signature.append(", ");
					//  name ALIAS FOR $1
					String p = parameters[i].trim();
					alias.append(p.substring(0,p.indexOf(" ")))
						.append(" ALIAS FOR $").append(i+1).append(";\n");
					//  Datatape
					signature.append(p.substring(p.lastIndexOf(" ")+1));
				}
			}
			signature.append(")");
			sb.append(signature);
		//	System.out.println("Alias: " + alias.toString());
		//	System.out.println("Signature: " + signature.toString());
		}
		//  No Parameters
		else
		{
			String error = "ConvertFunction - Missing Parameter ()";
			System.out.println (error);
			m_conversionError = error;
			return result;
		}
		sb.append("\n");
		//  Need to create drop statement
		if (orReplacePos != -1)
		{
			String drop = "DROP " + name + signature.toString();
		//	System.out.println(drop);
			result.add(drop);
		}
	//	System.out.println("1>" + sb.toString() + "<1");

		//  Second Group -> RETURN VARCHAR AS
		//  RETURNS VARCHAR AS
		m.find();
		group = m.group();
		m.appendReplacement(sb, "");
		if (group.startsWith("RETURN"))
			sb.append("RETURNS").append(group.substring(group.indexOf(" ")));
		sb.append(" '\nDECLARE\n")
			.append(alias);         //  add aliases here
	//	System.out.println("2>" + sb.toString() + "<2");

		//  remainder statements
		while (m.find())
		{
			String group2 = m.group();
			if (group2.indexOf('$') != -1)   //  Group character needs to be escaped
				group2 = Util.replace(group2, "$", "\\$");
			m.appendReplacement(sb, group2);
			sb.append("\n");
		}
		m.appendTail(sb);

		//  finish
		sb.append("' LANGUAGE 'plpgsql';");
	//	System.out.println(">" + sb.toString() + "<");
		result.add(sb.toString());
		//
		return result;
	}   //  convertFunction

	/**
	 *  Convert Procedure.
	 *  <pre>
	 *      CREATE OR REPLACE PROCEDURE AD_Message_X
	 *      (p_AD_Message IN VARCHAR, p_AD_Language IN VARCHAR)
	 *      ...
	 *      END AD_Message_X;
	 *  =>
	 *      CREATE FUNCTION AD_Message_X
	 *      (VARCHAR, VARCHAR)
	 *      RETURNS VARCHAR AS '
	 *      DECLARE
	 *      p_AD_Message ALIAS FOR $1;
	 *      p_AD_Language ALIAS FOR $2;
	 *      ....
	 *      END;
	 *      ' LANGUAGE 'plpgsql';
	 *  </pre>
	 *  @param sqlStatement
	 *  @return CREATE and DROP Function statement
	 */
	private ArrayList convertProcedure (String sqlStatement)
	{
		ArrayList result = new ArrayList();
		//  Convert statement - to avoid handling contents of comments
		String stmt = converSimpleStatement(sqlStatement);
		//  Double quotes '
		stmt = Pattern.compile("'").matcher(stmt).replaceAll("''");
		//  remove OR REPLACE
		int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE ");
		if (orReplacePos != -1)
			stmt = "CREATE" + stmt.substring(orReplacePos+11);

		//  Line separators
		String match =
			  "(\\([^\\)]*\\))"                 //  (.) Parameter
			+ "|(\\bRETURN \\w+ (AS)|(IS))"     //  RETURN CLAUSE
			+ "|(;)"                            //  Statement End
			//  Nice to have - for readability
			+ "|(\\bBEGIN\\b)"                  //  BEGIN
			+ "|(\\bTHEN\\b)"
			+ "|(\\bELSE\\b)"
			+ "|(\\bELSIF\\b)";
		Matcher m = Pattern.compile(match, Pattern.CASE_INSENSITIVE).matcher(stmt);

		StringBuffer sb = new StringBuffer();
		//  First group -> ( )
		//  CREATE OR REPLACE FUNCTION AD_Message_Get ( p_AD_Message IN VARCHAR, p_AD_Language IN VARCHAR)
		//  CREATE FUNCTION AD_Message_Get (VARCHAR, VARCHAR)
		m.find();
		m.appendReplacement(sb, "");
		String name = sb.substring(6).trim();
		StringBuffer signature = new StringBuffer();
		//
		String group = m.group().trim();
	//	System.out.println("Group: " + group);
		StringBuffer alias = new StringBuffer();
		//  Parameters
		if (group.startsWith("(") && group.endsWith(")"))
		{
			//  Default not supported
			if (group.toUpperCase().indexOf(" DEFAULT ") != -1)
			{
				String error = "Convert.convertProcedure - DEFAULT in Parameter not supported";
				System.out.println (error);
				m_conversionError = error;
				return result;
			}
			signature.append("(");
			if (group.length() > 2)
			{
				group = group.substring(1,group.length()-1);
				//  Paraneters are delimited by ,
				String[] parameters = group.split(",");
				for (int i = 0; i < parameters.length; i++)
				{
					if (i != 0)
						signature.append(", ");
					//  name ALIAS FOR $1
					String p = parameters[i].trim();
					alias.append(p.substring(0,p.indexOf(" ")))
						.append(" ALIAS FOR $").append(i+1).append(";\n");
					//  Datatape
					signature.append(p.substring(p.lastIndexOf(" ")+1));
				}
			}
			signature.append(")");
			sb.append(signature);
		//	System.out.println("Alias: " + alias.toString());
		//	System.out.println("Signature: " + signature.toString());
		}
		//  No Parameters
		else
		{
			String error = "Convert.converProcedure - Missing Parameter ()";
			System.out.println (error);
			m_conversionError = error;
			return result;
		}
		sb.append("\n");