ハンズオンセクション 10

DB変更が発生しました
事前準備
(一次)開発時のDB変更
運用後のDB変更
さらに運用後のDB変更
- 商品ステータスの alter 文の例
- 会員フォローイングの定義とデータ
履歴にコメントを登録してみよう
- ハコメントを知ろう
- ハコメントしてみよう
次のセクション

DB変更が発生しました

ここでは、DB変更を体験してみましょう！

(ひとつまえ) ハンズオンセクション 9

事前準備

特に要りません。

(一次)開発時のDB変更

以下のDB変更が発生しました。DDL修正後、Renewal (manageの1番) してみましょう。

ReplaceSchemaタスク

明らかにおかしいカラム名があった: SERVICE_POINT_COUNT に変更
商品の定価が必須になった: REGULAR_PRICE に NotNull 制約を付与

ReplaceSchema で落ちます。エラー内容から原因を特定してみましょう。商品の定価が必須になったので、データのなかったレコードでエラーが発生するはずです。商品のデータ入っているエクセルデータを開いて、以下のようにデータを定義して、再度 Renewal (manageの1番) してみましょう。

商品ID: 15: 定価: 500000
商品ID: 16: 定価: 4000000

外だしSQLの修正

さて、さらに Renewal が落ちたはずです。(今までのエクササイズがちゃんと終わっていれば...) エラー内容の通りに直しましょう。OutsideSqlTestタスクがぜんぶ通るまで直し続けます。

OutsideSqlTestタスク

コンパイルエラーの修正

さて、Renewal が最後のタスクまで通っても、今度はコンパイルエラーが発生したはずです。(今までのエクササイズがちゃんと終わっていれば...) エラー内容の通りに直しましょう。

HistoryHTMLの確認

HistoryHTML を見てみましょう。変更した内容が出力されているはずです。

単体テストの実行

最後に、今まで作った単体テストを全て実行してグリーンになることを確認しましょう。

DB変更が発生したら (まとめ)

ということでDB変更が発生した場合は、DBFluteタスクのオンパレードとなります。

DBFluteタスク - DB変更が発生したら

運用後のDB変更

次は運用後のDB変更を想定してやってみましょう。

運用後のDB変更

それでは、運用後を想定したDB変更をしてみましょう。

1. SavePrevious

まずは、DB変更前のスキーマ(現状のスキーマ)のDDL(PreviousDDL)を保存しましょう。

AlterCheck - まずは SavePrevious

2. 普通にDB変更

以下の内容を playsql 配下の DDL に反映して Renewal (manageの1番) を実行。

会員セキュリティ情報にリマインダ回数を追加 (忘れっぽい人を分析する): MEMBER_SECURITY に REMINDER_USE_COUNT を追加; REMINDER_USE_COUNT は INTEGER 型で、NOT NULL
会員サービスに代理キーを付与 (律儀にIDの役割を分ける): MEMBER_SERVICE に MEMBER_SERVICE_ID を追加; PK を MEMBER_ID から MEMBER_SERVICE_ID に変更; MEMBER_SERVICE_ID は INTEGER 型で、AUTO_INCREMENT; MEMBER_ID にユニーク制約を付与

(...ReplaceSchema実行中)

落ちます。データがDB変更されていないからですね。playsql/data/ut/xls 配下のテストデータに今回の内容を反映。データの修正内容は以下の通り。

MEMBER_SERVICE_ID: 上から 1, 2, 3, 4...
REMINDER_USE_COUNT: 上から適当に

気を取り直して、Renewal (manageの1番) を実行。

3. AlterCheck

さて、まだ alter 文を書いてませんが、というか、どこに書いたらいいのかもわからないまま、いきなり AlterCheck を実行してみましょう。manage.bat(sh)を叩いて該当の番号を入力して、どーん。

AlterCheck - AlterCheck の実行

落ちるはずなので、よーくエラーメッセージを読んでみましょう。

