目次

概要

Somaは2 Way SQLをサポートします。2 Way SQLとは、次の2つの環境で動作可能なSQLの総称です。
  1. SQLのツール(SQL Server Management Studioなど)
  2. 実行プログラム
2 Way SQLは、バインド変数や条件分岐をSQLコメント内に特別な式言語で表現することで実現されています。式言語を含んだSQLコメントは式コメントと呼ばれます。
SQLのツールで実行する場合、式コメントは単なるSQLコメントなのでSQLの発行に何の影響も与えませんが、実行時はSomaに解釈されSQLは変換されて発行されます。
2 Way SQLにより、動作確認しながら組み立てたSQLをプログラム用の記述に変換する手間を減らすことができます。また、プログラムのデバッグ時にプログラム内のSQLをツールで動作する形に変換する必要もなくなります。

2 Way SQLは、DbモジュールとLocalDbモジュールのquery関数やexecute関数に使用可能です。
これらの関数には、タプルを要素とするリストを渡すことができます。タプルはkey/value形式で、式コメントの中でkeyにより参照され値が評価されます。

また、2 Way SQLは、DbクラスとLocalDbクラスのQueryメソッドやExecuteメソッドに使用可能です。
これらのメソッドには、匿名型、System.Collections.IDictionary型、System.Collections.Generic.IDictionary<TKey,TValue>型のいずれかのインスタンスを渡すことができます。匿名型のプロパティで与えられた値は式コメントの中で参照され評価されます。

バインド変数コメント

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

バインド変数は/*~*/というブロックコメントで囲んで示します。

単一のバインド変数へのマッピング

バインド変数コメントの直後にはテスト用データの指定が必須です。 テスト用データは、実行時には除去され使用されません。テストデータは、開発時にSQLのツールによるテスト実行や構文チェックにのみ使用されます。

以下に例を示します。

F#
let empList = 
  Db.query<Employee>
    config
    "select * from Employee where Age > /* min */10 and Age < /* max */30"
    ["min" @= 5; "max" @= 35]
C#
var empList = 
  db.Query<Employee>(
    "select * from Employee where Age > /* min */10 and Age < /* max */30",
    new { min = 5, max = 35 } );

ここでのバインド変数コメントは /* min */ と /* max */ です。バインド変数コメントの直後の 10 や 30 はテストデータです。

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

IN句における複数のバインド変数へのマッピング

System.Collections.IEnumerable型の値はIN句内の複数のバインド変数にマッピングできます。バインド変数コメントの直後には、括弧つきでテスト用データを指定する必要があります。テスト用データは、実行時には除去され使用されません。テストデータは、開発時にSQLのツールでテスト実行や構文チェックに使用されます。
以下に例を示します。

F#
let empList = 
  Db.query<Employee>
    config
    "select * from Employee where EmployeeName in /* nameList */('aaa', 'bbb')"
    ["nameList" @= ["KING"; "SMITH"; "JOHNE"]]
C#
var empList = 
  db.query<Employee>(
    "select * from Employee where EmployeeName in /* nameList */('aaa', 'bbb')",
    new { nameList = ["KING"; "SMITH"; "JOHNE"] });
ここでのバインド変数コメントは /* nameList */ です。バインド変数コメントの直後の ('aaa', 'bbb') はテストデータです。

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

埋め込み変数コメント

埋め込み変数を示す式コメントを埋め込み変数コメントと呼びます。 埋め込み変数の値は、SQLを組み立てる際にSQLの一部として直接埋め込まれます。

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

F#
let empList = 
  Db.query<Employee>
    config
    "select * from Employee where Salary > /* salary */100 /*# orderBy */"
    ["salary" @= 1000; "orderBy" @= "order by Salary, EmployeeId"]
C#
var empList = 
  db.query<Employee>(
    "select * from Employee where Salary > /* salary */100 /*# orderBy */",
    new { salary = 1000, orderBy = "order by Salary, EmployeeId" } );

実行されるSQL
select * from Employee where Salary > @p0 order by Salary, EmployeeId

通常のブロックコメント

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

F#
let empList = 
  Db.query<Employee>
    config
    "/** ここは通常のブロックコメント */ select * from Employee"
    []
C#
var empList = 
  db.query<Employee>(
    "/** ここは通常のブロックコメント */ select * from Employee");

条件分岐コメント

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

条件式は、結果がSystem.Boolean型と評価される式でなければいけません。
以下に例を示します。

F#
let empList = 
  Db.query<Employee>
    config
    "select * from Employee where /*% if employeeId <> null */ EmployeeId = /* employeeId */99 /*% end */"
    ["employeeId" @= 1]
C#
var empList = 
  db.query<Employee>(
    "select * from Employee where /*% if employeeId <> null */ EmployeeId = /* employeeId */99 /*% end */",
    new { employeeId = 1 } );

条件式の評価結果が真の場合に実行されるSQL(SQL Server用の設定を使用する場合)
select * from Employee where EmployeeId = @p0

条件式の評価結果が偽の場合に実行されるSQL(SQL Server用の設定を使用する場合)
select * from Employee

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

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

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

F#
let empList = 
  Db.query<Employee>
    config
    "select * from Employee where /*% if employeeId <> null */ EmployeeId = /* employeeId */99 /*% end */"
    ["employeeId" @= 1]
C#
var empList = 
  db.query<Employee>(
    "select * from Employee where /*% if employeeId <> null */ EmployeeId = /* employeeId */99 /*% end */",
    new { employeeId = 1 } );

この例では評価結果が偽の場合(employeeIdがnullの場合)、WHERE句が検索条件を持たなくなります。つまりWHERE句が不要と言えます。この場合、SomaはWHERE句を除去し次のようなSQLを実行します。

