機能ユニット

はじめに

データベースで計算を実行することについては、2 つの考え方があります。それは素晴らしいと考える人と、間違っている人です。関数、ストアドプロシージャ、生成または計算された列、およびトリガーの世界がすべてバラ色であるとは言えません！これらのツールは決して完璧ではなく、軽率な実装はパフォーマンスが悪く、メンテナを苦しめるなど、論争の存在をある程度説明できます。

しかし、データベースは定義上、情報の処理と操作に非常に優れており、それらのほとんどは、ユーザーが同じ制御とパワーを利用できるようにしています（SQLite と MS Access は程度が低いですが）。外部データ処理プログラムは、データベースから情報を取り出す必要があり、多くの場合ネットワーク経由で、何もできない状態から開始します。そして、データベースプログラムがネイティブのセット演算、インデックス作成、一時テーブル、および半世紀にわたるデータベースの進化のその他の成果を最大限に活用できる場合、複雑な外部プログラムは、ある程度の車輪の再発明を伴う傾向があります。それでは、データベースを活用してみませんか？

データベースをプログラミングしたくないかもしれない理由

• データベース機能は、特にトリガーにおいて、目に見えなくなる傾向があります。この弱点は、データベースと対話するチームやアプリケーションの規模にほぼ比例して拡大します。データベース内プログラミングを覚えていたり、認識したりしている人が少なくなるためです。ドキュメントは役立ちますが、せいぜいその程度です。
• SQL は、データセットを操作するために特別に構築された言語です。データセットの操作ではないものには特に適していません。また、それらの他のものが複雑になるほど、適していません。
• RDBMS の機能と SQL の方言は異なります。単純な生成列は広くサポートされていますが、より複雑なデータベースロジックを他のストアに移植するには、少なくとも時間と労力がかかります。
• データベーススキーマのアップグレードは、通常、アプリケーションのアップグレードよりも面倒です。急速に変化するロジックは、他の場所で保守するのが最適ですが、状況が安定したら、もう一度見直す価値はあります。
• データベースプログラムの管理は、期待するほど簡単ではありません。多くのスキーマ移行ツールは、組織化に関してはほとんど、またはまったく役に立たず、diff が広範囲に及んだり、コードレビューが煩雑になったりします（sqitch の依存関係グラフと個々のオブジェクトの再加工は注目すべき例外であり、migra は問題を完全に回避しようとしています）。テストでは、pgTAP や utPLSQL などのフレームワークは、ブラックボックステストの統合を改善しますが、追加のサポートとメンテナンスの責任も伴います。
• 確立された外部コードベースでは、構造的な変更は、労力を要し、リスクも伴う傾向があります。

一方、SQL は、適しているタスクに対して、速度、簡潔さ、耐久性、および自動化されたワークフローを「正典化」する機会を提供します。データモデリングは、実体を段ボールに昆虫のようにピンで留めるだけのものではなく、動きのあるデータと静止データとの区別は難しいものです。静止は実際には、より細かいグレードでのより遅い動きです。情報は常にここからあちらへ流れており、データベースのプログラマビリティは、それらの流れを管理および指示するための強力なツールです。

一部のデータベースエンジンは、SQL と他のプログラミング言語の違いを分割し、それらの他のプログラミング言語も受け入れます。SQL Server は、任意の .NET Framework 言語で記述された関数をサポートしています。Oracle には Java ストアドプロシージャがあります。PostgreSQL は C による拡張機能を許可し、Python、Perl、および Tcl でユーザープログラミングが可能です。プラグインにより、シェルスクリプト、R、JavaScript などが追加されています。通常の対象を締めくくるのは、MySQL と MariaDB では SQL のみ、MS Access は VBA でのみプログラミング可能、SQLite はユーザープログラミングがまったくできません。

SQL がタスクに不十分な場合、または他のコードを再利用したい場合は、SQL 以外の言語を使用することがオプションですが、データベースプログラミングを諸刃の剣にする他の問題は解決しません。どちらかというと、これらに頼ることで、デプロイメントと相互運用性がさらに複雑になります。Caveat scriptor: 書き手は注意してください。

