TheBackShed.com

Forum Index : Microcontroller and PC projects : PicoDB

Author

Message

lizby
Guru

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

Posted: 04:46am 19 Jan 2026

Copy link to clipboard

Print this post

"Vibe coding", a fun activity for a long-retired, increasingly mobility-constrained programmer analyst.

Following the 4-day project with Claude AI to build a MMBasic database retrieval system, I was not happy that it was so tightly tied to the actual data. I started over with ChatGPT-5.2 with a JSON structure, and spent 2 days on it. I found json to be too bulky for me to like, and the experience with ChatGPT was terrible--many instances of "Page not responding: Wait/Close" with 10-minute waits. Chat said this was normal with long sessions, but that's not what I experience with Claude or Gemini.

So I switched to fixed-length records with Gemini, and had a real 5-day grind. Recently read my favorite AI substacker say that 2026 will become "all day, every day" for dedicated users, and that's what it was for me. The result is a generic, multi-table, multi-database relational retrieval system (updates to come).

It's 1700 lines long, with far more features than the 2100-line version that Claude came up with. I've implemented the same 500-record fictional person database as with Claude (2 tables), and part of Microsoft's test Northwind database (4 table).

It can be broken into the a library section and a user section. The stub of the user section is less than 100 lines long and takes less than 1% of program memory. The remained is loaded with LIBRARY LOAD.

Here is how Gemini has documented it (I'm not sure how the formatting will translate here). When I get update access, I will enter this on fruitoftheshed.

Project Name: PicoDB - Relational Database Engine for MMBasic
PicoDB is a lightweight, fully relational database engine written in MMBasic. It is designed for the RP2350 Pico2 microcontroller (PicoMite) where RAM is somewhat scarce but flash SD card storage is plentiful. It supports both a Native Command Mode and a subset of SQL, allowing for complex data querying, filtering, and reporting on hardware with limited resources.

Key Features
Dual Mode: Interact via a CLI using Native commands (pipe/unix style) or standard SQL (SELECT, DELETE).
Import Wizard: Built-in MAKE-DB command converts standard CSV files into fixed-width binary tables and automatically generates the schema.
Relational Engine: Supports Implicit and Explicit Joins across up to 5 open tables simultaneously.
Indexing: B-Tree style indexing implementation for instant lookups (O(log n) vs O(n)).
Aggregates: Built-in support for COUNT, SUM, AVG, MIN, MAX.
Context Switching: Instantly switch databases using USE-DB without restarting the program.

1. Installation
1. Code: Upload db.bas to your device.
2. Help Files: Create two text files on your storage root (SD or Flash) named native.hlp and sql.hlp. (Content provided below).
3. Run: RUN "db.bas"

2. Quick Start (The "30 Second" Tutorial)
Step 1: Import Data
You don't need to write schema files manually. Just upload your CSV files (with header lines) and run the import wizard.
DB:us500> make-db mydata employees.csv,customers.csv

This analyzes the CSVs, determines field types (String/Int/Float), creates the mydata.def schema, and converts data to fixed-width binary .dat files.
Step 2: Load the Database
DB:us500> use-db mydata
Switching to database: mydata
Database 'mydata' Loaded. 2 Tables, 15 Fields.

Step 3: Query (SQL Mode)
SQL
DB:mydata> MODE SQL (switch back with MODE NATIVE)
DB:mydata> SELECT first_name, job_title FROM employees WHERE salary > 50000 ORDER BY last_name

3. Native Command Mode
The native mode is optimized for minimal typing and "pipe-like" logic.
Syntax: [filter] [actions...]

Quote
Command Description Example
SHOW Select specific fields to display. age > 25 SHOW name, city
ORDER BY Sort results. Add DESC for reverse. ORDER BY age DESC
LIMIT Stop after N matches. LIMIT 5
BATCH Run a script of commands from a file. BATCH test_suite.bat
RULER Inspect raw data alignment. RULER employees.dat
CSV Write output to .csv file CSV filename
TXT Write output to .csv file TXT filename
INDEX Index field INDEX us500 on city
MAKE-DB Generate def and dat files from CSV MAKE-DB NW employees,regions
USE-DB Pick a database schema to use USE-DB NW
MODE SQL Switch to SQL mode MODE SQL
MODE NAT Switch to native mode MODE NATIVE

Operators:
=, <>, >, <, >=, <= (Numeric and String)
~: Case-insensitive "Contains" (also " LIKE " translates to "~")
|: OR operator (city="London" | city="Paris")
&: AND operator (Implicit, but can be explicit)

4. SQL Mode (SQLite V2.8 Subset)
For users comfortable with standard database syntax.
Supported Commands:
SELECT [fields] FROM [table] WHERE [condition] ORDER BY [field] LIMIT [n]
DELETE FROM [table] WHERE [condition]
UNDELETE FROM [table] WHERE [condition]
Aggregates: COUNT, SUM, AVG, MIN, MAX

Examples:
SQL
SELECT count(ID) FROM users WHERE active = 'Yes'
DELETE FROM logs WHERE date < '2023-01-01'
SELECT * FROM sales WHERE region = 'North' ORDER BY amount DESC

5. Technical Specifications
Max Tables Open: 5 simultaneous tables.
Max Record Size: Configurable (default 256 bytes per row).
File Handles Used:
#1 - #5: Data Tables (Runtime)
#6 - #7: Indexing (Read/Write) (uses MMBasic Structures)
#8: Utilities (Help/Schema Loader)
#9 - #10: Import Wizard (CSV processing)

6. Appendix: Help Files
Save these text files to your storage to enable the built-in help system.
native.hlp

Quote ============================================================
DATABASE ENGINE HELP (Native Mode)
============================================================
CORE COMMANDS:
SHOW [fields] : Display specific fields.
[filter] ... : Filter data (e.g., age > 25).
EXIT : Quit.

DATA MANAGEMENT:
USE-DB [name] : Switch database context.
MAKE-DB [name] [files] : Import CSVs to DB.
INDEX [table] [field] : Build index for instant search.

FILTER OPERATORS:
=, <, >, <=, >= : Comparison.
~ : 'Contains' (Case Insensitive).
| : OR operator.

sql.hlp

Quote ============================================================
SQL MODE HELP (SQLite V2.8 Subset)
============================================================
COMMANDS:
SELECT [fields] FROM [table] [WHERE...] [ORDER BY...]
DELETE FROM [table] WHERE [condition]
MODE NATIVE : Switch to Native mode.

EXAMPLES:
SELECT * FROM us500 WHERE city = 'Abilene'
SELECT count(state) FROM us500

NOTES:
* Use single quotes for strings: city = 'New York'/FONT]

db.zip

~
Edited 2026-01-19 14:58 by lizby

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

Print this page

To reply to this topic, you need to log in.