Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

MySQL / MariaDB ストアドルーチンの開発

UCIDM が呼び出すオブジェクト を MySQL と MariaDB のストアドルーチンで実装する方法を説明します。JSON 型と JSON 関数を使うため、MySQL 8.0 以降と MariaDB 10.9 以降が対象です。

外部連携設定の「データベースタイプ」は、MySQL と MariaDB のどちらも同じ値を選びます。UCIDM が発行する SQL も同じです。ただし JSON 関数の書き方には差があるので、MariaDB での違い にまとめました。

呼び出しの仕様そのものは ストアドプロシージャ／ファンクション にまとめています。本ページは MySQL 固有の書き方に絞ります。

UCIDM が発行する SQL

実装の制約は、UCIDM が発行する SQL から決まります。MySQL に対しては次の3種類を発行します。

-- 反映プロシージャの呼び出し
CALL ucidm_apply_user(?);

-- 取得ファンクションの呼び出し
SELECT ucidm_get_user(?);

-- インストール前の削除 (名前だけで指定する)
DROP PROCEDURE IF EXISTS ucidm_apply_user;
DROP FUNCTION IF EXISTS ucidm_get_user;

ここから、MySQL での実装は次の形に決まります。

  • 引数はちょうど1つで、型は JSON にします
  • 取得ファンクションの戻り値は SELECT から参照できる型にします
  • オブジェクトは、外部連携設定の「データベース名」で指定したデータベースに作られます

MySQL はルーチンのオーバーロードに対応していないため、DROP は名前だけで指定します。引数の型を変えても古い定義が残ることはありません。

実装例の前提

社員管理の既存スキーマに ID 連携する例で説明します。Oracle PL/SQL の開発 と同じスキーマを MySQL の型で用意します。

-- ユーザーの基本情報
CREATE TABLE emp_user (
  user_name    VARCHAR(255) PRIMARY KEY,
  display_name TEXT NOT NULL,
  status       VARCHAR(50),
  emp_no       INT,
  pwd_changed  DATETIME,
  dept_code    VARCHAR(50)
) DEFAULT CHARSET=utf8mb4;

-- メールアドレスは別テーブル
CREATE TABLE emp_contact (
  user_name VARCHAR(255) PRIMARY KEY,
  email     VARCHAR(255)
) DEFAULT CHARSET=utf8mb4;

-- パスワードも別テーブル
CREATE TABLE emp_cred (
  user_name VARCHAR(255) PRIMARY KEY,
  password  VARCHAR(255)
) DEFAULT CHARSET=utf8mb4;

-- グループは連番の代理キーが主キー
CREATE TABLE emp_group_seq (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  primary_id VARCHAR(255) UNIQUE
) DEFAULT CHARSET=utf8mb4;
CREATE TABLE emp_group (
  id    INT PRIMARY KEY,
  label VARCHAR(255)
) DEFAULT CHARSET=utf8mb4;
CREATE TABLE emp_member (
  group_name VARCHAR(255),
  user_name  VARCHAR(255),
  PRIMARY KEY (group_name, user_name)
) DEFAULT CHARSET=utf8mb4;

-- 部署名から部署コードへの変換マスタ
CREATE TABLE emp_dept_code (
  name VARCHAR(255) PRIMARY KEY,
  code VARCHAR(255)
) DEFAULT CHARSET=utf8mb4;
INSERT INTO emp_dept_code(name, code)
  VALUES ('営業部', 'D001'), ('開発部', 'D002'), ('人事部', 'D003');

主キーになるカラムに TEXT は使えないので VARCHAR(255) にしています。文字セットを utf8mb4 にしているのは、絵文字などの4バイト文字を含む名前を格納するためです。サーバーの既定値が utf8mb3 の環境では、指定しないと反映時にエラーになります。

属性マッピングも Oracle の例と同じです。左の列が反映プロシージャの attrs のキーになります。

