> ## 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. # C++ Extension Examples > Learn from the vsql_complex reference implementation using the C++ SDK The `vsql_complex` extension is VillageSQL's reference implementation for custom types using the C++ 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 C++ SDK with `VEF_GENERATE_ENTRY_POINTS()`: ```cpp theme={null} #include // 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() .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() .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( "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()`, 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("name")` with `.clear<&fn>()` and `.accumulate<&fn>()`; the result function has signature `void(const State&, ResultType)` *** ## 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} void complex_from_string(std::string_view from, vsql::CustomResult out); void complex_to_string(vsql::CustomArg in, vsql::StringResult out); ``` Uses platform-independent byte-order functions for cross-platform compatibility. *** ## Argument and Result Types Implementations work with typed argument and result types rather than raw protocol structs: **Arithmetic Example (from complex.cc):** ```cpp theme={null} 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 functions (from complex.cc):** ```cpp theme={null} 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):** ```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** | Call `in.is_null()` on the argument type | | **Error handling** | Call `out.error("message")` on the result type | | **Result output** | Call `out.set(value)` / `out.set_length(n)` on the result type | *** ## 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