Web Form と MVC とで使用しているデータベースは同じものです。そこで、そのデータベースについて説明します。データベースサーバには、 SQL Server 2014 Express を使用しています。 このデータベースサーバは、 Microsoft 社から無償で提供されているものです。製品版と比較すると、機能制限はありますが、今回のプログラムでデータベースサーバとして使用する分には問題ありません。 データベース用ツールとしては、やはり無償で提供されている SQL Server 2014 Management Studio を使用しました。この URL で使用している画面は、このツールのスクリーンショットになります。
データベースを構成するテーブルは、以下の6つです。小さなデータベースであることが分かります。
大分類テーブルは、「Major」としました。その構造は、このようになります。
簡単な説明です。
SQL コードを以下に示します。
CREATE TABLE [dbo].[Major](
[ID] [nvarchar](2) NOT NULL,
[Name] [nvarchar](20) NOT NULL,
[Comment] [nvarchar](128) NULL,
[CDate] [datetime] NOT NULL CONSTRAINT [DF_Major_CDate] DEFAULT (getdate()),
[MDate] [datetime] NULL,
[Version] [int] NOT NULL CONSTRAINT [DF_Major_Version] DEFAULT ((0)),
[Color] [nvarchar](2) NULL,
CONSTRAINT [PK_Major] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
小分類テーブルは、「Minor」としました。その構造は、このようになります。
簡単な説明です。
SQL コードを以下に示します。
CREATE TABLE [dbo].[Minor](
[MID] [nvarchar](2) NOT NULL,
[ID] [nvarchar](2) NOT NULL,
[Name] [nvarchar](20) NOT NULL,
[Comment] [nvarchar](128) NULL,
[CDate] [datetime] NOT NULL CONSTRAINT [DF_Minor_CDate] DEFAULT (getdate()),
[MDate] [datetime] NULL,
[Version] [int] NOT NULL CONSTRAINT [DF_Minor_Version] DEFAULT ((0)),
CONSTRAINT [PK_Minor] PRIMARY KEY CLUSTERED
(
[MID] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Minor] WITH CHECK ADD CONSTRAINT [FK_Major_Minor] FOREIGN KEY([MID])
REFERENCES [dbo].[Major] ([ID])
GO
ALTER TABLE [dbo].[Minor] CHECK CONSTRAINT [FK_Major_Minor]
GO
単位テーブルは、「Unit」としました。その構造は、このようになります。
簡単な説明です。
SQL コードを以下に示します。
CREATE TABLE [dbo].[Unit](
[ID] [nvarchar](2) NOT NULL,
[Name] [nvarchar](4) NOT NULL,
[Comment] [nvarchar](128) NULL,
[CDate] [datetime] NOT NULL CONSTRAINT [DF_Unit_CDate] DEFAULT (getdate()),
[MDate] [datetime] NULL,
[Version] [int] NOT NULL CONSTRAINT [DF_Unit_Version] DEFAULT ((0)),
CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
エリアテーブルは、「Area」としました。その構造は、このようになります。
エリアとは、地図上の三か所以上の地点で構成された、閉局面を意味します。それぞれの地点は、緯度と経度で示されます。以下は、エリアテーブルの簡単な説明です。
SQL コードを以下に示します。
CREATE TABLE [dbo].[Area](
[ID] [nvarchar](2) NOT NULL,
[Name] [nvarchar](20) NOT NULL,
[Comment] [nvarchar](128) NULL,
[Center] [nvarchar](40) NULL,
[Area] [nvarchar](max) NULL,
[CDate] [datetime] NOT NULL CONSTRAINT [DF_Area_CDate] DEFAULT (getdate()),
[MDate] [datetime] NULL,
[Version] [int] NOT NULL CONSTRAINT [DF_Area_Version] DEFAULT ((0)),
CONSTRAINT [PK_Area] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
フィールドワークテーブルは、「Material」としました。フィールドワークデータに緯度・経度の項目があるのは、使用しているカメラに GPS の機能が付いているためでした。
写真データには、自動的に緯度経度情報が埋め込まれています。この写真の特徴を生かす上でも、地図データと連動させようという考えがうまれてきました。フィールドワークデータには複数の写真が登録できます。
そこで、最初の写真データの緯度経度情報を、フィールドワークデータの緯度経度として取り込むような工夫が、画面のプログラムになされています。
さてテーブルの構造は、以下のようになっています。
簡単な説明です。
将来的な仕様追加を考慮して、未使用のフィールが多くあります。
SQL コードを以下に示します。
CREATE TABLE [dbo].[Material](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RDate] [datetime] NOT NULL,
[RDateText] [nvarchar](128) NULL,
[MajorID] [nvarchar](2) NOT NULL,
[MinorID] [nvarchar](2) NOT NULL,
[Title] [nvarchar](128) NOT NULL,
[Keyword] [nvarchar](128) NULL,
[Place] [nvarchar](128) NULL,
[Latitude] [nvarchar](20) NULL,
[Longitude] [nvarchar](20) NULL,
[Altitude] [int] NULL,
[Weather] [nvarchar](128) NULL,
[Amount] [int] NULL,
[UnitID] [nvarchar](2) NULL,
[Width] [int] NULL,
[Depth] [int] NULL,
[Height] [int] NULL,
[Others] [nvarchar](128) NULL,
[CDate] [datetime] NOT NULL CONSTRAINT [DF_Contents_CDate] DEFAULT (getdate()),
[MDate] [datetime] NULL,
[Version] [int] NOT NULL CONSTRAINT [DF_Contents_Version] DEFAULT ((0)),
[AreaID] [nvarchar](2) NULL,
[Contents] [nvarchar](max) NULL,
CONSTRAINT [PK_Material] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Material] WITH CHECK ADD CONSTRAINT [FK_Contents_Unit] FOREIGN KEY([UnitID])
REFERENCES [dbo].[Unit] ([ID])
GO
ALTER TABLE [dbo].[Material] CHECK CONSTRAINT [FK_Contents_Unit]
GO
ALTER TABLE [dbo].[Material] WITH CHECK ADD CONSTRAINT [FK_Item_Minor] FOREIGN KEY([MajorID], [MinorID])
REFERENCES [dbo].[Minor] ([MID], [ID])
GO
ALTER TABLE [dbo].[Material] CHECK CONSTRAINT [FK_Item_Minor]
GO
フィールドワークデータを構成する写真ファイル自体は、ファイルとして管理することにしました。 データベースで管理しているのは、そのファイル名に関する部分だけです。 写真はブラウザから参照できないといけません。そのため、写真データ自体をデータベースに格納する利点と欠点を考慮して、このような利用方法としました。
イメージテーブルは、「Image」としました。その構造は、このようになります。
簡単な説明です。
SQL コードを以下に示します。
CREATE TABLE [dbo].[Image](
[ItemID] [int] NOT NULL,
[Sequence] [int] NOT NULL,
[Name] [nvarchar](260) NULL,
[Dir] [nvarchar](260) NULL,
[BaseName] [nvarchar](128) NULL,
[Body] [image] NULL,
CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED
(
[ItemID] ASC,
[Sequence] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Image] WITH CHECK ADD CONSTRAINT [FK_Material_Image] FOREIGN KEY([ItemID])
REFERENCES [dbo].[Material] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Image] CHECK CONSTRAINT [FK_Material_Image]
このデータベースで使用しているストアドプロシージャは2つです。
ProcMinorTable の SQL 文を示します。小分類テーブルを中心として、大分類テーブルを JOIN し、大分類名を取り出している簡単なストアドプロシージャです。
ストアドプロシージャの引数として、大分類コードと小分類コードを渡して、条件付きの検索を可能にしています。
ALTER PROCEDURE [dbo].[ProcMinorTable]
@MajorID nchar(2),
@MinorID nchar(2)
AS
DECLARE @strSQL nvarchar(4000)
SET @strSQL=''
SET @strSQL=@strSQL+ 'SELECT I.*, A.Name As MajorName '
SET @strSQL=@strSQL+ 'FROM Minor As I INNER JOIN Major As A ON A.ID = I.MID WHERE 1 = 1 '
IF @MajorID is not null AND @MajorID <> '' BEGIN
SET @strSQL=@strSQL+' AND I.MID=''' + @MajorID +''''
END
IF @MinorID is not null AND @MinorID <> '' BEGIN
SET @strSQL=@strSQL+' AND I.ID=''' + @MinorID +''''
END
SET @strSQL=@strSQL+' ORDER BY I.MID, I.ID '
PRINT(@strSQL)
EXECUTE(@strSQL)
ProcMaterialTable の SQL 文を示します。フィールドワークテーブルを中心として、大分類テーブルと小分類テーブルを JOIN し、 大分類名と小分類名を取り出しています。さらに、緯度経度が有効かどうかの情報と、副問合せにより写真データが何件あるかを検索しています。
ストアドプロシージャの引数として、大分類コード・小分類コード・調査日付の開始日と終了日の範囲指定・タイトル名称の一部・調査場所名の一部を 渡して、条件付きの検索を可能にしています。
まだ画面の説明をしていませんが、このプロシージャを使用する画面では、特定のエリアに含まれるかどうかも検索条件に指定できます。 この条件は、 SQL で実現することはできませんので、プログラムコードで行っています。
ALTER PROCEDURE [dbo].[ProcMaterialTable]
@majorID nvarchar(2) = '',
@minorID nvarchar(2) = '',
@sdate nvarchar(10) = '',
@edate nvarchar(10) = '',
@title nvarchar(128) = '',
@place nvarchar(128) = ''
AS
declare @strSQL nvarchar(4000)
SET @strSQL=''
BEGIN
SET NOCOUNT ON;
SET @strSQL=@strSQL+'SELECT Row_Number()OVER(ORDER BY A.MajorID, A.MinorID, A.ID) As Row_Number, '
SET @strSQL=@strSQL+'B.Name as MajorName, B.Color as ColorID, C.Name as MinorName, D.Name as UnitName, IsMap =
case
when Latitude is null then ''-''
when len(Latitude) = 0 then ''-''
when Longitude is null then ''-''
when len(Longitude) = 0 then ''-''
else ''○''
end, count(E.ItemID) as Photos, A.ID, A.MajorID, A.MinorID, A.Title, A.Place, A.Keyword, A.Others, A.Contents, A.RDate, A.Latitude, A.Longitude, A.Amount, A.UnitID, A.Weather, A.Altitude, A.Width, A.Depth, A.Height, A.CDate, A.Version from Material A '
SET @strSQL=@strSQL+'inner join Major B on B.ID = A.MajorID '
SET @strSQL=@strSQL+'inner join Minor C on C.MID = A.MajorID and C.ID = A.MinorID '
SET @strSQL=@strSQL+'left join Unit D on D.ID = A.UnitID and D.ID <> -1 '
SET @strSQL=@strSQL+'left join Image E on E.ItemID = A.ID where 1 = 1 '
IF @majorID is not null AND @majorID <> '' BEGIN
SET @strSQL=@strSQL+'AND MajorID = ' + @majorID + ' '
END
IF @minorID is not null AND @minorID <> '' BEGIN
SET @strSQL=@strSQL+'AND MinorID = ' + @minorID + ' '
END
If @sdate is not null And @sdate <> '' BEGIN
SET @strSQL=@strSQL+' AND rdate >= Cast(''' + @sdate + ''' as Date) '
End
If @edate is not null And @edate <> '' BEGIN
SET @strSQL=@strSQL+' AND rdate <= Cast(''' + @edate + ''' as Date) '
End
IF @title is not null AND @title <> '' BEGIN
SET @strSQL=@strSQL+' AND Title LIKE ''%'+ @title + '%'' '
END
IF @place is not null AND @place <> '' BEGIN
SET @strSQL=@strSQL+' AND Place LIKE ''%'+ @place + '%'' '
END
SET @strSQL=@strSQL+' group by A.ID, A.MajorID, A.MinorID, A.Title, A.Place, A.Keyword, A.Others, A.Contents, A.RDate, A.Latitude, A.Longitude, A.Amount, A.UnitID, A.Weather, A.CDate, A.Version
, A.Altitude, A.Width, A.Depth, A.Height, B.Name, B.Color, C.Name, D.Name '
SET @strSQL=@strSQL+' order by A.MajorID, A.MinorID, A.ID '
END
PRINT(@strSQL)
EXECUTE(@strSQL)
このデータベースで使用しているトリガーは5個です。
それぞれのトリガーは、同じような構造をもっています。次には、大分類更新トリガーと小分類更新トリガーの登録用 SQL 文を示します。
CREATE TRIGGER [TRG_Major] ON dbo.Major AFTER UPDATE AS BEGIN SET NOCOUNT ON;
update Major set Major.MDate = GETDATE(), Major.Version = Major.Version + 1
WHERE [ID] IN (SELECT [ID] FROM inserted) End
CREATE TRIGGER [TRG_Minor] ON dbo.Minor AFTER UPDATE AS BEGIN SET NOCOUNT ON;
update Minor set Minor.MDate = GETDATE(), Minor.Version = Minor.Version + 1
WHERE [ID] IN (SELECT [ID] FROM inserted) and [MID] in (SELECT [MID] From inserted) End
「初めに」 の章でも書いたように、このプログラムでは Entity Framework を使用しないことにしました。 しかし、データベースに関するツールが全くない状態から始めたのではありません。 Windows Form, Windows WPF ですでに実績のあるクラスライブラリを所有していました。 そこで、 Web Form でも MVC でもこのライブラリを使用することにしました。 Entity Framework を使用しないと決めても、開発に問題は発生しないと分かっていたため、 そうした決定が可能であったともいえます。
このようなライブラリはそれぞれの企業や開発者はすでに独自にお持ちであったり、利用実績があるものをご存じと思います。以下のプログラムではこうしたクラスがあることを 前提とした記述になっています。適宜、読み替えてください。これ以降では、このクラスライブラリに関しては説明しませんので、この章で簡単な説明をしたいと思います。
クラスライブラリの大要は、次の2種類から構成されています。このクラスライブラリには、トランザクションを管理するクラスはありません。 トランザクションに関する議論は、 こちら を参照してください。
Database は、データベースを表現した抽象クラスです。データベースを操作する上で、必要となる機能を網羅したクラスとなっています。
そうした機能の多くは virtual メソッドとして定義してあり、その具体的な実装は派生クラスで行うことを想定しています。
現在は、以下の2つの派生クラス-OracleDatabase と SqlServer-が利用できるようになっています。クラスの間を結んでいる線は、派生関係を示しています。
上にあるクラスが親のクラスで、下にあるのが子のクラスです。
Database
├ OracleDatabase
└ SqlServer
OracleDatabase は Database クラスで virtual として定義されたメソッドを、オラクルデータベース用に実装しているクラスです。同様に、SqlServer は SQL Server 用に実装しているクラスになります。
例えば、今回のプロジェクトでは SQL Server を利用すると決まり、そのクラス名が FieldworkDB となった場合は次のようにします。
Database
└ SqlServer
└ FieldworkDB
SqlServer クラスから、さらに FieldworkDB クラスを派生して利用します。万が一、今回使用するデータベースがオラクルに代わってしまっても、
SQL Server 固有の機能を使っている部分以外は、親クラスを OracleDatabase に変更して再コンパイルするだけで、データベースの差分を吸収するように設計・実装されています。
FieldworkDB には、今回のプロジェクト独自の情報をカプセル化する目的もあります。例えば、データベース名やユーザ名・パスワードなどの接続情報を管理することです。
Table は、データベースを構成するテーブルを表現したクラスです。データテーブルの検索や更新などの機能を標準で持っています。 抽象クラスではありませんので、インスタンスを作ることができますが、そのような設計・実装にはしません。 データベースのテーブルに対応したクラスを派生して利用します。 その際の要点は、データベースのスキーマに関する知識、例えばフィールド名などは、そのクラスに隠ぺいしてしまい、クラスの利用者には公開しないことです。
大分類テーブルのクラスを MajorTable とした場合、クラス階層は次のようになります。
Table
└ MajorTable
View に対応したクラスも、 Table から派生するのが良いと思います。その場合は、検索系のメソッドだけをそのクラスに実装することになります。
Database クラスと Table クラスの使い方の例を示します。
1 FieldworkDB database = new FieldworkDB();
2 MajorTable majorTable = new MajorTable(database);
3 majorTable.Read();
4 DataRow row;
5 for (int i = 0, cnt = majorTable.GetRows(); i < cnt; ++i)
6 {
7 row = majorTable.GetRow(i);
8 何かの処理
9 }
1行目:データベースクラス FieldworkDB は通常のコンストラクタでインスタンスを作成します。
2行目:テーブルクラス MajorTable は自身が含まれるデータベースをコンストラクタに指定することが必須になります。
3~7行目:テーブルクラスのメソッド Read(), GetRows(), GetRow(int) は、親クラス Table に用意されているメソッドで、
それぞれ 無条件の全件検索を行う、データの総件数を返す、引数番目のデータを返す メソッドになります。
Database クラスと Table クラスには、データベースを操作する上で必要と思われる汎用の機能が実装されており、運用での機能不足には遭遇していません。