マッピングテーブルを作ってセキュアに本番データを更新する方法
人生長年生きてれば、本番データ変更してほしい依頼を受けた経験は数回あるはずです。
安心にデバックしやすく本番データを更新する方法を記載します。
■ 前提条件
- ポスグレ 想定
- SQLある程度わかる
依頼内容例
依頼として以下のような関連性を持つようなデータ関係に更新してほしいという依頼が来たと想定します。
| item_code | picture_code |
|---|---|
| 'I001' | 'P001' |
| 'I001' | 'P009' |
| 'I001' | 'P0s2' |
| 'I002' | 'P0s01' |
| 'I002' | 'P001' |
| 'I003' | 'P002' |
| 'I004' | 'P003' |
| 'I005' | 'P004' |
例） item_codeが'I001'の時picture_codeは'P001'になる
■ モデル構成
STEP1: temp_tableを作成する
更新専用の一時的に利用するtempテーブル作成します。
create temp table temp_table (
item_id integer,
item_code character varying,
picture_code character varying
);
STEP2: tempテーブルに必要な情報をinsertする
イメージとしてはマッピング情報をinsertするような感じ
insert into temp_table values
(null, 'I001', 'P001'),
(null, 'I001', 'P009'),
(null, 'I001', 'P0s2'),
(null, 'I002', 'P0s01'),
(null, 'I002', 'P001'),
(null, 'I003', 'P002'),
(null, 'I004', 'P003'),
(null, 'I005', 'P004');
STEP3: itemコードとitem_idを紐付け
現状tempテーブルのitem_idがnullなので商品テーブルから対象のレコードを探して出してきて（where temp_table.item_code = items.item_code;）、ヒットしたidをtempテーブルのitem_idにUPDATEしてあげる。（伝われ！）
update temp_table
set item_id = items.id
from items
where temp_table.item_code = items.item_code;
STEP4: 紐付けできたか確認
select count(*)
from temp_table
where item_id is null;
結果が以下のように０の場合temp_tableのitem_idに全ての値が入力されたというSTEP3が無事に成功した証明になる。
count
-------
0
(1 row)
STEP５: 写真モデルのFKを更新
update pictures
set item_id = temp_table.item_id
from temp_table
where pictures.pictures_code = temp_table.pictures_code;
これで以下のようなマッピングできた状態を実現できる
| item_code | picture_code |
|---|---|
| 'I001' | 'P001' |
| 'I001' | 'P009' |
| 'I001' | 'P0s2' |
| 'I002' | 'P0s01' |
| 'I002' | 'P001' |
| 'I003' | 'P002' |
| 'I004' | 'P003' |
| 'I005' | 'P004' |
STEP６: temp_tablesをdrop
もう必要ないのでdropする
drop table temp_table;
一連の流れ
-- 本番データ触る時は必ずトランザクション貼る
BEGIN;
-- tempテーブル作成
create temp table temp_table (
item_id integer,
item_code character varying,
picture_code character varying
);
-- insertする
insert into temp_table values
(null, 'I001', 'P001'),
(null, 'I001', 'P009'),
(null, 'I001', 'P0s2'),
(null, 'I002', 'P0s01'),
(null, 'I002', 'P001'),
(null, 'I003', 'P002'),
(null, 'I004', 'P003'),
(null, 'I005', 'P004');
-- temp_tableのitem_idをUPDATE
update temp_table
set item_id = items.id
from items
where temp_table.item_code = items.item_code;
-- temp_tableのitem_idが全部UPDATEされているかの確認
select count(*)
from temp_table
where item_id is null;
-- picturesの外部キーを対象の商品idに変更
update pictures
set item_id = temp_table.item_id
from temp_table
where pictures.pictures_code = temp_table.pictures_code;
-- 完了したら、temp_tableは必要ないので削除
drop table temp_table;
-- 問題なければCOMMITしちゃいましょう
COMMIT;
まとめ
temp_tableを作ることで、デバックしやすくなる。
失敗しても、temp_tableをDropすればデータの欠陥も最小限にすむなどのメリットもあります。
まあ、別にわざわざtemp_tableを作成しなくてもOKではあるんですが、こういう方法もあるんですよ程度で聞いていただければ！