Benscomputer.no-ip.org - Writing a front end for Claims_DB - Part 2

Creating a Claims_DB front end Tutorial Part 1 - Introduction

OK, so we've created our Database, now we need to provide users with a way to interact with it. We are going to create a Web based interface using BASH scripts as CGI scripts. These will provide the interface as well as doing any necessary processing.

Lets create and enter our working directory

mkdir ~/src
cd ~/src

Now the first thing we are going to need is an Index page. You could put some sort of Password authentication on this page, but that's outside the scope of this article (if you are going to add a password, make sure you use SSL).

Now, we want our Index page to be pretty interactive, so we are going to have the following elements

Drop Down boxes to generate Reports
Button to Add a Record

The reports fields should allow the user to select values available within the database. Given the dataset we are using, location on bike would be a good choice.

So whilst we are aiming for something like this;

We want the list to generate dynamically, so simply coding it into the HTML won't meet our needs.

So lets create the script

nano index.sh

#!/bin/bash
#
# Index page for Claims_DB front 
end.
# Copyright Ben Tasker 2009
#
# Released under the GNU GPL
# See 
http://benscomputer.no-ip.org/LICENSE
#

# Set Variables here

# Where did you install 
Claims_DB?
CLAIMS_PROGS="/home/ben/programs/Claims_DB/"

# The name of the Database we are 
using
DBNAME="Sample_DB"

# Function Main!

# Tell the browser what mime type we are 
using
echo Content-type: text/html
echo ""

# May as well start the HTML generation

/bin/cat 
<< EOM
<html><head><title>Claims_DB Sample Database 
Frontend</title>
<body bgcolor="white">
<center><b>Claims_DB Sample Database 
Frontend</b></center><br><br>
EOM

The script now generates the headers, our page would render at this point. There will be errors because the code is incomplete, but the server would generate a very basic page if we were to use this. Next we want to provide links to the various functions that we will generate later on;

# Lets create our Add record 
button

/bin/cat << EOM
<!-- Pass user to the add 
record CGI -->
<FORM ACTION="add_record.sh" METHOD="GET"><INPUT TYPE="SUBMIT" VALUE="Add 
Record"></FORM>
<br><br>
<!-- Section ends -->
EOM

So we now have a button allowing us to move to the Add Record form. If we used it at the moment we'd get a 404 as we haven't created that script yet. But lets finish off the Index page first

# Now lets create the drop down box for our report


/bin/cat << EOM
<!-- User to select Query criteria, then send them to the report generator -->
<FORM ACTION="gen_report.sh" METHOD="GET">
<SELECT NAME="CRITERIA">
EOM

# Now we want to run our own query to provide the user with valid criteria
# We want to run a SELECT DISTINCT style query.

# Grab the available locations, we know they are stored in Column 3

# Until new functionality is implemented, we need to grab the entire table
DBROOT="$DBNAME" TABLE="Catalogue" "$CLAIMS_PROGS"/bin/read_records_claims.sh -read > /tmp/LOCATIONS.tmp

# Now seperate out Column 3, and remove duplicates
awk -F\, '{print $3}' /tmp/LOCATIONS.tmp > /tmp/LOCATS2.tmp
awk '{
if ($0 in stored_lines)
x=1
else
print
stored_lines[$0]=1
}' /tmp/LOCATS2.tmp > /tmp/LOCATIONS.tmp


# Strip out the text delimeter - "
sed s/\"//g /tmp/LOCATIONS.tmp > /tmp/LOCATS2.tmp

# Read the resulting file line by line and create an Option value for each
while read -r a
do
/bin/cat << EOM
<option value="$a">$a</option>
EOM
done < /tmp/LOCATSS2.tmp

# Tidy up!!!!!!!!
rm -f /tmp/LOCATIONS.tmp 2> /dev/null
rm -f /tmp/LOCATS2.tmp 2> /dev/null

# Now finish creating the form

/bin/cat << EOM
</select>
<INPUT TYPE="submit" Value="Generate Report"></form>
<br><br>
</body>
</html>
EOM

# Page generation finished
exit

We now have a valid page, the user will only be presented with locations that exist in the table. We should probably do a little more on the aesthetic side of life, but the basic functionality exists.

You can grab a full copy of the code so far here.

Tutorial Part 3 - add_record.sh


