+
 / \
1   *
   / \
  2   3

dotnet add package Antlr4.Runtime.Standard

grammar Arithmetic; // grammar name 需要和文件名一致
// 語(yǔ)法規(guī)則
// op=('*'|'/') 表示 op 將 ‘*' 或者 ‘/' 標(biāo)記為一個(gè)操作符號(hào)
// # MulDiv 將這個(gè)規(guī)則命名為 MulDiv，訪問 AST 時(shí)會(huì)用到
expr:   expr op=('*'|'/') expr   # MulDiv
    |   expr op=('+'|'-') expr   # AddSub
    |   INT                      # Int
    |   '(' expr ')'             # Parens
    ;
// 詞法規(guī)則
INT :   [0-9]+ ;
WS  :   [ \t\r\n]+ -> skip ; // 表示忽略空格

java -jar antlr-4.13.2-complete.jar -Dlanguage=CSharp Arithmetic.g4

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net9.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="Antlr4.Runtime.Standard" Version="4.13.1"/>
        <PackageReference Include="Antlr4BuildTasks" Version="12.8.0" PrivateAssets="all"/>
    </ItemGroup>
    <ItemGroup>
        <Antlr4 Include="**\*.g4"/>
    </ItemGroup>
</Project>

public class ArithmeticVisitor : ArithmeticBaseVisitor<int>
{
    // 解析乘除法
    public override int VisitMulDiv(ArithmeticParser.MulDivContext context)
    {
        // context 包含了當(dāng)前節(jié)點(diǎn)的信息
        // context.expr(0) 和 context.expr(1) 分別表示乘除法的兩個(gè)操作數(shù)
        // 訪問子節(jié)點(diǎn)，獲取操作數(shù)的值
        int left = Visit(context.expr(0));
        int right = Visit(context.expr(1));
        return context.op.Text switch
        {
            "*" => left * right,
            "/" => left / right,
            _ => throw new NotSupportedException($"Operator {context.op.Text} is not supported.")
        };
    }
    // 解析加減法
    public override int VisitAddSub(ArithmeticParser.AddSubContext context)
    {
        int left = Visit(context.expr(0));
        int right = Visit(context.expr(1));
        return context.op.Text switch
        {
            "+" => left + right,
            "-" => left - right,
            _ => throw new NotSupportedException($"Operator {context.op.Text} is not supported.")
        };
    }
    // 去掉括號(hào)，訪問括號(hào)內(nèi)的表達(dá)式
    public override int VisitParens(ArithmeticParser.ParensContext context) => Visit(context.expr());
    // 解析整數(shù)
    public override int VisitInt(ArithmeticParser.IntContext context) => int.Parse(context.INT().GetText());
}

Console.WriteLine(Evaluate("1 + 2 * 3")); // 7
Console.WriteLine(Evaluate("(1 + 2) * 3")); // 9
int Evaluate(string expression)
{
    // 創(chuàng)建詞法分析器
    var lexer = new ArithmeticLexer(new AntlrInputStream(expression));
    var tokens = new CommonTokenStream(lexer);
    // 創(chuàng)建語(yǔ)法分析器，傳入詞法分析器的輸出的token流
    var parser = new ArithmeticParser(tokens);
    // 用 visitor 模式解析表達(dá)式
    var visitor = new ArithmeticVisitor();
    return visitor.Visit(parser.expr());
}

