This project is read-only.

目次

概要

SQLファイルの管理方法、読み込み方法、SQLコメントに記述できる式について説明します。

2 Way SQL

Somaは2 Way SQLをサポートします。2 Way SQLとは、1つのSQLが次の2つの環境で動作することを言います。
  1. 実行プログラム
  2. SQLのツール(SQL Server Management Studioなど)
2 Way SQLは、SQLを単独でテストしたり、プログラム開発者とSQL作成者の作業を分離したりするのに非常に有効な方法です。
Somaでは、バインド変数や条件分岐等プログラムで必要とされる情報をSQLコメントで表現することで2 Way SQLを実現しています。

SQLファイルの管理方法

SQLファイル(拡張子がsql)をリソースファイル(拡張子がresx)で管理する必要があります。
SQLファイルをリソースファイルへ登録するには、リソースファイルを開いて左上のメニューから「Files」を選択します。

sql_resource.png

SQLファイルをソリューションエクスプローラーから上記のビューへドラッグ&ドロップします。

sql_files.png

Visual Studioから行うSQLの実行やバリデーション

SQLファイルを開くと、SQL Serverに接続するコマンドが有効になります。

sql_file_execution.png

「Execute SQL」や「Validate SQL Syntax」というボタンを押すことでSQLの実行やバリデーションを行えます。

式コメント

Somaでは、SQLコメント中に特別な式を記述をすることで値のバインディングや条件分岐を行います。 Somaに解釈されるSQLコメントを式コメントと呼びます。

式コメント概要

式コメントの中では、パラメータとして渡されたオブジェクトのプロパティにアクセスできます。パラメータとして使用できる型は、プリミティブ型、配列型、System.String型以外です。アクセス可能なプロパティは、publicなgetアクセサを持っている必要があります。式コメントにおいては、プロパティへのアクセスは読み取りのみ可能です。書き込みはできません。また、プロパティの大文字/小文字は区別されます。

匿名型をパラメータとする利用例

少数のプリミティブ型やSystem.String型の値をSQLのバインド変数へ割り当てるには、 匿名型をパラメータとして利用できます。
以下に例を示します。

string employeeName = "King";
decimal salary = 2000;
var param = new {employeeName, salary};
GetResultListByResource<Employee>(() => EmployeeResource.SelectByNameAndSalary, param)
SQLファイルでは式コメント内で匿名型が持つemployeeNameプロパティとsalaryプロパティにアクセスできます。

select * from Employee where EmployeeName = /* employeeName */'AAA' and Salary = /* salary */1000

定義済みの型をパラメータする利用例

匿名型ではなくあらかじめ定義された型をパラメータとして利用できます。
以下に例を示します。

public class EmployeeParam
{
    public string EmployeeName { get; set; }
    public decimal Salary { get; set; }
}
上記のEmployeeParamがあらかじめ定義されていればこのクラスをパラメータとして使用できます。

EmployeeParam param = new EmployeeParam
{
    EmployeeName = "King",
    Salary = 2000
};
GetResultListByResource<Employee>(() => EmployeeResource.SelectByNameAndSalary, param)
SQLファイルでは式コメント内でEmployeeParamクラスが持つEmployeeNameプロパティやSalaryプロパティにアクセスできます。

select * from Employee where EmployeeName = /* EmployeeName */'AAA' and Salary = /* Salary */1000

通常のブロックコメント

ブロックコメントの始まりを示す/*の直後の3文字目が次の文字のいずれかの場合、そのブロックコメントは式コメントではなく通常(SQL本来)のブロックコメントだとみなされます。
  • *
  • +
  • :
特に理由がない限り、通常のブロックコメントは3文字目を * とし、/** ~ */ と記述することをお奨めします。
以下に例を示します。

/**
 * ここは通常のブロックコメント。
 */
select * from Employee where EmployeeName = /* EmployeeName */'AAA' and Salary = /* Salary */1000

バインド変数コメント

バインド変数を示す式コメントをバインド変数コメントと呼びます。 バインド変数はSystem.Data.Common.DbParameterのインスタンスにマッピングされます。

バインド変数は/*~*/というブロックコメントで囲んで示します。バインド変数コメントの直後にはテスト用データの指定が必須です。 テスト用データは、実行時には除去され使用されません。テストデータは、開発時にSQLのツールでテスト実行や構文チェックに使用されることを想定しています。

例えば、SQLファイルに次のように記述するとします。

select * from Employee where Age > /* min */10 and Age < /* max */30 
この場合、データプロバイダには次のSQLが渡されます(SQL Server用のデータプロバイダを使用する場合)。

select * from Employee where Age > @p0 and Age < @p1 
バインド変数コメントは@p0や@p1などのパラメータを表す文字列に置換され、テスト用データは除去されます。

IEnumerable型を利用したIN句内の複数のバインド変数の表現

IEnumerable型のプロパティはIN句内の複数のバインド変数を表現できます。バインド変数コメントの直後には、括弧つきでテスト用データを指定する必要があります。
この機能を利用するプログラムコードとSQLファイルの記述例は次のとおりです。

List<string> names = new List<string>{"KING", "SMITH", "JOHNE"};
GetResultListByResource<Employee>(() => EmployeeResource.SelectByNames, new {names});
select * from Employee where EmployeeName in /* names */('aaa', 'bbb')
この場合、データプロバイダには次のSQLが渡されます(SQL Server用のデータプロバイダを使用する場合)。

select * from Employee where EmployeeName in (@p0, @p1, @p2) 
バインド変数コメントは@p0や@p1などのパラメータを表す文字列に置換され、テスト用データは除去されます。

埋め込み変数コメント

埋め込み変数を示す式コメントを埋め込み変数コメントと呼びます。 埋め込み変数の値は、SQLを組み立てる際にSQLの一部として直接埋め込まれます。 SQLインジェクションを防ぐため、埋め込み変数の値にシングルクォテーション、セミコロン、行コメント、ブロックコメントは含めることを禁止しています。

埋め込み変数は/*#~*/というブロックコメントで示します。 埋め込み変数はORDER BY句など、SQLの一部をプログラムで組み立てたい場合に使用できます。 プログラムコードとSQLファイルの記述例は次のとおりです。

new param = new { salary = 1000, orderBy = "order by Salary, EmployeeId" };
GetResultListByResource<Employee>(() => EmployeeResource.SelectBySalary, param);
select * from Employee where Salary > /* salary */100 /*# orderBy */
この場合、データプロバイダには次のSQLが渡されます(SQL Server用のデータプロバイダを使用する場合)。

select * from Employee where Salary > @p0 order by Salary, EmployeeId

条件分岐コメント

条件分岐を示す式コメントを条件分岐コメントと呼びます。 構文は次のとおりです。
/*%if 条件式 */ ~ /*%end */

条件式は、結果がSystem.Boolean型と評価される式でなければいけません。SQLファイルの記述例を示します。

select * from Employee where 
/*%if employeeId != null */
    EmployeeId = /* employeeId */99
/*%end */
条件式の評価結果が真の場合(employeeIdがnullでない場合)、データプロバイダには次のSQLが渡されます(SQL Server用のデータプロバイダを使用する場合)。

select * from Employee where EmployeeId = @p0
条件式の評価結果が偽の場合(employeeIdがnullの場合)、データプロバイダには次のSQLが渡されます。

select * from Employee
条件式の評価結果が偽の場合に条件分岐コメントの外にあるWHERE句が出力されないのは、次で説明する句の自動除去機能が働いているためです。

条件分岐コメントにおける句の自動除去

条件分岐コメントを使用した場合、条件分岐コメントの前にあるWHERE、HAVING、GROUP BY、 ORDER BY、FOR UPDATEの句は、自動で出力の要/不要が判定されます。

例えば、SQLファイルに次の記述をしたとします。

select * from Employee where 
/*%if employeeId != null */
    EmployeeId = /* employeeId */99
/*%end */
この例では評価結果が偽の場合(employeeIdがnullの場合)、WHERE句が検索条件を持たなくなります。つまりWHERE句が不要と言えます。この場合、Somaは、WHERE句を除去して次のSQLをデータプロバイダに渡します。

select * from Employee

条件分岐コメントにおけるANDやORの自動除去

条件分岐コメントを使用した場合、条件分岐コメントの後ろにつづくANDやORは、自動で出力の要/不要が判定されます。

