· 9 years ago · Sep 28, 2016, 04:12 PM
1-- ---
2-- Globals
3-- ---
4
5-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
6-- SET FOREIGN_KEY_CHECKS=0;
7
8-- ---
9-- Table 'Users'
10--
11-- ---
12
13DROP TABLE IF EXISTS `Users`;
14
15CREATE TABLE `Users` (
16 `uid` INTEGER(32) NOT NULL AUTO_INCREMENT DEFAULT 1,
17 `email_add` VARCHAR(30) NOT NULL,
18 `username` VARCHAR(30) NOT NULL,
19 `password` VARCHAR(30) NOT NULL,
20 `fullname` VARCHAR(30) NULL DEFAULT NULL,
21 `fb_id` INTEGER(32) NULL DEFAULT NULL,
22 `twitter_id` INTEGER(32) NULL DEFAULT NULL,
23 `image_url` VARCHAR(300) NULL DEFAULT NULL,
24 `privacy_level` INTEGER(2) NOT NULL DEFAULT NULL,
25 `tag_option` INTEGER(2) NOT NULL DEFAULT NULL,
26 PRIMARY KEY (`uid`, `email_add`, `username`)
27);
28
29-- ---
30-- Table 'Followers'
31--
32-- ---
33
34DROP TABLE IF EXISTS `Followers`;
35
36CREATE TABLE `Followers` (
37 `uid` INTEGER(32) NOT NULL AUTO_INCREMENT DEFAULT NULL,
38 `followersID` INTEGER(32) NOT NULL,
39 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
40 `request_status` INT NOT NULL DEFAULT NULL,
41 PRIMARY KEY (`uid`, `followersID`)
42);
43
44-- ---
45-- Table 'following'
46--
47-- ---
48
49DROP TABLE IF EXISTS `following`;
50
51CREATE TABLE `following` (
52 `uid` INTEGER(32) NOT NULL DEFAULT NULL,
53 `following_id` INTEGER(32) NOT NULL DEFAULT NULL,
54 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
55 `request_status` INTEGER NOT NULL DEFAULT NULL,
56 PRIMARY KEY (`uid`, `following_id`)
57);
58
59-- ---
60-- Table 'Blocked_user'
61--
62-- ---
63
64DROP TABLE IF EXISTS `Blocked_user`;
65
66CREATE TABLE `Blocked_user` (
67 `uid` INTEGER(32) NOT NULL AUTO_INCREMENT DEFAULT NULL,
68 `blocked_uid` INTEGER(32) NOT NULL DEFAULT NULL,
69 PRIMARY KEY (`blocked_uid`)
70);
71
72-- ---
73-- Table 'Hidden_user'
74--
75-- ---
76
77DROP TABLE IF EXISTS `Hidden_user`;
78
79CREATE TABLE `Hidden_user` (
80 `uid` INTEGER(32) NOT NULL AUTO_INCREMENT DEFAULT NULL,
81 `hidden_uid` INTEGER(32) NOT NULL DEFAULT NULL,
82 PRIMARY KEY (`hidden_uid`)
83);
84
85-- ---
86-- Table 'Photo'
87--
88-- ---
89
90DROP TABLE IF EXISTS `Photo`;
91
92CREATE TABLE `Photo` (
93 `photo_id` INTEGER(32) NOT NULL AUTO_INCREMENT DEFAULT NULL,
94 `uid` INTEGER(32) NOT NULL DEFAULT NULL,
95 `image_url` VARCHAR(300) NOT NULL DEFAULT 'NULL',
96 `location_id` INTEGER NULL DEFAULT NULL,
97 `caption` VARCHAR(300) NULL DEFAULT NULL,
98 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
99 PRIMARY KEY (`photo_id`, `uid`)
100);
101
102-- ---
103-- Table 'tagged_photo'
104--
105-- ---
106
107DROP TABLE IF EXISTS `tagged_photo`;
108
109CREATE TABLE `tagged_photo` (
110 `uid` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
111 `photo_id` INTEGER(32) NOT NULL DEFAULT NULL,
112 PRIMARY KEY (`photo_id`, `uid`)
113);
114
115-- ---
116-- Table 'Comment'
117--
118-- ---
119
120DROP TABLE IF EXISTS `Comment`;
121
122CREATE TABLE `Comment` (
123 `uid` INTEGER NOT NULL AUTO_INCREMENT DEFAULT NULL,
124 `photo_id` INTEGER(32) NOT NULL DEFAULT NULL,
125 `comment` VARCHAR(300) NOT NULL DEFAULT 'NULL',
126 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
127 PRIMARY KEY (`uid`, `photo_id`)
128);
129
130-- ---
131-- Table 'Likes'
132--
133-- ---
134
135DROP TABLE IF EXISTS `Likes`;
136
137CREATE TABLE `Likes` (
138 `uid` INTEGER NOT NULL DEFAULT NULL,
139 `photo_id` INTEGER(32) NOT NULL DEFAULT NULL,
140 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
141 PRIMARY KEY (`uid`, `photo_id`)
142);
143
144-- ---
145-- Table 'Location'
146--
147-- ---
148
149DROP TABLE IF EXISTS `Location`;
150
151CREATE TABLE `Location` (
152 `location_id` INTEGER NOT NULL DEFAULT NULL,
153 `GPS` INTEGER NOT NULL DEFAULT NULL,
154 `location_name` VARCHAR(300) NOT NULL DEFAULT 'NULL',
155 PRIMARY KEY (`location_id`)
156);
157
158-- ---
159-- Foreign Keys
160-- ---
161
162ALTER TABLE `Users` ADD FOREIGN KEY (uid) REFERENCES `Blocked_user` (`uid`);
163ALTER TABLE `Followers` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
164ALTER TABLE `Followers` ADD FOREIGN KEY (followersID) REFERENCES `Users` (`uid`);
165ALTER TABLE `following` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
166ALTER TABLE `following` ADD FOREIGN KEY (following_id) REFERENCES `Users` (`uid`);
167ALTER TABLE `Blocked_user` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
168ALTER TABLE `Blocked_user` ADD FOREIGN KEY (blocked_uid) REFERENCES `Users` (`uid`);
169ALTER TABLE `Hidden_user` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
170ALTER TABLE `Hidden_user` ADD FOREIGN KEY (hidden_uid) REFERENCES `Users` (`uid`);
171ALTER TABLE `Photo` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
172ALTER TABLE `tagged_photo` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
173ALTER TABLE `tagged_photo` ADD FOREIGN KEY (photo_id) REFERENCES `Photo` (`photo_id`);
174ALTER TABLE `Comment` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
175ALTER TABLE `Comment` ADD FOREIGN KEY (photo_id) REFERENCES `Photo` (`photo_id`);
176ALTER TABLE `Likes` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
177ALTER TABLE `Likes` ADD FOREIGN KEY (photo_id) REFERENCES `Photo` (`photo_id`);
178
179-- ---
180-- Table Properties
181-- ---
182
183-- ALTER TABLE `Users` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
184-- ALTER TABLE `Followers` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
185-- ALTER TABLE `following` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
186-- ALTER TABLE `Blocked_user` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
187-- ALTER TABLE `Hidden_user` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
188-- ALTER TABLE `Photo` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
189-- ALTER TABLE `tagged_photo` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
190-- ALTER TABLE `Comment` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
191-- ALTER TABLE `Likes` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
192-- ALTER TABLE `Location` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
193
194-- ---
195-- Test Data
196-- ---
197
198-- INSERT INTO `Users` (`uid`,`email_add`,`username`,`password`,`fullname`,`fb_id`,`twitter_id`,`image_url`,`privacy_level`,`tag_option`) VALUES
199-- ('','','','','','','','','','');
200-- INSERT INTO `Followers` (`uid`,`followersID`,`timestamp`,`request_status`) VALUES
201-- ('','','','');
202-- INSERT INTO `following` (`uid`,`following_id`,`timestamp`,`request_status`) VALUES
203-- ('','','','');
204-- INSERT INTO `Blocked_user` (`uid`,`blocked_uid`) VALUES
205-- ('','');
206-- INSERT INTO `Hidden_user` (`uid`,`hidden_uid`) VALUES
207-- ('','');
208-- INSERT INTO `Photo` (`photo_id`,`uid`,`image_url`,`location_id`,`caption`,`timestamp`) VALUES
209-- ('','','','','','');
210-- INSERT INTO `tagged_photo` (`uid`,`photo_id`) VALUES
211-- ('','');
212-- INSERT INTO `Comment` (`uid`,`photo_id`,`comment`,`timestamp`) VALUES
213-- ('','','','');
214-- INSERT INTO `Likes` (`uid`,`photo_id`,`timestamp`) VALUES
215-- ('','','');
216-- INSERT INTO `Location` (`location_id`,`GPS`,`location_name`) VALUES
217-- ('','','');