· 9 years ago · Oct 03, 2016, 07:34 AM
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` BIGINT(12) NOT NULL AUTO_INCREMENT DEFAULT 1,
17 `email_add` VARCHAR(64) NOT NULL,
18 `username` VARCHAR(32) NOT NULL,
19 `password` VARCHAR(32) NOT NULL,
20 `fullname` VARCHAR(128) NULL DEFAULT NULL,
21 `fb_id` VARCHAR(32) NULL DEFAULT NULL,
22 `twitter_id` VARCHAR(32) NULL DEFAULT NULL,
23 `image_url` VARCHAR(128) NULL DEFAULT 'http://www.clipartkid.com/images/356/people-icon-clipart-best-cf18Sr-clipart.png',
24 `privacy_level` INTEGER(1) NOT NULL DEFAULT 1,
25 `tag_option` INTEGER(1) NOT NULL DEFAULT 1,
26 PRIMARY KEY (`uid`)
27);
28
29-- ---
30-- Table 'Followers'
31--
32-- ---
33
34DROP TABLE IF EXISTS `Followers`;
35
36CREATE TABLE `Followers` (
37 `followers_id` BIGINT(12) NOT NULL,
38 `uid` BIGINT(12) NOT NULL DEFAULT NULL,
39 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
40 `request_status` CHAR(1) NOT NULL DEFAULT '1',
41 PRIMARY KEY (`followers_id`, `uid`)
42);
43
44-- ---
45-- Table 'Following'
46--
47-- ---
48
49DROP TABLE IF EXISTS `Following`;
50
51CREATE TABLE `Following` (
52 `following_id` BIGINT(12) NOT NULL DEFAULT NULL,
53 `uid` BIGINT(12) NOT NULL DEFAULT NULL,
54 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
55 `request_status` CHAR(1) NOT NULL DEFAULT '1',
56 PRIMARY KEY (`following_id`, `uid`)
57);
58
59-- ---
60-- Table 'Blocked_user'
61--
62-- ---
63
64DROP TABLE IF EXISTS `Blocked_user`;
65
66CREATE TABLE `Blocked_user` (
67 `blocked_uid` BIGINT(12) NOT NULL DEFAULT NULL,
68 `uid` BIGINT(12) NOT NULL DEFAULT NULL,
69 PRIMARY KEY (`blocked_uid`, `uid`)
70);
71
72-- ---
73-- Table 'Hidden_user'
74--
75-- ---
76
77DROP TABLE IF EXISTS `Hidden_user`;
78
79CREATE TABLE `Hidden_user` (
80 `hidden_uid` BIGINT(12) NOT NULL DEFAULT NULL,
81 `uid` BIGINT(12) NOT NULL DEFAULT NULL,
82 PRIMARY KEY (`hidden_uid`, `uid`)
83);
84
85-- ---
86-- Table 'Photo'
87--
88-- ---
89
90DROP TABLE IF EXISTS `Photo`;
91
92CREATE TABLE `Photo` (
93 `photo_id` BIGINT(16) NOT NULL AUTO_INCREMENT DEFAULT NULL,
94 `uid` BIGINT(12) NOT NULL DEFAULT NULL,
95 `image_url` VARCHAR(128) NOT NULL DEFAULT 'NULL',
96 `location_id` INTEGER(8) NOT NULL DEFAULT NULL,
97 `caption` MEDIUMTEXT(2048) NOT NULL DEFAULT 'NULL',
98 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
99 PRIMARY KEY (`photo_id`)
100);
101
102-- ---
103-- Table 'Tagged_photo'
104--
105-- ---
106
107DROP TABLE IF EXISTS `Tagged_photo`;
108
109CREATE TABLE `Tagged_photo` (
110 `photo_id` BIGINT(16) NOT NULL DEFAULT NULL,
111 `uid` BIGINT(12) 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 `photo_id` BIGINT(16) NOT NULL DEFAULT NULL,
124 `uid` BIGINT(12) NOT NULL DEFAULT NULL,
125 `comment` MEDIUMTEXT(2048) NOT NULL DEFAULT 'NULL',
126 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
127 PRIMARY KEY (`photo_id`, `uid`)
128);
129
130-- ---
131-- Table 'Likes'
132--
133-- ---
134
135DROP TABLE IF EXISTS `Likes`;
136
137CREATE TABLE `Likes` (
138 `uid` BIGINT(12) NOT NULL DEFAULT NULL,
139 `photo_id` BIGINT(16) NOT NULL DEFAULT NULL,
140 `timestamp` TIMESTAMP NOT NULL DEFAULT 'NULL',
141 PRIMARY KEY (`photo_id`, `uid`)
142);
143
144-- ---
145-- Table 'Location'
146--
147-- ---
148
149DROP TABLE IF EXISTS `Location`;
150
151CREATE TABLE `Location` (
152 `location_id` INTEGER(32) NOT NULL DEFAULT NULL,
153 `location_name` VARCHAR(256) NOT NULL DEFAULT 'NULL',
154 `longitude` FLOAT(10,6) NOT NULL DEFAULT NULL,
155 `latitude` FLOAT(10,6) NOT NULL DEFAULT NULL,
156 PRIMARY KEY (`location_id`)
157);
158
159-- ---
160-- Foreign Keys
161-- ---
162
163ALTER TABLE `Users` ADD FOREIGN KEY (uid) REFERENCES `Blocked_user` (`uid`);
164ALTER TABLE `Users` ADD FOREIGN KEY (uid) REFERENCES `Comment` (`uid`);
165ALTER TABLE `Followers` ADD FOREIGN KEY (followers_id) REFERENCES `Users` (`uid`);
166ALTER TABLE `Followers` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
167ALTER TABLE `Following` ADD FOREIGN KEY (following_id) REFERENCES `Users` (`uid`);
168ALTER TABLE `Following` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
169ALTER TABLE `Blocked_user` ADD FOREIGN KEY (blocked_uid) REFERENCES `Users` (`uid`);
170ALTER TABLE `Blocked_user` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
171ALTER TABLE `Hidden_user` ADD FOREIGN KEY (hidden_uid) REFERENCES `Users` (`uid`);
172ALTER TABLE `Hidden_user` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
173ALTER TABLE `Photo` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
174ALTER TABLE `Tagged_photo` ADD FOREIGN KEY (photo_id) REFERENCES `Photo` (`photo_id`);
175ALTER TABLE `Tagged_photo` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
176ALTER TABLE `Comment` ADD FOREIGN KEY (photo_id) REFERENCES `Photo` (`photo_id`);
177ALTER TABLE `Likes` ADD FOREIGN KEY (uid) REFERENCES `Users` (`uid`);
178ALTER TABLE `Likes` ADD FOREIGN KEY (photo_id) REFERENCES `Photo` (`photo_id`);
179
180-- ---
181-- Table Properties
182-- ---
183
184-- ALTER TABLE `Users` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
185-- ALTER TABLE `Followers` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
186-- ALTER TABLE `Following` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
187-- ALTER TABLE `Blocked_user` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
188-- ALTER TABLE `Hidden_user` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
189-- ALTER TABLE `Photo` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
190-- ALTER TABLE `Tagged_photo` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
191-- ALTER TABLE `Comment` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
192-- ALTER TABLE `Likes` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
193-- ALTER TABLE `Location` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
194
195-- ---
196-- Test Data
197-- ---
198
199-- INSERT INTO `Users` (`uid`,`email_add`,`username`,`password`,`fullname`,`fb_id`,`twitter_id`,`image_url`,`privacy_level`,`tag_option`) VALUES
200-- ('','','','','','','','','','');
201-- INSERT INTO `Followers` (`followers_id`,`uid`,`timestamp`,`request_status`) VALUES
202-- ('','','','');
203-- INSERT INTO `Following` (`following_id`,`uid`,`timestamp`,`request_status`) VALUES
204-- ('','','','');
205-- INSERT INTO `Blocked_user` (`blocked_uid`,`uid`) VALUES
206-- ('','');
207-- INSERT INTO `Hidden_user` (`hidden_uid`,`uid`) VALUES
208-- ('','');
209-- INSERT INTO `Photo` (`photo_id`,`uid`,`image_url`,`location_id`,`caption`,`timestamp`) VALUES
210-- ('','','','','','');
211-- INSERT INTO `Tagged_photo` (`photo_id`,`uid`) VALUES
212-- ('','');
213-- INSERT INTO `Comment` (`photo_id`,`uid`,`comment`,`timestamp`) VALUES
214-- ('','','','');
215-- INSERT INTO `Likes` (`uid`,`photo_id`,`timestamp`) VALUES
216-- ('','','');
217-- INSERT INTO `Location` (`location_id`,`location_name`,`longitude`,`latitude`) VALUES
218-- ('','','','');