0
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だけで実現してみる[第5回：Wikisテーブル編]

0
Posted at

はじめに

前回は Results テーブルと Issues テーブルの CRUD 操作を紹介しました。最終回となる第 5 回では、Wikis テーブルの CRUD 操作を見ていきます。

Wiki はプリザンターの 3 つ目のデータテーブルで、ナレッジベースやドキュメント管理に使用されます。Results や Issues と同じく Items テーブルとの連携が必要ですが、テーブル構造自体はシンプルです。

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

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

Wikis テーブル

Results・Issues テーブルとの違い

Wikis テーブルは Results や Issues と同様に Sites・Items テーブルと連携しますが、いくつかの重要な違いがあります。

特徴 Results / Issues Wikis
1 サイトあたりのレコード数 複数レコード 1 レコード
Status・Manager・Owner あり なし
拡張カラム（ClassA 〜 Z 等） あり あり
_history テーブル あり あり
_deleted テーブル あり あり

最も大きな違いは、Wiki サイトには 1 つのサイトに 1 つの Wiki レコードしか存在しないという点です。

テーブル構造（主要カラム）

カラム 型 NULL 説明
SiteId bigint NO サイト ID
WikiId bigint NO Wiki ID（主キー、IDENTITY）
Ver int NO バージョン番号
Title nvarchar(1024) YES タイトル
Body nvarchar(max) YES 本文（Markdown）
Locked bit YES レコードロックフラグ
Comments nvarchar(max) YES コメント（JSON）
Creator int NO 作成者 UserId
Updator int NO 更新者 UserId
CreatedTime datetime NO 作成日時
UpdatedTime datetime NO 更新日時

Results や Issues と同じく、ClassA 〜 ClassZ、NumA 〜 NumZ、DateA 〜 DateZ、DescriptionA 〜 DescriptionZ、CheckA 〜 CheckZ、AttachmentsA 〜 AttachmentsZ の拡張カラムを持っています。

API 内部実装の解析

Create の内部フロー

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

  1. 拡張 SQL の実行（OnCreatingExtendedSqls）
  2. Rds.InsertItems() で Items テーブルに INSERT（ReferenceType = 'Wikis'）
  3. Rds.InsertWikis() で Wikis テーブルに INSERT
  4. SCOPE_IDENTITY() で WikiId を取得
  5. Rds.UpdateItems() で Items テーブルの Title・FullText を更新
  6. 拡張 SQL の実行（OnCreatedExtendedSqls）

Wiki の作成は、通常 Sites テーブルの作成とセットで行われます。

Update の内部フロー

  1. SetByBeforeUpdateServerScript() で更新前サーバスクリプトを実行
  2. Versions.VerUp() でバージョンアップが必要か判定
  3. バージョンアップする場合、Rds.WikisCopyToStatement() で現在のレコードを Wikis_history にコピー
  4. Ver をインクリメント
  5. Rds.UpdateWikis() で Wikis テーブルを UPDATE
  6. UpdateRelatedRecords() で Items テーブルの Title・FullText を同期
  7. SetByAfterUpdateServerScript() で更新後サーバスクリプトを実行

ストアドプロシージャ

Wiki の作成（サイト作成込み）

Wiki サイトの作成は Sites → Items（サイト）→ Wikis → Items（Wiki）→ Permissions の順序で行います。

