在线云评测系统日志（二）：数据库逻辑的确定

1.数据库建表

         

Table : Problem

id : num

subscribe : text

test_case : varchar(200)

test_result : varchar(200)

submissions：num(16)

acceptences：num(16)

 

Table : User

id : num

mail : varchar(32)

name : varchar(16)

password : varchar(64)

 

Table : SubmitCode

id : num

user : User.id

problem : Problem.id

submit_code_text : text

submit_date : Date

condition : varchar(32)

 

Table : AcceptedCode

id : num

user : User.id

problem : Problem.id

accepted_code_text : text

accepted_date : Date

time­_cost: num(16)

memory_cost: num(16)

 

Table : Discussion

id : num

user : User.id

problem : Problem.id

pub_date:Date

submit_text : text

vote_count :num

view _count: num

Table : Response

id :num

discussion :Discussion.id

user : User.id

pud_date:Date

res_text:text

vote_count:num

 2.Python-django 中 Models.py 文件 代码实现

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import datetime
from django.db import models
from django.utils import timezone
from django.utils.encoding import python_2_unicode_compatible

class Problem(models.Model):
	subscribe = models.TextField()
	test_case = models.CharField(max_length=200)
	test_result = models.CharField(max_length=200)
	submissions = models.IntegerField(16)
	acceptences = models.IntegerField(16)

class User(models.Model):
	mail = models.CharField(max_length=32)
	name = models.CharField(max_length=16)
	password = models.CharField(max_length=64)

class SubmitCode(models.Model):
	user = models.ForeignKey(User, on_delete=models.CASCADE)
	problem = models.ForeignKey(Problem, on_delete=models.CASCADE)
	submit_code_text = models.TextField()
	submit_date = models.DateTimeField('date published')
	Condition = models.CharField(max_length=32)

class AcceptedCode(models.Model):
	user = models.ForeignKey(User, on_delete=models.CASCADE)
	problem = models.ForeignKey(Problem, on_delete=models.CASCADE)
	accepted_code_text = models.TextField()
	accepted_date = models.DateTimeField('date published')
	time_cost = models.IntegerField(16)
	memory_cost = models.IntegerField(16)

class Discussion(models.Model):
	user = models.ForeignKey(User, on_delete=models.CASCADE)
	problem = models.ForeignKey(Problem, on_delete=models.CASCADE)
	pub_date = models.DateTimeField('date published')
	submit_text = models.TextField()
	vote_count = models.IntegerField(default=0)
	view_count = models.IntegerField(default=0)

class Response(models.Model):
	discussion = models.ForeignKey(Discussion, on_delete=models.CASCADE)
	user = models.ForeignKey(User, on_delete=models.CASCADE)
	pub_date = models.DateTimeField('date published')
	res_text = models.TextField()
	vote_count = models.IntegerField(default=0)

3sql语句实现

