TheBackShed.com

Forum Index : Microcontroller and PC projects : PicoDB

Author

Message

lizby
Guru

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

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 FOTS

zeitfest
Guru

Joined: 31/07/2019
Location: Australia
Posts: 659

Posted: 08:29am 19 Jan 2026

Copy link to clipboard

Print this post

Now you need COMMITs and ROLLBACKs...locks...views...
it is a long slippery slope

lizby
Guru

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

Posted: 02:11pm 19 Jan 2026

Copy link to clipboard

Print this post

zeitfest said Now you need COMMITs and ROLLBACKs...locks...views...
it is a long slippery slope

Won't be looking for anything past SQLite V2.8, and only a subset of that.

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

PeteCotton

Guru

Joined: 13/08/2020
Location: Canada
Posts: 613

Posted: 08:02pm 19 Jan 2026

Copy link to clipboard

Print this post

Very cool!

lizby
Guru

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

Posted: 02:45am 22 Jan 2026

Copy link to clipboard

Print this post

Many updates, including single and batch record insertions, UPDATE ... WHERE, and CALLBACK to user code.

Code is now 2,261 lines long

I think this is a quite functional database manager now. The bulk of the code can be loaded into the library, leaving only about 200 lines of stub for a main program to which the user can add essentially anything as user code.

Simple queries on indexed terms are quite fast--tens of milliseconds.

Here is the native language help text:

Quote ============================================================
DATABASE ENGINE HELP (Native Mode v6a2)
============================================================
CORE COMMANDS
MAKE-DB : Create database from csv files
[filter] ... : Filter data (e.g., age > 25).
RUN : Execute 'UserCode' subroutine.
EXIT : Quit the engine.
HELP / ? : Show this screen.

DATA MODIFICATION (CRUD)
INSERT INTO : Add record.
Ex: INSERT INTO us500 name=John, age=40
UPDATE : Modify record(s).
Single: UPDATE us500 5 SET age=41
Bulk: UPDATE us500 SET city=X WHERE city=Y
DELETE FROM : Soft delete. Ex: id=5 DELETE FROM us500
UNDELETE FROM : Restore. Ex: id=5 UNDELETE FROM us500

TRANSACTIONS & PERFORMANCE
INDEX : Rebuild index. Ex: index us500 ON city
SET INDEXING OFF : Defer indexing (Fast Batch Mode).
SET INDEXING ON : Enable auto-indexing (Safe Mode).
REINDEX : Commit changes (Rebuild dirty indexes).
CACHE : Load database into RAM.

DATA MANAGEMENT
USE-DB : Switch database. Ex: use-db nw
MAKE-DB : Import CSVs. Ex: make-db nw file.csv
BATCH : Run script file. Ex: batch jobs.txt

TOOLS & AGGREGATES
COUNT, SUM, AVG : Statistics. Ex: AVG salary WHERE state=TX
MIN, MAX : Statistics. Ex: MAX age
RULER : Show file ruler. Ex: ruler data.csv
FORMAT : Save view. Ex: format view1 name,city
TEST : Run 'Test_Callback' hook.

ADVANCED
LIMIT [n] : Stop after [n] matches.
ORDER BY [field] : Sort results (Add DESC for reverse).
MODE SQL : Switch to SQL Syntax parser.
MODE NATIVE : Switch to Native Syntax parser.

FILTER OPERATORS
=, <, >, <=, >= : Numeric/String comparison.
~ : 'Contains' (Case Insensitive).
LIKE : Pattern Match (% is wildcard).
| : OR operator.
============================================================

Fuller PDF documentation attached.

If anyone is interested in doing anything with it, I'll provide the code and would be happy to work to make it suitable for real projects.

PicoDB.pdf

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

lizby
Guru

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

Posted: 01:18am 23 Jan 2026

Copy link to clipboard

Print this post

Arithmetic added today (UPDATE jobsort SET age = age + 1 WHERE occupation = student) and GROUP BY (COUNT occupation GROUP BY occupation).

Here's a new test suit, with 46 commands:

Quote # test.bat - Comprehensive database test suite
# Lines starting with # are comments And will be skipped

# Test 1: Simple equality query
city=Abilene

# Test 2: Compound query with And
city=Abilene & state=TX

# Test 3: Query with quoted value(spaces)
city="San Francisco"

# Test 4: OR query
city=Abilene | city=Fresno

# Test 5: Complex query with parentheses
(city=Abilene | city=Fresno) & state=CA

# Test 6: LIKE pattern matching
city~%San%

# Test 7: Numeric comparison
age>60

# Test 8: ORDER BY ascending
state=LA order by age

# Test 9: ORDER BY descending
state=LA order by salary desc

# Test 10: LIMIT results
state=LA limit 5

# Test 11: ORDER BY with LIMIT
state=LA order by salary desc limit 3

# Test 12: SHOW selected fields
state=LA show last_name, first_name, city limit 5

# Test 13: SHOW with ORDER BY And LIMIT
state=TX order by age desc show last_name, age, occupation limit 5

# Test 14: Output To TXT file
state=LA show last_name, first_name, city, age txt Louisiana.txt

# Test 15: Output To CSV file
state=LA show last_name, first_name, city, age csv Louisiana.csv

# Test 16: COUNT aggregate
count state group by state

# Test 17: COUNT on another field
count occupation group by occupation

# Test 18: SUM aggregate
sum salary

# Test 19: AVG aggregate
avg age

# Test 20: MIN aggregate
min salary

# Test 21: MAX aggregate
max age

# Test 22: Aggregate with WHERE
avg salary where state=CA

# Test 23: Another aggregate with WHERE
max age where occupation=Engineer

# Test 24: DELETE test(mark records)
city=Abilene & state=TX delete from us-500

# Test 25: Verify deletion(should show 0 matches)
city=Abilene & state=TX

