Writing a Front End for Claims_DB Part 2 - index.sh
This content was originally published to benscomputer.no-ip.org
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
<title>Claims_DB Sample Database
Frontend</title>
<center><b>Claims_DB Sample Database
Frontend</b></center>
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