这个周我的下一步工作主要是数据库的设计：
首先为了更好的设计OnlineJudge的数据库，我首先查看了SDUOJ的针对数据库部分的源码：
# -*- coding: utf-8 -*-
from django.db import models
from django.contrib.auth.models import User
# Create your models here.
LANG_CHOICE = (
(0, 'NONE'),
(1, 'C'),
(2, 'C++'),
(3, 'Java'),
# (4, 'Python'),
# (5, 'Pascal'),
# (6, 'FORTRAN'),
)
class Problem(models.Model):
uid = models.ForeignKey(User)
create_time = models.DateTimeField(auto_now_add=True)
limit_time = models.PositiveIntegerField(default=1)
limit_memory = models.PositiveIntegerField(default=1024 * 1024 * 128)
# answer_lang = models.PositiveSmallIntegerField(choices=LANG_CHOICE, default=0)
title = models.CharField(max_length=254, unique = True)
content = models.TextField()
input = models.TextField(default='')
output = models.TextField(default='')
# sample_input = models.TextField()
# sample_output = models.TextField()
# file_input = models.FileField()
#file_output = models.FileField()
note = models.TextField(blank=True)
source = models.TextField(blank=True)
# True表示该题目可见， False表示用于比赛，不可见
visible = models.BooleanField(default=True)
# the number of contests which use this problem
numberOfContest = models.IntegerField(default=0)
# CCF题目专用
isCCF = models.BooleanField(default=False)
def accepted(self):
query = Submit.objects.filter(pid=self, status=0)
return query.count()
def submitted(self):
query = Submit.objects.filter(pid=self)
return query.count()
def samples(self):
query = TestCase.objects.filter(pid=self, sample=True)
return query
def __str__(self):
return str(self.title)
class Meta:
ordering = ['create_time']
class UserInfo(models.Model):
id = models.OneToOneField(User, primary_key=True, related_name='info')
school = models.CharField(max_length=50, blank=True)
sid = models.CharField(max_length=50, blank=True)
nickname = models.CharField(max_length=50, blank=True)
problem_ac = models.IntegerField(default = 0)
problem_try = models.IntegerField(default = 0)
problems_ac = models.ManyToManyField(Problem, related_name='aceduser')
problems_try = models.ManyToManyField(Problem, related_name='trieduser')
def __str__(self):
return str(self.id)
def cnt_ac(self):
return self.problems_ac.count()
def cnt_try(self):
return self.problems_try.count()
def ratio(self):
if self.problem_try==0:
return 0
return int(self.problem_ac/self.problem_try*100)
# class ProblemsAC(models.Model):
# uid = models.ForeignKey(UserInfo);
# pid = models.ForeignKey(Problem);
# number = models.IntegerField(default=0);
# class ProblemsTry(models.Model):
# uid = models.ForeignKey(UserInfo);
# pid = models.ForeignKey(Problem);
# number = models.IntegerField(default=0);
class TestCase(models.Model):
pid = models.ForeignKey(Problem)
uid = models.ForeignKey(User)
time = models.DateTimeField(auto_now_add=True)
sample = models.BooleanField(default=False)
input = models.TextField()
output = models.TextField()
# CCF专用
score = models.IntegerField(default=0)
def __str__(self):
return ('Sample ' if self.sample else '')+str(self.pid)
class Meta:
ordering = ['time']
class Contest(models.Model):
uid = models.ForeignKey(User)
name = models.CharField(max_length=256)
start_time = models.DateTimeField()
duration_time = models.DurationField()
problems = models.ManyToManyField(Problem, related_name="contests")
rank = models.TextField(default="{}") #cached rank
last_submit_id = models.IntegerField(default = 0) #last submit id add to rank
private = models.BooleanField(default=False)
password = models.CharField(max_length=256,blank=True)
accounts = models.ManyToManyField(UserInfo, related_name="accessable_contests",blank=True)
# users = models.ManyToManyField(User, related_name="contests")
def __str__(self):
return str(self.name)
class Meta:
ordering = ['start_time']
def get_submits(self):
return Submit.objects.filter(cid=self.id)
def get_problem_list(self):
problems = self.problems.all()
lst = []
cnt = 0
for problem in problems:
lst.append([cnt, chr(cnt + 65), problem])
cnt += 1
return lst
class Submit(models.Model):
STATUS_CHOICE = (
(0, 'Accepted'),
(1, 'Waiting'),
(2, 'Compiling'),
(3, 'Running'),
(-1, 'Compilation Error'),
(-2, 'Syntax Error'),
(-3, 'Runtime Error'),
(-4, 'Output Limit Exceeded'),
(-5, 'Time Limit Exceeded'),
(-6, 'Memory Limit Exceeded'),
(-7, 'Wrong Answer'),
(-8, 'Presentation Error'),
)
pid = models.ForeignKey(Problem)
uid = models.ForeignKey(User)
time = models.DateTimeField(auto_now_add=True)
lang = models.PositiveSmallIntegerField(choices=LANG_CHOICE)
status = models.SmallIntegerField(choices=STATUS_CHOICE, default=1)
run_time = models.PositiveSmallIntegerField(null=True, default=0)
run_memory = models.PositiveIntegerField(null=True, default=0)
source_code = models.FileField(default=None, upload_to='/home/sduacm/OnlineJudge/JudgeFiles/source/')
# -1表示非比赛提交, 其余为比赛提交
cid = models.IntegerField(default=-1)
return_code = models.IntegerField(null=True)
# CCF题目专用
score = models.IntegerField(default=0)
def __str__(self):
return str(self.id)+' '+str(self.pid) + ' ' + str(self.uid) + ' ' + str(self.lang) + ' ' + str(self.cid)
class Meta:
ordering = ['time']
通过阅读源码，同时参考以往OJ的设计以及自己使用OJ的经验，对于onlinejudge的数据库做出基本的设计：
数据库的基本表结构
用户表
题目表
测试结果表
比赛信息表
数据库表之间的关系
用户和题目之间存在多对多关系（测试结果表）
题目和比赛之间存在多对多关系（比赛表）
首先针对OnlineJudge，用户可以使用的语言有以下三种
1.Java
2.C++
3.C
用户表应当包含一下几个属性：
UserID
PassWord
NickName
Gender
Blog
University
Problem_TRY
Problem_ACCEPT
Accuracy
其次，题目表应当包含一下属性：
UserID
Create_Time
Limit_Time
Limit_Memory
Title
Content
Input
Output
Note
Source
Visible //比赛题目非比赛人员不可见
而测试结果表则应有如下属性值：
ProblemID
UserID
Time
Language
Status
Running_TIME
Running_MEMORY
Source_CODE
IS_Contest //是否为比赛提交
Return_CODE
比赛表应有的属性值为：
UerID
UserName
Start_TIME
Duration_TIME
Problems_SET
Rank
Last_Submit_ID
Private
Password
Participants
基于这个设计，CXA将其转换为具体的UML图和数据库实体关系图以及数据字典，而在接下来的一个星期，我们需要将具体的数据库设计转化为真实可用的数据库，并将OnlineJudge的雏形实现出来。
