LoginSignup
2
2
@yaju(やじゅ)

【.NET】SQL構文解析ライブラリーのTSQL Parserを使用する 応用編

Last updated at Posted at 2022-08-07

はじめに

前記事ではTSQL Parserを使用して、PostgreSQL 9.6とPostgreSQL 12で性能検証をしてみたいという段階で、TSQL Parserの紹介程度でした。

その後、実際にTSQL Parserを活用してPostgreSQL 9.6とPostgreSQL 12で性能検証用のプログラムを作成しました。
前記事から1年程過ぎてしまったのですが、久しぶりに記事にLGTMを頂いたので公開用にクラス化してみました。

BaseSQLParserクラス

TSQL Parserを活用した性能検証用のプログラムの一部を公開用にクラス化したもの。

TSQL Parser

SQLの構文解析プログラムです。NuGetで見つけることが出来ます。

何をするものか

20近くの既存アプリケーションのSQLを抜き出して、PostgreSQL 9.6とPostgreSQL 12で性能検証をする作業がありました。
ただ、1000近くのSQLがあるためプログラムを組んで自動で性能検証をしたいと考えました。
自動でやるためには、登録・更新・削除するデータをDB上から抜き出し、該当のSQLに抽出条件や登録・更新データのパラメーターとして当てはめて実行したいのです。

使用例

下記のようなSQLがあった場合、DB上に既にあるデータ（例えば性能検証用なのでテーブルの件数の中央のデータ）を取得して、クエリパラメータに当て嵌めたSQLを出力します。別のプログラムで出力したSQLをPostgreSQL 9.6とPostgreSQL 12で5回繰り返して平均を確認します。

insert into
    stafflist
values
    (
        :id,
        :name,
        (
            select
                name
            from
                address
            where
                id = :id
        )
    )

ソースコード

【2022/08/14追記】
元にしたソースコードが古かったので差し替えました。

BaseSQLParser.cs
using System;
using System.Collections.Generic;
using System.Text;
using TSQL;
using TSQL.Statements;
using TSQL.Tokens;

/// <summary>
/// パースルールクラス
/// </summary>
public class ParseRule
{
    public string TableName { get; set; }
    public string Field { get; set; }
    public string Operator { get; set; }
    public string FieldValue { get; set; }
    public string FieldGroup { get; set; }
    public bool IsDone { get; set; }
}

/// <summary>
/// SQLパース基底クラス
/// </summary>
public class BaseSQLParser
{
    public const string PLACEHOLDER = "@";

    private List<ParseRule> _ruleList = new List<ParseRule>();
    private string _subTableName = "";

    /// <summary>
    /// パラメーターに値をセットしたSQLを生成
    /// </summary>
    /// <param name="sql">対象SQL</param>
    /// <param name="message">例外メッセージ</param>
    /// <returns>true : 正常 / False : 異常</returns>
    public bool GenerateSetParamater(ref string sql, ref string message)
    {
        bool result = false;

        try
        {
            List<TSQLStatement> sqlStatementList = TSQLStatementReader.ParseStatements(sql);
            TSQLStatement statement = sqlStatementList[0];

            _ruleList.Clear();
            result = GenerateSetParamaterSub("", statement);
            if (!result) return false;

            StringBuilder sb = new StringBuilder();
            int i = 0;
            foreach (TSQLToken token in statement.Tokens)
            {
                string str = token.Text;
                if (token.Type == TSQLTokenType.Variable)
                {
                    ParseRule rule = _ruleList[i++];
                    str = rule.FieldValue;
                }
                sb.AppendLine(str);
            }
            sql = sb.ToString();
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }

        return result;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="table">テーブル名</param>
    /// <param name="statement">ステートメント</param>
    /// <returns>true : 正常 / False : 異常</returns>
    private bool GenerateSetParamaterSub(string table, TSQLStatement statement)
    {
        // パラメーターが無ければ、そのまま返す
        bool hasVariable = false;
        foreach (TSQLToken token in statement.Tokens)
        {
            if (token.Type == TSQLTokenType.Variable)
            {
                hasVariable = true;
                break;
            }
        }
        if (!hasVariable) return true;

        switch (statement.Type)
        {
            case TSQLStatementType.Select:
                GetSQLSelectParse(statement);
                break;
            case TSQLStatementType.Insert:
                GetSQLInsertParse(statement);
                break;
            case TSQLStatementType.Update:
                GetSQLUpdateParse(statement);
                break;
            case TSQLStatementType.Delete:
                GetSQLDeleteParse(statement);
                break;
        }

        // 解析中のSQLによる実行処理
        SQLProcess(table, _ruleList, statement);

        return true;
    }

    /// <summary>
    /// 解析中のSQLによる実行処理
    /// </summary>
    /// <param name="table">テーブル名</param>
    /// <param name="rules">パースルール</param>
    /// <param name="statement">ステートメント</param>
    public virtual void SQLProcess(string table, List<ParseRule> rules, TSQLStatement statement)
    {

    }

    /// <summary>
    /// SELECTステートメント解析処理
    /// </summary>
    /// <param name="statement">SQLステートメント</param>
    private void GetSQLSelectParse(TSQLStatement statement)
    {
        TSQLSelectStatement select = statement as TSQLSelectStatement;
        string table = "";

        if (select.With != null)
        {
            GetSQLParse(ref table, select.With.Tokens);
        }

        if (select.Select != null)
        {
            GetSQLParse(ref table, select.Select.Tokens);
        }

        if (select.From != null)
        {
            GetSQLParse(ref table, select.From.Tokens);
        }

        if (select.Where != null)
        {
            GetSQLParse(ref table, select.Where.Tokens);
        }
        if (select.SetOperators.Count > 0)
        {
            // UNION/EXCEPT前のパラメーターの値をセット
            SQLProcess(table, _ruleList, statement);

            // UNION/EXCEPT後のSQL解析
            GetSQLSelectParse(select.SetOperators[0].Select);
        }
    }

    /// <summary>
    /// INSERTステートメント解析処理
    /// </summary>
    /// <param name="statement">SQLステートメント</param>
    private void GetSQLInsertParse(TSQLStatement statement)
    {
        TSQLInsertStatement insert = statement as TSQLInsertStatement;

        string table = "";
        foreach (TSQLToken token in insert.Insert.Tokens)
        {
            switch (token.Type)
            {
                case TSQLTokenType.Identifier:
                    if (table == "") table = token.Text;
                    break;
            }
            if (table != "") break;
        }

        if (insert.Values != null)
        {
            GetSQLParse(ref table, insert.Values.Tokens);
        }
        if (insert.Select != null)
        {
            GetSQLParse(ref table, insert.Select.Select.Tokens);
            if (_ruleList.Count > 0)
            {
                SQLProcess(table, _ruleList, insert.Select);
            }

            GetSQLSelectParse(insert.Select);
        }
    }

    /// <summary>
    /// UPDATEステートメント解析処理
    /// </summary>
    /// <param name="statement">SQLステートメント</param>
    private void GetSQLUpdateParse(TSQLStatement statement)
    {
        TSQLUpdateStatement update = statement as TSQLUpdateStatement;

        string table = "";
        foreach (TSQLToken token in update.Update.Tokens)
        {
            switch (token.Type)
            {
                case TSQLTokenType.Identifier:
                    if (table == "") table = token.Text;
                    break;
            }
            if (table != "") break;
        }

        if (update.Set != null)
        {
            GetSQLParse(ref table, update.Set.Tokens);
        }

        if (update.Where != null)
        {
            GetSQLParse(ref table, update.Where.Tokens);
        }
    }

    /// <summary>
    /// DELETEステートメント解析処理
    /// </summary>
    /// <param name="statement">SQLステートメント</param>
    private void GetSQLDeleteParse(TSQLStatement statement)
    {
        TSQLDeleteStatement delete = statement as TSQLDeleteStatement;

        string table = "";
        foreach (TSQLToken token in delete.From.Tokens)
        {
            switch (token.Type)
            {
                case TSQLTokenType.Identifier:
                    if (table == "") table = token.Text;
                    break;
            }
            if (table != "") break;
        }

        if (delete.Where != null)
        {
            GetSQLParse(ref table, delete.Where.Tokens);
        }
    }

    /// <summary>
    /// SQLパース処理
    /// </summary>
    /// <param name="table">テーブル名</param>
    /// <param name="tokenList">トークンリスト</param>
    private void GetSQLParse(ref string table, List<TSQLToken> tokenList)
    {
        ParseRule ruleValue = new ParseRule();
        string beforeToken = "";
        string lastbeforeToken = "";
        string beforeTable = "";
        TSQLTokenType beforType = TSQLTokenType.Keyword;
        int beginPosition = 0;
        Stack<string> stack = null;
        StringBuilder sb = new StringBuilder();
        bool isFrom = false;

        foreach (TSQLToken token in tokenList)
        {
            if (token == null) continue;
            switch (token.Type)
            {
                case TSQLTokenType.Keyword:
                    if (beforeToken == "(" && token.Text.ToUpper() == "SELECT")
                    {
                        if (stack == null)
                        {
                            stack = new Stack<string>();
                            stack.Push("(");
                            beginPosition = token.BeginPosition;
                            beforeTable = table;
                        }
                        else
                            isFrom = true;
                    }
                    if (token.Text.ToUpper() == "LIKE")
                        ruleValue.Operator = token.Text.ToUpper();
                    break;
                case TSQLTokenType.Identifier:
                    if (beforeToken.ToUpper() == "FROM")
                    {
                        table = token.Text;
                        _subTableName = table;
                    }
                    else
                    {
                        if (lastbeforeToken == table && beforeToken == ".")
                        {
                            table += "." + token.Text;
                            _subTableName = table;
                        }
                    }
                    break;
                case TSQLTokenType.Character:
                    if (stack == null) break;

                    if (token.Text == "(")
                        stack.Push(")");
                    if (token.Text == ")")
                        stack.Pop();

                    if (stack.Count == 0)
                    {
                        if (isFrom)
                            sb.AppendLine(token.Text);

                        // サブクエリー処理
                        string subSql = sb.ToString();
                        sb.Clear();
                        List<TSQLStatement> sqlStatementList = TSQLStatementReader.ParseStatements(subSql);
                        GenerateSetParamaterSub(table, sqlStatementList[0]);
                        stack = null;
                        table = beforeTable;
                    }
                    break;
                case TSQLTokenType.Variable:
                    if (stack != null) break;

                    string field = token.Text.Replace(PLACEHOLDER, "");
                    if (field.IndexOf("I_") == 0)
                    {
                        if (_ruleList.Exists(x => x.Field == field))
                            break;
                    }

                    ruleValue.TableName = (table != "" ? table : _subTableName);
                    ruleValue.Field = field;
                    ruleValue.IsDone = false;
                    ruleValue.FieldGroup = tokenList[0].Text;
                    _ruleList.Add(ruleValue);
                    ruleValue = new ParseRule();
                    break;
                case TSQLTokenType.StringLiteral:
                case TSQLTokenType.NumericLiteral:
                    break;
            }

            // サブクエリーを格納
            if (stack != null)
                sb.AppendLine(token.Text);

            // トークンを記憶
            lastbeforeToken = beforeToken;
            beforeToken = token.Text;
            beforType = token.Type;
        }
    }
}

使用方法

NpgSQLParserクラス

BaseSQLParserクラスには、解析中のSQLによる実行処理（SQLProcess）仮想メソッドにしているので、BaseSQLParserを継承してSQLProcessメソッドの内部を書き換えます。
クエリパラメーターが含まれていた場合のみSQLProcessメソッドが呼ばれます。
ruleListにクエリパラメーターが入ってきます。

今回SQLの例
ruleList[0].Field = "id"
ruleList[0].FieldGroup = null
ruleList[0].TableName = "stafflist"
ruleList[1].Field = "name"
ruleList[1].FieldGroup = null
ruleList[1].TableName = "stafflist"
ruleList[2].Field = "id"
ruleList[2].FieldGroup = "where"
ruleList[2].TableName = "address"

今回の例ではクエリパラメーターに固定文字列をそのままセットしています。
ここの部分で本来はPostgreSQLを参照して、適切なクエリパラメーターにセットするようにします。

NpgSQLParser
public class NpgSQLParser : BaseSQLParser
{
    public override void SQLProcess(string table, List<ParseRule> ruleList, TSQLStatement statement) 
    {
        foreach (ParseRule rule in ruleList)
        {
            if (rule.TableName == table || !rule.IsDone)
            {
                switch (rule.TableName)
                {
                    case "stafflist":
                        string value = "";
                        string col = rule.Field;
                        switch (col)
                        {
                            case "id":
                                value = "1";
                                break;
                            case "name":
                                value = "'鈴木 一郎'";
                                break;
                        }
                        rule.FieldValue = value;
                        rule.IsDone = true;
                        break;
                    case "address":
                        Random rnd = new Random();
                        rule.FieldValue = rnd.Next(1, 48).ToString();
                        rule.IsDone = true;
                        break;
                }
            }
        }
    }
}

呼び出し処理

PostgreSQLのクエリパラメーターのプレースホルダーはコロン(:)記号ですが、TSQL ParserはSQLServer用でプレースホルダーはアットマーク(@)記号となっているため置き換えます。時刻の書式または型変換にあるコロン(:)記号はそのままにしたいので元に戻します。

あと、出力結果用SQL整形にはベクターにあった「FreeView」を使用しています。
NuGetだといいSQLFormatterが見つからなかったんですよね。
オンライン上やVSCode拡張とかなら、良さげなSQLFormatterはあるのですが・・・

frmMain.cs
private void button1_Click(object sender, EventArgs e)
{
    string targetSql = @"
insert into
    stafflist
values
    (
        :id,
        :name,
        (
            select
                name
            from
                address
            where
                id = :id
        )
    )";

    NpgSQLParser sqlParser = new NpgSQLParser();
    string message = "";
    string sql = targetSql.Replace(":", "@").Trim();
    // 時間のコロン記号は元に戻す
    sql = sql.Replace("hh24@mi", "hh24;mi");
    sql = sql.Replace("HH24@MI", "HH24;MI");
    sql = sql.Replace("mi@ss", "mi:ss");
    sql = sql.Replace("MI@SS", "MI:SS");
    // 型変換のコロン記号は元に戻す
    sql = sql.Replace("@@", "::");
    bool isOK = sqlParser.GenerateSetParamater(ref sql, ref message, false);

    // SQL整形
    FreeView.Sql.SqlRule rule = new FreeView.Sql.SqlRule();
    FreeView.Sql.SqlFormatter formatter = new FreeView.Sql.SqlFormatter(rule);
    string formattedSQL = formatter.Format(sql);
    Console.WriteLine(formattedSQL);
}

出力結果

insert into
    stafflist
values
    (
        1,
        '鈴木 一郎',
        (
            select
                name
            from
                address
            where
                id = 23
        )
    )

最後に

これを使用して自動で性能検証用のSQL出力がある程度は出来たのですが、さすがに複雑なSQLでは無理だったので、その部分は手動で値を埋め込みました。

新人の社員研修として

応用でSQL整形プログラムを作ることも出来ます。新人の社員研修でチームを組んでSQL整形プログラムを作らせたら面白いと思いました。SQL整形ってカラムを1行ずつ出力したり、ある程度までカラムを横並びにしたりとか、前カンマや後カンマとかフォーマットパターンが幾つかありますからね。チームによって癖が出てきていいかなと。いいSQLフォーマットが出来ればそのまま会社で使えるしね。

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2