Creating Extensions

-- Unqualified (preferred for cleaner code)
SELECT complex_abs(impedance) FROM signals;

-- Qualified with extension name (explicit)
SELECT vsql_complex.complex_abs(impedance) FROM signals;

cd /path/to/villagesql-source
cp -r villagesql/sdk/template my-extension
cd my-extension

{
  "$schema": "https://raw.githubusercontent.com/villagesql/villagesql-docs/main/mysql-8.4/0.0.3/manifest.json.schema.json",
  "name": "my_awesome_extension",
  "version": "1.0.0",
  "description": "My custom VillageSQL extension that does amazing things",
  "author": "Your Name",
  "license": "GPL-2.0"
}

-- manifest.json has "name": "my_awesome_extension"
INSTALL EXTENSION my_awesome_extension;  -- ✅ Correct: no quoting needed
INSTALL EXTENSION `my-awesome-extension`;  -- ⚠️ Works, but requires backtick quoting

#include <villagesql/extension.h>
#include <villagesql/func_builder.h>
#include <villagesql/type_builder.h>

// Your implementation code here

VEF_GENERATE_ENTRY_POINTS(
  make_extension("my_extension", "1.0.0")  // name, version
    .func(make_func<&my_reverse_impl>("my_reverse")
      .returns(STRING)
      .param(STRING)
      .build())
    .func(make_func<&count_vowels_impl>("count_vowels")
      .returns(INT)
      .param(STRING)
      .build())
);

void complex_conjugate_impl(CustomArg in, CustomResult out) {
    if (in.is_null()) { out.set_null(); return; }
    auto src = in.value();    // Span<const unsigned char> — raw binary
    auto dst = out.buffer();  // Span<unsigned char>
    // ... read src, write result to dst ...
    out.set_length(src.size());
}

.func(make_func<&complex_conjugate_impl>("complex_conjugate")
          .returns(COMPLEX)
          .param(COMPLEX)
          .build())

.func(make_func<&complex_add_impl>("complex_add")
          .returns(COMPLEX)
          .param(COMPLEX)
          .param(COMPLEX)
          .deterministic()
          .build())

-- Deterministic VDFs can be used in generated columns
CREATE TABLE t (
    a COMPLEX,
    b COMPLEX,
    result COMPLEX GENERATED ALWAYS AS (complex_add(a, b)) STORED
);
-- STORED vs VIRTUAL follows standard MySQL generated column rules

make_func<&large_result_impl>("large_result")
  .returns(STRING)
  .param(INT)
  .buffer_size(65536)  // Request 64KB buffer
  .build()

void large_result_impl(vef_context_t* ctx, vef_invalue_t* input,
                       vef_vdf_result_t* result) {
    size_t needed = calculate_output_size(input);

    if (needed > result->max_str_len) {
        result->type = VEF_RESULT_ERROR;
        strcpy(result->error_msg, "Output exceeds buffer size");
        return;
    }

    // Write output to result->str_buf
    result->type = VEF_RESULT_VALUE;
    result->actual_len = actual_output_length;
}

typedef void (*vef_vdf_func_t)(vef_context_t *ctx, vef_vdf_args_t *args,
                                vef_vdf_result_t *result);

void my_func_impl(vef_context_t* ctx, vef_invalue_t* input,
                  vef_vdf_result_t* result) {
    // ... compute result ...
    return;  // always void -- no return value
}

void my_func_impl(vef_context_t* ctx, vef_invalue_t* input,
                  vef_vdf_result_t* result) {
    if (input->is_null) {
        result->type = VEF_RESULT_NULL;
        return;
    }
    // Safe to read input->str_value, input->int_value, etc.
}

void upper_impl(vef_context_t* ctx, vef_invalue_t* input,
                vef_vdf_result_t* result) {
    if (input->is_null) {
        result->type = VEF_RESULT_NULL;
        return;
    }

    size_t len = input->str_len;
    if (len > result->max_str_len) {
        result->type = VEF_RESULT_ERROR;
        snprintf(result->error_msg, VEF_MAX_ERROR_LEN,
                 "Input length %zu exceeds buffer size %zu",
                 len, result->max_str_len);
        return;
    }

    for (size_t i = 0; i < len; i++) {
        result->str_buf[i] = toupper(input->str_value[i]);
    }
    result->type = VEF_RESULT_VALUE;
    result->actual_len = len;
}

