KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > compiere > dbPort > Convert


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

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 /**
24  * Convert SQL to Target DB
25  *
26  * @author Jorg Janke, Victor Perez
27  * @version $Id: Convert.java,v 1.18 2003/06/14 04:30:35 jjanke Exp $
28  */

29 public class Convert
30 {
31     /**
32      * Cosntructor
33      * @param type
34      * @param conn optional - required for execute
35      */

36     public Convert (String JavaDoc 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 JavaDoc ("Unsupported database");
44         //
45
setConnection(conn);
46     } // Convert
47

48     /** Version */
49     public static final String JavaDoc VERSION = "$Id: Convert.java,v 1.18 2003/06/14 04:30:35 jjanke Exp $";
50     /** RegEx: insensitive and dot to include line end characters */
51     public static final int REGEX_FLAGS = Pattern.CASE_INSENSITIVE | Pattern.DOTALL;
52
53     /** Is Oracle */
54     private boolean m_isOracle = false;
55     /** Used Resorce Bundle */
56     private TreeMap m_map;
57
58     /** Used Connection */
59     private Connection m_conn = null;
60     /** Statement used */
61     private Statement m_stmt = null;
62
63     /** Last Conversion Error */
64     private String JavaDoc m_conversionError = null;
65     /** Last Execution Error */
66     private Exception JavaDoc m_exception = null;
67     /** Verbose Messages */
68     private boolean m_verbose = true;
69
70     /**
71      * Dispose - close resources
72      */

73     public void dispose()
74     {
75         try
76         {
77             if (m_stmt != null)
78                 m_stmt.close();
79             // connection not closed.
80
}
81         catch (SQLException e)
82         {
83             System.err.println("Convert.setConnection " + e);
84         }
85     } // dispose
86

87     /**
88      * Set Connection (set to AutoCommit)
89      * @param conn
90      */

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     } // setConnection
104

105     /**
106      * Get Connection
107      * @return connection
108      */

109     public Connection getConnection()
110     {
111         return m_conn;
112     } // getConnection
113

114     /**
115      * Set Verbose
116      * @param verbose
117      */

118     public void setVerbose (boolean verbose)
119     {
120         m_verbose = verbose;
121     } // setVerbose
122

123     /**
124      * Is Oracle DB
125      * @return true if connection is Oracle DB
126      */

127     public boolean isOracle()
128     {
129         return m_isOracle;
130     } // isOracle
131

132     /*************************************************************************/
133
134     /**
135      * Execute SQL Statement (stops at first error).
136      * If an error occured hadError() returns true.
137      * You can get details via getConversionError() or getException()
138      * @param sqlStatements
139      * @returns true if success
140      * @throws IllegalStateException if no connection
141      */

142     public boolean execute (String JavaDoc sqlStatements)
143     {
144         if (m_conn == null)
145             throw new IllegalStateException JavaDoc ("Convert.execute requires connection");
146         //
147
String JavaDoc[] 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 JavaDoc statement = null;
155         try
156         {
157             if (m_stmt == null)
158                 m_stmt = m_conn.createStatement();
159             //
160
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                         // Ignore Drop Errors
195
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             } // for all statements
206
}
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     } // execute
217

218     /**
219      * Return last execution exception
220      * @return execution exception
221      */

222     public Exception JavaDoc getException()
223     {
224         return m_exception;
225     } // getException
226

227     /**
228      * Returns true if a conversion or execution error had occured.
229      * Get more details via getConversionError() or getException()
230      * @return true if error had occured
231      */

232     public boolean hasError()
233     {
234         return (m_exception != null) | (m_conversionError != null);
235     } // hasError
236

237     /**
238      * Convert SQL Statement (stops at first error).
239      * Statements are delimited by /
240      * If an error occured hadError() returns true.
241      * You can get details via getConversionError()
242      * @param sqlStatements
243      * @return converted statement as a string
244      */

