Goal
The goal of this lab is to demonstrate a few small résumé-extending skills.
- Putting your XML and associated DTD file on the web
- Writing an SQL statement to perform a friendly query
- Completing a program to use the following middleware platforms:
- Python
- JDBC
- sqlite3 C API
- ODBC
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.
- Download a copy of the database as either a a sqlite3 database file or an SQL statement? and execute your commands using sqlite3
-
Use the mysql-workbench GUI
and connect to the database
socialDB
on joe.cs.unca.edu using thecsci343
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.