Software Engineering for Internet Applications

An overview of software engineering techniques for developing web applications. Based on

• Eve Andersson, Philip Greenspun, and Andrew Grumet Software Engineering for Internet Applications [1], 2006.
• Philip Greenspun SQL for Web Nerds [2]
• Philip Greenspun Philip and Alex's Guide to Web Publishing [3] 1998–2003.

• MIT's 6.171 [4]
• Based on experiences with ArsDigita [5] and photo.net [6] (their story [7]).
• Class sometimes used Open Architecture Community System [8], we now call these Internet Social Networks [9].
• Builds a fancy front-end to a database (cf. Visual Basic).
• Note that memory continues to become cheaper.

• But, a (good) database gives you ACID. Good for multi-user systems.

1. Atomicity: all or nothing
2. Consistency: only legal operations allowed.
3. Isolation: no peeking until the other is done.
4. Durability: once done, you are done forever.

1. Develop data model.
2. Develop legal transactions on model: inserts and updates.
3. Design page flow.
4. Implement pages

• Then deploy, and goto 1.

• Develop user classes.
• Develop use cases.
• Study existing competition, both online and offline.
• Get a catchy hostname.

• Your users table might start out like

create table users (
        user_id                 integer primary key,
        first_names             varchar(50),
        last_name               varchar(50) not null,
        email                   varchar(100) not null unique,
        -- we encrypt passwords using operating system crypt function
        password                varchar(30) not null,
        registration_date       timestamp(0)
);

• Then after a month:

create table users (
        user_id                 integer primary key,
        first_names             varchar(50),
        last_name               varchar(50) not null,
        email                   varchar(100) not null unique,
        password                varchar(30) not null,
        -- user's personal homepage elsewhere on the Internet
        url                     varchar(200),
        registration_date       timestamp(0),
        -- an optional photo; if Oracle Intermedia Image is installed
        -- use the image datatype instead of BLOB
        portrait                blob
);

• After 6 months

create table users (
        user_id                 integer primary key,
        first_names             varchar(50),
        last_name               varchar(50) not null,
        email                   varchar(100) not null unique,
        password                varchar(30) not null,
        -- user's personal homepage elsewhere on the Internet
        url                     varchar(200),
        registration_date       timestamp(0)
        -- an optional photo; if Oracle Intermedia Image is installed
        -- use the image datatype instead of BLOB
        portrait                blob,
        -- with a 4 GB maximum, we're all set for Life of Johnson
        biography               clob,
        birthdate               date,
        -- current politically correct column name would be "gender"
        -- but data models often outlive linguistic fashion so
        -- we stick with more established usage
        sex                     char(1) check (sex in ('m','f')),
        country_code            char(2) references country_codes(iso),
        postal_code             varchar(80),
        home_phone              varchar(100),
        work_phone              varchar(100),
        mobile_phone            varchar(100),
        pager                   varchar(100),
        fax                     varchar(100),
        aim_screen_name         varchar(50),
        icq_number              varchar(50)
);

• Better use more than one table.

create table users (
        user_id                 integer primary key,
        first_names             varchar(50),
        last_name               varchar(50) not null,
        email                   varchar(100) not null unique,
        password                varchar(30) not null,
        registration_date       timestamp(0)
);

create table users_extra_info (
        user_info_id            integer primary key,
        user_id                 not null references users,
        field_name              varchar(100) not null,
        field_type              varchar(100) not null,
        -- one of the three columns below will be non-NULL
        varchar_value   varchar(4000),
        blob_value      blob,
        date_value      timestamp(0),
        check ( not (varchar_value is null and 
                     blob_value is null and
                     date_value is null))
        -- in a real system, you'd probably have additional columns
        -- to store when each row was inserted and by whom

);

-- make it fast to get all extra fields for a particular user
create index users_extra_info_by_user on users_extra_info(user_id);

• User user_id to link the two.

• To add groups you might think to

