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, '(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/vsql.h>

// Type name constants as char arrays — required as non-type template parameters
// (NTTPs) so that make_type can auto-generate VDF names like "COMPLEX::from_string".
static constexpr const char kComplexTypeName[] = "COMPLEX";
static constexpr const char kComplex2TypeName[] = "COMPLEX2";

// Type objects: encode/decode/compare VDFs are embedded via template parameters.
// No separate .func() registration is needed for type operations.
constexpr auto COMPLEX =
    vsql::make_type<kComplexTypeName>()
        .persisted_length(kComplexSize)
        .max_decode_buffer_length(64)
        .from_string<&complex_from_string>()
        .to_string<&complex_to_string>()
        .compare<&complex_compare>()
        .intrinsic_default_str("(0,0)")
        .build();

constexpr auto COMPLEX2 =
    vsql::make_type<kComplex2TypeName>()
        .persisted_length(kComplexSize)
        .max_decode_buffer_length(64)
        .from_string<&complex2_from_string>()
        .to_string<&complex_to_string>()
        .compare<&complex_compare>()
        .hash<&complex2_hash>()
        .intrinsic_default_str("(0,0)")
        .build();

using namespace vsql;

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .type(COMPLEX)
        .type(COMPLEX2)
        // Arithmetic operations
        .func(make_func<&complex_add_impl>("complex_add")
                  .returns(COMPLEX)
                  .param(COMPLEX)
                  .param(COMPLEX)
                  .deterministic()
                  .build())
        .func(make_func<&complex_subtract_impl>("complex_subtract")
                  .returns(COMPLEX)
                  .param(COMPLEX)
                  .param(COMPLEX)
                  .deterministic()
                  .build())
        .func(make_func<&complex_multiply_impl>("complex_multiply")
                  .returns(COMPLEX)
                  .param(COMPLEX)
                  .param(COMPLEX)
                  .deterministic()
                  .build())
        .func(make_func<&complex_divide_impl>("complex_divide")
                  .returns(COMPLEX)
                  .param(COMPLEX)
                  .param(COMPLEX)
                  .deterministic()
                  .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())
        // Aggregate functions
        .func(make_aggregate_func<ComplexSumState, &complex_sum_result>(
                  "complex_sum")
                  .returns(COMPLEX)
                  .param(COMPLEX)
                  .clear<&complex_sum_clear>()
                  .accumulate<&complex_sum_accumulate>()
                  .build()))

Key patterns:

Single macro call registers everything — no manual SQL needed
Type objects are constexpr variables built with vsql::make_type<kName>(), where kName is a static constexpr const char[] used as a non-type template parameter
Type operations (.from_string<>(), .to_string<>(), .compare<>(), .hash<>()) are embedded in the type object — no separate .func() calls needed for them
.compare() enables ORDER BY and indexing; .hash() is optional
.intrinsic_default_str("(0,0)") sets the default value written when INSERT IGNORE or UPDATE IGNORE assigns NULL to a NOT NULL column of this type
Function registration uses make_func<&impl>("name") with .build()
Aggregate registration uses make_aggregate_func<State, &result_fn>("name") with .clear<&fn>() and .accumulate<&fn>(); the result function has signature void(const State&, ResultWrapper)

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 provides typed argument and result wrappers, so implementations work with C++ types instead of raw protocol structs: Arithmetic Example (from complex.cc):

void complex_add_impl(CustomArg in_l, CustomArg in_r, CustomResult out) {
  // Handle NULL inputs and validate arguments
  // ...
  store_complex(out.buffer().data(), Complex{lhs.re + rhs.re, lhs.im + rhs.im});
  out.set_length(kComplexSize);
}

Utility Function Wrappers (from complex.cc):

void complex_real_impl(CustomArg in, RealResult out) {
  // Handle NULL and validate
  // ...
  out.set(cx.re);
}

void complex_abs_impl(CustomArg in, RealResult out) {
  // Handle NULL and validate
  // ...
  out.set(sqrt(cx.re * cx.re + cx.im * cx.im));
}

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	Call `in.is_null()` on the typed argument wrapper
Error handling	Call `out.error("message")` on the result wrapper
Result output	Call `out.set(value)` / `out.set_length(n)` on the result wrapper

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

​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