DROP TABLE equivalency_rules
GO

CREATE TABLE equivalency_rules (id nvarchar(85) NOT NULL, rule_group_id nvarchar(85) NOT NULL, substitute_id nvarchar(85) NOT NULL, target_id nvarchar(85) NOT NULL, create_date datetime NOT NULL, created_by nvarchar(85) NOT NULL, count int NOT NULL)
GO

ALTER TABLE equivalency_rules ADD CONSTRAINT PK_EQUIVALENCY_RULES PRIMARY KEY (id)
GO

ALTER TABLE equivalency_rules ADD CONSTRAINT FK_EQ_RULES_SUBSTITUTE_LID FOREIGN KEY (substitute_id) REFERENCES learningObject (learningid)
GO

ALTER TABLE equivalency_rules ADD CONSTRAINT FK_EQ_RULES_TARGET_LID FOREIGN KEY (target_id) REFERENCES learningObject (learningid)
GO

CREATE NONCLUSTERED INDEX nuk_eq_rules_sub ON equivalency_rules(substitute_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140813-ns-1', 'nicky.siu', 'nschema/102.changeLog.xml', GETDATE(), 172, '8:39262176ebd91634f09654abe67f46d6', 'dropTable tableName=equivalency_rules; createTable tableName=equivalency_rules; addPrimaryKey constraintName=PK_EQUIVALENCY_RULES, tableName=equivalency_rules; addForeignKeyConstraint baseTableName=equivalency_rules, constraintName=FK_EQ_RULES_SUB...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140819-ns-1::nicky.siu
CREATE NONCLUSTERED INDEX nuk_eq_rules_groupId ON equivalency_rules(rule_group_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140819-ns-1', 'nicky.siu', 'nschema/102.changeLog.xml', GETDATE(), 173, '8:73c4201952c23eca8c1d0305e974a0a6', 'createIndex indexName=nuk_eq_rules_groupId, tableName=equivalency_rules', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141106-kh-1::kelvin.hung
IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'i_sessiondetail'
            )
            DROP TRIGGER i_sessiondetail
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'u_sessiondetail'
            )
            DROP TRIGGER u_sessiondetail
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'd_sessiondetail'
            )
            DROP TRIGGER d_sessiondetail
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141106-kh-1', 'kelvin.hung', 'nschema/102.changeLog.xml', GETDATE(), 174, '8:aec52f6e9a0246c3847e18c79d9373f4', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140827-dc-1::derek.chan
ALTER TABLE sessiondetail ADD enforce_mod_rev char(1) CONSTRAINT DF_sessiondetail_enforce_mod_rev DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140827-dc-1', 'derek.chan', 'nschema/102.changeLog.xml', GETDATE(), 175, '8:24830600e81b4a6207e02d1531d4f3a1', 'addColumn tableName=sessiondetail', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140828-js-1::janice.sung
CREATE TABLE submod_tran_subt (id nvarchar(85) NOT NULL, substitute_tran_id nvarchar(15) NOT NULL, target_tran_id nvarchar(15) NOT NULL, program_tran_id nvarchar(15) NOT NULL)
GO

ALTER TABLE submod_tran_subt ADD CONSTRAINT PK_SUBMOD_TRAN_SUBT PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140828-js-1', 'janice.sung', 'nschema/102.changeLog.xml', GETDATE(), 176, '8:e5c01a987bcf9223c518bfb2881b9b8e', 'createTable tableName=submod_tran_subt; addPrimaryKey constraintName=PK_SUBMOD_TRAN_SUBT, tableName=submod_tran_subt', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140828-sm-1::simon.mok
DROP VIEW valid_transcript_vw
GO

CREATE VIEW valid_transcript_vw AS SELECT
		    transcriptid,
		    userid,
		    learningid,
		    currid,
		    learningType,
		    eventid,
		    startdate,
		    finishdate,
		    finalgrade,
		    studentstatus,
		    progress,
		    assessDate,
		    attendance,
		    perfGradeid,
		    sessionid,
		    creditTranscriptLink,
		    curriculumTranscriptLink,
		    assessmentscore,
		    requestSource,
		    contentSessionID,
		    certSessionID,
		    enrollmentDate,
		    remindercount,
		    costcenter,
		    confirmation_status,
		    reopen,
		    isOpen,
		    pre_assess_score,
		    post_assess_score,
		    usage_expiration_date,
		    quick_eval,
		    notifyemail,
		    completion_deadline,
		    deadline_expired,
		    revision_number,
		    ntraininghours,
		    allowDappraiserAssess,
		    offline_key,
		    mekp_sync,
		    send_notif_ew,
		    update_meaning,
		    last_updated,
		    updater_legal_name,
		    update_meaning_text,
		    last_updater_uid,
		    creator_uid,
		    datecreated,
		    curr_total_time,
		    attempts_allowed,
		    grading_method,
		    attempts_count,
		    completion_method,
		    final_score,
		    weighting,
		    weightedscore,
		    case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = transcript.transcriptid) then 1 else 0 end as sub_learning
		  FROM transcript
		  WHERE studentstatus NOT IN (8, 9, 10)
		  UNION ALL
		  SELECT
		    a.transcriptid,
		    a.userid,
		    a.learningid,
		    a.currid,
		    a.learningType,
		    a.eventid,
		    b.startdate,
		    b.finishdate,
		    b.finalgrade,
		    b.studentstatus,
		    b.progress,
		    b.assessDate,
		    b.attendance,
		    b.perfGradeid,
		    a.sessionid,
		    NULL,
		    a.curriculumTranscriptLink,
		    b.assessmentscore,
		    a.requestSource,
		    b.contentSessionID,
		    b.certSessionID,
		    a.enrollmentDate,
		    b.remindercount,
		    b.costcenter,
		    b.confirmation_status,
		    b.reopen,
		    a.isOpen,
		    b.pre_assess_score,
		    b.post_assess_score,
		    b.usage_expiration_date,
		    b.quick_eval,
		    b.notifyemail,
		    b.completion_deadline,
		    b.deadline_expired,
		    b.revision_number,
		    b.ntraininghours,
		    b.allowDappraiserAssess,
		    b.offline_key,
		    b.mekp_sync,
		    b.send_notif_ew,
		    b.update_meaning,
		    b.last_updated,
		    b.updater_legal_name,
		    a.update_meaning_text,
		    a.last_updater_uid,
		    a.creator_uid,
		    a.datecreated,
		    b.curr_total_time,
		    b.attempts_allowed,
		    b.grading_method,
		    b.attempts_count,
		    b.completion_method,
		    b.final_score,
		    b.weighting,
		    b.weightedscore,
		    case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = b.transcriptid) then 1 else 0 end as sub_learning
		  FROM transcript a, transcript b
		  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140828-sm-1', 'simon.mok', 'nschema/102.changeLog.xml', GETDATE(), 177, '8:6e19d2724bfd641e0cf43bb09bc18fbf', 'dropView viewName=valid_transcript_vw; createView viewName=valid_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140903-ns-1::nicky.siu
update sessiondetail set enforce_mod_rev = 'Y' where currid in (select learningid from learningObject where allowReEnroll in ('3', '4'))
GO

update learningObject set allowReEnroll = '2' where allowReEnroll = '3'
GO

update learningObject set allowReEnroll = '1' where allowReEnroll = '4'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140903-ns-1', 'nicky.siu', 'nschema/102.changeLog.xml', GETDATE(), 178, '8:f2e51731016bcf1666f3fd0ea6a7ed53', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140903-sm-1::simon.mok
DROP VIEW valid_transcript_vw
GO

CREATE VIEW valid_transcript_vw AS SELECT
		    transcriptid,
		    userid,
		    learningid,
		    currid,
		    learningType,
		    eventid,
		    startdate,
		    finishdate,
		    finalgrade,
		    studentstatus,
		    progress,
		    assessDate,
		    attendance,
		    perfGradeid,
		    sessionid,
		    creditTranscriptLink,
		    curriculumTranscriptLink,
		    assessmentscore,
		    requestSource,
		    contentSessionID,
		    certSessionID,
		    enrollmentDate,
		    remindercount,
		    costcenter,
		    confirmation_status,
		    reopen,
		    isOpen,
		    pre_assess_score,
		    post_assess_score,
		    usage_expiration_date,
		    quick_eval,
		    notifyemail,
		    completion_deadline,
		    deadline_expired,
		    revision_number,
		    ntraininghours,
		    allowDappraiserAssess,
		    offline_key,
		    mekp_sync,
		    send_notif_ew,
		    update_meaning,
		    last_updated,
		    updater_legal_name,
		    update_meaning_text,
		    last_updater_uid,
		    creator_uid,
		    datecreated,
		    curr_total_time,
		    attempts_allowed,
		    grading_method,
		    attempts_count,
		    completion_method,
		    final_score,
		    weighting,
		    weightedscore,
		    case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = transcript.transcriptid) then 'Y' else 'N' end as sub_learning
		  FROM transcript
		  WHERE studentstatus NOT IN (8, 9, 10)
		  UNION ALL
		  SELECT
		    a.transcriptid,
		    a.userid,
		    a.learningid,
		    a.currid,
		    a.learningType,
		    a.eventid,
		    b.startdate,
		    b.finishdate,
		    b.finalgrade,
		    b.studentstatus,
		    b.progress,
		    b.assessDate,
		    b.attendance,
		    b.perfGradeid,
		    a.sessionid,
		    CASE WHEN 1=1 THEN NULL ELSE '' END as creditTranscriptLink,
		    a.curriculumTranscriptLink,
		    b.assessmentscore,
		    a.requestSource,
		    b.contentSessionID,
		    b.certSessionID,
		    a.enrollmentDate,
		    b.remindercount,
		    b.costcenter,
		    b.confirmation_status,
		    b.reopen,
		    a.isOpen,
		    b.pre_assess_score,
		    b.post_assess_score,
		    b.usage_expiration_date,
		    b.quick_eval,
		    b.notifyemail,
		    b.completion_deadline,
		    b.deadline_expired,
		    b.revision_number,
		    b.ntraininghours,
		    b.allowDappraiserAssess,
		    b.offline_key,
		    b.mekp_sync,
		    b.send_notif_ew,
		    b.update_meaning,
		    b.last_updated,
		    b.updater_legal_name,
		    a.update_meaning_text,
		    a.last_updater_uid,
		    a.creator_uid,
		    a.datecreated,
		    b.curr_total_time,
		    b.attempts_allowed,
		    b.grading_method,
		    b.attempts_count,
		    b.completion_method,
		    b.final_score,
		    b.weighting,
		    b.weightedscore,
		    case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = b.transcriptid) then 'Y' else 'N' end as sub_learning
		  FROM transcript a, transcript b
		  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140903-sm-1', 'simon.mok', 'nschema/102.changeLog.xml', GETDATE(), 179, '8:95fa08eefd9ed724fa21d89b5766ff7b', 'dropView viewName=valid_transcript_vw; createView viewName=valid_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140904-rr-1::ray.ruff
CREATE NONCLUSTERED INDEX NUK_USER_LID_STATUS ON transcript(userid, learningid, studentstatus)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140904-rr-1', 'ray.ruff', 'nschema/102.changeLog.xml', GETDATE(), 180, '8:f9b550481f48ec09792b4e1619afc93b', 'createIndex indexName=NUK_USER_LID_STATUS, tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140910-sm-1::simon.mok
ALTER TABLE transcript ADD sub_learning char(1) CONSTRAINT DF_transcript_sub_learning DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140910-sm-1', 'simon.mok', 'nschema/102.changeLog.xml', GETDATE(), 181, '8:49fd42c085feeac4ba6242ed79a0a31c', 'addColumn tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140910-sm-2::simon.mok
DROP VIEW valid_transcript_vw
GO

CREATE VIEW valid_transcript_vw AS SELECT
		    transcriptid,
		    userid,
		    learningid,
		    currid,
		    learningType,
		    eventid,
		    startdate,
		    finishdate,
		    finalgrade,
		    studentstatus,
		    progress,
		    assessDate,
		    attendance,
		    perfGradeid,
		    sessionid,
		    creditTranscriptLink,
		    curriculumTranscriptLink,
		    assessmentscore,
		    requestSource,
		    contentSessionID,
		    certSessionID,
		    enrollmentDate,
		    remindercount,
		    costcenter,
		    confirmation_status,
		    reopen,
		    isOpen,
		    pre_assess_score,
		    post_assess_score,
		    usage_expiration_date,
		    quick_eval,
		    notifyemail,
		    completion_deadline,
		    deadline_expired,
		    revision_number,
		    ntraininghours,
		    allowDappraiserAssess,
		    offline_key,
		    mekp_sync,
		    send_notif_ew,
		    update_meaning,
		    last_updated,
		    updater_legal_name,
		    update_meaning_text,
		    last_updater_uid,
		    creator_uid,
		    datecreated,
		    curr_total_time,
		    attempts_allowed,
		    grading_method,
		    attempts_count,
		    completion_method,
		    final_score,
		    weighting,
		    weightedscore,
		    sub_learning
		  FROM transcript
		  WHERE studentstatus NOT IN (8, 9, 10)
		  UNION ALL
		  SELECT
		    a.transcriptid,
		    a.userid,
		    a.learningid,
		    a.currid,
		    a.learningType,
		    a.eventid,
		    b.startdate,
		    b.finishdate,
		    b.finalgrade,
		    b.studentstatus,
		    b.progress,
		    b.assessDate,
		    b.attendance,
		    b.perfGradeid,
		    a.sessionid,
		    CASE WHEN 1=1 THEN NULL ELSE '' END as creditTranscriptLink,
		    a.curriculumTranscriptLink,
		    b.assessmentscore,
		    a.requestSource,
		    b.contentSessionID,
		    b.certSessionID,
		    a.enrollmentDate,
		    b.remindercount,
		    b.costcenter,
		    b.confirmation_status,
		    b.reopen,
		    a.isOpen,
		    b.pre_assess_score,
		    b.post_assess_score,
		    b.usage_expiration_date,
		    b.quick_eval,
		    b.notifyemail,
		    b.completion_deadline,
		    b.deadline_expired,
		    b.revision_number,
		    b.ntraininghours,
		    b.allowDappraiserAssess,
		    b.offline_key,
		    b.mekp_sync,
		    b.send_notif_ew,
		    b.update_meaning,
		    b.last_updated,
		    b.updater_legal_name,
		    a.update_meaning_text,
		    a.last_updater_uid,
		    a.creator_uid,
		    a.datecreated,
		    b.curr_total_time,
		    b.attempts_allowed,
		    b.grading_method,
		    b.attempts_count,
		    b.completion_method,
		    b.final_score,
		    b.weighting,
		    b.weightedscore,
		    b.sub_learning
		  FROM transcript a, transcript b
		  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140910-sm-2', 'simon.mok', 'nschema/102.changeLog.xml', GETDATE(), 182, '8:33ffe9e7d78fdd8efd52c5c5d0370bd1', 'dropView viewName=valid_transcript_vw; createView viewName=valid_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140915-hf-1::hubert.fajarit
ALTER TABLE learningObject ADD esig_course_withdraw char(1) CONSTRAINT DF_learningObject_esig_course_withdraw DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_course_modify char(1) CONSTRAINT DF_learningObject_esig_course_modify DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_course_launch char(1) CONSTRAINT DF_learningObject_esig_course_launch DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_course_import char(1) CONSTRAINT DF_learningObject_esig_course_import DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_course_finish char(1) CONSTRAINT DF_learningObject_esig_course_finish DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_exam_launch char(1) CONSTRAINT DF_learningObject_esig_exam_launch DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_wizard_modify_trans char(1) CONSTRAINT DF_learningObject_esig_wizard_modify_trans DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_reviewer_modify_trans char(1) CONSTRAINT DF_learningObject_esig_reviewer_modify_trans DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_editor_modify_trans char(1) CONSTRAINT DF_learningObject_esig_editor_modify_trans DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_modify_attendance char(1) CONSTRAINT DF_learningObject_esig_modify_attendance DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_course_withdraw_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_course_modify_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_course_launch_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_course_import_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_course_finish_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_exam_launch_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_wzrd_modify_trans_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_rvwr_modify_trans_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_edtr_modify_trans_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_modify_attendance_meaning nvarchar (max)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-hf-1', 'hubert.fajarit', 'nschema/102.changeLog.xml', GETDATE(), 183, '8:fe44aa97d49a3bf489fa7810309cd44d', 'addColumn tableName=learningObject', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140915-ge-1::gian.espino
ALTER TABLE code_orgstructure ADD esig_course_csv_loader_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_prog_csv_loader_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_course_withdraw_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_course_modify_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_course_launch_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_course_import_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_course_finish_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_exam_launch_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_question_mod_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_question_import_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_grading_test_ans_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_review_mod_trans_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_editor_mod_trans_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_mod_attendance_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_wizard_mod_trans_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_mod_ext_training_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_mod_cert_meaning nvarchar (max)
GO

ALTER TABLE code_orgstructure ADD esig_award_revert_cert_meaning nvarchar (max)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-ge-1', 'gian.espino', 'nschema/102.changeLog.xml', GETDATE(), 184, '8:9405c09aac59bede80082f68c28ab9ac', 'addColumn tableName=code_orgstructure', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140915-ge-2::gian.espino
ALTER TABLE code_orgstructure ADD mail_viewpdf_option char(1) CONSTRAINT DF_code_orgstructure_mail_viewpdf_option DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD mail_viewpdf_templateid nvarchar(15)
GO

ALTER TABLE code_orgstructure ADD mail_viewpdf_margins nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-ge-2', 'gian.espino', 'nschema/102.changeLog.xml', GETDATE(), 185, '8:b9ddad1b262a6442ecd6daf565ba24c4', 'addColumn tableName=code_orgstructure', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140918-dr-1::dreidel.regala
ALTER TABLE prereq ADD prereqcontrol int
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140918-dr-1', 'dreidel.regala', 'nschema/102.changeLog.xml', GETDATE(), 186, '8:c9e3ee40eff0b58dc3e8c9f0375ad6ac', 'addColumn tableName=prereq', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20140923-dr-3::dreidel.regala
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE prereq DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'prereq') AND [c].[name] = N'prereqcontrol'
EXEC sp_executesql @sql
GO

ALTER TABLE prereq DROP COLUMN prereqcontrol
GO

ALTER TABLE prereq ADD prereqcontrol int CONSTRAINT DF_prereq_prereqcontrol DEFAULT 0 NOT NULL
GO

ALTER TABLE prereq DROP CONSTRAINT PK_PREREQ
GO