実行されるSQL
select * from Employee

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

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

F#
let empList = 
  Db.query<Employee>
    config
    "select * from Employee where /*% if employeeId <> null */ EmployeeId > /* employeeId */99 /*% end */ and EmployeeName like 's%'"
    ["employeeId" @= 1]
F#
var empList = 
  db.query<Employee>(
    "select * from Employee where /*% if employeeId <> null */ EmployeeId > /* employeeId */99 /*% end */ and EmployeeName like 's%'",
    new { employeeId = 1 } );

この例では評価結果が偽の場合(employeeIdがnullの場合)、WHERE句の直後にANDが続くことになります。つまりANDが不要と言えます。この場合、SomaはANDを除去し次のようなSQLを実行します。

実行される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 */ ...)

elifとelseを利用した例

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

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

以下に例を示します。

F#
let empList = 
  Db.query<Employee>
    config
    @"select 
        * 
     from
        Employee 
     where 
     /*% if employeeId <> null */
       EmployeeId = /* employeeId */9999
     /*% elif departmentId <> null */ 
       and
       DepartmentId = /* departmentId */99
     /*% else */
       and
       DepartmentId is null
     /*% end */"
    ["employeeId" @= 1; "departmentId" @= 2]
C#
var empList = 
  db.query<Employee>(
    @"select 
        * 
     from
        Employee 
     where 
     /*% if employeeId <> null */
       EmployeeId = /* employeeId */9999
     /*% elif departmentId <> null */ 
       and
       DepartmentId = /* departmentId */99
     /*% else */
       and
       DepartmentId is null
     /*% end */",
    new { employeeId = 1, departmentId = 2 } );

employeeId <> null が成立する場合、次のSQLが実行されます。

実行されるSQL
select 
  * 
from
  Employee 
where 
  EmployeeId = @p0

employeeId <> null が成立せず、department_id <> null が成立する場合、次のSQLが実行されます。DepartmentIdの直前のANDは自動で除去されるため出力されません。

実行されるSQL
select 
  * 
from
  Employee 
where 
  DepartmentId = @p0

employeeId <> null もdepartment_id <> null も成立しない場合、次のSQLが実行されます。DepartmentIdの直前のANDは自動で除去されるため出力されません。

実行されるSQL
select 
  * 
from
  Employee 
where 
  DepartmentId is null

繰り返しコメント

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

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

識別子は、繰り返される要素を指す変数です。 式は、System.Collections.IEnumerable型として評価される式でなければいけません。
以下に例を示します。

F#
let deptList = 
  Db.query<Employee>
    config
    @"select * from Employee where
     /*% for name in nameList */
     EmployeeName = /* name */'hoge'
     /*%if name_has_next */
     /*# 'or' */
     /*% end */
     /*% end */"
    ["nameList" @= ["KING"; "SMITH"; "JOHNE"]]
C#
var deptList = 
  db.query<Employee>(
    @"select * from Employee where
     /*% for name in nameList */
     EmployeeName = /* name */'hoge'
     /*%if name_has_next */
     /*# 'or' */
     /*% end */
     /*% end */",
    new { nameList = ["KING"; "SMITH"; "JOHNE"] } );

実行されるSQL
select * from Employee where
EmployeeName = @p0
or
EmployeeName = @p1
or
EmployeeName = @p2

バインド変数の @p0 と @p1 と @p2 には、それぞれ "KING" と "SMITH" と "JOHNE" がバインドされます。

繰り返しコメントの内側では、次の特別な変数を使用できます。
  • item_has_next : 次の繰り返し要素が存在するかどうかを示すSystem.Boolean型の値。
item は繰り返される要素を指す変数です。つまり繰り返される要素を指す変数が name の場合、この変数は name_has_next になります。

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

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

以下に例を示します。

F#
let deptList = 
  Db.query<Employee>
    config
    @"select * from Employee where
     /*% for name in nameList */
     EmployeeName = /* name */'hoge'
     /*%if name_has_next */
     /*# 'or' */
     /*% end */
     /*% end */
     or
     Salary > 1000"
    ["nameList" @= []]
C#
var deptList = 
  Db.query<Employee>(
    @"select * from Employee where
     /*% for name in nameList */
     EmployeeName = /* name */'hoge'
     /*%if name_has_next */
     /*# 'or' */
     /*% end */
     /*% end */
     or
     Salary > 1000",
    new { nameList = Enumerable.Empty<string>() } );

この例ではnamesの要素数が0の場合(繰り返しが行われない場合)、WHERE句が検索条件を持たなくなります。つまりWHERE句が不要と言えます。この場合、Somaは、WHERE句を除去して次のSQLを実行します。

select * from Employee

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

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

F#
let deptList = 
  Db.query<Employee>
    config
    @"select * from Employee where
     /*% for name in nameList */
     EmployeeName = /* name */'hoge'
     /*%if name_has_next */
     /*# 'or' */
     /*% end */
     /*% end */
     or
     Salary > 1000"
    ["nameList" @= []]
C#
var deptList = 
  db.query<Employee>(
    @"select * from Employee where
     /*% for name in nameList */
     EmployeeName = /* name */'hoge'
     /*%if name_has_next */
     /*# 'or' */
     /*% end */
     /*% end */
     or
     Salary > 1000",
    new { nameList = Enumerable.Empty<string>() } );

この例ではnamesの要素数が0の場合(繰り返しが行われない場合)、WHERE句の直後にORが続くことになります。つまりORが不要と言えます。この場合、SomaはORを除去して次のSQLを実行します。

select * from Employee where Salary > 1000

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

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

Last edited Aug 20, 2011 at 8:05 PM by toshihiro, version 29

Comments

No comments yet.