1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@pmc-ko(K Ohtsuka)in株式会社ピー・エム・シー ITソリューション事業部

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

1
Posted at

はじめに

プリザンターは 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 の内部フロー

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

Update の内部フロー

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

ストアドプロシージャ

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 操作を紹介します。

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?