# Test 26: UNDELETE test(restore records)
city=Abilene & state=TX undelete from us-500

# Test 27: Verify undelete(should show 2 matches)
city=Abilene & state=TX

# Test 28: Multi-condition with jobsort fields
state=CA & age>50 & salary>100000

# Test 29: LIKE with multiple conditions
city~%Los% & occupation=Manager

# Test 30: LIKE with multiple conditions
city~%Los% & occupation=Engineer

# Test 31: Save format as .fmt
format person Last_Name, First_Name, Address, City, State, Zip

# Test 32: Test format
state=LA format occupation

# Test 33: Final complex query
(state=CA | state=TX) & age>50 order by salary desc show last_name, first_name, state, age, salary limit 10

# --- NEW FUNCTIONALITY TESTS ---

# Test 34: INSERT a new record
INSERT INTO us500 First_Name=Testy, Last_Name=McTester, city=DebugCity, state=TX, zip=99999

# Test 35: Verify INSERT (Should find Testy)
last_name=McTester

# Test 36: UPDATE (String assignment)
UPDATE us500 SET city="FixedCity" WHERE last_name=McTester

# Test 37: Verify UPDATE
last_name=McTester show first_name, last_name, city

# Test 38: ARITHMETIC UPDATE (The "Age+1" Logic)
# First, show current ages for a small group
occupation=Student limit 5 show last_name, age

# Test 39: Execute Arithmetic Update
UPDATE jobsort SET age = age + 1 WHERE occupation=Student

# Test 40: Verify Arithmetic Update (Ages should be +1)
occupation=Student limit 5 show last_name, age

# Test 41: MODE SQL (Switch parser to SQL compatibility)
MODE SQL

# Test 42: SQL SELECT Syntax
SELECT first_name, last_name, city FROM us500 WHERE state='CA' LIMIT 3

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

# Test 44: Return to NATIVE mode
MODE NATIVE

# Test 45: Ruler Tool (Visual Debugging)
ruler jobsort.dat

# Test 46: Explicit Indexing Controls
SET INDEXING OFF
SET INDEXING ON

And the results of running: BATCH test.bat

Quote Initializing Database Engine... A:/dbfx/d6a6.bas
Database 'us500' Loaded.
2 Tables, 18 Fields.
DB:us500> batch test.bat
Running Batch: test.bat
BATCH> # test.bat - Comprehensive database test suite
BATCH> # Lines starting with # are comments And will be skipped
BATCH>
BATCH> # Test 1: Simple equality query
BATCH> city=Abilene
DEBUG: Filter=[city=Abilene]
DIAG: Analyzing Query [city=Abilene]
DIAG: Index Found! Switching to Indexed Strategy.
Strategy: Indexed Range
------------------------------------------------
Lilli Scriven 33 State St Abilene TX 79601
Carlee Boulter 8284 Hart St Abilene KS 67410
Glory Schieler 5 E Truman Rd Abilene TX 79602
Kati Rulapaugh 6980 Dorsett Rd Abilene KS 67410
------------------------------------------------
4 matches found.
Runtime: 45.865 ms

BATCH>
BATCH> # Test 2: Compound query with And
BATCH> city=Abilene & state=TX
DEBUG: Filter=[city=Abilene&state=TX]
DIAG: Analyzing Query [city=Abilene&state=TX]
Strategy: Complex Scan
------------------------------------------------
Lilli Scriven 33 State St Abilene TX 79601
Glory Schieler 5 E Truman Rd Abilene TX 79602
------------------------------------------------
2 matches found.
Runtime: 2987.957 ms

BATCH>
BATCH> # Test 3: Query with quoted value(spaces)
BATCH> city="San Francisco"
DEBUG: Filter=[city="San Francisco"]
DIAG: Analyzing Query [city="San Francisco"]
DIAG: Index Found! Switching to Indexed Strategy.
Strategy: Indexed Range
------------------------------------------------
Tarra Nachor 39 Moccasin Dr San Francisco CA 94104
Norah Waymire 6 Middlegate Rd #106 San Francisco CA 94107
Trinidad Mcrae 10276 Brooks St San Francisco CA 94105
Kallie Blackwood 701 S Harrison Rd San Francisco CA 94104
Stephaine Vinning 3717 Hamann Industrial Pky San Francisco CA 94104
------------------------------------------------
5 matches found.
Runtime: 52.702 ms

BATCH>
BATCH> # Test 4: OR query
BATCH> city=Abilene | city=Fresno
DEBUG: Filter=[city=Abilene|city=Fresno]
DIAG: Analyzing Query [city=Abilene|city=Fresno]
Strategy: Complex Scan
------------------------------------------------
Kati Rulapaugh 6980 Dorsett Rd Abilene KS 67410
Lilli Scriven 33 State St Abilene TX 79601
Glory Schieler 5 E Truman Rd Abilene TX 79602
Carlee Boulter 8284 Hart St Abilene KS 67410
------------------------------------------------
4 matches found.
Runtime: 3960.715 ms

BATCH>
BATCH> # Test 5: Complex query with parentheses
BATCH> (city=Abilene | city=Fresno) & state=CA
DEBUG: Filter=[(city=Abilene|city=Fresno)&state=CA]
DIAG: Analyzing Query [(city=Abilene|city=Fresno)&state=CA]
Strategy: Complex Scan
------------------------------------------------
------------------------------------------------
0 matches found.
Runtime: 6400.998 ms

