Index: lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20140101.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20140101.sql,v diff -u -r1.1 -r1.2 --- lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20140101.sql 8 Jan 2014 17:47:02 -0000 1.1 +++ lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20140101.sql 2 Apr 2014 12:29:22 -0000 1.2 @@ -8,7 +8,8 @@ ALTER TABLE tl_laasse10_session ADD COLUMN group_leader_uid BIGINT; ALTER TABLE tl_laasse10_session ADD INDEX FK_laasse10_session_1 (group_leader_uid), ADD CONSTRAINT tl_laasse10_session FOREIGN KEY (group_leader_uid) REFERENCES tl_laasse10_user (uid) ON DELETE CASCADE ON UPDATE CASCADE; --- adding a constraint to the assessment user table so no same user_id and session_id can be repetead +-- Adding a constraint to the assessment user table so no same user_id and session_id can be repetead +-- (Please, note there are some duplicate users exist (their session_uid is null), they got formed when techers created questions in authoring. It's required to keep track of questions' authors.) ALTER TABLE tl_laasse10_user ADD UNIQUE INDEX(user_id, session_uid); ----------------------Put all sql statements above here------------------------- Index: lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20140101.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20140101.sql,v diff -u -r1.1 -r1.2 --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20140101.sql 8 Jan 2014 17:49:42 -0000 1.1 +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20140101.sql 2 Apr 2014 12:29:21 -0000 1.2 @@ -11,7 +11,15 @@ -- Put automatic letters in front of MCQ answers ALTER TABLE tl_lamc11_content ADD COLUMN prefix_answers_with_letters TINYINT(1) NOT NULL DEFAULT 1; --- Adding a constraint to the MCQ user table so no same user_id and session_id can be repetead +-- Adding a constraint to the MCQ user table so no same user_id and session_id can be repetead. +-- In order to achieve this remove duplicate users and according attempts. +CREATE TABLE temp_select AS SELECT MAX(uid) uid +FROM tl_lamc11_que_usr GROUP BY que_usr_id, mc_session_id; +ALTER TABLE temp_select ADD INDEX index1 (uid ASC); +DELETE FROM tl_lamc11_usr_attempt WHERE que_usr_id NOT IN (SELECT uid FROM temp_select); +DELETE FROM tl_lamc11_que_usr WHERE uid NOT IN (SELECT uid FROM temp_select); +DROP TABLE temp_select; + ALTER TABLE tl_lamc11_que_usr ADD UNIQUE INDEX(que_usr_id, mc_session_id); ----------------------Put all sql statements above here------------------------- Index: lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20140101.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20140101.sql,v diff -u -r1.1 -r1.2 --- lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20140101.sql 8 Jan 2014 17:50:56 -0000 1.1 +++ lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20140101.sql 2 Apr 2014 12:29:25 -0000 1.2 @@ -8,7 +8,15 @@ ALTER TABLE tl_laqa11_session ADD COLUMN qa_group_leader_uid BIGINT; ALTER TABLE tl_laqa11_session ADD INDEX FK_laqa11_session1 (qa_group_leader_uid), ADD CONSTRAINT FK_laqa11_session1 FOREIGN KEY (qa_group_leader_uid) REFERENCES tl_laqa11_que_usr (uid) ON DELETE CASCADE ON UPDATE CASCADE; --- adding a constraint to the QA user table so no same user_id and session_id can be repetead +-- Adding a constraint to the QA user table so no same que_usr_id and qa_session_id can be repetead +-- In order to achieve this remove duplicate users and according responses. +CREATE TABLE temp_select AS SELECT MAX(uid) uid +FROM tl_laqa11_que_usr GROUP BY que_usr_id, qa_session_id; +ALTER TABLE temp_select ADD INDEX index1 (uid ASC); +DELETE FROM tl_laqa11_usr_resp WHERE que_usr_id NOT IN (SELECT uid FROM temp_select); +DELETE FROM tl_laqa11_que_usr WHERE uid NOT IN (SELECT uid FROM temp_select); +DROP TABLE temp_select; + ALTER TABLE tl_laqa11_que_usr ADD UNIQUE INDEX(que_usr_id, qa_session_id); ----------------------Put all sql statements above here------------------------- Index: lams_tool_vote/src/java/org/lamsfoundation/lams/tool/vote/dbupdates/patch20131227.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_vote/src/java/org/lamsfoundation/lams/tool/vote/dbupdates/patch20131227.sql,v diff -u -r1.1 -r1.2 --- lams_tool_vote/src/java/org/lamsfoundation/lams/tool/vote/dbupdates/patch20131227.sql 2 Jan 2014 23:12:51 -0000 1.1 +++ lams_tool_vote/src/java/org/lamsfoundation/lams/tool/vote/dbupdates/patch20131227.sql 2 Apr 2014 12:29:23 -0000 1.2 @@ -8,7 +8,15 @@ ALTER TABLE tl_lavote11_session ADD COLUMN group_leader_uid BIGINT; ALTER TABLE tl_lavote11_session ADD INDEX FK_lavote11_session_1 (group_leader_uid), ADD CONSTRAINT tl_lavote11_session FOREIGN KEY (group_leader_uid) REFERENCES tl_lavote11_usr (uid) ON DELETE CASCADE ON UPDATE CASCADE; --- adding a constraint to the vote user table so no same user_id and vote_session_id can be repetead +-- Adding a constraint to the vote user table so no same user_id and vote_session_id can be repetead +-- In order to achieve this remove duplicate users and according attempts. +CREATE TABLE temp_select AS SELECT MAX(uid) uid +FROM tl_lavote11_usr GROUP BY user_id, vote_session_id; +ALTER TABLE temp_select ADD INDEX index1 (uid ASC); +DELETE FROM tl_lavote11_usr_attempt WHERE que_usr_id NOT IN (SELECT uid FROM temp_select); +DELETE FROM tl_lavote11_usr WHERE uid NOT IN (SELECT uid FROM temp_select); +DROP TABLE temp_select; + ALTER TABLE tl_lavote11_usr ADD UNIQUE INDEX(user_id, vote_session_id); ----------------------Put all sql statements above here-------------------------