convert.java

Java写的ERP系统

		//  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;
	}   //  convertProcedure

	/**
	 *  Convert Trigger.
	 *  <pre>
	 *      DROP FUNCTION emp_trgF();
	 *      CREATE FUNCTION emp_trg () RETURNS OPAQUE AS '....
	 *          RETURN NEW; ...
	 *          ' LANGUAGE 'plpgsql';
	 *      DROP TRIGGER emp_trg ON emp;
	 *      CREATE TRIGGER emp_trg BEFORE INSERT OR UPDATE ON emp
	 *      FOR EACH ROW EXECUTE PROCEDURE emp_trgF();
	 *  </pre>
	 *  @param sqlStatement
	 *  @return CREATE and DROP TRIGGER and associated Function statement
	 */
	private ArrayList convertTrigger (String sqlStatement)
	{
		ArrayList result = new ArrayList();
		//  Convert statement - to avoid handling contents of comments
		String stmt = converSimpleStatement(sqlStatement);

		//  Trigger specific replacements
		stmt = Pattern.compile("\\bINSERTING\\b").matcher(stmt).replaceAll("TG_OP='INSERT'");
		stmt = Pattern.compile("\\bUPDATING\\b").matcher(stmt).replaceAll("TG_OP='UPDATE'");
		stmt = Pattern.compile("\\bDELETING\\b").matcher(stmt).replaceAll("TG_OP='DELETE'");
		stmt = Pattern.compile(":new.").matcher(stmt).replaceAll("NEW.");
		stmt = Pattern.compile(":old.").matcher(stmt).replaceAll("OLD.");

		//  Double quotes '
		stmt = Pattern.compile("'").matcher(stmt).replaceAll("''");
		//  remove OR REPLACE
		int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE ");
		//  trigger Name
		int triggerPos = stmt.toUpperCase().indexOf(" TRIGGER ") + 9;
		String triggerName = stmt.substring(triggerPos);
		triggerName = triggerName.substring(0, triggerName.indexOf(" "));
		//  table name
		String tableName = stmt.substring(stmt.toUpperCase().indexOf(" ON ")+4);
		tableName = tableName.substring(0, tableName.indexOf(" "));

		//  Function Drop
		if (orReplacePos != -1)
		{
			String drop = "DROP FUNCTION " + triggerName + "F()";
		//	System.out.println(drop);
			result.add(drop);
		}

		//  Function & Trigger
		int pos = stmt.indexOf("DECLARE ");
		if (pos == -1)
			pos = stmt.indexOf("BEGIN ");
		String functionCode = stmt.substring(pos);
		StringBuffer triggerCode = new StringBuffer ("CREATE TRIGGER ");
		triggerCode.append(triggerName).append("\n")
			.append(stmt.substring(triggerPos+triggerName.length(), pos))
			.append("\nEXECUTE PROCEDURE ").append(triggerName).append("F();");

		//  Add NEW to existing Return   --> DELETE Trigger ?
		functionCode = Pattern.compile("\\bRETURN;", Pattern.CASE_INSENSITIVE)
			.matcher(functionCode)
			.replaceAll("RETURN NEW;");
		//  Add final return and change name
		functionCode = Pattern.compile("\\bEND " + triggerName + ";", Pattern.CASE_INSENSITIVE)
			.matcher(functionCode)
			.replaceAll("\nRETURN NEW;\nEND " + triggerName + "F;");

		//  Line separators
		String match =
			  "(\\(.*\\))"                      //  (.) Parameter
			+ "|(;)"                            //  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(functionCode);

		//  Function Header
		StringBuffer sb = new StringBuffer("CREATE FUNCTION ");
		sb.append(triggerName).append("F() RETURNS OPAQUE AS '\n");

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

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

		//  Trigger Drop
		if (orReplacePos != -1)
		{
			String drop = "DROP TRIGGER " + triggerName.toLowerCase() + " ON " + tableName;
	//		System.out.println(drop);
			result.add(drop);
		}

		//  Trigger
		//  Remove Column references OF ... ON
		String trigger = Pattern.compile("\\sOF.*ON\\s")
			.matcher(triggerCode)
			.replaceAll(" ON ");
	//	System.out.println(trigger);
		result.add(trigger);

		//
		return result;
	}   //  convertTrigger

	/**
	 *  Convert View.
	 *  Handle CREATE OR REPLACE
	 *  @param sqlStatement
	 *  @return converted statement(s)
	 */
	private ArrayList convertView (String sqlStatement)
	{
		ArrayList result = new ArrayList();
		String stmt = converSimpleStatement(sqlStatement);

		//  remove OR REPLACE
		int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE ");
		if (orReplacePos != -1)
		{
			int index = stmt.indexOf(" VIEW ");
			int space = stmt.indexOf(' ', index+6);
			String drop = "DROP VIEW " + stmt.substring(index+6, space);
			result.add(drop);
			//
			String create = "CREATE" + stmt.substring(index);
			result.add(create);
		}
		else    //  simple statement
			result.add(stmt);
		return result;
	}   //  convertView

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

	/**
	 *  Converts Decode, Outer Join and Sequence.
	 *  <pre>
	 *      DECODE (a, 1, 'one', 2, 'two', 'none')
	 *       => CASE WHEN a = 1 THEN 'one' WHEN a = 2 THEN 'two' ELSE 'none' END
	 *
	 *      AD_Error_Seq.nextval
	 *       => nextval('AD_Error_Seq')
	 *
	 *      RAISE_APPLICATION_ERROR (-20100, 'Table Sequence not found')
	 *       => RAISE EXCEPTION 'Table Sequence not found'
	 *
	 *  </pre>
	 *  @param sqlStatement
	 *  @return converted statement
	 */
	private String convertComplexStatement(String sqlStatement)
	{
		String retValue = sqlStatement;
		StringBuffer sb = null;

		//  Convert all decode parts
		while (retValue.indexOf("DECODE") != -1)
			retValue = convertDecode(retValue);

		/**
		 * Sequence Handling --------------------------------------------------
		 *  AD_Error_Seq.nextval
		 *  => nextval('AD_Error_Seq')
		 */
		Matcher m = Pattern.compile("\\w+\\.(nextval)|(curval)", Pattern.CASE_INSENSITIVE)
			.matcher(retValue);
		sb = new StringBuffer();
		while (m.find())
		{
			String group = m.group();
		//	System.out.print("-> " + group);
			int pos = group.indexOf(".");
			String seqName = group.substring(0,pos);
			String funcName = group.substring(pos+1);
			group = funcName + "('" + seqName + "')";
		//	System.out.println(" => " + group);
			if (group.indexOf('$') != -1)   //  Group character needs to be escaped
				group = Util.replace(group, "$", "\\$");
			m.appendReplacement(sb, group);
		}
		m.appendTail(sb);
		retValue = sb.toString();

		/**
		 * RAISE --------------------------------------------------------------
		 *  RAISE_APPLICATION_ERROR (-20100, 'Table Sequence not found')
		 *  => RAISE EXCEPTION 'Table Sequence not found'
		 */
		m = Pattern.compile("RAISE_APPLICATION_ERROR\\s*\\(.+'\\)", Pattern.CASE_INSENSITIVE)
			.matcher(retValue);
		sb = new StringBuffer();
		while (m.find())
		{
			String group = m.group();
			System.out.print("-> " + group);
			String result = "RAISE EXCEPTION " + group.substring(group.indexOf('\''), group.lastIndexOf('\'')+1);
			System.out.println(" => " + result);

			if (result.indexOf('$') != -1)   //  Group character needs to be escaped
				result = Util.replace(result, "$", "\\$");
			m.appendReplacement(sb, result);
		}
		m.appendTail(sb);
		retValue = sb.toString();

		//  Truncate Handling -------------------------------------------------
		while (retValue.indexOf("TRUNC") != -1)
			retValue = convertTrunc (retValue);

		//  Outer Join Handling -----------------------------------------------
		int index = retValue.indexOf("SELECT ");
		if (index != -1 && retValue.indexOf("(+)", index) != -1)
			retValue = convertOuterJoin(retValue);

		return retValue;
	}   //  convertComplexStatement

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

	/**
	 *  Converts Decode.
	 *  <pre>
	 *      DECODE (a, 1, 'one', 2, 'two', 'none')
	 *       => CASE WHEN a = 1 THEN 'one' WHEN a = 2 THEN 'two' ELSE 'none' END
	 *  </pre>
	 *  @param sqlStatement
	 *  @return converted statement
	 */
	private String convertDecode(String sqlStatement)
	{
	//	System.out.println("DECODE<== " + sqlStatement);
		String statement = sqlStatement;
		StringBuffer sb = new StringBuffer("CASE");

		int index = statement.indexOf("DECODE");
		String firstPart = statement.substring(0,index);

		//  find the opening (
		index = statement.indexOf('(', index);
		statement = statement.substring(index+1);

		//  find the expression "a" - find first , ignoring ()
		index = Util.findIndexOf (statement, ',');
		String expression = statement.substring(0, index).trim();
	//	System.out.println("Expression=" + expression);

		//  Pairs "1, 'one',"
		statement = statement.substring(index+1);
		index = Util.findIndexOf (statement, ',');
		while (index != -1)
		{
			String first = statement.substring(0, index);
			char cc = statement.charAt(index);
			statement = statement.substring(index+1);
		//	System.out.println("First=" + first + ", Char=" + cc);
			//
			boolean error = false;
			if (cc == ',')
			{
				index = Util.findIndexOf (statement, ',',')');
				if (index == -1)
					error = true;
				else
				{
					String second = statement.substring(0, index);
					sb.append(" WHEN ").append(expression).append("=").append(first.trim())
						.append(" THEN ").append(second.trim());
		//			System.out.println(">>" + sb.toString());
					statement = statement.substring(index+1);
					index = Util.findIndexOf (statement, ',',')');
				}
			}
			else if (cc == ')')
			{
				sb.append(" ELSE ").append(first.trim()).append(" END");
		//		System.out.println(">>" + sb.toString());
				index = -1;
			}
			else
				error = true;
			if (error)
			{
				System.err.println("Convert.convertDecode Error - SQL=(" + sqlStatement
					+ ")\n====Result=(" + sb.toString()
					+ ")\n====Statement=(" + statement
					+ ")\n====First=(" + first
					+ ")\n====Index=" + index);
				m_conversionError = "Decode conversion error";
			}
		}
		sb.append(statement);
		sb.insert(0, firstPart);
	//	System.out.println("DECODE==> " + sb.toString());
		return sb.toString();
	}	//  convertDecode

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

	/**
	 *  Convert Outer Join.
	 *  Converting joins can ve very complex when multiple tables/keys are involved.
	 *  The main scenarios supported are two tables with multiple key columns
	 *  and multiple tables with single key columns.
	 *  <pre>
	 *      SELECT a.Col1, b.Col2 FROM tableA a, tableB b WHERE a.ID=b.ID(+)
	 *      => SELECT a.Col1, b.Col2 FROM tableA a LEFT OUTER JOIN tableB b ON (a.ID=b.ID)
	 *
	 *      SELECT a.Col1, b.Col2 FROM tableA a, tableB b WHERE a.ID(+)=b.ID
	 *      => SELECT a.Col1, b.Col2 FROM tableA a RIGHT OUTER JOIN tableB b ON (a.ID=b.ID)