BATCH>
BATCH> # Test 6: LIKE pattern matching
BATCH> city~%San%
DEBUG: Filter=[city~%San%]
DIAG: Analyzing Query [city~%San%]
Strategy: Complex Scan
------------------------------------------------
Leota Dilliard 7 W Jackson Blvd San Jose CA 95111
Veronika Inouye 6 Greenleaf Ave San Jose CA 95111
Valentine Gillian 775 W 17th St San Antonio TX 78204
Kallie Blackwood 701 S Harrison Rd San Francisco CA 94104
Bobbye Rhym 30 W 80th St #1995 San Carlos CA 94070
Cory Gibes 83649 W Belmont Ave San Gabriel CA 91776
Elvera Benimadho 99385 Charity St #840 San Jose CA 95110
Carma Vanheusen 68556 Central Hwy San Leandro CA 94577
Louisa Cronauer 524 Louisiana Ave Nw San Leandro CA 94577
Shawna Palaspas 5 N Cleveland Massillon Rd Thousand Oaks CA 91362
Xuan Rochin 2 Monroe St San Mateo CA 94403
Stephaine Vinning 3717 Hamann Industrial Pky San Francisco CA 94104
Merilyn Bayless 195 13n N Santa Clara CA 95054
Georgene Montezuma 98 University Dr San Ramon CA 94583
Melodie Knipp 326 E Main St #6496 Thousand Oaks CA 91362
Norah Waymire 6 Middlegate Rd #106 San Francisco CA 94107
Aliza Baltimore 1128 Delaware St San Jose CA 95132
Mozell Pelkowski 577 Parade St South San Francisco CA 94080
Charlene Hamilton 985 E 6th Ave Santa Rosa CA 95407
Lorean Martabano 85092 Southern Blvd San Antonio TX 78204
Tarra Nachor 39 Moccasin Dr San Francisco CA 94104
Trinidad Mcrae 10276 Brooks St San Francisco CA 94105
Mari Lueckenbach 1 Century Park E San Diego CA 92110
Antione Onofrio 4 S Washington Ave San Bernardino CA 92410
Daniel Perruzza 11360 S Halsted St Santa Ana CA 92705
Dorothy Chesterfield 469 Outwater Ln San Diego CA 92126
Gail Similton 62 Monroe St Thousand Palms CA 92276
------------------------------------------------
27 matches found.
Runtime: 2871.971 ms

BATCH>
BATCH> # Test 7: Numeric comparison
BATCH> age>60
DEBUG: Filter=[age>60]
DIAG: Analyzing Query [age>60]
Strategy: Linear Scan
------------------------------------------------
------------------------------------------------
0 matches found.
Runtime: 1332.269 ms

BATCH>
BATCH> # Test 8: ORDER BY ascending
BATCH> state=LA order by age
DEBUG: Filter=[state=LA]
DIAG: Analyzing Query [state=LA]
Strategy: Linear Scan
------------------------------------------------
Sorting 9 records...
James Butt 6649 N Blue Gum St New Orleans LA 70116
Solange Shinko 426 Wolf St Metairie LA 70002
Arlene Klusman 3 Secor Rd New Orleans LA 70112
Larae Gudroe 6651 Municipal Rd Houma LA 70360
Willodean Konopacki 55 Hawthorne Blvd Lafayette LA 70506
Terrilyn Rodeigues 3718 S Main St New Orleans LA 70130
Kayleigh Lace 43 Huey P Long Ave Lafayette LA 70508
Jutta Amyot 49 N Mays St Broussard LA 70518
Cordelia Storment 393 Hammond Dr Lafayette LA 70506
------------------------------------------------
9 matches found.
Runtime: 1258.744 ms

BATCH>
BATCH> # Test 9: ORDER BY descending
BATCH> state=LA order by salary desc
DEBUG: Filter=[state=LA]
DIAG: Analyzing Query [state=LA]
Strategy: Linear Scan
------------------------------------------------
Sorting 9 records...
James Butt 6649 N Blue Gum St New Orleans LA 70116
Solange Shinko 426 Wolf St Metairie LA 70002
Arlene Klusman 3 Secor Rd New Orleans LA 70112
Larae Gudroe 6651 Municipal Rd Houma LA 70360
Willodean Konopacki 55 Hawthorne Blvd Lafayette LA 70506
Terrilyn Rodeigues 3718 S Main St New Orleans LA 70130
Kayleigh Lace 43 Huey P Long Ave Lafayette LA 70508
Jutta Amyot 49 N Mays St Broussard LA 70518
Cordelia Storment 393 Hammond Dr Lafayette LA 70506
------------------------------------------------
9 matches found.
Runtime: 1268.538 ms

BATCH>
BATCH> # Test 10: LIMIT results
BATCH> state=LA limit 5
DEBUG: Filter=[state=LA]
DIAG: Analyzing Query [state=LA]
Strategy: Linear Scan
------------------------------------------------
James Butt 6649 N Blue Gum St New Orleans LA 70116
Solange Shinko 426 Wolf St Metairie LA 70002
Arlene Klusman 3 Secor Rd New Orleans LA 70112
Larae Gudroe 6651 Municipal Rd Houma LA 70360
Willodean Konopacki 55 Hawthorne Blvd Lafayette LA 70506
------------------------------------------------
5 matches found.
Runtime: 544.284 ms

BATCH>
BATCH> # Test 11: ORDER BY with LIMIT
BATCH> state=LA order by salary desc limit 3
DEBUG: Filter=[state=LA]
DIAG: Analyzing Query [state=LA]
Strategy: Linear Scan
------------------------------------------------
Sorting 9 records...
James Butt 6649 N Blue Gum St New Orleans LA 70116
Solange Shinko 426 Wolf St Metairie LA 70002
Arlene Klusman 3 Secor Rd New Orleans LA 70112
------------------------------------------------
9 matches found.
Runtime: 1240.825 ms

BATCH>
BATCH> # Test 12: SHOW selected fields
BATCH> state=LA show last_name, first_name, city limit 5
DEBUG: Filter=[state=LA]
DIAG: Analyzing Query [state=LA]
Strategy: Linear Scan
------------------------------------------------
Butt James New Orleans
Shinko Solange Metairie
Klusman Arlene New Orleans
Gudroe Larae Houma
Konopacki Willodean Lafayette
------------------------------------------------
5 matches found.
Runtime: 529.948 ms

