/******************************************************************************
* Product: Adempiere ERP & CRM Smart Business Solution *
* Copyright (C) 1999-2006 Adempiere, Inc. All Rights Reserved. *
* This program is free software; you can redistribute it and/or modify it *
* under the terms version 2 of the GNU General Public License as published *
* by the Free Software Foundation. This program is distributed in the hope *
* that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
* warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. *
* See the GNU General Public License for more details. *
* You should have received a copy of the GNU General Public License along *
* with this program; if not, write to the Free Software Foundation, Inc., *
* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. *
*****************************************************************************/
package org.compiere.dbPort;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.logging.Level;
import org.compiere.util.CLogger;
import org.compiere.util.Util;
/***
* Convert from oracle syntax to sql 92 standard
* @author Low Heng Sin
*
*/
public abstract class Convert_SQL92 extends Convert {
/** Logger */
private static final CLogger log = CLogger.getCLogger (Convert_SQL92.class);
/**************************************************************************
* Convert Outer Join.
* Converting joins can be 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.
* {@code
* 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)
* Assumptions:
* - No outer joins in sub queries (ignores sub-queries)
* - OR condition ignored (not sure what to do, should not happen)
* Limitations:
* - Parameters for outer joins must be first - as sequence of parameters changes
* }
* @param sqlStatement
* @return converted statement
*/
protected String convertOuterJoin (String sqlStatement)
{
boolean trace = false;
//
int fromIndex = Util.findIndexOf (sqlStatement.toUpperCase(), " FROM ");
int whereIndex = Util.findIndexOf(sqlStatement.toUpperCase(), " WHERE ");
int endWhereIndex = Util.findIndexOf(sqlStatement.toUpperCase(), " GROUP BY ");
if (endWhereIndex == -1)
endWhereIndex = Util.findIndexOf(sqlStatement.toUpperCase(), " ORDER BY ");
if (endWhereIndex == -1)
endWhereIndex = sqlStatement.length();
//
if (trace)
{
if (log.isLoggable(Level.INFO)) log.info("OuterJoin<== " + sqlStatement);
}
//
String selectPart = sqlStatement.substring(0, fromIndex);
String fromPart = sqlStatement.substring(fromIndex, whereIndex);
String wherePart = sqlStatement.substring(whereIndex, endWhereIndex);
String rest = sqlStatement.substring(endWhereIndex);
// find/remove all (+) from WHERE clase ------------------------------
String newWherePart = wherePart;
ArrayList joins = new ArrayList();
int pos = newWherePart.indexOf("(+)");
while (pos != -1)
{
// find starting point
int start = newWherePart.lastIndexOf(" AND ", pos);
int startOffset = 5;
if (start == -1)
{
start = newWherePart.lastIndexOf(" OR ", pos);
startOffset = 4;
}
if (start == -1)
{
start = newWherePart.lastIndexOf("WHERE ", pos);
startOffset = 6;
}
if (start == -1)
{
String error = "Start point not found in clause " + wherePart;
log.severe(error);
m_conversionError = error;
return sqlStatement;
}
// find end point
int end = newWherePart.indexOf(" AND ", pos);
if (end == -1)
end = newWherePart.indexOf(" OR ", pos);
if (end == -1)
end = newWherePart.length();
// extract condition
String condition = newWherePart.substring(start+startOffset, end);
joins.add(condition);
if (trace)
if (log.isLoggable(Level.INFO)) log.info("->" + condition);
// new WHERE clause
newWherePart = newWherePart.substring(0, start) + newWherePart.substring(end);
//
pos = newWherePart.indexOf("(+)");
}
// correct beginning
newWherePart = newWherePart.trim();
if (newWherePart.startsWith("AND "))
newWherePart = "WHERE" + newWherePart.substring(3);
else if (newWherePart.startsWith("OR "))
newWherePart = "WHERE" + newWherePart.substring(2);
if (trace)
if (log.isLoggable(Level.INFO)) log.info("=> " + newWherePart);
// Correct FROM clause -----------------------------------------------
// Disassemble FROM
String[] fromParts = fromPart.trim().substring(4).split(",");
HashMap fromAlias = new HashMap(); // tables to be processed
HashMap fromLookup = new HashMap(); // used tabled
for (int i = 0; i < fromParts.length; i++)
{
String entry = fromParts[i].trim();
String alias = entry; // no alias
String table = entry;
int aPos = entry.lastIndexOf(' ');
if (aPos != -1)
{
alias = entry.substring(aPos+1);
table = entry.substring(0, entry.indexOf(' ')); // may have AS
}
fromAlias.put(alias, table);
fromLookup.put(alias, table);
if (trace)
if (log.isLoggable(Level.INFO)) log.info("Alias=" + alias + ", Table=" + table);
}
/** Single column
SELECT t.TableName, w.Name FROM AD_Table t, AD_Window w
WHERE t.AD_Window_ID=w.AD_Window_ID(+)
-- 275 rows
SELECT t.TableName, w.Name FROM AD_Table t
LEFT OUTER JOIN AD_Window w ON (t.AD_Window_ID=w.AD_Window_ID)
SELECT t.TableName, w.Name FROM AD_Table t, AD_Window w
WHERE t.AD_Window_ID(+)=w.AD_Window_ID
-- 239 rows
SELECT t.TableName, w.Name FROM AD_Table t
RIGHT OUTER JOIN AD_Window w ON (t.AD_Window_ID=w.AD_Window_ID)
** Multiple columns
SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
FROM AD_TreeNode tn, AD_TreeBar tb
WHERE tn.AD_Tree_ID=tb.AD_Tree_ID(+) AND tn.Node_ID=tb.Node_ID(+)
AND tn.AD_Tree_ID=10
-- 235 rows
SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
FROM AD_TreeNode tn LEFT OUTER JOIN AD_TreeBar tb
ON (tn.Node_ID=tb.Node_ID AND tn.AD_Tree_ID=tb.AD_Tree_ID AND tb.AD_User_ID=0)
WHERE tn.AD_Tree_ID=10
SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
FROM AD_TreeNode tn, AD_TreeBar tb
WHERE tn.AD_Tree_ID=tb.AD_Tree_ID(+) AND tn.Node_ID=tb.Node_ID(+)
AND tn.AD_Tree_ID=10 AND tb.AD_User_ID(+)=0
-- 214 rows
SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,tb.IsActive
FROM AD_TreeNode tn LEFT OUTER JOIN AD_TreeBar tb
ON (tn.Node_ID=tb.Node_ID AND tn.AD_Tree_ID=tb.AD_Tree_ID AND tb.AD_User_ID=0)
WHERE tn.AD_Tree_ID=10
*/
StringBuilder newFrom = new StringBuilder ();
for (int i = 0; i < joins.size(); i++)
{
Join first = new Join ((String)joins.get(i));
first.setMainTable((String)fromLookup.get(first.getMainAlias()));
fromAlias.remove(first.getMainAlias()); // remove from list
first.setJoinTable((String)fromLookup.get(first.getJoinAlias()));
fromAlias.remove(first.getJoinAlias()); // remove from list
if (trace)
if (log.isLoggable(Level.INFO)) log.info("-First: " + first);
//
if (newFrom.length() == 0)
newFrom.append(" FROM ");
else
newFrom.append(", ");
newFrom.append(first.getMainTable()).append(" ").append(first.getMainAlias())
.append(first.isLeft() ? " LEFT" : " RIGHT").append(" OUTER JOIN ")
.append(first.getJoinTable()).append(" ").append(first.getJoinAlias())
.append(" ON (").append(first.getCondition());
// keep it open - check for other key comparisons
for (int j = i+1; j < joins.size(); j++)
{
Join second = new Join ((String)joins.get(j));
second.setMainTable((String)fromLookup.get(second.getMainAlias()));
second.setJoinTable((String)fromLookup.get(second.getJoinAlias()));
if ((first.getMainTable().equals(second.getMainTable())
&& first.getJoinTable().equals(second.getJoinTable()))
|| second.isConditionOf(first) )
{
if (trace)
if (log.isLoggable(Level.INFO)) log.info("-Second/key: " + second);
newFrom.append(" AND ").append(second.getCondition());
joins.remove(j); // remove from join list
fromAlias.remove(first.getJoinAlias()); // remove from table list
//----
for (int k = i+1; k < joins.size(); k++)
{
Join third = new Join ((String)joins.get(k));
third.setMainTable((String)fromLookup.get(third.getMainAlias()));
third.setJoinTable((String)fromLookup.get(third.getJoinAlias()));
if (third.isConditionOf(second))
{
if (trace)
if (log.isLoggable(Level.INFO)) log.info("-Third/key: " + third);
newFrom.append(" AND ").append(third.getCondition());
joins.remove(k); // remove from join list
fromAlias.remove(third.getJoinAlias()); // remove from table list
}
else if (trace)
if (log.isLoggable(Level.INFO)) log.info("-Third/key-skip: " + third);
}
}
else if (trace)
if (log.isLoggable(Level.INFO)) log.info("-Second/key-skip: " + second);
}
newFrom.append(")"); // close ON
// check dependency on first table
for (int j = i+1; j < joins.size(); j++)
{
Join second = new Join ((String)joins.get(j));
second.setMainTable((String)fromLookup.get(second.getMainAlias()));
second.setJoinTable((String)fromLookup.get(second.getJoinAlias()));
if (first.getMainTable().equals(second.getMainTable()))
{
if (trace)
if (log.isLoggable(Level.INFO)) log.info("-Second/dep: " + second);
// FROM (AD_Field f LEFT OUTER JOIN AD_Column c ON (f.AD_Column_ID = c.AD_Column_ID))
// LEFT OUTER JOIN AD_FieldGroup fg ON (f.AD_FieldGroup_ID = fg.AD_FieldGroup_ID),
newFrom.insert(6, '('); // _FROM ...
newFrom.append(')'); // add parantesis on previous relation
//
newFrom.append(second.isLeft() ? " LEFT" : " RIGHT").append(" OUTER JOIN ")
.append(second.getJoinTable()).append(" ").append(second.getJoinAlias())
.append(" ON (").append(second.getCondition());
joins.remove(j); // remove from join list
fromAlias.remove(second.getJoinAlias()); // remove from table list
// additional join columns would come here
newFrom.append(")"); // close ON
//----
for (int k = i+1; k < joins.size(); k++)
{
Join third = new Join ((String)joins.get(k));
third.setMainTable((String)fromLookup.get(third.getMainAlias()));
third.setJoinTable((String)fromLookup.get(third.getJoinAlias()));
if (second.getJoinTable().equals(third.getMainTable()))
{
if (trace)
if (log.isLoggable(Level.INFO)) log.info("-Third-dep: " + third);
// FROM ((C_BPartner p LEFT OUTER JOIN AD_User c ON (p.C_BPartner_ID=c.C_BPartner_ID))
// LEFT OUTER JOIN C_BPartner_Location l ON (p.C_BPartner_ID=l.C_BPartner_ID))
// LEFT OUTER JOIN C_Location a ON (l.C_Location_ID=a.C_Location_ID)
newFrom.insert(6, '('); // _FROM ...
newFrom.append(')'); // add parantesis on previous relation
//
newFrom.append(third.isLeft() ? " LEFT" : " RIGHT").append(" OUTER JOIN ")
.append(third.getJoinTable()).append(" ").append(third.getJoinAlias())
.append(" ON (").append(third.getCondition());
joins.remove(k); // remove from join list
fromAlias.remove(third.getJoinAlias()); // remove from table list
// additional join columns would come here
newFrom.append(")"); // close ON
}
else if (trace)
if (log.isLoggable(Level.INFO)) log.info("-Third-skip: " + third);
}
}
else if (trace)
if (log.isLoggable(Level.INFO)) log.info("-Second/dep-skip: " + second);
} // dependency on first table
}
// remaining Tables
Iterator it = fromAlias.keySet().iterator();
while (it.hasNext())
{
Object alias = it.next();
Object table = fromAlias.get(alias);
newFrom.append(", ").append(table);
if (!table.equals(alias))
newFrom.append(" ").append(alias);
}
if (trace)
if (log.isLoggable(Level.INFO)) log.info(newFrom.toString());
//
StringBuilder retValue = new StringBuilder (sqlStatement.length()+20);
retValue.append(selectPart)
.append(newFrom).append(" ")
.append(newWherePart).append(rest);
//
if (trace)
if (log.isLoggable(Level.INFO)) log.info("OuterJoin==> " + retValue.toString());
return retValue.toString();
} // convertOuterJoin
/**
* Converts Decode.
* {@code
* DECODE (a, 1, 'one', 2, 'two', 'none')
* => CASE WHEN a = 1 THEN 'one' WHEN a = 2 THEN 'two' ELSE 'none' END
* }
* @param sqlStatement
* @return converted statement
*/
protected String convertDecode(String sqlStatement, int fromIndex)
{
String statement = sqlStatement;
StringBuilder sb = new StringBuilder("CASE");
int index = statement.toUpperCase().indexOf("DECODE", fromIndex);
if (index <= 0) return sqlStatement;
char previousChar = statement.charAt(index - 1);
if (!(Character.isWhitespace(previousChar) || isOperator(previousChar)))
return sqlStatement;
String firstPart = statement.substring(0,index);
// find the opening (
index = index + 6;
while (index < statement.length()) {
char c = statement.charAt(index);
if (Character.isWhitespace(c)) {
index++;
continue;
}
if (c == '(') break;
return sqlStatement;
}
statement = statement.substring(index+1);
// find the expression "a" - find first , ignoring ()
index = Util.findIndexOf (statement, ',');
String expression = statement.substring(0, index).trim();
// 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);
//
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());
statement = statement.substring(index+1);
index = Util.findIndexOf (statement, ',',')');
}
}
else if (cc == ')')
{
sb.append(" ELSE ").append(first.trim()).append(" END");
index = -1;
}
else
error = true;
if (error)
{
log.log(Level.SEVERE, "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);
return sb.toString();
} // convertDecode
/**
* Converts Delete.
*
*
* DELETE C_Order i WHERE
* => DELETE FROM C_Order WHERE
*
*
* @param sqlStatement
* @return converted statement
*/
protected String convertDelete(String sqlStatement) {
int index = sqlStatement.toUpperCase().indexOf("DELETE ");
if (index < 7) {
return "DELETE FROM " + sqlStatement.substring(index + 7);
}
return sqlStatement;
} // convertDelete
/**
* Is character a valid SQL operator
* @param c
* @return true if c is SQL operator
*/
protected boolean isOperator(char c)
{
if ('=' == c)
return true;
else if ('<' == c)
return true;
else if ('>' == c)
return true;
else if ('|' == c)
return true;
else if ('(' == c)
return true;
else if (')' == c)
return true;
else if ('+' == c)
return true;
else if ('-' == c)
return true;
else if ('*' == c)
return true;
else if ('/' == c)
return true;
else if ('!' == c)
return true;
else if (',' == c)
return true;
else if ('?' == c)
return true;
else if ('#' == c)
return true;
else if ('@' == c)
return true;
else if ('~' == c)
return true;
else if ('&' == c)
return true;
else if ('^' == c)
return true;
else if ('!' == c)
return true;
else
return false;
}
}