Massachusetts Institute of Technology
Department Electrical Engineering and Computer Science

6.916: Software Engineering of Innovative Web Services
Problem set 5

Reading for this week:

• http://photo.net/doc/general-comments.html
• http://arsdigita.com/free-tools/oracle-driver.html (documents the BLOB interface)
• Oracle Intermedia Text reference (http://philip.greenspun.com/sql/ref/intermediatext)
• Oracle Tree Extensions sections in Oracle 8: The Complete Reference
• SQL for Web Nerds, tree chapter

Online assistance: 6.916 Q&A forum

Objectives

The Huge Picture

The Big Picture

• collaboratively maintain the family's geneaology
• associate photographs and documents with individuals or subtrees at different ages, e.g., "Joey Smith at Age 3" or "Bill and Margaret Smith and theirs kids in 1983" or "Alice Wong's English paper, Grade 10"
• plan reunions and smaller get-togethers

Privacy

• show to designated users (i.e., if it is something nasty about your Aunt Maude that your brother would appreciate, you want him to see it but not Aunt Maude)
• show to registered users (i.e., only people in the extended family)
• show to everyone

Commentability

Assumptions

You can assume that your server needs to serve only one family at a time. You don't have to persistently maintain a tree for more than one family.

For the purposes of the problem set, you can assume that your family is trustworthy. You can let any registered user edit anything.

Exercise 1: Build the data model

Here are some guidelines for your data model:

• create a table called "family_relatives" to store the family members. Here is a skeleton:

  
  create table family_relatives (
  	relative_id	integer primary key,
  	-- optional pointer to users table
  	user_id		references users,
  	spouse		references family_relatives,
  	mother		references family_relatives,
  	father		references family_relatives,
  	-- in case they don't know the exact birthdate
  	birthyear	integer,
  	birthday	date,
  	-- sadly, not everyone is still with us
  	deathyear	integer,
  	first_names	varchar(100) not null,
  	last_name	varchar(100) not null,
  	sex		char(1) check (sex in ('m','f')),
  	-- note the use of multi-column check constraints
  	check ( birthyear is not null or birthday is not null)
  );
  

• create a table called "family_photos" to store photographs and scanned documents, such as diplomas. We'll draw some inspiration from the /doc/sql/general-comments.sql file:

  
  create table family_photos (
  	family_photo_id		integer primary key,
  	photo			blob not null,
  	-- file name including extension but not path
  	client_file_name	varchar(500),
  	file_type		varchar(100),	-- this is a MIME type (e.g., image/jpeg)
  	file_extension		varchar(50), 	-- e.g., "jpg"
  	caption			varchar(4000),
  	-- when was this photo taken
  	item_date		date,
  	item_year		integer,
  	original_width		integer,
  	original_height		integer,
  	access_control		varchar(20)
               check (access_control in ('public', 'family', 'designated')),
  	check (item_date is not null or item_year is not null)
  );
  
  -- a photo might contain more than one person so we need
  -- an extra table
  create table family_photo_relative_map (
  	relative_id		references family_relatives,
  	family_photo_id		references family_photos,
  	primary key (relative_id, family_photo_id)
  );
  
  create table family_photo_access_control (
  	family_photo_id		references family_photos,
  	user_id			references users,
  	-- note the order we spec the primary key
  	-- (makes it fast to ask "who gets to see this photo")
  	primary key (family_photo_id, user_id)
  );
  

• create a table called "family_stories" to record stories about family members at different ages:

  
  create table family_stories (
  	family_story_id		integer primary key,
  	story			clob not null,
  	item_date		date,
  	item_year		integer,
  	access_control		varchar(20)
               check (access_control in ('public', 'family', 'designated')),
  	check (item_date is not null or item_year is not null)
  );
  
  -- a story might be about more than one person
  create table family_story_relative_map (
  	family_story_id		references family_stories,
  	relative_id		references family_relatives,
  	primary key (relative_id, family_story_id)
  );
  

  Exercise 2: The family tree page

  Build a set of Tcl scripts at /family that will allow users to
  • view the family tree
  • add rows to the family_relatives table
  • remove rows added by mistake
  A good thing to remember is that the Macintosh user interface works well. Pick the object first and then the verb. So let the user add a parent, spouse, or child by first selecting an existing relative.

  Exercise 3: Uploading photos

  Extend the "/family/one-relative.tcl" page that you built in Exercise 2. so that users can attach photos to relatives. You'll need to use the ns_ora blob_dml_file API call documented in http://arsdigita.com/free-tools/oracle-driver.html and also ns_queryget from the AOLserver API.

  You can find source code models for photo uploading in /comments/upload-attachment.tcl.

  Once a photo is in the system and associated with one relative, provide an interface to add an association to one or more additional relatives.

  Edit the family tree display page to show an extra little "p" when one or more photos including a relative are available. Once the user clicks on an individual, show the available photos of that person organized by the age the person would be in the photo (based on what your server knows about the date of the photo and the birth date of the relative).

  For writing photos back to the Web, you'll need to return appropriate HTTP and MIME headers, then call ns_ora write_blob to pull the photo direct from Oracle and write it to the Web. Examples of ACS pages that do this include the following:

  • http://software.arsdigita.com/www/file-storage/download-file.tcl
  • http://software.arsdigita.com/www/shared/portrait-bits.tcl

  Exercise 4: Making photos a focus for collaboration

  Using the system described at http://photo.net/doc/general-comments.html, extend your photo display pages so that family members can comment on photos and even upload additional photos or documents as attachments.

  Exercise 5: Adding Stories

  Repeat exercises 3 and 4 but for stories. You'll need to use the ns_ora clob_dml API call documented in http://arsdigita.com/free-tools/oracle-driver.html

  Exercise 6: Event Planning Data Model

  Add an event planner, e.g., for reunions, to your system. You'll need to extend the data model to include at least two extra tables: family_events (what is being planned) and family_events_registration (who is signed up). Each event should have a creator who will be the coordinator.

  Exercise 7: Event Planning Pages

  Modify the /pvt/home.tcl page to show registered users upcoming events right at the top of the workspace. If they click on an event summary, take them to a page where they can sign up. Upon registering for the event, the creator of the event should be emailed.

  Provide pages in a /family/events/ directory that will let people see who is signed up for a particular event, spam the attendees, etc. Link this in with general comments so that users can make public comments on an event.

  Exercise 8: Full-text Indexing

  Once you've got a bunch of stories in the family_stories, it will be nice to provide users with the ability to search through them.

  Build a /family/search-stories.tcl page that lets users search through the CLOBs in family_stories. Note that

  1. the SQL LIKE command doesn't work with CLOBs (thank you, Oracle).
  2. if you had many megabytes of text, it would be very slow to sequentially search all of it
  3. it would be nicer to users if querying for "dogs" matched documents containing "dog" (stemming)
  All of these issues can be addressed to some extent by using the Oracle Intermedia text indexing system:

  
  create index family_stories_ctx on family_stories (story)
  indextype is ctxsys.context;
  

  For examples of how to query and present results, read http://photo.net/doc/site-wide-search.html and the Oracle Intermedia Text reference at http://philip.greenspun.com/sql/ref/intermediatext.

  Exercise 9: Access Control

  Develop a test suite of URLs and users and make sure that the main /family area presents the right information to the public, to registered users (the family), and to designated users (ones on the access control list for particular items).

  Optional

  Read Sigmund Freud's Civilization and Its Discontents.

  Who Wrote This and When

  This problem set was written by Philip Greenspun in October 1999 for MIT Course 6.916. It is copyright 1999 Philip Greenspun but may be reused provided credit is given to the original author with a hyperlink to this document.

  It is permanently housed at http://philip.greenspun.com/teaching/psets/ps5/ps5.adp.

  ---

  Maintainer: teadams@mit.edu