sqlite database form in lua

Forum: Programming and Scripting
Topic: sqlite database form in lua
started by: u2musicmike

Posted by u2musicmike on Feb. 24 2007,15:31

I was messing around with one of DSL's lua programs and discovered that cut and paste works with lua. I had some recipes in text files which were too hard to import so I started working on some lua code.

First I created a sqlite database:

Code Sample

#!/bin/sh
# Make a database with sqlite
sqlite cookbok.db <<EOF
create table rbookpg(cat text,title text,ingredients text,directions text,servings real,calories real);
EOF

Next I worked on the lua program:

Code Sample

#!/bin/lua

-- cookbook.lua by Mike White, 2/23/07 site:lua-users.org
-- This is a data entry form for a sqlite database containing recipes
-- Thanks to mikshaw and robert for their help with this code

window_width = 500
window_height = 350

w = fltk:Fl_Window(window_width,window_height,"Recipe Entry")
mainBox = fltk.Fl_Box(10,10,window_width-20,window_height-20)
mainBox:box(fltk.FL_EMBOSSED_BOX)

function catfood(self)
foodcat=self:label();
end

selbc = fltk:Fl_Round_Button(130,20,18,25,"Breads_Cereals")
selbc:type(fltk.FL_RADIO_BUTTON); selbc:callback(catfood);
selbc:selection_color(fltk.FL_RED)

selmd = fltk:Fl_Round_Button(260,20,18,25,"Main Dishes")
selmd:type(fltk.FL_RADIO_BUTTON); selmd:callback(catfood);
selmd:selection_color(fltk.FL_RED)

selveg = fltk:Fl_Round_Button(130,40,18,25,"Vegetables")
selveg:type(fltk.FL_RADIO_BUTTON); selveg:callback(catfood);
selveg:selection_color(fltk.FL_RED)

selmisc = fltk:Fl_Round_Button(260,40,18,25,"Miscellaneous")
selmisc:type(fltk.FL_RADIO_BUTTON); selmisc:callback(catfood);
selmisc:selection_color(fltk.FL_RED)

title = fltk:Fl_Input(100,75,320,25,"Title: ")

ingred = fltk:Fl_Multiline_Input(100,100,320,50,"Ingredients: ")

direct = fltk:Fl_Multiline_Input(100,150,320,50,"Directions: ")

serv = fltk:Fl_Input(100,200,50,25,"Servings: ")

calo = fltk:Fl_Input(100,225,50,25,"Calories: ")

entryButton = fltk:Fl_Button(30,290,100,30,"enter")
entryButton:callback(

function(entryButton)
sqltext = "insert into rbookpg values$\""..foodcat .."\"\,\""..title:value().."\"\,\""..ingred:value().."\"\,\""..direct:value().."\"\,\""..serv:value().."\"\,\""..calo:value().."\"$"
os.execute('sqlite cookbok.db \''..sqltext..'\'')

end)

findButton = fltk:Fl_Button(140,290,100,30,"find")
findButton:callback(
function(findButton)

clause = " WHERE "
sqltext = "Select * From rbookpg"
Criteria = title:value()
if title:value() ~= "" then
sqltext = sqltext..clause.."title Like ".."\'%"..Criteria.."%\'"
clause = " AND "
end

Criteria = ingred:value()
if ingred:value() ~= "" then
sqltext = sqltext..clause.."ingredients Like ".."\'%"..Criteria.."%\'"
end

os.execute('sqlfind.sh '.."\""..sqltext.."\"")

end)

listButton = fltk:Fl_Button(250,290,100,30,"list")
listButton:callback(
function(listButton)

sqltext = "select title from rbookpg"
os.execute('sqlfind.sh '.."\""..sqltext.."\"")

end)

cancelButton = fltk:Fl_Button(360,290,100,30,"quit")
cancelButton:callback(
function(cancelButton)
os.exit(0)
end)

w:show()
Fl:run()

Finally I did the rest with a bash script:

Code Sample

#!/bin/sh

# sqlfind.sh by Mike White, 2/23/07
# This bash script takes a sql statement on the command line
# and displays the results with dillo

SQLITE_BINARY="/usr/bin/sqlite"

DBNAME="cookbok.db"

if [ -n "$1" ]
# Test if command line argument present (non-empty).
then
SQL_COMMAND=$1
else
exit 0 # Default, if not specified on command line.
fi

com="sql"
tmp=/tmp/${com}:t.$$
html=${tmp}.html
header="<HTML><HEAD><TITLE> Database Query </TITLE></HEAD> <BODY><TABLE border=1>"
footer="</TABLE></BODY></HTML>"
echo $header > $html
$SQLITE_BINARY -html -header $DBNAME "$SQL_COMMAND" >> $html
echo $footer >> $html

if [ ! -s $html ]
then
echo "$0: failed to generate HTML file"
exit 0
fi
#File exists and is of length > 0, so open it
dillo $html

exit 0

The lua code still needs some error checking routines because it has bombed when pasting text with apostrophes.