' ======================================================
' SQLite Database Management System
' By Dan Kokenge
' ======================================================
ver$    = "1.20"               		' version number
cssclass "body", "{font-family:arial; font-size:18px; font-weight:bold; margin-right:3%; margin-left:3%; }"
bf$	= "<B><FONT SIZE=2 FACE=Arial>"     ' Bold font
bh$ 	= "<B><FONT SIZE=4 FACE=Arial>"     ' Bold font Huge
ba$	= "<SPAN STYLE='font-family:Arial; font-weight:700; font-size:10pt'>"
q$ 	= "','"
dim null$(2)
null$(1) = "NOT NULL"
null$(2) = "NULL"
dim pk$(2)

projectDir$ = "SQLite_project"
if left$(Platform$,3) = "win" then
sqliteDb$   = DefaultDir$ + "\projects\" + projectDir$ + "\SQLite.db"
else
sqliteDb$   = DefaultDir$ + "/projects/" + projectDir$ + "/SQLite.db"
end if

pk$(1)	= ""
pk$(2)	= "PRIMARY KEY"

dim type$(28)
dim typeVal$(28)
type$(01)   = ""            : typeVal$(01)  = ""
type$(02)   = "VARCHAR"     : typeVal$(02)  = "C"
type$(03)   = "BIGINT"      : typeVal$(03)  = "I"
type$(04)   = "BLOB"        : typeVal$(04)  = "C"
type$(05)   = "BOOLEAN"     : typeVal$(05)  = "B"
type$(06)   = "CHAR"        : typeVal$(06)  = "C"
type$(07)   = "CLOB"        : typeVal$(07)  = "C"
type$(08)   = "DATE"        : typeVal$(08)  = "C"
type$(09)   = "DATETIME"    : typeVal$(09)  = "C"
type$(10)   = "DECIMAL"     : typeVal$(10)  = "D"
type$(11)   = "DOUBLE"      : typeVal$(11)  = "D"
type$(12)   = "FLOAT"       : typeVal$(12)  = "D"
type$(13)   = "INT"         : typeVal$(13)  = "I"
type$(14)   = "INTEGER"     : typeVal$(14)  = "I"
type$(15)   = "LONGBLOB"    : typeVal$(15)  = "C"
type$(16)   = "LONGTEXT"    : typeVal$(16)  = "C"
type$(17)   = "MEDIUMBLOB"  : typeVal$(17)  = "C"
type$(18)   = "MEDIUMINT"   : typeVal$(18)  = "I"
type$(19)   = "MEDIUMTEXT"  : typeVal$(19)  = "C"
type$(20)   = "SMALLINT"    : typeVal$(20)  = "I"
type$(21)   = "TEXT"        : typeVal$(21)  = "C"
type$(22)   = "TIME"        : typeVal$(22)  = "C"
type$(23)   = "TIMESTAMP"   : typeVal$(23)  = "C"
type$(24)   = "TINYBLOB"    : typeVal$(24)  = "C"
type$(25)   = "TINYINT"     : typeVal$(25)  = "I"
type$(26)   = "TINYTEXT"    : typeVal$(26)  = "C"
type$(27)   = "VARCHAR"     : typeVal$(27)  = "C"
type$(28)   = "YEAR"        : typeVal$(28)  = "I"

' ----------------------------------------------
' if not exist then create the SQLite manager
' ----------------------------------------------
files   #f,sqliteDb$
if  #f HASANSWER() = 0 then     ' does file already exist
     sqliteconnect  #sql, sqliteDb$
     sql$   = "create table db (dbName varchar(12), dbFile varchar(30), dbLoc varchar(100))"
        #sql execute(sql$)
        #sql disconnect()
     Print "New SQLlite3 database created"
end if

'call SetCSS
[main]
gosub [heading]
sqliteconnect   #sql, sqliteDb$            ' Connect to the DB
gosub [showDb]
#sql disconnect()
wait

' -------------------------------
' Show Databases
' -------------------------------
[showDb]
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD COLSPAN=4 BGCOLOR=#FFCC99 ALIGN=CENTER>";bf$;"Database</TD></TR>"
html "<TR BGCOLOR=#FFCC99 ALIGN=CENTER>"
html "<TD>" 
button    #create, "Create",[createDb]
        #create cssclass("sBtn")
html " "
button    #load, "Load", [addDb]
        #load cssclass("sBtn")
html "</TD>"
html "<TD>";bf$;"Name</TD>"
html "<TD>";bf$;"File</TD>"
html "<TD>";bf$;"Location</TD></TR>"

sql$    = "
SELECT dbName,dbFile,dbLoc 
FROM    db 
ORDER BY dbName"
#sql execute(sql$)
rows    =   #sql ROWCOUNT()   'Get the number of rows
dim adbName$(rows)
dbNum   = 0
WHILE   #sql hasanswer()
    result$ = #sql nextrow$(" |")
    dbName$ = trim$(word$(result$,1,"|"))
    dbFile$ = trim$(word$(result$,2,"|"))
    dbLoc$  = trim$(word$(result$,3,"|"))
    html "<TR align=center><TD nowrap>"
    a$ = dbName$ + "|" + dbLoc$ + dbFile$
'button #addDb, "Add Like", [addLikeDb]
'#addDb cssclass("sBtn")
'#addDb setkey(a$)
'html " "
    button	#delDb, "Delete", [deleteDb]
		#delDb cssclass("sBtn")
		#delDb setkey(dbName$)
    html " "
    button	#tbldb, "Tables", [shoTables]
		#tbldb cssclass("sBtn")
		#tbldb setkey(a$)
     
    html " "
    button    #expdb, "Export", [exportDb]
            #expdb cssclass("sBtn")
            #expdb setkey(a$)
    html " "
    button    #xref, "Xref",[xref]
            #xref cssclass("sBtn")
            #xref setkey(a$)
     
    html "</TD><TD>";bf$;dbName$;"</TD>"
    html "<TD>";bf$;dbFile$;"</TD>"
    html "<TD align=left>";bf$;dbLoc$;"</TD></TR>"
WEND
html "</TABLE>"
RETURN

' --------------------------------
' Heading and message area
' --------------------------------
[heading]
cls
html ba$
html "<CENTER><TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 width=100% BGCOLOR=tan>"
html "<TR><TD width=5%>";bf$;" V ";ver$;"</TD>"
html "<TD ALIGN=CENTER>";bh$;"SQLite Database Manager</TD><TD width=5%>"
    button    #ex, "Exit RB System",[doExit]
            #ex cssclass("eBtn")
html "</TD></TR></TABLE>"
html "<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 >"
html "<TR><TD BGCOLOR=red id=errMsg></TD></TR>"
html "<TR><TD BGCOLOR=yellow id=wrnMsg></TD></TR>"
html "<TR><TD BGCOLOR=lime id=infoMsg></TD></TR>"
html "</TABLE>"
gosub [doMsg]
RETURN

 ' ----------------------------------
 ' Database Table Information
 ' ----------------------------------
[shoTables]
a$	= EventKey$
dbName$ = word$(a$,1,"|")
lf$	= word$(a$,2,"|")
a$	= lower$(shell$("dir "+lf$))
a1$	= lower$(dbName$)
if instr(a$,a1$) = 0 then
	errNum = 1
	errMsg$(errNum) = lf$;"<BR>***** Not on file ****"
	gosub [doMsg]
	wait
end if

[shoTables1]
tbls$   = ""
cma$    = ""
gosub [heading]
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR BGCOLOR=#FFCC99>"
html "<TD colspan=2>";bf$;"Database:";bh$;dbName$
html "</TD></TR>"

sqliteconnect   #sql1, lf$
sql1$   = "
SELECT  name 
FROM    sqlite_master
WHERE   type = 'table' 
ORDER BY name"  
#sql1 execute(sql1$)
rows    = #sql1 ROWCOUNT() 	'Get the number of rows
html "<TR BGCOLOR=#FFCC99>"
html "<TD nowrap>";lf$
html "</TD><TD>";bf$;rows;" Tables</TD></TR>"

WHILE   #sql1 hasanswer()
    result1$    = #sql1 nextrow$(" |")
    tableName$  = trim$(word$(result1$,1,"|"))
't$ = a$ + "|" + tableName$
tbls$ = tbls$ + cma$ + tableName$
    cma$    = ","
    html "<TR align=center><TD nowrap>"
    button    #fields, "Fields",[shoFields]
            #fields cssclass("sBtn")
            #fields setkey(tableName$)
    html " "
    button    #browse, "Browse",[browseTbl]
            #browse cssclass("sBtn")
            #browse setkey(tableName$)
    html " "
    button    #query, "Query", [queryTbl]
            #query cssclass("sBtn")
            #query setkey(tableName$)
    html " "
    button	#loadCsv, "LoadCsv", [loadCsv]
		#loadCsv cssclass("sBtn")
		#loadCsv setkey(tableName$)
    html " "
    button	#empty, "Empty", [mtTbl] 
		#empty cssclass("sBtn")
		#empty setkey(tableName$)
    html " "
    button	#ren, "Rename", [renTbl] 
		#ren cssclass("sBtn")
		#ren setkey(tableName$)
    html " "
    button	#drop, "Drop", [dropTbl]
		#drop cssclass("sBtn")
		#drop setkey(tableName$)

    html "</TD><TD>";bf$;tableName$
    html "</TD></TR>"
WEND
#sql1 disconnect()
html "<TR BGCOLOR=#FFCC99 ALIGN=CENTER><TD colspan=2>"
button    #addtbl, "Add Table",[addTbl]
        #addtbl cssclass("sBtn")
html " "
button    #exit, "Exit", [main]
        #exit cssclass("sBtn")
html "</TD></TR></TABLE>"
wait

' ------------------------------------
' Fields of table
' ------------------------------------
[shoFields]
tableName$  = EventKey$
tbl$ = tableName$
[shoFields1]
gosub [heading]

fields$ = ""
q$  = ""
sqliteconnect   #sql1, lf$
sql1$   = "SELECT * FROM ";tbl$;" limit 1"
#sql1 execute(sql1$)
fields$ = " " + #sql1 columnnames$() + " "
sql1$   = "
SELECT sql 
FROM    sqlite_master 
WHERE   type = 'table'
AND tbl_name = '";tbl$;"'"
#sql1 execute(sql1$)
fieldNum    = 0
WHILE #sql1 hasanswer()
    result$ = #sql1 nextrow$(" |")
'result$ = strRep$(result$,chr$(10)," ")
    result$ = strip$(result$)
    result$ = strRep$(result$,"'''","")
    t1$     = word$(result$,1,"(")
    t2$     = word$(result$,2,t1$)
    t3$     = t1$ + t2$
    createTmp$  = "( " + mid$(strRep$(t2$,","," , "),2) ' create talbe hold 
    html "<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0><TR><TD VALIGN=TOP>"
    html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>
    <TR  BGCOLOR=#FFCC99 align=center>
    <TD colspan=9>";bf$;"Database:";bh$;dbName$;bf$;"    Table:";bh$;tbl$;"</TD></TR>
    <TR  BGCOLOR=#FFCC99 align=center>
    <TD>";bf$;"Maint</TD><TD>";bf$
    html "Fields"
    html "</TD><TD>";bf$;"Name</TD><TD>";bf$;"Type</TD><TD>";bf$;"Length</TD>
    <TD>";bf$;"Dec</TD><TD>";bf$;"Def</TD><TD>";bf$;"Null</TD><TD>";bf$;"Primary</TD></TR>"
