Index: lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190722.sql =================================================================== diff -u -r81fcd69ba653630d8b86561657f00fdbe838d022 -r25339f55307608349defd1e9d666eac54809843f --- lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190722.sql (.../patch20190722.sql) (revision 81fcd69ba653630d8b86561657f00fdbe838d022) +++ lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190722.sql (.../patch20190722.sql) (revision 25339f55307608349defd1e9d666eac54809843f) @@ -9,7 +9,7 @@ `type` TINYINT NOT NULL, `question_id` INT NOT NULL, `version` SMALLINT NOT NULL DEFAULT 1, - `create_date` DATETIME NOT NULL DEFAULT NOW(), + `create_date` DATETIME, `content_folder_id` char(36), `name` TEXT, `description` MEDIUMTEXT, @@ -31,6 +31,7 @@ `min_words_limit` int(11) DEFAULT 0, `hedging_justification_enabled` TINYINT(1) DEFAULT 0, `tmp_question_id` BIGINT, + `owner_id` BIGINT, PRIMARY KEY (uid), INDEX (tmp_question_id), CONSTRAINT UQ_question_version UNIQUE INDEX (question_id, version)); @@ -153,7 +154,7 @@ ON q.question_uid = mcq.uid JOIN tl_lamc11_content AS c ON mcq.mc_content_id = c.uid; - + -- remove columns from MCQ which are duplicated in Question Bank ALTER TABLE tl_lamc11_que_content DROP COLUMN question, DROP COLUMN mark, @@ -162,13 +163,13 @@ -- add missing display order in options, if any UPDATE tl_lamc11_options_content AS c - JOIN (SELECT MIN(uid)-1 AS uid_shift, mc_que_content_id - FROM tl_lamc11_options_content - WHERE displayOrder IS NULL - GROUP BY mc_que_content_id) AS s - USING (mc_que_content_id) - SET c.displayOrder = c.uid - s.uid_shift - WHERE c.displayOrder IS NULL; + JOIN (SELECT MIN(uid)-1 AS uid_shift, mc_que_content_id + FROM tl_lamc11_options_content + WHERE displayOrder IS NULL + GROUP BY mc_que_content_id) AS s + USING (mc_que_content_id) + SET c.displayOrder = c.uid - s.uid_shift + WHERE c.displayOrder IS NULL; -- fill table with options matching unique QB questions inserted above INSERT INTO lams_qb_option (qb_question_uid, display_order, name, max_mark) @@ -540,7 +541,7 @@ TRIM(aq.description), IFNULL(aq.max_mark, 1), aq.feedback, aq.penalty_factor, aq.answer_required, aq.multiple_answers_allowed, aq.incorrect_answer_nullifies_mark, aq.feedback_on_correct, aq.feedback_on_partially_correct, aq.feedback_on_incorrect, aq.shuffle, aq.prefix_answers_with_letters, aq.case_sensitive, aq.correct_answer, - aq.allow_rich_editor, aq.max_words_limit, aq.min_words_limit, aq.hedging_justification_enabled, q.target_uid + aq.allow_rich_editor, aq.max_words_limit, aq.min_words_limit, aq.hedging_justification_enabled, q.target_uid, NULL FROM (SELECT uid, title AS question, question AS description, @@ -569,7 +570,7 @@ ON aq.uid = q.target_uid JOIN tl_laasse10_assessment AS assessment ON aq.assessment_uid = assessment.uid; - + -- set up references to QB question UIDs created above INSERT INTO lams_qb_tool_question SELECT q.question_uid, qb.uid, assess.content_id, aq.sequence_id @@ -750,7 +751,7 @@ INSERT INTO lams_qb_question (uid, `type`, question_id, version, create_date, name, description, max_mark, feedback, answer_required, min_words_limit, tmp_question_id) - SELECT NULL, 6, @question_id:=@question_id + 1, 1, IFNULL(c.creation_date, NOW()), + SELECT NULL, 6, @question_id:=@question_id + 1, 1, c.creation_date, SUBSTRING(TRIM(REPLACE(REPLACE(strip_tags(qa.question, false) COLLATE utf8mb4_0900_ai_ci, ' ', ' '), '\t', '')), 1, 200), qa.question, 1, qa.feedback, qa.answer_required, qa.min_words_limit, q.target_uid FROM (SELECT uid, @@ -809,8 +810,8 @@ -- clean up ALTER TABLE tl_laqa11_usr_resp DROP COLUMN qa_que_content_id, DROP COLUMN answer; - + ALTER TABLE lams_qb_question DROP COLUMN tmp_question_id; DROP TABLE tmp_question, tmp_question_match, @@ -821,4 +822,27 @@ -- add a table for generating questionId in QbQuestion and possible other sequences in the future CREATE TABLE lams_sequence_generator (lams_qb_question_question_id INT); CREATE UNIQUE INDEX IDX_lams_qb_question_question_id ON lams_sequence_generator(lams_qb_question_question_id); -INSERT INTO lams_sequence_generator(lams_qb_question_question_id) VALUES ((SELECT MAX(question_id) FROM lams_qb_question)); \ No newline at end of file +INSERT INTO lams_sequence_generator(lams_qb_question_question_id) VALUES ((SELECT MAX(question_id) FROM lams_qb_question)); + + +-- find earlierst occurence of questions and fill create date and owner +UPDATE lams_qb_question AS qb, + (SELECT qt.qb_question_uid, + d.user_id AS owner_id, + MIN(a.create_date_time) AS create_date + FROM lams_qb_tool_question AS qt JOIN + lams_learning_activity AS a USING (tool_content_id) JOIN + lams_learning_design AS d USING (learning_design_id) + GROUP BY qb_question_uid + ) AS s +SET qb.create_date = s.create_date, + qb.owner_id = s.owner_id +WHERE qb.uid = s.qb_question_uid; + +-- fill missing gaps +UPDATE lams_qb_question + SET create_date = NOW() + WHERE create_date IS NULL; + +-- fortify date column so there are no NULLs +ALTER TABLE lams_qb_question MODIFY COLUMN create_date DATETIME NOT NULL DEFAULT NOW(); \ No newline at end of file Index: lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190724.sql =================================================================== diff -u -r89279cb44b252167269043889b3c3c0a4164e0bb -r25339f55307608349defd1e9d666eac54809843f --- lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190724.sql (.../patch20190724.sql) (revision 89279cb44b252167269043889b3c3c0a4164e0bb) +++ lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190724.sql (.../patch20190724.sql) (revision 25339f55307608349defd1e9d666eac54809843f) @@ -29,18 +29,35 @@ CONSTRAINT FK_lams_qb_collection_share_2 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE ); - + +-- add questions to public collection INSERT INTO lams_qb_collection VALUES (1, 'Public questions', NULL, false); INSERT INTO lams_qb_collection_question - SELECT 1, question_id FROM lams_qb_question; + SELECT 1, question_id FROM lams_qb_question + WHERE owner_id IS NULL; +-- add questions to private collections +INSERT INTO lams_qb_collection + SELECT NULL, 'My questions', owner_id, true + FROM (SELECT DISTINCT owner_id FROM lams_qb_question WHERE owner_id IS NOT NULL) AS qb; + +INSERT INTO lams_qb_collection_question + SELECT c.uid, qb.question_id + FROM lams_qb_question AS qb + JOIN lams_qb_collection AS c ON qb.owner_id = c.user_id + WHERE qb.owner_id IS NOT NULL; + +ALTER TABLE lams_qb_question DROP COLUMN owner_id; + + INSERT INTO lams_configuration VALUES ('QbCollectionsTransferEnable', 'true', 'config.qb.collections.transfer.enable', 'config.header.qb', 'BOOLEAN', 1); - + + -- Put all sql statements above here -- 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;