BATCH>
BATCH> # Test 13: SHOW with ORDER BY And LIMIT
BATCH> state=TX order by age desc show last_name, age, occupation limit 5
DEBUG: Filter=[state=TX]
DIAG: Analyzing Query [state=TX]
Strategy: Linear Scan
------------------------------------------------
Sorting 39 records...
Albares
Kolmetz
Figeroa
Gillian
Hollack
------------------------------------------------
39 matches found.
Runtime: 1278.926 ms

BATCH>
BATCH> # Test 14: Output To TXT file
BATCH> state=LA show last_name, first_name, city, age txt Louisiana.txt
DEBUG: Filter=[state=LA]
DIAG: Analyzing Query [state=LA]
Strategy: Linear Scan
------------------------------------------------
Butt James New Orleans
Shinko Solange Metairie
Klusman Arlene New Orleans
Gudroe Larae Houma
Konopacki Willodean Lafayette
Rodeigues Terrilyn New Orleans
Lace Kayleigh Lafayette
Amyot Jutta Broussard
Storment Cordelia Lafayette
------------------------------------------------
9 matches found.
Runtime: 1248.851 ms

BATCH>
BATCH> # Test 15: Output To CSV file
BATCH> state=LA show last_name, first_name, city, age csv Louisiana.csv
DEBUG: Filter=[state=LA]
DIAG: Analyzing Query [state=LA]
Strategy: Linear Scan
------------------------------------------------
Butt James New Orleans
Shinko Solange Metairie
Klusman Arlene New Orleans
Gudroe Larae Houma
Konopacki Willodean Lafayette
Rodeigues Terrilyn New Orleans
Lace Kayleigh Lafayette
Amyot Jutta Broussard
Storment Cordelia Lafayette
------------------------------------------------
9 matches found.
Runtime: 1247.298 ms

BATCH>
BATCH> # Test 16: COUNT aggregate
BATCH> count state group by state
DIAG: Analyzing Query []
---------------------------------------------
Group (state) Result
---------------------------------------------
LA 9
MI 14
NJ 52
AK 6
OH 22
IL 15
CA 72
SD 1
MD 17
PA 29
NY 46
TX 39
AZ 9
TN 10
WI 11
KS 5
NM 2
OR 6
FL 28
MN 7
MA 12
SC 3
RI 5
CO 8
ID 4
NC 8
IN 9
WY 3
VA 7
HI 4
GA 7
AR 1
NV 2
ME 3
WA 8
MS 4
CT 5
MO 4
NH 1
ND 1
MT 1
IA 1
OK 1
KY 1
UT 1
NE 2
DC 1
---------------------------------------------
Runtime: 1067.571 ms

BATCH>
BATCH> # Test 17: COUNT on another field
BATCH> count occupation group by occupation
DIAG: Analyzing Query []
---------------------------------------------
Group (occupation) Result
---------------------------------------------
Analyst 70
Developer 75
Engineer 77
Manager 75
Nurse 67
Sales 67
Student 7
Teacher 61
---------------------------------------------
Runtime: 1049.784 ms

BATCH>
BATCH> # Test 18: SUM aggregate
BATCH> sum salary
DIAG: Analyzing Query []
Result: 3.972191e+07
Runtime: 1042.804 ms

BATCH>
BATCH> # Test 19: AVG aggregate
BATCH> avg age
DIAG: Analyzing Query []
Result: 41.198
Runtime: 1044.87 ms

BATCH>
BATCH> # Test 20: MIN aggregate
BATCH> min salary
DIAG: Analyzing Query []
Result: 0
Runtime: 1047.834 ms

BATCH>
BATCH> # Test 21: MAX aggregate
BATCH> max age
DIAG: Analyzing Query []
Result: 65
Runtime: 1045.927 ms

BATCH>
BATCH> # Test 22: Aggregate with WHERE
BATCH> avg salary where state=CA
DEBUG: Filter=[state=CA]
DIAG: Analyzing Query [state=CA]
Result: 79625
Runtime: 1365.758 ms

