Index: lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20130812.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20130812.sql,v diff -u -r1.3 -r1.3.2.1 --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20130812.sql 30 Sep 2013 20:46:17 -0000 1.3 +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20130812.sql 12 Oct 2014 18:24:03 -0000 1.3.2.1 @@ -4,7 +4,7 @@ ----------------------Put all sql statements below here------------------------- --LDEV-3085 Autosave feature for MCQ -CREATE TABLE temp_select AS SELECT attempt.uid +CREATE TEMPORARY TABLE temp_select AS SELECT attempt.uid FROM tl_lamc11_usr_attempt attempt INNER JOIN( SELECT que_usr_id, mc_que_content_id, max(attemptOrder) attemptOrder @@ -13,7 +13,7 @@ ) ss ON attempt.que_usr_id = ss.que_usr_id AND attempt.mc_que_content_id = ss.mc_que_content_id AND attempt.attemptOrder = ss.attemptOrder; ALTER TABLE temp_select ADD INDEX index1 (uid ASC); DELETE FROM tl_lamc11_usr_attempt WHERE uid NOT IN (SELECT uid FROM temp_select); -DROP TABLE temp_select; +DROP TEMPORARY TABLE temp_select; ALTER TABLE tl_lamc11_usr_attempt DROP COLUMN attemptOrder; ALTER TABLE tl_lamc11_que_usr DROP COLUMN last_attempt_order; Index: lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20131021.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20131021.sql,v diff -u -r1.1 -r1.1.2.1 --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20131021.sql 21 Oct 2013 19:09:51 -0000 1.1 +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20131021.sql 12 Oct 2014 18:24:03 -0000 1.1.2.1 @@ -4,11 +4,11 @@ ----------------------Put all sql statements below here------------------------- --LDEV-3138 Prevent erroneously creating 2 identical user results for 1 question in DB -CREATE TABLE temp_select AS SELECT MAX(uid) uid +CREATE TEMPORARY TABLE temp_select AS SELECT MAX(uid) uid FROM tl_lamc11_usr_attempt GROUP BY que_usr_id, mc_que_content_id; ALTER TABLE temp_select ADD INDEX index1 (uid ASC); DELETE FROM tl_lamc11_usr_attempt WHERE uid NOT IN (SELECT uid FROM temp_select); -DROP TABLE temp_select; +DROP TEMPORARY TABLE temp_select; ALTER TABLE tl_lamc11_usr_attempt ADD UNIQUE INDEX attempt_unique_index (que_usr_id, mc_que_content_id); 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.2 -r1.2.2.1 --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20140101.sql 2 Apr 2014 12:29:21 -0000 1.2 +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20140101.sql 12 Oct 2014 18:24:03 -0000 1.2.2.1 @@ -13,12 +13,11 @@ -- 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; +CREATE TEMPORARY 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; +DROP TEMPORARY TABLE temp_select; ALTER TABLE tl_lamc11_que_usr ADD UNIQUE INDEX(que_usr_id, mc_session_id); @@ -27,4 +26,4 @@ -- If there were no errors, commit and restore autocommit to on COMMIT; SET AUTOCOMMIT = 1; -SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file +SET FOREIGN_KEY_CHECKS=1; 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.2 -r1.2.2.1 --- lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20140101.sql 2 Apr 2014 12:29:25 -0000 1.2 +++ lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20140101.sql 12 Oct 2014 18:23:59 -0000 1.2.2.1 @@ -10,12 +10,11 @@ -- 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; +CREATE TEMPORARY 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; +DROP TEMPORARY TABLE temp_select; ALTER TABLE tl_laqa11_que_usr ADD UNIQUE INDEX(que_usr_id, qa_session_id); @@ -24,4 +23,4 @@ -- If there were no errors, commit and restore autocommit to on COMMIT; SET AUTOCOMMIT = 1; -SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file +SET FOREIGN_KEY_CHECKS=1; Index: lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20131212.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20131212.sql,v diff -u -r1.4 -r1.4.2.1 --- lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20131212.sql 24 Dec 2013 10:20:05 -0000 1.4 +++ lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20131212.sql 12 Oct 2014 18:24:04 -0000 1.4.2.1 @@ -6,11 +6,10 @@ -- LKC-40 -- Remove logs belong to all other users except leader DROP TABLE IF EXISTS temp_select; -CREATE TABLE temp_select AS SELECT group_leader_uid uid - FROM tl_lascrt11_session WHERE group_leader_uid IS NOT NULL; +CREATE TEMPORARY TABLE temp_select AS SELECT group_leader_uid uid FROM tl_lascrt11_session WHERE group_leader_uid IS NOT NULL; ALTER TABLE temp_select ADD INDEX index1 (uid ASC); DELETE FROM tl_lascrt11_answer_log WHERE user_uid NOT IN (SELECT uid FROM temp_select); -DROP TABLE temp_select; +DROP TEMPORARY TABLE temp_select; --Make ScratchieAnswerVisitLog belong to session and not user, thus being shared by all users ALTER TABLE tl_lascrt11_answer_log DROP FOREIGN KEY FK_NEW_610529188_693580A441F9365D; @@ -47,4 +46,4 @@ -- If there were no errors, commit and restore autocommit to on COMMIT; SET AUTOCOMMIT = 1; -SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file +SET FOREIGN_KEY_CHECKS=1; 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.2 -r1.2.2.1 --- lams_tool_vote/src/java/org/lamsfoundation/lams/tool/vote/dbupdates/patch20131227.sql 2 Apr 2014 12:29:23 -0000 1.2 +++ lams_tool_vote/src/java/org/lamsfoundation/lams/tool/vote/dbupdates/patch20131227.sql 12 Oct 2014 18:24:01 -0000 1.2.2.1 @@ -10,12 +10,11 @@ -- 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; +CREATE TEMPORARY 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; +DROP TEMPORARY TABLE temp_select; ALTER TABLE tl_lavote11_usr ADD UNIQUE INDEX(user_id, vote_session_id); @@ -24,4 +23,4 @@ -- If there were no errors, commit and restore autocommit to on COMMIT; SET AUTOCOMMIT = 1; -SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file +SET FOREIGN_KEY_CHECKS=1;