3
SET FOREIGN_KEY_CHECKS=0;
5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
12
CREATE DATABASE `codescore` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
20
DROP TABLE IF EXISTS `clarifications`;
21
CREATE TABLE IF NOT EXISTS `clarifications` (
22
`CID` int(11) NOT NULL auto_increment,
23
`Message` longtext collate utf8_bin,
24
`Response` longtext collate utf8_bin,
25
`CompID` int(11) NOT NULL,
27
KEY `CompID` (`CompID`)
28
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
38
DROP TABLE IF EXISTS `coaches`;
39
CREATE TABLE IF NOT EXISTS `coaches` (
40
`PersonID` int(11) NOT NULL,
41
`SchoolID` int(11) NOT NULL,
42
`Password` varchar(255) collate utf8_bin NOT NULL,
43
PRIMARY KEY (`PersonID`,`SchoolID`),
44
KEY `SchoolID` (`SchoolID`)
45
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
55
DROP TABLE IF EXISTS `competitions`;
56
CREATE TABLE IF NOT EXISTS `competitions` (
57
`CompID` int(11) NOT NULL auto_increment,
58
`EndTime` timestamp NOT NULL default '0000-00-00 00:00:00',
59
`Name` varchar(255) collate utf8_bin NOT NULL,
60
`StartTime` timestamp NOT NULL default '0000-00-00 00:00:00',
61
`Active` tinyint(1) NOT NULL default '1',
63
PRIMARY KEY (`CompID`)
64
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;
71
DROP TABLE IF EXISTS `competitors`;
72
CREATE TABLE IF NOT EXISTS `competitors` (
73
`CID` int(11) NOT NULL,
74
`PID` int(11) NOT NULL,
75
`TID` int(11) NOT NULL,
76
PRIMARY KEY (`CID`,`PID`,`TID`),
79
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
89
DROP TABLE IF EXISTS `compileargs`;
90
CREATE TABLE IF NOT EXISTS `compileargs` (
91
`ArgID` int(11) NOT NULL auto_increment,
92
`LangID` int(11) NOT NULL,
93
`Position` int(11) NOT NULL,
94
`Val` varchar(255) collate utf8_bin NOT NULL,
95
PRIMARY KEY (`ArgID`),
96
KEY `LangID` (`LangID`)
97
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8 ;
107
DROP TABLE IF EXISTS `executeargs`;
108
CREATE TABLE IF NOT EXISTS `executeargs` (
109
`ArgID` int(11) NOT NULL auto_increment,
110
`LangID` int(11) NOT NULL,
111
`Position` int(11) NOT NULL,
112
`Val` varchar(255) collate utf8_bin NOT NULL,
113
PRIMARY KEY (`ArgID`),
114
KEY `LangID` (`LangID`)
115
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;
125
DROP TABLE IF EXISTS `judges`;
126
CREATE TABLE IF NOT EXISTS `judges` (
127
`Password` varchar(255) collate utf8_bin NOT NULL,
128
`PersonID` int(11) NOT NULL,
129
`CompID` int(11) NOT NULL,
130
PRIMARY KEY (`PersonID`,`CompID`),
131
KEY `PersonID` (`PersonID`),
132
KEY `CompID` (`CompID`)
133
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
143
DROP TABLE IF EXISTS `judgesolutions`;
144
CREATE TABLE IF NOT EXISTS `judgesolutions` (
145
`CompileStdErr` longtext collate utf8_bin,
146
`CompileStdOut` longtext collate utf8_bin,
147
`ExeStdErr` longtext collate utf8_bin,
148
`ExeStdOut` longtext collate utf8_bin,
149
`LangID` int(11) NOT NULL,
150
`PID` int(11) NOT NULL,
151
`SID` int(11) NOT NULL auto_increment,
152
`Source` longtext collate utf8_bin,
153
`Stat` int(11) default NULL,
154
`Time` timestamp NULL default NULL,
155
`StdOutDiff` longtext collate utf8_bin,
156
PRIMARY KEY (`SID`,`PID`),
157
KEY `LangID` (`LangID`),
160
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
170
DROP TABLE IF EXISTS `languages`;
171
CREATE TABLE IF NOT EXISTS `languages` (
172
`FileExtention` varchar(16) collate utf8_bin NOT NULL,
173
`LID` int(11) NOT NULL auto_increment,
174
`Name` varchar(255) collate utf8_bin NOT NULL,
176
UNIQUE KEY `Name` (`Name`)
177
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ;
184
DROP TABLE IF EXISTS `people`;
185
CREATE TABLE IF NOT EXISTS `people` (
186
`Name` varchar(255) collate utf8_bin NOT NULL,
187
`PersonID` int(11) NOT NULL auto_increment,
188
`School` int(11) default NULL,
189
PRIMARY KEY (`PersonID`),
190
UNIQUE KEY `Name` (`Name`,`School`),
191
KEY `School` (`School`)
192
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;
202
DROP TABLE IF EXISTS `problems`;
203
CREATE TABLE IF NOT EXISTS `problems` (
204
`Description` longtext collate utf8_bin,
205
`Name` varchar(255) collate utf8_bin NOT NULL,
206
`ProblemID` int(11) NOT NULL auto_increment,
207
`SampleInput` longtext collate utf8_bin,
208
`UnixName` varchar(128) collate utf8_bin NOT NULL,
209
`Active` tinyint(1) NOT NULL default '1',
210
`SampleOutput` longtext collate utf8_bin,
211
PRIMARY KEY (`ProblemID`),
212
UNIQUE KEY `UnixName` (`UnixName`),
213
UNIQUE KEY `Name` (`Name`)
214
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ;
221
DROP TABLE IF EXISTS `problemset`;
222
CREATE TABLE IF NOT EXISTS `problemset` (
223
`CompID` int(11) NOT NULL,
224
`ProbID` int(11) NOT NULL,
225
PRIMARY KEY (`CompID`,`ProbID`),
226
KEY `ProbID` (`ProbID`)
227
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
237
DROP TABLE IF EXISTS `problemstatus`;
238
CREATE TABLE IF NOT EXISTS `problemstatus` (
239
`Name` varchar(255) collate utf8_bin default NULL,
240
`StatID` int(11) NOT NULL auto_increment,
241
PRIMARY KEY (`StatID`)
242
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;
249
DROP TABLE IF EXISTS `schools`;
250
CREATE TABLE IF NOT EXISTS `schools` (
251
`Name` varchar(255) collate utf8_bin NOT NULL,
252
`SchoolID` int(11) NOT NULL auto_increment,
253
PRIMARY KEY (`SchoolID`),
254
UNIQUE KEY `Name` (`Name`)
255
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;
262
DROP TABLE IF EXISTS `teamcredentials`;
263
CREATE TABLE IF NOT EXISTS `teamcredentials` (
264
`CompID` int(11) NOT NULL,
265
`Password` varchar(255) collate utf8_bin NOT NULL,
266
`TID` int(11) NOT NULL,
267
PRIMARY KEY (`CompID`,`TID`),
269
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
279
DROP TABLE IF EXISTS `teams`;
280
CREATE TABLE IF NOT EXISTS `teams` (
281
`Name` varchar(255) collate utf8_bin NOT NULL,
282
`School` int(11) NOT NULL,
283
`TeamID` int(11) NOT NULL auto_increment,
284
PRIMARY KEY (`TeamID`),
285
UNIQUE KEY `Name` (`Name`,`School`),
286
KEY `School` (`School`)
287
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=16 ;
297
DROP TABLE IF EXISTS `teamsubmissions`;
298
CREATE TABLE IF NOT EXISTS `teamsubmissions` (
299
`SubID` int(11) NOT NULL auto_increment,
300
`CompileStdErr` longtext collate utf8_bin,
301
`CompileStdOut` longtext collate utf8_bin,
302
`ExeStdErr` longtext collate utf8_bin,
303
`ExeStdOut` longtext collate utf8_bin,
304
`LangID` int(11) NOT NULL,
305
`PID` int(11) NOT NULL,
306
`Points` int(11) NOT NULL,
307
`Source` longtext collate utf8_bin,
308
`Stat` int(11) default NULL,
309
`TID` int(11) NOT NULL,
310
`Time` timestamp NULL default NULL,
311
`StdOutDiff` longtext collate utf8_bin,
312
`UUID` varchar(37) collate utf8_bin default NULL,
313
PRIMARY KEY (`SubID`,`PID`,`TID`),
314
UNIQUE KEY `UUID` (`UUID`),
315
KEY `LangID` (`LangID`),
319
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=29 ;
323
DROP TRIGGER IF EXISTS `codescore`.`setUUID`;
325
CREATE TRIGGER `codescore`.`setUUID` BEFORE INSERT ON `codescore`.`teamsubmissions`
340
DROP TABLE IF EXISTS `testcases`;
341
CREATE TABLE IF NOT EXISTS `testcases` (
342
`CaseID` int(11) NOT NULL auto_increment,
343
`Input` longtext collate utf8_bin NOT NULL,
344
`Output` longtext collate utf8_bin NOT NULL,
345
`ProbID` int(11) NOT NULL,
346
`Active` tinyint(1) NOT NULL default '1',
347
PRIMARY KEY (`CaseID`),
348
KEY `ProbID` (`ProbID`)
349
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8 ;
359
ALTER TABLE `clarifications`
360
ADD CONSTRAINT `clarifications_ibfk_3` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`) ON DELETE CASCADE ON UPDATE CASCADE,
361
ADD CONSTRAINT `clarifications_ibfk_1` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`),
362
ADD CONSTRAINT `clarifications_ibfk_2` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`) ON DELETE CASCADE ON UPDATE CASCADE;
366
ALTER TABLE `coaches`
367
ADD CONSTRAINT `coaches_ibfk_1` FOREIGN KEY (`SchoolID`) REFERENCES `schools` (`SchoolID`);
371
ALTER TABLE `competitors`
372
ADD CONSTRAINT `competitors_ibfk_1` FOREIGN KEY (`CID`) REFERENCES `competitions` (`CompID`) ON DELETE CASCADE ON UPDATE CASCADE,
373
ADD CONSTRAINT `competitors_ibfk_2` FOREIGN KEY (`PID`) REFERENCES `people` (`PersonID`) ON DELETE CASCADE ON UPDATE CASCADE,
374
ADD CONSTRAINT `competitors_ibfk_3` FOREIGN KEY (`TID`) REFERENCES `teams` (`TeamID`) ON DELETE CASCADE ON UPDATE CASCADE;
378
ALTER TABLE `compileargs`
379
ADD CONSTRAINT `compileargs_ibfk_1` FOREIGN KEY (`LangID`) REFERENCES `languages` (`LID`) ON DELETE CASCADE ON UPDATE CASCADE;
383
ALTER TABLE `executeargs`
384
ADD CONSTRAINT `executeargs_ibfk_1` FOREIGN KEY (`LangID`) REFERENCES `languages` (`LID`) ON DELETE CASCADE ON UPDATE CASCADE;
389
ADD CONSTRAINT `judges_ibfk_1` FOREIGN KEY (`PersonID`) REFERENCES `people` (`PersonID`),
390
ADD CONSTRAINT `judges_ibfk_2` FOREIGN KEY (`PersonID`) REFERENCES `people` (`PersonID`) ON DELETE CASCADE ON UPDATE CASCADE,
391
ADD CONSTRAINT `judges_ibfk_3` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`);
395
ALTER TABLE `judgesolutions`
396
ADD CONSTRAINT `judgesolutions_ibfk_1` FOREIGN KEY (`LangID`) REFERENCES `languages` (`LID`) ON DELETE CASCADE ON UPDATE CASCADE,
397
ADD CONSTRAINT `judgesolutions_ibfk_3` FOREIGN KEY (`Stat`) REFERENCES `problemstatus` (`StatID`) ON DELETE CASCADE ON UPDATE CASCADE,
398
ADD CONSTRAINT `judgesolutions_ibfk_4` FOREIGN KEY (`PID`) REFERENCES `problems` (`ProblemID`) ON DELETE CASCADE ON UPDATE CASCADE;
403
ADD CONSTRAINT `people_ibfk_1` FOREIGN KEY (`School`) REFERENCES `schools` (`SchoolID`) ON DELETE CASCADE ON UPDATE CASCADE;
407
ALTER TABLE `problemset`
408
ADD CONSTRAINT `problemset_ibfk_1` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`) ON DELETE CASCADE ON UPDATE CASCADE,
409
ADD CONSTRAINT `problemset_ibfk_2` FOREIGN KEY (`ProbID`) REFERENCES `problems` (`ProblemID`) ON DELETE CASCADE ON UPDATE CASCADE;
413
ALTER TABLE `teamcredentials`
414
ADD CONSTRAINT `teamcredentials_ibfk_1` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`),
415
ADD CONSTRAINT `teamcredentials_ibfk_2` FOREIGN KEY (`TID`) REFERENCES `teams` (`TeamID`),
416
ADD CONSTRAINT `teamcredentials_ibfk_3` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`) ON DELETE CASCADE ON UPDATE CASCADE,
417
ADD CONSTRAINT `teamcredentials_ibfk_4` FOREIGN KEY (`TID`) REFERENCES `teams` (`TeamID`) ON DELETE CASCADE ON UPDATE CASCADE;
422
ADD CONSTRAINT `teams_ibfk_1` FOREIGN KEY (`School`) REFERENCES `schools` (`SchoolID`) ON DELETE CASCADE ON UPDATE CASCADE;
426
ALTER TABLE `teamsubmissions`
427
ADD CONSTRAINT `teamsubmissions_ibfk_1` FOREIGN KEY (`LangID`) REFERENCES `languages` (`LID`) ON DELETE CASCADE ON UPDATE CASCADE,
428
ADD CONSTRAINT `teamsubmissions_ibfk_2` FOREIGN KEY (`PID`) REFERENCES `problems` (`ProblemID`) ON DELETE CASCADE ON UPDATE CASCADE,
429
ADD CONSTRAINT `teamsubmissions_ibfk_3` FOREIGN KEY (`Stat`) REFERENCES `problemstatus` (`StatID`) ON DELETE CASCADE ON UPDATE CASCADE,
430
ADD CONSTRAINT `teamsubmissions_ibfk_4` FOREIGN KEY (`TID`) REFERENCES `teams` (`TeamID`) ON DELETE CASCADE ON UPDATE CASCADE;
434
ALTER TABLE `testcases`
435
ADD CONSTRAINT `testcases_ibfk_1` FOREIGN KEY (`ProbID`) REFERENCES `problems` (`ProblemID`) ON DELETE CASCADE ON UPDATE CASCADE;
437
SET FOREIGN_KEY_CHECKS=1;