Index: lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190110.sql =================================================================== diff -u -r851d7676f2e9e74ac0f9346dbb47ee8ed4f4c9e4 -r832ea8c2f95a9ae49aa28fea549d1759dbeb27ef --- lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190110.sql (.../patch20190110.sql) (revision 851d7676f2e9e74ac0f9346dbb47ee8ed4f4c9e4) +++ lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190110.sql (.../patch20190110.sql) (revision 832ea8c2f95a9ae49aa28fea549d1759dbeb27ef) @@ -3,7 +3,9 @@ SET FOREIGN_KEY_CHECKS=0; ----------------------Put all sql statements below here------------------------- --- LDEV-4746 Create a Question Bank question structure +-- LDEV-4746 Create Question Bank table structure + +-- Create QB question table CREATE TABLE lams_qb_question (`uid` BIGINT AUTO_INCREMENT, `local` TINYINT(1) NOT NULL DEFAULT 1, `type` TINYINT NOT NULL, @@ -13,7 +15,14 @@ `mark` INT, `feedback` TEXT, PRIMARY KEY (uid), - CONSTRAINT UQ_question_version UNIQUE KEY (question_id, version)); + CONSTRAINT UQ_question_version UNIQUE INDEX (question_id, version), + INDEX (`local`)); + +-- Create a question table from which tools' questions will inherit +CREATE TABLE lams_qb_tool_question (`tool_question_uid` BIGINT AUTO_INCREMENT, + `qb_question_uid` BIGINT NOT NULL, + PRIMARY KEY (tool_question_uid), + CONSTRAINT FK_lams_qb_tool_question_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON UPDATE CASCADE); -- Convert MCQ question into Question Bank question -- This part of patch should be in MCQ, but it must be run after lams_qb_question is created and it must not run in parallel with other tools' patches, @@ -25,17 +34,14 @@ INSERT INTO lams_qb_question SELECT NULL, 1, 1, @question_id:=@question_id + 1, 1, question, mark, feedback FROM (SELECT DISTINCT TRIM(question) AS question, mark, IF(TRIM(feedback) = '', NULL, TRIM(feedback)) AS feedback FROM tl_lamc11_que_content) AS mcq; --- prepare MCQ's question table for referencing Question Bank's question -ALTER TABLE tl_lamc11_que_content ADD COLUMN qb_question_uid BIGINT AFTER uid, - ADD CONSTRAINT FK_tl_lamc11_que_content_2 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON UPDATE CASCADE; - -- find matching questions in Question Bank and set up references -UPDATE tl_lamc11_que_content AS mcq, lams_qb_question AS qb - SET mcq.qb_question_uid = qb.uid - WHERE TRIM(mcq.question) = qb.name - AND mcq.mark = qb.mark - AND (TRIM(mcq.feedback) = qb.feedback OR (IF(TRIM(mcq.feedback) = '', NULL, TRIM(mcq.feedback)) IS NULL AND qb.feedback IS NULL)) - AND qb.question_id > @start_question_id; +INSERT INTO lams_qb_tool_question +SELECT mcq.uid, qb.uid +FROM tl_lamc11_que_content AS mcq JOIN lams_qb_question AS qb + ON TRIM(mcq.question) = qb.name + AND mcq.mark = qb.mark + AND (TRIM(mcq.feedback) = qb.feedback OR (IF(TRIM(mcq.feedback) = '', NULL, TRIM(mcq.feedback)) IS NULL AND qb.feedback IS NULL)) +WHERE qb.question_id > @start_question_id; -- remove columns from MCQ which are duplicated in Question Bank ALTER TABLE tl_lamc11_que_content DROP COLUMN question, @@ -45,24 +51,26 @@ -- create Question Bank option CREATE TABLE lams_qb_option (`uid` BIGINT AUTO_INCREMENT, `qb_question_uid` BIGINT NOT NULL, - `display_order`TINYINT NOT NULL DEFAULT 1, + `display_order` TINYINT NOT NULL DEFAULT 1, `name` TEXT NOT NULL, `correct` TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (uid), + INDEX (display_order), CONSTRAINT FK_lams_qb_option_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON DELETE CASCADE ON UPDATE CASCADE); INSERT INTO lams_qb_option (qb_question_uid, display_order, name, correct) - SELECT DISTINCT q.qb_question_uid, o.displayOrder, TRIM(o.mc_que_option_text), o.correct_option - FROM tl_lamc11_que_content AS q JOIN tl_lamc11_options_content AS o ON q.uid = o.mc_que_content_id; + SELECT DISTINCT tq.qb_question_uid, o.displayOrder, TRIM(o.mc_que_option_text), o.correct_option + FROM tl_lamc11_options_content AS o + JOIN lams_qb_tool_question AS tq ON o.mc_que_content_id = tq.tool_question_uid; ALTER TABLE tl_lamc11_options_content ADD COLUMN qb_option_uid BIGINT AFTER uid, ADD CONSTRAINT FK_tl_lamc11_options_content_2 FOREIGN KEY (qb_option_uid) REFERENCES lams_qb_option (uid) ON UPDATE CASCADE; -UPDATE tl_lamc11_options_content AS mco, tl_lamc11_que_content AS mcq, lams_qb_option AS qo +UPDATE tl_lamc11_options_content AS mco, lams_qb_tool_question AS tq, lams_qb_option AS qo SET mco.qb_option_uid = qo.uid WHERE TRIM(mco.mc_que_option_text) = qo.name - AND qo.qb_question_uid = mcq.qb_question_uid - AND mco.mc_que_content_id = mcq.uid; + AND qo.qb_question_uid = tq.qb_question_uid + AND mco.mc_que_content_id = tq.tool_question_uid; ALTER TABLE tl_lamc11_options_content DROP COLUMN mc_que_option_text, DROP COLUMN correct_option, Index: lams_common/src/java/org/lamsfoundation/lams/qb/QbToolQuestion.java =================================================================== diff -u --- lams_common/src/java/org/lamsfoundation/lams/qb/QbToolQuestion.java (revision 0) +++ lams_common/src/java/org/lamsfoundation/lams/qb/QbToolQuestion.java (revision 832ea8c2f95a9ae49aa28fea549d1759dbeb27ef) @@ -0,0 +1,50 @@ +package org.lamsfoundation.lams.qb; + +import javax.persistence.CascadeType; +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.FetchType; +import javax.persistence.GeneratedValue; +import javax.persistence.GenerationType; +import javax.persistence.Id; +import javax.persistence.Inheritance; +import javax.persistence.InheritanceType; +import javax.persistence.JoinColumn; +import javax.persistence.ManyToOne; +import javax.persistence.Table; + +/** + * + * Serves as a super class for all tools' questions. + * Storing references to QB questions in a single table allows easy tracking of a question's usage thorough LAMS. + * + * @author Marcin Cieslak + */ +@Entity +@Table(name = "lams_qb_tool_question") +@Inheritance(strategy = InheritanceType.JOINED) +public abstract class QbToolQuestion { + @Id + @Column(name = "tool_question_uid") + @GeneratedValue(strategy = GenerationType.IDENTITY) + protected Long uid; + + // part of question's data is stored in Question Bank's DB tables + // getters and setters of this data (question, mark, feedback) are mapped to QbQuestion + @ManyToOne(optional = false, fetch = FetchType.EAGER, cascade = { CascadeType.DETACH, CascadeType.MERGE, + CascadeType.PERSIST, CascadeType.REFRESH }) + @JoinColumn(name = "qb_question_uid") + protected QbQuestion qbQuestion; + + public Long getUid() { + return this.uid; + } + + public QbQuestion getQbQuestion() { + return qbQuestion; + } + + public void setQbQuestion(QbQuestion qbQuestion) { + this.qbQuestion = qbQuestion; + } +} Index: lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/model/McQueContent.java =================================================================== diff -u -r851d7676f2e9e74ac0f9346dbb47ee8ed4f4c9e4 -r832ea8c2f95a9ae49aa28fea549d1759dbeb27ef --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/model/McQueContent.java (.../McQueContent.java) (revision 851d7676f2e9e74ac0f9346dbb47ee8ed4f4c9e4) +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/model/McQueContent.java (.../McQueContent.java) (revision 832ea8c2f95a9ae49aa28fea549d1759dbeb27ef) @@ -32,18 +32,17 @@ import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.FetchType; -import javax.persistence.GeneratedValue; -import javax.persistence.GenerationType; -import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.OneToMany; +import javax.persistence.PrimaryKeyJoinColumn; import javax.persistence.Table; import javax.persistence.Transient; import org.apache.commons.lang.builder.ToStringBuilder; import org.hibernate.annotations.SortComparator; import org.lamsfoundation.lams.qb.QbQuestion; +import org.lamsfoundation.lams.qb.QbToolQuestion; /** *

@@ -55,7 +54,9 @@ */ @Entity @Table(name = "tl_lamc11_que_content") -public class McQueContent implements Serializable, Comparable { +// in this entity's table primary key is "uid", but it references "tool_question_uid" in lams_qb_tool_question +@PrimaryKeyJoinColumn(name = "uid") +public class McQueContent extends QbToolQuestion implements Serializable, Comparable { public static class OptionComparator implements Comparator { @Override public int compare(McOptsContent o1, McOptsContent o2) { @@ -67,18 +68,6 @@ private static final long serialVersionUID = 4022287106119453962L; - @Id - @Column - @GeneratedValue(strategy = GenerationType.IDENTITY) - private Long uid; - - // part of question's data is stored in Question Bank's DB tables - // getters and setters of this data (question, mark, feedback) are mapped to QbQuestion - @ManyToOne(optional = false, fetch = FetchType.EAGER, cascade = { CascadeType.DETACH, CascadeType.MERGE, - CascadeType.PERSIST, CascadeType.REFRESH }) - @JoinColumn(name = "qb_question_uid") - private QbQuestion qbQuestion; - /** * It stores sha1(question) value that allows us to search for the McQueContentc with the same question */ @@ -142,22 +131,6 @@ return newMcOptionsContent; } - public Long getUid() { - return this.uid; - } - - public void setUid(Long uid) { - this.uid = uid; - } - - public QbQuestion getQbQuestion() { - return qbQuestion; - } - - public void setQbQuestion(QbQuestion qbQuestion) { - this.qbQuestion = qbQuestion; - } - public String getQuestion() { return this.qbQuestion.getName(); }