はじめに

プリザンターは GUI や API を通じてデータの作成・読取・更新・削除（CRUD）を行いますが、内部的にはそれらの操作が SQL に変換されて実行されています。運用やデータ移行の場面では、プリザンターを介さずに SQL だけで直接データを操作したいケースがあります。

本連載では、プリザンターの API 実装を解析し、内部で実行される SQL 操作をストアドプロシージャとして再現する方法を、テーブルごとに紹介していきます。

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

データベースを直接操作する場合は、必ず事前にバックアップを取得してください。プリザンターの内部整合性が崩れると、アプリケーションが正常に動作しなくなる可能性があります。

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

バージョン 1.4 系を対象にしています

プリザンターのテーブル共通設計

SQL を書く前に、プリザンターのテーブルに共通する設計パターンを押さえておきましょう。

マルチテナント設計

Depts、Groups、Users、Sites などのマスタテーブルは、TenantId カラムを複合主キーの先頭に持っています。SQL を書く際は必ず TenantId を条件に含めてください。

監査カラム

ほとんどのテーブルに以下の監査カラムが存在します。INSERT や UPDATE の際に適切な値を設定する必要があります。

カラム	型	説明
`Creator`	int	レコード作成者の `UserId`
`Updator`	int	最終更新者の `UserId`
`CreatedTime`	datetime	レコード作成日時
`UpdatedTime`	datetime	最終更新日時

楽観的排他制御（Ver カラム）

Ver カラム（int 型）は楽観的排他制御に使用されます。プリザンターは更新時に Ver をインクリメントし、競合を検出します。SQL で更新する場合も Ver を適切にインクリメントしてください。

履歴テーブル（_history）

プリザンターはすべてのテーブルに対応する _history テーブル（Depts_history、Groups_history、Users_history、Results_history、Issues_history、Wikis_history）を持っています。

API の内部実装では、更新時にバージョンがインクリメントされる場合、更新前の現在のレコードを _history テーブルにコピーしてから本テーブルを更新します。この仕組みにより、プリザンターの画面から過去のバージョンを参照できるようになっています。

論理削除（Disabled カラム）

マスタテーブルには Disabled カラム（bit 型）があり、論理削除に使用されます。1 が無効、0 が有効です。プリザンターの画面からの削除操作は、この値を 1 に変更する論理削除です。

Depts テーブル

Depts（組織）テーブルは、プリザンターのマスタテーブルの中でも最もシンプルな構造をしています。まずはここから CRUD 操作を見ていきましょう。

テーブル構造

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

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

API 内部実装の解析

プリザンターの DeptModel.cs を解析すると、CRUD 操作は以下の順序で実行されていることがわかります。

Create の内部フロー

TenantId にコンテキストのテナント ID を設定
Rds.InsertDepts() で Depts テーブルに INSERT
StatusUtilities.UpdateStatus() でステータスを更新
トランザクション内で一括実行し、SCOPE_IDENTITY() で DeptId を取得

Update の内部フロー

Versions.VerUp() でバージョンアップが必要か判定
バージョンアップする場合、Rds.DeptsCopyToStatement() で現在のレコードを Depts_history にコピー
Ver をインクリメント
Rds.UpdateDepts() で Depts テーブルを UPDATE
楽観的排他制御による競合チェック

ストアドプロシージャ

API の内部実装を再現したストアドプロシージャを紹介します。

組織の作成

