スキーマ変更とロールバックスクリプトの作成

目標

拡張-移行-縮小パターンでスキーマを変更し、ロールバックスクリプト・バッチバックフィル・検証スクリプト・作業手順書まで揃えた、変更管理の一式を作れるようになります。

なぜ重要なのか

「downスクリプトがあるから安全だ」は、最もよくある思い込みです。DROP COLUMNを元に戻すdownは、カラムの構造を復活させるだけで、値は復活させられません。可逆性はコードの属性ではなくコードとデータと時間の組み合わせなので、同じ変更でも、昨日デプロイしたなら可逆で、100万件が溜まったあとは不可逆です。そして、大量バックフィルを1つのトランザクションで実行すると、ロックとログが急増して、リードレプリカが遅れ、照会サービスが古いデータを表示します。このラボは、そうした落とし穴を自分の手で避けてみる訓練です。

ステップ

準備: dbo-schemaラボの/root/db/si.dbが必要です。なければ/opt/lab/fixtures/dbo/si-base.sqlで作成できます。
現在のスキーマを/root/db/schema_v1.sqlに保存し、/root/db/baseline.txtを作成してください。3行です。
```
orders_cnt=<ORDERS 행 수>
orders_amt=<ORDERS 의 ORD_AMT 합계>
item_qty=<ORDER_ITEM 의 QTY 합계>
```
(山括弧の中の韓国語はプレースホルダーで、順にORDERSの行数、ORDERSのORD_AMTの合計、ORDER_ITEMのQTYの合計です。)
/root/db/changelog.csvを作成してください。1行目はchg_id,date,object,ddl_file,rollback_file,est_sec,approverです。ステップ3から5で作成する変更3件を、あらかじめ登録します。 ddl_file・rollback_file・approverは空にしてはいけません。est_secには数値が入っている必要があります。
/root/db/mig/V2__add_dlvr_sts.sqlを作成してください。
- ORDERSにDLVR_STS_CDカラムを追加します(デフォルト値は'01')
- 既存の行をすべて'01'で埋めます適用後、DLVR_STS_CDがNULLの行が0件である必要があります。
/root/db/mig/V2__rollback.sqlを作成してください。適用するとスキーマがschema_v1.sqlと同じになる必要があります。 (si.dbのコピーでup → downを実行して確認してください。)
/root/db/mig/V3__item_qty_check.sqlを作成してください。 ORDER_ITEM.QTYのCHECKを1以上9999以下に強化します。 sqliteは制約の変更をサポートしていないため、テーブル再作成の手順が必要です。適用後も、ORDER_ITEMの行数とQTYの合計がステップ1のベースラインと同じで、外部キーの関係も維持されている必要があります。
/root/db/batch-update.shを作成してください。2つの引数(DB파일 배치크기。プレースホルダーはDBファイルとバッチサイズです)を受け取り、ORDERSのDLVR_STS_CDが'01'の行を'02'に更新しますが、1回にバッチサイズの分ずつ繰り返し処理します。最後の行にbatches=<반복횟수> updated=<총건수>(プレースホルダーは繰り返し回数と総件数です)を出力します。 (元のDBを汚さないよう、引数で受け取ったDBにだけ適用します。)
/root/db/mig-verify.shを作成してください。2つの引数(기준선파일 DB파일。プレースホルダーはベースラインファイルとDBファイルです)を受け取り、ベースラインの3つの指標を現在のDBと比較して、すべて同じなら1行目にOKを、異なる場合はNGで始まる行とともに、どの指標が異なるかを出力します。終了コードもそれぞれ0と0以外の値です。
/root/db/mig-runbook.mdを作成してください。## 작업 개요、## 사전 백업、## 적용 절차、## 검증、## 롤백 기준、## 롤백 절차(韓国語の見出しで、順に作業概要、事前バックアップ、適用手順、検証、ロールバック基準、ロールバック手順を意味します)の6つのh2見出しが必要で、本文にバックアップファイルのパスと数値で書かれたロールバック基準(所要時間または検証失敗件数)が含まれている必要があります。

参考

スキーマのダンプ: sqlite3 /root/db/si.db .schema > schema_v1.sql
テーブル再作成の順序: PRAGMA foreign_keys=off → 新しいテーブルを作成 → INSERT ... SELECT → 既存のテーブルをDROP → RENAME → インデックスを再作成 → PRAGMA foreign_keys=on
影響行数: SELECT changes();
よくあるミス1: コピーを作らず、元のDBで試して元に戻せなくなることです。
よくあるミス2: テーブル再作成のときにインデックスを作り直さないことです。 DROPするとインデックスも一緒に消えます。
よくあるミス3: バッチバックフィルのループに終了条件がなく、無限ループになることです。影響を受ける行が0件になったら止まる必要があります。

