Post by meerkat on Mar 6, 2019 22:14:44 GMT -5
I wanted to see what it would take to do SQLite file maintenance using a grid. This program request a database and a table. It gives a list of data in the table. You can select ADC (Add, Chg., Dell) to select the record to maintain. It displays the record detail that can be changed, added, or deleted. Not being very familiar with LB, this may look a little crude. But you get some idea of what it may involve doing maintenance with a grid.
Because of my lack of knowledge of LB, there is probably a better way to do this. Or at least some better ideas.
Because of my lack of knowledge of LB, there is probably a better way to do this. Or at least some better ideas.
' -------------------------------------------------
' Supply database and table name
' -------------------------------------------------
[getDb]
filedialog "Find a Database File", "*.db", db$
if db$ = "" then
notice "No file selected. Thank you and have a great day!"
end
end if
' --------------------------------
' see if the file is sqlite format
' --------------------------------
open db$ for input as #1
a$ = lower$(input$(#1,15))
close #1
if left$(a$,13) <> "sqlite format" then ' check file format
notice db$;" ** Not SQLite file format **";a$
goto [getDb]
end if
' ----------------------------------------
' get tabel names from DB for selection
' ----------------------------------------
sqliteconnect #sql, db$ ' Connect to the DB
sql$ = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
dim tblList$(rows)
for i = 1 to rows
#row = #sql #nextrow()
tblList$(i) = #row name$()
next i
[getTbl]
combobox #tbl.list, tblList$(), [tblSel], 5, 35, 200, 50+rows*8
button #tbl.ex "Exit", [tblEx], UR, 15, 5
WindowWidth = 270 : WindowHeight = 100 + rows*12
open "Select a Table" for window as #tbl
wait
[tblSel]
tblName$ = #tbl.list selection$() ' what was selected
print "tblName:";tblName$
if tblName$ = "" then
notice "Table selected is MT"
'goto [getTbl]
wait
end if
[tblEx]
close #tbl
if tblName$ = "" then
#sql disconnect()
end
end if
' ---------------------------------
' How many records are in the table
' ---------------------------------
sql$ = "SELECT count(*) as numRecords FROM ";tblName$
#sql execute(sql$)
#row = #sql #nextrow()
numRecords = #row numRecords()
' -----------------------------------------
' names and number of columns
' -----------------------------------------
sql$ = "SELECT * FROM ";tblName$;" LIMIT 1"
#sql execute(sql$)
colNames$ = #sql columnnames$()
j = 0
for numCols = 1 to len(colNames$)
j = instr(colNames$,",",j+1)
if j = 0 then exit for
next numCols
' ------- this also gets the type and sizes
sql$ = "PRAGMA table_info("+tblName$+")" ' returns cid|name|type|notnull|dflt_value|pk
#sql execute(sql$)
numFlds = #sql ROWCOUNT()
dim fldName$(numFlds)
dim fldType$(numFlds)
dim fldSize$(numFlds)
dim fldDecm$(numFlds)
for i = 1 to numFlds
result$ = #sql nextrow$(" |") ' use nextrow because data has underline (dflt_value) in it
fldName$(i) = word$(result$,2," |")
a$ = word$(result$,3," |") + "( )"
fldType$(i) = word$(a$,1,"(")
a$ = word$(a$,2,"(")
a$ = word$(a$,1,")")
if instr(a$,",") then ' see if it has decimals
fldSize$(i) = word$(a$,1,",")
fldDecm$(i) = word$(a$,2,",")
else
fldSize$(i) = a$
end if
next i
' --------------------------------------------------------
' get max widths of data in each column
' and force it between 2 and 12 characters
' --------------------------------------------------------
x$ = strRep$(colNames$,",",")),max(length(")
sql$ = "SELECT max(length("+x$+")) FROM ";tblName$;" LIMIT 1"
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
if rows < 1 then
notice "No data in table:";tblName$
goto [getTbl]
end
end if
result$ = #sql nextrow$(",")
dim colWid(numCols)
colWid$ = "40,40"
cma$ = ","
totWid = 80
for i = 1 to numCols
colWid(i) = min(12,max(2,val(word$(result$,i,",")))) ' width between 2 and 12
colWid$ = colWid$ + cma$ + str$(colWid(i)* 10) ' adjust for pixels
totWid = totWid + colWid(i) * 10
'cma$ = ","
next i
' ----- set some start stuff -----------
pageNum = 1 ' start with page one
lpp = 20 ' num of lines per page
limit$ = " LIMIT ";lpp ' limit for sql
' ---------------------------------------
' sho a page of table data
' ---------------------------------------
[nextPage]
dim a$(numFlds+2, lpp)
print "141 dim a(:";numFlds+2;" ";lpp
sql$ = "SELECT rowid,* FROM ";tblName$;" ORDER BY ";tblName$;".rowid ";limit$
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
for i = 1 to rows
a$(0,i-1) = "ACD"
result$ = #sql nextrow$(" ";chr$(251))
for j = 1 to numCols
x$ = left$(trim$(word$(result$,j,chr$(251))),12)
a$(j,i-1) = x$
next j
next i
grid #w.grid, a$(), [clicked], 120, 10, 100+totWid, 100+rows*17.5 ' create a grid widget
' ----------sho page options -------------------------- x y w h
statictext #w.pgeHd, "_________Page_________" ,000,030,180,020
button #w.pre, "Prev", [pre] , UL ,000,060,030,020
button #w.nxt, "Next", [nxt] , UL ,080,060,030,020
button #w.ext, "EXIT", [ext] , UL ,040,090,030,020
textbox #w.pge ,040,058,030,020
statictext #w.rec, "Records:";numRecords ,002,110,100,020
open "DB:";db$;" Table:";tblName$ for window as #w ' open the grid
a = eval("#w.grid columnwidths("+colWid$+")") ' set grid col widths
print "166:";colWid$
#w.pge pageNum ' sho current page
q$ = chr$(34)
cma$ = ","
cn$ = """Mnt"",""ID""" ' column names for grid
for i = 1 to numCols ' set length of colNames and center then
cn$ = cn$ + cma$ + q$ + align$(fldName$(i),colWid(i),"c") + q$
'cma$ = ","
next i
print "176:";cn$
'input "xxx";x
a = eval("#w.grid columnnames("+cn$+")") ' put column names on the grid '
a = eval("#w.grid rownames("+rn$+")") 'label the rows
#w.grid rowlabelwidth(40) 'set sizes for rows
wait
[clicked]
xy$ = #w.grid cellxy$()
val$ = #w.grid value$() 'contents of cell clicked
print "xy:";xy$
print "val:";val$
c = val(word$(xy$,1))
if c <> 1 then wait
' ===================================================
' Record Maintenance
' ===================================================
r = val(word$(xy$,2))
#w.grid selectxy(2,r)
rowid$ = #w.grid value$() 'contents of cell clicked
dim m$(1,numFlds)
sql$ = "SELECT * FROM ";tblName$;" WHERE rowid = ";rowid$
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
if rows < 1 then
notice "Record not on file"
wait
end if
' ----- put data in grid array --------
result$ = #sql nextrow$(" ";chr$(251))
dim fldVal$(numFlds)
for i = 1 to numFlds
fldVal$(i) = trim$(word$(result$,i,chr$(251)))
m$(0,i -1) = fldVal$(i)
next i
grid #wm.grid, m$(), [clkMnt], 0, 10, 300, 100+numFlds*20 ' create a grid widget
' ----------sho Add Change Delete and Exit buttons ---- x y w h
button #wm.add, "Add", [add] , UL ,010,060,030,020
button #wm.chg, "Chg", [chg] , UL ,010,090,030,020
button #wm.del, "Del", [del] , UL ,010,120,030,020
button #wm.ext, "EXIT", [mex] , UL ,010,150,030,020
open "DB:";db$;" Table:";tblName$ for window as #wm ' open the grid
#wm.grid columnnames( "Value" )
#wm.grid columnwidths( 100 )
' ----- maintenance row names"
cma$ = ""
rmn$ = "" ' row names for grid
for i = 1 to numFlds ' set length of colNames and center then
rmn$ = rmn$ + cma$ + q$ + align$(fldName$(i),12,"r") + q$
cma$ = ","
next i
a = eval("#wm.grid rownames("+rmn$+")") 'label the rows
#wm.grid rowlabelwidth(200) 'set sizes for rows
wait
' ------------ Clicked maintenance -------
[clkMnt]
xy$ = #wm.grid cellxy$()
val$ = #wm.grid value$() 'contents of cell clicked
x = val(word$(xy$,1))
y = val(word$(xy$,2))
p$ = "Field: ";fldName$(y);" val: ";val$
prompt p$ ; val$
#wm.grid setvalue( val$ )
fldVal$(y) = val$
wait
' --------------------------------
' add a new record
' --------------------------------
[add]
val$ = ""
f$ = ""
goto [acd]
' --------------------------------
' change the record
' --------------------------------
[chg]
sep$ = ""
f$ = " (rowid,"
for i = 1 to numFlds
f$ = f$ + sep$ + fldName$(i)
sep$ = ","
next i
f$ = f$ + ")"
val$ = rowid$ + "','"
[acd]
sep$ = ""
for i = 1 to numFlds
val$ = val$ + sep$ + fldVal$(i)
sep$ = "','"
next i
sql$ = "INSERT OR REPLACE INTO ";tblName$;f$;" VALUES('";val$;"')"
print sql$
#sql execute(sql$)
goto [mex]
' -------------------------------
' Delete a record
' -------------------------------
[del]
sql$ = "DELETE FROM ";tblName$;" WHERE rowid = ";rowid$
print sql$
#sql execute(sql$)
[mex] ' get outta here
close #wm.grid
close #w.grid
goto [nextPage] ' re show the record list
' ------- exit------------------
[ext]
close #w
#sql disconnect()
end
' ---------------------------------
' Previous Next or User Page Number
' ----------------------------------
[pre]
ap = -1
goto [pageIt]
[nxt]
ap = 1
[pageIt]
prePage = pageNum
pageNum = pageNum + ap
totPages = int(numRecords / lpp)
#w.pge "!contents? goPageNum$"
if goPageNum$ <> "" then
goPageNum = int(val(goPageNum$))
if goPageNum <> prePage then
if goPageNum > totPages or goPageNum < 1 then
notice "Page must be between 1 and ";totPages
wait
end if
pageNum = goPageNum
end if
end if
pageNum = max(1,pageNum) ' make suer it has a page number
if lpp < 1 then lpp = 30 ' lines per page must be specified
lpp = max(5,lpp) ' make sure it has a least 5 lines per page
lpp = min(60,lpp) ' don not allow over 60 lines per page
if lpp * totPages <> numRecords then totPages = totPages + 1
pageNum = min(totPages,pageNum)
pageNum = max(1,pageNum)
limitBeg = (pageNum * lpp) - lpp 'limit begin value
limit$ = " LIMIT " ; limitBeg ; "," ; lpp
close #w
goto [nextPage]
' ---------------------------------------
' 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$ = space$(width)
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