Lab 3
A Real Application

Introduction

This week you will turn your site into a real application! The key is adding backend server support with a database. Instead of asking for a pre-set text file, you will ask for your perl script. Your script will call a database and return text or XML.

Requirements for this Lab

Note: if you have a compelling case to modify the requirements based on your site, discuss with the instructor in advance. Normally, requests for a substantive change should be made before the end of the lab period when the lab begins.

  1. All data now comes from a database, accessed by your Perl script.
  2. There should be some way for the user to add new "things" to your database. These could be people, books, races, whatever, but there should be at least 3 different "attributes" associated with each one (e.g., name, address, and phone).
  3. Adding the new things should be done via AJAX (you'll probably use a <form>, but it shouldn't really submit the form to the server - use AJAX instead).
  4. There should be at least two different ways to query info from the database to display in your main page. For instance, you might have an option to search for people, then each person is clickable for more details. Most likely, this should be the two interactions you used for Lab02, but it can change (Note: the words "query" and "interaction" are used interchangeably here.). More specific requirements:
    1. You can use either XML or text to send the data, but at least one query must be asynchronous.
    2. Both queries must use AJAX.
    3. The two queries must fetch different kinds of things. For instance, one could fetch a list of items (with ID's and names), and one could then fetch the details for that item. But one query to fetch item #23 and another to fetch item #87 does not count as two distinct queries. It's fine if both queries are fetching data from the same database "table", but different columns should be involved.
    4. At least one of these queries need to fetch data that was stored in the "adding new things" step above (this also lets you verify that the "adding new things" part works!)
    5. At least one of the queries must be based on a value that the user enters in a search box OR at least one query must be based on something the user clicks, where clicking on different elements yields different results (e.g. clicking on "Fred" vs. clicking on "Wilma"). This probably means you will have a "WHERE" clause in your SQL query - if it doesn't, check with the instructor.
    6. If the user clicks twice on something, the page should not display the same information twice. So if the user clicks a button to get a list of items, then clicks again, be sure to delete the old list before you insert the new list.
  5. As always, your code must be commented!
  6. You may use PHP or Perl. Examples given are in Perl.
  7. In your default.htm page, under "Lab3" include:
    1. A link to it452/Lab03/index.html
    2. Brief user instructions (aimed at the instructor) explaining how to see your interactions (what to click on, enter, etc. - make it easy to see that you have earned full credit!)
  8. Ensure your page works with Chrome. Having it work on IE/Firefox is encouraged but not required.
  9. Ensure that NO errors appear in the Console when you execute your lab in Chrome.
  10. Ensure all your pages validate and that you have met all requirements.
  11. Check AGAIN to make sure everything still works. You'd be surprised how often things seem to break at the end!

Suggested Plan of Attack

  1. Don't try to solve everything at once! Do one step (like writing the Perl file), and ensure it works before you proceed.
  2. Read the very important tips about creating Perl files. Perl will not work if you don't follow this!
  3. Read the tips on debugging Perl, so you know about these for later.
  4. Get password info from the instructor.
  5. Plan and create the database table(s) you will need (use MySQL Workbench - see helpful info below).
  6. Insert some dummy data into the tables for testing (again, using MySQL Workbench).
  7. Write your Perl program to query the database. Test via entering the browser URL, before you attempt to use it from JavaScript. If you don't know what each of these things is, ask!
  8. Get your first AJAX interaction from last week working, where you replace the static file with the Perl program to read from the database. At first, the "query" can be hard-coded so that it always returns all the data from the database.
  9. Improve your interaction so that the database results depend on what was clicked or entered in a search box (or do this for interaction #2).
  10. Get your second AJAX interaction working.
  11. Implement the ability for the user to add new data to the database.
  12. Make sure that clicking a second time doesn't continue to show the old results or an old list.

NOTE: all HTML files must validate as HTML5 without errors for full credit. The penalty for a file that does not validate is 10%.

Helpful Stuff

  1. As always, the "Error Console" in Chrome/Firefox is invaluable.
  2. To connect to the database from Perl, use something like (password to be provided): $databaseHandle = DBI->connect( "dbi:mysql:database=mXXXXXX;host=cardhu.cs.usna.edu:3306;user=mXXXXXX;password=TBD" );
  3. To create tables and edit your tables, use MySQL Workbench. Click on "New Connection" and name it cardhu. Set the host to cardhu.cs.usna.edu, and enter your username. It should connect after prompting for a password. Right click on your name to create tables, and right click on your table names to edit and view their rows. TIP: every table should have an "id" column, and you should set this to "auto-increment" when you create it.
  4. For problems with perl, see tips about debugging Perl
  5. On the command line, database errors will usually appear automatically. From the browser, they won't - instead insert this as a debug: print $DBI::errstr;
  6. Do not use the die() function alone - the error it generates can't be seen from your web page.
  7. Useful SQL examples:
    1. CREATE TABLE comments (USER_NAME VARCHAR(20), TIMESTAMP DATETIME, COMMENT MEDIUMTEXT, PAGE INTEGER);
    2. INSERT INTO comments (USER_NAME, TIMESTAMP, COMMENT, PAGE) VALUES ('Jamie', '2006-09-27 11:30:00', 'hi', 2);
    3. CREATE TABLE topics (TOPIC VARCHAR(20), OWNER VARCHAR(20));
    4. INSERT INTO topics (TOPIC, OWNER) VALUES ('Cars', 'Jamie');

Deliverables

  1. All of your files for Lab03 should be in a folder called "it452/Lab03" (without the quotes) on the Web drive.
  2. Your main page for Lab03 should be called "index.html" (without the quotes) and placed inside the folder it452/Lab03.
  3. Your instructor will assume that your web pages are viewable at http://intranet.cs.usna.edu/~mXXXXXX/it452/Lab03/index.html where XXXXXX is your alpha number. You should check that this URL is viewable and that everything works correctly from a computer where somebody else is logged in.
  4. All files must be complete and saved to your Web drive before you submit the hardcopy of your assignment. Do NOT modify your files after you have submitted your assignment.
  5. Turn in the following hardcopy on or before the due date, stapled together in the following order (coversheet on top):
    1. Completed assignment cover and grading sheets.
    2. The first page of the output of the validator, as run on the final version of your Lab03/index.html. This should show that your document correctly validated, but turn it in anyway if you can't get your page to validate.
    3. A printout of the source to your Lab03/index.html file. Print 4-pages per page, double-sided if possible.
    4. Printouts of external JavaScript files, if any.
    5. Printout of the source of the Perl/PHP files that you use.

Addenda

NOTE: Do NOT put spaces into your URLs. For examples something like "query.cgi?q=dog" is fine, but "query.cgi? q = dog" is not.

NOTE: Dealing with anonymous functions can be tricky. In particular, suppose you were creating a table row, and you wanted each cell to pop up the current cell number (cell 1, cell 2, etc.) when you click on it. You might make code that looked like this:

        for (var i =0; i < elems.length; i++) {
           // Create the <td>
           newTD = document.createElement("td");
           newTD.innerHTML = elems[i];
           newTD.onclick   = function() {window.alert ("Hi, this is cell "+i) };
 
           // Add it to the row
           newTR.appendChild(newTD);   
        }

This code looks good but will not work properly. Remember what you learned about closures in class. The alert message will be the same (Hi, this is node 5) regardless of what cell you click on. The reason is that when you use function() to create the anonymous function, it creates a closure that wraps the variables that are currently in scope. Each one of these closures that is created all refer to the same variable 'i'. So when the code actually executes (when someone clicks on the cell), the 'i' value that is used is the value of 'i' that was set during the last iteration. Solution? First, make a new function as follows:

        function makeMyEventHandler(num) {
               return function() {window.alert("Hi, this is cell "+num)};
        }

Note that this a function that returns a function. Then change part of the loop body to be this:

       newTD.onclick   = makeMyEventHandler(i); // Yes, parentheses here!

Why does this work? Well, when you call makeMyEventHandler(), you are creating a new scope. The value of 'i' is copied into 'num'. Because a new copy of 'num' is made every time the function is called, each cell gets its own separate copy to reference.

It's worth reading this one more time - this issue comes up more often than you might think.

NOTE: XML is great, but can be a little trick to get working. If you choose to use XML, here are some tips:

  1. You should *not* have a call like:
         print header();
        
    Instead, you want:
         print "Content-type: text/xml; charset=UTF-8\n\n";
         
  2. To be a valid XML document, there must be a "root" element that surrounds all the other elements. So, if you have 3 elements, put something like a element around all of them.
  3. Before trying to get your Perl-generated XML to work via Javascript and AJAX, first use Chrome or Firefox (the XML check doesn't reliably work in IE!) and just run the Perl script via typing the URL into the browser address bar (with arguments if needed). Check to make sure you have something like this:

    Notice that the browser is automatically formatting the XML in a "XML tree" view, with little minus signs next to some elements (you can click on these to collapse it). If you don't get this, then something is wrong! (a warning about no "style information" is fine). If you don't see this, check that the "Content-type" header is correct and that you have a single root element. For instance, here is what you get if you header is wrong (text/plain instead of text/xml):

    Or, if you don't have a single root element, you might get an error like this: