0
0

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

プリザンター内部で動くSQL文を調べてみる[第3回：編集編]

0
Posted at

はじめに

第 2 回 では一覧画面の SQL（WHERE 句・ORDER BY 句）を詳しく見ました。
本記事（第 3 回）では 編集画面（レコードの参照・作成・更新） の SQL を解説します。一覧画面と異なり、1 件のレコードに対する SELECT や INSERT / UPDATE が中心となります。

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

回 タイトル
第 1 回 アーキテクチャ編 ── SQL を組み立てる仕組みの全体像
第 2 回 一覧編 ── フィルタとソートの WHERE・ORDER BY
第 3 回（本記事） 編集編 ── SELECT・INSERT・UPDATE の実装
第 4 回 カレンダー編 ── 日付範囲クエリの実装
第 5 回 ガントチャート編 ── GanttUtilities.Where() と GanttLimit
第 6 回 バーンダウンチャート編 ── 現在 + 履歴 UNION クエリ
第 7 回 時系列チャート編 ── HorizontalAxis と NormalAndHistory
第 8 回 カンバン編 ── KambanLimit・全件取得・カード移動
第 9 回 クロス集計編 ── GROUP BY と日付軸グループ化式
第 10 回 画像ライブラリ編 ── Binaries INNER JOIN クエリ
第 11 回 分析チャート編 ── 分析パーツごとの現在＋履歴 SQL

処理フロー

編集画面（参照・編集）

GET /items/{id}/edit
 └─ IssueUtilities.Editor(context, ss, issueId, clearSessions)  ← L1465 IssueUtilities.cs
     └─ new IssueModel(context, ss, issueId: id, ...)           ← L738 IssueModel.cs
         └─（コンストラクタ内で自動的に）Get(context, ss, ...)   ← L864 IssueModel.cs
             └─ Rds.SelectIssues(EditorColumns,
                    WHERE: SiteId + IssueId)
                 ① SELECT（1件取得）

更新

POST /items/{id}/update
 └─ IssueModel.Update(context, ss, ...)                        ← L1949 IssueModel.cs
     └─ UpdateStatements(context, ss, ...)                     ← L2108 IssueModel.cs
         ├─ IfDuplicatedStatements()    SELECT（重複チェック・設定時のみ）
         ├─ IssuesCopyToStatement()     INSERT INTO Issues_history（バージョンアップ時のみ）
         ├─ UpdateIssues()              ② UPDATE Issues
         └─ UpdateRelatedRecordsStatements()
             ├─ UpdateItems()           ③ UPDATE Items（タイトル・全文検索索引）
             └─ InsertLinks()           INSERT / DELETE Links

new IssueModel(context, ss, issueId: X) のコンストラクタは 内部で自動的に Get() を呼び出し、指定した issueId のレコードを SELECT します。コンストラクタを呼ぶだけでレコードが取得済みになるのがプリザンターの設計上のポイントです。

ver クエリパラメータが URL に含まれる場合（例：/items/{id}/edit?ver=3）、通常の Normal テーブルではなく NormalAndHistory（通常 + 履歴の UNION ALL）を使ってクエリが実行されます。これにより過去バージョンの内容を参照できます。

Models/Issues/IssueModel.cs
if (context.QueryStrings.ContainsKey("ver"))
{
    Get(
        context: context,
        tableType: Sqls.TableTypes.NormalAndHistory,  // 履歴テーブルを含む
        column: column,
        where: Rds.IssuesWhereDefault(context, this)
            .Issues_Ver(context.QueryStrings.Int("ver")),  // 指定バージョンで絞り込み
        ss: ss);
}
else
{
    Get(context: context, ss: ss, view: view, column: column);  // 通常テーブル
}

https://github.com/Implem/Implem.Pleasanter/blob/c76832d69f14afc52119ad3b220b2cc9829e958e/Implem.Pleasanter/Models/Issues/IssueModel.cs#L757-L773

編集画面のSQL

1. レコード取得（SELECT）

編集画面・詳細画面でレコードを 1 件取得するのが IssueModel.Get() です。