連携先の属性名連携元の属性名用途
cncnemp_user.display_name
mailmailemp_contact.email
empNoemployeeNumberemp_user.emp_no (数値)
pwdChangedpwdChangedTimeemp_user.pwd_changed (日時)
departmentouemp_user.dept_code (マスタ変換)
ucidmUserEnableducidmUserEnabledemp_user.status

グループの属性マッピングは label に cn を割り当てます。

最後の ucidmUserEnabled は、JSON の enabled を解決するために必要です。動作設定で「ユーザーステータス設定情報を使用する」を off にした場合、UCIDM は「ステータス用属性名」に指定した名前をマッピング後の属性から探します。属性マッピングにこの行がないと enabled が解決されず、JSON に含まれません。「ユーザーステータス設定情報を使用する」を on にした場合はマッピングは不要です。

ユーザー反映プロシージャ

opCode で4つの操作を分岐します。

CREATE PROCEDURE ucidm_apply_user(p JSON)
BEGIN
  DECLARE op  VARCHAR(8);
  DECLARE pid VARCHAR(255);
  SET op  = p->>'$.opCode';
  SET pid = p->>'$.primaryID';

  IF op = 'I' THEN
    INSERT INTO emp_user(user_name, display_name, status, emp_no, pwd_changed, dept_code)
      VALUES (pid,
              p->>'$.attrs.cn',
              IF(p->>'$.enabled' = 'true', 'active', 'inactive'),
              CAST(p->>'$.attrs.empNo' AS SIGNED),
              STR_TO_DATE(p->>'$.attrs.pwdChanged', '%Y%m%d%H%i%sZ'),
              (SELECT code FROM emp_dept_code WHERE name = p->>'$.attrs.department'));
    INSERT INTO emp_contact(user_name, email) VALUES (pid, p->>'$.attrs.mail');

  ELSEIF op = 'U' THEN
    UPDATE emp_user SET
      display_name = COALESCE(p->>'$.attrs.cn', display_name),
      emp_no       = COALESCE(CAST(p->>'$.attrs.empNo' AS SIGNED), emp_no),
      pwd_changed  = COALESCE(STR_TO_DATE(p->>'$.attrs.pwdChanged', '%Y%m%d%H%i%sZ'), pwd_changed),
      dept_code    = COALESCE((SELECT code FROM emp_dept_code
                                 WHERE name = p->>'$.attrs.department'), dept_code),
      status       = CASE WHEN p->>'$.enabled' IS NOT NULL
                          THEN IF(p->>'$.enabled' = 'true', 'active', 'inactive')
                          ELSE status END
      WHERE user_name = pid;
    IF p->>'$.attrs.mail' IS NOT NULL THEN
      INSERT INTO emp_contact(user_name, email) VALUES (pid, p->>'$.attrs.mail') AS new
        ON DUPLICATE KEY UPDATE email = new.email;
    END IF;

  ELSEIF op = 'P' THEN
    INSERT INTO emp_cred(user_name, password) VALUES (pid, p->>'$.attrs.password') AS new
      ON DUPLICATE KEY UPDATE password = new.password;

  ELSEIF op = 'D' THEN
    DELETE FROM emp_contact WHERE user_name = pid;
    DELETE FROM emp_cred WHERE user_name = pid;
    DELETE FROM emp_user WHERE user_name = pid;
  END IF;
END

分岐ごとの要点を順に見ます。

I では、attrs の文字列を CAST と STR_TO_DATE でカラムの型に変換しています。pwdChanged の元になる LDAP の pwdChangedTime は 20260220213129Z という generalizedTime 形式なので、書式指定子 %Y%m%d%H%i%sZ で解釈します。部署は emp_dept_code を副問い合わせで引いてコードに変換しています。

status は enabled から導いています。この書き方では、enabled が渡らなかったときも IF の偽側に落ちて inactive で登録される点に注意してください。属性マッピングの設定漏れで enabled が解決されないと、全員が無効な状態で登録されます。未解決のときに別の値を入れたい場合は、p->>'$.enabled' IS NULL の場合を分けてください。