(...エラーメッセージを熟読中)

差分は確認できましたでしょうか？この時点では、変更したもの全てが差分になっているはずです。そうしましたら、作成された alter-schema.sql に以下の内容を転記して再度 AlterCheck を！

e.g. 運用後のDB変更のエクササイズの alter 文 @alter-schema.sql
-- add column
alter table MEMBER_SECURITY add REMINDER_USE_COUNT BIGINT NOT NULL after REMINDER_ANSWER;

-- add identity column as primary key
alter table MEMBER_SERVICE add MEMBER_SERVICE_ID INTEGER NOT NULL;
alter table MEMBER_SERVICE drop foreign key FK_MEMBER_SERVICE_MEMBER;
alter table MEMBER_SERVICE drop primary key;
update MEMBER_SERVICE set MEMBER_SERVICE_ID = MEMBER_ID;
alter table MEMBER_SERVICE add primary key (MEMBER_SERVICE_ID);
alter table MEMBER_SERVICE add constraint FK_MEMBER_SERVICE_MEMBER 
    foreign key (MEMBER_ID) references `MEMBER` (MEMBER_ID);
alter table MEMBER_SERVICE modify column MEMBER_SERVICE_ID INTEGER AUTO_INCREMENT NOT NULL;
alter table MEMBER_SERVICE add constraint UQ_MEMBER_SERVICE unique (MEMBER_ID);

まだ差分が出ますでしょうか？

create の結果と alter の結果が一致するまでエラーとなりDB変更はロールバックされ続けます。差分がなくなるまで修正と AlterCheck を繰り返しましょう。

さらに運用後のDB変更

それでは、エクササイズです。さらに運用後を想定したDB変更をしてみましょう。

間違えないように、AlterCheck の運用を思い出しながら踏みしめながら じっくりやりましょう。

商品ステータスに表示順カラムを追加 (会員ステータスと同じように): PRODUCT_STATUS に DISPLAY_ORDER を追加; DISPLAY_ORDER はユニーク制約; ONS, PST, SST の順番で 1, 2, 3
会員フォローイングを追加 (他の会員をフォローできるように): ※定義とデータは、後ほど

商品ステータスの alter 文の例

商品ステータスの表示順カラムの追加とデータ移行のための alter 文の例は以下の通り。

e.g. さらに運用後のDB変更のエクササイズの alter 文 @alter-schema.sql
-- add column for display order
alter table PRODUCT_STATUS add DISPLAY_ORDER INTEGER NOT NULL after PRODUCT_STATUS_NAME;
update PRODUCT_STATUS set DISPLAY_ORDER = 1 where PRODUCT_STATUS_CODE= 'ONS';
update PRODUCT_STATUS set DISPLAY_ORDER = 2 where PRODUCT_STATUS_CODE= 'PST';
update PRODUCT_STATUS set DISPLAY_ORDER = 3 where PRODUCT_STATUS_CODE= 'SST';
alter table PRODUCT_STATUS add constraint unique(DISPLAY_ORDER);

会員フォローイングの定義とデータ

会員フォローイング create 文は以下の通り。

会員フォローイングテーブルの create 文 @replace-schema-10-basic.sql
create table MEMBER_FOLLOWING(
    MEMBER_FOLLOWING_ID BIGINT AUTO_INCREMENT NOT NULL COMMENT '会員フォローイングID: 連番',
    MY_MEMBER_ID INTEGER NOT NULL COMMENT 'わたし: 気になった人がいて...勇気を振り絞った会員のID。',
    YOUR_MEMBER_ID INTEGER NOT NULL COMMENT 'あなた: いきなりのアクションに...ちょっと心揺らいだ会員のID。',
    FOLLOW_DATETIME DATETIME NOT NULL COMMENT 'その瞬間: ふりかえるとちょっと恥ずかしい気持ちになる日時',
    PRIMARY KEY (MEMBER_FOLLOWING_ID),
    UNIQUE (MY_MEMBER_ID, YOUR_MEMBER_ID)
) COMMENT='会員フォローイング: とある会員が他の会員をフォローできる。すると、フォローした会員の購入履歴が閲覧できる。';

