use myersjac;

# fotofinish drop tables



# fotofinish create tables

--
-- Table structure for table `photo_comments`
--
CREATE TABLE `photo_comments` (
  `comment_id` int(8) NOT NULL AUTO_INCREMENT,
  `user_id` int(6) NOT NULL,
  `file_id` int(8) NOT NULL,
  `comment` varchar(512) NOT NULL,
  `comment_date` datetime NOT NULL,
  `security` tinyint(4) DEFAULT NULL,
  `reply` tinyint(4) DEFAULT NULL,
  `sort_order` decimal(16,8) DEFAULT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `user_id_idx2` (`user_id`),
  KEY `file_id_idx1` (`file_id`),
  CONSTRAINT `file_id1`
     FOREIGN KEY (`file_id`)
     REFERENCES `photo_files` (`file_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  CONSTRAINT `user_id2`
     FOREIGN KEY (`user_id`)
     REFERENCES `photo_users` (`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE
) COMMENT='Table to hold comments about a picture';

--
-- Table structure for table `photo_files`
--
CREATE TABLE `photo_files` (
  `file_id` int(8) NOT NULL AUTO_INCREMENT,
  `user_id` int(6) NOT NULL,
  `upload_date` datetime NOT NULL,
  `upload_name` varchar(128) NOT NULL,
  `caption` varchar(256) NOT NULL,
  `file_location` varchar(45) NOT NULL,
  `security` tinyint(4) DEFAULT NULL,
  `narrative` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`file_id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `user_id`
     FOREIGN KEY (`user_id`)
     REFERENCES `photo_users` (`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE
) COMMENT='Table to hold all uploaded photos to fotofinish';

--
-- Table structure for table `photo_files_tags`
--
CREATE TABLE `photo_files_tags` (
  `files_tags_id` int(10) NOT NULL AUTO_INCREMENT,
  `file_id` int(8) NOT NULL,
  `tag_id` int(10) NOT NULL,
  PRIMARY KEY (`files_tags_id`),
  KEY `file_id_idx3` (`file_id`),
  KEY `tag_id_idx` (`tag_id`),
  CONSTRAINT `file_id3`
     FOREIGN KEY (`file_id`)
     REFERENCES `photo_files` (`file_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  CONSTRAINT `tag_id`
     FOREIGN KEY (`tag_id`)
     REFERENCES `photo_tags` (`tag_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE
) COMMENT='Table to hold M:M relationship between files and tags';

--
-- Table structure for table `photo_files_users`
--

CREATE TABLE `photo_files_users` (
  `files_users_id` int(10) NOT NULL AUTO_INCREMENT,
  `file_id` int(8) NOT NULL,
  `user_id` int(6) NOT NULL,
  PRIMARY KEY (`files_users_id`),
  KEY `file_id_idx4` (`file_id`),
  KEY `user_id_idx4` (`user_id`),
  CONSTRAINT `file_id4`
     FOREIGN KEY (`file_id`)
     REFERENCES `photo_files` (`file_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  CONSTRAINT `user_id5`
     FOREIGN KEY (`user_id`)
     REFERENCES `photo_users` (`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE
)  COMMENT='Table to hold M:M relationship between files and users
            (file can be `sent` to user';

--
-- Table structure for table `photo_friendships`
--

CREATE TABLE `photo_friendships` (
  `friendship_id` int(8) NOT NULL AUTO_INCREMENT,
  `user_id` int(6) NOT NULL,
  `friend_id` int(6) NOT NULL,
  `accepted` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`friendship_id`),
  KEY `user_id_idx4` (`user_id`),
  KEY `friend_id_idx` (`user_id`),
  KEY `friend_id` (`friend_id`),
  CONSTRAINT `friend_id`
     FOREIGN KEY (`friend_id`)
     REFERENCES `photo_users` (`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  CONSTRAINT `user_id4`
     FOREIGN KEY (`user_id`)
     REFERENCES `photo_users` (`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE
) COMMENT='Table to hold friend relationships.  1-way friendships are possible.
           A user can open up photos to people they declare as friends, but the
           reciprocal relationship only happens after the declared friends
           accept the friendship.';

--
-- Table structure for table `photo_ratings`
--
CREATE TABLE `photo_ratings` (
  `rating_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(6) NOT NULL,
  `file_id` int(8) NOT NULL,
  `rating` tinyint(4) NOT NULL,
  `rating_date` datetime NOT NULL,
  PRIMARY KEY (`rating_id`),
  KEY `user_id_idx3` (`user_id`),
  KEY `file_id_idx2` (`file_id`),
  KEY `user_id1_idx` (`user_id`),
  KEY `file_id2_idx` (`file_id`),
  CONSTRAINT `user_id1`
     FOREIGN KEY (`user_id`)
     REFERENCES `photo_users` (`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  CONSTRAINT `file_id2`
     FOREIGN KEY (`file_id`)
     REFERENCES `photo_files` (`file_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE
) COMMENT='Table to hold ratings of photos from registered users';

--
-- Table structure for table `photo_tags`
--
CREATE TABLE `photo_tags` (
  `tag_id` int(10) NOT NULL AUTO_INCREMENT,
  `tag` varchar(30) NOT NULL,
  `user_tag` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_UNIQUE` (`tag`)
) COMMENT='Table to hold all hashtags assigned by users
           (in much the same way as Twitter)';

--
-- Table structure for table `photo_users`
--
CREATE TABLE `photo_users` (
  `user_id` int(6) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(40) NOT NULL,
  `last_name` varchar(60) NOT NULL,
  `join_date` datetime NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` char(40) NOT NULL,
  `salt` char(5) DEFAULT '^hV8{',
  `profile_pic_id` int(8) DEFAULT NULL,
  `admin` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) COMMENT='Table to hold registered users of fotofinish';

#  Some alterations after the fact.

# Create account for guest
INSERT INTO `myersjac`.`photo_users` VALUE
(0, 'Guest', 'User', '2010-01-01', 'guest', 'none', null, null);     

# Make user 1 (Jack) an admin
UPDATE `myersjac`.`photo_users` SET admin=1 where user_id=1;