CREATE PROCEDURE [dbo].[sp_CreateDept]
    @TenantId INT,
    @UserId INT,
    @DeptCode NVARCHAR(1024) = NULL,
    @DeptName NVARCHAR(1024) = NULL,
    @Body NVARCHAR(MAX) = NULL,
    @NewDeptId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO [Depts] (
            [TenantId], [Ver], [DeptCode], [DeptName], [Body],
            [Disabled], [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        VALUES (
            @TenantId, 1, @DeptCode, @DeptName, @Body,
            0, @UserId, @UserId, GETDATE(), GETDATE()
        );

        SET @NewDeptId = SCOPE_IDENTITY();

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

組織の更新（履歴保存付き）

プリザンターの API 実装と同様に、更新前のレコードを Depts_history テーブルに保存してからメインテーブルを更新します。

CREATE PROCEDURE [dbo].[sp_UpdateDept]
    @TenantId INT,
    @DeptId INT,
    @UserId INT,
    @DeptCode NVARCHAR(1024) = NULL,
    @DeptName NVARCHAR(1024) = NULL,
    @Body NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 更新前のレコードを履歴テーブルに保存
        INSERT INTO [Depts_history]
        SELECT * FROM [Depts]
        WHERE [TenantId] = @TenantId
            AND [DeptId] = @DeptId;

        -- メインテーブルを更新
        UPDATE [Depts]
        SET
            [DeptCode] = @DeptCode,
            [DeptName] = @DeptName,
            [Body] = @Body,
            [Ver] = [Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = GETDATE()
        WHERE
            [TenantId] = @TenantId
            AND [DeptId] = @DeptId
            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_DeleteDept]
    @TenantId INT,
    @DeptId INT,
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 削除前のレコードを履歴テーブルに保存
        INSERT INTO [Depts_history]
        SELECT * FROM [Depts]
        WHERE [TenantId] = @TenantId
            AND [DeptId] = @DeptId;

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

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

組織の物理削除

物理削除は関連テーブルへの影響が大きいため、すべての参照を解除してから削除します。

CREATE PROCEDURE [dbo].[sp_PhysicalDeleteDept]
    @TenantId INT,
    @DeptId INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 履歴テーブルの削除
        DELETE FROM [Depts_history]
        WHERE [TenantId] = @TenantId
            AND [DeptId] = @DeptId;

        -- 関連する権限の削除
        DELETE FROM [Permissions]
        WHERE [DeptId] = @DeptId;

        -- 関連するグループメンバーの削除
        DELETE FROM [GroupMembers]
        WHERE [DeptId] = @DeptId;

        -- ユーザーの所属組織をクリア
        UPDATE [Users]
        SET [DeptId] = 0
        WHERE [TenantId] = @TenantId
            AND [DeptId] = @DeptId;

        -- 組織の削除
        DELETE FROM [Depts]
        WHERE [TenantId] = @TenantId
            AND [DeptId] = @DeptId;

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

物理削除は、プリザンターの標準的な削除方法ではありません。関連テーブルへの影響を十分に確認した上で実行してください。通常は論理削除を推奨します。

Read（読取）

組織の一覧取得

特定テナントの有効な組織を一覧取得します。

SELECT
    [DeptId],
    [DeptCode],
    [DeptName],
    [Body],
    [CreatedTime],
    [UpdatedTime]
FROM
    [Depts]
WHERE
    [TenantId] = @TenantId
    AND [Disabled] = 0
ORDER BY
    [DeptId];

組織コードで検索

SELECT
    [DeptId],
    [DeptCode],
    [DeptName],
    [Body]
FROM
    [Depts]
WHERE
    [TenantId] = @TenantId
    AND [DeptCode] = @DeptCode
    AND [Disabled] = 0;

組織の変更履歴を取得

SELECT
    [Ver],
    [DeptCode],
    [DeptName],
    [Updator],
    [UpdatedTime]
FROM
    [Depts_history]
WHERE
    [TenantId] = @TenantId
    AND [DeptId] = @DeptId
ORDER BY
    [Ver] DESC;

操作後の確認

CRUD 操作を行った後は、プリザンターの管理画面から対象データが正しく表示されることを確認しましょう。特に以下の点を確認してください。

TenantId が正しいテナントを指しているか
Ver が正しくインクリメントされているか
Creator・Updator に有効な UserId が設定されているか
CreatedTime・UpdatedTime が妥当な日時になっているか
Depts_history テーブルに更新前のレコードが保存されているか

まとめ

第 1 回では、プリザンターのテーブル共通設計と Depts（組織）テーブルの CRUD 操作を紹介しました。

マスタテーブルは TenantId を含む複合主キーを持つ
監査カラム（Creator、Updator、CreatedTime、UpdatedTime）は INSERT・UPDATE 時に必ず設定する
Ver カラムによる楽観的排他制御を意識する
更新時は _history テーブルに更新前のレコードを保存する（API と同じ動作）
削除は Disabled フラグによる論理削除が推奨

次回は Groups テーブルとグループメンバー管理の CRUD 操作を紹介します。