-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathOutdoorBuddies_database_schema.sql
152 lines (134 loc) · 5.92 KB
/
OutdoorBuddies_database_schema.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
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
USE DATABASE outdoorbuddies;
CREATE TABLE UserProfile (
user_id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(255) NOT NULL,
lastname VARCHAR(255) NOT NULL,
username VARCHAR(255) NOT NULL,
passwd VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
dob DATE,
gender CHAR(1),
mobile CHAR(10),
address VARCHAR(255),
imageurl VARCHAR(255),
createddate DATE,
CONSTRAINT pk_UserID PRIMARY KEY(user_id)
);
INSERT INTO UserProfile VALUES(NULL,"sridhar","somasani","sridhar_somasani","sridhar","[email protected]","1990-1-1","M","1234567895","University Place","images/male.jpg", CURRENT_DATE);
INSERT INTO UserProfile VALUES(NULL,"adithya", "tallap", "adithya_tallap", "adithya", "[email protected]", "1990-1-1", "M", "1234567895", "Lamar Place", "images/male.jpg", CURRENT_DATE);
INSERT INTO UserProfile VALUES(NULL,"shiva", "ragi", "shiva_ragi", "shiva", "[email protected]", "1990-1-1", "M", "1234567895", "Lamar Place", "images/male.jpg", CURRENT_DATE);
INSERT INTO UserProfile VALUES(NULL,"abhinav", "kotha", "abhinav_kotha", "abhinav", "[email protected]", "1990-1-1", "M", "1234567895", "University Place", "images/male.jpg", CURRENT_DATE);
INSERT INTO UserProfile VALUES(NULL,"pranith", "thipparthi", "pranith_thipparthi", "pranith", "[email protected]", "1990-1-1", "M", "1234567895", "Lamar Place", "images/male.jpg", CURRENT_DATE);
DROP PROCEDURE IF EXISTS usp_AddNewUser;
DELIMITER $$
CREATE PROCEDURE usp_AddNewUser(IN fname varchar(255),
IN lname VARCHAR(255),
IN uname VARCHAR(255),
IN passwd VARCHAR(255),
IN _email VARCHAR(255),
IN _dob VARCHAR(255),
IN _gender CHAR(1),
IN _mobile CHAR(10),
IN _address VARCHAR(255),
IN _imageurl VARCHAR(255)
)
BEGIN
INSERT INTO UserProfile (user_id,firstname, lastname, username, passwd, email, dob, gender, mobile, address, imageurl, createddate) VALUES(NULL, fname, lname, uname, passwd, _email, _dob, _gender, _mobile, _address, _imageurl, CURRENT_DATE);
SELECT * FROM UserProfile WHERE username = uname AND email = _email;
END $$
DELIMITER ;
# SHOW PROCEDURE STATUS LIKE '%'
CALL usp_AddNewUser('test','test','test','test','[email protected]','2014-11-03','M','1234567896','No Clue','images/male.jpg');
CREATE TABLE Games (
game_id INT NOT NULL AUTO_INCREMENT,
game_name VARCHAR(255) NOT NULL,
game_desc VARCHAR(255) NOT NULL,
create_date DATE,
CONSTRAINT pk_GameID PRIMARY KEY(game_id)
);
CREATE TABLE GameEvents (
event_id INT NOT NULL AUTO_INCREMENT,
game_id INT NOT NULL,
isCompleted TINYINT NOT NULL DEFAULT 0,
event_date DATE,
event_start_time VARCHAR(5) NOT NULL,
event_end_time VARCHAR(5) NOT NULL,
event_place VARCHAR(255) NOT NULL,
event_address VARCHAR(255) NOT NULL,
create_date DATE,
modified_date DATE,
CONSTRAINT pk_EventID PRIMARY KEY(event_id),
CONSTRAINT fk_GameID FOREIGN KEY (game_id) REFERENCES Games(game_id)
);
CREATE TABLE EventParticipants(
event_participant_id INT NOT NULL AUTO_INCREMENT,
event_id INT NOT NULL,
user_id INT NOT NULL,
accept TINYINT NOT NULL DEFAULT 0,
is_attended TINYINT NOT NULL DEFAULT 0,
feedback VARCHAR(255),
expertise INT DEFAULT 0,
CONSTRAINT pk_EventParticipantsID PRIMARY KEY(event_participant_id),
CONSTRAINT fk_EventParticipants_UserID FOREIGN KEY(user_id) REFERENCES UserProfile(user_id),
CONSTRAINT fk_EventParticipants_EventID FOREIGN KEY(event_id) REFERENCES GameEvents(event_id)
);
CREATE TABLE EventGallery(
gallery_id INT NOT NULL AUTO_INCREMENT,
event_id INT NOT NULL,
description VARCHAR(255),
image_url VARCHAR(255) NOT NULL,
CONSTRAINT pk_EventGalleryID PRIMARY KEY(gallery_id),
CONSTRAINT fk_EventGallery_EventID FOREIGN KEY(event_id) REFERENCES GameEvents(event_id)
);
INSERT INTO Games VALUES(NULL, "Football", "football description", CURRENT_DATE);
INSERT INTO Games VALUES(NULL, "Basketball", "basketball description", CURRENT_DATE);
INSERT INTO Games VALUES(NULL, "Cricket", "cricket description", CURRENT_DATE);
INSERT INTO Games VALUES(NULL, "Tennis", "tennis description", CURRENT_DATE);
DROP PROCEDURE IF EXISTS usp_AddNewEvent;
DELIMITER $$
CREATE PROCEDURE usp_AddNewEvent(
IN eventdate varchar(255),
IN starttime VARCHAR(255),
IN endtime VARCHAR(255),
IN gametype VARCHAR(255),
IN place VARCHAR(255),
IN address VARCHAR(255)
)
BEGIN
DECLARE event_game_id INT;
DECLARE event_current_id INT DEFAULT -1;
SET event_game_id = -1;
SELECT game_id into @event_game_id from Games WHERE game_name = gametype;
INSERT INTO GameEvents (event_id,game_id, isCompleted, event_date, event_start_time, event_end_time, event_place, event_address, create_date, modified_date) VALUES(NULL, @event_game_id, 0, eventdate, starttime, endtime, place, address, CURRENT_DATE, CURRENT_DATE);
SELECT LAST_INSERT_ID();
END $$
DELIMITER ;
CALL usp_AddNewEvent('2014-11-03','10 AM','11 AM','Football','placeddd','addressss');
DROP PROCEDURE IF EXISTS usp_AddNewEvent;
DELIMITER $$
CREATE PROCEDURE usp_AddNewEvent(
IN eventdate varchar(255),
IN starttime VARCHAR(255),
IN endtime VARCHAR(255),
IN gametype VARCHAR(255),
IN place VARCHAR(255),
IN address VARCHAR(255)
)
BEGIN
DECLARE event_game_id INT;
DECLARE event_current_id INT DEFAULT -1;
SET event_game_id = -1;
SELECT game_id into @event_game_id from Games WHERE game_name = gametype;
INSERT INTO GameEvents (event_id,game_id, isCompleted, event_date, event_start_time, event_end_time, event_place, event_address, create_date, modified_date) VALUES(NULL, @event_game_id, 0, eventdate, starttime, endtime, place, address, CURRENT_DATE, CURRENT_DATE);
SELECT LAST_INSERT_ID();
END $$
DELIMITER ;
CALL usp_AddNewEvent('2014-11-03','10 AM','11 AM','Football','placeddd','addressss');
DELIMITER $$
CREATE PROCEDURE usp_GetScheduleEvents(
IN userid INT
)
BEGIN
SELECT ge.event_id, ge.game_id, ge.isCompleted, ge.event_start_time, ge.event_end_time, ge.event_date, ge.event_place, ge.event_address, ep.accept, ep.is_attended FROM gameevents ge inner join eventparticipants ep on ge.event_id = ep.event_id WHERE ep.user_id = userid;
END $$
DELIMITER ;