create table users (
        user_id                 integer primary key,
            ...
        -- a space-separated list of group IDs
        group_memberships       varchar(4000),
        ...
);

• But a much better idea is to create a user_groups table and them create a map:

create table user_group_map (
        user_id         not null references users;
        user_group_id   not null references user_groups;
        unique(user_id, user_group_id)
);

• To answer: “To which groups does Norman Horowitz belong” you do (join 3 tables)

select user_groups.group_name
        from users, user_groups, user_group_map
        where users.first_names = 'Norman' and users.last_name = 'Horowitz'
                and users.user_id = user_group_map.user_id
                and user_groups.user_group_id = user_group_map.user_group_id;

• Your webapp will have: articles, comments on articles, news, comments on news, questions, answers. How many tables do you need?

create table articles_raw (
        article_id              integer primary key,
        -- who contributed this and when
        creation_user           not null references users,
        creation_date           not null date,
        -- what language is this in?
        -- visit http://www.w3.org/International/O-charset-lang
        -- to see the allowable 2-character codes (en is English, ja is Japanese)
        language                char(2) references language_codes,
        -- could be text/html or text/plain or some sort of XML document
        mime_type               varchar(100) not null,
        -- will hold the title in most cases
        one_line_summary        varchar(200) not null,
        -- the entire article; 4 GB limit
        body                    clob
        --articles go thru an editorial process
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
    );

• Maybe you don't trust that people will always remember to add where editorial_status == 'approved', in this case create a view:

create view articles_approved 
        as
        select * 
        from articles_raw
        where editorial_status = 'approved';

• Comments need to refer to the article they are about and the user who posted it:

