💎

Carbunqlを用いた動的SQLの生成

2023/03/06に公開

はじめに

静的なSQL文を加工し、引数に応じて動的にSQL文を生成する手法についての考察と、「Carbunql」というSQL加工ライブラリについての紹介記事です。

動的SQLが必要になるケース

よくあるケースは以下の通りです。

任意項目が多数ある検索画面、レポート画面
ETLなどのバッチ処理

なお、ここでいう動的SQLの定義は以下とします。

ベースとなるSQLは存在している
引数によって検索条件やソート条件を改変する必要がある

一般的な解法

動的なSQLを生成する方法は様々あり、一長一短あります。

全パラメータ記述法（静的SQL）

そもそも論として静的SQLでカバーする方法を紹介します。

var sql = @"select * from table where 
case when :param is null then true else val = :param end";

SQLクライアント上でパラメータの全パターンをテスト可能です。

問題点

SQLの検索条件が複雑になるため、適切な実行計画が採用されないことがあります。
この問題点を解決する方法が、不要な検索条件を記述しない動的SQLとなります。

文字列連結法

SQLは文字列なので文字列加工で対応することができます。

var sql = @"select a.a_id, a.val from table as a
where 1 = 1";
if (param != null) sql += "and a.val = :param";

非常にシンプルかつ、楽な実装です。

問題点

条件が複雑になるとコードの視認性が悪くなります。
また、「検索条件を末尾に足す」という前提があるため、WHERE句以降の処理（ORDER句など）があると面倒です。

文字列置換法

独自のプレースホルダを置換することで任意の場所に検索条件を差し込みする方法です。

var sql = @"select a.a_id, a.val from table as a
where 1 = 1 /*dynamic_condition*/
order by id";
var cnd = string.Empty;
if (parameter != null) cnd = "and a.val = :param";
if (!string.IsNullOrEmpty(cnd)) sql = sql.Replace("/*dynamic_condition*/", cnd);

文字列連結方法の問題点である「末尾にしか足せない」問題が解決されます。
SQLコメントを使用した解法であるため、SQL単体としても成立します。

問題点

ほぼありません。

強いて言うなら「検索条件文字列を生成する処理の汎用性が低い」程度です。
動的SQLの生成処理が多数ある場合、専用の加工処理が必要となります。

番外編　2WaySQL

SQLコメントに独自の構文を定義、専用のSQLパーサーを通してSQLを生成する方法です。

SQLコメントを置換することでSQL単体としても成立し、保守性も維持できます。
分岐式を記述できたりもします。
最近は見かけないです。

問題点

専用のパーサーが必要で、デファクトスタンダードと言われるライブラリがありません。
現状ではメジャーなSQLクライアントがパラメータプレースホルダに対応しているので、再流行することは難しいと思います。

SQLパース法

専用のパーサーを通してSQLをオブジェクト化し加工する方法です。

using Carbunql.Building;
using Carbunql.Values;

//sql parse
var sq = new SelectQuery(@"select a.a_id, a.val from table as a");

//get FROM table
var a = sq.FromClause!.Root;

//add WHERE clause
sq.Where(a, "val").Equal(":prm");

こちらが本記事のメインととなります。次項にて詳細に説明します。

Carbunqlとは

C#用SLECTクエリ加工ライブラリです。
SQLをオブジェクト化し、加工を加え、SQLに書き戻すことができます。
オブジェクト化しているため、高度な加工を行うことが可能です。

特徴

DBMSを使用しません。DB接続文字列も不要です。
SELECTクエリ文字列だけあれば使用可能です。

SELECTクエリなら何でもパースできることを目標としており、CTE、サブクエリ、VALUES句のパースに対応しています。

もしパースできない事例があれば情報提供ください。改良を検討します。（利用頻度が低いもの、DBMS固有のものなどは期待に添えないこともあります）

制限事項

パースができるのはSELECTクエリだけです
SQLに記述したコメント文は除去されます
SQL実行機能はありません

SELECTクエリ以外（INSERTクエリ、UPDATEクエリなど）はパース対象外です。

コメント文が除去されるためヒント句のあるSELECTクエリの加工には不向きです。

Carbunql自体にはSQL実行機能を持っていませんので、SQL文字列に変換して利用するか、Dapper用ライブラリCarbunql.Darpperを利用してください。
Carbunql.Dapperを使用するとパラメータ情報の引き継ぎが行えるので、おすすめです。

導入方法

Nugetから導入できます。

PM> Install-Package Carbunql

なお、.NET 6 以上が必要となります。

使い方

C#のサンプルコードを用いて使い方を説明します。
サンプルコード全文はこちらを参照ください。

SQLをオブジェクト化する

コンストラクタにSQL文字列を指定し、「SelectQuery」クラスをインスタンスします。

using Carbunql;
using Carbunql.Values;

//sql parse
var sq = new SelectQuery(@"select a.a_id, a.val from table as a");

