Table of Contents

Overview

Soma supports 2 way SQL. 2 way SQL is SQL which is runnable in two environments.
  1. SQL tools(SQL Server Management Studio etc.)
  2. execution program

2 way SQL is available because bind variables and condtional branches are expressed by a special Expression Language which described in SQL comments. The SQL comments which contain the expression language are called expression comments.
When 2 Way SQL is issued by SQL tools, expression comments are no effect. But when it issued in execution programs, expression comments are interpreted by Soma.
2 way SQL allows you to save time to convert normal SQL to the program dedicated SQL.

Bind Variable Comment

Expression comments for bind variables are called bind variable comments. Bind variables are mapped to System.Data.Common.DbParameter instances.
Bind variables are described with the block comment /*~*/.

Mapping to a Bind Variable

A test literals must be placed right after a bind variable comment. It is removed and not used in runtime. It is only used in development time to check sql sentense.

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 } );

Bind variable comments in this examples are /* min */ and /* max */. Literals 10 and 30 are test data.

issued SQL
select * from Employee where Age > @p0 and Age < @p1 
Bind variable comments are replaced with parameter strings such as @p0 and @p1, and test literals are removed.
For parameters @p0 and @p1, values 5 and 35 are bound.

Mapping to Bind Variables with IN Clause

The value of System.Collections.IEnumerable is mapped to bind variables with IN clause. Test literals must be placed right after bind variables comment. They are removed and not used in runtime. They are only used in development time to check sql sentense.

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"] });
Bind variables comment in this examples is /* nameList */. Literals 'aaa' and 'bbb' are test data.

issued SQL
select * from Employee where EmployeeName in (@p0, @p1, @p2) 
Bind variables comment is replaced with parameter strings such as @p0, @p1 and @p2. And test literals are removed.
For parameters @p0, @p1 and @p2, values "KING", "SMITH" and "JOHNE" are bound.

Embedded Variable Comment

Expression comments for embedded variables are called embedded variable comments. Values of Embedded variables are embedded into SQL directly.
Embedded variables are described with the block comment /*#~*/.
Embedded variables are not escaped automatically. Developers are responsible for avoiding SQL injection.

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" } );

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

Normal Block Comment

When the string "/*" is followed by the character '*', '+' or ':', it means the start of the normal SQL block comment. To represent the normal block comment, the form /** ~ */ is most recommended.

F#
let empList = 
  Db.query<Employee>
    config
    "/** Here is the normal block comment. */ select * from Employee"
    []
C#
var empList = 
  db.query<Employee>(
    "/** Here is the normal block comment. */ select * from Employee");

Condition Comment

Expression comments about conditon are called condition comments. Syntax is following:

/*% if condition_expression */ ~ /*% end */

The condition expression must be an expression which can be evaluated as 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 } );

issued SQL, in case the condition expression is evaluated as true
select * from Employee where EmployeeId = @p0

issued SQL, in case the condition expression is evaluated as false
select * from Employee

In case the condition expression is evaluated as false, the where clause is removed. This feature is explained next.

Auto Removal of Clauses on Condition Comment

WHERE、HAVING、GROUP BY、ORDER BY and FOR UPDATE clauses are automatically removed, when they precede a condition comment and they are not wanted as a result of condition evaluation.

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 } );

In this example, if expression evaluation is false (or employeeId is null), WHERE clause is empty. So Soma removes WHERE clause and build the following SQL.

issued SQL
select * from Employee

Auto Removal of Keywords on Condition Comment

AND and OR keywords are automatically removed, when they follow a condition comment and they are not wanted as a result of condition evaluation.

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 } );

In this example, if expression evaluation is false (or employeeId is null), AND keyword follows WHERE keyword. So Soma removes AND keyword and build the following SQL.

issued SQL
select * from Employee where EmployeeName like 's%'

Limitation of Condition Comment

The start and end of the condition comment must be included in the same clause such as SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY and FOR UPDATE.
For example, following SQL is illegal, because the 'if comment' is included in FROM clause but the corresponding 'end comment' is included in WHERE clause.

select * from Employee /*% if employeeId <> null */ where EmployeeId = /* employeeId */99 /*% end */
The start and end of the condition comment must be on the same level.
For example, following SQL is illegal, because the 'if comment' is on the outer side of parenthesis but the corresponding 'end comment' is on the inner side of parenthesis.

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

elif and else Comments

Some 'elif comment' and one 'else comment' are available between 'if comment' and 'end comment'.

/*%if condition_expression */ ~ /*%elif condition_expression */ ~ /*%else */ ~ /*%end */

Examples.

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 } );

If 'employeeId <> null' is evaluated as true, following SQL is issued.

issued SQL
select 
  * 
from
  Employee 
where 
  EmployeeId = @p0

If 'employeeId <> null' is evaluated as false and 'department_id <> null' is evaluated as true, following SQL is issued. AND keyword precedes DepartmentId is removed automatically.

issued SQL
select 
  * 
from
  Employee 
where 
  DepartmentId = @p0

If both 'employeeId <> null' and 'department_id <> null' are evaluated as false, following SQL is issued. AND keyword precedes DepartmentId is removed automatically.

issued SQL
select 
  * 
from
  Employee 
where 
  DepartmentId is null

Iteration Comment

Expression comments about iteration are called iteration comments. Syntax is following:

/*%for identifier in expression */ ~ /*%end */

The identifier is variable which indicates iterated elements. The expression must be evaluable as 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"] } );

issued SQL
select * from Employee where
EmployeeName = @p0
or
EmployeeName = @p1
or
EmployeeName = @p2

For parameters @p0, @p1 and @p2, values "KING", "SMITH" and "JOHNE" are bound.

Inside iteration comments, following special variable is available.
  • item_has_next : indicates whether next iterated element is existent or not. This varialbe must be evaluable as System.Boolean.
The item indicates iterated elements. For example, if the identifier is name, this variable is name_has_next.

Auto Removal of Clauses on Iteration Comment

WHERE、HAVING、GROUP BY、ORDER BY and FOR UPDATE clauses are automatically removed, when they precede a iteration comment and they are not wanted as a result of iteration expression evaluation.

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>() } );

In this example, if the 'nameList' expression is evaluated it has no elements, WHERE clause is empty. So Soma removes WHERE clause and build the following SQL.

issued SQL
select * from Employee

Auto Removal of Keywords on Iteration Comment

AND and OR keywords are automatically removed, when they follow a iteration comment and they are not wanted as a result of condition evaluation.

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>() } );

In this example, if the 'nameList' expression is evaluated it has no elements, OR keyword follows WHERE keyword. So Soma removes OR keyword and build the following SQL.

issued SQL
select * from Employee where Salary > 1000

Limitation of Iteration Comment

The start and end of the iteration comment must be included in the same clause such as SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY and FOR UPDATE.
And, the start and end of the iteration comment must be on the same level. For example, it is illegal that the 'for comment' is on the outer side of parenthesis and the corresponding 'end comment' is on the inner side of parenthesis.

Last edited Jul 2, 2011 at 2:02 AM by toshihiro, version 20

Comments

No comments yet.