245     public String JavaDoc convertAll (String JavaDoc sqlStatements)
246     {
247         String JavaDoc[] sql = convert (sqlStatements);
248         StringBuffer JavaDoc sb = new StringBuffer JavaDoc (sqlStatements.length() + 10);
249         for (int i = 0; i < sql.length; i++)
250         {
251             // line.separator
252
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     } // convertAll
258

259     /**
260      * Convert SQL Statement (stops at first error).
261      * If an error occured hadError() returns true.
262      * You can get details via getConversionError()
263      * @param sqlStatements
264      * @return Array of converted Statements
265      */

266     public String JavaDoc[] convert (String JavaDoc 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         //
275
return convertIt (sqlStatements);
276     } // convert
277

278     /**
279      * Return last conversion error or null.
280      * @return lst conversion error
281      */

282     public String JavaDoc getConversionError()
283     {
284         return m_conversionError;
285     } // getConversionError
286

287     /*************************************************************************/
288
289     /**
290      * Conversion routine (stops at first error).
291      * <pre>
292      * - mask / in Strings
293      * - break into single statement
294      * - unmask statements
295      * - for each statement: convertStatement
296      * - remove comments
297      * - process FUNCTION/TRIGGER/PROCEDURE
298      * - process Statement: convertSimpleStatement
299      * - based on ConvertMap
300      * - convertComplexStatement
301      * - decode, sequence, exception
302      * </pre>
303      * @param sqlStatements
304      * @return array of converted statements
305      */

306     private String JavaDoc[] convertIt (String JavaDoc sqlStatements)
307     {
308         // Need to mask / in SQL Strings !
309
final char MASK = '\u001F'; // Unit Separator
310
StringBuffer JavaDoc masked = new StringBuffer JavaDoc(sqlStatements.length());
311         Matcher m = Pattern.compile("'[^']+'", Pattern.DOTALL).matcher(sqlStatements);
312         while (m.find())
313         {
314             String JavaDoc group = m.group(); // SQL string
315
if (group.indexOf("/") != -1) // / in string
316
group = group.replace('/', MASK);
317             if (group.indexOf('$') != -1) // Group character needs to be escaped
318
group = Util.replace(group, "$", "\\$");
319             m.appendReplacement(masked, group);
320         }
321         m.appendTail(masked);
322         String JavaDoc tempResult = masked.toString();
323         /** @todo Need to mask / in comments */
324
325         // Statements ending with /
326
String JavaDoc[] sql = tempResult.split("\\s/\\s"); // ("(;\\s)|(\\s/\\s)");
327
ArrayList result = new ArrayList (sql.length);
328         // process statements
329
for (int i = 0; i < sql.length; i++)
330         {
331             String JavaDoc statement = sql[i];
332             if (statement.indexOf(MASK) != -1)
333                 statement = statement.replace(MASK, '/');
334             result.addAll(convertStatement(statement)); // may return more than one target statement
335
}
336         // convert to array
337
sql = new String JavaDoc[result.size()];
338         result.toArray(sql);
339         return sql;
340     } // convertIt
341

342     /**
343      * Convert single Statements.
344      * - remove comments
345      * - process FUNCTION/TRIGGER/PROCEDURE
346      * - process Statement
347      * @param sqlStatement
348      * @return converted statement
349      */

350     private ArrayList convertStatement (String JavaDoc sqlStatement)
351     {
352         ArrayList result = new ArrayList();
353         if (m_isOracle)
354         {
355             result.add(sqlStatement);
356             return result;
357         }
358
359         // remove comments
360
String JavaDoc statement = removeComments (sqlStatement);
361     // System.out.println("------------------------------------------------------------");
362
// System.out.println(statement);
363
// System.out.println("------------------->");
364

365         String JavaDoc cmpString = statement.toUpperCase();
366         boolean isCreate = cmpString.startsWith("CREATE ");
367
368         // Process
369
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         // Simple Statement
382
else
383             result.add(converSimpleStatement(statement));
384         //
385
// System.out.println("<-------------------");
386
// for (int i = 0; i < result.size(); i++)
387
// System.out.println(result.get(i));
388
// System.out.println("------------------------------------------------------------");
389

390         return result;
391     } // convertStatement
392

393     /**
394      * Convert simple SQL Statement.
395      * Based on ConvertMap
396      *
397      * @param sqlStatement
398      * @return converted Statement
399      */

400     private String JavaDoc converSimpleStatement (String JavaDoc sqlStatement)
401     {
402         // Error Checks
403
if (sqlStatement.toUpperCase().indexOf("EXCEPTION WHEN") != -1)
404         {
405             String JavaDoc 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         // Standard Statement
412
String JavaDoc retValue = sqlStatement;
413         Iterator iter = m_map.keySet().iterator();
414         while (iter.hasNext())
415         {
416             String JavaDoc regex = (String JavaDoc)iter.next();
417             try
418             {
419                 Pattern p = Pattern.compile(regex, REGEX_FLAGS);
420                 Matcher m = p.matcher(retValue);
421                 retValue = m.replaceAll((String JavaDoc)m_map.get(regex));
422             }
423             catch (Exception JavaDoc e)
424             {
425                 String JavaDoc error = "Convert.convertSimpleStatement - Error expression: " + regex + " - " + e;
426                 System.out.println(error);
427                 m_conversionError = error;
428             }
429         }
430
431         // Convert Decode, Sequence, Join, ..
432
return convertComplexStatement(retValue);
433     } // convertSimpleStatement
434

435     /**
436      * Clean up Statement.
437      * Remove all comments and while spaces
438      * Database specific functionality can me tagged as follows:
439      * <pre>
440      * &#047;*ORACLE&gt;*&#047;
441      * Oracle Specific Statement
442      * &#047;*&lt;ORACLE*&#047;
443      * &#047;*POSTGRESQL&gt;
444      * PostgreSQL Specicic Statements
445      * &lt;POSTGRESQL*&#047;
446      * </pre>
447      * @param statement
448      * @return sql statement
449      */

450     protected String JavaDoc removeComments (String JavaDoc statement)
451     {
452         String JavaDoc clean = statement.trim();
453
454         // Remove /*ORACLE>*/ /*<ORACLE*/
455
Matcher m = Pattern.compile("\\/\\*ORACLE>.*<ORACLE\\*\\/", Pattern.DOTALL).matcher(clean);
456         clean = m.replaceAll("");
457
458         // Remove /.POSTGRESQL>
459
m = Pattern.compile("\\/\\*POSTGRESQL>").matcher(clean);
460         clean = m.replaceAll("");
461         // Remove <POSTGRESQL./
462
m = Pattern.compile("<POSTGRESQL\\*\\/").matcher(clean);
463         clean = m.replaceAll("");
464
465         // Remove /* */
466
m = Pattern.compile("\\/\\*.*\\*\\/", Pattern.DOTALL).matcher(clean);
467         clean = m.replaceAll("");
468
469         // Remove --
470
m = Pattern.compile("--.*$").matcher(clean); // up to EOL
471
clean = m.replaceAll("");
472         m = Pattern.compile("--.*[\\n\\r]").matcher(clean); // -- at BOL
473
clean = m.replaceAll("");
474
475         // Convert cr/lf/tab to single space
476
m = Pattern.compile("\\s+").matcher(clean);
477         clean = m.replaceAll(" ");
478
479         clean = clean.trim();
480         return clean;
481     } // removeComments
482

483     /**
484      * Convert Function.
485      * <pre>
486      * CREATE OR REPLACE FUNCTION AD_Message_Get
487      * (p_AD_Message IN VARCHAR, p_AD_Language IN VARCHAR)
488      * RETURN VARCHAR AS
489      * ...
490      * END AD_Message_Get;
491      * =>
492      * CREATE FUNCTION AD_Message_Get
493      * (VARCHAR, VARCHAR)
494      * RETURNS VARCHAR AS '
495      * DECLARE
496      * p_AD_Message ALIAS FOR $1;
497      * p_AD_Language ALIAS FOR $2;
498      * ....
499      * END;
500      * ' LANGUAGE 'plpgsql';
501      * </pre>
502      * @param sqlStatement
503      * @return CREATE and DROP Function statement
504      */

505     private ArrayList convertFunction (String JavaDoc sqlStatement)
506     {
507         ArrayList result = new ArrayList();
508         // Convert statement - to avoid handling contents of comments
509
String JavaDoc stmt = converSimpleStatement(sqlStatement);
510         // Double quotes '
511
stmt = Pattern.compile("'").matcher(stmt).replaceAll("''");
512         // remove OR REPLACE
513
int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE ");
514         if (orReplacePos != -1)
515             stmt = "CREATE" + stmt.substring(orReplacePos+11);
516
517         // Line separators
518
String JavaDoc match =
519               "(\\([^\\)]*\\))" // (.) Parameter
520
+ "|(\\bRETURN \\w+ (AS)|(IS))" // RETURN CLAUSE
521
+ "|(;)" // Statement End
522
// Nice to have - for readability
523
+ "|(\\bBEGIN\\b)" // BEGIN
524
+ "|(\\bTHEN\\b)"
525             + "|(\\bELSE\\b)"
526             + "|(\\bELSIF\\b)";
527         Matcher m = Pattern.compile(match, Pattern.CASE_INSENSITIVE).matcher(stmt);
528
529         StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
530         // First group -> ( )
531
// CREATE OR REPLACE FUNCTION AD_Message_Get ( p_AD_Message IN VARCHAR, p_AD_Language IN VARCHAR)
532
// CREATE FUNCTION AD_Message_Get (VARCHAR, VARCHAR)
533
m.find();
534         m.appendReplacement(sb, "");
535         String JavaDoc name = sb.substring(6).trim();
536         StringBuffer JavaDoc signature = new StringBuffer JavaDoc();
537         //
538
String JavaDoc group = m.group().trim();
539     // System.out.println("Group: " + group);
540
StringBuffer JavaDoc alias = new StringBuffer JavaDoc();
541         // Parameters
542
if (group.startsWith("(") && group.endsWith(")"))
543         {
544             // Default not supported
545
if (group.toUpperCase().indexOf(" DEFAULT ") != -1)
546             {
547                 String JavaDoc 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                 // Paraneters are delimited by ,
557
String JavaDoc[] parameters = group.split(",");
558                 for (int i = 0; i < parameters.length; i++)
559                 {
560                     if (i != 0)
561                         signature.append(", ");
562                     // name ALIAS FOR $1
563
String JavaDoc p = parameters[i].trim();
564                     alias.append(p.substring(0,p.indexOf(" ")))
565                         .append(" ALIAS FOR $").append(i+1).append(";\n");
566                     // Datatape
567
signature.append(p.substring(p.lastIndexOf(" ")+1));
568                 }
569             }
570             signature.append(")");
571             sb.append(signature);
572         // System.out.println("Alias: " + alias.toString());
573
// System.out.println("Signature: " + signature.toString());
574
}
575         // No Parameters
576
else
577         {
578             String JavaDoc error = "ConvertFunction - Missing Parameter ()";
579             System.out.println (error);
580             m_conversionError = error;
581             return result;
582         }
583         sb.append("\n");
584         // Need to create drop statement
585
if (orReplacePos != -1)
586         {
587             String JavaDoc drop = "DROP " + name + signature.toString();
588         // System.out.println(drop);
589
result.add(drop);
590         }
591     // System.out.println("1>" + sb.toString() + "<1");
592

593         // Second Group -> RETURN VARCHAR AS
594
// RETURNS VARCHAR AS
595
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); // add aliases here
602
// System.out.println("2>" + sb.toString() + "<2");
603

604         // remainder statements
605
while (m.find())
606         {
607             String JavaDoc group2 = m.group();
608             if (group2.indexOf('$') != -1) // Group character needs to be escaped
609
group2 = Util.replace(group2, "$", "\\$");
610             m.appendReplacement(sb, group2);
611             sb.append("\n");
612         }
613         m.appendTail(sb);
614
615         // finish
616
sb.append("' LANGUAGE 'plpgsql';");
617     // System.out.println(">" + sb.toString() + "<");
618
result.add(sb.toString());
619         //
620
return result;
621     } // convertFunction
622

623     /**
624      * Convert Procedure.
625      * <pre>
626      * CREATE OR REPLACE PROCEDURE AD_Message_X
627      * (p_AD_Message IN VARCHAR, p_AD_Language IN VARCHAR)
628      * ...
629      * END AD_Message_X;
630      * =>
631      * CREATE FUNCTION AD_Message_X
632      * (VARCHAR, VARCHAR)
633      * RETURNS VARCHAR AS '
634      * DECLARE
635      * p_AD_Message ALIAS FOR $1;
636      * p_AD_Language ALIAS FOR $2;
637      * ....
638      * END;
639      * ' LANGUAGE 'plpgsql';
640      * </pre>
641      * @param sqlStatement
642      * @return CREATE and DROP Function statement
643      */

644     private ArrayList convertProcedure (String JavaDoc sqlStatement)
645     {
646         ArrayList result = new ArrayList();
647         // Convert statement - to avoid handling contents of comments
648
String JavaDoc stmt = converSimpleStatement(sqlStatement);
649         // Double quotes '
650
stmt = Pattern.compile("'").matcher(stmt).replaceAll("''");
651         // remove OR REPLACE
652
int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE ");
653         if (orReplacePos != -1)
654             stmt = "CREATE" + stmt.substring(orReplacePos+11);
655
656         // Line separators
657
String JavaDoc match =
658               "(\\([^\\)]*\\))" // (.) Parameter
659
+ "|(\\bRETURN \\w+ (AS)|(IS))" // RETURN CLAUSE
660
+ "|(;)" // Statement End
661
// Nice to have - for readability
662
+ "|(\\bBEGIN\\b)" // BEGIN
663
+ "|(\\bTHEN\\b)"
664             + "|(\\bELSE\\b)"
665             + "|(\\bELSIF\\b)";
666         Matcher m = Pattern.compile(match, Pattern.CASE_INSENSITIVE).matcher(stmt);
667
668         StringBuffer JavaDoc sb = new StringBuffer JavaDoc();
669         // First group -> ( )
670
// CREATE OR REPLACE FUNCTION AD_Message_Get ( p_AD_Message IN VARCHAR, p_AD_Language IN VARCHAR)
671
// CREATE FUNCTION AD_Message_Get (VARCHAR, VARCHAR)
672
m.find();
673         m.appendReplacement(sb, "");
674         String JavaDoc name = sb.substring(6).trim();
675         StringBuffer JavaDoc signature = new StringBuffer JavaDoc();
676         //
677
String JavaDoc group = m.group().trim();
678     // System.out.println("Group: " + group);
679
StringBuffer JavaDoc alias = new StringBuffer JavaDoc();
680         // Parameters
681
if (group.startsWith("(") && group.endsWith(")"))
682         {
683             // Default not supported
684
if (group.toUpperCase().indexOf(" DEFAULT ") != -1)
685             {
686                 String JavaDoc 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                 // Paraneters are delimited by ,
696
String JavaDoc[] parameters = group.split(",");
697                 for (int i = 0; i < parameters.length; i++)
698                 {
699                     if (i != 0)
700                         signature.append(", ");
701                     // name ALIAS FOR $1
702
String JavaDoc p = parameters[i].trim();
703                     alias.append(p.substring(0,p.indexOf(" ")))
704                         .append(" ALIAS FOR $").append(i+1).append(";\n");
705                     // Datatape
706
signature.append(p.substring(p.lastIndexOf(" ")+1));
707                 }
708             }
709             signature.append(")");
710             sb.append(signature);
711         // System.out.println("Alias: " + alias.toString());
712
// System.out.println("Signature: " + signature.toString());
713
}
714         // No Parameters
715
else
716         {
717             String JavaDoc error = "Convert.converProcedure - Missing Parameter ()";
718             System.out.println (error);
719             m_conversionError = error;
720             return result;
721         }
722         sb.append("\n");
723         // Need to create drop statement
724
if (orReplacePos != -1)
725         {
726             String JavaDoc drop = "DROP " + name + signature.toString();
727         // System.out.println(drop);
728
result.add(drop);
729         }
730     // System.out.println("1>" + sb.toString() + "<1");
731

732         // Second Group -> RETURN VARCHAR AS
733
// RETURNS VARCHAR AS
734
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); // add aliases here
741
// System.out.println("2>" + sb.toString() + "<2");
742

743         // remainder statements
744
while (m.find())
745         {
746             String JavaDoc group2 = m.group();
747             if (group2.indexOf('$') != -1) // Group character needs to be escaped
748
group2 = Util.replace(group2, "$", "\\$");
749             m.appendReplacement(sb, group2);
750             sb.append("\n");
751         }
752         m.appendTail(sb);
753
754         // finish
755
sb.append("' LANGUAGE 'plpgsql';");
756     // System.out.println(">" + sb.toString() + "<");
757
result.add(sb.toString());
758         //
759
return result;
760     } // convertProcedure
761