ALTER TABLE prereq ADD CONSTRAINT PK_PREREQ PRIMARY KEY (learningid, prereqid, prereqcontrol)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140923-dr-3', 'dreidel.regala', 'nschema/102.changeLog.xml', GETDATE(), 187, '8:a3cb319129d882fc7ccdd09e6e8907f5', 'dropColumn columnName=prereqcontrol, tableName=prereq; addColumn tableName=prereq; dropPrimaryKey constraintName=PK_PREREQ, tableName=prereq; addPrimaryKey constraintName=PK_PREREQ, tableName=prereq', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141008-rr-1::ray.ruff
CREATE TABLE system_migration_tasks (taskid nvarchar(85) NOT NULL, action_date datetime)
GO

ALTER TABLE system_migration_tasks ADD CONSTRAINT PK_MIGRATION_TASKS PRIMARY KEY (taskid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141008-rr-1', 'ray.ruff', 'nschema/102.changeLog.xml', GETDATE(), 188, '8:37d4343b51a4317a8571bab2307cb05c', 'createTable tableName=system_migration_tasks; addPrimaryKey constraintName=PK_MIGRATION_TASKS, tableName=system_migration_tasks', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141010-rr-1::ray.ruff
CREATE TABLE system_daily_stats (effective_date datetime NOT NULL, n_daily_logins int CONSTRAINT DF_system_daily_stats_n_daily_logins DEFAULT 0 NOT NULL, n_unique_logins int CONSTRAINT DF_system_daily_stats_n_unique_logins DEFAULT 0 NOT NULL, n_enrollments int CONSTRAINT DF_system_daily_stats_n_enrollments DEFAULT 0 NOT NULL, n_course_launches int CONSTRAINT DF_system_daily_stats_n_course_launches DEFAULT 0 NOT NULL, n_exams_delivered int CONSTRAINT DF_system_daily_stats_n_exams_delivered DEFAULT 0 NOT NULL, n_peak_sessions int CONSTRAINT DF_system_daily_stats_n_peak_sessions DEFAULT 0 NOT NULL, n_page_hits int CONSTRAINT DF_system_daily_stats_n_page_hits DEFAULT 0 NOT NULL, n_api_calls int CONSTRAINT DF_system_daily_stats_n_api_calls DEFAULT 0 NOT NULL, n_active_accounts int CONSTRAINT DF_system_daily_stats_n_active_accounts DEFAULT 0 NOT NULL, n_total_accounts int CONSTRAINT DF_system_daily_stats_n_total_accounts DEFAULT 0 NOT NULL, n_total_training_records int CONSTRAINT DF_system_daily_stats_n_total_training_records DEFAULT 0 NOT NULL)
GO

ALTER TABLE system_daily_stats ADD CONSTRAINT PK_SYSTEM_DAILY_STATS PRIMARY KEY (effective_date)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141010-rr-1', 'ray.ruff', 'nschema/102.changeLog.xml', GETDATE(), 189, '8:e71dd3d08f5d8e36d728716ed4d9ea67', 'createTable tableName=system_daily_stats; addPrimaryKey constraintName=PK_SYSTEM_DAILY_STATS, tableName=system_daily_stats', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141010-rr-2::ray.ruff
ALTER TABLE transcript ADD actual_training_time_sec int CONSTRAINT DF_transcript_actual_training_time_sec DEFAULT 0 NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141010-rr-2', 'ray.ruff', 'nschema/102.changeLog.xml', GETDATE(), 190, '8:5941ec7c8dd9a2faae8526c72f3c7140', 'addColumn tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141014-dc-1::derek.chan
CREATE VIEW aggregated_transcript_vw AS SELECT
				    t1.transcriptid,
				    a2.userid,
					t1.learningid,
				    t1.currid,
				    t1.learningType,
				    t1.eventid,
				    t1.startdate,
				    t1.finishdate,
				    t1.finalgrade,
				    t1.studentstatus,
				    t1.progress,
				    t1.assessDate,
				    t1.attendance,
				    t1.perfGradeid,
				    t1.sessionid,
				    t1.creditTranscriptLink,
				    t1.curriculumTranscriptLink,
				    t1.assessmentscore,
				    t1.requestSource,
				    t1.contentSessionID,
				    t1.certSessionID,
				    t1.enrollmentDate,
				    t1.remindercount,
				    t1.costcenter,
				    t1.confirmation_status,
				    t1.reopen,
				    t1.isOpen,
				    t1.pre_assess_score,
				    t1.post_assess_score,
				    t1.usage_expiration_date,
				    t1.quick_eval,
				    t1.notifyemail,
				    t1.completion_deadline,
				    t1.deadline_expired,
				    t1.revision_number,
				    t1.ntraininghours,
				    t1.allowDappraiserAssess,
				    t1.offline_key,
				    t1.mekp_sync,
				    t1.send_notif_ew,
				    t1.update_meaning,
				    t1.last_updated,
				    t1.updater_legal_name,
				    t1.update_meaning_text,
				    t1.last_updater_uid,
				    t1.creator_uid,
				    t1.datecreated,
				    t1.curr_total_time,
				    t1.attempts_allowed,
				    t1.grading_method,
				    t1.attempts_count,
				    t1.completion_method,
				    t1.final_score,
				    t1.weighting,
				    t1.weightedscore,
					case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = t1.transcriptid) then 'Y' else 'N' end as sub_learning,
					case when a2.userid = t1.userid then 'Y' else 'N' end belongsToLearnerAssignment
				  FROM transcript t1
				  inner join users_assignment a1 on a1.userid = t1.userid
				  inner join users_assignment a2 on a2.user_meta_id = a1.user_meta_id
				  WHERE t1.studentstatus NOT IN (8, 9, 10)
		
		union all
		
		SELECT
				    a.transcriptid,
				    a2.userid,
				    a.learningid,
				    a.currid,
				    a.learningType,
				    a.eventid,
				    b.startdate,
				    b.finishdate,
				    b.finalgrade,
				    b.studentstatus,
				    b.progress,
				    b.assessDate,
				    b.attendance,
				    b.perfGradeid,
				    a.sessionid,
				    CASE WHEN 1=1 THEN NULL ELSE '' END as creditTranscriptLink,
				    a.curriculumTranscriptLink,
				    b.assessmentscore,
				    a.requestSource,
				    b.contentSessionID,
				    b.certSessionID,
				    a.enrollmentDate,
				    b.remindercount,
				    b.costcenter,
				    b.confirmation_status,
				    b.reopen,
				    a.isOpen,
				    b.pre_assess_score,
				    b.post_assess_score,
				    b.usage_expiration_date,
				    b.quick_eval,
				    b.notifyemail,
				    b.completion_deadline,
				    b.deadline_expired,
				    b.revision_number,
				    b.ntraininghours,
				    b.allowDappraiserAssess,
				    b.offline_key,
				    b.mekp_sync,
				    b.send_notif_ew,
				    b.update_meaning,
				    b.last_updated,
				    b.updater_legal_name,
				    a.update_meaning_text,
				    a.last_updater_uid,
				    a.creator_uid,
				    a.datecreated,
				    b.curr_total_time,
				    b.attempts_allowed,
				    b.grading_method,
				    b.attempts_count,
				    b.completion_method,
				    b.final_score,
				    b.weighting,
				    b.weightedscore,
				    case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = b.transcriptid) then 'Y' else 'N' end as sub_learning,
					case when a2.userid = a.userid then 'Y' else 'N' end belongsToLearnerAssignment
				  FROM transcript a
				  inner join transcript b on b.transcriptid = a.creditTranscriptLink
				  inner join users_assignment a1 on a1.userid = a.userid
				  inner join users_assignment a2 on a2.user_meta_id = a1.user_meta_id
				  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

CREATE VIEW aggregated_ext_training_vw AS select recordid, a2.userid, title, startdate, enddate, grade, score, comments, training_hours, duration_unit, 
			course_type, subject, venue, language, cost, currency, vendor_info1, studentstatus,
			display_filename, path, upload_date, uploaded_by, case when a2.userid = et.userid then 'Y' else 'N' end belongsToLearnerAssignment 
			from external_training et
			inner join users_assignment a1 on a1.userid = et.userid
			inner join users_assignment a2 on a2.user_meta_id = a1.user_meta_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141014-dc-1', 'derek.chan', 'nschema/102.changeLog.xml', GETDATE(), 191, '8:2fa6acc8dee2c10b461a0d7777262cc5', 'createView viewName=aggregated_transcript_vw; createView viewName=aggregated_ext_training_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141015-kh-1::kelvin.hung
INSERT INTO permissions (objectid, objecttype, permissioncode, identityid, identitytype, operator, count) VALUES ('27', 'TSS', 2, '*ROOT*', 'O', 'OR', 1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141015-kh-1', 'kelvin.hung', 'nschema/102.changeLog.xml', GETDATE(), 192, '8:6046d286056276e3f12fd80923a31cd9', 'insert tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141015-kh-2::kelvin.hung
DELETE FROM logical_domain_config WHERE config_key = 'general.userSetsCompletedStatusEnabled'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141015-kh-2', 'kelvin.hung', 'nschema/102.changeLog.xml', GETDATE(), 193, '8:cbb49251a1fb465f5dc2a7a446708144', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141016-kc-1::kelvin.chan
CREATE TABLE question_choices (questionid nvarchar(85) NOT NULL, displayorder int NOT NULL, choice nvarchar(1000) NOT NULL)
GO

ALTER TABLE question_choices ADD CONSTRAINT FK_QUESTION_CHOICES_QUESTIONID FOREIGN KEY (questionid) REFERENCES question (questionid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141016-kc-1', 'kelvin.chan', 'nschema/102.changeLog.xml', GETDATE(), 194, '8:8bb1342d3fd2812662f4fd53d0e85c96', 'createTable tableName=question_choices; addForeignKeyConstraint baseTableName=question_choices, constraintName=FK_QUESTION_CHOICES_QUESTIONID, referencedTableName=question', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141016-kc-2::kelvin.chan
INSERT INTO question_choices (questionid, displayorder, choice)
			SELECT questionid, displayorder, choice FROM
			(SELECT questionid,1 displayorder, choice1 choice FROM question_type1 WHERE NOT (choice1 IS NULL OR choice1 = '')
			UNION ALL
			SELECT questionid,2 displayorder, choice2 choice FROM question_type1 WHERE NOT (choice2 IS NULL OR choice2 = '')
			UNION ALL
			SELECT questionid,3 displayorder, choice3 choice FROM question_type1 WHERE NOT (choice3 IS NULL OR choice3 = '')
			UNION ALL
			SELECT questionid,4 displayorder, choice4 choice FROM question_type1 WHERE NOT (choice4 IS NULL OR choice4 = '')
			UNION ALL
			SELECT questionid,5 displayorder, choice5 choice FROM question_type1 WHERE NOT (choice5 IS NULL OR choice5 = '')
			UNION ALL
			SELECT questionid,6 displayorder, choice6 choice FROM question_type1 WHERE NOT (choice6 IS NULL OR choice6 = '')
			UNION ALL
			SELECT questionid,7 displayorder, choice7 choice FROM question_type1 WHERE NOT (choice7 IS NULL OR choice7 = '')
			UNION ALL
			SELECT questionid,8 displayorder, choice8 choice FROM question_type1 WHERE NOT (choice8 IS NULL OR choice8 = '')
			UNION ALL
			SELECT questionid,9 displayorder, choice9 choice FROM question_type1 WHERE NOT (choice9 IS NULL OR choice9 = '')
			UNION ALL
			SELECT questionid,10 displayorder, choice10 choice FROM question_type1 WHERE NOT (choice10 IS NULL OR choice10 = '')) choices
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141016-kc-2', 'kelvin.chan', 'nschema/102.changeLog.xml', GETDATE(), 195, '8:9ed2430cfc166ec3c4459ca53760f6ee', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141016-kc-3::kelvin.chan
INSERT INTO question_choices (questionid, displayorder, choice)
			SELECT questionid, displayorder, choice FROM
			(SELECT questionid,1 displayorder, choice1 choice FROM question_type7 WHERE NOT (choice1 IS NULL OR choice1 = '')
			UNION ALL
			SELECT questionid,2 displayorder, choice2 choice FROM question_type7 WHERE NOT (choice2 IS NULL OR choice2 = '')
			UNION ALL
			SELECT questionid,3 displayorder, choice3 choice FROM question_type7 WHERE NOT (choice3 IS NULL OR choice3 = '')
			UNION ALL
			SELECT questionid,4 displayorder, choice4 choice FROM question_type7 WHERE NOT (choice4 IS NULL OR choice4 = '')
			UNION ALL
			SELECT questionid,5 displayorder, choice5 choice FROM question_type7 WHERE NOT (choice5 IS NULL OR choice5 = '')
			UNION ALL
			SELECT questionid,6 displayorder, choice6 choice FROM question_type7 WHERE NOT (choice6 IS NULL OR choice6 = '')
			UNION ALL
			SELECT questionid,7 displayorder, choice7 choice FROM question_type7 WHERE NOT (choice7 IS NULL OR choice7 = '')
			UNION ALL
			SELECT questionid,8 displayorder, choice8 choice FROM question_type7 WHERE NOT (choice8 IS NULL OR choice8 = '')) choices
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141016-kc-3', 'kelvin.chan', 'nschema/102.changeLog.xml', GETDATE(), 196, '8:531616d398b8731f8f8c32b106a5d435', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141016-kc-5::kelvin.chan
ALTER TABLE question_choices ADD choiceid nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141016-kc-5', 'kelvin.chan', 'nschema/102.changeLog.xml', GETDATE(), 197, '8:5b7bc561e5abde88838944642cc7a127', 'addColumn tableName=question_choices', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141016-kc-7::kelvin.chan
DECLARE @counter nvarchar(85) 
   				SET @counter = '0' 
   				UPDATE question_choices 
   				SET @counter = choiceid = CAST(@counter AS INT) + 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141016-kc-7', 'kelvin.chan', 'nschema/102.changeLog.xml', GETDATE(), 198, '8:1b2dadcd12932c40930da29e11cd00a1', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141016-kc-10::kelvin.chan
ALTER TABLE question_choices ALTER COLUMN choiceid nvarchar(85) NOT NULL
GO

ALTER TABLE question_choices ADD CONSTRAINT PK_QUESTION_CHOICES PRIMARY KEY (choiceid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141016-kc-10', 'kelvin.chan', 'nschema/102.changeLog.xml', GETDATE(), 199, '8:bcaa1077595311e994afc4b6ab49da55', 'addNotNullConstraint columnName=choiceid, tableName=question_choices; addPrimaryKey constraintName=PK_QUESTION_CHOICES, tableName=question_choices', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141016-kc-11::kelvin.chan
IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'i_question_type1'
            )
            DROP TRIGGER i_question_type1
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'u_question_type1'
            )
            DROP TRIGGER u_question_type1
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'd_question_type1'
            )
            DROP TRIGGER d_question_type1
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'i_question_type7'
            )
            DROP TRIGGER i_question_type7
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'u_question_type7'
            )
            DROP TRIGGER u_question_type7
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'd_question_type7'
            )
            DROP TRIGGER d_question_type7
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141016-kc-11', 'kelvin.chan', 'nschema/102.changeLog.xml', GETDATE(), 200, '8:e7c5b590f2023d43b02ea442e0ae15b8', 'sql; sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141028-gl-10::garbo.lim
ALTER TABLE question_type7 ADD answer_temp nvarchar(1000)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141028-gl-10', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 201, '8:cfa4d67b4585539eb48f02e8ead4ec40', 'addColumn tableName=question_type7', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141028-gl-11::garbo.lim
UPDATE question_type7 SET answer_temp = LTRIM(RTRIM(CAST(answer AS NVARCHAR)))
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141028-gl-11', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 202, '8:41fba8f009807c7bfa51983aed98756c', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141028-gl-14::garbo.lim
UPDATE question_type7 SET answer_temp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(answer_temp, '1', '1;'), '2', '2;'), '3', '3;'), '4', '4;'), '5', '5;'), '6', '6;'), '7', '7;'), '8', '8;')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141028-gl-14', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 203, '8:fbc80d49db5349e4108e2fb11034c44f', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141028-gl-16::garbo.lim
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type7 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type7') AND [c].[name] = N'answer'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type7 DROP COLUMN answer
GO

exec sp_rename 'question_type7.answer_temp', 'answer'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141028-gl-16', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 204, '8:53c9fa0c1ea8d3a6dd92adf5fb2f6faa', 'dropColumn columnName=answer, tableName=question_type7; renameColumn newColumnName=answer, oldColumnName=answer_temp, tableName=question_type7', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141016-kc-12::kelvin.chan
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice1'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice1
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice2'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice2
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice3'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice3
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice4'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice4
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice5'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice5
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice6'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice6
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice7'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice7
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice8'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice8
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice9'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice9
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type1 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type1') AND [c].[name] = N'choice10'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type1 DROP COLUMN choice10
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type7 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type7') AND [c].[name] = N'choice1'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type7 DROP COLUMN choice1
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type7 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type7') AND [c].[name] = N'choice2'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type7 DROP COLUMN choice2
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type7 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type7') AND [c].[name] = N'choice3'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type7 DROP COLUMN choice3
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type7 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type7') AND [c].[name] = N'choice4'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type7 DROP COLUMN choice4
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type7 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type7') AND [c].[name] = N'choice5'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type7 DROP COLUMN choice5
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type7 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type7') AND [c].[name] = N'choice6'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type7 DROP COLUMN choice6
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type7 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type7') AND [c].[name] = N'choice7'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type7 DROP COLUMN choice7
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE question_type7 DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'question_type7') AND [c].[name] = N'choice8'
EXEC sp_executesql @sql
GO

ALTER TABLE question_type7 DROP COLUMN choice8
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141016-kc-12', 'kelvin.chan', 'nschema/102.changeLog.xml', GETDATE(), 205, '8:e580b491a7d42c3dfd00afbe886ed037', 'dropColumn columnName=choice1, tableName=question_type1; dropColumn columnName=choice2, tableName=question_type1; dropColumn columnName=choice3, tableName=question_type1; dropColumn columnName=choice4, tableName=question_type1; dropColumn columnNa...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141028-gl-1::garbo.lim
IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'i_testquestion'
            )
            DROP TRIGGER i_testquestion
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'u_testquestion'
            )
            DROP TRIGGER u_testquestion
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'd_testquestion'
            )
            DROP TRIGGER d_testquestion
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141028-gl-1', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 206, '8:fb42fa93dc32322794c74c4f021eefb1', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141028-gl-3::garbo.lim
ALTER TABLE testquestion ADD guess_temp nvarchar(1000)
GO

ALTER TABLE testquestion ADD answer_temp nvarchar(1000)
GO

ALTER TABLE testquestion ADD choice_order_temp nvarchar(1000)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141028-gl-3', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 207, '8:eeefabbc4a7648a2b0c3fbff802dc1f0', 'addColumn tableName=testquestion', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141028-gl-4::garbo.lim
ALTER TABLE testquestion DROP CONSTRAINT FK_testquestion_questionid
GO

