 Notice. New forum software under development. It's going to miss a few functions and look a bit ugly for a while, but I'm working on it full time now as the old forum was too unstable. Couple days, all good. If you notice any issues, please contact me. |
Forum Index : Microcontroller and PC projects : First stab at an MMBasic database
| Author | Message | ||||
| lizby Guru  Joined: 17/05/2016 Location: United StatesPosts: 3540 |
The new structures open a lot of opportunities. Users have already suggested one--databases. I had some fabricated US personal data (500 invented names and locations, but located in accordance with actual demographics). I asked Gemini to convert it into a file for MMBasic on the Pico2 using a structure, and then to produce a simple query program for it to ask for single or AND and/or OR queries. It's fairly fast--half a second to find 69 records out of 500 which have a state of either MD or NJ; quarter of a second to find the single record with a City of Baltimore and a Zip of 21230. This is the 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 Here's the whole query program: 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" I've uploaded the single database file as a zip file: us-500.dbt. Much more could be done--multiple files, indexes if speed is an issue, forms. I would imagine that 4-8 files with up to 1000 records each would be about as much as anyone would be likely to want on a picomite, especially since you can run SQLite on far more powerful and flexible platforms. Congratulations to Peter on this fresh inspiration. us-500.zip PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on fruitoftheshed |
||||
| Amnesie Guru Joined: 30/06/2020 Location: GermanyPosts: 745 |
 Amazing all those new opportunities to try new stuff! Wow! |
||||
| lizby Guru Joined: 17/05/2016 Location: United StatesPosts: 3540 |
Time for an update. I made some progress yesterday with Gemini, but it was often frustrating. It would forget context, create test data (incorrectly) when I already had test data , make the same errors repeatedly, and make new programs instead of building on the one we were working on. Six months ago I might have persisted, since the progress was still tangible, but this morning I subscribed to Claude Pro. It has mostly gotten things perfect--the only error it has repeated is putting a DIM were it can be looped to multiple times. I added a second database for each (fictitious) person in the original one with Age, Occupation, and Salary, plus a link to the original database and Last_Name so I could confirm that I was matching correctly. Then I sorted it on Occupation using STRUCT SORT so the rows wouldn't just line up. With Gemini I got "=", ">", "<" operators working, and "&" (and) and "|" (or) conjunctions, and (with Claude) parentheses to get order correct. With Claude, the main program can now build an index on any field (with, e.g., "index jobsort on Age"), and can load certain special indices (main table to secondary, i.e. us-500 to jobsort). At this point the program is 872 lines long. All MMBasic, of course. A search for (State=PA|State=NJ)&Age>49 (that takes a join of the 2 tables) took 1.3 seconds to retrieve 20 records (from 500). After loading the index into memory (with INDEX LOAD jobsort_recno), the same query took .3 seconds. I should note that Claude added about 600 working lines to a 274-line MMBasic program between 12:45 and 6:15 this afternoon. As I recall, my most productive day, about 40 years ago, was 60 line of complex C code, debugged. Here's what it looked like: > 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: I'm looking forward to seeing regex for STRUCT(FIND ...) Edited 2026-01-09 10:33 by lizby PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on fruitoftheshed |
||||
| matherp Guru Joined: 11/12/2012 Location: United KingdomPosts: 10828 |
Excellent work  I use AI through the Github Copilot Chat plugin to VSCODE. This give me access to a range of models for a single monthly fee. For simple stuff, I use Claude Sonnet or GPT-5.1-Codex-Max. But for the hard stuff Clause Opus 4.5 is miles ahead of the others but consumes budget much faster with a x3 budget ratio.  Edited 2026-01-09 18:43 by matherp |
||||
 Print this page |
 |
The Back Shed's forum code is written, and hosted, in Australia. | © JAQ Software 2026 |
