// Version: 1.0
// ANTLR Version: 3.1.12
// Date: 2007-09-07
//
// Description: Grammar for the internal SQL select of ISQL
//
// =======================================================================================
// Author: Andrea Ferrandi 
// based on a MSSQL parser by Tomasz Jastrzebski 

grammar ISQLSelect;

options
{
output=AST;
backtrack=true;
}

@header
{
package isql;

import java.util.TreeMap;
}
@lexer::header
{
package isql;
}

@members
{
public static class TableSource
{
private String table;
private List<String> lstColumns = new ArrayList<String>(1);
public void addColumn(String column) { lstColumns.add(column); }
public List<String> getColumns() { return lstColumns; }
public String getTable() { return table; }
public void setTable(String table) { this.table = table; }
}

public static interface IConstant {}

public static class NumberConstant implements IConstant
{
private double number;
public NumberConstant(String sNumber) { this.number = Double.parseDouble(sNumber); }
public double getNumber() { return number; }
}

public static class StringConstant implements IConstant
{
private String text;
public StringConstant(String text) { this.text = text; }
public String getText() { return text; }
}

public static class DateConstant implements IConstant
{
private String date;
public DateConstant(String date) { this.date = date; }
public String getDate() { return date; }
}

public static class BooleanConstant implements IConstant
{
private boolean value;
public BooleanConstant(String booleanValue) { this.value = booleanValue.equalsIgnoreCase("true"); }
public boolean getValue() { return value; }
}


public static class Function
{
private String functionId;
private List<IExpression> lstParameter = new ArrayList<IExpression>(1);
public void setFunctionId(String functionId) { this.functionId = functionId; }
public void addParameter(IExpression parameter) { lstParameter.add(parameter); }
public String getFunctionId() { return functionId; }
public List<IExpression> getParameters() { return lstParameter; }
}

public static class SelectItem
{
private IExpression what;
private String alias;
public SelectItem(IExpression what, String alias) { this.what = what; this.alias = alias; }
public IExpression getWhat() { return what; }
public String getAlias() { return alias; }
}

public static class TableColumn
{
private String table;
private String column;
public TableColumn(String table, String column) { this.table = table; this.column = column; }
public String getTable() { return table; }
public String getColumn() { return column; }
public String toString() { return table+"."+column; }
}

public static class When
{
private TableColumn test;
private IConstant compared;
private String operator;
public When(TableColumn test, IConstant compared, String operator)
{
this.test = test;
this.compared = compared;
this.operator = operator;
}
public TableColumn getTest() { return test; }
public IConstant getCompared() { return compared; }
public String getOperator() { return operator; }
}

public static class WhenThen
{
private When when;
private IExpression then;
public WhenThen(When when, IExpression then)
{
this.when = when;
this.then = then;
}
public When getWhen() { return when; }
public IExpression getThen() { return then; }
}

public static class CaseFunction
{
private TableColumn test;
private List<WhenThen> lstWhenThen = new ArrayList<WhenThen>(1);
private IExpression expElse;
public void setTest(TableColumn test) { this.test = test; }
public void addWhenThen(WhenThen whenThen) { lstWhenThen.add(whenThen); }
public void setElse(IExpression expElse) { this.expElse = expElse; }
public TableColumn getTest() { return test; }
public List<WhenThen> getWhenThens() { return lstWhenThen; }
public IExpression getElse() { return expElse; }

}

public static interface IExpression {}


public static class SimpleExpression implements IExpression
{
private SubExpression sub;
public SimpleExpression(SubExpression sub) { this.sub = sub; }
public SubExpression getSubExpression() { return sub; }
}

public static class ComplexExpression implements IExpression
{
private IExpression a;
private IExpression b;
private String operator;
public ComplexExpression(IExpression a, IExpression b, String operator) 
{ 
this.a = a; 
this.b = b;
this.operator = operator;
}
public IExpression getA() { return a; }
public IExpression getB() { return b; }
public String getOperator() { return operator; }
}

public static abstract class SubExpression 
{
private String unaryOperator = null;
public void setUnaryOperator(String unaryOperator) { this.unaryOperator = unaryOperator; }
public String getUnaryOperator() { return unaryOperator; }
}

public static class ConstantSubExpression extends SubExpression
{
private IConstant constant;
public ConstantSubExpression(IConstant constant) { this.constant = constant; }
public IConstant getConstant() { return constant; }
}

public static class FunctionSubExpression extends SubExpression
{
private Function function;
public FunctionSubExpression(Function function) { this.function = function; }
public Function getFunction() { return function; }
}

public static class ExpressionSubExpression extends SubExpression
{
private IExpression expression;
public ExpressionSubExpression(IExpression expression) { this.expression = expression; }
public IExpression getExpression() { return expression; }
}

public static class TableColumnSubExpression extends SubExpression
{
private TableColumn tableColumn;
public TableColumnSubExpression(TableColumn tableColumn) { this.tableColumn = tableColumn; }
public TableColumn getTableColumn() { return tableColumn; }
}

public static class CaseSubExpression extends SubExpression
{
private CaseFunction caseFunction;
public CaseSubExpression(CaseFunction caseFunction) { this.caseFunction = caseFunction; }
public CaseFunction getCase() { return caseFunction; }
}

public static interface IPredicate {}

public static class Comparison implements IPredicate
{
private IExpression test;
private IExpression compared;
private String operator;
public Comparison(IExpression test, IExpression compared, String operator)
{
this.test = test;
this.compared = compared;
this.operator = operator;
}
public IExpression getTest() { return test; }
public IExpression getCompared() { return compared; }
public String getOperator() { return operator; }
}
public static abstract class NegatePredicate implements IPredicate
{
private boolean negated = false;
public void setNegated(boolean negated) { this.negated = negated; }
public boolean isNegated() { return negated; }
}

public static class Like extends NegatePredicate
{
private IExpression test;
private String compared;
public Like(IExpression test, String compared)
{
this.test = test;
if(compared != null)
this.compared = compared.substring(1, compared.length() - 1);
}
public IExpression getTest() { return test; }
public String getCompared() { return compared; }
}

public static class In extends NegatePredicate
{
private IExpression test;
private List<IConstant> lstConstants;
public In(IExpression test, List<IConstant> lstConstants)
{
this.test = test;
this.lstConstants = lstConstants;
}
public IExpression getTest() { return test; }
public List<IConstant> getConstants() { return lstConstants; }
}

public static interface ISearchCondition {}


public static class SimpleSearchCondition implements ISearchCondition
{
private SubSearchCondition sub;
public SimpleSearchCondition(SubSearchCondition sub) { this.sub = sub; }
public SubSearchCondition getSubSearchCondition() { return sub; }
}

public static class ComplexSearchCondition implements ISearchCondition
{
private ISearchCondition a;
private ISearchCondition b;
private String operator;
public ComplexSearchCondition(ISearchCondition a, ISearchCondition b, String operator) 
{ 
this.a = a; 
this.b = b;
this.operator = operator;
}
public ISearchCondition getA() { return a; }
public ISearchCondition getB() { return b; }
public String getOperator() { return operator; }
}

public static abstract class SubSearchCondition
{
private boolean negated;
public void setNegated(boolean negated) { this.negated = negated; }
public boolean getNegated() { return negated; }
}

public static class RecursiveCondition extends SubSearchCondition
{
private ISearchCondition condition;
public RecursiveCondition(ISearchCondition condition) { this.condition = condition; }
public ISearchCondition getSearchCondition() { return condition; }
}

public static class PredicateCondition extends SubSearchCondition
{
private IPredicate predicate;
public PredicateCondition(IPredicate predicate) { this.predicate = predicate; }
public IPredicate getPredicate() { return predicate; }
}

public static class OrderBy
{
private TableColumn orderBy;
private boolean ascendent = true;
public OrderBy(TableColumn orderBy)
{
this.orderBy = orderBy;
}
public TableColumn getOrderBy() { return orderBy; }
public void setAscendent(boolean ascendent) { this.ascendent = ascendent; }
public boolean isAscendent() { return ascendent; }
}


private Map<String, TableSource> mapTableSource = new TreeMap<String, TableSource>();
private void addTableSource(TableSource tableSource) { 
if(tableSource != null) 
{
String table = tableSource.getTable();
if(table != null)
mapTableSource.put(table, tableSource); 
}
}
public Map<String, TableSource> getTableSources() { return mapTableSource; }

private List<SelectItem> lstSelectItem = new ArrayList<SelectItem>(1);
private void addSelectItem(SelectItem item) { lstSelectItem.add(item); }
public List<SelectItem> getSelectItems() { return lstSelectItem; }

private List<TableColumn> lstGroupBy = new ArrayList<TableColumn>(1);
private void addGroupBy(TableColumn groupBy) { lstGroupBy.add(groupBy); }
public List<TableColumn> getGroupBys() { return lstGroupBy; }

private List<OrderBy> lstOrderBy = new ArrayList<OrderBy>(1);
private void addOrderBy(OrderBy orderBy) { lstOrderBy.add(orderBy); }
public List<OrderBy> getOrderBys() { return lstOrderBy; }

private ISearchCondition where;
public ISearchCondition getWhere() { return where; }

private boolean distinct;
public boolean isDistinct() { return distinct; }
}