762     /**
763      * Convert Trigger.
764      * <pre>
765      * DROP FUNCTION emp_trgF();
766      * CREATE FUNCTION emp_trg () RETURNS OPAQUE AS '....
767      * RETURN NEW; ...
768      * ' LANGUAGE 'plpgsql';
769      * DROP TRIGGER emp_trg ON emp;
770      * CREATE TRIGGER emp_trg BEFORE INSERT OR UPDATE ON emp
771      * FOR EACH ROW EXECUTE PROCEDURE emp_trgF();
772      * </pre>
773      * @param sqlStatement
774      * @return CREATE and DROP TRIGGER and associated Function statement
775      */

776     private ArrayList convertTrigger (String JavaDoc sqlStatement)
777     {
778         ArrayList result = new ArrayList();
779         // Convert statement - to avoid handling contents of comments
780
String JavaDoc stmt = converSimpleStatement(sqlStatement);
781
782         // Trigger specific replacements
783
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         // Double quotes '
790
stmt = Pattern.compile("'").matcher(stmt).replaceAll("''");
791         // remove OR REPLACE
792
int orReplacePos = stmt.toUpperCase().indexOf(" OR REPLACE ");
793         // trigger Name
794
int triggerPos = stmt.toUpperCase().indexOf(" TRIGGER ") + 9;
795         String JavaDoc triggerName = stmt.substring(triggerPos);
796         triggerName = triggerName.substring(0, triggerName.indexOf(" "));
797         // table name
798
String JavaDoc tableName = stmt.substring(stmt.toUpperCase().indexOf(" ON ")+4);
799         tableName = tableName.substring(0, tableName.indexOf(" "));
800
801         // Function Drop
802
if (orReplacePos != -1)
803         {
804             String JavaDoc drop = "DROP FUNCTION " + triggerName + "F()";
805         // System.out.println(drop);
806
result.add(drop);
807         }
808
809         // Function & Trigger
810
int pos = stmt.indexOf("DECLARE ");
811         if (pos == -1)
812             pos = stmt.indexOf("BEGIN ");
813         String JavaDoc functionCode = stmt.substring(pos);
814         StringBuffer JavaDoc triggerCode = new StringBuffer JavaDoc ("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         // Add NEW to existing Return --> DELETE Trigger ?
820
functionCode = Pattern.compile("\\bRETURN;", Pattern.CASE_INSENSITIVE)
821             .matcher(functionCode)
822             .replaceAll("RETURN NEW;");
823         // Add final return and change name
824
functionCode = Pattern.compile("\\bEND " + triggerName + ";", Pattern.CASE_INSENSITIVE)
825             .matcher(functionCode)
826             .replaceAll("\nRETURN NEW;\nEND " + triggerName + "F;");
827
828         // Line separators
829
String JavaDoc match =
830               "(\\(.*\\))" // (.) Parameter
831
+ "|(;)" // Statement End
832
// Nice to have - for readability
833
+ "|(\\bBEGIN\\b)" // BEGIN
834
+ "|(\\bTHEN\\b)"
835             + "|(\\bELSE\\b)"
836             + "|(\\bELSIF\\b)";
837         Matcher m = Pattern.compile(match, Pattern.CASE_INSENSITIVE).matcher(functionCode);
838
839         // Function Header
840
StringBuffer JavaDoc sb = new StringBuffer JavaDoc("CREATE FUNCTION ");
841         sb.append(triggerName).append("F() RETURNS OPAQUE AS '\n");
842
843         // remainder statements
844
while (m.find())
845         {
846             String JavaDoc group = m.group();
847             if (group.indexOf('$') != -1) // Group character needs to be escaped
848
group = Util.replace(group, "$", "\\$");
849             m.appendReplacement(sb, group);
850             sb.append("\n");
851         }
852         m.appendTail(sb);
853
854         // finish Function
855
sb.append("' LANGUAGE 'plpgsql';");
856     // System.out.println(">" + sb.toString() + "<");
857
result.add(sb.toString());
858
859         // Trigger Drop
860
if (orReplacePos != -1)
861         {
862             String JavaDoc drop = "DROP TRIGGER " + triggerName.toLowerCase() + " ON " + tableName;
863     // System.out.println(drop);
864
result.add(drop);
865         }
866
867         // Trigger
868
// Remove Column references OF ... ON
869
String JavaDoc trigger = Pattern.compile("\\sOF.*ON\\s")
870             .matcher(triggerCode)
871             .replaceAll(" ON ");
872     // System.out.println(trigger);
873
result.add(trigger);
874
875         //
876
return result;
877     } // convertTrigger
878

879     /**
880      * Convert View.
881      * Handle CREATE OR REPLACE
882      * @param sqlStatement
883      * @return converted statement(s)
884      */

885     private ArrayList convertView (String JavaDoc sqlStatement)
886     {
887         ArrayList result = new ArrayList();
888         String JavaDoc stmt = converSimpleStatement(sqlStatement);
889
890         // remove OR REPLACE
891
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 JavaDoc drop = "DROP VIEW " + stmt.substring(index+6, space);
897             result.add(drop);
898             //
899
String JavaDoc create = "CREATE" + stmt.substring(index);
900             result.add(create);
901         }
902         else // simple statement
903
result.add(stmt);
904         return result;
905     } // convertView
906

907     /*************************************************************************/
908
909     /**
910      * Converts Decode, Outer Join and Sequence.
911      * <pre>
912      * DECODE (a, 1, 'one', 2, 'two', 'none')
913      * => CASE WHEN a = 1 THEN 'one' WHEN a = 2 THEN 'two' ELSE 'none' END
914      *
915      * AD_Error_Seq.nextval
916      * => nextval('AD_Error_Seq')
917      *
918      * RAISE_APPLICATION_ERROR (-20100, 'Table Sequence not found')
919      * => RAISE EXCEPTION 'Table Sequence not found'
920      *
921      * </pre>
922      * @param sqlStatement
923      * @return converted statement
924      */

925     private String JavaDoc convertComplexStatement(String JavaDoc sqlStatement)
926     {
927         String JavaDoc retValue = sqlStatement;
928         StringBuffer JavaDoc sb = null;
929
930         // Convert all decode parts
931
while (retValue.indexOf("DECODE") != -1)
932             retValue = convertDecode(retValue);
933
934         /**
935          * Sequence Handling --------------------------------------------------
936          * AD_Error_Seq.nextval
937          * => nextval('AD_Error_Seq')
938          */

939         Matcher m = Pattern.compile("\\w+\\.(nextval)|(curval)", Pattern.CASE_INSENSITIVE)
940             .matcher(retValue);
941         sb = new StringBuffer JavaDoc();
942         while (m.find())
943         {
944             String JavaDoc group = m.group();
945         // System.out.print("-> " + group);
946
int pos = group.indexOf(".");
947             String JavaDoc seqName = group.substring(0,pos);
948             String JavaDoc funcName = group.substring(pos+1);
949             group = funcName + "('" + seqName + "')";
950         // System.out.println(" => " + group);
951
if (group.indexOf('$') != -1) // Group character needs to be escaped
952
group = Util.replace(group, "$", "\\$");
953             m.appendReplacement(sb, group);
954         }
955         m.appendTail(sb);
956         retValue = sb.toString();
957
958         /**
959          * RAISE --------------------------------------------------------------
960          * RAISE_APPLICATION_ERROR (-20100, 'Table Sequence not found')
961          * => RAISE EXCEPTION 'Table Sequence not found'
962          */

963         m = Pattern.compile("RAISE_APPLICATION_ERROR\\s*\\(.+'\\)", Pattern.CASE_INSENSITIVE)
964             .matcher(retValue);
965         sb = new StringBuffer JavaDoc();
966         while (m.find())
967         {
968             String JavaDoc group = m.group();
969             System.out.print("-> " + group);
970             String JavaDoc result = "RAISE EXCEPTION " + group.substring(group.indexOf('\''), group.lastIndexOf('\'')+1);
971             System.out.println(" => " + result);
972
973             if (result.indexOf('$') != -1) // Group character needs to be escaped
974
result = Util.replace(result, "$", "\\$");
975             m.appendReplacement(sb, result);
976         }
977         m.appendTail(sb);
978         retValue = sb.toString();
979
980         // Truncate Handling -------------------------------------------------
981
while (retValue.indexOf("TRUNC") != -1)
982             retValue = convertTrunc (retValue);
983
984         // Outer Join Handling -----------------------------------------------
985
int index = retValue.indexOf("SELECT ");
986         if (index != -1 && retValue.indexOf("(+)", index) != -1)
987             retValue = convertOuterJoin(retValue);
988
989         return retValue;
990     } // convertComplexStatement
991

