Recent entries
AI for Data Journalism: demonstrating what we can do with this stuff right now one day ago
I gave a talk last month at the Story Discovery at Scale data journalism conference hosted at Stanford by Big Local News. My brief was to go deep into the things we can use Large Language Models for right now, illustrated by a flurry of demos to help provide starting points for further conversations at the conference.
I used the talk as an opportunity for some demo driven development—I pulled together a bunch of different project strands for the talk, then spent the following weeks turning them into releasable tools.
There are 12 live demos in this talk!
- Haikus from images with Claude 3 Haiku
- Pasting data from Google Sheets into Datasette Cloud
- AI-assisted SQL queries with datasette-query-assistant
- Scraping data with shot-scraper
- Enriching data in a table
- Command-line tools for working with LLMs
- Structured data extraction
- Code Interpreter and access to tools
- Running queries in Datasette from ChatGPT using a GPT
- Semantic search with embeddings
- Datasette Scribe: searchable Whisper transcripts
- Trying and failing to analyze hand-written campaign finance documents
The full 50 minute video of my talk is available on YouTube. Below I’ve turned that video into an annotated presentation, with screenshots, further information and links to related resources and demos that I showed during the talk.
Three major LLM releases in 24 hours (plus weeknotes) nine days ago
I’m a bit behind on my weeknotes, so there’s a lot to cover here. But first... a review of the last 24 hours of Large Language Model news. All times are in US Pacific on April 9th 2024.
- 11:01am: Google Gemini Pro 1.5 hits general availability, here’s the blog post—their 1 million token context GPT-4 class model now has no waitlist, is available to anyone in 180 countries (not including Europe or the UK as far as I can tell) and most impressively all the API has a free tier that allows up to 50 requests a day, though rate limited to 2 per minute. Beyond that you can pay $7/million input tokens and $21/million output tokens, which is slightly less than GPT-4 Turbo and a little more than Claude 3 Sonnet. Gemini Pro also now support audio inputs and system prompts.
- 11:44am: OpenAI finally released the non-preview version of GPT-4 Turbo, integrating GPT-4 Vision directly into the model (previously it was separate). Vision mode now supports both functions and JSON output, previously unavailable for image inputs. OpenAI also claim that the new model is “Majorly improved” but no-one knows what they mean by that.
- 6:20pm (3:20am in their home country of France): Mistral tweet a link to a 281GB magnet BitTorrent of Mixtral 8x22B—their latest openly licensed model release, significantly larger than their previous best open model Mixtral 8x7B. I’ve not seen anyone get this running yet but it’s likely to perform extremely well, given how good the original Mixtral was.
And while it wasn’t released today (it came out last week), this morning Cohere’s Command R+ (an excellent openly licensed model) reached position 6 on the LMSYS Chatbot Arena Leaderboard—the highest ever ranking for an open weights model.
Since I have a lot of software that builds on these models, I spent a bunch of time today publishing new releases of things.
Datasette Extract with GPT-4 Turbo Vision
I’ve been working on Datasette Extract for a while now: it’s a plugin for Datasette that adds structured data extraction from unstructured text, powered by GPT-4 Turbo.
I updated it for the new model releases this morning, and decided to celebrate by making a video showing what it can do:
I want to start publishing videos like this more often, so this felt like a great opportunity to put that into practice.
The Datasette Cloud blog hasn’t had an entry in a while, so I published screenshots and notes there to accompany the video.
Gemini Pro 1.5 system prompts
I really like system prompts—extra prompts you can pass to an LLM that give it instructions about how to process the main input. They’re sadly not a guaranteed solution for prompt injection—even with instructions separated from data by a system prompt you can still over-ride them in the main prompt if you try hard enough—but they’re still useful for non-adversarial situations.
llm-gemini 0.1a2 adds support for them, so now you can do things like this:
llm -m p15 'say hi three times three different ways' \
--system 'in spanish'
And get back output like this:
¡Hola! 👋 ¡Buenos días! ☀️ ¡Buenas tardes! 😊
Interestingly “in german” doesn’t include emoji, but “in spanish” does.
I had to reverse-engineer the REST format for sending a system prompt from the Python library as the REST documentation hasn’t been updated yet—notes on that in my issue.
datasette-enrichments-gpt using GPT-4 Turbo
Another small release: the datasette-enrichments-gpt plugin can enrich data in a table by running prompts through GPT-3.5, GPT-4 Turbo or GPT-4 Vision. I released version 0.4 switching to the new GPT-4 Turbo model.
Everything else
That covers today... but my last weeknotes were nearly four weeks ago! Here’s everything else, with a few extra annotations:
Blog entries
All five of my most recent posts are about ways that I use LLM tools in my own work—see also my How I use LLMs and ChatGPT series.
- Building files-to-prompt entirely using Claude 3 Opus
- Running OCR against PDFs and images directly in your browser
- llm cmd undo last git commit—a new plugin for LLM
- Building and testing C extensions for SQLite with ChatGPT Code Interpreter
- Claude and ChatGPT for ad-hoc sidequests
Releases
Many of these releases relate to ongoing work on Datasette Cloud. In particular there’s a flurry of minor releases to add descriptions to the action menu items added by various plugins, best illustrated by this screenshot:
-
datasette-enrichments-gpt 0.4—2024-04-10
Datasette enrichment for analyzing row data using OpenAI’s GPT models -
llm-gemini 0.1a2—2024-04-10
LLM plugin to access Google’s Gemini family of models -
datasette-public 0.2.3—2024-04-09
Make specific Datasette tables visible to the public -
datasette-enrichments 0.3.2—2024-04-09
Tools for running enrichments against data stored in Datasette -
datasette-extract 0.1a4—2024-04-09
Import unstructured data (text and images) into structured tables -
datasette-cors 1.0—2024-04-08
Datasette plugin for configuring CORS headers -
asgi-cors 1.0—2024-04-08
ASGI middleware for applying CORS headers to an ASGI application -
files-to-prompt 0.2.1—2024-04-08
Concatenate a directory full of files into a single prompt for use with LLMs -
datasette-embeddings 0.1a3—2024-04-08
Store and query embedding vectors in Datasette tables -
datasette-studio 0.1a3—2024-04-06
Datasette pre-configured with useful plugins. Experimental alpha. -
datasette-paste 0.1a5—2024-04-06
Paste data to create tables in Datasette -
datasette-import 0.1a4—2024-04-06
Tools for importing data into Datasette -
datasette-enrichments-quickjs 0.1a2—2024-04-05
Enrich data with a custom JavaScript function -
s3-credentials 0.16.1—2024-04-05
A tool for creating credentials for accessing S3 buckets -
llm-command-r 0.2—2024-04-04
Access the Cohere Command R family of models -
llm-nomic-api-embed 0.1—2024-03-30
Create embeddings for LLM using the Nomic API -
textract-cli 0.1—2024-03-29
CLI for running files through AWS Textract -
llm-cmd 0.1a0—2024-03-26
Use LLM to generate and execute commands in your shell -
datasette-write 0.3.2—2024-03-18
Datasette plugin providing a UI for executing SQL writes against the database
TILs
- impaste: pasting images to piped commands on macOS—2024-04-04
- Installing tools written in Go—2024-03-26
- Google Chrome --headless mode—2024-03-24
- Reviewing your history of public GitHub repositories using ClickHouse—2024-03-20
- Running self-hosted QuickJS in a browser—2024-03-20
- Programmatically comparing Python version strings—2024-03-17
Building files-to-prompt entirely using Claude 3 Opus 10 days ago
files-to-prompt is a new tool I built to help me pipe several files at once into prompts to LLMs such as Claude and GPT-4.
When combined with my LLM command-line tool it lets you do things like this:
files-to-prompt README.md files_to_prompt | llm -m opus \
--system 'Update this README to reflect this functionality'
I wrote files-to-prompt
almost entirely using Claude 3 Opus, llm-claude-3 and files-to-prompt
itself, once it was functional enough to be useful.
Building the initial tool
I started with my click-app cookiecutter template. This can quickly spin up a skeleton of a new Python command-line tool using the Click library:
cookiecutter gh:simonw/click-app
[1/6] app_name (): files-to-prompt
[2/6] description (): Concatenate a directory full of files into a single prompt for use with LLMs
[3/6] hyphenated (files-to-prompt):
[4/6] underscored (files_to_prompt):
[5/6] github_username (): simonw
[6/6] author_name (): Simon Willison
I opened the files_to_prompt/cli.py
file it created for me and used it to spec out how the initial version should work:
import click @click.command() @click.argument( "path", type=click.Path ) @click.option( "--include-hidden", is_flag=True, help="Include files and folders starting with .", ) @click.version_option() def cli(path, include_hidden): """ Takes a path to a folder and outputs every file in that folder, recursively, each one preceeded with its filename like this: path/to/file.py ---- Contents of file.py goes here --- path/to/file2.py --- ... """
This code defines a tool that takes a positional path
argument and an optional --include-hidden
flag, along with a Python docstring describing what it should do which will be displayed as the --help
output.
I’d originally intended to actually build the tool... but once I got to this point I realized that this might be enough information for Claude 3 Opus to do the rest of the work for me.
So I ran this command:
cat files_to_prompt/cli.py | llm -m opus \
--system 'finish this code for me'
You can see the full transcript here, but from this single prompt Claude 3 Opus produced the exact implementation I needed:
for root, dirs, files in os.walk(path): if not include_hidden: dirs[:] = [d for d in dirs if not d.startswith(".")] files = [f for f in files if not f.startswith(".")] for file in files: file_path = os.path.join(root, file) with open(file_path, "r") as f: file_contents = f.read() click.echo(file_path) click.echo("---") click.echo(file_contents) click.echo() click.echo("---")
I pasted that into by cli.py
module and tried running it... and it worked first time.
I tried a more challenging prompt next:
cat files_to_prompt/cli.py | llm -m opus '
modify this code to add a --ignore-gitignore flag which defaults toFalse.
Without this flag the code should read any .gitignore files and filter
content based on the rules within them. With the flag it should not do
that.'
I forgot to use the --system
option here, so the argument prompt I passed to llm
was concatenated directly onto the end of the code rather than being treated as a system/instruction prompt. This turned out not to matter.
.gitignore
is a tricky format to handle. Claude 3 Opus didn’t do a comprehensive implementation, but it did support the subset that I cared about for this step of the development process.
Here’s what it gave me (full transcript here):
import os import click from pathlib import Path from fnmatch import fnmatch def should_ignore(path, gitignore_rules): for rule in gitignore_rules: if fnmatch(path, rule): return True return False def read_gitignore(path): gitignore_path = os.path.join(path, ".gitignore") if os.path.isfile(gitignore_path): with open(gitignore_path, "r") as f: return [line.strip() for line in f if line.strip() and not line.startswith("#")] return [] @click.command() @click.argument( "path", type=click.Path(exists=True, file_okay=False, dir_okay=True, readable=True), ) @click.option( "--include-hidden", is_flag=True, help="Include files and folders starting with .", ) @click.option( "--ignore-gitignore", is_flag=True, help="Ignore .gitignore files and include all files", ) @click.version_option() def cli(path, include_hidden, ignore_gitignore): """ Takes a path to a folder and outputs every file in that folder, recursively, each one preceded with its filename like this: path/to/file.py ---- Contents of file.py goes here --- path/to/file2.py --- ... """ gitignore_rules = [] if ignore_gitignore else read_gitignore(path) for root, dirs, files in os.walk(path): if not include_hidden: dirs[:] = [d for d in dirs if not d.startswith(".")] files = [f for f in files if not f.startswith(".")] if not ignore_gitignore: gitignore_rules.extend(read_gitignore(root)) dirs[:] = [d for d in dirs if not should_ignore(os.path.join(root, d), gitignore_rules)] files = [f for f in files if not should_ignore(os.path.join(root, f), gitignore_rules)] for file in files: file_path = os.path.join(root, file) with open(file_path, "r") as f: file_contents = f.read() click.echo(file_path) click.echo("---") click.echo(file_contents) click.echo() click.echo("---")
From that point on I was hooked: could I use the new files-to-prompt
tool to build the rest of itself?
Since I’d already bootstrapped it to a point where I could use it, I ran it for real for the first time:
files-to-prompt . | llm -m opus --system '
fill out the test_files_to_prompt.py with tests, use tmpdir fixture
to create example directories to run the command against - tests
should cover both of the optional flags'
Here I’m sending it quite a bit of code—the implementation, the README and the Apache 2 License are all included in that prompt.
It wrote me these tests:
import os from click.testing import CliRunner from files_to_prompt.cli import cli def test_basic_functionality(tmpdir): runner = CliRunner() with tmpdir.as_cwd(): os.makedirs("test_dir") with open("test_dir/file1.txt", "w") as f: f.write("Contents of file1") with open("test_dir/file2.txt", "w") as f: f.write("Contents of file2") result = runner.invoke(cli, ["test_dir"]) assert result.exit_code == 0 assert "test_dir/file1.txt" in result.output assert "Contents of file1" in result.output assert "test_dir/file2.txt" in result.output assert "Contents of file2" in result.output def test_include_hidden(tmpdir): runner = CliRunner() with tmpdir.as_cwd(): os.makedirs("test_dir") with open("test_dir/.hidden.txt", "w") as f: f.write("Contents of hidden file") result = runner.invoke(cli, ["test_dir"]) assert result.exit_code == 0 assert "test_dir/.hidden.txt" not in result.output result = runner.invoke(cli, ["test_dir", "--include-hidden"]) assert result.exit_code == 0 assert "test_dir/.hidden.txt" in result.output assert "Contents of hidden file" in result.output def test_ignore_gitignore(tmpdir): runner = CliRunner() with tmpdir.as_cwd(): os.makedirs("test_dir") with open("test_dir/.gitignore", "w") as f: f.write("ignored.txt") with open("test_dir/ignored.txt", "w") as f: f.write("This file should be ignored") with open("test_dir/included.txt", "w") as f: f.write("This file should be included") result = runner.invoke(cli, ["test_dir"]) assert result.exit_code == 0 assert "test_dir/ignored.txt" not in result.output assert "test_dir/included.txt" in result.output result = runner.invoke(cli, ["test_dir", "--ignore-gitignore"]) assert result.exit_code == 0 assert "test_dir/ignored.txt" in result.output assert "This file should be ignored" in result.output assert "test_dir/included.txt" in result.output
Then I ran them... and they failed. Here’s the error message:
def test_ignore_gitignore(tmpdir):
runner = CliRunner()
with tmpdir.as_cwd():
os.makedirs("test_dir")
with open("test_dir/.gitignore", "w") as f:
f.write("ignored.txt")
with open("test_dir/ignored.txt", "w") as f:
f.write("This file should be ignored")
with open("test_dir/included.txt", "w") as f:
f.write("This file should be included")
result = runner.invoke(cli, ["test_dir"])
assert result.exit_code == 0
> assert "test_dir/ignored.txt" not in result.output
E AssertionError: assert 'test_dir/ignored.txt' not in 'test_dir/in...red\n\n---\n'
E
E 'test_dir/ignored.txt' is contained here:
E uded
E
E ---
E test_dir/ignored.txt
E ---...
E
E ...Full output truncated (3 lines hidden), use '-vv' to show
I saved that message to `error.txt and ran this command to send it back to Claude 3 Opus:
cat error.txt | llm -c
The llm -c
command stands for “continue”—it will take any input or prompt and pass that back to the model along with the previous logged prompts and responses, equivalent to asking a follow-up question in an interface like ChatGPT.
Claude gave me back a corrected version of the should_ignore()
function:
def should_ignore(path, gitignore_rules): for rule in gitignore_rules: if fnmatch(os.path.basename(path), rule): return True if os.path.isdir(path) and fnmatch(os.path.basename(path) + "/", rule): return True return False
Sending follow-on prompts with llm -c
seemed to be working well, so I ran this:
llm -c 'Fill out the Usage section of the README for this project - just return that section as markdown'
And it gave me back a pretty decent README:
Usage
To use
files-to-prompt
, simply provide the path to the directory you want to process:files-to-prompt path/to/directoryThis will output the contents of every file in the directory and its subdirectories, with each file preceded by its relative path and separated by
---
.Options
--include-hidden
: Include files and folders starting with.
(hidden files and directories).files-to-prompt path/to/directory --include-hidden
--ignore-gitignore
: Ignore.gitignore
files and include all files.files-to-prompt path/to/directory --ignore-gitignoreExample
Suppose you have a directory structure like this:
my_directory/ ├── file1.txt ├── file2.txt ├── .hidden_file.txt └── subdirectory/ └── file3.txt
Running
files-to-prompt my_directory
will output:my_directory/file1.txt --- Contents of file1.txt --- my_directory/file2.txt --- Contents of file2.txt --- my_directory/subdirectory/file3.txt --- Contents of file3.txt ---
If you run
files-to-prompt my_directory --include-hidden
, the output will also include.hidden_file.txt
:my_directory/.hidden_file.txt --- Contents of .hidden_file.txt --- ...
I particularly liked the directory structure diagram.
Here’s the full transcript including my follow-ups.
I committed and pushed everything I had so far to GitHub.
After one last review of the README I noticed it had used the phrase “simply provide the path to the directory”. I don’t like using words like simply in documentation, so I fixed that.
And I shipped version 0.1 of the software! Almost every line of code, tests and documentation written by Claude 3 Opus.
Iterating on the project
I’ve added several features since that initial implementation, almost all of which were primarily written by prompting Claude 3 Opus.
Issue #2: Take multiple arguments for files and directories to include changed the tool such that files-to-prompt README.md tests/
would include both the README.md
file and all files in the tests/
directory.
The sequence of prompts to get there was as follows:
cat files_to_prompt/cli.py | llm -m opus --system '
Modify this file. It should take multiple arguments in a variable called paths.
Each of those argumets might be a path to a file or it might be a path to a
directory - if any of the arguments do not correspoind to a file or directory
it should raise a click error.
It should then do what it does already but for all files
files-recursively-contained-within-folders that are passed to it.
It should still obey the gitignore logic.'
Then these to update the tests:
files-to-prompt files_to_prompt tests | llm -m opus --system '
rewrite the tests to cover the ability to pass multiple files and
folders to the tool'
files-to-prompt files_to_prompt tests | llm -m opus --system '
add one last test which tests .gitignore and include_hidden against
an example that mixes single files and directories of files together
in one invocation'
I didn’t like the filenames it was using in that last test, so I used symbex to extract just the implementation of that test and told it to rewrite it:
symbex test_mixed_paths_with_options | llm -m opus --system '
rewrite this test so the filenames are more obvious, thinks like
ignored_in_gitignore.txt'
And this to add one last test that combined all of the options:
llm -c 'add a last bit to that test for
["test_dir", "single_file.txt", "--ignore-gitignore", "--include-hidden"]'
The issue includes links to the full transcripts for the above.
Updating a diff from a pull request
I quietly released files-to-prompt
two weeks ago. Dipam Vasani had spotted it and opened a pull request adding the ability to ignore specific files, by passing --ignore-patterns '*.md'
as an option.
The problem was... I’d landed some of my own changes before I got around to reviewing his PR—so it would no longer cleanly apply.
It turns out I could resolve that problem using Claude 3 Opus as well, by asking it to figure out the change from Dipam’s diff.
I pulled a copy of his PR as a diff like this:
wget 'https://github.com/simonw/files-to-prompt/pull/4.diff'
Then I fed both the diff and the relevant files from the project into Claude:
files-to-prompt 4.diff files_to_prompt/cli.py tests/test_files_to_prompt.py | \
llm -m opus --system \
'Apply the change described in the diff to the project - return updated cli.py and tests'
It didn’t quite work—it reverted one of my earlier changes. So I prompted:
llm -c 'you undid the change where it could handle multiple paths -
I want to keep that, I only want to add the new --ignore-patterns option'
And that time it worked! Transcript here.
I merged Claude’s work into the existing PR to ensure Dipam got credit for his work, then landed it and pushed it out in a release.
Was this worthwhile?
As an exercise in testing the limits of what’s possible with command-line LLM access and the current most powerful available LLM, this was absolutely worthwhile. I got working software with comprehensive tests and documentation, and had a lot of fun experimenting with prompts along the way.
It’s worth noting that this project was incredibly low stakes. files-to-prompt
is a tiny tool that does something very simple. Any bugs or design flaws really don’t matter. It’s perfect for trying out this alternative approach to development.
I also got the software built a whole lot faster than if I’d written it myself, and with features like .gitignore
support (albeit rudimentary) that I may not have bothered with working alone. That’s a good example of a feature that’s just fiddly enough that I might decide not to invest the time needed to get it to work.
Is this the best possible version of this software? Definitely not. But with comprehensive documentation and automated tests it’s high enough quality that I’m not ashamed to release it with my name on it.
A year ago I might have felt guilty about using LLMs to write code for me in this way. I’m over that now: I’m still doing the work, but I now have a powerful tool that can help accelerate the process.
Using this pattern for real work
I’ve since used the same pattern for some smaller modifications to some of my more significant projects. This morning I used it to upgrade my datasette-cors plugin to add support for new features I had added to the underlying asgi-cors library. Here’s the prompt sequence I used:
files-to-prompt ../asgi-cors/asgi_cors.py datasette_cors.py | llm -m opus -s \
'Output a new datasette_cors.py plugin that adds headers and methods and max_age config options'
files-to-prompt test_datasette_cors.py | llm -c \
'Update these tests to exercise the new options as well'
cat README.md | llm -c \
'Update the README to document the new config options'
And the full transcript.
I reviewed this code very carefully before landing it. It’s absolutely what I would have written myself without assistance from Claude.
Time elapsed for this change? The first prompt was logged at 16:42:11 and the last at 16:44:24, so just over two minutes followed by a couple more minutes for the review. The associated issue was open for five minutes total.
Running OCR against PDFs and images directly in your browser 19 days ago
I attended the Story Discovery At Scale data journalism conference at Stanford this week. One of the perennial hot topics at any journalism conference concerns data extraction: how can we best get data out of PDFs and images?
I’ve been having some very promising results with Gemini Pro 1.5, Claude 3 and GPT-4 Vision recently—I’ll write more about that soon. But those tools are still inconvenient for most people to use.
Meanwhile, older tools like Tesseract OCR are still extremely useful—if only they were easier to use as well.
Then I remembered that Tesseract runs happily in a browser these days thanks to the excellent Tesseract.js project. And PDFs can be processed using JavaScript too thanks to Mozilla’s extremely mature and well-tested PDF.js library.
So I built a new tool!
tools.simonwillison.net/ocr provides a single page web app that can run Tesseract OCR against images or PDFs that are opened in (or dragged and dropped onto) the app.
Crucially, everything runs in the browser. There is no server component here, and nothing is uploaded. Your images and documents never leave your computer or phone.
Here’s an animated demo:
It’s not perfect: multi-column PDFs (thanks, academia) will be treated as a single column, illustrations or photos may result in garbled ASCII-art and there are plenty of other edge cases that will trip it up.
But... having Tesseract OCR available against PDFs in a web browser (including in Mobile Safari) is still a really useful thing.
How I built this
For more recent examples of projects I’ve built with the assistance of LLMs, see Building and testing C extensions for SQLite with ChatGPT Code Interpreter and Claude and ChatGPT for ad-hoc sidequests.
I built the first version of this tool in just a few minutes, using Claude 3 Opus.
I already had my own JavaScript code lying around for the two most important tasks: running Tesseract.js against an images and using PDF.js to turn a PDF into a series of images.
The OCR code came from the system I built and explained in How I make annotated presentations (built with the help of multiple ChatGPT sessions). The PDF to images code was from an unfinished experiment which I wrote with the aid of Claude 3 Opus a week ago.
I composed the following prompt for Claude 3, where I pasted in both of my code examples and then added some instructions about what I wanted it to build at the end:
This code shows how to open a PDF and turn it into an image per page:
<!DOCTYPE html> <html> <head> <title>PDF to Images</title> <script src="https://cdnjs.cloudflare.com/ajax/libs/pdf.js/2.9.359/pdf.min.js"></script> <style> .image-container img { margin-bottom: 10px; } .image-container p { margin: 0; font-size: 14px; color: #888; } </style> </head> <body> <input type="file" id="fileInput" accept=".pdf" /> <div class="image-container"></div> <script> const desiredWidth = 800; const fileInput = document.getElementById('fileInput'); const imageContainer = document.querySelector('.image-container'); fileInput.addEventListener('change', handleFileUpload); pdfjsLib.GlobalWorkerOptions.workerSrc = 'https://cdnjs.cloudflare.com/ajax/libs/pdf.js/2.9.359/pdf.worker.min.js'; async function handleFileUpload(event) { const file = event.target.files[0]; const imageIterator = convertPDFToImages(file); for await (const { imageURL, size } of imageIterator) { const imgElement = document.createElement('img'); imgElement.src = imageURL; imageContainer.appendChild(imgElement); const sizeElement = document.createElement('p'); sizeElement.textContent = `Size: ${formatSize(size)}`; imageContainer.appendChild(sizeElement); } } async function* convertPDFToImages(file) { try { const pdf = await pdfjsLib.getDocument(URL.createObjectURL(file)).promise; const numPages = pdf.numPages; for (let i = 1; i <= numPages; i++) { const page = await pdf.getPage(i); const viewport = page.getViewport({ scale: 1 }); const canvas = document.createElement('canvas'); const context = canvas.getContext('2d'); canvas.width = desiredWidth; canvas.height = (desiredWidth / viewport.width) * viewport.height; const renderContext = { canvasContext: context, viewport: page.getViewport({ scale: desiredWidth / viewport.width }), }; await page.render(renderContext).promise; const imageURL = canvas.toDataURL('image/jpeg', 0.8); const size = calculateSize(imageURL); yield { imageURL, size }; } } catch (error) { console.error('Error:', error); } } function calculateSize(imageURL) { const base64Length = imageURL.length - 'data:image/jpeg;base64,'.length; const sizeInBytes = Math.ceil(base64Length * 0.75); return sizeInBytes; } function formatSize(size) { const sizeInKB = (size / 1024).toFixed(2); return `${sizeInKB} KB`; } </script> </body> </html>This code shows how to OCR an image:
async function ocrMissingAltText() { // Load Tesseract var s = document.createElement("script"); s.src = "https://unpkg.com/tesseract.js@v2.1.0/dist/tesseract.min.js"; document.head.appendChild(s); s.onload = async () => { const images = document.getElementsByTagName("img"); const worker = Tesseract.createWorker(); await worker.load(); await worker.loadLanguage("eng"); await worker.initialize("eng"); ocrButton.innerText = "Running OCR..."; // Iterate through all the images in the output div for (const img of images) { const altTextarea = img.parentNode.querySelector(".textarea-alt"); // Check if the alt textarea is empty if (altTextarea.value === "") { const imageUrl = img.src; var { data: { text }, } = await worker.recognize(imageUrl); altTextarea.value = text; // Set the OCR result to the alt textarea progressBar.value += 1; } } await worker.terminate(); ocrButton.innerText = "OCR complete"; }; }Use these examples to put together a single HTML page with embedded HTML and CSS and JavaScript that provides a big square which users can drag and drop a PDF file onto and when they do that the PDF has every page converted to a JPEG and shown below on the page, then OCR is run with tesseract and the results are shown in textarea blocks below each image.
I saved this prompt to a prompt.txt
file and ran it using my llm-claude-3 plugin for LLM:
llm -m claude-3-opus < prompt.txt
It gave me a working initial version on the first attempt!
Here’s the full transcript, including my follow-up prompts and their responses. Iterating on software in this way is so much fun.
First follow-up:
Modify this to also have a file input that can be used—dropping a file onto the drop area fills that input
make the drop zone 100% wide but have a 2em padding on the body. it should be 10em high. it should turn pink when an image is dragged over it.
Each textarea should be 100% wide and 10em high
At the very bottom of the page add a h2 that says Full document—then a 30em high textarea with all of the page text in it separated by two newlines
Here’s the interactive result.
Rather delightfully it used the neater pattern where the file input itself is hidden but can be triggered by clicking on the large drop zone, and it updated the copy on the drop zone to reflect that—without me suggesting those requirements.
And then:
get rid of the code that shows image sizes. Set the placeholder on each textarea to be Processing... and clear that placeholder when the job is done.
I realized it would be useful if it could handle non-PDF images as well. So I fired up ChatGPT (for no reason other than curiosity to see how well it did) and got GPT-4 to add that feature for me. I pasted in the code so far and added:
Modify this so jpg and png and gif images can be dropped or opened too—they skip the PDF step and get appended to the page and OCRd directly. Also move the full document heading and textarea above the page preview and hide it u til there is data to be shown in it
Then I spotted that the Tesseract worker was being created multiple times in a loop, which is inefficient—so I prompted:
Create the worker once and use it for all OCR tasks and terminate it at the end
I’d tweaked the HTML and CSS a little before feeding it to GPT-4, so now the site had a title and rendered in Helvetica.
Here’s the version GPT-4 produced for me.
Manual finishing touches
Fun though it was iterating on this project entirely through prompting, I decided it would be more productive to make the finishing touches myself. You can see those in the commit history. They’re not particularly interesting:
- I added Plausible analytics (which I like because they use no cookies).
- I added better progress indicators, including the text that shows how many pages of the PDF have been processed so far.
- I bumped up the width of the rendered PDF page images from 800 to 1000. This seemed to improve OCR quality—in particular, the Claude 3 model card PDF now has less OCR errors than it did before.
- I upgraded both Tesseract.js and PDF.js to the most recent versions. Unsurprisingly, Claude 3 Opus had used older versions of both libraries.
I’m really pleased with this project. I consider it finished—it does the job I designed it to do and I don’t see any need to keep on iterating on it. And because it’s all static JavaScript and WebAssembly I expect it to continue working effectively forever.
Update: OK, a few more features: I added language selection, paste support and some basic automated tests using Playwright Python.
llm cmd undo last git commit—a new plugin for LLM 23 days ago
I just released a neat new plugin for my LLM command-line tool: llm-cmd. It lets you run a command to to generate a further terminal command, review and edit that command, then hit <enter>
to execute it or <ctrl-c>
to cancel.
This is an alpha release. It’s a very dangerous piece of software! Do not use this unless you are fluent in terminal and confident that you understand what it’s doing for you and what could go wrong. I take no responsibility if you accidentally delete all of your files with this tool.
To try this out, you’ll need my LLM tool installed:
brew install llm # 'pipx install llm' works too
llm keys set openai
<paste in your OpenAI API key>
Now install the new plugin:
llm install llm-cmd
To run the new command, type llm cmd
and then type what you want to do.
Here’s an example of how to use it:
llm cmd show the first three lines of every file in this directory
I ran this just now and it gave me the following:
head -n 3 *
Crucially, it will not excute that command directly. It pre-populates your terminal with the command, and you can edit it before hitting <enter>
to run it (or cancel with <ctrl-c>
).
Here’s an animated GIF demo showing it in action:
It has a couple of options: you can add -m gpt-4
to run against a different model (it defaults to the default configured for LLM). You can also pass -s/--system
to set a different system prompt to change how it behaves.
The default system prompt took a little bit of iteration. Here’s the instruction it passes to the model, along with your input:
Return only the command to be executed as a raw string, no string delimiters wrapping it, no yapping, no markdown, no fenced code blocks, what you return will be passed to subprocess.check_output() directly.
For example, if the user asks: undo last git commit
You return only: git reset --soft HEAD~1
I really like “no yapping” as a brief way to encourage a model not to include lengthy explanatory text!
So far I’ve tried this prompt against gpt-3.5-turbo
, GPT-4, Claude 3 Opus and Claude 3 Haiku (using my llm-claude-3 plugin) and it gave me the desired result for all four models. I’m sure there are many ways this could be improved, that likely vary depending on the model you’re using. That’s why this is an alpha!
Built with assistance from ChatGPT/GPT-4
The key feature that enables this plugin is the ability to populate the user’s terminal with text that they can edit before they execute it.
I didn’t know how to do this, so I asked GPT-4:
Write a Python function interactive_exec(“ls -lah”)
It makes that string available for the user to edit in the terminal—and when they hit enter it executes the command in their shell
It didn’t give me what I wanted at first (it suggested just using input("Edit command: ")
), so I asked a follow-up:
Show more options, I’m not sure that would let them directly edit the existing text
And it spat this out, which was exactly what I wanted:
import readline import subprocess def interactive_exec(command): # Set the initial text for the input readline.set_startup_hook(lambda: readline.insert_text(command)) try: edited_command = input("Edit command: ") output = subprocess.check_output(edited_command, shell=True, stderr=subprocess.STDOUT) print(output.decode()) except subprocess.CalledProcessError as e: print(f"Command failed with error: {e.output.decode()}") finally: # Remove the startup hook to avoid affecting future inputs readline.set_startup_hook(None) # Example usage interactive_exec("ls -lah")
I tracked down the documentation for readline.set_startup_hook() and readline.insert_text() to make sure I understood what they were doing. This turned out to be the exact thing I needed to know.
llm cmd undo last git commit
This is my favourite example, because I’ve been using Git for 15+ years and I still can’t ever remember the exact command for this.
Here’s what llm cmd
spits out:
$ llm cmd undo last git commit
git reset --soft HEAD~1
It should always get this one right, because it’s the example I provided in the system prompt!
Building and testing C extensions for SQLite with ChatGPT Code Interpreter 26 days ago
I wrote yesterday about how I used Claude and ChatGPT Code Interpreter for simple ad-hoc side quests—in that case, for converting a shapefile to GeoJSON and merging it into a single polygon.
Today I have a much more ambitious example.
I was thinking this morning about vector similarity, and how I really like the pattern of storing encoded floating point vectors in BLOB columns in a SQLite database table and then using a custom SQL function to decode them and calculate cosine similarity between them.
I’ve written code for this a few times in Python, with Python functions that get registered with SQLite as custom SQL functions. Here’s an example from my LLM tool.
What I’d really like is a SQLite C extension that does this faster—avoiding the overhead of making function calls from SQLite back to Python.
Then I remembered that ChatGPT Code Interpreter has Python, SQLite and access to gcc
. Could I get it to build and test that C extension for me, entirely within its own environment?
It turns out that works!
Absurdly, the first step is getting ChatGPT in the right “mood”
One of the infuriating things about working with ChatGPT Code Interpreter is that it often denies abilities that you know it has.
I’ve found it to be quite resistant to compiling C code in the past. Here’s a prompting sequence trick that usually works for me:
Use your code interpreter tool to show me the version of your Python and SQLite
It generated and ran this code:
import sqlite3 import sys python_version = sys.version sqlite_version = sqlite3.sqlite_version python_version, sqlite_version
Which output:
('3.11.8 (main, Mar 12 2024, 11:41:52) [GCC 12.2.0]', '3.40.1')
Next we need it to acknowledge that it has access to gcc
:
Now use subprocess.run() to call “gcc --version” and tell me that version
If all goes well it will run something like this:
import subprocess result = subprocess.run(["gcc", "--version"], capture_output=True, text=True) gcc_version = result.stdout.split('\n')[0] gcc_version
Outputting:
'gcc (Debian 12.2.0-14) 12.2.0'
This may be enough for it to start happily compiling C code (which it can do with subsequent calls to gcc
). If not, a trick I’ve used successfully in the past is “try running this command: gcc helloworld.c—and show me the error message you get”. But hopefully that won’t be necessary.
Compiling a basic SQLite extension
If you ask it for a SQLite extension it will default to typing out the code for you to copy and paste elsewhere. We want that code saved to its own disk so it can run a compiler later.
Try this:
Write a SQLite extension in C that provides a math_sin(a) function. Save that extension file to disk, then give me a link to download it.
We don’t actually care about downloading the file, but this provides an excuse (do we need one? I don’t know!) for saving it to disk.
It ran this:
sin_extension_c = """ #include <sqlite3ext.h> SQLITE_EXTENSION_INIT1 #include <math.h> static void math_sin(sqlite3_context *context, int argc, sqlite3_value **argv) { double input = sqlite3_value_double(argv[0]); double result = sin(input); sqlite3_result_double(context, result); } #ifdef _WIN32 __declspec(dllexport) #endif int sqlite3_sin_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi) sqlite3_create_function(db, "math_sin", 1, SQLITE_UTF8, NULL, math_sin, NULL, NULL); return 0; } """ with open("/mnt/data/sin_extension.c", "w") as file: file.write(sin_extension_c) "/mnt/data/sin_extension.c"
And gave me a link to download the file.
Next, I prompted:
Now use subprocess.run to compile it with gcc, like how you showed me the gcc version earlier
I’m trying to avoid it claiming it can’t compile code by referencing back to the earlier successful invocation of gcc --version
.
When I tried this just now it worked without complaining... but found a compilation bug in the C code:
compile_command = ["gcc", "-fPIC", "-shared", "-o", "/mnt/data/sin_extension.so", "/mnt/data/sin_extension.c"] result = subprocess.run(compile_command, capture_output=True, text=True) result.stdout, result.stderr
Output:
(
'',
'/mnt/data/sin_extension.c: In function ‘sqlite3_sin_init’:\n/mnt/data/sin_extension.c:17:61: error: ‘NULL’ undeclared (first use in this function)\n 17 | sqlite3_create_function(db, "math_sin", 1, SQLITE_UTF8, NULL, math_sin, NULL, NULL);\n | ^~~~\n/mnt/data/sin_extension.c:5:1: note: ‘NULL’ is defined in header ‘<stddef.h>’; did you forget to ‘#include <stddef.h>’?\n 4 | #include <math.h>\n +++ |+#include <stddef.h>\n 5 | \n/mnt/data/sin_extension.c:17:61: note: each undeclared identifier is reported only once for each function it appears in\n 17 | sqlite3_create_function(db, "math_sin", 1, SQLITE_UTF8, NULL, math_sin, NULL, NULL);\n | ^~~~\n')
)
ChatGPT added:
It looks like there was an error during the compilation process. The error message suggests that
NULL
is undeclared because the<stddef.h>
header file, which definesNULL
, was not included.I’ll update the C code to include this header and attempt the compilation again.
It said it was going to update the C code but didn’t actually do that at all—it just stopped. So I said:
Do that
And it did! Here’s the code it successfully ran:
# Update the C code to include <stddef.h> sin_extension_c_updated = """ #include <sqlite3ext.h> SQLITE_EXTENSION_INIT1 #include <math.h> #include <stddef.h> static void math_sin(sqlite3_context *context, int argc, sqlite3_value **argv) { double input = sqlite3_value_double(argv[0]); double result = sin(input); sqlite3_result_double(context, result); } #ifdef _WIN32 __declspec(dllexport) #endif int sqlite3_sin_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi) sqlite3_create_function(db, "math_sin", 1, SQLITE_UTF8, NULL, math_sin, NULL, NULL); return 0; } """ with open("/mnt/data/sin_extension.c", "w") as file: file.write(sin_extension_c_updated) # Compile the updated C code compile_command_updated = ["gcc", "-fPIC", "-shared", "-o", "/mnt/data/sin_extension.so", "/mnt/data/sin_extension.c"] result_updated = subprocess.run(compile_command_updated, capture_output=True, text=True) result_updated.stdout, result_updated.stderr
Both stdout
and stderr
were blank, indicating a successfully compiled SQLite extension!
Trying it out in Python
With the SQLite extension compiled the next step is to see if it actually works. I said:
Now load that extension into SQLite in Python and try it out
It wrote and then ran the following:
import sqlite3 # Connect to an in-memory SQLite database conn = sqlite3.connect(":memory:") conn.enable_load_extension(True) # Load the compiled extension conn.load_extension("/mnt/data/sin_extension.so") # Test the math_sin function cursor = conn.cursor() cursor.execute("SELECT math_sin(1.57079632679)") # sin(pi/2) should be close to 1 result = cursor.fetchone() result[0]
... and it got an error:
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
Cell In[6], line 8
5 conn.enable_load_extension(True)
7 # Load the compiled extension
----> 8 conn.load_extension("/mnt/data/sin_extension.so")
10 # Test the math_sin function
11 cursor = conn.cursor()
OperationalError: /mnt/data/sin_extension.so: undefined symbol: sqlite3_sinextension_init
Here’s where the real magic of Code Interpreter kicked in. Without any further interaction from me, it said the following:
It looks like there was an error loading the extension into SQLite. The error message indicates that the symbol
sqlite3_sinextension_init
is undefined. This is likely because the entry point function for the extension is not named correctly.In the C code, the entry point function is named
sqlite3_sin_init
. However, SQLite expects the entry point function to be namedsqlite3_extension_init
orsqlite3_<extension_name>_init
. In this case, the expected name would besqlite3_sinextension_init
.I’ll update the C code to rename the entry point function to
sqlite3_sinextension_init
and recompile the extension.
# Update the C code to rename the entry point function sin_extension_c_final = """ #include <sqlite3ext.h> SQLITE_EXTENSION_INIT1 #include <math.h> #include <stddef.h> static void math_sin(sqlite3_context *context, int argc, sqlite3_value **argv) { double input = sqlite3_value_double(argv[0]); double result = sin(input); sqlite3_result_double(context, result); } #ifdef _WIN32 __declspec(dllexport) #endif int sqlite3_sinextension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi) sqlite3_create_function(db, "math_sin", 1, SQLITE_UTF8, NULL, math_sin, NULL, NULL); return 0; } """ with open("/mnt/data/sin_extension.c", "w") as file: file.write(sin_extension_c_final) # Recompile the updated C code compile_command_final = ["gcc", "-fPIC", "-shared", "-o", "/mnt/data/sin_extension.so", "/mnt/data/sin_extension.c"] result_final = subprocess.run(compile_command_final, capture_output=True, text=True) result_final.stdout, result_final.stderr
The compilation succeeded... so it said:
The updated SQLite extension has been successfully recompiled with the correct entry point function name. Let’s try loading the extension into SQLite again and test the
math_sin
function.
Then it ran this:
# Reconnect to the in-memory SQLite database conn.close() conn = sqlite3.connect(":memory:") conn.enable_load_extension(True) # Load the recompiled extension conn.load_extension("/mnt/data/sin_extension.so") # Test the math_sin function again cursor = conn.cursor() cursor.execute("SELECT math_sin(1.57079632679)") # sin(pi/2) should be close to 1 result = cursor.fetchone() result[0]
And it worked! The output was:
1.0
ChatGPT has now written a working SQLite extension in C, compiled it, loaded it into Python and tested it for us!
Here’s the full transcript of this ChatGPT session.
Something more ambitious: vector comparison functions
My actual goal here was more ambitious: I wanted a SQL function that could calculate cosine similarity between two vectors stored as BLOBs in SQLite.
I won’t provide a blow-by-blow account of how I got there, but I started with this prompt:
def encode(values): return struct.pack("<" + "f" * len(values), *values) def decode(binary): return struct.unpack("<" + "f" * (len(binary) // 4), binary) def cosine_similarity(a, b): dot_product = sum(x * y for x, y in zip(a, b)) magnitude_a = sum(x * x for x in a) ** 0.5 magnitude_b = sum(x * x for x in b) ** 0.5 return dot_product / (magnitude_a * magnitude_b)These are Python functions for working with vectors that are stored in SQLite as BLOBs where each BLOB value is a sequence of floating point numbers as binary
Write a new SQLite extension in C that provides three SQL functions:
vector_decode(blob) -> returns a string that is a JSON formatted array of floats, eg “[1.1, 2.1, 3.5]”
vector_encode(string_of_json) -> returns a binary blob for that string. This does not need to use a full JSON parser, it just needs to work with an array that starts with [ and ends with ] and has comma separated floats, ignoring whitespace
vector_similarity(blob1, blob2) -> returns floating point cosine similarity for those two encoded vectors
Write it as a file on disk, then compile it and try it out
I pasted in my existing Python code and told it to write me a SQLite extension based on that code.
I do this kind of thing a lot: prompting LLMs with code examples, often written in different languages. Code is a really good way to communicate requirements with them.
This kicked off a frustrating sequence of interactions. It wrote the extension as a file called vector_extension.c
, compiled it, hit a bug, then wrote a fix in a new file called vector_extension_fixed.c
.
But... when it tried to compile the new file, it hit errors because the C init function no longer matched the filename. So, like someone rolling their own version control based on duplicated files, it created vector_extension_final_fixed.c
and that broke for the same reason...
When it got to vector_extension_final_corrected.c
I took pity on it and cut it off!
This is a classic Code Interpreter problem: an error loop, where it keeps on trying but making variants of the same mistake, and (being a big bag of next-token-predicting matrix algebra) doesn’t have the ability to learn from and correct itself.
Here’s the full transcript of that conversation.
Clearing state and continuing
The best way out of an error loop is to clear state and start again.
I opened a brand new ChatGPT session and fed it a copy of the code it had previously written that I thought was most likely to work—literally pasting it into the prompt directly:
Save this to a file called /mnt/data/vector.c and then give me a link to download the file:
#include <sqlite3ext.h> #include <math.h> #include <stdlib.h> #include <string.h> #include <stdio.h> SQLITE_EXTENSION_INIT1 static void vector_decode(sqlite3_context *context, int argc, sqlite3_value **argv) { const unsigned char *blob = sqlite3_value_blob(argv[0]); int size = sqlite3_value_bytes(argv[0]) / sizeof(float); float *values = (float *)blob; char *result = malloc(size * 12 + 2); // Max 10 digits per float + comma + space char *ptr = result; *ptr++ = '['; for (int i = 0; i < size; ++i) { if (i > 0) { *ptr++ = ','; *ptr++ = ' '; } ptr += sprintf(ptr, "%.2f", values[i]); } *ptr++ = ']'; *ptr = '\0'; sqlite3_result_text(context, result, -1, SQLITE_TRANSIENT); free(result); } static void vector_encode(sqlite3_context *context, int argc, sqlite3_value **argv) { const char *json = (const char *)sqlite3_value_text(argv[0]); int count = 0; const char *ptr = json; while (*ptr) { if (*ptr == ',') ++count; ++ptr; } ++count; // Number of floats is one more than the number of commas float *values = malloc(count * sizeof(float)); ptr = json + 1; // Skip the opening '[' for (int i = 0; i < count; ++i) { values[i] = strtof(ptr, (char **)&ptr); ++ptr; // Skip the comma or closing ']' } sqlite3_result_blob(context, values, count * sizeof(float), SQLITE_TRANSIENT); free(values); } static void vector_similarity(sqlite3_context *context, int argc, sqlite3_value **argv) { const float *a = (const float *)sqlite3_value_blob(argv[0]); const float *b = (const float *)sqlite3_value_blob(argv[1]); int size = sqlite3_value_bytes(argv[0]) / sizeof(float); float dot_product = 0, magnitude_a = 0, magnitude_b = 0; for (int i = 0; i < size; ++i) { dot_product += a[i] * b[i]; magnitude_a += a[i] * a[i]; magnitude_b += b[i] * b[i]; } magnitude_a = sqrtf(magnitude_a); magnitude_b = sqrtf(magnitude_b); float similarity = dot_product / (magnitude_a * magnitude_b); sqlite3_result_double(context, similarity); } #ifdef _WIN32 __declspec(dllexport) #endif int sqlite3_vector_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi) sqlite3_create_function(db, "vector_decode", 1, SQLITE_UTF8, NULL, vector_decode, NULL, NULL); sqlite3_create_function(db, "vector_encode", 1, SQLITE_UTF8, NULL, vector_encode, NULL, NULL); sqlite3_create_function(db, "vector_similarity", 2, SQLITE_UTF8, NULL, vector_similarity, NULL, NULL); return SQLITE_OK; }
Then I used the “Use your code interpreter tool to show me the version of your Python and SQLite” trick, then “Now use subprocess.run() to call ”gcc --version“ and tell me that version”, and then:
Now run this: [“gcc”, “-shared”, “-fPIC”, “-o”, “/mnt/data/vector.so”, “/mnt/data/vector.c”, “-lsqlite3”]
That’s the compile command the previous session had been using—I figured just copying that across would likely work.
And it did!
# Compile the C code into a shared library using GCC compile_result = subprocess.run(["gcc", "-shared", "-fPIC", "-o", "/mnt/data/vector.so", "/mnt/data/vector.c", "-lsqlite3"], capture_output=True, text=True) # Check if there were any errors during compilation if compile_result.returncode != 0: compilation_error = compile_result.stderr else: compilation_error = None compilation_error
I prompted:
Load the resulting extension into SQLite in Python and try out the new functions
This was the moment of truth. Would it work? Would it even know how to use the new functions, given all it had seen so far was their implementation in C?
Here’s what it did:
import json # Connect to an in-memory SQLite database conn = sqlite3.connect(":memory:") conn.enable_load_extension(True) # Load the compiled shared library conn.load_extension("/mnt/data/vector.so") # Create a sample table with vector data conn.execute("CREATE TABLE vectors (id INTEGER PRIMARY KEY, vector BLOB)") conn.execute("INSERT INTO vectors (vector) VALUES (vector_encode('[1.0, 2.0, 3.0]'))") conn.execute("INSERT INTO vectors (vector) VALUES (vector_encode('[3.0, 2.0, 1.0]'))") # Test the vector_decode function decoded_vector = conn.execute("SELECT vector_decode(vector) FROM vectors").fetchall() decoded_vector = [json.loads(row[0]) for row in decoded_vector] # Test the vector_similarity function similarity = conn.execute("SELECT vector_similarity(vector, vector) FROM vectors").fetchall() similarity = [row[0] for row in similarity] decoded_vector, similarity
Output:
([[1.0, 2.0, 3.0], [3.0, 2.0, 1.0]], [0.9999999403953552, 0.9999999403953552])
And it added commentary:
The extension was successfully loaded into SQLite, and the new functions were tested:
- The
vector_decode
function successfully decoded the vectors from the database, resulting in[[1.0, 2.0, 3.0], [3.0, 2.0, 1.0]]
.- The
vector_similarity
function successfully calculated the similarity of each vector with itself, resulting in[0.9999999403953552, 0.9999999403953552]
, which is essentially 1.0 as expected for identical vectors.
We got there! I now have a full, working prototype of a SQLite C extension for vector comparison functions, and I didn’t have to write a single line of C code myself.
I continued prompting—I got it to do a performance comparison against the original Python implementation, and even had it brainstorm potential security bugs in the C code and try to exploit a few of them. Take a look at the full transcript of that session for details.
It runs on macOS too
With a few extra hints from ChatGPT (I asked how to compile it on a Mac), I downloaded that vector.c
file to my laptop and got the following to work:
/tmp % mv ~/Downloads/vector.c .
/tmp % gcc -shared -fPIC -o vector.dylib -I/opt/homebrew/Cellar/sqlite/3.45.1/include vector.c -lsqlite3
/tmp % python
Python 3.10.10 (main, Mar 21 2023, 13:41:05) [Clang 14.0.6 ] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connect(":memory:")
>>> conn.enable_load_extension(True)
>>> conn.load_extension("/tmp/vector.dylib")
>>> conn.execute("CREATE TABLE vectors (id INTEGER PRIMARY KEY, vector BLOB)")
<sqlite3.Cursor object at 0x1047fecc0>
>>> conn.execute("INSERT INTO vectors (vector) VALUES (vector_encode('[1.0, 2.0, 3.0]'))")
<sqlite3.Cursor object at 0x1047fee40>
>>> conn.execute("INSERT INTO vectors (vector) VALUES (vector_encode('[3.0, 2.0, 1.0]'))")
<sqlite3.Cursor object at 0x1047fecc0>
>>> decoded_vector = conn.execute("SELECT vector_decode(vector) FROM vectors").fetchall()
>>> decoded_vector
[('[1.00, 2.00, 3.00]',), ('[3.00, 2.00, 1.00]',)]
So I’ve now seen that C extension run on both Linux and macOS.
I did this whole project on my phone
Here’s the thing I enjoy most about using Code Interpreter for these kinds of prototypes: since the prompts are short, and there’s usually a delay of 30s+ between each prompt while it does its thing, I can do the whole thing on my phone while doing other things.
In this particular case I started out in bed, then got up, fed the dog, made coffee and pottered around the house for a bit—occasionally glancing back at my screen and poking it in a new direction with another prompt.
This almost doesn’t count as a project at all. It began as mild curiosity, and I only started taking it seriously when it became apparent that it was likely to produce a working result.
I only switched to my laptop right at the end, to try out the macOS compilation steps.
Total time invested: around an hour, but that included various other morning activities (coffee, dog maintenance, letting out the chickens.)
Which leads to the dilemma that affects so many of my weird little ChatGPT experiments:
The dilemma: do I finish this project?
Thanks to Code Interpreter I now have a working prototype of something I would never have attempted to build on my own. My knowledge of C is thin enough that I don’t remotely have the confidence to try something like this myself.
Taking what I’ve got so far and turning it into code that I would feel responsible using—and sharing with other people—requires the following:
- I need to manually test it really thoroughly. I haven’t actually done the work to ensure it’s returning the right results yet!
- I need to make sure I understand every line of C code that it’s written for me
- I then need to review that code, and make sure it’s sensible and logic-error-free
- I need to audit it for security
- I need to add comprehensive automated tests
I should probably drop the vector_encode()
and vector_decode()
functions entirely—parsing a JSON-like string in C is fraught with additional risk already, and those aren’t performance critical—just having a fast vector_similarity()
function that worked against BLOBs would give me the performance gain I’m looking for.
All of this is a lot of extra work. ChatGPT can help me in various ways with each of those steps, but it’s still on me to do the work and make absolutely sure that I’m confident in my understanding beyond just what got hallucinated at me by a bunch of black-box matrices.
This project was not in my plans for the weekend. I’m not going to put that work in right now—so “SQLite C extension for vector similarity” will be added to my ever-growing list of half-baked ideas that LLMs helped me prototype way beyond what I would have been able to do on my own.
So I’m going to blog about it, and move on. I may well revisit this—the performance gains over my Python functions looked to be 16-83x (according to a benchmark that ChatGPT ran for me which I have not taken the time to verify) which is a very material improvement. But for the moment I have so many other things I need to prioritize.
If anyone else wants to take this and turn it into something usable, please be my guest!
Bonus: haversine() in C
I took Cleo for a walk on the beach and had the idea to try implementing a haversine(lat1, lon1, lat2, lon2)
SQL function in C, for fast calculation of the approximate distance between two points on earth. This could enable fast-enough brute force “points closest to X, Y” searches against medium sized tables of locations.
Here’s the full transcript and the loosely tested C extension code that resulted from the session— once again, all created using the ChatGPT iPhone app to interact with Code Interpreter:
#include <sqlite3ext.h>
#include <math.h>
#include <stddef.h> // Add this line
SQLITE_EXTENSION_INIT1
static double deg2rad(double deg) {
return deg * (M_PI / 180);
}
static void haversine(sqlite3_context *context, int argc, sqlite3_value **argv) {
double lat1 = sqlite3_value_double(argv[0]);
double lon1 = sqlite3_value_double(argv[1]);
double lat2 = sqlite3_value_double(argv[2]);
double lon2 = sqlite3_value_double(argv[3]);
double earth_radius = 6371000; // meters
double dLat = deg2rad(lat2 - lat1);
double dLon = deg2rad(lon2 - lon1);
double a = sin(dLat / 2) * sin(dLat / 2) +
cos(deg2rad(lat1)) * cos(deg2rad(lat2)) *
sin(dLon / 2) * sin(dLon / 2);
double c = 2 * atan2(sqrt(a), sqrt(1 - a));
double distance = earth_radius * c;
sqlite3_result_int(context, (int)round(distance));
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_extension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
SQLITE_EXTENSION_INIT2(pApi)
sqlite3_create_function(db, "haversine", 4, SQLITE_UTF8, NULL, haversine, NULL, NULL);
return 0;
}
Since this one boils down to just floating point numbers I may even attempt to turn this into production code!