Pages not Rendering Properly? Home News About me Contact Me Phorm's History Porcelain Pictures Services Projects Articles Getting Started With Linux Documentation Reviews Dispute Archives Page Archives Yearly Indexes Archived Links Terms and conditons of webhosting Links Sitemap Search Benscomputer.no-ip.org Copyright Information Privacy Policy	Writing a front end for Claims_DB - Part 2 - index.sh Creating a Claims_DB front end Tutorial Part 1 - Introduction OK, so we've created our Database, now we need to provide users with a way to interact with it. We are going to create a Web based interface using BASH scripts as CGI scripts. These will provide the interface as well as doing any necessary processing. Lets create and enter our working directory mkdir ~/src cd ~/src Now the first thing we are going to need is an Index page. You could put some sort of Password authentication on this page, but that's outside the scope of this article (if you are going to add a password, make sure you use SSL). Now, we want our Index page to be pretty interactive, so we are going to have the following elements Drop Down boxes to generate Reports Button to Add a Record The reports fields should allow the user to select values available within the database. Given the dataset we are using, location on bike would be a good choice. So whilst we are aiming for something like this; Location: We want the list to generate dynamically, so simply coding it into the HTML won't meet our needs. So lets create the script nano index.sh #!/bin/bash # # Index page for Claims_DB front end. # Copyright Ben Tasker 2009 # # Released under the GNU GPL # See http://benscomputer.no-ip.org/LICENSE # # Set Variables here # Where did you install Claims_DB? CLAIMS_PROGS="/home/ben/programs/Claims_DB/" # The name of the Database we are using DBNAME="Sample_DB" # Function Main! # Tell the browser what mime type we are using echo Content-type: text/html echo "" # May as well start the HTML generation /bin/cat << EOM <html><head><title>Claims_DB Sample Database Frontend</title> <body bgcolor="white"> <center><b>Claims_DB Sample Database Frontend</b></center><br><br> EOM The script now generates the headers, our page would render at this point. There will be errors because the code is incomplete, but the server would generate a very basic page if we were to use this. Next we want to provide links to the various functions that we will generate later on; # Lets create our Add record button /bin/cat << EOM <!-- Pass user to the add record CGI --> <FORM ACTION="add_record.sh" METHOD="GET"><INPUT TYPE="SUBMIT" VALUE="Add Record"></FORM> <br><br> <!-- Section ends --> EOM So we now have a button allowing us to move to the Add Record form. If we used it at the moment we'd get a 404 as we haven't created that script yet. But lets finish off the Index page first # Now lets create the drop down box for our report /bin/cat << EOM <!-- User to select Query criteria, then send them to the report generator --> <FORM ACTION="gen_report.sh" METHOD="GET"> <SELECT NAME="CRITERIA"> EOM # Now we want to run our own query to provide the user with valid criteria # We want to run a SELECT DISTINCT style query. # Grab the available locations, we know they are stored in Column 3 # Until new functionality is implemented, we need to grab the entire table DBROOT="$DBNAME" TABLE="Catalogue" "$CLAIMS_PROGS"/bin/read_records_claims.sh -read > /tmp/LOCATIONS.tmp # Now seperate out Column 3, and remove duplicates awk -F\, '{print $3}' /tmp/LOCATIONS.tmp > /tmp/LOCATS2.tmp awk '{ if ($0 in stored_lines) x=1 else print stored_lines[$0]=1 }' /tmp/LOCATS2.tmp > /tmp/LOCATIONS.tmp # Strip out the text delimeter - " sed s/\"//g /tmp/LOCATIONS.tmp > /tmp/LOCATS2.tmp # Read the resulting file line by line and create an Option value for each while read -r a do /bin/cat << EOM <option value="$a">$a</option> EOM done < /tmp/LOCATSS2.tmp # Tidy up!!!!!!!! rm -f /tmp/LOCATIONS.tmp 2> /dev/null rm -f /tmp/LOCATS2.tmp 2> /dev/null # Now finish creating the form /bin/cat << EOM </select> <INPUT TYPE="submit" Value="Generate Report"></form> <br><br> </body> </html> EOM # Page generation finished exit We now have a valid page, the user will only be presented with locations that exist in the table. We should probably do a little more on the aesthetic side of life, but the basic functionality exists. You can grab a full copy of the code so far here. Tutorial Part 3 - add_record.sh