--
-- Host: localhost    Database: myersjac
-- ------------------------------------------------------


--
-- 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 NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `user_id2` FOREIGN KEY (`user_id`) REFERENCES `photo_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=latin1 COMMENT='Table to hold comments about a picture';

--
-- Dumping data for table `photo_comments`
--

INSERT INTO `photo_comments` VALUES (1,1,37,'Alicia is such a great friend and wonderful person.','2014-06-22 17:01:05',NULL,NULL,1.00000000),(2,1,37,'And...I forgot to mention, a talented IT person as well.','2014-06-23 15:56:21',NULL,NULL,2.00000000),(4,3,25,'My wife\'s a real farm girl.  She wouldn\'t be caught dead in a city like this.   lol!','2014-06-24 14:50:09',NULL,NULL,4.00000000),(5,3,45,'Taken with my Ricoh DL-2842-SX camera.','2014-06-24 14:52:41',NULL,NULL,5.00000000),(6,1,45,'Is that an expensive camera?','2014-06-24 14:59:02',NULL,NULL,6.00000000),(7,1,25,'Ya, I hear you.  But isn\'t there a quaint charm to the place?','2014-06-24 15:18:05',NULL,NULL,7.00000000),(8,3,25,'Have you MET my wife???','2014-06-24 15:23:01',NULL,NULL,8.00000000),(9,1,25,'Touché','2014-06-24 15:27:48',NULL,NULL,9.00000000),(10,4,25,'Oh please!  Bill\'s wife seems to have more common sense than both of you put together...','2014-06-24 15:29:08',NULL,NULL,10.00000000),(11,1,25,'So it\'s a bit crowded.  It\'s New York City for heaven\'s sake.  What do you expect?  But if you had to live in the city, I find the architecture of these apartments to offset any of the crowding issues.','2014-06-24 15:34:23',NULL,NULL,11.00000000),(12,3,25,'Well I wouldn\'t mind the city.  But I\'d go bat-shit crazy on a farm.  So I\'m guessing that living in the suburbs is a decent compromise.','2014-06-24 15:46:59',NULL,NULL,12.00000000),(13,4,25,'Right now, millions of farmers are screaming \"Halleluia!\"','2014-06-24 16:06:44',NULL,NULL,13.00000000),(14,3,25,'REALLY?  Was that called for?','2014-06-24 16:08:56',NULL,NULL,14.00000000),(15,4,25,'Yes ☻','2014-06-24 16:21:51',NULL,NULL,15.00000000),(16,5,25,'I thought this was supposed to be a photo sharing site.','2014-06-24 16:25:09',NULL,NULL,16.00000000),(17,1,25,'Who knew my cool pic of apartments would cause such controversy?','2014-06-24 16:29:50',NULL,NULL,17.00000000),(18,3,25,'Who knew my wife would cause so much controversy?','2014-06-24 16:32:18',NULL,NULL,18.00000000),(19,5,25,'What kind of site did I just sign up for?','2014-06-24 16:34:28',NULL,NULL,19.00000000),(20,1,25,'Don\'t let this get you distracted, Derek.  What kind of photos do you like?','2014-06-24 18:16:58',NULL,NULL,20.00000000),(21,5,25,'I like old-time photos especially from the 1800\'s.','2014-06-24 18:33:43',NULL,NULL,21.00000000),(22,1,25,'I have photos of my great-great grandparents from around the civil war...','2014-06-24 19:27:29',NULL,NULL,22.00000000),(23,5,25,'Wow.  Could you post them here?','2014-06-24 19:35:13',NULL,NULL,23.00000000),(24,1,25,'I\'ll try... but I don\'t want to take them out of their frame.  They are so old!','2014-06-24 19:45:00',NULL,NULL,24.00000000),(25,1,25,'Actually, the pictures on my wall are good reproductions of the original photos that were made in 1978 or so.','2014-06-24 19:57:24',NULL,NULL,25.00000000),(26,1,25,'However, that is still \"old enough\" for me to not want to disturb them.','2014-06-24 20:02:18',NULL,NULL,26.00000000),(27,1,25,'Maybe I can take a HD picture of them without deframing them...','2014-06-24 20:02:56',NULL,NULL,27.00000000),(28,5,25,'Do you think you would be able to do that?','2014-06-24 20:04:21',NULL,NULL,28.00000000),(29,5,25,'I mean you don\'t have to go crazy and do it now.','2014-06-24 20:07:23',NULL,NULL,29.00000000),(30,5,25,'But it would mean a lot 2 me if you could do it sometime.','2014-06-24 20:11:09',NULL,NULL,30.00000000),(31,5,25,'I could even pay you...','2014-06-24 20:11:26',NULL,NULL,31.00000000),(74,4,43,'This looks remarkably like the logo from \"Objects first with Java\"','2014-06-26 19:44:37',NULL,NULL,74.00000000),(86,6,48,'This is an inspirational pic!','2014-06-26 21:10:25',NULL,NULL,86.00000000),(208,1,28,'not apple','2014-06-27 17:27:06',NULL,NULL,208.00000000),(238,4,48,'Why thank you!','2014-06-27 21:14:45',NULL,NULL,238.00000000),(241,1,28,'not berry','2014-06-27 21:18:03',NULL,NULL,241.00000000),(242,1,28,'not choco','2014-06-27 21:18:13',NULL,NULL,242.00000000),(243,1,28,'not date','2014-06-27 21:18:31',NULL,NULL,243.00000000);

--
-- 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 NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1 COMMENT='Table to hold all uploaded photos to fotofinish';

--
-- Dumping data for table `photo_files`
--

INSERT INTO `photo_files` VALUES (25,1,'2014-06-20 00:00:00','apartments.gif','Apartments in the Bronx','../../files/Jack/apartments.gif',0,'Check out these old apartments in New York.  They really crowd \'em in'),(26,1,'2014-06-22 12:55:07','JackAndAmyLee.png','Me and Amy Lee from Evanescence','../../files/Jack/JackAndAmyLee.png',0,'My daughter and I got to meet Amy Lee from the goth rock band Evanescence.  She rocked the house!'),(27,1,'2014-06-20 00:00:00','elton.jpg','Elton John on the keyboard','../../files/Jack/elton.jpg',0,'Photo snapped at the Susquehanna Center in Camden, NJ'),(28,1,'2014-06-20 00:00:00','Cherry Pie.gif','A slice of cherry pie','../../files/Jack/Cherry Pie.gif',0,''),(29,1,'2014-06-20 00:00:00','clab.gif','Cocoa','../../files/Jack/clab.gif',0,''),(30,1,'2014-06-20 00:00:00','ylab.gif','Dexter','../../files/Jack/ylab.gif',0,''),(31,1,'2014-06-20 00:00:00','oberserver3.gif','The Observer Pattern','../../files/Jack/oberserver3.gif',0,'This is a depiction of the observer pattern.  It is used whenever you wish to take action when the state of an object changes.'),(32,1,'2014-06-20 00:00:00','decoator.gif','The decorator pattern','../../files/Jack/decoator.gif',0,'This design pattern is used whenever you wish to enhance (or decorate) an object by wrapping it in a class that can extend or modify that class\'s functionality.'),(33,1,'2014-06-21 00:00:00','deadjim.jpg','\"He\'s dead, Jim\"','../../files/Jack/deadjim.jpg',0,'Dr. McCoy from Star Trek.'),(34,1,'2014-06-21 00:27:57','fun.jpg','Billy Joel and Elton John having fun on stage','../../files/Jack/fun.jpg',0,'This shot was from the Susquehanna Center in Camden.  We had great seats, and this was a great concert.'),(35,1,'2014-06-21 03:30:18','sunset.jpg','A beautiful sunset','../../files/Jack/sunset.jpg',0,'in Ocean City'),(36,1,'2014-06-21 03:32:39','Adrienne.jpg','My friend Adrenne','../../files/Jack/Adrienne.jpg',1,''),(37,1,'2014-06-21 03:38:34','alicia.jpg','My friend Alicia','../../files/Jack/alicia.jpg',1,''),(38,1,'2014-06-21 03:42:23','islan.jpg','My friend Anne','../../files/Jack/islan.jpg',1,''),(39,1,'2014-06-21 03:45:19','Jake.jpg','My friend Jake','../../files/Jack/Jake.jpg',1,''),(41,1,'2014-06-21 04:30:50','barney.jpg','A dinosaur from your imagination','../../files/Jack/barney.jpg',0,''),(42,1,'2014-06-21 04:31:45','Saad.jpg','My friend, Saad','../../files/Jack/Saad.jpg',1,''),(43,1,'2014-06-21 04:34:38','bluej.gif','A blue jay','../../files/Jack/bluej.gif',0,''),(44,1,'2014-06-21 04:36:29','strawberry1.gif','MY strawberries','../../files/Jack/strawberry1.gif',1,''),(45,3,'2014-06-22 18:15:29','deerfire.jpg','Fire in the woods','../../files/Bill/deerfire.jpg',0,'The sky explodes with volcanic intensity as two deer stand in the stream, helpless.  This picture illustrates the fragility of our world against the primal forces of nature.  The deer represent us -- powerless to completely master our environment.'),(46,4,'2014-06-22 18:54:15','Laverne.jpg','A woman and a friend for all time','../../files/Cherise/Laverne.jpg',1,'Laverne De Fazio (Penny Marshall) is known for being a tough-talking tomboy. She grew up in Brooklyn, with her Italian immigrant parents and grandmother; Laverne\'s parents moved to Milwaukee, where her mother died and was buried. Laverne works alongside best friend and roommate Shirley and is known for being the cynic of the pair. Despite her cynicism, she was also known for being hurt easily. Laverne enjoys dating tough guys of the \"Purple Fiends\" gang and picking up sailors at the dock with old lady neighbor Mrs. Colchek.'),(47,6,'2014-06-26 15:26:42','Image3.gif','This is a bagel','../../files/Elias/Image3.gif',0,''),(48,4,'2014-06-26 20:51:25','cloudBackground.jpg','heavenly clouds','../../files/Cherise/cloudBackground.jpg',0,'');

--
-- 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 NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `tag_id` FOREIGN KEY (`tag_id`) REFERENCES `photo_tags` (`tag_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1 COMMENT='Table to hold M:M relationship between files and tags';

