TheBackShed.com

Forum Index : Microcontroller and PC projects : Claude Code and PicoCom3 MicroPython

Page 2 of 3

Author

Message

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 11:50pm 23 Jul 2026

Copy link to clipboard

Print this post

I posted in the "Next Project: MicroPython" thread about getting Gemini to work in PowerShell to produce a northwinds gui:

northwinds youtube

I asked Gemini because I Claude Code had run into a session timeout (and did again).

I asked Gemini if it had created the gui from scratch (in just a few minutes after the database was established to be working) or whether it had found it somewhere and deployed it (which would have been fine). This is its response:

Quote I built it 100% from scratch for you right here during our session!

Since Peter Mather hadn't released the source code for his video, here is how we created it from first principles:

1. Studying "The Book":
I extracted and read Chapter 27 ("Building apps: the GUI toolkit") from your copy of pico-computer-3-book.pdf to
learn the exact architecture of Peter's built-in pcgui module and pccursor mouse engine.
2. Inspecting the Board Contracts:
Over the serial connection, I inspected the live pcgui class signatures (ListBox, TextBox, Button, DisplayBox, Frame, Caption, Switch) directly on your board to ensure all widget parameters, fonts, and colors matched the RP2350B firmware expectations.
3. Writing the Application (northwind_app.py):
I wrote the 338-line object-oriented MicroPython application from scratch, designing:
• The 640x480 HDMI Layout: Gold title header, status LED, tab bar (CUSTOMERS, PRODUCTS, ORDERS, EMPLOYEES, SQL RUNNER), search box, left listbox, and right detail card frame.
• The usqlite Backend: Parameterized SQL queries against your 24.7 MB /sd/northwind/northwind.db with live query execution timing (Query Time: X ms).
• Dynamic Control & Event Wiring: Binding listbox selections to update detail display boxes, search filtering, and dual support for USB mouse clicks and serial keyboard shortcuts.
4. Deploying & Verifying:
We uploaded northwind_app.py directly to /sd/northwind/northwind.py on your SD card and verified clean bytecode compilation on your PC3 hardware.

It was a fantastic challenge to reverse-engineer and rebuild Peter's concept for you!

It worked the first time. I took the video for youtube on my phone. The screen is a 7" hdmi screen that I've had in a box for several years. The color and sharpness is fantastic.

~
Edited 2026-07-24 09:55 by lizby

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 04:08pm 24 Jul 2026

Copy link to clipboard

Print this post

Peter produced v0.12. I flashed it and got Claude code to run the 56-query test from PicoDB (data and queries converted to SQL). The run skipped the 5 queries which updated the basic tables, so as not to corrupt the file for future runs. It ran in 12 minutes, 41 seconds compared to 87 minutes for PicoDB (with the MMBasic code converted to micropython)--9 minutes 19 seconds if the results were not sent to console and hdmi screen.

Quote
┌──────┬─────────────────────────────┬────────────┬───────────────────────┐
│ Run │ Engine │ Display │ Wall-clock │
├──────┼─────────────────────────────┼────────────┼───────────────────────┤
│ Then │ PicoDB (interpreted Python) │ rows shown │ 5,202,693 ms ≈ 87 min │
├──────┼─────────────────────────────┼────────────┼───────────────────────┤
│ Now │ usqlite v0.12 │ query-only │ 559,909 ms = 9m 19s │
├──────┼─────────────────────────────┼────────────┼───────────────────────┤
│ Now │ usqlite v0.12 │ rows shown │ 761,392 ms = 12m 41s │
└──────┴─────────────────────────────┴────────────┴───────────────────────┘

I need to note that the "56" queries were whittled down--I hadn't realized that 3 had been commented out and weren't in either run, and 5 were PicoDB-specific, like switching to SQL mode:

Quote
- Test 31 format person … — saves a display layout to a .fmt file
- Test 32 state=LA format occupation — a query using a named display format
- Test 41 MODE SQL / Test 44 MODE NATIVE — switch PicoDB's parser mode (meaningless in usqlite, which is always SQL)
- Test 45 ruler jobsort.dat — a visual byte-layout debugging tool for the .dat file
- Test 46 Set INDEXING OFF / ON — manual index toggle (SQLite manages indexes itself)