// Correct -- error goes to error_msg
result->type = VEF_RESULT_ERROR;
snprintf(result->error_msg, VEF_MAX_ERROR_LEN,
         "Invalid input: expected positive integer, got %lld",
         input->int_value);
return;

typedef bool (*vef_encode_func_t)(unsigned char *buffer, size_t buffer_size,
                                  const char *from, size_t from_len,
                                  size_t *length);

typedef bool (*vef_decode_func_t)(const unsigned char *buffer,
                                  size_t buffer_size, char *to, size_t to_size,
                                  size_t *to_length);

#include <algorithm>
#include <string>
#include <cstring>

// String reverse implementation
void my_reverse_impl(vef_context_t* ctx, vef_invalue_t* input,
                     vef_vdf_result_t* result) {
    if (input->is_null) {
        result->type = VEF_RESULT_NULL;
        return;
    }

    // Reverse the string
    size_t len = input->str_len;
    for (size_t i = 0; i < len; i++) {
        result->str_buf[i] = input->str_value[len - 1 - i];
    }
    result->type = VEF_RESULT_VALUE;
    result->actual_len = len;
}

// Count vowels implementation
void count_vowels_impl(vef_context_t* ctx, vef_invalue_t* input,
                       vef_vdf_result_t* result) {
    if (input->is_null) {
        result->type = VEF_RESULT_NULL;
        return;
    }

    int64_t count = 0;
    for (size_t i = 0; i < input->str_len; i++) {
        char lower = std::tolower(input->str_value[i]);
        if (lower == 'a' || lower == 'e' || lower == 'i' ||
            lower == 'o' || lower == 'u') {
            count++;
        }
    }
    result->int_value = count;
    result->type = VEF_RESULT_VALUE;
}

void my_func_impl(vef_context_t* ctx, vef_invalue_t* input,
                  vef_vdf_result_t* result) {
    // Check if input is NULL
    if (input->is_null) {
        result->type = VEF_RESULT_NULL;
        return;
    }

    // Access input data
    const char* str = input->str_value;
    size_t len = input->str_len;

    // Process and write result to result->str_buf
    // ...

    result->type = VEF_RESULT_VALUE;
    result->actual_len = output_length;
}

void validate_age_impl(vef_context_t* ctx, vef_invalue_t* age_input,
                       vef_vdf_result_t* result) {
    if (age_input->is_null) {
        result->type = VEF_RESULT_NULL;
        return;
    }

    long long age = age_input->int_value;

    // Validate input range
    if (age < 0 || age > 150) {
        result->type = VEF_RESULT_ERROR;
        // Write error message
        const char* msg = "Age must be between 0 and 150";
        strcpy(result->error_msg, msg);
        return;
    }

    // Return valid result
    result->type = VEF_RESULT_VALUE;
    result->int_value = age;
}

// Encode: string -> binary. false = success, true = error.
// Set *length = SIZE_MAX to produce SQL NULL.
bool mytype_from_string(std::string_view from,
                        villagesql::Span<unsigned char> buf,
                        size_t *length) { /* ... */ }

// Decode: binary -> string. false = success, true = error.
bool mytype_to_string(villagesql::Span<const unsigned char> data,
                      villagesql::Span<char> out,
                      size_t *out_len) { /* ... */ }

// Compare: returns <0, 0, or >0.
int mytype_compare(villagesql::Span<const unsigned char> a,
                   villagesql::Span<const unsigned char> b) { /* ... */ }

// Hash: returns hash code (optional).
size_t mytype_hash(villagesql::Span<const unsigned char> data) { /* ... */ }

// Encode: Convert string representation to binary
bool encode_mytype(unsigned char* buffer, size_t buffer_size,
                   const char* from, size_t from_len, size_t* length) {
    // Parse 'from' string and write binary to 'buffer'
    // Write binary to 'buffer' and set *length to bytes written
    // Return false on success, true on error (e.g., set *length = 0)
}

// Decode: Convert binary to string representation
bool decode_mytype(const unsigned char* buffer, size_t buffer_size,
                   char* to, size_t to_size, size_t* to_length) {
    // Read binary from 'buffer' and write string to 'to'
    // Set *to_length to string length
    // Return false on success, true on error
}

