[UDT] Case study

MySQL User Defined Type project

[!CAUTION] STATUS: DRAFT

Case study

For illustration, assume a third party wants to implement a "complex_number", as defined in math:

• https://en.wikipedia.org/wiki/Complex_number

complex_number data type

The first question is how to represent this new type:

CREATE TYPE complex_number ...;

The SQL specification allows several options here. Database vendors also have their own syntax.

For illustration, PostgreSQL CREATE TYPE supports the following:

• Composite Types
• Enumerated Types
• Range Types
• Base Types

MySQL will support Base Types, using a subset of the official SQL syntax.

<user-defined type definition> ::=
  CREATE TYPE <schema-resolved user-defined type name>
  AS <predefined type>

Base Types

CREATE TYPE complex_number AS binary(16);

The part AS binary(16) is the storage representation for the complex_number logical type.

The real and imaginary part are stored using 8 bytes each, to store a double in x64 architectures.

Because the type is opaque, all operations must be explicitly implemented in code, in a component.

complex_number operations

Operations are exposed as native functions, provided by a component.

For example:

complex_number complex_number_from_string(VARCHAR p1);
VARCHAR complex_number_to_string(complex_number p1);

complex_number complex_number_add(complex_number p1, complex_number p2);
complex_number complex_number_sub(complex_number p1, complex_number p2);
complex_number complex_number_mul(complex_number p1, complex_number p2);
complex_number complex_number_div(complex_number p1, complex_number p2);

Currently, user defined functions (UDF) can not be used as is. Limitations are:

• UDF can not accept arbitrary types, only integers or strings.
• UDF can not return arbitrary types, only integer or strings.

Also, the SQL UDF SQL syntax is very cumbersome and error prone:

DECLARE FUNCTION foo RETURNS STRING SONAME "foo_implementation.so"

Issues with this syntax:

• the parameter number and types are not part of the function declaration
• each function must be declared in SQL, pointing to the implementation in C++ using a symbol name.

An alternative is for a component, upon initialization, to declare (using a service instead of SQL), the native function.

For user defined types, this is more appealing:

INSTALL COMPONENT "file:://component_complex_number";

will by itself register all the native functions related to the complex_number user defined type:

• complex_number_from_string()
• complex_number_to_string()
• complex_number_add()
• complex_number_sub()
• complex_number_mul()
• complex_number_div()