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
# 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?

# The name of the Database we are using

# 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 

<center><b>Claims_DB Sample Database 

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 

/bin/cat << EOM
<!-- Pass user to the add record CGI -->
<!-- Section ends -->

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">

# 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)
}' /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
/bin/cat << EOM
<option value="$a">$a</option>
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
<INPUT TYPE="submit" Value="Generate Report"></form>

# Page generation finished

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