alter table MEMBER_FOLLOWING add constraint FK_MEMBER_FOLLOWING_MY_MEMBER 
    foreign key (MY_MEMBER_ID) references `MEMBER` (MEMBER_ID);

alter table MEMBER_FOLLOWING add constraint FK_MEMBER_FOLLOWING_YOUR_MEMBER 
    foreign key (YOUR_MEMBER_ID) references `MEMBER` (MEMBER_ID);

create index IX_MEMBER_FOLLOWING_UNIQUE_REVERSE on MEMBER_FOLLOWING(YOUR_MEMBER_ID, MY_MEMBER_ID);
create index IX_MEMBER_FOLLOWING_FOLLOW_DATETIME on MEMBER_FOLLOWING(FOLLOW_DATETIME);

会員フォローイングのデータは以下の通り、20-member.xlsに新しいシートを作って、全てのセルを文字列型にしてから、コピーで貼付けましょう。 (Chromeだとそのまま貼付けられないという報告あり、一度テキストに貼付けてから、そのテキスト上でもう一度コピーすればOKという報告あり)

会員フォローイングのデータ (エクセルにそのまま貼れるようにとタブ区切り) @20-member.xls
MEMBER_FOLLOWING_ID	MY_MEMBER_ID	YOUR_MEMBER_ID	FOLLOW_DATETIME
1	1	2	2006/12/24 23:59:59
2	1	3	2007/11/11 16:16:16
3	1	4	2006/12/23 22:56:29
4	1	5	2007/11/01 02:13:00
5	1	6	2012/05/24 23:12:59
6	1	9	2005/11/11 16:32:01
7	1	15	2006/01/28 23:59:59
8	1	18	2007/11/09 16:23:00
9	2	1	2009/12/22 23:59:59
10	2	3	2007/11/11 09:01:00
11	2	4	2006/11/23 23:59:59
12	2	9	2012/11/15 12:43:00
13	2	14	2006/12/24 23:59:59
14	2	19	2011/04/01 16:23:01
15	3	1	2006/12/24 23:59:20
16	3	2	2008/05/01 12:23:02
17	3	4	2006/12/24 23:59:21
18	3	6	2009/06/11 17:23:03
19	3	9	2010/12/24 23:59:22
20	3	10	2007/11/01 16:12:04
21	4	1	2006/12/24 23:59:23
22	4	2	2007/11/01 16:23:05
23	4	3	2011/12/24 23:59:24
24	4	4	2007/11/01 16:23:06
25	4	5	2013/05/24 11:59:25
26	4	6	2007/06/21 01:23:57
27	4	9	2006/12/24 23:59:26
28	4	11	2007/11/01 16:23:08
29	4	12	2013/02/24 23:59:27
30	4	13	2012/11/01 16:23:03
31	4	14	2006/07/24 23:59:28
32	4	15	2007/11/01 17:23:10
33	4	16	2007/11/01 16:23:10
34	4	17	2006/07/24 23:59:29
35	4	18	2011/11/01 17:23:11
36	4	19	2007/11/01 16:23:11
37	5	1	2008/07/24 23:59:30
38	5	3	2007/11/01 17:23:12
39	5	6	2007/11/01 16:23:12
40	6	1	2006/07/24 23:59:31
41	6	2	2007/11/01 17:23:13
42	6	3	2007/11/01 16:23:13
43	6	4	2006/07/24 23:52:32
44	6	5	2007/11/01 11:23:14
45	6	6	2007/11/21 06:23:14
46	8	1	2011/07/24 23:52:33
47	8	2	2007/11/01 11:23:15
48	8	3	2007/11/21 06:23:15
49	9	1	2006/07/24 23:52:34
50	9	2	208/11/01 11:23:16
51	9	3	2007/11/21 06:23:16
52	9	10	2006/07/24 23:52:35
53	9	20	2009/11/01 11:23:17
54	10	1	2007/11/21 06:23:17
55	10	2	2006/07/24 23:52:36
56	10	3	2010/11/01 11:23:18
57	10	4	2006/12/21 23:59:59
58	10	5	2007/11/1 16:16:16
59	10	6	2006/08/23 22:56:29
60	10	9	2007/11/01 02:11:00
61	10	11	2012/05/24 23:12:59
62	10	12	2005/11/10 16:32:01
63	10	13	2006/01/28 23:59:59
64	10	14	2007/10/09 16:23:00
65	10	15	2009/12/22 23:59:59
66	10	16	2007/11/01 09:01:00
67	10	17	2006/11/23 23:59:59
68	10	18	2011/11/15 12:43:00
69	10	19	2006/07/24 23:59:59
70	10	20	2011/08/01 16:23:01
71	11	12	2006/09/24 23:59:20
72	11	13	2008/01/01 12:23:02
73	11	14	2006/12/24 23:59:21
74	11	15	2009/02/11 17:23:03
75	11	16	2010/03/24 23:59:22
76	11	17	2007/09/01 16:12:04
77	11	19	2006/12/24 23:59:23
78	12	1	2007/11/01 16:23:05
79	12	11	2011/05/24 23:59:24
80	12	13	2007/11/01 16:23:06
81	12	14	2013/06/24 11:59:25
82	12	15	2007/06/21 01:23:57
83	12	19	2006/12/24 23:59:26
84	13	11	2007/08/01 16:23:08
85	13	12	2013/02/24 23:59:27
86	13	14	2012/11/01 16:23:03
87	14	1	2006/07/24 23:59:28
88	14	10	2012/05/24 23:12:59
89	14	11	2005/11/10 16:32:01
90	14	20	2006/01/28 23:59:59
91	16	17	2007/10/09 16:23:00
92	17	16	2009/12/22 23:59:59
93	18	1	2007/11/01 09:01:00
94	18	2	2006/11/23 23:59:59
95	18	3	2011/11/15 12:43:00
96	18	4	2006/07/24 23:59:59
97	18	5	2011/08/01 16:23:01
98	18	6	2006/09/24 23:59:20
99	18	9	2008/01/01 12:23:02
100	18	11	2006/12/24 23:59:21
101	18	12	2009/02/11 17:23:03
102	18	13	2010/03/24 23:59:22
103	18	14	2007/09/01 16:12:04
104	18	15	2006/12/24 23:59:23
105	19	1	2007/11/01 16:23:05
106	19	10	2011/05/24 23:59:24
107	19	11	2007/11/01 16:23:06
108	19	12	2013/06/24 11:59:25
109	19	20	2013/06/24 11:59:25
110	20	10	2013/06/24 11:59:25
111	20	19	2013/06/24 11:59:25

実業務ではERDツール

ハンズオンではERDツールを割り切ってDDLを手修正してもらっていますが、実業務ではERDツールで変更してDDLは自動生成することが推奨されます。ここでは、DDLの勉強になればと。

履歴にコメントを登録してみよう

ハコメントを知ろう

まず、こちらのページをじっくりお読みください。

Hacomment (はこめんと) on Intro

ハコメントしてみよう

この時点で、HistoryHTMLに履歴がそれなりに溜まっているので、確認してみましょう。 (直接開くのではなく、SchemaHTMLからリンクで辿ることもできます)

HistoryHTML

それでは、DB変更の履歴にはこんなことが書いてあったらいいだろう を想像して、Intro経由でそれぞれの履歴(diff)に対してハコメントしてみましょう。 (もし、git環境があれば、作成されたハコメントのpieceをコミット)

次のセクション

さて、次のセクションへ

ハンズオンセクション 11