Hart Lambur (hal2001@columbia.edu)
Blake Shaw (bs2018@columbia.edu)
Lawrence Wang (levity@gmail.com)
Columbia University
New York, NY 10027
Our application requires Java, Apache with mod_python, Apache Ant, and a scheduling app such as cron. There are no installation or configuration instructions because our application is hosted. The only client-side requirements are a Web browser that supports Java, and iTunes.
The following diagram shows the basic control flow of the web frontend.
(applet.psp, header.psp, common.psp not shown; used in other pages with PSP's include directive)
applet.psp (9 lines): This file is included in cutunes.psp and welcome.psp; it contains the HTML that embeds the Java applet that syncs users' music with our server.
common.psp (65): This file, included on every page after login, contains frequently-used methods as well as the check to make sure the user accessing the page is logged in and redirect otherwise.
compat.psp (120): This is the template for displaying compatible users.
cutunes.psp (208): This is the template for displaying individual and community top charts.
header.psp (34): This file, included on every page after login, contains the title and links at the top of every page.
login.psp (130): This page has a username/password form, with an option to enter your UNI instead if you are a new user. We limit usernames to UNIs to simplify the process of confirming that new users are Columbia students.
newuser.psp (108): This page sends an email to the submitted UNI with a generated password, and stores the new user's information in the database. To send the email, it opens a pipe to the ssmtp program, which forwards email to the mail server specified in its configuration, which in this case is send.columbia.edu. newuser.psp also makes use of the python-ldap module, which looks up the submitted UNI on ldap.columbia.edu to retrieve the user's name and major. If this information is available, it is stored in the database and retrieved by various other pages so that users can know more about the people with whom they share musical tastes.
recs.psp (156): This page displays recommendations.
songinfo.psp (183): This page displays information for a single selected song.
welcome.psp (137): This page contains an introduction to the project and FAQ, as well as an option to sync your data.
Below is the current database schema we are using. All the top_* tables are used to cache the community wide top 40 charts. All the rec_* tables are used to cache the recommendation tables. To see the evolution of the database schema, see our wiki page for it at Database.
create table top_songs ( play_total integer, artist char(100) not null, album char(100), name char(100) not null);
create table top_songs_week ( play_total integer, artist char(100) not null, album char(100), name char(100) not null);
create table top_albums ( play_total integer, artist char(100) not null, album char(100));
create table top_albums_week ( play_total integer, artist char(100) not null, album char(100));
create table top_artists ( play_total integer, artist char(100) not null);
create table top_artists_week ( play_total integer, artist char(100) not null);
create table message_logs (
id integer primary key auto_increment,
log_time datetime not null,
error bool,
function char(50),
message char(200));
create table sync_logs (
id integer primary key auto_increment,
user_id integer references user (id),
sync_time datetime not null,
version char(25),
message char(40));
create table user ( id integer not null auto_increment, name char(30) not null, password char(40) not null default 'xxxx', last_sync datetime not null default '2005-01-01 00:00:00', primary key (id));
create table song ( id integer not null, name char(100) not null, artist char(100) not null, album char(100), year integer, length integer, primary key (id));
create table has_song ( user_id integer not null, song_id integer not null, user_track_id integer not null, last_played datetime, play_count integer, play_count_week integer, foreign key (user_id) references user (id), foreign key (song_id) references song (id), primary key (user_id, song_id, user_track_id));
create table compatibility (
user_id integer references user (id),
other_user_id integer references song (id),
rating integer,
primary key (user_id, other_user_id));
create table compatibility_week (
user_id integer references user (id),
other_user_id integer references song (id),
rating integer,
primary key (user_id, other_user_id));
create table rec_song (
user_id integer primary key references user (id),
recommendation char(200),
rating integer );
create table rec_song_week (
user_id integer primary key references user (id),
recommendation char(200),
rating integer );
create table rec_album (
user_id integer primary key references user (id),
recommendation char(200),
rating integer );
create table rec_album_week (
user_id integer primary key references user (id),
recommendation char(200),
rating integer );
create table rec_artist (
user_id integer primary key references user (id),
recommendation char(200),
rating integer );
create table rec_artist_week (
user_id integer primary key references user (id),
recommendation char(200),
rating integer );