« back to all changes in this revision
Viewing changes to CodeScore_DD.sql
- browse files at revision 12
- compare with another revision
- download diff
- download tarball
- view history from revision 12
- Committer: Adam Cornett
- Date: 2008-02-27 21:59:49 UTC
- Revision ID: acornett@ajc-laptop-20080227215949-tfc37qsd01z3l7sp
Adding the MySQL data definition for the database. This file does not have any data, just the create script for the tables and relations.
- files added:
- CodeScore_DD.sql
added
removed
CodeScore_DD.sql
1
-- phpMyAdmin SQL Dump
2
-- version 2.11.2.1
3
-- http://www.phpmyadmin.net
4
--
5
-- Host: localhost
6
-- Generation Time: Feb 27, 2008 at 04:58 PM
7
-- Server version: 3.23.32
8
-- PHP Version: 5.2.3-1ubuntu6.3
9
10
SET FOREIGN_KEY_CHECKS=0;
11
12
13
SET AUTOCOMMIT=0;
14
START TRANSACTION;
15
16
--
17
-- Database: `codescore`
18
--
19
DROP DATABASE `codescore`;
20
CREATE DATABASE `codescore`;
21
USE codescore;
22
23
-- --------------------------------------------------------
24
25
--
26
-- Table structure for table `clarifications`
27
--
28
29
DROP TABLE IF EXISTS `clarifications`;
30
CREATE TABLE IF NOT EXISTS `clarifications` (
31
`CID` int(11) NOT NULL auto_increment,
32
`Message` longtext,
33
`Response` longtext,
34
`CompID` int(11) NOT NULL,
35
PRIMARY KEY (`CID`),
36
KEY `CompID` (`CompID`)
37
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
38
39
-- --------------------------------------------------------
40
41
--
42
-- Table structure for table `competitions`
43
--
44
45
DROP TABLE IF EXISTS `competitions`;
46
CREATE TABLE IF NOT EXISTS `competitions` (
47
`CompID` int(11) NOT NULL auto_increment,
48
`EndTime` timestamp NOT NULL default '0000-00-00 00:00:00',
49
`Name` varchar(255) NOT NULL,
50
`StartTime` timestamp NOT NULL default '0000-00-00 00:00:00',
51
PRIMARY KEY (`CompID`)
52
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
53
54
-- --------------------------------------------------------
55
56
--
57
-- Table structure for table `competitors`
58
--
59
60
DROP TABLE IF EXISTS `competitors`;
61
CREATE TABLE IF NOT EXISTS `competitors` (
62
`CID` int(11) NOT NULL,
63
`PID` int(11) NOT NULL,
64
`TID` int(11) NOT NULL,
65
PRIMARY KEY (`CID`,`PID`,`TID`),
66
KEY `PID` (`PID`),
67
KEY `TID` (`TID`)
68
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
69
70
-- --------------------------------------------------------
71
72
--
73
-- Table structure for table `compileargs`
74
--
75
76
DROP TABLE IF EXISTS `compileargs`;
77
CREATE TABLE IF NOT EXISTS `compileargs` (
78
`ArgID` int(11) NOT NULL auto_increment,
79
`LangID` int(11) NOT NULL,
80
`Position` int(11) NOT NULL,
81
`Val` varchar(255) NOT NULL,
82
PRIMARY KEY (`ArgID`),
83
KEY `LangID` (`LangID`)
84
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
85
86
-- --------------------------------------------------------
87
88
--
89
-- Table structure for table `executeargs`
90
--
91
92
DROP TABLE IF EXISTS `executeargs`;
93
CREATE TABLE IF NOT EXISTS `executeargs` (
94
`ArgID` int(11) NOT NULL auto_increment,
95
`LangID` int(11) NOT NULL,
96
`Position` int(11) NOT NULL,
97
`Val` varchar(255) NOT NULL,
98
PRIMARY KEY (`ArgID`),
99
KEY `LangID` (`LangID`)
100
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
101
102
-- --------------------------------------------------------
103
104
--
105
-- Table structure for table `judgecredentials`
106
--
107
108
DROP TABLE IF EXISTS `judgecredentials`;
109
CREATE TABLE IF NOT EXISTS `judgecredentials` (
110
`CID` int(11) NOT NULL auto_increment,
111
`Password` varchar(255) default NULL,
112
`PersonID` int(11) default NULL,
113
PRIMARY KEY (`CID`),
114
KEY `PersonID` (`PersonID`)
115
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
116
117
-- --------------------------------------------------------
118
119
--
120
-- Table structure for table `languages`
121
--
122
123
DROP TABLE IF EXISTS `languages`;
124
CREATE TABLE IF NOT EXISTS `languages` (
125
`FileExtention` varchar(16) NOT NULL,
126
`LID` int(11) NOT NULL auto_increment,
127
`Name` varchar(255) NOT NULL,
128
PRIMARY KEY (`LID`),
129
UNIQUE KEY `Name` (`Name`)
130
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
131
132
-- --------------------------------------------------------
133
134
--
135
-- Table structure for table `people`
136
--
137
138
DROP TABLE IF EXISTS `people`;
139
CREATE TABLE IF NOT EXISTS `people` (
140
`Name` varchar(255) NOT NULL,
141
`PersonID` int(11) NOT NULL auto_increment,
142
`School` int(11) NOT NULL,
143
`IsJudge` tinyint(1) default NULL,
144
PRIMARY KEY (`PersonID`),
145
UNIQUE KEY `Name` (`Name`,`School`)
146
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
147
148
-- --------------------------------------------------------
149
150
--
151
-- Table structure for table `problems`
152
--
153
154
DROP TABLE IF EXISTS `problems`;
155
CREATE TABLE IF NOT EXISTS `problems` (
156
`Description` longtext,
157
`Name` varchar(255) NOT NULL,
158
`ProblemID` int(11) NOT NULL auto_increment,
159
`SampleInput` longtext,
160
`UnixName` varchar(128) NOT NULL,
161
`Active` tinyint(1) NOT NULL default '1',
162
`SampleOutput` longtext,
163
PRIMARY KEY (`ProblemID`),
164
UNIQUE KEY `UnixName` (`UnixName`),
165
UNIQUE KEY `Name` (`Name`)
166
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
167
168
-- --------------------------------------------------------
169
170
--
171
-- Table structure for table `problemset`
172
--
173
174
DROP TABLE IF EXISTS `problemset`;
175
CREATE TABLE IF NOT EXISTS `problemset` (
176
`CompID` int(11) NOT NULL,
177
`ProbID` int(11) NOT NULL,
178
PRIMARY KEY (`CompID`,`ProbID`),
179
KEY `ProbID` (`ProbID`)
180
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
181
182
-- --------------------------------------------------------
183
184
--
185
-- Table structure for table `problemstatus`
186
--
187
188
DROP TABLE IF EXISTS `problemstatus`;
189
CREATE TABLE IF NOT EXISTS `problemstatus` (
190
`Name` varchar(255) default NULL,
191
`StatID` int(11) NOT NULL auto_increment,
192
PRIMARY KEY (`StatID`)
193
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
194
195
-- --------------------------------------------------------
196
197
--
198
-- Table structure for table `schools`
199
--
200
201
DROP TABLE IF EXISTS `schools`;
202
CREATE TABLE IF NOT EXISTS `schools` (
203
`Name` varchar(255) NOT NULL,
204
`SchoolID` int(11) NOT NULL auto_increment,
205
PRIMARY KEY (`SchoolID`),
206
UNIQUE KEY `Name` (`Name`)
207
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
208
209
-- --------------------------------------------------------
210
211
--
212
-- Table structure for table `submission`
213
--
214
215
DROP TABLE IF EXISTS `submission`;
216
CREATE TABLE IF NOT EXISTS `submission` (
217
`CompileStdErr` longtext,
218
`CompileStdOut` longtext,
219
`ExeStdErr` longtext,
220
`ExeStdOut` longtext,
221
`LangID` int(11) NOT NULL,
222
`PID` int(11) NOT NULL,
223
`Points` int(11) NOT NULL,
224
`SID` int(11) NOT NULL auto_increment,
225
`Source` longtext,
226
`Stat` int(11) default NULL,
227
`TID` int(11) NOT NULL,
228
`Time` timestamp NULL default NULL,
229
`StdOutDiff` longtext,
230
PRIMARY KEY (`SID`,`PID`,`TID`),
231
KEY `LangID` (`LangID`),
232
KEY `PID` (`PID`),
233
KEY `Stat` (`Stat`),
234
KEY `TID` (`TID`)
235
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
236
237
-- --------------------------------------------------------
238
239
--
240
-- Table structure for table `teamcredentials`
241
--
242
243
DROP TABLE IF EXISTS `teamcredentials`;
244
CREATE TABLE IF NOT EXISTS `teamcredentials` (
245
`CompID` int(11) NOT NULL,
246
`Password` varchar(255) NOT NULL,
247
`TID` int(11) NOT NULL,
248
PRIMARY KEY (`CompID`,`TID`),
249
KEY `TID` (`TID`)
250
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
251
252
-- --------------------------------------------------------
253
254
--
255
-- Table structure for table `teams`
256
--
257
258
DROP TABLE IF EXISTS `teams`;
259
CREATE TABLE IF NOT EXISTS `teams` (
260
`Name` varchar(255) NOT NULL,
261
`School` int(11) NOT NULL,
262
`TeamID` int(11) NOT NULL auto_increment,
263
PRIMARY KEY (`TeamID`),
264
UNIQUE KEY `Name` (`Name`,`School`),
265
KEY `School` (`School`)
266
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
267
268
-- --------------------------------------------------------
269
270
--
271
-- Table structure for table `testcases`
272
--
273
274
DROP TABLE IF EXISTS `testcases`;
275
CREATE TABLE IF NOT EXISTS `testcases` (
276
`CaseID` int(11) NOT NULL auto_increment,
277
`Input` longtext NOT NULL,
278
`Output` longtext NOT NULL,
279
`ProbID` int(11) NOT NULL,
280
`Active` tinyint(1) NOT NULL default '1',
281
PRIMARY KEY (`CaseID`),
282
KEY `ProbID` (`ProbID`)
283
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
284
285
--
286
-- Constraints for dumped tables
287
--
288
289
--
290
-- Constraints for table `clarifications`
291
--
292
ALTER TABLE `clarifications`
293
ADD CONSTRAINT `clarifications_ibfk_1` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`),
294
ADD CONSTRAINT `clarifications_ibfk_2` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`) ON DELETE CASCADE ON UPDATE CASCADE;
295
296
--
297
-- Constraints for table `competitors`
298
--
299
ALTER TABLE `competitors`
300
ADD CONSTRAINT `competitors_ibfk_1` FOREIGN KEY (`CID`) REFERENCES `competitions` (`CompID`) ON DELETE CASCADE ON UPDATE CASCADE,
301
ADD CONSTRAINT `competitors_ibfk_2` FOREIGN KEY (`PID`) REFERENCES `people` (`PersonID`) ON DELETE CASCADE ON UPDATE CASCADE,
302
ADD CONSTRAINT `competitors_ibfk_3` FOREIGN KEY (`TID`) REFERENCES `teams` (`TeamID`) ON DELETE CASCADE ON UPDATE CASCADE;
303
304
--
305
-- Constraints for table `compileargs`
306
--
307
ALTER TABLE `compileargs`
308
ADD CONSTRAINT `compileargs_ibfk_1` FOREIGN KEY (`LangID`) REFERENCES `languages` (`LID`) ON DELETE CASCADE ON UPDATE CASCADE;
309
310
--
311
-- Constraints for table `executeargs`
312
--
313
ALTER TABLE `executeargs`
314
ADD CONSTRAINT `executeargs_ibfk_1` FOREIGN KEY (`LangID`) REFERENCES `languages` (`LID`) ON DELETE CASCADE ON UPDATE CASCADE;
315
316
--
317
-- Constraints for table `judgecredentials`
318
--
319
ALTER TABLE `judgecredentials`
320
ADD CONSTRAINT `judgecredentials_ibfk_1` FOREIGN KEY (`PersonID`) REFERENCES `people` (`PersonID`),
321
ADD CONSTRAINT `judgecredentials_ibfk_2` FOREIGN KEY (`PersonID`) REFERENCES `people` (`PersonID`) ON DELETE CASCADE ON UPDATE CASCADE;
322
323
--
324
-- Constraints for table `problemset`
325
--
326
ALTER TABLE `problemset`
327
ADD CONSTRAINT `problemset_ibfk_1` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`) ON DELETE CASCADE ON UPDATE CASCADE,
328
ADD CONSTRAINT `problemset_ibfk_2` FOREIGN KEY (`ProbID`) REFERENCES `problems` (`ProblemID`) ON DELETE CASCADE ON UPDATE CASCADE;
329
330
--
331
-- Constraints for table `submission`
332
--
333
ALTER TABLE `submission`
334
ADD CONSTRAINT `submission_ibfk_1` FOREIGN KEY (`LangID`) REFERENCES `languages` (`LID`) ON DELETE CASCADE ON UPDATE CASCADE,
335
ADD CONSTRAINT `submission_ibfk_2` FOREIGN KEY (`PID`) REFERENCES `problems` (`ProblemID`) ON DELETE CASCADE ON UPDATE CASCADE,
336
ADD CONSTRAINT `submission_ibfk_3` FOREIGN KEY (`Stat`) REFERENCES `problemstatus` (`StatID`) ON DELETE CASCADE ON UPDATE CASCADE,
337
ADD CONSTRAINT `submission_ibfk_4` FOREIGN KEY (`TID`) REFERENCES `teams` (`TeamID`) ON DELETE CASCADE ON UPDATE CASCADE;
338
339
--
340
-- Constraints for table `teamcredentials`
341
--
342
ALTER TABLE `teamcredentials`
343
ADD CONSTRAINT `teamcredentials_ibfk_1` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`),
344
ADD CONSTRAINT `teamcredentials_ibfk_2` FOREIGN KEY (`TID`) REFERENCES `teams` (`TeamID`),
345
ADD CONSTRAINT `teamcredentials_ibfk_3` FOREIGN KEY (`CompID`) REFERENCES `competitions` (`CompID`) ON DELETE CASCADE ON UPDATE CASCADE,
346
ADD CONSTRAINT `teamcredentials_ibfk_4` FOREIGN KEY (`TID`) REFERENCES `teams` (`TeamID`) ON DELETE CASCADE ON UPDATE CASCADE;
347
348
--
349
-- Constraints for table `teams`
350
--
351
ALTER TABLE `teams`
352
ADD CONSTRAINT `teams_ibfk_1` FOREIGN KEY (`School`) REFERENCES `schools` (`SchoolID`) ON DELETE CASCADE ON UPDATE CASCADE;
353
354
--
355
-- Constraints for table `testcases`
356
--
357
ALTER TABLE `testcases`
358
ADD CONSTRAINT `testcases_ibfk_1` FOREIGN KEY (`ProbID`) REFERENCES `problems` (`ProblemID`) ON DELETE CASCADE ON UPDATE CASCADE;
359
360
SET FOREIGN_KEY_CHECKS=1;
361
362
COMMIT;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1