Add custom SQL operators to MemCP

Add custom SQL operators to MemCP

Applications can expose their own Scheme functions as scalar SQL functions. The SQL parsers look up function names in the shared sql_builtins registry and place the registered Scheme procedure directly into the generated query plan.

Register a Scheme function for SQL

The smallest example adds one to its argument:

<syntaxhighlight lang="scheme"> (sql_builtins "ADD_1" (lambda (x) (+ x 1))) </syntaxhighlight>

After the module containing this declaration has been imported, the function is available in SQL:

<syntaxhighlight lang="sql"> SELECT ADD_1(4) AS result; -- result: 5 </syntaxhighlight>

SQL function names are normalized to upper case before lookup, so registry keys should conventionally be uppercase. The function may still be written as add_1(...), Add_1(...), or ADD_1(...) in SQL.

The declaration must run after lib/sql-parser.scm or lib/sql-builtins.scm has created the shared registry. A custom application module loaded after lib/main.scm is a suitable place:

<syntaxhighlight lang="scheme"> /* apps/my-sql-functions.scm */ (define sql_add_1 (lambda (x) (if (nil? x) nil (+ x 1))))

(sql_builtins "ADD_1" sql_add_1) </syntaxhighlight>

Load the module when starting MemCP:

<syntaxhighlight lang="bash"> ./memcp --api-port=4321 --mysql-port=3307 \

 lib/main.scm apps/my-sql-functions.scm

</syntaxhighlight>

Then test it through the HTTP frontend:

<syntaxhighlight lang="bash"> curl --fail-with-body -u root:strong-password \

 --data-binary 'SELECT ADD_1(4), ADD_1(NULL)' \
 http://localhost:4321/sql/test

</syntaxhighlight>

Parameters, NULL, and result types

The lambda parameter list defines the accepted arity. A three-argument function is registered in exactly the same way:

<syntaxhighlight lang="scheme"> (sql_builtins "CLAMP" (lambda (value lower upper) (if (nil? value) nil (max lower (min upper value))))) </syntaxhighlight>

<syntaxhighlight lang="sql"> SELECT CLAMP(12, 0, 10); -- 10 </syntaxhighlight>

A custom function is responsible for its observable SQL behavior:

• decide whether SQL NULL propagates, produces a boolean, or has another documented meaning;
• return values that the SQL result encoder and surrounding expression understand;
• avoid hidden mutable state when the function can run inside parallel shard scans;
• do not perform I/O or database writes from a function advertised as a pure scalar expression;
• validate strings, lists, numbers, JSON values, and other dynamic Scheme inputs explicitly.

The registry stores a Scheme procedure, not a complete SQL type declaration. Consequently, type and arity mistakes are normally detected when the generated plan is optimized or executed. Add successful and must-fail tests for NULLs, wrong arity, invalid types, boundary values, and both SQL syntax frontends where the function is intended to work.

Use an existing Scheme builtin

An existing Scheme function can be exported without wrapping it:

<syntaxhighlight lang="scheme"> (sql_builtins "MY_UNIX_TIMESTAMP" unix_timestamp) </syntaxhighlight>

Aliases in lib/sql-builtins.scm use this pattern extensively. A wrapper lambda is preferable when SQL needs different NULL handling, argument order, defaults, or result normalization.

Implement the underlying builtin in Go

For hot functionality that cannot be expressed efficiently in Scheme, first declare a typed Scheme builtin in Go. The reduced pattern below follows the runtime's Declaration and TypeDescriptor API:

<syntaxhighlight lang="go"> DeclareInSection("Arithmetic / Logic", &Globalenv, &Declaration{ Name: "add_1", Desc: "adds one to an integer and propagates nil", Fn: func(args ...Scmer) Scmer { if args[0].IsNil() { return NewNil() } return NewInt(args[0].Int() + 1) }, Type: &TypeDescriptor{ Params: []*TypeDescriptor{ {Kind: "int", ParamName: "value", ParamDesc: "value to increment"}, }, Return: &TypeDescriptor{Kind: "int"}, Const: true, }, }) </syntaxhighlight>

Use DeclareInSection to place a function in an existing generated API chapter. A new chapter starts with DeclareTitle and ordinary Declare calls. A new initializer must also be called from the runtime initialization sequence; merely adding a Go function does not register it.

Set Const only when repeated calls with the same arguments are deterministic, side-effect-free, and safe to constant-fold. Describe every parameter and return value so make docs can generate a useful SCM reference. More advanced declarations can describe optional or variadic parameters, nested list/association types, ownership, optimizer hooks, and a native JIT emitter.

After the Scheme builtin exists, expose it to SQL separately:

<syntaxhighlight lang="scheme"> (sql_builtins "ADD_1" add_1) </syntaxhighlight>

This separation is intentional: declaring a Scheme builtin makes it available to Scheme and generated plans, while registering it in sql_builtins gives the SQL parser a function name.

Scalar functions versus new SQL syntax

sql_builtins is the right mechanism for ordinary calls such as ADD_1(x). Adding an infix operator, aggregate syntax, table-producing function, clause, or construct that changes relational cardinality requires parser and planner work as well. Such a change must preserve the boundary between parser AST, logical decorrelation/join ordering, and physical lowering described in Query Planner and Physical Lowering.

See Introduction to Scheme, SCM Builtins, Full SCM API documentation, and Contributing.