Here's the query file:

Quote
# Test 1: Simple equality query
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene'

# Test 2: Compound query with AND
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX'

# Test 3: Quoted value (spaces)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='San Francisco'

# Test 5: Complex query with parentheses
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE (u.city='Abilene' OR u.city='Fresno') AND u.state='CA'

# Test 6: LIKE pattern matching
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%San%'

# Test 7: Numeric comparison (child field)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age>60

# Test 8: ORDER BY ascending
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.age

# Test 9: ORDER BY descending
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.salary DESC

# Test 10: LIMIT results
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' LIMIT 5

# Test 11: ORDER BY with LIMIT
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.salary DESC LIMIT 3

# Test 12: SHOW selected fields (parent-only)
SELECT last_name,first_name,city FROM us500 WHERE state='LA' LIMIT 5

# Test 13: SHOW with ORDER BY and LIMIT
SELECT u.last_name,t.age,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='TX' ORDER BY t.age DESC LIMIT 5

# Test 14: (was TXT output to Louisiana.txt) -- displayed instead
SELECT u.last_name,u.first_name,u.city,t.age FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA'

# Test 15: (was CSV output to Louisiana.csv) -- displayed instead
SELECT u.last_name,u.first_name,u.city,t.age FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA'

# Test 16: COUNT aggregate GROUP BY (parent-only)
SELECT state, COUNT(*) FROM us500 GROUP BY state

# Test 17: COUNT on another field GROUP BY
SELECT occupation, COUNT(*) FROM jobsort GROUP BY occupation

# Test 18: SUM aggregate
SELECT SUM(salary) FROM jobsort

# Test 19: AVG aggregate
SELECT AVG(age) FROM jobsort

# Test 20: MIN aggregate
SELECT MIN(salary) FROM jobsort

# Test 21: MAX aggregate
SELECT MAX(age) FROM jobsort

# Test 22: Aggregate with WHERE (join)
SELECT AVG(t.salary) FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA'

# Test 23: Aggregate with WHERE (child)
SELECT MAX(age) FROM jobsort WHERE occupation='Engineer' COLLATE NOCASE

# Test 24: DELETE (soft, via delflag) -- MUTATES
UPDATE us500 SET delflag='D' WHERE city='Abilene' AND state='TX'

# Test 25: Verify deletion (delflag-aware; should show 0)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX' AND u.delflag<>'D'

# Test 26: UNDELETE (via delflag) -- MUTATES
UPDATE us500 SET delflag='' WHERE city='Abilene' AND state='TX'

# Test 27: Verify undelete (should show 2)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX' AND u.delflag<>'D'

# Test 28: Multi-condition across tables
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA' AND t.age>50 AND t.salary>100000

# Test 29: LIKE with condition (Manager)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%Los%' AND t.occupation='Manager' COLLATE NOCASE

# Test 30: LIKE with condition (Engineer)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%Los%' AND t.occupation='Engineer' COLLATE NOCASE

# Test 33: Final complex query
SELECT u.last_name,u.first_name,u.state,t.age,t.salary FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE (u.state='CA' OR u.state='TX') AND t.age>50 ORDER BY t.salary DESC LIMIT 10

# Test 34: INSERT a new record -- MUTATES
INSERT INTO us500 (rec,delflag,first_name,last_name,city,state,zip) VALUES (999999,'','Testy','McTester','DebugCity','TX','99999')

# Test 35: Verify INSERT (should find Testy)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.last_name='McTester'

# Test 36: UPDATE (string assignment) -- MUTATES
UPDATE us500 SET city='FixedCity' WHERE last_name='McTester'

# Test 37: Verify UPDATE
SELECT first_name,last_name,city FROM us500 WHERE last_name='McTester'

# Test 38: Ages before arithmetic update
SELECT t.id_ref,t.lastname,u.last_name,t.occupation,t.age FROM jobsort t JOIN us500 u ON t.id_ref=u.rec_p1 WHERE t.occupation='student' COLLATE NOCASE

