Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>You can do this much like how I demonstrated in <a href="http://bkiers.blogspot.com/2011/03/creating-your-own-programming-language.html" rel="noreferrer">my blog posts</a> (and since I know you read those, I won't go in much detail). The only difference in this case is that each of your rows of data has its own scope. An easy way to pass this scope along is by providing it as a parameter to the <code>eval(...)</code> method.</p> <p>Below is a quick demo of how this could be implemented. Note that I quickly hacked this together based on my blog posts: not all functionality is available (see the many <code>TODO</code>'s, and there are likely (small) bugs in it as well. Use at your own risk!). </p> <p>Besides ANTLR v3.3, you need the following 3 files for this demo:</p> <h2>Select.g</h2> <pre class="lang-none prettyprint-override"><code>grammar Select; options { output=AST; } tokens { // imaginary tokens ROOT; ATTR_LIST; UNARY_MINUS; // literal tokens Eq = '='; NEq = '!='; LT = '&lt;'; LTEq = '&lt;='; GT = '&gt;'; GTEq = '&gt;='; Minus = '-'; Not = '!'; Select = 'select'; From = 'from'; Where = 'where'; And = 'AND'; Or = 'OR'; } parse : select_stat EOF -&gt; ^(ROOT select_stat) ; select_stat : Select attr_list From Id where_stat ';' -&gt; ^(Select attr_list Id where_stat) ; attr_list : Id (',' Id)* -&gt; ^(ATTR_LIST Id+) ; where_stat : Where expr -&gt; expr | -&gt; ^(Eq Int["1"] Int["1"]) // no 'where', insert '1=1' which is always true ; expr : or_expr ; or_expr : and_expr (Or^ and_expr)* ; and_expr : eq_expr (And^ eq_expr)* ; eq_expr : rel_expr ((Eq | NEq)^ rel_expr)* ; rel_expr : unary_expr ((LT | LTEq | GT | GTEq)^ unary_expr)? ; unary_expr : Minus atom -&gt; ^(UNARY_MINUS atom) | Not atom -&gt; ^(Not atom) | atom ; atom : Str | Int | Id | '(' expr ')' -&gt; expr ; Id : ('a'..'z' | 'A'..'Z' | '_') ('a'..'z' | 'A'..'Z' | '_' | Digit)*; Str : '\'' ('\'\'' | ~('\'' | '\r' | '\n'))* '\'' { // strip the surrounding quotes and replace '' with ' setText($text.substring(1, $text.length() - 1).replace("''", "'")); } ; Int : Digit+; Space : (' ' | '\t' | '\r' | '\n') {skip();}; fragment Digit : '0'..'9'; </code></pre> <h2>SelectWalker.g</h2> <pre class="lang-none prettyprint-override"><code>tree grammar SelectWalker; options { tokenVocab=Select; ASTLabelType=CommonTree; } @header { import java.util.List; import java.util.Map; import java.util.Set; } @members { private Map&lt;String, List&lt;B&gt;&gt; dataPool; public SelectWalker(CommonTreeNodeStream nodes, Map&lt;String, List&lt;B&gt;&gt; data) { super(nodes); dataPool = data; } } query returns [List&lt;List&lt;Object&gt;&gt; result] : ^(ROOT select_stat) {$result = (List&lt;List&lt;Object&gt;&gt;)$select_stat.node.eval(null);} ; select_stat returns [Node node] : ^(Select attr_list Id expr) {$node = new SelectNode($attr_list.attributes, dataPool.get($Id.text), $expr.node);} ; attr_list returns [List&lt;String&gt; attributes] @init{$attributes = new ArrayList&lt;String&gt;();} : ^(ATTR_LIST (Id {$attributes.add($Id.text);})+) ; expr returns [Node node] : ^(Or a=expr b=expr) {$node = null; /* TODO */} | ^(And a=expr b=expr) {$node = new AndNode($a.node, $b.node);} | ^(Eq a=expr b=expr) {$node = new EqNode($a.node, $b.node);} | ^(NEq a=expr b=expr) {$node = new NEqNode($a.node, $b.node);} | ^(LT a=expr b=expr) {$node = null; /* TODO */} | ^(LTEq a=expr b=expr) {$node = null; /* TODO */} | ^(GT a=expr b=expr) {$node = new GTNode($a.node, $b.node);} | ^(GTEq a=expr b=expr) {$node = null; /* TODO */} | ^(UNARY_MINUS a=expr) {$node = null; /* TODO */} | ^(Not a=expr) {$node = null; /* TODO */} | Str {$node = new AtomNode($Str.text);} | Int {$node = new AtomNode(Integer.valueOf($Int.text));} | Id {$node = new IdNode($Id.text);} ; </code></pre> <h2>Main.java</h2> <p><em>(yes, stick all these Java classes in the same file: <code>Main.java</code>)</em></p> <pre><code>import org.antlr.runtime.*; import org.antlr.runtime.tree.*; import org.antlr.stringtemplate.*; import java.util.*; public class Main { static Map&lt;String, List&lt;B&gt;&gt; getData() { Map&lt;String, List&lt;B&gt;&gt; map = new HashMap&lt;String, List&lt;B&gt;&gt;(); List&lt;B&gt; data = new ArrayList&lt;B&gt;(); data.add(new B("id_1", 345, "89", "abd")); data.add(new B("id_2", 45, "89", "abd")); data.add(new B("id_3", 1, "89", "abd")); data.add(new B("id_4", 45, "8", "abd")); data.add(new B("id_5", 45, "89", "abc")); data.add(new B("id_6", 45, "99", "abC")); map.put("poolX", data); return map; } public static void main(String[] args) throws Exception { String src = "select C, Y from poolX where X = 45 AND Y &gt; '88' AND Z != 'abc';"; SelectLexer lexer = new SelectLexer(new ANTLRStringStream(src)); SelectParser parser = new SelectParser(new CommonTokenStream(lexer)); CommonTree tree = (CommonTree)parser.parse().getTree(); SelectWalker walker = new SelectWalker(new CommonTreeNodeStream(tree), getData()); List&lt;List&lt;Object&gt;&gt; result = walker.query(); for(List&lt;Object&gt; row : result) { System.out.println(row); } } } class B { String C; Integer X; String Y; String Z; B(String c, Integer x, String y, String z) { C = c; X = x; Y = y; Z = z; } Object getAttribute(String attribute) { if(attribute.equals("C")) return C; if(attribute.equals("X")) return X; if(attribute.equals("Y")) return Y; if(attribute.equals("Z")) return Z; throw new RuntimeException("Unknown attribute: B." + attribute); // or use your Apache Bean-util API, or even reflection here instead of the above... } } interface Node { Object eval(B b); } class AtomNode implements Node { final Object value; AtomNode(Object v) { value = v; } public Object eval(B b) { return value; } } abstract class BinNode implements Node { final Node left; final Node right; BinNode(Node l, Node r) { left = l; right = r; } public abstract Object eval(B b); } class AndNode extends BinNode { AndNode(Node l, Node r) { super(l, r); } @Override public Object eval(B b) { return (Boolean)super.left.eval(b) &amp;&amp; (Boolean)super.right.eval(b); } } class EqNode extends BinNode { EqNode(Node l, Node r) { super(l, r); } @Override public Object eval(B b) { return super.left.eval(b).equals(super.right.eval(b)); } } class NEqNode extends BinNode { NEqNode(Node l, Node r) { super(l, r); } @Override public Object eval(B b) { return !super.left.eval(b).equals(super.right.eval(b)); } } class GTNode extends BinNode { GTNode(Node l, Node r) { super(l, r); } @Override public Object eval(B b) { return ((Comparable)super.left.eval(b)).compareTo((Comparable)super.right.eval(b)) &gt; 0; } } class IdNode implements Node { final String id; IdNode(String i) { id = i; } @Override public Object eval(B b) { return b.getAttribute(id); } } class SelectNode implements Node { final List&lt;String&gt; attributes; final List&lt;B&gt; data; final Node expression; SelectNode(List&lt;String&gt; a, List&lt;B&gt; d, Node e) { attributes = a; data = d; expression = e; } @Override public Object eval(B ignored) { List&lt;List&lt;Object&gt;&gt; result = new ArrayList&lt;List&lt;Object&gt;&gt;(); for(B b : data) { if((Boolean)expression.eval(b)) { // 'b' passed, check which attributes to include List&lt;Object&gt; row = new ArrayList&lt;Object&gt;(); for(String attr : attributes) { row.add(b.getAttribute(attr)); } result.add(row); } } return result; } } </code></pre> <p>If you now generate the lexer, parser and tree walker and run the Main class:</p> <pre class="lang-none prettyprint-override"><code>java -cp antlr-3.3.jar org.antlr.Tool Select.g java -cp antlr-3.3.jar org.antlr.Tool SelectWalker.g javac -cp antlr-3.3.jar *.java java -cp .:antlr-3.3.jar Main </code></pre> <p>you will see that the output for the query: </p> <pre class="lang-none prettyprint-override"><code>select C, Y from poolX where X = 45 AND Y &gt; '88' AND Z != 'abc'; </code></pre> <p>with input:</p> <pre class="lang-none prettyprint-override"><code>C X Y Z "id_1" 345 "89" "abd" "id_2" 45 "89" "abd" "id_3" 1 "89" "abd" "id_4 45 "8" "abd" "id_5" 45 "89" "abc" "id_6" 45 "99" "abC" </code></pre> <p>is:</p> <pre class="lang-none prettyprint-override"><code>[id_2, 89] [id_6, 99] </code></pre> <p>And note that if the <code>where</code> statement is omitted, the expression <code>1 = 1</code> is automatically inserted, causing the query:</p> <pre class="lang-none prettyprint-override"><code>select C, Y from poolX; </code></pre> <p>to print the following:</p> <pre class="lang-none prettyprint-override"><code>[id_1, 89] [id_2, 89] [id_3, 89] [id_4, 8] [id_5, 89] [id_6, 99] </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload