Extension Examples - VillageSQL Server for MySQL

The vsql_complex extension is VillageSQL’s reference implementation for custom types using the VEF SDK—demonstrating production-ready extension patterns. Source: villagesql/examples/vsql_complex/ in VillageSQL repository

What vsql_complex Provides

COMPLEX type for complex numbers (a + bi) with arithmetic, utilities, and aggregation. Usage Example:

INSTALL EXTENSION vsql_complex;

CREATE TABLE signals (
    id INT PRIMARY KEY,
    impedance COMPLEX
);

INSERT INTO signals VALUES (1, COMPLEX(50.0, 10.0));

SELECT
    complex_real(impedance) as resistance,
    complex_imag(impedance) as reactance,
    complex_abs(impedance) as magnitude
FROM signals;

Directory Structure

vsql_complex/
├── CMakeLists.txt       # Build config with VEF_CREATE_VEB
├── manifest.json        # Extension metadata
├── src/
│   └── complex.cc       # Complete implementation: types, functions, and VEF registration
└── test/
    ├── t/*.test         # Test cases
    └── r/*.result       # Expected results

VEF Registration Pattern

File: src/complex.cc vsql_complex uses the VEF SDK with VEF_GENERATE_ENTRY_POINTS():

#include <villagesql/extension.h>

constexpr const char *COMPLEX = "COMPLEX";
constexpr const char *COMPLEX2 = "COMPLEX2";

VEF_GENERATE_ENTRY_POINTS(
  make_extension("vsql_complex", "0.0.1")

    // Register COMPLEX type with comparison (enables ORDER BY)
    .type(make_type(COMPLEX)
      .persisted_length(kComplexSize)
      .max_decode_buffer_length(64)
      .encode(&encode_complex)
      .decode(&decode_complex)
      .compare(&cmp_complex)
      .build())

    // Register COMPLEX2 type with custom hash
    .type(make_type(COMPLEX2)
      .persisted_length(kComplexSize)
      .max_decode_buffer_length(64)
      .encode(&encode_complex2)
      .decode(&decode_complex)
      .compare(&cmp_complex)
      .hash(&hash_complex2)
      .build())

    // Type conversion functions
    .func(make_func("complex_from_string")
      .from_string<&encode_complex>(COMPLEX))
    .func(make_func("complex_to_string")
      .to_string<&decode_complex>(COMPLEX))
    .func(make_func("complex2_from_string")
      .from_string<&encode_complex2>(COMPLEX2))
    .func(make_func("complex2_to_string")
      .to_string<&decode_complex>(COMPLEX2))

    // Arithmetic operations
    .func(make_func<&complex_add_impl>("complex_add")
      .returns(COMPLEX)
      .param(COMPLEX)
      .param(COMPLEX)
      .build())
    .func(make_func<&complex_subtract_impl>("complex_subtract")
      .returns(COMPLEX)
      .param(COMPLEX)
      .param(COMPLEX)
      .build())
    .func(make_func<&complex_multiply_impl>("complex_multiply")
      .returns(COMPLEX)
      .param(COMPLEX)
      .param(COMPLEX)
      .build())
    .func(make_func<&complex_divide_impl>("complex_divide")
      .returns(COMPLEX)
      .param(COMPLEX)
      .param(COMPLEX)
      .build())

    // Utility functions
    .func(make_func<&complex_real_impl>("complex_real")
      .returns(REAL)
      .param(COMPLEX)
      .build())
    .func(make_func<&complex_imag_impl>("complex_imag")
      .returns(REAL)
      .param(COMPLEX)
      .build())
    .func(make_func<&complex_abs_impl>("complex_abs")
      .returns(REAL)
      .param(COMPLEX)
      .build())
    .func(make_func<&complex_conjugate_impl>("complex_conjugate")
      .returns(COMPLEX)
      .param(COMPLEX)
      .build()))

Key patterns:

Single macro call registers everything - no manual SQL needed
.compare() enables ORDER BY and indexing
.hash() is optional (uses default if not provided)
Function registration uses make_func<&impl>("name") with .build()
Type conversion uses special .from_string<>() and .to_string<>() methods

Binary Storage Format

COMPLEX stores 16 bytes (little-endian):

Bytes 0-7: Real part (double)
Bytes 8-15: Imaginary part (double)

Encode/Decode (complex.cc):

bool encode_complex(unsigned char *buffer, size_t buffer_size,
                    const char *from, size_t from_len, size_t *length);
bool decode_complex(const unsigned char *buffer, size_t buffer_size,
                    char *to, size_t to_buffer_size, size_t *to_length);

Uses platform-independent byte-order functions for cross-platform compatibility.

Wrapper Functions

The VEF SDK allows clean C++ wrapper functions without raw UDF boilerplate: Arithmetic Example (from complex.cc):

void complex_add_impl(vef_context_t *ctx, vef_invalue_t *in_l,
                      vef_invalue_t *in_r, vef_vdf_result_t *out) {
  // Handle NULL inputs and validate arguments
  // ...
  ReturnComplex(Complex{lhs->re + rhs->re, lhs->im + rhs->im}, out);
}

Utility Function Wrappers (from complex.cc):

void complex_real_impl(vef_context_t *ctx, vef_invalue_t *in,
                       vef_vdf_result_t *out) {
  // Handle NULL and validate
  // ...
  out->real_value = cx->re;
  out->type = VEF_RESULT_VALUE;
}

void complex_abs_impl(vef_context_t *ctx, vef_invalue_t *in,
                      vef_vdf_result_t *out) {
  // Handle NULL and validate
  // ...
  out->real_value = sqrt(cx->re * cx->re + cx->im * cx->im);
  out->type = VEF_RESULT_VALUE;
}

Testing Strategy

Test File (test/t/complex_create.test):

INSTALL EXTENSION vsql_complex;

CREATE TABLE t1 (id INT, val COMPLEX);
INSERT INTO t1 VALUES (1, COMPLEX(1.0, 2.0));
SELECT * FROM t1;

DROP TABLE t1;
UNINSTALL EXTENSION vsql_complex;

Generate Results:

cd /path/to/villagesql/build/mysql-test
./mysql-test-run.pl --suite=vsql_complex --record

Run Tests:

./mysql-test-run.pl --suite=vsql_complex

Key Implementation Patterns

Pattern	Usage
Fixed-length storage	Set `.persisted_length()` in type builder
Platform-independent	Use custom byte-order functions for doubles
NULL handling	Check `in->is_null` in VDF implementations
Error handling	Return `VEF_RESULT_ERROR` with error message
Result output	Set `out->type` and populate result fields

Manifest

File: manifest.json

{
  "name": "vsql_complex",
  "version": "0.0.1",
  "description": "Complex number data type for VillageSQL",
  "author": "VillageSQL Contributors",
  "license": "GPL-2.0"
}

Next Steps

Create Extension

Build your own extension

Extension Architecture

Understand the internals

vsql_complex Source

View complete source code

Available Extensions

Browse extension catalog

​What vsql_complex Provides

​Directory Structure

​VEF Registration Pattern

​Binary Storage Format

​Wrapper Functions

​Testing Strategy

​Key Implementation Patterns

​Manifest

​Next Steps