Index: lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190110.sql =================================================================== diff -u -r6de3d240b76c900f96a93ab784838f2153fbe7c5 -r7cc242376863992495429d37f502de2a51e7a887 --- lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190110.sql (.../patch20190110.sql) (revision 6de3d240b76c900f96a93ab784838f2153fbe7c5) +++ lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190110.sql (.../patch20190110.sql) (revision 7cc242376863992495429d37f502de2a51e7a887) @@ -54,13 +54,17 @@ -- so the safest solution is to place it here -- default value for a concat result is 1024 characters, which can be too little for multiple concatenated answers --- 16000 is chosen because varchar can have maximum length of about 16000 characters on utf8mb4 -SET group_concat_max_len = 16000; +-- we choose a value big enough to accept anything +SET group_concat_max_len = 100000; + +-- MULTIPLE CHOICE (MCQ) + + -- create a mapping of MCQ question UID -> its question text + all answers in a single column -- if this column is not *exactly* as in an other row, it means it should be a separate question in QB CREATE TABLE tmp_question (question_uid BIGINT PRIMARY KEY, - content VARCHAR(16000)) + content TEXT) AS SELECT q.uid AS question_uid, GROUP_CONCAT(TRIM(question), TRIM(mc_que_option_text) ORDER BY displayOrder) AS content FROM tl_lamc11_que_content AS q @@ -83,7 +87,7 @@ -- fill Question Bank question table with unique questions, with manually incremented question ID SET @question_id = (SELECT IF(MAX(question_id) IS NULL, 0, MAX(question_id)) FROM lams_qb_question); -INSERT INTO lams_qb_question SELECT NULL, 1, 1, @question_id:=@question_id + 1, 1, c.creation_date, +INSERT INTO lams_qb_question SELECT NULL, 1, 1, @question_id:=@question_id + 1, 1, IFNULL(c.creation_date, NOW()), mcq.question, NULL, mcq.mark, mcq.feedback, q.target_uid FROM (SELECT uid, TRIM(question) AS question, @@ -117,9 +121,13 @@ INSERT INTO lams_qb_option (qb_question_uid, display_order, name, correct) SELECT q.uid, o.displayOrder, TRIM(o.mc_que_option_text), o.correct_option FROM tl_lamc11_options_content AS o - JOIN lams_qb_question AS q ON o.mc_que_content_id = q.tmp_question_id - ORDER BY o.displayOrder; + JOIN lams_qb_question AS q + ON o.mc_que_content_id = q.tmp_question_id + ORDER BY o.mc_que_content_id, o.displayOrder; +ALTER TABLE tl_lamc11_usr_attempt DROP FOREIGN KEY FK_tl_lamc11_usr_attempt_2, + DROP FOREIGN KEY FK_tl_lamc11_usr_attempt_3; + -- rewrite references from MCQ options to QB options UPDATE tl_lamc11_usr_attempt AS ua, tl_lamc11_options_content AS mco, lams_qb_tool_question AS tq, lams_qb_option AS qo SET ua.mc_que_option_id = qo.uid @@ -133,17 +141,20 @@ SELECT uid, mc_que_content_id, mc_que_option_id FROM tl_lamc11_usr_attempt; -- clean up -ALTER TABLE tl_lamc11_usr_attempt DROP FOREIGN KEY FK_tl_lamc11_usr_attempt_2, - DROP FOREIGN KEY FK_tl_lamc11_usr_attempt_3, +ALTER TABLE tl_lamc11_usr_attempt DROP INDEX attempt_unique_index, DROP COLUMN mc_que_content_id, DROP COLUMN mc_que_option_id; DROP TABLE tl_lamc11_options_content; --- prepare for Scratchie migration +-- prepare for next tool migration DELETE FROM tmp_question; DELETE FROM tmp_question_match; + +-- SCRATCHIE + + -- shift Scratchie question UIDs by offset equal to existing UIDs of MCQ in lams_qb_tool_question SET @max_tool_question_id = (SELECT MAX(tool_question_uid) FROM lams_qb_tool_question); UPDATE tl_lascrt11_scratchie_item SET uid = uid + @max_tool_question_id ORDER BY uid DESC; @@ -223,14 +234,30 @@ DROP COLUMN create_by_author, DROP COLUMN session_uid, DROP COLUMN order_id; + +-- some Scratchie options can be ordered from 0, some from 1 +-- shift ones ordered from 0 by +1 to match the other group +CREATE TABLE tmp_scratchie_answer +SELECT scratchie_item_uid FROM tl_lascrt11_scratchie_answer WHERE order_id = 0 AND scratchie_item_uid IS NOT NULL; + +ALTER TABLE tmp_scratchie_answer ADD PRIMARY KEY (scratchie_item_uid); +UPDATE tl_lascrt11_scratchie_answer AS sa +SET order_id = order_id + 1 +WHERE EXISTS + (SELECT 1 FROM tmp_scratchie_answer WHERE scratchie_item_uid = sa.scratchie_item_uid); + +DROP TABLE tmp_scratchie_answer; + + -- fill table with options matching unique QB questions inserted above INSERT INTO lams_qb_option (qb_question_uid, display_order, name, correct) - SELECT q.uid, o.order_id + 1, TRIM(o.description), o.correct + SELECT q.uid, o.order_id, TRIM(o.description), o.correct FROM tl_lascrt11_scratchie_answer AS o JOIN lams_qb_question AS q ON o.scratchie_item_uid = q.tmp_question_id - ORDER BY o.order_id; + WHERE o.scratchie_item_uid IS NOT NULL + ORDER BY o.scratchie_item_uid, o.order_id; ALTER TABLE tl_lascrt11_answer_log ADD COLUMN scratchie_item_uid BIGINT; @@ -239,14 +266,18 @@ SET @max_answer_uid = (SELECT MAX(answer_uid) FROM lams_qb_tool_answer); UPDATE tl_lascrt11_answer_log SET uid = uid + @max_answer_uid ORDER BY uid DESC; +ALTER TABLE tl_lascrt11_answer_log DROP FOREIGN KEY FK_NEW_610529188_693580A438BF8DFE, + DROP KEY FK_NEW_lascrt11_30113BFC309ED321; + -- rewrite references from Scratchie options to QB options UPDATE tl_lascrt11_answer_log AS sl, tl_lascrt11_scratchie_answer AS sa, lams_qb_tool_question AS tq, lams_qb_option AS qo SET sl.scratchie_answer_uid = qo.uid, sl.scratchie_item_uid = tq.tool_question_uid - WHERE sa.order_id + 1 = qo.display_order + WHERE sa.order_id = qo.display_order AND sl.scratchie_answer_uid = sa.uid AND qo.qb_question_uid = tq.qb_question_uid - AND sa.scratchie_item_uid = tq.tool_question_uid; + AND sa.scratchie_item_uid = tq.tool_question_uid + AND sa.scratchie_item_uid IS NOT NULL; -- prepare for answer inheritance INSERT INTO lams_qb_tool_answer @@ -255,9 +286,7 @@ -- cleanup ALTER TABLE lams_qb_question DROP COLUMN tmp_question_id; -ALTER TABLE tl_lascrt11_answer_log DROP FOREIGN KEY FK_NEW_610529188_693580A438BF8DFE, - DROP KEY FK_NEW_lascrt11_30113BFC309ED321, - DROP COLUMN scratchie_item_uid, +ALTER TABLE tl_lascrt11_answer_log DROP COLUMN scratchie_item_uid, DROP COLUMN scratchie_answer_uid; DROP TABLE tl_lascrt11_scratchie_answer,