例えば、SQLファイルに次の記述をしたとします。

select * from Employee where 
/*%if employeeId != null */
    EmployeeId = /* employeeId */99
/*%end */
and EmployeeName like 's%'
この例では評価結果が偽の場合(employeeIdがnullの場合)、WHERE句の直後にANDが続くことになります。つまりANDが不要と言えます。この場合、Somaは、ANDを除去して次のSQLをデータプロバイダに渡します。

select * from Employee where EmployeeName like 's%'

条件分岐コメントにおける制約

条件分岐コメントのifとendはSQLの同じ節に含まれなければいけません。 節とは、SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、FOR UPDATEの節です。例えば、次のSQLファイルの記述では、ifがFROM節にありendがWHERE節にあるため不正です。

select * from Employee /*%if employeeId != null */ where EmployeeId = /* employeeId */99 /*%end */
また、ifとendは同じレベルの文に含まれなければいけません。 次の例では、ifが括弧の外にありendが括弧の内側にあるので不正です。

select * from Employee where EmployeeId in /*%if departmentId != null */(...  /*%end */ ...)

elseifとelseを利用した例

ifとendのブロックの間には任意の数のelseifと1つ以下のelseブロックを含めることができます。

/*%if 条件式 */ ~ /*%elseif 条件式 */ ~ /*%else */ ~ /*%end */

SQLファイルの記述例を示します。

select 
  * 
from
  Employee 
where 
/*%if employeeId != null */
  EmployeeId = /* employeeId */9999
/*%elseif departmentId != null */ 
  and
  DepartmentId = /* departmentId */99
/*%else*/
  and
  DepartmentId is null
/*%end */
employeeId != null が成立する場合、データプロバイダには次のSQLが渡されます(SQL Server用のデータプロバイダを使用する場合)。

select 
  * 
from
  Employee 
where 
  EmployeeId = @p0
employeeId != null が成立せず、department_id != null が成立する場合、データプロバイダには次のSQLが渡されます。DepartmentIdの直前のANDは自動で除去されるため出力されません。
select 
  * 
from
  Employee 
where 
  DepartmentId = @p0
employeeId != null もdepartment_id != null も成立しない場合、データプロバイダには次のSQLが渡されます。DepartmentIdの直前のANDは自動で除去されるため出力されません。

select 
  * 
from
  Employee 
where 
  DepartmentId is null

繰り返しコメント

繰り返しを示す式コメントを繰り返しコメントと呼びます。 構文は、次のとおりです。

/*%foreach 識別子 in 式 */ ~ /*%end */

識別子は、繰り返される要素を指す変数です。 式は、System.Collections.IEnumerable型として評価される式でなければいけません。SQLファイルの記述例を示します。
select * from Employee where
/*%foreach name in names */
EmployeeName like /* name */'hoge'
  /*%if name_has_next */
/*# "or" */
  /*%end */
/*%end */
namesが3つの要素からなる列挙子を表す場合、データプロバイダには次のSQLが渡されます(SQL Server用のデータプロバイダを使用する場合)。

select * from Employee where
EmployeeName like @p0
or
EmployeeName like @p1
or
EmployeeName like @p2

繰り返しコメントの内側では、次の2つの特別な変数を使用できます。
  • item_has_next : 次の繰り返し要素が存在するかどうかを示すSystem.Boolean型の値。
  • item_index : 繰り返しのインデックスを表すSystem.Int32型の値。値は0始まり。
itemは繰り返される要素を指す変数です。つまり繰り返される要素を指す変数がnameの場合、これらの変数はそれぞれname_has_nextとname_indexになります。

繰り返しコメントにおける句の自動除去

繰り返しコメントを使用した場合、繰り返しコメントの前にあるWHERE、HAVING、GROUP BY、 ORDER BY、FOR UPDATEの句は、自動で出力の要/不要が判定されます。

例えば、SQLファイルに次の記述をしたとします。

select * from Employee where 
/*%foreach name in names */
EmployeeName like /* name */'hoge'
  /*%if name_has_next */
/*# "or" */
  /*%end */