BATCH>
BATCH> # Test 23: Another aggregate with WHERE
BATCH> max age where occupation=Engineer
DEBUG: Filter=[occupation=Engineer]
DIAG: Analyzing Query [occupation=Engineer]
DIAG: Index Found! Switching to Indexed Strategy.
Alline Jeanty 55713 Lake City Hwy South Bend IN 46601
Dominque Dickerson 69 Marquette Ave Hayward CA 94545
Eladia Saulter 3958 S Dupont Hwy #7 Ramsey NJ 07446
Louisa Cronauer 524 Louisiana Ave Nw San Leandro CA 94577
Lenna Paprocki 639 Main St Anchorage AK 99501
Alex Loader 37 N Elm St #916 Tacoma WA 98409
Xochitl Discipio 3158 Runamuck Pl Round Rock TX 78664
Alisha Slusarski 3273 State St Middlesex NJ 08846
Brittni Gillaspie 67 Rv Cent Boise ID 83709
Kristofer Bennick 772 W River Dr Bloomington IN 47404
Noah Kalafatis 1950 5th Ave Milwaukee WI 53209
Dierdre Yum 63381 Jenks Ave Philadelphia PA 19134
Tegan Arceo 62260 Park Stre Monroe Township NJ 08831
Lai Harabedian 1933 Packer Ave #2 Novato CA 94945
Valentin Klimek 137 Pioneer Way Chicago IL 60604
Cammy Albares 56 E Morehead St Laredo TX 78045
Deonna Kippley 57 Haven Ave #90 Southfield MI 48075
Janine Rhoden 92 Broadway Astoria NY 11103
Jenelle Regusters 3211 E Northeast Loop Tampa FL 33619
Jettie Mconnell 50 E Wacker Dr Bridgewater NJ 08807
Helaine Halter 8 Sheridan Rd Jersey City NJ 07304
Ressie Auffrey 23 Palo Alto Sq Miami FL 33134
Casie Good 5221 Bear Valley Rd Nashville TN 37211
Mitsue Tollner 7 Eads St Chicago IL 60632
Raymon Calvaresi 6538 E Pomona St #60 Indianapolis IN 46222
Galen Cantres 617 Nw 36th Ave Brook Park OH 44142
Alpha Palaia 43496 Commercial Dr #29 Cherry Hill NJ 08003
Jeanice Claucherty 19 Amboy Ave Miami FL 33142
Celeste Korando 7 W Pinhook Rd Lynbrook NY 11563
Carmela Cookey 9 Murfreesboro Rd Chicago IL 60623
Kimberlie Duenas 8100 Jacksonville Rd #7 Hays KS 67601
Dalene Riden 66552 Malone Rd Plaistow NH 03865
Wilda Giguere 1747 Calle Amanecer #2 Anchorage AK 99501
Roslyn Chavous 63517 Dupont St Jackson MS 39211
Pamella Schmierer 5161 Dorsett Rd Homestead FL 33030
Peggie Sturiale 9 N 14th St El Cajon CA 92020
Rolande Spickerman 65 W Maple Ave Pearl City HI 96782
Elouise Gwalthney 9506 Edgemore Ave Bladensburg MD 20710
Jaclyn Bachman 721 Interstate 45 S Colorado Springs CO 80919
Kasandra Semidey 369 Latham St #500 Saint Louis MO 63102
Iluminada Ohms 72 Southern Blvd Mesa AZ 85204
Ernie Stenseth 45 E Liberty St Ridgefield Park NJ 07660
Ahmed Angalich 2 W Beverly Blvd Harrisburg PA 17110
Melissa Wiklund 61 13 Stoneridge #835 Findlay OH 45840
Alease Buemi 4 Webbs Chapel Rd Boulder CO 80303
Nichelle Meteer 72 Beechwood Ter Chicago IL 60657
Teddy Pedrozo 46314 Route 130 Bridgeport CT 06610
Rosio Cork 4 10th St W High Point NC 27263
Rory Papasergi 83 County Road 437 #8581 Clarks Summit PA 18411
Ma Layous 78112 Morris Ave North Haven CT 06473
Donte Kines 3 Aspen St Worcester MA 01602
Marjory Mastella 71 San Mateo Ave Wayne PA 19087
Edna Miceli 555 Main St Erie PA 16502
Britt Galam 2500 Pringle Rd Se #508 Hatfield PA 19440
Aliza Baltimore 1128 Delaware St San Jose CA 95132
Valentine Gillian 775 W 17th St San Antonio TX 78204
Matthew Neither 636 Commerce Dr #42 Shakopee MN 55379
Thurman Manno 9387 Charcot Ave Absecon NJ 08201
Eun Coody 84 Bloomfield Ave Spartanburg SC 29301
Natalie Fern 7140 University Ave Rock Springs WY 82901
Alishia Sergi 2742 Distribution Way New York NY 10025
Beckie Silvestrini 7116 Western Ave Dearborn MI 48126
Geoffrey Acey 7 West Ave #1 Palatine IL 60067
Mitzie Hudnall 17 Jersey Ave Englewood CO 80110
Jose Stockham 128 Bransten Rd New York NY 10011
Paz Sahagun 919 Wall Blvd Meridian MS 39307
Jutta Amyot 49 N Mays St Broussard LA 70518
Shonda Greenbush 82 Us Highway 46 Clifton NJ 07011
Tiera Frankel 87 Sierra Rd El Monte CA 91731
Skye Fillingim 25 Minters Chapel Rd #9 Minneapolis MN 55401
Gearldine Gellinger 4 Bloomfield Ave Irving TX 75061
Tarra Nachor 39 Moccasin Dr San Francisco CA 94104
Martina Staback 7 W Wabansia Ave #227 Orlando FL 32822
Sheridan Zane 2409 Alabama Rd Riverside CA 92501
Freeman Gochal 383 Gunderman Rd #197 Coatesville PA 19320
Markus Lukasik 89 20th St E #779 Sterling Heights MI 48310
Jovita Oles 8 S Haven St Daytona Beach FL 32114
Result: -9.9999999e+07
Runtime: 496.81 ms

BATCH>
BATCH> # Test 24: DELETE test(mark records)
BATCH> city=Abilene & state=TX delete from us-500
DEBUG: Filter=[city=Abilene&state=TX]
DIAG: Analyzing Query [city=Abilene&state=TX]
Error: Table us-500 not found.
Delete #315
Error: Table us-500 not found.
Delete #477
Action Complete. 2 records affected.
Runtime: 3071.699 ms

BATCH>
BATCH> # Test 25: Verify deletion(should show 0 matches)
BATCH> city=Abilene & state=TX
DEBUG: Filter=[city=Abilene&state=TX]
DIAG: Analyzing Query [city=Abilene&state=TX]
Strategy: Complex Scan
------------------------------------------------
Lilli Scriven 33 State St Abilene TX 79601
Glory Schieler 5 E Truman Rd Abilene TX 79602
------------------------------------------------
2 matches found.
Runtime: 3167.115 ms

BATCH>
BATCH> # Test 26: UNDELETE test(restore records)
BATCH> city=Abilene & state=TX undelete from us-500
DEBUG: Filter=[city=Abilene&state=TX]
DIAG: Analyzing Query [city=Abilene&state=TX]
Error: Table us-500 not found.
Restore #315
Error: Table us-500 not found.
Restore #477
Action Complete. 2 records affected.
Runtime: 3068.579 ms

