SQL Parser Paramer

notes.md

Example output:

$ java -cp target/classes/ com.foundationdb.sql.ParamerDemo "SELECT * FROM USERS WHERE NAME ='BOB' AND ID=123 AND STATUS=TRUE"
 pre: SELECT * FROM USERS WHERE NAME ='BOB' AND ID=123 AND STATUS=TRUE
post: SELECT * FROM users WHERE ((name = $1) AND (id = $2)) AND (status = $3)
  $1 => BOB (String)
  $2 => 123 (Integer)
  $3 => true (Boolean)

Extensions:

• Avoiding transformation of clauses outside of the WHERE (e.g. SELECT a = 'YES' ...)
• Detecting equivalent values (e.g. WHERE a = 5 AND b = 5)
• Parameterizing more node types (e.g. BetweenOperatorNode)

Paramer.java

import com.foundationdb.sql.parser.BinaryRelationalOperatorNode;
import com.foundationdb.sql.parser.ConstantNode;
import com.foundationdb.sql.parser.NodeTypes;
import com.foundationdb.sql.parser.ParameterNode;
import com.foundationdb.sql.parser.Visitable;
import com.foundationdb.sql.parser.Visitor;

import java.util.LinkedHashMap;
import java.util.Map;

public class Paramer implements Visitor
{
    public final Map<ParameterNode, ConstantNode> substitutions = new LinkedHashMap<ParameterNode, ConstantNode>();

    @Override
    public Visitable visit(Visitable node) throws StandardException {
        if(node instanceof BinaryRelationalOperatorNode) {
            BinaryRelationalOperatorNode bin = (BinaryRelationalOperatorNode)node;
            if(bin.getRightOperand() instanceof ConstantNode) {
                ConstantNode con = (ConstantNode)bin.getRightOperand();
                ParameterNode param = new ParameterNode();
                param.setNodeType(NodeTypes.PARAMETER_NODE);
                param.init(substitutions.size(), null);
                bin.setRightOperand(param);
                substitutions.put(param, con);
            }
        }
        return node;
    }

    @Override
    public boolean visitChildrenFirst(Visitable node) {
        return false;
    }

    @Override
    public boolean stopTraversal() {
        return false;
    }

    @Override
    public boolean skipChildren(Visitable node) throws StandardException {
        return false;
    }
}

ParamerDemo.java

import com.foundationdb.sql.parser.ConstantNode;
import com.foundationdb.sql.parser.ParameterNode;
import com.foundationdb.sql.parser.SQLParser;
import com.foundationdb.sql.parser.StatementNode;
import com.foundationdb.sql.unparser.NodeToString;

import java.util.Map.Entry;

public class ParamerDemo
{
    public static void main(String[] args) throws Exception {
        SQLParser parser = new SQLParser();
        for(String s : args) {
            StatementNode stmt = parser.parseStatement(s);
            Paramer paramer = new Paramer();
            stmt.accept(paramer);
            String post = new NodeToString().toString(stmt);

            System.out.println(" pre: " + s);
            System.out.println("post: " + post);
            for(Entry<ParameterNode, ConstantNode> i : paramer.substitutions.entrySet()) {
                System.out.printf("  $%d => %s (%s)\n",
                                  i.getKey().getParameterNumber() + 1,
                                  i.getValue().getValue(),
                                  i.getValue().getValue().getClass().getSimpleName());
            }
        }
    }
}