MBEESQL.MWB - Connecting a microbee program to the world

[Graham72]

(SQL, or Structured Query Language, is the standard used by database systems for over 20 years worldwide, to store, retrieve and manage structured data.)

Originally I wrote a SQL demo program and then thought why not make it actually do something useful.

MBEESQL is a relational database engine written in MicroWorld BASIC for the MicroBee Z80 running CP/M. It implements four fundamental SQL database operations known as CRUD — Create (INSERT), Read (SELECT), Update (UPDATE) and Delete (DELETE) — along with WHERE clauses for filtered searching, primary key management, and a VACUUM command to compact the database by removing deleted rows.  MBEESQL brings these concepts to the Microbee, emulated using UBEE512. The BASIC program stores its data in DBMWB.DAT — a plain comma-delimited text file cataloguing the MicroWorld BASIC programs from the MBUG disk library by name and disc nos. RAM constraints prevent listing what each program does.

The flag field in each record marks it as Active or Deleted, enabling soft-delete — records are never immediately removed but flagged for later compaction. All write operations implement ACID compliance: data is written atomically to a temporary file first, then renamed over the live database, ensuring a system crash at any point leaves the data intact and recoverable. On startup MBEESQL automatically checks for and recovers any interrupted write. Browsing a large collection can be sped up by using --clock-200 in UBEE512.

Local PC access is enabled by rcpmfs, which maps a Windows folder as a CP/M drive. DBMWB.DAT is stored in that folder and updated whenever MBEESQL writes it. The beesql.html web viewer, opened in any local browser, reads the file directly and auto-refreshes every five seconds — search, sort and scroll across all 1,199 records is instant. An Excel workbook accesses the same data with filtering and charting.

Internet access via ngrok requires a small Python watcher script on the PC that serves DBMWB.DAT over a local HTTP server. ngrok creates a secure public tunnel with a stable URL. The web viewer, with one line changed to fetch from that URL, becomes globally accessible — anyone with the link can browse the MBUG catalogue in real time, seeing updates within seconds of entry on the MicroBee.

The result is a three-tier system — emulated MicroBee for SQL data entry a BASIC program, a PC as the bridge, and any browser anywhere as the front end — all anchored by a 21KB plain text file written in 1980's Microworld BASIC. 

---

Anyone interested? I only need to get my head around ngrok to complete the proof of concept. cheers G

[ChickenMan]

Sounds interesting, so yes please

[Graham72]

an update, apart from a strap undoing on my backpack -laptop screen smashed, and then breaking my little toe - Progress is slow as memory constraints have meant I have broken up the data into three files.

Is there a contrainst into how many active file names BASIC6.23 can cope with? I am using 1.dat 2.dat 3.dat 1.tmp 2.tmp 3.tmp. The temp files are created by NAME xxx.dat AS xxx.tmp.

I have succesfully created the excel and .html files to display data outside the Microbee environment. A big thanks to ChickenMan for testing & finding several problems in the html file. (fixed)

[fathertedcrilly]

(SQL, or Structured Query Language, is the standard used by database systems for over 20 years worldwide, to store, retrieve and manage structured data.)

Originally I wrote a SQL demo program and then thought why not make it actually do something useful.

MBEESQL is a relational database engine written in MicroWorld BASIC for the MicroBee Z80 running CP/M. It implements four fundamental SQL database operations known as CRUD — Create (INSERT), Read (SELECT), Update (UPDATE) and Delete (DELETE) — along with WHERE clauses for filtered searching, primary key management, and a VACUUM command to compact the database by removing deleted rows.  MBEESQL brings these concepts to the Microbee, emulated using UBEE512. The BASIC program stores its data in DBMWB.DAT — a plain comma-delimited text file cataloguing the MicroWorld BASIC programs from the MBUG disk library by name and disc nos. RAM constraints prevent listing what each program does.

it'd be interesting to see how you've modelled your data structures.

Given the relative paucity of what is available in basic

Thanks
Tony

[Graham72]

Original Plan that has been modfied for mwbasic

The program maintains up to 1200 records in memory using four parallel arrays — R() for auto-increment IDs, N0() for program names, N() for disk numbers, and N2() for active/deleted flags — with working variables A1$ and A2$ handling user input and A5/A6/A6
/A6 supporting search queries. The data is stored in sequential file called DBMWB.DAT, now 3 files, with each record stored as a comma-separated line in the format id,name,disk,flag.  The command set mirrors core SQL operations: Insert adds a new record with the next available ID, Select lists all active records, Select Where filters by a nominated field and search term, Update modifies a record by ID, and Delete performs a soft delete by setting the flag to 0 rather than removing the line. A Vacuum command rewrites the file with deleted records stripped out — equivalent to SQLite's own VACUUM — and a Count command returns the number of active records. The conceptual model is essentially identical to a lightweight embedded database like SQLite.

[Graham72]

Sorry, too many versions on my laptop this is the latest listing: