FileTable（SQL Server）

ITコーディネータのシュウです。

先日、会社の親睦会でさいたま市のさぎ山記念公園というところに行ったときに撮った写真です。

仕事が結構忙しくなってせわしない状況ですが、世界の情勢もいろいろと変化が激しいことを感じるこの頃ですね。アメリカでは、大統領選で、当初の予想を覆してトランプ大統領が次期大統領に決定し、TPPを離脱するというメッセージを出しましたし、お隣の韓国では、朴大統領のスキャンダルによる弾劾手続きが始まろうとしている状況で、朴大統領自身も任期満了前に辞任する意向を示しました。日本にも影響があると思われる出来事がさまざま起きている現状です。
目まぐるしく変わる国際情勢の中、先がなかなか見えない状況がありますが、今できることをしっかりとやって行くしかないと思いながら、、、
今日も頑張るぞ！

＜本日の題材＞
FileTable（SQL Server）

システムでは、画像データを画面上に表示させたいというケースがときにあるかと思います。そのときに、その画像データを物理ファイルとして持ちつつ、データベースではファイルパスを管理する方法と、データベースにバイナリデータ自体を保存する方法のどちらを採用するか、など判断に迷うことがあるかと思います。
今回は、ファイルパスを管理する方法で、SQL Server 2012で追加された機能であるFileTableを使う方法を取り上げてみたいと思います。FileTableでは、Windowsファイル名前空間をサポートすることで、エクスプローラからファイルのツリー階層を確認したり、データを登録することができますし、アプリケーションからファイルパスを指定してファイルを表示したりすることができます。

例）
SQL ServerのDB上に商品マスタを作成し、その商品イメージの画像データをFileTableを使用して保存します。
まず、そのための準備として、SQL Serverの設定が必要になります。
これについては、参考になるホームページもいくつかあり、そちらの内容も参考にしながら設定してみました。

１）インスタンスレベルでFileStreamを有効化
スタートメニューから、[Microsoft SQL Server 2014] の [構成ツール] の SQL Server 2014 構成マネージャを起動し、[SQL Serverのサービス]から FileStreamを有効化したいインスタンスを選択します。そして、マウスの右クリックでプロパティを選択し、[FILESTREAM]タブを選択します。そこで、下図のように、[Transact-SQLアクセスに対してFILESTREAMを有効にする] [ファイルI/Oアクセスに対してFILESTREAMを有効にする]にチェックを入れます。

次に、SQL Server 2014 Management Studioを起動し、サーバーのプロパティ画面の、[詳細設定]メニューから、[FILESTREAMアクセスレベル]を[有効なフルアクセス]に変更します。

２）FileStreamを有効にしたデータベースの作成

SQL Server 2014 Management Studioで新しいデータベース（BLOG_DATA）を作成します。このとき、左側メニューの[ファイルグループ]を選択し、下側のFILESTREAM用のファイルグループを追加し、任意の名前を設定します。

次に、左側メニューの[全般]を選択し、[追加]ボタンにより[ファイルの種類]が[FILESTREAMデータ]のデータベースファイルを1行追加します。このとき、 [ファイルグループ]は先ほど追加したFILESTREAM用のファイルグループが自動的に選択され、[論理名]は任意の名前にします。

さらに、左側メニューの[オプション]を選択し、[FILESTREAMディレクトリ名]に任意の文字列を入力、[FILESTREAM非トランザクションアクセス]をFullにしてデータベースを作成します。

３）FileTableの作成
FILESTREAMデータやファイル、ディレクトリなどの情報を格納する固定スキーマを含んだ特殊なユーザーテーブルとして、FileTable というものを作成します。FileTableを作成するスクリプトは以下のようになります。

CREATE TABLE FileTable_gazo1 AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'FileTable_gazo1',
FILETABLE_COLLATE_FILENAME = database_default
)
GO

実際のデータベースをManagement Studioで見てみると、「FileTable_gazo1」というテーブルが、「FileTables」というカテゴリの中に作成されているのがわかります。

４）画像データの登録
上記で作成したFileTableにエクスプローラで接続して、画像データを登録します。SQLServer Management Studioから先ほど作成したFileTableを右クリックし、コンテキストメニューから［FileTableディレクトリの探索］を選択します。

すると、エクスプローラが開いてきます。

ここに、エクスプローラで、別のフォルダから画像データ（今回はペンの画像）をドラッグ＆ドロップで持ってきます。ドラッグ＆ドロップした後の状態が以下。

次に、この画像情報を持つ商品マスタテーブルを作成し、データを登録します。

CREATE TABLE dbo.syomst(
商品コード varchar(10) NOT NULL,
商品名 nvarchar(20) NULL,
ファイル名 varchar(50) NULL,
CONSTRAINT PK_syomst PRIMARY KEY (商品コード)
GO

INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0001', N'ペン_01', 'images01.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0002', N'ペン_02', 'images02.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0003', N'ペン_03', 'images03.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0004', N'ペン_04', 'images04.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0005', N'ペン_05', 'images05.jpg')
…（途中省略）
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0040', N'ペン_40', 'images40.jpg')
GO