'print "fields:";fields$
    i = 1
    while word$(fields$,i,",") <> ""
        fieldName$  = trim$(word$(fields$,i,","))
        f2$         = trim$(word$(fields$,i + 1,","))
        a$          = trim$(word$(t2$,2,fieldName$ + " "))
        if f2$ <> "" then a$    = trim$(word$(a$,1,f2$))    
        a$  = a$ + " "
        fieldNull$  = ""
        fieldKey$   = ""
        fieldDef$   = ""
        fieldLen$   = ""
        fieldDec$   = ""
        fieldType$  = ""

        a$ = strRep$(a$,chr$(34),"")
        a$ = strRep$(a$,"  "," ")
        a$ = strRep$(a$,"  "," ")
        a$ = trim$(a$)
        a$ = "|" + left$(a$,len(a$) - 1) + " |"
        ua$ = upper$(a$)
'print "----------------------------"
'print "a:";a$
'print "b:";b$
'print "ua:";ua$
        a1$ = word$(ua$,1,"DEFAULT")
        for j = 1 to 28
             if instr(a1$,type$(j)) then fieldType$ = type$(j)
        next j
        fieldLen$   = word$(a1$,2,"(")
        if fieldLen$ <> "" then
            fieldLen$   = strRep$(fieldLen$,")",", ,")
            fieldDec$   = word$(fieldLen$,2,",")
            fl          = val(word$(fieldLen$,1,","))
            if fl > 0 then 
                fieldLen$ = str$(fl)
               else
                fieldLen$ = ""
            end if
        end if    

        fieldDef$ = trim$(word$(ua$,2," DEFAULT"))
        if fieldDef$ <> "" then
            fieldDef$ = word$(fieldDef$,1," ")
            fieldDef$ = trim$(fieldDef$)
        end if

        if instr(ua$," PRIMARY KEY") then fieldKey$ = "KEY"

        if instr(ua$," NOT NULL") then fieldNull$ = "NOT NULL"

        a$ = fieldName$ + " |" + fieldType$ + " |" + fieldLen$ + " |" + fieldDec$ + " |" + fieldDef$ + " |" + fieldNull$ + " |" + fieldKey$
'print a$
        html  "<TR><TD>"
        button    #chg, "Change", [chgField]
                #chg cssclass("sBtn")
                #chg setkey(a$)
        html " "
        button    #del, "Delete", [delField]
                #del cssclass("sBtn")
                #del setkey(a$)

        html "</TD><TD align=center>"
        button    #b4Field, "Before", [b4Field]
                #b4Field cssclass("sBtn")
                #b4Field setkey(a$)
        html "</TD><TD>";bf$;fieldName$
        html "</TD><TD>";bf$;fieldType$
        html "</TD><TD align=center>";bf$;fieldLen$
        html "</TD><TD align=center>";bf$;fieldDec$
        html "</TD><TD>";bf$;fieldDef$
        html "</TD><TD align=center>";bf$;fieldNull$
        html "</TD><TD align=center>";bf$;fieldKey$
        html "</TD></TR>"
        i = i + 1
    wend
    fieldNum = fieldNum - 1 
    a$  = dbName$ + "|" + lf$
    html "<TR align=right><TD>";bf$;"Add"
    TEXTBOX #addFields,"1",2
    html "Field(s)</TD><TD>"
        button    #atEndField, "At End", [atEndField]
                #atEndField cssclass("sBtn")
                #atEndField setkey(a$)
    html "</TD></TR>"
    html "<TR  BGCOLOR=#FFCC99 align=center><TD colspan=9>";bf$
    button    #exit, "Exit", [shoTables]
            #exit cssclass("sBtn")
            #exit setkey(a$)
    html "</TD></TR></TABLE>"
WEND

' ------------------------------
' index information
' ------------------------------
index$  = ""
bar$    = ""
sql1$   = "
SELECT  name,sql
FROM    sqlite_master
WHERE   type = 'index' 
AND sql <> ''
AND tbl_name = '";tbl$;"'
ORDER BY name"  
#sql1 execute(sql1$)
rows    = #sql1 ROWCOUNT()       'Get the number of rows
if rows > 0 then
    html "</TD><TD valign=top>"
    html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
    html "<TR  BGCOLOR=#FFCC99 ALIGN=CENTER><TD COLSPAN=4>";bf$;"Database Keys</TD></TR>"
    html "<TR  BGCOLOR=#FFCC99 ALIGN=CENTER>"
    html "<TD>";bf$;"Action</TD>"
    html "<TD>";bf$;"Key Name</TD>"
    html "<TD>";bf$;"Type</TD>"
    html "<TD>";bf$;"Field</TD>"
    html "</TR>"
end if

WHILE #sql1 hasanswer()
    result1$    = #sql1 nextrow$(" |")
    idxName$    = trim$(word$(result1$,1,"|"))
    a$      = trim$(word$(result1$,2,"|"))
index$  = index$ + bar$ + a$
bar$    = "|"
    a1$         = word$(a$,2,"CREATE ")
    idxType$    = word$(a1$,1," ")
    a1$     = word$(a$,2,"(")
    a1$     = word$(a1$,1,")")
    i = 1
    html "<TR><TD>"
    button    #idel, "Delete", [delIdx]
            #idel cssclass("sBtn")
            #idel setkey(idxName$)
    html "</TD><TD>";bf$;idxName$
    html "</TD><TD align=center>";bf$;idxType$
    html "</TD><TD>";bf$
    br$ = ""

    while   word$(a1$,i,",") <> ""
        idxField$   = trim$(word$(a1$,i,","))
        i = i + 1
        html br$;idxField$
        br$ = "<BR>"
    WEND
    html "</TD></TR>"
WEND
#sql1 disconnect()
html "<TR  BGCOLOR=#FFCC99 ALIGN=CENTER><TD COLSPAN=4>";bf$
    button    #iadd, "Add Index", [addIdx]
            #iadd cssclass("sBtn")
html "</TD></TR></TABLE></TABLE>"
wait

