Derby SVN + ColdFusion = SQL Parser

Brain dump follows.

Checkout Derby from SVN.

Apply a patch:

Index: java/engine/org/apache/derby/impl/sql/compile/BinaryOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/BinaryOperatorNode.java	(revision 911671)
+++ java/engine/org/apache/derby/impl/sql/compile/BinaryOperatorNode.java	(working copy)
@@ -732,6 +732,26 @@
 	}
 
 	/**
+	 * Get the operator
+	 *
+	 * @return String The operator.
+	 */
+	public String getOperatorString()
+	{
+		return operator;
+	}
+
+	/**
+	 * Get the methodName
+	 *
+	 * @return String The methodName.
+	 */
+	public String getMethodName()
+	{
+		return methodName;
+	}
+
+	/**
 	 * Categorize this predicate.  Initially, this means
 	 * building a bit map of the referenced tables for each predicate.
 	 * If the source of this ColumnReference (at the next underlying level) 
Index: java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/SelectNode.java	(revision 911671)
+++ java/engine/org/apache/derby/impl/sql/compile/SelectNode.java	(working copy)
@@ -360,6 +360,36 @@
 	}
 
 	/**
+	 * Return the groupByList for this SelectNode.
+	 *
+	 * @return GroupByList	The groupByList for this SelectNode.
+	 */
+	public GroupByList getGroupByList()
+	{
+		return groupByList;
+	}
+
+	/**
+	 * Return the havingClause for this SelectNode.
+	 *
+	 * @return HavingClause	The havingClause for this SelectNode.
+	 */
+	public ValueNode getHavingClause()
+	{
+		return havingClause;
+	}
+
+	/**
+	 * Return the orderByList for this SelectNode.
+	 *
+	 * @return OrderByList	The orderByList for this SelectNode.
+	 */
+	public OrderByList getOrderByList()
+	{
+		return orderByList;
+	}
+
+	/**
 	 * Find colName in the result columns and return underlying columnReference.
 	 * Note that this function returns null if there are more than one FromTable
 	 * for this SelectNode and the columnReference needs to be directly under
Index: java/engine/org/apache/derby/impl/sql/compile/QueryTreeNodeVector.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/QueryTreeNodeVector.java	(revision 911671)
+++ java/engine/org/apache/derby/impl/sql/compile/QueryTreeNodeVector.java	(working copy)
@@ -46,7 +46,7 @@
 		return v.size();
 	}
 
-	QueryTreeNode elementAt(int index)
+	public QueryTreeNode elementAt(int index)
 	{
 		return (QueryTreeNode) v.elementAt(index);
 	}
Index: java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java	(revision 911671)
+++ java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java	(working copy)
@@ -198,7 +198,7 @@
 	 *
 	 * @return	The operator of this unary operator.
 	 */
-	String getOperatorString()
+	public String getOperatorString()
 	{
 		return operator;
 	}
@@ -267,6 +267,16 @@
 	}
 
 	/**
+	 * Get the methodName
+	 *
+	 * @return String The methodName.
+	 */
+	public String getMethodName()
+	{
+		return methodName;
+	}
+
+	/**
 	 * Get the parameter operand of this unary operator.
 	 * For the example below, for abs unary operator node, we want to get ?
 	 * select * from t1 where -? = max_cni(abs(-?), sqrt(+?))
Index: java/engine/org/apache/derby/impl/sql/compile/BinaryListOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/BinaryListOperatorNode.java	(revision 911671)
+++ java/engine/org/apache/derby/impl/sql/compile/BinaryListOperatorNode.java	(working copy)
@@ -95,6 +95,26 @@
 	}
 
 	/**
+	 * Get the operator
+	 *
+	 * @return String The operator.
+	 */
+	public String getOperatorString()
+	{
+		return operator;
+	}
+
+	/**
+	 * Get the methodName
+	 *
+	 * @return String The methodName.
+	 */
+	public String getMethodName()
+	{
+		return methodName;
+	}
+
+	/**
 	 * Prints the sub-nodes of this object.  See QueryTreeNode.java for
 	 * how tree printing is supposed to work.
 	 *

Build Derby:

alias javac='/System/Library/Frameworks/JavaVM.framework/Versions/1.6/Commands/javac -target 1.5'
export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/1.6/Home/
ant -quiet clobber ; ant -quiet buildsource ; ant -quiet buildjars

Archive $CFROOT/lib/derby*.jar to a cool, dry place.
Stop ColdFusion.
Copy $DERBY/jars/sane/derby*.jar to $CFROOT/lib.
Restart ColdFusion.

Execute template:

<cfscript>
createObject("java", "java.lang.System").
	setProperty("derby.debug.true", "StopAfterParsing");
driver = createObject("java", "org.apache.derby.jdbc.EmbeddedDriver").init();
props = createObject("java","java.util.Properties");
conn = driver.connect("jdbc:derby:memory:dummy;create=true",props);
sql = "SELECT a, b FROM foo, bar";
try { conn.prepareStatement(sql); }
catch(org.apache.derby.impl.jdbc.EmbedSQLException e) {
	if(e.sqlState neq "42Z55") rethrow();
}
qt = conn.getContextManager().
	getContext("LanguageConnectionContext").
	getLastQueryTree();
select = qt.getResultSetNode();
from = select.getFromList();
tableCount = from.size();
for(tableNum = 0; tableNum lt tableCount; tableNum++) {
	baseTable = from.getOptimizable(tableNum);
	tableName = baseTable.getTableName().toString();
	writeDump(tableName);
}
// bytes = createObject("java", "java.io.ByteArrayOutputStream").init();
// createObject("java", "org.apache.derby.iapi.services.sanity.SanityManager").
	SET_DEBUG_STREAM(createObject("java", "java.io.PrintWriter").init(bytes));
// qt.treePrint();
// writeDump(bytes.toString());
try { driver.connect("jdbc:derby:memory:dummy;shutdown=true", props); }
catch(any e) { }
</cfscript>

Anybody wanna rewrite QoQ?

By Rick Osborne