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

{
  "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

INSTALL EXTENSION my-awesome-extension;  -- ❌ Wrong (hyphens)

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

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

#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: 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'
    // Set *length to bytes written
    // Return false on success, true on error
}

// Decode: Convert binary to string representation
bool decode_mytype(const unsigned char* buffer, size_t buffer_size,
                   char* to, size_t to_buffer_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: Enable ORDER BY and indexing (optional but recommended)
int compare_mytype(const unsigned char* a, size_t a_len,
                   const unsigned char* b, size_t b_len) {
    // Return: negative if a<b, 0 if a==b, positive if a>b
}

// Hash: Custom hash function (optional, uses default if not provided)
size_t hash_mytype(const unsigned char* buffer, size_t buffer_size) {
    // 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)              // 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)'));

#include <cstring>
#include <cmath>

// Encode complex number from "(real,imag)" to 16 bytes
bool encode_complex(unsigned char* buffer, size_t buffer_size,
                    const char* from, size_t from_len, size_t* length) {
    if (buffer_size < 16) return true;

    // Parse "(real,imag)" format
    double real, imag;
    if (sscanf(from, "(%lf,%lf)", &real, &imag) != 2) {
        return true;
    }

    // Write 16 bytes: 8 bytes real + 8 bytes imag
    memcpy(buffer, &real, 8);
    memcpy(buffer + 8, &imag, 8);
    *length = 16;
    return false;
}

// Decode 16 bytes to "(real,imag)" string
bool decode_complex(const unsigned char* buffer, size_t buffer_size,
                    char* to, size_t to_buffer_size, size_t* to_length) {
    if (buffer_size < 16) return true;

    double real, imag;
    memcpy(&real, buffer, 8);
    memcpy(&imag, buffer + 8, 8);

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

    *to_length = len;
    return false;
}

// Compare for ORDER BY: compare real part first, then imaginary
int compare_complex(const unsigned char* a, size_t a_len,
                    const unsigned char* b, size_t b_len) {
    if (a_len < 16 || b_len < 16) return 0;

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

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

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

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