Except for NULL, the following SML# types corespond to SQL basic types:

In addition to SML# basic types, the following types are defined for the interoperation with SQL:

The concrete correspondence between SML# and SQL types depend on the selection of database engines. See Section 22.8.1 for details of the type correspondence for each database engines.

SQL’s NULL corresponds to SML#’s NONE of option type. Expressions that may evaluate to NULL, such as references to columns without the NOT NULL constraint, have the option type of one of the above basic types.

SQL.bool3 is the SML# type of the SQL boolean expressions, which consists of comarison and logical operators. This SQL.bool3 type is intoduced just for the typechecking of SQL queries in SML# and thus it does not correspond to any SQL type.

SML# distinguishes the types of boolean expressions SQL.bool3 and boolean values bool. This is due to the historical confusion of the SQL standards on dealing with truth values. Traditionally, the SQL’s truth value is a 3-valued boolean consiting of true, false, and unknown, which are not first-class citizens (they cannot be stored in any table, for example). Therefore, there is no literal denoting truth values. The first-class boolean value has been introduced in SQL99 as an optional feature (feature ID T031). However, the optional feature has been criticized since this optional specification includes serious inconsistencies against the SQL core features. Consequently, almost all of RDBMS vendors have not supported the BOOLEAN type. Even after a few decades since SQL99 had shipped out, no popular RDBMS except for PostgreSQL supports the BOOLEAN type.

To avoid misunderstanding and incompatibility due to the confusion, SML# enforces the type distinction between boolean expressions and values. Thus, boolean literal true and false cannot be used as boolean expressions, and vice versa. See also Section 22.4.2 for boolean literals, and Section 22.4.6 for boolean expressions.

SML#’s record and list types are associated with the structures of SQL’s tables, views, and schema. SQL tables and views corresponds to the type of a list of a record whose field names are column names and field types are column types. If a column is not defined with the NOT NULL constraint, the type of the column in SML# is the option type of one of the basic types. For example, the structure of

CREATE TABLE foo (bar INT, baz TEXT NOT NULL);

is represented by the following SML# type:

{bar : int option, baz : string} list

As seen in the above example, the constraints that means “it is not NULL”, such as the NOT NULL and PRIMARY KEY constraints, are represented in SML# types and therefore typechecked at compile-time. Any other SQL constraints are not reflected in SML# types and are checked at run-time when an SQL query is executed in a database server.

SQL schema are represented in a record type, each field of which represents the name and structure of each table. For example, the SQL schema

CREATE TABLE employee (id INT PRIMARY KEY, name TEXT NOT NULL,
                       age INT NOT NULL, deptId INT, salary INT);
CREATE TABLE department (deptId INT PRIMARY KEY, name TEXT NOT NULL);

is represented as follows in SML#:

{
  employee : {id : int, name : string, age : int,
              deptId : int option, salary : int option} list,
  department : {deptId : int, name : string} list
}

Each category of SQL queries and their subexpressions has a different type in SML#. The following table shows the correspondence between the syntax categories of SQL and SML# types:

where $\tau$ is either a table or basic type and $w$ is the type atom that identifies a connection to a database server. The meaning of the above SML# types are the following:

• •

  ($\tau {}_{\mathrm{1}}$ -> $\tau {}_{\mathrm{2}}$, $w$) SQL.exp is the type of SQL value expressions that have type $\tau {}_2$ under the database connection $w$ and row type $\tau {}_1$.

• •

  ($\tau {}_{\mathrm{1}}$,$\tau {}_{\mathrm{2}}$,$w$) SQL.select is the type of SELECT clauses that transforms tables of type $\tau {}_1$ to those of type $\tau {}_2$ under the database connection $w$.

• •

  For any other category, ($\tau$,$w$) SQL.$X$ is the type of constructs of $X$ that have type $\tau$ under the database connection $w$.

SML# gives database connections and query results the following types:

The typical usage of these types are the following:

1. 1.

   The _sqlserver expression generates a connection information of the $\tau$ SQL.server type (see also Section 22.3).

