README.md

ParseSql

Fetch column, table info from sql without requests.

Usage:

var columns = new List<ParseSql.ColumnInfo>();
var parseSuccess = ParseSql.ParseColumnInfos(providerName, sql, ref columns);

if (!parseSuccess)
{
    Console.ForegroundColor = ConsoleColor.Red;
    Console.Error.WriteLine("SqlSample gsp parse error: {0}", ParseSql.ParseError);
    Console.ResetColor();
    Console.Error.WriteLine("SqlSample:\n" + sql + "\n");

    continue;
}

ParseSql.cs

using System.Collections.Generic;
using System.Linq;
using System.Diagnostics;
using gudusoft.gsqlparser;
using System;

public static class ParseSql
{
    public class ColumnInfo
    {
        public string tableName { get; set; } = string.Empty;
        public string tableAlias { get; set; } = string.Empty;
        public string columnName { get; set; } = string.Empty;
    }

    public static string ParseError { get; set; } = string.Empty;

    public static bool ParseColumnInfos(string providerName, string sql, ref List<ColumnInfo> columns)
    {
        EDbVendor eDbVendor = providerName switch
        {
            "sqlite" => EDbVendor.dbvgeneric,
            "sqlserver" => EDbVendor.dbvmssql,
            _ => throw new ArgumentOutOfRangeException(nameof(providerName), "unknown provider " + providerName)
        };

        // Parse Table.Column notation from sql
        TGSqlParser sqlparser = new TGSqlParser(eDbVendor);
        sqlparser.sqltext = sql;
        int pec = sqlparser.parse();
        if (pec != 0)
        {
            ParseError = sqlparser.Errormessage;

            return false;
        }

        // TODO Если ни алиаса ни названия таблицы не указано, то можно определить таблицу, если это поле уникально
        // TODO Если указана звезда в SELECT, то брать из парсера таблицы и все поля через dataSchema

        const int state_find_select = 1;
        const int state_acc_column = 2;
        const int state_acc_table = 3;

        int state = state_find_select;

        // var columns = new List<ColumnInfo>();
        var cc = new ColumnInfo(); // current column
        var tables = new List<(string name, string alias)>();
        (string name, string alias) ct = (name: "", alias: ""); // current alias
        string[] StopWords = { "where", "order", "group", "having", "limit", "offset" }; // add more if needed
        bool openTableAcc = true;
        bool wasJoin = false;
        foreach (var t in sqlparser.sourcetokenlist)
        {
            var tt = (TSourceToken)t;

            if (tt.astext == "*")
            {
                ParseError = "asterisk unsupported";
                return false;
            }

            if (state == state_find_select)
            {
                if (tt.astext?.ToLower() == "select")
                    state = state_acc_column;

                continue;
            }

            else if (state == state_acc_column)
            {
                if (tt.astext?.ToLower() == "from")
                {
                    if (!string.IsNullOrEmpty(cc.columnName))
                        columns.Add(cc);
                    state = state_acc_table;

                    continue;
                }

                else if (tt.astext == ",")
                {
                    if (!string.IsNullOrEmpty(cc.columnName))
                        columns.Add(cc);
                    cc = new ColumnInfo();

                    continue;
                }

                else if (tt.DbObjType == EDbObjectType.column)
                {
                    cc.columnName = tt.astext ?? string.Empty;
                }

                else if (tt.DbObjType == EDbObjectType.table)
                {
                    cc.tableName = tt.astext ?? string.Empty;
                }

                else if (tt.DbObjType == EDbObjectType.table_alias)
                {
                    cc.tableAlias = tt.astext ?? string.Empty;
                }
            }

            else if (state == state_acc_table)
            {
                if (!openTableAcc)
                {
                    if (tt.astext?.ToLower() == "join")
                    {
                        wasJoin = true;
                        openTableAcc = true;
                    }
                }
                else
                {
                    if (tt.DbObjType == EDbObjectType.table)
                    {
                        ct = (name: tt.astext, alias: "");
                    }

                    else if (tt.DbObjType == EDbObjectType.table_alias)
                    {
                        ct.alias = tt.astext;
                    }

                    else if (tt.astext == "," || tt.astext?.ToLower() == "join")
                    {
                        if (!string.IsNullOrEmpty(ct.name))
                        {
                            tables.Add(ct);
                            ct = (name: "", alias: "");
                        }
                    }

                    if (tt.astext?.ToLower() == "join")
                        wasJoin = true;

                    if (wasJoin && tt.astext?.ToLower() == "on")
                        openTableAcc = false;
                }

                if (tt.astext != null && StopWords.Contains(tt.astext.ToLower()))
                {
                    if (!string.IsNullOrEmpty(ct.name))
                    {
                        tables.Add(ct);
                        ct = (name: "", alias: "");
                    }
                    break;
                }
            }
        }

        // resolve tableName in columns
        foreach (var c in columns)
        {
            if (!string.IsNullOrEmpty(c.tableName))
                continue;

            Debug.Assert(!string.IsNullOrEmpty(c.tableAlias));
            var table = tables.First(t => t.alias == c.tableAlias);
            c.tableName = table.name;
        }

        return true;
    }

}