Irony mysql解析模板

模板可以解析大部分sql，自测通过。在其原有的demo基础上修改的。Irony源码地址:https://github.com/IronyProject/Irony

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Irony.Parsing;

namespace Irony.Samples.SQL {
  // Loosely based on SQL89 grammar from Gold parser. Supports some extra TSQL constructs.

  [Language("SQL", "89", "SQL 89 grammar")]
  public class SqlGrammar : Grammar {
    public SqlGrammar() : base(false) { //SQL is case insensitive
      //Terminals
      var comment = new CommentTerminal("comment", "/*", "*/");
      var lineComment = new CommentTerminal("line_comment", "--", "\n", "\r\n");
      NonGrammarTerminals.Add(comment);
      NonGrammarTerminals.Add(lineComment);
      var number = new NumberLiteral("number");
      var string_literal = new StringLiteral("string", "'", StringOptions.AllowsDoubledQuote);
      var Id_simple = TerminalFactory.CreateSqlExtIdentifier(this, "id_simple"); //covers normal identifiers (abc) and quoted id's ([abc d], "abc d")
      var comma = ToTerm(",");
      var dot = ToTerm(".");
      var star = ToTerm("*");
      var CREATE = ToTerm("CREATE"); 
      var NULL = ToTerm("NULL");
      var NOT = ToTerm("NOT");
      var UNIQUE = ToTerm("UNIQUE"); 
      var WITH = ToTerm("WITH");
      var TABLE = ToTerm("TABLE"); 
      var ALTER = ToTerm("ALTER"); 
      var ADD = ToTerm("ADD"); 
      var COLUMN = ToTerm("COLUMN"); 
      var DROP = ToTerm("DROP"); 
      var CONSTRAINT = ToTerm("CONSTRAINT");
      var INDEX = ToTerm("INDEX"); 
      var ON = ToTerm("ON");
      var KEY = ToTerm("KEY");
      var PRIMARY = ToTerm("PRIMARY"); 
      var INSERT = ToTerm("INSERT");
      var INTO = ToTerm("INTO");
      var UPDATE = ToTerm("UPDATE");
      var SET = ToTerm("SET"); 
      var VALUES = ToTerm("VALUES");
      var DELETE = ToTerm("DELETE");
      var SELECT = ToTerm("SELECT"); 
      var FROM = ToTerm("FROM");
      var AS = ToTerm("AS");
      var COUNT = ToTerm("COUNT");
      var JOIN = ToTerm("JOIN");
      var BY = ToTerm("BY");
      var @case = ToTerm("Case");
      var when = ToTerm("When");
      var then = ToTerm("then");
      var @if = ToTerm("if");
      var @else = ToTerm("else");
      var end = ToTerm("end");
      #region define
      //Non-terminals
      var Id = new NonTerminal("Id");
      var stmt = new NonTerminal("stmt");
      var createTableStmt = new NonTerminal("createTableStmt");
      var createIndexStmt = new NonTerminal("createIndexStmt");
      var alterStmt = new NonTerminal("alterStmt");
      var dropTableStmt = new NonTerminal("dropTableStmt");
      var dropIndexStmt = new NonTerminal("dropIndexStmt");
      var selectStmt = new NonTerminal("selectStmt");
      var insertStmt = new NonTerminal("insertStmt");
      var updateStmt = new NonTerminal("updateStmt");
      var deleteStmt = new NonTerminal("deleteStmt");
      var fieldDef = new NonTerminal("fieldDef");
      var fieldDefList = new NonTerminal("fieldDefList");
      var nullSpecOpt = new NonTerminal("nullSpecOpt");
      var typeName = new NonTerminal("typeName"); 
      var typeSpec = new NonTerminal("typeSpec");
      var typeParamsOpt = new NonTerminal("typeParams");
      var constraintDef = new NonTerminal("constraintDef");
      var constraintListOpt = new NonTerminal("constraintListOpt");
      var constraintTypeOpt = new NonTerminal("constraintTypeOpt");
      var idlist = new NonTerminal("idlist"); 
      var idlistPar = new NonTerminal("idlistPar"); 
      var uniqueOpt = new NonTerminal("uniqueOpt");
      var orderList = new NonTerminal("orderList");
      var orderMember = new NonTerminal("orderMember"); 
      var orderDirOpt = new NonTerminal("orderDirOpt");
      var withClauseOpt = new NonTerminal("withClauseOpt");
      var alterCmd = new NonTerminal("alterCmd");
      var insertData = new NonTerminal("insertData"); 
      var intoOpt = new NonTerminal("intoOpt");
      var assignList = new NonTerminal("assignList");
      var whereClauseOpt = new NonTerminal("whereClauseOpt");
      var assignment = new NonTerminal("assignment");
      var expression = new NonTerminal("expression");
      var exprList = new NonTerminal("exprList");
      var selRestrOpt = new NonTerminal("selRestrOpt");
      var selList = new NonTerminal("selList");
      var intoClauseOpt = new NonTerminal("intoClauseOpt");
      var fromClauseOpt = new NonTerminal("fromClauseOpt");
      var groupClauseOpt = new NonTerminal("groupClauseOpt");
      var havingClauseOpt = new NonTerminal("havingClauseOpt");
      var orderClauseOpt = new NonTerminal("orderClauseOpt");
      //
      var selectClauseOpt = new NonTerminal("selectClauseOpt");
      var pageOpt = new NonTerminal("pageOpt");
      var sysFunc = new NonTerminal("sysFunc");
      var sysFuncArg = new NonTerminal("sysFuncArg");
      var sysFuncArgList = new NonTerminal("sysFuncArgList");
      var sysFuncName = new NonTerminal("sysFuncName");
      var casewhenClauseOpt = new NonTerminal("casewhenClauseOpt");
      // var sysFuncOpt = new NonTerminal("sysFuncOpt");

      var columnItemList = new NonTerminal("columnItemList");
      var columnItem = new NonTerminal("columnItem");
      var columnSource = new NonTerminal("columnSource");
      var asOpt = new NonTerminal("asOpt");
      var aliasOpt = new NonTerminal("aliasOpt");
      var aggregate = new NonTerminal("aggregate");
      var aggregateArg = new NonTerminal("aggregateArg");
      var aggregateName = new NonTerminal("aggregateName");
      var tuple = new NonTerminal("tuple");
      var joinChainOpt = new NonTerminal("joinChainOpt");
      var joinKindOpt = new NonTerminal("joinKindOpt");
      var term = new NonTerminal("term");
      var unExpr = new NonTerminal("unExpr");
      var unOp = new NonTerminal("unOp");
      var binExpr = new NonTerminal("binExpr");
      var binOp = new NonTerminal("binOp");
      var betweenExpr = new NonTerminal("betweenExpr");
      var inExpr = new NonTerminal("inExpr");
      var parSelectStmt = new NonTerminal("parSelectStmt");
      var notOpt = new NonTerminal("notOpt");
      var funCall = new NonTerminal("funCall");
      var stmtLine = new NonTerminal("stmtLine");
      var semiOpt = new NonTerminal("semiOpt");
      var stmtList = new NonTerminal("stmtList");
      var funArgs = new NonTerminal("funArgs");
      var inStmt = new NonTerminal("inStmt");
#endregion

      //BNF Rules
      this.Root = stmtList;
      stmtLine.Rule = stmt + semiOpt;
      semiOpt.Rule = Empty | ";";
      stmtList.Rule = MakePlusRule(stmtList, stmtLine);

      // 巴科斯范式（Backus-Naur Form）BnfExpression
      //引号中的字符表示这些他们本身，quote表示引号
      //引号外的字符表示语法。
      //<> 尖括号中的内容表示必选项
      //[] 方括号中的内容表示可选项
      //{ }大括号中的内容表示可重复0到无数次的项
      //   | 竖线表示左右任选一项
      //:= 冒号等于时被定义为的意思
      // 例如：
      // <pp>:=<tt>|<tt><pp>

      //ID
      Id.Rule = MakePlusRule(Id, dot, Id_simple)| MakePlusRule(Id, dot, star)|Id_simple+Id_simple|selectClauseOpt|casewhenClauseOpt;

      stmt.Rule = createTableStmt | createIndexStmt | alterStmt 
                | dropTableStmt | dropIndexStmt 
                | selectStmt | insertStmt | updateStmt | deleteStmt
                | "GO" ;
      //Create table
      #region createsql
      createTableStmt.Rule = CREATE + TABLE + Id + "(" + fieldDefList + ")" + constraintListOpt;
      fieldDefList.Rule = MakePlusRule(fieldDefList, comma, fieldDef);
      fieldDef.Rule = Id + typeName + typeParamsOpt + nullSpecOpt;
      nullSpecOpt.Rule = NULL | NOT + NULL | Empty;
      typeName.Rule = ToTerm("BIT") | "DATE" | "TIME" | "TIMESTAMP" | "DECIMAL" | "REAL" | "FLOAT" | "SMALLINT" | "INTEGER"
                                   /*| "INTERVAL" */| "CHARACTER"
                                   // MS SQL types:  
                                   | "DATETIME" | "INT" | "DOUBLE" | "CHAR" | "NCHAR" | "VARCHAR" | "NVARCHAR"
                                   | "IMAGE" | "TEXT" | "NTEXT";
      typeParamsOpt.Rule = "(" + number + ")" | "(" + number + comma + number + ")" | Empty;
      constraintDef.Rule = CONSTRAINT + Id + constraintTypeOpt;
      constraintListOpt.Rule = MakeStarRule(constraintListOpt, constraintDef );
      constraintTypeOpt.Rule = PRIMARY + KEY + idlistPar | UNIQUE + idlistPar | NOT + NULL + idlistPar
                             | "Foreign" + KEY + idlistPar + "References" + Id + idlistPar;
      idlistPar.Rule = "(" + idlist + ")";
      idlist.Rule = MakePlusRule(idlist, comma, Id);
      #endregion
      #region othersql
      //Create Index
      createIndexStmt.Rule = CREATE + uniqueOpt + INDEX + Id + ON + Id + orderList + withClauseOpt;
      uniqueOpt.Rule = Empty | UNIQUE;
      orderList.Rule = MakePlusRule(orderList, comma, orderMember);
      orderMember.Rule = Id + orderDirOpt;
      orderDirOpt.Rule = Empty | "ASC" | "DESC";
      withClauseOpt.Rule = Empty | WITH + PRIMARY | WITH + "Disallow" + NULL | WITH + "Ignore" + NULL;

      //Alter 
      alterStmt.Rule = ALTER + TABLE + Id + alterCmd;
      alterCmd.Rule = ADD + COLUMN  + fieldDefList + constraintListOpt 
                    | ADD + constraintDef
                    | DROP + COLUMN + Id
                    | DROP + CONSTRAINT + Id;

      //Drop stmts
      dropTableStmt.Rule = DROP + TABLE + Id;
      dropIndexStmt.Rule = DROP + INDEX + Id + ON + Id; 

      //Insert stmt
      insertStmt.Rule = INSERT + intoOpt + Id + idlistPar + insertData;
      insertData.Rule = selectStmt | VALUES + "(" + exprList + ")"; 
      intoOpt.Rule = Empty | INTO; //Into is optional in MSSQL

      //Update stmt
      updateStmt.Rule = UPDATE + Id + SET + assignList + whereClauseOpt;
      assignList.Rule = MakePlusRule(assignList, comma, assignment);
      assignment.Rule = Id + "=" + expression;

      //Delete stmt
      deleteStmt.Rule = DELETE + FROM + Id + whereClauseOpt;
      #endregion

      #region selectsql
      //Select stmt
      selectStmt.Rule = SELECT + selRestrOpt + selList + intoClauseOpt + fromClauseOpt + whereClauseOpt +
                        groupClauseOpt + havingClauseOpt + orderClauseOpt+pageOpt;
      selRestrOpt.Rule = Empty | "ALL" | "DISTINCT";
      selList.Rule = columnItemList | "*";
      // comma:逗号
      columnItemList.Rule = MakePlusRule(columnItemList, comma, columnItem);
      // alias:别名
      // columnItem匹配的是 xxx as xx
      // 问题：怎样匹配 xx.*?
      columnItem.Rule = columnSource + aliasOpt|columnItem+comma+ columnSource + aliasOpt;
      aliasOpt.Rule = Empty | asOpt + Id|Id; 
      asOpt.Rule = Empty | AS;
      columnSource.Rule = aggregate | Id|selectClauseOpt|sysFunc|expression;
      // aggregate:聚合
      aggregate.Rule = aggregateName + "(" + aggregateArg + ")" 
        | aggregateName + "(" + aggregateArg + ")"+binOp+number;
      aggregateArg.Rule = expression | "*"|"distinct"+Id; 
      aggregateName.Rule = COUNT | "Avg" | "Min" | "Max" | "StDev" | "StDevP" | "Sum" | "Var" | "VarP";
      //
      var whenthenClause = new NonTerminal("whenthenClause");
      var whenthenItem = new NonTerminal("whenthenItem");
      whenthenItem.Rule = when + expression + then + term;
      whenthenClause.Rule = MakePlusRule(whenthenClause, whenthenItem);
      // Empty| when + expression + then + term| whenthenClause + when + expression + then + term;

      sysFunc.Rule = sysFuncName + "(" + sysFuncArgList + sysFuncArg + ")"
        | sysFuncName + "(" + sysFuncArgList + "'%Y-%m-%d'" + ")" | sysFuncName + "(" + sysFuncArgList + ")"
        | sysFuncName + number + term;
        // | sysFuncName + "(" + sysFuncArg+comma+expression + ")"
        ;// "date_format(, '%Y-%m-%d')";
      sysFuncArg.Rule = expression | "*";
      sysFuncArgList.Rule = Empty | MakePlusRule(sysFuncArgList, comma, sysFuncArg);// sysFuncArg + comma | sysFuncArgList+ sysFuncArg + comma;

      sysFuncName.Rule = COUNT|"date_format" | "GROUP_CONCAT"| "CONCAT"| "ROUND"
        |"if"| "unix_timestamp"|"date_sub"| "CURDATE"|"interval";
      casewhenClauseOpt.Rule = Empty | @case+ whenthenClause + @else+expression+ end
        | @case +expression+ whenthenClause + @else + expression + end;

      intoClauseOpt.Rule = Empty | INTO + Id;
      fromClauseOpt.Rule = Empty | FROM + columnItem + joinChainOpt; 

      joinChainOpt.Rule = Empty | joinKindOpt + JOIN + idlist + ON + Id + "=" + Id;
      // 添加字段 joinChainOpt
      var someClauseOpt = new NonTerminal("someClauseOpt");
      someClauseOpt.Rule = expression;// Id + "=" + Id| someClauseOpt+"and"+ Id + "=" + Id | someClauseOpt + "&&" + Id + "=" + Id;
      // 添加字段 joinChainOpt
      joinChainOpt.Rule =  Empty | joinChainOpt+joinKindOpt + JOIN + selectClauseOpt + ON + someClauseOpt | joinChainOpt+joinKindOpt + JOIN + idlist + ON + someClauseOpt;

      // joinChainOpt.Rule = Empty | joinChainOpt+ joinKindOpt + JOIN + selectClauseOpt + ON + Id + "=" + Id| joinChainOpt + joinKindOpt + JOIN + idlist + ON + Id + "=" + Id;
      joinKindOpt.Rule = Empty | "INNER" | "LEFT" | "RIGHT"| joinKindOpt+"OUTER";
      whereClauseOpt.Rule = Empty | "WHERE" + expression;
      groupClauseOpt.Rule = Empty | "GROUP" + BY + idlist;
      havingClauseOpt.Rule = Empty | "HAVING" + expression; 
      orderClauseOpt.Rule = Empty | "ORDER" + BY + orderList;
      selectClauseOpt.Rule =  "(" + selectStmt.Rule + ")"+ aliasOpt ;
      pageOpt.Rule = Empty | "limit" + number | "limit" + number + comma + number;

#endregion

      //Expression
      exprList.Rule = MakePlusRule(exprList, comma, expression);
      expression.Rule = term | unExpr | binExpr| betweenExpr| sysFunc; //-- BETWEEN doesn't work - yet; brings a few parsing conflicts 
      term.Rule = Id | string_literal | number | funCall | tuple | parSelectStmt;// | inStmt;
      tuple.Rule = "(" + exprList + ")";
      parSelectStmt.Rule = "(" + selectStmt + ")"; 
      unExpr.Rule = unOp + term;
      unOp.Rule = NOT | "+" | "-" | "~"; 
      binExpr.Rule = expression + binOp + expression;
      binOp.Rule = ToTerm("+") | "-" | "*" | "/" | "%" //arithmetic
                 | "&" | "|" | "^" |"&&"|"||"                    //bit
                 | "=" | ">" | "<" | ">=" | "<=" | "<>" | "!=" | "!<" | "!>"
                 | "AND" | "OR" | "LIKE" | NOT + "LIKE" | "IN" | NOT + "IN" |ToTerm("is")| ToTerm("is")+NOT; 
      betweenExpr.Rule = expression + notOpt + "BETWEEN" + expression + "AND" + expression;
      notOpt.Rule = Empty | NOT;
      //funCall covers some psedo-operators and special forms like ANY(...), SOME(...), ALL(...), EXISTS(...), IN(...)
      funCall.Rule = Id + "(" + funArgs  + ")";
      funArgs.Rule = selectStmt | exprList;
      inStmt.Rule = expression + "IN" + "(" + exprList + ")";

      //Operators
      RegisterOperators(10, "*", "/", "%"); 
      RegisterOperators(9, "+", "-");
      RegisterOperators(8, "=", ">", "<", ">=", "<=", "<>", "!=", "!<", "!>", "LIKE", "IN");
      RegisterOperators(7, "^", "&", "|","||","&&");
      RegisterOperators(6, NOT); 
      RegisterOperators(5, "AND");
      RegisterOperators(4, "OR");

      MarkPunctuation(",", "(", ")");
      MarkPunctuation(asOpt, semiOpt);
      //Note: we cannot declare binOp as transient because it includes operators "NOT LIKE", "NOT IN" consisting of two tokens. 
      // Transient non-terminals cannot have more than one non-punctuation child nodes.
      // Instead, we set flag InheritPrecedence on binOp , so that it inherits precedence value from it's children, and this precedence is used
      // in conflict resolution when binOp node is sitting on the stack
      base.MarkTransient(stmt, term, asOpt, aliasOpt, stmtLine, expression, unOp, tuple);
      binOp.SetFlag(TermFlags.InheritPrecedence); 

    }//constructor

  }//class
}//namespace