/*%end */
この例ではnamesの要素数が0の場合(繰り返しが行われない場合)、WHERE句が検索条件を持たなくなります。つまりWHERE句が不要と言えます。この場合、Somaは、WHERE句を除去して次のSQLをデータプロバイダに渡します。

select * from Employee

繰り返しコメントにおけるANDやORの自動除去

繰り返しコメントを使用した場合、繰り返しコメントの後ろにつづくANDやORは、自動で出力の要/不要が判定されます。

例えば、SQLファイルに次の記述をしたとします。

select * from Employee where 
/*%foreach name in names */
EmployeeName like /* name */'hoge'
  /*%if name_has_next */
/*# "or" */
  /*%end */
/*%end */
or
Salary > 1000
この例ではnamesの要素数が0の場合(繰り返しが行われない場合)、WHERE句の直後にORが続くことになります。つまりORが不要と言えます。この場合、Somaは、ORを除去して次のSQLをデータプロバイダに渡します。

select * from Employee where Salary > 1000

繰り返しコメントにおける制約

繰り返しコメントのforeachとendはSQLの同じ節に含まれなければいけません。 節とは、SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、FOR UPDATEの節です。
また、foreachとendは同じレベルの文に含まれなければいけません。つまり、どちらかが括弧の外側、他方が括弧の内側といった記述は認められません。

式言語

式コメントでは簡易的な式言語を使用できます。以下に式言語の文法を説明します。ここで言及していない、宣言、代入、算術演算子、メソッドの実行といった機能はサポートしていません。

リテラル

以下のリテラルを使用可能です。
リテラル 説明
null System.Object nullリテラル
true System.Boolean trueリテラル
false System.Boolean falseリテラル
10 System.Int32 数値リテラル
"abc" System.String 文字列リテラル


例を示します。

select * from Employee where 
/*%if employeeName != null && employeeName.Lenght > 5 */
    EmployeeName = /* employeeName */'smith'
/*%end */
この例では、nullリテラルと数値リテラルを使用しています。

比較演算子

以下の比較演算子を使用可能です。
演算子 説明
== 等値演算子
!= 不等演算子
< 小なり演算子
<= 小なりイコール演算子
> 大なり演算子
>= 大なりイコール演算子


大なり演算子を使用する例を示します。

select * from Employee where 
/*%if employeeName.Length > 0 */
    EmployeeName = /* employeeName */'smith'
/*%end */
比較演算子を利用するには、 被演算子の型がその演算子をサポートしている必要があります。

論理演算子

以下の論理演算子を使用できます。
演算子 説明
! 論理否定演算子
&& 論理積演算子
|| 論理和演算子


論理和演算子と論理積演算を使用する例を示します。

select * from Employee where 
/*%if (departmentId == null || managerId == null) && employee_name != null */
    EmployeeName = /* employeeName */'smith'
/*%end */
演算子が適用される優先度は括弧を利用して制御できます。

プロパティアクセス

プロパティアクセスには非staticなプロパティへのアクセスとstaticなプロパティへのアクセスの2種類があります。アクセスできるプロパティはpublicなgetアクセサを持つものに限ります。ただし、インデクサにはアクセスできません。
プロパティへのアクセスは読み取りのみ可能です。書き込みはできません。また、プロパティの大文字/小文字は区別されます。

非staticなプロパティへのアクセス

ドット(.)でプロパティ名をつなげてアクセスします。例を示します。

select * from Employee where EmployeeName = /* Employee.EmployeeName */'smith'
この例では、バインド変数コメント内でEmployeeインスタンスのEmployeeNameプロパティにアクセスしています。

staticなプロパティへのアクセス

型名を$で囲み、その後ろにプロパティ名をつなげてアクセスします。例を示します。

update Employee set EmployeeName = /* employeeName */, ModifiedTime = /* $System.DateType$Now */ where = /* employeeId */1
この例では、バインド変数コメント内でSystem.DateTypeのNowプロパティにアクセスしています。

組み込み関数