U では、すべてのカラムを COALESCE で包んでいます。更新イベントで届く attrs には変更のあった属性しか入らないため、これがないと今回渡されなかったカラムが NULL で上書きされます。status だけは COALESCE ではなく CASE で書いています。enabled は真偽値なので、false を「値なし」と区別する必要があるためです。

P はパスワード更新です。emp_cred に行があるとは限らないので ON DUPLICATE KEY UPDATE で挿入と更新を兼ねています。パスワードマッピングを設定していない場合、$.attrs.password には平文が入ります。連携先の要件にあわせて、パスワードマッピングでハッシュ化するか、プロシージャ内で変換してください。

D では3つのテーブルから順に削除します。外部キー制約がある場合は、参照している側から先に削除します。

JSON からの値の取り出しには p->>'$.opCode' を使っています。JSON_UNQUOTE(JSON_EXTRACT(p, '$.opCode')) の省略形で、引用符を外した文字列を返します。パスが存在しなければ NULL を返すので、IS NOT NULL で「キーが渡されたかどうか」を判定できます。

ユーザー取得ファンクション

ファンクションは問い合わせるだけなので、RETURN に副問い合わせを1つ書く形にできます。

CREATE FUNCTION ucidm_get_user(p JSON) RETURNS JSON READS SQL DATA
RETURN (
  SELECT JSON_OBJECT(
           'cn',         u.display_name,
           'mail',       c.email,
           'empNo',      u.emp_no,
           'pwdChanged', DATE_FORMAT(u.pwd_changed, '%Y%m%d%H%i%sZ'),
           'department', d.name)
  FROM emp_user u
         LEFT JOIN emp_contact c ON c.user_name = u.user_name
         LEFT JOIN emp_dept_code d ON d.code = u.dept_code
  WHERE u.user_name = p->>'$.primaryID'
)

該当する行がなければ、この副問い合わせの結果は NULL になります。Oracle のような例外処理は要りません。

READS SQL DATA は省略できません。バイナリログを有効にしたサーバーでは、データアクセスの特性を宣言していないルーチンの作成が ERROR 1418 で拒否されます。

JSON のキーは、属性マッピングの「連携先の属性名」にあわせています。名前だけでなく値の形もあわせると、連携履歴の差分が読める形になります。この例では department を emp_dept_code と結合して部署名に戻しています。カラムに入っている dept_code をそのまま返すと、変更前がコード、変更後が部署名という差分になり、比較になりません。pwdChanged を DATE_FORMAT で generalizedTime の書式に戻しているのも同じ理由です。JSON_OBJECT に DATETIME をそのまま渡すと 2026-02-20 21:31:29.000000 という MySQL 独自の形式になります。

グループの実装

グループはユーザーと同じくエントリです。primaryID で識別し、属性マッピングの結果を attrs で受け取ります。ユーザーとの違いは2つあります。enabled が渡らないことと、パスワード更新の P が来ないことです。反映プロシージャが分岐するのは I、U、D の3つになります。

グループの反映プロシージャは、外部の識別子と連番の代理キーを対応づけます。AUTO_INCREMENT の採番結果は LAST_INSERT_ID() で受け取ります。

CREATE PROCEDURE ucidm_apply_group(p JSON)
BEGIN
  DECLARE op  VARCHAR(8);
  DECLARE pid VARCHAR(255);
  DECLARE gid INT;
  SET op  = p->>'$.opCode';
  SET pid = p->>'$.primaryID';

  IF op = 'I' THEN
    INSERT INTO emp_group_seq(primary_id) VALUES (pid);
    SET gid = LAST_INSERT_ID();
    INSERT INTO emp_group(id, label) VALUES (gid, p->>'$.attrs.label');
  ELSEIF op = 'U' THEN
    UPDATE emp_group SET label = COALESCE(p->>'$.attrs.label', label)
      WHERE id = (SELECT id FROM emp_group_seq WHERE primary_id = pid);
  ELSEIF op = 'D' THEN
    DELETE FROM emp_member WHERE group_name = pid;
    DELETE FROM emp_group WHERE id = (SELECT id FROM emp_group_seq WHERE primary_id = pid);
    DELETE FROM emp_group_seq WHERE primary_id = pid;
  END IF;
