dbms-project-backend/db.sql at master · patelraj0602/dbms-project-backend · GitHub

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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
CREATE DATABASE letsgo;

USE letsgo;

/* use this command only if you are using SQLite, not needed to use if we using mySQL
 PRAGMA foreign_keys = true;
 */
/* user TABLE */
CREATE TABLE users(
  user_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  salt VARCHAR(255) NOT NULL,
  auth_token VARCHAR(255),
  auth_expiry VARCHAR(32)
);

/* website feedback TABLE */
CREATE TABLE feedback(
  feedback_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  feedback VARCHAR(512)
);

/* spots TABLE */
CREATE TABLE spots (
  spot_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  type ENUM ('place', 'restaurant', 'movie', 'hotel') NOT NULL,
  location_link VARCHAR(100) NOT NULL,
  city VARCHAR(30) NOT NULL,
  description VARCHAR(550) NOT NULL,
  latitude FLOAT(25) NOT NULL,
  longitude FLOAT(25) NOT NULL,
  user_rating FLOAT,
  google_rating FLOAT NOT NULL,
  thumbnail VARCHAR(100)
);

/* images TABLE */
CREATE TABLE images(
  file_name VARCHAR(100) PRIMARY KEY,
  spot_id INT,
  FOREIGN KEY (spot_id) REFERENCES spots(spot_id) ON DELETE
  SET
    NULL
);

ALTER TABLE
  spots
ADD
  FOREIGN KEY (thumbnail) REFERENCES images(file_name);

/* fav_place spots TABLE*/
CREATE TABLE fav_spots(
  user_id INT NOT NULL,
  spot_id INT NOT NULL,
  PRIMARY KEY (user_id, spot_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  FOREIGN KEY (spot_id) REFERENCES spots(spot_id) ON DELETE CASCADE
);

/* want to go spots TABLE*/
CREATE TABLE wtg_spots(
  user_id INT NOT NULL,
  spot_id INT NOT NULL,
  PRIMARY KEY (user_id, spot_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  FOREIGN KEY (spot_id) REFERENCES spots(spot_id) ON DELETE CASCADE
);

/* spots reviews TABLE*/
CREATE TABLE reviews(
  review_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  spot_id INT NOT NULL,
  user_id INT NOT NULL,
  description VARCHAR(120),
  rating FLOAT NOT NULL,
  FOREIGN KEY (spot_id) REFERENCES spots(spot_id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE views (
  user_id INT NOT NULL,
  spot_id INT NOT NULL,
  view_count INT,
  PRIMARY KEY(user_id, spot_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  FOREIGN KEY (spot_id) REFERENCES spots(spot_id) ON DELETE CASCADE
);

insert into
  spots (
    name,
    type,
    location_link,
    city,
    description,
    latitude,
    longitude,
    google_rating
  )
values
  (
    "Kozhikode Beach",
    "place",
    "https://goo.gl/maps/jxwKw4DozMX6TzVN6",
    "Calicut",
    "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat",
    11.318540227169247,
    75.94220455579601,
    4.0
  );

insert into
  spots (
    name,
    type,
    location_link,
    city,
    description,
    latitude,
    longitude,
    google_rating
  )
values
  (
    "BBQ Nations",
    "restaurant",
    "https://goo.gl/maps/jxwKw4DozMX6TzVN6",
    "Calicut",
    "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat",
    11.318540227169247,
    75.94220455579601,
    4.0
  );

insert into
  spots (
    name,
    type,
    location_link,
    city,
    description,
    latitude,
    longitude,
    google_rating
  )
values
  (
    "Crown Cinema",
    "movie",
    "https://goo.gl/maps/jxwKw4DozMX6TzVN6",
    "Calicut",
    "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat",
    11.318540227169247,
    75.94220455579601,
    4.0
  );

insert into
  spots (
    name,
    type,
    location_link,
    city,
    description,
    latitude,
    longitude,
    google_rating
  )
values
  (
    "Taj Residency",
    "hotel",
    "https://goo.gl/maps/jxwKw4DozMX6TzVN6",
    "Calicut",
    "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat",
    11.318540227169247,
    75.94220455579601,
    4.0
  );