Table of Contents

Overview

In the expression comment of SQL Description, the simple expression language is available.
This document describes the grammer of the expression language.

Literals

Supported literals are followings:

Type Sample Literals
System.Object null
System.Boolean true, false
System.Byte 0uy, 19uy
System.SByte 0y, 19y
System.Int16 0s, 19s
System.UInt16 0us, 19us
System.Int32 0, 19
System.UInt32 0u, 19u
System.Int64 0L, 19L
System.UInt64 0UL, 19UL
System.Single 0.0f, 19.7f
System.Double 0.0, 19.7
System.Decimal 0M, 19M, 19.03M
Math.BigInt 0I, 19I
System.String 'abc'


example : null and System.Int32
select * from Employee where 
/*% if employeeName <> null && employeeName.Lenght > 5 */
    EmployeeName = /* employeeName */'smith'
/*% end */

Comparison Operators

Supported comparison operators are followings:

Operator Description
= equals
== equals
<> not equal
!= not equal
< less than
<= less than or equals
> greater than
>= greater than or equals


example : greater than
select * from Employee where 
/*%if employeeName.Length > 0 */
    EmployeeName = /* employeeName */'smith'
/*%end */

Comparison operators is not available, unless they are supported by operand types.

Boolean Operators

Supported boolean operators are followings:

Operator Description
not Boolean negation
! Boolean negation
&& Boolean AND
|| Boolean OR


example : Boolean AND and Boolean OR
select * from Employee where 
/*%if (departmentId = null || managerId = null) && employee_name <> null */
    EmployeeName = /* employeeName */'smith'
/*%end */

Operator Precedence are controllable with parentheses.

Types

In the expression, the value has its type. Binary operators are only applicable to same type operands.

Option Types

The value of F# option type is comparable to the null value. If the option value is equal to the null value, the value is None, otherwise Some.

select * from Employee where 
/*%if employeeId <> null */
    EmployeeId = /* employeeId */0
/*%end */
In this example, type of 'employeeId' is option.

Nullable Types

The value of nullable type is comparable to the null value. If the nullable value is equal to the null value, the value is null, otherwise not null.

select * from Employee where 
/*%if employeeId <> null */
    EmployeeId = /* employeeId */0
/*%end */
In this example, type of 'employeeId' is nullable.

Property Access

In this document, the word property means both CRL propperty and CRL field.
The properties are classified by static and non-static.
They are case-insensitive and must be public.
Only read access is allowed, write access is not allowed.
Indexer access is not allowed except for System.Collections.IDictionary and System.Collections.Generic.IDictionary<TKey, TValue>. Property access to them are converted indexer access.

Non-static Property Access

The property name follows dot(.).
Below example shows access to the EmployeeName property of the Employee instance.

F#
let emp = { Employee.EmployeeId = 0; EmployeeName = "Hoge"; DepartmentId = 2; VersionNo = 0}
let empList = Db.query<Employee> config "select * from Employee where EmployeeName = /* emp.EmployeeName */'smith'" ["emp" @= emp]

Below example shows access to the indexer property of the IDictionary<String, Object> instance.

F#
let emp = new Dictionary<string, obj>()
emp.["EmployeeName"] <- "Hoge"
let empList = Db.query<Employee> config "select * from Employee where EmployeeName = /* emp.EmployeeName */'smith'" ["emp" @= emp]

Static Property Access

Type name must be enclosed by $. The property name follows dot(.).
Below example shows access to the System.DateTime.Now property.

update Employee set EmployeeName = /* employeeName */, ModifiedTime = /* $System.DateTime$.Now */ where = /* employeeId */1

Built-in Functions

Built-in functions are uitilities for the evaluation and the conversion. Supported functions are followings:

F# like Signature Description
val isNullOrEmpty : System.Object -> System.Boolean Indicates whether the argument is a null reference or an Empty string. If the argument type is Option<String>, the element value is evaluated.
val isNullOrWhiteSpace : System.Object -> System.Boolean Indicates whether the argument is a null reference, empty, or consists only of white-space characters. When the argument type is Option<String>, the element value is evaluated.
val date : System.Object -> System.Nullable<System.DateTime> Gets the date from the argument. In ohter words, information about time are removed. If the argument type is Option<DateTime> or Nullable<DateTime>, the element value is evaluated.
val nextDate : System.Object -> System.Nullable<System.DateTime> Gets the next date from the argument. In ohter words, information about time are removed. If the argument type is Option<DateTime> or Nullable<DateTime>, the element value is evaluated.
val prevDate : System.Object -> System.Nullable<System.DateTime> Gets the previous date from the argument. In ohter words, information about time are removed. If the argument type is Option<DateTime> or Nullable<DateTime>, the element value is evaluated.
val escape : System.Object -> System.String Escapes wildcard characters such as '%', '_' and so on dependent on RDBMS. If the argument is null, returns null. If the argument type is Option<String>, the element value is evaluated.
val startsWith : System.Object -> System.String Converts the argument to the string for prefix search and escapes wildcard characters such as '%', '_' and so on dependent on RDBMS. If the argument is null, returns null. If the argument type is Option<String>, the element value is evaluated.
val contains : System.Object -> System.String Converts the argument to the string for partial match and escapes wildcard characters such as '%', '_' and so on dependent on RDBMS. If the argument is null, returns null. If the argument type is Option<String>, the element value is evaluated.
val endsWith : System.Object -> System.String Converts the argument to the string for sufix search and escapes wildcard characters such as '%', '_' and so on dependent on RDBMS. If the argument is null, returns null. If the argument type is Option<String>, the element value is evaluated.
val charString : System.Object -> Soma.Core.CharString Converts the argument  to Soma.Core.CharString. If the argument is null, returns null. If the argument type is Option<String>, the element value is evaluated.
val charStringList : System.Object -> System.Collections.IEnumerable<Soma.Core.CharString> Converts the argument  to IEnumerable of Soma.Core.CharString. If the argument is null, returns empty IEnumerable.