END

グループ取得ファンクションはユーザーと同じ形です。返す JSON のキーは、グループの属性マッピングの「連携先の属性名」にあわせます。

CREATE FUNCTION ucidm_get_group(p JSON) RETURNS JSON READS SQL DATA
RETURN (
  SELECT JSON_OBJECT('id', s.id, 'label', g.label)
  FROM emp_group_seq s JOIN emp_group g ON g.id = s.id
  WHERE s.primary_id = p->>'$.primaryID'
)

メンバーの実装

メンバーはエントリではなく、グループとユーザーの関係です。属性を持たないため attrs も enabled も渡らず、グループとメンバーの2つの primaryID の組だけで識別します。opCode も追加の I と削除の D の2つだけで、更新に相当する操作はありません。

メンバーの追加と削除では、UCIDM はまずグループ取得ファンクションでグループの存在を確認します。グループが見つからなければエラーになり、メンバー反映プロシージャは呼ばれません。

反映プロシージャは追加と削除を分岐するだけです。UCIDM は追加前にメンバー取得ファンクションで重複を確認しますが、他の経路で登録された行と衝突しないように INSERT IGNORE を使っています。

CREATE PROCEDURE ucidm_apply_member(p JSON)
BEGIN
  DECLARE op VARCHAR(8);
  SET op = p->>'$.opCode';

  IF op = 'I' THEN
    INSERT IGNORE INTO emp_member(group_name, user_name)
      VALUES (p->>'$.groupPrimaryID', p->>'$.memberPrimaryID');
  ELSEIF op = 'D' THEN
    DELETE FROM emp_member
      WHERE group_name = p->>'$.groupPrimaryID' AND user_name = p->>'$.memberPrimaryID';
  END IF;
END

メンバー取得ファンクションの戻り値は存在確認にしか使われません。行の有無さえ判別できれば、JSON の中身は何でもかまいません。追加時に JSON が返れば登録済みとしてスキップし、削除時に NULL が返れば何もしません。

CREATE FUNCTION ucidm_get_member(p JSON) RETURNS JSON READS SQL DATA
RETURN (
  SELECT JSON_OBJECT('group_name', m.group_name, 'user_name', m.user_name)
  FROM emp_member m
  WHERE m.group_name = p->>'$.groupPrimaryID' AND m.user_name = p->>'$.memberPrimaryID'
)

mysql クライアントで動作を確認する

管理画面に登録する前に、mysql クライアントで作成して手で呼び出すのが確実です。

$ mysql -h 127.0.0.1 -u root -p verify

プロシージャの本体にはセミコロンが含まれるため、mysql クライアントで作成するときは文の区切り文字を一時的に変更します。

DELIMITER $$
CREATE PROCEDURE ucidm_apply_user(p JSON)
BEGIN
...
END$$
DELIMITER ;

作成した6つのオブジェクトは information_schema.routines で確認します。

SELECT routine_name, routine_type FROM information_schema.routines
 WHERE routine_schema = DATABASE() AND routine_name LIKE 'ucidm\_%';

反映と取得を手で呼び出して、期待どおりのデータが入るかを確認します。

CALL ucidm_apply_user('{"opCode":"I","primaryID":"user01","enabled":true,
  "attrs":{"cn":"山田太郎","mail":"taro@example.com","empNo":"1042",
  "pwdChanged":"20260220213129Z","department":"営業部"}}');

SELECT ucidm_get_user('{"primaryID":"user01"}');