public class ArithmeticListener : ArithmeticBaseListener
{
    // 使用棧來(lái)存儲(chǔ)操作數(shù)
    private readonly Stack<int> _stack = new();
    public int Result => _stack.Pop();
    public override void ExitMulDiv(ArithmeticParser.MulDivContext context)
    {
        int right = _stack.Pop();
        int left = _stack.Pop();
        int result = context.op.Text switch
        {
            "*" => left * right,
            "/" => left / right,
            _ => throw new NotSupportedException($"Operator {context.op.Text} is not supported.")
        };
        _stack.Push(result);
    }
    public override void ExitAddSub(ArithmeticParser.AddSubContext context)
    {
        int right = _stack.Pop();
        int left = _stack.Pop();
        int result = context.op.Text switch
        {
            "+" => left + right,
            "-" => left - right,
            _ => throw new NotSupportedException($"Operator {context.op.Text} is not supported.")
        };
        _stack.Push(result);
    }
    public override void ExitParens(ArithmeticParser.ParensContext context)
    {
        // ExitParens 方法在這里不需要做任何操作，因?yàn)槲覀円呀?jīng)在 MulDiv 和 AddSub 中處理了括號(hào)內(nèi)的表達(dá)式
    }
    public override void ExitInt(ArithmeticParser.IntContext context)
    {
        int value = int.Parse(context.INT().GetText());
        _stack.Push(value);
    }
}

Console.WriteLine(Evaluate("1 + 2 * 3")); // 7
Console.WriteLine(Evaluate("(1 + 2) * 3")); // 9
int Evaluate(string expression)
{
    // 創(chuàng)建詞法分析器
    var lexer = new ArithmeticLexer(new AntlrInputStream(expression));
    var tokens = new CommonTokenStream(lexer);
    // 創(chuàng)建語(yǔ)法分析器，傳入詞法分析器的輸出的token流
    var parser = new ArithmeticParser(tokens);
    var listener = new ArithmeticListener();
    // 解析表達(dá)式
    parser.AddParseListener(listener);
    parser.expr();
    // 獲取結(jié)果
    return listener.Result;
}

lexer grammar SqlLexer;
options {
  caseInsensitive = true; // 忽略大小寫
}
// 關(guān)鍵字
SELECT : 'SELECT' ;
FROM   : 'FROM' ;
WHERE  : 'WHERE' ;
ORDER  : 'ORDER' ;
BY     : 'BY'  ;
ASC    : 'ASC' ;
DESC   : 'DESC' ;
AND    : 'AND' ;
OR     : 'OR' ;
COMMA  : ',' ;
STAR   : '*'  ;
// 運(yùn)算符
EQ     : '=' ;
NEQ    : '!=' ;
LIKE   : 'LIKE' ;
// 字面量
STRING_LITERAL
    : '\'' ( ~('\'' | '\\') | '\\' . )* '\'' // 字符串字面量
    ;
// 標(biāo)識(shí)符
IDENTIFIER
    : [a-z_][a-z0-9_]* // 用于表名、列名等
    ;
WS     : [ \t\r\n]+ -> skip ; // 忽略空格

parser grammar SqlParser;
options { tokenVocab=SqlLexer; }
query
    : SELECT selectList FROM tableName (WHERE whereClause)? (ORDER BY orderByClause)?
    ;
selectList
    : columnName (COMMA columnName)* 
    | STAR
    ;
columnName: IDENTIFIER ;
tableName: IDENTIFIER ;
whereClause
    : whereCondition (AND whereCondition)*
    ;
whereCondition
    : columnName op=(EQ | NEQ) STRING_LITERAL 
    | columnName op=LIKE STRING_LITERAL
    ;
orderByClause
    : orderByCondition (COMMA orderByCondition)*
    ;
orderByCondition
    : columnName (ASC | DESC)?
    ;

public abstract class Expression;
public class QueryExpression : Expression
{
    public required string TableName { get; init; }
    public required bool SelectAll { get; init; }
    public required IEnumerable<string> SelectList { get; init; }
    public required IEnumerable<WhereCondition> WhereConditions { get; init; }
    public required IEnumerable<OrderByCondition> OrderByConditions { get; init; }
}
public class WhereCondition : Expression
{
    public required string ColumnName { get; init; }
    public WhereConditionOperator Operator { get; init; }
    public required string Value { get; init; }
}
public enum WhereConditionOperator
{
    Equal,
    NotEqual,
    StartsWith,
    EndsWith,
    Contains
}
public class OrderByCondition : Expression
{
    public required string ColumnName { get;  init; }
    public  bool IsDescending { get; init; } 
}

