"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

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

  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.

Very cool!

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

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

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

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

  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