SQL CLRを極める3つのコーディング・テクニック
前回の「SQL CLRコーディング、最初の一歩 」では、SQL CLRに関する基本的なコーディングを解説しました。コーディングの容易さや、SQL Server 2005とVisual Studio 2005の綿密な連携による生産性の高さなど、多くの利点を感じ取ることができたはずです。
今回は、テーブルを返すためのユーザー定義関数であるテーブル値関数（以下、TVF）の解説に加えて、外部データベースとの接続、正規表現の利用などを解説します。
SQL CLRでTVFを使うメリット
TVFは、テーブルを返すことのできるユーザー定義関数です。前回解説したスカラ値関数（SVF）が単一の値を返す関数であったのに対して、TVFではテーブル構造のデータを返すことができます。
Transact-SQLでもTVFを作成することができましたが、その内部動作はSQL CLRとは異なります。Transact-SQL TVFでは、結果が中間テーブルに保存され、参照する際はこの中間テーブルを利用します。一方、CLR TVFでは結果がストリーミングとして提供されます。中間テーブルを利用する場合、すべてのデータが中間テーブルにセットされるまでデータを利用することができませんが、ストリーミングモデルの場合、最初の行が利用可能となった時点で直ちに結果を参照できます。従って、膨大な量の行が返される場合はCLR TVFが適しているといえるでしょう。
TVFは次のようなシーンでの利用が想定されます。
- 入力引数からテーブルを作成する場合（カンマ区切りの文字列を受け取り、テーブルとして返すなど）
- 外部データからテーブルを作成する場合（イベントログを読み取り、テーブルとして返すなど）
上記以外にも、TVFはストアドプロシージャと異なりFROM句で参照できるメリットがあるため、処理結果をより柔軟に利用することが可能です。
TVFプログラムのサンプル作成
それでは、早速TVFのサンプルを作成してみましょう。今回のサンプルはカンマ区切りで渡された文字列を分解してテーブル値で返します。サンプル内の接続データベースはSQL Server 2005のサンプルデータベースであるAdventure Worksを利用しています。
using System; using System.Collections; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { //FillRowMethodName：データを返すメソッドの名前を表す //TableDefinition ：結果のテーブル定義を表す //IEnumerable ：.NET Frameworkで配列およびコレクションを表す型 [SqlFunction(FillRowMethodName = "FillRow", TableDefinition = "addr NVARCHAR(50)")] public static IEnumerable SampleTVF(string str) { return str.Split(','); } public static void FillRow(object row, out string str) { str = (string)row; } }
コードをデバッグするため、Test.SQLへ下記ステートメントを記述します。
SELECT addr FROM dbo.SampleTVF('tokyo,saitama,tokyo,oosaka') GROUP BY addr
実行した結果はリスト3のとおりです。引数に従って関数よりテーブルが返され、さらに呼び出し側でGROUP BY句によるグループ化が行われていることに注目してください。
addr ----------- oosaka saitama tokyo
前回解説したストアドプロシージャやスカラ値関数に比べて若干癖のあるコーディングが必要となりますが、一度動きを確認すれば、それほど苦労せず使いこなすことができるはずです。
外部データベースへの接続
SQL CLRを利用する大きなメリットの1つとして、外部データベースへの接続が挙げられます。ストアドプロシージャやユーザー定義関数などから外部データベースへ接続できるため、異種データベース間でのデータ連携などデータベース・プログラミングにおける柔軟性が大きく高まっています。
外部データベースへの接続に当たって、下記の2点に注意する必要があります。
- 権限セットの指定
- コンテキスト接続と通常の接続
1．権限セットの指定
権限セットについては、第4回「SQL CLRの仕組みとメリットを理解しよう 」ですでに取り上げました。ネットワークやファイル、外部データベースなど多様なリソースへのアクセスが可能なSQL CLRの動作を制限するための仕組みです。今回は外部データベースへの接続を行うので、この権限セットを変更する必要があります。
SQL CLRは権限セットのデフォルトで「セーフ」が指定され、外部リソースへのアクセスが不可能な状態となっていますが、外部リソースを利用する場合は、「外部」を指定します。
「外部」や「アンセーフ」を指定する場合、「セーフ」に比べセキュリティが緩くなったことを踏まえて、作成したアセンブリへのアクセスを制限するなどの対策が必要となります。SQL CLRのセキュリティの詳細は、MSDN2のドキュメント「CLR統合のセキュリティ 」を確認してください。
また、セキュリティ上の懸念から「外部」「アンセーフ」を利用するにはかなり厳しい条件をクリアする必要があります。条件の詳細は同じくMSDN2のドキュメント「アセンブリの作成 」を確認してください。マイクロソフトの推奨では、アセンブリファイルから非対称キーを作成し、そのキーにマップするログインを作成する必要があります。権限セットの変更がどれだけのセキュリティ的な脅威をもたらすか、この条件から分かりますね。
| 1 | アセンブリファイルより非対称キーを作成し、キーにひも付けられるログインを作成する。このログインに対し、利用する権限セットに合わせて「EXTERNAL ACCESS ASSEMBLY」または「UNSAFE ASSEMBLY」権限を与える |
|---|---|
| 2 | データベース所有者（DBO）が利用する権限セットに合わせて「EXTERNAL ACCESS ASSEMBLY」または「UNSAFE ASSEMBLY」権限を持ち、さらにデータベースの「TRUSTWORTHY」データベース・プロパティがONに設定されている。非推奨 |
| 表1 権限セット「外部」「アンセーフ」を利用するための条件 | |
メモ：Visual Studio上では権限セット名が次のように翻訳されています。MSDNでは翻訳前の英語名が使われているので注意してください。翻訳は外部アクセス、とした方が分かりやすいですよね。
| 英語名 | 翻訳名 | |
|---|---|---|
| SAFEセーフ | ||
| EXTERNAL_ACCESS外部 | ||
| UNSAFEアンセーフ | ||
権限セットを変更するには、プロジェクトのプロパティより行います。「プロジェクト」メニュー内の「＜プロジェクト名＞のプロパティ」より「データベース」タブを開き、「アクセス許可のレベル」を「外部」に設定します（図1）。
2．コンテキスト接続と通常の接続
SQL CLRでは、大きく分けて2種類のデータベースへの接続方法がサポートされています。1つが前回で解説したコンテキスト接続であり、2つ目が一般的なADO.NETなどで見慣れた接続先を指定する通常の接続です。コンテキスト接続は実行時の認証情報を引き継ぎインプロセスで実行されるため、通常の接続に対しパフォーマンスの面で優位性を持っています。
今回は外部データベースへの接続を行うため、通常の接続によるSQL CLRの実行を確認してみましょう。以下にコンテキスト接続と通常の接続との違いを示します。接続文字列の記述が異なるだけで、コード自体に大きな違いはないことが分かります。
//コンテキスト接続 //using(SqlConnection conn = new SqlConnection("context connection=true")) { //通常の接続 using (SqlConnection conn = new SqlConnection( "server=MyServer; database=AdventureWorks; " + "user id=MyUser; password=Password")) { conn.Open(); SqlCommand cmd = new SqlCommand( "SELECT Name, GroupName FROM HumanResources.Department", conn); SqlDataReader r = cmd.ExecuteReader(); while (r.Read()) { SqlContext.Pipe.Send(r); } }
正規表現の利用
SQL CLRはこれまでに何度か触れたとおり、パフォーマンスなどの面からTransact-SQLをすべて置き換えられるわけではありませんが、Transact-SQLでは困難だった複雑な計算処理や文字列操作などでは大きく力を発揮します。そこで、今回は.NET Frameworkで提供される正規表現ライブラリの利用を確認してみましょう。
正規表現とは文字列のパターンを表現する表記法です。文字列の検索や置換といったシーンで利用されています。ここでは正規表現による入力値チェックをSQL CLRで実装します。
.NET FrameworkではRegularExpressions名前空間で、正規表現を利用するための各種クラスが提供されています。基本的な利用の流れとしては、Regexクラスへ正規表現パターンを格納し、Regexクラスで実行したパターンマッチングの結果をMatchクラスへ格納します。その詳細については＠IT記事「スマートな文字列処理のための正規表現入門（前編） 」を確認していただくとして、早速サンプルの確認を行いましょう。次のサンプルでは、入力値に英文字以外が含まれる場合にエラーを返しています。
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Text.RegularExpressions; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void SampleStored(string FirstName) { //正規表現パターンの定義 Regex regex = new Regex("[^A-Za-z]"); //パターンマッチング結果の格納 Match m = regex.Match(FirstName); //マッチする文字列が含まれる場合、True if (m.Success) { SqlContext.Pipe.Send("不正な入力です！"); return; } //コンテキスト接続 using(SqlConnection conn = new SqlConnection("context connection=true")) { conn.Open(); SqlCommand cmd = new SqlCommand( "SELECT ContactID, FirstName,LastName FROM Person.Contact" + " Where FirstName=@p1", conn); SqlParameter p1 = new SqlParameter("@p1", FirstName); cmd.Parameters.Add(p1); //ExecuteAndSend：コマンドを実行し、結果を直接返す SqlContext.Pipe.ExecuteAndSend(cmd); } } }
上記コードにて作成したストアドプロシージャの呼び出しに英文字以外が含まれていると、次のようにエラーが返ります。
EXEC SampleStored 'Robert!' 不正な入力です！ (0 行が返されました) EXEC SampleStored 'Phillip' ContactID FirstName LastName ----------- ---------------------- 47 Phillip Bacalzo 19349 Phillip Suri ～中略～ (23 行が返されました)
SQL CLRでの正規表現の利用を確認できました。SQL CLRでの利用といっても、C#で書く正規表現の利用法と違いはありません。C#やVB.NETで築いたノウハウをそのまま移行できることが分かります。
今回のサンプルでは正規表現を取り上げましたが、.NET Frameworkには正規表現以外にもさまざまなライブラリが提供されています。Transact-SQLでは困難だった処理に対して、これらのライブラリ群を活用することでSQL CLRの本領が発揮されるといえるでしょう。
まとめ
今回は、TVFに加えて、SQL CLRの花形ともいえる外部データベースへの接続、.NET Frameworkのライブラリ利用を取り上げました。従来、これらの処理を実装するためには個別にプログラムを作成する必要がありましたが、SQL CLRによってデータベース内でこれらの処理を行うことが可能となります。また、C#やVB.NETで作成した独自ライブラリなどのコード資産を生かし、生産性を高めることもできるでしょう。
しかしながら、万能に見えるSQL CLRですがセキュリティ面での注意点に加え、過剰なSQL CLR化はデータベースの負荷増大につながります。また、従来型のTransact-SQLが優位性を保つ処理も存在するため、開発者にはそれぞれに適した使い分けが求められます。（
