Skip to main content
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

PatternUsage
Fixed-length storageSet .persisted_length() in type builder
Platform-independentUse custom byte-order functions for doubles
NULL handlingCheck in->is_null in VDF implementations
Error handlingReturn VEF_RESULT_ERROR with error message
Result outputSet 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): 
1	(3.00,4.00)
2	(5.00,12.00)
3	(-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