BATCH>
BATCH> # Test 27: Verify undelete(should show 2 matches)
BATCH> city=Abilene & state=TX
DEBUG: Filter=[city=Abilene&state=TX]
DIAG: Analyzing Query [city=Abilene&state=TX]
Strategy: Complex Scan
------------------------------------------------
Lilli Scriven 33 State St Abilene TX 79601
Glory Schieler 5 E Truman Rd Abilene TX 79602
------------------------------------------------
2 matches found.
Runtime: 3168.426 ms

BATCH>
BATCH> # Test 28: Multi-condition with jobsort fields
BATCH> state=CA & age>50 & salary>100000
DEBUG: Filter=[state=CA&age>50&salary>100000]
DIAG: Analyzing Query [state=CA&age>50&salary>100000]
Strategy: Complex Scan
------------------------------------------------
Glendora Sarbacher 2140 Diamond Blvd Rohnert Park CA 94928
Dorothy Chesterfield 469 Outwater Ln San Diego CA 92126
Charlene Hamilton 985 E 6th Ave Santa Rosa CA 95407
Louisa Cronauer 524 Louisiana Ave Nw San Leandro CA 94577
Dominque Dickerson 69 Marquette Ave Hayward CA 94545
Aliza Baltimore 1128 Delaware St San Jose CA 95132
Staci Schmaltz 18 Coronado Ave #563 Pasadena CA 91106
Georgene Montezuma 98 University Dr San Ramon CA 94583
Gail Similton 62 Monroe St Thousand Palms CA 92276
------------------------------------------------
9 matches found.
Runtime: 4058.21 ms

BATCH>
BATCH> # Test 29: LIKE with multiple conditions
BATCH> city~%Los% & occupation=Manager
DEBUG: Filter=[city~%Los%&occupation=Manager]
DIAG: Analyzing Query [city~%Los%&occupation=Manager]
Strategy: Complex Scan
------------------------------------------------
Kiley Caldarera 25 E 75th St #69 Los Angeles CA 90034
Cristal Samara 4119 Metropolitan Dr Los Angeles CA 90021
------------------------------------------------
2 matches found.
Runtime: 4575.886 ms

BATCH>
BATCH> # Test 30: LIKE with multiple conditions
BATCH> city~%Los% & occupation=Engineer
DEBUG: Filter=[city~%Los%&occupation=Engineer]
DIAG: Analyzing Query [city~%Los%&occupation=Engineer]
Strategy: Complex Scan
------------------------------------------------
------------------------------------------------
0 matches found.
Runtime: 4621.754 ms

BATCH>
BATCH> # Test 31: Save format As .fmt
BATCH> format person Last_Name, First_Name, Address, City, State, Zip
Format 'person' saved to person.fmt
Runtime: 12.527 ms

BATCH>
BATCH> # Test 32: Test format
BATCH> state=LA format occupation
DEBUG: Filter=[state=LA]
DIAG: Analyzing Query [state=LA]
Strategy: Linear Scan
------------------------------------------------
Cordelia Storment Analyst 81000 39 LA
Arlene Klusman Analyst 82000 22 LA
James Butt Analyst 116000 63 LA
Solange Shinko Analyst 109000 19 LA
Kayleigh Lace Developer 37000 29 LA
Jutta Amyot Engineer 107000 63 LA
Larae Gudroe Manager 40000 36 LA
Terrilyn Rodeigues Manager 58000 34 LA
Willodean Konopacki Manager 61000 30 LA
------------------------------------------------
9 matches found.
Runtime: 1442.773 ms

BATCH>
BATCH> # Test 33: Final complex query
BATCH> (state=CA | state=TX) & age>50 order by salary desc show last_name, first_name, state, age, salary limit 10
DEBUG: Filter=[(state=CA|state=TX)&age>50]
DIAG: Analyzing Query [(state=CA|state=TX)&age>50]
Strategy: Complex Scan
------------------------------------------------
Sorting 34 records...
Sarbacher Glendora CA 55 129000
Schmaltz Staci CA 60 120000
Cronauer Louisa CA 64 117000
Montezuma Georgene CA 62 117000
Similton Gail CA 59 117000
Chesterfield Dorothy CA 59 112000
Hamilton Charlene CA 65 109000
Dickerson Dominque CA 62 108000
Baltimore Aliza CA 61 106000
Keener Ruthann TX 61 102000
------------------------------------------------
34 matches found.
Runtime: 6220.476 ms

BATCH>
BATCH> # --- NEW FUNCTIONALITY TESTS ---
BATCH>
BATCH> # Test 34: INSERT a new record
BATCH> INSERT INTO us500 First_Name=Testy, Last_Name=McTester, city=DebugCity, state=TX, zip=99999
Inserted new record #508 into us500
Auto-Updating Index: city...
Indexing us500.city...
..... Read 508 records.
Saved us500_city.ndx
Auto-Updating Index: state...
Indexing us500.state...
..... Read 508 records.
Saved us500_state.ndx
Runtime: 1308.76 ms

BATCH>
BATCH> # Test 35: Verify INSERT(Should find Testy)
BATCH> last_name=McTester
DEBUG: Filter=[last_name=McTester]
DIAG: Analyzing Query [last_name=McTester]
Strategy: Linear Scan
------------------------------------------------
------------------------------------------------
0 matches found.
Runtime: 1317.898 ms

BATCH>
BATCH> # Test 36: UPDATE(String assignment)
BATCH> UPDATE us500 SET city="FixedCity" WHERE last_name=McTester
DEBUG: Filter=[last_name=McTeste]
DIAG: Analyzing Query [last_name=McTeste]
Action Complete. 0 records affected.
Auto-Updating Index: city...
Indexing us500.city...
..... Read 508 records.
Saved us500_city.ndx
Auto-Updating Index: state...
Indexing us500.state...
..... Read 508 records.
Saved us500_state.ndx
Runtime: 2406.186 ms