Models/Issues/IssueModel.cs
public IssueModel Get(
    Context context,
    SiteSettings ss,
    View view = null,
    Sqls.TableTypes tableType = Sqls.TableTypes.Normal,
    SqlColumnCollection column = null,
    SqlWhereCollection where = null,
    SqlOrderByCollection orderBy = null, ...)
{
    // WHERE: デフォルトは SiteId + IssueId のみ
    where = (view != null)
        ? Rds.IssuesWhere().SiteId(SiteId)
        : where ?? Rds.IssuesWhereDefault(context, issueModel: this);

    // 列フィルタ（タイムスタンプ照合など）を追加
    view = view ?? new View();
    view.SetColumnsWhere(context, ss, where,
        siteId: SiteId, id: IssueId, timestamp: Timestamp.ToDateTime());

    // SELECT 列: EditorColumns（編集画面の表示列）＋拡張 SQL 列
    column = (column ?? Rds.IssuesEditorColumns(ss))
        ?.SetExtendedSqlSelectingColumn(context, ss, view);

    // JOIN: 列・WHERE・ORDER BY が参照するテーブルを自動算出
    join = join ?? ss.Join(context, join: new IJoin[] { column, where, orderBy });

    // クエリ実行して IssueModel にセット
    Set(context, ss, Repository.ExecuteTable(context, statements:
        Rds.SelectIssues(
            tableType: tableType,
            column: column,
            join: join,
            where: where,
            orderBy: orderBy)));
    return this;
}

Rds.IssuesWhereDefault

WHERE 句の基本条件は IssuesWhereDefault() が生成します。

Libraries/DataSources/Rds.cs
public static IssuesWhereCollection IssuesWhereDefault(
    Context context, IssueModel issueModel)
{
    return IssuesWhere()
        .SiteId(issueModel.SiteId)   // サイト絞り込み
        .IssueId(issueModel.IssueId); // レコード絞り込み
}

生成される SQL 条件：

WHERE "Issues"."SiteId" = @SiteId_0
  AND "Issues"."IssueId" = @IssueId_0

一覧画面との違い

項目 一覧画面 編集画面
SELECT 列 GridColumns（一覧表示列） EditorColumns（編集表示列）
WHERE 条件 フィルタ・権限 + SiteId SiteId + IssueId のみ
ORDER BY ColumnSorterHash + デフォルト 通常なし
ページネーション あり（OFFSET … FETCH NEXT） なし
件数取得 SQL あり（SELECT COUNT） なし

2. 新規画面（レコードのコピー元取得）

新規作成画面は基本的に SQL を発行しません。ただし 別レコードからのコピー作成（CopyFrom パラメータ指定時）では、コピー元レコードを Get() で取得します。

Models/Issues/IssueUtilities.cs
public static string EditorNew(Context context, SiteSettings ss)
{
    var copyFrom = context.QueryStrings.Long("CopyFrom");
    if (ss.AllowReferenceCopy == true && copyFrom > 0)
    {
        var issueModel = new IssueModel(
            context, ss, issueId: copyFrom,
            methodType: BaseModel.MethodTypes.New);
        // コピー元を SELECT で取得して値を複写
        if (issueModel.AccessStatus == Databases.AccessStatuses.Selected
            && Permissions.CanRead(context, ss.SiteId, issueModel.IssueId))
        {
            issueModel = issueModel.CopyAndInit(context, ss);
        }
    }
    // 通常の新規作成は SQL なし（空の IssueModel を使用）
    return Editor(context, ss, issueModel ?? new IssueModel(context, ss, ...));
}

3. レコード新規作成（INSERT）

新規保存時は IssueModel.Create() → CreateStatements() で INSERT 文を生成します。

Models/Issues/IssueModel.cs
public List<SqlStatement> CreateStatements(Context context, SiteSettings ss, ...)
{
    var statements = new List<SqlStatement>();

    // ① 重複チェック（IfDuplicated: 一意制約の事前検証 SELECT）
    statements.AddRange(IfDuplicatedStatements(ss: ss));

    statements.AddRange(new List<SqlStatement>
    {
        // ② Items テーブルに INSERT（ReferenceId を自動採番で取得）
        Rds.InsertItems(
            selectIdentity: true,
            param: Rds.ItemsParam()
                .ReferenceType("Issues")
                .SiteId(SiteId)
                .Title(Title.DisplayValue)),

        // ③ Issues テーブルに INSERT
        Rds.InsertIssues(
            param: Rds.IssuesParamDefault(
                context, ss, issueModel: this, setDefault: true)),

        // ④ Links テーブルに INSERT（リンク項目）
        InsertLinks(context, ss, setIdentity: true),
    });

    // ⑤ 添付ファイルを Binaries テーブルに UPDATE/INSERT
    statements.AddRange(UpdateAttachmentsStatements(context, ss));

    // ⑥ 行レベル権限を Permissions テーブルに INSERT
    statements.AddRange(PermissionUtilities.InsertStatements(...));

    return statements;
}

