🧨

長文SQL（SELECTクエリ）の保守性を爆上げしたい

2024/06/16に公開

SQL

tech

要約

SQLは保守性が低い
実験的にC#でDSLを作った
使ってみてね

対象読書

長文SQL（選択クエリ）を書く人
可読性を上げたい人
保守性を上げたい人
再利用性を上げたい人
テスタビリティを上げたい人
実験的試みに寛容な人

SQLの課題

本題に入る前に軽く触れておきます。読み飛ばしても構いません。

リファクタリング機能が弱い

テーブルエリアス名、列エイリアス名、CTE名。名前は重要ですが、その名前を変更する支援機能がありません。これらの名前を後から変えるのは大変です。

流れるような記述ができない

長文SQLを記述する場合、以下のような手順を踏むことが多いです。

選択クエリを書く
動作確認する
書いたクエリをWITH句、サブクエリにに移動させる
選択クエリを書く
（繰り返す）

この、「移動させる」という作業を挟まないと次のコードを書くことができません。そして、「移動させる」という行為は後述する「デバッグしづらい問題」に繋がります。

デバッグがしづらい

with 
cte_a as (
    select * from table_data
),
cte_b as (
    select * from cte_a
),
cte_c as (
    select * from cte_b
)
select
    *
from
    cte_c

一番最後に記述したSELECTクエリしかデバッグ（データの閲覧）できません。
WITH句で定義したクエリの内容をデバッグするには、一番最後のクエリをコメントアウトして書き換える必要があります。

このため、デバッグは

デバッグできる状態にコードを変える（※）
不具合の場所を特定、修正する
（※）で書き換えたコードをもとに戻す

という工程を踏むことになります。

他の言語にあるようなブレイクポイントやログ出力等の非破壊的アプローチを取ることがSQLではできません。

共通化機能が弱い

CTEでクエリを共通化することはできますが、CTEは該当クエリでのみ有効です。他のクエリで使うにはVIEW、表関数を作成する必要があります。

ただ、これらはデータベースに定義するため、視認性、保守性、可搬性、デプロイの容易さに懸念があります。有益な場合もありますが、他言語の共通化の感覚でVIEWを作れば、VIEWで溢れかえるため、気軽に使用することはできません

単体テストが大変

動作検証をするにはテストデータをデータベースに登録する必要があります。これを実現するには以下の要件を満たす必要があります。

開発用データベースが必要
テーブル等の環境も整える
テストデータを開発者毎に独立させる

1、2は開発者共通環境ですが、3は開発者個人環境が必要になります。

解決案

リファクタリング問題
→IDEの問題

流れるように書けない
→SQL構文の問題

デバッグしづらい
→SQL構文の問題

共通化しづらい
→SQL構文の問題

テストしづらい
→CTEを使えば物理テーブル依存をなくせる

大半の課題は正攻法ではとても解決できそうにありません。

よって、DSL（Domain Specific Language）を使用して解決を試みました。

DSLの使い方

実際にコンソールアプリを作りながら、DSLの使い方、効果を見ていきましょう。

開発環境

IDE：Visual Studio 2022
言語：C#
バージョン：.NET8

成果物（ソースコード）

デモの成果物を公開します。

デモがうまく動作しない場合、コードだけ見たい場合はこちらを参照ください。

プロジェクト

デモコード

DSLのインストール

PackageManagerからCarbunql.TypeSafeをインストールしてください

PM > NuGet\Install-Package Carbunql.TypeSafe

モデルを定義する

はじめに、テーブルをマッピングするモデルを定義します。以下のルールに従って定義してください。

テーブル名＝レコード名
列名＝プロパティ名
IDataRowインターフェイスを実装する

テーブル定義が下記の場合、

モデル定義はこのようになります。

using Carbunql;
using Carbunql.TypeSafe;

public record store : IDataRow
{
    public long store_id { get; set; }
    public string store_name { get; set; }
    public IDataSet DataSet { get; set; } = null!;
}

IDataRowはTypeSafeビルドができることを示すインターフェイスです。インターフェイスを適用しようとすると、DataSetプロパティの実装が求められますので、

public IDataSet DataSet { get; set; } = null!;

と決め打ちで定義してください。

選択クエリを関数として定義する

つづいて選択クエリを作成します。選択クエリは再利用することを前提とするため、関数として定義してください。

全列全行を取得するコードを先に提示します。

using Carbunql;
using Carbunql.TypeSafe;

internal class Program
{
    private static void Main(string[] args)
    {
    }

