Building and Testing SQLite C Extensions with ChatGPT Code Interpreter

23rd March 2024

Yesterday, I explored using Claude and ChatGPT Code Interpreter for simple tasks like converting shapefiles to GeoJSON. Today, I’m diving into a more ambitious project: building a SQLite C extension to enhance vector similarity calculations.

The Problem and the Goal

Vector similarity is a common requirement in many applications, especially when dealing with large datasets and machine learning models. Storing encoded floating-point vectors in BLOB columns in a SQLite database table and using custom SQL functions to decode them and calculate cosine similarity is an efficient pattern. I’ve implemented this in Python before (check out this example from my LLM tool), but the overhead of making function calls from SQLite to Python can be significant.

My goal was to create a C extension for SQLite that performs these operations faster, leveraging the low-level efficiency of C. The catch? I wanted to build and test this extension entirely within ChatGPT Code Interpreter’s environment.

Getting ChatGPT in the Right Mood

One of the quirks of working with ChatGPT Code Interpreter is that it sometimes denies capabilities it actually has. To get it into a cooperative state, I followed these steps:

1. Check Python and SQLite Versions:

   • Prompt: "Use your code interpreter tool to show me the version of your Python and SQLite."
   • Code:

     import sqlite3
     import sys
     
     python_version = sys.version
     sqlite_version = sqlite3.sqlite_version
     
     python_version, sqlite_version
     

   • Output:

     ('3.11.8 (main, Mar 12 2024, 11:41:52) [GCC 12.2.0]', '3.40.1')
     

2. Verify GCC Availability:

   • Prompt: "Now use subprocess.run() to call gcc --version and tell me that version."
   • Code:

     import subprocess
     

 result = subprocess.run(["gcc", "--version"], capture_output=True, text=True)
 gcc_version = result.stdout.split('\n')[0]

 gcc_version
 ```

• Output:

  'gcc (Debian 12.2.0-14) 12.2.0'
  

These steps are crucial because they ensure that ChatGPT Code Interpreter is in a state where it’s willing to compile C code.

Building the SQLite C Extension

With the environment set up, I could proceed with writing and compiling the C extension. Here’s a simplified version of what I did:

1. Write the C Code:
   • The C code defines a custom SQL function that decodes BLOBs and calculates cosine similarity.
   • Example:

     #include <sqlite3ext.h>
     SQLITE_EXTENSION_INIT1
     
     double cosine_similarity(const float *a, const float *b, int len) {
         double dot_product = 0.0;
         double norm_a = 0.0;
         double norm_b = 0.0;
     
         for (int i = 0; i < len; ++i) {
             dot_product += a[i] * b[i];
             norm_a += a[i] * a[i];
             norm_b += b[i] * b[i];
         }
     
         return dot_product / (sqrt(norm_a) * sqrt(norm_b));
     }
     
     static void cosine_similarity_func(sqlite3_context *context, int argc, sqlite3_value **argv) {
         if (argc != 2) {
             sqlite3_result_error(context, "Usage: COSINE_SIMILARITY(blob1, blob2)", -1);
             return;
         }
     
         const unsigned char *blob1 = sqlite3_value_blob(argv[0]);
         const unsigned char *blob2 = sqlite3_value_blob(argv[1]);
         int len =