Extension Examples

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, '(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, '(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"
}

Exporting and Importing Data with Custom Types

VillageSQL supports SELECT INTO OUTFILE and LOAD DATA INFILE for custom types, allowing you to export and import data while preserving custom type values.

SELECT INTO OUTFILE

Custom types serialize to their string representation when exported:

INSTALL EXTENSION vsql_complex;

-- Create table with custom type
CREATE TABLE signals (
    id INT PRIMARY KEY,
    reading COMPLEX
);

INSERT INTO signals VALUES
    (1, '(3.0,4.0)'),
    (2, '(5.0,12.0)'),
    (3, '(-1.0,2.0)');

-- Export to file
SELECT * FROM signals INTO OUTFILE '/tmp/signals_export.txt';

File contents (/tmp/signals_export.txt):

(3.00,4.00)
(5.00,12.00)
(-1.00,2.00)

LOAD DATA INFILE

Load the exported data back into a table:

-- Create new table with same schema
CREATE TABLE signals_imported (
    id INT PRIMARY KEY,
    reading COMPLEX
);

-- Import data
LOAD DATA INFILE '/tmp/signals_export.txt' INTO TABLE signals_imported;

-- Verify
SELECT * FROM signals_imported;

Export with Custom Delimiters

You can use custom field and line terminators:

SELECT * FROM signals
INTO OUTFILE '/tmp/signals_csv.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Output:

"1","(3.00,4.00)"
"2","(5.00,12.00)"
"3","(-1.00,2.00)"

Export with VDF Functions

Export computed values using extension functions:

SELECT
    id,
    reading,
    complex_abs(reading) AS magnitude,
    complex_real(reading) AS real_part,
    complex_imag(reading) AS imag_part
INTO OUTFILE '/tmp/signals_computed.txt'
FROM signals;

Custom types are exported in their string representation format. Binary export with SELECT INTO DUMPFILE is not currently supported for custom types.

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

Getting Started

Extensions

Using Extensions

Creating Extensions

What vsql_complex Provides

Directory Structure

VEF Registration Pattern

Binary Storage Format

Wrapper Functions

Testing Strategy

Key Implementation Patterns

Manifest

Exporting and Importing Data with Custom Types

SELECT INTO OUTFILE

LOAD DATA INFILE

Export with Custom Delimiters

Export with VDF Functions

Next Steps

Create Extension

Extension Architecture

vsql_complex Source

Available Extensions

​What vsql_complex Provides

​Directory Structure

​VEF Registration Pattern

​Binary Storage Format

​Wrapper Functions

​Testing Strategy

​Key Implementation Patterns

​Manifest

​Exporting and Importing Data with Custom Types

​SELECT INTO OUTFILE

​LOAD DATA INFILE

​Export with Custom Delimiters

​Export with VDF Functions

​Next Steps

Create Extension

Extension Architecture

vsql_complex Source

Available Extensions

What vsql_complex Provides

Directory Structure

VEF Registration Pattern

Binary Storage Format

Wrapper Functions

Testing Strategy

Key Implementation Patterns

Manifest

Exporting and Importing Data with Custom Types

SELECT INTO OUTFILE

LOAD DATA INFILE

Export with Custom Delimiters

Export with VDF Functions

Next Steps