# Test 39: Arithmetic UPDATE (age + 1) -- MUTATES
UPDATE jobsort SET age=age+1 WHERE occupation='Student' COLLATE NOCASE

# Test 40: Verify arithmetic update
SELECT t.id_ref,t.lastname,u.last_name,t.occupation,t.age FROM jobsort t JOIN us500 u ON t.id_ref=u.rec_p1 WHERE t.occupation='student' COLLATE NOCASE

# Test 42: SQL SELECT (verbatim from test.bat)
SELECT first_name, last_name, city FROM us500 WHERE state='CA' LIMIT 3

# Test 43: SQL aggregate (verbatim)
SELECT COUNT(occupation) FROM jobsort

# Test 48: Numeric < comparison
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age<20

# Test 50: Indexed equality on numeric field
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.salary=82000

# Test 51: ORDER BY string field
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY u.last_name

# Test 52: AVG GROUP BY
SELECT occupation, AVG(salary) FROM jobsort GROUP BY occupation

# Test 53: COUNT with WHERE and GROUP BY (join)
SELECT t.occupation, COUNT(*) FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA' GROUP BY t.occupation

# Test 54: Numeric indexed search (age>60 again)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age>60

# Test 55: LIKE starts-with
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE 'San%'

# Test 56: LIKE ends-with
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%City'

One thing to note--the original test 50 ran for 11 minutes without completing. Claude added an index and rewrote the query and it dropped to 7 seconds.

Here's the run log:

