プリザンターの内部CRUD操作をSQLだけで実現してみる[第2回：Groupsテーブル編]

Posted at 2026-04-03

はじめに

前回は共通設計と Depts（組織）テーブルの CRUD 操作を紹介しました。第 2 回では、Groups テーブルと GroupMembers テーブルを使ったグループ管理の CRUD 操作を見ていきます。

回	テーマ
第 1 回	概要と Depts テーブル編 — 共通設計と組織テーブルの CRUD
第 2 回（本記事）	Groups テーブル編 — グループとメンバー管理の CRUD
第 3 回	Users テーブル編 — ユーザー管理の CRUD
第 4 回	Results・Issues テーブル編 — レコードデータの CRUD
第 5 回	Wikis テーブル編 — Wiki の CRUD

本連載の SQL は SQL Server を対象にしています。PostgreSQL や MySQL を使用している場合は、関数名やデータ型を適宜読み替えてください。

Groups テーブル

Groups テーブルはユーザーをグループ化するためのマスタテーブルです。権限管理やメール通知のグルーピングに使用されます。

テーブル構造

カラム	型	NULL	説明
`TenantId`	int	NO	テナント ID（複合主キー 1）
`GroupId`	int	NO	グループ ID（複合主キー 2、IDENTITY）
`Ver`	int	NO	バージョン番号
`GroupName`	nvarchar(256)	YES	グループ名
`Body`	nvarchar(max)	YES	説明
`Disabled`	bit	NO	無効フラグ（既定値: 0）
`Creator`	int	NO	作成者 UserId
`Updator`	int	NO	更新者 UserId
`CreatedTime`	datetime	NO	作成日時
`UpdatedTime`	datetime	NO	更新日時

主キーは (TenantId, GroupId) の複合主キーです。GroupId は IDENTITY（自動採番）です。

API 内部実装の解析

Create の内部フロー

GroupModel.Create() を解析すると、以下の順序で処理されます。

TenantId にコンテキストのテナント ID を設定
Rds.InsertGroups() で Groups テーブルに INSERT
Rds.InsertGroupMembers() で作成者自身を管理者として GroupMembers に追加
API 経由の場合、リクエストで指定されたメンバー（ユーザー・組織・子グループ）を追加
GroupMemberUtilities.SyncGroupMembers() でグループメンバーの同期処理を実行

Update の内部フロー

Versions.VerUp() でバージョンアップが必要か判定
バージョンアップする場合、Rds.GroupsCopyToStatement() で現在のレコードを Groups_history にコピー
Ver をインクリメント
Rds.UpdateGroups() で Groups テーブルを UPDATE
GroupMembers のメンバーリストを更新（全入れ替え方式）
GroupMemberUtilities.SyncGroupMembers() で同期処理を実行

ストアドプロシージャ

グループの作成（作成者を管理者として自動追加）

