機能単位

はじめに

データベース内で計算を実行することについては、2つの考え方があります。それは「素晴らしい」と考える人々と、「間違っている」と考える人々です。これは、関数、ストアドプロシージャ、生成列または計算列、そしてトリガーの世界が常に順風満帆であるという意味ではありません！これらのツールは決して完璧ではなく、不十分に検討された実装はパフォーマンスが低下したり、保守担当者を苦しめたりする可能性があり、それが論争の存在をある程度説明しています。

しかし、データベースは、定義上、情報の処理と操作に非常に優れており、そのほとんどがユーザーに同様の制御と機能を提供しています（SQLiteとMS Accessは程度が低いですが）。外部のデータ処理プログラムは、何かを行う前に、しばしばネットワークを介してデータベースから情報を引き出す必要があり、不利な立場から始まります。データベースプログラムがネイティブの集合演算、インデックス作成、一時テーブル、その他半世紀にわたるデータベースの進化の恩恵を最大限に活用できるのに対し、複雑な外部プログラムは、ある程度の車輪の再発明を伴う傾向があります。では、なぜデータベースを活用しないのでしょうか？

データベースをプログラミングしたくないかもしれない理由がこちらです！

• データベースの機能は、特にトリガーにおいて、目に見えなくなりがちです。この弱点は、データベースとやり取りするチームやアプリケーションの規模にほぼ比例して拡大します。なぜなら、データベース内でのプログラミングを覚えている人や意識している人が少なくなるからです。ドキュメントは役立ちますが、その効果には限りがあります。
• SQLはデータセットを操作するために特別に構築された言語です。データセットの操作以外のことは特に得意ではなく、それ以外のことが複雑になるほど、その得意度は低くなります。
• RDBMSの機能とSQL方言は異なります。単純な生成列は広くサポートされていますが、より複雑なデータベースロジックを他のストアに移植するには、少なくとも時間と労力がかかります。
• データベーススキーマのアップグレードは、通常、アプリケーションのアップグレードよりも多くの問題を含んでいます。急速に変化するロジックは、安定したらいつか再検討する価値はあるものの、他の場所で保守するのが最善です。
• データベースプログラムの管理は、期待するほど単純ではありません。多くのスキーマ移行ツールは、整理のためにほとんど何もしないため、広範な差分と手間のかかるコードレビューにつながります（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：書き手は用心せよ。

関数 vs プロシージャ

デフォルトと生成

単純な計算であっても、十分に頻繁に実行されたり、複数の競合する実装が存在したりすると問題を引き起こす可能性があります。単一行の値に対する操作（メートル法とヤードポンド法単位間の変換、請求書の小計のためにレートに労働時間を乗算、地理的多角形の面積計算など）は、いずれかの問題に対処するためにテーブル定義で宣言できます。

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の方言の違いが顕著になる領域です。あるデータベース用に開発された関数やプロシージャは、変更なしに別のデータベースで実行されない可能性が高いです。それはSQL ServerのTOPを標準のLIMIT句に置き換えることでも、企業OracleからPostgreSQLへの移行で一時的な状態の保存方法を完全に再構築することでも同じです。SQLでワークフローを標準化することは、他のほとんどの選択肢よりも、現在のプラットフォームと方言に徹底的にコミットすることにもなります。

クエリでの計算

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

データを取得した後、それが本当に準備できる前に各レコードに対して何らかの操作を実行する必要がある（またはそうなるかもしれないと考える）場合は、代わりにデータベースからデータを出す際に変換することを検討してください！特定の日付から営業日数を計算する？2つのJSONBフィールド間の差分を生成する？実質的に、クエリしている情報のみに依存する計算はすべて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でより効果的にできることをデータベースの外で不器用に表現している自分自身に気づいたり、さらに悪いことに気づかなかったりする可能性があります。

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

よくある質問

CREATE PROCEDURE procedure_name
AS
common_sql_statement;

EXEC procedute_name