    /// <summary>
    /// すべての店舗を取得する選択クエリ
    /// </summary>
    /// <returns></returns>
    private static FluentSelectQuery<store> SelectAllStore()
    {
        //データセット（テーブル）にエイリアス名をつける
        var s = Sql.DefineDataSet<store>();
        //Fromにデータセットを設定する
        return Sql.From(() => s);
    }
}

本ライブラリでは以下の順序でSQLをビルドしていきます。

データセットとその名称の定義
データセットを使用したSQL文の構築（SQL文はFROM句から記述）

では、最初にデータセットを定義します。

//データセット（テーブル）にエイリアス名をつける
var s = Sql.DefineDataSet<store>();

上記は"store"テーブルに"s"という名称をつけた、という意味になります。

次に、このデータセットを使ってSQL文を構築します。データセットをFrom句に指定するには以下のように記述します。

Sql.From(() => s)

これで

SELECT * FROM store AS s

というSQLと同義の関数が作成されます。

関数をテストする

ToTextメソッドを使用するとコマンド文字列が取得できます。

private static void Main(string[] args)
{
    //選択クエリを取得する
    var query = SelectAllStore();

    // 選択クエリをコマンド文字列として出力する
    // select * from store
    Console.WriteLine(query.ToText());
}

実行結果は以下のようになります。

SELECT
    *
FROM
    store AS s

選択クエリ関数をサブクエリとして再利用する

事前に定義した関数をサブクエリとして参照し、検索条件の追加をしてみましょう。

/// <summary>
/// 店舗ID=1を選択するクエリ
/// </summary>
/// <returns></returns>
private static FluentSelectQuery<store> SelectStoreId1()
{
    //データセット（クエリ）にエイリアス名をつける
    var all_s = Sql.DefineDataSet(() => SelectAllStore());
    //Fromにデータセットを設定する
    //Were条件も追加する
    return Sql.From(() => all_s)
        .Where(() => all_s.store_id == 1);
}

データセットを定義するのは前回と同様です。今回はテーブルではなく関数を参照していることに注目ください。

//データセット（クエリ）にエイリアス名をつける
var all_s = Sql.DefineDataSet(() => SelectAllStore());

上記は"SelectAllStoreクエリ"に"all_s"という名称をつけた、という意味になります。

実行結果は以下のようになります。

SELECT
    *
FROM
    (
        SELECT
            *
        FROM
            store AS s
    ) AS all_s
WHERE
    all_s.store_id = 1

選択クエリ関数をCTEして再利用する

先程は選択クエリ関数をサブクエリとして参照しましたが、CTEとして参照することも可能です。

/// <summary>
/// 店舗ID=1を選択するクエリ（CTE）
/// </summary>
/// <returns></returns>
private static FluentSelectQuery<store> SelectStoreId1UseCTE()
{
    //選択クエリに名前をつけるとCTEと認識します
    var all_store = SelectAllStore();

    //データセット（CTE）にエイリアス名をつける
    var all_s = Sql.DefineDataSet(() => all_store);
    //Fromにデータセットを設定する
    //Were条件も追加する
    return Sql.From(() => all_s)
        .Where(() => all_s.store_id == 1);
}

サブクエリとして利用する場合とほぼ同じですが、選択クエリに名前をつけているところがポイントになります。

//選択クエリに名前をつけるとCTEと認識します
var all_store = SelectAllStore();

このようにすることで、選択クエリ関数をCTEとして扱うことができます。それ以外はサブクエリと全く同じです。FROM句に設定するだけで自動的にWITH句の生成もされます。

Sql.From(() => all_s)

実行結果は以下のようになります。

WITH
    all_store AS (
        SELECT
            *
        FROM
            store AS s
    )
SELECT
    *
FROM
    all_store AS all_s
WHERE
    all_s.store_id = 1

以上、単独でも実行可能な選択クエリを再利用し、新しい選択クエリを生成することができました。これで、

再利用性
保守性
可読性

は大きく改善できたかと思います。

テストデータも関数として定義する

物理テーブルよりもCTEが優先的に参照される仕様^[1]を用いてテスタビリティも改善しましょう。

テストデータクエリはコンストラクタにコレクションを渡すだけで作成できます。

private static FluentSelectQuery<store> SelectTestStoreData()
{
    //コレクションからVALUESクエリを生成し、さらに選択クエリにする
    return new FluentSelectQuery<store>([
           new store(){store_id = 1, store_name = "abc"},
           new store(){store_id = 2, store_name = "def"},
    ]);
}

実行結果は以下のようになります。

SELECT
    v.store_id,
    v.store_name