CREATE PROCEDURE [dbo].[sp_CreateWiki]
    @TenantId INT,
    @UserId INT,
    @Title NVARCHAR(1024),
    @Body NVARCHAR(MAX) = NULL,
    @ParentSiteId BIGINT = 0,
    @InheritPermission BIGINT = 0,
    @NewSiteId BIGINT OUTPUT,
    @NewWikiId BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 1. Sitesテーブルにサイトを作成
        INSERT INTO [Sites] (
            [TenantId], [Ver], [SiteName], [Title], [Body],
            [ReferenceType], [ParentId], [InheritPermission],
            [SiteSettings], [Publish],
            [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        VALUES (
            @TenantId, 1, @Title, @Title, N'',
            N'Wikis', @ParentSiteId, @InheritPermission,
            N'{"Version":1.017}', 0,
            @UserId, @UserId, GETDATE(), GETDATE()
        );

        SET @NewSiteId = SCOPE_IDENTITY();

        -- 2. Itemsテーブルにサイトのエントリを追加
        INSERT INTO [Items] (
            [ReferenceId], [ReferenceType], [SiteId], [Title],
            [FullText], [SearchIndexCreatedTime], [UpdatedTime]
        )
        VALUES (
            @NewSiteId, N'Sites', @NewSiteId, @Title,
            @Title, GETDATE(), GETDATE()
        );

        -- 3. Wikisテーブルにレコードを作成
        INSERT INTO [Wikis] (
            [SiteId], [Ver], [Title], [Body],
            [Locked], [Comments],
            [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        VALUES (
            @NewSiteId, 1, @Title, @Body,
            0, N'[]',
            @UserId, @UserId, GETDATE(), GETDATE()
        );

        SET @NewWikiId = SCOPE_IDENTITY();

        -- 4. ItemsテーブルにWikiのエントリを追加
        INSERT INTO [Items] (
            [ReferenceId], [ReferenceType], [SiteId], [Title],
            [FullText], [SearchIndexCreatedTime], [UpdatedTime]
        )
        VALUES (
            @NewWikiId, N'Wikis', @NewSiteId, @Title,
            @Title + N' ' + ISNULL(@Body, N''),
            GETDATE(), GETDATE()
        );

        -- 5. 権限の設定（作成者にフル権限を付与）
        INSERT INTO [Permissions] (
            [ReferenceId], [DeptId], [GroupId], [UserId],
            [DeptName], [GroupName], [Name], [PermissionType],
            [Creator], [Updator], [CreatedTime], [UpdatedTime]
        )
        VALUES (
            @NewSiteId, 0, 0, @UserId,
            N'', N'', N'', 511,
            @UserId, @UserId, GETDATE(), GETDATE()
        );

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

実運用では SiteSettings にカラム定義やビュー設定が含まれます。上記の例では最小限の JSON のみ設定しています。カラム定義を追加する場合は、プリザンターの管理画面から設定するか、適切な JSON を構築してください。

Wiki の更新（履歴保存 + Items 同期）

CREATE PROCEDURE [dbo].[sp_UpdateWiki]
    @WikiId BIGINT,
    @UserId INT,
    @Title NVARCHAR(1024) = NULL,
    @Body NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 更新前のレコードを履歴テーブルに保存
        INSERT INTO [Wikis_history]
        SELECT * FROM [Wikis]
        WHERE [WikiId] = @WikiId;

        -- Wikisテーブルの更新
        UPDATE [Wikis]
        SET
            [Title] = ISNULL(@Title, [Title]),
            [Body] = ISNULL(@Body, [Body]),
            [Ver] = [Ver] + 1,
            [Updator] = @UserId,
            [UpdatedTime] = GETDATE()
        WHERE
            [WikiId] = @WikiId;

        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR(N'対象のWikiが見つかりません。', 16, 1);
        END

        -- Itemsテーブルの同期
        DECLARE @CurrentTitle NVARCHAR(1024);
        DECLARE @CurrentBody NVARCHAR(MAX);
        SELECT @CurrentTitle = [Title], @CurrentBody = [Body]
        FROM [Wikis] WHERE [WikiId] = @WikiId;

        UPDATE [Items]
        SET
            [Title] = ISNULL(@CurrentTitle, N''),
            [FullText] = ISNULL(@CurrentTitle, N'') + N' ' + ISNULL(@CurrentBody, N''),
            [SearchIndexCreatedTime] = GETDATE(),
            [UpdatedTime] = GETDATE()
        WHERE
            [ReferenceId] = @WikiId;

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

Wiki の削除（ゴミ箱に移動）

CREATE PROCEDURE [dbo].[sp_DeleteWiki]
    @WikiId BIGINT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Wikis_deletedテーブルへコピー
        INSERT INTO [Wikis_deleted]
        SELECT * FROM [Wikis]
        WHERE [WikiId] = @WikiId;

        -- Wikisテーブルから削除
        DELETE FROM [Wikis]
        WHERE [WikiId] = @WikiId;

        -- Itemsテーブルから削除（Wikiレコード）
        DELETE FROM [Items]
        WHERE [ReferenceId] = @WikiId;

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

Wiki の物理削除（サイトごと完全削除）

CREATE PROCEDURE [dbo].[sp_PhysicalDeleteWiki]
    @WikiId BIGINT,
    @SiteId BIGINT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 履歴の削除
        DELETE FROM [Wikis_history]
        WHERE [WikiId] = @WikiId;

        -- ゴミ箱の削除
        DELETE FROM [Wikis_deleted]
        WHERE [WikiId] = @WikiId;

        -- 添付ファイルの削除
        DELETE FROM [Binaries]
        WHERE [ReferenceId] = @WikiId;

        DELETE FROM [Binaries_deleted]
        WHERE [ReferenceId] = @WikiId;

        -- Itemsテーブルの削除（WikiレコードとSiteレコード）
        DELETE FROM [Items]
        WHERE [ReferenceId] = @WikiId
            OR [ReferenceId] = @SiteId;

        -- 権限の削除
        DELETE FROM [Permissions]
        WHERE [ReferenceId] = @SiteId;

        -- Wikisテーブルの削除
        DELETE FROM [Wikis]
        WHERE [WikiId] = @WikiId;

        -- Sitesテーブルの削除
        DELETE FROM [Sites]
        WHERE [SiteId] = @SiteId;

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

Read（読取）

Wiki の一覧取得

特定テナントの全 Wiki を取得します。

SELECT
    w.[WikiId],
    w.[SiteId],
    i.[Title],
    w.[Body],
    w.[CreatedTime],
    w.[UpdatedTime]
FROM
    [Wikis] w
    INNER JOIN [Items] i
        ON w.[WikiId] = i.[ReferenceId]
    INNER JOIN [Sites] s
        ON w.[SiteId] = s.[SiteId]
WHERE
    s.[TenantId] = @TenantId
ORDER BY
    w.[UpdatedTime] DESC;

Wiki の変更履歴を取得

SELECT
    wh.[Ver],
    wh.[Title],
    wh.[Body],
    wh.[Updator],
    u.[Name] AS [UpdaterName],
    wh.[UpdatedTime]
FROM
    [Wikis_history] wh
    LEFT JOIN [Users] u
        ON wh.[Updator] = u.[UserId]
WHERE
    wh.[WikiId] = @WikiId
ORDER BY
    wh.[Ver] DESC;

連載のまとめ

全 5 回にわたって、プリザンターの API 内部実装を解析し、CRUD 操作をストアドプロシージャとして再現する方法を紹介しました。

回 テーブル ポイント
第 1 回 Depts（組織） 共通設計（TenantId、監査カラム、Ver、_history、Disabled）
第 2 回 Groups / GroupMembers 3 パターンのメンバー追加（ユーザー・組織・子グループ）、作成者の管理者自動追加
第 3 回 Users LoginId 重複チェック、パスワードハッシュ、セキュリティ考慮事項
第 4 回 Results / Issues Items テーブルとの連携、拡張カラム、_history・_deleted テーブル
第 5 回 Wikis 1 サイト 1 レコードの特殊性、サイト作成 + 権限設定を含むフル作成フロー

すべてのテーブルに共通する API 内部実装のパターン

  • 作成時: メインテーブルに INSERT → SCOPE_IDENTITY() で ID 取得
  • 更新時: _history テーブルに更新前レコードをコピー → メインテーブルを UPDATE → Ver インクリメント
  • 削除時（マスタ）: Disabled フラグによる論理削除
  • 削除時（データ）: _deleted テーブルへの移動（ゴミ箱）

共通する注意点

  • バックアップの取得: 直接 SQL を実行する前に必ずデータベースのバックアップを取得する
  • TenantId の指定: マスタテーブルでは必ず TenantId を条件に含める
  • 監査カラムの設定: Creator・Updator・CreatedTime・UpdatedTime は INSERT・UPDATE 時に必ず設定する
  • Ver のインクリメント: 楽観的排他制御のため UPDATE 時に Ver をインクリメントする
  • _history テーブルへの保存: 更新前のレコードを必ず _history テーブルにコピーする
  • Items テーブルの同期: Results・Issues・Wikis の操作時は Items テーブルも同期する
  • トランザクション: 複数テーブルにまたがる操作はトランザクションで囲む

これらのストアドプロシージャを活用することで、一括データ移行やバッチ処理など、GUI や API では難しい操作も効率的に行えるようになります。

0
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
0
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?