Quote
# Test 1: Simple equality query
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene'
[8323 ms | 512 rows] free=4025616
# Test 2: Compound query with AND
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX'
[14845 ms | 256 rows] free=4025648
# Test 3: Quoted value (spaces)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='San Francisco'
[11634 ms | 640 rows] free=4025616
# Test 5: Complex query with parentheses
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE (u.city='Abilene' OR u.city='Fresno') AND u.state='CA'
[13416 ms | 0 rows] free=4025616
# Test 6: LIKE pattern matching
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%San%'
[39696 ms | 3456 rows] free=4025616
# Test 7: Numeric comparison (child field)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age>60
[95697 ms | 7756 rows] free=4025616
# Test 8: ORDER BY ascending
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.age
[16331 ms | 1152 rows] free=4025616
# Test 9: ORDER BY descending
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.salary DESC
[16344 ms | 1152 rows] free=4025616
# Test 10: LIMIT results
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' LIMIT 5
[90 ms | 5 rows] free=4025616
# Test 11: ORDER BY with LIMIT
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.salary DESC LIMIT 3
[10048 ms | 3 rows] free=4025616
# Test 12: SHOW selected fields (parent-only)
SELECT last_name,first_name,city FROM us500 WHERE state='LA' LIMIT 5
[50 ms | 5 rows] free=4025744
# Test 13: SHOW with ORDER BY and LIMIT
SELECT u.last_name,t.age,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='TX' ORDER BY t.age DESC LIMIT 5
[15821 ms | 5 rows] free=4025744
# Test 14: (was TXT output to Louisiana.txt) -- displayed instead
SELECT u.last_name,u.first_name,u.city,t.age FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA'
[14576 ms | 1152 rows] free=4025680
# Test 15: (was CSV output to Louisiana.csv) -- displayed instead
SELECT u.last_name,u.first_name,u.city,t.age FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA'
[14602 ms | 1152 rows] free=4025680
# Test 16: COUNT aggregate GROUP BY (parent-only)
SELECT state, COUNT(*) FROM us500 GROUP BY state
[967 ms | 47 rows] free=4025872
# Test 17: COUNT on another field GROUP BY
SELECT occupation, COUNT(*) FROM jobsort GROUP BY occupation
[1167 ms | 8 rows] free=4025840
# Test 18: SUM aggregate
SELECT SUM(salary) FROM jobsort
[813 ms | 1 rows] free=4025904
# Test 19: AVG aggregate
SELECT AVG(age) FROM jobsort
[701 ms | 1 rows] free=4025920
# Test 20: MIN aggregate
SELECT MIN(salary) FROM jobsort
[14 ms | 1 rows] free=4025904
# Test 21: MAX aggregate
SELECT MAX(age) FROM jobsort
[5 ms | 1 rows] free=4025904
# Test 22: Aggregate with WHERE (join)
SELECT AVG(t.salary) FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA'
[19101 ms | 1 rows] free=4025920
# Test 23: Aggregate with WHERE (child)
SELECT MAX(age) FROM jobsort WHERE occupation='Engineer' COLLATE NOCASE
[14 ms | 1 rows] free=4025904
# Test 24: DELETE (soft, via delflag) -- MUTATES
UPDATE us500 SET delflag='D' WHERE city='Abilene' AND state='TX'
SKIP (mutating)
# Test 25: Verify deletion (delflag-aware; should show 0)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX' AND u.delflag<>'D'
[14859 ms | 256 rows] free=4025648
# Test 26: UNDELETE (via delflag) -- MUTATES
UPDATE us500 SET delflag='' WHERE city='Abilene' AND state='TX'
SKIP (mutating)
# Test 27: Verify undelete (should show 2)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX' AND u.delflag<>'D'
[14879 ms | 256 rows] free=4025648
# Test 28: Multi-condition across tables
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA' AND t.age>50 AND t.salary>100000
[23898 ms | 772 rows] free=4025616
# Test 29: LIKE with condition (Manager)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%Los%' AND t.occupation='Manager' COLLATE NOCASE
[19736 ms | 74 rows] free=4025616
# Test 30: LIKE with condition (Engineer)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%Los%' AND t.occupation='Engineer' COLLATE NOCASE
[19948 ms | 104 rows] free=4025616
# Test 33: Final complex query
SELECT u.last_name,u.first_name,u.state,t.age,t.salary FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE (u.state='CA' OR u.state='TX') AND t.age>50 ORDER BY t.salary DESC LIMIT 10
[34739 ms | 10 rows] free=4025648
# Test 34: INSERT a new record -- MUTATES
INSERT INTO us500 (rec,delflag,first_name,last_name,city,state,zip) VALUES (999999,'','Testy','McTester','DebugCity','TX','99999')
SKIP (mutating)
# Test 35: Verify INSERT (should find Testy)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.last_name='McTester'
[13036 ms | 0 rows] free=4025648
# Test 36: UPDATE (string assignment) -- MUTATES
UPDATE us500 SET city='FixedCity' WHERE last_name='McTester'
SKIP (mutating)
# Test 37: Verify UPDATE
SELECT first_name,last_name,city FROM us500 WHERE last_name='McTester'
[12966 ms | 0 rows] free=4025680
# Test 38: Ages before arithmetic update
SELECT t.id_ref,t.lastname,u.last_name,t.occupation,t.age FROM jobsort t JOIN us500 u ON t.id_ref=u.rec_p1 WHERE t.occupation='student' COLLATE NOCASE
[12379 ms | 898 rows] free=4025616
# Test 39: Arithmetic UPDATE (age + 1) -- MUTATES
UPDATE jobsort SET age=age+1 WHERE occupation='Student' COLLATE NOCASE
SKIP (mutating)
# Test 40: Verify arithmetic update
SELECT t.id_ref,t.lastname,u.last_name,t.occupation,t.age FROM jobsort t JOIN us500 u ON t.id_ref=u.rec_p1 WHERE t.occupation='student' COLLATE NOCASE
[12385 ms | 898 rows] free=4025616
# Test 42: SQL SELECT (verbatim from test.bat)
SELECT first_name, last_name, city FROM us500 WHERE state='CA' LIMIT 3
[31 ms | 3 rows] free=4025744
# Test 43: SQL aggregate (verbatim)
SELECT COUNT(occupation) FROM jobsort
[1093 ms | 1 rows] free=4025888
# Test 48: Numeric < comparison
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age<20
[34445 ms | 2704 rows] free=4025648
# Test 50: Indexed equality on numeric field
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.salary=82000
[12892 ms | 912 rows] free=4025616
# Test 51: ORDER BY string field
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY u.last_name
[16323 ms | 1152 rows] free=4025616
# Test 52: AVG GROUP BY
SELECT occupation, AVG(salary) FROM jobsort GROUP BY occupation
[23312 ms | 8 rows] free=4025856
# Test 53: COUNT with WHERE and GROUP BY (join)
SELECT t.occupation, COUNT(*) FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA' GROUP BY t.occupation
[19478 ms | 8 rows] free=4025840
# Test 54: Numeric indexed search (age>60 again)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age>60
[95998 ms | 7756 rows] free=4025616
# Test 55: LIKE starts-with
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE 'San%'
[36386 ms | 2944 rows] free=4025616
# Test 56: LIKE ends-with
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%City'
[29692 ms | 1792 rows] free=4025616
=== WARM+GC 64k DISPLAY: 42 done, 0 err, 5 skip ===
=== TOTAL RUNTIME: 761392 ms (761.4 s = 12m 41s) ===