create table comments_on_articles_raw (
        comment_id              integer primary key,
        -- on what article is this a comment?
        refers_to               not null references articles,
        creation_user           not null references users,
        creation_date           not null date,
        language                char(2) references language_codes,
        mime_type               varchar(100) not null,
        one_line_summary        varchar(200) not null,
        body                    clob,
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

create view comments_on_articles_approved 
        as
        select * 
        from comments_on_articles_raw
        where editorial_status = 'approved';

• Hmmm, that looks just like article. Lets merge them.

create table content_raw (
        content_id              integer primary key,
        -- if not NULL, this row represents a comment
        refers_to               references content_raw,
        -- who contributed this and when
        creation_user           not null references users,
        creation_date           not null date,
        -- what language is this in?
        -- visit http://www.w3.org/International/O-charset-lang
        -- to see the allowable 2-character codes (en is English, ja is Japanese)
        language                char(2) references language_codes,
        -- could be text/html or text/plain or some sort of XML document
        mime_type               varchar(100) not null,
        one_line_summary        varchar(200) not null,
        -- the entire article; 4 GB limit
        body                    clob,
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

-- if we want to be able to write some scripts without having to think
-- about the fact that different content types are merged

create view articles_approved 
        as
        select * 
        from content_raw
        where refers_to is null
        and editorial_status = 'approved';

create view comments_on_articles_approved 
        as
        select * 
        from content_raw
        where refers_to is not null
        and editorial_status = 'approved';

-- let's build a single full-text index on both articles and comments
-- using Oracle Intermedia Text (formerly known as "Context")

create index content_ctx on content_raw (body) indextype is ctxsys.context;

• News have expiration_time and release_time (for timed press releases).
• But, these would also be useful for articles, lets just add them in. But, we will now need a content_type

create table content_raw (
        content_id              integer primary key,
        -- 'news' or 'article' or 'comment' or...
        content_type            varchar(100) not null,
        refers_to               references content,
        creation_user           not null references users,
        creation_date           not null date,
        release_time            date,
        expiration_time         date,
        language                char(2) references language_codes,
        mime_type               varchar(100) not null,
        one_line_summary        varchar(200) not null,
        body                    clob,
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

create view news_current_and_approved
        as
        select *
        from content_raw 
        where content_type = 'news'
        and (release_time is null or sysdate >= release_time)
        and (expiration_time is null or sysdate  <= expiration_time)
        and editorial_status = 'approved';

• A large site has publishers, information designers, graphic designers, authors, and programmers.
• A large site has thousands of pages.
• A large site needs a single theme.
• You need to allow all of them to work at the same time without breaking each other's work.

• Multiple people working on the same document can lead to a losing a lot of data:
  1. Ada and Bob get a local copy.
  2. Ada edits and uploads.
  3. Bob edits and uploads.
• Best solution is to keep all copies around, to facilitate reversions and merging.

create table content_raw (
        -- the combination of these two is the key
        content_id              integer primary key,
        version_number          integer,

        content_type            varchar(100) not null,
        refers_to               references content_raw,
        creation_user           not null references users,
        creation_date           not null date,
        release_time            date,
        expiration_time         date,
        -- some of our content is geographically specific 
        zip_code                varchar(5),
        -- a lot of our readers will appreciate Spanish versions
        language                char(2) references language_codes,
        mime_type               varchar(100) not null,
        one_line_summary        varchar(200) not null,
        -- let's use BLOB in case this is a Microsoft Word doc or JPEG
        -- a BLOB can also hold HTML or plain text
        body                    blob,
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
        primary key (content_id, version_number)
);

• To get all versions we would have to use the clumsy:

-- note the use of MAX on VARCHAR column; this works just fine

select content_id, max(zip_code)
        from content_raw
        where content_id = 5657
        group by content_id

• A better solution is to use two tables:

-- stuff about an item that doesn't change from version to version
create table content_raw (
        content_id              integer primary key,
        content_type            varchar(100) not null,
        refers_to               references content_raw,
        creation_user           not null references users,
        creation_date           not null date,
        release_time            date,
        expiration_time         date,
        mime_type               varchar(100) not null,
        zip_code                varchar(5)
);

-- stuff about a version of an item
create table content_versions (
        version_id              integer primary key,
        content_id              not null references content_raw,
        version_date            date not null,
        language                char(2) references language_codes,
        one_line_summary        varchar(200) not null,
        body                    blob,
        editorial_status        varchar(30) 
              check (editorial_status in ('submitted','rejected','approved','expired')),
        -- audit the person who made the last change to editorial status
        editor_id               references users,
        editorial_status_date   date
);

• We can then get the latest version with

select * 
        from content_versions
        where content_id = 5657
        and editorial_status = 'approved'
        and version_id = (select max(version_id)
                  from content_versions
                  where content_id = 5657
                  and editorial_status = 'approved')

• Notice that it requires scanning all of content_versions.

• Most often we only need the latest version, thus a faster implementation would be:

create table content_versions (
        version_id              integer primary key,
        content_id              not null references content_raw,
        version_date            date not null,
        ...
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired')),

        current_version_p       char(1) check(current_version_p in ('t','f')),
        ...
);

create view live_versions 
        as
        select * 
        from content_versions
        where current_version_p = 't';

• Obviously, use a source control system: CVS, subversion, etc.
• So, do I copy over the whole database and deployment environment to my PC?
• No. Generally we want:

1. Three HTTP servers: deployment, testing, development.
2. Two or three RDBMS tablespaces: deployment and testing/development.
3. One source control repository.

• Screen Space is a scarce resource. Use it wisely. Whitespace is good.
• Response Time is less than 1 second. Build a whole new platform [10] if you have to. 10 seconds and you might as well be down.
• Use words not icons.
• Color can be distracting.
• Edward Tufte: Every decoration must convey information.

1. A navigation directory (really?)
2. News and events on front page.
3. Single search box.
4. Direct links to most used services.

• Your webapp is likely to include

1. RDMS table definitions.
2. Stored procedures that run in the database
3. Procedures that run inside your Web or application server program that are shared by more than one page
4. Scripts that generate individual pages
5. (Possibly) templates that work in conjunction with page scripts
6. Documentation explaining the objectives of the module

• Keeping all completely separate is extremely difficult and often not worth the effort.
• Good CSS usage lets you separate the look from the layout and content.
• Layout depends on the data you display.
• Layout designer should be able to describe what he wants in English. Server-side programmers will translate to SQL.
• However, innovative new websites (flickr, digg, gcal) deeply blend design and functionality.

• RDBMS: runs CPU-bound. Extra CPUs should be on the one machine.
• Business logic (calculate state tax) can be kept in the RDBMS: faster access to data. If multiple RDBMS then maybe it can be in its own program.
• Merging the result of the SQL queries with HTML is normally done in the webserver (tomcat, IIS+ASP.NET). These can be duplicated as needed.
• HTTPS adds CPU overhead to your websevers: duplicate. Also, hardware encryption machines exist that are much more effecient.
• 10 dynamic pages per second is a universal constant.

• Small: One multi-CPU machine for RDBMS, multiple single-CPU machines running webservers.
• Session ID needs to be in RDBMS.
• Load balancing could be done by DNS in round-robin fashion. But this might be bad when you give all of aol.com the same IP.
• A load [11] balancing [12] router [13] (free [14]) routes requests as per network load. Only one IP needed.
• Statics (pictures, javascript libs, css) can be off-loaded: akamai [15]
• Remember: memory is getting cheap and large.

• If RDBMS or load balancer fail we are dead.
• A good load balancer will tell you how to set up multiple ones.
• For the RDBMS you can:
  1. Make sure it never fails: multiple power supplies, RAID, etc. Follow Oracle's instructions.
  2. Buy multiple ones and run something like Oracle's Parallel Server.

• Captchas [16] to control users/posts.
• Moderators to control trolls. Before or after?
• IP/country bans.
• Intelligent filters? An never-ending battle.

• Search is good.
• Contextual search is better.
• Personalized search might be best.
• Amazon does it well, others not so much.

• We can use our RDBMS to do search.

select * 
        from content
        where body like '%' || :user_query || '%'

--if the user is searching for running, it becomes
select * 
        from content
        where body like '%running%'


--to match upper and lower you do

select * 
        from content
        where upper(body) like upper('%running%')

--for multiple words we need to do 'and'

select * 
        from content
        where upper(body) like upper('%running%')
        and upper(body) like upper('%shoes%')

• But, we are running in O(n) time.

• Build a hash table where the keys are the words and the values are the documents (pointers) in which these appear.
• Throw out common words.
• Stem.
• Insertion of a new document sucks, but retrieval is O(1).
• Implement this index as a separate module?

• Oracle, and others, have implemented full-text indexes within the database.

create table content (
        content_id              integer primary key,
        refers_to               references content_raw,
        -- who contributed this and when
        creation_user           not null references users,
        creation_date           not null date,
        modified_date           not null date,
        mime_type               varchar(100) not null,
        one_line_summary        varchar(200) not null,
        body                    clob,
        editorial_status        varchar(30) 
          check (editorial_status in ('submitted','rejected','approved','expired'))
);

-- create an Oracle Text index (the product used to be called
-- Oracle Context, hence the CTX prefixes on many procedures)

create index content_text 
        on content(body) 
        indextype is ctxsys.context;

-- let's look at opinions on running shoes from 
-- users who registered in the last 30 days, sorting
-- results in order of decreasing relevance

select 
  score(1), 
  content.content_id, 
  content.one_line_summary, 
  users.first_names,
  users.last_name
        from content, users
        where contains(body, 'running shoes', 1) > 0
        and users.registration_date > current_timestamp - interval '30' day
        and content.creation_us
        er = users.user_id
        order by score(1) desc;

--The score(1) function refers to the contains() call

• Oracle provides filters that extract indexable text from .xls, .doc, .pdf and other file types.