BATCH>
BATCH> # Test 37: Verify UPDATE
BATCH> last_name=McTester show first_name, last_name, city
DEBUG: Filter=[last_name=McTester]
DIAG: Analyzing Query [last_name=McTester]
Strategy: Linear Scan
------------------------------------------------
------------------------------------------------
0 matches found.
Runtime: 1304.133 ms

BATCH>
BATCH> # Test 38: ARITHMETIC UPDATE(The "Age+1" Logic)
BATCH> # First, show current ages For a small group
BATCH> occupation=Student limit 5 show last_name, age
DEBUG: Filter=[occupation=Student]
DIAG: Analyzing Query [occupation=Student]
DIAG: Index Found! Switching to Indexed Strategy.
Strategy: Indexed Range
------------------------------------------------
Virgina Tegarden 755 Harbor Way Milwaukee WI 53226
Christiane Eschberger 96541 W Central Blvd Phoenix AZ 85034
Alaine Bergesen 7667 S Hulen St #42 Yonkers NY 10701
Jesusita Flister 3943 N Highland Ave Lancaster PA 17601
An Fritz 506 S Hacienda Dr Atlantic City NJ 08401
Annmarie Castros 80312 W 32nd St Conroe TX 77301
Jerry Dallen 393 Lafayette Ave Richmond VA 23219
------------------------------------------------
7 matches found.
Runtime: 70.622 ms

BATCH>
BATCH> # Test 39: Execute Arithmetic Update
BATCH> UPDATE jobsort SET age = age + 1 WHERE occupation=Student
DEBUG: Filter=[occupation=Student]
DIAG: Analyzing Query [occupation=Student]
Updated #432
Updated #433
Updated #434
Updated #435
Updated #436
Updated #437
Updated #438
Action Complete. 7 records affected.
Auto-Updating Index: age...
Indexing jobsort.age...
..... Read 500 records.
Saved jobsort_age.ndx
Auto-Updating Index: id_ref...
Indexing jobsort.id_ref...
..... Read 500 records.
Saved jobsort_id_ref.ndx
Auto-Updating Index: occupation...
Indexing jobsort.occupation...
..... Read 500 records.
Saved jobsort_occupation.ndx
Runtime: 3644.226 ms

BATCH>
BATCH> # Test 40: Verify Arithmetic Update(Ages should be +1)
BATCH> occupation=Student limit 5 show last_name, age
DEBUG: Filter=[occupation=Student]
DIAG: Analyzing Query [occupation=Student]
DIAG: Index Found! Switching to Indexed Strategy.
Strategy: Indexed Range
------------------------------------------------
Virgina Tegarden 755 Harbor Way Milwaukee WI 53226
Christiane Eschberger 96541 W Central Blvd Phoenix AZ 85034
Alaine Bergesen 7667 S Hulen St #42 Yonkers NY 10701
Jesusita Flister 3943 N Highland Ave Lancaster PA 17601
An Fritz 506 S Hacienda Dr Atlantic City NJ 08401
Annmarie Castros 80312 W 32nd St Conroe TX 77301
Jerry Dallen 393 Lafayette Ave Richmond VA 23219
------------------------------------------------
7 matches found.
Runtime: 70.091 ms

BATCH>
BATCH> # Test 41: Mode SQL(Switch parser To SQL compatibility)
BATCH> Mode SQL
Mode: SQL
Runtime: 0.3020000011 ms

BATCH>
BATCH> # Test 42: SQL SELECT Syntax
BATCH> SELECT first_name, last_name, city FROM us500 WHERE state='CA' LIMIT 3
DEBUG: Filter=[state="CA"]
DIAG: Analyzing Query [state="CA"]
Strategy: Linear Scan
------------------------------------------------
Stephaine Barfield Gardena
Thaddeus Ankeny Roseville
Xuan Rochin San Mateo
------------------------------------------------
3 matches found.
Runtime: 87.173 ms

BATCH>
BATCH> # Test 43: SQL AGGREGATE
BATCH> SELECT COUNT(occupation) FROM jobsort
DIAG: Analyzing Query []
Result: 500
Runtime: 1217.56 ms

BATCH>
BATCH> # Test 44: Return To NATIVE mode
BATCH> Mode NATIVE
Mode: Native
Runtime: 0.6739999987 ms

BATCH>
BATCH> # Test 45: Ruler Tool(Visual Debugging)
BATCH> ruler jobsort.dat
File: [jobsort.dat] Length: 42
Data: [ 3 Venere 63 Analyst 106000]
| | | | | | | |
5 10 15 20 25 30 35 40
Runtime: 9.019000001 ms

BATCH>
BATCH> # Test 46: Explicit Indexing Controls
BATCH> Set INDEXING OFF
Auto-Indexing Disabled.
Runtime: 0.2430000007 ms

BATCH> Set INDEXING ON
Auto-Indexing Enabled.
Runtime: 0.1909999996 ms

Batch Complete; Total Time: 79373.955 ms
Runtime: 79374.611 ms

DB:us500>

This vibe coding is fun, although it took me/us (but mostly me) a number of hours to debug several problems in "UPDATE jobsort SET age = age + 1 WHERE occupation = student". Multiple logic errors. Gemini kept inventing solutions that weren't and speculating about "silent" errors. TRACE LIST provided the final clues. So there's still a use for my 57 years of coding and debugging practice.

Some of these tests didn't SHOW the data expected, but that's a debugging issue for tomorrow.

~
Edited 2026-01-23 11:32 by lizby

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

Briano
Newbie

Joined: 20/01/2026
Location: Canada
Posts: 10

Posted: 01:25am 08 Feb 2026

Copy link to clipboard

Print this post

Where can I download the employees.csv and customers.csv files?
Thank you.....Brian