992     /*************************************************************************/
993
994     /**
995      * Converts Decode.
996      * <pre>
997      * DECODE (a, 1, 'one', 2, 'two', 'none')
998      * => CASE WHEN a = 1 THEN 'one' WHEN a = 2 THEN 'two' ELSE 'none' END
999      * </pre>
1000     * @param sqlStatement
1001     * @return converted statement
1002     */

1003    private String JavaDoc convertDecode(String JavaDoc sqlStatement)
1004    {
1005    // System.out.println("DECODE<== " + sqlStatement);
1006
String JavaDoc statement = sqlStatement;
1007        StringBuffer JavaDoc sb = new StringBuffer JavaDoc("CASE");
1008
1009        int index = statement.indexOf("DECODE");
1010        String JavaDoc firstPart = statement.substring(0,index);
1011
1012        // find the opening (
1013
index = statement.indexOf('(', index);
1014        statement = statement.substring(index+1);
1015
1016        // find the expression "a" - find first , ignoring ()
1017
index = Util.findIndexOf (statement, ',');
1018        String JavaDoc expression = statement.substring(0, index).trim();
1019    // System.out.println("Expression=" + expression);
1020

1021        // Pairs "1, 'one',"
1022
statement = statement.substring(index+1);
1023        index = Util.findIndexOf (statement, ',');
1024        while (index != -1)
1025        {
1026            String JavaDoc first = statement.substring(0, index);
1027            char cc = statement.charAt(index);
1028            statement = statement.substring(index+1);
1029        // System.out.println("First=" + first + ", Char=" + cc);
1030
//
1031
boolean error = false;
1032            if (cc == ',')
1033            {
1034                index = Util.findIndexOf (statement, ',',')');
1035                if (index == -1)
1036                    error = true;
1037                else
1038                {
1039                    String JavaDoc second = statement.substring(0, index);
1040                    sb.append(" WHEN ").append(expression).append("=").append(first.trim())
1041                        .append(" THEN ").append(second.trim());
1042        // System.out.println(">>" + sb.toString());
1043
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        // System.out.println(">>" + sb.toString());
1051
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    // System.out.println("DECODE==> " + sb.toString());
1068
return sb.toString();
1069    } // convertDecode
1070

1071    /*************************************************************************/
1072
1073    /**
1074     * Convert Outer Join.
1075     * Converting joins can ve very complex when multiple tables/keys are involved.
1076     * The main scenarios supported are two tables with multiple key columns
1077     * and multiple tables with single key columns.
1078     * <pre>
1079     * SELECT a.Col1, b.Col2 FROM tableA a, tableB b WHERE a.ID=b.ID(+)
1080     * => SELECT a.Col1, b.Col2 FROM tableA a LEFT OUTER JOIN tableB b ON (a.ID=b.ID)
1081     *
1082     * SELECT a.Col1, b.Col2 FROM tableA a, tableB b WHERE a.ID(+)=b.ID
1083     * => SELECT a.Col1, b.Col2 FROM tableA a RIGHT OUTER JOIN tableB b ON (a.ID=b.ID)
1084     * Assumptions:
1085     * - No outer joins in sub queries (ignores sub-queries)
1086     * - OR condition ignored (not sure what to do, should not happen)
1087     * Limitations:
1088     * - Parameters for outer joins must be first - as sequence of parameters changes
1089     * </pre>
1090     * @param sqlStatement
1091     * @return converted statement
1092     */

1093    private String JavaDoc convertOuterJoin (String JavaDoc sqlStatement)
1094    {
1095        boolean trace = false;
1096        //
1097
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        //
1105
if (trace)
1106        {
1107            System.out.println();
1108            System.out.println("OuterJoin<== " + sqlStatement);
1109        // System.out.println("From=" + fromIndex + ", Where=" + whereIndex + ", End=" + endWhereIndex + ", Length=" + sqlStatement.length());
1110
}
1111        //
1112
String JavaDoc selectPart = sqlStatement.substring(0, fromIndex);
1113        String JavaDoc fromPart = sqlStatement.substring(fromIndex, whereIndex);
1114        String JavaDoc wherePart = sqlStatement.substring(whereIndex, endWhereIndex);
1115        String JavaDoc rest = sqlStatement.substring(endWhereIndex);
1116
1117        // find/remove all (+) from WHERE clase ------------------------------
1118
String JavaDoc newWherePart = wherePart;
1119        ArrayList joins = new ArrayList();
1120        int pos = newWherePart.indexOf("(+)");
1121        while (pos != -1)
1122        {
1123            // find starting point
1124
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 JavaDoc error = "Convert.convertOuterJoin - start point not found in clause " + wherePart;
1139                System.err.println (error);
1140                m_conversionError = error;
1141                return sqlStatement;
1142            }
1143            // find end point
1144
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        // System.out.println("<= " + newWherePart + " - Start=" + start + "+" + startOffset + ", End=" + end);
1150

1151            // extract condition
1152
String JavaDoc condition = newWherePart.substring(start+startOffset, end);
1153            joins.add(condition);
1154            if (trace)
1155                System.out.println("->" + condition);
1156            // new WHERE clause
1157
newWherePart = newWherePart.substring(0, start) + newWherePart.substring(end);
1158        // System.out.println("=> " + newWherePart);
1159
//
1160
pos = newWherePart.indexOf("(+)");
1161        }
1162        // correct beginning
1163
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        // Correct FROM clause -----------------------------------------------
1172
// Disassemble FROM
1173
String JavaDoc[] fromParts = fromPart.trim().substring(4).split(",");
1174        HashMap fromAlias = new HashMap(); // tables to be processed
1175
HashMap fromLookup = new HashMap(); // used tabled
1176
for (int i = 0; i < fromParts.length; i++)
1177        {
1178            String JavaDoc entry = fromParts[i].trim();
1179            String JavaDoc alias = entry; // no alias
1180
String JavaDoc 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(' ')); // may have AS
1186
}
1187            fromAlias.put(alias, table);
1188            fromLookup.put(alias, table);
1189            if (trace)
1190                System.out.println("Alias=" + alias + ", Table=" + table);
1191        }
1192
1193        /** Single column
1194            SELECT t.TableName, w.Name FROM AD_Table t, AD_Window w
1195            WHERE t.AD_Window_ID=w.AD_Window_ID(+)
1196            -- 275 rows
1197            SELECT t.TableName, w.Name FROM AD_Table t
1198            LEFT OUTER JOIN AD_Window w ON (t.AD_Window_ID=w.AD_Window_ID)
1199
1200            SELECT t.TableName, w.Name FROM AD_Table t, AD_Window w
1201            WHERE t.AD_Window_ID(+)=w.AD_Window_ID
1202            -- 239 rows
1203            SELECT t.TableName, w.Name FROM AD_Table t
1204            RIGHT OUTER JOIN AD_Window w ON (t.AD_Window_ID=w.AD_Window_ID)
1205
1206        ** Multiple columns
1207            SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
1208            FROM AD_TreeNode tn, AD_TreeBar tb
1209            WHERE tn.AD_Tree_ID=tb.AD_Tree_ID(+) AND tn.Node_ID=tb.Node_ID(+)
1210              AND tn.AD_Tree_ID=10
1211            -- 235 rows
1212            SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
1213            FROM AD_TreeNode tn LEFT OUTER JOIN AD_TreeBar tb
1214              ON (tn.Node_ID=tb.Node_ID AND tn.AD_Tree_ID=tb.AD_Tree_ID AND tb.AD_User_ID=0)
1215            WHERE tn.AD_Tree_ID=10
1216
1217            SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
1218            FROM AD_TreeNode tn, AD_TreeBar tb
1219            WHERE tn.AD_Tree_ID=tb.AD_Tree_ID(+) AND tn.Node_ID=tb.Node_ID(+)
1220             AND tn.AD_Tree_ID=10 AND tb.AD_User_ID(+)=0
1221            -- 214 rows
1222            SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
1223            FROM AD_TreeNode tn LEFT OUTER JOIN AD_TreeBar tb
1224              ON (tn.Node_ID=tb.Node_ID AND tn.AD_Tree_ID=tb.AD_Tree_ID AND tb.AD_User_ID=0)
1225            WHERE tn.AD_Tree_ID=10
1226
1227         */

1228        StringBuffer JavaDoc newFrom = new StringBuffer JavaDoc ();
1229        for (int i = 0; i < joins.size(); i++)
1230        {
1231            Join first = new Join ((String JavaDoc)joins.get(i));
1232            first.setMainTable((String JavaDoc)fromLookup.get(first.getMainAlias()));
1233            fromAlias.remove(first.getMainAlias()); // remove from list
1234
first.setJoinTable((String JavaDoc)fromLookup.get(first.getJoinAlias()));
1235            fromAlias.remove(first.getJoinAlias()); // remove from list
1236
if (trace)
1237                System.out.println("-First: " + first);
1238            //
1239
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            // keep it open - check for other key comparisons
1248
for (int j = i+1; j < joins.size(); j++)
1249            {
1250                Join second = new Join ((String JavaDoc)joins.get(j));
1251                second.setMainTable((String JavaDoc)fromLookup.get(second.getMainAlias()));
1252                second.setJoinTable((String JavaDoc)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); // remove from join list
1261
fromAlias.remove(first.getJoinAlias()); // remove from table list
1262
//----
1263
for (int k = i+1; k < joins.size(); k++)
1264                    {
1265                        Join third = new Join ((String JavaDoc)joins.get(k));
1266                        third.setMainTable((String JavaDoc)fromLookup.get(third.getMainAlias()));
1267                        third.setJoinTable((String JavaDoc)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); // remove from join list
1274
fromAlias.remove(third.getJoinAlias()); // remove from table list
1275
}
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(")"); // close ON
1284
// check dependency on first table
1285
for (int j = i+1; j < joins.size(); j++)
1286            {
1287                Join second = new Join ((String JavaDoc)joins.get(j));
1288                second.setMainTable((String JavaDoc)fromLookup.get(second.getMainAlias()));
1289                second.setJoinTable((String JavaDoc)fromLookup.get(second.getJoinAlias()));
1290                if (first.getMainTable().equals(second.getMainTable()))
1291                {
1292                    if (trace)
1293                        System.out.println("-Second/dep: " + second);
1294                    // FROM (AD_Field f LEFT OUTER JOIN AD_Column c ON (f.AD_Column_ID = c.AD_Column_ID))
1295
// LEFT OUTER JOIN AD_FieldGroup fg ON (f.AD_FieldGroup_ID = fg.AD_FieldGroup_ID),
1296
newFrom.insert(6, '('); // _FROM ...
1297
newFrom.append(')'); // add parantesis on previous relation
1298
//
1299
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); // remove from join list
1303
fromAlias.remove(second.getJoinAlias()); // remove from table list
1304
// additional join colums would come here
1305
newFrom.append(")"); // close ON
1306
//----
1307
for (int k = i+1; k < joins.size(); k++)
1308                    {
1309                        Join third = new Join ((String JavaDoc)joins.get(k));
1310                        third.setMainTable((String JavaDoc)fromLookup.get(third.getMainAlias()));
1311                        third.setJoinTable((String JavaDoc)fromLookup.get(third.getJoinAlias()));
1312                        if (second.getJoinTable().equals(third.getMainTable()))
1313                        {
1314                            if (trace)
1315                                System.out.println("-Third-dep: " + third);
1316                            // FROM ((C_BPartner p LEFT OUTER JOIN AD_User c ON (p.C_BPartner_ID=c.C_BPartner_ID))
1317
// LEFT OUTER JOIN C_BPartner_Location l ON (p.C_BPartner_ID=l.C_BPartner_ID))
1318
// LEFT OUTER JOIN C_Location a ON (l.C_Location_ID=a.C_Location_ID)
1319
newFrom.insert(6, '('); // _FROM ...
1320
newFrom.append(')'); // add parantesis on previous relation
1321
//
1322
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); // remove from join list
1326
fromAlias.remove(third.getJoinAlias()); // remove from table list
1327
// additional join colums would come here
1328
newFrom.append(")"); // close ON
1329
}
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            } // dependency on first table
1337
}
1338        // remaining Tables
1339
Iterator it = fromAlias.keySet().iterator();
1340        while (it.hasNext())
1341        {
1342            Object JavaDoc alias = it.next();
1343            Object JavaDoc 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        //
1351
StringBuffer JavaDoc retValue = new StringBuffer JavaDoc (sqlStatement.length()+20);
1352        retValue.append(selectPart)
1353            .append(newFrom).append(" ")
1354            .append(newWherePart).append(rest);
1355        //
1356
if (trace)
1357            System.out.println("OuterJoin==> " + retValue.toString());
1358        return retValue.toString();
1359    } // convertOuterJoin
1360

1361    /**
1362     * Convert RowNum.
1363     * <pre>
1364     * SELECT Col1 FROM tableA WHERE ROWNUM=1
1365     * => SELECT Col1 FROM tableA LIMIT 1
1366     * Assumptions/Limitations:
1367     * - RowNum not used in SELECT part
1368     * </pre>
1369     * @param sqlStatement
1370     * @return converted statement
1371     */

1372    private String JavaDoc convertRowNum (String JavaDoc sqlStatement)
1373    {
1374        System.out.println("RowNum<== " + sqlStatement);
1375        String JavaDoc retValue = sqlStatement;
1376        //
1377
// System.out.println("RowNum==> " + retValue);
1378
return retValue;
1379    } // convertRowNum
1380

1381    /**
1382     * Convert TRUNC.
1383     * Assumed that it is used for date only!
1384     * <pre>
1385     * TRUNC(myDate)
1386     * => DATE_Trunc('day',myDate)
1387     *
1388     * TRUNC(myDate,'oracleFormat')
1389     * => DATE_Trunc('pgFormat',myDate)
1390     *
1391     * Oracle => PostgreSQL (list not complete!)
1392     * Q quarter
1393     * MM month
1394     * DD day
1395     * Spacial handling of DAY,DY (Starting dat of the week)
1396     * => DATE_Trunc('day',($1-DATE_PART('dow',$1)));
1397     * </pre>
1398     * @param sqlStatement
1399     * @return converted statement
1400     */

1401    private String JavaDoc convertTrunc (String JavaDoc sqlStatement)
1402    {
1403        int index = sqlStatement.indexOf("TRUNC");
1404        String JavaDoc beforeStatement = sqlStatement.substring(0, index);
1405        String JavaDoc afterStatement = sqlStatement.substring(index);
1406        afterStatement = afterStatement.substring(afterStatement.indexOf('(')+1);
1407        index = Util.findIndexOf(afterStatement, ')');
1408        String JavaDoc temp = afterStatement.substring(0, index).trim();
1409        afterStatement = afterStatement.substring(index+1);
1410    // System.out.println("Trunc<== " + temp);
1411
StringBuffer JavaDoc retValue = new StringBuffer JavaDoc ("DATE_Trunc("); // lower case otherwise endless-loop
1412
if (temp.indexOf(',') == -1)
1413            retValue.append("'day',").append(temp);
1414        else // with format
1415
{
1416            int pos = temp.indexOf(',');
1417            String JavaDoc variable = temp.substring(0, pos).trim();
1418            String JavaDoc 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    // System.out.println("Trunc==> " + retValue.toString());
1436
//
1437
retValue.insert(0, beforeStatement);
1438        retValue.append(afterStatement);
1439        return retValue.toString();
1440    } // convertTrunc
1441

1442} // Convert
1443
Popular Tags