' ------------------------------
' add change delete field 
' ------------------------------
[b4Field]
b4Field$    = " " + trim$(word$(EventKey$,1,"|")) + " "
acd$    = "Add b4 " + b4Field$
addFields   = val(#addFields contents$())
goto [beField]

[atEndField]
acd$    = "Add At End"
addFields   = val(#addFields contents$())
[beField]
fieldName$  = ""
fieldType$  = ""
fieldLen$   = ""
fieldDec$   = ""
fieldDef$   = ""
fieldNull$  = ""
fieldKey$   = ""
goto [acdField1]

[chgField]
acd$    = "Change"
addFields   = 1
goto [acdField]

[delField]
acd$    = "Delete"
addFields   = 1

[acdField]
fieldName$  = trim$(word$(EventKey$,1,"|"))
fieldType$  = trim$(word$(EventKey$,2,"|"))
fieldLen$   = trim$(word$(EventKey$,3,"|"))
fieldDec$   = trim$(word$(EventKey$,4,"|"))
fieldDef$   = trim$(word$(EventKey$,5,"|"))
fieldNull$  = trim$(word$(EventKey$,6,"|"))
fieldKey$   = trim$(word$(EventKey$,7,"|"))

[acdField1]
addFields   = min(30,addFields) ' Restrict add fields to 30
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR BGCOLOR=#FFCC99>"
html "<TD ALIGN=center colspan=7>";bf$;"Datbase:";bh$;dbName$;bf$;"Table:";bh$;tbl$
html "</TD></TR><TR BGCOLOR=#FFCC99 ALIGN=CENTER>
<TD>";bf$;"Num</TD>
<TD>";bf$;"Name</TD>
<TD>";bf$;"Type</TD>
<TD>";bf$;"Length</TD>
<TD>";bf$;"Decimal</TD>
<TD>";bf$;"Default</TD>
<TD>";bf$;"Null</TD>"
'<TD>";bf$;"Primary</TD>
html "</TR>"

for i = 1 to addFields
    html "<TR><TD align=center>";bf$;i;"</TD><TD>"
    fieldName.i$    = "#fieldName";i
    TEXTBOX #fieldName.i$   ,fieldName$,20
    preFieldName$   = fieldName$    'previous field name
    html "</TD><TD>"
    if acd$ <> "Delete" then
        if fieldType$ = "" then fieldType$ = "VARCHAR"
    end if
    type$(1)    = fieldType$
    fieldType.i$    = "#fieldType";i
    listbox #fieldType.i$,  type$(),1

    html "</TD><TD align=center>"
    fieldLen.i$ = "#fieldLen";i
    TEXTBOX #fieldLen.i$,   fieldLen$,3

    html "</TD><TD align=center>"
    fieldDec.i$ = "#fieldDec";i
    TEXTBOX #fieldDec.i$,   fieldDec$,3

    html "</TD><TD>"
    fieldDef.i$ = "#fieldDef";i
    TEXTBOX #fieldDef.i$,   fieldDef$,40

    html "</TD><TD>"
    if fieldDef$ <> "" then fieldNull$ = "NOT NULL"
    if fieldNull$ = "NOT NULL" then
        null$(1) = "NOT NULL"
        null$(2) = "NULL"
        else
        null$(1) = "NULL"
        null$(2) = "NOT NULL"
    end if
    fieldNull.i$    = "#fieldNull";i
    listbox #fieldNull.i$,  null$(),1

'html "</TD><TD>"
'if fieldKey$ = "KEY" then 
'pk$(1) = "PRIMARY KEY"
'pk$(2) = ""
'else
'pk$(2) = "PRIMARY KEY"
'pk$(1) = ""
'end if
'fieldKey.i$ = "fieldKey";i
'listbox #fieldKey.i$,pk$(),1
    html "</TD></TR>"
next i

html "<TR><TD COLSPAN=7 BGCOLOR=#FFCC99 ALIGN=CENTER>"
button	#acdField, acd$, [doAcdField]
	#acdField cssclass("sBtn")
	#acdField setkey(fieldName$)
if acd$ = "Delete" then html " ";bf$;fieldName$
html " "
button    #exit, "Exit", [shoFields1]
        #exit cssclass("sBtn")
html "</TD></TR></TABLE>"

wait

[doAcdField]
errNum  = 0
html "</center>"
fields$ 	= strRep$(fields$,","," ,")
fieldName$ 	= " " + fieldName$ + " "
lfields$    	= lower$(fields$)
oldFieldName$   = fieldName$
preFieldName$	= " " + trim$(preFieldName$) + " "

if acd$ = "Delete" then goto [doAlter]  ' no need to check stuff on Delete

cma$		= ""
newField$	= ""
for i = 1 to addFields
    fieldName.i$	= "#fieldName";i
    fieldName$		= trim$(#fieldName.i$ contents$())

    fieldType.i$	= "#fieldType";i
    fieldType$		= #fieldType.i$ selection$()

    fieldLen.i$		= "#fieldLen";i
    fieldLen$		= #fieldLen.i$ contents$()

    fieldDec.i$		= "#fieldDec";i
    fieldDec$		= #fieldDec.i$ contents$()

    fieldDef.i$		= "#fieldDef";i
    fieldDef$		= trim$(#fieldDef.i$ contents$())
    fieldDef$		= strRep$(fieldDef$,"''","'")

'fieldKey.i$ = "#fieldKey";i
'fieldKey$ = #fieldKey.i$ selection$()

    for j = 2 to 28
        if fieldType$   = type$(j) then
            if typeVal$(j) = "I" then
                if fieldDef$ <> "" then
                    fdef        = val(fieldDef$)
                    fieldDef$   = str$(fdef)
                end if
                fdec        = val(fieldDec$)
                if fdec = 0 then 
                    fieldDec$   = ""
                    else
                    fieldDec$   = str$(fdec)
                end if
                flen        = val(fieldLen$)
                if flen < fdec then flen = fdec + 1
                if flen = 0 then 
                    fieldLen$   = ""
                    else
                    fieldLen$   = str$(flen)
                end if
            end if
        goto [endFd]
        end if
    next j
    [endFd]
    fieldNull.i$	= "#fieldNull";i
    fieldNull$  	= #fieldNull.i$ selection$()
    if fieldNull$	= "NULL" then fieldDef$ = ""

if fieldName$ <> "" then
    a$  = ""
    b$  = upper$(fieldName$)
    if left$(b$,1) < "A" then
        errNum  = errNum + 1
        errMsg$(errNum) = "Field Name ";i;" must start with a character"
    end if

    if left$(b$,1) > "Z" then
        errNum  = errNum + 1
        errMsg$(errNum) = "Field Name ";i;" must start with a character"
    end if

    for ii = 1 to len(b$)
        if mid$(b$,ii,1) = "_" then goto [charOk]
        if mid$(b$,ii,1) >= "A" and mid$(b$,ii,1) <= "Z" then goto [charOk]
        if mid$(b$,ii,1) >= "0" and mid$(b$,ii,1) <= "9" then goto [charOk]
        errNum  = errNum + 1
        errMsg$(errNum) = "Field name ";i;" has invalid character:";mid$(fieldName$,ii,1)

        [charOk]
        a$  = a$ + mid$(fieldName$,ii,1)
    next ii
    fieldName$	= " " + trim$(a$) + " "
    if preFieldName$ <> fieldName$ then     ' if name changed then it must be unique

a$ = " " + a$ + " "

        a$  = lower$(fieldName$)
        if instr(lfields$,a$) > 0 then
            errNum  = errNum + 1
            errMsg$(errNum) = "Field Name ";i;" ";fieldName$;" already used"
        end if
    end if

if fieldLen$ <> "" then
    x$ = fieldLen$
    if fieldDec$ <> "" then x$ = x$ + "," + fieldDec$
    fieldType$ = fieldType$ + "(" + x$ + ")"
end if

if fieldDef$ <> "" then fieldDef$ = "DEFAULT " + "'" + fieldDef$ + "'"
newField$   = newField$ + cma$ + fieldName$ + " " + fieldType$ + " " + fieldNull$ + " " + fieldDef$
cma$    = ",  "
end if
next i
if errNum > 0 then 
    gosub [doMsg]
wait
end if
' ----------------------------------------------------------------------------------
' Alter Database
' SQLite does not have a ALTER command. Therefore we have to create a temp
' table like the original, copy the database, make a new table with the ALTERations
' and copy the tamp back. What a bunch of crap.
' ----------------------------------------------------------------------------------
[doAlter]
on error goto [rollMeBack]
sqliteconnect   #sql1, lf$
#sql1 execute("BEGIN TRANSACTION")

if acd$ = "Add At End" then
' can only add one field at a time
    i = 1
    while   word$(newField$,i,",  ") <> ""
        sql1$   = "ALTER TABLE ";tbl$;" add ";word$(newField$,i,",  ")
        infoNum = infoNum + 1
        infoMsg$(infoNum)   = "Add at End sql1:";sql1$
        #sql1 execute(sql1$)
        i = i + 1
    WEND
goto [commit]
wait
end if

' eleminate all defaults because the old systems put garbage there
createTmp$ = strRep$(createTmp$,chr$(34),"")
createTmp$ = strRep$(createTmp$,"\","")
createTmp$ = strRep$(createTmp$," '' ","||")
createTmp$ = strRep$(createTmp$,"''","")
createTmp$ = strRep$(createTmp$,"||","''")
createTmp$ = strRep$(createTmp$,"  "," ")
createTmp$ = strRep$(createTmp$,"  "," ")
createTmp$ = strRep$(createTmp$,"default ,"," ,")
createTmp$ = strRep$(createTmp$,"DEFAULT ,"," ,")
a1$ = word$(createTmp$,1,", PRIMARY KEY")
if a1$ <> "" then
    a2$ = word$(createTmp$,2,", PRIMARY KEY")
    a3$ = word$(a2$,2,")")
    createTmp$ = a1$ + a3$
end if

'sql1$ = "CREATE TABLE __";tbl$;"__ " + createTmp$ 
sql1$ = "CREATE TABLE __";tbl$;"__ " + "(" + fields$ + ")" 

'print "1 ";sql1$
#sql1 execute(sql1$)
'print "2 ";sql1$
sql1$   = "INSERT INTO __";tbl$;"__ SELECT * FROM ";tbl$
'print "3 ";sql1$
#sql1 execute(sql1$)
sql1$ = "DROP TABLE ";tbl$ 
'print "DROP ";sql1$
'input a
#sql1 execute(sql1$)
'print "DROPPED : ";acd$
'input xx
tfields$    = fields$   ' To Fields

' ----------------------------------
' Change a field
' ----------------------------------
if acd$ = "Change" then
    tfields$    = strRep$(fields$,preFieldName$,fieldName$) ' old filed to new field
    a1$         = word$(fields$,2,preFieldName$)               ' get field name after changed field
    a1$         = " " + trim$(word$(a1$,2,",")) + " "       ' next field in list

'print "fields:";fields$
'print "preFieldName:";preFieldName$
'print "fieldName:";fieldName$
'print "newField:";newField$
'print "a1:";a1$
'print "createTmp:";createTmp$
'input x
    a2$ = word$(createTmp$,1,preFieldName$) + newField$
'print "a2:";a2$

    if trim$(a1$) = "" then
            a2$ = a2$ + ")"     ' if it is at the end
              else
            a2$ = a2$ + "," + a1$ + word$(createTmp$,2,a1$)
    end if
'print "a2:";a2$
a2$ = strRep$(a2$,"default''","default '' ")
a2$ = strRep$(a2$,"DEFAULT''","DEFAULT '' ")
a2$ = strRep$(a2$,"'' ","|")

a2$ = strRep$(a2$,"''","'")
a2$ = strRep$(a2$,"|","''")

    sql1$   = "CREATE TABLE ";tbl$;" ";a2$
'print "4 ";sql1$
'input xx
end if

if left$(acd$,6) = "Add b4" then
    a1$ = word$(createTmp$,1,b4Field$)
    a2$ = newField$ +  " , " + b4Field$ + word$(createTmp$,2,b4Field$)
    sql1$ = "CREATE TABLE ";tbl$;" ";a1$;a2$ 
end if
'print "preFieldName:";preFieldName$
'print "fieldName:";fieldName$
'print "newField:";newField$
'print "a1:";a1$

'print "createTmp:";createTmp$
'print "sql1:";sql1$
'input xx

' ------------------------------
' delete
' ------------------------------
if acd$ = "Delete" then
    a1$ = word$(fields$,2,fieldName$)       ' get field name after deleted field
    a1$ = " " + trim$(word$(a1$,2,",")) + " "       ' next field in list
    a2$ = trim$(strRep$(fields$,fieldName$,"")) ' eliminate deleted field
    a2$ = strRep$(a2$,", ,",",")    ' eliminate deleted field
    if left$(a2$,1) = "," then a2$ = mid$(a2$,2)
    a = len(a2$)
    if mid$(a2$,a,1) = "," then a2$ = left$(a2$,a-1)
    fields$ = a2$
    tfields$ = a2$
    if trim$(a1$) = "" then
        a2$ = word$(createTmp$,1,fieldName$)
            else
        a2$ = word$(createTmp$,1,fieldName$) + " " + a1$ + " " + word$(createTmp$,2,a1$)
    end if
    a2$ = trim$(a2$)
    a = len(a2$)
    if mid$(a2$,a,1) = "," then a2$ = left$(a2$,a-1) + ")"
    sql1$   = "CREATE TABLE ";tbl$;" ";a2$

'print "a1:";a1$
'print "a2:";a2$
'print "sql1:";sql1$
'print "fieldName";fieldName$

'print "b4 index:";index$

    
    index$  = strRep$(index$,"("," ( ")
    index$  = strRep$(index$,")"," ) ")
    index$  = strRep$(index$,","," , ")
    index$  = strRep$(index$,fieldName$,"")
    index$  = strRep$(index$,"  "," ")
    index$  = strRep$(index$,"  "," ")
'print "-- index:";index$
    index$  = strRep$(index$,", )",")")
    index$  = strRep$(index$,"( ,","(")
    index$  = strRep$(index$,", ,",",")
    index$  = strRep$(index$,",,",",")
    index$  = strRep$(index$,"(,","(")
    index$  = strRep$(index$,",)",")")
'print "aft index:";index$

    fields$ = trim$(strRep$(fields$,fieldName$,""))
    fields$ = strRep$(fields$,",,",",")
    if left$(fields$,1) = "," then fields$ = mid$(fields$,2)
    ii  = len(fields$)
    if mid$(fields$,ii,1) = "," then fields$    = left$(fields$,ii - 1)
    tfields$    = fields$
end if
'print "fields:";fields$
#sql1 execute(sql1$)
sql1$   = "INSERT INTO ";tbl$;" (";tfields$;") SELECT ";fields$;" FROM __";tbl$;"__"
'print "5 ";sql1$
#sql1 execute(sql1$)
'input xxx
i = 1
while word$(index$,i,"|") <> ""
    sql1$   = word$(index$,i,"|")
    a$  = trim$(word$(sql1$,2,"("))
' print "INDEX a: ";a$
' print "INDEX sql1:";sql1$
' input xx
    if a$ <> ")" then #sql1 execute(sql1$)
    i = i + 1
WEND
sql1$ = "DROP TABLE __";tbl$;"__"
#sql1 execute(sql1$)
[commit]
#sql1 execute("COMMIT TRANSACTION")
#sql1 disconnect()
goto [shoFields1]
wait

[rollMeBack]
#sql1 execute("ROLLBACK TRANSACTION")
#sql1 disconnect()
errNum  = errNum + 1
errMsg$(errNum) = "SQLite problem on: ";acd$
errNum  = errNum + 1
errMsg$(errNum) = "Restoring Database"
gosub [doMsg]
wait
' -------------------------------
' Add index
' -------------------------------
[addIdx]
 html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
 html "<TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>";bf$;"Add Index</TD></TR>"
 html "<TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>";bf$;"Index Name</TD><TD>"
    radiogroup #typeIndex, "Index, Unique", "Index"
               #typeIndex horizontal(1)
    TEXTBOX #idxName ,tbl$;"_",30

 html "</TD></TR><TR><TD COLSPAN=2 align=center>"
 html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0><TR align=center BGCOLOR=#FFCC99>"
 html "<TD>";bf$;"Seq</TD><TD>";bf$;"Name</TD><TD>";bf$;"Desc</TD></TR>"
 
i   = 1
while word$(fields$,i,",") <> ""
 html "<TR ALIGN=CENTER><TD>"
    seqIdx.i$   = "#seqIdx";i
    TEXTBOX #seqIdx.i$  ,"",3
 html "</TD><TD>";bf$;word$(fields$,i,",")
 html "</TD><TD>"
    descIdx.i$  = "#descIdx";i
    CHECKBOX #descIdx.i$, "", 0
html "</TD></TR>"
i = i + 1
WEND
numFields   = i - 1
 
 html "</TABLE></TD></TR><TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>"
button    #addIdx, "Add", [doAddIdx]
        #addIdx cssclass("sBtn")
html " "
button    #exit, "Exit", [shoFields1]
        #exit cssclass("sBtn")
 html "</TD></TR></TABLE>"
wait

[doAddIdx]
typeIndex$  = #typeIndex selection$()
idxName$    = trim$(#idxName contents$())
sqliteconnect   #sql1, lf$
sql1$   = "SELECT name FROM sqlite_master WHERE name = '";idxName$;"'"  
#sql1 execute(sql1$)
rows    = #sql1 ROWCOUNT()       'Get the number of rows
#sql1 disconnect()
if rows > 0 then
    errNum  = errNum + 1
    errMsg$(errNum) = "Name :";idxName$;" already exist"
    gosub [doMsg]
    wait
end if

dim aFieldNames$(numFields)
idxCount    = 0
for i = 1 to numFields
    seqIdx.i$   = "#seqIdx";i
    seqIdx  = val(#seqIdx.i$ contents$())

    if seqIdx > 0 then
        a$  = ""
        descIdx.i$  = "#descIdx";i
        descIdx     = #descIdx.i$ value()
        if descIdx <> 0 then a$ = " DESC"
        aFieldNames$(seqIdx)    = word$(fields$,i,",") + a$
        idxCount    = idxCount + 1
    end if
next i
if idxCount = 0 then
    errNum  = errNum + 1
    errMsg$(errNum) = "Please select at least one field to be indexed"
    gosub [doMsg]
    wait
end if

cma$    = ""
if typeIndex$ = "Index" then
    a$  = "CREATE INDEX "
      else
    a$  = "CREATE UNIQUE INDEX "
end if
a$  = a$;idxName$;" ON ";tbl$;"("
for i = 1 to numFields
    if aFieldNames$(i) <> "" then 
        a$  = a$ + cma$ + aFieldNames$(i)
        cma$    = ","
    end if
next i
sql1$   = a$ + ")"
on error goto [rollMeBack]
sqliteconnect   #sql1, lf$
#sql1 execute("BEGIN TRANSACTION")
#sql1 execute(sql1$)
#sql1 execute("COMMIT TRANSACTION")
#sql1 disconnect()
infoNum = infoNum + 1
infoMsg$(infoNum) = sql1$;" Successful"
on error goto [handler]
goto [shoFields1]
wait

' -------------------------------
' Delete index
' -------------------------------
[delIdx]
idxName$    = EventKey$
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER>";bf$;"Delete Index</TD></TR>"
html "<TR><TD ALIGN=CENTER>";bf$;idxName$
html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER>"
button    #delIdx, "Delete", [doDelIdx]
        #delIdx cssclass("sBtn")
        #delIdx setkey(idxName$)
if acd$ = "Delete" then html " ";bf$;fieldName$
html " "
button    #exit, "Exit", [shoFields1]
        #exit cssclass("sBtn")
html "</TD></TR></TABLE>"
wait

[doDelIdx]
sqliteconnect   #sql1, lf$
sql1$   = "DROP INDEX ";idxName$
#sql1 execute(sql1$)
#sql1 disconnect()
infoNum = infoNum + 1
infoMsg$(infoNum) = "INDEX: ";idxName$;" Deleted"
goto [shoFields1] 
[chgIdx]
' ------------------------------
' Load / Create Database
' ------------------------------
[createDb]
acd$    = "Create"
goto [acdDb]
[addDb]
acd$    = "Load"
[acdDb]
gosub [heading]
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD COLSPAN=2 BGCOLOR=#FFCC99 align=center>";bf$;acd$;" Database</TD></TR>"
html "<TR><TD BGCOLOR=#FFCC99>";bf$;"Name</B></TD><TD>"
TEXTBOX #dbName, "",10

html "</TD></TR><TR><TD BGCOLOR=#FFCC99 align=right>";bf$;"File</TD><TD>"
TEXTBOX #dbFile, "",30
html "</TD></TR><TR>"

html "<TD COLSPAN=2 BGCOLOR=#FFCC99 align=center>";bf$
button    #addDb, acd$, [addDbFile]
        #addDb cssclass("sBtn")
html " "
button    #exit, "Exit", [main]
        #exit cssclass("sBtn")

html "</TD></TR>"
html "</TABLE>"
wait

[addDbFile]
dbName$ = trim$(#dbName contents$())
dbFile$ = trim$(#dbFile contents$())

if left$(Platform$,3) = "win" then
	dbFile$ = strRep$(dbFile$,"/","\")  ' convert fwd slash to back slash
	typeSlsh$	= "\"
 else
	typeSlsh$	= "/"
end if

' seperate directory from file
i   = 1
a$  = ""
a1$ = ""
slsh$   = ""
while word$(dbFile$,i,typeSlsh$) <> ""
    a1$ = a$
    a$  = a$ + slsh$ + word$(dbFile$,i,typeSlsh$)
    slsh$   = typeSlsh$
    i = i + 1
WEND
dbLoc$  = a1$ + typeSlsh$
dbFile$ = word$(dbFile$,i - 1,typeSlsh$)
addDb$  = dbLoc$;dbFile$

sqliteconnect   #sql, sqliteDb$
sql$    = "SELECT * FROM db WHERE dbName = '";dbName$;"' OR (dbLoc = '";dbLoc$;"' AND dbFile = '";dbFile$;"')"
#sql execute(sql$)
rows    = #sql ROWCOUNT()    'Get the number of rows
#sql disconnect()
if rows > 0 then 
    errNum  = errNum + 1
    errMsg$(errNum) = "DB Name and/or DB File already on DB file"
    gosub [doMsg]
    wait
end if

on error goto [loadErr]
files   #f, addDb$
if acd$ = "Load" then
    if  #f HASANSWER() = 0 then     ' does file exist
        goto [loadErr]
    end if
    goto [doAddDb]
end if

on error goto [createErr]
if acd$ = "Create" then
    if  #f HASANSWER() <> 0 then    ' does file exist
        goto [createErr]
    end if
    sqliteconnect   #sql, addDb$
    sql$    = "CREATE TABLE t1(x CHAR(1))" 
    #sql execute(sql$)
    sql$    = "DROP TABLE t1"
    #sql execute(sql$)
    #sql disconnect()
    infoNum = infoNum + 1
    infoMsg$(infoNum) = "New SQLlite3 database ";addDb$;" created"
end if

[doAddDb]
dbNames$    = "dbName,dbFile,dbLoc"
dbVals$     = "'";dbName$;q$;dbFile$;q$;dbLoc$;"'"
sql$        = "INSERT into db ("; dbNames$; ") VALUES ("; dbVals$ ; ")"
sqliteconnect   #sql, sqliteDb$
#sql execute(sql$)
#sql disconnect()
goto [main]
wait
[loadErr]
errNum  = errNum + 1
addDb$  = strRep$(addDb$,"\","\\")
errMsg$(errNum) = "Cannot Load ";addDb$;" not on file"
gosub   [doMsg]
wait

[createErr]
errNum  = errNum + 1        ' yes it does
addDb$  = strRep$(addDb$,"\","\\")
errMsg$(errNum) = "Cannot Create ";addDb$;" already on file"
gosub   [doMsg]
wait

' ---------------------------------
' Delete Database from management
' ---------------------------------
[deleteDb]
dbName$ = EventKey$
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER>";bf$;"Delete Database:";bh$;dbName$;"</TD></TR>"
html "<TR><TD>This will delete the database from your management list,<BR>but not the database file. </TD></TR>"
html "<TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER>"

button    #addtbl, "Delete",[doDeleteDb]
        #addtbl cssclass("sBtn")
html " "
button    #exit, "Exit", [main]
        #exit cssclass("sBtn")
html "</TD></TR></TABLE>"
wait
[doDeleteDb]
sqliteconnect   #sql, sqliteDb$            ' Connect to the DB
sql$    = "DELETE FROM db WHERE dbName = '";dbName$;"'"
#sql execute(sql$)
#sql disconnect()
goto [main]

' ------------------------
' Add a table
' ------------------------
[addTbl]
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>";bf$;"Add New Table to Database:";bh$;dbName$
html "</TD></TR><TR><TD BGCOLOR=#FFCC99 align=right>";bf$;"Table Name</TD><TD>"
TEXTBOX #tableName, "",30
html "</TD></TR><TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>"
button	#addTbl, "Add", [doAddTbl]
	#addTbl cssclass("sBtn")
html " "
button	#exit, "Exit", [shoTables1]
	#exit cssclass("sBtn")

html "</TD></TR></TABLE>"

wait
[doAddTbl]
tableName$  = trim$(#tableName contents$())
if tableName$   = "" then
    errNum  = errNum + 1
    errMsg$(errNum) = "Please Specify a Table Name"
end if

if instr(tbls$,tableName$) > 0 then
    errNum  = errNum + 1
    errMsg$(errNum) = "Table already exists"
end if
if errNum > 0 then 
    gosub [doMsg]
    wait
end if

sqliteconnect   #sql1, lf$

sql1$ = "create table ";tableName$;" (x)"
#sql1 execute(sql1$)
#sql1 disconnect()
infoNum = infoNum + 1
infoMsg$(infoNum) = "Table ";tableName$;" created"
goto    [shoTables1]
wait

' ------------------------
' Rename Table
' ------------------------
[renTbl]
tableName$ = EventKey$
html bf$;"<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0><TR>"
html "<TD BGCOLOR=#FFCC99 ALIGN=CENTER>Rename From</TD><TD>";tableName$;"</TD>"
html "</TR><TR>"
html "<TD BGCOLOR=#FFCC99 align=right>";"Rename To</TD><TD>"
TEXTBOX #renm, "",30
html "</TD></TR><TR><TD BGCOLOR=#FFCC99 align=center colspan=2>"

button	#renTbl, "Rename", [doRenTbl]
	#renTbl cssclass("sBtn")
html " "
button	#exit, "Exit", [shoTables1]
	#exit cssclass("sBtn")
html "</TD></TR></TABLE>"
wait

[doRenTbl]
renName$  = trim$(#renm contents$())
print "at [doRenTbl] to:";renName$
sqliteconnect   #sql1, lf$
sql1$	= "SELECT * FROM sqlite_master WHERE type='table' and tbl_name = '";renName$;"'"
#sql1 execute(sql1$)
rows    =   #sql1 ROWCOUNT()   'Get the number of rows
if rows > 0 then
	errNum  = errNum + 1
	errMsg$(errNum) = "Table ";renName$;" already used"
	gosub [doMsg]
	wait
end if

sql1$ = "ALTER TABLE ";tableName$;" RENAME TO ";renName$
#sql1	execute(sql1$)

infoNum = infoNum + 1
infoMsg$(infoNum) = "Table ";tableName$;" Renamed to ";renName$
goto    [shoTables1]

' ------------------------
' Drop a table
' ------------------------
[dropTbl]
tableName$ = EventKey$
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD>";bf$;"Drop table:";bh$;tableName$;bf$;" for sure</TD>"
html "</TR><TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER>"
button    #addTbl, "Drop", [doDropTbl]
        #addTbl cssclass("sBtn")
html " "
button    #exit, "Exit", [shoTables1]
        #exit cssclass("sBtn")
html "</TD></TR></TABLE>"
wait

[doDropTbl]
sqliteconnect   #sql1, lf$
sql1$ = "drop table ";tableName$
#sql1 execute(sql1$)
#sql1 disconnect()
infoNum = infoNum + 1
infoMsg$(infoNum) = "Table ";tableName$;" Dropped"
goto    [shoTables1]
wait
' ------------------------
' Empty a table
' ------------------------
[mtTbl]
tableName$ = EventKey$
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD>";bf$;"Empty table:";bh$;tableName$;bf$;" for sure</TD>"
html "</TR><TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER>"
button    #addTbl, "Empty", [domtTbl]
        #addTbl cssclass("sBtn")
html " "
button    #exit, "Exit", [shoTables1]
        #exit cssclass("sBtn")
html "</TD></TR></TABLE>"
wait

[domtTbl]
sqliteconnect   #sql1, lf$
sql1$ = "DELETE FROM ";tableName$
#sql1 execute(sql1$)
#sql1 disconnect()
infoNum = infoNum + 1
infoMsg$(infoNum) = "Table ";tableName$;" emptied"
goto    [shoTables1]
wait

' ---------------------------------
' Query Table
' ---------------------------------
[queryTbl]
tableName$ = EventKey$
tblFields$  = ""
i = 1
numQry  = 0
cma$    = ""
sqliteconnect   #sql1, lf$
sql1$   = "SELECT *  FROM ";tableName$;" limit 1"
#sql1 execute(sql1$)
colNames$   = #sql1 columnnames$()
j = 1
while word$(colNames$,j,",") <> ""
    tblFields$  = tblFields$ + cma$ + tbl$ + "." + trim$(word$(colNames$,j,","))
    numQry  = numQry + 1
    cma$    = ","
    j = j + 1
WEND
#sql1 disconnect()

dim ddFields$(numQry)

i   = 1
while word$(tblFields$,i,",") <> ""
    ddFields$(i)    = word$(tblFields$,i,",")
    i = i + 1
WEND
qry$    = "SELECT * FROM ";tableName$;" LIMIT 0,20"

[queryTbl1]
gosub [heading]
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>";bf$;"Database:";bh$;dbName$;" Time:";time$();"</TD></TR><TR><TD>"
textarea #query, qry$ ,80,20

html "</TD><TD align=center valign=top>";bf$;"Table.Name<BR>"
listbox #ddFields,ddFields$(),15
        #ddFields setid("ddFields")
html "</TD></TR><TR><TD COLSPAN=2>";bf$
html "On SELECTS if you do not specify a LIMIT the system defaults to LIMIT 0,20<BR>"
html "Remember that SQL commands will not allow double qoutes ("") only single quotes (')<BR>"
html "If you do multiple INSERTS or CREATES seperate each statement with semicolons (;)"

html "</TD></TR><TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>"
button	#queryExec, "Execute", [queryExec]
        #queryExec cssclass("sBtn")
html " "
button	#exit, "Exit", [shoTables1]
	#exit cssclass("sBtn")
html "</TD></TR></TABLE></CENTER>"
wait
' ---------------------------------
' Execute query statements
' ---------------------------------

[queryExec]
sql1$     = #query contents$()
sql1$ = trim$(sql1$)
if sql1$ = "" then 
' errNum = errNum + 1
' errMsg$(errNum) = "Nothing to execute"
' gosub [doMsg] 
wait
end if

sqliteconnect   #sql1, lf$
begTimeSec	= time$("seconds")
begTimeMil	= time$("milleseconds")
' Not a select statement then see if there is more
if lower$(left$(sql1$,6)) <> "select" then  
    a1$ = sql1$ + ";_END_;"
    ii = 1
    while word$(a1$,ii,";") <> "_END_"
        sql1$   = trim$(word$(a1$,ii,";"))
if asc(sql1$) < 14 then sql1$ = mid$(sql1$,1)
print sql1$
    p0 = 1
    p3$ = ""
    while word$(sql1$,p0,chr$(10)) <> ""
        p1$ = word$(sql1$,p0,chr$(10))
        p0  = p0 + 1
        a$ = trim$(p1$)
        if left$(a$,1) <> "#" then
'p   = instr(p1$,"#")
'if p > 0 then p1$ = left$(p1$,p - 1)
      
      p3$ = p3$ + p1$ + " "
	end if
    WEND 
    sql1$   = trim$(p3$)            
        if len(sql1$) > 7 then #sql1 execute(sql1$)
    ii = ii + 1
    WEND
    #sql1 disconnect()
print "---------------- Query Complete ------------------------"
'infoNum = infoNum + 1
'infoMsg$(infoNum) = "================== QUERY END ====================="
'gosub [doMsg]
    wait
end if

'ON ERROR GOTO [queryBad]
#sql1 execute(sql1$)
rows        = #sql1 ROWCOUNT()       'Get the number of rows
colNames$   = #sql1 columnnames$()
colHdng$    = ""
sep$        = "<TR align=center BGCOLOR=#FFCC99><TD>";bf$
nc = 1      ' get column headings
while word$(colNames$,nc,",") <> ""
        colHdng$    = colHdng$ + sep$ + word$(colNames$,nc,",")
        nc = nc + 1
        sep$    = "</TD><TD>";bf$
WEND
nc  = nc - 1
cls
html ba$
    html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
    html "<TR BGCOLOR=#FFCC99 ALIGN=CENTER><TD colspan=";nc;">";bf$;"Database:";bh$;dbName$;"</TD></TR>";colHdng$;"</TD></TR>"

    numRows = 0
    WHILE   #sql1 hasanswer()
        result1$    = #sql1 nextrow$(" |")
        sep$    = "<TR><TD>"
        for i = 1 to nc
            html sep$;trim$(word$(result1$,i,"|"))
            sep$    = "</TD><TD>"
        next i
        html    "</TD></TR>"
        numRows = numRows + 1
        if numRows > 50 then goto [queryExit]   ' just in case they accidently ask for infinity
    WEND
    [queryExit]
    #sql1 disconnect()
    html "<TR BGCOLOR=#FFCC99 align=center>"
    html "<TD colspan=";nc;">"    
    button	#queryGen, "Gen RB Code", [queryGen]
            	#queryGen cssclass("sBtn")
    html " "
    button	#query, "Query", [queryTbl1]
            	#query cssclass("sBtn")
    html " "
    button   	#exit, "Exit", [shoTables1]
          	#exit cssclass("sBtn")
    html "</TD></TR></TABLE>"
    html "Run Time Seconds:";time$("seconds") - begTimeSec
    html " MilleSec:";time$("milleseconds") -   begTimeMil
    wait

[queryGen]
a$ = lower$(trim$(sql1$))
if left$(a$,6) <> "select" then
    wrnNum  = wrnNum + 1
    wrnMsg$(wrnNum) = "Can only generate Run Basic Code on SELECT queries"
    gosub [doMsg]
wait
end if
html "<HR><CENTER>Copy and past the following code</center><HR>"
print "dbName$ =""";dbName$;""""
print "sqliteconnect    #sql1, """;lf$;""""
print "sql1$ = """;sql1$;""""
print "#sql1 execute(sql1$)"
print "rows = #sql1 ROWCOUNT()       'Get the number of rows"
print "colNames$ = #sql1 columnnames$()"
print "colHdng$ = """""
print "sep$ = ""<TR align=center BGCOLOR=#FFCC99><TD>"""
print "nc = 1"
print "while word$(colNames$,nc,"","") <> """""
print " colHdng$    = colHdng$ + sep$ + word$(colNames$,nc,"","")"
print " nc = nc + 1"
print " sep$    = ""</TD><TD>"""
print "WEND"

print "nc   = nc - 1"
print "cls"
print "html ""<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"""
print "html ""<TR BGCOLOR=#FFCC99 ALIGN=CENTER><TD colspan="";nc;"">Database:"";dbName$;""</TD></TR>"";colHdng$;""</TD></TR>"""
print "WHILE    #sql1 hasanswer()"
print "	#row = #sql1 #nextrow()"
for i = 1 to nc
fieldName$  = trim$(word$(colNames$,i,","))
print fieldName$;"$ = #row ";fieldName$;"$()"
next i

print "html ""<TR>"""
for i = 1 to nc
fieldName$  = trim$(word$(colNames$,i,","))
print "html ""<TD>"";";fieldName$;"$;""</TD>"""
next i
print "html ""</TR>"""
print "WEND"
print "html ""</TABLE>"""
print "#sql1 disconnect()"
print "wait"
wait

[queryBad]
#sql1 disconnect()
errNum  = errNum + 1
errMsg$(errNum) = "Invalid SQL query ";sql1$
gosub [doMsg]
wait
' ---------------------------------
' Browse Table
' ---------------------------------
[browseTbl]
tableName$	= EventKey$
limitBeg	= 0
shoRows		= 20
pageNum		= 0
xpLst$		= "No"

[doBrowse]
limitBeg	= max(0,limitBeg)  ' can not be less than zero
sqliteconnect   #sql1, lf$
sql1$		= "SELECT *  FROM ";tableName$;" limit 1"
#sql1 execute(sql1$)
colNames$	= "Maint," + #sql1 columnnames$()
colHdng$	 = ""
sep$        = "<TR align=center BGCOLOR=#FFCC99><TD>";bf$
nc = 1
while word$(colNames$,nc,",") <> ""
    colHdng$    = colHdng$ + sep$ + word$(colNames$,nc,",")
    nc = nc + 1
    sep$    = "</TD><TD>";bf$
WEND
'nc = nc - 1
sql1$ = "SELECT count(*) FROM ";tableName$
#sql1 execute(sql1$)
result1$    = #sql1 nextrow$(" |")
numRows     = val(word$(result1$,1,"|"))

hdng$   = "<CENTER><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>
<TR BGCOLOR=#FFCC99 ALIGN=CENTER><TD colspan=";nc;">"
hdng1$ =  "  ";bf$;"Database:";bh$;dbName$;bf$;" Table:";bh$;tableName$;bf$;" Rows:";bf$;numRows;"</TD></TR>";colHdng$;"</TD></TR>"
#sql1 disconnect()
[doBrowse1]
cls
html ba$
gosub [heading]
sqliteconnect   #sql1, lf$
html hdng$
    button	#xpl, "Expand", [xpLst]
        #xpl cssclass("sBtn")
        #xpl setkey(tableName$)
html xpLst$;" "
    button	#addDtl, "Add", [addDtl]

html hdng1$
sql1$   = "SELECT rowid,* FROM '";tableName$;"' LIMIT ";limitBeg;",";shoRows

#sql1 execute(sql1$)
WHILE   #sql1 hasanswer()
    result1$    = #sql1 nextrow$(" |")
    for i = 1 to nc
        if i = 1 then
          html "<TR><TD align=center>"
          recid$    = word$(result1$,i,"|")
          button  #addlDtl, "A", [addlDtl]
                #addlDtl cssclass("sBtn")
                #addlDtl setkey(recid$)
          button  #chgDtl, "C", [chgDtl]
                #chgDtl cssclass("sBtn")
                #chgDtl setkey(recid$)
          button  #delDtl, "D", [delDtl]
                #delDtl cssclass("sBtn")
                #delDtl setkey(recid$)
         else
           html "</TD><TD>"
           if xpLst$ = "No" then
           	html left$(word$(result1$,i,"|"),30)
           	else
           	html word$(result1$,i,"|")
       	   end if
        end if
    next i
    html    "</TD></TR>"
WEND
#sql1 disconnect()
html "<TR BGCOLOR=#FFCC99>
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 WIDTH=60% BGCOLOR=#FFCC99>
<TR><TD ALIGN=CENTER>";bf$
    button    #browseShow, "Show", [browseShow]
            #browseShow cssclass("sBtn")
html " "
    TEXTBOX #shoRows, shoRows,3

html "rows starting from record #"
limitBeg    = limitBeg + shoRows
    TEXTBOX #limitBeg, limitBeg,4
html "</TD><TD ALIGN=CENTER>";bf$
html "PageNumber"
pageNum = max(1,pageNum)
    TEXTBOX #pageNum, pageNum,3
html " "
button    #browseNext, "Next", [browseNext]
        #browseNext cssclass("sBtn")
html "  "
button    #browsePrev, "Prev", [browsePrev]
        #browsePrev cssclass("sBtn")
html "  "
button    #browseEnd, "Last", [browseEnd]
        #browseEnd cssclass("sBtn")
html "</TD><TD BGCOLOR=#FFBA75 ALIGN=CENTER>"
    button    #exit, "Exit", [shoTables1]
            #exit cssclass("sBtn")
html "</TD></TR></TABLE>"
html "</TD></TR></TABLE>"

wait
[browseNext]
addPage = 1
goto [browsePage]

[browsePrev]
addPage = -1

[browsePage]
pageNum     = val(#pageNum contents$())
pageNum     = pageNum + addPage
shoRows     = val(#shoRows contents$())
limitBeg    = shoRows * pageNum
if limitBeg + shoRows > numRows then limitBeg = numRows - shoRows
goto [doBrowse]

[browseEnd]
shoRows = val(#shoRows contents$())
limitBeg    = numRows - shoRows
pageNum     = int(numRows / limitBeg)
goto [doBrowse]

[browseShow]
shoRows     = val(#shoRows contents$())
limitBeg    = val(#limitBeg contents$())
if limitBeg + shoRows > numRows then limitBeg = numRows - shoRows
pageNum     = int(numRows / limitBeg)
goto [doBrowse]

' --------------------------------------
' expand browse list to full info
' --------------------------------------
[xpLst]
if xpLst$ = "Yes" then
   xpLst$ = "No"
   else
   xpLst$ = "Yes"
end if
addPage = 0
goto [browsePage]

' --------------------------------------
' Change / Delete detail Record
' --------------------------------------
[addDtl]
acd$    = "Add"
goto [doDtlAcd]
[addlDtl]
acd$    = "Add Like"
goto [doDtlAcd]
[chgDtl]
acd$    = "Change"
goto [doDtlAcd]
[delDtl]
acd$ = "Delete"
[doDtlAcd]
cls
html ba$
gosub [heading]
rowid$ = trim$(EventKey$)
sqliteconnect   #sql1, lf$
sql1$   = "SELECT * FROM sqlite_master WHERE type = 'table' and tbl_name = '";tableName$;"'"
#sql1 execute(sql1$)
result1$    = #sql1 nextrow$(" |")
theSql$     = trim$(word$(result1$,5,"|")) + ","    

sql1$   = "SELECT * FROM '";tableName$;"' WHERE rowid = ";rowid$
#sql1 execute(sql1$)
cns$        = #sql1 columnnames$()
result1$    = #sql1 nextrow$(" |")

html "<CENTER><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 bgcolor=wheat>"
html "<TR BGCOLOR=#FFCC99 ALIGN=CENTER><TD colspan=4>";bf$;"Database:";bh$;dbName$;bf$;" Table:";bh$;tableName$;"</TD></TR>"

i = 1
while word$(cns$,i,",") <> ""
    cn$ = word$(cns$,i,",")
    val$    = trim$(word$(result1$,i,"|"))
if acd$ = "Add" then val$ = ""
    t1$ = trim$(word$(theSql$,2,cn$))
    t2$ = trim$(word$(t1$,1,","))
    type$   = trim$(word$(t2$,1,"("))
    if type$ = "" then type$ = t2$
    t3$ = trim$(word$(t2$,2,"("))
    t3$ = trim$(word$(t3$,1,")"))

html "<TR><TD BGCOLOR=#FFCC99 align=right>";bf$;cn$
html "</TD><TD>"
    val.i$  = "#val";i
if lower$(type$) = "text" then
    textarea #val.i$, val$ ,30,2
     else
    textbox #val.i$, val$
end if
html "</td><td BGCOLOR=#FFCC99>";type$;"</td><td BGCOLOR=#FFCC99 align=center>";t3$
html "</td></tr>"
i = i + 1
WEND
nc = i - 1

#sql1 disconnect()
html "<TR BGCOLOR=#FFCC99 align=center><TD colspan=4>"
button    #dtlAcd, acd$, [dtlAcd]
        #dtlAcd cssclass("sBtn")
        #dtlAcd setkey(rowid$)
html " "
button    #exit, "Exit", [browseTbl]
        #exit cssclass("sBtn")
        #exit setkey(tableName$)

html "</TD></TR></TABLE>"
wait

' make detali ACD take place
[dtlAcd]
dbVals$ = ""
cma$    = ""
for i = 1 to nc
    val.i$  = "#val";i
    val$    = trim$(#val.i$ contents$())
    val$    = strRep$(val$,"'","''")
    dbVals$ = dbVals$ + cma$ + val$
    cma$    = "','"
next i
dbVals$ = "'" + dbVals$ + "'"
if acd$ = "Change" then
    sql1$   = "UPDATE " + tableName$ + " SET " + sqlSet$(cns$,dbVals$) + " WHERE rowid = '";rowid$;"'"
end if
if acd$ = "Delete" then
    sql1$   = "DELETE FROM " + tableName$ + " WHERE rowid = '";rowid$;"'"
end if
if acd$ = "Add" or acd$ = "Add Like" then
    sql1$   = "INSERT INTO " + tableName$ + " VALUES (" + dbVals$ + ")"
end if
on error goto [rollMeBack]
sqliteconnect   #sql1, lf$
#sql1 execute("BEGIN TRANSACTION")
#sql1 execute(sql1$)
#sql1 execute("COMMIT TRANSACTION")
#sql1 disconnect()
infoNum = infoNum + 1
infoMsg$(infoNum) = tableName$;" ";acd$;" Maintenance Successful"
on error goto [handler]
limitBeg    = limitBeg - shoRows
goto [doBrowse]
wait

' --------------------------------------
' Load CSV file to DB
' --------------------------------------
[loadCsv]
tbl$ = EventKey$
gosub [heading]

html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD BGCOLOR=#FFCC99 ALIGN=center colspan=2>";bf$;"load CSV to Table:";tbl$;"</TD><TD>"
html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Field Name</TD><TD>"
    TEXTBOX #csvFile, DefaultDir$ + "\public\",30
html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Fields terminated by</TD><TD>"
    TEXTBOX #fTrm, ",",3
html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Fields enclosed by</TD><TD>"
    TEXTBOX #fEnc, "'",3

html "</TD></TR><TR><TD colspan=2>";bf$;"First Row<BR>"
    radiogroup #fRow, "1 Use it for Field Names,2 Skip it,3 It is Data ", "1 Use it for Field Names"
            #fRow horizontal(0)

html "</TD></TR><TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>"
    button    #doLoadCsv, "Load CSV",[doLoadCsv]
            #doLoadCsv cssclass("sBtn")
    html " "
    button    #exit, "Exit",[shoTables1]
            #exit cssclass("sBtn")
            #exit setkey(tbl$)
html "</TD></TR></TABLE>"
wait

[doLoadCsv]
csvFile$ = trim$(#csvFile contents$())
fTrm$   = trim$(#fTrm contents$())
fEnc$   = trim$(#fEnc contents$())
fRow$   = left$(#fRow selection$(),1)

if csvFile$ = "" then
    errNum  = errNum + 1
    errMsg$(errNum) = "No input CSV file specified"
end if

on error goto [csvFileErr]
files   #f, csvFile$
if  #f HASANSWER() = 0 then     ' does file exist
    errNum  = errNum + 1
    errMsg$(errNum) = "CSV file ";csvFile$;" is not valid"
end if
on error goto [handler]
goto [csvChkNum]
[csvFileErr]
on error goto [handler]
errNum  = errNum + 1
errMsg$(errNum) = "CSV file ";csvFile$;" is not valid"

[csvChkNum]
on error goto [handler]
if errNum > 0 then
    gosub [doMsg]
    wait
end if

open csvFile$ for input as #f
if eof(#f) then
    close #f
    errNum = errNum + 1
    errMsg$(errNum) = "The file is empty"
    gosub [doMsg]
    wait
end if 

sep$    = fEnc$;fTrm$;fEnc$     ' seperation character
if fRow$ = "2" then line input #f, a$   ' skip first row

if fRow$ = "1" then             ' use as field names
    a$ = ""
    i = 1
    line input #f, a$
    fields$ = ""
    cma$    = ""
    l   = len(fEnc$)
    while word$(a$,i,sep$) <> ""
        fld$    = word$(a$,i,sep$)
        if i = 1 then fld$ = mid$(fld$,l + 1)       ' get rid of that first enclose char
        fields$ = fields$ + cma$ + fld$
        cma$    = ","
        i = i + 1
    WEND
    numFields = i - 1
    l1 = len(fields$)
    fields$ = left$(fields$,l1 - l)
end if

sqliteconnect   #sql1, lf$
if fNam = 0 then 
    sql1$   = "SELECT * FROM ";tbl$
    #sql1 execute(sql1$)
    fields$ = #sql1 columnnames$()
end if
sql1$   = "BEGIN TRANSACTION"
#sql1 execute(sql1$)
on error goto [rollMeBack]  
l   = len(fEnc$)

while not(eof(#f))
    line input #f, a$
    vals$   = ""
    cma$    = ""
    for i = 1 to numFields
        val$    = word$(a$,i,sep$)
        if i = 1 then val$ = "'" + mid$(val$,l + 1)     ' get rid of that first enclose char
        vals$   = vals$ + cma$ + val$
        cma$    = "','"
    next i
    l1 = len(vals$)
    vals$ = left$(vals$,l1 - l) + "'"
    sql1$ = "INSERT INTO ";tbl$;" (";fields$;") VALUES (";vals$;")"
    #sql1 execute(sql1$)
WEND
#sql1 execute("COMMIT TRANSACTION")
#sql1 disconnect()
close #f
infoNum = infoNum + 1
infoMsg$(infoNum) = "CSV File:";csvFile$;" loaded to:";tbl$
goto [shoTables]
wait

 ' ----------------------------------
 ' Export Database
 ' ----------------------------------
[exportDb]
a$  = EventKey$
dbName$ = word$(a$,1,"|")
lf$ = word$(a$,2,"|")
gosub [heading]

html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR BGCOLOR=#FFCC99>"
html "<TD >";bf$;"Export Database:";bh$;dbName$
html "</TD></TR>"
html "<TR><TD ALIGN=CENTER>"
    button    #exportCsv, "CSV",[exportCsv]
            #exportCsv cssclass("sBtn")
html " "
    button    #exportSql, "SQL",[exportSql]
            #exportSql cssclass("sBtn")

html "</TD></TR></TABLE>"
wait

' -------------------------------
' Export data as SQL
' -------------------------------
[exportSql]

html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD VALIGN=TOP>"
html "<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 WIDTH=100%>"
html "<TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER>Export</TD></TR><TR><TD>"

radiogroup #stuct, "1 Structure only,2 Structure and Data,3 Data only", "1 Structure only" 
        #stuct horizontal(0)

html "</TD></TR></TABLE></TD><TD>"
html "<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 WIDTH=100%>"
html "<TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER>Structure</TD></TR>"
html "<TR><TD>"
    CHECKBOX #drop, "Add Drop", 0
html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER>Data</TD></TR>"
html "<TR><TD>"
    CHECKBOX #cins, "Complete Inserts", 0
html "</TD></TR></TABLE></TD></TR>"
html "<TR><TD BGCOLOR=#FFCC99 ALIGN=CENTER COLSPAN=2 ALIGN=CENTER>Export File:";bh$;dbName$;".sql</TD></TR>"
html "<TR><TD ALIGN=CENTER COLSPAN=2>"
    button  #doExpDb, "Export",[doExpDb]
            #doExpDb cssclass("sBtn")
    html " "
    button  #exit, "Exit",[main]
            #exit cssclass("sBtn")
            #exit setkey(tableName$)

html "</TD></TR></TABLE>"
wait

[doExpDb]
on error goto [errSQL1]
stuct   = val(left$(#stuct selection$(),1))
drop    = #drop value()
cins    = #cins value()

open DefaultDir$ + "\public\";dbName$;".sql" for output as #f
print #f, "# --------------------------------------------------------"
print #f, "# SQLite Dump"
print #f, "# Generation Time:";date$();" ";time$()
print #f, "# SQLite Version:";ver$
print #f, "# Database:";fl$
print #f, "# --------------------------------------------------------"

preTbl$ = ""
sqliteconnect   #sql1, lf$
sqliteconnect   #sql, lf$
sql1$   = "SELECT * FROM sqlite_master ORDER BY tbl_name,type desc,name"  
#sql1 execute(sql1$)
rows    = #sql1 ROWCOUNT()       'Get the number of rows
if rows = 0 then
    errNum  = errNum + 1
    errMsg$(errNum) = "Name :";dbName$;" has nothing to export"
    gosub [doMsg]
    close #f
    wait
end if

WHILE   #sql1 hasanswer()
    result1$    = #sql1 nextrow$(" |")
    type$       = trim$(word$(result1$,1,"|"))  
    tbl$        = trim$(word$(result1$,3,"|"))  
    rootPage$   = trim$(word$(result1$,4,"|"))  
    theSql$     = trim$(word$(result1$,5,"|"))  

if preTbl$ = "" then preTbl$ = tbl$

if preTbl$ <> tbl$ then gosub [exportData]


if type$ = "table" and stuct < 3 then
sql$    = "SELECT * FROM '";tbl$;"'"
#sql execute(sql$)
colNames$	= #sql columnnames$()
nc 		= 1
while word$(colNames$,nc,",") <> ""
nc		= nc + 1
WEND
dim acolName$(nc)
dim adef$(nc)
nc	= nc - 1

    print #f, ""
    print #f, "# --------------------------------------------------------"
    print #f, "# Table structure for: ";tbl$;"   ";nc;" fields"
    print #f, "# --------------------------------------------------------"
    if drop = 1 then print #f, "DROP TABLE ";tbl$;";"

    theSql$ = strRep$(theSql$,"  "," ")
    theSql$ = strRep$(theSql$,"  "," ")
    theSql$ = strRep$(theSql$,"( ","(")
    theSql$ = strRep$(theSql$," ,",",")
    theSql$ = strRep$(theSql$,", ",",")
    cma$ = ","
for i = 1 to nc
	acolName$(i)	= trim$(word$(colNames$,i,","))
	ii		= instr(theSql$,acolName$(i);" ")
	adef$(i)	= mid$(theSql$,ii + len(acolName$(i)))
next i
print #f, "CREATE TABLE ";chr$(9);tbl$;" ("

for i = 1 to nc
	adef$(i) = word$(adef$(i),1,",";acolName$(i + 1);" ")
	c9$	 = chr$(9)
	if len(acolName$(i)) < 8 then c9$ = c9$ ; c9$
	if i = nc then cma$ = ""
	print #f, acolName$(i);c9$;trim$(adef$(i));cma$
next i
    print #f, ";"
end if
if type$ <> "table" and stuct < 3 then
    print #f, theSql$;";"
end if
wend

#sql1 disconnect()
gosub [exportData]
#sql disconnect()
close #f
infoNum = infoNum + 1
a$ = strRep$(DefaultDir$,"\","\\")
infoMsg$(infoNum) = "Database ";dbName$;" export complete<BR> in file: ";a$;"\\public\\";dbName$;".sql"
goto [main]
wait

[exportData]
if stuct = 1 then RETURN
sql$    = "SELECT * FROM '";preTbl$;"'"
#sql execute(sql$)
rows    = #sql ROWCOUNT()    'Get the number of rows
if rows > 0 then
    print #f, ""
    print #f, "# --------------------------------------------------------"
    print #f, "# Dumping ";rows;" records for table: ";preTbl$
    print #f, "# --------------------------------------------------------"
    colNames$   = #sql columnnames$()
    nc = 1
    while word$(colNames$,nc,",") <> ""
        nc = nc + 1
    WEND
    nc  = nc - 1
    WHILE   #sql hasanswer()
        result$ = #sql nextrow$(" |")
        i = 1
        vals$   = ""
        sep$    = ""
        for i = 1 to nc         ' for each number of col names
            a$  = trim$(word$(result$,i,"|"))
            	a$  = trim$(word$(result$,i,"|"))
	' mark carriage returns and tabs
    		a$ = strRep$(a$,chr$(13),"<13>")
          	a$ = strRep$(a$,chr$(9),"<9>")
          	a$ = strRep$(a$,"'","<=+=>")
           	a$ = strip$(a$)
           	a$ = strRep$(a$,"<13>",chr$(13))
          	a$ = strRep$(a$,"<=+=>","'")
           	a$ = strRep$(a$,"<9>",chr$(9))
            vals$   = vals$ + sep$ + esc$(a$,"")
            sep$    = "','"
            i = i + 1
        next i
        if cins = 1 then
          print #f, "INSERT INTO ";preTbl$;" (";colNames$;") VALUES ('";vals$;"');" 
         else
          print #f, "INSERT INTO ";preTbl$;" VALUES ('";vals$;"');" 
        end if
    wend
end if
preTbl$ = tbl$
RETURN

' ---------------------------------
' Export csv
' ---------------------------------
[exportCsv]
gosub [heading]
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Tables</TD><TD>"
sqliteconnect   #sql1, lf$
sql1$   = "SELECT tbl_name FROM sqlite_master 
WHERE type = 'table' 
ORDER BY tbl_name"
#sql1 execute(sql1$)
rows    = #sql1 ROWCOUNT()       'Get the number of rows
dim aFieldNames$(rows)
dim infoMsg$(rows + 1)
i = 0
    ttbl.i$ = "#ttbl";i
    CHECKBOX #ttbl.i$, "*All*", 1
    html "<BR>"
WHILE   #sql1 hasanswer()
    result1$    = #sql1 nextrow$(" |")
    tbl$        = trim$(word$(result1$,1,"|"))  
    i = i + 1
    ttbl.i$ = "#ttbl";i
    CHECKBOX #ttbl.i$, tbl$, 0
    html "<BR>"
    aFieldNames$(i) = tbl$
WEND
numTbl  = i
#sql1 disconnect()

html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Fields terminated by</TD><TD>"
TEXTBOX #fTrm, ",",3

html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Fields enclosed by</TD><TD>"
TEXTBOX #fEnc, "'",3

html "</TD></TR><TR><TD BGCOLOR=#FFCC99 ALIGN=RIGHT>Fields escaped by</TD><TD>"
TEXTBOX #fEsc, "\",3

html "</TD></TR><TR><TD ALIGN=RIGHT colspan=2>"
    CHECKBOX #fNam, "Put field names on first row", 0

html "</TD></TR><TR><TD COLSPAN=2 BGCOLOR=#FFCC99 ALIGN=CENTER>"
    button    #doCsvDb, "Export",[doCsvDb]
            #doCsvDb cssclass("sBtn")
    html " "
    button    #exit, "Exit",[main]
            #exit cssclass("sBtn")
            #exit setkey(tableName$)
html "</TD></TR></TABLE>"
wait

[doCsvDb]
fTrm$   = trim$(#fTrm contents$())  ' terminate fields - default is , nothing is blank
if fTrm$ = "" then fTrm$ = " "
fEnc$   = trim$(#fEnc contents$())  ' enclose in quote or something
fEsc$   = trim$(#fEsc contents$())  ' escape
fNam    = #fNam value()


sqliteconnect   #sql1, lf$
i = 0
ttbl.i$ = "#ttbl";i
ttbAll  = #ttbl.i$ value()

for i = 1 to numTbl
    ttbl.i$ = "#ttbl";i
    ttbl    = #ttbl.i$ value()
    if ttbl = 1 or ttbAll = 1 then
open DefaultDir$ + "\public\";aFieldNames$(i);".csv" for output as #f
infoNum = infoNum + 1
infoMsg$(infoNum)   = "Output File: ";"\\public\\";aFieldNames$(i);".csv"

sql1$   = "SELECT * from ";aFieldNames$(i)
#sql1 execute(sql1$)

colNames$   = #sql1 columnnames$()
sep$    = ""    ' separator
fields$ = ""
ii = 1
while word$(colNames$,ii,",") <> ""
    a$  = trim$(word$(colNames$,ii,","))    
    a$  = fEnc$ + esc$(a$,fEsc$) + fEnc$
    fields$ = fields$ + cma$ + a$
    sep$    = fTrm$
    ii = ii + 1
wend
if fNam = 1 then print #f,fields$   
numCols = ii - 1

WHILE   #sql1 hasanswer()
    result1$    = #sql1 nextrow$(" |")
    vals$   = ""
    sep$    = ""
    for ii = 1 to numCols
        a$  = trim$(word$(result1$,ii,"|"))
	' mark carriage returns and tabs
    		a$ = strRep$(a$,chr$(13),"<13>")
          	a$ = strRep$(a$,chr$(9),"<9>")
           	a$ = strip$(a$)
           	a$ = strRep$(a$,"<13>",chr$(13))
           	a$ = strRep$(a$,"<9>",chr$(9))

        a$  = fEnc$ + esc$(a$,fEsc$) + fEnc$
        vals$   = vals$ + sep$ + a$
        sep$    = fTrm$
    next ii
    print #f, vals$    
WEND
close #f
end if
next i
#sql1 disconnect()
goto [main]
wait

[xref]
' ==========================================
' Table - field cross reference
' ==========================================
a$	= EventKey$
dbName$	= word$(a$,1,"|")
lf$	= word$(a$,2,"|")
a$	= ""
bf$	= "<SPAN STYLE='font-family:Arial; font-weight:700; font-size:10pt'>"
' --------------------------------------
' create in memory database
' --------------------------------------
sqliteconnect #mem, ":memory:"
mem$	= "CREATE TABLE xref(tbl text,tcnt,fld text,cnt int)"
#mem execute(mem$)

sqliteconnect   #sql1, lf$
sql1$   = "
SELECT  name
FROM    sqlite_master
WHERE   type = 'table'
ORDER BY name"
#sql1 execute(sql1$)
rows    = #sql1 ROWCOUNT()       'Get the number of rows
tbls$	= ""
cma$	= ""
WHILE   #sql1 hasanswer()
	result1$	= #sql1 nextrow$(" |")
	tableName$	= trim$(word$(result1$,1,"|"))
	tbls$		= tbls$ + cma$ + tableName$
	cma$		= ","
	'print tableName$
WEND

' ------------------------------------
' Fields of table
' ------------------------------------
ii = 1
WHILE word$(tbls$,ii,",") <> ""
	tbl$	= word$(tbls$,ii,",")
	sql1$   = "SELECT * FROM ";tbl$;" limit 1"
	#sql1 execute(sql1$)
	fields$ = " " + #sql1 columnnames$() + " "
	'print tbl$;":";fields$
	i = 1
	while word$(fields$,i,",") <> ""
		fieldName$  = trim$(word$(fields$,i,","))
		mem$ = "INSERT INTO xref VALUES('";tbl$;"',";ii;",'";fieldName$;"',0)"
		#mem execute(mem$)
		i = i + 1
	WEND
	ii = ii + 1
WEND
#sql1 disconnect()

mem$	= "
SELECT	fld,count(fld) as cnt
FROM	xref
GROUP BY fld"
#mem execute(mem$)
WHILE    #mem hasanswer()
	#row	= #mem #nextrow()
	fld$	= #row fld$()
	cnt	= #row cnt()
	cnt1 	= max(cnt1,cnt)	
	if cnt > 1 then a$ = a$ + fld$;",";cnt;"|"
WEND

' -------------------------------------
' table xref
' -------------------------------------
open DefaultDir$ + "\public\";dbName$;"_xref.htm" for output as #f
bf1$	= "<SPAN STYLE='font-family:Arial; font-weight:700; font-size:10pt'>"
print #f, bf1$;"<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0><TR bgcolor=#cccccc><TD>"
print #f, "Database Cross Reference"
print #f, "</TD></TR><TR><TD>"
print #f, "Generation Time:";date$();" ";time$()
print #f, "</TD></TR><TR><TD>"
print #f, "SQLite Version:";ver$
print #f, "</TD></TR><TR><TD>"
print #f, "Database:";dbName$
print #f, "</TD></TR><TR><TD>"
print #f, rows;" Tables"
print #f, "</TD></TR></TABLE>"

print #f, "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0><TR bgcolor=#cccccc valign=bottom align=center><TD>Table</TD>"

i = 1
WHILE word$(a$,i,"|") <> ""
	x$	= word$(a$,i,"|")
	fld$	= word$(x$,1,",")
	mem$ = "UPDATE xref SET cnt = ";i;" WHERE fld = '";fld$;"'"
	'print mem$
	#mem execute(mem$)
	print #f, "<TD>"
	br$ = ""
	for j = 1 to len(fld$)
		print #f, br$;mid$(fld$,j,1)
		br$ = "<BR>"
	next j	
	print #f, "</TD>"
	cnt1 = i
	i = i + 1
WEND
mem$	= "
SELECT	*
FROM	xref
WHERE cnt > 0
ORDER BY tbl,fld"
#mem execute(mem$)
WHILE    #mem hasanswer()
	#row	= #mem #nextrow()
	tbl$	= #row tbl$()
	fld$	= #row fld$()
	cnt	= #row cnt()
	
	if preTbl$ <> tbl$ then
		if preTbl$ <> "" then
			for i = i to cnt1
				print #f, "<TD></TD>"
			next i
		end if
		print #f, "</TR><TR align=center><TD bgcolor=#cccccc align=left>";tbl$;"</TD>"
		preTbl$ = tbl$
		i = 1
	end if
	
	for i = i to cnt
		if i <> cnt then print #f, "<TD></TD>"
	next i
	print #f, "<TD bgcolor=silver> X </TD>"
	i = i + 1
WEND
print #f, "</TABLE>"

' -----------------------------------------
' Field xref
' -----------------------------------------

print #f, "<P><P><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0><TR bgcolor=#cccccc align=center valign=bottom><TD>Field</TD>"
i = 1
WHILE word$(tbls$,i,",") <> ""
	tbl$	= word$(tbls$,i,",")
	print #f, "<TD>"
	br$ = ""
	for j = 1 to len(tbl$)
		print #f, br$;mid$(tbl$,j,1)
		br$ = "<BR>"
	next j	
	print #f, "</TD>"
	cnt1 = i
	i = i + 1
WEND

mem$	= "
SELECT	*
FROM	xref
ORDER BY fld,tbl"
#mem execute(mem$)
WHILE    #mem hasanswer()
	#row	= #mem #nextrow()
	tbl$	= #row tbl$()
	fld$	= #row fld$()
	cnt	= #row cnt()
	tcnt	= #row tcnt()
	
	if preFld$ <> fld$ then
		if preFld$ <> "" then
			for i = i to cnt1
				print #f, "<TD></TD>"
			next i
		end if
		print #f, "</TR><TR><TD bgcolor=#cccccc>";fld$;"</TD>"
		preFld$ = fld$
		i = 1
	end if
	
	for i = i to tcnt
		if i <> tcnt then print #f, "<TD></TD>"
	next i
	print #f, "<TD align=center bgcolor=silver> X </TD>"
	i = i + 1
WEND
print #f, "</TABLE>"
close #f
infoNum = infoNum + 1
a$ = strRep$(DefaultDir$,"\","\\")
infoMsg$(infoNum) = "Database ";dbName$;" Cross Ref complete. File: ";a$;"\\public\\";dbName$;"_xref.htm"

goto [main]

' -----------------------------
' Error Handler
' -----------------------------
[errFile]
errNum  = errNum + 1
errMsg$(errNum) = "Data base master file error:";sqliteDb$
gosub [doMsg]
wait
[errSQL1]
errNum  = errNum + 1
errMsg$(errNum) = "sqlError:";sql1$
gosub [doMsg]
#sql1 disconnect()
wait

[errDbFile]
errNum  = errNum + 1
errMsg$(errNum) = "File:";dbFile$;" does not exist"
gosub [doMsg]
wait

[doMsg]

    html "<script> document.getElementById('infoMsg').innerHTML = '"
    br$ = ""
    for i = 1 to infoNum
        html br$;infoMsg$(i)
        br$ = "<BR>"
    next i
    html "';</script>"

    html "<script> document.getElementById('wrnMsg').innerHTML = '"
    br$ = ""
    for i = 1 to wrnNum
        html br$;wrnMsg$(i)
        br$ = "<BR>"
    next i
    html "';</script>"

    html "<script> document.getElementById('errMsg').innerHTML = '"
    br$ = ""
    for i = 1 to errNum
        html br$;errMsg$(i)
        br$ = "<BR>"
    next i
    html "';</script>"

infoNum = 0
wrnNum  = 0
errNum  = 0
on error goto [handler]
RETURN

[handler]
html "<script> document.getElementById('errMsg').innerHTML = '"
html "Error msg:";Err$
html "<BR>Error Number:";Err
html "';</script>"
on error goto [handler]
WAIT

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

' -----------------------------------------
' strip junk 
' -----------------------------------------
FUNCTION strip$(str$)
    strip$ = ""
    for i = 1 to len(str$)
        a$ = MID$(str$,i,1)
        a = ASC(a$)
        if a > 31 then
                   if a <  127 then
             if a$ <> "'" then
                       if a$ <> """" then
            strip$ = strip$ + a$
               end if
             end if
           end if
        end if
    next i
END FUNCTION

' -------------------------------------------------------
' Convert sql field and values notation to set notation
' supply:
'   flds = database field names seperated by comma
'   vals = corresponding values in seperated by comma
'          character names should be in single qoutes
' -------------------------------------------------------
FUNCTION sqlSet$(flds$,vals$)
ix  = 1
sqlSet$ = ""
qq$ = ""
cma$    = ""
while (word$(flds$,ix,",") <> "")
     sqlSet$ = sqlSet$ + cma$ + word$(flds$,ix,",") + " = " + qq$ + word$(vals$,ix,",'")
     cma$   = ", "
     qq$    = "'"
     ix = ix + 1
WEND
END FUNCTION

' -------------------------------
' Insert escape characters
' supply
' str$ = string to excape
' wth$ = escape with. 
'        Leave blank to double the escape character
' -------------------------------
FUNCTION esc$(str$,wth$)
for i = 1 to len(str$)
    c$    = mid$(str$,i,1)
    if c$ = "'" or c$ = "\" then
        if wth$ = "" then
           esc$ = esc$ + c$ + c$
          else
           esc$    = esc$ + wth$ + c$
        end if
       else
        esc$    = esc$ + c$
    end if
next i
END FUNCTION

 ' -----------------------------------------
 ' Get outta here
 ' -----------------------------------------
[doExit]
html "<script language='javascript' type='text/javascript'>
var a = history.length;
a = a - 1;
window.open('','_parent','');
window.close();
history.go(-a);
</script>"
wait

' ----------------------------
' CSS
' ----------------------------
SUB SetCSS 
CSSClass "a.sBtn", "{
Border-Width:1px; Border-Color:black; Border-Style:solid; background:wheat;
Font-Size:11pt; Font-Weight:Bold; Font-Family:Arial; color:black;Text-Decoration:None;
}"
'CSSClass "a.eBtn", "{
'Border-Width:1px; Border-Color:black; Border-Style:solid; background:#FF9999;
'Font-Size:10pt; Font-Weight:Bold; Font-Family:Arial; color:black;Text-Decoration:None;
'}"
 END SUB