Index: lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190110.sql =================================================================== diff -u -rfba7287887f6dd83d3098100af6320cccf1f3e36 -reb5312af3f0d7ca64ca3b41988129079c7509427 --- lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190110.sql (.../patch20190110.sql) (revision fba7287887f6dd83d3098100af6320cccf1f3e36) +++ lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190110.sql (.../patch20190110.sql) (revision eb5312af3f0d7ca64ca3b41988129079c7509427) @@ -26,7 +26,26 @@ `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); +-- 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, + `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); +-- create an answer table from tools' answers will inherit +CREATE TABLE lams_qb_tool_answer (`answer_uid` BIGINT AUTO_INCREMENT, + `tool_question_uid` BIGINT NOT NULL, + `qb_option_uid` BIGINT NOT NULL, + PRIMARY KEY (answer_uid), + CONSTRAINT FK_lams_qb_tool_answer_1 FOREIGN KEY (tool_question_uid) + REFERENCES lams_qb_tool_question (tool_question_uid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_qb_tool_answer_2 FOREIGN KEY (qb_option_uid) + REFERENCES lams_qb_option (uid) ON DELETE CASCADE ON UPDATE CASCADE); + -- Migrate 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, -- so the safest solution is to place it here @@ -82,16 +101,6 @@ DROP COLUMN mark, DROP COLUMN feedback; --- 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, - `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); - -- 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.displayOrder, TRIM(o.mc_que_option_text), o.correct_option @@ -107,16 +116,17 @@ AND qo.qb_question_uid = tq.qb_question_uid AND mco.mc_que_content_id = tq.tool_question_uid; --- rewrite foreign keys and column name -ALTER TABLE tl_lamc11_usr_attempt DROP FOREIGN KEY FK_tl_lamc11_usr_attempt_3, - RENAME COLUMN mc_que_option_id TO qb_option_uid; - -ALTER TABLE tl_lamc11_usr_attempt ADD CONSTRAINT FK_tl_lamc11_usr_attempt_3 FOREIGN KEY (qb_option_uid) - REFERENCES lams_qb_option (uid) ON DELETE CASCADE ON UPDATE CASCADE; - +-- prepare for answer inheritance +INSERT INTO lams_qb_tool_answer + SELECT uid, mc_que_content_id, mc_que_option_id FROM tl_lamc11_usr_attempt; + -- clean up DROP TABLE tl_lamc11_options_content; +ALTER TABLE tl_lamc11_usr_attempt DROP FOREIGN KEY FK_tl_lamc11_usr_attempt_2, + DROP FOREIGN KEY FK_tl_lamc11_usr_attempt_3, + DROP COLUMN mc_que_content_id, + DROP COLUMN mc_que_option_id; -- prepare for Scratchie migration @@ -196,29 +206,33 @@ ORDER BY o.order_id; -ALTER TABLE tl_lascrt11_answer_log ADD COLUMN scratchie_item_uid BIGINT, - DROP FOREIGN KEY FK_NEW_610529188_693580A438BF8DFE, - RENAME COLUMN scratchie_answer_uid TO qb_option_uid; - +ALTER TABLE tl_lascrt11_answer_log ADD COLUMN scratchie_item_uid BIGINT; + +-- shift Scratchie answer UIDs by offset equal to existing UIDs of MCQ answers in lams_qb_tool_answer +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; + -- 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.qb_option_uid = qo.uid, + SET sl.scratchie_answer_uid = qo.uid, sl.scratchie_item_uid = tq.tool_question_uid WHERE sa.order_id + 1 = qo.display_order - AND sl.qb_option_uid = sa.uid + 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; - - -ALTER TABLE tl_lascrt11_answer_log ADD CONSTRAINT FK_tl_lascrt11_answer_log_1 FOREIGN KEY (scratchie_item_uid) - REFERENCES tl_lascrt11_scratchie_item (uid) ON DELETE CASCADE ON UPDATE CASCADE, - ADD CONSTRAINT FK_tl_lascrt11_answer_log_2 FOREIGN KEY (qb_option_uid) - REFERENCES lams_qb_option (uid) ON DELETE CASCADE ON UPDATE CASCADE; - - + +-- prepare for answer inheritance +INSERT INTO lams_qb_tool_answer + SELECT uid, scratchie_item_uid, scratchie_answer_uid FROM tl_lascrt11_answer_log; + + -- 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, + DROP COLUMN scratchie_answer_uid; + DROP TABLE tl_lascrt11_scratchie_answer, tmp_question, tmp_question_match, Index: lams_common/src/java/org/lamsfoundation/lams/qb/model/QbToolAnswer.java =================================================================== diff -u --- lams_common/src/java/org/lamsfoundation/lams/qb/model/QbToolAnswer.java (revision 0) +++ lams_common/src/java/org/lamsfoundation/lams/qb/model/QbToolAnswer.java (revision eb5312af3f0d7ca64ca3b41988129079c7509427) @@ -0,0 +1,55 @@ +package org.lamsfoundation.lams.qb.model; + +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; + +/** + * + * @author Marcin Cieslak + */ +@Entity +@Table(name = "lams_qb_tool_answer") +@Inheritance(strategy = InheritanceType.JOINED) +public abstract class QbToolAnswer { + @Id + @Column(name = "answer_uid") + @GeneratedValue(strategy = GenerationType.IDENTITY) + protected Long uid; + + @ManyToOne(optional = false, fetch = FetchType.LAZY) + @JoinColumn(name = "tool_question_uid") + protected QbToolQuestion qbToolQuestion; + + @ManyToOne(optional = false, fetch = FetchType.LAZY) + @JoinColumn(name = "qb_option_uid") + protected QbOption qbOption; + + public QbToolQuestion getQbToolQuestion() { + return qbToolQuestion; + } + + public void setQbToolQuestion(QbToolQuestion qbToolQuestion) { + this.qbToolQuestion = qbToolQuestion; + } + + public QbOption getQbOption() { + return qbOption; + } + + public void setQbOption(QbOption qbOption) { + this.qbOption = qbOption; + } + + public Long getUid() { + return this.uid; + } +} Index: lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dao/hibernate/McUsrAttemptDAO.java =================================================================== diff -u -r650202864c49257c570cd5c4d496ca3789db7846 -reb5312af3f0d7ca64ca3b41988129079c7509427 --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dao/hibernate/McUsrAttemptDAO.java (.../McUsrAttemptDAO.java) (revision 650202864c49257c570cd5c4d496ca3789db7846) +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dao/hibernate/McUsrAttemptDAO.java (.../McUsrAttemptDAO.java) (revision eb5312af3f0d7ca64ca3b41988129079c7509427) @@ -50,14 +50,14 @@ public class McUsrAttemptDAO extends LAMSBaseDAO implements IMcUsrAttemptDAO { private static final String LOAD_PARTICULAR_QUESTION_ATTEMPT = "from attempt in class McUsrAttempt where attempt.mcQueUsr.uid=:queUsrUid" - + " and attempt.mcQueContent.uid=:mcQueContentId order by attempt.qbOption.uid"; + + " and attempt.qbToolQuestion.uid=:mcQueContentId order by attempt.qbOption.uid"; private static final String LOAD_FINAL_USER_QUESTION_ATTEMPTS_FOR_QUESTION_SESSION = "from attempt in class McUsrAttempt where " - + " attempt.mcQueUsr.mcSessionId=:sessionUid AND attempt.mcQueContent.uid=:mcQueContentId " + + " attempt.mcQueUsr.mcSessionId=:sessionUid AND attempt.qbToolQuestion.uid=:mcQueContentId " + " AND attempt.mcQueUsr.responseFinalised = true order by attempt.mcQueUsr.uid"; private static final String LOAD_ALL_QUESTION_ATTEMPTS = "from attempt in class McUsrAttempt where attempt.mcQueUsr.uid=:queUsrUid" - + " AND attempt.mcQueUsr.responseFinalised = true order by attempt.mcQueContent.uid, attempt.qbOption.uid"; + + " AND attempt.mcQueUsr.responseFinalised = true order by attempt.qbToolQuestion.uid, attempt.qbOption.uid"; private static final String FIND_ATTEMPTS_COUNT_BY_OPTION = "select count(*) from " + McUsrAttempt.class.getName() + " as attempt where attempt.qbOption.uid=? AND attempt.mcQueUsr.responseFinalised = true"; @@ -68,7 +68,7 @@ @Override public McUsrAttempt getUserAttemptByUid(Long uid) { - return (McUsrAttempt) this.getSession().get(McUsrAttempt.class, uid); + return this.getSession().get(McUsrAttempt.class, uid); } @Override @@ -81,21 +81,23 @@ return getSessionFactory().getCurrentSession().createQuery(LOAD_ALL_QUESTION_ATTEMPTS) .setParameter("queUsrUid", userUid).list(); } - + @Override public List getFinalizedAttemptsBySessionId(final Long sessionId) { - final String LOAD_QUESTION_ATTEMPTS_BY_SESSION_ID = "SELECT attempt, u.portraitUuid FROM " + McUsrAttempt.class.getName() - + " AS attempt, " + User.class.getName() + " as u WHERE attempt.mcQueUsr.mcSession.mcSessionId=:sessionId" + final String LOAD_QUESTION_ATTEMPTS_BY_SESSION_ID = "SELECT attempt, u.portraitUuid FROM " + + McUsrAttempt.class.getName() + " AS attempt, " + User.class.getName() + + " as u WHERE attempt.mcQueUsr.mcSession.mcSessionId=:sessionId" + " AND attempt.mcQueUsr.responseFinalised = true AND u.userId=attempt.mcQueUsr.queUsrId"; return getSessionFactory().getCurrentSession().createQuery(LOAD_QUESTION_ATTEMPTS_BY_SESSION_ID) .setParameter("sessionId", sessionId).list(); } - + @Override public List getLeadersFinalizedAttemptsByContentId(final Long contentId) { - final String LOAD_QUESTION_ATTEMPTS_BY_SESSION_ID = "SELECT attempt, u.portraitUuid FROM " + McUsrAttempt.class.getName() - + " AS attempt, " + User.class.getName() + " as u WHERE attempt.mcQueUsr=attempt.mcQueUsr.mcSession.groupLeader " + final String LOAD_QUESTION_ATTEMPTS_BY_SESSION_ID = "SELECT attempt, u.portraitUuid FROM " + + McUsrAttempt.class.getName() + " AS attempt, " + User.class.getName() + + " as u WHERE attempt.mcQueUsr=attempt.mcQueUsr.mcSession.groupLeader " + " AND attempt.mcQueContent.mcContent.mcContentId=:contentId " + " AND attempt.mcQueUsr.responseFinalised = true AND u.userId=attempt.mcQueUsr.queUsrId"; @@ -133,8 +135,8 @@ @SuppressWarnings("unchecked") public List getUserAttemptsByQuestionSession(final Long sessionUid, final Long mcQueContentId) { List userAttemptList = getSessionFactory().getCurrentSession() - .createQuery(LOAD_FINAL_USER_QUESTION_ATTEMPTS_FOR_QUESTION_SESSION).setParameter("sessionUid", sessionUid) - .setParameter("mcQueContentId", mcQueContentId).list(); + .createQuery(LOAD_FINAL_USER_QUESTION_ATTEMPTS_FOR_QUESTION_SESSION) + .setParameter("sessionUid", sessionUid).setParameter("mcQueContentId", mcQueContentId).list(); return userAttemptList; } @@ -166,17 +168,17 @@ } return ((Number) list.get(0)).intValue(); } - + @Override public List getLearnerMarksByContentId(Long toolContentId) { - + final String FIND_MARKS_FOR_CONTENT_ID = "SELECT user.queUsrId, user.lastAttemptTotalMark FROM " + McQueUsr.class.getName() + " user WHERE user.mcSession.mcContent.mcContentId = ? AND user.responseFinalised = true"; List list = (List) doFind(FIND_MARKS_FOR_CONTENT_ID, new Object[] { toolContentId }); - - List toolOutputDtos = new ArrayList(); + + List toolOutputDtos = new ArrayList<>(); if (list != null && list.size() > 0) { for (Object[] element : list) { Index: lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/model/McUsrAttempt.java =================================================================== diff -u -rc87bb47eb670934f10192c08922f83367bc36230 -reb5312af3f0d7ca64ca3b41988129079c7509427 --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/model/McUsrAttempt.java (.../McUsrAttempt.java) (revision c87bb47eb670934f10192c08922f83367bc36230) +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/model/McUsrAttempt.java (.../McUsrAttempt.java) (revision eb5312af3f0d7ca64ca3b41988129079c7509427) @@ -28,16 +28,16 @@ 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.PrimaryKeyJoinColumn; import javax.persistence.Table; import javax.persistence.Transient; import org.apache.commons.lang.builder.ToStringBuilder; +import org.hibernate.Hibernate; import org.lamsfoundation.lams.qb.model.QbOption; +import org.lamsfoundation.lams.qb.model.QbToolAnswer; /** *