INSERT で発行される SQL のイメージ

-- ① Items テーブルへの INSERT（ID は自動採番）
INSERT INTO "Items" ("Creator", "Updator", "ReferenceType", "SiteId", "Title", ...)
VALUES (@U, @U, N'Issues', @SiteId_0, @Title_0, ...)

-- ② Issues テーブルへの INSERT（Items の採番 ID を外部キーとして使用）
INSERT INTO "Issues" ("Creator", "Updator", "ItemId", "SiteId", "Ver", "Title",
    "StartTime", "CompletionTime", "WorkValue", "ProgressRate", "Status", ...)
VALUES (@U, @U, @@identity, @SiteId_0, @Ver_0, @Title_0,
    @StartTime_0, @CompletionTime_0, @WorkValue_0, @ProgressRate_0, @Status_0, ...)

SqlInsert.Build_InsertStatement() が SqlParamCollection の各エントリを列名と値のリストに変換し、INSERT INTO ... (columns) VALUES (params) を組み上げます。

新規作成で実行される SQL の全体

順序 SQL の種類 対象テーブル
① SELECT（重複チェック） Issues
② INSERT Items
③ INSERT Issues
④ INSERT Links
⑤ UPDATE / INSERT Binaries（添付ファイル）
⑥ INSERT Permissions（行レベル権限設定時）

Repository.ExecuteScalar_response() でトランザクション実行され、採番した ID が返ります。

4. レコード更新（UPDATE）

更新時は IssueModel.Update() → UpdateStatements() で UPDATE 文を生成します。

Models/Issues/IssueModel.cs
private List<SqlStatement> UpdateStatements(
    Context context, SiteSettings ss,
    SqlWhereCollection where = null, SqlParamCollection param = null, ...)
{
    return new List<SqlStatement>
    {
        // Issues テーブルを更新
        Rds.UpdateIssues(
            where: where,
            param: param ?? Rds.IssuesParamDefault(
                context, ss, issueModel: this)),

        // 楽観的排他制御: 更新件数が 0 なら競合エラー
        new SqlStatement() { IfConflicted = true, Id = IssueId }
    };
}

UpdateStatements() 全体では次の順で SQL が積まれます。

Models/Issues/IssueModel.cs
public List<SqlStatement> UpdateStatements(
    Context context, SiteSettings ss, ...)
{
    // WHERE: SiteId + IssueId + UpdatedTime（楽観的排他用タイムスタンプ）
    var where = Rds.IssuesWhereDefault(context, issueModel: this)
        .UpdatedTime(timestamp, _using: timestamp.InRange() && checkConflict);

    // ① 重複チェック
    statements.AddRange(IfDuplicatedStatements(ss: ss));

    // ② バージョンアップ時：現行レコードを Issues_history にコピー
    if (verUp)
    {
        statements.Add(Rds.IssuesCopyToStatement(
            where: where,
            tableType: Sqls.TableTypes.History,
            ColumnNames()));
        Ver++;
    }

    // ③ Issues テーブルを UPDATE
    statements.AddRange(UpdateStatements(context, ss, where: where, ...));

    // ④ 添付ファイルを Binaries テーブルに UPDATE / INSERT
    statements.AddRange(UpdateAttachmentsStatements(context, ss, verUp: verUp));

    // ⑤ 行レベル権限を Permissions テーブルに UPDATE / INSERT
    if (ss.PermissionForUpdating?.Any() == true)
        statements.AddRange(PermissionUtilities.UpdateStatements(...));

    return statements;
}

UPDATE で発行される SQL のイメージ