ALTER TABLE testquestion DROP CONSTRAINT PK_TESTQUESTION
GO

UPDATE testquestion SET answer_temp = LTRIM(RTRIM(CAST(answer AS NVARCHAR))), guess_temp = LTRIM(RTRIM(CAST(guess AS NVARCHAR))), choice_order_temp = choice_order
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141028-gl-4', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 208, '8:c4b26218e82470e868004f947231a323', 'dropForeignKeyConstraint baseTableName=testquestion, constraintName=FK_testquestion_questionid; dropPrimaryKey constraintName=PK_TESTQUESTION, tableName=testquestion; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141028-gl-7::garbo.lim
UPDATE testquestion SET answer_temp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(answer_temp, '1', '1;'), '2', '2;'), '3', '3;'), '4', '4;'), '5', '5;'), '6', '6;'), '7', '7;'), '8', '8;'),
		                                guess_temp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(guess_temp, '1', '1;'), '2', '2;'), '3', '3;'), '4', '4;'), '5', '5;'), '6', '6;'), '7', '7;'), '8', '8;')
		        WHERE qtype = 7
GO

UPDATE testquestion SET choice_order_temp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(choice_order_temp, '0', '0;'), '1', '1;'), '2', '2;'), '3', '3;'), '4', '4;'), '5', '5;'), '6', '6;'), '7', '7;'), '8', '8;'), '9', '9;') 
				WHERE qtype = 7 OR qtype = 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141028-gl-7', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 209, '8:c1b664cce74bf325fefe1d66409354b8', 'sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141028-gl-9::garbo.lim
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE testquestion DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'testquestion') AND [c].[name] = N'answer'
EXEC sp_executesql @sql
GO

ALTER TABLE testquestion DROP COLUMN answer
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE testquestion DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'testquestion') AND [c].[name] = N'guess'
EXEC sp_executesql @sql
GO

ALTER TABLE testquestion DROP COLUMN guess
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE testquestion DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'testquestion') AND [c].[name] = N'choice_order'
EXEC sp_executesql @sql
GO

ALTER TABLE testquestion DROP COLUMN choice_order
GO

exec sp_rename 'testquestion.answer_temp', 'answer'
GO

exec sp_rename 'testquestion.guess_temp', 'guess'
GO

exec sp_rename 'testquestion.choice_order_temp', 'choice_order'
GO

ALTER TABLE testquestion ADD CONSTRAINT PK_TESTQUESTION PRIMARY KEY (instanceid, sectionnum, questionnum)
GO

ALTER TABLE testquestion ADD CONSTRAINT FK_testquestion_questionid FOREIGN KEY (questionid) REFERENCES question (questionid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141028-gl-9', 'garbo.lim', 'nschema/102.changeLog.xml', GETDATE(), 210, '8:251492c7beb63b4f217142d87097e2dc', 'dropColumn columnName=answer, tableName=testquestion; dropColumn columnName=guess, tableName=testquestion; dropColumn columnName=choice_order, tableName=testquestion; renameColumn newColumnName=answer, oldColumnName=answer_temp, tableName=testques...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141024-kc-1::kelvin.chan
ALTER TABLE question_choices ADD last_updated datetime
GO

ALTER TABLE question_choices ADD updater_legal_name nvarchar(250)
GO

ALTER TABLE question_choices ADD update_meaning_text nvarchar(500)
GO

ALTER TABLE question_choices ADD last_updater_uid nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141024-kc-1', 'kelvin.chan', 'nschema/102.changeLog.xml', GETDATE(), 211, '8:69267db8252af618d7bba4a5e48f85ae', 'addColumn tableName=question_choices', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141029-sm-1::simon.mok
ALTER TABLE report_wizard_aggregate DROP CONSTRAINT FK_COLUMN_REF_REPORT_AGGREGATE
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE report_wizard_aggregate DROP CONSTRAINT ' + QUOTENAME([kc].[name]) FROM [sys].[key_constraints] AS [kc] WHERE [kc].[parent_object_id] = OBJECT_ID(N'report_wizard_aggregate') AND [kc].[type] = 'PK'
EXEC sp_executesql @sql
GO

ALTER TABLE report_wizard_aggregate ALTER COLUMN report_column_key nvarchar(50) NULL
GO

ALTER TABLE report_wizard_aggregate ALTER COLUMN report_column_key nvarchar(100)
GO

ALTER TABLE report_wizard_aggregate ALTER COLUMN report_column_key nvarchar(100) NOT NULL
GO

ALTER TABLE report_wizard_aggregate ADD PRIMARY KEY (report_id, report_column_key)
GO

ALTER TABLE report_wizard_column DROP CONSTRAINT FK_COLUMN_REF_REPORT_WIZARD
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE report_wizard_column DROP CONSTRAINT ' + QUOTENAME([kc].[name]) FROM [sys].[key_constraints] AS [kc] WHERE [kc].[parent_object_id] = OBJECT_ID(N'report_wizard_column') AND [kc].[type] = 'PK'
EXEC sp_executesql @sql
GO

ALTER TABLE report_wizard_column ALTER COLUMN report_column_key nvarchar(50) NULL
GO

ALTER TABLE report_wizard_column ALTER COLUMN report_column_key nvarchar(100)
GO

ALTER TABLE report_wizard_column ALTER COLUMN report_column_key nvarchar(100) NOT NULL
GO

ALTER TABLE report_wizard_column ADD PRIMARY KEY (report_id, report_column_key)
GO

ALTER TABLE report_wizard_column ADD CONSTRAINT FK_COLUMN_REF_REPORT_WIZARD FOREIGN KEY (report_id) REFERENCES report_wizard (report_id)
GO

ALTER TABLE report_wizard_aggregate ADD CONSTRAINT FK_COLUMN_REF_REPORT_AGGREGATE FOREIGN KEY (report_id, report_column_key) REFERENCES report_wizard_column (report_id, report_column_key)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141029-sm-1', 'simon.mok', 'nschema/102.changeLog.xml', GETDATE(), 212, '8:3f6ab943e28e2811b2a9ac8d482dc32a', 'dropForeignKeyConstraint baseTableName=report_wizard_aggregate, constraintName=FK_COLUMN_REF_REPORT_AGGREGATE; dropPrimaryKey tableName=report_wizard_aggregate; dropNotNullConstraint columnName=report_column_key, tableName=report_wizard_aggregate;...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/102.changeLog.xml::20141030-js-1::janice.sung
ALTER TABLE learningObject ALTER COLUMN picture varchar(500)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141030-js-1', 'janice.sung', 'nschema/102.changeLog.xml', GETDATE(), 213, '8:337806edd56b5c9845378a1b05759720', 'modifyDataType columnName=picture, tableName=learningObject', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::13Oct2014-sn-1::sam.ng
UPDATE ekpversion SET dblevel = 103, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('13Oct2014-sn-1', 'sam.ng', 'nschema/103.changeLog.xml', GETDATE(), 214, '8:cf9a185921c17d2e5f1395fd9b924c75', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20140915-gl-1::garbo.lim
IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'i_course_competency_exp'
            )
            DROP TRIGGER i_course_competency_exp
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'u_course_competency_exp'
            )
            DROP TRIGGER u_course_competency_exp
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'd_course_competency_exp'
            )
            DROP TRIGGER d_course_competency_exp
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-gl-1', 'garbo.lim', 'nschema/103.changeLog.xml', GETDATE(), 215, '8:a827d2ecd98eedc1761c567e8c3c0149', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20140915-gl-2::garbo.lim
ALTER TABLE course_competency_exp DROP CONSTRAINT PK_COURSE_COMPETENCY_EXP
GO

ALTER TABLE course_competency_exp ADD id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-gl-2', 'garbo.lim', 'nschema/103.changeLog.xml', GETDATE(), 216, '8:42935e78c87f0dcc6224c6818702772e', 'dropPrimaryKey constraintName=PK_COURSE_COMPETENCY_EXP, tableName=course_competency_exp; addColumn tableName=course_competency_exp', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20140915-gl-4::garbo.lim
DECLARE @counter nvarchar(85) 
   				SET @counter = '0' 
   				UPDATE course_competency_exp 
   				SET @counter = id = CAST(@counter AS INT) + 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-gl-4', 'garbo.lim', 'nschema/103.changeLog.xml', GETDATE(), 217, '8:6ab1b362032dc9f67d6052554a7cdde5', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20140915-gl-7::garbolim
ALTER TABLE course_competency_exp ALTER COLUMN id nvarchar(85) NOT NULL
GO

ALTER TABLE course_competency_exp ADD CONSTRAINT PK_COURSE_COMPETENCY_EXP PRIMARY KEY (id)
GO

CREATE TABLE course_competency_exp_tran (id nvarchar(85), course_comp_exp_id nvarchar(85) NOT NULL, transcript_id nvarchar(15) NOT NULL)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-gl-7', 'garbolim', 'nschema/103.changeLog.xml', GETDATE(), 218, '8:28739792d0a83f7f330f9f1cd8ee22bc', 'addNotNullConstraint columnName=id, tableName=course_competency_exp; addPrimaryKey constraintName=PK_COURSE_COMPETENCY_EXP, tableName=course_competency_exp; createTable tableName=course_competency_exp_tran', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20140915-gl-9::garbolim
insert into course_competency_exp_tran (course_comp_exp_id, transcript_id)
			select  id, transcript_id from course_competency_exp
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-gl-9', 'garbolim', 'nschema/103.changeLog.xml', GETDATE(), 219, '8:d5cfad23b88a7e7a07599f77ed24dac9', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20140915-gl-11::garbolim
DECLARE @counter nvarchar(85) 
   				SET @counter = '0' 
   				UPDATE course_competency_exp_tran 
   				SET @counter = id = CAST(@counter AS INT) + 1
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-gl-11', 'garbolim', 'nschema/103.changeLog.xml', GETDATE(), 220, '8:d0e61f458e6413ac9357629ab1dcbf3b', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20140915-gl-13::garbolim
ALTER TABLE course_competency_exp_tran ALTER COLUMN id nvarchar(85) NOT NULL
GO

ALTER TABLE course_competency_exp_tran ADD CONSTRAINT PK_COURSE_COMPETENCY_EXP_TRAN PRIMARY KEY (id)
GO

ALTER TABLE course_competency_exp_tran ADD CONSTRAINT UK_COURSE_COMPETENCY_EXP_TRAN UNIQUE (course_comp_exp_id, transcript_id)
GO

ALTER TABLE course_competency_exp ADD complete_date datetime
GO

update course_competency_exp set complete_date = (select case when finishdate is null then startdate else finishdate end from transcript where transcript.transcriptid = course_competency_exp.transcript_id)
GO

DELETE FROM course_competency_exp WHERE complete_date IS NULL
GO

ALTER TABLE course_competency_exp ALTER COLUMN complete_date datetime NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140915-gl-13', 'garbolim', 'nschema/103.changeLog.xml', GETDATE(), 221, '8:db27c9bb5e41d884f1deac9b307faea0', 'addNotNullConstraint columnName=id, tableName=course_competency_exp_tran; addPrimaryKey constraintName=PK_COURSE_COMPETENCY_EXP_TRAN, tableName=course_competency_exp_tran; addUniqueConstraint constraintName=UK_COURSE_COMPETENCY_EXP_TRAN, tableName...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141030-dc-1::derek.chan
ALTER TABLE equivalency_rules ADD contains_program char(1) CONSTRAINT DF_equivalency_rules_contains_program DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141030-dc-1', 'derek.chan', 'nschema/103.changeLog.xml', GETDATE(), 222, '8:64253e58ab8526aec3510364a3e6e754', 'addColumn tableName=equivalency_rules', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141031-rr-1::ray.ruff
ALTER TABLE code_orgstructure ADD homepage_template_id nvarchar(15)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141031-rr-1', 'ray.ruff', 'nschema/103.changeLog.xml', GETDATE(), 223, '8:f7f0778488c0cdf922e16da72c1fc52d', 'addColumn tableName=code_orgstructure', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141105-kh-0::kelvin.hung
DROP VIEW aggregated_transcript_vw
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141105-kh-0', 'kelvin.hung', 'nschema/103.changeLog.xml', GETDATE(), 224, '8:4d08b4bf1ad648d71c6f1a65cd9794cc', 'dropView viewName=aggregated_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141105-kh-1::kelvin.hung
IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'i_submod_tran_subt'
            )
            DROP TRIGGER i_submod_tran_subt
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'u_submod_tran_subt'
            )
            DROP TRIGGER u_submod_tran_subt
GO

IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'd_submod_tran_subt'
            )
            DROP TRIGGER d_submod_tran_subt
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141105-kh-1', 'kelvin.hung', 'nschema/103.changeLog.xml', GETDATE(), 225, '8:574805108832a00ffe1c04cc09479557', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141105-kh-2::kelvin.hung
DROP TABLE submod_tran_subt
GO

CREATE TABLE submod_tran_subt (id nvarchar(85) NOT NULL, learner_id nvarchar(85) NOT NULL, substitute_tran_id nvarchar(15) NOT NULL, substitute_learning_id nvarchar(85) NOT NULL, target_tran_id nvarchar(15) NOT NULL, target_learning_id nvarchar(85) NOT NULL, program_tran_id nvarchar(15) NOT NULL, program_learning_id nvarchar(85) NOT NULL)
GO

ALTER TABLE submod_tran_subt ADD CONSTRAINT PK_SUBMOD_TRAN_SUBT PRIMARY KEY (id)
GO

ALTER TABLE submod_tran_subt ADD CONSTRAINT FK_SUBMOD_TRAN_LEARNERID FOREIGN KEY (learner_id) REFERENCES users_assignment (userid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141105-kh-2', 'kelvin.hung', 'nschema/103.changeLog.xml', GETDATE(), 226, '8:de464cea221397de8d059424a13986a0', 'dropTable tableName=submod_tran_subt; createTable tableName=submod_tran_subt; addPrimaryKey constraintName=PK_SUBMOD_TRAN_SUBT, tableName=submod_tran_subt; addForeignKeyConstraint baseTableName=submod_tran_subt, constraintName=FK_SUBMOD_TRAN_LEARN...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141105-kh-3::kelvin.hung
CREATE VIEW aggregated_transcript_vw AS SELECT
				    t1.transcriptid,
				    a2.userid,
					t1.learningid,
				    t1.currid,
				    t1.learningType,
				    t1.eventid,
				    t1.startdate,
				    t1.finishdate,
				    t1.finalgrade,
				    t1.studentstatus,
				    t1.progress,
				    t1.assessDate,
				    t1.attendance,
				    t1.perfGradeid,
				    t1.sessionid,
				    t1.creditTranscriptLink,
				    t1.curriculumTranscriptLink,
				    t1.assessmentscore,
				    t1.requestSource,
				    t1.contentSessionID,
				    t1.certSessionID,
				    t1.enrollmentDate,
				    t1.remindercount,
				    t1.costcenter,
				    t1.confirmation_status,
				    t1.reopen,
				    t1.isOpen,
				    t1.pre_assess_score,
				    t1.post_assess_score,
				    t1.usage_expiration_date,
				    t1.quick_eval,
				    t1.notifyemail,
				    t1.completion_deadline,
				    t1.deadline_expired,
				    t1.revision_number,
				    t1.ntraininghours,
				    t1.allowDappraiserAssess,
				    t1.offline_key,
				    t1.mekp_sync,
				    t1.send_notif_ew,
				    t1.update_meaning,
				    t1.last_updated,
				    t1.updater_legal_name,
				    t1.update_meaning_text,
				    t1.last_updater_uid,
				    t1.creator_uid,
				    t1.datecreated,
				    t1.curr_total_time,
				    t1.attempts_allowed,
				    t1.grading_method,
				    t1.attempts_count,
				    t1.completion_method,
				    t1.final_score,
				    t1.weighting,
				    t1.weightedscore,
					case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = t1.transcriptid) then 'Y' else 'N' end as sub_learning,
					case when a2.userid = t1.userid then 'Y' else 'N' end belongsToLearnerAssignment
				  FROM transcript t1
				  inner join users_assignment a1 on a1.userid = t1.userid
				  inner join users_assignment a2 on a2.user_meta_id = a1.user_meta_id
				  WHERE t1.studentstatus NOT IN (8, 9, 10)
		
		union all
		
		SELECT
				    a.transcriptid,
				    a2.userid,
				    a.learningid,
				    a.currid,
				    a.learningType,
				    a.eventid,
				    b.startdate,
				    b.finishdate,
				    b.finalgrade,
				    b.studentstatus,
				    b.progress,
				    b.assessDate,
				    b.attendance,
				    b.perfGradeid,
				    a.sessionid,
				    CASE WHEN 1=1 THEN NULL ELSE '' END as creditTranscriptLink,
				    a.curriculumTranscriptLink,
				    b.assessmentscore,
				    a.requestSource,
				    b.contentSessionID,
				    b.certSessionID,
				    a.enrollmentDate,
				    b.remindercount,
				    b.costcenter,
				    b.confirmation_status,
				    b.reopen,
				    a.isOpen,
				    b.pre_assess_score,
				    b.post_assess_score,
				    b.usage_expiration_date,
				    b.quick_eval,
				    b.notifyemail,
				    b.completion_deadline,
				    b.deadline_expired,
				    b.revision_number,
				    b.ntraininghours,
				    b.allowDappraiserAssess,
				    b.offline_key,
				    b.mekp_sync,
				    b.send_notif_ew,
				    b.update_meaning,
				    b.last_updated,
				    b.updater_legal_name,
				    a.update_meaning_text,
				    a.last_updater_uid,
				    a.creator_uid,
				    a.datecreated,
				    b.curr_total_time,
				    b.attempts_allowed,
				    b.grading_method,
				    b.attempts_count,
				    b.completion_method,
				    b.final_score,
				    b.weighting,
				    b.weightedscore,
				    case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = b.transcriptid) then 'Y' else 'N' end as sub_learning,
					case when a2.userid = a.userid then 'Y' else 'N' end belongsToLearnerAssignment
				  FROM transcript a
				  inner join transcript b on b.transcriptid = a.creditTranscriptLink
				  inner join users_assignment a1 on a1.userid = a.userid
				  inner join users_assignment a2 on a2.user_meta_id = a1.user_meta_id
				  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141105-kh-3', 'kelvin.hung', 'nschema/103.changeLog.xml', GETDATE(), 227, '8:cf0f2e33556a3a50ff0f1326f4f7e8be', 'createView viewName=aggregated_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141104-sn-2::sam.ng
