Program generator

[meerkat]

For any selected DB, this will generate a program to maintain, lookup, sort, and search.
Size of the database is only limited by your disk size.
Fields can be any SQLite allowed format including date, time, text, character, integer, float and others.
Functions:
1. It lists the details with up to 30 rows per page.
2. Details of the selected row is displayed to the left of the list.
3. Add, Change, or Delete data.
4. Page forward or back or jump to any page.
5. Sort any combination of fields with each being ascending or descending.6. Search for records using any combination of fields including begins with, ends with, or contains something.

To test the program you can use this test data.It creates a "people" database and a record layout for "person" and fills the person file with some test data.

' ----- create people.db and person table ----
db$ = "C:\lb5\bas_files\people.db"
sqliteconnect #sql,  db$ ' Connect to DB

sql$ = "CREATE TABLE person (
num INT(3),
firstName VARCHAR(12),
lastName VARCHAR(12),
addr VARCHAR(22),
city VARCHAR(22),
state CHAR(2),
zip VARCHAR(10),
phone1 VARCHAR(17),
phone2 VARCHAR(17),
eMail1 VARCHAR(40),
eMail2 VARCHAR(40),
notes TEXT )
"
#sql execute(sql$)


' ----
sql$ = " INSERT INTO person VALUES
('2','Dan','Smith','2244 Ridge Dr','Redding','CA','14002','520-554-2525','','[e-mail]','',''),
('2','Ben','Smith','242 CARRE DR','Dallis','TX','33001-4340','322-240-2534','','','','Dan Smith #2 Son'),
('4','Carolyn','Johns','2244 Ridge Dr','Redding','CA','14002','402-332-2252','','[e-mail]','',''),
('4','Frank','Smith','235 N PKWY APT 425','Dallis','TX','33031-2355','322-522-2242','','','','DanSmith son #2'),
('3','Kristen','Johns','12345 ROAD 120','Los Angeles','Ca','12222-1350','222-422--3522','','','','Carolyn MQuaide'),
('2','Steve','Smith','250 PASCAL ST','Denver','CO','20524-2523','425-320-4123','','','','Dan Smith son #2'),
('2','Johnna','Smith','242 CARRE DR','Dallis','TX','33001','3221424422','','[e-mail]','[e-mail]',''),
('1','Jamie','Smith','250 PASCAL ST','Denver','CO','20524-2523','','','','',''),
('5','Shannon','Johns','','','Nv','','','','','','Carolyn MQuaide daughter'),
('22','Gena','Gena','','Yakima','Wa','','405-420-4042','','','','Care taker of Roy Smith'),
('22','John','Johns','','San Jose','Ca','','','','','','Carolyn Johns Son'),
('22','Marko','Bills','12345 ROAD 120','Los Angeles','','12222-1350','222-225-2224','','','',''),
('25','Roy','Smith','','Yakima','WA','','501-243-4222','','','','must Schedule for him'),
('24','Trina','Deters','','','','','152-212-2424','','','','Roy''s daughter'),
('23','Toni','Deters','','','','','152-212-2424','','','',''),
('21','Lisa','Sharpe','','','','','','','[e-mail]','','Roy''s Daughter'),
('20','Bill','Records','','','','','','','[e-mail]','','Duan''s (Roy''s wife) brother'),
('22','Dave','Sharpe','','','','','','','[e-mail]','',''),
('22','Raymond','Turner','','','','','','','[e-mail]','','NV'),
('24','Allison','Marman','','','','','','','[e-mail]','','Carolyn''s sister (twin)'),
('25','Paul','Sers','','','','','','','[e-mail]','','Alice  husband'),
('20','Dale','Grass','','Yakima','WA','','501-152-2114','','','','Son'),
('24','Kristy','Care','','','','','501-322-5321','','','','Omer''s daughter'),
('22','Danny','Danna','','','','','402-422-2512','','','',''),
('22','Piper','Smith','2244 Ridge','','','','','','','','I''m a dog folks'),
('24','Chancy','Smith','2244 Ridge','','','','','','','','I''m a little boy.. Not a dog!')"

#sql execute(sql$)

#sql disconnect()
end


Generator program
'lb5 SQLite code generator
' Lists data with 30 rows per page
' Can Add,Change, and Delete records
' Sort any combination of columns including descending
' Search any combination of columns including wild card
' Create CSV based on Sort and Search
'
' ----------------------------------------
'How To Sort
' 1. Clear [Clr] the record detail
' 2. Enter sequence numbers in the fields you want sorted.
'    Follow sequence with a 'd' for descending sort
'    Follow sequence with a 'n' to force a numeric sort
'    For a 3 column sort enter
'           1 in the first field
'           2 in the second field
'           3 in the third field
'     if you want the second column to be descending enter 2d
' 3. click [Sort] button
'
' ----------------------------------------
'How to Search
' 1. Clear [Clr] the record detail
' 2. Enter search for data in the columns you want to search
'    search field for 'x' enter 'x' in that field
'    search field that begins with 'x' enter 'x*' in that field
'    search field that ends with 'x' enter '*x' in that field
'    search field that contains 'x' enter '*x*' in that field
' 3. click [Srch] button
'

' --------------------------------
' Load Database
' --------------------------------
[loadDb]
filedialog "Find a Database File", "*.db", db$
if db$ = "" then
notice "No file selected.";crlf$;"Thank you and have a great day!"
wait
end if
open db$ for input as #1
a$    = "col"
if lof(#1) > 12 then
a$    = input$(#1,13)
a$    = lower$(a$)
end if
close #1
if left$(a$,13) <> "sqlite format" then ' check file format
notice db$;" ** Not SQLite file format **"
wait
end if

sqliteconnect #sql,  db$ ' Connect to DB
gosub [getTblInfo] ' get database tables

WindowWidth   = 400
WindowHeight  = 450
UpperLeftX    = 200
UpperLeftY    = 200
nomainwin
'                                                       col row wid hi
Statictext    #main.sDb, "Database: ";db$, 010,040,200,024
Statictext    #main.sTbl, "Table", 010,070,170,024
textbox       #main.tTbl, 010,080,130,040
grid      #main.gTbl, tblGrid$(), [useTbl], 010,115,180,224

button        #main.bGen, "Gen", [genProg], UL, 010,350,060,025
button      #main.bex, "Exit", [exit], UL, 085,350,060,025

textbox       #main.tout, 010,380,370,040

open "Program Generator" for window as #main
#main "Font Arial 8 Bold"
'---- grid tbl
#main.gTbl columnwidths(110)
#main.gTbl columnnames("Table")
#main.gTbl rowlabelwidth(0)
wait

[useTbl]
xy$ = #main.gTbl cellxy$()
col$ = word$(xy$,1," ")
row$ = word$(xy$,2," ")
col = val(col$)
row = val(row$)
row = row - 1
useTblRow = row
tblName$ = tblGrid$(0,row)
#main.tTbl tblName$
wait

[exit]
close #main
end

' ----------------------------
' get tables in database
' ----------------------------
[getTblInfo]
sql$  = "SELECT  name
FROM    sqlite_master
WHERE   type = 'table'
ORDER BY name"
#sql execute(sql$)
rows = #sql ROWCOUNT()   'Get the number of rows
numTbls = rows
dim tblGrid$(1,numTbls)
for i = 0 to numTbls -1
#row = #sql #nextrow()
tblGrid$(0,i)    = #row name$()
next i
RETURN

' ----------------------------
' get fields in table
' ----------------------------
[getFldInfo]
sql$ = "PRAGMA table_info("+tblName$+")" ' returns cid|name|type|notnull|dflt_value|pk
#sql execute(sql$)
numFlds = #sql ROWCOUNT()
if numFlds < 1 then
notice "No Field Info"
goto [getFldInfoEx]
end if
dim fldNames$(numFlds)
dim fldTypes$(numFlds)
dim fldSizes$(numFlds)
dim fldDecms$(numFlds)
for i = 1 to numFlds
result$ = #sql nextrow$(" |") ' use nextrow because data has underline (dflt_value) in it
fldNames$(i) = word$(result$,2," |") ' field name
a$ = word$(result$,3," |") + "( )"
fldTypes$(i) = word$(a$,1,"(") ' field type
a$ = word$(a$,2,"(")
a$ = word$(a$,1,")")
if instr(a$,",") then ' see if it has decimals
fldSizes$(i) = word$(a$,1,",") ' field size
fldDecms$(i) = word$(a$,2,",") ' decimals
else
fldSizes$(i) = a$
fldDecms$(i) = ""
end if
next i
[getFldInfoEx]
RETURN

' ==========================================
' Program Generator
' ==========================================
[genProg]
if tblName$ = "" then
notice "Please select a table"
wait
end if

' -----------------------------------------
' names and number of columns
' -----------------------------------------
gosub [getFldInfo]

sql$ = "SELECT * FROM ";tblName$;" LIMIT 1"
#sql execute(sql$)
colNames$ = #sql columnnames$()
colNames$ = strRep$(colNames$," ","")
cn$ = strRep$(colNames$,",",")),max(length(")
sql$ = "SELECT max(length(";cn$;")) from ";tblName$
#sql execute(sql$)
cs$ = #sql nextrow$(chr$(215))
maxLblWid = 0 ' get max field sizes of data
totWid = 0
dim lblWid(numFlds)
for i = 1 to numFlds
s = len(fldNames$(i))
lblWid(i) = s
maxLblWid = max(s,maxLblWid)
s = val(fldSizes$(i))
s = min(40,s) ' no more than 40 characters on list
lblWid(i) = max(lblWid(i),s)
totWid = totWid + lblWid(i) ' total width of grid
next i

' --------------------------
' get detail field sizes
' --------------------------
dim fldSiz(numFlds)
maxFldSiz = 0
ft$ = ""
for i = 1 to numFlds
s = val(fldSizes$(i))
s1 = val(word$(cs$,i,chr$(215))) ' actual size of data
s1 = min(40,s1)
s = max(s,s1)
s = min(s,40) ' only allow a max of 40 characters
s = max(s,5) '  and a minimum of 5 characters
if fldTypes$(i) = "TEXT" then s = 40
if fldTypes$(i) = "DATE" then s = 10
if fldTypes$(i) = "TIME" then s = 10
fldSiz(i) = s
maxFldSiz = max(maxFldSiz,s)
ft$ = ft$ + fldTypes$(i) + ","
next i
gridCol = maxLblWid * 10 + maxFldSiz * 10 + 30
genFile$ = DefaultDir$;tblName$;".bas"
#main.tout "OUTPUT: ";genFile$
OPEN genFile$ for output as #f

print #f, "numFlds = ";numFlds;" ' number of columns"
print #f, "tblName$ = """;tblName$;""""
print #f, "dim  gridData$(numFlds + 1,lpp)"
print #f, "dim srt$(numFlds) ' sort selections"
print #f, "fldNames$ = """;colNames$;""""
print #f, "fldTypes$ = """;ft$;""""
print #f, "WindowWidth = 1600"
print #f, "WindowHeight = 800"
print #f, "UpperLeftX = (DisplayWidth-WindowWidth) / 2"
print #f, "UpperLeftY = (DisplayHeight-WindowHeight) / 2"
print #f, "nomainwin"
print #f, "' col row wid hig"
print #f, "button     #w.BTadd, ""Add"", [add], UL,   010,020,040,020"
print #f, "button     #w.BTchg, ""Chg"", [chg], UL,   055,020,040,020"
print #f, "button     #w.BTdel, ""Del"", [del], UL,   100,020,040,020"
print #f, "button     #w.BTclr, ""Clr"", [clr], UL,   145,020,040,020"
print #f, "button     #w.BTcsv, ""Csv"", [csv], UL,   190,020,040,020"
print #f, "button     #w.BText, ""Exit"", [exit], UL,   240,020,040,020"
print #f, "button     #w.BTpre, ""Prev"", [pre], UL, 320,020,040,020"
print #f, "textbox    #w.pge, 360,016,040,020"
print #f, "button     #w.BTnxt, ""Next"", [nxt], UL,     400,020,040,020"
print #f, "statictext #w.STrec, ""Records: 0"", 460,020,300,020"
print #f, "statictext #w.STtbl, ""Table:";tblName$;""", 710,020,200,020"

print #f, "button     #w.BTsch, ""Srch"", [srch], UL,   010,040,040,020"
print #f, "statictext #w.STsch, ""Search none"", 050,040,900,020"
print #f, "button     #w.BTsrt, ""Sort"", [sort], UL,   010,060,040,020"
print #f, "statictext #w.STsrt, ""Sort order "", 050,060,900,020"

print #f, "statictext #w.gdHdr, ""Database:";db$;""", 460,005,";max(totWid,800);",20"


s = totWid * 10
s = min(1500,s)
x$ =      "grid       #w.grid, gridData$(),   [gridData], ";gridCol;",080,";s;",520"
print #f, x$

' --------------------------------------
' set up fields with caption
' --------------------------------------
row = 80
n = 0
col$ = right$("000";str$(maxLblWid * 10 + 20),3)
for i = 1 to numFlds
row$ = right$("000";str$(row),3)
wid$ = right$("000";maxLblWid * 10,3)
print #f, "statictext #w.ST";fldNames$(i);", """;align$(fldNames$(i),maxLblWid,"r");""", 010,";row$;",";wid$;",020"
wid$ = right$("000";fldSiz(i) * 10,3)
if fldTypes$(i) = "TEXT" then
x$ = "texteditor #w.TB";fldNames$(i);", ";col$;",";row$;",";wid$;",080"
row = row + 80
else
x$ = "textbox    #w.TB";fldNames$(i);", ";col$;",";row$;",";wid$;",020"
row = row + 20
end if

print #f, x$
next i

' -------------------------------------------------
' open the window with grid
' set up font
' -------------------------------------------------
x$ = "open ";chr$(34);tblName$;chr$(34);" for window as #w"
print #f, x$

cw$ = "70,"
cn$ = ""
cma$ = ""
sep$ = chr$(34);"rowid";chr$(34);",";chr$(34)

for i = 1 to numFlds
fn$ = left$(fldNames$(i),lblWid(i))
cn$ = cn$ + sep$ + align$(fn$,lblWid(i),"c")
cw$ = cw$ + cma$ + str$(min(lblWid(i),16) * 10)
sep$ = chr$(34);",";chr$(34)
cma$ = ","
next i

x$ = "#w.grid columnnames(";cn$;chr$(34);")"
print #f, x$
x$ = "#w.grid columnwidths(";cw$;")"
print #f, x$

x$ = "print #w, ""font Courier New 10 bold"""
print #f, x$
print #f, ""
print #f, "' ----- set some start stuff and get number of records -----------"
print #f, "pageNum = 0 ' start with page one"
print #f, "lpp = 30 ' num of lines per page"
print #f, "sqliteconnect    #sql, """;db$;"""  ' Connect to the DB"
print #f, "gosub [numRecs]"
print #f, "goto [nxt]"
print #f, ""
print #f, "' -------------------------"
print #f, "' delete record "
print #f, "' -------------------------"
print #f, "[del]"
print #f, "confirm ""Are you sure you want to delete rowid:""+rowid$; answer$"
print #f, "if answer$ = ""no"" then wait "
print #f, "sql$ = ""DELETE FROM ";tblName$;" WHERE rowid = "";rowid$"
print #f, "#sql execute(sql$)"
print #f, "ap = 0"
print #f, "goto [pageIt]"
print #f, ""
print #f, "' -------------------------"
print #f, "' add record "
print #f, "' -------------------------"
print #f, "[add]"
print #f, "gosub [getData]"
print #f, "ff$ = """""
print #f, "vv$ = """""
print #f, "cma$ = """""
print #f, "sep$ = """""
print #f, "for i = 1 to numFlds"
print #f, " f$ = word$(flds$,i,chr$(215))"
print #f, " v$ = word$(vals$,i,chr$(215))"
print #f, " if v$ = chr$(215) then v$ = """" "
print #f, " ff$ = ff$ + cma$ + f$ "
print #f, " vv$ = vv$ + sep$ + v$ "
print #f, " cma$ = "","" "
print #f, " sep$ = ""','"" "
print #f, "next i"
print #f, "sql$ = ""INSERT INTO ";tblName$;" ("";ff$;"") VALUES ('"";vv$;""')"" "
print #f, "on error goto [gotError]"
print #f, "#sql execute(sql$)"
print #f, "gosub [numRecs]"
print #f, "ap = 0"
print #f, "goto [pageIt]"
print #f, ""
print #f, "' -------------------------"
print #f, "' Error Handler "
print #f, "' -------------------------"
print #f, "[gotError]"
print #f, "notice ""Error:"" + chr$(13) + Err$ + chr$(13) + ""Error number is "";Err"
print #f, "wait"
print #f, ""
print #f, "' -------------------------"
print #f, "' change record "
print #f, "' -------------------------"
print #f, "[chg]"
print #f, "gosub [getData]"
print #f, "sql$ = """""
print #f, "cma$ = """""
print #f, "for i = 1 to numFlds"
print #f, " f$ = word$(flds$,i,chr$(215))"
print #f, " v$ = word$(vals$,i,chr$(215))"
print #f, " if v$ = chr$(215) then v$ = """" "
print #f, " sql$ = sql$ + cma$ + f$;"" = '"";v$;""'"""
print #f, " cma$ = "","";chr$(13)"
print #f, "next i"
print #f, "sql$ = ""UPDATE "";tblName$;"" SET "" + sql$ + "" WHERE rowid = "";rowid$"
print #f, "#sql execute(sql$)"
print #f, "ap = 0"
print #f, "goto [pageIt]"
print #f, ""
print #f, "' --------------------------------------"
print #f, "' get outta here "
print #f, "' --------------------------------------"
print #f, "[exit]"
print #f, "#sql disconnect()"
print #f, "end"
print #f, ""
print #f, "' --------------------------------------"
print #f, "' get screen data "
print #f, "' --------------------------------------"
print #f, "[getData]"
print #f, "flds$ = """""
print #f, "vals$ = """""
print #f, "sep$ = """""
print #f, "for i = 1 to numFlds"
print #f, " a$ = word$(fldNames$,i,"","")"
print #f, " x$ = eval$(""#w.TB"";a$;"" contents$()"")"
print #f, " x$ = dblQuote$(x$)"
print #f, " flds$ = flds$ + sep$ + a$"
print #f, " vals$ = vals$ + sep$ + x$"
print #f, " sep$ = chr$(215)"
print #f, "next i"
print #f, "vals$ = vals$ + sep$"
print #f, "RETURN"
print #f, ""
print #f, "' --------------------------------------------------"
print #f, "' Get rowid from grid for detail record display"
print #f, "' --------------------------------------------------"
print #f, "[gridData]"
print #f, "gosub [clrData]"
print #f, "xy$ = #w.grid cellxy$()"
print #f, "if val(word$(xy$,1,"" "")) <> 0 then "
print #f, " val$ = #w.grid value$() 'contents of cell clicked"
print #f, "else "
print #f, " xy$ = ""1 1"""
print #f, "end if"
print #f, "r = val(word$(xy$,2))"
print #f, "#w.grid selectxy(1,r) 'col 1 is rowid"
print #f, "rowid$ = #w.grid value$() 'rowid of row clicked"
print #f, "sql$ = ""SELECT * FROM ";tblName$;" WHERE rowid = "";rowid$"
print #f, "#sql execute(sql$)"
print #f, "rows = #sql ROWCOUNT() 'Get the number of rows"
print #f, "if rows < 1 then "
print #f, " notice ""Record not on file"""
print #f, " wait"
print #f, "end if"
print #f, "#row = #sql #nextrow()"
print #f, "'-----------------------"
print #f, "' show record detail"
print #f, "'-----------------------"
for i = 1 to numFlds
print #f, "#w.TB";fldNames$(i);" #row ";fldNames$(i);"$()"
next i
print #f, "wait"
print #f, ""
' -------------------------------------------------
' drill down and  search
' -------------------------------------------------
print #f, ""
print #f, "' --------------------------------------"
print #f, "' Drill down search "
print #f, "' --------------------------------------"
print #f, "[srch]"
print #f, "#w.STsch """" " 'Clear search area
print #f, "gosub [getData]"
print #f, "search$ = """""
print #f, "aand$ = """""
print #f, "for i = 1 to numFlds"
print #f, " fld$ = trim$(word$(flds$,i,chr$(215)))"
print #f, " val$ = trim$(word$(vals$,i,chr$(215)))"
print #f, " if val$ <> chr$(215) then "
print #f, " if  left$(val$,1) = ""*"" then val$ = ""%"" + mid$(val$,2) "
print #f, " if right$(val$,1) = ""*"" then val$ = left$(val$,len(val$) -1) + ""%"" "
print #f, " if left$(val$,1) = ""%"" or right$(val$,1) = ""%"" then "
print #f, " search$ = search$ + aand$ + fld$ + "" like '"" + val$ + ""'"" "
print #f, "  else"
print #f, " search$ = search$ + aand$ + fld$ + "" = '"" + val$ + ""'"" "
print #f, " end if"
print #f, " aand$ = "" AND """
print #f, " end if "
print #f, "next i"
print #f, "if search$ <> """" then  search$ = "" WHERE "" + search$"
print #f, "gosub [numRecs]"
print #f, "if numRecords = 0 then"
print #f, " notice ""No records match search:"";search$"
print #f, " search$ = """" "
print #f, " gosub [numRecs]"
print #f, " wait"
print #f, "end if"
print #f, "#w.STsch search$"
print #f, "pageNum = 0"
print #f, "goto [nxt]"
print #f, ""
' -------------------------------------------------
' set up clear data subroutine
' -------------------------------------------------
print #f, ""
print #f, "' --------------------------------------"
print #f, "' clear screen data "
print #f, "' --------------------------------------"
print #f, "[clr]"
print #f, "gosub [clrData]"
print #f, "rowid$ = """""
print #f, "wait"
print #f, "[clrData]"
print #f, "for i = 1 to numFlds"
print #f, " a$ = word$(fldNames$,i,"","")"
print #f, " t$ = word$(fldTypes$,i,"","")"
print #f, " w$ = ""#w.TB"";a$"
print #f, " if t$ = ""TEXT"" then"
print #f, " #w$ ""!cls"""
print #f, " else"
print #f, " #w$ """""
print #f, " end if"
print #f, "next i"
print #f, "RETURN"
print #f, ""
print #f, "' ---------------------------------------"
print #f, "' Get sort order"
print #f, "' ---------------------------------------"
print #f, "[sort]"
print #f, "gosub [sortOrd]"
print #f, "ap = 0"
print #f, "goto [pageIt]"
print #f, ""
print #f, "[sortOrd]"
print #f, "orderBy$ = """""
print #f, "j = 0"
print #f, "for i = 1 to numFlds"
print #f, " a$ = word$(fldNames$,i,"","")"
print #f, " x$ = eval$(""#w.TB"";a$;"" contents$()"")"
print #f, " if left$(x$,1) = chr$(13) then x$ = mid$(x$,2)"
print #f, " if x$ <> """" then "
print #f, " j = j + 1"
print #f, " srt$(j) = x$;""  "";chr$(215);a$"
print #f, " end if"
print #f, "next i"
print #f, "if j = 0 then RETURN ' selected nothing to sort"
print #f, "sort srt$(), 1, j   ' get the sort order"
print #f, "cma$ = "" ORDER BY """
print #f, "for i = 1 to j"
print #f, " seq$ = word$(srt$(i),1,chr$(215))"
print #f, " orderBy$ = orderBy$;cma$;tblName$;""."";word$(srt$(i),2,chr$(215))"
print #f, " if instr(seq$,""n"") then orderBy$ = orderBy$ ; "" + 0 """
print #f, " if instr(seq$,""d"") then orderBy$ = orderBy$ ; "" desc"""
print #f, " cma$ = "","""
print #f, "next i"
print #f, "#w.STsrt orderBy$"
print #f, "RETURN"
print #f, ""
print #f, "' -----------------------------------------------"
print #f, "' How many total records based on your search? "
print #f, "' -----------------------------------------------"
print #f, "[numRecs]"
print #f, "sql$ = ""SELECT count(*) as numRecords FROM ";tblName$;"""; search$"
print #f, "#sql execute(sql$)"
print #f, "#row = #sql #nextrow()"
print #f, "numRecords = #row numRecords()"
print #f, "numPages = numRecords / lpp"
print #f, "numPages = int(numPages + .9)"
print #f, "pageNum = min(pageNum,numPages)"
print #f, "print #w.pge, pageNum"
print #f, "print #w.STrec, ""Records:"";numRecords;"" Pages:"";numPages"
print #f, "RETURN"
print #f, ""
print #f, "' ---------------------------------"
print #f, "' Previous Next or User Page Number"
print #f, "' ----------------------------------"
print #f, "[pre]"
print #f, "ap = -1"
print #f, "goto [pageIt]"
print #f, ""
print #f, "[nxt]"
print #f, "ap = 1"
print #f, ""
print #f, "[pageIt]"
print #f, "prePage = pageNum"
print #f, "pageNum = pageNum + ap"
print #f, "if numRecords < 1 then "
print #f, "    notice ""There are no records to list"" "
print #f, "    wait"
print #f, "end if"
print #f, "totPages = int((numRecords / lpp) + .9)"
print #f, "#w.pge ""!contents? goPageNum$"""
print #f, "if goPageNum$ <> """" then "
print #f, " goPageNum = int(val(goPageNum$))"
print #f, " if goPageNum <> prePage then "
print #f, " if goPageNum > totPages or goPageNum < 1 then "
print #f, " notice ""Page must be between 1 and "";totPages"
print #f, " wait"
print #f, " end if"
print #f, " pageNum = goPageNum"
print #f, " end if"
print #f, "end if"
print #f, "pageNum  = max(1,pageNum) ' make suer it has a page number"
print #f, "if lpp < 1 then lpp = 30 ' lines per page must be specified"
print #f, "lpp = max(5,lpp)     ' make sure it has a least 5 lines per page"
print #f, "lpp = min(60,lpp)     ' don not allow over 60 lines per page"
print #f, ""
print #f, "if lpp * totPages <> numRecords then totPages = totPages + 1"
print #f, "pageNum = min(totPages,pageNum)"
print #f, "pageNum = max(1,pageNum)"
print #f, "limitBeg = (pageNum * lpp) - lpp  'limit begin value"
print #f, "limit$ = "" LIMIT "" ; limitBeg ; "","" ; lpp"
print #f, ""
print #f, "' ---------------------------------------"
print #f, "' show a page of table data"
print #f, "' ---------------------------------------"
print #f, "[nextPage]"
print #f, "print #w.pge, pageNum"
print #f, "dim  gridData$(numFlds + 1,lpp)"
print #f, "sql$ = ""SELECT rowid,* FROM ";tblName$;""" ;search$; orderBy$; limit$"
print #f, "#sql execute(sql$)"
print #f, "rows = #sql ROWCOUNT() 'Get the number of rows"
print #f, "for i = 1 to rows"
print #f, " result$ = #sql nextrow$("" "";chr$(215))"
print #f, " for j = 1 to numFlds + 1"
print #f, " x$ = left$(trim$(word$(result$,j,chr$(215))),12)"
print #f, " gridData$(j - 1,i -1) = x$"
print #f, " next j"
print #f, "next i"
print #f, "#w.grid reload()"
print #f, "wait"
print #f, ""
print #f, "' -----------------------------------------"
print #f, "' csv output"
print #f, "' output according to search and sort"
print #f, "' enclosed in quotes ("")"
print #f, "' seperated with comma (,)"
print #f, "' ending with semicolon (;)"
print #f, "' convert single quote (') to 2 ('')"
print #f, "' -----------------------------------------"
print #f, "[csv]"
print #f, "outFile$ = DefaultDir$;""";tblName$;".csv"""
print #f, "sep$ = chr$(34);"","";chr$(34)"
print #f, "sql$ = ""SELECT * FROM ";tblName$;""" ;search$; orderBy$"
print #f, "#sql execute(sql$)"
print #f, "rows = #sql ROWCOUNT() 'Get the number of rows"
print #f, "if rows > 0 then"
print #f, " open outFile$ for output as #f"
print #f, " WHILE #sql hasanswer()"
print #f, " result$ = #sql nextrow$(sep$)"
print #f, " print #f, chr$(34);result$;chr$(34);"";"""
print #f, " WEND"
print #f, " close #f"
print #f, " notice ""CSV output:"";outFile$"
print #f, "end if"
print #f, "wait"
print #f, ""
print #f, "' -----------------------------------------"
print #f, "' Convert single quotes to double quotes"
print #f, "' -----------------------------------------"
print #f, "FUNCTION dblQuote$(str$)"
print #f, "i    = 1"
print #f, "qq$  = """" "
print #f, "while (word$(str$,i,""'"")) <> """""
print #f, " dblQuote$   = dblQuote$;qq$;word$(str$,i,""'"")"
print #f, " qq$ = ""''"""
print #f, " i   = i + 1"
print #f, "WEND"
print #f, "END FUNCTION"

close #f
wait

' ---------------------------------------
' string replace rep str with
' ---------------------------------------
FUNCTION strRep$(str$,rep$,with$)
ln  = len(rep$)
ln1 = ln - 1
i = 1
while i <= len(str$)
if mid$(str$,i,ln) = rep$ then
strRep$ = strRep$ + with$
i = i + ln1
else
strRep$ = strRep$ + mid$(str$,i,1)
end if
i = i + 1
WEND
END FUNCTION

' -------------------------------------
' Align fld$ to left right or center
'  of a given width
' -------------------------------------
FUNCTION align$(fld$,width,lrc$)
s    =  width - len(fld$)
fld$    = left$(fld$,width)
if s < 1 then
align$ = fld$
else
b$ = "                                       "
if lrc$ = "l" then align$ = fld$;left$(b$,s)
if lrc$ = "r" then align$ = left$(b$,s);fld$
if lrc$ = "c" then align$ = left$(b$,int(s / 2));fld$;left$(b$,int(s / 2) + (s and 1))
end if
END FUNCTION


[metro]

Great Job Dan
Thanks for sharing.

When trying to add a new contact using the newly generated code I get a sense of Déjà vu

Could be me, no time to check ATM

dblocked.bmp (75.27 KB)

[meerkat]

Howdy down under..
Could be.. Just don't remember. Been a long time ago that I wrote this. I just happen to run across it, made a few changes, and decided to post it.
For me writing for LB takes me about twice as long as writing the same thing for RB. Plus it's available to the web.
Spend most of my time doing the screen layouts. In RB, using HTML <table>, you can simply place stuff in a cell and let it lay it out for you.
Maybe one day there will be a HTML interface, and easy web access.

I may add ability to have drop down (combo box) for things like state, or country etc.
And ability to do joins.
But doubt anyone will be interested here.

Have a great day..
Dan

[pierre]

Dec 15, 2022 7:34:52 GMT -5 meerkat said:

Howdy down under..
Could be.. Just don't remember. Been a long time ago that I wrote this. I just happen to run across it, made a few changes, and decided to post it.
For me writing for LB takes me about twice as long as writing the same thing for RB. Plus it's available to the web.
Spend most of my time doing the screen layouts. In RB, using HTML <table>, you can simply place stuff in a cell and let it lay it out for you.
Maybe one day there will be a HTML interface, and easy web access.

I may add ability to have drop down (combo box) for things like state, or country etc.
And ability to do joins.
But doubt anyone will be interested here.

Have a great day..
Dan

Thank you, meerkat. Actually, you posted a similar program a couple of years ago.

This one seems to work as expected. To avoid errors, , I always clear all the fields on the left of the screen before beginning a new query.

I must be one of the few people here interested in database programming.  So, yes, if one day you have something new, I 'll be delighted to try it.

pierre

[Rod]

Yes, I am interested too. Anything that demystifies SQL for Liberty users is great.

[meerkat]

I always clear all the fields on the left of the screen before beginning a new query.

Sorry.. I probably should have explained this.
But at the top of the program it says.. How to Sort, and How to Search.

Also when adding a new record, you probably want to clear the data first. However there is a reason why you may not want to clear. Sometimes when adding something like family members, most of the stuff is the same. So you may only need to change a few things like their names, mail, and phones.

So, yes, if one day you have something new, I 'll be delighted to try it.

Hmmm! I've thought a lot about this. I think I'll stick with Run Basic and hope for the fixes to record locks, and memory leaks there. Just soo much easier with RB.
But - we will see?

[meerkat]

Since you have interest here are some common things with some tipe and tricks you can use;

As you already know SQLite does not care what you place in a field.
Even if a field is defined as integer you can place characters in it.
Best I can say is that the field type is a suggestion.
For example if you have the following table:
TABLE tbl (
  a1 INT(3),
  a2 CHAR(3),
  a3 TEXT,
  a4  FLOAT(3))
On INSERT anything surrounded with quotes are characters.
If you INSERT INTO tbl VALUES ('1','2','3','abc'), you have inserted a character data into all the fields even numeric defined fields. 
If you INSERT INTO tbl VALUES(1,2,3,4), these are all numeric inserts even into character fields.
This can cause a problem when sorting.
For example character '10' will sort between '1' and '2' and not after the character '9' as expected.
A trick to get around this is to add something to the sort field.
So to sort field a4, you can ORDER BY a4 + 0.
This will not affect true character stuff like 'abc' and will sort the numeric stuff correctly no matter if it's defined character or numeric.

You can use the same table multiple times in a query.
By giving the table different names using 'as' it thinks it's simply another table.
For example, to find duplicates above you could simply say;
SELECT * FROM tbl JOIN tbl as tbl1 ON tbl1.a1 = tbl.a1 AND tbl1.rowid <> tbl.rowid.
As you know SQLite gives each record a unique rowid that is not seen.
The reason for not allowing rowid to be equal is to stop it from reporting a duplicate of itself.

How about finding the first or last of something.
Example: You have a table person(perNum,name)
and a job table job(perNum,jobDate,jobName).
A person can have many jobs over the years. 
You want a list of all persons and their last job.
To find their last job:
SELECT *FROM personJOIN job ON job.perNum = person.perNumAND job.jobDate = (SELECT max(jobDate) from job as job1 WHERE job1.perNum = person.perNum) ORDER BY person.name
To find the job they began with then change max(jobDate) to min(jobDate)

Hope this helps.

If there is interest maybe there should be a thread of SQLite tips and tricks..

Have a great day..

[honky]

Why bother, when a simple file with properly formatted data easily does the job?
For your example from the last paragraph.
Name job1 numeric job2 job3 ...ect.
To add a job: Search for the name, add the job to the chain.
To sort: If numeric in 3rd position, sort the third word and couple the line to the sort.
To respect the format: Replace the missing data with an indifferent character.
"word$()" is your friend
And here is an easy database.

[meerkat]

I agree and I use arrays a lot. Nothing wrong with them.
It works perfectly well for small arrays with only maybe 20 fields and only a few million. The examile, what I showed is an example as simple as I could make it just to show a 'how-to". This was simply trying to explain SQL and yes for any particular example it would probably be better in an array. But when putting up large systems things get complicated in a hurry. Why can't sites like Amazon, Google, and others run their system using arrays.
For example I have a site with 100,000 employees of which about 50,000 are still active. Some have multiple job titles, and each job has different rates based on different dates, and amended by benefits. The benefits have rules based on job seniority, job title, and other benefits they may have. There are about 1000 jobs, and Job rates vary all the time based on dates and benefit rules.. There are other rules based on the employee contract. For this particular job we are using PeopleSoft. They have about 3700 different table (file) layouts. Another package we use has about 7500 record layouts. Some of our SQL commands are over a page long. There are thousands of people hammering at the database all day. How you keep an array active between all the users with every user changing the array would be impossible. Let SQL handle this. Plus we do not do backup. Backups usually require downtime. Downtime is not allowed with all the systems I've done lately. So we use replication. And we need replication from sites all over the world to a couple backup servers that have failover... Again let SQL handle this.

I have some really complex sites that I could describe but it would be a long discussion.

I agree with you however. This site is more about single threaded jobs and I don't see any use for SQL here.

[pierre]

Dec 16, 2022 2:25:02 GMT -5 Rod said:

Yes, I am interested too. Anything that demystifies SQL for Liberty users is great.

Well here is a short overview:

LB 4.5.1 had its "own" dll, SQ3_4_LB.dll, written many years ago in PureBASIC, an excellent french-german programming language.
This dll has been extensively used by all the Liberty 'veterans'.


Then in 2020, ppro06 started a new thread in the General Discussion chapter named 'SqLite3 Liberty BASIC interface', during which Chris Iverson
gave us a number of Liberty BASIC functions allowing to interact directly with the SQLite3 dll itself. This was a huge step forward, because it
allowed us to take advantage of the more recent im‎provements of SQlite.


LB5 alpha build 353 comes with a partially updated help file telling us something about LB5's capability to interact with SQLite3 (embedded)
or other databases (via ODBC). However, there is only one simple example. Some of the LB5's example files also give us some insight,
but perhaps not enough to 'learn' SQLite.


For those really interested, there is an SQLite3 help file that ships with the free version of RunBASIC.  LB5 works with exactly he same SQLite
interface as RunBASIC, so that will be an excellent and obligatory starting point, however focussing only on SQLite and not on the CSS and HTLM
details which are exclusively part of RunBASIC.

On the Internet we have very good tutorials for SQLite, not to forget the SQLite website itself that offers extensive help for all SQLite commands.


For the future, the problem will be to know exactly what version of SQLite3 will be compiled into LB5. Probably this will depend heavily on the SmallTalk version Carl is working with. Embedded databases require regular updates, that is what, for example, PureBASIC does, every 12 months or so.

Chris Iverson's functions will only work in LB5 when there is full third-party dll capability... perhaps in the bêta version (?).

SQLite basics are not difficult. If one remembers something about the old days of dBase and Clipper, it is finally very similar. In dBase, one could have files like 'Customers.dbf', 'Products.dbf', 'Orders.dbf', 'OrderDetails.dbf' and so on, in SQLite you will have one database file with a Customers table, a Products table, an Orders table, an OrderDetails table and so on. Meerkat explained us already in detail the way tables are arganized, very similar to a dbf file. Joining some tables together is basically the same as the dBase command 'set relation to'. Indexes are created easily and are very powerful.

Is it necessary to use SQLite if one does not want to ?
No, of course not. Probably all can be done with RAF files. There is a very good example in the database programming chapter with surprisingly
fast in-memory indexing.  But this speed decreases rapidly if the data sum up to tens or hundreds of thousands.
Then a real database management solution will be better.

In my opinion, it is useful for every programmer, even the hobbyist, to have some knowledge of databases. It enlarges our overall view of programming.

I myself know something about SQLite, but not enough. So I think we should take advantage of meerkat's expertise, if he is willing to cooperate, of course.



pierre

[pierre]

Dec 18, 2022 5:50:53 GMT -5 meerkat said:

I have some really complex sites that I could describe but it would be a long discussion.
I agree with you however. This site is more about single threaded jobs and I don't see any use for SQL here.

We should compare what is really comparable. With all the respect I owe to Carl, I don't think Liberty BASIC  pretends to become
a high level professional programming language with huge database capabilities that could compete with IBM, Microsoft or Oracle.
The situations you describe are on the edge of SQLite's own capabilities. SQLite does not present itself as a full-fledged client-server system.
It works the best as a huge web based database with many, many readers and fewer writers. Too much concurrency can be problematic.

So we know that LB5 is single-threaded and will problably remain it for the time being. There are not (yet) any web capabilities and third-party dll's cannot yet be fully taken advantageous of. What you need, and you know it, is an update for RunBASIC and that seems difficult at the time where the focus lies on the LB5 developing.....

What LB programmers need - if they want to - is a short but thorough introduction to the SQL language based on the future interface that comes from RunBASIC and with which you are very familiar. Conclude that there is eventually no use for SQL here, is abandoning our cause too easily !     
I think we really need you expertise, if you are willing, of course


pierre


NB your examples were helpful, perhaps somewhat tricky for the real beginner.
Just for the fun:
your example for duplicate values was very good for describlng the power of the rowid, but it does not tell ypu what lines exactly contain those duplcate values.

Say, you populate the database with some values an set the values of field a1 to 5 in rows 1, 6 and 9.

Then looking for duplicate values in field a1:

select count(a1) as '# duplicates', a1 as 'value of a1' , group_concat(rowid) as 'rowids' from tbl group by a1 having count(a1) > 1

and you obtain the following result:

# of duplicates    value of a1     rowids
-----------------    ------------   ----------
   3                       5                1, 6, 9

[pierre]

Dec 18, 2022 3:33:41 GMT -5 honky said:

And here is an easy database.

did you intend to include a sample database ?

pierre

[metro]

Dec 18, 2022 17:46:46 GMT -5 pierre said:

I agree with pierre don't give up on contributing here.
I have learnt a lot from you over the years both here and on the RB forum.

I wonder if Carl would consider sacrificing one month of LB5 development to address the need for RB2.0

I know there have been some set-backs for Carl getting LB5 off the ground and having to start from scratch again.
It was May 3rd 2010 when mmiscool boasted he had seen LB5 first hand after he caught up with Carl. (post on old forum)
I'm sure all of us waiting for LB5 could wait an extra month.

Of course I have probably underestimated how long it would take to fix the bugs in RB.
Just putting it out there.

Interesting the quote from pierre was lost when I posted

[Rod]

The nub of what we are missing in this community is multi user access. Yes Liberty is single threaded, but it can act as a very flexible client. To be able to access and manage a shared database would be a HUGE improvement to what we have now. Run BASIC hinted at such power but for whatever reasons it has not been adopted. SQL, CSS and HTML were perhaps a barrier and an accessible server also. If SQL just gives us single user database functionality then we have not gained much since RAF and CSV et al provide similar functionality.

Imagine if you could use Liberty to write clients that accessed core business info from anywhere! SQL database functionality will be great to have and learn but we need to be able to break out from our single user, single desk prison. So server provision is a key component, perhaps Carl can rent out storage and bandwidth.

Now hopefully someone is going to tell me we can have all of that now by doing xyz!

[meerkat]

For me, Run Basic worked great. What killed it for me was the database locks. The only way out of the locks was to kill the backend RB process and restart it. Not acceptable with a lot of online users. Another problem was memory leaks. The leaks were caused when you called another program. Somehow it left a lot of stuff behind and eventually you ran out of memory.

I Knew that LB's db interface would have the same locks, so I wrote something called the 'lock test monster' to prove it. I thought once fixed on LB it would be fixed on RB. But for some reason it's never been fixed.

I moved to REBOL. It's now called RED. You can use SQLite even for sites with high traffic. Their interface is written to ensure IO success and I have never had a problem with it. Even high IO triggers are successful. The name implies that it is a lightweight db server. So if you want replication with failover you have to write it yourself. The one I wrote works fine if anyone is interested.

In my mind HTML has the best user interface. Through the years they have added all kinds of interfaces to barcodes, image processing, and free form adjustments. And JavaScript is great, especially for graphics.

All this exists in RunBasic. If only we could get a couple bugs fixed.