DELIMITER $$ create trigger insertmap AFTER insert on challengegroup for each row BEGIN insert into user_challengegroup_map(user_challengegroup_map.challengegroupid, user_challengegroup_map.user) values (NEW.challengegroupid, NEW.owner); END; $$
DELIMITER $$
CREATE FUNCTION addQuestion
(
ques nvarchar(2000), op1 nvarchar(1000), op2 nvarchar(1000), op3 nvarchar(1000), op4 nvarchar(1000), lvl int, ans int, grp int, usr varchar(45))
RETURNS INTEGER
BEGIN
#insert question in table
INSERT into quiz_question
(question, option1, option2, option3, option4, answer, level)
values
(ques, op1, op2, op3, op4, ans, lvl);
#insert mapping
insert into question_challengegrp_map(ChallengeGrpId, QuestionId, Userid)
values
(grp, LAST_INSERT_ID() ,usr);
RETURN 1;
END $$
#addCodeQuestion sp
CREATE DEFINER=root
@localhost
PROCEDURE addCodeQuestion
(ques nvarchar(2000), detail nvarchar(1000), lvl int, tlimit float, mlimit int, test nvarchar(1000), grp int, usr varchar(45))
BEGIN
INSERT into code_question
(question, details, level, test_case, timelimit, memorylimit)
values
(ques, detail, lvl,test);
#insert mapping
insert into question_challengegrp_map(ChallengeGrpId, QuestionId, Userid)
values
(grp, LAST_INSERT_ID() ,usr);
END
#round_submission table
CREATE TABLE round_submission
(
id
int(11) NOT NULL AUTO_INCREMENT,
round_id
int(11) NOT NULL,
username
varchar(45) NOT NULL,
score
int(11) NOT NULL DEFAULT '0',
contest
varchar(45) NOT NULL,
PRIMARY KEY (id
),
KEY round_id_idx
(round_id
),
KEY username_idx
(username
),
CONSTRAINT round_id
FOREIGN KEY (round_id
) REFERENCES round
(Roundid
) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
#code_submission table
CREATE TABLE code_submission
(
user
varchar(50) NOT NULL DEFAULT 'none',
max_exe_time
double DEFAULT NULL,
roundid
int(11) NOT NULL DEFAULT '1',
score
double DEFAULT NULL,
code
longtext,
languageid
int(11) DEFAULT NULL,
questionid
int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (questionid
,roundid
,user
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;