lizby
Guru

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

Posted: 04:25am 08 Feb 2026

Copy link to clipboard

Print this post

I don't remember exactly where I got the NorthWinds CSV files--I did a bit of scratching around--but perplexity says:

Quote A ready‑made collection of Northwind CSV files (one CSV per table) is hosted as a ZIP archive at:
http://bitnine.net/tutorial/import-northwind-dataset.zip

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

Briano
Newbie

Joined: 20/01/2026
Location: Canada
Posts: 10

Posted: 04:54pm 09 Feb 2026

Copy link to clipboard

Print this post

lizby said I don't remember exactly where I got the NorthWinds CSV files--I did a bit of scratching around--but perplexity says:

Quote A ready‑made collection of Northwind CSV files (one CSV per table) is hosted as a ZIP archive at:
http://bitnine.net/tutorial/import-northwind-dataset.zip

Thank you @lizby and perplexity ....Brian

lizby
Guru

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

Posted: 01:44am 15 Feb 2026

Copy link to clipboard

Print this post

I had a 2-week gap where a physical world construction project got in the way, but back at it yesterday and today. For now, I am content with where it is.

Quote PicoMite DB Engine: Command Reference Platform: Raspberry Pi Pico 2 (RP2350)
1. Database Setup & Management These commands handle the creation and loading of database environments.
make-db <dbname> <csv_list> Description: Converts one or more CSV files into the engine's binary format (.dat) and creates the schema definition (.def). Automatically injects a "Soft Delete" flag (delFlag). Example: make-db us500 employees.csv, jobs.csv
use-db <dbname> Description: Loads a database environment (tables and schema). Example: use-db us500
Relational Joins (Schema Configuration) Description: PicoDB supports dynamic, memory-efficient relational joins. To link two tables, open the generated .def file and add a LINK directive at the bottom specifying the child table and the foreign key field. Syntax: LINK, <child_table>, <foreign_key> Example: LINK, jobsort, id_ref
ruler <filename> Description: Displays the first record of a file with a visual ruler. Useful for calculating fixed-width field positions manually. Example: ruler employees.csv
HELP / ? Description: Displays the built-in help screen containing a quick reference guide to all native commands.
EXIT Description: Closes all files and terminates the engine.
2. Data Retrieval (Native Mode) Syntax: [filter] [ORDER BY field [DESC]] [SHOW fields | SHOW ALL | FORMAT name] [LIMIT n] Queries are constructed by chaining clauses. If no command is recognized, the engine treats the input as a Filter.
Clauses
Filter Condition: Standard comparison operators (=, >, <, >=, <=, <>). Strings with spaces must be quoted. Queries can span multiple joined tables seamlessly.
Like Operator: Use ~ for wildcard searches (% = multi-char, _ = single-char).
Compound Logic: Use & (AND), | (OR), and parentheses () for complex queries.
SHOW <fields>: Selects specific columns to display (comma-separated).
SHOW ALL: Dumps the raw disk buffer to the screen for lightning-fast, perfectly aligned column printing of all fields across all joined tables.
FORMAT <name>: Applies a saved view (see Tools section).
ORDER BY <field> [DESC]: Sorts the results. Default is Ascending.
LIMIT <n>: Restricts the number of results returned.
TXT <file> / CSV <file>: Exports the results to a text or CSV file on the SD card.
Examples
Simple Search: zip="90210"
Cross-Table Query: state="TX" & occupation="Engineer"
Wildcard: title ~ "Eng%" (Matches Engineer, English, etc.)
Export: state="NY" CSV ny_export.csv

Fuller documentation attached.

Quote > load "d7.bas"
> memory
Program:
78K (23%) Program (2554 lines)
250K (77%) Free

Saved Variables:
16K (100%) Free

RAM:
0K ( 0%) 0 Variables
4K ( 1%) General
371K (99%) Free
> files
A:/
<DIR> .
<DIR> ..
00:00 01-01-2024 4 bootcount
00:14 01-01-2024 148 copyfile.bas
00:09 01-01-2024 86714 d6b5c.bas
01:09 01-01-2024 87879 d6b5d.bas
02:18 01-01-2024 87313 d7.bas
02:19 01-01-2024 22000 jobsort.dat
00:16 01-01-2024 22000 jobsort.good
02:19 01-01-2024 8000 jobsort_age.ndx
02:19 01-01-2024 8000 jobsort_id_ref.lnk
02:19 01-01-2024 20000 jobsort_occupation.ndx
02:19 01-01-2024 8000 jobsort_salary.ndx
04:05 01-01-2024 2315 native.hlp
00:50 01-01-2024 50 occupation.fmt
02:19 01-01-2024 50 person.fmt
00:21 01-01-2024 706 sql.hlp
00:46 01-01-2024 3639 test.bat
00:49 01-01-2024 45 us500-default.fmt
02:19 01-01-2024 107642 us500.dat
01:20 01-01-2024 1298 us500.def
00:14 01-01-2024 107000 us500.good
02:19 01-01-2024 20120 us500_city.ndx
02:19 01-01-2024 20120 us500_state.ndx
2 directories, 22 files, 13778944 bytes free
>

On the WeAct RP2350B module (no PSRAM yet--on order), 77% of program space free, and 13 megabytes on drive A:. Code is 2554 lines. Development with Gemini is still smooth, but debugging can be complicated if it doesn't see where the problem is. 45 tests run with the BATCH command in 78 seconds (CPUSPEED 360000).

PicoDB.pdf

PicoDB.zip

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

lizby
Guru

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

Posted: 03:08pm 06 Mar 2026

Copy link to clipboard

Print this post

The latest library.bas for PicoDB, with the db .hlp and .doc files and a user-code stub.

See PicoRR for "Vibe Virtual Model Railroad" example.

PicoDB_lib.zip

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

Print this page

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