Derby SVN + ColdFusion = SQL Parser

Brain dump follows.

1. Checkout Derby from SVN.

2. 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.
    	 *
   

3. 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

4. Archive $CFROOT/lib/derby*.jar to a cool, dry place.

5. Stop ColdFusion.

6. Copy $DERBY/jars/sane/derby*.jar to $CFROOT/lib.

7. Restart ColdFusion.

8. 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?