> ## 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. # Extension Examples > Learn from the vsql_complex reference implementation using the VEF SDK 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](https://github.com/villagesql/villagesql-server/tree/main/villagesql/examples/vsql-complex) *** ## What vsql\_complex Provides COMPLEX type for complex numbers (a + bi) with arithmetic, utilities, and aggregation. **Usage Example:** ```sql theme={null} 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()`: ```cpp theme={null} #include 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):** ```cpp theme={null} 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):** ```cpp theme={null} 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):** ```cpp theme={null} 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):** ```sql theme={null} 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:** ```bash theme={null} cd /path/to/villagesql/build/mysql-test ./mysql-test-run.pl --suite=vsql_complex --record ``` **Run Tests:** ```bash theme={null} ./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`** ```json theme={null} { "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: ```sql theme={null} 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: ```sql theme={null} -- 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: ```sql theme={null} 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: ```sql theme={null} 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 Build your own extension Understand the internals View complete source code Browse extension catalog