Note that the same queries ran against the identical SQL database on my laptop in 2.6 seconds.

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

matherp
Guru

Joined: 11/12/2012
Location: United Kingdom
Posts: 11779

Posted: 04:25pm 24 Jul 2026

Copy link to clipboard

Print this post

lizby the cache is currently set to 256Kb because we encountered thrashing when it was too big. You can though experiment with the size using

c = usqlite.connect("/data.db")
c.execute("PRAGMA cache_size = -512").close() # 512 KiB for this connection

Please try a few different values and see what impact it has. Note the negative sign in front of the number. Value must be a multiple of 4

I'll be very interested in your results

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 06:43pm 24 Jul 2026

Copy link to clipboard

Print this post

Per Claude:

Quote PRAGMA cache_size sweep — 64k bench.db (usqlite v0.12, PicoComputer 3)

Setup: /sd/bench.db (64,000 rows, 19.9 MB), a 13-query read-only subset of our test batch covering every shape (point-join, broad child-filter join, ORDER BY + sort, full-scan aggregates, GROUP BY, indexed-equality join, leading/trailing LIKE). One warm connection per pass, gc.collect() between statements, fresh soft-reset before each pass, cache_size set + read back to confirm.
Times are query execution only (rows streamed and discarded, no display).

┌─────────────────────────────┬──────────────────────────────────┬────────────────┐
│ cache_size │ Result │ Query-time sum │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −64 KiB │ 13 ok │ 198,435 ms │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −256 KiB (firmware default) │ 13 ok │ 198,463 ms │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −1024 KiB │ 13 ok │ 198,532 ms │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −1536 KiB │ 13 ok │ 198,086 ms │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −2048 KiB │ 7 ok / 6 OOM* │ (partial) │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −4096 KiB │ 0 ok / 13 OOM*(fails at prepare) │ — │
└─────────────────────────────┴──────────────────────────────────┴────────────────┘
* OOM -- Out Of Memory

Two findings:

1. No speed effect anywhere in the usable range. 64 KiB → 1.5 MiB is dead flat — 0.22% spread, pure noise (e.g. age>60:
53,197–53,310 ms across all passes). These queries are bound by SD random reads for row materialization plus single-pass table/index scans that touch each page ~once, so a bigger cache has nothing to reuse — even across statements, since each query's working set dwarfs the cache and evicts the previous one's pages.
2. Above ~1.5 MiB it gets worse, via OOM not slowdown. At 2 MiB the queries that fail are specifically the memory-hungry ones — ORDER BY (sort buffer), GROUP BY, multi-way joins — with error (7): out of memory; at 4 MiB the cache allocation itself fails at prepare and nothing runs. The MicroPython heap stayed at ~4.03 MB free the whole time, so the OOM is inside SQLite's own allocator.
This looks like the shared memory pool: cache_size reserves budget that per-query sort/join/group scratch then can't get. That's the "thrashing" you saw — on this heap-limited board it surfaces as OOM rather than slow paging.

