ストアドプロシージャ／ファンクション
手続き型 SQL とは
ストアドプロシージャは、データベースサーバー上に名前付きオブジェクトとして永続化され、クライアントからの呼び出しによってサーバープロセス内で実行される、手続き型ロジックと SQL 文の集合です。SQL 文と制御構文 (分岐・ループ・例外処理) を組み合わせて記述します。
ストアドプロシージャは SQL/PSM (Persistent Stored Modules) として標準化されています。しかし、歴史的経緯により、Sybase SQL Server (現 SAP Adaptive Server Enterprise) が最初にストアドプロシージャを実装し、その後、Oracle がサーバー側に手続きロジックを置くという実務上の慣行を PL/SQL という独自言語で広めました。SQL/PSM が標準化されるよりも前にそれぞれのデータベース製品が独自実装を進めた結果、多くのデータベースが SQL/PSM に準拠していないという現状があります。そのため、ここではストアドプロシージャ／ファンクションなどの総称を 「手続き型 SQL」 と呼びます。
それぞれのデータベース製品は次の手続き型言語を提供しています。
- Oracle => PL/SQL
- SQL Server / Sybase => T-SQL (Transact-SQL)
- PostgreSQL => PL/pgSQL
- MySQL / MariaDB => ストアドルーチン
- IBM Db2 => SQL PL
これらの手続き型言語を使い、文と式を次の機能で提供します。
- ストアドプロシージャ (stored procedure)
- CALL / EXECUTE で明示的に呼び出すルーチン
- 値を返さない (または OUT パラメータで返す) のが基本
- ストアドファンクション / ユーザー定義関数 (stored function, UDF)
- 値を返し、SQL 式の中で使えるルーチン
SQL/PSM 標準ではこれらの2つをまとめて SQL-invoked routine と呼びます。
UCIDM が呼び出すオブジェクト
RDBMS 外部連携 の反映方式に storedProcedure を選ぶと、UCIDM は決められた名前の6つのオブジェクトを呼び出してデータを反映します。この6つを連携先のスキーマにあわせて実装するのはシステム管理者の作業です。
オブジェクト名は UCIDM 側で固定されています。登録する CREATE 文にこの名前が含まれていないと、外部連携設定の保存時にエラーになります。
6つすべてに定義が必要です。ユーザーだけを連携する場合でも、グループとメンバーの4つを空欄にしたままでは設定を保存できません。実施しない操作の設定 で操作を無効にしても、定義そのものは求められます。使わないオブジェクトには、何もしないプロシージャと NULL を返すファンクションを登録しておいてください。
引数と戻り値の型
呼び出しの形はどの製品でも同じです。反映プロシージャは JSON を表す値1つを引数に取り、値を返しません。取得ファンクションは JSON を表す値1つを引数に取り、JSON オブジェクトを1つ返します。パッケージやオーバーロードは使えず、引数はちょうど1つです。
UCIDM が渡すのは JSON の文字列ですが、宣言する型は製品ごとに異なります。
PostgreSQL では特に注意してください。UCIDM が発行する DROP 文が引数の型で修飾されるため、json 以外の型で作成すると定義を変更したときに古いオブジェクトが残ります。
オブジェクトのインストール
管理画面の各項目に登録するのは CREATE 文そのものです。UCIDM は外部連携設定から連携先を組み立てるときに、6つのオブジェクトを DROP してから CREATE します。定義が前回インストールしたものと同じであれば、そのオブジェクトの再作成をスキップします。
このスキップの判定は、UCIDM のプロセス内に保持している定義のハッシュとの比較です。データベース側でオブジェクトを直接書き換えても、管理画面の定義が同じであれば UCIDM は再作成しません。逆に UCIDM を再起動すると、定義が同じでも一度は再作成します。
インストールはドライランモードでも実行されます。連携履歴の差分表示に取得ファンクションが要るためです。ドライランモードが抑止するのは反映プロシージャの呼び出しだけで、6つのオブジェクトの DROP と CREATE は行われます。
連携先に同じ名前のオブジェクトがすでにあると、UCIDM がそれを削除します。既存のオブジェクトと名前が衝突しないことを確認してください。
接続ユーザーに要る権限
UCIDM は、外部連携設定に指定した接続ユーザー1つで、オブジェクトのインストールとデータの反映の両方を実行します。このユーザーには次の3つの権限が要ります。
- 作成: 6つのオブジェクトを作成する権限
- 削除: インストールのたびに実行する DROP のための権限
- 呼び出し: 反映プロシージャを呼び出し、取得ファンクションを実行する権限
権限が足りないと、外部連携設定の保存時のインストールが失敗します。ドライランモードでもインストールは実行されるため、作成と削除の権限は連携を試す前から要ります。
これに加えて、プロシージャとファンクションが読み書きするテーブルへの権限も要ります。テーブルへのアクセスをオブジェクトの所有者の権限で検査する製品と、呼び出したユーザーの権限で検査する製品がありますが、UCIDM では作成するのも呼び出すのも同じ接続ユーザーなので、どちらの製品でも接続ユーザーにテーブルへの権限を与えることになります。ただし、権限をロール経由で与えたときに有効かどうかは製品によって異なります。
権限の与え方と GRANT 文の例は、データベース製品ごとのページにまとめています。
反映プロシージャが受け取る JSON
ユーザーとグループの反映プロシージャは、次の形の JSON を受け取ります。
opCode: 実行する操作の種別ですprimaryID: PrimaryID マッピング の結果です。連携先でエントリを一意に識別する値になりますenabled: ユーザーの有効無効です。真偽値で、解決できたときだけ含まれます。グループには付きませんattrs: 属性マッピング の結果です。キーは「連携先の属性名」になります
opCode によって、含まれるキーと期待される処理が変わります。
attrs の扱いには3つの制約があります。
まず、値はすべて文字列です。数値、日付、真偽値のカラムに格納するには、プロシージャ側で型変換します。次に、多値属性は先頭の1つだけが渡ります。最後に、U では今回の操作で届いた属性だけが attrs に入ります。渡されなかった属性を上書きして消さないように書く必要があります。
P の attrs のキーは、パスワードマッピングの設定で決まります。パスワードマッピングを設定していない場合は password というキーで平文が渡ります。
メンバー反映プロシージャが受け取る JSON は形が異なり、opCode も I と D だけです。
取得ファンクションが返す JSON
取得ファンクションが受け取る JSON は、対象を識別する値だけを持ちます。ユーザーとグループは {"primaryID": "u123"}、メンバーは {"groupPrimaryID": "g1", "memberPrimaryID": "u123"} です。
戻り値は JSON オブジェクトの文字列、または SQL の NULL です。対象が存在しないときは NULL を返してください。この戻り値には2つの用途があります。
1つ目は存在確認です。UCIDM は取得ファンクションの結果から opCode を決めます。
メンバーの追加と削除では、UCIDM はメンバー取得ファンクションより先にグループ取得ファンクションを呼びます。グループが見つからなければエラーになり、メンバー反映プロシージャは呼ばれません。
2つ目は連携履歴の「変更前の値」の表示です。返した JSON のキーが属性マッピングの「連携先の属性名」と一致したときに、そのキーの値が変更前の値として履歴に表示されます。キー名が一致しなければ表示されないだけで、連携そのものは成功します。メンバー取得ファンクションは存在確認にしか使われないため、JSON の中身は問いません。
エラーの扱い
プロシージャやファンクションが送出した例外は、そのまま UCIDM の連携エラーになります。連携履歴には失敗として記録され、データベースが返したメッセージも残ります。連携先の都合で反映できないデータを検出したい場合は、プロシージャ内で明示的に例外を送出してください。