Type US500
 DelFlag    As STRING LENGTH 1
 First_Name As STRING LENGTH 12
 Last_Name  As STRING LENGTH 15
 Company    As STRING LENGTH 32
 Address    As STRING LENGTH 33
 City       As STRING LENGTH 21
 County     As STRING LENGTH 22
 State      As STRING LENGTH 4
 ZIP        As STRING LENGTH 5
 Phone1     As STRING LENGTH 14
 Phone2     As STRING LENGTH 14
 Email      As STRING LENGTH 35
End Type

Option EXPLICIT
Option DEFAULT NONE

' Define Structure
Type US500
 DelFlag    As STRING LENGTH 1
 First_Name As STRING LENGTH 12
 Last_Name  As STRING LENGTH 15
 Company    As STRING LENGTH 32
 Address    As STRING LENGTH 33
 City       As STRING LENGTH 21
 County     As STRING LENGTH 22
 State      As STRING LENGTH 4
 ZIP        As STRING LENGTH 5
 Phone1     As STRING LENGTH 14
 Phone2     As STRING LENGTH 14
 Email      As STRING LENGTH 35
End Type

Dim db As US500

' --- Search Storage ---
' We store "OR Groups". Each group contains a list of "AND Conditions".
' Max 5 OR groups, Max 5 ANDs per group to save memory.
Dim OrGroups$(5) LENGTH 100
Dim INTEGER NumOrGroups

' Temporary parsing variables
Dim rawInput$, currentOr$, currentAnd$, token$
Dim qFld$, qVal$, dbVal$
Dim INTEGER p, i, j, k, orPos, andPos, matchOr, matchAnd
Dim INTEGER count = 0
Dim FLOAT startTime, duration
PRESS ANY KEY ...
>
> list
Option EXPLICIT
Option DEFAULT NONE

' Define Structure
Type US500
 DelFlag    As STRING LENGTH 1
 First_Name As STRING LENGTH 12
 Last_Name  As STRING LENGTH 15
 Company    As STRING LENGTH 32
 Address    As STRING LENGTH 33
 City       As STRING LENGTH 21
 County     As STRING LENGTH 22
 State      As STRING LENGTH 4
 ZIP        As STRING LENGTH 5
 Phone1     As STRING LENGTH 14
 Phone2     As STRING LENGTH 14
 Email      As STRING LENGTH 35
End Type

Dim db As US500

' --- Search Storage ---
' We store "OR Groups". Each group contains a list of "AND Conditions".
' Max 5 OR groups, Max 5 ANDs per group to save memory.
Dim OrGroups$(5) LENGTH 100
Dim INTEGER NumOrGroups

' Temporary parsing variables
Dim rawInput$, currentOr$, currentAnd$, token$
Dim qFld$, qVal$, dbVal$
Dim INTEGER p, i, j, k, orPos, andPos, matchOr, matchAnd
Dim INTEGER count = 0
Dim FLOAT startTime, duration

' ---------------------------------------------------------
' 1. Prompt and Parse (The "Compiler")
' ---------------------------------------------------------
Print "Search DB (Format: Field=Value & Field=Value | Field=Value)"
Line Input "Query: ", rawInput$

If rawInput$ = "" Then End

' Normalize input (Uppercase)
rawInput$ = UCase$(rawInput$)
NumOrGroups = 0

' --- Step A: Split by Pipe '|' (OR Logic) ---
Do While rawInput$ <> ""
 orPos = Instr(rawInput$, "|")

 If orPos > 0 Then
   currentOr$ = Trim$(Left$(rawInput$, orPos - 1))
   rawInput$ = Mid$(rawInput$, orPos + 1)
 Else
   currentOr$ = Trim$(rawInput$)
   rawInput$ = ""
 End If

 If currentOr$ <> "" Then
   NumOrGroups = NumOrGroups + 1
   OrGroups$(NumOrGroups) = currentOr$
 End If
Loop

Print "----------------------------------------------------------------"
Print "DEBUG: Parsed logic:"
For i = 1 To NumOrGroups
 Print "  OR Group " + Str$(i) + ": " + OrGroups$(i)
Next i
Print "----------------------------------------------------------------"

' ---------------------------------------------------------
' 2. Search Loop
' ---------------------------------------------------------
Print "Searching..."
startTime = Timer

Open "us-500.dbt" For INPUT As #1