insert into course_competency_exp (course_id, competency_id, user_id, expiry_date, transcript_id, id, complete_date) 
            	select exp.course_id, exp.competency_id, u2.userid, exp.expiry_date, exp.transcript_id, exp.id + u2.userid, exp.complete_date
            	from (select * from course_competency_exp) exp join users_assignment u on exp.user_id = u.userid join users_assignment u2 on u.user_meta_id = u2.user_meta_id
            	where exp.user_id <> u2.userid
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141104-sn-2', 'sam.ng', 'nschema/103.changeLog.xml', GETDATE(), 228, '8:011641e27207572bf9da88d8876e4e5d', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141111-sc-2::starry.chan
DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[' + @DynamicSchemaName + N'].[valid_users_vw]'))
    BEGIN
DECLARE @sql AS NVARCHAR(MAX) set @sql = N'CREATE VIEW [' + @DynamicSchemaName + N'].[valid_users_vw] AS SELECT ''This is a code stub which will be replaced by an Alter Statement'' as [code_stub]';
EXEC sp_executesql @sql;
END
GO

ALTER VIEW valid_users_vw AS select *
		  	from users
		  	where currentstatus NOT IN (3, 6)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141111-sc-2', 'starry.chan', 'nschema/103.changeLog.xml', GETDATE(), 229, '8:6d6d19359d5f9b9df2b06357685c0516', 'createView viewName=valid_users_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141118-sc-1::starry.chan
ALTER TABLE language_custom_string ALTER COLUMN string_value nvarchar(2000)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141118-sc-1', 'starry.chan', 'nschema/103.changeLog.xml', GETDATE(), 230, '8:f58ede5f835f4cbe72ba767677369ed9', 'modifyDataType columnName=string_value, tableName=language_custom_string', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141120-dr-1::dreidel.regala
ALTER TABLE prereq ADD section int CONSTRAINT DF_prereq_section DEFAULT 0 NOT NULL
GO

ALTER TABLE prereq ADD logic char(1) CONSTRAINT DF_prereq_logic DEFAULT 'Y' NOT NULL
GO

ALTER TABLE prereq DROP CONSTRAINT PK_PREREQ
GO

ALTER TABLE prereq ADD CONSTRAINT PK_PREREQ PRIMARY KEY (learningid, prereqid, section)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141120-dr-1', 'dreidel.regala', 'nschema/103.changeLog.xml', GETDATE(), 231, '8:93de33eaef1156bfd4fed1f6fdea1c1f', 'addColumn tableName=prereq; dropPrimaryKey constraintName=PK_PREREQ, tableName=prereq; addPrimaryKey constraintName=PK_PREREQ, tableName=prereq', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141121-dr-3::dreidel.regala
ALTER TABLE prereq DROP CONSTRAINT DF_prereq_logic
GO

ALTER TABLE prereq ADD CONSTRAINT DF_prereq_logic DEFAULT 'A' for logic
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141121-dr-3', 'dreidel.regala', 'nschema/103.changeLog.xml', GETDATE(), 232, '8:7274db2945cd88efdfce4b0df08bba8a', 'sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141126-rr-1::ray.ruff
CREATE NONCLUSTERED INDEX NUK_USER_COMP_ASSIGNEDBY ON user_competency(assigned_by)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141126-rr-1', 'ray.ruff', 'nschema/103.changeLog.xml', GETDATE(), 233, '8:0c7ab9e964fd37f06bcd18c768a904ed', 'createIndex indexName=NUK_USER_COMP_ASSIGNEDBY, tableName=user_competency', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141126-rr-2::ray.ruff
CREATE NONCLUSTERED INDEX NUK_AICC_ATTEMPTS_SESSION ON AICC_Core_Trans_Attempts(session_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141126-rr-2', 'ray.ruff', 'nschema/103.changeLog.xml', GETDATE(), 234, '8:7c60bca63969d6bfafb6adc97a734eb8', 'createIndex indexName=NUK_AICC_ATTEMPTS_SESSION, tableName=AICC_Core_Trans_Attempts', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141126-rr-3::ray.ruff
CREATE NONCLUSTERED INDEX NUK_TESTINSTANCE_SESSION ON testinstance(sessionID)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141126-rr-3', 'ray.ruff', 'nschema/103.changeLog.xml', GETDATE(), 235, '8:a30f79c3c5af2200715e86cbd6217f1b', 'createIndex indexName=NUK_TESTINSTANCE_SESSION, tableName=testinstance', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141126-rr-4::ray.ruff
CREATE NONCLUSTERED INDEX NUK_AICC_NODE_ATTEMPTS_SESSION ON AICC_Core_Trans_Node_Attempts(sco_session_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141126-rr-4', 'ray.ruff', 'nschema/103.changeLog.xml', GETDATE(), 236, '8:d9d3f9f7202db4b8325f67f7ec521ec1', 'createIndex indexName=NUK_AICC_NODE_ATTEMPTS_SESSION, tableName=AICC_Core_Trans_Node_Attempts', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141125-yt-1::yan.tung
update prereq set prereqcontrol = lo.prereqcontrol, section = 0, logic = lo.businesslogic
			from prereq as p inner join
			(
			SELECT learningid
				,CASE 
					WHEN prereqcontrol >= 100
						THEN prereqcontrol - 100
					ELSE prereqcontrol
					END as prereqcontrol
				,CASE 
					WHEN prereqcontrol >= 100
						THEN 'O'
					ELSE 'A'
					END as businesslogic
			FROM learningObject
			WHERE prereqcontrol IS NOT NULL
			) as lo
			on p.learningid = lo.learningid
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141125-yt-1', 'yan.tung', 'nschema/103.changeLog.xml', GETDATE(), 237, '8:26b70f79d54af9622ff69ecc098c9b5b', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141128-jw-1::joe.wong
INSERT INTO permissions (objectid, objecttype, permissioncode, identityid, identitytype, operator, count) VALUES ('R264', 'RT', 1, '*ROOT*', 'O', 'OR', 1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141128-jw-1', 'joe.wong', 'nschema/103.changeLog.xml', GETDATE(), 238, '8:2835f73c2330a462159bb03c10888b8e', 'insert tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141208-gl-1::garbo.lim
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE course_competency_exp DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'course_competency_exp') AND [c].[name] = N'transcript_id'
EXEC sp_executesql @sql
GO

ALTER TABLE course_competency_exp DROP COLUMN transcript_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141208-gl-1', 'garbo.lim', 'nschema/103.changeLog.xml', GETDATE(), 239, '8:8152dae4a7c4269f4517be85abd0c555', 'dropColumn columnName=transcript_id, tableName=course_competency_exp', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20141229-rr-1::ray.ruff
ALTER TABLE accounts ADD charge_code nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141229-rr-1', 'ray.ruff', 'nschema/103.changeLog.xml', GETDATE(), 240, '8:d20285cdd2452425c81056596d37af87', 'addColumn tableName=accounts', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20150119-sn-1::sam.ng
CREATE TABLE resolved_permissions_orgtype (userid nvarchar(85) NOT NULL, objectid nvarchar(85) NOT NULL, orgid nvarchar(85) NOT NULL, objecttype nvarchar(5) NOT NULL, permissioncode int NOT NULL)
GO

ALTER TABLE resolved_permissions_orgtype ADD CONSTRAINT PK_RESOLVED_PERMS_ORGTYPE PRIMARY KEY (objecttype, permissioncode, userid, objectid, orgid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150119-sn-1', 'sam.ng', 'nschema/103.changeLog.xml', GETDATE(), 241, '8:6c85270505afe41110ac97db705cf24e', 'createTable tableName=resolved_permissions_orgtype; addPrimaryKey constraintName=PK_RESOLVED_PERMS_ORGTYPE, tableName=resolved_permissions_orgtype', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20150119-sn-2::sam.ng
CREATE TABLE resolved_permissions (userid nvarchar(85) NOT NULL, objectid nvarchar(85) NOT NULL, objecttype nvarchar(5) NOT NULL, is_write_only char(1) NOT NULL)
GO

ALTER TABLE resolved_permissions ADD CONSTRAINT PK_RESOLVED_PERMISSIONS PRIMARY KEY (objecttype, is_write_only, userid, objectid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150119-sn-2', 'sam.ng', 'nschema/103.changeLog.xml', GETDATE(), 242, '8:b5711665366e00225cbe8e59606076c8', 'createTable tableName=resolved_permissions; addPrimaryKey constraintName=PK_RESOLVED_PERMISSIONS, tableName=resolved_permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20150119-sn-3::sam.ng
CREATE TABLE resolved_permissions_validated (objecttype nvarchar(5) NOT NULL, is_write_only char(1) NOT NULL)
GO

ALTER TABLE resolved_permissions_validated ADD CONSTRAINT PK_RESOLVED_PERMS_STATUS PRIMARY KEY (objecttype, is_write_only)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150119-sn-3', 'sam.ng', 'nschema/103.changeLog.xml', GETDATE(), 243, '8:b70c5b1e7d015c7e8eb85cd724f7fbe3', 'createTable tableName=resolved_permissions_validated; addPrimaryKey constraintName=PK_RESOLVED_PERMS_STATUS, tableName=resolved_permissions_validated', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20150119-sn-4::sam.ng
CREATE TABLE transcript_equivalent (userid nvarchar(85) NOT NULL, user_meta_id nvarchar(85) NOT NULL, learningid nvarchar(85) NOT NULL, revision_number int NOT NULL, rule_group_id nvarchar(85) NOT NULL, learningType nvarchar(1) NOT NULL, minFinishDate datetime, sortableMinFinishDate datetime, finishdate datetime, effectiveDate datetime)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150119-sn-4', 'sam.ng', 'nschema/103.changeLog.xml', GETDATE(), 244, '8:8c6e12b2dd2f5157a8ba27b5d3190ebc', 'createTable tableName=transcript_equivalent', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/103.changeLog.xml::20150119-sn-5::sam.ng
CREATE TABLE transcript_equivalent_status (is_effective char(1) NOT NULL)
GO

ALTER TABLE transcript_equivalent_status ADD CONSTRAINT PK_TRAN_EQUIV_STATUS PRIMARY KEY (is_effective)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150119-sn-5', 'sam.ng', 'nschema/103.changeLog.xml', GETDATE(), 245, '8:92ebea8cebf172db1743fd34150e6233', 'createTable tableName=transcript_equivalent_status; addPrimaryKey constraintName=PK_TRAN_EQUIV_STATUS, tableName=transcript_equivalent_status', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::30Dec2014-sg-1::shravan.gurrala
UPDATE ekpversion SET dblevel = 110, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('30Dec2014-sg-1', 'shravan.gurrala', 'nschema/110.changeLog.xml', GETDATE(), 246, '8:eb511c99176c0fb39e4f18d800871324', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150102-js-1::janice.sung
CREATE TABLE transcript_substatus (id nvarchar(85) NOT NULL, transcript_status int NOT NULL, substatus_code nvarchar(255) NOT NULL, substatus_name nvarchar(255), last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

ALTER TABLE transcript_substatus ADD CONSTRAINT PK_TRANSCRIPT_SUBSTATUS PRIMARY KEY (id)
GO

ALTER TABLE transcript_substatus ADD CONSTRAINT UK_TRANSCRIPT_SUBSTATUS UNIQUE (substatus_code)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150102-js-1', 'janice.sung', 'nschema/110.changeLog.xml', GETDATE(), 247, '8:d3bc890b710d3d616f58ceb5d229ebb8', 'createTable tableName=transcript_substatus; addPrimaryKey constraintName=PK_TRANSCRIPT_SUBSTATUS, tableName=transcript_substatus; addUniqueConstraint constraintName=UK_TRANSCRIPT_SUBSTATUS, tableName=transcript_substatus', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141230-kc-1::kelvin.chan
IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'i_courseDownloads'
            )
            DROP TRIGGER i_courseDownloads
GO

IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'u_courseDownloads'
            )
            DROP TRIGGER u_courseDownloads
GO

IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'd_courseDownloads'
            )
            DROP TRIGGER d_courseDownloads
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141230-kc-1', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 248, '8:34418fc0abede7ec2e6ba0f7dd056390', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141230-kc-2::kelvin.chan
ALTER TABLE courseDownloads ADD allowRefAccess char(1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141230-kc-2', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 249, '8:5bd7adb809c267a99aacfbc6ceef64b1', 'addColumn tableName=courseDownloads', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141230-kc-3::kelvin.chan
UPDATE courseDownloads SET allowRefAccess = (SELECT allowRefAccess FROM course WHERE course.courseid = courseDownloads.courseid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141230-kc-3', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 250, '8:e7dc62a9bfcfbe4f6c56869a707e5072', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141230-kc-4::kelvin.chan
UPDATE courseDownloads SET allowRefAccess = (SELECT allowreferenceaccess FROM curriculum WHERE curriculum.currid = courseDownloads.courseid) WHERE allowRefAccess is null
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141230-kc-4', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 251, '8:e003cb9f63764664392d1bbc89249a96', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141230-kc-5::kelvin.chan
UPDATE courseDownloads SET allowRefAccess = 'Y' WHERE allowRefAccess is null
GO

ALTER TABLE courseDownloads ALTER COLUMN allowRefAccess char(1) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141230-kc-5', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 252, '8:dcdd40a6fdb85e210f79e919fee7a830', 'sql; addNotNullConstraint columnName=allowRefAccess, tableName=courseDownloads', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141230-kc-6::kelvin.chan
IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'i_course'
            )
            DROP TRIGGER i_course
GO

IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'u_course'
            )
            DROP TRIGGER u_course
GO

IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'd_course'
            )
            DROP TRIGGER d_course
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141230-kc-6', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 253, '8:31be7692b917367ba7cc8c273736a107', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141230-kc-7::kelvin.chan
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE course DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'course') AND [c].[name] = N'allowRefAccess'
EXEC sp_executesql @sql
GO

ALTER TABLE course DROP COLUMN allowRefAccess
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141230-kc-7', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 254, '8:b1b632f265c1885036e19ca1d617276a', 'dropColumn columnName=allowRefAccess, tableName=course', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141230-kc-8::kelvin.chan
IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'i_curriculum'
            )
            DROP TRIGGER i_curriculum
GO

IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'u_curriculum'
            )
            DROP TRIGGER u_curriculum
GO

IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'd_curriculum'
            )
            DROP TRIGGER d_curriculum
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141230-kc-8', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 255, '8:33dcfd8473d4b64807fa6778490911af', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141230-kc-9::kelvin.chan
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE curriculum DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'curriculum') AND [c].[name] = N'allowreferenceaccess'
EXEC sp_executesql @sql
GO

