SQL Standard User Defined Types and Routines

This document describes standard support for user-defined types and routines in SQL:2003. The specifications are informal; for details, see the relevant portions of the standard. Much but not all of this is already implemented in Farrago.

User-Defined Types

In SQL:2003, it's possible to extend the type system in a number of ways:

The traditional concept of a domain is preserved from SQL92. A domain is not actually a user-defined type; it's really just an alias for a combination of a builtin type with a default value and a set of constraints. A column based on a domain can be assigned and compared with values of the underlying builtin type directly; there's no need (or even mechanism) to convert between the domain and the builtin type. Domains cannot be used to declare anything but columns; e.g. you can't declare a parameter to an SQL routine as a domain.
The simplest kind of user-defined type is a distinct type. Like a domain, a distinct type is defined in terms of a single builtin type. Unlike a domain, a distinct type is not directly assignable or comparable with the underlying builtin type or other distinct types based on it. Instead, explicit conversions are required. The canonical example for why distinct types are a good idea is the Mars Climate Orbiter, which crashed due to an error from mixing imperial and metric units without conversion. Here's a simple example of distinct type definition:
```
CREATE TYPE metric_meter AS DOUBLE;
CREATE TYPE imperial_foot AS DOUBLE;
```
More generally, a structured type is defined much like a class in an object-oriented programming language such as C++ or Java. A structured type is defined with an arbitrary number of attributes of any type (including user-defined types), and may optionally inherit from another structured type (single-inheritance only). Types may be FINAL or NOT FINAL (as with the Java final modifier) and INSTANTIABLE or NOT INSTANTIABLE (as with the Java abstract modifier). Here's the classic OO example:
```
CREATE TYPE Shape2D
NOT INSTANTIABLE
NOT FINAL;

CREATE TYPE Circle UNDER Shape2D
AS(
    radius DOUBLE DEFAULT 1
)
INSTANTIABLE
FINAL;

CREATE TYPE Rectangle UNDER Shape2D
NOT INSTANTIABLE
NOT FINAL;

CREATE TYPE Square UNDER Rectangle
AS(
    side_length DOUBLE DEFAULT 1
)
INSTANTIABLE
FINAL;

CREATE TYPE Oblong UNDER Rectangle
AS(
    width DOUBLE DEFAULT 1,
    length DOUBLE DEFAULT 2
)
INSTANTIABLE
NOT FINAL;
```
Attributes can have default values, but not constraints (meaning you can't even declare an attribute as NOT NULL). It's not even possible to use domains to work around this, since domains can only be based on builtin types. However, it is possible to use column-level constraints or validation code in a constructor instead.
```
CREATE TABLE PolkaDots(
    dot_id INT NOT NULL PRIMARY KEY,
    Circle dot NOT NULL CHECK ((dot.radius > 0) IS TRUE),
    center_x DOUBLE NOT NULL,
    center_y DOUBLE NOT NULL);
```
Note that the NOT NULL constraint on dot applies to the Circle object as a whole (not to its radius attribute, which is validated by the CHECK clause). Attempting to reference an attribute of a null type instance results in NULL (not an exception as you might expect).
Finally, an entire Java class can be used as the implementation of a user-defined type, as specified in SQL/JRT, also known as SQLJ Part 2. Java user-defined types differ significantly from non-Java structured types, so they aren't currently covered by this document.

Typed Tables

SQL:2003 allows structured types to be used in two different modes:

In the same fashion as a builtin type. For example, one might replace the three columns first_name/middle_name/last_name in a table with a single column of structured type full_name, and similarly for the parameters of user-defined routines which operate on names.
As the underlying definition of a typed table. A typed table is similar to the concept of an extent in an object-oriented database; it provides persistence and object identity for values of a user-defined type, and automatically maps the attributes of the structured-type onto the columns of the typed table. It also maps type inheritance onto extent inheritance (allowing, for example, a query against a shape table to implicitly union all circles with rectangles).

This document does not cover typed tables any further, nor does it cover locators and references; instead, the focus is on the semantics of structured types and their instances.

Routines

SQL behavior can be extended by defining various kinds of SQL-invoked routines. SQL-invoked routines may be implemented in the SQL standard stored procedure language, SQL/PSM (in which case they are called SQL routines), or in another language such as Java (in which case they are called external routines, defined in SQL/OLB, also known as SQLJ Part 1). The choice of implementation language is orthogonal to the contexts in which routines may be invoked, as listed here:

Procedures are routines invoked explicitly via the SQL CALL statement. Procedures are not associated with a particular type, and cannot be used in SQL row expressions. An SQL-invoked procedure is similar to a C++ free function with void return type.
```
CREATE PROCEDURE raise_annual_salary(IN raise_percentage DOUBLE)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
    UPDATE emps SET salary = salary + salary*raise_percentage;
END;

CALL raise_annual_salary(0.10);
```
Functions are routines which are invoked from SQL row expressions. Functions are not associated with a particular type. An SQL-invoked function is similar to a C++ free function with non-void return type.
```
CREATE FUNCTION sqr(x DOUBLE) 
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
RETURN(x*x);

SELECT sum(3.1415927*sqr(d.dot.radius)) as total_area FROM PolkaDots d;
```

Methods are routines which can be invoked from SQL row expressions and which are associated with a particular type. An SQL-invoked method is similar to a method in C++ or Java, and may be either static (type-level) or non-static (instance-level). Methods are declared as part of the type definition, but defined separately. Methods are normally invoked with a parenthesized argument list, but for niladic methods the parentheses can be dropped:


CREATE TYPE Shape2D
NOT INSTANTIABLE
NOT FINAL
INSTANCE METHOD area() RETURNS DOUBLE CONTAINS SQL;

CREATE TYPE Circle UNDER Shape2D
AS(
    radius DOUBLE DEFAULT 1
)
INSTANTIABLE
FINAL
OVERRIDING INSTANCE METHOD area() RETURNS DOUBLE CONTAINS SQL;

CREATE TYPE Rectangle UNDER Shape2D
NOT INSTANTIABLE
NOT FINAL
OVERRIDING INSTANCE METHOD area() RETURNS DOUBLE CONTAINS SQL
INSTANCE METHOD rect_width() RETURNS DOUBLE CONTAINS SQL
INSTANCE METHOD rect_length() RETURNS DOUBLE CONTAINS SQL;

CREATE TYPE Square UNDER Rectangle
AS(
    side_length DOUBLE DEFAULT 1
)
INSTANTIABLE
FINAL
OVERRIDING INSTANCE METHOD rect_width() RETURNS DOUBLE CONTAINS SQL;
OVERRIDING INSTANCE METHOD rect_length() RETURNS DOUBLE CONTAINS SQL;

CREATE TYPE Oblong UNDER Rectangle
AS(
    width DOUBLE DEFAULT 1,
    length DOUBLE DEFAULT 2
)
INSTANTIABLE
NOT FINAL
OVERRIDING INSTANCE METHOD rect_width() RETURNS DOUBLE CONTAINS SQL
OVERRIDING INSTANCE METHOD rect_length() RETURNS DOUBLE CONTAINS SQL;

CREATE INSTANCE METHOD area()
RETURNS DOUBLE
FOR Circle
RETURN 3.1415927*sqr(radius);

CREATE INSTANCE METHOD area()
RETURNS DOUBLE
FOR Rectangle
RETURN rect_width*rect_length;

CREATE INSTANCE METHOD rect_width()
RETURNS DOUBLE
FOR Oblong
RETURN width;

CREATE INSTANCE METHOD rect_length()
RETURNS DOUBLE
FOR Oblong
RETURN length;

CREATE INSTANCE METHOD rect_width()
RETURNS DOUBLE
FOR Square
RETURN side_length;

CREATE INSTANCE METHOD rect_length()
RETURNS DOUBLE
FOR Square
RETURN side_length;

SELECT d.dot.area FROM PolkaDots d;

SELECT d.dot.area() FROM PolkaDots d;

Constructor functions are special niladic functions which create new instances of types. By default, the system defines a constructor function for each type which sets all attributes to the default value; this definition cannot be overridden. Constructor functions may not be invoked except via the NEW expression:
```
INSERT INTO PolkaDots VALUES(1,NEW CIRCLE(),0,0);
```

Constructor methods are special methods which initialize a new instance of a type. Constructor methods are also invoked via the NEW expression, which (when given arguments) combines the system-defined constructor function with the appropriate user-defined constructor method:


CREATE TYPE Circle UNDER Shape2D
AS(
    radius DOUBLE DEFAULT 1
)
INSTANTIABLE
FINAL
CONSTRUCTOR METHOD Circle(radius DOUBLE) RETURNS Circle 
SELF AS RESULT
CONTAINS SQL;

CREATE CONSTRUCTOR METHOD Circle(radius DOUBLE)
FOR Circle
RETURNS Circle
BEGIN
    SET SELF.radius = radius;
    RETURN SELF;
END;

INSERT INTO PolkaDots VALUES(2,NEW CIRCLE(20.5),10,100);

(Note that the SQL SELF keyword serves the same purpose as the this keyword in Java.)

Observers are special niladic methods which read an attribute of a type (where the name of the attribute is the same as the name of the observer method). The system defines observers for all attributes automatically, and they cannot be overridden.
```
SELECT d.dot.radius() FROM PolkaDots d;

SELECT d.dot.radius FROM PolkaDots d;
```
Mutators are special monadic methods which instantiate a copy of an existing structured type instance and modify one of the copy's attributes (where the name of the attribute is the same as the name of the mutator method). As for observers, the system defines mutators for all attributes automatically, and they cannot be overridden. Mutator semantics are certainly confusing. The example below returns a result set of Circle objects equivalent to the dots in the PolkaDots table with doubled radii. Despite appearances, it does not directly return the radii themselves:
```
SELECT d.dot.radius(d.dot.radius*2) FROM PolkaDots d;
```
Traditional attribute lvalue semantics are also supported for updates:
```
UPDATE PolkaDots SET dot.radius = dot.radius*2;
```
This is equivalent to:
```
UPDATE PolkaDots SET dot = dot.radius(dot.radius*2);
```

Routine Modifiers

A number of modifiers can be applied to routines when they are defined:

LANGUAGE: this specifies the implementation language. The examples above all used SQL, but later we'll see some examples which specify JAVA instead.
PARAMETER STYLE: this can be one of {SQL | GENERAL | JAVA} and controls how routine parameter lists are mapped into other languages for external routines.
SPECIFIC name: an additional name by which an overloaded routine is known in the catalog. This allows a routine to be referenced by specific name without having to include its full signature.
[NOT] DETERMINISTIC: specifies whether the routine always produces the same result given the same inputs and persistent state.
[NOT] DYNAMIC_FUNCTION: specifies whether it is safe to cache query plans referring to the routine; default is false (NOT DYNAMIC_FUNCTION), indicating that plan caching is safe. An example of a DYNAMIC_FUNCTION would be an external value lookup routine (similar to the CURRENT_DATE expression) which should be re-evaluated for each query execution, but not for each row. Note that this is a Farrago-specific extension (not defined by SQL:2003).
{NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA }: constrains the usage of SQL within the implementation of the routine.
DYNAMIC RESULT SETS: for procedures, whether result sets are returned when the procedure is invoked. Procedural result sets are not covered in this document.
[ RETURNS NULL | CALLED ] ON NULL INPUT: for functions, whether the routine is actually called if one of its arguments is NULL. RETURNS NULL forces the executor to short-circuit the function invocation, supplying NULL for its result.

Routine Parameters

Most routine parameters are input-only. However, procedures allow parameters to be specified as one of

{ IN | OUT | INOUT
}

. Parameter names are optional (but still recommended) when an external routine is specified, since external routine lookup is by type signature. Parameters do not take default values.

Casting Around a Type Hierarchy

The equivalent of the Java instanceof keyword is the IS [NOT] OF syntax:


SELECT p.name
FROM ParkingLots p
WHERE p.geometry IS OF (Square,Circle);

Casting is sometimes required to get the right version of a method due to overloading or overriding. The normal SQL CAST keyword is not used for this purpose. Instead, the syntax for downcasting uses the TREAT keyword:


SELECT TREAT(p.geometry AS Square).side_length
FROM ParkingLots p
WHERE p.geometry IS OF (Square);

For upcasting, the TREAT keyword is omitted:


SELECT generate_label(d.dot AS Shape)
FROM PolkaDots p;

If the generate_label function were overloaded with different versions for Circles and for generic Shapes, then the query above will invoke the generic Shape version. For the gory details on routine resolution, consult the standard.

Besides explicit type hierarchy casting, SQL:2003 also provides for

User-defined casts via the CREATE CAST statement. These are relevant for structured types; for distinct types, cast functions to and from builtin types can be referenced as part of the type definition.
User-defined comparisons via the CREATE ORDERING statement.
User-defined transformation to and from representations which can be used in non-SQL programming languages. These are defined via the CREATE TRANSFORM statement, which is unnecessary for Java; JDBC already defines the SQLData interface for this purpose.
Implicit casting on the return value of functions via the CAST FROM clause (mostly useful with external routines where the routine wasn't defined with SQL in mind).
Type-preserving functions via the RESULT clause; I haven't decoded this part of the standard yet.

Bookkeeping

After a type has been defined, it can be maintained with the ALTER TYPE statement, which allows for

add attribute
add method
drop attribute
drop method

Types can be dropped, but only with RESTRICT semantics (no CASCADE).

Procedures, functions, and types may be defined in any schema, but methods must be defined in the same schema as the associated type.

SQL:2003 introduces the notion of a SQL-path, which is used to resolve unqualified references to procedures, functions, and types (similar to the way the PATH environment variable is used to resolve executable names). Each schema has a SQL-path associated with it; objects defined within that schema use that path implicitly. Sessions also have their own SQL-paths used for dynamic SQL.

As with table references in views, unqualified routine references are "frozen" at the time the invoking code is validated so that subsequent path changes do not affect them. However, the situation is a little more complicated in the case of late binding; instead of freezing on a single routine, the system freezes a candidate set with final matching performed at execution time. In the examples above, an invocation of shape.area() does not know until execution whether a particular shape is a Rectangle or a Circle.

External Routines

So far all of the examples have defined SQL routines (using SQL/PSM for the procedure implementation language). For Farrago, we are also interested in using Java as a routine implementation language. SQL/OLB provides this, including the capability to embed SQL in Java. However, it does NOT allow methods to be defined in Java for normal structured types, only procedures and functions. In order to define methods in Java, you have to go all the way and use Java structured types (SQL/JRT, not covered here).

Defining a routine with a Java implementation is a multi-step process:

Write the routine and compile it as a Java class (possibly using a SQLJ precompiler to translate any embedded SQL statements).
```
class MathFuncs {
    public static double sqr(double d)
    {
        return d*d;
    }
}
```
Package the compiled Java class into a JAR file.
From SQL, CALL the system-defined SQLJ.INSTALL_JAR routine to install the JAR into the DBMS. (The last parameter, which controls whether deployment descriptors are used, is currently ignored since these aren't supported yet.)
```
CALL SQLJ.INSTALL_JAR('file:/home/jvs/MathFuncs.jar','MathFuncsJavaLib', 0);
```

Issue a CREATE {FUNCTION|PROCEDURE} statement referencing the routine's Java implementation in the installed JAR.


CREATE FUNCTION sqr(d DOUBLE)
RETURNS DOUBLE
NO SQL
EXTERNAL NAME 'MathFuncsLib:MathFuncs.sqr'
LANGUAGE JAVA
PARAMETER STYLE JAVA;

Unfortunately, there's no way to combine all of this into a single DDL statement with the routine body inline. We may want to consider an extension for this, e.g.


CREATE FUNCTION sqr(d DOUBLE)
RETURNS DOUBLE
NO SQL
INLINE
LANGUAGE JAVA
PARAMETER STYLE JAVA
    {
        return d*d;
    }
;

The schema named SQLJ is a special schema maintained by the system (just like INFORMATION_SCHEMA). It contains other procedures for JAR maintenance (

REPLACE_JAR, REMOVE_JAR,
 ALTER_JAVA_PATH

). Besides Java classes, JAR files can also contain deployment descriptors such as the DDL for creating the SQL routines and granting access to them. In a way this is the inverse of the inline routine definition mentioned above: instead of defining everything in SQL, define everything in the JAR, and then issue a single CALL from SQL to deploy it.

Access Control

SQL:2003 types have no concept of access control such as the public/private/protected of Java and C++. However, the standard (and more powerful) GRANT/REVOKE mechanism can be used at the method level instead.

When external routines execute SQL statements, the authorization with which they run can be controlled via the EXTERNAL SECURITY clause on the routine definition:

EXTERNAL SECURITY DEFINER: the routine executes with the privileges of the user who created the routine.
EXTERNAL SECURITY INVOKER: the routine executes with the privileges of the user who invoked the routine.
EXTERNAL SECURITY IMPLEMENTATION DEFINED: the system decides (may be any user at all!).

SQL routines always run with INVOKER privileges.

User Defined Aggregates

SQL:2003 does not provide a mechanism for user-defined aggregates. See the Oracle, DB2, and PostgreSQL docs for various vendor-defined extensions in this area.