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?

Published by

Rick Osborne

I am a web geek who has been doing this sort of thing entirely too long. I rant, I muse, I whine. That is, I am not at all atypical for my breed.