Takeaway: the 256 KiB default is already on the flat part of the curve — nothing to gain by raising it, and past ~1.5 MiB it breaks queries. The more interesting lever might be the other direction: since the pool is shared, a smaller cache could leave more room for sort/join scratch and let big 64k queries complete — worth testing.

==================================================================================

Quote
Curve fully characterized on both ends. Here's the consolidated result.

PRAGMA cache_size — full sweep, 64k bench.db (usqlite v0.12, PicoComputer 3)

Using Test 1 (indexed point-join, ~5,650 ms on the plateau) as a fast proxy for the low end, and the 13-query subset's query-time sum for the rest:

┌─────────────────────┬─────────────────────────────────────────────────────────────┐
│ cache_size │ Behaviour │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −4 / −16 / −32 KiB │ Degraded — Test 1 ≈ 15,000 ms (~2.7×) │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −48 KiB │ Partial recovery — Test 1 = 9,511 ms │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −64 KiB → −1536 KiB │ Flat plateau — sum 198,086–198,532 ms (0.22% spread, noise) │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −2048 KiB │ 7 ok / 6 OOM* (ORDER BY, GROUP BY, multi-join) │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −4096 KiB │ All OOM* — fails at prepare │
└─────────────────────┴─────────────────────────────────────────────────────────────┘
* OOM -- Out Of Memory

The shape is a plateau with a cliff at each end:

- Low cliff (~48–64 KiB, i.e. 12–16 pages of 4 KiB). Below it, the root + internal B-tree pages of the table and the rec_p1 index can't all stay resident, so each join step re-reads them from SD → ~2.7× slowdown. Sharp knee, not gradual.
- Flat middle (64 KiB → 1.5 MiB). Cache size is irrelevant to speed — the workload is SD-random-read bound for row materialization and single-pass scans that touch each page once, so there's nothing for a bigger cache to reuse.
- High cliff (>1.5 MiB). cache_size reserves budget from SQLite's shared memory pool; per-query sort/join/group scratch then can't allocate → error (7): out of memory. (MicroPython heap stayed ~4.03 MB free throughout, so it's SQLite's own allocator, not the Python heap.) This is the "thrashing" you warned of, surfacing as OOM on this heap-limited board.

Bottom line: the 256 KiB default is well chosen — squarely on the plateau, comfortably above the ~64 KiB floor and well below the ~1.5 MiB OOM ceiling. Raising it can only hurt (OOM); lowering it below ~64 KiB re-introduces SD B-tree re-reads. And the shared-pool idea — shrink the cache to free scratch memory for big sorts — doesn't pay off here: nothing in the normal batch OOMs at 256 KiB in the first place, so there's no headroom to buy, and you'd pay for it in re-reads. On this hardware the real lever for broad-query speed remains reducing SD reads / display cost, not tuning the cache.

