Massachusetts Institute of Technology
Department Electrical Engineering and Computer Science

6.916: Software Engineering of Innovative Web Services
Problem set 4

This is an obsolete version of problem set 4. The current version is available at http://philip.greenspun.com/teaching/psets/ps4/ps4.adp.

• transactions chapter from Oracle Concepts document (http://philip.greenspun.com/sql/ref/transactions)

Online assistance: 6.916 Q&A forum

Objectives

The Huge Picture

The Big Picture

Another issue is a perennial side-show in the world-wide computer programming circus: the spectacle of nerds arguing over programming tools. The data model can't represent the information that the users need, the application doesn't do what what the users need it to do, and instead of writing code, the "engineers" are arguing about Java versus Lisp versus Perl versus Tcl. If you want to know why computer programmers get paid less than medical doctors, consider the situation of two trauma surgeons arriving at an accident scene. The patient is bleeding profusely. If surgeons were like programmers, they'd leave the patient to bleed out in order to have a really satisfying argument over the merits of two different kinds of tourniquet.

If you're programming one Web page at a time, you can switch to the Language du Jour in search of higher productivity. But you won't achieve significant gains unless you switch from writing code for one page. You need to think about ways to write down a formal description of the application and user experience, then let the computer generate the application automatically.

The Medium-Sized Picture

• links among knowledge objects
• facilities for browsing and searching that are more powerful than in the standard /bboard system, where browse-by-category is the only option beyond full-text search
• tracking and rewarding of contributions and reuse

A data model to represent your data model

1. Xerox PARC and MIT developed object-oriented programming systems in the 1970s, including Smalltalk and the Lisp Machine
2. People hailed these systems as rather advanced
3. The business community finally picked up the object-oriented buzz during the 1990s

In order to make your system comprehensible to the CIO/CTO types who will be adopting it, we'll use their vocabulary. A table row is an "object" and each column is an "element" of that object.

We need a way to represent the kinds of objects that our system will represent first. Let's assume that we'll have at least the following object types:

• person
• document
• class
• department
• technology
• administration group
• research group
• student group
• research sponsors
• employers of alumni

For each object type we'll be creating an Oracle table. For each Oracle table we create, we store one row in the metadata table:

create table km_metadata_objects (
        table_name              varchar(21) primary key,
	-- use this to build the mapping table
	really_short_name	varchar(10),
        pretty_name             varchar(100) not null,
        pretty_plural           varchar(100)
);

• name (a short description of the thing)
• overview (a longer description)
• who created this object
• when they created it
• who modified it last
• when they modified it
• who has the right to modify it
• who has the right to view it
• does it need approval?
• has it been approved?
• if so, by whom and when?
• should it go into a site-wide index?
• if so, under what section?

For elements that are unique to an object type, we need to represent one row in a meta data table per element. Note that we also use this table to represent links from object-type to object-type (abstract_data_type of "mapping"):

create table km_metadata_elements (
        metadata_id             integer primary key,
        table_name              not null references km_metadata_objects,
        column_name             varchar(30) not null,
        pretty_name             varchar(100) not null,
        abstract_data_type      varchar(30) not null, 	-- ie. "text" or "shorttext" "boolean" "mapping" "user" 
	-- this one is not null except when abstract_data_type is "mapping" or "user"
        oracle_data_type        varchar(30),   -- "varchar(4000)"
        -- e.g., "not null" or "check foobar in ('christof', 'patrick')"
        extra_sql               varchar(4000),
        -- values are 'text', 'textarea', 'select', 'radio', 
	-- 'selectmultiple', 'checkbox', 'checkboxmultiple', 'selectsql'
        presentation_type       varchar(100) not null,
        -- e.g., for textarea, this would be "rows=6 cols=60", for select, Tcl list,
        -- for selectsql, an SQL query that returns N district values
        -- for email addresses mailto:
        presentation_options    varchar(4000),
        -- pretty_name is going to be the short prompt, 
	-- e.g., for an update page, but we also need something
	-- longer if we have to walk the user through a long form
        entry_explanation       varchar(4000),
	-- if they click for yet more help 
        help_text               varchar(4000),
        -- note that this does NOT translate into a "not null" constraint in Oracle
        -- if we did this, it would prevent users from creating rows incrementaly
        mandatory_p             char(1) check (mandatory_p in ('t','f')),
        -- ordering in Oracle table creation, 0 would be on top, 1 underneath, etc.
        sort_key                integer,
        -- ordering within a form, lower number = higher on page 
        form_sort_key           integer,
        -- if there are N forms, starting with 0, to define this object, 
	-- on which does this go?  (relevant for very complex objects where
	-- you need more than one page to submit)
        form_number             integer,
        -- for full text index
        include_in_ctx_index_p  char(1) check (include_in_ctx_index_p in ('t','f')),
        -- add forms should be prefilled with the default value
        default_value           varchar(200),
        -- if the abstract_data_type is mapping, the table to which 
	-- we're mapping
        map_to_which_table_name  references km_metadata_objects,
	check ((abstract_data_type not in ('mapping','user') and oracle_data_type is not null)
                or
              (abstract_data_type in ('mapping','user'))),
        unique(table_name,column_name)
);

Exercise 1: Use prototype builder to construct admin pages for your metadata

> cd /web/yourservername/www/admin/
> chmod a+w km

Exercise 2: Fill your metadata tables with info

for the person type

date_of_birth, title

for the class type

prerequisites, professors (link to objects of type person)

for the document type

links to every possible other kind of object

for the technology type

manufacturer, model number

for the research_sponsors type

program officers (links to objects of type person)

Exercise 3: Write a program to generate DDL statements

Each object table should have an object_id column. Use a single Oracle sequence to generate keys for this column.

Remember that for every unique pairing of tables through an element of type "mapping", you'll have to create a mapping table to represent the many-to-many relation. Call these "km_t1rsn_t2rsn_map" where "t1rsn" is the "really short name" of table 1. For simplicity, assume that associations are bidirectional, e.g., if you associate the class English 101 with the English deparment the English department is associated with the class. You'll want to build a primary key constraint into the map table definition but also, for query efficiency, create a concatenated index on the columns in the other order. (The trees chapter of SQL for Web Nerds, at http://photo.net/sql/trees.html, gives some examples of concatenated indices. Also read the composite indices section of the Oracle Tuning manual: http://philip.greenspun.com/sql/ref/composite_indices. See also the Oracle SQL Reference section at http://philip.greenspun.com/sql/ref/create_index.)

Another good idea for the mapping table is to include a map_comment column where you can store a user-entered reason for relating two objects. Here is an example table defintion:

create table km_person_class_map (
	person_id	not null references persons,
	class_id 	not null references classes,
	map_comment	varchar(4000),
	creation_user	not null references users,
	creation_date	date not null default sysdate,
	primary key(person_id, class_id)
);

Exercise 4: Write a program to generate a "drop all tables" script

Exercise 5: Build the knowledge capture pages

When object-create-2.tcl is done inserting the row into the database, it should ns_returnredirect to object-display.tcl. This page should have small hyperlinks to edit single fields at a time (all linking to object-edit-element.tcl with different arguments). This page should show all the currently linked objects and have "add link" hyperlinks to object-add-link.tcl.

The page returned by object-add-link.tcl will look virtually identical to /km/index.tcl and will in fact link to the same URL: object-browse-one-type.tcl. When called with only table_name, this page will display a table of object names with dimensional controls at the top. The dimensions should be "mine|everyone's" and "creation date". The user ought to be able to click on a table header and sort by that column.

When called with extra arguments, object-browse-one-type.tcl will pass those arguments through to object-view-one.tcl and, if the user clicks a confirmation button, will eventually result in object-add-link-2.tcl being invoked. The extra arguments should be link_to_table_name and link_to_object_id.

Exercise 6: Gather statistics

-- we will be updating the reuse_p column of views so it 
-- will be easier to have a primary key 
create sequence km_object_view_id;

create table km_object_views (
	object_view_id	integer primary key,
	-- which user
	user_id		not null references users,
	-- two columns to specify which object 
	object_id	integer not null,
	table_name	varchar(21) not null,
	view_date	date not null,
	reuse_p		char(1) default 'f' check(reuse_p in ('t','f'))		
);

After the ns_conn close, insert a row into the km_object_views table iff there isn't already a log row for this user/object pair within 24 hours. You could do this with a

1. open a transaction
2. lock the table
3. count the number of matching rows within the last 24 hours
4. compare the result to 0 and insert if necessary
5. close the transaction

insert into msg_id_generator (last_msg_id)
select ('000000') from dual
where 0 = (select count(last_msg_id) from msg_id_generator);

Date/time arithmetic: see http://photo.net/sql/dates.html.

Exercise 7: Gather more statistics

Exercise 8: Explain the concurrency problem in Exercise 7

Exercise 9: Do a little performance tuning

Exercise 10: Display Statistics

Exercise 11: Build a site-wide index

Create a file /admin/km/generate-sws-triggers.tcl to read the meta data tables and

1. generate database triggers that will automatically put indexable object content into the site_wide_index table
2. insert one row into sws_table_to_section_map for every object type defined in km_metadata_objects

Exercise 12: Query pages for the site-wide index

See http://philip.greenspun.com/sql/ref/dbms_lob and http://philip.greenspun.com/sql/ref/intermediatext as references.

Optional

Who Wrote This and When

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