--
-- Dumping data for table `photo_files_tags`
--

INSERT INTO `photo_files_tags` VALUES (2,25,19),(3,26,17),(4,26,9),(5,27,17),(6,27,9),(7,28,5),(8,29,1),(9,29,21),(10,29,22),(11,30,1),(12,30,21),(13,30,23),(14,31,10),(15,31,24),(16,31,25),(17,32,10),(18,32,26),(19,32,24),(20,33,17),(21,33,27),(22,34,17),(23,34,28),(24,35,13),(25,35,29),(26,36,9),(27,36,30),(28,37,9),(29,37,30),(30,38,9),(31,38,30),(32,39,9),(33,39,30),(35,41,17),(36,41,30),(37,42,30),(38,43,2),(39,43,30),(40,44,5),(41,44,31),(42,45,2),(43,45,4),(44,45,30),(45,46,30),(46,47,5),(47,47,32),(48,48,30);

--
-- 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 NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `user_id5` FOREIGN KEY (`user_id`) REFERENCES `photo_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table to hold M:M relationship between files and users (file can be `sent` to user';

--
-- Dumping data for table `photo_files_users`
--


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

CREATE TABLE `photo_friendships` (
  `friendship_id` int(8) NOT NULL,
  `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 NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `user_id4` FOREIGN KEY (`user_id`) REFERENCES `photo_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table to hold friend relationships.  1-way friendships are possible.  A user can open up photos \nto people they declare as friends, but the reciprocal relationship only happens after the declared friends \naccept the friendship.';

--
-- Dumping data for table `photo_friendships`
--

INSERT INTO `photo_friendships` VALUES (0,4,3,NULL);

--
-- 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`),
  CONSTRAINT `file_id2` FOREIGN KEY (`file_id`) REFERENCES `photo_files` (`file_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `user_id3` FOREIGN KEY (`user_id`) REFERENCES `photo_users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table to hold ratings of photos from registered users';

--
-- Dumping data for table `photo_ratings`
--


--
-- 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`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1 COMMENT='Table to hold all hashtags assigned by users (in much the same way as Twitter)';

--
-- Dumping data for table `photo_tags`
--

INSERT INTO `photo_tags` VALUES (1,'#pets',0),(2,'#animals',0),(3,'#celebrations',0),(4,'#nature',0),(5,'#food',0),(6,'#drinks',0),(7,'#health',0),(8,'#travel',0),(9,'#people',0),(10,'#technology',0),(11,'#sports',0),(12,'#transportation',0),(13,'#sunrise_sunset',0),(14,'#mountains',0),(15,'#beaches',0),(16,'#gaming',0),(17,'#entertainment',0),(18,'#the_arts',0),(19,'#architecture',0),(20,'#business',0),(21,'#labrador',1),(22,'#cocoa',1),(23,'#dexter',1),(24,'#java',1),(25,'#design_patterns',1),(26,'#design_pattern',1),(27,'#startrek',1),(28,'#billy_joel',1),(29,'#ocean_city_nj',1),(30,'',1),(31,'#strawberries',1),(32,'#breakfast',1);

--
-- Temporary table structure for view `photo_view`
--

/*!50001 DROP VIEW IF EXISTS `photo_view`*/;
/*!50001 CREATE TABLE `photo_view` (
  `file_id` tinyint NOT NULL,
  `upload_date` tinyint NOT NULL,
  `upload_name` tinyint NOT NULL,
  `caption` tinyint NOT NULL,
  `file_location` tinyint NOT NULL,
  `security` tinyint NOT NULL,
  `narrative` tinyint NOT NULL,
  `user_id` tinyint NOT NULL,
  `username` tinyint NOT NULL,
  `hashtags` tinyint NOT NULL,
  `usertags` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Final view structure for view `photo_view`
--

/*!50001 DROP TABLE IF EXISTS `photo_view`*/;
/*!50001 DROP VIEW IF EXISTS `photo_view`*/;


