Project idea: SQLite Query Tool

[carlgundel]

Who would be interested in creating a Run BASIC app for interacting with SQLite?

-Carl

[StefanPendl]

Something along the lines of the Simple Database Framework demo

[carlgundel]

stefanp said:

Something along the lines of the Simple Database Framework demo

No, I mean a program which will let you enter queries against any SQLite database and display the results.

-Carl

[Jerry Muelver]

That's an interesting problem, somewhat like "how to query Google", or even how to search this forum. Do you have in mind to parse any old question into SQL? Or provide a basic 15-word or so vocabulary (field names from the database?) to the user from which to build queries?

PLAYERS more than 100 HOME RUNS
COUNTRY POPULATION greater than 50 million
all STOCKSYMBOL PRICE 2007 greater than PRICE 2000

Or set of drop-down pick lists to build queries? Or interactive Q&A to narrow the query, like a troubleshooting tree?

Is the computer plugged in?
Is the monitor turned on?
Did you enter your username?
....

Animal?
Vegetable?
Mineral?
Does it live on land?
Does it have wings?
Two legs, four, or more?
...

[David den Haring]

I think Carl is proposing a RB / sqlite version of this:

www.mysql.com/products/tools/query-browser/

...but in the browser and easier to use.

[carlgundel]

daviddh said:

I think Carl is proposing a RB / sqlite version of this:

www.mysql.com/products/tools/query-browser/

...but in the browser and easier to use.

Yup. I'm not proposing anything too fancy. Just a field to enter the database name. Another field to type a SQL query and an execute button. The results appear rendered below.

Any frills above this are just icing on the cake.

-Carl

[StefanPendl]

So it is more like the SQL Scratchpad created with LB, which can be found in the database section of the LB conforum.

[colinmac]

Carl said :-

"Yup. I'm not proposing anything too fancy. Just a field to enter the database name. Another field to type a SQL query and an execute button. The results appear rendered below. "

Perhaps other information also - a menu of hosted databases to make an initial choice - a list of tables and fields in the selected database so the user may compose a query - perhaps also some specimen queries to help the user out?

I assume we are looking here for something that is read only at this stage, rather than a database creation/management tool. This would avoid a lot of issues with locking and unlocking etc.



Colin

[carlgundel]

colinmac said:

Carl said :-

"Yup. I'm not proposing anything too fancy. Just a field to enter the database name. Another field to type a SQL query and an execute button. The results appear rendered below. "

Perhaps other information also - a menu of hosted databases to make an initial choice - a list of tables and fields in the selected database so the user may compose a query - perhaps also some specimen queries to help the user out?

I assume we are looking here for something that is read only at this stage, rather than a database creation/management tool. This would avoid a lot of issues with locking and unlocking etc.

I am just proposing an idea for a project. There are no hard requirements. As for bells and whistles, feel free to go to town.

-Carl

[colinmac]

Here is some simple code for reading, and writing to, an sqlite database.



' sqlite monitor - run-basic script by Colin McMurchie

call setCSS

dim dbases$(2)
dbases$(0) = "buggs.db"
dbases$(1) = "wiki.db"
' dbases$(2) = "cdlist.sq3"

[choosedb]
html "<h1> Choose a database to work with </h1>"

listbox #dbase, dbases$(),5
#dbase select(dbases$ )

link #go, "Select Database", [handleSelection]
print
link #exit ,"exit the programme?" ,[exit2]
wait

[handleSelection]
dbase$ = #dbase selection$()
SQLITECONNECT #dbh, dbase$
on error goto [fileerror]
sql$ = "select type , name from sqlite_master "
gosub [sqlexecute]
print
gosub [printscreen]
wait

end

[printscreen]
cls
gosub [printtable]
gosub [printmenu]
return

[printmenu]
div menubox
div sqlStatementBox
textarea #sqlstatement, sql$
end div ' sqlStatementBox
link #sqlstmnt, " process this statement", [warning]
print
div sqlErrorBox
textarea #sqlerror, monerror$
end div ' sqlErrorBox
print "Error Report "
link #exit ,"exit this database?" ,[exitdb]
#exit cssclass("menuItem")
end div ' menubox
return

[exitdb]
#dbh DISCONNECT() '  - Disconnect from the database
cls
goto [choosedb]

[exit2]
cls
end

[sqlexecute]
monerror$ = "No SQL error reported"
#dbh EXECUTE(sql$)' - Execute the SQL query in expr$
on error goto [fileerror]
#dbh caption( dbase$ )
#dbh TRCLASS("restable")
#dbh TDCLASS("restable")
return

[printtable]
div restablebox
render #dbh
end div
return

[warning]
sql$ = #sqlstatement contents$()
if upper$(word$(sql$,1)) = "SELECT" then goto [yes]
div warningbox
html "<h1> Warning ,h1>"
html "<h2> You are about to overwrite the database. Do you wish to continue?</h2>"
link #yes, "Yes" , [yes]
print "       " ;
link #no, "No", [no]
end div
wait
[yes]
gosub [sqlexecute]
[no]
gosub [printscreen]
wait

sub setCSS
cssclass "textarea", " {background: #fff } "
cssid #warningbox, "{ clear: left } "
cssid #restablebox, "{ height: 400px ; width: 65%; overflow: scroll ; float: left; }"
cssclass "caption", "{ background: #EEF }"
cssclass "table.relative", "{ table-layout: fixed }"
cssclass "table", "{  border: 8px ridge #CCF ; border-spacing: 3px ; float: left}"
cssclass "tr", "{ background: #ccf }"
cssid #menubox, " { width; 30% ; height: 400px ; border : 8px ; position: fixed ; right: 0px ; background: #ccf} "
cssclass "table.restable", "{ width: 100%; border: 3px ridge #DDF ;  border-spacing: 3px }"
cssclass "tr.restable", "{ background: #8f8 }"
cssid #sqlStatementBox, "{ height: 150px ; overflow: hidden } "
cssid #sqlErrorBox, "{ height: 150px ; overflow: hidden } "
end sub

[fileerror]
monerror$=Err$
gosub [printscreen]
wait



To should run if pasted into a new project. For convenience it reads the two databases used by Carl for beta three , so it is available to all testers without additional files. It is easy enough to add a further database, by including it in the rbp directory and adding its name to the listbox array.

There is one known problem. Using the query "select * from sqlite_master" generates an error on some databases but not on others. I will look at that further.

Colin