Creating Custom Types - VillageSQL Server for MySQL

Custom types use VEF Protocol 3, stable as of v0.0.4. Protocol 4 is under development and is available only via opt-in dev ABI headers (-DVSQL_USE_DEV_ABI=ON). Extensions built against the old Protocol 2 are rejected by the server and must be rebuilt.

Custom types let you define new column types — like COMPLEX, UUID, or VECTOR — that work with ORDER BY, indexes, and aggregate functions. This page is Step 4 of the Creating Extensions tutorial. Complete Steps 1–3 before continuing here.

Define Type Operations

Every custom type needs encode, decode, and compare operations, and optionally a hash operation. Implement them against these signatures and pass builder objects to vsql::make_type<>():

// Encode: string -> binary. Write to out.buffer() and call out.set_length(n).
void mytype_from_string(std::string_view from, vsql::CustomResult out) { /* ... */ }

// Decode: binary -> string. Write to out.buffer() and call out.set_length(n).
void mytype_to_string(vsql::CustomArg in, vsql::StringResult out) { /* ... */ }

// Compare: returns <0, 0, or >0.
int mytype_compare(vsql::CustomArg a, vsql::CustomArg b) { /* ... */ }

// Hash: returns hash code (optional).
size_t mytype_hash(vsql::CustomArg in) { /* ... */ }

For a from_string VDF (which returns the custom type), the server sizes the output buffer to at least the type’s persisted_length value before invoking the VDF, so buf.size() >= persisted_length is guaranteed on entry. This applies to both fixed-width types and parameterized types (where persisted_length is resolved from the type context at call time). No separate buffer-size request is needed.

Raw binary access goes through vsql::Span<T>, a non-owning view over a contiguous sequence of T — in.value() returns vsql::Span<const unsigned char> and out.buffer() returns vsql::Span<unsigned char>. Using C++20 or later, vsql::Span<T> is an alias for std::span<T>; under C++17 the SDK provides a minimal source-compatible fallback with the same data(), size(), empty(), indexing, and iterator surface. It is available via #include <villagesql/vsql.h>.

Register the Type

The vsql::make_type<kName>() template embeds encode, decode, compare, and hash operations directly in the type object. VDF names are auto-generated as TYPE::from_string, TYPE::to_string, TYPE::compare, and TYPE::hash at compile time. Separate .func(make_type_encode<>(...)) calls are not needed.

#include <villagesql/vsql.h>

using namespace vsql;

// Required for auto-generating VDF names at compile time.
static constexpr const char kMyTypeName[] = "MYTYPE";

constexpr auto MYTYPE =
    vsql::make_type<kMyTypeName>()
        .persisted_length(16)
        .max_decode_buffer_length(64)
        .from_string<&mytype_from_string>()   // auto: "MYTYPE::from_string"
        .to_string<&mytype_to_string>()       // auto: "MYTYPE::to_string"
        .compare<&mytype_compare>()           // auto: "MYTYPE::compare"
        .hash<&mytype_hash>()                 // optional; auto: "MYTYPE::hash"
        .intrinsic_default_str("...")         // must encode to exactly 16 bytes; see Development guide
        .build();

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .type(MYTYPE)
);

build() fails to compile if from_string, to_string, or compare is missing. Each template method validates the function pointer signature with static_assert. The type name is passed as a non-type template parameter (NTTP). Declare it as a static constexpr const char[] array — the pointer identity is used to key independent VDF name buffers, so two types sharing a function pointer still get separate auto-generated names.

Type Operations Reference

The template-based API auto-generates these SQL-callable VDFs:

Builder Method	Auto-Generated VDF Name	VDF SQL Signature
`.from_string<&f>()`	`TYPE::from_string`	`(STRING) -> CUSTOM(this type)`
`.to_string<&f>()`	`TYPE::to_string`	`(CUSTOM(this type)) -> STRING`
`.compare<&f>()`	`TYPE::compare`	`(CUSTOM(this type), CUSTOM(this type)) -> INT`
`.hash<&f>()`	`TYPE::hash`	`(CUSTOM(this type)) -> INT`

See Type Operation Builders for the full C++ signatures.

ALTER TABLE and Custom Types

ALTER TABLE ... MODIFY COLUMN and CHANGE COLUMN enforce these rules when custom types are involved:

From	To	Result
Non-custom	Custom	Error: `Cannot convert column 'col' to custom type 'MYTYPE'`
Custom	String type	Allowed
Custom	Non-string type	Error: `Cannot convert custom type column 'col' to non-string type`
Custom	Different custom type	Error if incompatible: `Cannot convert between incompatible custom types 'A' and 'B'`

Type Conversion Functions

With the template-based API, encode and decode VDFs are embedded in the type object and registered automatically — no separate .func() calls are needed. The auto-generated VDFs are SQL-callable:

-- Convert string to custom type (calls MYTYPE::from_string)
SELECT MYTYPE::from_string('(1.0,2.0)');

-- Convert custom type to string (calls MYTYPE::to_string)
SELECT MYTYPE::to_string(my_column) FROM my_table;

-- Explicit conversion in INSERT
INSERT INTO my_table (id, value)
VALUES (1, MYTYPE::from_string('(3.0,4.0)'));

