22.9 SQL Library: The SQL.Op structure

The SQL.Op structure provides SQL’s infix operators, aggregate functions, and other utilities, all of which are used for constructing SQL value expressions. In SML#’s SQL value expressions, the infixity of some identifiers are declared as follows:

infix 7 %
infix 5 like ||
nonfix mod

In an SQL value expression that is not evaluated by SML#, SQL functions and operators defined in this structure can be used without any structure prefix.

Almost all of functions defined in SQL.Op is overloaded on multiple SQL basic types (see Section 22.1). In the description, the overloaded type variables and their ranges are indicated by their names as follows:

•

’sql is one of the SQL basic types or their option types.
•

’sqlopt is the option type of an SQL basic type.
•

’num is one of the SQL basic numeric types or their option types.
•

’str is either string or string option.
•

Other type variables range over the set of all types.

The signature of the SQL.Op structure is the following:

structure SQL : sig
   $\cdots$
  structure Op : sig
    val Some : ’a -> ’a option
    val Part : ’a option list -> ’a list
    val Num : ’num -> numeric option
    val + : ’num * ’num -> ’num
    val - : ’num * ’num -> ’num
    val * : ’num * ’num -> ’num
    val / : ’num * ’num -> ’num
    val mod : ’num * ’num -> ’num
    val ~: ’num -> ’num
    val abs : ’num -> ’num
    val < : ’sql * ’sql -> bool3
    val > : ’sql * ’sql -> bool3
    val <= : ’sql * ’sql -> bool3
    val >= : ’sql * ’sql -> bool3
    val = : ’sql * ’sql -> bool3
    val <> : ’sql * ’sql -> bool3
    val || : ’str * ’str -> ’str
    val like : ’str * ’str -> bool3
    val nullif : ’sqlopt * ’sqlopt -> ’sqlopt
    val coalesce : ’b option * ’b -> ’b
    val coalesce’ : ’b option * ’b option -> ’b option
    val count : ’sql list -> int
    val avg : ’num list -> numeric option
    val sum : ’num list -> ’num option
    val sum’ : ’num option list -> ’num option
    val min : ’sql list -> ’sql option
    val min’ : ’sql option list -> ’sql option
    val max : ’sql list -> ’sql option
    val max’ : ’sql option list -> ’sql option
  end
end

Each of the following subsections describes the definitions for each category of their purposes.

22.9.1 Workarounds for type inconsistencies

SML# provides the following functions corresponding to implicit type cast. See Subsection 22.4.5 for details.

•

val Some : ’a -> ’a option
•

val Part : ’a option list -> ’a list
•

val Num : ’num -> numeric option

22.9.2 SQL operators and functions

The SQL.Op structure provides the following operators. These operators return an SQL value expression that concatinates given expressions with the operator. The comparison is not performed until the constructed query is executed on a database server.

•

Comparison operators: <, >, <=, >=, =, <> are provided for any SQL basic types. The type of these operators is

’sql * ’sql -> bool3
•

Arithmetic operators: Five infix operators +, -, *, /, % and two unary operators ~, abs are provided for any SQL numeric types. In the SQL value expression, % is declared as a infix identifier. The type of these operators is either

’num * ’num -> ’num

or

’num -> ’num
•

Modulo operation: Following the standard SQL, the modulo operator mod is also provided as a function. Note that some database engines supports only one of mod and %. In the SQL value expression, mod is declared as a nonfix identifier. The type of mod is

’num * ’num -> ’num
•

String operators: The pattern match operator like and string concatination operator || are available. Both identifiers are infix operators in SQL value expressions. Their types are the following:

val like : ’str * ’str -> bool3
val || : ’str * ’str -> ’str
•

NULLIF: the nullif function of the following type is provided:

val nullif : ’sqlopt * ’sqlopt -> ’sqlopt

Note that the two arguments must be an option type. Use Some if needed.
•

COALESCE: Two variants coalesce and coalesce’ are provided because of the option type.

val coalesce : ’b option * ’b -> ’b
val coalesce’ : ’b option * ’b option -> ’b option

In the SQL query sent to a server, both functions have the same name COALESCE. The type of coalesce is chosen for a paticular use of COALESCE that substitutes NULL value with non-NULL values. Different from the standard SQL, COALESCE with more than two arguments is not supported. Nest coalesce’ functions for more than two values.

22.9.3 SQL aggregation functions

count, avg, sum, min, and max are available. Because of the option type, sum, min, and max function has two variants such as sum and sum’. The name sent to a server is same regardless of the variant chosen.

The type of these functions are the following:

val count : ’sql list -> int
val avg : ’num list -> numeric option
val sum : ’num list -> ’num option
val sum’ : ’num option list -> ’num option
val min : ’sql list -> ’sql option
val min’ : ’sql option list -> ’sql option
val max : ’sql list -> ’sql option
val max’ : ’sql option list -> ’sql option