CSCI 343 — Experiences in middleware and data on the web

Goal

The goal of this lab is to demonstrate a few small résumé-extending skills.

Your web page at www.cs.unca.edu

If you have an account on the Computer Science workstations, you can create a web page with the address of http://www.cs.unca.edu/~userid . Some have argued that requiring students to do this for a class violates privacy regulations. For this reason, we will tell you have to both create and delete the web pages created during this lab. If they are present tomorrow, don’t sue us.

Creating a web presence

You start by creating a directory called public_html to hold your web files. While you are at it, create a file called index.txt within that directory.

[…]$ mkdir public_html
[…]$ chmod 755 public_html
[…]$ echo I am doing my lab > public_html/index.txt

At this point, you should be able to access your index.html file with the http://www.cs.unca.edu/~userid/index.txt URL. And, yes, the public_html does not appear in the URL.

Copying your XML and DTD files

Find the directory where you stored your NamesInput.dtd and NamesInput.xml files in the last lab and copy them into your public_html directory. (The copy command is called cp .)

You should be able to access both files from the browser using the appropriate URL.

Show someone your XML in the browser. The browser is unlikely to display your DTD file, though it will download it.

Referencing the DTD from the XML

Sometimes, probably rarely, XML files contain a document type declaration to the specifying DTD file. This looks something like the following:

<!DOCTYPE accounts PUBLIC
  '-//UNCA Computer Science//Shared Server accounts input//EN'
  'http://www.cs.unca.edu/SharedServer/AccountsInput.dtd'
>

Add a DOCTYPE to your XML file or just fix the one you already have. The first field must match the name of your XML file’s root entity and the second field should be PUBLIC. For today, it really doesn’t matter what you put in the third field so don’t change it; but the last field should be a reference your DTD file.

If you load the XML file into your browser, you’ll have to view the source to see the change.

Just like in the last lab, use xmllint to see if your XML file is valid.

[…]$ xmllint --noout --valid public_html/NamesInput.xml

Finally use the W3C Markup Validation Service to validate your XML and show the approved result to someone. Don’t worry about the message about the missing charset attribute. That is common with Apache web servers.

Uncreating a web presence

If you are concerned about outsiders viewing these files, execute the following command.

[…]$ chmod 700 public_html

A little SQL

Your next task is to write an SQL SELECT statement using the social database used in Homework 4. To test your SQL statement, you will need access to the database which can be obtained in one of two ways.

  1. Download a copy of the database as either a a sqlite3 database file or an SQL statement? and execute your commands using sqlite3
  2. Use the mysql-workbench GUI and connect to the database socialDB on joe.cs.unca.edu using the csci343 account.

Now write a SQL statement to print the names and grades of all pairs of friends that are in the same grade. Within a row, order the names of the two friends alphabetically. Then order the rows, alphabetically by the student names.

This is a difficult SQL statement to write. Feel free to consult your classmates in generating this statement. Also, end the SELECT with an ORDER BY clause.

Here is the output as generated by sqlite3.

Alexis|Gabriel|11
Alexis|Jessica|11
Andrew|Kris|10
Brittany|Haley|10
Brittany|Kris|10
Cassandra|Gabriel|9
Gabriel|Jessica|11
Gabriel|Jordan|9
Haley|Kris|10
John|Logan|12
Jordan|Kyle|12
Jordan|Logan|12
Jordan|Tiffany|9

Don’t stop until you have it working. If you have tried to 23 minutes and don’t have the right SELECT, talk to the instructor.

Executing the SQL from a program

This is the first time, I’ve tried this in a lab, so it may be rough. What you are going to do is try out several APIs for executing SQL from a program.

In each case, you will be given a link to an existing program presented in a recent class lecture and asked to modify it. The modification will be pretty minor. You will need to change the query and the code the prints the values within a row.

Python

We start with Python because that is the program you are least likely to have listed on your résumé. Download the listfriends Python program and store it under the filename samegradefriend.py .

Edit the file to the query you just generated. You run your file with the following command.

[…]$ python samegradefriend.py

slqite3 C

Now try a C interface that’s much easier that ODBC. Download the listfriends C program and store it under the filename samegradefriend.c . If you are using NetBeans, you can create a project and store samegradefriend.c within it. Again make the needed modifications.

You need to compile the program with the sqlite3 library. For the command line, you can do with this the following command.

[…]$ gcc -o samegradefriend -l sqlite3 samegradefriend.c

You can run the program simply by typing samegradefriend , but you must have a copy of social.sqlite3 located in the appropriate directory.

JDBC

We really ought to use NetBeans for this one. Create a NetBeans project with a Java main class file named SameGradeFriend . Cut-and-paste ListFriends into this file and make the needed changes.

It may take a while to get Java to stop complaining; but, once it does, run your program.

ODBC

You are on your own for this one. Download the files from the ODCB lecture and try your best if there is any time left.