This repository has been archived by the owner on Oct 21, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
setup.sql
114 lines (73 loc) · 2.21 KB
/
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
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
SET GLOBAL event_scheduler = ON;
CREATE DATABASE IF NOT EXISTS PleasantTours;
USE PleasantTours;
CREATE TABLE IF NOT EXISTS UnverifiedMembers (
ID CHAR(32),
Username VARCHAR(20) NOT NULL,
`Password` CHAR(97) NOT NULL,
Email VARCHAR(255) NOT NULL,
Expiration DATETIME DEFAULT ADDTIME(NOW(), 900), -- 900 seconds = 15 minutes
PRIMARY KEY (ID)
);
CREATE TABLE IF NOT EXISTS Members (
Username VARCHAR(20) NOT NULL,
`Password` CHAR(97) NOT NULL,
Email VARCHAR(255) NOT NULL,
Administrator BOOLEAN DEFAULT 0,
PRIMARY KEY (Username)
);
CREATE TABLE IF NOT EXISTS ResetPasswordMembers (
ID CHAR(32),
Member VARCHAR(20) NOT NULL,
Expiration DATETIME DEFAULT ADDTIME(NOW(), 900), -- 900 seconds = 15 minutes
PRIMARY KEY (ID),
FOREIGN KEY (Member) REFERENCES Members(Username)
);
CREATE TABLE IF NOT EXISTS Categories (
ID CHAR(16),
`Name` VARCHAR(100) NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE IF NOT EXISTS Countries (
ID CHAR(16),
`Name` VARCHAR(100) NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE IF NOT EXISTS Tours (
ID CHAR(16),
`Name` VARCHAR(100) NOT NULL,
ShortDescription VARCHAR(200) NOT NULL,
LongDescription VARCHAR(1000) NOT NULL,
DetailedInformations TEXT NOT NULL,
Price DECIMAL(15, 2) NOT NULL,
Sale TINYINT UNSIGNED DEFAULT 0,
Country CHAR(16) NOT NULL,
Category CHAR(16) NOT NULL,
Avatar VARCHAR(200) NOT NULL,
Hot BOOLEAN DEFAULT 0,
PRIMARY KEY (ID),
FOREIGN KEY (Country) REFERENCES Countries(ID),
FOREIGN KEY (Category) REFERENCES Categories(ID),
CHECK (Sale <= 100)
);
CREATE TABLE IF NOT EXISTS Reviews (
ID CHAR(16),
Tour CHAR(16) NOT NULL,
Author VARCHAR(20) NOT NULL,
Content VARCHAR(1000) NOT NULL,
Rating TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (Tour) REFERENCES Tours(ID),
FOREIGN KEY (Author) REFERENCES Members(Username),
CHECK (1 >= Rating <= 5)
);
DELIMITER $$
CREATE EVENT IF NOT EXISTS ExpirationChecker
ON SCHEDULE
EVERY 1 SECOND
DO
BEGIN
DELETE FROM UnverifiedMembers WHERE Expiration < NOW();
DELETE FROM ResetPasswordMembers WHERE Expiration < NOW();
END; $$
DELIMITER ;