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

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

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

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

GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON empdb.* TO 'ucidm'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON empdb.* TO 'ucidm'@'%';

-- ユーザーの基本情報
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');

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

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'
)

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

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'
)

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

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'
)

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

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"}');

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;

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

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

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