ALTER TABLE curriculum DROP COLUMN allowreferenceaccess
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141230-kc-9', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 256, '8:dfe159ed4727f97cee0b8425453261ad', 'dropColumn columnName=allowreferenceaccess, tableName=curriculum', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20141231-vn-1::vincent.ni
INSERT INTO permissions (objectid, objecttype, permissioncode, identityid, identitytype, operator, count) VALUES ('R418', 'RT', 1, '*ROOT*', 'O', 'OR', 1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20141231-vn-1', 'vincent.ni', 'nschema/110.changeLog.xml', GETDATE(), 257, '8:a1524b78ce81df4877d335b2f72dee3a', 'insert tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150105-rr-1::ray.ruff
CREATE NONCLUSTERED INDEX NUK_QUESTION_ARID ON question(archivereferenceid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150105-rr-1', 'ray.ruff', 'nschema/110.changeLog.xml', GETDATE(), 258, '8:959642e359904d7bb1bde31817dd3a72', 'createIndex indexName=NUK_QUESTION_ARID, tableName=question', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150105-rr-2::ray.ruff
CREATE NONCLUSTERED INDEX NUK_CODE_POOL_PARENT ON code_pool(parentpoolid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150105-rr-2', 'ray.ruff', 'nschema/110.changeLog.xml', GETDATE(), 259, '8:4c22af306696d7abe0738e7287a13aec', 'createIndex indexName=NUK_CODE_POOL_PARENT, tableName=code_pool', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150105-rr-3::ray.ruff
CREATE NONCLUSTERED INDEX NUK_QP_POOLID ON question_pools(poolid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150105-rr-3', 'ray.ruff', 'nschema/110.changeLog.xml', GETDATE(), 260, '8:e6c2ee493610dd6299081a1092ff7ab3', 'createIndex indexName=NUK_QP_POOLID, tableName=question_pools', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150106-js-1::janice.sung
ALTER TABLE transcript ADD student_substatus nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150106-js-1', 'janice.sung', 'nschema/110.changeLog.xml', GETDATE(), 261, '8:09439fad5a6283dc2a7d0bb20131f7bd', 'addColumn tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150106-js-2::janice.sung
DROP VIEW valid_transcript_vw
GO

CREATE VIEW valid_transcript_vw AS SELECT
		    transcriptid,
		    userid,
		    learningid,
		    currid,
		    learningType,
		    eventid,
		    startdate,
		    finishdate,
		    finalgrade,
		    studentstatus,
		    progress,
		    assessDate,
		    attendance,
		    perfGradeid,
		    sessionid,
		    creditTranscriptLink,
		    curriculumTranscriptLink,
		    assessmentscore,
		    requestSource,
		    contentSessionID,
		    certSessionID,
		    enrollmentDate,
		    remindercount,
		    costcenter,
		    confirmation_status,
		    reopen,
		    isOpen,
		    pre_assess_score,
		    post_assess_score,
		    usage_expiration_date,
		    quick_eval,
		    notifyemail,
		    completion_deadline,
		    deadline_expired,
		    revision_number,
		    ntraininghours,
		    allowDappraiserAssess,
		    offline_key,
		    mekp_sync,
		    send_notif_ew,
		    update_meaning,
		    last_updated,
		    updater_legal_name,
		    update_meaning_text,
		    last_updater_uid,
		    creator_uid,
		    datecreated,
		    curr_total_time,
		    attempts_allowed,
		    grading_method,
		    attempts_count,
		    completion_method,
		    final_score,
		    weighting,
		    weightedscore,
		    sub_learning,
		    actual_training_time_sec,
		    student_substatus
		  FROM transcript
		  WHERE studentstatus NOT IN (8, 9, 10)
		  UNION ALL
		  SELECT
		    a.transcriptid,
		    a.userid,
		    a.learningid,
		    a.currid,
		    a.learningType,
		    a.eventid,
		    b.startdate,
		    b.finishdate,
		    b.finalgrade,
		    b.studentstatus,
		    b.progress,
		    b.assessDate,
		    b.attendance,
		    b.perfGradeid,
		    a.sessionid,
		    CASE WHEN 1=1 THEN NULL ELSE '' END as creditTranscriptLink,
		    a.curriculumTranscriptLink,
		    b.assessmentscore,
		    a.requestSource,
		    b.contentSessionID,
		    b.certSessionID,
		    a.enrollmentDate,
		    b.remindercount,
		    b.costcenter,
		    b.confirmation_status,
		    b.reopen,
		    a.isOpen,
		    b.pre_assess_score,
		    b.post_assess_score,
		    b.usage_expiration_date,
		    b.quick_eval,
		    b.notifyemail,
		    b.completion_deadline,
		    b.deadline_expired,
		    b.revision_number,
		    b.ntraininghours,
		    b.allowDappraiserAssess,
		    b.offline_key,
		    b.mekp_sync,
		    b.send_notif_ew,
		    b.update_meaning,
		    b.last_updated,
		    b.updater_legal_name,
		    a.update_meaning_text,
		    a.last_updater_uid,
		    a.creator_uid,
		    a.datecreated,
		    b.curr_total_time,
		    b.attempts_allowed,
		    b.grading_method,
		    b.attempts_count,
		    b.completion_method,
		    b.final_score,
		    b.weighting,
		    b.weightedscore,
		    b.sub_learning,
		    b.actual_training_time_sec,
		    b.student_substatus
		  FROM transcript a, transcript b
		  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150106-js-2', 'janice.sung', 'nschema/110.changeLog.xml', GETDATE(), 262, '8:18a5f9fbe9acea0bc9842094c17bbfb4', 'dropView viewName=valid_transcript_vw; createView viewName=valid_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150108-dc-1::dennis.cheung
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE logical_domain DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'logical_domain') AND [c].[name] = N'logoff_url'
EXEC sp_executesql @sql
GO

ALTER TABLE logical_domain DROP COLUMN logoff_url
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150108-dc-1', 'dennis.cheung', 'nschema/110.changeLog.xml', GETDATE(), 263, '8:06e01661e5fa739da99e876630e9f663', 'dropColumn columnName=logoff_url, tableName=logical_domain', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150114-kh-1::kelvin.hung
DROP VIEW aggregated_transcript_vw
GO

CREATE VIEW aggregated_transcript_vw AS SELECT
		    t1.transcriptid,
		    a2.userid,
			t1.learningid,
		    t1.currid,
		    t1.learningType,
		    t1.eventid,
		    t1.startdate,
		    t1.finishdate,
		    t1.finalgrade,
		    t1.studentstatus,
		    tsub.substatus_code,
		    tsub.substatus_name,
		    t1.progress,
		    t1.assessDate,
		    t1.attendance,
		    t1.perfGradeid,
		    t1.sessionid,
		    t1.creditTranscriptLink,
		    t1.curriculumTranscriptLink,
		    t1.assessmentscore,
		    t1.requestSource,
		    t1.contentSessionID,
		    t1.certSessionID,
		    t1.enrollmentDate,
		    t1.remindercount,
		    t1.costcenter,
		    t1.confirmation_status,
		    t1.reopen,
		    t1.isOpen,
		    t1.pre_assess_score,
		    t1.post_assess_score,
		    t1.usage_expiration_date,
		    t1.quick_eval,
		    t1.notifyemail,
		    t1.completion_deadline,
		    t1.deadline_expired,
		    t1.revision_number,
		    t1.ntraininghours,
		    t1.allowDappraiserAssess,
		    t1.offline_key,
		    t1.mekp_sync,
		    t1.send_notif_ew,
		    t1.update_meaning,
		    t1.last_updated,
		    t1.updater_legal_name,
		    t1.update_meaning_text,
		    t1.last_updater_uid,
		    t1.creator_uid,
		    t1.datecreated,
		    t1.curr_total_time,
		    t1.attempts_allowed,
		    t1.grading_method,
		    t1.attempts_count,
		    t1.completion_method,
		    t1.final_score,
		    t1.weighting,
		    t1.weightedscore,
			case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = t1.transcriptid) then 'Y' else 'N' end as sub_learning,
			case when a2.userid = t1.userid then 'Y' else 'N' end belongsToLearnerAssignment
		  FROM transcript t1
			  inner join users_assignment a1 on a1.userid = t1.userid
			  inner join users_assignment a2 on a2.user_meta_id = a1.user_meta_id
			  left join transcript_substatus tsub on t1.student_substatus = tsub.id
		  WHERE t1.studentstatus NOT IN (8, 9, 10)
		  UNION ALL
		  SELECT
		    a.transcriptid,
		    a2.userid,
		    a.learningid,
		    a.currid,
		    a.learningType,
		    a.eventid,
		    b.startdate,
		    b.finishdate,
		    b.finalgrade,
		    b.studentstatus,
		    tsub.substatus_code,
		    tsub.substatus_name,
		    b.progress,
		    b.assessDate,
		    b.attendance,
		    b.perfGradeid,
		    a.sessionid,
		    CASE WHEN 1=1 THEN NULL ELSE '' END as creditTranscriptLink,
		    a.curriculumTranscriptLink,
		    b.assessmentscore,
		    a.requestSource,
		    b.contentSessionID,
		    b.certSessionID,
		    a.enrollmentDate,
		    b.remindercount,
		    b.costcenter,
		    b.confirmation_status,
		    b.reopen,
		    a.isOpen,
		    b.pre_assess_score,
		    b.post_assess_score,
		    b.usage_expiration_date,
		    b.quick_eval,
		    b.notifyemail,
		    b.completion_deadline,
		    b.deadline_expired,
		    b.revision_number,
		    b.ntraininghours,
		    b.allowDappraiserAssess,
		    b.offline_key,
		    b.mekp_sync,
		    b.send_notif_ew,
		    b.update_meaning,
		    b.last_updated,
		    b.updater_legal_name,
		    a.update_meaning_text,
		    a.last_updater_uid,
		    a.creator_uid,
		    a.datecreated,
		    b.curr_total_time,
		    b.attempts_allowed,
		    b.grading_method,
		    b.attempts_count,
		    b.completion_method,
		    b.final_score,
		    b.weighting,
		    b.weightedscore,
		    case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = b.transcriptid) then 'Y' else 'N' end as sub_learning,
			case when a2.userid = a.userid then 'Y' else 'N' end belongsToLearnerAssignment
		  FROM transcript a
			  inner join transcript b on b.transcriptid = a.creditTranscriptLink
			  inner join users_assignment a1 on a1.userid = a.userid
			  inner join users_assignment a2 on a2.user_meta_id = a1.user_meta_id
			  left join transcript_substatus tsub on a.student_substatus = tsub.id
		  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150114-kh-1', 'kelvin.hung', 'nschema/110.changeLog.xml', GETDATE(), 264, '8:65b8f6300cb3e2be6da757e9e906d8d5', 'dropView viewName=aggregated_transcript_vw; createView viewName=aggregated_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150114-jw-1::joe.wong
ALTER TABLE task_signoff_users ADD delegated_to nvarchar(85)
GO

ALTER TABLE task_signoff_users ADD delegated_by nvarchar(85)
GO

ALTER TABLE task_signoff_users ADD delegate_date datetime
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150114-jw-1', 'joe.wong', 'nschema/110.changeLog.xml', GETDATE(), 265, '8:1accd64110c9de12d9db7b60b724f8a1', 'addColumn tableName=task_signoff_users', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150115-kc-1::kelvin.chan
CREATE TABLE filter_templates (id nvarchar(85) NOT NULL, assignment_id nvarchar(85) NOT NULL, filter_name nvarchar(255) NOT NULL, filter_type nvarchar(10) NOT NULL, created_date datetime NOT NULL, creator_uid nvarchar(85) NOT NULL, last_updated datetime NOT NULL, last_updater_uid nvarchar(85) NOT NULL)
GO

ALTER TABLE filter_templates ADD CONSTRAINT PK_FILTER_TEMPLATES PRIMARY KEY (id)
GO

ALTER TABLE filter_templates ADD CONSTRAINT FK_FILTER_ASSIGNMENTID FOREIGN KEY (assignment_id) REFERENCES users_assignment (userid)
GO

ALTER TABLE filter_templates ADD CONSTRAINT UK_FILTER_TEMPLATES UNIQUE (assignment_id, filter_name)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150115-kc-1', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 266, '8:157af109e09de837fe29474a656fdd52', 'createTable tableName=filter_templates; addPrimaryKey constraintName=PK_FILTER_TEMPLATES, tableName=filter_templates; addForeignKeyConstraint baseTableName=filter_templates, constraintName=FK_FILTER_ASSIGNMENTID, referencedTableName=users_assignme...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150115-kc-2::kelvin.chan
CREATE TABLE filter_templates_criteria (id nvarchar(85) NOT NULL, filter_template_id nvarchar(85) NOT NULL, filter_criteria_type nvarchar(10) NOT NULL, filter_criteria_value nvarchar(2000) NOT NULL)
GO

ALTER TABLE filter_templates_criteria ADD CONSTRAINT PK_FILTER_CRITERIA PRIMARY KEY (id)
GO

ALTER TABLE filter_templates_criteria ADD CONSTRAINT FK_FILTER_FILTERID FOREIGN KEY (filter_template_id) REFERENCES filter_templates (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150115-kc-2', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 267, '8:e95d26914003f5ef5d355afd551c2cbe', 'createTable tableName=filter_templates_criteria; addPrimaryKey constraintName=PK_FILTER_CRITERIA, tableName=filter_templates_criteria; addForeignKeyConstraint baseTableName=filter_templates_criteria, constraintName=FK_FILTER_FILTERID, referencedTa...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150120-jw-1::joe.wong
ALTER TABLE task_signoff_users ADD CONSTRAINT FK_TASK_SIGNOFF_DELEGATE_BY FOREIGN KEY (delegated_by) REFERENCES users_assignment (userid)
GO

ALTER TABLE task_signoff_users ADD CONSTRAINT FK_TASK_SIGNOFF_DELEGATE_TO FOREIGN KEY (delegated_to) REFERENCES users_assignment (userid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150120-jw-1', 'joe.wong', 'nschema/110.changeLog.xml', GETDATE(), 268, '8:b3e876a9a5d8a84bf974a62296648c0f', 'addForeignKeyConstraint baseTableName=task_signoff_users, constraintName=FK_TASK_SIGNOFF_DELEGATE_BY, referencedTableName=users_assignment; addForeignKeyConstraint baseTableName=task_signoff_users, constraintName=FK_TASK_SIGNOFF_DELEGATE_TO, refer...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150128-jw-1::joe.wong
ALTER TABLE equivalency_rules ADD last_updated datetime
GO

ALTER TABLE equivalency_rules ADD last_updater_uid nvarchar(85)
GO

ALTER TABLE equivalency_rules ADD update_meaning_text nvarchar(500)
GO

ALTER TABLE equivalency_rules ADD updater_legal_name nvarchar(250)
GO

ALTER TABLE permissions ADD last_updated datetime
GO

ALTER TABLE permissions ADD last_updater_uid nvarchar(85)
GO

ALTER TABLE permissions ADD update_meaning_text nvarchar(500)
GO

ALTER TABLE permissions ADD updater_legal_name nvarchar(250)
GO

ALTER TABLE code_orgstructure ADD esig_equi_rule char(1) CONSTRAINT DF_code_orgstructure_esig_equi_rule DEFAULT 'I' NOT NULL
GO

ALTER TABLE code_orgstructure ADD esig_equi_rule_meaning nvarchar (max)
GO

ALTER TABLE learningObject ADD esig_equi_rule char(1) CONSTRAINT DF_learningObject_esig_equi_rule DEFAULT 'I' NOT NULL
GO

ALTER TABLE learningObject ADD esig_equi_rule_meaning nvarchar (max)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150128-jw-1', 'joe.wong', 'nschema/110.changeLog.xml', GETDATE(), 269, '8:affb48c6a51fc777e2b469bd11e3006c', 'addColumn tableName=equivalency_rules; addColumn tableName=permissions; addColumn tableName=code_orgstructure; addColumn tableName=learningObject', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150128-jw-2::joe.wong
update code_orgstructure set esig_equi_rule = 'O'
GO

update code_orgstructure set esig_equi_rule = 'N' where orgid = '*ROOT*'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150128-jw-2', 'joe.wong', 'nschema/110.changeLog.xml', GETDATE(), 270, '8:c89ae9fffffc618b46d9ca8e916d84b3', 'sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150129-ca-1::chris.au
ALTER TABLE courseDownloads ADD session_id nvarchar(15)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150129-ca-1', 'chris.au', 'nschema/110.changeLog.xml', GETDATE(), 271, '8:6745caba265c9ef7824af1e2de4eec37', 'addColumn tableName=courseDownloads', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150210-vn-1::vincent.ni
ALTER TABLE email_profile ADD enable_recipient_language char(1) CONSTRAINT DF_email_profile_enable_recipient_language DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150210-vn-1', 'vincent.ni', 'nschema/110.changeLog.xml', GETDATE(), 272, '8:786634404191530cf55d7c5555ca6a25', 'addColumn tableName=email_profile', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150212-kc-01::kelvin.chan
DROP TABLE filter_templates_criteria
GO

DROP TABLE filter_templates
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150212-kc-01', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 273, '8:6790fe7da63576cce9497ae64cf8ac6a', 'dropTable tableName=filter_templates_criteria; dropTable tableName=filter_templates', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150212-kc-02::kelvin.chan
CREATE TABLE filter_templates (id nvarchar(85) NOT NULL, assignment_id nvarchar(85) NOT NULL, filter_name nvarchar(255) NOT NULL, filter_type nvarchar(10) NOT NULL, created_date datetime NOT NULL, creator_uid nvarchar(85) NOT NULL, last_updated datetime NOT NULL, last_updater_uid nvarchar(85) NOT NULL)
GO

ALTER TABLE filter_templates ADD CONSTRAINT PK_FILTER_TEMPLATES PRIMARY KEY (id)
GO

ALTER TABLE filter_templates ADD CONSTRAINT FK_FILTER_ASSIGNMENTID FOREIGN KEY (assignment_id) REFERENCES users_assignment (userid)
GO

ALTER TABLE filter_templates ADD CONSTRAINT UK_FILTER_TEMPLATES UNIQUE (assignment_id, filter_name, filter_type)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150212-kc-02', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 274, '8:36f5be85b2d8e6aeb06a109e79b1f170', 'createTable tableName=filter_templates; addPrimaryKey constraintName=PK_FILTER_TEMPLATES, tableName=filter_templates; addForeignKeyConstraint baseTableName=filter_templates, constraintName=FK_FILTER_ASSIGNMENTID, referencedTableName=users_assignme...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150212-kc-03::kelvin.chan
CREATE TABLE filter_templates_criteria (id nvarchar(85) NOT NULL, filter_template_id nvarchar(85) NOT NULL, filter_criteria_type nvarchar(10) NOT NULL, filter_criteria_value nvarchar(2000) NOT NULL)
GO

ALTER TABLE filter_templates_criteria ADD CONSTRAINT PK_FILTER_CRITERIA PRIMARY KEY (id)
GO

ALTER TABLE filter_templates_criteria ADD CONSTRAINT FK_FILTER_FILTERID FOREIGN KEY (filter_template_id) REFERENCES filter_templates (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150212-kc-03', 'kelvin.chan', 'nschema/110.changeLog.xml', GETDATE(), 275, '8:e95d26914003f5ef5d355afd551c2cbe', 'createTable tableName=filter_templates_criteria; addPrimaryKey constraintName=PK_FILTER_CRITERIA, tableName=filter_templates_criteria; addForeignKeyConstraint baseTableName=filter_templates_criteria, constraintName=FK_FILTER_FILTERID, referencedTa...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150222-rr-1::ray.ruff
CREATE TABLE system_activity_profile (id nvarchar(15) NOT NULL, record_type char(1) NOT NULL, record_date datetime NOT NULL, n_max_sessions int NOT NULL, n_logins int NOT NULL, n_enrollments int NOT NULL, n_tx int NOT NULL)
GO

ALTER TABLE system_activity_profile ADD CONSTRAINT PK_SYSTEM_ACTIVITY_PROFILE PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150222-rr-1', 'ray.ruff', 'nschema/110.changeLog.xml', GETDATE(), 276, '8:56908935351314474d849ecc2d5a3bf2', 'createTable tableName=system_activity_profile; addPrimaryKey constraintName=PK_SYSTEM_ACTIVITY_PROFILE, tableName=system_activity_profile', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150222-rr-2::ray.ruff
CREATE NONCLUSTERED INDEX NUK_SYSTEM_ACTIVITY_DATE ON system_activity_profile(record_date)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150222-rr-2', 'ray.ruff', 'nschema/110.changeLog.xml', GETDATE(), 277, '8:340e6d6b283854c6e69eb4f093122362', 'createIndex indexName=NUK_SYSTEM_ACTIVITY_DATE, tableName=system_activity_profile', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150225-rr-1::ray.ruff
ALTER TABLE users_assignment ADD super_appraiser nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150225-rr-1', 'ray.ruff', 'nschema/110.changeLog.xml', GETDATE(), 278, '8:c0f276c415f3d538f966cd5406109869', 'addColumn tableName=users_assignment', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150304-sn-1::sam.ng
DROP VIEW user_appraisal_vw
GO

DROP VIEW valid_users_vw
GO

DROP VIEW users
GO

CREATE VIEW users AS select ua.userid, ua.dappraiser, ua.level1id, ua.level2id, ua.level3id, ua.level4id, ua.level5id,
    		       ua.costcenter, ua.joindate, um.userattr1, um.userattr2, um.userattr3, um.userattr4, ua.userrole,
    		       ua.datecreated, ua.jobtitle, ua.UserOption1, ua.UserOption2, ua.UserOption3, ua.profile, ua.managername,
    		       ua.manageremail, ua.departmentname, ua.locationcode, ua.hrmanagername, ua.hrmanageremail,
    		       ua.line_of_business, ua.skills_assessment_enabled, um.userattr5, um.userattr6, um.userattr7,
    		       um.userattr8, ua.leaf_orgid, ua.creditbalance, ua.hr_tag, ua.departmentid, ua.costcentername,
    		       ua.creator_uid, ua.last_updated, ua.last_updater_uid, ua.offline_player_eval_days, ua.offline_player_eval_start_date,
    		       ua.token_account_id, ua.domain_id, ua.user_meta_id, ua.assignment_ref_code, ua.dappraiserid,
    		       um.password, um.currentstatus, um.currentPID, um.familyname,
           		   um.givenname, um.othername, um.personaltitle, um.email, um.employeeno,
           		   um.externalAuthentication, um.autoDelete, um.deleteDate, um.dateofbirth,
           		   um.gender, um.expirationdate, um.nationality, um.dietary_constraints,
           		   um.middlename, um.skype_name, um.date_closed, ua.assignment_end_date, ua.super_appraiser
			from users_assignment ua, users_meta um
			where ua.user_meta_id = um.user_meta_id
GO

CREATE VIEW valid_users_vw AS select *
		  	from users
		  	where currentstatus NOT IN (3, 6)
GO

CREATE VIEW user_appraisal_vw AS select ua.id as appraisal_id, ua.appraisal_template_id, atp.template_name, ua.appraisee_id, da.currentPID as appraiser_id, da.givenname as appraiser_first_name, da.familyname as appraiser_last_name,
			sda.currentPID as super_appraiser_id, sda.givenname as super_appraiser_first_name, sda.familyname as super_appraiser_last_name, ast.code as status_code,
			ast.category_id as status_category_id, ac.code as status_category_code, ua.created_date, ua.completed_date, aso.appraisee_comments, aso.appraiser_comments,
			aso.compliance_attitude, aso.appraisee_acceptance, aso.appraiser_acceptance, aso.career_dev_dialogue,
			aso.average_goal_rating, aso.average_objective_rating, aso.average_competency_rating, aso.average_dimensions_rating, aso.overall_rating,
			atp.start_date as template_start_date, atp.end_date as template_end_date, aso.appraisee_reply
			from user_appraisal ua
			inner join appraisal_template atp on atp.id = ua.appraisal_template_id
			left join valid_users_vw da on da.userid = ua.appraiser_id
			left join valid_users_vw sda on sda.userid = ua.super_appraiser_id
			inner join appraisal_status ast on ast.id = ua.status_id
			left join appraisal_status_category ac on ac.id = ast.category_id
			left join appraisal_u_sign_off aso on aso.appraisal_id = ua.id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150304-sn-1', 'sam.ng', 'nschema/110.changeLog.xml', GETDATE(), 279, '8:05a84340c109ef931196f4a79307a988', 'dropView viewName=user_appraisal_vw; dropView viewName=valid_users_vw; dropView viewName=users; createView viewName=users; createView viewName=valid_users_vw; createView viewName=user_appraisal_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150305-rr-1::ray.ruff
ALTER TABLE cschedule ADD dept_enroll_limit int CONSTRAINT DF_cschedule_dept_enroll_limit DEFAULT 0 NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150305-rr-1', 'ray.ruff', 'nschema/110.changeLog.xml', GETDATE(), 280, '8:fa4b4110679b2f185a7b968cdfb3c88d', 'addColumn tableName=cschedule', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/110.changeLog.xml::20150306-gl-1::garbo.lim
DROP VIEW cschedule_vw
GO

CREATE VIEW cschedule_vw AS SELECT
			    cschedule.*,
			    (select
			    count(*)
			     from transcript
			     where transcript.eventid = cschedule.eventid and studentstatus in (2, 3)) nregistered
			FROM cschedule
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150306-gl-1', 'garbo.lim', 'nschema/110.changeLog.xml', GETDATE(), 281, '8:fdf566adca682ef65e0f296972e4d91d', 'dropView viewName=cschedule_vw; createView viewName=cschedule_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::24Feb2015-sg-1::shravan.gurrala
UPDATE ekpversion SET dblevel = 111, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('24Feb2015-sg-1', 'shravan.gurrala', 'nschema/111.changeLog.xml', GETDATE(), 282, '8:2aac8664798ada37324efb3aede333f2', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150224-rc-1::ryan.chen
ALTER TABLE appraisal_status ADD revert_email_id1 nvarchar(15)
GO

ALTER TABLE appraisal_status ADD revert_email_id2 nvarchar(15)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150224-rc-1', 'ryan.chen', 'nschema/111.changeLog.xml', GETDATE(), 283, '8:f6bfa356fcfbc772d0fb3bb894ff7973', 'addColumn tableName=appraisal_status', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150224-kc-1::kelvin.chan
ALTER TABLE appraisal_s_sign_off ADD display_s_appraiser_acceptance char(1) CONSTRAINT DF_appraisal_s_sign_off_display_s_appraiser_acceptance DEFAULT 'N' NOT NULL
GO

ALTER TABLE appraisal_s_sign_off ADD display_s_appraiser_comments char(1) CONSTRAINT DF_appraisal_s_sign_off_display_s_appraiser_comments DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150224-kc-1', 'kelvin.chan', 'nschema/111.changeLog.xml', GETDATE(), 284, '8:e5623d4506cfacd6050a43d859de4ca7', 'addColumn tableName=appraisal_s_sign_off', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150224-kc-2::kelvin.chan
ALTER TABLE appraisal_u_sign_off ADD s_appraiser_acceptance char(1) CONSTRAINT DF_appraisal_u_sign_off_s_appraiser_acceptance DEFAULT 'N' NOT NULL
GO

ALTER TABLE appraisal_u_sign_off ADD s_appraiser_comments nvarchar (max)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150224-kc-2', 'kelvin.chan', 'nschema/111.changeLog.xml', GETDATE(), 285, '8:c0725348cddcef4e67b9da1809f4b84d', 'addColumn tableName=appraisal_u_sign_off', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150302-rc-1::ryan.chen
DROP VIEW user_appraisal_vw
GO

CREATE VIEW user_appraisal_vw AS select ua.id as appraisal_id, ua.appraisal_template_id, atp.template_name, ua.appraisee_id, da.currentPID as appraiser_id, da.givenname as appraiser_first_name, da.familyname as appraiser_last_name,
		   		sda.currentPID as super_appraiser_id, sda.givenname as super_appraiser_first_name, sda.familyname as super_appraiser_last_name, ast.code as status_code,
		   		ast.category_id as status_category_id, ac.code as status_category_code, ua.created_date, ua.completed_date, aso.appraisee_comments, aso.appraiser_comments,
		   		aso.compliance_attitude, aso.appraisee_acceptance, aso.appraiser_acceptance, aso.career_dev_dialogue,
		   		aso.average_goal_rating, aso.average_objective_rating, aso.average_competency_rating, aso.average_dimensions_rating, aso.overall_rating,
		   		atp.start_date as template_start_date, atp.end_date as template_end_date, aso.s_appraiser_acceptance, aso.s_appraiser_comments, aso.appraisee_reply 
			from user_appraisal ua
			inner join appraisal_template atp on atp.id = ua.appraisal_template_id
			left join valid_users_vw da on da.userid = ua.appraiser_id
			left join valid_users_vw sda on sda.userid = ua.super_appraiser_id
			inner join appraisal_status ast on ast.id = ua.status_id
			left join appraisal_status_category ac on ac.id = ast.category_id
			left join appraisal_u_sign_off aso on aso.appraisal_id = ua.id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150302-rc-1', 'ryan.chen', 'nschema/111.changeLog.xml', GETDATE(), 286, '8:b59a9a9aaf8ab9bad8449672f87a6eeb', 'dropView viewName=user_appraisal_vw; createView viewName=user_appraisal_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150310-rc-1::ryan.chen
CREATE TABLE permission_template (id nvarchar(85) NOT NULL, template_code nvarchar(85) NOT NULL, template_name nvarchar(255) NOT NULL)
GO

ALTER TABLE permission_template ADD CONSTRAINT PK_PERMISSION_TEMPLATE PRIMARY KEY (id)
GO

ALTER TABLE permission_template ADD CONSTRAINT UK_PERMISSION_TEMPLATE UNIQUE (template_code)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150310-rc-1', 'ryan.chen', 'nschema/111.changeLog.xml', GETDATE(), 287, '8:1276bb0759a87932871e09980ec1cd2c', 'createTable tableName=permission_template; addPrimaryKey constraintName=PK_PERMISSION_TEMPLATE, tableName=permission_template; addUniqueConstraint constraintName=UK_PERMISSION_TEMPLATE, tableName=permission_template', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150313-jc-1::jing.chen
INSERT INTO permissions (objectid, objecttype, permissioncode, identityid, identitytype, operator, count) VALUES ('R265', 'RT', 1, '*ROOT*', 'O', 'OR', 1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150313-jc-1', 'jing.chen', 'nschema/111.changeLog.xml', GETDATE(), 288, '8:c03b1ddb4f06f6eb9845b710bb69ec5f', 'insert tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150317-gl-1::garbo.lim
ALTER TABLE question_type6 ADD autoscore char(1) CONSTRAINT DF_question_type6_autoscore DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150317-gl-1', 'garbo.lim', 'nschema/111.changeLog.xml', GETDATE(), 289, '8:32a1d83e188e0e08bd5920041a112893', 'addColumn tableName=question_type6', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150318-js-1::janice.sung
ALTER TABLE code_pool ADD auto_exempt char(1) CONSTRAINT DF_code_pool_auto_exempt DEFAULT 'N' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'code_pool'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.code_pool'; DECLARE @ColumnName SYSNAME set @ColumnName = N'auto_exempt'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'A flag to sepcifiy the auto exempt config [Y/N]. Default: N';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150318-js-1', 'janice.sung', 'nschema/111.changeLog.xml', GETDATE(), 290, '8:70e6953ed0e8afeb9a39a1688dae112f', 'addColumn tableName=code_pool', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150318-js-2::janice.sung
CREATE TABLE exam_pool_auto_exemption (id nvarchar(85) NOT NULL, poolid int NOT NULL, score_threshold int NOT NULL, learningid nvarchar(85) NOT NULL, sessionid nvarchar(15))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'exam_pool_auto_exemption'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.exam_pool_auto_exemption';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the exam pool auto exempt learning module or session and score threshold details';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.name = N'MS_Description' AND ep.minor_id=0); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'exam_pool_auto_exemption'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.exam_pool_auto_exemption'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'A Unique Identifier for the table';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'exam_pool_auto_exemption'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.exam_pool_auto_exemption'; DECLARE @ColumnName SYSNAME set @ColumnName = N'poolid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The pool ID of the question pool';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'exam_pool_auto_exemption'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.exam_pool_auto_exemption'; DECLARE @ColumnName SYSNAME set @ColumnName = N'score_threshold'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'A threshold for the exam score of the question pool';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'exam_pool_auto_exemption'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.exam_pool_auto_exemption'; DECLARE @ColumnName SYSNAME set @ColumnName = N'learningid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Learning ID of the learning object';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'exam_pool_auto_exemption'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.exam_pool_auto_exemption'; DECLARE @ColumnName SYSNAME set @ColumnName = N'sessionid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Session ID of the learning object';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE exam_pool_auto_exemption ADD CONSTRAINT PK_EXAM_POOL_AUTO_EXEMPTION PRIMARY KEY (id)
GO

ALTER TABLE exam_pool_auto_exemption ADD CONSTRAINT FK_EXAM_POOL_AUTO_EXEMPT_PID FOREIGN KEY (poolid) REFERENCES code_pool (poolid)
GO

ALTER TABLE exam_pool_auto_exemption ADD CONSTRAINT FK_EXAM_POOL_AUTO_EXEMPT_LID FOREIGN KEY (learningid) REFERENCES learningObject (learningid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150318-js-2', 'janice.sung', 'nschema/111.changeLog.xml', GETDATE(), 291, '8:589a6df4e105314c40466990aa76ed9c', 'createTable tableName=exam_pool_auto_exemption; addPrimaryKey constraintName=PK_EXAM_POOL_AUTO_EXEMPTION, tableName=exam_pool_auto_exemption; addForeignKeyConstraint baseTableName=exam_pool_auto_exemption, constraintName=FK_EXAM_POOL_AUTO_EXEMPT_P...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150312-mw-1::martin.wang
-- [DEV 8304] New table for language bundles
CREATE TABLE language_bundle (bundle_id nvarchar(85) NOT NULL, object_id nvarchar(85) NOT NULL, object_type nvarchar(5) NOT NULL, locale nvarchar(85), ilangid smallint, is_primary char(1) NOT NULL, last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.language_bundle';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the details of language bundles.';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.name = N'MS_Description' AND ep.minor_id=0); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'bundle_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID (primary key)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'object_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Object ID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'object_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Object types (corresponds to ObjectType enum)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'locale'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Language tag for locale (e.g. en, en_UK)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'ilangid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course Language ID (refers to code_itemLanguage.ilangid)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_primary'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'''Y'' if primary bundle; ''N'' otherwise';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that the record was updated';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'updater_legal_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'e-Signature property: legal name of the updater';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'update_meaning_text'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'e-Signature property: contents of update meaning';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'language_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.language_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updater_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'e-Signature property: user ID of updater';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE language_bundle ADD CONSTRAINT PK_LANGUAGE_BUNDLE PRIMARY KEY (bundle_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150312-mw-1', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 292, '8:f6b329bb1e8b82b8c9a39d1f77e24e92', 'createTable tableName=language_bundle; addPrimaryKey constraintName=PK_LANGUAGE_BUNDLE, tableName=language_bundle', '[DEV 8304] New table for language bundles', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150313-mw-2::martin.wang
insert into language_bundle(bundle_id, object_id, object_type,
			ilangid,
			is_primary)
			select CAST(ROW_NUMBER() OVER (ORDER BY
			learningid) AS
			VARCHAR) + 'LO' as rownum, learningid, 'LO', language,
			'Y' FROM
			learningObject
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150313-mw-2', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 293, '8:88c1d2cdd12b631e85a2a5e82f7055b9', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150315-mw-1::martin.wang
-- [DEV 8304] New table for resource string
CREATE TABLE resource_string (id nvarchar(85) NOT NULL, resource_key nvarchar(85) NOT NULL, bundle_id nvarchar(85) NOT NULL, value_varchar nvarchar(4000), value_ntext nvarchar (max), last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.resource_string';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the localized value of a localizable data field with respect to a language bundle.';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.name = N'MS_Description' AND ep.minor_id=0); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID (primary key)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'resource_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource ID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'bundle_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Bundle ID (refers to language_bundle.bundle_id)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'value_varchar'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Localized value in varchar-type';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'value_ntext'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Localized value in ntext-type';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that the record was updated';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'updater_legal_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'e-Signature property: legal name of the updater';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'update_meaning_text'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'e-Signature property: contents of update meaning';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updater_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'e-Signature property: user ID of updater';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE resource_string ADD CONSTRAINT PK_RESOURCE_STRING PRIMARY KEY (id)
GO

ALTER TABLE resource_string ADD CONSTRAINT FK_BUNDLE_ID_RESOURCE_STRING FOREIGN KEY (bundle_id) REFERENCES language_bundle (bundle_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150315-mw-1', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 294, '8:a4f38af66cf38a9d90247219d1556f27', 'createTable tableName=resource_string; addPrimaryKey constraintName=PK_RESOURCE_STRING, tableName=resource_string; addForeignKeyConstraint baseTableName=resource_string, constraintName=FK_BUNDLE_ID_RESOURCE_STRING, referencedTableName=language_bundle', '[DEV 8304] New table for resource string', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150316-mw-1::martin.wang
IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'i_learningObject'
			)
			DROP TRIGGER i_learningObject
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'u_learningObject'
			)
			DROP TRIGGER u_learningObject
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'd_learningObject'
			)
			DROP TRIGGER d_learningObject
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'i_audience'
			)
			DROP TRIGGER i_audience
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'u_audience'
			)
			DROP TRIGGER u_audience
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'd_audience'
			)
			DROP TRIGGER d_audience
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'i_objectives_topics'
			)
			DROP TRIGGER i_objectives_topics
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'u_objectives_topics'
			)
			DROP TRIGGER u_objectives_topics
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'd_objectives_topics'
			)
			DROP TRIGGER d_objectives_topics
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150316-mw-1', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 295, '8:ad79e781463ec154d52150777d50f08d', 'sql; sql; sql; sql; sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20140316-mw-2::martin.wang
-- [DEV 8304] New index for migration
CREATE NONCLUSTERED INDEX NUK_BUNDLE_ID_OBJECT_TYPE ON language_bundle(object_id, object_type)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20140316-mw-2', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 296, '8:a940646d20b6b818f45edd718801125d', 'createIndex indexName=NUK_BUNDLE_ID_OBJECT_TYPE, tableName=language_bundle', '[DEV 8304] New index for migration', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150317-mw-1::martin.wang
-- [DEV 8304] Migration of learningObject.title, learningObject.description1 and learningObject.support_contact to become multilingual data (20150317-mw-1 to 20150317-mw-4)
ALTER TABLE learningObject ADD title_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'learningObject'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.learningObject'; DECLARE @ColumnName SYSNAME set @ColumnName = N'title_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for title';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE learningObject ADD description_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'learningObject'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.learningObject'; DECLARE @ColumnName SYSNAME set @ColumnName = N'description_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for description';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE learningObject ADD support_contact_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'learningObject'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.learningObject'; DECLARE @ColumnName SYSNAME set @ColumnName = N'support_contact_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for support contact';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150317-mw-1', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 297, '8:fa846554aded127011ab0fce4f5af14f', 'addColumn tableName=learningObject; addColumn tableName=learningObject; addColumn tableName=learningObject', '[DEV 8304] Migration of learningObject.title, learningObject.description1 and learningObject.support_contact to become multilingual data (20150317-mw-1 to 20150317-mw-4)', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150317-mw-3::martin.wang
insert into resource_string(id, resource_key, bundle_id,
			value_varchar)
			select bundle_id + 'title', bundle_id + 'title',
			bundle_id, title
			from learningObject l join language_bundle b on
			l.learningid =
			b.object_id and b.object_type = 'LO'