CREATE PROCEDURE [dbo].[sp_CreateGroup]
    @TenantId INT,
    @UserId INT,
    @GroupName NVARCHAR(256) = NULL,
    @Body NVARCHAR(MAX) = NULL,
    @NewGroupId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- グループの作成
        INSERT INTO [Groups] (
            [TenantId], [Ver], [GroupName], [Body],
            [Disabled], [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        VALUES (
            @TenantId, 1, @GroupName, @Body,
            0, @UserId, @UserId, GETDATE(), GETDATE()
        );

        SET @NewGroupId = SCOPE_IDENTITY();

        -- 作成者を管理者としてグループに追加（API内部実装と同じ）
        INSERT INTO [GroupMembers] (
            [GroupId], [DeptId], [UserId], [ChildGroup], [Admin],
            [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        VALUES (
            @NewGroupId, 0, @UserId, 0, 1,
            @UserId, @UserId, GETDATE(), GETDATE()
        );

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

グループの更新（履歴保存付き）

CREATE PROCEDURE [dbo].[sp_UpdateGroup]
    @TenantId INT,
    @GroupId INT,
    @UserId INT,
    @GroupName NVARCHAR(256) = NULL,
    @Body NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 更新前のレコードを履歴テーブルに保存
        INSERT INTO [Groups_history]
        SELECT * FROM [Groups]
        WHERE [TenantId] = @TenantId
            AND [GroupId] = @GroupId;

        -- メインテーブルを更新
        UPDATE [Groups]
        SET
            [GroupName] = @GroupName,
            [Body] = @Body,
            [Ver] = [Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = GETDATE()
        WHERE
            [TenantId] = @TenantId
            AND [GroupId] = @GroupId
            AND [Disabled] = 0;

        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR(N'対象のグループが見つからないか、無効化されています。', 16, 1);
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

グループの論理削除（履歴保存付き）

CREATE PROCEDURE [dbo].[sp_DeleteGroup]
    @TenantId INT,
    @GroupId INT,
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 削除前のレコードを履歴テーブルに保存
        INSERT INTO [Groups_history]
        SELECT * FROM [Groups]
        WHERE [TenantId] = @TenantId
            AND [GroupId] = @GroupId;

        -- 論理削除
        UPDATE [Groups]
        SET
            [Disabled] = 1,
            [Ver] = [Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = GETDATE()
        WHERE
            [TenantId] = @TenantId
            AND [GroupId] = @GroupId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

Read（読取）

グループの一覧取得

SELECT
    [GroupId],
    [GroupName],
    [Body],
    [CreatedTime],
    [UpdatedTime]
FROM
    [Groups]
WHERE
    [TenantId] = @TenantId
    AND [Disabled] = 0
ORDER BY
    [GroupId];

グループとメンバー数を一緒に取得

SELECT
    g.[GroupId],
    g.[GroupName],
    COUNT(gm.[UserId]) AS [MemberCount]
FROM
    [Groups] g
    LEFT JOIN [GroupMembers] gm
        ON g.[GroupId] = gm.[GroupId]
        AND gm.[UserId] > 0
WHERE
    g.[TenantId] = @TenantId
    AND g.[Disabled] = 0
GROUP BY
    g.[GroupId],
    g.[GroupName]
ORDER BY
    g.[GroupId];

GroupMembers テーブル

GroupMembers テーブルはグループへの所属を管理する中間テーブルです。ユーザー単位の所属だけでなく、組織単位の所属やグループのネストにも対応しています。

テーブル構造

カラム	型	NULL	説明
`GroupId`	int	NO	グループ ID（複合主キー 1）
`DeptId`	int	NO	組織 ID（複合主キー 2）
`UserId`	int	NO	ユーザー ID（複合主キー 3）
`ChildGroup`	bit	NO	子グループフラグ（複合主キー 4、既定値: 0）
`Admin`	bit	NO	管理者フラグ（既定値: 0）
`Creator`	int	NO	作成者 UserId
`Updator`	int	NO	更新者 UserId
`CreatedTime`	datetime	NO	作成日時
`UpdatedTime`	datetime	NO	更新日時

主キーは (GroupId, DeptId, UserId, ChildGroup) の 4 カラム複合主キーです。

メンバー追加のパターン

GroupMembers には 3 つのメンバー追加パターンがあります。

パターン	DeptId	UserId	ChildGroup	説明
ユーザー単位	0	ユーザーID	0	特定ユーザーをメンバーに追加
組織単位	組織ID	0	0	組織に所属する全ユーザーをメンバーに追加
子グループ	0	子GroupId	1	別のグループを子グループとしてネスト

使用しないキーカラムには 0 を設定します。

メンバー追加のストアドプロシージャ

ユーザーをグループに追加

CREATE PROCEDURE [dbo].[sp_AddGroupMemberUser]
    @GroupId INT,
    @MemberUserId INT,
    @IsAdmin BIT = 0,
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [GroupMembers] (
        [GroupId], [DeptId], [UserId], [ChildGroup], [Admin],
        [Creator], [Updator], [CreatedTime], [UpdatedTime]
    )
    VALUES (
        @GroupId, 0, @MemberUserId, 0, @IsAdmin,
        @UserId, @UserId, GETDATE(), GETDATE()
    );
END;

組織をグループに追加

CREATE PROCEDURE [dbo].[sp_AddGroupMemberDept]
    @GroupId INT,
    @DeptId INT,
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [GroupMembers] (
        [GroupId], [DeptId], [UserId], [ChildGroup], [Admin],
        [Creator], [Updator], [CreatedTime], [UpdatedTime]
    )
    VALUES (
        @GroupId, @DeptId, 0, 0, 0,
        @UserId, @UserId, GETDATE(), GETDATE()
    );
END;

子グループとして追加

CREATE PROCEDURE [dbo].[sp_AddGroupMemberChildGroup]
    @ParentGroupId INT,
    @ChildGroupId INT,
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [GroupMembers] (
        [GroupId], [DeptId], [UserId], [ChildGroup], [Admin],
        [Creator], [Updator], [CreatedTime], [UpdatedTime]
    )
    VALUES (
        @ParentGroupId, 0, @ChildGroupId, 1, 0,
        @UserId, @UserId, GETDATE(), GETDATE()
    );
END;

子グループとして追加する場合、子グループの GroupId は UserId カラムに格納されます。ChildGroup フラグが 1 の場合、UserId カラムの値はユーザー ID ではなくグループ ID として解釈されます。

Read（読取）

グループのメンバー一覧

ユーザー単位のメンバーを一覧取得します。

SELECT
    gm.[UserId],
    u.[LoginId],
    u.[Name],
    gm.[Admin]
FROM
    [GroupMembers] gm
    INNER JOIN [Users] u
        ON gm.[UserId] = u.[UserId]
        AND u.[TenantId] = @TenantId
WHERE
    gm.[GroupId] = @GroupId
    AND gm.[DeptId] = 0
    AND gm.[ChildGroup] = 0
    AND gm.[UserId] > 0
    AND u.[Disabled] = 0
ORDER BY
    u.[Name];

組織単位のメンバーを含めた全メンバー

-- ユーザー直接追加
SELECT
    u.[UserId],
    u.[LoginId],
    u.[Name],
    gm.[Admin],
    N'ユーザー' AS [MemberType]
FROM
    [GroupMembers] gm
    INNER JOIN [Users] u
        ON gm.[UserId] = u.[UserId]
        AND u.[TenantId] = @TenantId
WHERE
    gm.[GroupId] = @GroupId
    AND gm.[ChildGroup] = 0
    AND gm.[UserId] > 0
    AND u.[Disabled] = 0

UNION

-- 組織単位で追加されたユーザー
SELECT
    u.[UserId],
    u.[LoginId],
    u.[Name],
    0 AS [Admin],
    N'組織（' + d.[DeptName] + N'）' AS [MemberType]
FROM
    [GroupMembers] gm
    INNER JOIN [Depts] d
        ON gm.[DeptId] = d.[DeptId]
        AND d.[TenantId] = @TenantId
    INNER JOIN [Users] u
        ON d.[DeptId] = u.[DeptId]
        AND u.[TenantId] = @TenantId
WHERE
    gm.[GroupId] = @GroupId
    AND gm.[DeptId] > 0
    AND gm.[ChildGroup] = 0
    AND d.[Disabled] = 0
    AND u.[Disabled] = 0

ORDER BY
    [Name];

メンバー削除

特定のユーザーをグループから削除

DELETE FROM [GroupMembers]
WHERE
    [GroupId] = @GroupId
    AND [UserId] = @MemberUserId
    AND [DeptId] = 0
    AND [ChildGroup] = 0;

特定の組織をグループから削除

DELETE FROM [GroupMembers]
WHERE
    [GroupId] = @GroupId
    AND [DeptId] = @DeptId
    AND [UserId] = 0
    AND [ChildGroup] = 0;

グループの全メンバーを削除

DELETE FROM [GroupMembers]
WHERE [GroupId] = @GroupId;

まとめ

第 2 回では、Groups テーブルと GroupMembers テーブルの CRUD 操作を紹介しました。

Groups テーブルは Depts テーブルと同様に TenantId を含む複合主キーを持つ
API の内部実装では、グループ作成時に作成者自身が管理者として自動追加される
更新時は Groups_history テーブルに更新前のレコードを保存する
GroupMembers テーブルは 4 カラムの複合主キーで、ユーザー・組織・子グループの 3 パターンに対応する
子グループの場合、UserId カラムに子グループの GroupId が入り ChildGroup フラグが 1 になる
グループの物理削除時は、GroupMembers と Permissions の関連レコードも削除が必要

次回は Users テーブルの CRUD 操作を紹介します。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up