1 14 package org.compiere.dbPort; 15 16 import java.util.*; 17 import java.util.regex.*; 18 import java.sql.*; 19 20 import org.compiere.db.*; 21 import org.compiere.util.*; 22 23 29 public class Convert 30 { 31 36 public Convert (String type, Connection conn) 37 { 38 if (Database.DB_ORACLE.equals(type)) 39 m_isOracle = true; 40 else if (Database.DB_POSTGRESQL.equals(type)) 41 m_map = ConvertMap.getPostgetSQLMap(); 42 else 43 throw new UnsupportedOperationException ("Unsupported database"); 44 setConnection(conn); 46 } 48 49 public static final String VERSION = "$Id: Convert.java,v 1.18 2003/06/14 04:30:35 jjanke Exp $"; 50 51 public static final int REGEX_FLAGS = Pattern.CASE_INSENSITIVE | Pattern.DOTALL; 52 53 54 private boolean m_isOracle = false; 55 56 private TreeMap m_map; 57 58 59 private Connection m_conn = null; 60 61 private Statement m_stmt = null; 62 63 64 private String m_conversionError = null; 65 66 private Exception m_exception = null; 67 68 private boolean m_verbose = true; 69 70 73 public void dispose() 74 { 75 try 76 { 77 if (m_stmt != null) 78 m_stmt.close(); 79 } 81 catch (SQLException e) 82 { 83 System.err.println("Convert.setConnection " + e); 84 } 85 } 87 91 public void setConnection (Connection conn) 92 { 93 m_conn = conn; 94 try 95 { 96 if (m_conn != null) 97 m_conn.setAutoCommit(true); 98 } 99 catch (SQLException e) 100 { 101 System.err.println("Convert.setConnection " + e); 102 } 103 } 105 109 public Connection getConnection() 110 { 111 return m_conn; 112 } 114 118 public void setVerbose (boolean verbose) 119 { 120 m_verbose = verbose; 121 } 123 127 public boolean isOracle() 128 { 129 return m_isOracle; 130 } 132 133 134 142 public boolean execute (String sqlStatements) 143 { 144 if (m_conn == null) 145 throw new IllegalStateException ("Convert.execute requires connection"); 146 String [] sql = convert (sqlStatements); 148 m_exception = null; 149 if (m_conversionError != null || sql == null) 150 return false; 151 152 boolean ok = true; 153 int i = 0; 154 String statement = null; 155 try 156 { 157 if (m_stmt == null) 158 m_stmt = m_conn.createStatement(); 159 for (i = 0; ok && i < sql.length; i++) 161 { 162 statement = sql[i]; 163 if (statement.length() == 0) 164 { 165 if (m_verbose) 166 System.out.println("Skipping empty (" + i + ")"); 167 } 168 else 169 { 170 if (m_verbose) 171 System.out.println("Executing (" + i + ") <<" + statement + ">>"); 172 else 173 System.out.println("Executing " + i); 174 try 175 { 176 m_stmt.clearWarnings(); 177 int no = m_stmt.executeUpdate(statement); 178 SQLWarning warn = m_stmt.getWarnings(); 179 if (warn != null) 180 { 181 if (m_verbose) 182 System.out.println("- " + warn); 183 else 184 { 185 System.out.println("Executing (" + i + ") <<" + statement + ">>"); 186 System.out.println("- " + warn); 187 } 188 } 189 if (m_verbose) 190 System.out.println("- ok " + no); 191 } 192 catch (SQLException ex) 193 { 194 if (!statement.startsWith("DROP ")) 196 { 197 ok = false; 198 m_exception = ex; 199 } 200 if (!m_verbose) 201 System.out.println("Executing (" + i + ") <<" + statement + ">>"); 202 System.out.println("Error executing " + i + "/" + sql.length + " = " + ex); 203 } 204 } 205 } } 207 catch (SQLException e) 208 { 209 m_exception = e; 210 if (!m_verbose) 211 System.out.println("Executing (" + i + ") <<" + statement + ">>"); 212 System.out.println("Error executing " + i + "/" + sql.length + " = " + e); 213 return false; 214 } 215 return ok; 216 } 218 222 public Exception getException() 223 { 224 return m_exception; 225 } 227 232 public boolean hasError() 233 { 234 return (m_exception != null) | (m_conversionError != null); 235 } 237 245 public String convertAll (String sqlStatements) 246 { 247 String [] sql = convert (sqlStatements); 248 StringBuffer sb = new StringBuffer (sqlStatements.length() + 10); 249 for (int i = 0; i < sql.length; i++) 250 { 251 sb.append(sql[i]).append("\n/\n"); 253 if (m_verbose) 254 System.out.println("Statement " + i + ": " + sql[i]); 255 } 256 return sb.toString(); 257 } 259 266 public String [] convert (String sqlStatements) 267 { 268 m_conversionError = null; 269 if (sqlStatements == null || sqlStatements.length() == 0) 270 { 271 m_conversionError = "Convert.convert - SQL_Statement is null or has zero length"; 272 return null; 273 } 274 return convertIt (sqlStatements); 276 } 278 282 public String getConversionError() 283 { 284 return m_conversionError; 285 } 287 288 289 306 private String [] convertIt (String sqlStatements) 307 { 308 final char MASK = '\u001F'; StringBuffer masked = new StringBuffer (sqlStatements.length()); 311 Matcher m = Pattern.compile("'[^']+'", Pattern.DOTALL).matcher(sqlStatements); 312 while (m.find()) 313 { 314 String group = m.group(); if (group.indexOf("/") != -1) group = group.replace('/', MASK); 317 if (group.indexOf('$') != -1) group = Util.replace(group, "$", "\\$"); 319 m.appendReplacement(masked, group); 320 } 321 m.appendTail(masked); 322 String tempResult = masked.toString(); 323 324 325 String [] sql = tempResult.split("\\s/\\s"); ArrayList result = new ArrayList (sql.length); 328 for (int i = 0; i < sql.length; i++) 330 { 331 String statement = sql[i]; 332 if (statement.indexOf(MASK) != -1) 333 statement = statement.replace(MASK, '/'); 334 result.addAll(convertStatement(statement)); } 336 sql = new String [result.size()]; 338 result.toArray(sql); 339 return sql; 340 } 342 350 private ArrayList convertStatement (String sqlStatement) 351 { 352 ArrayList result = new ArrayList(); 353 if (m_isOracle) 354 { 355 result.add(sqlStatement); 356 return result; 357 } 358 359 String statement = removeComments (sqlStatement); 361 365 String cmpString = statement.toUpperCase(); 366 boolean isCreate = cmpString.startsWith("CREATE "); 367 368 if (isCreate && cmpString.indexOf(" FUNCTION ") != -1) 370 result.addAll(convertFunction(statement)); 371 372 else if (isCreate && cmpString.indexOf(" TRIGGER ") != -1) 373 result.addAll(convertTrigger(statement)); 374 375 else if (isCreate && cmpString.indexOf(" PROCEDURE ") != -1) 376 result.addAll(convertProcedure(statement)); 377 378 else if (isCreate && cmpString.indexOf(" VIEW ") != -1) 379 result.addAll(convertView(statement)); 380 381 else 383 result.add(converSimpleStatement(statement)); 384 390 return result; 391 } 393 400 private String converSimpleStatement (String sqlStatement) 401 { 402 if (sqlStatement.toUpperCase().indexOf("EXCEPTION WHEN") != -1) 404 { 405 String error = "Convert.convertSimpleStatement - Exception clause needs to be converted: " + sqlStatement; 406 System.out.println (error); 407 m_conversionError = error; 408 return sqlStatement; 409 } 410 411 String retValue = sqlStatement; 413 Iterator iter = m_map.keySet().iterator(); 414 while (iter.hasNext()) 415 { 416 String regex = (String )iter.next(); 417 try 418 { 419 Pattern p = Pattern.compile(regex, REGEX_FLAGS); 420 Matcher m = p.matcher(retValue); 421 retValue = m.replaceAll((String )m_map.get(regex)); 422 } 423 catch (Exception e) 424 { 425 String error = "Convert.convertSimpleStatement - Error expression: " + regex + " - " + e; 426 System.out.println(error); 427 m_conversionError = error; 428 } 429 } 430 431 return convertComplexStatement(retValue); 433 } 435 450 protected String removeComments (String statement) 451 { 452 String clean = statement.trim(); 453 454 Matcher m = Pattern.compile("\\/\\*ORACLE>.*<ORACLE\\*\\/", Pattern.DOTALL).matcher(clean); 456 clean = m.replaceAll(""); 457 458 m = Pattern.compile("\\/\\*POSTGRESQL>").matcher(clean); 460 clean = m.replaceAll(""); 461 m = Pattern.compile("<POSTGRESQL\\*\\/").matcher(clean); 463 clean = m.replaceAll(""); 464 465 m = Pattern.compile("\\/\\*.*\\*\\/", Pattern.DOTALL).matcher(clean); 467 clean = m.replaceAll(""); 468 469 m = Pattern.compile("--.*$").matcher(clean); clean = m.replaceAll(""); 472 m = Pattern.compile("--.*[\\n\\r]").matcher(clean); clean = m.replaceAll(""); 474 475 m = Pattern.compile("\\s+").matcher(clean); 477 clean = m.replaceAll(" "); 478 479 clean = clean.trim(); 480 return clean; 481 } 483 505 private ArrayList convertFunction (String sqlStatement) 506 { 507 ArrayList result = new ArrayList(); 508 String stmt = converSimpleStatement(sqlStatement); 510 stmt = Pattern.compile("'").matcher(stmt).replaceAll("''"); 512 int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE "); 514 if (orReplacePos != -1) 515 stmt = "CREATE" + stmt.substring(orReplacePos+11); 516 517 String match = 519 "(\\([^\\)]*\\))" + "|(\\bRETURN \\w+ (AS)|(IS))" + "|(;)" + "|(\\bBEGIN\\b)" + "|(\\bTHEN\\b)" 525 + "|(\\bELSE\\b)" 526 + "|(\\bELSIF\\b)"; 527 Matcher m = Pattern.compile(match, Pattern.CASE_INSENSITIVE).matcher(stmt); 528 529 StringBuffer sb = new StringBuffer (); 530 m.find(); 534 m.appendReplacement(sb, ""); 535 String name = sb.substring(6).trim(); 536 StringBuffer signature = new StringBuffer (); 537 String group = m.group().trim(); 539 StringBuffer alias = new StringBuffer (); 541 if (group.startsWith("(") && group.endsWith(")")) 543 { 544 if (group.toUpperCase().indexOf(" DEFAULT ") != -1) 546 { 547 String error = "Convert.convertFunction - DEFAULT in Parameter not supported"; 548 System.out.println (error); 549 m_conversionError = error; 550 return result; 551 } 552 signature.append("("); 553 if (group.length() > 2) 554 { 555 group = group.substring(1,group.length()-1); 556 String [] parameters = group.split(","); 558 for (int i = 0; i < parameters.length; i++) 559 { 560 if (i != 0) 561 signature.append(", "); 562 String p = parameters[i].trim(); 564 alias.append(p.substring(0,p.indexOf(" "))) 565 .append(" ALIAS FOR $").append(i+1).append(";\n"); 566 signature.append(p.substring(p.lastIndexOf(" ")+1)); 568 } 569 } 570 signature.append(")"); 571 sb.append(signature); 572 } 575 else 577 { 578 String error = "ConvertFunction - Missing Parameter ()"; 579 System.out.println (error); 580 m_conversionError = error; 581 return result; 582 } 583 sb.append("\n"); 584 if (orReplacePos != -1) 586 { 587 String drop = "DROP " + name + signature.toString(); 588 result.add(drop); 590 } 591 593 m.find(); 596 group = m.group(); 597 m.appendReplacement(sb, ""); 598 if (group.startsWith("RETURN")) 599 sb.append("RETURNS").append(group.substring(group.indexOf(" "))); 600 sb.append(" '\nDECLARE\n") 601 .append(alias); 604 while (m.find()) 606 { 607 String group2 = m.group(); 608 if (group2.indexOf('$') != -1) group2 = Util.replace(group2, "$", "\\$"); 610 m.appendReplacement(sb, group2); 611 sb.append("\n"); 612 } 613 m.appendTail(sb); 614 615 sb.append("' LANGUAGE 'plpgsql';"); 617 result.add(sb.toString()); 619 return result; 621 } 623 644 private ArrayList convertProcedure (String sqlStatement) 645 { 646 ArrayList result = new ArrayList(); 647 String stmt = converSimpleStatement(sqlStatement); 649 stmt = Pattern.compile("'").matcher(stmt).replaceAll("''"); 651 int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE "); 653 if (orReplacePos != -1) 654 stmt = "CREATE" + stmt.substring(orReplacePos+11); 655 656 String match = 658 "(\\([^\\)]*\\))" + "|(\\bRETURN \\w+ (AS)|(IS))" + "|(;)" + "|(\\bBEGIN\\b)" + "|(\\bTHEN\\b)" 664 + "|(\\bELSE\\b)" 665 + "|(\\bELSIF\\b)"; 666 Matcher m = Pattern.compile(match, Pattern.CASE_INSENSITIVE).matcher(stmt); 667 668 StringBuffer sb = new StringBuffer (); 669 m.find(); 673 m.appendReplacement(sb, ""); 674 String name = sb.substring(6).trim(); 675 StringBuffer signature = new StringBuffer (); 676 String group = m.group().trim(); 678 StringBuffer alias = new StringBuffer (); 680 if (group.startsWith("(") && group.endsWith(")")) 682 { 683 if (group.toUpperCase().indexOf(" DEFAULT ") != -1) 685 { 686 String error = "Convert.convertProcedure - DEFAULT in Parameter not supported"; 687 System.out.println (error); 688 m_conversionError = error; 689 return result; 690 } 691 signature.append("("); 692 if (group.length() > 2) 693 { 694 group = group.substring(1,group.length()-1); 695 String [] parameters = group.split(","); 697 for (int i = 0; i < parameters.length; i++) 698 { 699 if (i != 0) 700 signature.append(", "); 701 String p = parameters[i].trim(); 703 alias.append(p.substring(0,p.indexOf(" "))) 704 .append(" ALIAS FOR $").append(i+1).append(";\n"); 705 signature.append(p.substring(p.lastIndexOf(" ")+1)); 707 } 708 } 709 signature.append(")"); 710 sb.append(signature); 711 } 714 else 716 { 717 String error = "Convert.converProcedure - Missing Parameter ()"; 718 System.out.println (error); 719 m_conversionError = error; 720 return result; 721 } 722 sb.append("\n"); 723 if (orReplacePos != -1) 725 { 726 String drop = "DROP " + name + signature.toString(); 727 result.add(drop); 729 } 730 732 m.find(); 735 group = m.group(); 736 m.appendReplacement(sb, ""); 737 if (group.startsWith("RETURN")) 738 sb.append("RETURNS").append(group.substring(group.indexOf(" "))); 739 sb.append(" '\nDECLARE\n") 740 .append(alias); 743 while (m.find()) 745 { 746 String group2 = m.group(); 747 if (group2.indexOf('$') != -1) group2 = Util.replace(group2, "$", "\\$"); 749 m.appendReplacement(sb, group2); 750 sb.append("\n"); 751 } 752 m.appendTail(sb); 753 754 sb.append("' LANGUAGE 'plpgsql';"); 756 result.add(sb.toString()); 758 return result; 760 } 762 776 private ArrayList convertTrigger (String sqlStatement) 777 { 778 ArrayList result = new ArrayList(); 779 String stmt = converSimpleStatement(sqlStatement); 781 782 stmt = Pattern.compile("\\bINSERTING\\b").matcher(stmt).replaceAll("TG_OP='INSERT'"); 784 stmt = Pattern.compile("\\bUPDATING\\b").matcher(stmt).replaceAll("TG_OP='UPDATE'"); 785 stmt = Pattern.compile("\\bDELETING\\b").matcher(stmt).replaceAll("TG_OP='DELETE'"); 786 stmt = Pattern.compile(":new.").matcher(stmt).replaceAll("NEW."); 787 stmt = Pattern.compile(":old.").matcher(stmt).replaceAll("OLD."); 788 789 stmt = Pattern.compile("'").matcher(stmt).replaceAll("''"); 791 int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE "); 793 int triggerPos = stmt.toUpperCase().indexOf(" TRIGGER ") + 9; 795 String triggerName = stmt.substring(triggerPos); 796 triggerName = triggerName.substring(0, triggerName.indexOf(" ")); 797 String tableName = stmt.substring(stmt.toUpperCase().indexOf(" ON ")+4); 799 tableName = tableName.substring(0, tableName.indexOf(" ")); 800 801 if (orReplacePos != -1) 803 { 804 String drop = "DROP FUNCTION " + triggerName + "F()"; 805 result.add(drop); 807 } 808 809 int pos = stmt.indexOf("DECLARE "); 811 if (pos == -1) 812 pos = stmt.indexOf("BEGIN "); 813 String functionCode = stmt.substring(pos); 814 StringBuffer triggerCode = new StringBuffer ("CREATE TRIGGER "); 815 triggerCode.append(triggerName).append("\n") 816 .append(stmt.substring(triggerPos+triggerName.length(), pos)) 817 .append("\nEXECUTE PROCEDURE ").append(triggerName).append("F();"); 818 819 functionCode = Pattern.compile("\\bRETURN;", Pattern.CASE_INSENSITIVE) 821 .matcher(functionCode) 822 .replaceAll("RETURN NEW;"); 823 functionCode = Pattern.compile("\\bEND " + triggerName + ";", Pattern.CASE_INSENSITIVE) 825 .matcher(functionCode) 826 .replaceAll("\nRETURN NEW;\nEND " + triggerName + "F;"); 827 828 String match = 830 "(\\(.*\\))" + "|(;)" + "|(\\bBEGIN\\b)" + "|(\\bTHEN\\b)" 835 + "|(\\bELSE\\b)" 836 + "|(\\bELSIF\\b)"; 837 Matcher m = Pattern.compile(match, Pattern.CASE_INSENSITIVE).matcher(functionCode); 838 839 StringBuffer sb = new StringBuffer ("CREATE FUNCTION "); 841 sb.append(triggerName).append("F() RETURNS OPAQUE AS '\n"); 842 843 while (m.find()) 845 { 846 String group = m.group(); 847 if (group.indexOf('$') != -1) group = Util.replace(group, "$", "\\$"); 849 m.appendReplacement(sb, group); 850 sb.append("\n"); 851 } 852 m.appendTail(sb); 853 854 sb.append("' LANGUAGE 'plpgsql';"); 856 result.add(sb.toString()); 858 859 if (orReplacePos != -1) 861 { 862 String drop = "DROP TRIGGER " + triggerName.toLowerCase() + " ON " + tableName; 863 result.add(drop); 865 } 866 867 String trigger = Pattern.compile("\\sOF.*ON\\s") 870 .matcher(triggerCode) 871 .replaceAll(" ON "); 872 result.add(trigger); 874 875 return result; 877 } 879 885 private ArrayList convertView (String sqlStatement) 886 { 887 ArrayList result = new ArrayList(); 888 String stmt = converSimpleStatement(sqlStatement); 889 890 int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE "); 892 if (orReplacePos != -1) 893 { 894 int index = stmt.indexOf(" VIEW "); 895 int space = stmt.indexOf(' ', index+6); 896 String drop = "DROP VIEW " + stmt.substring(index+6, space); 897 result.add(drop); 898 String create = "CREATE" + stmt.substring(index); 900 result.add(create); 901 } 902 else result.add(stmt); 904 return result; 905 } 907 908 909 925 private String convertComplexStatement(String sqlStatement) 926 { 927 String retValue = sqlStatement; 928 StringBuffer sb = null; 929 930 while (retValue.indexOf("DECODE") != -1) 932 retValue = convertDecode(retValue); 933 934 939 Matcher m = Pattern.compile("\\w+\\.(nextval)|(curval)", Pattern.CASE_INSENSITIVE) 940 .matcher(retValue); 941 sb = new StringBuffer (); 942 while (m.find()) 943 { 944 String group = m.group(); 945 int pos = group.indexOf("."); 947 String seqName = group.substring(0,pos); 948 String funcName = group.substring(pos+1); 949 group = funcName + "('" + seqName + "')"; 950 if (group.indexOf('$') != -1) group = Util.replace(group, "$", "\\$"); 953 m.appendReplacement(sb, group); 954 } 955 m.appendTail(sb); 956 retValue = sb.toString(); 957 958 963 m = Pattern.compile("RAISE_APPLICATION_ERROR\\s*\\(.+'\\)", Pattern.CASE_INSENSITIVE) 964 .matcher(retValue); 965 sb = new StringBuffer (); 966 while (m.find()) 967 { 968 String group = m.group(); 969 System.out.print("-> " + group); 970 String result = "RAISE EXCEPTION " + group.substring(group.indexOf('\''), group.lastIndexOf('\'')+1); 971 System.out.println(" => " + result); 972 973 if (result.indexOf('$') != -1) result = Util.replace(result, "$", "\\$"); 975 m.appendReplacement(sb, result); 976 } 977 m.appendTail(sb); 978 retValue = sb.toString(); 979 980 while (retValue.indexOf("TRUNC") != -1) 982 retValue = convertTrunc (retValue); 983 984 int index = retValue.indexOf("SELECT "); 986 if (index != -1 && retValue.indexOf("(+)", index) != -1) 987 retValue = convertOuterJoin(retValue); 988 989 return retValue; 990 } 992 993 994 1003 private String convertDecode(String sqlStatement) 1004 { 1005 String statement = sqlStatement; 1007 StringBuffer sb = new StringBuffer ("CASE"); 1008 1009 int index = statement.indexOf("DECODE"); 1010 String firstPart = statement.substring(0,index); 1011 1012 index = statement.indexOf('(', index); 1014 statement = statement.substring(index+1); 1015 1016 index = Util.findIndexOf (statement, ','); 1018 String expression = statement.substring(0, index).trim(); 1019 1021 statement = statement.substring(index+1); 1023 index = Util.findIndexOf (statement, ','); 1024 while (index != -1) 1025 { 1026 String first = statement.substring(0, index); 1027 char cc = statement.charAt(index); 1028 statement = statement.substring(index+1); 1029 boolean error = false; 1032 if (cc == ',') 1033 { 1034 index = Util.findIndexOf (statement, ',',')'); 1035 if (index == -1) 1036 error = true; 1037 else 1038 { 1039 String second = statement.substring(0, index); 1040 sb.append(" WHEN ").append(expression).append("=").append(first.trim()) 1041 .append(" THEN ").append(second.trim()); 1042 statement = statement.substring(index+1); 1044 index = Util.findIndexOf (statement, ',',')'); 1045 } 1046 } 1047 else if (cc == ')') 1048 { 1049 sb.append(" ELSE ").append(first.trim()).append(" END"); 1050 index = -1; 1052 } 1053 else 1054 error = true; 1055 if (error) 1056 { 1057 System.err.println("Convert.convertDecode Error - SQL=(" + sqlStatement 1058 + ")\n====Result=(" + sb.toString() 1059 + ")\n====Statement=(" + statement 1060 + ")\n====First=(" + first 1061 + ")\n====Index=" + index); 1062 m_conversionError = "Decode conversion error"; 1063 } 1064 } 1065 sb.append(statement); 1066 sb.insert(0, firstPart); 1067 return sb.toString(); 1069 } 1071 1072 1073 1093 private String convertOuterJoin (String sqlStatement) 1094 { 1095 boolean trace = false; 1096 int fromIndex = Util.findIndexOf (sqlStatement.toUpperCase(), " FROM "); 1098 int whereIndex = Util.findIndexOf(sqlStatement.toUpperCase(), " WHERE "); 1099 int endWhereIndex = Util.findIndexOf(sqlStatement.toUpperCase(), " GRPUP BY "); 1100 if (endWhereIndex == -1) 1101 endWhereIndex = Util.findIndexOf(sqlStatement.toUpperCase(), " ORDER BY "); 1102 if (endWhereIndex == -1) 1103 endWhereIndex = sqlStatement.length(); 1104 if (trace) 1106 { 1107 System.out.println(); 1108 System.out.println("OuterJoin<== " + sqlStatement); 1109 } 1111 String selectPart = sqlStatement.substring(0, fromIndex); 1113 String fromPart = sqlStatement.substring(fromIndex, whereIndex); 1114 String wherePart = sqlStatement.substring(whereIndex, endWhereIndex); 1115 String rest = sqlStatement.substring(endWhereIndex); 1116 1117 String newWherePart = wherePart; 1119 ArrayList joins = new ArrayList(); 1120 int pos = newWherePart.indexOf("(+)"); 1121 while (pos != -1) 1122 { 1123 int start = newWherePart.lastIndexOf(" AND ", pos); 1125 int startOffset = 5; 1126 if (start == -1) 1127 { 1128 start = newWherePart.lastIndexOf(" OR ", pos); 1129 startOffset = 4; 1130 } 1131 if (start == -1) 1132 { 1133 start = newWherePart.lastIndexOf("WHERE ", pos); 1134 startOffset = 6; 1135 } 1136 if (start == -1) 1137 { 1138 String error = "Convert.convertOuterJoin - start point not found in clause " + wherePart; 1139 System.err.println (error); 1140 m_conversionError = error; 1141 return sqlStatement; 1142 } 1143 int end = newWherePart.indexOf(" AND ", pos); 1145 if (end == -1) 1146 end = newWherePart.indexOf(" OR ", pos); 1147 if (end == -1) 1148 end = newWherePart.length(); 1149 1151 String condition = newWherePart.substring(start+startOffset, end); 1153 joins.add(condition); 1154 if (trace) 1155 System.out.println("->" + condition); 1156 newWherePart = newWherePart.substring(0, start) + newWherePart.substring(end); 1158 pos = newWherePart.indexOf("(+)"); 1161 } 1162 newWherePart = newWherePart.trim(); 1164 if (newWherePart.startsWith("AND ")) 1165 newWherePart = "WHERE" + newWherePart.substring(3); 1166 else if (newWherePart.startsWith("OR ")) 1167 newWherePart = "WHERE" + newWherePart.substring(2); 1168 if (trace) 1169 System.out.println("=> " + newWherePart); 1170 1171 String [] fromParts = fromPart.trim().substring(4).split(","); 1174 HashMap fromAlias = new HashMap(); HashMap fromLookup = new HashMap(); for (int i = 0; i < fromParts.length; i++) 1177 { 1178 String entry = fromParts[i].trim(); 1179 String alias = entry; String table = entry; 1181 int aPos = entry.lastIndexOf(' '); 1182 if (aPos != -1) 1183 { 1184 alias = entry.substring(aPos+1); 1185 table = entry.substring(0, entry.indexOf(' ')); } 1187 fromAlias.put(alias, table); 1188 fromLookup.put(alias, table); 1189 if (trace) 1190 System.out.println("Alias=" + alias + ", Table=" + table); 1191 } 1192 1193 1228 StringBuffer newFrom = new StringBuffer (); 1229 for (int i = 0; i < joins.size(); i++) 1230 { 1231 Join first = new Join ((String )joins.get(i)); 1232 first.setMainTable((String )fromLookup.get(first.getMainAlias())); 1233 fromAlias.remove(first.getMainAlias()); first.setJoinTable((String )fromLookup.get(first.getJoinAlias())); 1235 fromAlias.remove(first.getJoinAlias()); if (trace) 1237 System.out.println("-First: " + first); 1238 if (newFrom.length() == 0) 1240 newFrom.append(" FROM "); 1241 else 1242 newFrom.append(", "); 1243 newFrom.append(first.getMainTable()).append(" ").append(first.getMainAlias()) 1244 .append(first.isLeft() ? " LEFT" : " RIGHT").append(" OUTER JOIN ") 1245 .append(first.getJoinTable()).append(" ").append(first.getJoinAlias()) 1246 .append(" ON (").append(first.getCondition()); 1247 for (int j = i+1; j < joins.size(); j++) 1249 { 1250 Join second = new Join ((String )joins.get(j)); 1251 second.setMainTable((String )fromLookup.get(second.getMainAlias())); 1252 second.setJoinTable((String )fromLookup.get(second.getJoinAlias())); 1253 if ((first.getMainTable().equals(second.getMainTable()) 1254 && first.getJoinTable().equals(second.getJoinTable())) 1255 || second.isConditionOf(first) ) 1256 { 1257 if (trace) 1258 System.out.println("-Second/key: " + second); 1259 newFrom.append(" AND ").append(second.getCondition()); 1260 joins.remove(j); fromAlias.remove(first.getJoinAlias()); for (int k = i+1; k < joins.size(); k++) 1264 { 1265 Join third = new Join ((String )joins.get(k)); 1266 third.setMainTable((String )fromLookup.get(third.getMainAlias())); 1267 third.setJoinTable((String )fromLookup.get(third.getJoinAlias())); 1268 if (third.isConditionOf(second)) 1269 { 1270 if (trace) 1271 System.out.println("-Third/key: " + third); 1272 newFrom.append(" AND ").append(third.getCondition()); 1273 joins.remove(k); fromAlias.remove(third.getJoinAlias()); } 1276 else if (trace) 1277 System.out.println("-Third/key-skip: " + third); 1278 } 1279 } 1280 else if (trace) 1281 System.out.println("-Second/key-skip: " + second); 1282 } 1283 newFrom.append(")"); for (int j = i+1; j < joins.size(); j++) 1286 { 1287 Join second = new Join ((String )joins.get(j)); 1288 second.setMainTable((String )fromLookup.get(second.getMainAlias())); 1289 second.setJoinTable((String )fromLookup.get(second.getJoinAlias())); 1290 if (first.getMainTable().equals(second.getMainTable())) 1291 { 1292 if (trace) 1293 System.out.println("-Second/dep: " + second); 1294 newFrom.insert(6, '('); newFrom.append(')'); newFrom.append(second.isLeft() ? " LEFT" : " RIGHT").append(" OUTER JOIN ") 1300 .append(second.getJoinTable()).append(" ").append(second.getJoinAlias()) 1301 .append(" ON (").append(second.getCondition()); 1302 joins.remove(j); fromAlias.remove(second.getJoinAlias()); newFrom.append(")"); for (int k = i+1; k < joins.size(); k++) 1308 { 1309 Join third = new Join ((String )joins.get(k)); 1310 third.setMainTable((String )fromLookup.get(third.getMainAlias())); 1311 third.setJoinTable((String )fromLookup.get(third.getJoinAlias())); 1312 if (second.getJoinTable().equals(third.getMainTable())) 1313 { 1314 if (trace) 1315 System.out.println("-Third-dep: " + third); 1316 newFrom.insert(6, '('); newFrom.append(')'); newFrom.append(third.isLeft() ? " LEFT" : " RIGHT").append(" OUTER JOIN ") 1323 .append(third.getJoinTable()).append(" ").append(third.getJoinAlias()) 1324 .append(" ON (").append(third.getCondition()); 1325 joins.remove(k); fromAlias.remove(third.getJoinAlias()); newFrom.append(")"); } 1330 else if (trace) 1331 System.out.println("-Third-skip: " + third); 1332 } 1333 } 1334 else if (trace) 1335 System.out.println("-Second/dep-skip: " + second); 1336 } } 1338 Iterator it = fromAlias.keySet().iterator(); 1340 while (it.hasNext()) 1341 { 1342 Object alias = it.next(); 1343 Object table = fromAlias.get(alias); 1344 newFrom.append(", ").append(table); 1345 if (!table.equals(alias)) 1346 newFrom.append(" ").append(alias); 1347 } 1348 if (trace) 1349 System.out.println(newFrom.toString()); 1350 StringBuffer retValue = new StringBuffer (sqlStatement.length()+20); 1352 retValue.append(selectPart) 1353 .append(newFrom).append(" ") 1354 .append(newWherePart).append(rest); 1355 if (trace) 1357 System.out.println("OuterJoin==> " + retValue.toString()); 1358 return retValue.toString(); 1359 } 1361 1372 private String convertRowNum (String sqlStatement) 1373 { 1374 System.out.println("RowNum<== " + sqlStatement); 1375 String retValue = sqlStatement; 1376 return retValue; 1379 } 1381 1401 private String convertTrunc (String sqlStatement) 1402 { 1403 int index = sqlStatement.indexOf("TRUNC"); 1404 String beforeStatement = sqlStatement.substring(0, index); 1405 String afterStatement = sqlStatement.substring(index); 1406 afterStatement = afterStatement.substring(afterStatement.indexOf('(')+1); 1407 index = Util.findIndexOf(afterStatement, ')'); 1408 String temp = afterStatement.substring(0, index).trim(); 1409 afterStatement = afterStatement.substring(index+1); 1410 StringBuffer retValue = new StringBuffer ("DATE_Trunc("); if (temp.indexOf(',') == -1) 1413 retValue.append("'day',").append(temp); 1414 else { 1416 int pos = temp.indexOf(','); 1417 String variable = temp.substring(0, pos).trim(); 1418 String format = temp.substring(pos+1).trim(); 1419 if (format.equals("'Q'")) 1420 retValue.append("'quarter',").append(variable); 1421 else if (format.equals("'MM'")) 1422 retValue.append("'month',").append(variable); 1423 else if (format.equals("'DD'")) 1424 retValue.append("'day',").append(variable); 1425 else if (format.equals("'DY'") || format.equals("'DAY'")) 1426 retValue.append("'day',(").append(variable) 1427 .append("-DATE_PART('dow',").append(variable).append("))"); 1428 else 1429 { 1430 System.err.println("TRUNC format not supported: " + format); 1431 retValue.append("'day',").append(variable); 1432 } 1433 } 1434 retValue.append(')'); 1435 retValue.insert(0, beforeStatement); 1438 retValue.append(afterStatement); 1439 return retValue.toString(); 1440 } 1442} | Popular Tags |