'CaseInsensitive' = "True";

BNF ::= Query;

Query ::= AlterStm
                | CreateStm
                | DeleteStm
                | DropStm
                | InsertStm
                | SelectStm
                | UpdateStm;
                
           
AlterStm   ::= "ALTER" "TABLE" Id "ADD" "COLUMN" FieldDefList ConstraintOpt
                | "ALTER" "TABLE" Id "ADD" Constraint
                | "ALTER" "TABLE" Id "DROP" "COLUMN" Id
                | "ALTER" "TABLE" Id "DROP" "CONSTRAINT" Id;

CreateStm  ::= "CREATE" Unique "INDEX" IntegerLiteral "ON" Id "(" OrderList ")" WithClause
                | "CREATE" "TABLE" Id "(" FieldDefList ")" ConstraintOpt;

Unique      ::= "UNIQUE"
                | NULL;

WithClause ::= "WITH" "PRIMARY"
                | "WITH" "DISALLOW" "NULL"
                | "WITH" "IGNORE" "NULL"
                | NULL;

FieldDef   ::= Id Type "NOT" "NULL"
                | Id Type;

FieldDefList ::= FieldDef "," FieldDefList
                   | FieldDef;

Type  ::=  "BIT"
          |  "DATE"
          |  "TIME"
          |  "TIMESTAMP"
          |  "DECIMAL"
          |  "REAL"
          |  "FLOAT"
          |  "SMALLINT"
          |  "INTEGER"
          |  "INTERVAL"
          |  "CHARACTER";

ConstraintOpt  ::= Constraint
                    | NULL;

Constraint      ::= "CONSTRAINT" Id ConstraintType
                    | "CONSTRAINT" Id;

ConstraintType ::= "PRIMARY" "KEY" "(" IdList ")"
                    | "UNIQUE"      "(" IdList ")"
                    | "NOT" "NULL"    "(" IdList ")"
                    | "FOREIGN" "KEY" "(" IdList ")" "REFERENCES" Id "(" IdList ")";

DropStm        ::= "DROP" "TABLE" Id
                    | "DROP" "INDEX" Id "ON" Id;


InsertStm    ::= "INSERT" "INTO" Id "(" IdList ")" SelectStm
                  | "INSERT" "INTO" Id "(" IdList ")" "VALUES" "(" ExprList ")";

UpdateStm    ::= "UPDATE" Id "SET" AssignList WhereClause;

AssignList   ::= Id "=" Expression "," AssignList
                  | Id "=" Expression;

DeleteStm    ::= "DELETE" "FROM" Id WhereClause;


SelectStm    ::= "SELECT" Columns IntoClause FromClause WhereClause GroupClause HavingClause OrderClause;

Columns      ::= Restriction "*"
                  | Restriction ColumnList;

ColumnList   ::= ColumnItem "," ColumnList
                  | ColumnItem;

ColumnItem   ::= ColumnSource
                  | ColumnSource Id;
                  
ColumnSource ::= Aggregate
                  | Id;

Restriction   ::= "ALL"
                  | "DISTINCT"
                  | NULL;

Aggregate     ::= "Count"  "(" "*" ")"
                  | "Count"  "(" Expression ")"
                  | "Avg"    "(" Expression ")"
                  | "Min"    "(" Expression ")"
                  | "Max"    "(" Expression ")"
                  | "StDev"  "(" Expression ")"
                  | "StDevP" "(" Expression ")"
                  | "Sum"    "(" Expression ")"
                  | "Var"    "(" Expression ")"
                  | "VarP"   "(" Expression ")";

IntoClause   ::= "INTO" Id
                  | NULL;

FromClause   ::= "FROM" IdList JoinChain;

JoinChain    ::= Join JoinChain
                  | NULL;

Join          ::= "INNER" "JOIN" IdList "ON" Id "=" Id                  
                  | "LEFT" "JOIN" IdList "ON" Id "=" Id                  
                  | "RIGHT" "JOIN" IdList "ON" Id "=" Id                  
                  | "JOIN" IdList "ON" Id "=" Id;


WhereClause  ::= "WHERE" Expression
                  | NULL;

GroupClause  ::= "GROUP" "BY" IdList
                  | NULL;

OrderClause  ::= "ORDER" "BY" OrderList
                  | NULL;

OrderList    ::= Id OrderType "," OrderList
                  | Id OrderType;

OrderType    ::= "ASC"
                  | "DESC"
                  | NULL;

HavingClause ::= "HAVING" Expression
                  | NULL;


Expression  ::= AndExp "OR" Expression
                | AndExp;

AndExp     ::= NotExp "AND" AndExp
                | NotExp;

NotExp     ::= "NOT" PredExp
                | PredExp;

PredExp    ::= AddExp "BETWEEN" AddExp "AND" AddExp
                | AddExp "NOT" "BETWEEN" AddExp "AND" AddExp
                | Value "IS" "NOT" "NULL"
                | Value "IS" "NULL"
                | AddExp "LIKE" StringLiteral
                | AddExp "IN" Tuple
                | AddExp "="  AddExp
                | AddExp "<>" AddExp
                | AddExp "!=" AddExp
                | AddExp ">"  AddExp
                | AddExp ">=" AddExp
                | AddExp "<"  AddExp
                | AddExp "<=" AddExp
                | AddExp;

AddExp     ::= AddExp "+" MultExp
                | AddExp "-" MultExp
                | MultExp;

MultExp    ::= MultExp "*" NegateExp 
                | MultExp "/" NegateExp
                | NegateExp;

NegateExp  ::= "-" Value
                | Value;
        
Value       ::= Tuple
                | Id
                | IntegerLiteral
                | RealLiteral
                | StringLiteral
                | "NULL";

Tuple       ::= "(" SelectStm ")"
                | "(" ExprList ")";

ExprList  ::= Expression "," ExprList
               | Expression;

IdList     ::= IdMember "," IdList
                | IdMember;

IdMember   ::= Id
                | Id Id;


{StringCh1}      = {Printable} - ["\""];
{StringCh2}      = {Printable} - ["'"];
{IdChStandard}   = {Alphanumeric} + ["_"];
{IdChExtended}   = {Printable} - ["["] - ["]"];

StringLiteral   = "\"" {StringCh1}* "\"" | "'" {StringCh2} *"'";
IntegerLiteral  = {Digit}+;
RealLiteral     = {Digit}+ "." {Digit}+;
Id             = ({Letter}{IdChStandard}* | "[" {IdChExtended}+ "]")  ("."({Letter}{IdChStandard}* | "["{IdChExtended}+"]"))?;