関数とプロシージャ

デフォルトと生成

単純な計算でさえ、頻繁に実行される場合、または複数の競合する実装が存在する場合は、問題を引き起こす可能性があります。単一の行の値に対する操作（メートル法とインペリアル単位の変換、請求書の小計の作業時間に対するレートの乗算、地理的ポリゴンの面積の計算など）は、一方または他方の問題に対処するためにテーブル定義で宣言できます。

CREATE TABLE pythag (
  a INT NOT NULL,
  b INT NOT NULL,
  c DOUBLE PRECISION NOT NULL
    GENERATED ALWAYS AS (sqrt(pow(a, 2) + pow(b, 2)))
    STORED
);

ほとんどの RDBMS は、「格納」および「仮想」生成列の選択肢を提供しています。前者の場合、値は行が挿入または更新されるときに計算および格納されます。これは、バージョン 12 の PostgreSQL および MS Access での唯一のオプションです。仮想生成列は、ビューのようにクエリされるときに計算されるため、スペースを取りませんが、再計算される頻度が高くなります。どちらのタイプも厳密に制約されています。値は、所属する行の外部の情報に依存することはできず、更新することもできず、個々の RDBMS にはさらに具体的な制限がある場合があります。たとえば、PostgreSQL では、生成列でテーブルをパーティション分割することは禁止されています。

生成列は、特殊なツールです。より多くの場合、必要なのは、挿入時に値が指定されていない場合のデフォルトのみです。now() のような関数は、列のデフォルトとして頻繁に表示されますが、ほとんどのデータベースでは、組み込み関数だけでなくカスタム関数も許可されています（MySQL を除く。MySQL では、current_timestamp のみデフォルト値にすることができます）。

やや味気ないですが、単純な例として、ロット番号を YYYYXXX 形式で考えてみましょう。最初の 4 桁は現在の年を表し、後の 3 桁はインクリメントカウンタを表します。今年最初に生産されたロットは 2020001、2 番目は 2020002、というようになります。このような値を生成するデフォルトタイプまたは組み込み関数はありませんが、ユーザー定義関数は、各ロットに番号を付けることができます 作成時に

CREATE SEQUENCE lot_counter;

CREATE OR REPLACE FUNCTION next_lot_number () RETURNS TEXT AS $$
BEGIN
  RETURN date_part('year', now())::TEXT ||
    lpad(nextval('lot_counter'::REGCLASS)::TEXT, 2, '0');
END;
$$
LANGUAGE plpgsql;

CREATE TABLE lots (
  lot_number TEXT NOT NULL DEFAULT next_lot_number () PRIMARY KEY,
  current_quantity INT NOT NULL DEFAULT 0,
  target_quantity INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at TIMESTAMPTZ,
  CHECK (target_quantity > 0)
);

関数でのデータの参照

上記のシーケンスアプローチには、1 つの重要な弱点があります（および 少なくとも 1 つの重要でないもの ）：新年になると、lot_counter は 12 月 31 日と同じ値を持ちます。ただし、1 年間に作成されたロット数を追跡する方法は複数あり、lots 自体をクエリすることで、next_lot_number 関数は、年が明けた後も正しい値を保証できます。

CREATE OR REPLACE FUNCTION next_lot_number () RETURNS TEXT AS $$
BEGIN
  RETURN (
    SELECT date_part('year', now())::TEXT || lpad((count(*) + 1)::TEXT, 2, '0')
    FROM lots
    WHERE date_part('year', created_at) = date_part('year', now())
  );
END;
$$
LANGUAGE plpgsql;

ALTER TABLE lots
  ALTER COLUMN lot_number SET DEFAULT next_lot_number();

ワークフロー

