> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom Types in C++

> Define new column types for VillageSQL extensions in C++ — type operations, ALTER TABLE rules, conversion functions, and a complete COMPLEX type example.

<Warning>
  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.
</Warning>

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 in C++](/docs/mysql-9.7/dev/create)
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<>()`:

```cpp theme={null}
// 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) { /* ... */ }
```

<Note>
  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.
</Note>

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 C++ 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.

```cpp theme={null}
#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 Operations](/docs/mysql-9.7/dev/type-operations) 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:

```sql theme={null}
-- 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`:

```sql theme={null}
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:

```cpp theme={null}
#include <charconv>
#include <cstdlib>
#include <cstring>
#include <string>
#include <system_error>
#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;
    // strtod needs a null-terminated string; string_view is not one.
    const std::string input(from);
    const char *p = input.c_str();
    double real, imag;
    auto fail = [&] {
        out.warning("invalid complex format: expected (real,imag)");
    };
    if (*p++ != '(') return fail();
    char *endptr = nullptr;
    real = strtod(p, &endptr);
    if (endptr == p || *endptr != ',') return fail();
    p = endptr + 1;
    imag = strtod(p, &endptr);
    if (endptr == p || *endptr != ')') return fail();
    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();
    char *p = buf.data();
    char *const end = p + buf.size();
    if (p == end) return;
    *p++ = '(';
    auto re = std::to_chars(p, end, real);
    if (re.ec != std::errc() || re.ptr == end) return;
    p = re.ptr;
    *p++ = ',';
    auto im = std::to_chars(p, end, imag);
    if (im.ec != std::errc() || im.ptr == end) return;
    p = im.ptr;
    *p++ = ')';
    out.set_length(static_cast<size_t>(p - buf.data()));
}

// 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;
}
```

Decoding uses `std::to_chars` rather than `snprintf`: a fixed-precision format
such as `"%.6f"` drops digits a `double` needs, so `0.1234567891` reads back as
`0.123457`. Encoding copies the input before calling `strtod`, because
`std::string_view` carries no terminating null. The floating-point overloads of
`std::from_chars` would avoid the copy, but libc++ marks them available only
from macOS 26, so an extension that uses them fails to build for earlier
targets.

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

```sql theme={null}
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.

```sql theme={null}
CREATE TABLE signals (
    id INT PRIMARY KEY,
    impedance COMPLEX,
    -- Generated column computed by a VDF
    magnitude DOUBLE GENERATED ALWAYS AS (complex_abs(impedance)) STORED
);
```

<Note>
  `complex_abs` must be registered with `.deterministic()`. Traditional MySQL UDFs are not permitted in generated columns.
</Note>

See [vsql\_complex Example](/docs/mysql-9.7/dev/examples) 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.

```sql theme={null}
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:

```sql theme={null}
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.

<CardGroup cols={2}>
  <Card title="Continue: Build Your Extension" icon="hammer" href="/docs/mysql-9.7/dev/create#step-5-update-build-configuration">
    Return to the tutorial to build and install your extension.
  </Card>

  <Card title="Parameterized Types" icon="sliders" href="/docs/mysql-9.7/dev/type-operations#parameterized-types">
    Types that take parameters like VECTOR(1536) — dimension-aware encode, decode, and storage sizing.
  </Card>

  <Card title="Extension API Reference" icon="book" href="/docs/mysql-9.7/dev/extension-api-reference">
    VDF API contracts, null handling, buffer sizing, and advanced patterns.
  </Card>

  <Card title="Replication" icon="arrow-right-left" href="/docs/mysql-9.7/dev/managing#replication">
    ROW format requirements, extension install order, and version matching for replicated setups.
  </Card>
</CardGroup>