FROM
    (
        VALUES
            (1, CAST('abc' AS text)),
            (2, CAST('def' AS text))
    ) AS v (
        store_id, store_name
    )

テストデータを注入する

先ほど作ったテストデータクエリをWITH句に注入しましょう。

データセットを定義するところまでは同じですが、FROM句では参照しないのでWITH句に直接設定します。

query = SelectStoreId1UseCTE();
var store = SelectTestStoreData();
//WITH句に注入
query.With(() => store);

実行結果は以下のようになります。

WITH
    store AS (
        SELECT
            v.store_id,
            v.store_name
        FROM
            (
                VALUES
                    (1, CAST('abc' AS text)),
                    (2, CAST('def' AS text))
            ) AS v (
                store_id, store_name
            )
    ),
    all_store AS (
        SELECT
            *
        FROM
            store AS s
    )
SELECT
    *
FROM
    all_store AS all_s
WHERE
    all_s.store_id = 1

これで物理テーブルを全く参照しないテストクエリが完成します。

名称のリファクタリング

VisualStudioの機能が利用できます。関数名、エイリアス名をあとから好きなように修正できます。

コメント機能

SQLが長文になってくると、SQLのCTEとC#の関数のマッピングがしたくなります。この場合はCommentメソッドを使用してください。

private static FluentSelectQuery<store> SelectTestStoreData()
{
    //コレクションからVALUESクエリを生成し、さらに選択クエリにする
    return new FluentSelectQuery<store>([
           new store(){store_id = 1, store_name = "abc"},
           new store(){store_id = 2, store_name = "def"},
    ]).Comment($"Function:{nameof(SelectTestStoreData)}");
}

WITH
    store AS (
        /* Function:SelectTestStoreData */
        SELECT
            v.store_id,
            v.store_name
        FROM
            (
                VALUES
                    (1, CAST('abc' AS text)),
                    (2, CAST('def' AS text))
            ) AS v (
                store_id, store_name
            )
    ),
    all_store AS (
        SELECT
            *
        FROM
            store AS s
    )
SELECT
    *
FROM
    all_store AS all_s
WHERE
    all_s.store_id = 1

より実戦的なデモ

消費税計算

以下を仕様とした税計算処理を行うコードを書いてみます。

税は総額切り捨てで求めます
行単位でも税額切り捨てで求めます
総額と行単位の税額が合わない場合、不足額を按分する
按分の優先順位は切り捨てた端数の額の降順

成果物となった関数はこちら

private static FluentSelectQuery<SaleReport> SelectSaleReportQuery()
{
    var sale_with_tax = SelectSaleWithTaxQuery();
    var swt = Sql.DefineDataSet(() => sale_with_tax);

    var tax_summary = SelectTaxSummary();
    var ts = Sql.DefineDataSet(() => tax_summary);

    var getSubQuery = () => Sql.From(() => swt)
        .InnerJoin(() => ts, () => swt.tax_rate == ts.tax_rate)
        .Select(() => swt)
        .Select(() => new SaleReport
        {
            tax_adjustment = (swt.adjust_priority <= ts.tax_difference) ? 1 : 0
        }).Compile<SaleReport>(force: true);

    var q = Sql.DefineDataSet(() => getSubQuery());

    var query = Sql.From(() => q)
        .Comment("If the tax amount per line does not match the tax amount per tax rate, it will be adjusted.\r\nThe priority for adjustments will be in descending order of the rounded down fraction.")
        .Select(() => q)
        .Select(() => new SaleReport
        {
            tax = q.line_tax_truncated + q.tax_adjustment
        });

    return query.Compile<SaleReport>(); ;
}

生成されるSQL（長いので折りたたんでます）

