!debug.on !debug.echo.on pro$ = "Y" % pro = "Y"es and free="N"o shoPro$ = "" gosub knoFun % Get to know your functions. ! ! ----------------------------------------------- ! Show Databases Database Request - Add or Select ! ----------------------------------------------- dbRequest: err$ = "0010 bfRequest: Database Request" theDb: h$=hd$ % heading h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" sql$ = "SELECT rowid,dbFile,dbLoc FROM dbList ORDER BY dbFile" sql.raw_query cur,knoDB,sql$ sql.query.length numDB,cur ! for i = 1 to numDB sql.next xdone,cur,rowid$,dbFile$,dbLoc$ if mod(i,2) then h$=h$+"" +~ "" next i ! h$=h$+ "
Database"+shoPro$+"
" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "
ActionFileLocation
" else h$=h$+"
" end if h$=h$+"" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ ""+dbFile$+""+dbLoc$+"
" dbUserInput: gosub userInput i = is_in("^^",theData$) thisDb$ = mid$(theData$,i+2) rowid$ = left$(theData$,i-1) dbFile$ = thisDb$ thisDbFile$ = replace$(theData$,"^^","") ! h1$ = "" if rqst$ = "ext" then end if rqst$ = "new" then goto newDb if rqst$ = "lod" then goto selectFile if rqst$ = "hlp" then goto shoHelp if rqst$ = "web" then goto shoWeb if rqst$ = "fnd" then goto find if rqst$ = "del" then infMsg$ = "Database "+thisDb$+" deleted from list rowid:"+rowid$ sql$ = "DELETE FROM dbList WHERE rowid="+rowid$ sql.exec knoDB,sql$ popup infMsg$,0,0,0 goto dbRequest endif file.exists f,thisDbFile$ if f = 0 then errMsg$ = "0080 The File:"+thisDbFile$ +" no longer exists" goto dbRequest endif sql.open wwDB,thisDbFile$ if rqst$ = "tbl" then goto tbles if rqst$ = "sql" then goto loadSql if rqst$ = "xrf" then goto dbXref if rqst$ = "exp" then goto exportDb if rqst$ = "qry" then goto query errMsg$ = "0085 Unexpected data type:"+ type$ + data$ goto dbRequest ! selectFile: typFile$ = "db" gosub chooseFile % Choose file from directory extLabel$ = "dbRequest" if rqst$ = "ext" then goto dbRequest sql.open sfil,thisFile$ % if not SQL it will error trap sql$ = "INSERT INTO dbList VALUES('"+thisName$+"','"+ddrPath$+"')" sql.exec knoDB,sql$ infMsg$ = thisName$+" is a SQLite DB
path: "+thisFile$ goto dbRequest ! ! ================================================================== ! Load Sql ! ================================================================== loadSql: typFile$ = "sql" gosub chooseFile % Choose file from directory err$ = "0100 dbRequest: Load SQL from file: "+thisFile$ if rqst$ = "ext" then goto dbRequest infMsg$ = "Load Sql - Working File:"+thisFile$ popup infMsg$,0,0,4 !gosub shoProgress count = 0 cr$ = chr$(13) ccr$ = cr$+cr$ lf$ = chr$(10) fin = 10 text.open r,fin,thisFile$ do text.readln fin,a1$ a1$ = trim$(a1$) if left$(a1$,1) = "#" then goto getNextLoad if a1$ <> "EOF" then a2$ = a2$ + a1$ else a2$ = a2$ + ";" endif j = is_in(";",a2$) if j > 0 then a$ = left$(a2$,j) a2$ = mid$(a2$,j+1) a2$ = strRep$(a2$,lf$,"") a2$ = strRep$(a2$,cr$,"") gosub doLoadSql endif getNextLoad: until a1$ = "EOF" text.close fin goto dbRequest doLoadSql: a$ = replace$(a$,lf$,cr$) a$ = strRep$(a$,ccr$,cr$) a$ = replace$(a$,chr$(9)," ") a$ = strRep$(a$," "," ") a$ = strRep$(a$,"( ","(") a$ = strRep$(a$," )",")") a$ = strRep$(a$,", ",",") sql$ = strRep$(a$," ,",",") count = count + 1 if len(trim$(sql$)) > 1 then sql.exec wwDB,sql$ if mod(count,50) = 0 then infMsg$ = "Working .. Count:"+format$("#####",count) gosub shoProgress endif endif RETURN ! ! ========================================== ! Table - field cross reference ! ========================================== dbXref: ! ----- create in memory database -------- sql.open memDB, ":memory:" mem$ = "CREATE TABLE xref(tbl,fld,pos)" sql.exec memDB,mem$ cma$ = "" tblNames$ = "" sql$ = "SELECT tbl_name,sql FROM sqlite_master WHERE type='table' AND tbl_name <> 'android_metadata' ORDER BY tbl_name" sql.raw_query wwCur,wwDB,sql$ sql.query.length numTables, wwCur if numTables < 1 then popup "No Tables",0,0,0 wrnMsg$ = "0168 No Tables" sql.close memDB goto dbRequest endif xrfFile$ = "../data/"+dbFile$+"_xrf" file.exists f,xrfFile$+".html_save" if f <> 0 then file.delete f,xrfFile$+".html_save" file.exists f,xrfFile$+".html" if f <> 0 then file.rename xrfFile$+".html",xrfFile$+".html_save" text.open w,f,xrfFile$+".html" array.delete tableNames$[] dim tableNames$[numTables] maxFlds = 0 for nt = 1 to numTables sql.next xdone,wwCur,tbl_name$,rawTableSql$ infMsg$ = "Working on Table: "+tbl_name$ gosub shoProgress tableNames$[nt] = tbl_name$ gosub tableInfo if maxFlds < numFields then maxFlds = numFields for j = 1 to numFields mem$ = "INSERT INTO xref VALUES('"+tbl_name$+"','"+fname$[j]+"',"+format$("###",j)+")" % xref file sql.exec memDB,mem$ next j next nt mem$ = "SELECT fld FROM xref GROUP BY fld ORDER BY fld" sql.raw_query mmCur,memDB,mem$ sql.query.length totFlds, mmCur array.delete fldNames$[] dim fldNames$[totFlds] for i = 1 to totFlds sql.next xdone,mmCur,fld$ fldNames$[i] = fld$ next i ! ----- Heading ---------- hx$ = "" +~ "
"+dbFile$ +~ "" +~ "" text.writeln f, hx$ text.writeln f,"" for i = 1 to numTables hx$ = "" for j = 1 to len(tableNames$[i]) hx$ = hx$ + mid$(tableNames$[i],j,1)+"
" next j text.writeln f,"" next i text.writeln f,"" ! ------------- Detail xRef ---------- for nt = 1 to totFlds infMsg$ = "Working on field: "+ fldNames$[nt] gosub shoProgress mem$ = "SELECT fld,tbl FROM xref WHERE fld='"+fldNames$[nt]+"' ORDER BY tbl" sql.raw_query mmCur,memDB,mem$ sql.query.length rows, mmCur hx$ = "" +~ "" j = 1 for i = 1 to rows sql.next xdone,mmCur,fld$,tbl$ for j = j to numTables if tableNames$[j] = tbl$ then hx$ = hx$ + "" F_N.break else hx$ = hx$ + "" endif next j j = j + 1 next i for k = j to numTables hx$ = hx$ + "" next k text.writeln f,hx$+"" next nt text.writeln f, "
"+dbFile$+"