これでSelect句、From句、Where句などをオブジェクトとして操作可能になります。

もし、ベースとなるSQLが存在せず、ゼロからSQLを生成したい場合は引数を省略してください。

//blank query
var sq = new SelectQuery();

どちらの場合でも、SelectQueryクラスを使用できるのがポイントです。

WHERE句に条件を足す

SelectQueryクラスのWhereメソッド（第1引数はテーブル、第2引数は列名）を使用して条件を追加できます。
論理演算子にはデフォルトでANDが適用されます。

//sq is SelectQuery class

//get FROM table
var a = sq.FromClause!.Root;

//add WHERE clause
sq.Where(a, "val").Equal(":prm");

SELECT
    a.a_id,
    a.val
FROM
    table AS a
WHERE
    a.val = :prm

複数の条件を記述する場合は以下のように書きます。

//add WHERE clause
sq.Where(a, "val1").Equal(":prm1");
sq.Where(a, "val2").Equal(":prm2");

SELECT
    a.a_id,
    a.val1,
    a.val2
FROM
    table AS a
WHERE
    a.val1 = :prm1
    AND a.val2 = :prm2

パラメータ値

SelectQueryクラスはParametersプロパティでパラメータ値を管理できます。
※登録したパラメータ値を利用するにはCarbunql.Dapperを使用してください。

//add WHERE clause
if (parameter != null)
{
    sq.Where("a.val = :prm");
    sq.Parameters.Add(":prm", parameter);
}

/*
    :prm = 1
*/
SELECT
    a.a_id,
    a.val
FROM
    table AS a
WHERE
    a.val = :prm

OR条件

OR条件も記述できます。
以下は条件のグループ化（括弧）とOR条件の例です。

//add WHERE clause
sq.Where(() =>
{
    sq.Parameters.Add(":prm1", prm1);
    sq.Parameters.Add(":prm2", prm2);

    //(a.val = :prm1)
    var v1 = new ColumnValue(a, "val").Equal(":prm1").ToGroup();
    //(a.val = :prm2)
    var v2 = new ColumnValue(a, "val").Equal(":prm2").ToGroup();

    //((a.val = :prm1) or (a.val = :prm2))
    return v1.Or(v2).ToGroup();
});

ColumnValueクラスは列を表すクラスです。第1引数はテーブル、第2引数は列を指定してください。

/*
    :prm1 = 1
    :prm2 = 2
*/
SELECT
    a.a_id,
    a.val
FROM
    table AS a
WHERE
    ((a.val = :prm1) OR (a.val = :prm2))

高度な加工

文字列操作では対応が困難な加工方法についての紹介です。

SELECT句の列フィルタリング

SQLが選択している列はSelectClauseプロパティにて取得できます。
Removeもできます。

//params string[] columns = "a_id", "val3"

//sql parse
var sq = new SelectQuery(@"select a.a_id, a.val1, a.val2, a.val2 as val3 from table as a");

//get FROM table
var a = sq.FromClause!.Root;

//get not contains columns
var lst = sq.SelectClause!.Where(x => !columns.Contains(x.Alias)).ToList();

//remove columns
foreach (var item in lst) sq.SelectClause!.Remove(item);

SELECT
    a.a_id,
    a.val2 AS val3
FROM
    table AS a

外部結合を使用したフィルタリング

FROM句に結合条件を追加し、更にWHERE句に条件を足しましょう。

//sql parse
var sq = new SelectQuery(@"select a.a_id, a.val from table as a");

//get FROM table
var a = sq.FromClause!.Root;

//add left-join, and get joined table
var b = sq.FromClause!.LeftJoin("data").As("b").On(a, "a_id");

//add WHERE clause
sq.Where(b, "a_id").IsNull();

SELECT
    a.a_id,
    a.val
FROM
    table AS a
    LEFT JOIN data AS b ON a.a_id = b.a_id
WHERE
    b.a_id IS null

WITH句に対するフィルタリング

WITH句で使用されているクエリを所得、し、同クエリのWHERE句を更新しましょう。

//sql parse
var sq = new SelectQuery(@"with
cte as (
select a_id, val from table
)
select
a.a_id,
a.val
from
datasource as a");

var cte = sq.WithClause!.Where(x => x.Alias == "cte").First();

// cte SelectQuery
var cteq = cte.GetSelectQuery();

//get FROM table
var a = cteq.FromClause!.Root;

//add WHERE clause
cteq.Where(a, "val").Equal(":prm");
cteq.Parameters.Add(":prm", 1);

/*
    :prm = 1
*/
WITH
    cte AS (
        SELECT
            a_id,
            val
        FROM
            table
        WHERE
            table.val = :prm
    )
SELECT
    a.a_id,
    a.val
FROM
    datasource AS a

まとめ

Carbunqlを使用すると高度な加工が簡単に実装できます。

また、本記事には載せておりませんが、列の追加や、MERGEクエリ化などの加工も行えます。
デモサイトがありますので、ぜひ参照、お試しください。