// PARSER ********************************************************************************


// starting rule
statement
    : selectStatement EOF;
 
// select statement select ... from ... where ... group by ... order by ...
selectStatement
    :
    selectClause
    fromClause
    (whereClause)?
    (groupByClause)?
    (orderByClause)?
    ;

// select a,b,c ...        
selectClause
    :     
    'select' (selectHow=('all' | 'distinct') { distinct=($selectHow.text.equalsIgnoreCase("distinct")); } )? 
     selectList
    ;

// where a=b and ...
whereClause
    : 
    'where' searchCondition { where = $searchCondition.c; }
    ;

// order by a,b
orderByClause
    : 
    'order' 'by' first=orderByExpression { addOrderBy($first.o); }
    (COMMA other=orderByExpression  { addOrderBy($other.o); })*
    ;
    
orderByExpression returns [ OrderBy o]
    :	
    orderBy=tableColumn { $o = new OrderBy($orderBy.t); }
    (ascDesc=('asc' | 'desc') {  $o.setAscendent($ascDesc.text.equalsIgnoreCase("asc")); } ) ? 
;
// group by a,b

searchCondition returns [ISearchCondition c;] 
    : 
    first=subSearchCondition { $c = new SimpleSearchCondition($first.s); }
    ( operator=('and' | 'or') other=subSearchCondition 
    	{ $c = new ComplexSearchCondition($c, new SimpleSearchCondition($other.s), $operator.text); } )*
    ;