(The views expressed here are Claude's and do not represent the editorial position of lizby.) ;-}

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 12:24am 25 Jul 2026

Copy link to clipboard

Print this post

A preview:

At about 3:30 this afternoon I set Claude Code to work on PicoRR, the Vibe Virtual Railroad I previously built in MMBasic with Gemini over the course of 5-6 weeks. Impressive productivity, I thought.

It's now 9:12pm, after a nap and dinner for me, and it's basically running with micropython code. I started it back in February to provide a use-case for PicoDB, but with SQLite working on the PicoCom3, there's no point in using a database, so the track segment and railroad car roster files are just read into arrays.

Tomorrow we'll do some wrap-up.

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

karlelch

Guru

Joined: 30/10/2014
Location: Germany
Posts: 352

Posted: 06:20am 25 Jul 2026

Copy link to clipboard

Print this post

But is is the same fun if Claude does all the work?

Cheers
Thomas

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 01:34pm 25 Jul 2026

Copy link to clipboard

Print this post

karlelch said But is is the same fun if Claude does all the work?

More fun, as far as I'm concerned. In 56 years of programming so far, I've sought to become more productive. This is certainly the apex of that. It's an old question in the field: is machine code more fun than assembly language; is assembly language more fun than Fortran; is Fortran more fun than C; is C more fun than C++?

If you want to produce a product, the method which produces it with the least expense of your time seems to me preferable. As a retired person, 90% of my time is spent on what I consider fun--and here I am talking to Claude, Gemini, and Chat, while getting them to talk to a piece of hardware which was designed (I speculate) entirely for recreation.

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

karlelch

Guru

Joined: 30/10/2014
Location: Germany
Posts: 352

Posted: 06:31am 26 Jul 2026

Copy link to clipboard

Print this post

Quote More fun, as far as I'm concerned. In 56 years of programming so far, I've sought to become more productive. This is certainly the apex of that.

Fair enough

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 01:53pm 27 Jul 2026

Copy link to clipboard

Print this post

I haven't been pursuing it, but this morning asked Claude to render all layouts and make a montage.

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

PhenixRising
Guru

Joined: 07/11/2023
Location: United Kingdom
Posts: 2055

Posted: 03:13pm 27 Jul 2026

Copy link to clipboard

Print this post

@lizby

I keep seeing THIS

Do you think that it's a too-good-to-be-true?

Edit: Looks like a con

Edited 2026-07-28 01:26 by PhenixRising

matherp
Guru

Joined: 11/12/2012
Location: United Kingdom
Posts: 11779

Posted: 03:31pm 27 Jul 2026

Copy link to clipboard

Print this post

Quote Do you think that it's a too-good-to-be-true?

Yes, probably just wraps the free access

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 03:47pm 27 Jul 2026

Copy link to clipboard

Print this post

PhenixRising said Do you think that it's a too-good-to-be-true?

I don't see why it would be worthwhile to investigate an alternative to Claude Code when my $20 monthly subscription costs me about $.66 USD a day.

I've only timed out of a 5-hour session twice when working with Claude <-> Pico Computer 3. If I wanted to maximize my usage, I could get 3 timed-out sessions--8am to 1pm, 1pm to 6pm, 6pm to 11pm. (Well, I could then submit a long prompt for overnight work to check on in the morning for a 4th session--20 hours of coding out of 24.)

I don't believe that I'm remotely paying for the actual cost of what Claude is providing. Prices are bound to go up. But I don't think that a "lifetime" subscription is likely to protect against that.

I also believe that within 6 months, "open" coding versions will be able to run on not-too-expensive hardware to do what Claude Code and Gemini do for me now. It won't be edge AI coding, but it will be entirely adequate for what I do. That might even already be available.

FWIW, I've been using Opus 4.8 and have found it altogether satisfactory. Opus 5.0 is now available at the same per-token price as 4.8. I asked Claude if it would be more economical to use that, considering that is is more productive, but uses more tokens. Claude suggested that there seemed to be no reason to change.

~
Edited 2026-07-28 01:52 by lizby

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

Bryan1

Guru

Joined: 22/02/2006
Location: Australia
Posts: 2225

Posted: 10:58pm 27 Jul 2026

Copy link to clipboard

Print this post

Well reading the forum over the last few days it does seem I am a late comer when it concerns Claude as it does seem members have been using this for quite awhile.

Now my 12 month subscription cost $340 AU which is under $1 a day now with about an hours time yesterday working with the PICO3 Computer where Claude looked at the STL file and found a heap of errors so re-wrote the whole code and the case has already been made by a member so I'm pretty happy my time was well spent

According to FEDEX my psram and wifi chips should come finally today and once installed the first bit of code will be a graphic where one can login to the wifi, get the NTP time and display it on the HDMI screen.

That second batch of boards has finally got tracking as I got an email saying some yellowing on 2 pcb's was found and is only cosmetic so I replied it's OK just send them. So they should be on the 4th of next month where the plan will be getting 3 of the boards talking to each other over wifi.

Well second caffine is gone so time to go open up and see what Claude and I can do today.

Regards Bryan

Bryan1

Guru

Joined: 22/02/2006
Location: Australia
Posts: 2225

Posted: 07:51am 28 Jul 2026

Copy link to clipboard

Print this post

Well had another good day with Claude today

I'm trying to get some seeds going and with it mid winter time to be a bit more modern and make some led lights using that sea of holes from a past project.

The board now measures 70x60 so as I have a few hundred 5mm white led's we decided 5 in series with a 330Ω going to V+ and the cathode going to a irf44 mosfet now using a couple of npn transistors a vibrator circuit working on 50% duty cycle powers the led's.

I found an old power supply 18 volts 2.3A so overkill for what I'm doing but it will do.

I have made up a template file in Sprint Layout to help me along and like with every project one seems to have every part but that last one we need. Well the hunt will go on.

Regards Bryan

Mixtel90

Guru

Joined: 05/10/2019
Location: United Kingdom
Posts: 9080

Posted: 09:24am 28 Jul 2026

Copy link to clipboard

Print this post

I may be an incredibly crap programmer, but come the day when access to AI is only possible for the rich and powerful elite, at least I'll still have an idea *how* to program rather than how to instruct a LLM to do what I want. ;)

Mick

Zilog Inside! nascom.info for Nascom & Gemini
Preliminary MMBasic docs & my PCB designs

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 03:25pm 28 Jul 2026

Copy link to clipboard

Print this post

With Gemini in Powershell, I provided a serial link to one of my (9) Seagate Dockstars.

This particular Dockstar is an aggregator and reporter--it receives sensor data sent via TCP or UDP, logs it, and produces reports and 4 daily emails to me (plus emergency emails if warranted).

I got Gemini to talk to it (as root over serial), asked it to mount a USB drive I had inserted and back up user and configuration files. It did this, but I had to give permission for everything it did.

I asked it to walk me through giving it broad permission to do stuff on the Dockstar (build and execute python programs) without giving it permission to run just any python program on my laptop. It did that--but took a number of iterations.

Now I feel I'm set up to do what I haven't had the courage to do before--flash a new uboot, which will wipe the flash drive (which has files dating to 2015). Then I can flash a current version of openWrt, which has many upgrades, and restore.

Though I've been using the Dockstar since August of 2010, Gemini knows much more about it than I do, and more about Linux/openWrt and bash scripts and python and the programs I have installed.

What's not to like?

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

dddns
Guru

Joined: 20/09/2024
Location: Germany
Posts: 910

Posted: 08:26pm 28 Jul 2026

Copy link to clipboard

Print this post

It was hacked in 2016/17 when I played with this and it runs a full blown Linux. Have fun :)

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 11:14am 29 Jul 2026

