データベースのテーブルとストアドプロシージャのバージョン管理方法の紹介

Higtyのシステムの作り方

概要

この記事ではデータベースのテーブルやストアドプロシージャのバージョン管理手法について紹介します。テーブルの列の追加・変更・削除を行うとストアドプロシージャも変更する必要があり、適切に管理をしないとシステムを停止しないと変更ができない、ということになってしまいがちです。この記事で紹介する方法を使うと並列に展開したWEBアプリケーションの動作を止めることなく、複数のバージョンのアプリケーションとデータベースを同時に混在して存在させることができるようになります。


この記事ではSQLサーバーを例に解説していますが概念そのものはMySQLやOracleなどの他のデータベースでもそのまま活用できます。


「インターフェース不変の原則」で設計する

上記の

・システムを止めない

・複数のバージョンのアプリーケーションから実行可能

・水平分散DBの場合に複数のDBが共存可能

といったことを実現するために「インターフェース不変の原則」でテーブルとストアドプロシージャを設計します。


この記事で解説する「インターフェース不変の原則」とは「ストアドプロシージャの引数と結果セットは不変」という形で全てのストアドプロシージャを設計して運用します。


実際にストアドプロシージャを変更したい場合はどうすれば良いでしょうか？以下のように運用することで変更する場合と同様のことが可能です。

・ストアドプロシージャの名前にバージョン番号を付ける

・変更したい場合は新しいストアドプロシージャを新規に作成する

・既存のストアドプロシージャの変更はBodyのみ可能にする（引数と結果セットは絶対に変更しない）


変更する代わりに新しくストアドプロシージャを作成し、アプリケーションで新しく作ったストアドプロシージャを利用するようにする形です。詳しくは以下のセクションで解説していきます。


サンプルの定義（20191018）

まずはテーブル定義ですが以下のようなテーブルを考えます。

CREATE TABLE User
(UserId UNIQUEIDENTIFIER Not Null
,DisplayName NVARCHAR(128) Not Null
,Constraint User_PrimaryKey Primary Key NonClustered(UserId)
)
Go

CREATE TABLE Task
(TaskId UNIQUEIDENTIFIER Not Null
,Title NVARCHAR(128) Not Null
,UserId UNIQUEIDENTIFIER 
,Constraint Task_PrimaryKey Primary Key NonClustered(TaskId)
,Constraint Task_Fk_UserId Foreign Key(UserId) References User(UserId)On Update No Action On Delete No Action
)
Go


ビューの作成

データを取得するストアドプロシージャで使用するビューを定義します。

CREATE VIEW Task_View_20191018
AS

SELECT TaskId,Title,UserId 
FROM Task with(nolock) 

GO

データの取得をビュー経由にすることで再利用性と保守性がUPします。タスクの一覧ページでは様々な条件でデータを抽出したいですが結果セットは同じ場合が多いです。必要な列をビューに定義しておけばSELECT * from ViewNameで簡単に記述できます。またテーブルに列が追加されても全く影響がありません。列が変更・削除された場合もビューを修正すれば呼び出しているストアドプロシージャを変更する必要はなくなります。


ストアドプロシージャの定義

上記のテーブルを操作するストアドプロシージャを定義していきます。ストアドプロシージャの末尾にバージョン番号（_20191018）をつけます。日付をつけるとよいでしょう。


Task_Add_20191018

CREATE PROCEDURE Task_Add_20191018
(@TaskId UNIQUEIDENTIFIER
,@Title NVARCHAR(100) 
,@UserId UNIQUEIDENTIFIER
) AS

INSERT Task(TaskId,Title,UserId)
VALUES (@TaskId,@Title,@UserId)

Go

Task_Edit_20191018

CREATE PROCEDURE Task_Edit_20191018
(@TaskId UNIQUEIDENTIFIER
,@Title Nvarchar(100) 
,@UserId UNIQUEIDENTIFIER
) AS

UPDATE Task
SET Title = @Title 
, UserId = @UserId 
WHERE TaskId = @TaskId

Go

Task_Delete_20191018

CREATE PROCEDURE Task_Delete_20191018
(@TaskId UNIQUEIDENTIFIER 
) AS

DELETE Task
WHERE TasId = @TaskId

Go

Task_Get_20191018

CREATE PROCEDURE Task_Get_20191018
AS

SELECT * FROM Task_View_20191018 

Go



全体の構成

データはUI→アプリ→DBという感じで流れます。UIを図示すると以下のような感じです。


ストアドを以下のように図示することにします。


追加、編集、削除のストアドは以下のようになります。





テーブルは以下のようになります。


データの取得をするストアドは以下のようになります。



画面からデータベースまでにデータのやりとりは以下のようになります。



列の追加の場合

まずは列の追加の場合について考えます。追加する列がNULLを許可する場合と許可しない場合でそれぞれ考えてみます。


Null許可の列の追加（20191019）

