Exercise 4 - MySQL and JSON

For this exercise, you will start working with your group's server. Of course, that means some of the parts will be completed by your group, not necessarily individually.

1. [This step is all once-per-group work. Coordinate as necessary.] If your group hasn't already, complete the basic OS installation on your server. Install Apache and enable the userdir module and the expires module:

   sudo apt-get install apache2-mpm-prefork
sudo a2enmod userdir
sudo a2enmod expires

   Edit the file /etc/apache2/mods-enabled/userdir.conf to allow any overrides in an .htaccess file. Remember that when you make any changes to the server config, you need to restart Apache:

   /etc/init.d/apache2 restart

   If you have problems with your Apache config, your first stop should be the error log file: /var/log/apache2/error.log.

   Install the MySQL server and client: (Make sure everyone in the group knows the MySQL root password.)

   sudo apt-get install mysql-server mysql-client

   Note: If you would like to choose a different web or database server, you are welcome to do so with the following cautions: (1) my instructions will be for Apache/MySQL; (2) Apache is very flexible and well-suited for the many apps running together and config things we'll be doing in this course—other open source servers might not have the flexibility to deal with these (slightly odd) situations.

2. Create a database on your server. (Perhaps name the database your userid to prevent conflicts with group members.)

   Create a table people with columns id (an auto increment integer and the primary key), firstname, and lastname (both varchars).

   Create a table purchases with columns id (an auto increment integer and the primary key), person_id (a foreign key to the people table), date (a datetime), and price (a decimal(5,2)).

   For MySQL, the statements to create the tables are like this:

   CREATE TABLE `people` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `firstname` varchar(100) DEFAULT NULL,
     `lastname` varchar(100) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB CHARSET=utf8;
   
   CREATE TABLE `purchases` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `person_id` int(10) unsigned NOT NULL,
     `date` datetime NOT NULL,
     `price` decimal(5,2) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `person_ind` (`person_id`),
     CONSTRAINT `person_key` FOREIGN KEY (`person_id`) REFERENCES `people` (`id`)
   ) ENGINE=InnoDB CHARSET=utf8;

   Use the INSERT statement to populate these tables with a couple of rows of test data.

3. Use the mysqldump command to produce a dump of the database in a file database.txt.

4. Download the (UTF-8 encoded) JSON file exer4-courses.json which contains some class lists we can work with. [The course and instructor data is from some semester in the past; the student userids and registrations are randomly generated, so any similarity to real students is a coincidence.]

   Write a program in whatever programming language makes you happy (as long as it has a JSON library) that:

   1. Uses a JSON library to read the exer4-courses.json into a data structure.
   2. Counts the total number of courses each student is registered in. (e.g. “rzk11” is registered in 22 courses—I didn't say the data was realistic).
   3. Uses the JSON library to output that information as JSON in a file output.json in some sensible format.

   Some possible JSON libraries: JSON for Python, JSON for Ruby, JSON for PHP, JSON for Java.

5. Upload your database dump, program from your previous step, and the produced output.json to your group's web server. Create a public_html directory and put your files there (i.e. in /home/userid/public_html/).

   For group n, port 8000+n on cmpt470 is forwarded to your port 80. So, if everything is set up properly, your files should be available at a URLs like:

   http://cmpt470.csil.sfu.ca:8000+n/~userid/output.json

   [If for some reason, you have a server running on port 8080, you can find it on port 9000+n on cmpt470.]

6. Create an .htaccess file in your public_html that uses the AddType directive to make your program code serve as plain text (media type text/plain) and your JSON file with the correct type (application/json;charset=utf-8).

   Use Apache mod_expires to set a cache expiry time on your JSON file (but not your other files) of 6 hours after access.

Submit the URLs of your work through the Course Management System.