CSharp to PL/SQL 3

Posted by Aaron Feng Sun, 15 Apr 2007 01:25:00 GMT

UPDATED: Part 2 of this translation has been added.

After reading the ANTLR book, I decided to build a simple translator that will recognize C# like syntax and output PL/SQL. Even though I heard about ANTLR two years ago, I have not kept up with it, so I decided to start out simple. More complicated translator often involves creating an Abstract Syntrax Tree (AST), and a tree parser that will walk the tree during translation. In my translator, I will only use token rewrite and String Template in order to keep it simple. Rewrite allows you to "rewrite" the input token to something else. String Template is a template engine that will be used to specify the overall structure of the output.

The input language will look like the following:

public class Users {
    public int UserId;
    public string FirstName;
    public string LastName;
}

The output will be a script to create a Users table with 3 columns defined in the Users class. It will also create a sequence and a trigger on UserId column to auto increment the value on insert. Before creating any objects in the database it will check for the existence and drop them if they already exist.

The grammar used to generate lexer and parser is shown below (CSharpSQL.g):

grammar CSharpSQL;

options {
 output = template;
 rewrite = true;
}

@members {
  String className;
  List columns = new ArrayList();
}

// parser
program : declaration+ -> translate(
 name = { className }, 
 id = { className.substring(0,className.length() - 1) + columns.get(0) }, 
 columns = { columns } 
) ;

declaration : class_statment '{' (variable_statment)* '}' ;


class_statment : scope_modifier 'class' ID
{  
  className = $ID.text; 
} ;

variable_statment : scope_modifier type ID  ';'  
{    
  String tmp = $ID.text;
  if(tmp.toLowerCase().equals("id"))
   tmp = className.substring(0, className.length() - 1) + tmp;
  columns.add(tmp + " " + $type.text + " NOT NULL");
} ;

scope_modifier : 'public' ;

type : 'string' -> template() "nvarchar(255)"
     | 'int' -> template() "integer"
     | 'decimal' -> template() "number(21,6)"
     | 'DateTime' -> template() "date" ;
     // add more types if needed

// lexar
ID  :   ('a'..'z'|'A'..'Z'|'_') ('a'..'z'|'A'..'Z'|'0'..'9'|'_') * ;

WS  :   ( ' ' | '\t' | '\r' | '\n' )+ { $channel = HIDDEN; } ;

The grammar used here is a combined grammar which means it contains the rule for the lexer and parser. It uses a couple of member variables to remember matched tokens, so it can be passed to the template for the final generation.

The template used for the generation looks like the following (CSharpSQL.stg):

group CSharpSQL;

translate(name, id, columns) ::= <<
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE <name> CASCADE CONSTRAINTS PURGE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE <name>_SEQ';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

CREATE TABLE <name>
(
  <columns; separator=",\n">
)
/

CREATE SEQUENCE <name>_SEQ
        START WITH 1
        INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER <name>_TR
BEFORE INSERT ON <name>
FOR EACH ROW
DECLARE TEMP_NO int;
 BEGIN
     SELECT <name>_SEQ.NEXTVAL INTO :NEW.<id>Id FROM DUAL;
     SELECT <name>_SEQ.CURRVAL INTO GLOBALPKG.IDENTITY FROM DUAL;
END;
/
>>

The recognizer can be tested with this test class (Test.java):

import org.antlr.runtime.*;
import org.antlr.stringtemplate.*;
import java.io.*;

public class Test {
 public static void main(String[] args) throws Exception {
  FileReader groupFileReader = new FileReader("CSharpSQL.stg");
  StringTemplateGroup templates = new StringTemplateGroup(groupFileReader);
  groupFileReader.close();

  ANTLRInputStream input = new ANTLRInputStream(System.in);
  CSharpSQLLexer lexer = new CSharpSQLLexer(input);

  TokenRewriteStream tokens = new TokenRewriteStream(lexer);

  CSharpSQLParser parser = new CSharpSQLParser(tokens);
  parser.setTemplateLib(templates);

  CSharpSQLParser.program_return r = parser.program();
  StringTemplate output = r.getTemplate();
  System.out.println(output.toString());
 }
}

The language I defined here is fairly limited, but it can be expanded if needed. The output can be easily retargeted to a different database if a different template is specified. The reason I created this translator is because writing PL/SQL can be a tedious task, and error prone. With a simple translator, it can do most of the work for me.

Comments

Leave a response

  1. Adnan Thu, 09 Aug 2007 12:33:43 GMT

    Hello i was going thru your article. Very helpful. I am learning ANTLR these days and planning to make a SQL type language for my application. the thing which I am not able to grasp that how can I fetch the values from my expression. For INstance, there is a query

    Select objModule from repository where @type='music'

    Now after successful parsing, I have ti pick values like "objModule", and 'music'.

    How can I extract values and where should I define my function within parser?

    Thanks

  2. Aaron Feng Fri, 10 Aug 2007 02:28:35 GMT

    I'm not sure what you mean by "extract values". What exactly are you trying to accomplish?

    Each rule in the parser becomes a function generated by ANTLR. You can just add the code to a rule. Reuse that rule if you want common code some where else in your grammar.

  3. Bovlk Sat, 24 Nov 2007 16:41:02 GMT

    Hello, I solved my problem in the meantime. It really does not work with ANTLR 3.0.1 but works fine with ANTLR 3.0

Comments