タスクテーブルは2019年10月18日の時点ではタイトル、ユーザーしか入力欄がありません。2019年10月19日に締切日（NULLを許可）の列を追加する場合について考えます。バージョン番号を20191019にしましょう。列の追加のスクリプトは以下のようになります。


ALTER TABLE Task ADD DueDate DATE
GO

実行するとテーブルに列が追加されます。



既存のストアドプロシージャについて

列を追加した後のストアドプロシージャのBodyは変更が必要でしょうか？順番に見てみましょう。

Task_Add_20191018のBodyはそのままでDueDateにはNULLが入ります。

Task_Edit_20191018のBodyもそのままでDueDateの値は変更しないままです。

Task_Delete_20191018のBodyもそのままでレコードの削除はできます。


実はNull許可の列を追加する場合はAdd,Edit,DeleteのいずれのストアドプロシージャについてもBodyの変更の必要はありません。


結果として以下のような形になります。


UIに入力欄を追加する

UIに入力欄を追加します。UIにDueDateの欄を追加し、その値をテーブルに反映させる必要があります。そのためにはAdd,Editの2つのストアドプロシージャを変更する必要があります。しかし「インターフェース不変の原則」に従い、既存のストアドプロシージャを変更するのではなく、新しくストアドプロシージャを追加することにします。


Task_Add_20191019

CREATE PROCEDURE Task_Add_20191019
(@TaskId UNIQUEIDENTIFIER
,@Title NVARCHAR(128) 
,@UserId UNIQUEIDENTIFIER
,@DueDate Date
) AS

INSERT Task(TaskId,Title,UserId,DueDate)
VALUES (@TaskId,@Title,@UserId,@DueDate)

GO

Task_Edit_20191019

CREATE PROCEDURE Task_Edit_20191019
(@TaskId UNIQUEIDENTIFIER
,@Title Nvarchar(128) 
,@UserId UNIQUEIDENTIFIER
,@DueDate Date
) AS

UPDATE Task
SET Title = @Title 
, UserId = @UserId 
, DueDate = @DueDate 
WHERE TaskId = @TaskId

GO


DBのバージョンが1019になっており、それに対して1018のアプリと1019のアプリからDBへアクセスがあります。


この方式のメリットは以下になります。

1.UIのプログラムは変更せずにテーブルとストアドプロシージャだけ更新できる。

2.UIのアプリのバージョンを20191018と20191019を共存させることができる。


1→2の順番で更新するのも良いですし、一括で更新しても良いでしょう。


もしUIがWEBアプリで複数のサーバーでスケールアウトしている場合は、10個中1個のWEBサーバーだけUI(20191019)に更新して本番環境で問題がないことを確認し、その後に残りの9個を20191019に更新することも可能です。



一覧に表示欄を追加する

一覧にDueDateを表示するためには新しくビューとストアドプロシージャを定義します。


Task_View_20191019

CREATE VIEW Task_View_20191019
AS

SELECT TaskId,Title,UserId,DueDate 
from Task with(nolock) 

GO

Task_Get_20191019

CREATE PROCEDURE Task_Get_20191019
AS

SELECT * FROM Task_View_20191019 

GO


NotNullの列の追加

現在のテーブルは以下のようになっています。


今度はNotNullの列を追加する場合について考えます。BodyTextという列を追加する場合、以下のようなスクリプトになります。

ALTER TABLE Task ADD BodyText NVARCHAR(400)
Go
UPDATE Task SET BodyText = ''
GO
ALTER TABLE Task ALTER COLUMN BodyText NVARCHAR(400) NOT NULL
GO


NotNullの列を追加する場合は既存のレコードにセットする値を決めておく必要があります。最初はNull許可で列を追加し、列に値をセットしてから、列の定義をNotNullに変更します。文字列の場合は空文字、INTの場合はゼロなどにするのが適当でしょう。


結果としてテーブル定義は以下のようになります。


列を追加した後のストアドプロシージャのBodyは変更が必要でしょうか？順番に見てみましょう。


Task_Add_20191018のBodyはBodyTextに空文字をセットするように変更が必要です。

Task_Add_20191019のBodyはBodyTextに空文字をセットするように変更が必要です。

Task_Edit_20191018のBodyはそのままで大丈夫です。

Task_Edit_20191019のBodyはそのままで大丈夫です。

Task_Delete_20191018のBodyもそのままで大丈夫です。


NotNullの列を追加する場合、XXX_Addのストアドプロシージャは変更が必要です。新規に追加した列に値をセットするように変更する必要があります。


ALTER PROCEDURE Task_Add_20191018
(@TaskId UNIQUEIDENTIFIER
,@Title NVARCHAR(128) 
,@UserId UNIQUEIDENTIFIER
) AS