When explicit conversion is required. VillageSQL implicitly converts a string literal to a custom type on direct column assignment, so INSERT INTO t (val) VALUES ('(1.0,2.0)') works without an explicit call. But expressions that resolve to STRING type — CASE expressions, CONCAT, and similar — are not implicitly coerced. Wrap them with TYPE::from_string:

UPDATE my_table
SET val = MYTYPE::from_string(
  CASE (pk MOD 2)
    WHEN 0 THEN '(1.0,2.0)'
    ELSE '(0.0,0.0)'
  END
);

Example: COMPLEX Type

Here’s a complete example implementing a COMPLEX number type:

#include <cstdio>
#include <cstring>
#include <villagesql/vsql.h>

using namespace vsql;

// Encode: "(real,imag)" string -> 16 bytes little-endian
void encode_complex(std::string_view from, CustomResult out) {
    auto buf = out.buffer();
    if (buf.size() < 16) return;
    double real, imag;
    if (sscanf(from.data(), "(%lf,%lf)", &real, &imag) != 2) {
        out.warning("invalid complex format: expected (real,imag)");
        return;
    }
    memcpy(buf.data(), &real, 8);
    memcpy(buf.data() + 8, &imag, 8);
    out.set_length(16);
}

// Decode: 16 bytes -> "(real,imag)" string
void decode_complex(CustomArg in, StringResult out) {
    auto data = in.value();
    if (data.size() < 16) return;
    double real, imag;
    memcpy(&real, data.data(), 8);
    memcpy(&imag, data.data() + 8, 8);
    auto buf = out.buffer();
    int len = snprintf(buf.data(), buf.size(), "(%.6f,%.6f)", real, imag);
    if (len < 0 || static_cast<size_t>(len) >= buf.size()) return;
    out.set_length(static_cast<size_t>(len));
}

// Compare for ORDER BY: real part first, then imaginary
int compare_complex(CustomArg a, CustomArg b) {
    auto da = a.value();
    auto db = b.value();
    if (da.size() < 16 || db.size() < 16) return 0;
    double a_real, a_imag, b_real, b_imag;
    memcpy(&a_real, da.data(), 8);
    memcpy(&a_imag, da.data() + 8, 8);
    memcpy(&b_real, db.data(), 8);
    memcpy(&b_imag, db.data() + 8, 8);
    if (a_real < b_real) return -1;
    if (a_real > b_real) return 1;
    if (a_imag < b_imag) return -1;
    if (a_imag > b_imag) return 1;
    return 0;
}

After defining these operations, users can create tables with your custom type:

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

INSERT INTO signals VALUES (1, '(50.0,10.0)', '(0.95,0.31)');

-- ORDER BY works because we provided compare_complex!
SELECT * FROM signals ORDER BY impedance;

-- Prepared statements work with custom types
PREPARE stmt FROM 'SELECT * FROM signals WHERE impedance = ?';
SET @val = '(50.0,10.0)';
EXECUTE stmt USING @val;

-- Aggregate operations work with custom types
SELECT COUNT(DISTINCT impedance), MIN(impedance), MAX(impedance),
       GROUP_CONCAT(impedance ORDER BY impedance) FROM signals;

VDFs in Generated Columns

VDFs can be used in generated column expressions. The VDF must be declared .deterministic() in the extension builder — the server blocks non-deterministic functions in this context.

CREATE TABLE signals (
    id INT PRIMARY KEY,
    impedance COMPLEX,
    -- Generated column computed by a VDF
    magnitude DOUBLE GENERATED ALWAYS AS (complex_abs(impedance)) STORED
);

complex_abs must be registered with .deterministic(). Traditional MySQL UDFs are not permitted in generated columns.

See vsql_complex Example for the complete implementation.

VDFs in Functional Indexes

VDFs can be used in functional index expressions. The same .deterministic() requirement from generated columns applies here because MySQL implements functional indexes as hidden generated columns.

CREATE TABLE signals (
    id INT PRIMARY KEY,
    sig COMPLEX,
    INDEX idx_magnitude ((COMPLEX_ABS(sig)))
);

The optimizer uses the index when the same VDF expression appears in WHERE, ORDER BY, or GROUP BY. Cast the comparison value to the VDF’s return type so the optimizer matches the expression:

SELECT id FROM signals WHERE COMPLEX_ABS(sig) > CAST(20.0 AS DOUBLE);

Next Steps

When your type is defined, continue with Step 5 of the tutorial to build and install your extension.

Continue: Build Your Extension

Return to the tutorial to build and install your extension.

Parameterized Types

Types that take parameters like VECTOR(1536) — dimension-aware encode, decode, and storage sizing.

Extension API Reference

VDF API contracts, null handling, buffer sizing, and advanced patterns.

Replication

ROW format requirements, extension install order, and version matching for replicated setups.

Documentation Index

​Define Type Operations

​Register the Type

​Type Operations Reference

​ALTER TABLE and Custom Types

​Type Conversion Functions

​Example: COMPLEX Type

​VDFs in Generated Columns

​VDFs in Functional Indexes

​Next Steps

Continue: Build Your Extension

Parameterized Types

Extension API Reference

Replication

Define Type Operations

Register the Type

Type Operations Reference

ALTER TABLE and Custom Types

Type Conversion Functions

Example: COMPLEX Type

VDFs in Generated Columns

VDFs in Functional Indexes

Next Steps