2. 2.

   The SQL.connect function establishes a connection to the server described in the connection information of the $\tau$ SQL.server type and returns a connection handle of the $\tau$ SQL.conn type (see also Section 22.8.1).

3. 3.

   SQL queries are constructed as polymorphic functions of type [’a. ($\tau$,’a) SQL.db -> ($\tau {}^{\mathrm{\prime }}$ SQL.cursor,’a) SQL.command] (see also Section 22.1.6).

4. 4.

   The _sql syntax transforms the SQL query function to an SQL execution function of type $\tau$ SQL.conn -> $\tau {}^{\mathrm{\prime }}$ SQL.cursor (see also Section 22.7).

5. 5.

   Calling this function by giving it a connection handle of the $\tau$ SQL.conn type as its argument, the SQL query is sent to the server and evaluated on the server. If the evaluation succeeds, the function returns a cursor of the $\tau {}^{\prime }$ SQL.cursor type to access to the result (see also Section 22.7).

6. 6.

   The SQL.fetch or SQL.fetchAll function retrieves records of type $\tau {}^{\prime }$ from the cursor of the $\tau {}^{\prime }$ SQL.cursor type (see also Section 22.8.2).

In SML#, SQL expressions are typed through the correspondence between SML# types and the structures of SQL data and expressions defined above. An SQL query, which operates on tables, has a similar type to an SML# expression that does the same thing as the query for lists of records.

For example, an SQL query

SELECT t.name AS employeeName, t.age AS employeeAge
FROM employeeTable AS t
WHERE t.age > 20

is written in SML# as follows:

val Q = fn db => _sql select #t.name as employeeName, #t.age as employeeAge
                      from #db.employeeTable as t
                      where #t.age > 20

where db is the bound variable that abstracts the database instance on which the query is evaluated.

By the way, this query computes a table from the employeeTable table specified in its FROM clause by filtering its rows according to the condition written in the WHERE clause and transforming the rows by expressions in the SELECT clause. The SML# expression that performs the same thing for lists of records can be written easily as follows:

val Q’ =
  fn db => List.map
             (fn x => {employeeName = #name (#t x), employeeAge = #age (#t x)})
             (List.filter
                (fn x => #age (#t x) > 20)
                (List.map
                   (fn x => {t = x})
                   (#employeeTable db)))

The type of this Q’ is the following:

val Q’ : [’a#{employeeTable : ’b list},
          ’b#{age : int, name : ’c},
          ’c.
          ’a -> {employeeAge : ’c, employeeName : int} list]

In what follows, we refer to this function Q’ as a toy program of Q.

Through this correspondence between SQL queries and SML# expresions, SML# gives the above query Q the following type, which represents the fact that the query does the same thing for tables as the SML# expression for lists of records of the similar type:

val Q : [’a#{employeeTable : ’b list},
         ’b#{age : int, name : ’c},
         ’c::{int, ...}, ’d.
         (’a, ’d) SQL.db -> ({employeeAge : ’c, employeeName : int} list, ’d) SQL.query]

The kinded type variables occuring in the type of Q represent the following facts:

• •

  ’a represents the fact that the database on which this query runs must have at least a table named employeeTable of type ’b. Any other tables in the database does not affect the evaluation of the query.

• •

  ’b means that the employeeTable table must have at least two columns: the age column of type int and the name column of type ’c. The employeeTable table may have other columns.

• •

  ’c indicates that the name column may be of an arbitrary basic type (in fact, there is another type variable that the overload kind of ’c refers to, but it is omitted here).

• •

  ’d means that this query can be sent to a database server through an arbitrary connection handle (this type variable is used to make sure that a query is not across more than one databases).

As seen in the record-polymorphic type of Q, an SQL query is inherently polymorphic with respect to databases. SML# infers the most general polymorphic type of SQL queries with a target database abstract.

Similarly to complete SQL queries, several kinds of SQL query fragments may have the natural correspondence to SML# expressions dealing with lists and records. The typing rules introduced in this chapter are defined based on this correspondence.