// Compare: enables ORDER BY and indexing (required)
int compare_mytype(const unsigned char* data1, size_t len1,
                   const unsigned char* data2, size_t len2) {
    // Return: negative if data1<data2, 0 if equal, positive if data1>data2
}

// Hash: custom hash (optional, uses default binary hash if omitted)
size_t hash_mytype(const unsigned char* data, size_t len) {
    // Return hash value for the binary data
}

VEF_GENERATE_ENTRY_POINTS(
  make_extension("my_extension", "1.0.0")
    .type(make_type(MYTYPE)
      .persisted_length(16)
      .max_decode_buffer_length(64)
      .encode("mytype_from_string")      // VDF name instead of &func
      .decode("mytype_to_string")
      .compare("mytype_compare")         // Enables ORDER BY and indexes
      .hash("mytype_hash")              // Optional
      .build())
    .func(make_type_encode<&mytype_from_string>("mytype_from_string", MYTYPE))
    .func(make_type_decode<&mytype_to_string>("mytype_to_string", MYTYPE))
    .func(make_type_compare<&mytype_compare>("mytype_compare", MYTYPE))
    .func(make_type_hash<&mytype_hash>("mytype_hash", MYTYPE))
);

VEF_GENERATE_ENTRY_POINTS(
  make_extension("my_extension", "1.0.0")
    .type(make_type(MYTYPE)
      .persisted_length(16)              // Fixed storage size in bytes
      .max_decode_buffer_length(64)      // Max string representation size
      .encode(&encode_mytype)
      .decode(&decode_mytype)
      .compare(&compare_mytype)          // Enables ORDER BY and indexes
      .hash(&hash_mytype)                // Optional custom hash
      .build())
    .func(make_func<&mytype_constructor>("MYTYPE")  // Constructor function
      .returns(MYTYPE)
      .param(REAL)
      .param(REAL)
      .build())
);

VEF_GENERATE_ENTRY_POINTS(
  make_extension("my_extension", "1.0.0")
    .type(make_type(MYTYPE)
      .persisted_length(16)
      .max_decode_buffer_length(64)
      .encode(&encode_mytype)
      .decode(&decode_mytype)
      .compare(&compare_mytype)
      .build())
    // String → Custom Type conversion
    .func(make_func("mytype_from_string")
      .from_string<&encode_mytype>(MYTYPE))
    // Custom Type → String conversion
    .func(make_func("mytype_to_string")
      .to_string<&decode_mytype>(MYTYPE))
    .build()
);

-- Convert string to custom type
SELECT mytype_from_string('(1.0,2.0)');

-- Convert custom type 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)'));

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
);

#include <cstdio>
#include <cstring>

// Encode complex number from "(real,imag)" to 16 bytes
bool encode_complex(std::string_view from,
                    villagesql::Span<unsigned char> buf,
                    size_t *length) {
    if (buf.size() < 16) return true;

    double real, imag;
    if (sscanf(from.data(), "(%lf,%lf)", &real, &imag) != 2) return true;

    memcpy(buf.data(), &real, 8);
    memcpy(buf.data() + 8, &imag, 8);
    *length = 16;
    return false;
}

// Decode 16 bytes to "(real,imag)" string
bool decode_complex(villagesql::Span<const unsigned char> data,
                    villagesql::Span<char> out,
                    size_t *out_len) {
    if (data.size() < 16) return true;

    double real, imag;
    memcpy(&real, data.data(), 8);
    memcpy(&imag, data.data() + 8, 8);

    int len = snprintf(out.data(), out.size(), "(%.6f,%.6f)", real, imag);
    if (len < 0 || len >= (int)out.size()) return true;

    *out_len = len;
    return false;
}

