-
Notifications
You must be signed in to change notification settings - Fork 0
/
database_setup.sql
79 lines (68 loc) · 2.69 KB
/
database_setup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
create database letsreview;
use letsreview;
CREATE TABLE `topic` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`summary` varchar(64) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `topic` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL DEFAULT '',
`phone_no` varchar(10) DEFAULT NULL,
`email_id` varchar(30) NOT NULL,
`username` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email_id` (`email_id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `phone_no` (`phone_no`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;
CREATE TABLE `error_codes` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`code` varchar(10) NOT NULL,
`message` varchar(256) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `code` (`code`),
UNIQUE KEY `message` (`message`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
CREATE TABLE `authentication` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `username` (`username`),
CONSTRAINT `authentication_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;
CREATE TABLE `review` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`body` varchar(3000) DEFAULT '',
`user_id` bigint(20) DEFAULT NULL,
`rating` int(11) NOT NULL,
`created_on` datetime NOT NULL,
`topic_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`topic_id`),
KEY `topic_id` (`topic_id`),
CONSTRAINT `review_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
CONSTRAINT `review_ibfk_2` FOREIGN KEY (`topic_id`) REFERENCES `topic` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=367 DEFAULT CHARSET=latin1;
CREATE TABLE `user_session` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`session_token` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
CONSTRAINT `user_session_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1;
INSERT INTO `error_codes` (`id`, `code`, `message`)
VALUES
(1, '2004', 'username does not exists'),
(2, '2003', 'password is incorrect'),
(3, '2005', 'user is not logged in'),
(4, '2006', 'session token does not match'),
(5, '2007', 'you have already reviewed this topic'),
(6, '2000', 'username not available'),
(7, '2001', 'this mobile number is already registered'),
(8, '2002', 'this email id is already registered');