Do While Not Eof(#1)
 Struct LOAD #1, db

 matchOr = 0 ' Assume the record doesn't match any OR group yet

 ' --- Loop through OR Groups ---
 ' If ANY of these groups evaluates to TRUE, we print the record.
 For i = 1 To NumOrGroups
   currentOr$ = OrGroups$(i)
   matchAnd = 1 ' Assume this specific group is TRUE until an AND fails

   ' --- Loop through AND Conditions inside the group ---
   Do While currentOr$ <> ""
     andPos = Instr(currentOr$, "&")

     If andPos > 0 Then
       token$ = Trim$(Left$(currentOr$, andPos - 1))
       currentOr$ = Mid$(currentOr$, andPos + 1)
     Else
       token$ = Trim$(currentOr$)
       currentOr$ = ""
     End If

     ' Parse Field=Value for this single condition
     p = Instr(token$, "=")
     If p > 0 Then
       qFld$ = Trim$(Left$(token$, p - 1))
       qVal$ = Trim$(Mid$(token$, p + 1))

       ' Check Database Value
       dbVal$ = ""
       Select Case qFld$
         Case "CITY"       : dbVal$ = UCase$(Trim$(db.City, " ", "B"))
         Case "STATE"      : dbVal$ = UCase$(Trim$(db.State, " ", "B"))
         Case "ZIP"        : dbVal$ = UCase$(Trim$(db.ZIP, " ", "B"))
         Case "LAST_NAME"  : dbVal$ = UCase$(Trim$(db.Last_Name, " ", "B"))
         Case "FIRST_NAME" : dbVal$ = UCase$(Trim$(db.First_Name, " ", "B"))
         Case "COMPANY"    : dbVal$ = UCase$(Trim$(db.Company, " ", "B"))
         Case Else         : matchAnd = 0 ' Invalid field fails the group
       End Select

       If dbVal$ <> qVal$ Then
         matchAnd = 0
         Exit Do ' Short circuit: This AND group failed, stop checking it
       End If
     End If
   Loop

   ' If matchAnd is still 1, this OR group passed!
   If matchAnd = 1 Then
     matchOr = 1
     Exit For ' Short circuit: We found a valid OR group, no need to check others
   End If

 Next i

 ' If matchOr is 1, print the record
 If matchOr = 1 Then
   Print db.First_Name, db.Last_Name, db.City, db.State, db.ZIP
   count = count + 1
 End If
Loop

Close #1

duration = Timer - startTime

' ---------------------------------------------------------
' 3. Report
' ---------------------------------------------------------
Print "----------------------------------------------------------------"
Print "Found " + Str$(count) + " records."
Print "Runtime: " + Str$(duration) + " ms"

> run

Enter Query (e.g. Age>39) OR Command (Index tablename on fieldname)
Cmd: load index jobsort_recno
Loaded jobsort_recno index (500 records)

Enter Query (e.g. Age>39) OR Command (Index tablename on fieldname)
Cmd: (state=pa|state=nj)&age>49
Using index: us-500_STATE.ndx
Searching...
--------------------------------------------------------------------------------
Ronny Caiafa, 73 Southern Blvd, PA 19103 54 Engineer
Edna Miceli, 555 Main St, PA 16502 59 Teacher
Elly Morocco, 7 W 32nd St, PA 16502 57 Teacher
Jennie Drymon, 63728 Poway Rd #1, PA 18509 65 Teacher
Loren Asar, 6 Ridgewood Center Dr, PA 18518 54 Developer
Freeman Gochal, 383 Gunderman Rd #197, PA 19320 64 Engineer
Tyra Shields, 3 Fort Worth Ave, PA 19106 63 Nurse
Jacqueline Rowling, 1 N San Saba, PA 16501 65 Manager
Carmen Sweigard, 61304 N French Rd, NJ 08873 51 Sales
Becky Mirafuentes, 30553 Washington Rd, NJ 07062 53 Engineer
Ernie Stenseth, 45 E Liberty St, NJ 07660 64 Teacher
Kimbery Madarang, 798 Lund Farm Way, NJ 07866 54 Engineer
Delisa Crupi, 47565 W Grand Ave, NJ 07105 56 Manager
Candida Corbley, 406 Main St, NJ 08876 62 Developer
Vi Rentfro, 7163 W Clark Rd, NJ 07728 59 Engineer
Nelida Sawchuk, 3 State Route 35 S, NJ 07652 59 Teacher
Tasia Andreason, 4 Cowesett Ave, NJ 07032 51 Developer
Lynelle Auber, 32820 Corkwood Rd, NJ 07104 64 Developer
Alpha Palaia, 43496 Commercial Dr #29, NJ 08003 64 Sales
Selma Husser, 9 State Highway 57 #22, NJ 07306 60 Teacher
--------------------------------------------------------------------------------
20 matches found.
Runtime: 350.966 ms

Enter Query (e.g. Age>39) OR Command (Index tablename on fieldname)
Cmd:

Type US500
 DelFlag    As STRING LENGTH 1
 First_Name As STRING LENGTH 12
 Last_Name  As STRING LENGTH 15
 Company    As STRING LENGTH 32
 Address    As STRING LENGTH 33
 City       As STRING LENGTH 21
 County     As STRING LENGTH 22
 State      As STRING LENGTH 4
 ZIP        As STRING LENGTH 5
 Phone1     As STRING LENGTH 14
 Phone2     As STRING LENGTH 14
 Email      As STRING LENGTH 35
End Type

Type JobRec
 LinkID     As INTEGER
 LastName   As STRING LENGTH 15
 Age        As INTEGER
 Occupation As STRING LENGTH 20
 Salary     As INTEGER
End Type

Type IndexRec
 LinkID     As INTEGER           ' Pointer to Record Position
 StrKey     As STRING LENGTH 50  ' Value for String Sorts (Name)
End Type

| Operation                | Time        | Notes                          |
|--------------------------|-------------|--------------------------------|
| Simple query (indexed)   | 350ms       | Direct index lookup            |
| Complex query            | 730ms       | Sequential scan with join      |
| LIKE pattern match       | 400-2000ms  | Full table scan                |
| COUNT aggregation        | 320ms       | With REDIM PRESERVE & sort     |
| SUM/AVG/MIN/MAX          | 50-60ms     | Single-pass aggregation        |
| Aggregate with WHERE     | 150-1125ms  | Depends on join complexity     |
| Index creation           | ~2000ms     | One-time operation             |

> memory
Program:
 38K (11%) Program (1465 lines)
286K (89%) Free

Saved Variables:
 16K (100%) Free

RAM:
 55K (14%) 86 Variables
133K (35%) General
183K (51%) Free

# 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

# Test 17: COUNT on another field
count 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: Final complex query
(state=CA | state=TX) & age>50 order by salary desc show last_name, first_name, state, age, salary limit 10

CAVEAT: The code is entirely specific to the data files used, and I don't see any way to generalize it so that one could feed it a TYPE structure and then run the queries. So I am now using ChatGPT-5.2 to look into converting TinyDB from python to MMBasic. I don't expect the same performance, but may get generalization.