There are synonyms for built-in functions.

C# like Syntax Description
System.Boolean @IsNullOrEmpty(System.Object text) synonym for isNullOrEmpty
System.Boolean @isNullOrWhiteSpace(System.Object text) synonym for isNullOrEmpty
System.Nullable<System.DateTime> @Date(System.Object date) synonym for date
System.Nullable<System.DateTime> @NextDate(System.Object date) synonym for nextDate
System.Nullable<System.DateTime> @PrevDate(System.Object date) synonym for prevDate
System.String @Escape(System.Object text) synonym for escape
System.String @StartsWith(System.Object text) synonym for startsWith
System.String @Contains(System.Object text) synonym for contains
System.String @EndsWith(System.Object text) synonym for endsWith
System.String @CharString(System.Object text) synonym for charString
System.String @CharStringList(System.Object textList) synonym for charStringList

isNullOrEmpty

select * from Employee where 
/*%if not (isNullOrEmpty employeeName) */
    EmployeeName = /* employeeName */'smith'
/*%end */

Above example and below example are semantically same.

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

@IsNullOrEmpty is the synonym for isNullOrEmpty.

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

isNullOrWhiteSpace

select * from Employee where 
/*%if not (isNullOrWhiteSpace employeeName) */
    EmployeeName = /* employeeName */'smith'
/*%end */

@IsNullOrWhiteSpace is the synonym for isNullOrWhiteSpace.

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

date

This function is useful to remove time information.

select * from Employee where Hiredate > /* date hiredate */'2001-01-01'

@Date is the synonym for date.

select * from Employee where Hiredate > /* @Date(hiredate) */'2001-01-01'

nextDate

This function is useful to remove time information.

select * from Employee where Hiredate > /* nextDate hiredate */'2001-01-01'

@NextDate is the synonym for nextDate.

select * from Employee where Hiredate > /* @NextDate(hiredate) */'2001-01-01'

prevDate

This function is useful to to remove time information.

select * from Employee where Hiredate >= /* prevDate hiredate */'2001-01-01' and Hiredate < /* nextDate hiredate */'2001-01-01'

@PrevDate is the synonym for prevDate.

select * from Employee where Hiredate >= /* @PrevDate(hiredate) */'2001-01-01' and Hiredate < /* @NextDate(hiredate) */'2001-01-01'

escape

If the value of employeeName is "AB%C", it converted to "AB$%C" by the escape function. Since the escape character is '$', the string "escape '$'" is required in the SQL.

select * from Employee where EmployeeName like /* escape employeeName */'smith' escape '$'

@Escape is the synonym for escape.

select * from Employee where EmployeeName like /* @Escape(employeeName) */'smith' escape '$'

startsWith

If the value of employeeName is "ABC", it converted to "ABC%" by the startsWith function. If the value of employeeName is "AB%C", it converted to "AB$%C%". Since the escape character is '$', the string "escape '$'" is required in the SQL.

select * from Employee where EmployeeName like /* startsWith employeeName */'smith' escape '$'

@StartsWith is the synonym for startsWith.

select * from Employee where EmployeeName like /* @StartsWith(employeeName) */'smith' escape '$'

contains

If the value of employeeName is "ABC", it converted to "%ABC%" by the contains function. If the value of employeeName is "AB%C", it converted to "%AB$%C%". Since the escape character is '$', the string "escape '$'" is required in the SQL.

select * from Employee where EmployeeName like /* contains employeeName */'smith' escape '$'

@Contains is the synonym for contains.

select * from Employee where EmployeeName like /* @Contains(employeeName) */'smith' escape '$'

endsWith

If the value of employeeName is "ABC", it converted to "%ABC" by the endsWith function. If the value of employeeName is "AB%C", it converted to "%AB$%C". Since the escape character is '$', the string "escape '$'" is required in the SQL.

select * from Employee where EmployeeName like /* endsWith employeeName */'smith' escape '$'

@EndsWith is the synonym for endsWith.

select * from Employee where EmployeeName like /* @EndsWith(employeeName) */'smith' escape '$'

Last edited Jun 23, 2012 at 7:18 AM by toshihiro, version 16

Comments

No comments yet.