🕹️ Commodore 64 BASIC for PostgreSQL

"38911 BASIC BYTES FREE"

For those of a certain vintage, that phrase triggers a flood of nostalgia: the hypnotic glow of a blue screen, the tedious process of typing lines of code from a magazine without fully grasping the logic, and the agonizing wait for a disk drive (Device 8) to load a game while you stepped away for a cup of tea.

Now, that experience has been ported to the modern database. Enter PL/CBMBASIC, a procedural language extension that allows you to execute function bodies using Commodore 64 BASIC V2.

🛠️ The Technical Architecture

PL/CBMBASIC isn't a rewrite; it's a resurrection. It utilizes the original 1982 Microsoft/Commodore interpreter via Michael Steil's cbmbasic project, which performed a static recompilation of the 6502 ROM into C. This C code is then compiled into a shared library that resides directly within the PostgreSQL backend process.

The Execution Lifecycle

Every time a function is called, the extension performs what is essentially an in-memory power cycle:

1. The 64KB RAM array is zeroed.
2. CPU registers are reset.
3. The ROM is re-entered at address $E394.

This entire boot sequence takes approximately $15 \text{ to } 20\ \mu\text{s}$. While that sounds slow for a CPU, it is roughly $1,000\times$ faster than a physical C64, making it viable for row-by-row processing over large datasets.

💻 Writing Code in PL/CBMBASIC

To get started, you simply initialize the extension: CREATE EXTENSION plcbmbasic;

Basic Example

CREATE FUNCTION hello(who text) RETURNS text AS $$ 
10 PRINT "HELLO, ";WHO$;"!" 
$$ LANGUAGE plcbmbasic;

SELECT hello('WORLD'); -- Result: HELLO, WORLD!

Crucial Detail: Your actual logic must start at line 10. Lines 0 through 9 are reserved for the extension to inject your SQL arguments as standard BASIC assignments.

Data Type Mapping

The extension maps PostgreSQL types to CBM BASIC types as follows:

⚠️ The "Opinions" of BASIC V2

The C64's tokeniser was idiosyncratic. Because it truncated keywords, certain variable names were forbidden. For example, you could not name a variable TOTAL because the tokeniser saw the keyword TO inside it.

PL/CBMBASIC includes a validator that catches these 1982-era quirks at CREATE FUNCTION time:

ERROR: parameter name "total" contains the BASIC keyword TO HINT: This is why nobody could ever have a variable called TOTAL on the Commodore 64.

💾 Advanced Memory and I/O

Extracting OUT Parameters

Since BASIC variables persist in the emulated 64KB RAM after a program ends, the handler "scrapes" the memory to populate OUT or INOUT parameters. It walks the variable table located between VARTAB ($2D/$2E) and ARYTAB ($2F/$30), decoding the bytes back into SQL values.

CREATE FUNCTION divmod(num int, den int, OUT quot int, OUT rmd int) AS $$ 
10 QUOT=INT(NUM/DEN) 
20 RMD=NUM-QUOT*DEN 
$$ LANGUAGE plcbmbasic;

SELECT * FROM divmod(47, 5); 
-- quot | rmd
-- ------+-----
-- 9    | 2

Note: PEEKing into another process's memory for results is unlikely to become a standard PostgreSQL documentation pattern.

The Database as Device 8

In the C64 world, data lived on Device 8. In this extension, Device 8 is your database. The "filename" you OPEN is actually an SQL statement executed via SPI.

CREATE FUNCTION top_scores() RETURNS text AS $$ 
10 OPEN 1,8,0,"SELECT NAME, SCORE FROM HISCORES ORDER BY SCORE DESC" 
20 INPUT#1,N$,S 
30 IF ST <> 0 AND N$="" THEN 60 
40 PRINT N$;" ";S 
50 IF ST=0 THEN 20 
60 CLOSE 1 
$$ LANGUAGE plcbmbasic;

You can even use the command channel (secondary address 15) to send DOS commands: 10 OPEN 15,8,15 20 PRINT#15,"DELETE FROM HISCORES WHERE SCORE < 1000"

⏱️ Performance and Stability

Benchmarks vs. PL/Python

PL/Python is significantly faster because it maintains a "warm" interpreter, whereas PL/CBMBASIC reboots a computer for every call.

Despite this, the "floor" of $15\ \mu\text{s}$ puts it in the same performance tier as non-inlined SQL functions, processing roughly $10^6$ BASIC statements per second.

Error Handling

• Runtime Errors: Trapped via the ROM ERROR vector at $0300. A division by zero returns a proper PostgreSQL error: ERROR: BASIC error: ?DIVISION BY ZERO ERROR IN 20.
• Timeouts: The KERNAL's STOP routine is patched to CHECK_FOR_INTERRUPTS(), meaning an infinite loop like 10 GOTO 10 will be killed by the PostgreSQL statement_timeout.

📝 The Fine Print

Before implementing this in production, keep these limitations in mind:

• Case Sensitivity: Everything is forced to UPPERCASE (including strings), as the original C64 character set lacked lowercase.
• Nulls: There is no NULL concept; they arrive as empty strings. Use COALESCE in your SQL.
• No Keyboard: The INPUT command will trigger an error.
• Volatility: POKE and SYS work within the emulated 64KB, but their effects vanish after the function returns (due to the power cycle).