BEGIN;
--
-- Create model AcceptedCode
--
CREATE TABLE "judgeOL_acceptedcode" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "accepted_code_text" text NOT NULL, "accepted_date" datetime NOT NULL, "time_cost" integer NOT NULL, "memory_cost" integer NOT NULL);
--
-- Create model Discussion
--
CREATE TABLE "judgeOL_discussion" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "pub_date" datetime NOT NULL, "submit_text" text NOT NULL, "vote_count" integer NOT NULL, "view_count" integer NOT NULL);
--
-- Create model Problem
--
CREATE TABLE "judgeOL_problem" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "subscribe" text NOT NULL, "test_case" varchar(200) NOT NULL, "test_result" varchar(200) NOT NULL, "submissions" integer NOT NULL, "acceptences" integer NOT NULL);
--
-- Create model Response
--
CREATE TABLE "judgeOL_response" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "pub_date" datetime NOT NULL, "res_text" text NOT NULL, "vote_count" integer NOT NULL, "discussion_id" integer NOT NULL REFERENCES "judgeOL_discussion" ("id"));
--
-- Create model SubmitCode
--
CREATE TABLE "judgeOL_submitcode" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "submit_code_text" text NOT NULL, "submit_date" datetime NOT NULL, "Condition" varchar(32) NOT NULL, "problem_id" integer NOT NULL REFERENCES "judgeOL_problem" ("id"));
--
-- Create model User
--
CREATE TABLE "judgeOL_user" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "mail" varchar(32) NOT NULL, "name" varchar(16) NOT NULL, "password" varchar(64) NOT NULL);
--
-- Add field user to submitcode
--
ALTER TABLE "judgeOL_submitcode" RENAME TO "judgeOL_submitcode__old";
CREATE TABLE "judgeOL_submitcode" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "submit_code_text" text NOT NULL, "submit_date" datetime NOT NULL, "Condition" varchar(32) NOT NULL, "problem_id" integer NOT NULL REFERENCES "judgeOL_problem" ("id"), "user_id" integer NOT NULL REFERENCES "judgeOL_user" ("id"));
INSERT INTO "judgeOL_submitcode" ("user_id", "submit_date", "problem_id", "submit_code_text", "id", "Condition") SELECT NULL, "submit_date", "problem_id", "submit_code_text", "id", "Condition" FROM "judgeOL_submitcode__old";
DROP TABLE "judgeOL_submitcode__old";
CREATE INDEX "judgeOL_response_discussion_id_18832592" ON "judgeOL_response" ("discussion_id");
CREATE INDEX "judgeOL_submitcode_problem_id_ed01a2c7" ON "judgeOL_submitcode" ("problem_id");
CREATE INDEX "judgeOL_submitcode_user_id_91b67ea4" ON "judgeOL_submitcode" ("user_id");
--
-- Add field user to response
--
ALTER TABLE "judgeOL_response" RENAME TO "judgeOL_response__old";
CREATE TABLE "judgeOL_response" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "pub_date" datetime NOT NULL, "res_text" text NOT NULL, "vote_count" integer NOT NULL, "discussion_id" integer NOT NULL REFERENCES "judgeOL_discussion" ("id"), "user_id" integer NOT NULL REFERENCES "judgeOL_user" ("id"));
INSERT INTO "judgeOL_response" ("user_id", "vote_count", "res_text", "discussion_id", "pub_date", "id") SELECT NULL, "vote_count", "res_text", "discussion_id", "pub_date", "id" FROM "judgeOL_response__old";
DROP TABLE "judgeOL_response__old";
CREATE INDEX "judgeOL_response_discussion_id_18832592" ON "judgeOL_response" ("discussion_id");
CREATE INDEX "judgeOL_response_user_id_fe421d26" ON "judgeOL_response" ("user_id");
--
-- Add field problem to discussion
--
ALTER TABLE "judgeOL_discussion" RENAME TO "judgeOL_discussion__old";
CREATE TABLE "judgeOL_discussion" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "pub_date" datetime NOT NULL, "submit_text" text NOT NULL, "vote_count" integer NOT NULL, "view_count" integer NOT NULL, "problem_id" integer NOT NULL REFERENCES "judgeOL_problem" ("id"));
INSERT INTO "judgeOL_discussion" ("submit_text", "view_count", "problem_id", "vote_count", "pub_date", "id") SELECT "submit_text", "view_count", NULL, "vote_count", "pub_date", "id" FROM "judgeOL_discussion__old";
DROP TABLE "judgeOL_discussion__old";
CREATE INDEX "judgeOL_discussion_problem_id_12907979" ON "judgeOL_discussion" ("problem_id");
--
-- Add field user to discussion
--
ALTER TABLE "judgeOL_discussion" RENAME TO "judgeOL_discussion__old";
CREATE TABLE "judgeOL_discussion" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "pub_date" datetime NOT NULL, "submit_text" text NOT NULL, "vote_count" integer NOT NULL, "view_count" integer NOT NULL, "problem_id" integer NOT NULL REFERENCES "judgeOL_problem" ("id"), "user_id" integer NOT NULL REFERENCES "judgeOL_user" ("id"));
INSERT INTO "judgeOL_discussion" ("submit_text", "user_id", "view_count", "problem_id", "vote_count", "pub_date", "id") SELECT "submit_text", NULL, "view_count", "problem_id", "vote_count", "pub_date", "id" FROM "judgeOL_discussion__old";
DROP TABLE "judgeOL_discussion__old";
CREATE INDEX "judgeOL_discussion_problem_id_12907979" ON "judgeOL_discussion" ("problem_id");
CREATE INDEX "judgeOL_discussion_user_id_8a55a23a" ON "judgeOL_discussion" ("user_id");
--
-- Add field problem to acceptedcode
--
ALTER TABLE "judgeOL_acceptedcode" RENAME TO "judgeOL_acceptedcode__old";
CREATE TABLE "judgeOL_acceptedcode" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "accepted_code_text" text NOT NULL, "accepted_date" datetime NOT NULL, "time_cost" integer NOT NULL, "memory_cost" integer NOT NULL, "problem_id" integer NOT NULL REFERENCES "judgeOL_problem" ("id"));
INSERT INTO "judgeOL_acceptedcode" ("time_cost", "id", "memory_cost", "accepted_code_text", "problem_id", "accepted_date") SELECT "time_cost", "id", "memory_cost", "accepted_code_text", NULL, "accepted_date" FROM "judgeOL_acceptedcode__old";
DROP TABLE "judgeOL_acceptedcode__old";
CREATE INDEX "judgeOL_acceptedcode_problem_id_dfea4df2" ON "judgeOL_acceptedcode" ("problem_id");
--
-- Add field user to acceptedcode
--
ALTER TABLE "judgeOL_acceptedcode" RENAME TO "judgeOL_acceptedcode__old";
CREATE TABLE "judgeOL_acceptedcode" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "accepted_code_text" text NOT NULL, "accepted_date" datetime NOT NULL, "time_cost" integer NOT NULL, "memory_cost" integer NOT NULL, "problem_id" integer NOT NULL REFERENCES "judgeOL_problem" ("id"), "user_id" integer NOT NULL REFERENCES "judgeOL_user" ("id"));
INSERT INTO "judgeOL_acceptedcode" ("user_id", "time_cost", "id", "memory_cost", "accepted_code_text", "problem_id", "accepted_date") SELECT NULL, "time_cost", "id", "memory_cost", "accepted_code_text", "problem_id", "accepted_date" FROM "judgeOL_acceptedcode__old";
DROP TABLE "judgeOL_acceptedcode__old";
CREATE INDEX "judgeOL_acceptedcode_problem_id_dfea4df2" ON "judgeOL_acceptedcode" ("problem_id");
CREATE INDEX "judgeOL_acceptedcode_user_id_b4b37543" ON "judgeOL_acceptedcode" ("user_id");
COMMIT;