GO

insert into resource_string(id, resource_key, bundle_id,
			value_ntext)
			select bundle_id + 'desc', bundle_id + 'desc', bundle_id,
			description1 from learningObject l join language_bundle b on
			l.learningid = b.object_id and b.object_type = 'LO'
GO

insert into resource_string(id, resource_key, bundle_id,
			value_varchar)
			select bundle_id + 'contact', bundle_id + 'contact',
			bundle_id,
			support_contact from learningObject l join language_bundle
			b on
			l.learningid = b.object_id and b.object_type = 'LO'
GO

update learningObject set title_key = (select bundle_id + 'title'
			from
			language_bundle b where learningid = b.object_id and
			b.object_type =
			'LO')
			, description_key = (select bundle_id + 'desc'
			from
			language_bundle b where learningid = b.object_id and
			b.object_type =
			'LO')
			, support_contact_key = (select bundle_id +
			'contact' from
			language_bundle b where learningid = b.object_id and
			b.object_type =
			'LO')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150317-mw-3', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 298, '8:9e31133929edf61033fb72f3c5982429', 'sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150317-mw-5::martin.wang
-- [DEV 8304] Migration of audience.description to become multilingual data (20150317-mw-5 to 20150317-mw-8)
ALTER TABLE audience ADD description_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'audience'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.audience'; DECLARE @ColumnName SYSNAME set @ColumnName = N'description_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for description';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150317-mw-5', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 299, '8:51ff2b241f589c0348d1b2a78ad8f3f3', 'addColumn tableName=audience', '[DEV 8304] Migration of audience.description to become multilingual data (20150317-mw-5 to 20150317-mw-8)', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150317-mw-7::martin.wang
insert into resource_string(id, resource_key, bundle_id,
			value_varchar)
			select bundle_id + 'audience' + a.id, bundle_id +
			'audience' + a.id,
			bundle_id, description from audience a join
			language_bundle b on
			a.learningid = b.object_id and b.object_type =
			'LO'
GO