public class SqlAstBuilder : SqlParserBaseVisitor<QueryExpression>
{
    public override QueryExpression VisitQuery(SqlParser.QueryContext context)
    {
        var selectList = context.selectList();
        bool selectAll = selectList?.STAR() != null;
        var columns = selectList?.columnName()
            .Select(c => c.GetText())
            .ToList() ?? [];
        var tableName = context.tableName().GetText();
        var whereConditions = context.whereClause()
            ?.whereCondition()
            .Select(c =>
            {
                var stringValue = c.STRING_LITERAL().GetText().Trim('\'');
                var opText = c.op.Text.ToUpperInvariant();
                var op = WhereConditionOperator.Equal;
                if (opText == "=")
                {
                    op = WhereConditionOperator.Equal;
                }
                else if (opText == "!=")
                {
                    op = WhereConditionOperator.NotEqual;
                }
                else if (opText == "LIKE")
                {
                    if (stringValue.StartsWith("%") && stringValue.EndsWith("%"))
                    {
                        op = WhereConditionOperator.Contains;
                        stringValue = stringValue.Substring(1, stringValue.Length - 2);
                    }
                    else if (stringValue.StartsWith("%"))
                    {
                        op = WhereConditionOperator.EndsWith;
                        stringValue = stringValue.Substring(1);
                    }
                    else if (stringValue.EndsWith("%"))
                    {
                        op = WhereConditionOperator.StartsWith;
                        stringValue = stringValue.Substring(0, stringValue.Length - 1);
                    }
                }
                else
                {
                    throw new NotSupportedException($"Operator {c.op.Text} is not supported.");
                }
                return new WhereCondition
                {
                    ColumnName = c.columnName().GetText(),
                    Operator = op,
                    Value = stringValue
                };
            })
            .ToList() ?? [];
        var orderByConditions = context.orderByClause()
            ?.orderByCondition()
            .Select(c => new OrderByCondition
            {
                ColumnName = c.columnName().GetText(),
                IsDescending = c.DESC() != null
            })
            .ToList() ?? [];
        return new QueryExpression
        {
            SelectAll = selectAll,
            SelectList = columns,
            TableName = tableName,
            WhereConditions = whereConditions,
            OrderByConditions = orderByConditions
        };
    }
}

