beforeandafter.sql

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Feb 25, 2015 at 07:16 AM
-- Server version: 5.6.21
-- PHP Version: 5.6.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `beforeandafter`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_post`(IN `id` VARCHAR(128), IN `bf` BLOB, IN `af` BLOB, IN `post_time` TIMESTAMP, IN `ui` VARCHAR(128))
    NO SQL
BEGIN
Insert into post (post_id, before_pic, after_pic, user_id) values (id, bf, af, ui);
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_user`(IN `id` VARCHAR(10), IN `fn` VARCHAR(20), IN `ln` VARCHAR(20), IN `email` VARCHAR(30), IN `password` VARCHAR(20), IN `picture` BLOB)
    NO SQL
BEGIN
Insert into users values(id, fn, ln, email, password, picture);
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_user_friend`(IN `u_id` VARCHAR(128), IN `f_id` VARCHAR(128), IN `status` ENUM('Biased','Mutual'))
    NO SQL
BEGIN
Insert into user_followers values (u_id, f_id, status);
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `change_friendship_status`(IN `u_id` VARCHAR(128), IN `f_id` VARCHAR(128), IN `s` ENUM('Biased','Mutual'))
    NO SQL
BEGIN
Update user_followers set status=s where user_id=u_id AND friend_id=f_id;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `change_user_email`(IN `email` VARCHAR(30), IN `id` VARCHAR(10))
    NO SQL
BEGIN
Update users set user_email=email where user_id=id;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `change_user_names`(IN `fn` VARCHAR(20), IN `ln` VARCHAR(20), IN `id` VARCHAR(10))
    NO SQL
BEGIN
Update users set user_firstname=fn, user_lastname=ln where user_id=id;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `change_user_password`(IN `password` VARCHAR(20), IN `id` VARCHAR(128))
    NO SQL
BEGIN
Update users set user_password=password where user_id=id;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `change_user_picture`(IN `picture` BLOB, IN `id` VARCHAR(128))
    NO SQL
BEGIN
Update users set user_picture=picture where user_id=id;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `verify_user`(IN `email` VARCHAR(128), IN `password` VARCHAR(128))
    NO SQL
BEGIN
Select * from users where user_email=email AND user_password=password;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `post`
--

CREATE TABLE IF NOT EXISTS `post` (
  `post_id` varchar(10) NOT NULL,
  `before_pic` blob NOT NULL,
  `after_pic` blob NOT NULL,
  `post_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` varchar(10) NOT NULL,
  `user_firstname` varchar(128) NOT NULL,
  `user_lastname` varchar(128) NOT NULL,
  `user_email` varchar(128) NOT NULL,
  `user_password` varchar(128) NOT NULL,
  `user_picture` blob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `user_firstname`, `user_lastname`, `user_email`, `user_password`, `user_picture`) VALUES
('254666', 'Ali', 'Njie', '//a24UnSEmy75EFIH8nhPw==', 'dKqrDs3VuWs=', 0xffd8ffe000104a46494600010100000100010000ffe1003c45786966000049492a000800000002003101020007000000260000003b010200070000002d00000000000000476f6f676c650050696361736100ffdb0084000302020b0b0a0a0a0d0a080b0a0e0b0a0d0d0d0a0b0a0d0b0a080b0a0a0a0a0a0b0a0a0a0a0b08080a0a0a080a0a080a08080a0a0b08080e0d0a080d08080a080103040406050607060507080707070808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808080808ffc0001108008b008c03011100021101031101ffc4001d000000070101010000000000000000000002030405060708010009ffc4004710000201020402060704080306070000000102030411000512213141061322516171070814328191f052a1b1c1091523426272d1f13353e124344383a2b216354454748292ffc400160101010100000000000000000000000000000102ffc400161101010100000000000000000000000000001101ffda000c03010002110311003f00b1cbe00d8df006b1f1c0004df3c001a4c002f803a17fa38052241c300091b00419b00389fe8e0395a2e3011fa772a7f2fcf0124a1ccafcbebbb00b3570e1800893e380f6bc07981fa3806649f0028e51e1c7002d5df80e1c0105f97d7f7c0191cde18053d77e5f5e780591d320479a49238608c6a9259582c71a0e25989000e000bdc920004903019e7a5debdd08768b2dcb65cc34eded3525e180b77a43a56474ee32cb4cfb7f87c0e250d3d14f5e1a80c0665932474e5ac67cbbacbc0bf69e1925a812a8dae04f01b5c8d64052a95a6a9a58a5862a9a7962a8a59575472c66e8ea090470baba306478d82ba32b2b2ab2b01541be019ebe8f7be0014f2e01ca0abc02cd77eee580038c016efe27010dccfa4ceae515013d8200961466b97d56f68d1139164b86a981ac7b1ac9204a94abf5fa6a58dc3c52b7ba922b4323db8944974f5c83fcc864990f1048b1354e2243c01bf81d988f222ff1b600c5aeefece00eeb01171623c3fa603ccb800afd7d72c065ff00d203d2b779b2dca14b2c5d49ab9803d996579248610799312c5291c07fb413b955226a6a8fa0cee18caab4a2351b59558a83dc485201f1bec6d8cb29be5fd2ba548eded50a35cfbe480c77ef046e3c6de582ad2f545e9c34199be59a94d156c334d12a9ba4798d3c66577888255166812412a81db6484edd580758b8d3321b6d8aa22a16f80687205c6015c140c6c780ef3f90e7f77c700e4b0d86c6fe380e13f5be00a33e02219b74795c93be206d5ca668d59124d509f7a270b242fdfae0955e273e7193c771844848b56a0f6a292122f63036b8b7038d2d497551b0ec52d4d0aec38601e6833376d91e19f7f750e997e34d54f1bb1e41296aeb4ee36e230a14c59ea1631d9d2502e519596451c8bc12049907fcb61e38aa75a5cc0dec0ab6c6f622e3cd6fa87ff9b77e016d1ce0b283b6e07d6df7e031afadaf49d2ab3f95044d0bd1d2a533b990389ec4ccb30408bd4d84e63d3d64ba8246dd8259713535551e87ff00b3c84caed1b14b0d8056d57bdb9ed71c80f3df196530cc7d1024c291a19e24748d4129d5c81a400b7682310afb0ba480385dc036008593eac590c7067747d7cf1aca16b4403ab7fdbd5cd075422531a9488889a56d726856d200ed3ae2e2e3574f2f68df6dce34d0425efe1800d3b806f6dfefc01c67f33800c67cb00167f9e03ca9806a5841feff00d7f0c079e9b009e6a4078818063cc3a208dc2df97cb1224249e09d5047759a21bac73a2cb1a9bf144943085bf8e2eadc5f6618842099e062a648a48dd2e51d40a9489b812893ba5540c54b2978b32b0536ea98764da1f72faf97f70a5401fe49691ec3be9e4586b2fdfd5c154a37edb6c70551beb4de8e609054e7b14e52a1569a1a9a6d1712333474c92eb2e9253c88bd5078a4a63af4dc152cd86a6b3519354262210a332dfac7d0abbf64936d85f890091dc7196534c8b2f4a424ac34475a6932d1d6c550aa05ec5b4bea42585eda0b58ef6162d55797aa4fa399669a3cee6b7b3c49531d28d5da96b5bf6124a506e218a3ebd033fbd295d20e8738b8b8d192cb737efdf154257c00f01ed780eb11b6004f80e293dd8068597ebebebcf006097c4e004d51dffe98006a17c009e2c023a8cb94f118064aee86a9dc5b1224579eb46d32e4553d6309019a8e38cca15dd5bda124d31c8e0c88a56362511d548537180c5b2d701c541ef0781faf1c6592ea38c4a16286158e491d225d17d2d2cac234bfff00661df6f1c557d45adc8a1a558a8a04094d4e8228d45f654fde627de91cddddceeeeec4dc9271a68dea7eb6c07964c0185b01deb301deb3002492e3c701d594e019522c01b1af87e380f5b95b6c00d23b9b01c7011ae99fa56a5a46311d5515038c5111646ee96437119fe101dc023b16209952abb87d67ea75906828749be9532cdad749f75a40486246e08892e396c6f295d3eb4b51d65bf565194b6e04d30607bc3692080797562fc2e38e14a80fa55e9a4f99f56b3a2454e9a5969e32dd5fb42eb1d7eb3db694a3941a98aaaec146a72c4aab736f45c2f70ec0773adc1ff991dfef8879ef8887be80746592bb2c2557a95cc72f6770e842c69570b3b30075001413ba5f6daf8aadeb9ea9eb5b8df51faf3c69a36a271180e090f8e03babbff0fbf01d57f872e1cfcf0022e3ebfae03a1feadf2c0152cc3c7ebe38018a5c07529b01e14c70111f4c1e919686131230f6f953f66a05cc3113633b6da411b88958dd9c5ec5524b4dd4669cb610071bb6c5893762e7724b12492d7b924dcefe38ca034a03e937b92cd60373d917b9ee1b81c39e08758a9ca5f8127893dfdde16f8f9e03cca4f1dcfd7c07f6c004436eff008600c9a36b0d24ec41045b5023752a78dc711bdc1180b3fa37eb1b3a854ab83da40e3343a52a08e5ad0da299b95c1a7b8b5cb104b5ab56df467338aae2eba9dc489fbca769613f6648ee4a1ee6dd1f8a338df1a68718f01c0980e753dd80f08f00248493805029c0dac3e380775a0fafc301cf603802334678a9eaaa12212cb0d34f2a464d848f144f22a5f96a2b6f8e031bd166a6b17dbcc865332b3339e21c0d3a2dfb823b6809c1428b5c6e72c90d7a94d33a8b80aab28ef4fb43f890ee0f1b6a1cef8888fa678d1369409d69ec06372114d8b36907b46da6c39df8f1c04bf2b81ac2f217e1ef2a8b6fc8201dfb0df00e9353f0dbcf01c14fe07c3c7f1be03829c8f23ddcbe7fe980efd78fcb0050cbc860eaec9229043a1b15756d4bc388d414d882a6c2e08e2568bf463e90057d3b33054aa87409d54765964d7d54c8382893ab90320f71e37b595931acd5c483a9c557845803d32e27cb00a963c01653bef80b3da9e36f79149bf758dfcc58fdf802a5e8b447833afc6e3e47006e45d1b2b22fb9221d995bf794ec411b8208b820e03e5f74a3a175790e7599e5f1755252c736a58a66255a9a6449a9ceb5d4c93ac32468ce355c86d4ad65d396756053d6892256688c7a8302a58303c6e15ac350eeba293ddb622211d1ea04d4efa6766be95212fa5549dc0277278ebb1b80b6e180785054ea482a2e3897755d5c7902c4dfc40f86017ffe293b034f329dee5b4dbfe926e3c6e39601dd2aee2fb11e1c47c08e3e00df860074d5c8db6a00f713bfcb960144749f0fcc72f0f99dadcb00d7d2043149130206a59010786ca801f21d937dbeed826feaf19af5598106e12a6030db97588dd653b1e07516eba35ffe481cb6b8b8bde58cdcadb7c69a2a868ade780576faf2c015e1f57c07babfab0c0580b3600c5a8c03f74534ebeb1ce98e35691c9e0b1c60bb93e01413f0c07c8b6e9a3e61595798393d655d54b3589dd2376b451f808a211c6a01d8201be32cea59d3fa79e646a3a60c648a9dea6575ff00d3d2c4515e66ed0d83c91aed7377bdacac401bd11e94c73471966eaa7d21596e429702d753b0dcf85f11126eb64f8edc4fc0fdd80493f499d36785f6e6809161e36fb8f860155366424de3951bbd0802c385997883e363cb7c02e9f2a0fef463c8ef63fc2def01b6c2e7cb00551523a1ec33b2f02920d407f2c82ccbcf66571e0b7b808b749334325598b405ead40b5efa83686eb3f90a695e7dad42fd820849b2b049246a565b3065b828c3742a470653760c0820dbc301a43d1274dbdb298ebb7b5c3a56622c3ae437d1500280017b32c8a005591490a8b24431ac6b12ab62a857db0043b6f80e01809e09300723602bcf5b7e9efb0f46b32901025a954a1841362cd564acda7bcad2ad4bd87d9e2301f36b2083ab8c3016d0a2c396c3e58c30dcff00a397d1a1972dce3359806398473d2d38600de869a395646df8a4b50cd1953ce946db8beb1a64bf469591d4524728557160b22db74987124720db952388b73040889553e5b228fd93864ff2e43b792b715c442ba2cf413a5b5c6ff658ed7e3d96e07e3805357962b10da6edde2c18791ecfc89f860165450dc5b7e1ccd88e22e0f0bdb91efc02548c9ec3aea17f788fdd3c010366f304f90c0417a779ec34f99142020f67a750a83763791eeaa0d953b60126c2e0f1b8c04e725a1a9aaaca2a2864a5a76a9674334eaf208d121926242214d4e563288b7219d9016404b0aad2be8f7d0cd26581cc72555555ba9496a6a1ec5d6eacc9153a110d3425955b42abbec354af616d3492bdbe8e000e070c013572226ecd61f793e03fb601a1fa4929dd233a396d7f8dee3ee16c02fc93d6732896dadabe8c9ffdc53974f84948d51d9fe2654ef36c4a9562f46736a5aa36a6afcbea5becc35113483c0c5a848a7c1901f0c15943f497f4988abca32a248586096b655e4d3cf23410df63768d219ec2fc2637bdc59a9acb935546e85370082362798fef8cb2d3de84bf486cd97d3d1d0499451d453d3c6b0c4f4d3bc120a751a7b714a95093cec2ecefd6c2247672426a38b5aaccd96471534b2494af24519924291cda4910339658a4d24abe95b2ea0788b83717c444e693d23446c4249ab6be9d257573b12c0917e6541efc10bcf4a609058a4d7f155b0bf30755ec39ec6ddd805b495889b75dd9dad7d99472009f785adb1ded80785898ee3b63bd2d7e1cc0df86dbe03b3d490b6d047f136c05fe64f95af80aa7d64b26de86bc127575f4af652a15e9c45510b78758951385e04fb3bf1b62aa49d0ee97e89f27a8040295f44093c3ab6a8891c7994661f1e7c3111bb3a430e995c730c7e7738db66e91799200fb8601aaa33a27b318b9fb56d8790fccfc8e00b832217d521d6c77f0bf8dc6ff008601c3da0f979603e65f4a3d00677971265caf39a555b932431cad0002fbf5d06b84f024f6cec2fc31989116a4f4b73100196197c2a2243bff0032756df363821e73be9cbd53a4ce175ac4b1595dd87568cecba43926300bb76031038f33888221cdede1e78052d9f5c60002aefcefe7f57380396b4711653c36dc1eee5dfdf809b747fa47c1481bec08dae6fb83e3f0df016764fe8e28eb0133e6b4794228044d551b48b33127b08ab244a645b02c1a6436234a4a35e8aa73cc7a0fd1688dea3a635939007fe5d97488a5bbb5f5756bcb65693e2795527cc3a5796a32b50d557d5d295b21af454a97707b4dd98218cc041022b2990f56fa86c09888dfa50aa8e7caebc826c8f435696fdd78e63452a1009ff835d248fc7fc153cb014ad0e7e7d9d1d2ec63d2e00bdf5c6c1c5873dd4620fabfe9033641333af6f5d9d74f028e3529db91041c6da445e85a4ddce95beca3bbf2fc7cb00b95428b0161f8ff00ae009964c027b1fabe02f4e8cd2ccb1a4aa08129694756e549526d19b5c718c21e1cf00c5d2ee86d355395aba1a4aa0147fbe5241290cc5acc2578fad56b6ab15956c18f980a33a6fea25d1da891516866a49242e4bd0d64b1e8d2baaeb054ad5c40136528ab1802e4588b10a0bd60bf47c479765f5799d3e67512434e8acd055532333069163daaa1980dcb2804d2281bdc8076912336d57a38a94a0a3cd648625a1aa99e181d254323ce8d22ba98753322298a4bb69b0ba036eb17112089bd1b4cb72ce0a13b18ceebc78a902fcb6b83e3b6220aa9e89bc66c46adae2d7058791e7e17380ed0d46c4193ab3c549e4e391bf3f0c02ac9bd23c91165709246d70eae15e175f14371cf8f2c02f8ab7290757eaf32cee4e98e3965b2b5ac15220c430277ed06517e56daa9cf30accd6aa1a6f6aab11535345d444b75eb560becb6880d2d6d9a42e8761d9c04b322ad442238d2f084ed123b249b6d73ef9dc966b9eedf7b440cd0c415c2c71aec764503f0b036fabe035a7ab4d5bcb91d0cb21d4e1aaa1563b96a7a7aa9a1845f988d10440fd98c5ee6e4eb1b4ea596d8a11bbfc70043e03c079fdf80d2d94e63155d9e92af2ea98ac340a59e2915631b2aaf54cc02a80071e5803932e9834a4c720f757617074adf95f9b1f960196b991aa22d4a2eb14a4dc58dcb228e3e17c0523ebeba53a299cb25d6fec29b1dacd5f4c1858df8aeadbf0c0614f4879d2d651f47e12d1c74f4196441154b5dab2a6d354ca43c71d8bda05d2a245ba330965d6346759768dbb3645bdfed5ac76e3e5e1c7c3110cbd27cb9b4f6aecdc6e0582edfbbcfcf7c055f9c00d75239f1b6f71f9e01a25cbc5ac3f1fe836c048323e9ecd474d2410252ac9248b21a911deaa208410914e54342a770fa1c07525581180479609a565d5ed132df70008e204ee4b486cbe24b35fc0ed82a7cf5c55e325ccc554feca0ff000d7b3c15cd8c9b6da9b403c91786089a7a34f561e90668915405a2a6cba5b9154f3c063440da24410c323d4b54a3074785e14659236590c362458d46e8c9fa2f0d1d252d04059a1a788461dac1a57bb3cb33017b3cd2b492b004805ed736c69492623c70081e4fafafc301c27eb9f8600d45f1b79603e48fea70183a5e37041054b0d247020860ca477df196566f43bd6573ea3ff77ce332516034bd43cb1800dc5a2a8eb517bb65171b1b8db0a55bfd1dfd277d25880eb1b2dab205b554d1c7a996f7d27d91e9f61fca0f7ef738b5688f58efd21d519c651365b2e594b4c5e482469a9e69156f0beb2a6078d8b8636b033f608bf6cdaca542b3cce969569e39a20665a7a52630c8c144d4f1cf1d8a9209ea9e336bf6781d2410221a2a7d2eca542c74e91edef3482e37e210291c3be43cf10465ba4731b1eb4ea27704b3292796966200fe50b82097e95c81b4c94c07790dc2de056e3cb7c5526a4cea290b02a2323edba2ea1bf06621757812b7e588389d2389191d5dd1810c0b2c6e011cec864561e04107bb0126e8fe58955702afad2c6e62326926fc6d18d06dbfbaaba40db95b016ee45d1af66006809b006c3b3ddc380f223e382344fa9e66e7466b45a8955782ae35bec3acd50541504ecb78e92f6005dafc5b7d6358b5eb27fae78aa479753eb91509b5cdbfbfcf96029083d71a83da2aa9e4a2ad8c4152f0178a582490b2369323d3bb5398e32e1c0d134e7b3c2fb09513ccb7d2fe533005732a58cdc0d357ae98ea3c06aa848e363fc923f9e29534a7e8e3380c8d0c8878347246ea7c995883f027057c878abdafc7bb90fe98c32570b6d820e88eff005df806de94ff008671557c7ae9660cf99d03369d5faa6993b2a8bd88e6aa8a30422a8256348d03104d916e4db174d5614500655b807fb1c650e2993a0ed85ed0d36373b5c8dec4dafdc6d71cad804f57b9b9dcdcfe3808d2c2358db8abdfc763805cb97ae98ce917d23fee3802a6e8f45a876177bf7f762a9dba01e90aaa3aa82996797a86201473d62db7165eb03941b0d94af0c15b47d039d19ed305ec8929ab51edfbd1fb1bcfa4f70eb6185ec2dbc6be386262fbac6dce34d1244db8fae58095d374069330b2d65251d5db60d51042f22aec2cb2b219136fb320b603257af67a06a0ca60eb68a06a77b8ff008f53220b937b453cd2c6a36160230001616180a0b23e8ac12c4923c30b3b0b925177373dc001f0031965ffd9);

-- --------------------------------------------------------

--
-- Table structure for table `user_followers`
--

CREATE TABLE IF NOT EXISTS `user_followers` (
  `user_id` varchar(10) NOT NULL,
  `friend_id` varchar(10) NOT NULL,
  `status` enum('Biased','Mutual') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `post`
--
ALTER TABLE `post`
 ADD PRIMARY KEY (`post_id`), ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
 ADD PRIMARY KEY (`user_id`), ADD UNIQUE KEY `user_email` (`user_email`);

--
-- Indexes for table `user_followers`
--
ALTER TABLE `user_followers`
 ADD KEY `user_id` (`user_id`), ADD KEY `friend_id` (`friend_id`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `post`
--
ALTER TABLE `post`
ADD CONSTRAINT `post_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `user_followers`
--
ALTER TABLE `user_followers`
ADD CONSTRAINT `user_followers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
ADD CONSTRAINT `user_followers_ibfk_2` FOREIGN KEY (`friend_id`) REFERENCES `users` (`user_id`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;