submit cmps180-ark.w02 proj7 files...
Reminder: please do not submit any files containing large data sets or query results. All results should be truncated to a few lines. Large submissions will be rejected, either automatically or by a (not too happy) grader.
Programming work must be submitted electronically by midnight at the end of the day that it is due. Programming work submitted after the deadline but before the "late deadline" -- midnight 48 hours after the deadline -- will be accepted but penalized 50%, except for the first two project parts that are handed in late and the first two homework assignments handed in late. No programming work will be accepted after the late deadline.
Please be sure to follow the explicitly specified file naming scheme and additional submission instructions below.
Your Web front-end will interact with your PostgreSQL database using Perl, C or Java CGI scripts and embedded or call level SQL, or by using PHP and call level SQL. If you used PERL, C or Java for Project Part 5 then you may want to choose CGI scripts for this part. However, you are free to switch languages at this point if you like. You are also free to use other languages to implement your Web interface. Please note that PHP and CGI using Perl or C are the only implementations supported by the course staff. You are on your own if you use anything else (we will still try to help you, but it may be a case of the blind leading the near sighted). Please note that we have not gotten Java servlets to work in a secure way, so they are not supported.
/local/cmps180/cgi-bin/Individual htdocs directories for HTML and PHP files have been created for each student under
/local/cmps180/htdocs/You should first create symbolic links to your directories, replacing username with your CATS username. The cd command puts you in your home directory. Do this command from the linux1.ic.ucsc.edu computer.
cd ln -s /local/cmps180/cgi-bin/username cmps180-cgi ln -s /local/cmps180/htdocs/username cmps180-htdocsRunning your executable CGI or PHP programs over the web will require your cats username and a password which is the last four digits of your Social Security Number. The URL to access your html and php files should simply be:
http://linux.ic.ucsc.edu/~username/filenamewhere username is your cats username and filename is the name of your file such as "beers1.html" or "beers.php". The URL to access your CGI files should simply be
http://linux.ic.ucsc.edu/cgi-bin/cgiwrap/username/filenamewhere username is your CATS username and filename is the name of your file such as "beers1.cgi" or "beeers1.pl".
Your files must be readable by the group (which includes the Apache server and the instructor, TA, and graders) to be accessible to Apache. Use the command
chmod 750 filename1 filename2...or
chmod 750 *to set all files in the directory to readable by the group.
Note that we are using port 80, not 8000. The latter is used for webct.
Please take care to perform a clean submission - it's worth the effort for everyone involved!
You will be using HTML to define the look of your web site. It is assumed that you are already familiar with HTML but if you are not there are many good books available. Here is a page of links to useful tutorials: Programming HTML You can always get ideas by going to a page you like on the web and selecting View Source or View Page Source from you browser menus to see what the HTML for that page looks like.
PHP allows code to be included inline with HTML as in the example below. Blocks of PHP code are bracketed with "<?php" at the beginning and "?>" at the end to differentiate them from the HTML. PHP syntax is similar to Perl and C. Variable names start with $ and variable input from forms is retrieved by pre pending the form variable name with a $ as in the example below using "$beername" to obtain the "beername" input in the form.
Here is the example:
<html><body>
<h2> Find A Bar That serves a beer </h2>
<form name="input" action="beers.php" method="get">
Enter a Beer Name:
<input type="text" name="beername">
<input type="submit" value="Submit">
</form>
<?php
// start of php code block embedded in html
$database = pg_Connect ("dbname=jsnook_beers user=jsnook");
if (!$database) {
echo "An error occured.<BR>";
}
else if ($submit == "Submit" AND $beername != "" AND $beername[0]){
$select = "SELECT * FROM sells WHERE beer LIKE '%" . $beername . "%'";
// Get bars from sells
$result = pg_exec ($database, $select );
if (!$result || (pg_numrows($result) < 1)) {
echo "I'm Sorry We Found No Records For You, Try Again<BR>";
exit;
}
listTable($result);
}
// End of code block
?>
</body></html>
<?php
// another code block for functions.
// Note global variables are shared amongst blocks
// display data from an SQL query result
function listTable($result) {
if (!$result) {
echo "No Data to List.\n";
return;
}
echo "<table border='1' cellpadding='3'>";
echo "<tr>";
// display headings
for ($j=0; $j < pg_numfields($result); $j++) {
echo "<th>" . pg_fieldname($result, $j) . "</th>";
}
echo "</tr>";
$num = pg_numrows($result);
if ($num > 30) $num = 30; // limit size of result displayed
// display each row of the result
for ($i=0; $i < $num; $i++) {
echo "<tr>";
$r = pg_fetch_row($result, $i);
for ($j=0; $j < count($r); $j++) {
echo "<td>$r[$j]</td>";
}
echo "</tr>";
}
echo "</table>";
}
// end of code block
?>
Here is a sample Perl CGI program (WARNING I haven't debugged it yet). If you want you can use functions provided in the CGI.pm module by including the "use CGI" line in your script. I haven't used them here but they simplify printing out your html and parsing your form input. For more information go to CGI.pm
#! /usr/bin/perl
use CGI;
use DBI;
print "Content-type: text/html\n\n";
print "<html><head>\n";
print "<title>Find A Bar</title></head>\n";
print "<body bgcolor=\"#FFFFFF\" text=\"#000000\" link=\"#FF0000\" vlink=\"#800000\">\n";
print "<h2> Find A Bar That serves a beer </h2>"
# Out put the html form
print "<form name=\"input\" action=\"beers.php\" method=\"post\">"
print "Enter a Beer Name:"
print "<input type=\"text\" name=\"beername\">"
print "<input type=\"submit\" name =\"submit\" value=\"Submit\">"
print "</form>"
# Get the data from the form This code block is from CGI101.com web site
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$FORM{$name} = $value;
}
# Open a connection to the PostgreSQL database
$database = DBI->connect('dbi:Pg:dbname=jsnook_beers', 'jsnook', '');
if (!$database) {
print "Could not find database.<BR>";
}
else if ($FORM{'submit'} == "Submit" AND $FORM{'beername'} != "" ){
$select = $database->prepare("SELECT * FROM sells WHERE beer LIKE '%" . $FORM{'beername'} . "%'");
// Get bars from sells
$result = $select->execute;
if (!$result ) {
print "I'm Sorry We Found No Records For You, Try Again<BR>";
}
else
{
print "<table border='0' cellpadding='1'>";
# later try: $fname = $result->fname($field_num)
while (my @row_ary = $sth->fetchrow_array)
{
print "<tr>";
print "<td>$row_ary[0]</td><td>$row_ary[1]</td>\n";
print "</tr>";
}
print "</table>";
}
$select->finish;
undef $select;
}
$database->disconnect;
undef $database;
print "</body></html>\n";
#include "cgi-lib.h" #include "html-lib.h"
This document assumes a basic knowledge of HTML. We will not be providing documentation for HTML coding apart from the creation of forms. There are dozens of tutorials available online. You might check out the Yahoo HTML Guides and Tutorials Page.
CGI or Common Gateway Interface is a means for providing server-side services over the web by dynamically producing HTML documents, other kinds of documents, or performing other computations in response to communication from the user.
CGI scripts interact with the user through HTML forms. CGI programs reside in a special directory and provide service through a regular web server.
Input to CGI programs is passed to the program using web forms. Forms include text fields, radio buttons, check boxes, popup boxes, scroll tables, and the like.
Thus retrieving input is a two-step process: you must create an HTML document that provides forms to allow users to pass information to the server, and your CGI or Servlet program must have a means for parsing the input data and determining the action to take. For CGI, you can either code it yourself, find libraries or examples on the internet that handle CGI input. For perl The CGI.pm perl module provides excellent functionality (see cgi.pm. In PHP using method="GET" form variables can simply be retrieved by pre pending the name used in the form definition with a $.
Forms are designated within an HTML document by the fill-out form tag:
<FORM METHOD = "POST" ACTION = "http://form.url.com/cgi-bin/cgiprogram"> ... Contents of the form ... </FORM>
The URL given after ACTION is the URL of the CGI or PHP program (your program). The METHOD is the means of transferring data from the form to the CGI program. In this example, we have used the "POST" method, which is the recommended method with CGI. There is another method called "GET", that can be used easily with PHP. Both will be discussed in the next section.
Within the form you may have anything except another form. The tags used to create user interface objects are INPUT, SELECT, and TEXTAREA.
The INPUT tag specifies a simple input interface:
<INPUT TYPE="text" NAME="thisinput" VALUE="default" SIZE=10 MAXLENGTH=20> <INPUT TYPE="checkbox" NAME="thisbox" VALUE="on" CHECKED> <INPUT TYPE="radio" NAME="radio1" VALUE="1"> <INPUT TYPE="submit" VALUE="done"> <INPUT TYPE="radio" NAME="radio1" VALUE="2" CHECKED> <INPUT TYPE="hidden" NAME="notvisible" VALUE="5">
Which would produce the following form:
The different attributes are mostly self-explanatory. The TYPE is the variety of input object that you are presenting. Valid types include "text", "password", "checkbox", "radio", "submit", "reset", and "hidden". Every input but "submit" and "reset" has a NAME which will be associated with the value returned in the input to the CGI program. This will not be visible to the user (unless they read the HTML source). The other fields will be explained with the types:
The second type of interface is the SELECT interface, which includes popup menus and scrolling tables. Here are examples of both:
<SELECT NAME="menu"> <OPTION>option 1 <OPTION>option 2 <OPTION>option 3 <OPTION SELECTED>option 4 <OPTION>option 5 <OPTION>option 6 <OPTION>option 7 </SELECT> <SELECT NAME="scroller" MULTIPLE SIZE=7> <OPTION SELECTED>option 1 <OPTION SELECTED>option 2 <OPTION>option 3 <OPTION>option 4 <OPTION>option 5 <OPTION>option 6 <OPTION>option 7 </SELECT>
Which will give us:
The SIZE attribute determines whether it is a menu or a scrolled list. If it is 1 or it is absent, the default is a popup menu. If it is greater than 1, then you will see a scrolled list with SIZE elements. The MULTIPLE option, which forces the select to be a scrolled list, signifies that a more than one value may be selected (by default only one value can be selected in a scrolled list).
OPTION is more or less self-explanatory -- it gives the names and values of each field in the menu or scrolled table, and you can specify which are SELECTED by default.
The final type of interface is the TEXTAREA interface:
<TEXTAREA NAME="area" ROWS=5 COLS=30> Mary had a little lamb. A little lamb? A little lamb! Mary had a little lamb. It's fleece was white as snow. </TEXTAREA>
As usual, the NAME is the symbolic reference to which the input will be bound when submitted to the CGI program. The ROWS and COLS values are the visible size of the field. Any number of characters can be entered into a text area.
The default text of the text area is entered between the tags. Whitespace is supposedly respected (as between <PRE> HTML tags), including the newline after the first tag and before the last tag.
http://asdf.asdf.asdf/asdf?thisinput=default&thisbox=on&radio1=2Everything after the '?' is the query string. You'll see that a number of expressions appear concatenated by & symbols -- each expression assigns a string value to each form object. In this case, the text field named "thisinput" has the value "default", which is what was typed into the field, the checkbox "thisbox" has the value "on", and the radio button group "radio1" has the value "2" (the second button is checked -- note that this is the value I gave it, not a default value. The default is "on").
Let's look at another example from the second form:
http://zxcv.zxcv.zxcv/zxcv?menu=option+4&scroller=option+1&scroller=option+2
The menu has option 4 selected, and the scroller has option 1 and option 2 selected. Note that spaces are converted to '+' symbols in the URL string. The character '+' is converted to its hex value %2B. Other characters similarly converted are & (to %26), % (to %25), and $ (to %24). This conversion is automatic.
Using GET is not recommended, however. Some systems will truncate the URL before passing it to the CGI program, and thus the QUERY_STRING environment variable will contain only a prefix of the actual query string. Instead, you should use the POST method.
The POST query string is encoded in precisely the same form as the GET query string, but instead of being passed in the URL and read into the QUERY_STRING variable, it is given to the CGI program as standard input, which you can thus read using ANSI functions or regular character reading functions. The only quirk is that the server will not send EOF at the end of the data. Instead, the size of the string is passed in the environment variable CONTENT_LENGTH. If you are programing in C this can be accessed using the normal stdlib.h function:
char *value;
int length;
value = getenv("CONTENT_LENGTH");
sscanf(value, "%d", &length);
In perl it can be accessed as:
$ENV{'CONTENT_LENGTH'}
Decoding the data is thus just a question of walking through the input and picking out the values. These values can then be used to determine what the user wants to see.
An example linear array symbol table based mechanism written in C for parsing the input string is located at cgiparse.c. If you are programming in C it is probably easiest just to cut and paste these into your own code than to update the Makefile. You can use this in your CGI programs by calling Initialize() at the beginning of your code, and then calling GetValueFor(key) to retrieve the bindings for each of the FORM parameters. The value returned is a BindingSet structure, which allows you to scroll through value bindings for the variable. See the comments in the file for more detail.
The only work you have to do apart from constructing an HTML document on the fly with the output from the query is to add a short header at the top of the file. Your header will represent the MIME type for HTML, and consists of a single line of text followed by a blank line:
content-type: text/html <HTML> ... file ... </HTML>
There are, of course, many other types that you can return, but this is all you'll need to return your database queries.
CGI returns the HTML document to the user through standard output from the program, so you can just use a regular printf function in your C programs or print in Perl programs. In PHP you can use echo or just mix inline HTML with marked code blocks. The format for setting the content type (in C) is just:
printf("content-type: text/html\r\n\r\n");
Here is sample C code or a CGI used at Stanford that talks to an Oracle Database.
source code.
The HTML page demonstrates a few input features, though the only ones
that do anything are the username and password fields. These are used to
log onto an Oracle account when the CGI program is executed, create a
table, do some insertions, demonstrate the production of HTML formatting
through queries on the data (including a demonstration of constructing a
new form, which may provide some of you with ideas of how to make a really
cool interface), and then drop the table from your database. You may
freely cannibalize whatever portions you find useful as long as due credit is given.