public class SqlToCsvEngine(DirectoryInfo csvDirectory)
{
    public IEnumerable<Dictionary<string, string>> ExecuteQuery(string query)
    {
        // 創(chuàng)建詞法分析器
        var lexer = new SqlLexer(new AntlrInputStream(query));
        // 創(chuàng)建語(yǔ)法分析器，傳入詞法分析器的輸出的token流
        var tokens = new CommonTokenStream(lexer);
        var parser = new SqlParser(tokens);
        // 將查詢語(yǔ)句解析為自定義的 AST
        var astBuilder = new SqlAstBuilder();
        var expression = astBuilder.Visit(parser.query());
        // 處理 AST，執(zhí)行查詢
        if (expression is not { } queryExpression)
        {
            throw new InvalidOperationException("Expected a query expression");
        }
        // 讀取 CSV 文件
        var csvData = ReadCsv(queryExpression.TableName);
        // 過濾數(shù)據(jù)
        var filteredData = csvData.Where(row =>
        {
            // 處理 WHERE 條件
            // 處理 WHERE 條件
            bool isMatch = true;
            foreach (var condition in queryExpression.WhereConditions)
            {
                if (row.TryGetValue(condition.ColumnName, out var value))
                {
                    isMatch = condition.Operator switch
                    {
                        WhereConditionOperator.Equal => value == condition.Value,
                        WhereConditionOperator.NotEqual => value != condition.Value,
                        WhereConditionOperator.StartsWith => value.StartsWith(condition.Value),
                        WhereConditionOperator.EndsWith => value.EndsWith(condition.Value),
                        WhereConditionOperator.Contains => value.Contains(condition.Value),
                        _ => throw new ArgumentOutOfRangeException()
                    };
                }
                else
                {
                    throw new InvalidOperationException($"Column {condition.ColumnName} does not exist in CSV file.");
                }
            }
            return isMatch;
        });
        // 處理 ORDER BY 條件
        foreach (var orderByCondition in queryExpression.OrderByConditions)
        {
            Func<IEnumerable<Dictionary<string, string>>, Func<Dictionary<string, string>, string>,
                IEnumerable<Dictionary<string, string>>> orderByFunc;
            if (filteredData is IOrderedEnumerable<Dictionary<string, string>> orderedData)
            {
                orderByFunc = orderByCondition.IsDescending
                    ? (_, keySelector) => orderedData.ThenByDescending(keySelector)
                    : (_, keySelector) => orderedData.ThenBy(keySelector);
            }
            else
            {
                orderByFunc = orderByCondition.IsDescending
                    ? Enumerable.OrderByDescending
                    : Enumerable.OrderBy;
            }
            filteredData = orderByFunc(filteredData, row =>
            {
                if (row.TryGetValue(orderByCondition.ColumnName, out var value))
                {
                    return value;
                }
                throw new InvalidOperationException(
                    $"Order by column {orderByCondition.ColumnName} does not exist in CSV file.");
            });
        }
        // 處理 SELECT 條件
        if (queryExpression.SelectAll)
        {
            return filteredData;
        }
        var selectedData = filteredData.Select(row =>
        {
            var selectedRow = new Dictionary<string, string>();
            foreach (var columnName in queryExpression.SelectList)
            {
                if (row.TryGetValue(columnName, out var value))
                {
                    selectedRow[columnName] = value;
                }
                else
                {
                    throw new InvalidOperationException($"Column {columnName} does not exist in CSV file.");
                }
            }
            return selectedRow;
        });
        return selectedData;
    }
    private IEnumerable<Dictionary<string, string>> ReadCsv(string tableName)
    {
        var csvFile = new FileInfo(Path.Combine(csvDirectory.FullName, $"{tableName}.csv"));
        if (!csvFile.Exists)
        {
            throw new FileNotFoundException($"CSV file {csvFile.FullName} does not exist.");
        }
        using var reader = new StreamReader(csvFile.FullName);
        var headerLine = reader.ReadLine();
        if (headerLine == null)
        {
            throw new InvalidOperationException($"CSV file {csvFile.FullName} is empty.");
        }
        var headers = headerLine.Split(',');
        while (!reader.EndOfStream)
        {
            var line = reader.ReadLine();
            if (line == null) continue;
            var values = line.Split(',');
            yield return headers.Zip(values).ToDictionary(x => x.First, x => x.Second);
        }
    }
}

Name,City,Occupation,Company
Alice,New York,Engineer,TechCorp
Bob,Los Angeles,Designer,Creative Inc
Ben,Atlanta,Writer,Publishing House
Charlie,Chicago,Manager,Finance Group
David,Houston,Teacher,School District
Eve,Miami,Student,University
Frank,Seattle,Chef,Restaurant Co
Grace,San Francisco,Doctor,HealthCare
Hannah,Boston,Lawyer,Legal Partners
Ian,Denver,Architect,BuildIt

var directory = new DirectoryInfo("/Users/hkh/Desktop/test");
var engine = new SqlToCsvEngine(directory);
var sql =
  """
  SELECT Name, City, Occupation, Company
  FROM Employee
  WHERE City != 'Miami'
    AND Occupation LIKE '%er'
  ORDER BY Name ASC, Company DESC
  """;
  var result = engine.ExecuteQuery(sql);
// 打印頭部
foreach (var column in result.First().Keys)
{
    Console.Write($"{column}\t");
}
foreach (var row in result)
{
    Console.WriteLine();
    foreach (var (_, value) in row)
    {
        Console.Write($"{value}\t");
    }
}