-- ② バージョンアップ時のみ: 現行レコードを history テーブルにコピー
INSERT INTO "Issues_history" ("IssueId", "SiteId", "Ver", "Title", ...)
SELECT "IssueId", "SiteId", "Ver", "Title", ...
FROM "Issues"
WHERE "Issues"."SiteId" = @SiteId_0
  AND "Issues"."IssueId" = @IssueId_0

-- ③ Issues テーブルを UPDATE
UPDATE "Issues"
SET "Updator" = @U,
    "UpdatedTime" = GETUTCDATE(),
    "Title" = @Title_0,
    "Status" = @Status_0,
    "CompletionTime" = @CompletionTime_0,
    "ProgressRate" = @ProgressRate_0,
    ...
WHERE "Issues"."SiteId" = @SiteId_0
  AND "Issues"."IssueId" = @IssueId_0
  AND "Issues"."UpdatedTime" = @UpdatedTime_0  -- 楽観的排他制御

SqlUpdate.Build_UpdateStatement() が SqlParamCollection の Updating = true なエントリを 列名 = @パラメータ の形式に変換し、UPDATE ... SET ... WHERE ... を組み上げます。

楽観的排他制御

UPDATE の WHERE 句には UpdatedTime = @UpdatedTime_0 が含まれます。画面表示時と更新送信時のタイムスタンプが一致しない場合（他ユーザーが先に更新した場合）、UPDATE の影響行数が 0 になります。IfConflicted = true の SqlStatement がそれを検知して競合エラーを返します。

更新で実行される SQL の全体

順序 SQL の種類 対象テーブル 条件
① SELECT（重複チェック） Issues 一意制約設定時のみ
② INSERT（history コピー） Issues_history バージョンアップ時のみ
③ UPDATE Issues 常に
③ UPDATE Items（タイトル更新） タイトル変更時
④ UPDATE / INSERT / DELETE Binaries 添付ファイル変更時
⑤ UPDATE / INSERT Permissions 行レベル権限設定時

Items テーブルの自動更新

UPDATE 後、UpdateRelatedRecordsStatements() で Issues とは別に Items テーブルも更新されます。これはタイトル変更時だけでなく、全文検索索引の更新のために毎回実行されます。

Models/Issues/IssueModel.cs
public List<SqlStatement> UpdateRelatedRecordsStatements(...)
{
    var fullText = FullText(context, ss: ss);  // 全文検索用テキストを生成
    var statements = new List<SqlStatement>();

    // Items テーブルを UPDATE（タイトル + 全文検索索引）
    statements.Add(Rds.UpdateItems(
        where: Rds.ItemsWhere().ReferenceId(IssueId),
        param: Rds.ItemsParam()
            .SiteId(SiteId)
            .Title(Title.DisplayValue)            // タイトルを最新値に更新
            .FullText(fullText, _using: fullText != null) // 全文検索索引を更新
            .SearchIndexCreatedTime(DateTime.Now, _using: fullText != null),
        ...));
    ...
}

生成される UPDATE Items SQL のイメージ：

UPDATE "Items"
SET "SiteId" = @SiteId_0,
    "Title"  = @Title_0,                    -- 最新のタイトル値
    "FullText" = @FullText_0,               -- 全文検索用インデックス
    "SearchIndexCreatedTime" = @SearchIndexCreatedTime_0,
    "Updator" = @U,
    "UpdatedTime" = GETUTCDATE()
WHERE "Items"."ReferenceId" = @ReferenceId_0  -- IssueId と一致するレコード

Issues テーブルの Title 列と Items テーブルの Title 列は独立して管理されます。Issues の Title はフォームから入力される値そのものを保持し、Items の Title は表示用の DisplayValue（選択肢ラベルに変換済み）を保持します。

まとめ

本記事では、編集画面の SQL を解説しました。

  • 編集画面の SELECT：IssueModel.Get() が EditorColumns を使い、SiteId + IssueId で 1 レコードを取得する
  • 新規作成の INSERT：Items → Issues → Links の順に INSERT し、添付ファイルや行レベル権限も同一トランザクションで処理する
  • 更新の UPDATE：UpdatedTime を WHERE に含めた楽観的排他制御で競合を検知する。バージョンアップ時は現行レコードを history テーブルにコピーしてから UPDATE する

第 4 回では、カレンダービューの日付範囲クエリを詳しく見ていきます。

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

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?