Assignments‎ > ‎

Link Persistence

Due Wed, Friday Oct 26, 2012


On Next Friday, Oct 19 you must hand me a printed description of what functions you have implemented from the interface and what SQL each function uses.

Your goal in this project is to learn about database schemas and how to create a persistence layer that isolates an application from the underlying SQL and a specific database vendor. You must create an implementation called StorageService that implements interface StorageLayer, which I will provide to you below. Such a service could be used as part of a link manager application, such as the PeerScope.com system I described in class.

Schema

You are to create the necessary SQL table creation statements necessary to create the following schema. The key symbol on the left side of the column names in the following diagram indicate that they are primary key(s) for that table. Also note that most of the fields to be listed as NOT NULL. Further add constraints such that the ID and EMAIL columns are unique. Use the MySQL REFERENCES syntax to link tables for the foreign key relationships.
  • MEMBER: members of our link manager application
  • CHANNEL: a descriptor for a channel of links
  • LINK:an object representing a link with optional comments
  • MEMBERSHIP: a table that associates a member with a channel, indicating that the member is following that channel; very much like twitter.
Here is the schema diagram. It's easy enough just to create the SQL manually with a text editor, but you can also create the schema visually with MySQL Workbench.


Here's some sample data

-- ID = 1
insert into MEMBER (created,firstname,lastname,email,password)
VALUES ('2003-02-21','Terence','Parr','parrt@jguru.com','ick');

-- ID = 2
insert into MEMBER (created,firstname,lastname,email,password)
VALUES ('2003-02-21','Tom','Burns','tombu@jguru.com','icky');

-- ID = 3
insert into MEMBER (created,firstname,lastname,email,password)
VALUES ('2003-02-22','Mel','Berman','mel@jguru.com','hi');

-- ID = 1 owned by ter
insert into CHANNEL (created,ownerID,name,description)
VALUES ('2003-02-21',1,'computer languages','all about programming and other computer languages');

-- ID = 2 owned by tombu
insert into CHANNEL (created,ownerID,name,description)
VALUES ('2003-02-21',2,'jguru','anything related to jguru.com');

-- we are all members of 'jguru'
insert into MEMBERSHIP (MEMBERID,CHANNELID) VALUES (1,1);
insert into MEMBERSHIP (MEMBERID,CHANNELID) VALUES (2,1);
insert into MEMBERSHIP (MEMBERID,CHANNELID) VALUES (3,1);

-- tom/me members of 'computer languages'
insert into MEMBERSHIP (MEMBERID,CHANNELID) VALUES (1,2);
insert into MEMBERSHIP (MEMBERID,CHANNELID) VALUES (2,2);

insert into LINK (created,authorID,channelID,title,url,comments)
VALUES ('2012-03-01',1,1,'a test','http://nytimes.com', 'cool article');

insert into LINK (created,authorID,channelID,title,url,comments)
VALUES ('2012-03-01',2,2,'second test','http://cnn.com', 'news site');

Installing MySQL


In order to build this library, you must have access to a MySQL database.  To facilitate this, you will be given an AWS machine again upon which you must install mysql mysql-server using yum. Then, you must configure it to have user and so on that you can use. Use whatever username that you want as it will be just you testing it on this database. (You will have to investigate how to administer MySQL.)

Submission

You will create a jar file called db.jar containing *.class files and place it in a directory called db dist under your cs680 dir:

https://www/svn/userid/cs680/db/dist/db.jar

Pur your source Java code in db/src:

https://www/svn/userid/cs680/db/src/...

To jar your stuff up, you will "cd" to the directory containing your source code (perhaps db src) and create the jar in the db dir:

cd ~/cs680/db/src
jar cvf ~/cs680/db/dist/db.jar *.class
cd ~/cs680/db/dist
svn add db.jar
svn commit

All classes must be in the default package!

To learn more about submitting your project with svn, see Resources.

You must submit your source code for credit.

Grading

Your project must start up exactly like this:

$ java -cp db.jar:$CLASSPATH ParrtsAwesomeUnitTests

You may discuss this project in its generality with anybody you want and may look at any code on the internet except for a classmate's code. You should physically code this project completely yourself but can use all the help you find other than cutting-n-pasting or looking at code from a classmate or other Human being. There is a lot of sample code out there for implementing Java proxies, but they all are these massive programs that use lots of helper objects. For example, my program doesn't use any helper classes except for the Runnable client handler.  I've looked at most of these solutions on the web and I will be very suspicious if I see a similar coding pattern. You must implement this yourself.

I will deduct 10% if your program is not executable exactly in the fashion mentioned in the project; that is, class name, methods, lack-of-package, and jar must be exactly right. For you PC folks, note that case is significant for class names and file names on unix! All projects must run properly under linux at amazon.

ċ
Channel.java
(0k)
Terence Parr,
Oct 17, 2012, 12:48 PM
ċ
CompleteLink.java
(1k)
Terence Parr,
Oct 10, 2012, 5:11 PM
ċ
Entity.java
(0k)
Terence Parr,
Oct 10, 2012, 5:11 PM
ċ
Link.java
(1k)
Terence Parr,
Oct 10, 2012, 5:11 PM
ċ
Member.java
(0k)
Terence Parr,
Oct 10, 2012, 5:11 PM
ċ
StorageLayer.java
(4k)
Terence Parr,
Oct 10, 2012, 5:11 PM
Comments