山东大学-VirtualJudge-总结2

这个周我的下一步工作主要是数据库的设计：

首先为了更好的设计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的雏形实现出来。