はじめに
プリザンターのサイトを作り始める時の最初の難関がサイトの種類を選ぶことです。「期限付きテーブル」と「記録テーブル」の2つから選ぶことになるのですが、作成を進めていくうちにやっぱりこっちではなかったな・・・と思うことがしばしばあります。
項目の移動機能だとサイトの種類を跨いだ移動はできないですし、新しく作り直すとしてもデータがそこそこ入っているテーブルだと、データのエクスポート・インポートも面倒・・・。
今回はそれを解決する方法を紹介します！
記事中のSQLクエリはSQL Serverを想定したものになります。他のRDBMSを使用している場合は適時書き換えてください。
準備
SIteIdの取得
変換したいサイトのSiteIdを取得してください。
バックアップ
念の為データベースのバックアップを取得してください。
マッピングを考える
「期限付きテーブル」と「記録テーブル」では互いに存在しない項目があります。これらをデータ欠損なく移行させるには項目のマッピングを考える必要があります。
| 項目 | 型 | 期限付きテーブル | 記録テーブル |
|---|---|---|---|
| ReferenceId | long | IssueId | ResultId |
| StartTime | datetime | ○ | × |
| CompletionTime | datetime | ○ NULL非許容 |
× |
| WorkValue | decimal | ○ | × |
| ProgressRate | decimal | ○ | × |
| Title | nvarchar | NULL非許容 | NULL許容 |
| Status | nvarchar | NULL非許容 | NULL許容 |
このような違いがあります。期限付きテーブルにしか存在しない項目は記録テーブルに変換する時は適当な値にマップする必要があります。また、反対に記録テーブルから期限付きテーブルに変換するときはNULL許容or項目自体が存在しないところから、NULL非許容の項目にマップする必要があるため、なんらかのデフォルト値を与えることを考えなくてはいけません。
リンクテーブルを探す
対象のテーブルに対してリンクを設定している場合、JSON形式で設定をしている場合はViewやLookupでそれぞれの固有項目を使っている可能性があります。もちろん項目の再設定が必要になります。
対象のテーブルにリンクしているテーブルは下記のSQLで取得出来ます。
SELECT
[SourceId] AS [SiteId]
FROM
[Links]
WHERE
[DestinationId] = @SiteId
この取得した値をメモしておいてください。
変換作業
Sitesテーブルの書き換え
ReferenceTypeフィールドの書き換え
まずは、SitesテーブルのReferenceTypeフィールドを書き換えます。
これはSQLを使えば一発で書き換え可能です。
UPDATE
[Sites]
SET
[ReferenceType] = 'Issues'
WHERE
[SiteId] = @SiteId
AND [ReferenceType] = 'Results'
UPDATE
[Sites]
SET
[ReferenceType] = 'Issues'
WHERE
[SiteId] = @SiteId
AND [ReferenceType] = 'Results'
SiteSetingsフィールドの書き換え
SiteSetingsフィールドの中に格納されているサイト設定のJSONを書き換えます。インデントのない状態で格納されているので、VS Codeなどの適当なツールでフォーマットしてから書き換えると楽です。
{
"Version": 1.017,
- "ReferenceType": "Results",
+ "ReferenceType": "Issues",
"GridColumns": [
- "ResultId",
+ "IssueId",
"Status",
"DateA",
"Title",
"Body",
"Owner",
"Updator",
"UpdatedTime"
],
"EditorColumnHash": {
"General": [
"Status",
- "DateA",
+ "CompletionTime"
"Title",
"Body",
"Owner"
]
},
"LinkColumns": [
- "ResultId",
+ "IssueId",
"DateA",
"Title",
"Owner",
"Updator",
"UpdatedTime"
],
"HistoryColumns": [
"Ver",
- "DateA",
+ "CompletionTime"
"Title",
"Body",
"Owner",
"Updator",
"UpdatedTime"
],
"Columns": [
{
- "ColumnName": "DateA",
+ "ColumnName": "CompletionTime",
"LabelText": "対応日"
},
{
"ColumnName": "Status",
"ChoicesText": "100,未着手,未,status-new\n200,実施中,実,status-inprogress\n900,完了,完,status-closed\n910,保留,留,status-rejected",
"ChoicesControlType": "Radio",
"ValidateRequired": true
}
],
"ServerScripts": [
{
"Title": "完了日に日付に今日をセット",
"Name": "",
"BeforeCreate": true,
"BeforeUpdate": true,
- "Body": "if (model.DateA.getTime() == (new Date(1899,11,30)).getTime() && model.Status === 900) {\n model.DateA = utilities.Today();\n}",
+ "Body": "if (model.CompletionTime.getTime() == (new Date(1899,11,30)).getTime() && model.Status === 900) {\n model.CompletionTime = utilities.Today();\n}",
"Id": 1
}
],
"NoDisplayIfReadOnly": false
}
このような感じで、先ほど決めたマッピングに従って項目を書き換えていきます。スクリプトやサーバスクリプトも書き換えが必要なので忘れずに。今回はDateAフィールドをStartTimeフィールドとCompletionTimeフィールドにマップしています。書き換えが出来たらSiteSettingsフィールドに書き戻します。
Itemsテーブルの書き換え
次はItemsテーブルのReferenceTypeフィールドの書き換えを行います。
UPDATE
[Items]
SET
[ReferenceType] = 'Issues'
WHERE
[SiteId] = @SiteId
AND [ReferenceType] = 'Results'
UPDATE
[Items]
SET
[ReferenceType] = 'Results'
WHERE
[SiteId] = @SiteId
AND [ReferenceType] = 'Issues'
データの詰め替え
次に実際にデータを詰め替えです。Insert-Selectでデータをデータを詰め替えます。
先ほど決めたマッピングに応じて同名の項目はそのまま、互いに存在しない項目はそれぞれのマッピングを追加していきます。
INSERT INTO
[dbo].[Issues] (
[SiteId],
[IssueId],
[Ver],
[Title],
[Body],
[StartTime],
[CompletionTime],
[Status],
[Manager],
[Owner],
[Locked],
[ClassA],
[ClassB],
[ClassC],
[ClassD],
[ClassE],
[ClassF],
[ClassG],
[ClassH],
[ClassI],
[ClassJ],
[ClassK],
[ClassL],
[ClassM],
[ClassN],
[ClassO],
[ClassP],
[ClassQ],
[ClassR],
[ClassS],
[ClassT],
[ClassU],
[ClassV],
[ClassW],
[ClassX],
[ClassY],
[ClassZ],
[NumA],
[NumB],
[NumC],
[NumD],
[NumE],
[NumF],
[NumG],
[NumH],
[NumI],
[NumJ],
[NumK],
[NumL],
[NumM],
[NumN],
[NumO],
[NumP],
[NumQ],
[NumR],
[NumS],
[NumT],
[NumU],
[NumV],
[NumW],
[NumX],
[NumY],
[NumZ],
[DateA],
[DateB],
[DateC],
[DateD],
[DateE],
[DateF],
[DateG],
[DateH],
[DateI],
[DateJ],
[DateK],
[DateL],
[DateM],
[DateN],
[DateO],
[DateP],
[DateQ],
[DateR],
[DateS],
[DateT],
[DateU],
[DateV],
[DateW],
[DateX],
[DateY],
[DateZ],
[DescriptionA],
[DescriptionB],
[DescriptionC],
[DescriptionD],
[DescriptionE],
[DescriptionF],
[DescriptionG],
[DescriptionH],
[DescriptionI],
[DescriptionJ],
[DescriptionK],
[DescriptionL],
[DescriptionM],
[DescriptionN],
[DescriptionO],
[DescriptionP],
[DescriptionQ],
[DescriptionR],
[DescriptionS],
[DescriptionT],
[DescriptionU],
[DescriptionV],
[DescriptionW],
[DescriptionX],
[DescriptionY],
[DescriptionZ],
[CheckA],
[CheckB],
[CheckC],
[CheckD],
[CheckE],
[CheckF],
[CheckG],
[CheckH],
[CheckI],
[CheckJ],
[CheckK],
[CheckL],
[CheckM],
[CheckN],
[CheckO],
[CheckP],
[CheckQ],
[CheckR],
[CheckS],
[CheckT],
[CheckU],
[CheckV],
[CheckW],
[CheckX],
[CheckY],
[CheckZ],
[AttachmentsA],
[AttachmentsB],
[AttachmentsC],
[AttachmentsD],
[AttachmentsE],
[AttachmentsF],
[AttachmentsG],
[AttachmentsH],
[AttachmentsI],
[AttachmentsJ],
[AttachmentsK],
[AttachmentsL],
[AttachmentsM],
[AttachmentsN],
[AttachmentsO],
[AttachmentsP],
[AttachmentsQ],
[AttachmentsR],
[AttachmentsS],
[AttachmentsT],
[AttachmentsU],
[AttachmentsV],
[AttachmentsW],
[AttachmentsX],
[AttachmentsY],
[AttachmentsZ],
[Comments],
[Creator],
[Updator],
[CreatedTime]
)
SELECT
[SiteId],
[ResultId] AS [IssueId],
[Ver],
ISNULL([Title], '') AS [Title],
[Body],
[DateA] AS [StartTime],
[DateA] AS [CompletionTime],
[Status],
[Manager],
[Owner],
[Locked],
[ClassA],
[ClassB],
[ClassC],
[ClassD],
[ClassE],
[ClassF],
[ClassG],
[ClassH],
[ClassI],
[ClassJ],
[ClassK],
[ClassL],
[ClassM],
[ClassN],
[ClassO],
[ClassP],
[ClassQ],
[ClassR],
[ClassS],
[ClassT],
[ClassU],
[ClassV],
[ClassW],
[ClassX],
[ClassY],
[ClassZ],
[NumA],
[NumB],
[NumC],
[NumD],
[NumE],
[NumF],
[NumG],
[NumH],
[NumI],
[NumJ],
[NumK],
[NumL],
[NumM],
[NumN],
[NumO],
[NumP],
[NumQ],
[NumR],
[NumS],
[NumT],
[NumU],
[NumV],
[NumW],
[NumX],
[NumY],
[NumZ],
[DateA],
[DateB],
[DateC],
[DateD],
[DateE],
[DateF],
[DateG],
[DateH],
[DateI],
[DateJ],
[DateK],
[DateL],
[DateM],
[DateN],
[DateO],
[DateP],
[DateQ],
[DateR],
[DateS],
[DateT],
[DateU],
[DateV],
[DateW],
[DateX],
[DateY],
[DateZ],
[DescriptionA],
[DescriptionB],
[DescriptionC],
[DescriptionD],
[DescriptionE],
[DescriptionF],
[DescriptionG],
[DescriptionH],
[DescriptionI],
[DescriptionJ],
[DescriptionK],
[DescriptionL],
[DescriptionM],
[DescriptionN],
[DescriptionO],
[DescriptionP],
[DescriptionQ],
[DescriptionR],
[DescriptionS],
[DescriptionT],
[DescriptionU],
[DescriptionV],
[DescriptionW],
[DescriptionX],
[DescriptionY],
[DescriptionZ],
[CheckA],
[CheckB],
[CheckC],
[CheckD],
[CheckE],
[CheckF],
[CheckG],
[CheckH],
[CheckI],
[CheckJ],
[CheckK],
[CheckL],
[CheckM],
[CheckN],
[CheckO],
[CheckP],
[CheckQ],
[CheckR],
[CheckS],
[CheckT],
[CheckU],
[CheckV],
[CheckW],
[CheckX],
[CheckY],
[CheckZ],
[AttachmentsA],
[AttachmentsB],
[AttachmentsC],
[AttachmentsD],
[AttachmentsE],
[AttachmentsF],
[AttachmentsG],
[AttachmentsH],
[AttachmentsI],
[AttachmentsJ],
[AttachmentsK],
[AttachmentsL],
[AttachmentsM],
[AttachmentsN],
[AttachmentsO],
[AttachmentsP],
[AttachmentsQ],
[AttachmentsR],
[AttachmentsS],
[AttachmentsT],
[AttachmentsU],
[AttachmentsV],
[AttachmentsW],
[AttachmentsX],
[AttachmentsY],
[AttachmentsZ],
[Comments],
[Creator],
[Updator],
[CreatedTime]
FROM
[dbo].[Results];
今回の例だとTitleフィールドがNULL許容からNULL非許容の項目へInsertすることになるので、ISNULLでの空にならないように手当をしています。また、StartTimeフィールドとCompletionTimeフィールドにはDateAフィールドにマップしています。
履歴を継承する必要がある場合は(Results|Issues)_historyテーブル、ゴミ箱を継承する必要がある場合は(Results|Issues)_deletedテーブルに対しても同様の処理を行ってください。
リンクを作り直す
先ほどメモしたリンクしているテーブルの情報を元に、リンクを再設定します。設定が必要になるのはJSON型式で指定していて、固有項目を使用している場合のみですが、念の為、全てのテーブルを確認して下さい。
動作確認をする
ここまで来れば動作確認を行うことが可能です。実際に動かしてみて問題無く動作するかを確認して下さい。
データをクリーニングする
データの詰め替えのステップではInsert-Selectをおこなったので、変換元のテーブルにはデータが残っています。これを削除します。この削除については急ぐ必要がないので、運用に乗りきったタイミングでの実施でOKです。
DLETE FROM [Results] WHERE [SiteId] = @SIteId;
DLETE FROM [Results_deleted] WHERE [SiteId] = @SIteId;
DLETE FROM [Results_history] WHERE [SiteId] = @SIteId;
まとめ
今回は、期限付きテーブルと記録テーブルを相互に変換する方法を紹介しました。最初にサイトの設計をきちんとするのが望ましいのですが、運用しているとどうしても合わない・・・というケースはよくあるかと思います。
この方法を使えば、それぞれの項目をコンバート出来るので、運用上やっぱり合わない・・・というケースに柔軟に対応が出来るようになります。是非試して見てください。