Fields
*
C
o
u
n
t
"+hx$+"
"+fldNames$[nt]+""+format$("####",rows)+"X
" ! text.close f sql.close memDB infMsg$ = "Output xref file is "+xrfFile$+".html" !gosub userInput goto dbRequest ! ! ================================================================== ! Database tables ! ================================================================== tbles: file.exists f,thisDbFile$ if f = 0 then errMsg$ = "The Database "+thisDb$+" no longer exists at: "+thisDbFile$ wrnMsg$ = sql$ preThisDbFile$ = "_+_+_+-6=7-8=9-1=3-4=" goto dbRequest end if if preThisDbFile$ = thisDbFile$ then goto preTableSame sql.open wwDB,thisDbFile$ sql$ = "select tbl_name from sqlite_master WHERE type = 'table' " sql$=sql$+"AND tbl_name <> 'android_metadata' ORDER BY tbl_name" % get the list of tables sql.raw_query wwCur,wwDB,sql$ sql.query.length numTables, wwCur if numTables < 1 then popup "No Tables",0,0,0 goto theTables endif array.delete tableNames$[] dim tableNames$[numTables] for i = 1 to numTables sql.next xdone,wwCur,tbl_name$ tableNames$[i] = tbl_name$ next i !-------------------------------------------------- theTables: if preThisDbFile$ = thisDbFile$ then goto preTableSame preThisDbFile$ = thisDbFile$ htb$ = "" +~ "" +~ "" +~ "" +~ "" h1$= "" ! for i = 1 to numTables infMsg$ = "Working on Table: "+tableNames$[i] gosub shoProgress h1$=h1$+ "" next i ! htb$=htb$+h1$ h1$ = "" htb$=htb$+ "
Tables For:"+thisDbFile$+"
" +~ "" +~ "
"+thisDb$ +~ "
Maint"+format$("####",numTables)+" Tables
" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ ""+tableNames$[i]+"
" +~ "" +~ "" +~ "
" +~ "
" preTableSame: gosub msg % Display messages if any h$=hd$+ "
" + m$ + htb$ newTblSw = 0 mySearch$ = "" gosub userInput ! if type$ = "DAT:" then thisTable$ = theData$ if rqst$ = "fld" then goto fields % show fields if rqst$ = "lod" then goto loadCsv % load CSV if rqst$ = "brs" then goto fields % browse table if rqst$ = "emt" then goto mtTable % MT the table if rqst$ = "drp" then goto fields % drop the table if rqst$ = "gen" then goto fields % generate rfo program if rqst$ = "ren" then goto renTable % rename table if rqst$ = "add" then goto addTbl % add table if rqst$ = "ext" then goto dbRequest % rename table endif ! errMsg$ = "364 Unexpected data type:"+ type$ + data$ goto tbles ! ! --------------------------- ! make new database ! --------------------------- newDb: err$ = "0400 dbRequest: Create new DB" h$=hd$ % web page header h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ +~ "
" +~ "" +~ "" +~ "" +~ "" +~ "
Create Database
Database Name
" +~ "" +~ "" +~ "
" +~ "
" gosub userInput if type$ = "DAT:" then goto dbRequest ! if type$ = "FOR:" then % User data returned newDb$ = getData$("name",data$) newDb$ = strRep$(newDb$," ","") newDb$ = trim$(newDb$) sql.open newDB,newDb$ infMsg$ = "Database "+newDb$+" has been created as file '../databases/"+newDb$+"'" ! infMsg$ = infMsg$ + "
You need to [Include] the Database it to bring it into the system
" file.root dbLoc$ sql$ = "INSERT OR REPLACE INTO dbList VALUES('"+newDb$+"','../databases/')" sql.exec knoDB,sql$ goto dbRequest endif ! ! --------------------------- ! add new table ! --------------------------- addTbl: err$ = "0430 addTbl: Add Table" preThisDbFile$ = "" h$=hd$ % web page header h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ +~ "
" +~ "" +~ "" +~ "" +~ "" +~ "
Create Table
Table Name
" +~ "" +~ "" +~ "
" +~ "
" gosub userInput if type$ = "DAT:" then goto tbles ! ! ------------------------------------- ! get table name ! ------------------------------------- if type$ = "FOR:" then % User data returned br$ = "" errMsg$ = "" thisTable$ = getData$("name",data$) thisTable$ = strRep$(thisTable$," ","") if thisTable$ = "" then errMsg$ = "Please specify a table name" goto theFields endif x$ = lower$(thisTable$) ! for i = 1 to len(thisTable$) if mid$(x$,i,1) >= "a" & mid$(x$,i,1) <= "z" then goto testOk1 if mid$(x$,i,1) >= "0" & mid$(x$,i,1) <= "9" then goto testOk1 if mid$(x$,i,1) = "_" then goto testOk1 errMsg$ = "Table name must contain a->z, A->Z, 0->9, underscore (_)" goto theFields testOk1: next i ! sql$ = "SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name='"+thisTable$+"'" sql.raw_query tempCur,wwDB,sql$ sql.next xdone,tempCur,a$ if a$ = thisTable$ then errMsg$ = "Table:"+thisTable$+" already on file" goto theFields endif newTblSw = 1 % set switch for adding new table numFields = 0 preThisDbFile$ = "p;-)" goto theFields endif ! ================================================================== ! table fields ! ================================================================== fields: err$ = "0490 fields: Table Fields" sql$ = "SELECT sql FROM sqlite_master WHERE tbl_name = '"+thisTable$+"' AND type = 'table';" sql.raw_query wwCur, wwDB,sql$ sql.next xdone,wwCur,rawTableSql$ gosub tableInfo % table information if rqst$ = "brs" then goto tryBrowse % browse the table if rqst$ = "gen" then goto rfoGen % generate rfo program ! ================================================================== ! field maintenance ! ================================================================== theFields: err$ = "0500 fields: Field Maintenaance" h$=hd$ % web page header h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" hasPk = 0 for i = 1 to numFields h$=h$+ "" if right$(thisTable$,6) = "__save" | rqst$ = "drp" then h$=h$+ "" goto nxtFld endif h$=h$+ "" +~ "" +~ "" nxtFld: h$=h$+ "" +~ "" +~ "" +~ "" +~ "" if rqst$ = "drp" then h$=h$ + "" goto noPrime endif pkn$ = "[ ]" if fauto$[i] = "pkn" then pkn$ = "X" hasPk = 1 endif if pkn$ = "X" then h$=h$+ "" else if upper$(ftype$[i]) = "INTEGER" then h$=h$+ "" else h$=h$+ "" endif endif noPrime: h$=h$+ "" next i if rqst$ = "drp" then h$=h$+ "" +~ "
Database:"+thisDb$+"Table:"+thisTable$+"
MaintAddNameTypeLengthDecNullPrimary
Key
"+fname$[i]+""+ftype$[i]+""+fsize$[i]+""+fdcml$[i]+""+fnull$[i]+"[X]
" h$=h$+ "" goto nxtFld1 endif if right$(thisTable$,6) = "__save" then h$=h$+ "
" goto nxtFld2 endif h$=h$+ "
Add=>" +~ " nxtFld2: if newTblSw <> 1 then h$=h$+ "" endif !h$=h$+ "" h$=h$+ "" nxtFld1: h$=h$+ "" +~ " if rqst$ <> "drp" then if hasPk = 0 then h$=h$+ "No Primary" else h$=h$+ "" endif endif h$=h$+ "
" ! ! Index ! sql$ = "SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='"+thisTable$+"' ORDER BY name" sql.raw_query wwCur, wwDB,sql$ sql.query.length rows,wwCur h1$ = "" if rows > 0 then h1$= "" h1$=h1$+ "" h1$=h1$+ "" h1$=h1$+ "" for dispLine = 1 to rows sql.next xdone,wwCur,idxType$,name$,tbl_name$,root$,index$ idxType$ = "" a$ = upper$(index$) i = is_in("UNIQUE",a$) if i > 0 then idxType$ = "UNIQUE" j = is_in("(",index$) index$ = mid$(index$,j) h1$=h1$+ "" next dispLine endif h$ = h$ + h1$+ "" preRqst$ = "" gosub userInput if type$ = "DAT:" then % User data returned tableRqst$ = theData$ fldNum = 0 if isNumeric(theData$) = 1 then fldNum = val(theData$) ! if rqst$ = "add" then goto clrField preRqst$ = rqst$ if rqst$ = "pkn" then goto primary % Primary key if rqst$ = "pkx" then goto primary % Primary none if rqst$ = "bef" then goto clrField if rqst$ = "chg" then goto getField if rqst$ = "del" then goto getField if rqst$ = "drp" then goto dropTable ! if rqst$ = "tbl" then goto tables if rqst$ = "idx" then goto index if rqst$ = "db" then goto dbRequest if rqst$ = "end" then goto clrField if rqst$ = "ext" then goto tbles endif errMsg$ = "0570 Unexpected data type:"+ type$ + data$ goto dbRequest ! ! ----------------------------------- ! Clear field ! ----------------------------------- clrField: err$ = "0630 fields: Clr Field" name$ = "" type$ = "VARCHAR" size$ = "" dcml$ = "" null$ = "" goto fieldDtl getField: name$ = fname$[fldNum] type$ = ftype$[fldNum] size$ = fsize$[fldNum] dcml$ = fdcml$[fldNum] null$ = fnull$[fldNum] hname$ = name$ %hold name ! ! if delete make user it has no index ! if rqst$ = "del" then gosub chkIdx if errMsg$ <> "" then errMsg$ = "Cannot DELETE "+errMsg$ goto fields endif endif ! --------------------------------------------- ! Field Detail ! --------------------------------------------- fieldDtl: err$ = "0660 fieldDtl: Field Detail :"+nane$ h$=hd$ % web page header h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ + "
" +~ "
Index: "+thisTable$+"
NameTypeIndex
"+name$+""+idxType$+""+index$+"
" +~ "" +~ "" if rqst$ = "del" then h$=h$+ " else h$=h$+ "" endif h$=h$+ "" +~ "" +~ "" if rqst$ = "del" then h$=h$+ " else h$=h$+ "" endif h$=h$+ "" if rqst$ = "del" then h$=h$+ " else h$=h$+ "" endif h$=h$+ "" if rqst$ = "del" then h$=h$+ " else if upper$(null$) = "NULL" then chk$ = "checked" else chk$ = "" h$=h$+ "" endif h$=h$+ "
Field Maintenance
"+thisTable$+"
Name"+name$+"
Type" if rqst$ = "del" then h$=h$+ " else for i = 1 to numDataType if upper$(type$) = dataType$[i] then chk$ = "checked" else chk$ = "" h$=h$+ "" if mod(i,8) = 0 then h$=h$+ "" next i endif h$=h$+ "
"+type$+""+dataType$[i]+"
Size"+size$+"
Decimals"+dcml$+"
Null"+null$+"
" if rqst$ = "add" then a$ = "Add after: " +fname$[fldNum] if rqst$ = "bef" then a$ = "Add before: "+fname$[fldNum] if rqst$ = "chg" then a$ = "Change" if rqst$ = "del" then a$ = "Delete" if rqst$ = "end" then a$ = "Add At End" h$=h$+ "" +~ "" +~ "
" +~ "" gosub userInput if type$ = "DAT:" then % User data returned if rqst$ = "ext" then goto fields errMsg$ = "0730 unexpected error for fieldDtl button" goto fieldDtl: endif ! ------------------------------------- ! User wants to delete this field ! 1. rename the current talbe as table_save ! 2. create new table with deleted field ! 3. copy data from table_save to table ! ------------------------------------- if preRqst$ = "del" then makeCopy: err$ = "0740 fields: Make Copy" gosub makSaveTable % rename the table to table__save fldDtl$ = "" cma$ = "" colNames$ = "" for i = 1 to numFields if i <> fldNum then siz$ = "" if fsize$[i] <> "" then siz$ = "(" + fsize$[i] if fdcml$[i] <> "" then siz$ = siz$ + ","+fdcml$[i] siz$ = siz$ + ") " endif if fauto$[i] = "pkn" then fauto$[i] = "PRIMARY KEY" fldDtl$ = fldDtl$ +cma$+fname$[i]+" "+ftype$[i]+" "+siz$+fnull$[i]+" "+fauto$[i] colNames$ = colNames$ +cma$+fname$[i] cma$ = "," endif next i sql$ = "CREATE TABLE "+thisTable$+" ("+fldDtl$+");" sql.exec wwDB,sql$ sql$ = "INSERT into "+thisTable$+" ("+colNames$+") SELECT "+colNames$+" FROM "+saveTable$ sql.exec wwDB,sql$ goto fields endif ! ------------------------------------- ! Get user input for detail field 710 ! ------------------------------------- if type$ = "FOR:" then % User data returned errMsg$ = "" br$ = "" name$ = getData$("name",data$) name$ = strRep$(name$," ","") % Blanks not allowed name$ = strRep$(name$,"__","_") % Single underscors only name$ = trim$(name$) x$ = lower$(name$) if name$ = "" then errMsg$ = errMsg$ + br$+ "Please specify a field name" br$ = "
" endif if left$(x$,1) < "a" | left$(x$,1) > "z" then % first character muat be alpha errMsg$ = "Field Names must begin with Alpha "+name$ br$ = "
" endif for i = 1 to len(name$) if mid$(x$,i,1) >= "a" & mid$(x$,i,1) <= "z" then goto nxtNameChk if mid$(x$,i,1) >= "0" & mid$(x$,i,1) <= "9" then goto nxtNameChk if mid$(x$,i,1) = "_" then goto nxtNameChk errMsg$ = errMsg$ + br$ + "Illegal Field name..
Only Alpha, Numbers, and Underscore (_) allowed" br$ = "
" F_n.break endif nxtNameChk: next i if name$ <> hname$ % did they change the name for i = 1 to numFields % duplicate filed names not allowed if x$ = lower$(fname$[i]) then errMsg$ = errMsg$ + br$ + "Field name "+name$+" already in use" br$ = "
" endif next i endif type$ = getData$("type",data$) if type$ = "" then errMsg$ = errMsg$ + br$+"Please specify a data type" br$ = "
" else type$ = dataType$[val(type$)] endif size$ = getData$("size",data$) size$ = strRep$(size$,",","") % no commas (.) size$ = strRep$(size$,".","") % no periods (.) size$ = trim$(size$) if size$ <> "" & isNumeric(size$) = 0 then errMsg$ = errMsg$ + br$+"Field Size must be numeric "+size$ br$ = "
" endif dcml$ = getData$("dcml",data$) dcml$ = strRep$(dcml$,",","") % no commas dcml$ = strRep$(dcml$,".","") % no periods dcml$ = trim$(dcml$) if dcml$ <> "" & isNumeric(dcml$) = 0 then errMsg$ = errMsg$ + br$+"Field Decimals must be numeric" br$ = "
" endif if size$ = "" & dcml$ <> "" then errMsg$ = errMsg$ + br$+"Decimals is:"+dcml$+" but nn size" br$ = "
" endif theSize$ = "" if size$ <> "" then theSize$ = "("+size$+")" if dcml$ <> "" then theSize$ = "("+size$+","+dcml$+")" dcml$ = getData$("null",data$) if errMsg$ <> "" then goto fieldDtl % we got us a message 772 fldDtl$ = name$+" "+type$+theSize$+" "+null$ if newTblSw = 1 then sql$ = "CREATE TABLE "+thisTable$+" ("+fldDtl$+")" % If new table create it newTblSw = 0 sql.exec wwDB,sql$ goto fields endif ! see what the previous request was if preRqst$ = "end" then sql$ = "ALTER TABLE "+thisTable$+" ADD "+fldDtl$ sql.exec wwDB,sql$ goto fields endif if preRqst$ = "bef" then befFname$ = fname$[fldNum] i = is_in(befFname$,cleanTableSql$) befSql$ = left$(cleanTableSql$,i-1)+fldDtl$+","+mid$(cleanTableSql$,i) gosub makSaveTable % rename the table to table__save sql.exec wwDB,befSql$ % contains fld added before sql$ = "INSERT into "+thisTable$+"("+colNames$+") SELECT "+colNames$+" FROM "+saveTable$ sql.exec wwDB,sql$ goto fields endif ! ---------------- Changed table ------------------------- if preRqst$ = "chg" then f$ = "" newColNames$ = "" cma$ = "" for i = 1 to numFields if i = fldNum then f$ = f$ + cma$ + fldDtl$ newColNames$ = newColNames$ + cma$ + name$ else f$ = f$ + cma$ + replace$(fldData$[i],":",",") newColNames$ = newColNames$ + cma$ + fname$[i] endif cma$ = "," next i gosub makSaveTable % rename the table to table__save sql$ = "CREATE TABLE "+thisTable$+" ("+f$+")" % create new table with new info sql.exec wwDB,Sql$ sql$ = "INSERT into "+thisTable$+"("+newColNames$+") SELECT "+colNames$+" FROM "+saveTable$ sql.exec wwDB,sql$ goto fields endif errMsg$ = "820 Unexpected data type:"+ type$ +" data:"+data$+" rqst:"+rqst$+" pre:"+preRqst$ goto fields ! ------------------------------- ! check if index exist for name$ ! ------------------------------- chkIdx: sql$ = "SELECT * FROM sqlite_master WHERE type='index'" +~ " AND lower(substr(sql,16)) <> 'sqlite_autoindex'" +~ " AND tbl_name='"+thisTable$+"' ORDER BY name" sql.raw_query wwCur,wwDB,sql$ errMsg$ = "" xdone = 0 do sql.next xdone,wwCur,type$,idxName$,tblName$,idxDtl$ idxDtl$ = idxDtl$ + "" if !xdone then idxDtl$ = replace$(idxDtl$,"(",",") % see if(,)name$ exists if is_in(","+name$,idxDtl$) <> 0 then errMsg$ = name$+" Has index at: "+idxName$+"
" goto fields endif endif until xdone return ! ================================================================== ! table index ! ================================================================== index: err$ = "0910 index: Table Index: "+thisTable$ h$=hd$ % web page header h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" sql$ = "SELECT * FROM sqlite_master WHERE type='index'" +~ " AND lower(substr(sql,16)) <> 'sqlite_autoindex'" +~ " AND tbl_name='"+thisTable$+"' ORDER BY name" sql.raw_query wwCur, wwDB,sql$ sql.query.length rows,wwCur for dispLine = 1 to rows sql.next xdone,wwCur,type$,name$,tbl_name$,root$,index$ a$ = upper$(index$) i = is_in("UNIQUE",a$) idxType$ = "" if i > 0 then idxType$ = "UNIQUE" i = is_in("(",index$) if i > 0 then a$ = mid$(index$,i+1) else a$ = "" a$ = replace$(a$,")",",") a$ = strRep$(a$," "," ") + "xxx" i = is_in(",",a$) numIdx = 0 while i > 0 numIdx = numIdx + 1 i = is_in(",",a$,i+1) REPEAT array.delete idxField$[] dim idxField$[numIdx] for i = 1 to numIdx j = is_in(",",a$) idxField$[i] = left$(a$,j-1) a$ = mid$(a$,j+1) next i h$=h$+ "" next dispLine h$=h$+ "
Database Keys
ActionKey NameTypeField
" +~ "" +~ ""+name$ +~ ""+idxType$ +~ "" br$ = "" for i = 1 to numIdx h$=h$+ br$+idxField$[i] br$ = "
" next i h$=h$+ "
" +~ "" +~ "" +~ "
" gosub userInput if rqst$ = "del" then goto delIdx if rqst$ = "add" then goto addIdx if rqst$ = "ext" then goto fields errMsg$ = "970 Unexpected data type:"+ type$ + data$ goto index: ! -------------------------------------- ! move table to save | need thisTable$ = table name ! -------------------------------------- makSaveTable: err$ = "0980 tbles: Problems Saveing old Table:"+thisTable$ saveTable$ = ""+thisTable$+"__save" sql$ = "SELECT count(*) as cnt FROM sqlite_master WHERE tbl_name = '"+saveTable$+"' AND type = 'table';" sql.raw_query wwCur, wwDB,sql$ sql.next xdone,wwCur,cnt$ cnt = val(cnt$) if cnt <> 0 then % if save table exist - DROP IT sql$ = "DROP TABLE "+saveTable$ sql.exec wwDB,sql$ endif sql$ = "ALTER TABLE "+thisTable$+" RENAME TO "+saveTable$ sql.exec wwDB,sql$ RETURN ! -------------------- ! delete index ! -------------------- delIdx: err$ = "1000 index: Delete Index: "+name$ name$ = theData$ sql$ = "DROP INDEX '"+name$+"'" sql.exec wwDB,sql$ infMsg$ = "INDEX:"+name$+" deleted" goto index ! ------------------------------- ! Add index ! ------------------------------- addIdx: err$ = "1010 addIdx: Add Index: "+thisTable$ h$=hd$+"
% web page header gosub msg % Display messages if any h$=h$+ m$ +~ "
" +~ "" +~ "" +~ "" +~ "
Add Index
Index Name" +~ "
Key is Unique" +~ "Yes" +~ "
" +~ "" +~ "" for i = 1 to numFields h$=h$+ "" next i h$=h$+ "
SeqDescName
" +~ "" +~ "" +~ "" +~ ""+fname$[i] +~ "
" +~ "" +~ "" +~ "
" +~ "
" gosub userInput if type$ = "DAT:" then % User data returned thisTable$ = theData$ if rqst$ = "ext" then goto index endif ! ------------------------------------- ! get field definition ! ------------------------------------- array.delete idxField$[] array.delete idxSeq[] array.delete idxDesc$[] dim idxField$[numFields] dim idxSeq[numFields] dim idxDesc$[numFields] numIdx = 0 if type$ = "FOR:" then % User data returned uniq$ = getData$("uniq",data$) idxName$ = getData$("idxName",data$) br$ = "" for i = 1 to numFields seqNum$ = getData$("s"+trim$(format$("####",i)),data$) desc$ = getData$("d"+trim$(format$("####",i)),data$) if seqNum$ <> "" then if isNumeric(seqNum$) = 0 then errMsg$ = errMsg$ + br$ + "Field sequence:"+fname$[i]+" is not numeric "+seqNum$ br$ = "
" else numIdx = numIdx + 1 idxField$[numIdx] = fname$[i] idxSeq[numIdx] = val("0"+seqNum$) idxDesc$[numIdx] = desc$ endif endif nxtidxn: next i endif if numIdx < 1 then errMsg$ = errMsg$ + br$ + "Please specify a field to index by entering a seq number next to the field" br$ = "
endif if br$ <> "" then goto addIdx ! -------------------------- ! sort the index ! -------------------------- idxSeq[numIdx + 1] = 99999 sortSw = 1 while sortSw = 1 sortSw = 0 for i = 1 to numIdx if idxSeq[i] > idxSeq[i+1] then sortSw = 1 hseq = idxSeq[i] hif$ = idxField$[i] hde$ = idxDesc$[i] idxSeq[i] = idxSeq[i+1] idxField$[i] = idxField$[i+1] idxDesc$[i] = idxDesc$[i+1] idxSeq[i+1] = hseq idxField$[i+1] = hif$ idxDesc$[i+1] = hde$ endif next i REPEAT idxFields$ = "" a1$ = "" cma$ = "" cnc$ = "" oa$ = " ON " on$ = "" for i = 1 to numIdx idxFields$ = idxFields$ + cma$ + idxField$[i] + " " + idxDesc$[i] a1$ = a1$ + cnc$ + "coalesce(a."+idxField$[i]+",\"\")" on$ = on$ + oa$ + "a."+idxField$[i]+" = b."+idxField$[i] oa$ = " AND " cma$ = "," cnc$ = "||'^^'||" next i if uniq$ <> "" then sql$ = "SELECT DISTINCT("+a1$+") FROM "+thisTable$+" as a JOIN "+thisTable$+" as b "+on$+" WHERE a.rowid <> b.rowid LIMIT 5" sql.raw_query wwCur,wwDB,sql$ count=0 br$ = "" xdone = 0 do sql.next xdone,wwCur,data$ if !xdone then count = count + 1 errMsg$ = errMsg$ + br$ + idxFields$+": "+strRep$(data$,"^^",chr$(9)) br$ = "
" endif until xdone endif if br$ <> "" then errMsg$ = "Unique index has Duplicates
"+errMsg$ goto addIdx endif chkIdxName: sql$ = "SELECT name FROM sqlite_master WHERE type='index' AND name='"+idxName$+"'" sql.raw_query wwCur,wwDB,sql$ sql.next xdone,wwCur,a$ if a$ = idxName$ then idxName$ = idxName$ + "x" goto chkIdxName endif ! ---------------------------- ! add the index ! ---------------------------- sql$ = "CREATE "+uniq$+" INDEX "+idxName$+" ON "+thisTable$+"("+idxFields$+")" sql.exec wwDB,sql$ goto index ! --------------------------------- ! rename save table back ! --------------------------------- reNameSave: sql$ = "ALTER TABLE "+saveTable$+" RENAME TO "+thisTable$ sql.exec wwDB,sql$ goto fields ! -------------------------------- ! Primary key ! Make sure it's a unique field ! -------------------------------- primary: err$ = "1160 reNameSave: Primary " thisField$ = theData$ ! ! if no primary index and there was a index then ! set it off and make a copy ! if rqst$ = "pkx" then for i = 1 to numFields if fauto$[i] <> "" then fauto$[i] = "" goto makeCopy endif next i goto fields endif ! ! check for duplicates on requested primary key ! on$ = "ON a."+thisField$+" = b."+thisField$ sql$ = "SELECT a."+thisField$+" FROM "+thisTable$+" as a JOIN "+thisTable$+" as b "+on$+" AND a.rowid <> b.rowid LIMIT 1" sql.raw_query wwCur,wwDB,sql$ xdone = 0 do sql.next xdone,wwCur,data$ if !xdone then count = count + 1 errMsg$ = "Cannot assign Primary Key - Duplicate Key of field:"+thisField$+" Data:"+data$ goto theFields endif until xdone for i = 1 to numFields fauto$[i] = "" if fname$[i] = thisField$ then fauto$[i] = rqst$ next i goto makeCopy ' -------------------------------------- ' Load CSV file to DB ' -------------------------------------- loadCsv: err$ = "1200 loadCsv: Load CSC file to DB" sep$ = "," encl$ = "\"" term$ = ";" frow$ = "" loadCsv1: typFile$ = "csv" gosub chooseFile % Choose file from directory: return thisFile$ if rqst$ = "ext" then goto tbles ! byte.open r,bfil,thisFile$ h$=hd$ gosub msg % Display messages if any h$=h$+ m$ +~ "
" +~ "" +~ "
Load CSV to Table:"+thisTable$+"" +~ "
Field Name" +~ "" +~ "
Fields Enclosed With" +~ "" +~ "
Field Seperation" +~ "" +~ "
Line termination" +~ "" +~ "
First Row" +~ "Use it as Field Names
" +~ "Skip First Row
" +~ "Use First row as normal
" +~ "
" +~ "" +~ "" +~ "
" +~ "
" gosub userInput ! if type$ = "DAT:" then % User data returned if rqst$ = "ext" then goto theTables endif ! if type$ = "FOR:" then % User data returned br$ = "" thisFile$ = getData$("dir",data$) thisFile$ = trim$(thisFile$) if thisFile$ = "" then errMsg$ = errMsg$ + "Please specify a file name" br$ = "
" endif encl$ = trim$(getData$("encl",data$)) if left$(encl$,1) = "%" then encl$ = chr$(hex(mid$(encl$,2))) if encl$ = "" then wrnMag$ = wrnMsg$ + br$+"Fields are not enclosed" br$ = "
" endif sep$ = trim$(getData$("sep",data$)) if left$(sep$,1) = "%" then sep$ = chr$(hex(mid$(sep$,2))) if sep$ = "" then errMsg$ = errMsg$ + br$+"Please specify a seperation character" br$ = "
" endif term$ = trim$(getData$("term",data$)) if left$(term$,1) = "%" then term$ = chr$(hex(mid$(term$,2))) if term$ = "" then wrnMag$ = wrnMsg$ + br$+"Line has no termination character" br$ = "
" endif lt = len(term$) frow$ = trim$(getData$("frow",data$)) if errMsg$ <> "" then goto loadCsv1 sepr$ = encl$+sep$+encl$ endif ! thisFile$ = replace$(thisFile$,"%2F","/") i = is_in("%",thisFile$) while i > 0 thisFile$ = left$(thisFile$,i-1) + chr$(hex(mid$(thisFile$,i+1,2))) + mid$(thisFile$,i+3) i = is_in("%",thisFile$) repeat text.open r,f,thisFile$ if frow$ <> "3" then % Use first line for names text.readln f,data$ if data$ = "EOF" then errMsg$ = "No Data in file" goto loadCsv1 endif endif ! if frow$ = "1" then colNames$ = trim$(data$) colNames$ = replace$(colNames$,sepr$,",") % turn seperators to comma (,) colNames$ = replace$(colNames$,encl$,"") % get rid of leading and ending enclosures ln = len(colNames$) if term$ <> "" then if right$(colNames$,lt) = term$ then colNames$ = left$(colNames$,ln-lt) % get rid of termination characters endif endif ! if frow$ <> "1" then gosub tableInfo % use table infor for colNames ins$ = "INSERT INTO "+thisTable$+" ("+colNames$+") VALUES " cnt = 0 le = len(encl$) lt = len(term$) numCsv = 0 trm$ = "," do text.readln f,a$ a$ = trim$(a$) if a$ <> "EOF" then if sep$ <> "," then a$ = replace$(a$ if encl$ <> "'" then a$ = replace$(a$,encl$,"'") if term$ <> ";" then a$ = replace$(a$,term$,"") a$ = replace$(a$,";",");") sql$ = ins$ + "(" + a$ numCsv = numCsv + 1 if mod(numCsv,100) = 0 | numCsv = 1 then infMsg$ = thisTable$+" Count:"+format$("#######",numCsv) gosub shoProgress endif ! errMsg$ = "Err trying to load CSV num:"+str$(numCsv)+" data:"+a$ sql.exec wwDB,sql$ endif until a$ = "EOF" errMsg$ = "" text.close f infMsg$ = format$("######",numCsv)+" CSV records loaded to "+thisTable$ goto tbles ! ! =================================================================== ! browse the table ! =================================================================== tryBrowse: err$ = "1330 tryBrowse: Browse Table: "+thisTable$ ! sql$ = "SELECT count(*) FROM "+thisTable$ + mySearch$ % how many records sql.raw_query wwCur,wwDB,sql$ sql.next xdone,wwCur,numRecords$ numRecords = val(numRecords$) sortBy$ = "rowid" pageNum = 1 ! ! -------------------------------------------------- ! tryBrowse1: !html.open html.clear.cache html.load.string "." limitBeg = (pageNum * lpp) - lpp %limit begin value dispLine = 0 limit$ = " LIMIT " + format$("####%",limitBeg) + "," + format$("####%",lpp) totPages = numRecords / lpp % lpp is lines per page if floor(totPages) <> totPages then totPages = totPages + 1 totPages = floor(totPages) ! --------------------------------- h$=hd$ gosub msg h$=h$+m$ +~ "
" +~ "
" +~ "" +~ "" sql$ = "SELECT * FROM sqlite_master WHERE type='index'" +~ " AND lower(substr(sql,16)) <> 'sqlite_autoindex'" +~ " AND tbl_name='"+thisTable$+"' ORDER BY name" sql.raw_query wwCur, wwDB,sql$ sql.query.length rows, wwCur h1$ = "" if rows > 0 then h1$=h1$+ "" +~ "" endif h$ = h$ + h1$ + "" ! for i = 1 to numFields % column headers h$=h$+ "" next i numFlds = numFields + 1 h$=h$ + "" if sortBy$ = "rowid" then chk$ = "checked" else chk$ = "" h$=h$+ "" for i = 1 to numFields % column headers if sortBy$ = fname$[i] then chk$ = "checked" else chk$ = "" h$=h$+ "" next i h$=h$+ "" if sortBy$ = "" then sortBy$ = "rowid" i = (10 - mod(numFlds,10)) * 4 ac$ = "rowid,"+colNames$ + left$(fldFill$,i) r = 1 sql$ = "SELECT "+ac$+" FROM "+thisTable$+ mySearch$ + " ORDER BY "+sortBy$ +" "+ limit$ sql.raw_query wwCur,wwDB,sql$ sql.query.length rows, wwCur ! h1$ = "" for dispLine = 1 to rows gosub getNextSql rowid$ = vd$[1] h1$=h1$+ "" for i = 1 to numFields + 1 algn$ = "" if i = 1 then algn$ = "align=right" if i > 1 then if fsize$[i-1] = "" then fsize$[i-1] = "0" if (val(fsize$[i-1]) < 3) & (ftype$[i-1] <> "TEXT") then algn$ = "align=center" else algn$ = falgn$[i-1] endif endif h1$=h1$+"" next i h1$=h1$+ "" next dispLine ! h$=h$+ h1$ h1$ = "" h$=h$+ "
" +~ " " +~ "Browse Table:"+thisTable$ +~ " Records:"+numRecords$ +~ " Pages:"+format$("#####",totPages) + shoSearch$ + "
" +~ "" for i = 1 to rows sql.next xdone,wwCur,type$,name$,tbl_name$,root$,index$ j = is_in("(",index$) a$ = mid$(index$,j+1) a$ = replace$(a$,")","") if sortBy$ = a$ then chk$ = "checked" else chk$ = "" h1$=h1$+ ""+name$ next i hi$ = hi$ + "
Maintrowid"+fname$[i]+"
" +~ "
" +~ "" +~ "" +~ ""+left$(vd$[i],20)+"
" +~ "Lines/Page" +~ " Page" if pageNum <> 1 then h$=h$+ "" if pageNum <> totPages then h$=h$+ "" h$=h$+ "" +~ "" +~ "
" +~ "" gosub userInput if is_in("sortBy=",data$) > 0 then sortBy$ = word$(" "+data$,2,"sortBy=") i = is_in("&",sortBy$) sortBy$ = left$(sortBy$,i-1) endif if type$ = "FOR:" then % User data returned lpp$ = getData$("lpp",data$) lpp = val(lpp$) if lpp < 1 then lpp = 1 if lpp > 100 then lpp = 100 totPages = numRecords / lpp % lpp is lines per page if floor(totPages) <> totPages then totPages = totPages + 1 if is_in("srt",data$) > 0 then goto tryBrowse1 rqst$ = "nxt" if is_in("pre",data$) > 0 then rqst$ = "pre" pageNum$ = getData$("pageNum",data$) pageNum$ = strRep$(pageNum$,"+","") rqstPage = val(pageNum$) if rqstPage <> pageNum then pageNum = rqstPage else if rqst$ = "nxt" then pageNum = pageNum + 1 % Next Page if rqst$ = "pre" then pageNum = pageNum - 1 % Prev Page endif if pageNum > floor(totPages) then pageNum = 1 % wrap around page if past end if pageNum < 1 then pageNum = 1 goto tryBrowse1 endif if type$ = "DAT:" then % User data returned if rqst$ = "ext" then goto theTables % try a SQL statement if rqst$ = "add" then goto acd % Add Like data to table if rqst$ = "adn" then goto acd % Add New data to table if rqst$ = "chg" then goto acd % change data if rqst$ = "del" then goto acd % delete data from table if rqst$ = "src" then goto doSearch % search - drill down endif ! errMsg$ = "1450 Unexpected request:"+data$ goto tryBrowse1 ! ! ================================================================== ! search drill down ! ================================================================== doSearch: !if pro$ <> "Y" then % pro verson or free ! popup "Drill Down Search Only available in PRO version....",0,0,4 ! goto tryBrowse1 !endif h$=hd$ gosub msg h$=h$+m$ +~ "" +~ "
" +~ "
Search Table:"+thisTable$+" .... [ XX=Begins with *XX=Ends With *XX*=Contains ]
" +~ "" for i = 1 to numFields h$=h$ + "" next i h$=h$+ "
"+fname$[i]+"" +~ "
" +~ "" +~ "
" +~ "" gosub userInput mySearch$ = "" shoSearch$ = "" if rqst$ = "ext" then goto tryBrowse % try a SQL statement !print data$ if type$ = "FOR:" then % User data returned wa$ = " WHERE " for i = 1 to numFields s = 0 % switch to see if there is a * on the ends a$ = getData$(fname$[i],data$) if a$ <> "" then a$ = trim$(a$) shoSearch$ = shoSearch$ + fname$[i] + ":" + a$ + " " if mid$(a$,len(a$),1) = "*" then a$ = left$(a$,len(a$)-1) + "%" s = 1 endif if left$(a$,1) = "*" then a$ = "%" + mid$(a$,2) s = 1 endif if s = 0 then a$ = a$ + "%" mySearch$ = mySearch$ + wa$ + fname$[i]+" like '" + a$ + "'" wa$ = " AND " endif !print str$(i)+"|"+fname$[i]+"|"+a$+"=>"+mySearch$ next i endif if shoSearch$ <> "" then shoSearch$ = " Search: " + shoSearch$ type$ = "" a$ = "" goto tryBrowse % try a SQL statement ! ! ================================================================== ! add change or delete ! ================================================================== acd: err$ = "1460 tryBrowse: Add Change or Delete: |acd|"+thisTable$ acd$ = rqst$ hp$ = "" %Photo html stuff phCnt = 0 % photo count rowid$ = theData$ ac$ = "coalesce(" + replace$(colNames$,",",",'')||'^^'||coalesce(") + ",'')" h$=hd$ gosub msg h$=h$+ m$ +~ "" +~ "
" +~ "" +~ "" if rqst$ <> "adn" then sql$ = "SELECT "+ac$+" FROM "+thisTable$+" WHERE rowid="+rowid$ sql.raw_query wwCur,wwDB,sql$ sql.next xdone,wwCur,a$ a$ = a$ + " ^^ ^^ ^^" else a$ = " ^^ ^^ ^^" rowid$ = "" endif ! j = 1 for i = 1 to numFields if rqst$ = "adn" then j = 1 k = is_in("^^",a$,j) if k > 0 then h$=h$ +"" else ty$ = "text" if uTyp$ = "DATE" then ty$ = "date" if uTyp$ = "TIME" then ty$ = "time" ! if uTyp$ = "DECIMAL" then ty$ = "decimal" ! if uTyp$ = "DOUBLE" then ty$ = "decimal" ! if uTyp$ = "FLOAT" then ty$ = "decimal" ! if uTyp$ = "INT" then ty$ = "number" ! if uTyp$ = "INTEGER" then ty$ = "number" ! if uTyp$ = "BIGINT" then ty$ = "number" ! if uTyp$ = "MEDIUMINT" then ty$ = "number" ! if uTyp$ = "SMALLINT" then ty$ = "number" ! if uTyp$ = "TINYINT" then ty$ = "number" ! if uTyp$ = "YEAR" then ty$ = "number" h$=h$+ "" ext$ = word$(thisData$,2,".") if is_in(ext$,",bmp,gif,jpg,png,tif") > 0 then file.exists f,thisData$ if f <> 0 then hp$ = hp$ + "" phCnt = phCnt + 1 endif endif h$=h$+ "" h$=h$+ "" h$=h$+ "" endif j = k + 2 next i ! if phCnt > 0 then j = 100 / max(3,phCnt) hp$ = replace$(hp$,"!|^",format$("###",j)) p$ = "100" if phCnt < 4 then p$ = "75" if phCnt < 3 then p$ = "50" if phCnt < 2 then p$ = "25" hp$ = "
"+thisTable$+" Table "+acd$+" rowid:"+rowid$+"
FieldValueFormatSizeDcmlsPrim Key
"+fname$[i]+"" siz = val("0"+fsize$[i]) if siz > 50 then siz = 50 if siz = 0 then siz = 16 thisData$ = trim$(mid$(a$,j,k-j)) uTyp$ = upper$(ftype$[i]) if uTyp$ = "TEXT" h$=h$+ "Field: "+fname$[i]+"
Data: "+thisData$+"
"+ftype$[i]+""+fsize$[i]+""+fdcml$[i]+" if fauto$[i] = "pkn" then h$=h$+ "Yes" h$=h$+ "
" + hp$ + "
" endif h$=h$+ "" if acd$ = "del" then h$=h$+ "" if acd$ = "add" then h$=h$+ "" if acd$ = "adn" then h$=h$+ "" if acd$ = "chg" then h$=h$+ "" h$=h$+"" +~ "" +~ hp$ +~ "" gosub userInput if type$ = "DAT:" then % User data returned if rqst$ = "del" then goto delRecord % delete data from table if rqst$ = "ext" then goto tryBrowse1 % back to browse endif ! if type$ = "FOR:" then v$ = "" q$ = "'" q1$ = "" for i = 1 to slen -1 ! print screen$[i];"|";word$(screen$[i],2,"=");"|";fname$[i];"|";ftype$[i];"|"fsize$[i];"|";fauto$[i] value$ = word$(screen$[i],2,"=") value$ = replace$(value$,"'","''") % escape the quote value$ = trim$(value$) ! print i;" ";fname$[i];" ";ftype$[i];" ";fauto$[i] if fauto$[i] <> "" & value$ = "" then % with auto update get next number sql$ = "SELECT max("+fname$[i]+") +1 FROM "+thisTable$ sql.raw_query wwCur,wwDB,sql$ sql.next xdone,wwCur,value$ endif if left$(acd$,2) = "ad" then v$ = v$ + q$ + value$ q$ = "','" else v$ = v$ + q1$ + fname$[i] +"='"+ value$ +"'" q1$ = "," endif next i v$ = replace$(v$,"'n^u^ll'","null") if left$(acd$,2) = "ad" then sql$ = "INSERT INTO "+thisTable$+" ("+colNames$+") VALUES ("+v$+"')" else sql$ = "UPDATE "+thisTable$+" SET "+v$+" WHERE rowid="+rowid$ endif sql.exec wwDB,sql$ endif goto tryBrowse1 ! ! -------------- ! DELETE ! --------------- delRecord: sql$ = "DELETE FROM "+thisTable$+" WHERE rowid="+rowid$ sql.exec wwDB,sql$ infMsg$ = "Rowid "+rowid$+" Deleted" goto tryBrowse1 ! ! =================================================================== ! MT the table ! =================================================================== mtTable: err$ = "1570 fields: Empty Table: "+thisTable$ h$=hd$ h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ +~ "
" +~ "
Empty Table:"+thisTable$+"
" +~ " " +~ "" +~ "
" gosub userInput if rqst$ = "ext" then goto theTables !sql$ = "TRUNCATE TABLE "+thisTable$ sql$ = "DELETE FROM "+thisTable$ sql.exec wwDB,sql$ infMsg$ = "TABLE "+thisTable$+" has been Emptied" goto tbles ! ! =================================================================== ! Rename table ! =================================================================== renTable: err$ = "1590 fields: Rename Table: "+thisTable$ h$=hd$+"
" +~ "
" +~ "" +~ "" +~ "
Rename Table:"+thisTable$+"To
" +~ "" +~ "" +~ "
" +~ "" gosub userInput if type$ = "DAT:" then if rqst$ = "ext" then goto theTables errMsg$ = "Unexpected retrun from screen" goto tbles endif if type$ = "FOR:" then % User data returned rname$ = getData$("rname",data$) for i = 1 to numTables if rname$ = tableNames$[i] then errMsg$ = "The table name "+rname$+" already exists" goto tbles endif next i sql$ = "ALTER TABLE "+thisTable$+" RENAME TO "+rname$ sql.exec wwDB,sql$ infMsg$ = "TABLE "+thisTable$+" has been renamed to "+rname$ preThisDbFile$ = "^*" endif errMsg$ = "1620 Unexpected return from user at rename table" goto tbles ! ! =================================================================== ! drop the table ! =================================================================== dropTable: err$ = "1630 tbles: Drop Table: "+thisTable$ sql$ = "DROP TABLE "+thisTable$ sql.exec wwDB,sql$ infMsg$ = "TABLE "+thisTable$+" has been dropped" preThisDbFile$ = "_1+" goto tbles ! ! ================================================================== ! Dump Database ! ================================================================== dumpDb: err$ = "1640 tbles: Dump Database: "+thisDb$ h$=hd$ % web page header h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ +~ "" +~ "
Dump Database "+thisDb$ +~ "
" +~ "" +~ "" +~ "" +~ "" +~ "
" +~ "
" +~ "" +~ "" gosub userInput if rqst$ = "sch" then goto schema % dump schema if rqst$ = "dta" then goto dumpData % dumpData if rqst$ = "csv" then goto dumpCsv % dump CSV if rqst$ = "sql" then goto trySql % try a SQL statement if rqst$ = "ext" then goto theTables % back to tables errMsg$ = "1658 Unexpected data type:"+ type$ + data$ goto dumpDb ! ! ================================================================== ! Load Database ! ================================================================== loadDb: err$ = "1670 tbles: Load Database: "+thisDb$ h$=hd$ % web page header h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ +~ "" +~ "
Load Database "+thisDb$ +~ "
" +~ "" +~ "
" +~ "" +~ "
" +~ "" +~ "
" +~ "
" +~ "" +~ "" gosub userInput if rqst$ = "csv" then goto loadCsv % dump CSV if rqst$ = "sql" then goto trySql % try a SQL statement if rqst$ = "ext" then goto theTables % back to tables errMsg$ = "1685 Unexpected data type:"+ type$ + data$ goto loadDb ! ! ---------------------------------- ! Export Database ! ---------------------------------- exportDb: err$ = "1700 dbRequest: Invalid SQLite file" h1$= "
" +~ "
Export Database:"+thisDbFile$+"
" +~ "" +~ "" +~ "" +~ "" +~ "
" gosub msg h$=hd$+m$+h1$ gosub userInput if rqst$ = "sch" then goto schema % export the schema if rqst$ = "csv" then goto exportInfo % comma seperated variable if rqst$ = "dta" then goto exportInfo % export Data csv if rqst$ = "ext" then goto dbRequest % rename table errMsg$ = "1810 unexport Unexpected data type:"+ type$ + data$ goto exportDb ! ! ------------------------------------ ! CSV Data or Comma Seperated Variable ! ------------------------------------ exportInfo: err$ = "1720 dbRequest: Export CSV or Data "+dbFile$ expRqst$ = rqst$ if expRqst$ = "dta" then a$ = "Data" else a$ = "CSV" endif h1$= "
" +~ "" +~ "" +~ "" expdta: h1$=h1$+"
Export "+a$+"
Tables" +~ "*All Files*
" sql$ = "SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name <> 'android_metadata' ORDER BY tbl_name" sql.raw_query wwCur, wwDB,sql$ xdone = 0 do sql.next xdone,wwCur,tblName$ if !xdone then h1$=h1$+ ""+tblName$+"
" until xdone if expRqst$ = "dta" then goto expdta h1$=h1$+ "
Line terminated by" +~ "" +~ "
Fields enclosed by" +~ "" +~ "
Fields Seperated by" +~ "" +~ "
" +~ "Put Field Names on first row
" +~ "" +~ "" +~ "
" +~ "
" gosub msg h$=hd$+"
"+m$+h1$ gosub userInput ! if type$ = "DAT:" then if rqst$ = "ext" then goto dbRequest endif ! if type$ = "FOR:" then tblNames$ = "" cma$ = "" allFiles$ = "N" term$ = "" encl$ = "" sepr$ = "" frow$ = "" for i = 1 to slen screen$[i] = replace$(screen$[i],"?file=","file=") if left$(screen$[i],4) = "file" then if is_in("*allFiles*",screen$[i]) then allFiles$ = "Y" tblNames$ = tblNames$ + cma$ + "'"+mid$(screen$[i],6)+"'" cma$ = "," endif if expRqst$ <> "dta" then if left$(screen$[i],4) = "term" then term$ = mid$(screen$[i],6) if left$(term$,1) = "%" then term$ = chr$(hex(mid$(term$,2))) endif if left$(screen$[i],4) = "encl" then encl$ = mid$(screen$[i],6) if left$(encl$,1) = "%" then encl$ = chr$(hex(mid$(encl$,2))) endif if left$(screen$[i],4) = "sepr" then sepr$ = mid$(screen$[i],6) if left$(sepr$,1) = "%" then sepr$ = chr$(hex(mid$(sepr$,2))) endif if left$(screen$[i],4) = "frow" then frow$ = mid$(screen$[i],6) if left$(frow$,1) = "%" then frow$ = chr$(hex(mid$(frow$,2))) endif endif next i endif ! sql$ = "SELECT * FROM sqlite_master WHERE tbl_name IN ("+tblNames$+") AND type='table' ORDER BY tbl_name" if allFiles$ = "Y" then sql$ = "SELECT * FROM sqlite_master WHERE tbl_name <> 'android_metadata' AND type = 'table' ORDER BY tbl_name" sql.raw_query wwCur, wwDB,sql$ sql.query.length numTables, wwCur savFile$ = ".csv" if expRqst$ = "dta" then savFile$ = "_data.sql" for nt = 1 to numTables sql.next xdone,wwCur,type$,name$,thisTable$,root$,rawTableSql$ theFile$ = "../data/"+dbFile$+"_"+thisTable$ file.exists f,theFile$+savFile$+"_save" if f <> 0 then file.delete f,theFile$+savFile$+"_save" file.exists f,theFile$+savFile$ if f <> 0 then file.rename theFile$+savFile$,theFile$+savFile$+"_save" text.open w,f,theFile$ + savFile$ infMsg$ = "Working on Table: "+thisTable$+" File Out:"+thisTable$+savFile$ gosub shoProgress gosub tableInfo if expRqst$ = "dta" then gosub dumpData goto nxtNt endif if frow$ = "Y" then x$ = "" sep$ = sepr$ for i = 1 to numFields if i = numFields then sep$ = term$ x$ = x$ + encl$+fname$[i]+encl$+sep$ next i text.writeln f,x$ endif ac$ = "coalesce(" + replace$(colNames$,",",",'')||'^^'||coalesce(") + ",'')" sql$ = "SELECT "+ac$+" FROM "+thisTable$ sql.raw_query wwyCur,wwDB,sql$ sql.query.length numRecs, wwyCur cnt = 0 for nr = 1 to numRecs sql.next ydone,wwyCur,a$ x$ = encl$ + replace$(a$,"^^",encl$+sepr$+encl$) + encl$+term$ text.writeln f,x$ cnt = cnt + 1 ! if mod(cnt,300) = 0 then popup "Still working on table:"+thisTable$+" Records:"+format$("######",cnt),100,100,0 if mod(cnt,300) = 0 then infMsg$ = "Working.. Table:"+thisTable$+" Records:"+format$("######",cnt) gosub shoProgress endif next nr nxtNt: text.close f next nt infMsg$ = "output to "+dbFile$+"_{TableName}"+savFile$ goto dbRequest ! ------------------------------------- ! dump schema ! ------------------------------------- schema: thisDbFile$ = thisDb$ sqlDbFile$ = "../data/"+thisDb$ err$ = "1810 dbRequest: Dump Schema: "+thisDbFile$ file.exists f,sqlDbFile$+".sql_save" if f <> 0 then file.delete f,sqlDbFile$+".sql_save" file.exists f,sqlDbFile$+".sql" if f <> 0 then file.rename sqlDbFile$+".sql",sqlDbFile$+".sql_save" text.open w,f,sqlDbFile$+".sql" infMsg$ = "Working ... Please wait" gosub shoProgress sql$ = "SELECT * FROM sqlite_master WHERE tbl_name<>'android_metadata' ORDER BY tbl_name,type desc,name" sql.raw_query wwCur, wwDB,sql$ xdone = 0 do sql.next xdone,wwCur,type$,name$,tbl_name$,root$,rawTableSql$ if !xdone then rawTableSql$ = rawTableSql$ + "" if type$ = "table" then text.writeln f,"# ======================================" text.writeln f,"# Table:"+tbl_name$ text.writeln f,"# ======================================" gosub tableInfo fill$ = "" for i = 1 to maxFldSiz fill$ = fill$ + " " next i infMsg$ = "Working.. Table:"+tbl_name$ gosub shoProgress text.writeln f,"CREATE TABLE "+tbl_name$+" (" for i = 1 to numFields siz$="" if fsize$[i]<>"" & fdcml$[i]<>"" then siz$="("+fsize$[i]+","+fdcml$[i]+")" if fsize$[i]<>"" & fdcml$[i]= "" then siz$="("+fsize$[i]+")" if i <> numFields then cma$= "," else cma$=");" pk$ = "" if fauto$[i] = "pkn" then pk$ = " PRIMARY KEY" if fnull$[i] <> "null" then x$ = "" else x$ = chr$(9)+"NULL" text.writeln f," "+left$(fname$[i]+fill$,maxFldSiz)+" "+upper$(ftype$[i])+siz$+x$+pk$+cma$ next i end if if type$ = "index" then text.writeln f, " "+rawTableSql$+";" endif until xdone text.close f infMsg$ = "Schema output is "+sqlDbFile$+".sql" goto dbRequest ! ! ------------------------------------- ! dump data ! ------------------------------------- dumpData: text.writeln f,"" text.writeln f,"# =========================================================" text.writeln f,"# Table:"+thisTable$+" Number of records:"+format$("######",numRec) text.writeln f,"# =========================================================" ac$ = "coalesce(" + replace$(colNames$,",",",'')||'^^'||coalesce(") + ",'')" sql$ = "SELECT "+ac$+" FROM "+thisTable$ sql.raw_query wwyCur, wwDB,sql$ sql.query.length numRecs, wwyCur totRecs = totRecs + numRecs for dn = 1 to numRecs sql.next xdone,wwyCur,a$ a$ = replace$(a$,"'","''") % escape the quote a$ = replace$(a$,"^^","','") text.writeln f, "INSERT INTO "+thisTable$+" ("+colNames$+") VALUES ('"+a$+"');" if mod(dn,300) = 0 then infMsg$ = "Working.. Table:"+thisTable$+" Records:"+format$("######",dn) gosub shoProgress endif next dn RETURN ! ! ------------------------------------- ! User SQL commands ! ------------------------------------- query: err$ = "1980 query1: Invalid Query" gosub tableNames % get table names from DB procId$ = "" qry$ = "" query1: gosub msg h$=hd$+m$+"
" +~ "
SQL Command for:"+thisDb$+"
" +~ "
" +~ "" +~ "
Records per Page" +~ "" +~ "" +~ "" +~ "
" ! ------------------------------------ ! Display procedures ! ------------------------------------ h1$ = "" sql$ = "SELECT procId,dbFile,descr,notes,qry " +~ "FROM proc " +~ "WHERE proc.dbFile = '"+dbFile$+"' ORDER BY procId " sql.raw_query wwCur, knoDB,sql$ sql.query.length rows,wwCur if rows > 0 then h1$ = "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" for dispLine = 1 to rows sql.next xdone,wwCur,procId$,dbFile$,descr$,notes$,qry$ h1$=h1$+ "" +~ "" +~ "" +~ "" +~ "" next dispLine h1$=h1$+"
ActionProc IDDescrNotes
" +~ "" +~ ""+procId$+""+descr$+""+left$(notes$,50)+"
" endif h$ = h$ + h1$ + "" gosub userInput err$ = "1970 query1: Invalid Query" if rqst$ = "ext" then goto dbRequest if rqst$ = "del" then i = is_in("|",data$) procId$ = mid$(data$,i+1) sql$ = "DELETE FROM proc WHERE dbFile='"+dbFile$+"' AND procId='"+procId$+"'" sql.exec knoDB,sql$ infMsg$ = "Procedure "+procId$+" Deleted" popup infMsg$,0,0,0 goto query1 endif if rqst$ = "lod" then array.delete screen$[] dim screen$[2] if numRows < 1 then numRows = 10 screen$[2] = str$(numRows) i = is_in("|",data$) procId$ = mid$(data$,i+1) sql$ = "SELECT descr,notes,qry FROM proc WHERE dbFile='"+dbFile$+"' AND procId='"+procId$+"'" sql.raw_query cur,knoDB,sql$ sql.query.length rows,cur if rows > 0 then sql.next xdone,cur,descr$,notes$,qry$ goto qryParse else errMsg$ = "Procedure "+procId$+" not on file" goto query endif endif i = is_in("=",screen$[1]) qry$ = mid$(screen$[1],i+1) qry$ = replace$(qry$,"\n","\n ") % with space qry$ = replace$(qry$,"\t"," ") % with space qry$ = strRep$(qry$," "," ") %single space qry$ = strRep$(qry$," ,",",") % no spaces around commas qry$ = strRep$(qry$,", ",",") qryParse: qry$ = trim$(qry$) uqry$ = upper$(qry$) + " " if left$(uqry$,6) <> "SELECT" then gn$ = "" goto queryCmd % not a SELECT else gn$ = "" endif fm = is_in(" FROM ",uqry$) if fm = 0 then errMsg$ = "SELECT statement needs 'FROM'" goto query1 endif theTables$ = trim$(mid$(qry$,fm+5)) theTables$ = word$(theTables$,1," ") i = is_in("=",screen$[2]) numRows = val(mid$(screen$[2],i+1)) if numRows < 1 then numRows = 1 if numRows > 100 then numRows = 100 limit$ = " LIMIT "+mid$(screen$[2],i+1) begLimit = 0 i = is_in("LIMIT",uqry$) % get rid of LIMIT if i > 0 then qry$ = left$(qry$,i-1) endif selFlds$ = mid$(left$(qry$,fm-1),8) !selFlds$ = strRep$(selFlds$," "," ") if selFlds$ = "*" then ccma$ = "" tz$ = theTables$ + "," for i = 1 to len(qry$) % any joined tables j = is_in(" JOIN ",uqry$,i+1) if j = 0 then F_N.break i = j + 2 t$ = mid$(qry$,j+6) + " " j = is_in(" ",t$) if j > 0 then t$ = left$(t$,j-1) if is_in(upper$(t$),upper$(tz$)) = 0 then tz$ = tz$ + trim$(t$) + "," endif next i selFlds$ = "" while tz$ <> "" j = is_in(",",tz$) if j > 0 then thisTable$ = left$(tz$,j-1) thisTable$ = replace$(thisTable$,"\n","") tz$ = mid$(tz$,j+1) if thisTable$ <> "" then sql$ = "SELECT sql FROM sqlite_master WHERE tbl_name = '"+thisTable$+"' COLLATE NOCASE AND type = 'table';" sql.raw_query wwCur, wwDB,sql$ sql.query.length rows, wwCur if rows = 0 then errMsg$ = "Invalid table:"+thisTable$ goto query1 endif sql.next xdone,wwCur,rawTableSql$ gosub tableInfo % table information err$ = "1981 query1: Invalid Query" colNames$ = thisTable$+"." + replace$(colNames$,",",","+thisTable$+".") selFlds$ = selFlds$ + ccma$ + colNames$ ccma$ = "," endif endif REPEAT endif err$ = "2070 query1: Invalid Query" numFlds = 0 if selFlds$ <> "" then numFlds = 1 !selFlds$ = strRep$(selFlds$," ","") selFlds$ = strRep$(selFlds$,"\n"," ") for i = 0 to len(selFlds$) i = is_in(",",selFlds$,i+1) if i = 0 then F_N.break numFlds = numFlds + 1 next i if numFlds < 1 then errMsg$ = "No fields selected" goto query1 end if cma$ = "" hdrFlds$ = "" for i = 1 to numFlds a$ = word$(selFlds$,i,",") a1$ = word$(lower$(a$),2," as ") if a1$ <> "" then a$ = a1$ hdrFlds$ = hdrFlds$ + cma$ + a$ cma$ = "," next i hdrFlds$ = strRep$(hdrFlds$," ","") hdrFlds$ = strRep$(hdrFlds$,".","
") hdr$ = "Num"+replace$(hdrFlds$,",","")+"" i = (10 - mod(numFlds,10)) * 4 selFlds$ = selFlds$ + left$(fldFill$,i)+",null,null,null,null,null,null,null,null,null,null" r = 1 sql$ = "SELECT "+selFlds$+mid$(qry$,fm) sql.raw_query wwCur,wwDB,sql$ sql.query.length numRecs, wwCur query2: sql$ = "SELECT "+selFlds$+mid$(qry$,fm)+limit$ sql.raw_query wwCur,wwDB,sql$ sql.query.length rows, wwCur h2$ = "" for r = r to rows + (r-1) gosub getNextSql h2$=h2$+ ""+format$("####",r)+"" for j = 1 to numFlds h2$=h2$+ "" + vd$[j] + "" next j h2$=h2$+"" next r ! h$ = hd$ + "
" +~ "
" +~ "" +~ "
Records:"+trim$(format$("######",numRecs)) +~ "" +~ "" +~ "" +~ "" +~ gn$ +~ "" +~ "
" +~ ""+ hdr$+ h2$ +~ "
" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "
"+qry$ +~ "
" +~ "" +~ "" gosub userInput err$ = "2100 query1: Invalid Query" if rqst$ = "ext" then goto query1 if rqst$ = "csv" then goto queryCSV if rqst$ = "gen" then goto queryGEN ! ----------------------------- ! change starting record number ! ----------------------------- if type$ = "FOR:" then !print "data:";data$ sn = val(getData$("sn",data$)) sn = sn - mod(sn,numRows) + 1 if sn < 0 then sn = 1 begLimit = sn r = begLimit limit$ = " LIMIT "+format$("####%",begLimit)+","+format$("###",numRows) goto query2 endif ! ---------------------------- ! Save Procedure ! ---------------------------- if rqst$ = "sav" then preProcId$ = procId$ savProc: h$=hd$ % web page header h$=h$+ "
" gosub msg % Display messages if any h$=h$+ m$ +~ "
" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "
Procedure for DB:"+dbFile$+"
Proc ID" +~ "" +~ "
Descr" +~ "" +~ "
Notes" +~ "" +~ "
SQL"+ qry$ +~ ! "" +~ "
" +~ "" +~ "" +~ "
" +~ "
" gosub userInput if rqst$ = "ext" then goto query1 procId$ = getData$("procId",data$) procId$ = strRep$(procId$," ","") if procId$ = "" then errMsg$ = "Please enter a Procedure ID" goto savProc endif ! --------------------------- ! delets matched Procedure ! ---------------------------- ! sql$ = "DELETE FROM proc WHERE dbFile='"+dbFile$+"' AND procId='"+procId$+"'" ! sql.exec knoDB,sql$ descr$ = getData$("descr",data$) descr$ = replace$(descr$,"'","''") notes$ = getData$("notes",data$) notes$ = replace$(notes$,"'","''") ! qry$ = getData$("query",data$) qry$ = replace$(qry$,"'","''") sql$ = "INSERT OR REPLACE INTO proc values('"+procId$+"','"+dbFile$+"','"+descr$+"','"+notes$+"','"+qry$+"')" sql.exec knoDB,sql$ goto query1 endif if rqst$ = "nxt" then begLimit = begLimit + numRows if begLimit > numRecs then begLimit = 0 r = 1 endif limit$ = " LIMIT "+format$("####%",begLimit)+","+format$("###",numRows) goto query2 end if ! previous begLimit = begLimit - numRows begLimit = begLimit - mod(begLimit,numRows) + 1 if begLimit < 1 then begLimit = 1 r = begLimit limit$ = " LIMIT "+format$("####%",begLimit)+","+format$("###",numRows) goto query2 ' --------------------------------- ' query command ' --------------------------------- queryCmd: err$ = "2200 query1: SQL Command: "+qry$ Sql.exec wwDB, qry$ infMsg$ = "Command: "+qry$+" complete" goto query1 ! --------------------------------- ! sql to CSV ! --------------------------------- queryCSV: q$ = chr$(34) text.open w,f,procId$+"CSV.txt" popup "OUTPUT "+procId$+"CSV.txt",0,0,4 a$ = replace$(hdrFlds$,"
"," ") a$ = q$+replace$(a$,",",q$+","+q$)+q$ text.writeln f,a$ sql$ = "SELECT "+selFlds$+mid$(qry$,fm) sql.raw_query wwCur,wwDB,sql$ sql.query.length rows, wwCur for i = 1 to rows gosub getNextSql cma$ = "" a$ = "" for j = 1 to numFlds a$=a$+ cma$+q$ + vd$[j] + q$ cma$ = "," next j text.writeln f,a$ next i text.close f goto query1 ! --------------------------------- ! sql to GEN rfo-basic ! --------------------------------- queryGEN: if pro$ = "Y" then goto isProQry % pro or free version gosub chkGen if qry > 5 then goto query1 isProQry: popup "Use RFO-Basic OUTPUT TO: data/rfo_basicGEN.bas",0,0,4 file.exists f,"rfo_basicGen.bas" if f <> 0 then file.rename "rfo_basicGen.bas","rfo_basicGen.bas_save" gosub dbPath text.open w,f,"rfo_basicGEN.bas" text.writeln f,"! -----------------------" text.writeln f,"! rfo_BasicGEN.bas" text.writeln f,"! -----------------------" text.writeln f,"dbFile$ = \""+thisDbFile$+"\"" text.writeln f,"file.exists f,dbFile$" text.writeln f,"if f = 0 then" text.writeln f," print \"DB file \"+dbFile$+\" does not exist\"" text.writeln f,"endif" text.writeln f,"! -----------------------" text.writeln f,"html.open" text.writeln f,"html.clear.cache" text.writeln f,"numFlds\t= " + trim$(format$("###",numFlds)) text.writeln f,"dim fldData$["+trim$(format$("###",numFlds))+"]" text.writeln f,"lpp\t\t= 20 % lines per page" text.writeln f,"begLimit\t= 0" text.writeln f,"pgeNum\t= 1" hdrFlds$ = strRep$(hdrFlds$," ","") hdrFlds$ = strRep$(hdrFlds$,".","
") hdrFlds$ = replace$(hdrFlds$,",","")+"" text.writeln f,"hdrFlds$\t= \""+hdrFlds$+"\"" qry1$ = replace$(qry$,"\n"," ") text.writeln f,"sql$\t\t= \""+qry1$+"\"" text.writeln f,"sql.open wwDB,dbFile$" text.writeln f,"sql.raw_query wwCur,wwDB,sql$" text.writeln f,"sql.query.length numRecs, wwCur" text.writeln f,"lstPge = ceil(numRecs / lpp)" text.writeln f,"loop:" text.writeln f,"h$ = \"
\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"
\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"
\" +~" text.writeln f,"\"Page \" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"Records \"+format$(\"#####\",numRecs) +~" text.writeln f,"\"
\" +~" text.writeln f,"\"
\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\"" text.writeln f,"" text.writeln f,"h1$=\"\"" text.writeln f,"sql.raw_query wwCur,wwDB,sql$ + \" LIMIT \" + format$(\"###%\",begLimit) + \",\" + format$(\"####\",begLimit + lpp)" text.writeln f,"sql.query.length rows, wwCur" text.writeln f,"for j = 1 to rows" s$ = "" for i = 1 to numFlds s$=s$+",fldData$["+trim$(format$("###",i))+"]" next i text.writeln f,"sql.next xdone,wwCur"+s$ text.writeln f," h1$=h1$+\"\"" text.writeln f," for i = 1 to numFlds" text.writeln f," h1$=h1$+\"\"" text.writeln f," next i" text.writeln f,"h1$=h1$+\"\"" text.writeln f,"next j" text.writeln f,"h$=h$+h1$+\"
\"+hdrFlds$ +~" text.writeln f,"\"
\"+fldData$[i]+\"
\"" text.writeln f,"gosub userInput" text.writeln f,"if left$(data$,4) = \"BAK:\" then end" text.writeln f,"if is_in(\"&ext\",data$) > 0 then end" text.writeln f,"data$ = replace$(data$,\"+\",\"\")" text.writeln f,"i = is_in(\"?pge=\",data$) + 5" text.writeln f,"j = is_in(\"&\",data$,i)" text.writeln f,"pge = val(mid$(data$,i,j-i))" text.writeln f,"if is_in(\"&nxt\",data$) then" text.writeln f," if pge = pgeNum then" text.writeln f," pgeNum = pgeNum + 1" text.writeln f," else" text.writeln f," pgeNum = pge" text.writeln f," endif" text.writeln f,"endif" text.writeln f,"if is_in(\"&pre\",data$) then" text.writeln f," if pge = pgeNum then" text.writeln f," pgeNum = pgeNum - 1" text.writeln f," else" text.writeln f," pgeNum = pge" text.writeln f," endif" text.writeln f,"endif" text.writeln f,"if pgeNum < 1 then pgeNum = 1" text.writeln f,"if pgeNum > lstPge then pgeNum = lstPge" text.writeln f,"begLimit = (pgeNum * lpp) - lpp" text.writeln f,"goto loop" text.writeln f,"! ------------------------" text.writeln f,"userInput:" text.writeln f,"html.load.string h$" text.writeln f,"data$ = \"\"" text.writeln f,"do text.writeln f," html.get.datalink data$ text.writeln f," pause 250 % pause 1/4 sec text.writeln f,"until data$ <> \"\"" text.writeln f,"RETURN" text.writeln f,"end" text.close f goto query1 ! ------------------------------------ ! check gen count for non PRO version ! ------------------------------------ chkGen: qry = 0 sql.open knoDb,"sqLiteKnowTbls.db" sql$ = "SELECT qry FROM proc WHERE procid = 'version'" sql.raw_query cur,knoDB,sql$ sql.query.length rows, cur if rows < 1 then sql$ = "CREATE TABLE if not exists proc(procId CHAR(10),dbFile VARCHAR(33),descr VARCHAR(33),notes TEXT,qry TEXT)" sql.exec knoDB,sql$ sql$ = "INSERT INTO proc (procid,qry) VALUES ('version',0)" sql.exec knoDB,sql$ else sql.next xdone,cur,qry$ qry = val(qry$) endif qry = qry + 1 if qry < 6 then sql$ = "UPDATE proc SET qry = "+str$(qry)+" WHERE procid = 'version'" sql.exec knoDB,sql$ end if popup "You get 5 gens without ionSQLitePro. Count is "+str$(qry)+" tries",0,0,4 pause 4000 RETURN ! ----------------------------------- ! Find fields in tables ! ----------------------------------- find: h$=hd$ gosub msg h$=h$+m$ +~ "
" +~ "
" +~ "" +~ "" +~ "
Find Field Name
" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "
" +~ "" +~ h1$ gosub userInput if rqst$ = "ext" then goto dbRequest fnd$ = getData$("fnd",data$) lf = len(fnd$) actn$ = getData$("actn",data$) if is_in("&eql",data$) > 0 then wh$ = "eq" wh1$ = "Equal to" endif if is_in("&beg",data$) > 0 then wh$ = "bg" wh1$ = "Begining With" endif if is_in("&end",data$) > 0 then wh$ = "en" wh1$ = "Ending With" endif if is_in("&con",data$) > 0 then wh$ = "lk" wh1$ = "That Contain" endif h1$="" +~ "" +~ "" +~ "" +~ "" ufnd$ = upper$(fnd$) sql.open wwDB,thisDbFile$ sql$ = "select tbl_name,sql " +~ "FROM sqlite_master " +~ "WHERE type = 'table' AND tbl_name <> 'android_metadata' " +~ " AND sql like '%"+fnd$+"%' ORDER BY tbl_name" sql.raw_query wwCur,wwDB,sql$ sql.query.length numTables, wwCur for n = 1 to numTables sql.next xdone,wwCur,thisTable$,rawTableSql$ infMsg$ = "Checking tabe:"+thisTable$ gosub shoProgress gosub tableInfo % table information for i = 1 to numFields ufname$ = upper$(fname$[i]) if wh$ = "eq" & ufname$ = ufnd$ then goto findFound if wh$ = "bg" & left$(ufname$,lf) = ufnd$ then goto findFound j = (len(ufname$) - lf) + 1 if j > 0 & wh$ = "en" then if mid$(ufname$,j,lf) = ufnd$ then goto findFound endif if wh$ = "lk" then x$ = replace$(ufname$,ufnd$,"") if x$ <> ufname$ then goto findFound endif goto findNxt findFound: if mod(i,2) then h1$=h1$+"" +~ "" +~ "" +~ "" +~ "" +~ "" findNxt: next i next n h1$=h1$+"
Tables with fields "+wh1$+" "+fnd$+"
TableFieldTypeLengthDecimals
" else h1$=h1$+"
" end if h1$=h1$+thisTable$+""+fname$[i]+""+ftype$[i]+""+fsize$[i]+""+fdcml$[i]+""+fnull$[i]+"
" goto find ! --------------------------------- ! get table names from DB ! --------------------------------- tableNames: sql.open wwDB,thisDbFile$ sql$ = "select tbl_name from sqlite_master WHERE type = 'table' " +~ "AND tbl_name <> 'android_metadata' ORDER BY tbl_name" % get the list of tables sql.raw_query wwCur,wwDB,sql$ sql.query.length numTables, wwCur array.delete tableNames$[] dim tableNames$[numTables] for i = 1 to numTables sql.next xdone,wwCur,tbl_name$ tableNames$[i] = tbl_name$ next i RETURN ! ! =================================================================== ! table format clean up. ! supply rawTableSql$ from the sql field of the sqlite_master ! some have tabs, some have multiple spaces, ! some have leading blanks. ! make decimals with (.) not (,) ! so.. try to make them as standard as possible ! =================================================================== tableInfo: err$ = "2160 tbles: Table Info: "+thisTable$ b$ = upper$(rawTableSql$) a$ = "" for i = 1 to len(rawTableSql$) d$ = mid$(b$,i,1) if is_in(d$,"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_-.,)(") > 0 then a$ = a$ + mid$(rawTableSql$,i,1) else a$ = a$ + " " endif next i a$ = strRep$(a$," "," ") a$ = strRep$(a$,", ",",") a$ = strRep$(a$," ,",",") a$ = strRep$(a$,"( ","(") a$ = strRep$(a$," )",")") cleanTableSql$ = a$ i = is_in("(",a$) a$ = mid$(a$,i+1) ln = len(a$) a$ = left$(a$,ln-1) + "," ! ! ------------------------------------ ! get field data from table ! ------------------------------------ i = is_in(",",a$) % convert the (,) in decimal(x,x) to (x.x) while i > 1 d1$ = mid$(a$,i-1,1) d2$ = mid$(a$,i+1,1) if (d1$ >= "0" & d1$ <= "9") | (d2$ >= "0" & d2$ <= "9") then a$ = left$(a$,i -1) + ":" + mid$(a$,i+1) endif i = is_in(",",a$,i+1) repeat array.delete fldData$[] split fldData$[],a$,"," array.length numFields ,fldData$[] !array.delete fd$[] array.delete fname$[] array.delete ftype$[] array.delete fsize$[] array.delete fdcml$[] array.delete fnull$[] array.delete fauto$[] array.delete falgn$[] !dim fd$[numFields] dim fname$[numFields] dim ftype$[numFields] dim fsize$[numFields] dim fdcml$[numFields] dim fnull$[numFields] dim fauto$[numFields] dim falgn$[numFields] !numFields = numFields - 1 colNames$ = "" maxFldSiz = 0 cma$ = "" ! ! seperate field component data - name, type, size decimals, null for i = 1 to numFields f$ = fldData$[i] + " " fname$[i] = word$(f$,1," ") colNames$ = colNames$ + cma$ + fname$[i] cma$ = "," if maxFldSiz < len(fname$[i]) then maxFldSiz = len(fname$[i]) f$ = upper$(f$) ftype$[i] = word$(f$,2," ") ftype$[i] = word$(ftype$[i],1,"\\(") if is_in(ftype$[i],dataAlignRignt$) > 0 then falgn$[i] = "align=right" f$ = replace$(f$,"NOT NULL","") ii = is_in("NULL",x$) if ii > 0 then fnull$[i] = "NULL" f$ = replace$(f$,"NULL","") endif f$ = strRep$(f$," "," ") + " (:)" ii = is_in("PRIMARY KEY",f$) if ii > 0 then fauto$[i] = "pk" ii = is_in("PRIMARY KEY AUTO",f$) if ii > 0 then fauto$[i] = fauto$[i] + "a" else fauto$[i] = fauto$[i] + "n" endif f$ = replace$(f$,"PRIMARY KEY","") f$ = replace$(f$,"AUTOINCREMENT","") f$ = replace$(f$,"AUTO","") endif ii = is_in("(",f$) x$ = mid$(f$,ii+1) ii = is_in(")",x$) if ii > 0 then x$ = left$(x$,ii-1) fsize$[i] = word$(x$,1,":") fdcml$[i] = word$(x$,2,":") !print i;" fn:";fname$[i];" ft:";ftype$[i];" fs:";fsize$[i];" fd:";fdcml$[i];" nl:";fnull$[i];" f:";f$ next i RETURN ! ! ******************************* routines ************************************ ! ! ------------------------------------ ! error handler ! set err$ ! 1-4 number ! 6-n return address followed with : ! a message ! example ! err$ = "1234 progLabel: any message you like ! ------------------------------------ onError: errMsg$ = err$ + "
\nERROR: "+ getError$() if is_in("addIdx",err$) then goto addIdx if is_in("addTbl",err$) then goto addTbl if is_in("dbRequest",err$) then goto dbRequest if is_in("fieldDtl",err$) then goto fieldDtl if is_in("fields",err$) then goto fields if is_in("index",err$) then goto index if is_in("loadCsv",err$) then goto loadCsv if is_in("tbles",err$) then goto tbles if is_in("tryBrowse",err$) then goto tryBrowse if is_in("query1",err$) then goto query1 infMsg$ = "2225 Unknown Error"+err$ goto dbRequest ! ! ------------------------------------ ! messages ! ------------------------------------ msg: m$= "" m$=m$+ "" m$=m$+ "" m$=m$+ "" m$=m$+ "
"+errMsg$+"
"+wrnMsg$+"
"+infMsg$+"
" errMsg$ = "" wrnMsg$ = "" infMsg$ = "" RETURN shoMsg: if errMsg$ <> "" then html.load.url "javascript:document.getElementById('errMsg').innerHTML= '"+errMsg$+"';" if wrnMsg$ <> "" then html.load.url "javascript:document.getElementById('wrnMsg').innerHTML= '"+wrnMsg$+"';" if infMsg$ <> "" then html.load.url "javascript:document.getElementById('infMsg').innerHTML= '"+infMsg$+"';" errMsg$ = "" wrnMsg$ = "" infMsg$ = "" RETURN " ---------------------------------- " Show Progress messages " ---------------------------------- shoProgress: gosub msg html.clear.cache html.load.string "
"+m$ errMsg$ = "" wrnMsg$ = "" infMsg$ = "" RETURN ! ! ------------------------------------ ! User screen input ! ------------------------------------ userInput: !html.open html.load.string h$ userInput1: data$ = "" rqst$ = "" time yyyy$,mm$,dd$,hr$,mi$,ss$ startMi = val(mi$) do html.get.datalink data$ time yyyy$,mm$,dd$,hr$,mi$,ss$ curMi = val(mi$) if abs(startMi - curMi) > 300 % 300 minutes and then this dies html.close print "Timeout.. Program ended" end endif pause 250 % pause 1/4 sec until data$ <> "" type$ = left$(data$, 4) % Type of data link data$ = mid$(data$,5) % Trim first 4 characters if type$ = "BAK:" then % back key hit.. go back if possible ! print "BACK key: " + data$ ! if data$ = "1" then html.go.back else end data$ = "ext|backkey" type$ = "DAT:" % force back key to dat: ext rqst$ = "ext" goto userInputExit endif if type$ = "ERR:" then % An error occured errMsg$ = "ERROR " + data$ goto userInputExit endif if type$ = "LNK:" then data$ = word$(data$,2,"FORM") type$ = "FOR:" endif if type$ = "DAT:" then % User data returned i = is_in("|",data$) rqst$ = left$(data$,i-1) theData$ = mid$(data$,i+1) if rqst$ = "exit" then html.close print "User ended program." end endif goto userInputExit endif ! ! --------------------------------------------------- ! Form data returned. ! Note: Form data returning ! always exits the html. ! --------------------------------------------------- if type$ = "FOR:" then data$ = replace$(data$, "+", " ") % uncook.. data$ = replace$(data$,"%0D%0A","\n") for i = 1 to len(data$) i = is_in("%",data$,i) if i = 0 then F_N.break data$ = left$(data$,i-1)+ chr$(hex(mid$(data$,i+1,2))) + mid$(data$,i+3) next i array.delete screen$[] split screen$[] ,data$, "&" array.length slen, screen$[] if is_in("exit",data$) > 1 then % user request exit html.close print " Have a great day.." end endif goto userInputExit endif errMsg$ = "2140 Unexpected data type:"+ type$ + data$ userInputExit: RETURN ! ! --------------------------------------------------- ! Generate RFO-BASIC table maintenance code ! ---------------------------------------------------- rfoGen: if pro$ = "Y" then goto isPro % pro or free version gosub chkGen if qry > 5 then goto tbles isPro: outFile$ = "rfo_"+thisTable$+".bas" popup "use RFO-Basic OUTPUT TO: data/"+outFile$,0,0,4 file.exists f,outFile$ if f <> 0 then file.rename outFile$,outFile$+"_save" text.open w,f,outFile$ text.writeln f,"dbFields$ = \""+colNames$+"\"" colNames$ = replace$(colNames$,",","$,")+"$" gosub dbPath text.writeln f,"! -----------------------" text.writeln f,"! rfo_"+thisTable$+".bas" text.writeln f,"! -----------------------" text.writeln f,"dbFile$ = \""+thisDbFile$+"\"" text.writeln f,"file.exists f,dbFile$" text.writeln f,"if f = 0 then" text.writeln f," print \"DB file \"+dbFile$+\" does not exist\"" text.writeln f,"endif" text.writeln f,"" text.writeln f,"! -----------------------" !text.writeln f,"numFlds\t= " + format$("###",numFields) !text.writeln f,"dim fldData$["+format$("###",numFlds)+"]" text.writeln f,"lpp\t\t= 20 % lines per page" text.writeln f,"begLimit\t= 0" text.writeln f,"pgeNum\t= 1" text.writeln f,"tableId$ = \""+thisTable$+"\"" text.writeln f,"" text.writeln f,"sql.open theDb,dbFile$ % open SQLite DB" text.writeln f,"gosub knoFun" text.writeln f,"sortBy$ = \"rowid\"" text.writeln f,"! =================================================" text.writeln f,"! List" text.writeln f,"! =================================================" text.writeln f,"shoList:" text.writeln f,"sql$ = \"SELECT * FROM "+thisTable$+"\" + wh$" text.writeln f,"sql.raw_query cur,theDb,sql$" text.writeln f,"sql.query.length numRecs, cur % number of records in the table" text.writeln f,"" text.writeln f,"totPages = ceil(numRecs / lpp)" text.writeln f,"h$=hd$" text.writeln f,"gosub msg" text.writeln f,"h$=h$+m$ +~" text.writeln f,"\"
\" +~" text.writeln f,"\"
\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"
\"+srcFor$+\"Records:\"+format$(\"####%\",numRecs) +~" text.writeln f,"\"Pages:\"+format$(\"####%\",totPages) +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"Lines per Page\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"Sorted by:\"+sortBy$ +~" text.writeln f,"\"
\" +~" text.writeln f,"\"
\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" for i = 1 to numFields text.writeln f,"\"\" +~" next i text.writeln f,"\"\"" text.writeln f,"" text.writeln f,"sql$ = \"SELECT "+thisTable$+".rowid,* FROM "+thisTable$+" \"+wh$+\" ORDER BY \" +sortBy$ + \" LIMIT \"+ format$(\"###%\",begLimit) + \",\" + format$(\"###%\",lpp)" text.writeln f,"sql.raw_query cur,theDb,sql$" text.writeln f,"sql.query.length rows, cur" text.writeln f,"h1$ = \"\"" text.writeln f,"for i = 1 to rows" text.writeln f," sql.next xdone,cur,rowid$,"+colNames$ text.writeln f," if mod(i,2) then" text.writeln f," h1$=h1$+\"\"" text.writeln f," else" text.writeln f," h1$=h1$+\"\"" text.writeln f," end if" text.writeln f,"h1$=h1$+\"\" +~" text.writeln f,"\"\" +~" for i = 1 to numFields if is_in("INT",upper$(ftype$[i])) > 0 then text.writeln f,"\"\" +~" else text.writeln f,"\"\" +~" endif next i text.writeln f,"" text.writeln f,"\"\"" text.writeln f,"next i" text.writeln f,"h$=h$ + h1$+\"
Sort->
Maint
\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\"+rowid$+\"\"+"+fname$[i]+"$+\"\"+left$("+fname$[i]+"$,12)+\"
\"" text.writeln f,"" text.writeln f,"gosub userInput" text.writeln f,"" text.writeln f,"if type$ = \"BAK:\" then end" text.writeln f,"if rqst$ = \"ext\" then end" text.writeln f,"if type$ = \"DAT:\" then" text.writeln f,"if rqst$ = \"srt\" then" text.writeln f," i = is_in(\"|\",data$)" text.writeln f," sortBy$ = mid$(data$,i+1)" text.writeln f,"endif" text.writeln f,"if rqst$ = \"mnt\" then" text.writeln f," acd$ = word$(data$,2,\"\\\\|\")" text.writeln f," acdName$ = word$(data$,3,\"\\\\|\")" text.writeln f," acdRowid$ = word$(data$,4,\"\\\\|\")" text.writeln f," goto maint" text.writeln f," endif" text.writeln f,"endif" text.writeln f,"" text.writeln f,"if type$ = \"FOR:\" then" text.writeln f," pre$ = getData$(\"pre\",data$)" text.writeln f," nxt$ = getData$(\"nxt\",data$) text.writeln f," lpp = val(getData$(\"lpp\",data$))" text.writeln f," lpp = min(100,lpp)" text.writeln f," lpp = max(10,lpp)" text.writeln f," totPages = ceil(numRecs / lpp)" text.writeln f," pge = val(getData$(\"pgeNum\",data$))" text.writeln f," if nxt$ = \"Next\" then" text.writeln f," if pge = pgeNum then pge = pge + 1" text.writeln f," endif" text.writeln f," if pre$ = \"Prev\" then" text.writeln f," if pge = pgeNum then pge = pge - 1" text.writeln f," endif" text.writeln f," pgeNum = pge" text.writeln f," pgeNum = max(1,pgeNum)" text.writeln f," pgeNum = min(totPages,pgeNum)" text.writeln f," begLimit= (pgeNum * lpp) - lpp" text.writeln f,"endif" text.writeln f,"goto shoList" text.writeln f,"" text.writeln f,"! ------------------------------------" text.writeln f,"! Maintenance" text.writeln f,"! ------------------------------------" text.writeln f,"maint:" text.writeln f,"rowid$ = \"\"" for i = 1 to numFields text.writeln f,fname$[i]+"$\t=\"\"" next i text.writeln f,"if acd$ <> \"add\" then" text.writeln f," sql$ = \"SELECT rowid,* FROM "+thisTable$+" WHERE rowid = '\"+acdRowid$+\"'" text.writeln f," sql.raw_query cur,theDb,sql$" text.writeln f," sql.query.length rows, cur" text.writeln f," if rows < 1 then " text.writeln f," errMsg$ = \"Not on file for rowid:\"+acdRowid$" text.writeln f," goto shoList" text.writeln f," endif" text.writeln f,"sql.next xdone,cur,rowid$,"+colNames$ text.writeln f,"endif" text.writeln f,"" text.writeln f,"n$ = \"Maintenance\"" text.writeln f,"if acdName$ = \"Search\" then n$ = \"Search - Drill Down
Wild card=*: x*=begins *x=ends *x*=contains\"" text.writeln f,"h$=hd$ text.writeln f,"gosub msg text.writeln f,"h$=h$+m$ +~ text.writeln f,"\"
\" +~" text.writeln f,"\"
\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"
\" + n$ +~" for i = 1 to numFields text.writeln f,"\"
"+upper$(left$(fname$[i],1)) + mid$(fname$[i],2)+"\" +~" if upper$(ftype$[i]) = "TEXT" then text.writeln f,"\"\" +~" goto genInDone endif if upper$(ftype$[i]) = "DATE" then text.writeln f,"\"\" +~" else siz = min(40,val("0"+fsize$[i])) siz = max(3,siz) siz$ = format$("###%",siz) text.writeln f,"\"\" +~" endif genInDone: next i text.writeln f,"" text.writeln f,"\"
\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"
\"" text.writeln f,"gosub userInput" text.writeln f,"" text.writeln f,"if rqst$ = \"ext\" then goto shoList" text.writeln f,"if acd$ = \"vie\" then goto shoList" text.writeln f,"! =====================================" text.writeln f,"! Do requested Add Change Delete" text.writeln f,"! =====================================" text.writeln f,"if acd$ = \"del\" then" text.writeln f," sql$ = \"DELETE FROM area WHERE rowid = '\"+acdRowid$+\"'\"" text.writeln f," sql.exec theDb,sql$" text.writeln f," infMsg$ = \"Rowid \"+acdRowid$+\" Deleted\"" text.writeln f," goto shoList" text.writeln f,"end if" text.writeln f,"" text.writeln f," text.writeln f,"! --------------------------------------------- text.writeln f,"! Get data from the screen text.writeln f,"! --------------------------------------------- text.writeln f,"errMsg$ = \"\"" for i = 1 to numFields text.writeln f,fname$[i]+"$\t=getData$(\""+fname$[i]+"\",data$)" if is_in("INT",ftype$[i]) > 0 then text.writeln f,"if isNumeric("+fname$[i]+"$) = 0 then errMsg$ = errMsg$ + \""+fname$[i]+" is not Numeric \"" endif next i text.writeln f,"" text.writeln f,"if acdName$ = \"Search\" then errMsg$ = \"\"" text.writeln f,"if errMsg$ <> \"\" then goto shoList" text.writeln f,"" text.writeln f,"q$ = \"','\"" text.writeln f,"dbVals$ = \"'\"+"+replace$(colNames$,",","+q$+")+"+\"'\"" text.writeln f,"" text.writeln f,"if acdName$ = \"Search\" then goto drillDown" text.writeln f,"if acd$ = \"chg\" then" text.writeln f," GOSUB sqlSet" text.writeln f," sql$ = \"UPDATE area SET \"+sql$+\" WHERE rowid = '\"+acdRowid$+\"'\"" text.writeln f,"endif" text.writeln f,"if acd$ = \"add\" | acd$ = \"lik\" then" text.writeln f," sql$ = \"INSERT OR REPLACE into "+thisTable$+" (\"+ dbFields$+ \") VALUES (\"+ dbVals$ + \")\"" text.writeln f,"endif" text.writeln f,"" text.writeln f,"sql.exec theDB,sql$" text.writeln f,"" text.writeln f,"goto shoList" text.writeln f,"" text.writeln f,"! -------------------------------------------------------" text.writeln f,"! drill Down" text.writeln f,"! -------------------------------------------------------" text.writeln f,"drillDown:" text.writeln f,"ix = 1" text.writeln f,"pgeNum = 1" text.writeln f,"pge = 1" text.writeln f,"begLimit = 1" text.writeln f,"wh$ = \"\"" text.writeln f,"srcFor$ = \"\"" text.writeln f,"cma$ = \" WHERE \"" text.writeln f,"dbVals$ = \"',\" + dbVals$ + \",'','\"" text.writeln f,"dbVals$ = replace$(dbVals$,\"','\",\", \")" text.writeln f,"while (word$(dbFields$,ix,\",\") <> \"\")" text.writeln f," val$ = mid$(word$(dbVals$,ix,\",\"),2)" text.writeln f," if val$ <> \"\" then" text.writeln f," fld$ = word$(dbFields$,ix,\",\")" text.writeln f," if is_in(\"*\",val$) = 0 then val$ = val$ + \"*\"" text.writeln f," val$ = replace$(val$,\"**\",\"*\")" text.writeln f," srcFor$ = srcFor$ + \" \" +fld$+\":\"+ val$" text.writeln f," val$ = replace$(val$,\"*\",\"%\")" text.writeln f," wh$ = wh$ + cma$ + fld$ + \" like '\" + val$ + \"'\"" text.writeln f," cma$ = \" AND \"" text.writeln f," endif" text.writeln f," ix = ix + 1" text.writeln f,"REPEAT" text.writeln f,"goto shoList" text.writeln f,"" text.writeln f,"! -------------------------------------------------------" text.writeln f,"! Convert sql field and values notation to set notation" text.writeln f,"! -------------------------------------------------------" text.writeln f,"sqlSet:" text.writeln f,"ix = 1" text.writeln f,"sql$ = \"\"" text.writeln f,"qq$ = \"\"" text.writeln f,"cma$ = \"\"" text.writeln f,"while (word$(dbFields$,ix,\",\") <> \"\")" text.writeln f," sql$ = sql$ + cma$ + word$(dbFields$,ix,\",\") + \" = \" + qq$ + word$(dbVals$,ix,\",'\")" text.writeln f," cma$ = \", \"" text.writeln f," qq$ = \"'\"" text.writeln f," ix = ix + 1" text.writeln f,"REPEAT" text.writeln f,"RETURN" text.writeln f,"" text.writeln f,"! ------------------------------------" text.writeln f,"! User screen input" text.writeln f,"! ------------------------------------" text.writeln f,"userInput:" text.writeln f,"html.load.string h$" text.writeln f,"userInput1:" text.writeln f,"data$ = \"\"" text.writeln f,"rqst$ = \"\"" text.writeln f,"do" text.writeln f," html.get.datalink data$" text.writeln f," pause 250 % pause 1/4 sec" text.writeln f,"until data$ <> \"\"" text.writeln f,"" text.writeln f,"type$ = left$(data$, 4) % Type of data link" text.writeln f,"data$ = mid$(data$,5) % Trim first 4 characters" text.writeln f,"if type$ = \"BAK:\" then % back key hit.. go back if possible" text.writeln f," data$ = \"ext|backkey\"" text.writeln f," rqst$ = \"ext\"" text.writeln f," goto userInputExit" text.writeln f,"endif" text.writeln f,"if type$ = \"ERR:\" then % An error occured" text.writeln f," errMsg$ = \"ERROR \" + data$" text.writeln f," goto userInputExit" text.writeln f,"endif text.writeln f,"data$ = replace$(data$,\"+\",\"\") % convert metadata" text.writeln f,"for i = 1 to len(data$)" text.writeln f," i = is_in(\"%\",data$,i)" text.writeln f," if i = 0 then F_N.break" text.writeln f," data$ = left$(data$,i-1) + chr$(hex(mid$(data$,i+1,2))) + mid$(data$,i+3)" text.writeln f,"next i" text.writeln f,"" text.writeln f,"if type$ = \"LNK:\" then" text.writeln f," data$ = word$(data$,2,\"FORM\")" text.writeln f," type$ = \"FOR:\"" text.writeln f,"endif" text.writeln f,"if type$ = \"DAT:\" then % User data returned" text.writeln f," i = is_in(\"|\",data$)" text.writeln f," rqst$ = left$(data$,i-1)" text.writeln f," theData$ = mid$(data$,i+1)" text.writeln f," goto userInputExit" text.writeln f,"endif" text.writeln f,"" text.writeln f,"userInputExit:" text.writeln f,"RETURN" text.writeln f,"" text.writeln f,"! ------------------------------------" text.writeln f,"! messages" text.writeln f,"! ------------------------------------" text.writeln f,"msg:" text.writeln f,"m$= \"\"" text.writeln f,"m$=m$+ \"\"" text.writeln f,"m$=m$+ \"\"" text.writeln f,"m$=m$+ \"\"" text.writeln f,"m$=m$+ \"
\"+errMsg$+\"
\"+wrnMsg$+\"
\"+infMsg$+\"
\"" text.writeln f,"errMsg$ = \"\"" text.writeln f,"wrnMsg$ = \"\"" text.writeln f,"infMsg$ = \"\"" text.writeln f,"RETURN" text.writeln f,"" text.writeln f,"! =================================================================" text.writeln f,"! functions and other setup" text.writeln f,"! =================================================================" text.writeln f,"knoFun: % get to know your functions" text.writeln f,"html.open" text.writeln f,"html.clear.cache" text.writeln f,"lpp = 20 % lines per page" text.writeln f,"! ---------------------------------------" text.writeln f,"! Page Header" text.writeln f,"! ---------------------------------------" text.writeln f,"hd$=\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"ionSQL Management\" +~" text.writeln f,"\"\" +~" text.writeln f,"\"\"" text.writeln f,"" text.writeln f,"pgeNum = 1" text.writeln f,"lpp = 20" text.writeln f,"pgeNum = 1" text.writeln f,"clrHdr$ = \"#A3C266\" % Header Color" text.writeln f,"clrBkg$ = \"#D1E0B2\" % Background Color" text.writeln f,"clr0bg$ = \"#E0EBCC\" % Even line color" text.writeln f,"clr1bg$ = \"#FOF5E6\" % Odd Line color" text.writeln f,"limit$ = \" LIMIT 0,\"+ format$(\"###\",lpp)" text.writeln f,"" text.writeln f,"! ------------------------------------" text.writeln f,"! find data in returned FOR: info" text.writeln f,"! ------------------------------------" text.writeln f,"fn.def getData$(need$,fromData$)" text.writeln f,"need$ = need$+\"=\"" text.writeln f,"getData$ = word$(\" \"+fromData$,2,need$) + \"&\"" text.writeln f,"if left$(getData$,1) = \"&\" then getData$ = \"\"" text.writeln f,"getData$ = word$(getData$,1,\"&\")" text.writeln f,"fn.rtn replace$(getData$,\"'\",\"''\") % turn single quote to double quote" text.writeln f,"fn.end" text.writeln f,"" text.writeln f,"! ------------------------------------" text.writeln f,"! Test for numeric 1 is good, 0 is bad" text.writeln f,"! ------------------------------------" text.writeln f,"fn.def isNumeric(f$)" text.writeln f,"isNumeric = 1" text.writeln f,"j = 1" text.writeln f,"while is_in(\" \",f$) > 0" text.writeln f," f$ = replace$(f$,\" \",\"\")" text.writeln f,"repeat" text.writeln f,"if f$ = \"\" then f$ = \"0\"" text.writeln f,"if left$(f$,1) = \"-\" | left$(f$,1) = \"+\" then j = 2" text.writeln f,"for i = j to len(f$)" text.writeln f," if mid$(f$,i,1) = \".\" then" text.writeln f," if ddot$ = \".\" then isNumeric = 0" text.writeln f," ddot$ = \".\"" text.writeln f," goto nxtDigit" text.writeln f," endif" text.writeln f," if mid$(f$,i,1) = \",\" then goto nxtDigit" text.writeln f," if mid$(f$,i,1) < \"0\" then isNumeric = 0" text.writeln f," if mid$(f$,i,1) > \"9\" then isNumeric = 0" text.writeln f," nxtDigit:" text.writeln f,"next i" text.writeln f,"fn.rtn isNumeric" text.writeln f,"fn.end" text.writeln f,"" text.writeln f,"RETURN" text.close f goto tbles ! ! --------------------------------------------------- ! find path of db Directory ! ---------------------------------------------------- dbPath: if is_in("../databases/",thisDbFile$) = 1 then file.root root$ root$ = ".."+root$ + "/" ddrLvl = 0 for i = 1 to len(root$) i = is_in("/",root$,i+1) if i = 0 then F_N.break ddrLvl = ddrLvl + 1 if ddrLvl = 3 then F_N.break next i root$ = "../.." + mid$(root$,i) thisDbFile$ = replace$(root$,"/data/",mid$(thisDbFile$,3)) endif return ! --------------------------------------------------- ! Choose file from Directory ! ---------------------------------------------------- chooseFile: ddrPath$ = "" file.root root$ root$ = ".."+root$ + "/" ddrLvl = 0 for i = 1 to len(root$) i = is_in("/",root$,i+1) if i = 0 then F_N.break ddrLvl = ddrLvl + 1 next i array.delete dr$[] dim dr$[100] for i = 1 to ddrLvl dr$[i] = word$(root$,i,"/") + "/" next i ddrLoop: ! Get the listing from the path directory ! and sort it ARRAY.DELETE d1$[] FILE.DIR ddrPath$, d1$[] ARRAY.LENGTH length, d1$[] if d1$[1] = "" then length = length - 1 for i = 1 to length d1$[i] = lower$(d1$[i]) + " __^^__"+d1$[i] next i array.sort d1$[] ARRAY.DELETE d2$[] DIM d2$[length+1] d2$[1] = ".." FOR i = 1 TO length j = is_in("__^^__",d1$[i]) d2$[i + 1] = mid$(d1$[i],j+6) NEXT i h1$ = "
" +~ "" +~ "" h2$= "" for i = 1 to length + 1 thisFile$ = ddrPath$+d2$[i] thisName$ = d2$[i] if length > 100 & mod(i,20) = 0 then infMsg$ = "Working .. Count:"+format$("#####",i)+" "+thisName$ gosub shoProgress endif siz$ = "" if is_in("(d)",d2$[i]) = 0 & d2$[i] <> ".." then file.exists exst,thisFile$ if exst <> 0 then file.size siz,thisFile$ siz$ = format$("#########",siz) endif endif h2$=h2$+"" +~ "" next i h$=hd$+m$+h1$+h2$+"
" +~ "Path: "+ddrPath$+"
Count:"+format$("####",length) +~ " level:"+format$("###",ddrLvl) + "
Dir:"+root$ +~ "
" +~ "" +~ "
NameSize
" +~ ""+siz$+"
" gosub userInput ddrUserRqst: fctn$ = left$(data$,3) if fctn$ = "ext" then goto ddrExit s$ = mid$(data$,5) s = val(s$) IF s > 1 n = IS_IN("(d)", d2$[s]) IF n = 0 then thisFile$ = ddrPath$+d2$[s] thisName$ = d2$[s] popup "File:"+thisFile$,0,0,0 for dj = 0 to len(thisName$) dj = is_in(".",thisName$,dj+1) if dj = 0 then F_N.break di = dj next i if di = 0 then filExt$ = "unk" else filExt$ = lower$(mid$(thisName$,di+1)) endif if typFile$ = "sql" then RETURN if typFile$ = "csv" then RETURN if typFile$ = "db" then thisName$ = d2$[s] byte.open r,f,thisFile$ byte.read.buffer f,13,data$ byte.close f if is_in("SQLite format",data$) = 0 then errMsg$ = "File "+thisFile$+" is not a SQLite file" gosub shoMsg popup "File "+thisFile$+" is not a SQLite file",0,0,0 html.load.url thisFile$ gosub userInput1 goto dbRequest else infMsg$ = "SQLite file:"+thisFile$ RETURN endif endif gosub userInput1 goto ddrUserRqst ! GOTO ddrLoop ENDIF dname$ = LEFT$(d2$[s],n-1) ddrPath$=ddrPath$+dname$+"/" root$ = "" ddrLvl = ddrLvl+ 1 dr$[ddrLvl] = dname$ + "/" for i = 1 to ddrLvl root$ = root$ + dr$[i] next i GOTO ddrLoop ENDIF ! If s = 1 then must back one level ! if at start path then back up one level IF ddrPath$ = "" ddrPath$ = "../" ddrLvl = ddrLvl -1 root$ = "" for i = 1 to ddrLvl root$ = root$ + dr$[i] next i GOTO ddrLoop ENDIF ! Not at start path split the path by the "/" chars ARRAY.DELETE p$[] SPLIT p$[], ddrPath$, "/" ARRAY.LENGTH length, p$[] ! If the last entry is ".." then add "../" to back up one level IF p$[length] = ".." ddrPath$ = ddrPath$ + "../" ddrLvl = ddrLvl -1 root$ = "" for i = 1 to ddrLvl root$ = root$ + dr$[i] next i GOTO ddrLoop ENDIF ! Last entry is not ".." so must delete the ! last directory from the path If only one entry then path is back ! to the base path IF length = 1 ddrPath$ = "" file.root root$ root$ = ".."+root$+"/" GOTO ddrLoop ENDIF ! Reconstruct path without the last directory ddrPath$ = "" FOR i = 1 TO length - 1 ddrPath$ = ddrPath$ + p$[i] + "/" NEXT i root$ = "" ddrLvl = ddrLvl -1 FOR i = 1 TO length - 1 path$ = path$ + p$[i] + "/" next i for i = 1 to ddrLvl root$ = root$ + dr$[i] NEXT i GOTO ddrLoop ddrExit: rqst$ = "ext" RETURN ! ------------------------------------------ ! get field data from sql ! ------------------------------------------ getNextSql: sqlNxtErr$ = "" if numFlds < 11 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10] return endif if numFlds < 21 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10],vd$[11],vd$[12],vd$[13],vd$[14],vd$[15],vd$[16],vd$[17],vd$[18],vd$[19],vd$[20] return endif if numFlds < 31 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10],vd$[11],vd$[12],vd$[13],vd$[14],vd$[15],vd$[16],vd$[17],vd$[18],vd$[19],vd$[20],vd$[21],vd$[22],vd$[23],vd$[24],vd$[25],vd$[26],vd$[27],vd$[28],vd$[29],vd$[30] return endif if numFlds < 41 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10],vd$[11],vd$[12],vd$[13],vd$[14],vd$[15],vd$[16],vd$[17],vd$[18],vd$[19],vd$[20],vd$[21],vd$[22],vd$[23],vd$[24],vd$[25],vd$[26],vd$[27],vd$[28],vd$[29],vd$[30],vd$[31],vd$[32],vd$[33],vd$[34],vd$[35],vd$[36],vd$[37],vd$[38],vd$[39],vd$[40] return endif if numFlds < 51 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10],vd$[11],vd$[12],vd$[13],vd$[14],vd$[15],vd$[16],vd$[17],vd$[18],vd$[19],vd$[20],vd$[21],vd$[22],vd$[23],vd$[24],vd$[25],vd$[26],vd$[27],vd$[28],vd$[29],vd$[30],vd$[31],vd$[32],vd$[33],vd$[34],vd$[35],vd$[36],vd$[37],vd$[38],vd$[39],vd$[40],vd$[41],vd$[42],vd$[43],vd$[44],vd$[45],vd$[46],vd$[47],vd$[48],vd$[49],vd$[50] return endif if numFlds < 61 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10],vd$[11],vd$[12],vd$[13],vd$[14],vd$[15],vd$[16],vd$[17],vd$[18],vd$[19],vd$[20],vd$[21],vd$[22],vd$[23],vd$[24],vd$[25],vd$[26],vd$[27],vd$[28],vd$[29],vd$[30],vd$[31],vd$[32],vd$[33],vd$[34],vd$[35],vd$[36],vd$[37],vd$[38],vd$[39],vd$[40],vd$[41],vd$[42],vd$[43],vd$[44],vd$[45],vd$[46],vd$[47],vd$[48],vd$[49],vd$[50],vd$[51],vd$[52],vd$[53],vd$[54],vd$[55],vd$[56],vd$[57],vd$[58],vd$[59],vd$[60] return endif if numFlds < 71 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10],vd$[11],vd$[12],vd$[13],vd$[14],vd$[15],vd$[16],vd$[17],vd$[18],vd$[19],vd$[20],vd$[21],vd$[22],vd$[23],vd$[24],vd$[25],vd$[26],vd$[27],vd$[28],vd$[29],vd$[30],vd$[31],vd$[32],vd$[33],vd$[34],vd$[35],vd$[36],vd$[37],vd$[38],vd$[39],vd$[40],vd$[41],vd$[42],vd$[43],vd$[44],vd$[45],vd$[46],vd$[47],vd$[48],vd$[49],vd$[50],vd$[51],vd$[52],vd$[53],vd$[54],vd$[55],vd$[56],vd$[57],vd$[58],vd$[59],vd$[60],vd$[61],vd$[62],vd$[63],vd$[64],vd$[65],vd$[66],vd$[67],vd$[68],vd$[69],vd$[70] return endif if numFlds < 81 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10],vd$[11],vd$[12],vd$[13],vd$[14],vd$[15],vd$[16],vd$[17],vd$[18],vd$[19],vd$[20],vd$[21],vd$[22],vd$[23],vd$[24],vd$[25],vd$[26],vd$[27],vd$[28],vd$[29],vd$[30],vd$[31],vd$[32],vd$[33],vd$[34],vd$[35],vd$[36],vd$[37],vd$[38],vd$[39],vd$[40],vd$[41],vd$[42],vd$[43],vd$[44],vd$[45],vd$[46],vd$[47],vd$[48],vd$[49],vd$[50],vd$[51],vd$[52],vd$[53],vd$[54],vd$[55],vd$[56],vd$[57],vd$[58],vd$[59],vd$[60],vd$[61],vd$[62],vd$[63],vd$[64],vd$[65],vd$[66],vd$[67],vd$[68],vd$[69],vd$[70],vd$[71],vd$[72],vd$[73],vd$[74],vd$[75],vd$[76],vd$[77],vd$[78],vd$[79],vd$[80] return endif if numFlds < 91 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10],vd$[11],vd$[12],vd$[13],vd$[14],vd$[15],vd$[16],vd$[17],vd$[18],vd$[19],vd$[20],vd$[21],vd$[22],vd$[23],vd$[24],vd$[25],vd$[26],vd$[27],vd$[28],vd$[29],vd$[30],vd$[31],vd$[32],vd$[33],vd$[34],vd$[35],vd$[36],vd$[37],vd$[38],vd$[39],vd$[40],vd$[41],vd$[42],vd$[43],vd$[44],vd$[45],vd$[46],vd$[47],vd$[48],vd$[49],vd$[50],vd$[51],vd$[52],vd$[53],vd$[54],vd$[55],vd$[56],vd$[57],vd$[58],vd$[59],vd$[60],vd$[61],vd$[62],vd$[63],vd$[64],vd$[65],vd$[66],vd$[67],vd$[68],vd$[69],vd$[70],vd$[71],vd$[72],vd$[73],vd$[74],vd$[75],vd$[76],vd$[77],vd$[78],vd$[79],vd$[80],vd$[81],vd$[82],vd$[83],vd$[84],vd$[85],vd$[86],vd$[87],vd$[88],vd$[89],vd$[90] return endif if numFlds < 101 then sql.next xdone,wwCur,vd$[1],vd$[2],vd$[3],vd$[4],vd$[5],vd$[6],vd$[7],vd$[8],vd$[9],vd$[10],vd$[11],vd$[12],vd$[13],vd$[14],vd$[15],vd$[16],vd$[17],vd$[18],vd$[19],vd$[20],vd$[21],vd$[22],vd$[23],vd$[24],vd$[25],vd$[26],vd$[27],vd$[28],vd$[29],vd$[30],vd$[31],vd$[32],vd$[33],vd$[34],vd$[35],vd$[36],vd$[37],vd$[38],vd$[39],vd$[40],vd$[41],vd$[42],vd$[43],vd$[44],vd$[45],vd$[46],vd$[47],vd$[48],vd$[49],vd$[50],vd$[51],vd$[52],vd$[53],vd$[54],vd$[55],vd$[56],vd$[57],vd$[58],vd$[59],vd$[60],vd$[61],vd$[62],vd$[63],vd$[64],vd$[65],vd$[66],vd$[67],vd$[68],vd$[69],vd$[70],vd$[71],vd$[72],vd$[73],vd$[74],vd$[75],vd$[76],vd$[77],vd$[78],vd$[79],vd$[80],vd$[81],vd$[82],vd$[83],vd$[84],vd$[85],vd$[86],vd$[87],vd$[88],vd$[89],vd$[90],vd$[91],vd$[92],vd$[93],vd$[94],vd$[95],vd$[96],vd$[97],vd$[98],vd$[99],vd$[100] return endif sqlNxtErr$ = "Only 100 Columns of data allowed" return ! ! ================================================================= ! Show me the project web page ! ================================================================= shoWeb: html.load.url "http://www.kneware.com/ionsql/" do html.get.datalink data$ until data$ <> "" goto dbRequest ! ================================================================= ! give me some help ! ================================================================= shoHelp: h$=hd$ + "" +~ "" +~ "
ionSQL Database Manager" +~ "" +~ "
" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "" +~ "
CreateCreate your own databaseSimply enter a database {name}. There will be a sqlite file created in ..databases/{yourDBname}
IncludeYou can include an existing DB.The system shows a list of files and directories.
" +~ "Click a directory (d), it will go to that directory.
" +~ "Click [..] it will go up a directory
" +~ "Cick on a file. If it is a SQLite file. If it is it will be included.
" +~ "If not, it will try to display it using html. Therefore sometimes the screen will be blank.
" +~ "Use [backkey] to continue
ExitExit the DB system
Del ListDlete the database from the listDeleting a DB does not delete it from disk. It only deletes it from the SQLite management system.
TablesShow the tables in a databaseShows a list of tables with options.
ExportExport data from the DBExport CSV or SQL. You can export the Schema, or the data. Use it to create other DB's such as mySQL
Load SQLLoad SQL informationIt can be the Schema or the data or both
FindFind field in tablesFind tables that Equal, Begin With, or Contain a field
SQLEnter SQL commandsEnter your own SQL commands
XrefCreate a Cross Reference listCross reference list of field names to tables
FieldsLook at the fields in the TableAdd change and delete Fields or Index
BrowseList information in the tables.Sort on any field. Add, Change, and Delete information.
EmptyDelete all information in a table
RenameChange the table name
DropDrop the table form the DB
GenProgGenerate programGenerate rfo-basic code to maintain your table, or From your SQL commands.
Load CSVLoad CSVThey must match the database columns exactly, Or use firs column to specify columns.
" +~ "" gosub userInput goto dbRequest ! ! ================================================================= ! functions and other setup ! ================================================================= knoFun: % get to know your functions html.open html.clear.cache lpp = 20 % lines per page dim vd$[100] fldFill$ = ",'|','|','|','|','|','|','|','|','|','|'" ! ! ------------------------------------- ! known sqlite tables ! ------------------------------------- knowTbls: err$ = "2600 dbRequest: Known tables" file.exists f,"/../databases" if f = 0 then file.mkdir "../databases" endif file.exists f,"/../data" if f = 0 then file.mkdir "../data" endif sql.open knoDb,"sqLiteKnowTbls.db" sql$ = "CREATE TABLE if not exists dbList(dbFile text,dbLoc text)" sql.exec knoDB,sql$ sql$ = "SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name='proc'" sql.raw_query cur,knoDB,sql$ sql.query.length rows, cur if rows < 1 then sql$ = "CREATE TABLE if not exists proc(procId CHAR(10),dbFile VARCHAR(33),descr VARCHAR(33),notes TEXT,qry TEXT)" sql.exec knoDB,sql$ sql$ = "CREATE UNIQUE INDEX proc_ID ON proc(procId,dbFile)" sql.exec knoDB,sql$ endif ! ! --------------------------------------- ! Set your color scheme here ! --------------------------------------- clrHdr$ = "#A3C266" % Header Color" clrBkg$ = "#D1E0B2" % Background Color" clr0bg$ = "#E0EBCC" % Even line color" clr1bg$ = "#FOF5E6" % Odd Line color" dim clrOe$[2] % Odd / Even lines clrOe$[1] = "#E0EBCC" % odd line color clrOe$[2] = "#FOF5E6 % even line color ! ! --------------------------------------- ! data feild types ! --------------------------------------- numDataType = 26 dim dataType$[numDataType] dataType$[01] = "VARCHAR" dataType$[02] = "BIGINT" dataType$[03] = "BLOB" dataType$[04] = "BOOLEAN" dataType$[05] = "CHAR" dataType$[06] = "CLOB" dataType$[07] = "DATE" dataType$[08] = "DATETIME" dataType$[09] = "DECIMAL" dataType$[10] = "DOUBLE" dataType$[11] = "FLOAT" dataType$[12] = "INT" dataType$[13] = "INTEGER" dataType$[14] = "LONGBLOB" dataType$[15] = "LONGTEXT" dataType$[16] = "MEDIUMBLOB" dataType$[17] = "MEDIUMINT" dataType$[18] = "MEDIUMTEXT" dataType$[19] = "SMALLINT" dataType$[20] = "TEXT" dataType$[21] = "TIME" dataType$[22] = "TIMESTAMP" dataType$[23] = "TINYBLOB" dataType$[24] = "TINYINT" dataType$[25] = "TINYTEXT" dataType$[26] = "YEAR" dataAlignRignt$ = "BIGINT,DECIMAL,DOUBLE,FLOAT,INT,INTEGER,MEDIUMINT,SMALLINT,TINYINT,YEAR" ! ! --------------------------------------- ! Page Header ! --------------------------------------- hd$="" +~ "" +~ "" +~ "SQLite Management" +~ "" +~ "" ! ! ------------------------------------ ! trim left and right blanks ! ------------------------------------ fn.def trim$(value$) if len(value$) > 0 then WHILE left$(value$,1) = " " % trim left spaces value$ = mid$(value$,2) REPEAT if len(value$) > 0 then WHILE right$(value$,1) = " " % trim right spaces value$ = left$(value$,len(value$)-1) REPEAT endif endif fn.rtn value$ fn.end ! ! -------------------------------- ! string replace rep str with ! -------------------------------- fn.def strRep$(str$,rep$,with$) ln = len(rep$) i = is_in(rep$,str$) while i > 0 str$ = left$(str$,i-1) + with$ + mid$(str$,i+ln) i = is_in(rep$,str$,i) repeat fn.rtn str$ fn.end ! ! ------------------------------------ ! Test for numeric 1 is good, 0 is bad ! ------------------------------------ fn.def isNumeric(f$) isNumeric = 1 j = 1 f$ = strRep$(f$," ","") if left$(f$,1) = "-" | left$(f$,1) = "+" then j = 2 for i = j to len(f$) if mid$(f$,i,1) = "." then if ddot$ = "." then isNumeric = 0 ddot$ = "." goto nxtDigit endif if mid$(f$,i,1) = "," then goto nxtDigit if mid$(f$,i,1) < "0" then isNumeric = 0 if mid$(f$,i,1) > "9" then isNumeric = 0 nxtDigit: next i fn.rtn isNumeric fn.end ! ! ------------------------------------ ! find data in returned FOR: info ! ------------------------------------ fn.def getData$(need$,fromData$) need$ = need$+"=" getData$ = word$(" "+fromData$,2,need$) + "&" if left$(getData$,1) = "&" then getData$ = "" getData$ = word$(getData$,1,"&") fn.rtn getData$ fn.end ! ! ------------------------------------ ! return only numeric ! strip everything else ! ------------------------------------ fn.def makeNumeric(f$) f$ = strRep$(f$," ","") % no blanks f$ = strRep$(f$,",","") % no commas i = is_in(".",f$) if i > 0 then f$ = left$(f$,i) + strRep$(mid$(f$,i+1),".","") % only allow 1 dot if left$(f$,1) = "-" | left$(f$,1) = "+" then j = 2 % leading -+ is ok for i = j to len(f$) if mid$(f$,i,1) = "." then goto nxtDigit1 if mid$(f$,i,1) = "," then goto nxtDigit1 if mid$(f$,i,1) < "0" | mid$(f$,i,1) > "9" then f$ = left$(f$,i-1) + mid$(f$,i+1) nxtDigit1: next i fn.rtn f$ fn.end RETURN