2be1VzZK

1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
2SET AUTOCOMMIT = 0;
3START TRANSACTION;
4SET time_zone = "+00:00";
5
6-- users table
7DROP TABLE IF EXISTS `users`; -- all table names are plural
8CREATE TABLE `users` (
9	`userID` int(11) NOT NULL AUTO_INCREMENT, -- all column names are camelCase
10	`username` varchar(32) NOT NULL,
11	`hashedPass` varchar(256) NOT NULL, -- using PHP password_create() and password_verify()
12	`email` varchar(256) NOT NULL,
13	PRIMARY KEY (`userID`), -- make the userID column the primary key
14	UNIQUE KEY `username` (`username`) -- also require usernames to be unique
15);
16
17-- quests table
18DROP TABLE IF EXISTS `quests`;
19CREATE TABLE `quests` (
20	`questID` int(11) NOT NULL AUTO_INCREMENT,
21	`userID` int(11) NOT NULL,
22	`locationUUID` char(36) NOT NULL,
23	`difficulty` tinyint NOT NULL DEFAULT '0', -- tinyint allows for max 1 byte worth of integer characters, a max value of ~255
24	`mapName` varchar(48) NOT NULL,
25	`description` varchar(2048), -- varchar(2048) allows for almost roughly 3 paragraphs of latin-based text (tested based on character count of a generated lorem ipsum text of 5 paragraphs)
26	`crumbs` int(11) NOT NULL DEFAULT '0',
27	`dateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
28	PRIMARY KEY (`questID`),
29	CONSTRAINT `foreignKey_quests_userID` -- all foreign keys follow the naming scheme "foreignKey_currentTableName_columnName"
30		FOREIGN KEY (`userID`) REFERENCES `users`(`userID`)
31		ON DELETE CASCADE
32		ON UPDATE RESTRICT,
33	CONSTRAINT `foreignKey_quests_locationUUID`
34		FOREIGN KEY (`locationUUID`) REFERENCES `locations`(`locationUUID`)
35		ON DELETE CASCADE
36		ON UPDATE RESTRICT
37);
38
39-- comments table
40DROP TABLE IF EXISTS `comments`;
41CREATE TABLE `comments` (
42	`commentID` int(11) NOT NULL AUTO_INCREMENT,
43	`userID` int(11) NOT NULL,
44	`questID` int(11) NOT NULL,
45	`content` varchar(256) NOT NULL,
46	`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
47	PRIMARY KEY (`commentID`),
48	CONSTRAINT `foreignKey_comments_userID`
49		FOREIGN KEY (`userID`) REFERENCES `users`(`userID`)
50		ON DELETE CASCADE
51		ON UPDATE RESTRICT,
52	CONSTRAINT `foreignKey_comments_questID`
53		FOREIGN KEY (`questID`) REFERENCES `quests`(`questID`)
54		ON DELETE CASCADE
55		ON UPDATE RESTRICT
56);
57
58-- ratings table
59DROP TABLE IF EXISTS `ratings`;
60CREATE TABLE `ratings` (
61	`ratingID` int(11) NOT NULL AUTO_INCREMENT,
62	`userID` int(11) NOT NULL,
63	`questID` int(11) NOT NULL,
64	`rating` int(4) NOT NULL,
65	PRIMARY KEY (`ratingID`),
66	CONSTRAINT `foreignKey_ratings_userID`
67		FOREIGN KEY (`userID`) REFERENCES `users`(`userID`)
68		ON DELETE CASCADE
69		ON UPDATE RESTRICT,
70	CONSTRAINT `foreignKey_ratings_questID`
71		FOREIGN KEY (`questID`) REFERENCES `quests`(`questID`)
72		ON DELETE CASCADE
73		ON UPDATE RESTRICT
74);
75
76-- crumbs table
77DROP TABLE IF EXISTS `crumbs`;
78CREATE TABLE `crumbs` (
79	`crumbID` int(11) NOT NULL AUTO_INCREMENT,
80	`questID` int(11) NOT NULL,
81	`order` int(11) NOT NULL,
82	`hints` int(11) NOT NULL DEFAULT '0',
83	PRIMARY KEY (`crumbID`),
84	CONSTRAINT `foreignKey_crumbs_questID`
85		FOREIGN KEY (`questID`) REFERENCES `quests`(`questID`)
86		ON DELETE CASCADE
87		ON UPDATE RESTRICT
88);
89
90-- riddles table
91DROP TABLE IF EXISTS `riddles`;
92CREATE TABLE `riddles` (
93	`crumbID` int(11) NOT NULL,
94	`question` varchar(128) NOT NULL,
95	`answer` varchar(32) NOT NULL,
96	CONSTRAINT `foreignKey_riddles_crumbID`
97		FOREIGN KEY (`crumbID`) REFERENCES `crumbs`(`crumbID`)
98		ON DELETE CASCADE
99		ON UPDATE RESTRICT
100);
101
102-- locations table
103DROP TABLE IF EXISTS `locations`;
104CREATE TABLE `locations` (
105	`locationUUID` char(36) NOT NULL,
106	`latitude` decimal(10,7) NOT NULL,
107	`longitude` decimal(10,7) NOT NULL,
108	PRIMARY KEY(`locationUUID`)
109);
110
111-- hints table
112DROP TABLE IF EXISTS `hints`;
113CREATE TABLE `hints` (
114	`crumbID` int(11) NOT NULL,
115	`hint` VARCHAR(64) NOT NULL,
116	`order` int(11) NOT NULL,
117	CONSTRAINT `foreignKey_hints_crumbID`
118		FOREIGN KEY (`crumbID`) REFERENCES `crumbs`(`crumbID`)
119		ON DELETE CASCADE
120		ON UPDATE RESTRICT
121);
122
123-- progress table
124DROP TABLE IF EXISTS `progresses`;
125CREATE TABLE `progresses` (
126	`userID` int(11) NOT NULL,
127	`crumbID` int(11) NOT NULL,
128	`completed` BOOLEAN NOT NULL DEFAULT 'FALSE',
129	CONSTRAINT `foreignKey_progresses_crumbID`
130		FOREIGN KEY (`crumbID`) REFERENCES `crumbs`(`crumbID`)
131		ON DELETE CASCADE
132		ON UPDATE RESTRICT,
133);