groupByClause
    : 
    'group' 'by' groupBy=tableColumn { addGroupBy($groupBy.t); }
    (COMMA tableColumn { addGroupBy($groupBy.t); })*
    ;

// a=b and c=d 


subSearchCondition returns [SubSearchCondition s; ] @init { boolean negated=false; }
    :
    ('not' { negated = true; })? (
          ((LPAREN searchCondition RPAREN ) => LPAREN recCondition=searchCondition RPAREN { $s = new RecursiveCondition($recCondition.c); })
        | (predCondition=predicate  { $s = new PredicateCondition($predCondition.p); })
        )
        { $s.setNegated(negated); }
;

// a=b    a like b   a in (c,d,e)	
predicate returns [IPredicate p]
    :
      test=whereExpression (
        comparisonOperator comparedExp=whereExpression { $p = new Comparison($test.e, $comparedExp.e, $comparisonOperator.text); }
      | 'like' comparedText=StringLiteral { $p = new Like($test.e, $comparedText.text); }
          | 'in' LPAREN (constantSequence) RPAREN  { $p = new In($test.e, $constantSequence.l);  }
    )
    ;
// c,d,e 
constantSequence returns [List<IConstant> l = new ArrayList<IConstant>(1);]
    :	
    first=constant {  $l.add($first.c); }
    (COMMA other=constant {  $l.add($other.c); })*
;
	    
selectList  options { k=3; } 
    : 
    item=selectItem { addSelectItem($item.i); } 
    ( COMMA  item=selectItem { addSelectItem($item.i); } )*
    ;

selectItem returns [SelectItem i]
    :
    what=selectExpression  'as'  alias=Identifier { $i = new SelectItem($what.e, $alias.text); }
    ;


// from [col1, col2, col3] table1, [col4, col5] table2
fromClause
    : 
    'from' fromTable=tableSource { addTableSource($fromTable.t); }  
    (COMMA fromTable=tableSource { addTableSource($fromTable.t);  }  )*
    ;
tableSource returns [  TableSource t =  new TableSource(); ]
    :     
    LSQUARE column=Identifier  { $t.addColumn($column.text); } 
    (COMMA column=Identifier  { $t.addColumn($column.text); })* 
    RSQUARE 'as' tableName=Identifier  { $t.setTable($tableName.text); }    
    ;


    
// table.column
tableColumn returns [ TableColumn t; ]
    : 
    table=Identifier DOT column=Identifier { $t = new TableColumn($table.text, $column.text); }
    ;

// 23 + 44 
selectExpression returns [IExpression e; ]
    : // current definition ignores operator precedence
      subA=selectSubExpression { $e = new SimpleExpression($subA.s); }
      (operator=binaryOperator subB=selectSubExpression { $e = new ComplexExpression($e, new SimpleExpression($subB.s), $operator.text); })*
    ;
// 
selectSubExpression returns [SubExpression s; ]   @init { String unop= null; }
     :    
    (unaryOperator { unop=$unaryOperator.text;  })?
    (
      constant { $s = new ConstantSubExpression($constant.c); }
    | function { $s = new FunctionSubExpression($function.f); }
    | LPAREN selectExpression RPAREN { $s = new ExpressionSubExpression($selectExpression.e); }
    | tableColumn  { $s = new TableColumnSubExpression($tableColumn.t); }
    | caseFunction { $s = new CaseSubExpression($caseFunction.c); }    
    )
    {$s.setUnaryOperator(unop); }
    ;
    