// Compare for ORDER BY: compare real part first, then imaginary
int compare_complex(villagesql::Span<const unsigned char> a,
                    villagesql::Span<const unsigned char> b) {
    if (a.size() < 16 || b.size() < 16) return 0;

    double a_real, a_imag, b_real, b_imag;
    memcpy(&a_real, a.data(), 8);
    memcpy(&a_imag, a.data() + 8, 8);
    memcpy(&b_real, b.data(), 8);
    memcpy(&b_imag, b.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;
}

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;

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

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

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

cmake_minimum_required(VERSION 3.16)
project(my_extension)

# Find VillageSQL Extension Framework
find_package(VillageSQLExtensionFramework QUIET
    PATHS "${CMAKE_CURRENT_SOURCE_DIR}/../../../cmake"
)

# The framework detects build flags via 4 methods (in order):
# 1. Explicit MYSQL_INCLUDE_FLAGS/MYSQL_CXXFLAGS
# 2. VillageSQL_BUILD_DIR (reads CMakeCache.txt from VillageSQL build)
# 3. VSQL_BASE_DIR (uses mysql_config)
# 4. Default - mysql_config from PATH

# Build shared library with your source files
add_library(extension SHARED
    src/extension.cc
    src/my_functions.cc
)

# Create VEB archive
VEF_CREATE_VEB(
    NAME my_extension
    LIBRARY_TARGET extension
    MANIFEST ${CMAKE_CURRENT_SOURCE_DIR}/manifest.json
)

# Install VEB to VillageSQL extensions directory
install(FILES ${VEB_OUTPUT} DESTINATION ${INSTALL_DIR})

mkdir build
cd build

# Configure the build
cmake ..

# Or, if building against VillageSQL source:
cmake .. -DVillageSQL_BUILD_DIR=/path/to/villagesql/build

# Or, to build against the unstable dev ABI headers:
cmake .. -DVSQL_USE_DEV_ABI=ON

# Build the extension
make

make show_veb

manifest.json
lib/myext.so

make install

# Find the VEF directory
mysql -u root -p -e "SHOW VARIABLES LIKE 'veb_dir';"

# Copy the VEB file
sudo cp my-awesome-extension.veb /path/to/veb_dir/

// Called once before the first row
void my_prerun(vef_context_t* ctx, vef_prerun_args_t* args,
               vef_prerun_result_t* result) {
    // Allocate state, open connections, etc.
    void* state = malloc(sizeof(MyState));
    result->user_data = state;
}

// Called once after all rows processed
void my_postrun(vef_context_t* ctx, vef_postrun_args_t* args,
                vef_postrun_result_t* result) {
    // Clean up state allocated in prerun
    if (args->user_data) {
        free(args->user_data);
    }
}

// Register with extension
VEF_GENERATE_ENTRY_POINTS(
  make_extension("my_ext", "1.0.0")
    .func(make_func<&my_func_impl>("my_func")
      .returns(STRING)
      .param(STRING)
      .prerun(&my_prerun)
      .postrun(&my_postrun)
      .build())
);

-- COUNT(DISTINCT) works with custom types
SELECT COUNT(DISTINCT impedance) FROM signals;

-- MIN and MAX work with custom types (requires compare function)
SELECT MIN(impedance), MAX(impedance) FROM signals;

-- GROUP_CONCAT works with custom types
SELECT GROUP_CONCAT(impedance ORDER BY impedance SEPARATOR ', ') FROM signals;

SELECT
    id,
    impedance,
    LAG(impedance)  OVER (ORDER BY id) AS prev_impedance,
    LEAD(impedance) OVER (ORDER BY id) AS next_impedance
FROM signals;

SELECT
    id,
    impedance,
    FIRST_VALUE(impedance) OVER w AS first_impedance,
    LAST_VALUE(impedance)  OVER w AS last_impedance,
    NTH_VALUE(impedance, 2) OVER w AS second_impedance
FROM signals
WINDOW w AS (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

CREATE TEMPORARY TABLE tmp_signals (
    id        INT PRIMARY KEY,
    impedance COMPLEX
);

INSERT INTO tmp_signals VALUES (1, '(10,5)'), (2, '(20,0)');
SELECT id, impedance FROM tmp_signals;

CREATE TABLE signals (
    id        INT PRIMARY KEY,
    impedance COMPLEX,
    label     VARCHAR(50)
);
CREATE TABLE signal_log (
    id        INT,
    label     VARCHAR(50),
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER signals_after_insert
AFTER INSERT ON signals
FOR EACH ROW
    INSERT INTO signal_log (id, label) VALUES (NEW.id, NEW.label);

INSERT INTO signals VALUES (1, '(10,5)', 'sensor_a');
SELECT id, label FROM signal_log;

make show_veb
tail -f /var/log/mysql/error.log

SELECT * FROM INFORMATION_SCHEMA.EXTENSIONS;

# Verify mysql_config is available
which mysql_config
mysql_config --version

# Check compiler version
gcc --version  # or clang --version

# Verify CMake version (3.16+ required)
cmake --version