スキーマのスナップショットとベースライン

現在のスキーマを/root/db/schema_v1.sqlに保存し、/root/db/baseline.txtを作成してください。3行です。

orders_cnt=<ORDERS 행 수>
orders_amt=<ORDERS 의 ORD_AMT 합계>
item_qty=<ORDER_ITEM 의 QTY 합계>

(山括弧の中の韓国語はプレースホルダーで、順にORDERSの行数、ORDERSのORD_AMTの合計、ORDER_ITEMのQTYの合計です。)

作業後に「もともと何件でしたか」と聞くことになったら、もう遅いです。スキーマとデータの指標の両方を残してください。

変更管理台帳

/root/db/changelog.csvを作成してください。1行目はchg_id,date,object,ddl_file,rollback_file,est_sec,approverです。ステップ3から5で作成する変更3件を、あらかじめ登録します。 ddl_file・rollback_file・approverは空にしてはいけません。est_secには数値が入っている必要があります。

ロールバックスクリプトのパスを必須項目にすると、台帳を埋めているうちに「これは元に戻せないな」とデプロイ前に気づきます。

拡張スクリプト

/root/db/mig/V2__add_dlvr_sts.sqlを作成してください。

ORDERSにDLVR_STS_CDカラムを追加します(デフォルト値は'01')
既存の行をすべて'01'で埋めます適用後、DLVR_STS_CDがNULLの行が0件である必要があります。

カラムを追加して既存の行を埋めます。NULL許容で追加してから埋める方法と、最初からNOT NULLで追加する方法の違いを考えてみてください。

ロールバックスクリプト

/root/db/mig/V2__rollback.sqlを作成してください。適用するとスキーマがschema_v1.sqlと同じになる必要があります。 (si.dbのコピーでup → downを実行して確認してください。)

元に戻したあとのスキーマが、元のスキーマと同じかを確認できる必要があります。スナップショットを比較するのが最も確実です。

テーブル再作成のマイグレーション

/root/db/mig/V3__item_qty_check.sqlを作成してください。 ORDER_ITEM.QTYのCHECKを1以上9999以下に強化します。 sqliteは制約の変更をサポートしていないため、テーブル再作成の手順が必要です。適用後も、ORDER_ITEMの行数とQTYの合計がステップ1のベースラインと同じで、外部キーの関係も維持されている必要があります。

制約を変えるには、新しいテーブルを作ってデータを移す手順が必要です。順序を守らないと、データや参照が壊れます。

バッチバックフィル

/root/db/batch-update.shを作成してください。2つの引数(DB파일 배치크기。プレースホルダーはDBファイルとバッチサイズです)を受け取り、ORDERSのDLVR_STS_CDが'01'の行を'02'に更新しますが、1回にバッチサイズの分ずつ繰り返し処理します。最後の行にbatches=<반복횟수> updated=<총건수>(プレースホルダーは繰り返し回数と総件数です)を出力します。 (元のDBを汚さないよう、引数で受け取ったDBにだけ適用します。)

大量のUPDATEを1つのトランザクションで行うと、ロックとログが急増します。影響を受ける行が0件になるまで繰り返す構造にしてください。

マイグレーション検証スクリプト

/root/db/mig-verify.shを作成してください。2つの引数(기준선파일 DB파일。プレースホルダーはベースラインファイルとDBファイルです)を受け取り、ベースラインの3つの指標を現在のDBと比較して、すべて同じなら1行目にOKを、異なる場合はNGで始まる行とともに、どの指標が異なるかを出力します。終了コードもそれぞれ0と0以外の値です。

行数と合計だけでは、「1件が抜けて別の1件が2倍になった」状況を検出できません。指標を複数置いてください。

作業手順書

/root/db/mig-runbook.mdを作成してください。## 작업 개요、## 사전 백업、## 적용 절차、## 검증、## 롤백 기준、## 롤백 절차(韓国語の見出しで、順に作業概要、事前バックアップ、適用手順、検証、ロールバック基準、ロールバック手順を意味します)の6つのh2見出しが必要で、本文にバックアップファイルのパスと数値で書かれたロールバック基準(所要時間または検証失敗件数)が含まれている必要があります。

明け方に読むドキュメントです。コマンドをそのままコピーして使えて、いつ止めて元に戻すかが数値で書かれている必要があります。