// 23 + 44 
whereExpression returns [IExpression e; ]
    : // current definition ignores operator precedence
      subA=whereSubExpression { $e = new SimpleExpression($subA.s); }
      (operator=binaryOperator subB=whereSubExpression { $e = new ComplexExpression($e, new SimpleExpression($subB.s), $operator.text); })*
    ;
// 
whereSubExpression returns [SubExpression s; ]   @init { String unop= null; }
     :    
    (unaryOperator { unop=$unaryOperator.text;  })?
    (
      constant { $s = new ConstantSubExpression($constant.c); }
    | LPAREN whereExpression RPAREN { $s = new ExpressionSubExpression($whereExpression.e); }
    | tableColumn  { $s = new TableColumnSubExpression($tableColumn.t); }
    )
    {$s.setUnaryOperator(unop); }
    ;
    

// fun(par1, par2, par3)
function returns [Function f = new Function(); ]
    : // LEFT and RIGHT keywords are also function names
    functionId=Identifier { $f.setFunctionId($functionId.text); }
    LPAREN (
          parameter=selectExpression  { $f.addParameter($parameter.e); }
          (COMMA parameter=selectExpression { $f.addParameter($parameter.e); })*
        )?
    RPAREN
    ;



constant returns [IConstant c]
    : 
    Number { $c = new NumberConstant($Number.text); }
    | StringLiteral { $c = new StringConstant($StringLiteral.text); }
    | DateLiteral { $c = new DateConstant($DateLiteral.text); }
    | booleanValue { $c = new BooleanConstant($booleanValue.text); }
    ;

caseWhen returns [When w; ]
    :	
    tableColumn comparisonOperator constant { $w = new When($tableColumn.t, $constant.c, $comparisonOperator.text); }
;
// case a 
// when b then c 
// when d then e
// else f
caseFunction returns [ CaseFunction c = new CaseFunction(); ]
    : 'case' tableColumn { $c.setTest($tableColumn.t); }
          ('when' caseWhen 'then' then=selectExpression { $c.addWhenThen(new WhenThen($caseWhen.w, $then.e)); }          
          )+
    ('else' elseExp=selectExpression {$c.setElse($elseExp.e); })? 
    'end'
    ;


unaryOperator
    : 
    MINUS | TILDE
    ;
    
binaryOperator
    : 
    arithmeticOperator | bitwiseOperator
    ;
    
arithmeticOperator
    : 
    PLUS | MINUS | STAR | DIVIDE | MOD
    ;

bitwiseOperator
    : 
    AMPERSAND | TILDE | BITWISEOR | BITWISEXOR
    ;

comparisonOperator
    :
    EQUAL | NOTEQUAL | LESSTHANOREQUALTO 
    | LESSTHAN | GREATERTHANOREQUALTO |  GREATERTHAN 
    ;
        
logicalOperator
    : 
    'all' | 'and' | 'any' | 'exists' | 'in' | 'like' | 'not' | 'or' | 'some'
    ;
    
booleanValue
    :
    'true' | 'false'	
    ;    	

    
// Operators

DOT : '.'; // generated as a part of Number rule
COLON : ':' ;
COMMA : ',' ;
SEMICOLON : ';' ;

LPAREN : '(' ;
RPAREN : ')' ;
LSQUARE : '[' ;
RSQUARE : ']' ;

EQUAL : '=' ;
NOTEQUAL : '<>' ;
LESSTHANOREQUALTO : '<=' ;
LESSTHAN : '<' ;
GREATERTHANOREQUALTO : '>=' ;
GREATERTHAN : '>' ;

DIVIDE : '/' ;
PLUS : '+' ;
MINUS : '-' ;
STAR : '*' ;
MOD : '%' ;

AMPERSAND : '&' ;
TILDE : '~' ;
BITWISEOR : '|' ;
BITWISEXOR : '^' ;


    
    
// LITERALS

fragment
Letter
    : 'a'..'z' | '_' 
    ;

fragment
Digit
    : 
    '0'..'9'
    ;

fragment
Exponent
    : 
    'e' ( PLUS|MINUS )? (Digit)+
    ;

StringLiteral
    :    
    '\'' (~'\'')* '\'' ( '\'' (~'\'')* '\'' )*
    ;

DateLiteral
    :    
    '#' (~'#')* '#' 
    ;




Number
    :
    (Digit)+ ( DOT (Digit)* (Exponent)? | Exponent)?
    ;

Identifier
    : 
   Letter (Letter | Digit)*
    ;

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