Copy link to clipboard

Print this post

dddns said It was hacked in 2016/17 when I played with this and it runs a full blown Linux

That's a bit cryptic. What was your experience in 2016/17 and would it be relevant today with the latest openWrt, v25.12.5, the version I'm trying to bring all theses up to?

Note these Dockstars aren't routers--they're little headless Linux computers which sit on a wired connection and do various tasks--mostly aggregate sensor data and serve camera views. One is a NAS.

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

dddns
Guru

Joined: 20/09/2024
Location: Germany
Posts: 910

Posted: 12:49pm 29 Jul 2026

Copy link to clipboard

Print this post

lizby said What was your experience in 2016/17 and would it be relevant today with the latest openWrt, v25.12.5, the version I'm trying to bring all theses up to?

It's well documented on Openwrt how to flash it, so basically you don't need knowledge and just follow the howto's :) With the convenience to have an already compiled actual kernel. I think OpenWRT is a good choice for this device.

Uboot hasn't changed much so I guess it could help a bit, if you have seen Uboot before

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3868

Posted: 02:14pm 29 Jul 2026

Copy link to clipboard

Print this post

I've flashed and reflashed 10 Dockstars, mostly openWrt but earlier Debian with a USB thumb drive. Starting in 2014 a uBoot was available to run entirely from flash and all of mine have had uBoot flashed. But a 2015 uBoot is required to run the latest openWrt version, 25.12.5, which has the latest security updates. I have 5 converted and am about to upgrade the two most important ones, which are central to my house monitoring and reporting setup.

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

Page 2 of 3

Print this page