opCode を U、P、D に変えて、更新と削除も同じ手順で確認してください。存在しない primaryID に対して取得ファンクションが NULL を返すことも確認します。

管理画面に登録する

動作を確認できたら、CREATE 文を管理画面の対応する項目に貼り付けます。DELIMITER の行は含めないでください。DELIMITER は mysql クライアントの機能であり、サーバーに送る SQL ではありません。UCIDM は登録された定義をそのまま1つの文としてサーバーに送るため、区切り文字を変える必要がありません。同じ理由で、末尾の END の後に $$ を付けたままにしないでください。

UCIDM が先に DROP するので CREATE OR REPLACE にする必要はありません。MySQL にはそもそもこの構文がありません。

登録後に UCIDM がインストールを実行するので、実施しない操作の設定 やドライランモードとあわせて、まず限定した範囲で連携を試すことを勧めます。ドライランモードでは反映プロシージャは呼ばれませんが、取得ファンクションは呼ばれます。

つまずきやすい点

型変換の失敗が黙って通る

CAST('abc' AS SIGNED) はエラーになりません。0 を返して警告を出すだけです。STR_TO_DATE も解釈できない文字列に対して NULL を返します。Oracle や PostgreSQL では例外になる操作が、MySQL では既定値のまま格納されて連携が成功してしまいます。

値の妥当性を連携側で保証できない場合は、変換前に検査して、不正なら明示的にエラーにしてください。

IF p->>'$.attrs.empNo' IS NOT NULL AND p->>'$.attrs.empNo' NOT REGEXP '^[0-9]+$' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'empNo must be numeric';
END IF;

複数の文をまとめてロールバックするには明示的な指定が要る

MySQL の自動コミットは文ごとに働きます。プロシージャの中に複数の INSERT を書いても、それぞれが独立したトランザクションになります。途中で失敗すると、それより前の文の結果は残ります。

ユーザーを3つのテーブルに分配する例のように、まとめて成功か失敗かにしたい場合は、プロシージャの中でトランザクションを制御します。RESIGNAL を忘れると、エラーが UCIDM に伝わらず連携が成功したことになります。

CREATE PROCEDURE ucidm_apply_user(p JSON)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  -- ここに反映処理を書く
  COMMIT;
END

真偽値は TINYINT で表す

MySQL に真偽値型はありません。TINYINT(1) のカラムを取得ファンクションでそのまま返すと 0 や 1 という数値になります。JSON の真偽値として返したい場合は u.active = 1 のように比較式にします。

エラーメッセージに位置情報が付かない

Oracle や PostgreSQL と違い、MySQL はプロシージャのどの文で失敗したかを返しません。連携履歴に残るのはデータベースのエラーメッセージだけです。どこで失敗したかを絞り込むには、mysql クライアントで同じ JSON を渡して呼び出し、文を分けて確かめてください。

SIGNAL SQLSTATE '45000' で返した MESSAGE_TEXT はそのまま履歴に残るので、検査を入れる箇所ではメッセージに文脈を含めておくと調査が楽になります。

MariaDB での違い

MariaDB でも同じ設計で動きますが、2箇所を書き換えます。

1つ目は JSON の値の取り出しです。p->>'$.opCode' の代わりに JSON_VALUE(p, '$.opCode') を使います。

2つ目は ON DUPLICATE KEY UPDATE の書き方です。MySQL 8.0.20 以降で使える行エイリアス (... VALUES (...) AS new ON DUPLICATE KEY UPDATE email = new.email) は MariaDB にないので、VALUES() 関数の形にします。

INSERT INTO emp_contact(user_name, email) VALUES (pid, JSON_VALUE(p, '$.attrs.mail'))
  ON DUPLICATE KEY UPDATE email = VALUES(email);

残りの構文 (ELSEIF、STR_TO_DATE、INSERT IGNORE、LAST_INSERT_ID()、READS SQL DATA) はどちらでも同じです。

Last updated on unknown (main@)