update audience set description_key = (select bundle_id +
			'audience' + id
			from language_bundle b where learningid = b.object_id
			and
			b.object_type = 'LO')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150317-mw-7', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 300, '8:17d73fba3ccaf4145381edd698424d70', 'sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150317-mw-9::martin.wang
-- [DEV 8304] Migration of objectives_topics.description to become multilingual data (20150317-mw-9 to 20150317-mw-13)
ALTER TABLE objectives_topics ADD description_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'objectives_topics'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.objectives_topics'; DECLARE @ColumnName SYSNAME set @ColumnName = N'description_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for description';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150317-mw-9', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 301, '8:fb01d28b39d7d25e35f36e6fd7499cb5', 'addColumn tableName=objectives_topics', '[DEV 8304] Migration of objectives_topics.description to become multilingual data (20150317-mw-9 to 20150317-mw-13)', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150317-mw-11::martin.wang
insert into resource_string(id, resource_key, bundle_id,
			value_varchar)
			select bundle_id + 'objective' + CAST(o.entrynum AS
			NVARCHAR(85)),
			bundle_id + 'objective' + CAST(o.entrynum AS
			NVARCHAR(85)),
			bundle_id, description from objectives_topics o join
			language_bundle
			b on o.learningid = b.object_id and b.object_type =
			'LO'
GO

update objectives_topics set description_key = (select bundle_id
			+
			'objective' + CAST(entrynum AS NVARCHAR(85)) from language_bundle b
			where learningid = b.object_id and b.object_type = 'LO')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150317-mw-11', 'martin.wang', 'nschema/111.changeLog.xml', GETDATE(), 302, '8:f59996d5bfd5e0513ec1a393b6ff2b13', 'sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150323-vn-1::vincent.ni
IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'i_autoenroll'
			)
			DROP TRIGGER i_autoenroll
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'u_autoenroll'
			)
			DROP TRIGGER u_autoenroll
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'd_autoenroll'
			)
			DROP TRIGGER d_autoenroll
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150323-vn-1', 'vincent.ni', 'nschema/111.changeLog.xml', GETDATE(), 303, '8:620f8e2b84c98e255496523da5a386e0', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150323-vn-2::vincent.ni
ALTER TABLE autoenroll ADD durationType nvarchar(10)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'durationType'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'To support relative join dates, AL:At Least, LT: Less Than';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150323-vn-2', 'vincent.ni', 'nschema/111.changeLog.xml', GETDATE(), 304, '8:f22d19eca0c6c4fba5dd7c7827c49885', 'addColumn tableName=autoenroll', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150323-vn-3::vincent.ni
update autoenroll set durationType = 'AL' where targetType='D'
			and durationenabled='Y'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150323-vn-3', 'vincent.ni', 'nschema/111.changeLog.xml', GETDATE(), 305, '8:09fa1ddb25168605fbf9513102e29bf5', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150325-vn-4::vincent.ni
ALTER TABLE jobprofile_autoenroll ADD durationType nvarchar(10)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'jobprofile_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.jobprofile_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'durationType'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'To support relative join dates, AL:At Least, LT: Less Than';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150325-vn-4', 'vincent.ni', 'nschema/111.changeLog.xml', GETDATE(), 306, '8:2c76ade8c1717ac86a2850eae7d0d1d5', 'addColumn tableName=jobprofile_autoenroll', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150325-vn-5::vincent.ni
update jobprofile_autoenroll set durationType = 'AL' where
			targetType='D' and durationenabled='Y'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150325-vn-5', 'vincent.ni', 'nschema/111.changeLog.xml', GETDATE(), 307, '8:15fcdae9b921f55d5c56c5d2ada0bc17', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150324-ca-1::chris.au
ALTER TABLE learningObject DROP CONSTRAINT fk_learning_object_language
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150324-ca-1', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 308, '8:cd7896ff2fd5892c4bef18ced365782b', 'dropForeignKeyConstraint baseTableName=learningObject, constraintName=fk_learning_object_language', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150324-ca-2::chris.au
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE learningObject DROP CONSTRAINT ' + QUOTENAME([df].[name]) FROM [sys].[columns] AS [c] INNER JOIN [sys].[default_constraints] AS [df] ON [df].[object_id] = [c].[default_object_id] WHERE [c].[object_id] = OBJECT_ID(N'learningObject') AND [c].[name] = N'language'
EXEC sp_executesql @sql
GO

ALTER TABLE learningObject DROP COLUMN language
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150324-ca-2', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 309, '8:4cefc885ba7fc9c2f4eb8ffceceec949', 'dropColumn columnName=language, tableName=learningObject', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150325-ca-1::chris.au
CREATE VIEW learningObject_vw AS select lo.learningid,lo.learningType,lo.mediaid,lo.subject,lo.title,lo.description1,lo.hyperlink,lo.assignedChatRoom,lo.assignedForum,lo.editcontrol,lo.showInCatalog,lo.allowPeerReview,lo.datecreated,lo.autoDelete,lo.deleteDate,lo.picture,lo.evaluation,lo.evalRequired,lo.duration,lo.policyid,lo.org_policy_override,lo.prereqcontrol,lo.testRequired,lo.startAvailabilityDate,lo.endAvailabilityDate,lo.vendorname,lo.allowReopen,lo.testEnablesCompletion,lo.enforcePrePostTestOrder,lo.completionTimeLimit,lo.allowReEnroll,lo.historyExpireWindow,lo.display_course_interest,lo.wikiURL,lo.supervisorAssessment,lo.allowDappraiserAssess,lo.sticky,lo.creator_uid,lo.last_updated,lo.last_updater_uid,lo.mekp_target,lo.update_meaning,lo.wiki_key,lo.wiki_display,lo.archived,lo.updater_legal_name,lo.update_meaning_text,lo.auto_archive_date,lo.show_expiration_date,lo.show_begin_date,lo.subject_category_id,lo.require_esignature,lo.support_contact,lo.domain_id,lo.allow_auto_recommend,lo.allowSupervisorAssess,lo.esig_course_withdraw,lo.esig_course_modify,lo.esig_course_launch,lo.esig_course_import,lo.esig_course_finish,lo.esig_exam_launch,lo.esig_wizard_modify_trans,lo.esig_reviewer_modify_trans,lo.esig_editor_modify_trans,lo.esig_modify_attendance,lo.esig_course_withdraw_meaning,lo.esig_course_modify_meaning,lo.esig_course_launch_meaning,lo.esig_course_import_meaning,lo.esig_course_finish_meaning,lo.esig_exam_launch_meaning,lo.esig_wzrd_modify_trans_meaning,lo.esig_rvwr_modify_trans_meaning,lo.esig_edtr_modify_trans_meaning,lo.esig_modify_attendance_meaning,lo.esig_equi_rule,lo.esig_equi_rule_meaning,lo.title_key,lo.description_key,lo.support_contact_key, lang.ilangid as language 
			from learningObject lo
			left join language_bundle lang on lang.object_id = lo.learningid and lang.object_type = 'LO' and is_primary = 'Y'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150325-ca-1', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 310, '8:9aa023a8e4144a6c17a939483f75ce5a', 'createView viewName=learningObject_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-jc-1::jing.chen
CREATE TABLE active_system_language (id nvarchar(85) NOT NULL)
GO

ALTER TABLE active_system_language ADD CONSTRAINT PK_ACTIVE_SYSTEM_LANGUAGE PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-jc-1', 'jing.chen', 'nschema/111.changeLog.xml', GETDATE(), 311, '8:dbb9d306ea687ba53eec1f78364db173', 'createTable tableName=active_system_language; addPrimaryKey constraintName=PK_ACTIVE_SYSTEM_LANGUAGE, tableName=active_system_language', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-dr-1::dreidel.regala
ALTER TABLE transcript ADD is_reqd_custom_attr_complete char(1) CONSTRAINT DF_transcript_is_reqd_custom_attr_complete DEFAULT 'N' NOT NULL
GO

ALTER TABLE enrollment_policy_ext_details ADD updated_email_profile_id nvarchar(15)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-dr-1', 'dreidel.regala', 'nschema/111.changeLog.xml', GETDATE(), 312, '8:cda2b0b071b1e0d0eef4403bb96d3a4a', 'addColumn tableName=transcript; addColumn tableName=enrollment_policy_ext_details', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-dr-2::dreidel.regala
CREATE TABLE custom_enroll_attributes (id nvarchar(15) NOT NULL, policy_id nvarchar(15) NOT NULL, attribute_id nvarchar(15) NOT NULL, is_included char(1) NOT NULL, is_mandatory char(1) NOT NULL, is_required char(1) NOT NULL)
GO

ALTER TABLE custom_enroll_attributes ADD CONSTRAINT PK_CUST_ENROLL_ATTR PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-dr-2', 'dreidel.regala', 'nschema/111.changeLog.xml', GETDATE(), 313, '8:7cb6ad54986931883c43566100f4062b', 'createTable tableName=custom_enroll_attributes; addPrimaryKey constraintName=PK_CUST_ENROLL_ATTR, tableName=custom_enroll_attributes', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-dr-3::dreidel.regala
CREATE TABLE custom_enroll_attr_reminder (policy_id nvarchar(15) NOT NULL, incomplete_email_profile_id nvarchar(15) NOT NULL, days_from int NOT NULL)
GO

ALTER TABLE custom_enroll_attr_reminder ADD CONSTRAINT PK_CUST_ENROLL_ATTR_NOTIF PRIMARY KEY (policy_id, incomplete_email_profile_id, days_from)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-dr-3', 'dreidel.regala', 'nschema/111.changeLog.xml', GETDATE(), 314, '8:91e9b2fda6ee4556abf8745584b3bfa1', 'createTable tableName=custom_enroll_attr_reminder; addPrimaryKey constraintName=PK_CUST_ENROLL_ATTR_NOTIF, tableName=custom_enroll_attr_reminder', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-dr-5::dreidel.regala
DROP VIEW valid_transcript_vw
GO

CREATE VIEW valid_transcript_vw AS SELECT
		    transcriptid,
		    userid,
		    learningid,
		    currid,
		    learningType,
		    eventid,
		    startdate,
		    finishdate,
		    finalgrade,
		    studentstatus,
		    progress,
		    assessDate,
		    attendance,
		    perfGradeid,
		    sessionid,
		    creditTranscriptLink,
		    curriculumTranscriptLink,
		    assessmentscore,
		    requestSource,
		    contentSessionID,
		    certSessionID,
		    enrollmentDate,
		    remindercount,
		    costcenter,
		    confirmation_status,
		    reopen,
		    isOpen,
		    pre_assess_score,
		    post_assess_score,
		    usage_expiration_date,
		    quick_eval,
		    notifyemail,
		    completion_deadline,
		    deadline_expired,
		    revision_number,
		    ntraininghours,
		    allowDappraiserAssess,
		    offline_key,
		    mekp_sync,
		    send_notif_ew,
		    update_meaning,
		    last_updated,
		    updater_legal_name,
		    update_meaning_text,
		    last_updater_uid,
		    creator_uid,
		    datecreated,
		    curr_total_time,
		    attempts_allowed,
		    grading_method,
		    attempts_count,
		    completion_method,
		    final_score,
		    weighting,
		    weightedscore,
		    sub_learning,
		    actual_training_time_sec,
		    student_substatus,
		    is_reqd_custom_attr_complete
		  FROM transcript
		  WHERE studentstatus NOT IN (8, 9, 10)
		  UNION ALL
		  SELECT
		    a.transcriptid,
		    a.userid,
		    a.learningid,
		    a.currid,
		    a.learningType,
		    a.eventid,
		    b.startdate,
		    b.finishdate,
		    b.finalgrade,
		    b.studentstatus,
		    b.progress,
		    b.assessDate,
		    b.attendance,
		    b.perfGradeid,
		    a.sessionid,
		    CASE WHEN 1=1 THEN NULL ELSE '' END as creditTranscriptLink,
		    a.curriculumTranscriptLink,
		    b.assessmentscore,
		    a.requestSource,
		    b.contentSessionID,
		    b.certSessionID,
		    a.enrollmentDate,
		    b.remindercount,
		    b.costcenter,
		    b.confirmation_status,
		    b.reopen,
		    a.isOpen,
		    b.pre_assess_score,
		    b.post_assess_score,
		    b.usage_expiration_date,
		    b.quick_eval,
		    b.notifyemail,
		    b.completion_deadline,
		    b.deadline_expired,
		    b.revision_number,
		    b.ntraininghours,
		    b.allowDappraiserAssess,
		    b.offline_key,
		    b.mekp_sync,
		    b.send_notif_ew,
		    b.update_meaning,
		    b.last_updated,
		    b.updater_legal_name,
		    a.update_meaning_text,
		    a.last_updater_uid,
		    a.creator_uid,
		    a.datecreated,
		    b.curr_total_time,
		    b.attempts_allowed,
		    b.grading_method,
		    b.attempts_count,
		    b.completion_method,
		    b.final_score,
		    b.weighting,
		    b.weightedscore,
		    b.sub_learning,
		    b.actual_training_time_sec,
		    b.student_substatus,
		    b.is_reqd_custom_attr_complete
		  FROM transcript a, transcript b
		  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-dr-5', 'dreidel.regala', 'nschema/111.changeLog.xml', GETDATE(), 315, '8:b8b04cf7d9d66a04242b0a8878479190', 'dropView viewName=valid_transcript_vw; createView viewName=valid_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150330-dr-6::dreidel.regala
DROP VIEW aggregated_transcript_vw
GO

CREATE VIEW aggregated_transcript_vw AS SELECT
		    t1.transcriptid,
		    a2.userid,
			t1.learningid,
		    t1.currid,
		    t1.learningType,
		    t1.eventid,
		    t1.startdate,
		    t1.finishdate,
		    t1.finalgrade,
		    t1.studentstatus,
		    tsub.substatus_code,
		    tsub.substatus_name,
		    t1.progress,
		    t1.assessDate,
		    t1.attendance,
		    t1.perfGradeid,
		    t1.sessionid,
		    t1.creditTranscriptLink,
		    t1.curriculumTranscriptLink,
		    t1.assessmentscore,
		    t1.requestSource,
		    t1.contentSessionID,
		    t1.certSessionID,
		    t1.enrollmentDate,
		    t1.remindercount,
		    t1.costcenter,
		    t1.confirmation_status,
		    t1.reopen,
		    t1.isOpen,
		    t1.pre_assess_score,
		    t1.post_assess_score,
		    t1.usage_expiration_date,
		    t1.quick_eval,
		    t1.notifyemail,
		    t1.completion_deadline,
		    t1.deadline_expired,
		    t1.revision_number,
		    t1.ntraininghours,
		    t1.allowDappraiserAssess,
		    t1.offline_key,
		    t1.mekp_sync,
		    t1.send_notif_ew,
		    t1.update_meaning,
		    t1.last_updated,
		    t1.updater_legal_name,
		    t1.update_meaning_text,
		    t1.last_updater_uid,
		    t1.creator_uid,
		    t1.datecreated,
		    t1.curr_total_time,
		    t1.attempts_allowed,
		    t1.grading_method,
		    t1.attempts_count,
		    t1.completion_method,
		    t1.final_score,
		    t1.weighting,
		    t1.weightedscore,
			case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = t1.transcriptid) then 'Y' else 'N' end as sub_learning,
			case when a2.userid = t1.userid then 'Y' else 'N' end belongsToLearnerAssignment,
			t1.is_reqd_custom_attr_complete
		  FROM transcript t1
			  inner join users_assignment a1 on a1.userid = t1.userid
			  inner join users_assignment a2 on a2.user_meta_id = a1.user_meta_id
			  left join transcript_substatus tsub on t1.student_substatus = tsub.id
		  WHERE t1.studentstatus NOT IN (8, 9, 10)
		  UNION ALL
		  SELECT
		    a.transcriptid,
		    a2.userid,
		    a.learningid,
		    a.currid,
		    a.learningType,
		    a.eventid,
		    b.startdate,
		    b.finishdate,
		    b.finalgrade,
		    b.studentstatus,
		    tsub.substatus_code,
		    tsub.substatus_name,
		    b.progress,
		    b.assessDate,
		    b.attendance,
		    b.perfGradeid,
		    a.sessionid,
		    CASE WHEN 1=1 THEN NULL ELSE '' END as creditTranscriptLink,
		    a.curriculumTranscriptLink,
		    b.assessmentscore,
		    a.requestSource,
		    b.contentSessionID,
		    b.certSessionID,
		    a.enrollmentDate,
		    b.remindercount,
		    b.costcenter,
		    b.confirmation_status,
		    b.reopen,
		    a.isOpen,
		    b.pre_assess_score,
		    b.post_assess_score,
		    b.usage_expiration_date,
		    b.quick_eval,
		    b.notifyemail,
		    b.completion_deadline,
		    b.deadline_expired,
		    b.revision_number,
		    b.ntraininghours,
		    b.allowDappraiserAssess,
		    b.offline_key,
		    b.mekp_sync,
		    b.send_notif_ew,
		    b.update_meaning,
		    b.last_updated,
		    b.updater_legal_name,
		    a.update_meaning_text,
		    a.last_updater_uid,
		    a.creator_uid,
		    a.datecreated,
		    b.curr_total_time,
		    b.attempts_allowed,
		    b.grading_method,
		    b.attempts_count,
		    b.completion_method,
		    b.final_score,
		    b.weighting,
		    b.weightedscore,
		    case when EXISTS(SELECT * FROM submod_tran_subt WHERE program_tran_id = b.transcriptid) then 'Y' else 'N' end as sub_learning,
			case when a2.userid = a.userid then 'Y' else 'N' end belongsToLearnerAssignment,
			b.is_reqd_custom_attr_complete
		  FROM transcript a
			  inner join transcript b on b.transcriptid = a.creditTranscriptLink
			  inner join users_assignment a1 on a1.userid = a.userid
			  inner join users_assignment a2 on a2.user_meta_id = a1.user_meta_id
			  left join transcript_substatus tsub on a.student_substatus = tsub.id
		  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150330-dr-6', 'dreidel.regala', 'nschema/111.changeLog.xml', GETDATE(), 316, '8:0d9f58e13abcbcc28699e79012bde139', 'dropView viewName=aggregated_transcript_vw; createView viewName=aggregated_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150521-sn-1::sam.ng