/* If the tax amount per line does not match the tax amount per tax rate, it will be adjusted.
The priority for adjustments will be in descending order of the rounded down fraction. */
WITH
    sale AS (
        /* test data */
        SELECT
            v.sale_id,
            v.product_name,
            v.unit_price,
            v.quantity,
            v.tax_rate
        FROM
            (
                VALUES
                    (1, CAST('apple' AS text), 105, 5, CAST(0.07 AS double precision)),
                    (2, CAST('orange' AS text), 203, 3, CAST(0.07 AS double precision)),
                    (3, CAST('banana' AS text), 233, 9, CAST(0.07 AS double precision)),
                    (4, CAST('tea' AS text), 309, 7, CAST(0.08 AS double precision)),
                    (5, CAST('coffee' AS text), 555, 9, CAST(0.08 AS double precision)),
                    (6, CAST('cola' AS text), 456, 2, CAST(0.08 AS double precision))
            ) AS v (
                sale_id, product_name, unit_price, quantity, tax_rate
            )
    ),
    sale_with_tax AS (
        /* Calculate tax on a line-by-line basis */
        SELECT
            q.sale_id,
            q.product_name,
            q.unit_price,
            q.quantity,
            q.tax_rate,
            q.line_tax_truncated,
            q.line_tax_raw,
            ROW_NUMBER() OVER(
                PARTITION BY
                    q.tax_rate
                ORDER BY
                    q.line_tax_raw - CAST(q.line_tax_truncated AS double precision) DESC,
                    q.sale_id
            ) AS adjust_priority
        FROM
            (
                SELECT
                    s.sale_id,
                    s.product_name,
                    s.unit_price,
                    s.quantity,
                    s.tax_rate,
                    CAST(TRUNC(CAST(s.unit_price * s.quantity AS double precision) * s.tax_rate) AS integer) AS line_tax_truncated,
                    CAST(s.unit_price * s.quantity AS double precision) * s.tax_rate AS line_tax_raw,
                    CAST(null AS integer) AS adjust_priority
                FROM
                    Sale AS s
            ) AS q
    ),
    tax_summary AS (
        /* Calculate tax by tax rate */
        SELECT
            q.tax_rate,
            q.total_tax,
            q.line_tax_trancated_summary,
            q.total_tax - q.line_tax_trancated_summary AS tax_difference
        FROM
            (
                SELECT
                    swt.tax_rate,
                    CAST(TRUNC(SUM(swt.line_tax_raw)) AS integer) AS total_tax,
                    SUM(swt.line_tax_truncated) AS line_tax_trancated_summary,
                    CAST(null AS integer) AS tax_difference
                FROM
                    sale_with_tax AS swt
                GROUP BY
                    swt.tax_rate
            ) AS q
    )
SELECT
    q.sale_id,
    q.product_name,
    q.unit_price,
    q.quantity,
    q.tax_rate,
    q.line_tax_truncated,
    q.line_tax_raw,
    q.adjust_priority,
    q.tax_adjustment,
    q.line_tax_truncated + q.tax_adjustment AS tax
FROM
    (
        SELECT
            swt.sale_id,
            swt.product_name,
            swt.unit_price,
            swt.quantity,
            swt.tax_rate,
            swt.line_tax_truncated,
            swt.line_tax_raw,
            swt.adjust_priority,
            CASE
                WHEN swt.adjust_priority <= ts.tax_difference THEN 1
                ELSE 0
            END AS tax_adjustment,
            CAST(null AS integer) AS tax
        FROM
            sale_with_tax AS swt
            INNER JOIN tax_summary AS ts ON swt.tax_rate = ts.tax_rate
    ) AS q

ソースコード全文はこちらになります。

クリエイトテーブル変換

ToCreateTableQueryメソッドを使用してください。

query.ToCreateTableQuery("tmp", isTemporary: true).ToText();

インサートクエリ変換

ToInsertQueryメソッドを使用してください。

query.ToInsertQuery("tmp", isTemporary: true).ToText();

まとめ

DSLを覚える必要がありますが、長文SQLの保守性を大きく改善できたかと思います。

ただし、DSLにも問題点があります。

C#をSQLに翻訳するコードが必要（翻訳できないパターンは例外が出る）
SQLの方言問題があり、一概に翻訳できない（現在はPostgresのみ対応）
ビルド処理は速いとは言えない（OLTPにあまり向かない）

OLTPでは長文SQLは発生しにくいのでビルド処理速度の改善は後回しとしますが、翻訳、方言問題については引き続き改善していきたいと思っています。

https://github.com/mk3008/Carbunql

脚注

Postgresのドキュメント
https://www.postgresql.org/docs/current/sql-select.html
A WITH query is referenced by writing its name, just as though the query's name were a table name. (In fact, the WITH query hides any real table of the same name for the purposes of the primary query. If necessary, you can refer to a real table of the same name by schema-qualifying the table's name.) An alias can be provided in the same way as for a table. ↩︎

要約

対象読書

SQLの課題

解決案

DSLの使い方

開発環境

成果物（ソースコード）

DSLのインストール

モデルを定義する

選択クエリを関数として定義する

関数をテストする

選択クエリ関数をサブクエリとして再利用する

選択クエリ関数をCTEして再利用する

テストデータも関数として定義する

テストデータを注入する

名称のリファクタリング

コメント機能

より実戦的なデモ

消費税計算

クリエイトテーブル変換

インサートクエリ変換

まとめ

Discussion