SQL设计.txt



//10-03
ALTER TABLE `contest`
ADD COLUMN `hideRankMinute`  int(11) NOT NULL AFTER `registerShowComplete`,
ADD COLUMN `isHideOthersStatus`  bit(1) NOT NULL AFTER `hideRankMinute`,
ADD COLUMN `isHideOthersStatusInfo`  bit(1) NOT NULL AFTER `isHideOthersStatus`;



CREATE TABLE `t_group_member` (
`group_id`  int(11) NOT NULL ,
`username`  varchar(30) NOT NULL ,
`status`  int(11) NOT NULL ,
`join_time`  datetime NOT NULL ,
PRIMARY KEY (`group_id`, `username`)
)
;


CREATE TABLE `t_group` (
`id`  int(11) NOT NULL AUTO_INCREMENT FIRST,
`name`  varchar(30) NOT NULL ,
`type`  int(11) NOT NULL ,
`time`  datetime NOT NULL ,
PRIMARY KEY (`id`)
);





ALTER TABLE t_game_repetition ADD COLUMN `time` DATETIME DEFAULT NULL AFTER `win`;

ALTER TABLE t_ai_info ADD COLUMN isHide INT DEFAULT 0;

CREATE TABLE t_game_repetition
(
id INT(11) AUTO_INCREMENT,
blackId INT(11) NOT NULL,
blackAuthor VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
whiteId INT(11) NOT NULL,
whiteAuthor VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
processes TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
win VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE t_ai_info
(
id INT(11) AUTO_INCREMENT,
username VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
game_id INT(11) NOT NULL,
ai_name VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
ai_code TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
introduce VARCHAR(300) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE `t_vote` (
`did`  int(11) NOT NULL ,
`id`  int(11) NOT NULL ,
`isHide`  int(11) NOT NULL ,
`isDisable`  int(11) NOT NULL ,
`des`  varchar(300) NOT NULL ,
PRIMARY KEY (`did`, `id`)
);

ALTER TABLE `t_viewcode`
ADD COLUMN `type` int(11) NOT NULL DEFAULT 0 AFTER `pid`;

PRIMARY KEY (`did`, `id`, `type`)



CREATE TABLE `t_some_opt_record` (
`username`  varchar(30) NOT NULL ,
`time`  timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP ,
`type`  int(11) NOT NULL ,
`id`  int(11) NOT NULL ,
PRIMARY KEY (`username`, `time`)
);
ALTER TABLE `t_some_opt_record`
ADD COLUMN `data`  varchar(100) NOT NULL AFTER `id`;

部分题目创建权限
ALTER TABLE `problem`
ADD COLUMN `owner`  varchar(30) NOT NULL AFTER `totalAcUser`;


//比赛kind属性分离
ALTER TABLE `contest`
ADD COLUMN `problemCanPutTag`  bit NOT NULL AFTER `kind`,
ADD COLUMN `statusReadOut`  bit NOT NULL AFTER `problemCanPutTag`,
ADD COLUMN `registerShowComplete`  bit NOT NULL AFTER `statusReadOut`;

update contest set problemCanPutTag=1 where kind = 0;
update contest set statusReadOut=1 where kind = 0;
update contest set registerShowComplete=1 where kind = 3;


//收藏
id
username
type   1题目收藏 2代码收藏
startid
txt    备注

CREATE TABLE  `vjudge`.`t_star` (
`id` INT NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`type` INT( 11 ) NOT NULL ,
`star_id` INT( 11 ) NOT NULL ,
`text` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
PRIMARY KEY (  `id` )
) ENGINE = INNODB;
ALTER TABLE  `t_star` ADD INDEX  `username` (  `username` );

//ACB账单

CREATE TABLE  `vjudge`.`t_acborder` (
`id` INT NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`change` INT NOT NULL ,
`reason` INT NOT NULL ,
`mark` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
PRIMARY KEY (  `id` )
) ENGINE = INNODB;
ALTER TABLE  `t_acborder` CHANGE  `change`  `acbchange` INT( 11 ) NOT NULL;
ALTER TABLE  `t_acborder` ADD  `time` DATETIME NOT NULL;

//下次更新内容
CREATE TABLE  `vjudge`.`t_verify` (
`id` INT NOT NULL ,
`VerifyType` INT NOT NULL ,
`username` VARCHAR( 30 ) NOT NULL ,
`name` VARCHAR( 30 ) NOT NULL ,
`school` VARCHAR( 50 ) NOT NULL ,
`gender` INT NOT NULL ,
`faculty` VARCHAR( 30 ) NOT NULL ,
`major` VARCHAR( 30 ) NOT NULL ,
`cla` VARCHAR( 30 ) NOT NULL ,
`no` VARCHAR( 30 ) NOT NULL ,
`phone` VARCHAR( 30 ) NOT NULL ,
`email` VARCHAR( 50 ) NOT NULL ,
`time` DATETIME NOT NULL,
`result` INT NOT NULL,
PRIMARY KEY (  `id` )
) ENGINE = INNODB;
ALTER TABLE  `t_verify` CHANGE  `id`  `id` INT( 11 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE  `t_verify` ADD  `reason` VARCHAR( 100 ) NOT NULL ;
ALTER TABLE  `users` ADD  `graduationTime` DATETIME NULL ;
ALTER TABLE  `t_verify` ADD  `graduationTime` DATETIME NOT NULL ;


ALTER TABLE  `users` ADD  `rank` INT NOT NULL DEFAULT  '0';
UPDATE users SET rank=(select rank+1 FROM v_user WHERE v_user.username=users.username)

////
drop view v_solved;
CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_solved` AS select `t_usersolve`.`username` AS `username`,sum(`t_usersolve`.`status`) AS `acnum` from `t_usersolve` group by `t_usersolve`.`username`;

replace into  t_usersolve select * from usersolve_view


ALTER TABLE  `statu` ADD  `score` INT NOT NULL DEFAULT  '0' AFTER result;

表结构：
    statu：
        id	        int(11)
    	ruser	    varchar(20)
    	pid	        int(11)
    	cid	        int(11)
    	lang	    int(11)
    	submitTime	datetime
    	result	    int(11)
    	timeUsed	varchar(10)
    	memoryUsed	varchar(10)
    	code	    text
    users:
    	username	varchar(30)
    	password	varchar(32)
    	nick	    varchar(50)
    	gender	    int(11)
    	school	    varchar(50)
    	Email	    varchar(50)
    	motto	    varchar(100)
    	registertime datetime
    	type	    int(11)
    	solved	    int(11)		//
    	submissions	int(11)		//
    	Mark	    varchar(100)
    contestproblems:
    	cid			int(11)
    	pid			int(11)
    	tpid		int(11)
？？？？
功能：查询contest内的题目列表(按顺序) 以及每题的ac人数和提交人数

	SELECT t.pid as pid,problem.pid as tpid,problem.title
	FROM (SELECT pid,tpid FROM contestproblems WHERE cid=[cid] order by pid)t,problem
	WHERE t.tpid=problem.pid
	ORDER BY t.pid

功能：查询contest内的题目列表及radio
	SELECT tt.pid,title,count(username),acuser
    FROM contestusersolve_view
    RIGHT JOIN
        (SELECT t4.pid as pid,t4.title,count(username) acuser
         FROM
            (SELECT pid,username
             FROM contestusersolve_view
             WHERE cid=? AND solved=1
            )t1
         RIGHT JOIN
            (SELECT t.pid as pid,problem.title as title
             FROM
                (SELECT pid,tpid
                 FROM contestproblems
                 WHERE cid=? order by pid
                )t,
                problem
             WHERE t.tpid=problem.pid
             ORDER BY t.pid
            )t4
         ON t1.pid=t4.pid
         GROUP BY t4.pid
        )tt
    ON tt.pid=contestusersolve_view.pid and contestusersolve_view.cid=?
    GROUP BY contestusersolve_view .pid

视图：usersolve_view：
    CREATE ALGORITHM = TEMPTABLE VIEW usersolve_view(
        username,
        pid,
        solved
    ) AS
    SELECT ruser, pid, MAX( result =1 )
    FROM statu
    WHERE cid = -1
    GROUP BY ruser, pid

功能：查询用户AC题目列表（不包括contest内的）
    SELECT pid FROM usersolve_view WHERE solved=1 AND username=[user]
功能：查询用户提交了但没AC的题目列表（不包括contest内的）
    SELECT pid FROM usersolve_view WHERE solved=0 AND username=[user]
功能：查询AC了pid题目的用户列表
    SELECT username FROM usersolve_view WHERE solved=1 AND pid=[pid]
功能：查询提交了pid题目的用户列表
    SELECT username FROM usersolve_view WHERE pid=[pid]
功能：查询提交了pid题目的用户数量
    SELECT COUNT(username) FROM usersolve_view WHERE pid=[pid]
功能：查询AC了pid题目的用户数量
    SELECT COUNT(username) FROM usersolve_view WHERE pid=[pid] AND solved=1



视图：contestusersolve_view：
    CREATE ALGORITHM = TEMPTABLE VIEW contestusersolve_view(
    	cid,
        pid,
        username,
        solved
    ) AS
    SELECT cid, pid, ruser, MAX( result =1 )
    FROM statu
    WHERE cid <> -1
    GROUP BY ruser, pid, cid