00100REM *** MBEESQL - MBUG MWB Catalogue .45 ***
00101REM --- VARIABLES ---
00102REM Q  (int)  Max records (450)
00103REM B  (int)  Records loaded count
00104REM X  (int)  Next available ID
00105REM Z  (int)  Load counter (mod 10) / pagination counter
00106REM E  (int)  Loop index (FOR loops)
00107REM C  (int)  Match/active record counter
00108REM K  (int)  Delete/update hit counter / inner loop for N fuzzy search
00109REM J  (int)  Uppercase loop index (SELECT WHERE value)
00110REM M  (int)  Menu/uppercase scratch
00111REM R  (real array) Record IDs  R(Q)
00112REM D  (real array) Disk numbers D(Q)
00113REM F  (real array) Active flags  F(Q) 1=active 0=deleted
00114REM N0 (str array) Program names N0$(Q)
00115REM A0$ Command input / menu selection
00116REM A1$ Insert/update: program name input
00117REM A2$ Insert/update: disk number input
00118REM A3$ Temp copy of N0$(E) for fuzzy N search uppercase conversion
00119REM A4$ Uppercase conversion scratch string
00120REM A5$ WHERE value (search term)
00121REM A6$ WHERE field selector (N/D/I)
00122REM A7$ Current DAT filename (DBMWB1-3.DAT)
00123REM A8$ Temp filename for write (DBMWB?.TMP)
00124REM U0$ Uppercased N0$(E) for SEARCH in fuzzy N match
00125REM Z7$ KEY input scratch (field selection + pagination)
00360REM --- PROGRAM START ---
00361STRS(18000)
00362Q=450:DIM R(Q),N0(Q),D(Q),F(Q)
00364FOR E=1 TO 3
00365ON ERROR GOTO 367
00366NAME "DBMWB"+CHR(E+48)+".TMP" AS "DBMWB"+CHR(E+48)+".DAT"
00367ON ERROR GOTO 0:NEXT E
00400CLS
00420PRINT "  MBEESQL - MBUG MWB Catalogue  "
00440PRINT " 1  Load 0-F  (422 programs)"
00450PRINT " 2  Load G-O  (338 programs)"
00460PRINT " 3  Load P-Z  (439 programs)"
00470PRINT " Q  Quit ",FRE($),FRE(0)
00490PRINT "Select: ";
00491A4$="123Q":GOSUB 9850:A0$=Z7$
00500IF A0$="1":A7$="DBMWB1.DAT":GOTO 560
00510IF A0$="2":A7$="DBMWB2.DAT":GOTO 560
00520IF A0$="3":A7$="DBMWB3.DAT":GOTO 560
00530IF A0$="Q" THEN GOTO 9000
00560GOSUB 6000
00561REM ID OFFSET: FILE1>=1 FILE2>=500 FILE3>=1000 PREVENTS DUPLICATE IDS IN DBMWB.DAT
00562IF A0$="2" AND X<500:X=500
00563IF A0$="3" AND X<1000:X=1000
00570CLS
00590PRINT "  MBEESQL - File: ";A7$
00600PRINT "  ";B;" records loaded"
00620PRINT " I  Insert"
00630PRINT " S  Select all"
00640PRINT " W  select Where"
00650PRINT " U  Update where id=?"
00660PRINT " D  Delete where id=?"
00670PRINT " V  Vacuum"
00680PRINT " C  Count"
00690PRINT " L  Change file"
00700PRINT " Q  Quit"
00710PRINT " H  Help"
00750PRINT
00760PRINT "CMD> ";
00761A4$="ISWUDVCHLQ":GOSUB 9850:A0$=Z7$
00775IF A0$="Q" THEN GOTO 9000
00780IF A0$="I" THEN GOSUB 1000
00790IF A0$="S" THEN GOSUB 2000
00800IF A0$="W" THEN GOSUB 3000
00810IF A0$="U" THEN GOSUB 5000
00820IF A0$="D" THEN GOSUB 4000
00830IF A0$="V" THEN GOSUB 8000
00840IF A0$="C" THEN GOSUB 8500
00850IF A0$="H" THEN GOSUB 9700
00860IF A0$="L" THEN GOTO 400
00870GOTO 750
01000REM INSERT
01010PRINT "-- INSERT --"
01020INPUT "Name : ",A1$
01030IF A1$="" THEN PRINT "Cancelled":RETURN
01040INPUT "Disk : ",A2$
01050IF A2$="" THEN PRINT "Cancelled":RETURN
01060B=B+1
01070IF B>Q THEN PRINT "DB full":B=B-1:RETURN
01080R(B)=X:N0$(B)=A1$:D(B)=VAL(A2$):F(B)=1
01120GOSUB 7000
01130PRINT "Inserted id=";R(B)
01140RETURN
02000REM SELECT ALL
02010PRINT "-- SELECT ALL --"
02020IF B=0 THEN PRINT "No records":RETURN
02030C=0:Z=0:GOSUB 9800
02060FOR E=1 TO B
02070IF F(E)=0 THEN GOTO 2100
02080PRINT N0$(E);" | ";D(E)
02090C=C+1:Z=Z+1:IF Z<12 THEN 2100
02091PRINT "M=more X=exit";
02092Z7$=KEY:IF Z7$="" THEN 2092
02093M=(ASC(Z7$)AND95):Z7$=CHR(M):IF Z7$="X" THEN NEXT* E 2110
02094Z=0:CLS
02100NEXT E
02110PRINT "--- ";C;" active ---"
02120RETURN
03000REM SELECT WHERE
03010PRINT "-- SELECT WHERE --"
03020PRINT "N=name  D=disk  I=id"
03030PRINT "Field (N/D/I) ";
03031A4$="NDI":GOSUB 9850:A6$=Z7$:PRINT A6$
03050INPUT "Value : ",A5$
03055A4$="":FOR J=1 TO LEN(A5$):M=(ASC(A5$(;J,J))):IF M>=97 AND M<=122:A4$=A4$+CHR(M-32) ELSE LET A4$=A4$+CHR(M)
03056NEXT J:A5$=A4$
03060IF A5$="" THEN PRINT "Cancelled":RETURN
03065C=0:Z=0:GOSUB 9800
03100FOR E=1 TO B
03110IF F(E)=0 THEN GOTO 3160
03120IF A6$<>"N" THEN GOTO 3130
03121GOSUB 3200
03122IF SEARCH(U0$,A5$)<1 THEN GOTO 3160
03130IF A6$<>"D" THEN GOTO 3135
03131IF D(E)<>VAL(A5$) THEN GOTO 3160
03135IF A6$<>"I" THEN GOTO 3150
03136IF R(E)<>VAL(A5$) THEN GOTO 3160
03150PRINT N0$(E);" | ";D(E):C=C+1:Z=Z+1:IF Z<12 THEN 3160
03155PRINT "M=more X=exit";
03156Z7$=KEY:IF Z7$="" THEN 3156
03157M=(ASC(Z7$)AND95):Z7$=CHR(M):IF Z7$="X" THEN NEXT* E 3170
03158Z=0:CLS
03160NEXT E
03170PRINT "--- ";C;" record(s) ---"
03180RETURN
03200A3$=N0$(E):U0$=""
03210FOR K=1 TO LEN(A3$)
03220M=(ASC(A3$(;K,K))AND95):IF M>0:U0$=U0$+CHR(M) ELSE LET U0$=U0$+" "
03230NEXT K:RETURN
04000REM DELETE
04010PRINT "-- DELETE id=? --"
04020INPUT "ID : ",A5$
04030IF A5$="" THEN PRINT "Cancelled":RETURN
04040K=0
04050FOR E=1 TO B
04060IF R(E)<>VAL(A5$) THEN GOTO 4080
04070IF F(E)=1:F(E)=0:K=K+1
04080NEXT E
04090IF K=0 THEN PRINT "Not found":RETURN
04100GOSUB 7000
04110PRINT "--- ";K;" deleted (V to vacuum) ---"
04120RETURN
05000REM UPDATE
05010PRINT "-- UPDATE id=? --"
05020INPUT "ID : ",A5$
05030IF A5$="" THEN PRINT "Cancelled":RETURN
05040K=0
05050FOR E=1 TO B
05060IF R(E)<>VAL(A5$) OR F(E)<>1 THEN GOTO 5130
05070PRINT "Current: ";N0$(E);" | ";D(E)
05080INPUT "Name : ",A1$
05090IF A1$="" THEN PRINT "Cancelled":RETURN
05100INPUT "Disk : ",A2$
05110IF A2$="" THEN PRINT "Cancelled":RETURN
05120N0$(E)=A1$:D(E)=VAL(A2$):K=K+1
05130NEXT E
05140IF K=0 THEN PRINT "Not found":RETURN
05150GOSUB 7000
05160PRINT "--- ";K;" updated ---"
05170RETURN
06000REM LOAD
06020B=0:X=1:Z=0
06030ON ERROR GOTO 6110
06040OPEN "I",6,A7$
06060IN#6 ON:OUT#0:OUT#0 OFF
06070IF EOF(6) THEN GOTO 6110
06080INPUT R(B+1),N0$(B+1),D(B+1),F(B+1)
06085Z=Z+1:IF Z=10:Z=0:OUT#0:PRINT B;:OUT#0 OFF
06090B=B+1
06100IF R(B)>=X:X=R(B)+1
06105IF B<Q THEN GOTO 6070
06108OUT#0:PRINT "Warning: record limit reached":OUT#0 OFF
06110IN#0:OUT#0:OUT#0 ON:CLS:ON ERROR GOTO 0:CLOSE 6:RETURN
07000REM WRITE
07010A8$=A7$(;1,6)+"TMP"
07020OPEN "O",6,A8$:OUT#6
07060FOR E=1 TO B
07070PRINT R(E);",";N0$(E);",";D(E);",";F(E)
07080NEXT E
07090OUT#0:CLOSE 6
07110ON ERROR GOTO 7130
07120KILL A7$
07130ON ERROR GOTO 0
07140NAME A8$ AS A7$
07170RETURN
08000REM VACUUM
08010PRINT "-- VACUUM --"
08020K=0:C=0:FOR E=1 TO B
08040IF F(E)=0:K=K+1:GOTO 8070
08050C=C+1
08060R(C)=R(E):N0$(C)=N0$(E):D(C)=D(E):F(C)=F(E)
08070NEXT E
08080B=C
08090GOSUB 7000
08100PRINT K;" rows removed, ";B;" remain"
08110RETURN
08500REM COUNT
08510PRINT "-- COUNT --"
08520C=0:K=0:FOR E=1 TO B
08540IF F(E)=1:C=C+1 ELSE LET K=K+1
08550NEXT E
08560PRINT "File   : ";A7$
08570PRINT "Total  : ";B
08580PRINT "Active : ";C
08590PRINT "Deleted: ";K
08600PRINT "Next ID: ";X
08610RETURN
09000PRINT "Goodbye!":END
09700REM HELP
09710CLS:PRINT:PRINT ,FRE($),FRE(0)
09720PRINT "I=Insert S=Select W=Where U=Update D=Delete"
09725PRINT "V=Vacuum C=Count L=Change Q=Quit"
09730RETURN
09800PRINT "NAME     | DISK"
09810PRINT "---------+-----"
09820RETURN
09850REM KEY VALIDATE - set A4$ to allowed chars before calling
09855Z7$=KEY:IF Z7$="" THEN 9855
09856M=(ASC(Z7$)):IF M>=97 AND M<=122:Z7$=CHR(M-32)
09857IF SEARCH(A4$,Z7$)<1 THEN 9855
09858RETURN
09999SAVE"SQLBEE45"