基于信号处理的在线云评测+社区系统（ 2）

2017年5月-6月，本小组做山东大学软件工程专业项目实训。我们做的项目是“基于信号处理的在线云评测+社区系统”，我和另一位组员@zhan010g负责数据库后台方面。

系统需要的表有用户表，问题表，提交代码表，采纳代码表，评论表和回复表。每个表有主码id和其他属性。

下面是我建表的SQL语句：

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;