単一ステートメントの関数でさえ、外部コードよりも重要な利点があります。実行がデータベースの ACID 保証の安全性から決して離れないことです。上記の next_lot_number を、クライアントアプリケーションや手動プロセスの可能性と比較してください。クライアントアプリケーションや手動プロセスでは、今年のロット数を集計するために 1 つの SQL ステートメント を実行し、次に新しいロットを挿入する 2 番目のステートメントを実行します。注文が十分に速く行われている場合、ロット数は、カウントと挿入の間で変化する可能性があります。

複数ステートメントのストアドプログラムは、SQL には例外処理からセーブポイントまで、手続き型コードを記述するために必要なすべてのツールが含まれているため（ウィンドウ関数と共通テーブル式で チューリング完全！）、無限の可能性の空間を開きます。データ処理ワークフロー全体をデータベース内で実行でき、システムの他の領域への露出を最小限に抑え、データベースと他のドメイン間の時間のかかるラウンドトリップを排除できます。

一般的なソフトウェアアーキテクチャの多くは、複雑さを管理および分離し、サブシステム間の境界を越えて拡散するのを防ぐことです。多かれ少なかれ複雑なワークフローに、データをアプリケーションバックエンド、スクリプト、または cron ジョブに取り込み、消化して追加し、結果を保存することが含まれる場合、本当にデータベースの外部に冒険する必要があるのは何かを自問する時が来ました。

上記のように、これは、RDBMS の種類と SQL の方言の違いが表面化する領域です。1 つのデータベース用に開発された関数またはプロシージャは、SQL Server の TOP を標準の LIMIT 句に置き換えるか、エンタープライズ Oracle から PostgreSQL への移行で一時的な状態を格納する方法を完全に再加工するかなど、変更なしに別のデータベースで実行される可能性は低いでしょう。SQL でワークフローを正典化することは、他のほとんどすべての選択肢よりも徹底的に現在のプラットフォームと方言にコミットすることにもなります。

クエリでの計算

これまで、テーブル定義にバインドされているか、複数テーブルのワークフローを管理しているかにかかわらず、関数を使用してデータを保存および変更する方法を見てきました。ある意味で、それは関数を使用できるより強力な用途ですが、関数はデータ取得にも場所があります。count のような標準の組み込み関数から、Postgres の jsonb_build_object、PostGIS の ST_SnapToGrid などの拡張機能まで、クエリですでに使用している可能性のある多くのツールは、関数として実装されています。もちろん、これらはデータベース自体とより緊密に統合されているため、ほとんどが SQL 以外の言語（たとえば、PostgreSQL および PostGIS の場合は C）で記述されています。

データを取得し、実際に準備が整う前に各レコードに対して何らかの操作を実行する必要があることがよくある場合（またはそうなる可能性があると思う場合）は、代わりにデータベースから出る途中でそれらを変換することを検討してください！日付から一定数の営業日を予測する？2 つの JSONB フィールド間の diff を生成する？クエリしている情報のみに依存する実質的にすべての計算は、SQL で実行できます。そして、データベースで行われたことは、一貫してアクセスされる限り、データベースの上に構築されたものに関する限り、正典です。

言っておかなければなりません。アプリケーションバックエンドを使用している場合、そのデータアクセスキットは、クエリ結果を関数で拡張することから得られるメリットを制限する可能性があります。そのようなライブラリのほとんどは、任意の SQL を実行できますが、モデルクラスに基づいて一般的な SQL ステートメントを生成するライブラリは、クエリ SELECT リストのカスタマイズを許可する場合と許可しない場合があります。生成列またはビューは、ここでの答えになる可能性があります。

トリガーと結果

関数とプロシージャは、データベースの設計者とユーザーの間で十分に議論の的になりますが、トリガーを使用すると物事が本当に動き出します。トリガーは、通常はプロシージャ（SQLite は単一ステートメントのみを許可）である自動アクションを定義し、別のアクションの前、後、または代わりとして実行されます。

開始アクションは、通常、テーブルへの挿入、更新、または削除であり、トリガープロシージャは、通常、各レコードまたはステートメント全体に対して実行するように設定できます。SQL Server は、主に詳細なセキュリティ対策を実施する方法として、更新可能なビューのトリガーも許可しています。また、SQL Server、PostgreSQL、および Oracle はすべて、データベース構造の変更に反応できるイベントまたは DDL トリガーの形式をいくつか提供しています。

トリガーの一般的な低リスクの使用法は、無効なデータが格納されるのを防ぐための非常に強力な制約として使用することです。すべての主要なリレーショナルデータベースでは、候補レコードの外部の情報を評価できるのは、主キーと外部キー、および UNIQUE 制約のみです。たとえば、1 か月に作成できるロットは 2 つのみであるということをテーブル定義で宣言することはできません。また、最も単純なデータベースとコードのソリューションは、上記の lot_number へのカウントしてから設定するアプローチと同様の競合状態に脆弱です。テーブル全体または他のテーブルを含む他の制約を実施するには、レコードの範囲外を見るための トリガー が必要です。

CREATE FUNCTION enforce_monthly_lot_limit () RETURNS TRIGGER
AS $$
DECLARE current_count BIGINT;
BEGIN
  SELECT count(*) INTO current_count
  FROM lots
  WHERE date_trunc('month', created_at) = date_trunc('month', NEW.created_at);

  IF current_count >= 2 THEN
    RAISE EXCEPTION 'Two lots already created this month';
  END IF;

  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER monthly_lot_limit
BEFORE INSERT ON lots
FOR EACH ROW
EXECUTE PROCEDURE enforce_monthly_lot_limit();

DML の実行を開始し、フロー制御を使用すると、はるかに複雑なステップのシーケンスを開始し、範囲を拡大して他のテーブルを含めることは、ほんのわずかなステップです。おそらく、lots へのレコードの挿入自体が、orders への挿入によって開始されたトリガーの最終操作であり、人間のユーザーまたはアプリケーションバックエンドが lots に直接書き込む権限はありません。または、items がロットに追加されると、そのトリガーは current_quantity の更新を処理し、target_quantity に達すると他のプロセスを開始する可能性があります。

トリガーと関数は、定義者のアクセスレベルで実行できます（PostgreSQL では、関数の LANGUAGE の横にある SECURITY DEFINER 宣言）。これにより、制限されているユーザーにも、より広範囲のプロセスを開始する権限が与えられ、それらのプロセスを検証およびテストすることがさらに重要になります。

トリガー-アクション-トリガー-アクションのコールスタックは、任意に長くなる可能性がありますが、そのようなフローで同じテーブルまたはレコードを複数回変更する形式での真の再帰は、一部のプラットフォームでは違法であり、一般的にほとんどすべての場合に悪い考えです。トリガーのネストは、その範囲と影響を理解する能力を急速に上回ります。ネストされたトリガーを多用するデータベースは、複雑な領域から複雑な領域へと移行し始め、分析、デバッグ、および予測が困難または不可能になります。

実用的なプログラマビリティ

データベースでの計算は、より高速で簡潔に表現されるだけでなく、あいまいさを排除し、標準を設定します。上記の例では、データベースユーザーは、ロット番号を自分で計算する必要や、誤って処理できるよりも多くのロットを作成してしまう心配から解放されます。特にアプリケーション開発者は、データベースを構造と永続性のみを提供する「ダムストレージ」として考えるように訓練されてきたことが多く、その結果、SQL でより効果的に実行できることをデータベースの外部で不器用に表現していることに気づくか、さらに悪いことに、気づかない可能性があります。

プログラマビリティは、リレーショナルデータベースの不当に見過ごされている機能です。それを避ける理由と、その使用を制限する理由が存在しますが、関数、プロシージャ、およびトリガーはすべて、データモデルが埋め込まれているシステムに課す複雑さを制限するための強力なツールです。

FAQ

CREATE PROCEDURE procedure_name
AS
common_sql_statement;

EXEC procedute_name