INSERT Task(TaskId,Title,UserId,Description)
VALUES (@TaskId,@Title,@UserId,'''')

GO

CREATE PROCEDURE Task_Add_20191019
(@TaskId UNIQUEIDENTIFIER
,@Title NVARCHAR(128) 
,@UserId UNIQUEIDENTIFIER
,@DueDate Date
)As

INSERT DTask(TaskCD,Title,UserCD,DueDate,Description)
VALUES (@TaskCD,@Title,@UserCD,@DueDate,'''')

Go

Edit,Deleteのストアドプロシージャはそのままで大丈夫です。


UIに入力欄を追加する

UIを更新しBodyTextを入力できるように新しいバージョン(20191020)をリリースする場合はNotNullのときと同様に新しくストアドプロシージャを追加することになります。


Task_Add_v20191020

CREATE PROCEDURE Task_Add_20191020
(@TaskId UNIQUEIDENTIFIER
,@Title NVARCHAR(128) 
,@UserId UNIQUEIDENTIFIER
,@DueDate Date
,@BodyText NVARCHAR(400)
)As

INSERT Task(TaskId,Title,UserId,DueDate,BodyText)
VALUES (@TaskId,@Title,@UserId,@DueDate,@BodyText)

GO

Task_Edit_v20191020

CREATE PROCEDURE Task_Edit_20191020
(@TaskId UNIQUEIDENTIFIER
,@Title Nvarchar(100) 
,@UserId UNIQUEIDENTIFIER
,@DueDate Date
,@BodyText NVARCHAR(400)
) AS

UPDATE Task
SET Title = @Title 
, UserId = @UserId 
, DueDate = @DueDate 
, BodyText = @BodyText
WHERE TaskId = @TaskId

Go


一覧に表示欄を追加する

データの取得部分は以下のようにビューとストアドプロシージャを追加します。

Task_View_20191020

CREATE VIEW Task_View_20191020
AS

SELECT TaskId,Title,UserId,DueDate,BodyText 
from Task with(nolock) 

GO

Task_Get_20191020

CREATE PROCEDURE Task_Get_20191020
AS

SELECT * FROM Task_View_20191020

GO



列の変更をする場合

列の変更をする場合は2つパターンがあり、入力範囲が小さくなる場合と入力範囲が大きくなる場合があります。


・小さくなる場合の例　NVARCHAR(128)→NVARCHAR(64)

・大きくなる場合の例　NVARCHAR(128)→NVARCHAR(400)


入力範囲が大きくなる（変更される）場合

BodyTextはNvarchar(400)ですがこれをNvarchar(800)に変更します。

ALTER TABLE Task ALTER COLUMN BodyText NVARCHAR(800) NOT NULL
GO

入力範囲を大きくする変更の場合、ストアドはいずれも変更の必要はありません。

例えばFloat→Intを考えてみましょう。最初にUIを変更し小数が入らないようにする必要があります。UIの変更と一緒にストアドプロシージャの引数もIntを使用するように変更します。


入力範囲が小さくなる（変更される）場合

小数のデータが入らないようにした後、テーブルに存在する小数のデータを整数に変更します。この際に何かしらのルールで変換処理を行う必要があります。その後にテーブルの変更を行います。



列の削除をする場合

列の削除をする場合について解説します。BodyText列を削除することにしましょう。既存のバージョンは20191020、変更後のバージョンは20191021とします。


変更が必要な既存のストアドプロシージャはAdd,Edit,Getになります。

CREATE PROCEDURE Task_Add_20191020
(@TaskId UNIQUEIDENTIFIER
,@Title NVARCHAR(128) 
,@UserId UNIQUEIDENTIFIER
,@DueDate Date
,@BodyText NVARCHAR(400)
) AS

INSERT Task(TaskId,Title,UserId,DueDate)
VALUES (@TaskId,@Title,@UserId,@DueDate)

GO

CREATE PROCEDURE Task_Edit_20191020
(@TaskId UNIQUEIDENTIFIER
,@Title NVARCHAR(128) 
,@UserId UNIQUEIDENTIFIER
,@DueDate Date
,@BodyText NVARCHAR(400)
) AS

UPDATE Task
SET Title = @Title
, UserId = @UserId
, DueDate = @DueDate
WHERE TaskId = @TaskId

GO

「インターフェース不変の原則」により引数は変更せずBodyだけ変更します。引数を変更しないことでUIでストアドプロシージャを呼び出している部分は変更の必要がありません。

一覧に表示するデータの取得をしているストアドプロシージャも変更の必要があります。実際にはビューを変更するだけで大丈夫です。削除した列の代わりに何かしらの値を返すように変更します。


ALTER VIEW DTask_View_20191020
AS

SELECT TaskId,Title,UserId,DueDate,'' as BodyText
from DTask with(nolock) 

GO



まとめ

「インターフェース不変の原則」でデータベースのスキーマの変更をすることで

・システムを止めない

・複数のバージョンのアプリーケーションから実行可能

・水平分散DBの場合に複数のDBが共存可能


といったことを実現できます。ストアドプロシージャのバージョン管理に悩んでいる方は参考にしてみてください。