@@ -49,15 +49,11 @@ */ @Entity @Table(name = "tl_lamc11_usr_attempt") -public class McUsrAttempt implements Serializable, Comparable { - +//in this entity's table primary key is "uid", but it references "answer_uid" in lams_qb_tool_answer +@PrimaryKeyJoinColumn(name = "uid") +public class McUsrAttempt extends QbToolAnswer implements Serializable, Comparable { private static final long serialVersionUID = 4514268732673337338L; - @Id - @Column - @GeneratedValue(strategy = GenerationType.IDENTITY) - private Long uid; - @Column(name = "attempt_time") private Date attemptTime; @@ -71,10 +67,6 @@ private boolean passed; @ManyToOne(fetch = FetchType.LAZY) - @JoinColumn(name = "mc_que_content_id") - private McQueContent mcQueContent; - - @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "que_usr_id") private McQueUsr mcQueUsr; @@ -91,7 +83,7 @@ public McUsrAttempt(Date attemptTime, McQueContent mcQueContent, McQueUsr mcQueUsr, QbOption qbOption, Integer mark, boolean passed, boolean attemptCorrect, int confidenceLevel) { this.attemptTime = attemptTime; - this.mcQueContent = mcQueContent; + this.qbToolQuestion = mcQueContent; this.mcQueUsr = mcQueUsr; this.qbOption = qbOption; this.mark = mark; @@ -133,14 +125,6 @@ return true; } - public Long getUid() { - return this.uid; - } - - public void setUid(Long uid) { - this.uid = uid; - } - public Date getAttemptTime() { return this.attemptTime; } @@ -149,12 +133,12 @@ this.attemptTime = attemptTime; } - public org.lamsfoundation.lams.tool.mc.model.McQueContent getMcQueContent() { - return this.mcQueContent; + public McQueContent getMcQueContent() { + return (McQueContent) Hibernate.unproxy(qbToolQuestion); } - public void setMcQueContent(org.lamsfoundation.lams.tool.mc.model.McQueContent mcQueContent) { - this.mcQueContent = mcQueContent; + public void setMcQueContent(McQueContent mcQueContent) { + this.qbToolQuestion = mcQueContent; } public org.lamsfoundation.lams.tool.mc.model.McQueUsr getMcQueUsr() { @@ -165,14 +149,6 @@ this.mcQueUsr = mcQueUsr; } - public QbOption getQbOption() { - return qbOption; - } - - public void setQbOption(QbOption qbOption) { - this.qbOption = qbOption; - } - public McOptsContent getMcOptionsContent() { if (mcOptionsContent == null) { mcOptionsContent = new McOptsContent(); Index: lams_tool_lamc/web/learning/CombinedAnswersContent.jsp =================================================================== diff -u -r7e2e42fae15b4f16cba0e496614b3927d72b4d4d -reb5312af3f0d7ca64ca3b41988129079c7509427 --- lams_tool_lamc/web/learning/CombinedAnswersContent.jsp (.../CombinedAnswersContent.jsp) (revision 7e2e42fae15b4f16cba0e496614b3927d72b4d4d) +++ lams_tool_lamc/web/learning/CombinedAnswersContent.jsp (.../CombinedAnswersContent.jsp) (revision eb5312af3f0d7ca64ca3b41988129079c7509427) @@ -37,8 +37,8 @@ - checked="checked" disabled="disabled" > @@ -50,8 +50,8 @@ -