内部 CRUD 操作を SQL だけで実現する
プリザンターの GUI や API による作成・読取・更新・削除（CRUD）は、内部で SQL に変換されて実行されています。運用やデータ移行でプリザンターを介さずに SQL だけでデータを操作したい場合に備え、API の内部実装を解析して、その SQL 操作をストアドプロシージャとして再現する方法をテーブルごとにまとめます。
データベースを直接書き換える操作です
- このページの SQL は、プリザンターのデータベースを直接変更します。必ず事前にデータベースのバックアップを取得してください。
- プリザンターの内部整合性が崩れると、アプリケーションが正常に動作しなくなる可能性があります。
- このページは SQL Server 専用です。T-SQL のストアドプロシージャ・テーブル値パラメータ・トランザクション制御を使うため、PostgreSQL / MySQL ではそのまま実行できません。
- 元の連載は 1.4 系を対象にしています。このページでは、列定義・ID の採番・競合チェックを 1.5.8.1 のソースで確認し、合わない箇所を直しています。
テーブル共通の設計
SQL を書く前に、プリザンターのテーブルに共通する設計パターンを押さえておきます。
マルチテナント設計
Depts、Groups、Users、Sites などのマスタテーブルは、TenantId カラムを複合主キーの先頭に持っています。SQL を書く際は必ず TenantId を条件に含めてください。
監査カラム
ほとんどのテーブルに次の監査カラムがあり、INSERT・UPDATE の際に適切な値を設定する必要があります。
| カラム | 型 | 説明 |
|---|---|---|
Creator | int | レコード作成者の UserId |
Updator | int | 最終更新者の UserId |
CreatedTime | datetime | レコード作成日時 |
UpdatedTime | datetime | 最終更新日時 |
バージョン（Ver カラム）
Ver カラム（int 型）はレコードのバージョン番号です。プリザンターは更新でバージョンアップする場合（verUp が true のとき）に、更新前のレコードを _history テーブルへコピーしてから Ver をインクリメントします。バージョンアップしない更新では Ver は変わりません（1.5.8.1 のソースでは DeptModel.cs#L1066-L1073）。SQL で更新する場合も、履歴を残すときは Ver を適切にインクリメントしてください。
楽観的排他制御について
競合の検知に使うのは Ver ではなく UpdatedTime です。更新時の WHERE 句に、画面や API から受け取ったタイムスタンプ（Timestamp）と一致する UpdatedTime の条件を加え、影響行数が 0 なら IfConflicted = true の SqlStatement で競合エラーにします。Ver はバージョン番号（履歴の管理）に使われ、競合判定には使われません。1.5.8.1 のソースでは Depts / Groups / Users / Issues のどれも同じ実装です（DeptModel.cs#L1065、DeptModel.cs#L1109、IssueModel.cs#L2146-L2151）。詳しくは 内部で動く SQL 文 の UPDATE の説明を参照してください。
履歴テーブル（_history）
各テーブルには対応する _history テーブル（Depts_history、Groups_history、Users_history、Results_history、Issues_history、Wikis_history）があります。
API の内部実装では、更新時にバージョンがインクリメントされる場合、更新前の現在のレコードを _history テーブルにコピーしてから本テーブルを更新します。これによりプリザンターの画面から過去のバージョンを参照できます。
図を読み込み中…
TIP
API の内部実装で _history へコピーするのは、Versions.VerUp() がバージョンアップ要と判定した場合です。このページのストアドプロシージャは、更新・削除のたびに常に _history へコピーする形にしています。
削除の方式
| 対象 | 削除方式 |
|---|---|
| マスタテーブル（Depts / Groups / Users） | Disabled カラム（bit 型、1 が無効、0 が有効）による論理削除。画面からの削除操作はこの値を 1 にする |
| データテーブル（Results / Issues / Wikis） | _deleted テーブルへの移動（ゴミ箱）。ゴミ箱からの復元はこのテーブルのデータを戻す仕組み |
データテーブルと Sites・Items の関係
データテーブル（Results / Issues / Wikis）は Sites・Items テーブルと連携して動作します。
図を読み込み中…
Wikis も同様に Items（ReferenceId = WikiId）と Sites に連携します。
| テーブル | 役割 |
|---|---|
Sites | サイト（テーブル）の定義情報を管理。ReferenceType で記録テーブルか期限付きテーブルかなどを区別する |
Items | 全データレコードのインデックス。全文検索の FullText カラムや表示用 Title を保持する |
Results / Issues / Wikis | 実際のデータを格納するテーブル |
レコードを作成する際は、メインテーブルと Items の両方にレコードを追加する必要があります。
すべてのテーブルに共通する API 内部実装のパターン
| 操作 | パターン |
|---|---|
| 作成 | メインテーブルに INSERT → SCOPE_IDENTITY() で ID 取得（データテーブルは Items → メインテーブルの順） |
| 更新 | _history テーブルに更新前レコードをコピー → メインテーブルを UPDATE → Ver インクリメント |
| 削除（マスタ） | Disabled フラグによる論理削除 |
| 削除（データ） | _deleted テーブルへの移動（ゴミ箱） |
Depts（組織）
マスタテーブルの中で最もシンプルな構造です。
| カラム | 型 | NULL | 説明 |
|---|---|---|---|
TenantId | int | NO | テナント ID（複合主キー 1） |
DeptId | int | NO | 組織 ID（複合主キー 2、IDENTITY） |
Ver | int | NO | バージョン番号 |
DeptCode | nvarchar(1024) | NO | 組織コード |
DeptName | nvarchar(1024) | NO | 組織名 |
Body | nvarchar(max) | YES | 説明 |
Disabled | bit | NO | 無効フラグ（既定値: 0） |
Creator | int | NO | 作成者 UserId |
Updator | int | NO | 更新者 UserId |
CreatedTime | datetime | NO | 作成日時 |
UpdatedTime | datetime | NO | 更新日時 |
DeptCode と DeptName は NULL を許可しないため、下のストアドプロシージャでは引数の既定値を空文字（N''）にしています（1.5.8.1 の列定義 Depts_DeptCode.json には Nullable の指定がありません）。
API 内部実装（DeptModel.cs）
- Create:
TenantIdにコンテキストのテナント ID を設定 →Rds.InsertDepts()で INSERT →StatusUtilities.UpdateStatus()でステータスを更新 → トランザクション内で一括実行し、SCOPE_IDENTITY()でDeptIdを取得 - Update:
Versions.VerUp()でバージョンアップ要否を判定 → 必要ならRds.DeptsCopyToStatement()でDepts_historyにコピー →Verをインクリメント →Rds.UpdateDepts()で UPDATE → 楽観的排他制御による競合チェック
作成
sql
CREATE PROCEDURE [dbo].[sp_CreateDept]
@TenantId INT,
@UserId INT,
@DeptCode NVARCHAR(1024) = N'',
@DeptName NVARCHAR(1024) = N'',
@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;更新（履歴保存付き）
更新前のレコードを Depts_history に保存してからメインテーブルを更新します。
sql
CREATE PROCEDURE [dbo].[sp_UpdateDept]
@TenantId INT,
@DeptId INT,
@UserId INT,
@DeptCode NVARCHAR(1024) = N'',
@DeptName NVARCHAR(1024) = N'',
@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;論理削除（履歴保存付き）
sql
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;物理削除
DANGER
物理削除はプリザンターの標準的な削除方法ではありません。関連テーブルへの影響を十分に確認したうえで実行してください。通常は論理削除を推奨します。
物理削除は関連テーブルへの影響が大きいため、すべての参照を解除してから削除します。
sql
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;読取
sql
-- 有効な組織の一覧
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;Groups（グループ）と GroupMembers
Groups はユーザーをグループ化するマスタテーブルで、権限管理やメール通知のグルーピングに使用されます。構造は Depts と同様で、主キーは (TenantId, GroupId)、GroupId は IDENTITY です。
| カラム | 型 | NULL | 説明 |
|---|---|---|---|
TenantId | int | NO | テナント ID（複合主キー 1） |
GroupId | int | NO | グループ ID（複合主キー 2、IDENTITY） |
Ver | int | NO | バージョン番号 |
GroupName | nvarchar(256) | NO | グループ名 |
Body | nvarchar(max) | YES | 説明 |
Disabled | bit | NO | 無効フラグ（既定値: 0） |
Creator / Updator / CreatedTime / UpdatedTime | NO | 監査カラム |
API 内部実装（GroupModel）
- Create:
TenantIdを設定 →Rds.InsertGroups()で INSERT →Rds.InsertGroupMembers()で作成者自身を管理者としてGroupMembersに追加 → API 経由の場合、リクエストで指定されたメンバー（ユーザー・組織・子グループ）を追加 →GroupMemberUtilities.SyncGroupMembers()で同期処理 - Update:
Versions.VerUp()で判定 → 必要ならRds.GroupsCopyToStatement()でGroups_historyにコピー →Verをインクリメント →Rds.UpdateGroups()で UPDATE →GroupMembersのメンバーリストを更新（全入れ替え方式） →GroupMemberUtilities.SyncGroupMembers()で同期処理
作成（作成者を管理者として自動追加）
sql
CREATE PROCEDURE [dbo].[sp_CreateGroup]
@TenantId INT,
@UserId INT,
@GroupName NVARCHAR(256) = N'',
@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;更新・論理削除
Depts と同じパターンです（Groups_history に SELECT * でコピーしてから、GroupName / Body を UPDATE、または Disabled = 1 に UPDATE。いずれも Ver = Ver + 1、Updator、UpdatedTime を更新）。
sp_UpdateGroup（グループの更新）
sql
CREATE PROCEDURE [dbo].[sp_UpdateGroup]
@TenantId INT,
@GroupId INT,
@UserId INT,
@GroupName NVARCHAR(256) = N'',
@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;sp_DeleteGroup（グループの論理削除）
sql
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;グループの物理削除時は、GroupMembers と Permissions の関連レコードも削除が必要です。
GroupMembers のテーブル構造
GroupMembers はグループへの所属を管理する中間テーブルで、ユーザー単位の所属だけでなく、組織単位の所属やグループのネストにも対応しています。主キーは (GroupId, DeptId, UserId, ChildGroup) の 4 カラム複合主キーです。
| カラム | 型 | 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 / Updator / CreatedTime / UpdatedTime | NO | 監査カラム |
メンバー追加の 3 パターン
使用しないキーカラムには 0 を設定します。
| パターン | DeptId | UserId | ChildGroup | 説明 |
|---|---|---|---|---|
| ユーザー単位 | 0 | ユーザーID | 0 | 特定ユーザーをメンバーに追加 |
| 組織単位 | 組織ID | 0 | 0 | 組織に所属する全ユーザーをメンバーに追加 |
| 子グループ | 0 | 子GroupId | 1 | 別のグループを子グループとしてネスト |
INFO
子グループとして追加する場合、子グループの GroupId は UserId カラムに格納されます。ChildGroup フラグが 1 の場合、UserId カラムの値はユーザー ID ではなくグループ ID として解釈されます。
sql
-- ユーザーをグループに追加
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;組織単位（sp_AddGroupMemberDept）は VALUES (@GroupId, @DeptId, 0, 0, 0, ...)、子グループ（sp_AddGroupMemberChildGroup）は VALUES (@ParentGroupId, 0, @ChildGroupId, 1, 0, ...) になります。
メンバーの読取・削除
sql
-- ユーザー単位のメンバー一覧
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 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];組織単位で追加されたユーザーを含めた全メンバーは、上のユーザー直接追加の SELECT と、GroupMembers → Depts（gm.DeptId = d.DeptId）→ Users（d.DeptId = u.DeptId）を JOIN した SELECT を UNION して取得します。
組織単位のメンバーを含めた全メンバーの取得
sql
-- ユーザー直接追加
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];sql
-- 特定のユーザーをグループから削除
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;Users（ユーザー）
Users テーブルはカラム数が 50 以上あり、認証・認可・セキュリティに関わる重要なテーブルです。CRUD でよく使うカラムに絞って示します。
| 分類 | 主なカラム |
|---|---|
| 基本情報 | TenantId（複合主キー 1）、UserId（複合主キー 2、IDENTITY）、Ver、LoginId（ユニーク）、Name、Password（ハッシュ済み）、DeptId（既定値: 0）、FirstName、LastName、Birthday、Gender、Language、TimeZone、Theme、Body |
| 権限・ロール（bit、既定値: 0） | TenantManager、ServiceManager、Developer、AllowCreationAtTopSite、AllowGroupAdministration、AllowGroupCreation、AllowApi（ほかに ApiKey） |
| セキュリティ | Disabled、Lockout、LockoutCounter、PasswordExpirationTime、PasswordChangeTime、LastLoginTime、EnableSecondaryAuthentication |
API 内部実装（UserModel）
- Create: メールアドレスのバリデーション（API 経由の場合） →
TenantIdを設定 → パスワード履歴の設定（Security.EnforcePasswordHistoriesが有効な場合） → パスワード有効期限の設定 →Rds.InsertUsers()で INSERT →LoginIdの重複チェック（DbExceptionでDuplicateKeyエラーをキャッチ） → メールアドレスの登録（API 経由の場合） - Update: メールアドレスのバリデーション →
Versions.VerUp()で判定 → 必要ならRds.UsersCopyToStatement()でUsers_historyにコピー →Verをインクリメント →Rds.UpdateUsers()で UPDATE → 楽観的排他制御による競合チェック →LoginIdの重複チェック → メールアドレスの更新
作成
パスワードは SQL で設定しない
パスワードはプリザンター内部でハッシュ化（SHA-512 + ソルト）されて格納されます。SQL で直接ユーザーを作成する場合は、パスワードを設定せずに作成し、プリザンターの管理画面からパスワードを設定する運用を推奨します。
sql
CREATE PROCEDURE [dbo].[sp_CreateUser]
@TenantId INT,
@UserId INT,
@LoginId NVARCHAR(256),
@Name NVARCHAR(128),
@FirstName NVARCHAR(256) = NULL,
@LastName NVARCHAR(256) = NULL,
@DeptId INT = 0,
@Language NVARCHAR(16) = N'ja',
@TimeZone NVARCHAR(64) = N'Tokyo Standard Time',
@NewUserId INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
-- LoginIdの重複チェック
IF EXISTS (
SELECT 1 FROM [Users]
WHERE [TenantId] = @TenantId
AND [LoginId] = @LoginId
)
BEGIN
RAISERROR(N'指定されたログインIDは既に使用されています。', 16, 1);
END
INSERT INTO [Users] (
[TenantId], [Ver], [LoginId], [Name], [FirstName], [LastName],
[DeptId], [Language], [TimeZone],
[TenantManager], [AllowCreationAtTopSite],
[AllowGroupAdministration], [AllowGroupCreation], [AllowApi],
[Disabled], [Lockout], [LockoutCounter],
[EnableSecondaryAuthentication], [Developer], [ServiceManager],
[Creator], [Updator], [CreatedTime], [UpdatedTime]
)
VALUES (
@TenantId, 1, @LoginId, @Name, @FirstName, @LastName,
@DeptId, @Language, @TimeZone,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
@UserId, @UserId, GETDATE(), GETDATE()
);
SET @NewUserId = SCOPE_IDENTITY();
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;更新・論理削除
Depts と同じパターンで、Users_history にコピーしてから UPDATE します。更新（sp_UpdateUser）では、指定されなかった項目を維持するために [Name] = ISNULL(@Name, [Name]) のように ISNULL で現在値にフォールバックしています。
sp_UpdateUser（ユーザーの更新）
sql
CREATE PROCEDURE [dbo].[sp_UpdateUser]
@TenantId INT,
@TargetUserId INT,
@UserId INT,
@Name NVARCHAR(128) = NULL,
@FirstName NVARCHAR(256) = NULL,
@LastName NVARCHAR(256) = NULL,
@DeptId INT = NULL,
@Language NVARCHAR(16) = NULL,
@TimeZone NVARCHAR(64) = NULL
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
-- 更新前のレコードを履歴テーブルに保存
INSERT INTO [Users_history]
SELECT * FROM [Users]
WHERE [TenantId] = @TenantId
AND [UserId] = @TargetUserId;
-- メインテーブルを更新
UPDATE [Users]
SET
[Name] = ISNULL(@Name, [Name]),
[FirstName] = ISNULL(@FirstName, [FirstName]),
[LastName] = ISNULL(@LastName, [LastName]),
[DeptId] = ISNULL(@DeptId, [DeptId]),
[Language] = ISNULL(@Language, [Language]),
[TimeZone] = ISNULL(@TimeZone, [TimeZone]),
[Ver] = [Ver] + 1,
[Updator] = @UserId,
[UpdatedTime] = GETDATE()
WHERE
[TenantId] = @TenantId
AND [UserId] = @TargetUserId
AND [Disabled] = 0;
IF @@ROWCOUNT = 0
BEGIN
RAISERROR(N'対象のユーザーが見つからないか、無効化されています。', 16, 1);
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;sp_DeleteUser（ユーザーの論理削除）
sql
CREATE PROCEDURE [dbo].[sp_DeleteUser]
@TenantId INT,
@TargetUserId INT,
@UserId INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
-- 削除前のレコードを履歴テーブルに保存
INSERT INTO [Users_history]
SELECT * FROM [Users]
WHERE [TenantId] = @TenantId
AND [UserId] = @TargetUserId;
-- 論理削除
UPDATE [Users]
SET
[Disabled] = 1,
[Ver] = [Ver] + 1,
[Updator] = @UserId,
[UpdatedTime] = GETDATE()
WHERE
[TenantId] = @TenantId
AND [UserId] = @TargetUserId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;ユーザーの物理削除は Creator・Updator の参照が切れるため、論理削除を推奨します。
ロックアウトの解除（履歴保存付き）
sql
CREATE PROCEDURE [dbo].[sp_UnlockUser]
@TenantId INT,
@TargetUserId INT,
@UserId INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
-- 更新前のレコードを履歴テーブルに保存
INSERT INTO [Users_history]
SELECT * FROM [Users]
WHERE [TenantId] = @TenantId
AND [UserId] = @TargetUserId;
UPDATE [Users]
SET
[Lockout] = 0,
[LockoutCounter] = 0,
[Ver] = [Ver] + 1,
[Updator] = @UserId,
[UpdatedTime] = GETDATE()
WHERE
[TenantId] = @TenantId
AND [UserId] = @TargetUserId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;読取と管理クエリ
sql
-- 有効なユーザーを組織情報とともに取得
SELECT u.[UserId], u.[LoginId], u.[Name], u.[DeptId], d.[DeptName],
u.[TenantManager], u.[LastLoginTime], u.[CreatedTime], u.[UpdatedTime]
FROM [Users] u
LEFT JOIN [Depts] d
ON u.[TenantId] = d.[TenantId]
AND u.[DeptId] = d.[DeptId]
AND d.[Disabled] = 0
WHERE u.[TenantId] = @TenantId
AND u.[Disabled] = 0
ORDER BY u.[UserId];
-- 長期間（3 か月）ログインしていないユーザー
SELECT [UserId], [LoginId], [Name], [LastLoginTime]
FROM [Users]
WHERE [TenantId] = @TenantId
AND [Disabled] = 0
AND (
[LastLoginTime] IS NULL
OR [LastLoginTime] < DATEADD(MONTH, -3, GETDATE())
)
ORDER BY [LastLoginTime];
-- ロックアウトされたユーザー
SELECT [UserId], [LoginId], [Name], [LockoutCounter], [UpdatedTime]
FROM [Users]
WHERE [TenantId] = @TenantId
AND [Lockout] = 1;Results（記録テーブル）と Issues（期限付きテーブル）
テーブル構造
Results の主要カラムです。
| カラム | 型 | NULL | 説明 |
|---|---|---|---|
SiteId | bigint | NO | サイト ID |
ResultId | bigint | NO | レコード ID（主キー。Items.ReferenceId の採番値を使う） |
Ver | int | NO | バージョン番号 |
Title | nvarchar(1024) | YES | タイトル |
Body | nvarchar(max) | YES | 内容 |
Status | int | YES | ステータス |
Manager | int | YES | 管理者 UserId |
Owner | int | YES | 担当者 UserId |
Locked | bit | YES | レコードロックフラグ |
Comments | nvarchar(max) | YES | コメント（JSON） |
Creator / Updator / CreatedTime / UpdatedTime | NO | 監査カラム |
Issues は Results の全カラムに加えて、次のカラムを持ちます（ID 列は IssueId）。
| カラム | 型 | NULL | 説明 |
|---|---|---|---|
IssueId | bigint | NO | レコード ID（主キー。Items.ReferenceId の採番値を使う） |
StartTime | datetime | YES | 開始日 |
CompletionTime | datetime | NO | 完了日 |
WorkValue | decimal | YES | 作業量 |
ProgressRate | decimal | YES | 進捗率 |
残作業量（RemainingWorkValue）は物理カラムではありません。SELECT のたびに WorkValue - (WorkValue * ProgressRate * 0.01) で計算されるため、INSERT / UPDATE の対象にしないでください。確認したソースでは、列定義に ComputeColumn と NotUpdate があり（Issues_RemainingWorkValue.json）、CodeDefiner は NotUpdate の列をテーブルに作りません（TablesConfigurator.cs#L164-L172）。
WARNING
CompletionTime は NULL 非許容です。Issues テーブルに INSERT する際は必ず値を指定してください。
拡張カラム
各カテゴリ A 〜 Z の 26 カラムずつ、合計 156 カラムがあります。定義（ラベル名や選択肢など）は Sites テーブルの SiteSettings カラム（JSON）に格納されています。
| カテゴリ | 型 | 用途 |
|---|---|---|
ClassA 〜 ClassZ | nvarchar(1024) | 分類（ドロップダウンなど） |
NumA 〜 NumZ | decimal | 数値 |
DateA 〜 DateZ | datetime | 日付 |
DescriptionA 〜 DescriptionZ | nvarchar(max) | 説明（長文テキスト） |
CheckA 〜 CheckZ | bit | チェック（真偽値） |
AttachmentsA 〜 AttachmentsZ | nvarchar(max) | 添付ファイル（JSON） |
API 内部実装（ResultModel / IssueModel）
Create
SetByBeforeCreateServerScript()で作成前サーバースクリプトを実行- 重複チェック（
IfDuplicatedStatements()） Rds.InsertItems()でItemsテーブルに INSERT（ReferenceTypeを設定）Rds.InsertResults()またはRds.InsertIssues()でメインテーブルに INSERTInsertLinks()でリンク情報を作成- 添付ファイルの処理
- 権限の設定（
PermissionForCreatingがある場合） SCOPE_IDENTITY()で ID を取得Rds.UpdateItems()でItemsテーブルのTitle・FullTextを更新SetByAfterCreateServerScript()で作成後サーバースクリプトを実行
Update
SetByBeforeUpdateServerScript()で更新前サーバースクリプトを実行Versions.VerUp()でバージョンアップが必要か判定- 必要なら
Rds.ResultsCopyToStatement()でResults_historyにコピー VerをインクリメントRds.UpdateResults()でメインテーブルを UPDATE- 楽観的排他制御による競合チェック
UpdateRelatedRecords()でItemsテーブルのTitle・FullTextを同期SetByAfterUpdateServerScript()で更新後サーバースクリプトを実行
SQL で直接操作するとスクリプトは動かない
上記のとおり、API 経由の作成・更新ではサーバースクリプトや重複チェック、リンク・権限の処理も行われます。このページのストアドプロシージャはそれらを再現していません。
作成（Items → Results）
API 内部と同じく Items → Results の順に登録し、Items の採番 ID を ResultId として使います。IDENTITY 列なのは Items.ReferenceId だけで、Results.ResultId・Issues.IssueId・Wikis.WikiId・Sites.SiteId は IDENTITY ではありません。API 内部でも Items の INSERT 直後の採番値（Def.Sql.Identity）をそのまま ID 列に入れています。そのため IDENTITY_INSERT は不要です（IDENTITY_INSERT を ON にする処理は、IDENTITY 列のないテーブルではエラーになるため削除しています）。1.5.8.1 のソースで確認しました（Items_ReferenceId.json、Results_ResultId.json、WikiModel.cs#L746-L761）。
sql
CREATE PROCEDURE [dbo].[sp_CreateResult]
@SiteId BIGINT,
@UserId INT,
@Title NVARCHAR(1024) = NULL,
@Body NVARCHAR(MAX) = NULL,
@Status INT = NULL,
@ManagerUserId INT = NULL,
@OwnerUserId INT = NULL,
@NewResultId BIGINT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
-- Itemsテーブルに登録（API内部と同じ順序：Items → Results）
INSERT INTO [Items] (
[ReferenceType], [SiteId], [Title],
[SearchIndexCreatedTime], [UpdatedTime]
)
VALUES (
N'Results', @SiteId, ISNULL(@Title, N''),
GETDATE(), GETDATE()
);
DECLARE @ReferenceId BIGINT = SCOPE_IDENTITY();
-- Resultsテーブルに登録（ResultId は Items の採番値）
INSERT INTO [Results] (
[SiteId], [ResultId], [Ver], [Title], [Body],
[Status], [Manager], [Owner], [Locked], [Comments],
[Creator], [Updator], [CreatedTime], [UpdatedTime]
)
VALUES (
@SiteId, @ReferenceId, 1, @Title, @Body,
@Status, @ManagerUserId, @OwnerUserId, 0, N'[]',
@UserId, @UserId, GETDATE(), GETDATE()
);
SET @NewResultId = @ReferenceId;
-- Itemsテーブルの FullText を更新
UPDATE [Items]
SET
[Title] = ISNULL(@Title, N''),
[FullText] = ISNULL(@Title, N'') + N' ' + ISNULL(@Body, N''),
[SearchIndexCreatedTime] = GETDATE()
WHERE
[ReferenceId] = @NewResultId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;Issues 用の sp_CreateIssue も同じ流れで、ReferenceType を N'Issues' にし、StartTime・CompletionTime（必須）・WorkValue を受け取ります。INSERT 時は ProgressRate に 0 を設定し、@Status の既定値は 100 です（RemainingWorkValue は計算列のため INSERT しません）。
FullText について
このストアドプロシージャが設定する Items.FullText はタイトルと本文を連結した簡易的なものです。プリザンター本体が生成する FullText（サイトのタイトルや編集画面の各項目、コメントなどを含む）の作られ方は 検索機能の内部実装 を参照してください。
更新（履歴保存 + Items 同期）
sql
CREATE PROCEDURE [dbo].[sp_UpdateResult]
@ResultId BIGINT,
@SiteId BIGINT,
@UserId INT,
@Title NVARCHAR(1024) = NULL,
@Body NVARCHAR(MAX) = NULL,
@Status INT = NULL,
@ManagerUserId INT = NULL,
@OwnerUserId INT = NULL
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
-- 更新前のレコードを履歴テーブルに保存
INSERT INTO [Results_history]
SELECT * FROM [Results]
WHERE [ResultId] = @ResultId;
-- Resultsテーブルの更新
UPDATE [Results]
SET
[Title] = ISNULL(@Title, [Title]),
[Body] = ISNULL(@Body, [Body]),
[Status] = ISNULL(@Status, [Status]),
[Manager] = ISNULL(@ManagerUserId, [Manager]),
[Owner] = ISNULL(@OwnerUserId, [Owner]),
[Ver] = [Ver] + 1,
[Updator] = @UserId,
[UpdatedTime] = GETDATE()
WHERE
[SiteId] = @SiteId
AND [ResultId] = @ResultId;
IF @@ROWCOUNT = 0
BEGIN
RAISERROR(N'対象のレコードが見つかりません。', 16, 1);
END
-- Itemsテーブルの同期（タイトルとFullText）
DECLARE @CurrentTitle NVARCHAR(1024);
DECLARE @CurrentBody NVARCHAR(MAX);
SELECT @CurrentTitle = [Title], @CurrentBody = [Body]
FROM [Results] WHERE [ResultId] = @ResultId;
UPDATE [Items]
SET
[Title] = ISNULL(@CurrentTitle, N''),
[FullText] = ISNULL(@CurrentTitle, N'') + N' ' + ISNULL(@CurrentBody, N''),
[SearchIndexCreatedTime] = GETDATE(),
[UpdatedTime] = GETDATE()
WHERE
[ReferenceId] = @ResultId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;Issues 用の sp_UpdateIssue も同じ形で、ProgressRate・StartTime・CompletionTime を ISNULL 付きで更新項目に加えます。
削除（ゴミ箱に移動）
_deleted テーブルに移動する方式に合わせておくと、画面のゴミ箱から復元できます。
sql
CREATE PROCEDURE [dbo].[sp_DeleteResult]
@ResultId BIGINT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
-- Results_deletedテーブルへコピー
INSERT INTO [Results_deleted]
SELECT * FROM [Results]
WHERE [ResultId] = @ResultId;
-- Resultsテーブルから削除
DELETE FROM [Results]
WHERE [ResultId] = @ResultId;
-- Itemsテーブルから削除
DELETE FROM [Items]
WHERE [ReferenceId] = @ResultId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;物理削除
DANGER
履歴・ゴミ箱・添付ファイルまで完全に削除します。元に戻せません。
sql
CREATE PROCEDURE [dbo].[sp_PhysicalDeleteResult]
@ResultId BIGINT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
BEGIN TRY
-- 履歴の削除
DELETE FROM [Results_history]
WHERE [ResultId] = @ResultId;
-- ゴミ箱の削除
DELETE FROM [Results_deleted]
WHERE [ResultId] = @ResultId;
-- 添付ファイルの削除
DELETE FROM [Binaries]
WHERE [ReferenceId] = @ResultId;
DELETE FROM [Binaries_deleted]
WHERE [ReferenceId] = @ResultId;
-- Itemsテーブルから削除
DELETE FROM [Items]
WHERE [ReferenceId] = @ResultId;
-- Resultsテーブルから削除
DELETE FROM [Results]
WHERE [ResultId] = @ResultId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;読取
sql
-- Results の一覧（タイトルは Items から取得）
SELECT r.[ResultId], i.[Title], r.[Status], r.[Owner], r.[Manager],
r.[CreatedTime], r.[UpdatedTime]
FROM [Results] r
INNER JOIN [Items] i
ON r.[ResultId] = i.[ReferenceId]
WHERE r.[SiteId] = @SiteId
ORDER BY r.[ResultId] DESC;
-- 変更履歴（更新者名付き）
SELECT rh.[Ver], rh.[Title], rh.[Status], rh.[Updator],
u.[Name] AS [UpdaterName], rh.[UpdatedTime]
FROM [Results_history] rh
LEFT JOIN [Users] u
ON rh.[Updator] = u.[UserId]
WHERE rh.[ResultId] = @ResultId
ORDER BY rh.[Ver] DESC;大量データのバッチ処理
データ移行やバッチ処理向けに、テーブル値パラメータ（TVP）を使った一括 CRUD の例です。
- 一括作成は、TVP と
MERGE文（ON 1 = 0で常に INSERT）を組み合わせ、OUTPUT句でItemsの採番 ID と入力行番号の対応を取ってから、メインテーブルに一括登録します - 一括更新・一括削除は、対象 ID を一時テーブルに収集してから JOIN ベースで処理します
テーブル型の定義
sql
CREATE TYPE [dbo].[ResultBatchType] AS TABLE (
[RowNo] INT,
[Title] NVARCHAR(1024) NULL,
[Body] NVARCHAR(MAX) NULL,
[Status] INT NULL,
[Manager] INT NULL,
[Owner] INT NULL,
[ClassA] NVARCHAR(1024) NULL,
[ClassB] NVARCHAR(1024) NULL,
[ClassC] NVARCHAR(1024) NULL,
[NumA] DECIMAL(18, 2) NULL,
[NumB] DECIMAL(18, 2) NULL,
[NumC] DECIMAL(18, 2) NULL,
[DateA] DATETIME NULL,
[DateB] DATETIME NULL,
[DateC] DATETIME NULL,
[DescriptionA] NVARCHAR(MAX) NULL,
[DescriptionB] NVARCHAR(MAX) NULL,
[DescriptionC] NVARCHAR(MAX) NULL,
[CheckA] BIT NULL,
[CheckB] BIT NULL,
[CheckC] BIT NULL
);Issues 用の IssueBatchType は、これに StartTime・CompletionTime・WorkValue・ProgressRate を加えたものです。拡張カラムは使用する分だけ含めてください（上の例は A 〜 C の 3 カラムずつ）。実際のサイト設定に合わせて追加・削除します。
一括作成
sql
CREATE PROCEDURE [dbo].[sp_BulkCreateResults]
@SiteId BIGINT,
@UserId INT,
@Data [dbo].[ResultBatchType] READONLY,
@InsertedCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @InsertedCount = 0;
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @Now DATETIME = GETDATE();
-- 一時テーブルでItems登録→ReferenceId取得を一括処理
DECLARE @InsertedItems TABLE (
[ReferenceId] BIGINT,
[RowNo] INT
);
-- 1. Itemsテーブルに一括登録
MERGE INTO [Items] AS target
USING (
SELECT
[RowNo],
N'Results' AS [ReferenceType],
@SiteId AS [SiteId],
ISNULL([Title], N'') AS [Title]
FROM @Data
) AS source
ON 1 = 0 -- 常にINSERT
WHEN NOT MATCHED THEN
INSERT ([ReferenceType], [SiteId], [Title],
[SearchIndexCreatedTime], [UpdatedTime])
VALUES (source.[ReferenceType], source.[SiteId], source.[Title],
@Now, @Now)
OUTPUT inserted.[ReferenceId], source.[RowNo]
INTO @InsertedItems;
-- 2. Resultsテーブルに一括登録（ResultId は Items の採番値）
INSERT INTO [Results] (
[SiteId], [ResultId], [Ver],
[Title], [Body], [Status], [Manager], [Owner],
[ClassA], [ClassB], [ClassC],
[NumA], [NumB], [NumC],
[DateA], [DateB], [DateC],
[DescriptionA], [DescriptionB], [DescriptionC],
[CheckA], [CheckB], [CheckC],
[Locked], [Comments],
[Creator], [Updator], [CreatedTime], [UpdatedTime]
)
SELECT
@SiteId, ii.[ReferenceId], 1,
d.[Title], d.[Body], d.[Status], d.[Manager], d.[Owner],
d.[ClassA], d.[ClassB], d.[ClassC],
d.[NumA], d.[NumB], d.[NumC],
d.[DateA], d.[DateB], d.[DateC],
d.[DescriptionA], d.[DescriptionB], d.[DescriptionC],
d.[CheckA], d.[CheckB], d.[CheckC],
0, N'[]',
@UserId, @UserId, @Now, @Now
FROM @Data d
INNER JOIN @InsertedItems ii ON d.[RowNo] = ii.[RowNo];
-- 3. ItemsテーブルのFullTextを一括更新
UPDATE i
SET
[FullText] = ISNULL(r.[Title], N'') + N' ' + ISNULL(r.[Body], N''),
[SearchIndexCreatedTime] = @Now
FROM [Items] i
INNER JOIN [Results] r ON i.[ReferenceId] = r.[ResultId]
INNER JOIN @InsertedItems ii ON r.[ResultId] = ii.[ReferenceId];
SET @InsertedCount = (SELECT COUNT(*) FROM @InsertedItems);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;Issues 用の sp_BulkCreateIssues では、CompletionTime が未指定の行に ISNULL(d.[CompletionTime], DATEADD(DAY, 7, @Now)) を、ProgressRate に ISNULL(d.[ProgressRate], 0) を設定しています（RemainingWorkValue は計算列のため INSERT しません）。
一括更新
プリザンターの BulkUpdate API と同様に、条件に一致するレコードを一括で更新します。更新前のレコードは _history テーブルに保存します。
sql
CREATE PROCEDURE [dbo].[sp_BulkUpdateResults]
@SiteId BIGINT,
@UserId INT,
@Status INT = NULL,
@Manager INT = NULL,
@Owner INT = NULL,
@ClassA NVARCHAR(1024) = NULL,
@WhereStatus INT = NULL,
@WhereOwner INT = NULL,
@UpdatedCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @UpdatedCount = 0;
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @Now DATETIME = GETDATE();
-- 対象レコードのIDを収集
DECLARE @TargetIds TABLE ([ResultId] BIGINT);
INSERT INTO @TargetIds
SELECT [ResultId] FROM [Results]
WHERE [SiteId] = @SiteId
AND (@WhereStatus IS NULL OR [Status] = @WhereStatus)
AND (@WhereOwner IS NULL OR [Owner] = @WhereOwner);
-- 1. 更新前のレコードを履歴テーブルに一括保存
INSERT INTO [Results_history]
SELECT r.* FROM [Results] r
INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];
-- 2. Resultsテーブルを一括更新
UPDATE r
SET
[Status] = ISNULL(@Status, r.[Status]),
[Manager] = ISNULL(@Manager, r.[Manager]),
[Owner] = ISNULL(@Owner, r.[Owner]),
[ClassA] = ISNULL(@ClassA, r.[ClassA]),
[Ver] = r.[Ver] + 1,
[Updator] = @UserId,
[UpdatedTime] = @Now
FROM [Results] r
INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];
SET @UpdatedCount = @@ROWCOUNT;
-- 3. Itemsテーブルの更新日時を同期
UPDATE i
SET [UpdatedTime] = @Now
FROM [Items] i
INNER JOIN @TargetIds t ON i.[ReferenceId] = t.[ResultId];
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;Issues 用の sp_BulkUpdateIssues は、更新項目に CompletionTime・ProgressRate を加えた同じ形です。
一括削除（ゴミ箱に移動）
sql
CREATE PROCEDURE [dbo].[sp_BulkDeleteResults]
@SiteId BIGINT,
@WhereStatus INT = NULL,
@DeletedCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @DeletedCount = 0;
BEGIN TRANSACTION;
BEGIN TRY
-- 対象レコードのIDを収集
DECLARE @TargetIds TABLE ([ResultId] BIGINT);
INSERT INTO @TargetIds
SELECT [ResultId] FROM [Results]
WHERE [SiteId] = @SiteId
AND (@WhereStatus IS NULL OR [Status] = @WhereStatus);
-- 1. Results_deletedテーブルへ一括コピー
INSERT INTO [Results_deleted]
SELECT r.* FROM [Results] r
INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];
SET @DeletedCount = @@ROWCOUNT;
-- 2. Resultsテーブルから一括削除
DELETE r FROM [Results] r
INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];
-- 3. Itemsテーブルから一括削除
DELETE i FROM [Items] i
INNER JOIN @TargetIds t ON i.[ReferenceId] = t.[ResultId];
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;Issues 用の sp_BulkDeleteIssues も同じ形です（Issues_deleted へコピー）。 サイト内の全レコードを履歴・ゴミ箱・添付ファイルごと完全に削除する sp_BulkPhysicalDeleteResults もあります。単票の物理削除と同じ順序（_history → _deleted → Binaries / Binaries_deleted → Items → Results）を、対象 ID の一時テーブルとの JOIN で一括実行するものです。
sp_BulkPhysicalDeleteResults（一括物理削除）
sql
CREATE PROCEDURE [dbo].[sp_BulkPhysicalDeleteResults]
@SiteId BIGINT,
@DeletedCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @DeletedCount = 0;
BEGIN TRANSACTION;
BEGIN TRY
-- 対象レコードのIDを収集
DECLARE @TargetIds TABLE ([ResultId] BIGINT);
INSERT INTO @TargetIds
SELECT [ResultId] FROM [Results]
WHERE [SiteId] = @SiteId;
SET @DeletedCount = (SELECT COUNT(*) FROM @TargetIds);
-- 1. 履歴テーブルの一括削除
DELETE rh FROM [Results_history] rh
INNER JOIN @TargetIds t ON rh.[ResultId] = t.[ResultId];
-- 2. ゴミ箱テーブルの一括削除
DELETE rd FROM [Results_deleted] rd
INNER JOIN @TargetIds t ON rd.[ResultId] = t.[ResultId];
-- 3. 添付ファイルの一括削除
DELETE b FROM [Binaries] b
INNER JOIN @TargetIds t ON b.[ReferenceId] = t.[ResultId];
DELETE bd FROM [Binaries_deleted] bd
INNER JOIN @TargetIds t ON bd.[ReferenceId] = t.[ResultId];
-- 4. Itemsテーブルの一括削除
DELETE i FROM [Items] i
INNER JOIN @TargetIds t ON i.[ReferenceId] = t.[ResultId];
-- 5. Resultsテーブルの一括削除
DELETE r FROM [Results] r
INNER JOIN @TargetIds t ON r.[ResultId] = t.[ResultId];
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH
END;使用例
sql
-- テーブル型変数にデータを準備して一括作成
DECLARE @BatchData [dbo].[ResultBatchType];
INSERT INTO @BatchData ([RowNo], [Title], [Status], [Owner], [ClassA])
VALUES
(1, N'案件A', 100, 2, N'カテゴリ1'),
(2, N'案件B', 100, 3, N'カテゴリ2'),
(3, N'案件C', 200, 2, N'カテゴリ1');
DECLARE @Count INT;
EXEC [dbo].[sp_BulkCreateResults]
@SiteId = 12345,
@UserId = 1,
@Data = @BatchData,
@InsertedCount = @Count OUTPUT;
PRINT N'作成件数: ' + CAST(@Count AS NVARCHAR);sql
-- ステータス100（未着手）のレコードを一括で200（処理中）に変更
DECLARE @Count INT;
EXEC [dbo].[sp_BulkUpdateIssues]
@SiteId = 12345,
@UserId = 1,
@Status = 200,
@WhereStatus = 100,
@UpdatedCount = @Count OUTPUT;CSV から大量にインポートする場合は、BULK INSERT でステージングテーブル（RowNo INT IDENTITY(1, 1) を持つ一時テーブル）に取り込み、そこから TVP に INSERT ... SELECT して sp_BulkCreateResults を呼び出します。
sql
CREATE TABLE #StagingResults (
[RowNo] INT IDENTITY(1, 1),
[Title] NVARCHAR(1024),
[Body] NVARCHAR(MAX),
[Status] INT,
[Owner] INT,
[ClassA] NVARCHAR(1024)
);
BULK INSERT #StagingResults
FROM 'C:\Import\results.csv'
WITH (
CODEPAGE = '65001',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
);
DECLARE @BatchData [dbo].[ResultBatchType];
INSERT INTO @BatchData ([RowNo], [Title], [Body], [Status], [Owner], [ClassA])
SELECT [RowNo], [Title], [Body], [Status], [Owner], [ClassA]
FROM #StagingResults;
DECLARE @Count INT;
EXEC [dbo].[sp_BulkCreateResults]
@SiteId = 12345,
@UserId = 1,
@Data = @BatchData,
@InsertedCount = @Count OUTPUT;
DROP TABLE #StagingResults;Wikis
Wikis も Sites・Items と連携するデータテーブルですが、Results / Issues と次の違いがあります。
| 特徴 | Results / Issues | Wikis |
|---|---|---|
| 1 サイトあたりのレコード数 | 複数レコード | 1 レコード |
Status・Manager・Owner | あり | なし |
| 拡張カラム（ClassA 〜 Z 等） | あり | あり |
_history テーブル | あり | あり |
_deleted テーブル | あり | あり |
主要カラムは SiteId、WikiId（主キー。Items.ReferenceId の採番値を使う）、Ver、Title、Body（本文、Markdown）、Locked、Comments（JSON）と監査カラムです。
API 内部実装（WikiModel）
- Create: 拡張 SQL の実行（
OnCreatingExtendedSqls） →Rds.InsertItems()でItemsに INSERT（ReferenceType = 'Wikis'） → 採番された ID をWikiIdにしてRds.InsertWikis()で INSERT →Rds.UpdateItems()でItemsのTitle・FullTextを更新 → 拡張 SQL の実行（OnCreatedExtendedSqls）。Wiki の作成は通常Sitesの作成とセットで行われます - Update:
SetByBeforeUpdateServerScript()→Versions.VerUp()で判定 → 必要ならRds.WikisCopyToStatement()でWikis_historyにコピー →Verをインクリメント →Rds.UpdateWikis()で UPDATE →UpdateRelatedRecords()でItemsを同期 →SetByAfterUpdateServerScript()
作成（サイト作成込み）
Wiki サイトの作成は Items（サイト）→ Sites → Items（Wiki）→ Wikis → Permissions の順序で行います。API 内部と同じく、Items に INSERT して採番された ReferenceId を SiteId / WikiId に使います（1.5.8.1 のソースでは SiteModel.cs#L2366-L2373）。
sql
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. Itemsテーブルにサイトのエントリを追加（ReferenceId を採番）
INSERT INTO [Items] (
[ReferenceType], [SiteId], [Title],
[FullText], [SearchIndexCreatedTime], [UpdatedTime]
)
VALUES (
N'Sites', 0, @Title,
@Title, GETDATE(), GETDATE()
);
SET @NewSiteId = SCOPE_IDENTITY();
UPDATE [Items] SET [SiteId] = @NewSiteId
WHERE [ReferenceId] = @NewSiteId;
-- 2. Sitesテーブルにサイトを作成（SiteId は Items の採番値）
INSERT INTO [Sites] (
[TenantId], [SiteId], [Ver], [SiteName], [Title], [Body],
[ReferenceType], [ParentId], [InheritPermission],
[SiteSettings], [Publish],
[Creator], [Updator], [CreatedTime], [UpdatedTime]
)
VALUES (
@TenantId, @NewSiteId, 1, @Title, @Title, N'',
N'Wikis', @ParentSiteId,
-- 継承しない場合、InheritPermission には自分自身の SiteId を入れる
CASE WHEN @InheritPermission = 0 THEN @NewSiteId ELSE @InheritPermission END,
N'{"Version":1.017}', 0,
@UserId, @UserId, GETDATE(), GETDATE()
);
-- 3. ItemsテーブルにWikiのエントリを追加（ReferenceId を採番）
INSERT INTO [Items] (
[ReferenceType], [SiteId], [Title],
[FullText], [SearchIndexCreatedTime], [UpdatedTime]
)
VALUES (
N'Wikis', @NewSiteId, @Title,
@Title + N' ' + ISNULL(@Body, N''),
GETDATE(), GETDATE()
);
SET @NewWikiId = SCOPE_IDENTITY();
-- 4. Wikisテーブルにレコードを作成（WikiId は Items の採番値）
INSERT INTO [Wikis] (
[SiteId], [WikiId], [Ver], [Title], [Body],
[Locked], [Comments],
[Creator], [Updator], [CreatedTime], [UpdatedTime]
)
VALUES (
@NewSiteId, @NewWikiId, 1, @Title, @Body,
0, N'[]',
@UserId, @UserId, 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;WARNING
実運用では SiteSettings にカラム定義やビュー設定が含まれます。上記の例では最小限の JSON のみ設定しています。カラム定義を追加する場合は、プリザンターの管理画面から設定するか、適切な JSON を構築してください。
更新・削除・物理削除
- 更新（
sp_UpdateWiki）:Wikis_historyにコピー →Title/BodyをISNULL付きで UPDATE（Ver + 1）→ItemsのTitle・FullText・SearchIndexCreatedTime・UpdatedTimeを同期。sp_UpdateResultと同じ形です - 削除（
sp_DeleteWiki）:Wikis_deletedにコピー →Wikisから削除 →Itemsから Wiki レコードを削除 - 物理削除（
sp_PhysicalDeleteWiki、サイトごと完全削除）: 次の順に削除します
sql
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;
DELETE FROM [Items] WHERE [ReferenceId] = @WikiId OR [ReferenceId] = @SiteId;
DELETE FROM [Permissions] WHERE [ReferenceId] = @SiteId;
DELETE FROM [Wikis] WHERE [WikiId] = @WikiId;
DELETE FROM [Sites] WHERE [SiteId] = @SiteId;実際には、これらをトランザクションと TRY...CATCH で囲んだストアドプロシージャにします。
sp_PhysicalDeleteWiki（Wiki の物理削除）
sql
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;読取
sql
-- テナント内の全 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;操作後の確認と注意点のまとめ
SQL で CRUD 操作を行った後は、プリザンターの管理画面から対象データが正しく表示されることを確認してください。
TenantIdが正しいテナントを指しているか（マスタテーブルでは必ず条件に含める）Verが正しくインクリメントされているかCreator・Updatorに有効なUserIdが設定されているかCreatedTime・UpdatedTimeが妥当な日時になっているか_historyテーブルに更新前のレコードが保存されているかResults・Issues・Wikisの操作時にItemsテーブルも同期されているか- 複数テーブルにまたがる操作がトランザクションで囲まれているか
DANGER
直接 SQL を実行する前に、必ずデータベースのバックアップを取得してください。