-- [DEV 8308] Default whitelist ".xlf" file for XLIFF import
UPDATE logical_domain_config SET config_value=cast(config_value as nvarchar(max))+',.xlf' WHERE config_key='security.allowedFiles'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150521-sn-1', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 317, '8:d9298263de255cc387a5584556ca2388', 'sql; sql; sql; sql', '[DEV 8308] Default whitelist ".xlf" file for XLIFF import', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-gl-1::garbo.lim
CREATE TABLE temp_lang_locale (languageid nvarchar(85) NOT NULL, locale nvarchar(85) NOT NULL)
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('1', 'en')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('2', 'zh-CN')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('3', 'zh-TW')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('4', 'ja-JP')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('5', 'ko-KR')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('6', 'de-DE')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('7', 'es-ES')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('8', 'nl-NL')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('9', 'fr-FR')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('10', 'zh-HK')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('11', 'it-IT')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('12', 'pl-PL')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('13', 'el-GR')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('14', 'pt-PT')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('15', 'pt-BR')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('16', 'es-CO')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('17', 'fr-CA')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('18', 'ar-SA')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('19', 'hi-IN')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('20', 'ms-MY')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('21', 'th-TH')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('22', 'tr-TR')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('23', 'en-GB')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('24', 'en-CA')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('25', 'uk-UA')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('26', 'cs-CZ')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('27', 'da-DK')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('28', 'fi-FI')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('29', 'nb-NO')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('30', 'sv-SE')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('31', 'ru-RU')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('32', 'ro-RO')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('33', 'vi-VN')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('34', 'af-ZA')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('35', 'hu-HU')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('36', 'sk-SK')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('37', 'he-IL')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('38', 'so-SO')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('39', 'fr-CA')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('40', 'de-AT')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('41', 'de-CH')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('42', 'tl-PH')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('43', 'id-ID')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('44', 'bg-BG')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('45', 'hr-HR')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('46', 'en-US')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('47', 'sr-RS')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('48', 'et-EE')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('49', 'nl-NL-x-informal')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('50', 'es-MX')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('51', 'bs-BA')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('52', 'sl-SI')
GO

INSERT INTO temp_lang_locale (languageid, locale) VALUES ('53', 'ar-AE')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-gl-1', 'garbo.lim', 'nschema/111.changeLog.xml', GETDATE(), 318, '8:acfdf343bd1ce9d3d676d834967cf7ac', 'createTable tableName=temp_lang_locale; insert tableName=temp_lang_locale; insert tableName=temp_lang_locale; insert tableName=temp_lang_locale; insert tableName=temp_lang_locale; insert tableName=temp_lang_locale; insert tableName=temp_lang_local...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-gl-3::garbo.lim
insert into language_bundle(bundle_id, object_id, object_type,
			locale,
			is_primary)
			select CAST(ROW_NUMBER() OVER (ORDER BY a.artid) AS
			VARCHAR) + 'NW', a.artid, 'NW', case when l.locale is null then
			'*ALL*' else l.locale end, 'Y'
			from article a
			left join autonewsfilter
			af on a.artid = af.artid and af.filtertype = 'G'
			left join
			temp_lang_locale l on l.languageid = af.filtervalue
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-gl-3', 'garbo.lim', 'nschema/111.changeLog.xml', GETDATE(), 319, '8:4b8cae468f5f544895e9b7ce8043d5d7', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-gl-6::garbo.lim
ALTER TABLE article ADD title_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'article'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.article'; DECLARE @ColumnName SYSNAME set @ColumnName = N'title_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for title';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE article ADD teaser_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'article'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.article'; DECLARE @ColumnName SYSNAME set @ColumnName = N'teaser_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for teaser';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE article ADD text1_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'article'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.article'; DECLARE @ColumnName SYSNAME set @ColumnName = N'text1_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for text';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-gl-6', 'garbo.lim', 'nschema/111.changeLog.xml', GETDATE(), 320, '8:1c5818b8b611ebfedb3bed57799b77ef', 'addColumn tableName=article; addColumn tableName=article; addColumn tableName=article', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-gl-8::garbo.lim
insert into resource_string(id, resource_key, bundle_id,
			value_varchar)
			select bundle_id + 'title', bundle_id + 'title',
			bundle_id, title
			from article a join language_bundle b on a.artid =
			b.object_id and
			b.object_type = 'NW'
GO

insert into resource_string(id, resource_key, bundle_id,
			value_ntext)
			select bundle_id + 'text1', bundle_id + 'text1',
			bundle_id, text1
			from article a join language_bundle b on a.artid =
			b.object_id and
			b.object_type = 'NW'
GO

insert into resource_string(id, resource_key, bundle_id,
			value_varchar) select bundle_id + 'teaser', bundle_id + 'teaser',
			bundle_id, teaser from article a join language_bundle b on a.artid =
			b.object_id and b.object_type = 'NW'
GO

update article set title_key = (select bundle_id + 'title' from
			language_bundle b where artid = b.object_id and b.object_type = 'NW')
			, text1_key = (select bundle_id + 'text1' from language_bundle b
			where artid = b.object_id and b.object_type = 'NW')
			, teaser_key =
			(select bundle_id + 'teaser' from language_bundle b where artid =
			b.object_id and b.object_type = 'NW')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-gl-8', 'garbo.lim', 'nschema/111.changeLog.xml', GETDATE(), 321, '8:8f0f90dcb5a976dc3fda492b12aea88c', 'sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150327-gl-10::garbo.lim
delete from autonewsfilter where filtertype = 'G'
GO

insert into active_system_language (id)
        	select distinct locale from language_bundle where object_type = 'NW' and locale is not null and locale <> '*ALL*'
GO

DROP TABLE temp_lang_locale
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150327-gl-10', 'garbo.lim', 'nschema/111.changeLog.xml', GETDATE(), 322, '8:14d5b4ee5c0308ac4887e55c9f6f9cc8', 'sql; sql; dropTable tableName=temp_lang_locale', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150330-ca-1::chris.au
DROP VIEW learningObject_vw
GO

CREATE VIEW learningObject_vw AS select lo.learningid,lo.learningType,lo.mediaid,lo.subject,lo.title,lo.description1,lo.hyperlink,lo.assignedChatRoom,lo.assignedForum,lo.editcontrol,lo.showInCatalog,lo.allowPeerReview,lo.datecreated,lo.autoDelete,lo.deleteDate,lo.picture,lo.evaluation,lo.evalRequired,lo.duration,lo.policyid,lo.org_policy_override,lo.prereqcontrol,lo.testRequired,lo.startAvailabilityDate,lo.endAvailabilityDate,lo.vendorname,lo.allowReopen,lo.testEnablesCompletion,lo.enforcePrePostTestOrder,lo.completionTimeLimit,lo.allowReEnroll,lo.historyExpireWindow,lo.display_course_interest,lo.wikiURL,lo.supervisorAssessment,lo.allowDappraiserAssess,lo.sticky,lo.creator_uid,lo.last_updated,lo.last_updater_uid,lo.mekp_target,lo.update_meaning,lo.wiki_key,lo.wiki_display,lo.archived,lo.updater_legal_name,lo.update_meaning_text,lo.auto_archive_date,lo.show_expiration_date,lo.show_begin_date,lo.subject_category_id,lo.require_esignature,lo.support_contact,lo.domain_id,lo.allow_auto_recommend,lo.allowSupervisorAssess,lo.esig_course_withdraw,lo.esig_course_modify,lo.esig_course_launch,lo.esig_course_import,lo.esig_course_finish,lo.esig_exam_launch,lo.esig_wizard_modify_trans,lo.esig_reviewer_modify_trans,lo.esig_editor_modify_trans,lo.esig_modify_attendance,lo.esig_course_withdraw_meaning,lo.esig_course_modify_meaning,lo.esig_course_launch_meaning,lo.esig_course_import_meaning,lo.esig_course_finish_meaning,lo.esig_exam_launch_meaning,lo.esig_wzrd_modify_trans_meaning,lo.esig_rvwr_modify_trans_meaning,lo.esig_edtr_modify_trans_meaning,lo.esig_modify_attendance_meaning,lo.esig_equi_rule,lo.esig_equi_rule_meaning,lo.title_key,lo.description_key,lo.support_contact_key, lang.ilangid as language, lang.locale 
			from learningObject lo
			left join language_bundle lang on lang.object_id = lo.learningid and lang.object_type = 'LO' and is_primary = 'Y'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150330-ca-1', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 323, '8:0c09893e4eccb877a6807ce02a9e645a', 'dropView viewName=learningObject_vw; createView viewName=learningObject_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150326-tz-1::ted.zhang
ALTER TABLE article ADD articleType nvarchar(1) CONSTRAINT DF_article_articleType DEFAULT 'N' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'article'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.article'; DECLARE @ColumnName SYSNAME set @ColumnName = N'articleType'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'N=news;L=newsletter;T=template';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150326-tz-1', 'ted.zhang', 'nschema/111.changeLog.xml', GETDATE(), 324, '8:39b80e21dec88429f5472b863b83f187', 'addColumn tableName=article', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150331-dc-0::dennis.cheung
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR' AND name =
			'i_transcript'
			)
			DROP TRIGGER i_transcript
GO

IF EXISTS ( SELECT 1 FROM sys.objects WHERE type = 'TR' AND name
			= 'u_transcript'
			) DROP TRIGGER u_transcript
GO

IF EXISTS ( SELECT 1 FROM sys.objects WHERE type = 'TR' AND name
			= 'd_transcript' ) DROP TRIGGER d_transcript
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150331-dc-0', 'dennis.cheung', 'nschema/111.changeLog.xml', GETDATE(), 325, '8:802b3dba4f9b82d3235696c37deef8bb', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150331-dc-1::dennis.cheung
ALTER TABLE transcript ADD bundle_id nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'transcript'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.transcript'; DECLARE @ColumnName SYSNAME set @ColumnName = N'bundle_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Bundle ID (refers to     language_bundle.bundle_id)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

UPDATE transcript
			SET transcript.bundle_id=lb.bundle_id
			FROM language_bundle lb
			WHERE lb.object_id=transcript.learningid
			AND lb.object_type='LO'
			AND lb.is_primary='Y'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150331-dc-1', 'dennis.cheung', 'nschema/111.changeLog.xml', GETDATE(), 326, '8:624445ec3042de08f42af7d673530ab4', 'addColumn tableName=transcript; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150331-dc-2::dennis.cheung
ALTER TABLE transcript ALTER COLUMN bundle_id nvarchar(85) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150331-dc-2', 'dennis.cheung', 'nschema/111.changeLog.xml', GETDATE(), 327, '8:78306997d19719cd676ab63af2d7ad40', 'addNotNullConstraint columnName=bundle_id, tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150331-dc-3::dennis.cheung
ALTER TABLE transcript ADD CONSTRAINT FK_TRANSCRIPT_BUNDLE_ID FOREIGN KEY (bundle_id) REFERENCES language_bundle (bundle_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150331-dc-3', 'dennis.cheung', 'nschema/111.changeLog.xml', GETDATE(), 328, '8:77ec01e9b3ad23f01f93af1fa3b8fd19', 'addForeignKeyConstraint baseTableName=transcript, constraintName=FK_TRANSCRIPT_BUNDLE_ID, referencedTableName=language_bundle', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150409-rc-1::ryan.chen
-- [DEV 8519] Prerequisite Checks for Program Module Launch
ALTER TABLE sessiondetail ADD enforce_mod_preq char(1) CONSTRAINT DF_sessiondetail_enforce_mod_preq DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150409-rc-1', 'ryan.chen', 'nschema/111.changeLog.xml', GETDATE(), 329, '8:15f7c22d73087e438c4e9fec78551573', 'addColumn tableName=sessiondetail', '[DEV 8519] Prerequisite Checks for Program Module Launch', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150410-dr-7::dreidel.regala
CREATE TABLE dynamic_resource_attribute (attribute_id nvarchar(15) NOT NULL, code nvarchar(85) NOT NULL, label_key nvarchar(85), visibility nvarchar(15))
GO

ALTER TABLE dynamic_resource_attribute ADD CONSTRAINT PK_DYNAMIC_RES_ATTRIBUTE PRIMARY KEY (attribute_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150410-dr-7', 'dreidel.regala', 'nschema/111.changeLog.xml', GETDATE(), 330, '8:ca4ccda697f79d863661f2a284fa0a7a', 'createTable tableName=dynamic_resource_attribute; addPrimaryKey constraintName=PK_DYNAMIC_RES_ATTRIBUTE, tableName=dynamic_resource_attribute', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150410-sn-1::sam.ng
-- [DEV 8482] New table for course bundles
CREATE TABLE course_bundle (bundle_id nvarchar(85) NOT NULL, courseid nvarchar(85) NOT NULL, effective_revision int NOT NULL, approved_revision int, launchInterface smallint NOT NULL, organizationID nvarchar(15), itemID nvarchar(15), launchpage nvarchar (max), launchParms nvarchar(255), accumulateTime char(1), skillsoft_course_type int, bookmark char(1), navigationTreeVisible nvarchar(15), previousNextButtonsVisible char(1), launched_win_width int, launched_win_height int, ieCompatibilityMode char(1), last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.course_bundle';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the details of course bundles, including revision info and launch properties.';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.name = N'MS_Description' AND ep.minor_id=0); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'bundle_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID (primary key)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'courseid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course ID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'effective_revision'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Effective revision for this course bundle';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'approved_revision'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Approved revision for this course bundle';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'launchInterface'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Launch interface, learning type (0/1/5) 0 - On The Job Training, 1 - External Certification, 5 - Exam/Coaching/Internship/Online/Self Training Paper/Self Training Video/Special Interest Group';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'organizationID'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Organization ID for the latest non-preview revision of this course bundle, maps to ContentOrganization (organizationID)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'itemID'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course Item ID for the latest non-preview revision of this course bundle, maps to ContentItem (itemID)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'launchpage'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Launch page information';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'launchParms'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Launch parameters e.g. LREADONLY=Y';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'accumulateTime'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Accumulate Course Time? (Y/N)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'skillsoft_course_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Skillsoft course type';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'bookmark'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'(For multi-sco course only) Allows Bookmark? (Y/N/Null)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'navigationTreeVisible'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'(For multi-sco course only) Is NavigationTree Visible to User? (Y/N/Null)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'previousNextButtonsVisible'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'(For multi-sco course only) Show Previous and Next Buttons? (Y/N/Null)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'launched_win_width'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The user-defined width of the popup window of the launched course.';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'launched_win_height'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The user-defined height of the popup window of the launched course.';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'ieCompatibilityMode'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The IE Compatibility Mode';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that the record was updated';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'updater_legal_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'e-Signature property: legal name of the updater';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'update_meaning_text'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'e-Signature property: contents of update meaning';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updater_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'e-Signature property: user ID of updater';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE course_bundle ADD CONSTRAINT PK_COURSE_BUNDLE PRIMARY KEY (bundle_id)
GO

INSERT INTO course_bundle(bundle_id, courseid, effective_revision, approved_revision, launchInterface,
        	                            organizationID, itemID, launchpage, launchParms, accumulateTime, skillsoft_course_type,
        	                            bookmark, navigationTreeVisible, previousNextButtonsVisible,
        	                            launched_win_width, launched_win_height, ieCompatibilityMode)
        	SELECT bundle_id, courseid, effective_revision, approved_revision, launchInterface,
        	       organizationID, itemID, launchpage, launchParms, accumulateTime, skillsoft_course_type,
        	       bookmark, navigationTreeVisible, previousNextButtonsVisible,
        	       launched_win_width, launched_win_height, ieCompatibilityMode
            FROM course c JOIN language_bundle b ON c.courseid = b.object_id and b.object_type = 'LO' and b.is_primary = 'Y'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150410-sn-1', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 331, '8:d57659cab4a5e0db85174ad7c15fe9e3', 'createTable tableName=course_bundle; addPrimaryKey constraintName=PK_COURSE_BUNDLE, tableName=course_bundle; sql', '[DEV 8482] New table for course bundles', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150410-sn-2::sam.ng
IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'i_course_revision'
			)
			DROP TRIGGER i_course_revision
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'u_course_revision'
			)
			DROP TRIGGER u_course_revision
GO

IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'd_course_revision'
			)
			DROP TRIGGER d_course_revision
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150410-sn-2', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 332, '8:e3a505088ca2481b1e56f68084eddffd', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150519-sn-1::sam.ng
-- 20150519-sn-? are to ensure that each row in course_revision has bundle_id
CREATE TABLE course_revision_migrated (course_id nvarchar(85) NOT NULL, revision_number int NOT NULL, date_revised datetime, launch_interface smallint, organization_id nvarchar(15), item_id nvarchar(15), encoding nvarchar(25), revision_comment nvarchar (max), allow_download char(1), publish_for char(1), update_meaning nvarchar(8), last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar (max), last_updater_uid nvarchar(85), available char(1), course_raw_byte_size int, date_effective datetime, date_approved datetime)
GO

insert into course_revision_migrated(course_id, revision_number, date_revised, launch_interface, organization_id,
	        										 item_id, encoding, revision_comment, allow_download, publish_for,
	        										 update_meaning, last_updated, last_updater_uid, updater_legal_name, update_meaning_text, 
	        										 available, course_raw_byte_size, date_effective, date_approved)
	        	select course_id, revision_number, date_revised, launch_interface, organization_id,
	        		   item_id, encoding, revision_comment, allow_download, publish_for,
	        		   update_meaning, last_updated, last_updater_uid, updater_legal_name, update_meaning_text,
	        		   available, course_raw_byte_size, date_effective, date_approved
	        	from course_revision
	        	where not exists (SELECT bundle_id
								 FROM language_bundle b
								 WHERE course_id = b.object_id and b.object_type = 'LO' and b.is_primary = 'Y')
GO

delete from course_revision
	        	where not exists (SELECT bundle_id
								 FROM language_bundle b
								 WHERE course_id = b.object_id and b.object_type = 'LO' and b.is_primary = 'Y')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150519-sn-1', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 333, '8:e2cdefc1693712931e94f3d87f03a0f3', 'createTable tableName=course_revision_migrated; sql; sql', '20150519-sn-? are to ensure that each row in course_revision has bundle_id', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150410-sn-3::sam.ng
-- [DEV 8482] New column for course revisions
ALTER TABLE course_revision ADD bundle_id nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course_revision'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course_revision'; DECLARE @ColumnName SYSNAME set @ColumnName = N'bundle_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Language Bundle ID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE course_revision DROP CONSTRAINT pk_course_revision
GO

UPDATE course_revision
        	SET bundle_id = (SELECT bundle_id
							 FROM language_bundle b
							 WHERE course_id = b.object_id and b.object_type = 'LO' and b.is_primary = 'Y')
GO

ALTER TABLE course_revision ALTER COLUMN bundle_id nvarchar(85) NOT NULL
GO

ALTER TABLE course_revision ADD CONSTRAINT pk_course_revision PRIMARY KEY (course_id, revision_number, bundle_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150410-sn-3', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 334, '8:5c4712721f95d4c29503f0c0426e8414', 'addColumn tableName=course_revision; dropPrimaryKey constraintName=pk_course_revision, tableName=course_revision; sql; addNotNullConstraint columnName=bundle_id, tableName=course_revision; addPrimaryKey constraintName=pk_course_revision, tableName...', '[DEV 8482] New column for course revisions', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/111.changeLog.xml::20150519-sn-2::sam.ng