組み込み関数は、値の評価と変換を行うためのユーティリティです。組み込み関数には次の種類があります。
戻り値の型 関数名とパラメータ 説明
Boolean @IsNullOrEmpty(String value) 文字列がnullもしくは空の場合にtrueを返します。
Boolean @IsNullOrWhiteSpace(String value) 文字列がnullもしくは空白の場合にtrueを返します。
String @Escape(String value) LIKE検索のために文字列に含まれるワイルドカード(%_など)を$でエスケープします。引数がnullの場合、戻り値はnullになります。
String @StartsWith(String value) 値を前方一致検索のための文字列へ変換します。また、文字列に含まれるワイルドカード(%_など)を$でエスケープします。引数がnullの場合、戻り値はnullになります。
String @Contains(String value) 値を中間一致検索のための文字列へ変換します。文字列に含まれるワイルドカード(%_など)を$でエスケープします。引数がnullの場合、戻り値はnullになります。
String @EndsWith(String value) 値を後方一致検索のための文字列へ変換します。文字列に含まれるワイルドカード(%_など)を$でエスケープします。引数がnullの場合、戻り値はnullになります。

@IsNullOrEmpty

@IsNullOrEmptyを使用すると文字列がnullもしくは空である場合の条件式を簡潔に記述できます。SQLファイルの記述例を示します。

select * from Employee where 
/*%if @IsNullOrEmpty(employeeName) == false */
    EmployeeName = /* employeeName */'smith'
/*%end */
これは、次のように記述するのと意味的に等価です。

select * from Employee where 
/*%if employeeName != null && employeeName.Length > 0 */
    EmployeeName = /* employeeName */'smith'
/*%end */

@IsNullOrWhiteSpace

@IsNullOrWhiteSpaceを使用すると文字列がnullもしくは空白である場合の条件式を記述できます。SQLファイルの記述例を示します。

select * from Employee where 
/*%if @IsNullOrWhiteSpace(employeeName) == false */
    EmployeeName = /* employeeName */'smith'
/*%end */

@Escape

@Escapeを使用すると文字列に含まれるLIKE検索用のワイルドカードをエスケープできます。SQLファイルの記述例を示します。

select * from Employee where EmployeeName like /* @StartsWith(employeeName) */'smith' escape '$'
この例では、プログラムから渡されるemployeeNameの値が「AB%C」である場合、@Escapeにより値は「AB$%C」に変換されます。 @Escapeを利用する場合、SQLには「escape '$'」を必ず指定してください。

@StartsWith

@StartsWithを使用すると文字列をLIKE検索で使用可能な前方一致検索用の文字列に変換できます。SQLファイルの記述例を示します。

select * from Employee where EmployeeName like /* @StartsWith(employeeName) */'smith' escape '$'
この例では、プログラムから渡されるemployeeNameの値が「ABC」である場合、@StartsWithにより値は「ABC%」に変換されます。もし、employeeNameの値が「AB%C」というように「%」を含んでいる場合、「%」は「$」でエスケープされ、値は「AB$%C%」に変換されます。 @StartsWithを利用する場合、SQLには「escape '$'」を必ず指定してください。

@Contains

@Containsを使用すると文字列をLIKE検索で使用可能な中間一致検索用の文字列に変換できます。SQLファイルの記述例を示します。

select * from Employee where EmployeeName like /* @Contains(employeeName) */'smith' escape '$'
この例では、プログラムから渡されるemployeeNameの値が「ABC」である場合、@Containsにより値は「%ABC%」に変換されます。もし、employeeNameの値が「AB%C」というように「%」を含んでいる場合、「%」は「$」でエスケープされ、値は「%AB$%C%」に変換されます。 @Containsを利用する場合、SQLには「escape '$'」を必ず指定してください。

@EndsWith

@EndsWithを使用すると文字列をLIKE検索で使用可能な後方一致検索用の文字列に変換できます。SQLファイルの記述例を示します。

select * from Employee where EmployeeName like /* @EndsWith(employeeName) */'smith' escape '$'
この例では、プログラムから渡されるemployeeNameの値が「ABC」である場合、@EndsWithにより値は「%ABC」に変換されます。もし、employeeNameの値が「AB%C」というように「%」を含んでいる場合、「%」は「$」でエスケープされ、値は「%AB$%C」に変換されます。 @EndsWithを利用する場合、SQLには「escape '$'」を必ず指定してください。

Last edited Aug 16, 2010 at 5:26 PM by toshihiro, version 36

Comments

No comments yet.