Saturday, July 6, 2013

DSL for generating SQL through ANTLR

Here I present a very simple example for creating a DSL.
As an example say you create a web app for allowing people to post projects and people to contribuute to.
So as a search people can pass in a DSL from a web page and that will be translated to SQL in the background.

I used ANTLR4 for generating the artifacts

Steps
Create the grammer
grammar Projects;


// PARSER
program : ((expression) ';')+;



expression
    : 'find all projects' # all
| 'find' ent ' with ' ID SIGN comp # points
;
ent : 'projects'|'contributor';
comp : ID|INT;
arg : ID|STRING;

// LEXER

STRING : '"' (' '..'~')* '"';
ID     : ('a'..'z'|'A'..'Z')+;
INT    : '0'..'9'+;
SIGN   : ('>'|'<'|'='|'<='|'>=')+;


WS     : [ \t\n\r]+ -> skip ;

Right now I am just putting 2 commands for listing all projects and the other can be like
find projects with contributor='shashank';
or
find projects with points>=100;

This is a very elementry grammer for now but can be extended very easily.

as a 2nd step this grammer is fed to ANTLR tool for generating the parsers .

Finally we make use of the listener pattern which gets invoked as the AST is walked down by the parser.

Putting the snippet here

import java.util.HashMap;
import java.util.Map;
import java.util.Stack;
public class MyProjectListener  extends ProjectBaseListener
{
  public Stack<Integer>       stack = new Stack<Integer>();
  public Map<String, Integer> sym   = new HashMap<String, Integer>();

 

  public void exitPoints(ProjectParser.PointsContext ctx)
  {
    //sym.put(ctx.INT().getText(), stack.pop());
System.out.println("Entity="+ctx.ent().getText());
System.out.println("Field to be compared"+ctx.ID().getText());
System.out.println("sign="+ctx.SIGN().getText());
System.out.println("Value"+ctx.comp().getText());

System.out.println("Select "+ctx.ent().getText()+" from "+ctx.ent().getText()+"s where "+ctx.ID().getText()+" "+ctx.SIGN().getText()+""+ctx.comp().getText());



  }
 
 
}

  This the code which generates the sql commands based on parsing of the dsl command we feed in

Finally we create a sample client like this


import org.antlr.v4.runtime.*;
import org.antlr.v4.runtime.tree.*;
import java.io.*;
public class TestSensor {
public static void main(String[] args) throws Exception {
// create a CharStream that reads from standard input
ANTLRInputStream input = new ANTLRInputStream(new FileInputStream("code.txt"));
// create a lexer that feeds off of input CharStream
ProjectLexer lexer = new ProjectLexer(input);
// create a buffer of tokens pulled from the lexer
CommonTokenStream tokens = new CommonTokenStream(lexer);
// create a parser that feeds off the tokens buffer
ProjectParser parser = new ProjectParser(tokens);
parser.setBuildParseTree(true);
parser.addParseListener(new MyProjectListener());
ParseTree tree = parser.program(); // begin parsing at init rule

System.out.println(tree.toStringTree(parser)); // print LISP-style tree
}
}

code.txt is nothing but a file containing the command

as an example find contributor with points>=100;

This results in SQL like

Select contributor from contributors where points >=100

This is a very simple example I have put forward but like I stated the whole thing can be extended very easily .
Cheers

Shashank