DROP TABLE course_revision_migrated
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150519-sn-2', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 335, '8:c8a5c2098db7d8b95c09e35cdbbdf9a6', 'dropTable tableName=course_revision_migrated', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150414-sn-1::sam.ng
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, lang.bundle_id  
			from learningObject lo
			inner 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 ('20150414-sn-1', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 336, '8:792225ba46b17d830bc68167765164c7', 'dropView viewName=learningObject_vw; createView viewName=learningObject_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150414-dr-8::dreidel.regala
CREATE TABLE class_resource_attribute (resource_id nvarchar(15) NOT NULL, attribute_id nvarchar(15) NOT NULL, attribute_value_data nvarchar(2000), update_meaning nvarchar(8), last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar (max), last_updater_uid nvarchar(85))
GO

ALTER TABLE class_resource_attribute ADD CONSTRAINT PK_CLS_RES_ATTRIBUTE PRIMARY KEY (resource_id, attribute_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150414-dr-8', 'dreidel.regala', 'nschema/111.changeLog.xml', GETDATE(), 337, '8:455a84d545d6f1c91f8957992a4dd31f', 'createTable tableName=class_resource_attribute; addPrimaryKey constraintName=PK_CLS_RES_ATTRIBUTE, tableName=class_resource_attribute', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-js-1::janice.sung
ALTER TABLE code_pool
			DROP CONSTRAINT DF_code_pool_auto_exempt,
			COLUMN
			auto_exempt
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150415-js-1', 'janice.sung', 'nschema/111.changeLog.xml', GETDATE(), 338, '8:25029eaf7f900e92e52bb50c1eaa2579', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-js-3::janice.sung
DROP TABLE exam_pool_auto_exemption
GO

CREATE TABLE question_pool_auto_exemption (id nvarchar(85) NOT NULL, poolid 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'question_pool_auto_exemption'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.question_pool_auto_exemption';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the question pool auto exempt learning module or session 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'question_pool_auto_exemption'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_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'question_pool_auto_exemption'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_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'question_pool_auto_exemption'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_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'question_pool_auto_exemption'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_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 question_pool_auto_exemption ADD CONSTRAINT PK_QUESTION_POOL_AUTO_EXEMPT PRIMARY KEY (id)
GO

ALTER TABLE question_pool_auto_exemption ADD CONSTRAINT FK_QUES_POOL_AUTO_EXEMPT_PID FOREIGN KEY (poolid) REFERENCES code_pool (poolid)
GO

ALTER TABLE question_pool_auto_exemption ADD CONSTRAINT FK_QUES_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 ('20150415-js-3', 'janice.sung', 'nschema/111.changeLog.xml', GETDATE(), 339, '8:84b5e90059c72f9e9c9d39c4a75b91de', 'dropTable tableName=exam_pool_auto_exemption; createTable tableName=question_pool_auto_exemption; addPrimaryKey constraintName=PK_QUESTION_POOL_AUTO_EXEMPT, tableName=question_pool_auto_exemption; addForeignKeyConstraint baseTableName=question_poo...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-kh-1::kelvin.hung
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 ('20150415-kh-1', 'kelvin.hung', 'nschema/111.changeLog.xml', GETDATE(), 340, '8:34418fc0abede7ec2e6ba0f7dd056390', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-kh-2::kelvin.hung
-- [DEV 8524] New column for Course Downloads
ALTER TABLE courseDownloads ADD bundle_id nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'courseDownloads'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.courseDownloads'; 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

UPDATE courseDownloads
        	SET bundle_id = (SELECT bundle_id
							 FROM language_bundle b
							 WHERE courseid = b.object_id and b.object_type = 'LO' and b.is_primary = 'Y')
			WHERE session_id is NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150415-kh-2', 'kelvin.hung', 'nschema/111.changeLog.xml', GETDATE(), 341, '8:37fbc7267e170a8b1df006c0a534e75b', 'addColumn tableName=courseDownloads; sql', '[DEV 8524] New column for Course Downloads', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-jw-1::joe.wong
ALTER TABLE course_bundle ADD instantAccess char(1)
GO

ALTER TABLE course_bundle ADD show_toolbars char(1) CONSTRAINT DF_course_bundle_show_toolbars DEFAULT 'N'
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'instantAccess'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Allow direct launch';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'show_toolbars'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Show browser toolbar in launch window';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 ('20150415-jw-1', 'joe.wong', 'nschema/111.changeLog.xml', GETDATE(), 342, '8:9094ff4a9be3374a4a301e56490f519f', 'addColumn tableName=course_bundle', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-jw-2::joe.wong
update course_bundle 
			set show_toolbars = (select show_toolbars from course c where c.courseid = course_bundle.courseid), 
			    instantAccess = (select instantAccess from course c where c.courseid = course_bundle.courseid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150415-jw-2', 'joe.wong', 'nschema/111.changeLog.xml', GETDATE(), 343, '8:90a7680b7b2194c2260f139113a4b352', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-jw-3::joe.wong
ALTER TABLE course_bundle ALTER COLUMN instantAccess char(1) NOT NULL
GO

ALTER TABLE course_bundle ALTER COLUMN show_toolbars char(1) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150415-jw-3', 'joe.wong', 'nschema/111.changeLog.xml', GETDATE(), 344, '8:61d5ab9017bedeb640e2a7f9c414f756', 'addNotNullConstraint columnName=instantAccess, tableName=course_bundle; addNotNullConstraint columnName=show_toolbars, tableName=course_bundle', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

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

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

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

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 ('20150415-vn-6', 'vincent.ni', 'nschema/111.changeLog.xml', GETDATE(), 345, '8:1c1cfb983ee0b188ce6f87c9cd0255e6', 'sql; sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-vn-7::vincent.ni
ALTER TABLE cschedule ADD bundle_id nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'cschedule'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cschedule'; DECLARE @ColumnName SYSNAME set @ColumnName = N'bundle_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'session 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 cschedule ADD CONSTRAINT FK_BUNDLE_ID_CSCHEDULE FOREIGN KEY (bundle_id) REFERENCES language_bundle (bundle_id)
GO

UPDATE cschedule
			SET cschedule.bundle_id = A.bundle_id
			FROM cschedule
			INNER JOIN (
				SELECT lb.object_id
					,lb.bundle_id
				FROM language_bundle lb
					,learningObject lo
				WHERE lb.object_id = lo.learningid
					AND lo.learningType IN ('R','W')
					AND lb.object_type = 'LO'
					AND lb.is_primary = 'Y'
				) A ON cschedule.courseid = A.object_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150415-vn-7', 'vincent.ni', 'nschema/111.changeLog.xml', GETDATE(), 346, '8:a62f037893ccc58f7e874cf4914dd5d8', 'addColumn tableName=cschedule; addForeignKeyConstraint baseTableName=cschedule, constraintName=FK_BUNDLE_ID_CSCHEDULE, referencedTableName=language_bundle; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-vn-8::vincent.ni
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 ('20150415-vn-8', 'vincent.ni', 'nschema/111.changeLog.xml', GETDATE(), 347, '8:fdf566adca682ef65e0f296972e4d91d', 'dropView viewName=cschedule_vw; createView viewName=cschedule_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150415-vn-9::vincent.ni
ALTER TABLE sessiondetail ADD bundle_id nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'sessiondetail'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.sessiondetail'; DECLARE @ColumnName SYSNAME set @ColumnName = N'bundle_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'session 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 sessiondetail ADD CONSTRAINT FK_BUNDLE_ID_SESSIONDETAIL FOREIGN KEY (bundle_id) REFERENCES language_bundle (bundle_id)
GO

UPDATE sessiondetail
			SET sessiondetail.bundle_id = lb.bundle_id
			FROM sessiondetail
			INNER JOIN language_bundle lb ON sessiondetail.currid = lb.object_id
				AND lb.is_primary = 'Y'
				AND lb.object_type = 'LO'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150415-vn-9', 'vincent.ni', 'nschema/111.changeLog.xml', GETDATE(), 348, '8:e24986f56c25db1d54777b1f0582dc6d', 'addColumn tableName=sessiondetail; addForeignKeyConstraint baseTableName=sessiondetail, constraintName=FK_BUNDLE_ID_SESSIONDETAIL, referencedTableName=language_bundle; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150416-nl-1::nick.lee
CREATE TABLE auto_exempt_policy (id nvarchar(85) NOT NULL, code nvarchar(85) NOT NULL, name nvarchar(85) NOT NULL, last_updated datetime, updater_legal_name nvarchar(250), CONSTRAINT PK_AUTO_EXEMPT_POLICY PRIMARY KEY (id), UNIQUE (code))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'auto_exempt_policy'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.auto_exempt_policy';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Automatic Exemption Policy';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'auto_exempt_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.auto_exempt_policy'; 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'auto_exempt_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.auto_exempt_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'code'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique code';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'auto_exempt_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.auto_exempt_policy'; 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'auto_exempt_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.auto_exempt_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'updater_legal_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150416-nl-1', 'nick.lee', 'nschema/111.changeLog.xml', GETDATE(), 349, '8:932f21a4d0ce92e1f33b4fb50400cca0', 'createTable tableName=auto_exempt_policy', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150417-sn-1::sam.ng
ALTER TABLE course_bundle ADD CONSTRAINT FK_BUNDLE_ID_COURSE_BUNDLE FOREIGN KEY (bundle_id) REFERENCES language_bundle (bundle_id)
GO

ALTER TABLE course_revision ADD CONSTRAINT FK_BUNDLE_ID_COURSE_REVISION 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 ('20150417-sn-1', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 350, '8:86a64ad6d0cc0af3f85943a904aebfc9', 'addForeignKeyConstraint baseTableName=course_bundle, constraintName=FK_BUNDLE_ID_COURSE_BUNDLE, referencedTableName=language_bundle; addForeignKeyConstraint baseTableName=course_revision, constraintName=FK_BUNDLE_ID_COURSE_REVISION, referencedTabl...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150420-kc-1::kalam.chan
-- [DEV 8553] New table for relation of auto exemption policy and code pools
CREATE TABLE auto_exempt_code_pool_rlt (policy_id nvarchar(85) NOT NULL, learningid nvarchar(85) NOT NULL, sessionid nvarchar(15))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'auto_exempt_code_pool_rlt'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.auto_exempt_code_pool_rlt';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table relates auto exempt policy and question pools.';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'auto_exempt_code_pool_rlt'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.auto_exempt_code_pool_rlt'; DECLARE @ColumnName SYSNAME set @ColumnName = N'policy_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Auto exemption policy 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'auto_exempt_code_pool_rlt'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.auto_exempt_code_pool_rlt'; 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'auto_exempt_code_pool_rlt'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.auto_exempt_code_pool_rlt'; 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 auto_exempt_code_pool_rlt ADD CONSTRAINT FK_AUTO_EXEMPT_CODE_POOL_PID FOREIGN KEY (policy_id) REFERENCES auto_exempt_policy (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150420-kc-1', 'kalam.chan', 'nschema/111.changeLog.xml', GETDATE(), 351, '8:2bdbeec25184adb46207569c77b6658d', 'createTable tableName=auto_exempt_code_pool_rlt; addForeignKeyConstraint baseTableName=auto_exempt_code_pool_rlt, constraintName=FK_AUTO_EXEMPT_CODE_POOL_PID, referencedTableName=auto_exempt_policy', '[DEV 8553] New table for relation of auto exemption policy and code pools', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150421-dr-9::dreidel.regala
ALTER TABLE course_interest ADD is_notified char(1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150421-dr-9', 'dreidel.regala', 'nschema/111.changeLog.xml', GETDATE(), 352, '8:01b4653a46b033e79aa37484f7a9bc90', 'addColumn tableName=course_interest', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150423-nl-1::nick.lee
ALTER TABLE transcript ADD allowBundleSwitch char(1) CONSTRAINT DF_transcript_allowBundleSwitch DEFAULT 'Y' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150423-nl-1', 'nick.lee', 'nschema/111.changeLog.xml', GETDATE(), 353, '8:5064af86f6ec3cb4f9c2eba1503eec82', 'addColumn tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150423-kc-1::kalam.chan
ALTER TABLE code_testpool ADD auto_exempt_enabled nvarchar(1) CONSTRAINT DF_code_testpool_auto_exempt_enabled DEFAULT 'D' NOT NULL
GO

ALTER TABLE code_testpool ADD policy_id nvarchar(85)
GO

ALTER TABLE code_testpool ADD CONSTRAINT FK_CODE_TESTPOOL_PID FOREIGN KEY (policy_id) REFERENCES auto_exempt_policy (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150423-kc-1', 'kalam.chan', 'nschema/111.changeLog.xml', GETDATE(), 354, '8:f7362608a3313a9d643de504ac159604', 'addColumn tableName=code_testpool; addForeignKeyConstraint baseTableName=code_testpool, constraintName=FK_CODE_TESTPOOL_PID, referencedTableName=auto_exempt_policy', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150423-dr-10::dreidel.regala
ALTER TABLE dynamic_resource_attribute ADD type nvarchar(15)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150423-dr-10', 'dreidel.regala', 'nschema/111.changeLog.xml', GETDATE(), 355, '8:bcafb07754b9e06304e63f11568a730b', 'addColumn tableName=dynamic_resource_attribute', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150424-dc-1::dennis.cheung
UPDATE transcript SET revision_number = (
				select MAX(r.revision_number) 
				from course_revision r
				join course c
				on r.course_id = c.courseid
				where r.course_id = transcript.learningid
				and r.bundle_id= transcript.bundle_id 
				and r.date_effective is not null
				AND
				(
					transcript.startdate< r.date_effective
					AND r.revision_number <= c.effective_revision
					AND
					(
						(
						  	   transcript.studentstatus in  (1,2,5,6,7,11,18,23,24,25,26)
								AND r.publish_for in  ('A','C','D','P','B')
						)OR(
							     transcript.studentstatus not in (1,2,5,6,7,11,18,23,24,25,26,4,13,22,14,15)
								AND r.publish_for in  ('A','D','P','B')
						)OR(
								transcript.studentstatus in  (4,13,22,14,15)  AND r.publish_for in  ('A')  )
					)OR(
						r.date_effective <= transcript.startdate
					)
				)
			) where revision_number is null
GO

update transcript
			set revision_number = (
				select effective_revision
				from course_bundle c
				where c.courseid = transcript.learningid
				and c.bundle_id=transcript.bundle_id
			)
			where revision_number is null
GO

update transcript
			set revision_number = 0
			where revision_number is null
GO

ALTER TABLE transcript ALTER COLUMN revision_number int NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150424-dc-1', 'dennis.cheung', 'nschema/111.changeLog.xml', GETDATE(), 356, '8:ab0bd4176c0cff3b11b3cfc2ac346f96', 'sql; sql; sql; addNotNullConstraint columnName=revision_number, tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150323-yt-1::yan.tung
ALTER TABLE prereq ADD is_mandatory char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'prereq'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.prereq'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_mandatory'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'To support mandatory / optional prerequisite configurations';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-yt-1', 'yan.tung', 'nschema/111.changeLog.xml', GETDATE(), 357, '8:d8cd1ad69017dc21c4516bde79f0943d', 'addColumn tableName=prereq', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150423-yt-2::yan.tung
ALTER TABLE prereq DROP CONSTRAINT fk_prereq_prereqid
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150423-yt-2', 'yan.tung', 'nschema/111.changeLog.xml', GETDATE(), 358, '8:d85c5cf2076d816b357224c5a62641f9', 'dropForeignKeyConstraint baseTableName=prereq, constraintName=fk_prereq_prereqid', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150430-bc-1::ben.chan
CREATE TABLE enrollment_restriction (restriction_id int NOT NULL, learning_id nvarchar(85) NOT NULL, session_id nvarchar(15), start_date datetime, end_date datetime, restriction_process nvarchar(85) NOT NULL, CONSTRAINT PK_ENROLLMENT_RESTRICTION PRIMARY KEY (restriction_id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'enrollment_restriction'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.enrollment_restriction';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Enrollment Restriction';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'enrollment_restriction'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction'; DECLARE @ColumnName SYSNAME set @ColumnName = N'restriction_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'enrollment_restriction'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction'; DECLARE @ColumnName SYSNAME set @ColumnName = N'start_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Start date of the restriction';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'enrollment_restriction'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction'; DECLARE @ColumnName SYSNAME set @ColumnName = N'end_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'End date of the restriction';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'enrollment_restriction'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction'; DECLARE @ColumnName SYSNAME set @ColumnName = N'restriction_process'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'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

CREATE TABLE enrollment_restriction_rule (rule_id int NOT NULL, restriction_id int NOT NULL, enrollment_limit_per_org int NOT NULL, CONSTRAINT PK_ENROLLMENT_RESTRICTION_RULE PRIMARY KEY (rule_id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'enrollment_restriction_rule'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.enrollment_restriction_rule';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Enrollment Restriction Rules';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'enrollment_restriction_rule'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction_rule'; DECLARE @ColumnName SYSNAME set @ColumnName = N'rule_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'enrollment_restriction_rule'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction_rule'; DECLARE @ColumnName SYSNAME set @ColumnName = N'restriction_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Restriction ID (foreign 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

CREATE TABLE enrollment_restriction_jp (rule_id int NOT NULL, jobprofile_id nvarchar(15) NOT NULL, CONSTRAINT PK_ENROLLMENT_RESTRICTION_JP PRIMARY KEY (rule_id, jobprofile_id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'enrollment_restriction_jp'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.enrollment_restriction_jp';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Enrollment Restriction Rules Job Profile';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'enrollment_restriction_jp'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction_jp'; DECLARE @ColumnName SYSNAME set @ColumnName = N'rule_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Rule 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'enrollment_restriction_jp'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction_jp'; DECLARE @ColumnName SYSNAME set @ColumnName = N'jobprofile_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Job profile ID (foreign 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

CREATE TABLE enrollment_restriction_org (rule_id int NOT NULL, organization_id nvarchar(15) NOT NULL, CONSTRAINT PK_ENROLLMENT_RESTRICTION_ORG PRIMARY KEY (rule_id, organization_id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'enrollment_restriction_org'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.enrollment_restriction_org';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Enrollment Restriction Rules Job Organization';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'enrollment_restriction_org'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction_org'; DECLARE @ColumnName SYSNAME set @ColumnName = N'rule_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Rule 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'enrollment_restriction_org'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.enrollment_restriction_org'; DECLARE @ColumnName SYSNAME set @ColumnName = N'organization_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Organization ID (foreign 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

ALTER TABLE enrollment_restriction_org ADD CONSTRAINT FK_RULE_ORG_RULE_ID FOREIGN KEY (rule_id) REFERENCES enrollment_restriction_rule (rule_id)
GO

ALTER TABLE enrollment_restriction_jp ADD CONSTRAINT FK_RULE_JPROFILE_RULE_ID FOREIGN KEY (rule_id) REFERENCES enrollment_restriction_rule (rule_id)
GO

ALTER TABLE enrollment_restriction_rule ADD CONSTRAINT FK_RESTRICTION_ID FOREIGN KEY (restriction_id) REFERENCES enrollment_restriction (restriction_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150430-bc-1', 'ben.chan', 'nschema/111.changeLog.xml', GETDATE(), 359, '8:e57bb7b7a59ab04f214574d098df4ac5', 'createTable tableName=enrollment_restriction; createTable tableName=enrollment_restriction_rule; createTable tableName=enrollment_restriction_jp; createTable tableName=enrollment_restriction_org; addForeignKeyConstraint baseTableName=enrollment_re...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150505-js-2::janice.sung
insert into language_bundle(bundle_id, object_id, object_type, locale, is_primary)
			select CAST(ROW_NUMBER() OVER (ORDER BY certid) AS VARCHAR) + 'CR', CAST(certid AS NVARCHAR(85)), 'CR', '*ALL*', 'Y' FROM certifications
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150505-js-2', 'janice.sung', 'nschema/111.changeLog.xml', GETDATE(), 360, '8:55087a3e084f3caaec9619f0caf0b64f', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150505-js-5::janice.sung
IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'TR' AND name = 'i_certifications'
            )
            DROP TRIGGER i_certifications
GO

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

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150505-js-5', 'janice.sung', 'nschema/111.changeLog.xml', GETDATE(), 361, '8:ac846966688e4ff5f8bcf953503ae364', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150505-js-6::janice.sung
ALTER TABLE certifications ADD title_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certifications'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certifications'; 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 certifications ADD description_key nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certifications'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certifications'; 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 ('20150505-js-6', 'janice.sung', 'nschema/111.changeLog.xml', GETDATE(), 362, '8:009a78f3446b1d035884fb9b5230c80a', 'addColumn tableName=certifications; addColumn tableName=certifications', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150505-js-8::janice.sung
insert into resource_string(id, resource_key, bundle_id, value_varchar) select bundle_id + 'title', bundle_id + 'title', bundle_id, title from certifications c join language_bundle b on CAST(c.certid  AS NVARCHAR(85)) = b.object_id and b.object_type = 'CR'
GO

insert into resource_string(id, resource_key, bundle_id, value_varchar) select bundle_id + 'desc', bundle_id + 'desc', bundle_id, description from certifications c join language_bundle b on CAST(c.certid  AS NVARCHAR(85)) = b.object_id and b.object_type = 'CR'
GO

update certifications set title_key = (select bundle_id + 'title' from language_bundle b where CAST(certid  AS NVARCHAR(85)) = b.object_id and b.object_type = 'CR')
			, description_key = (select bundle_id + 'desc' from language_bundle b where CAST(certid  AS NVARCHAR(85)) = b.object_id and b.object_type = 'CR')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150505-js-8', 'janice.sung', 'nschema/111.changeLog.xml', GETDATE(), 363, '8:a1e9600ab263eb0620aa279f28f2fcf6', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150511-ca-1::chris.au
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,
		    a.last_updated,
		    a.updater_legal_name,
		    a.update_meaning_text,
		    a.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,
		    a.bundle_id,
		    a.allowBundleSwitch,
			lang.ilangid,
			lang.locale
		  FROM transcript a, language_bundle lang
		  WHERE studentstatus NOT IN (8, 9, 10)
		  and a.bundle_id = lang.bundle_id
		  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,
		    b.bundle_id,
		    b.allowBundleSwitch,
			lang.ilangid,
			lang.locale
		  FROM transcript a, transcript b, language_bundle lang
		  WHERE a.studentstatus = 8 AND b.transcriptid = a.creditTranscriptLink AND b.studentstatus NOT IN (8, 9, 10)
		  and b.bundle_id = lang.bundle_id
GO

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

-- Changeset nschema/111.changeLog.xml::20150511-ca-2::chris.au
IF EXISTS (
			SELECT 1
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'i_withdrawnStudents'
			)
			DROP TRIGGER i_withdrawnStudents
GO

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

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150511-ca-2', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 365, '8:c39c2c858117c5992ee3c2de23f773c6', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150511-ca-3::chris.au
ALTER TABLE withdrawnStudents ADD bundle_id nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'withdrawnStudents'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawnStudents'; 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 withdrawnStudents
			SET bundle_id = (SELECT bundle_id
			FROM transcript t WHERE withdrawnStudents.transcriptid = t.transcriptid)
			WHERE bundle_id is null
GO

UPDATE withdrawnStudents
			SET bundle_id = (SELECT bundle_id
			FROM language_bundle lang WHERE lang.object_id = withdrawnStudents.learningid and lang.object_type = 'LO' and is_primary = 'Y')
			WHERE bundle_id is null
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150511-ca-3', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 366, '8:664fc98a7168dd83dd812434a72b46e4', 'addColumn tableName=withdrawnStudents; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150511-kc-1::kelvin.chan
ALTER TABLE article ADD picture_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'picture_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for picture';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 downloadurl_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'downloadurl_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Resource key for url';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 ('20150511-kc-1', 'kelvin.chan', 'nschema/111.changeLog.xml', GETDATE(), 367, '8:ea25c32e968bce17cc579c433d41f931', 'addColumn tableName=article; addColumn tableName=article', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150511-kc-3::kelvin.chan
insert into resource_string(id, resource_key, bundle_id, value_varchar)
			select bundle_id + 'picture', bundle_id + 'picture', bundle_id, picture
			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 + 'downloadURL', bundle_id + 'downloadURL', bundle_id, downloadURL
			from article a join language_bundle b on a.artid = b.object_id and b.object_type = 'NW'
GO

update article set
			picture_key = (select bundle_id + 'picture' from language_bundle b where artid = b.object_id and b.object_type = 'NW'), 
			downloadurl_key = (select bundle_id + 'downloadURL' 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 ('20150511-kc-3', 'kelvin.chan', 'nschema/111.changeLog.xml', GETDATE(), 368, '8:1c21778a2ac230c4e01a4742d29ea4c7', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150514-ca-1::chris.au
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,
			t1.bundle_id,
		    t1.allowBundleSwitch,
			case when lang.ilangid is null then lang.locale else cast(lang.ilangid as char(5)) end language_tag
		  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
			  inner join language_bundle lang on lang.bundle_id = t1.bundle_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,
			b.bundle_id,
		    b.allowBundleSwitch,
			case when lang.ilangid is null then lang.locale else cast(lang.ilangid as char(5)) end language_tag
		  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
			  inner join language_bundle lang on b.bundle_id = lang.bundle_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 ('20150514-ca-1', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 369, '8:49ba2485e28d87753caa87fa237a295d', 'dropView viewName=aggregated_transcript_vw; createView viewName=aggregated_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150512-sn-1::sam.ng
DROP TABLE transcript_equivalent
GO

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, bundle_id nvarchar(85), effective_revision int)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150512-sn-1', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 370, '8:f5d9565f89ef26b24e1afde039ac9726', 'dropTable tableName=transcript_equivalent; createTable tableName=transcript_equivalent', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150521-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, lang.bundle_id, 
			case when lang.ilangid is null then lang.locale else cast(lang.ilangid as char(5)) end language_tag  
			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 ('20150521-ca-1', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 371, '8:34bc84317de1e6ef65c433641bc125ab', 'dropView viewName=learningObject_vw; createView viewName=learningObject_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150522-ge-1::gian.espino
ALTER TABLE certifications ADD awardmethod char(1) CONSTRAINT DF_certifications_awardmethod DEFAULT 'I' NOT NULL
GO

CREATE TABLE certusers_pending (id nvarchar(85) NOT NULL, userid nvarchar(85) NOT NULL, certid numeric(8, 0) NOT NULL, awarddate datetime NOT NULL, awardedby nvarchar(85), comments nvarchar(255), certstatus char(1) NOT NULL, testinstanceid nvarchar(15), learningid nvarchar(85), awardstatus char(1) NOT NULL, last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

ALTER TABLE certusers_pending ADD CONSTRAINT PK_CERTUSERS_PENDING PRIMARY KEY (id)
GO

ALTER TABLE certusers_pending ADD CONSTRAINT FK_CERTUSERS_PENDING_CERTID FOREIGN KEY (certid) REFERENCES certifications (certid)
GO

ALTER TABLE certusers_pending ADD CONSTRAINT FK_CERTUSERS_PENDING_USERID FOREIGN KEY (userid) REFERENCES users_assignment (userid)
GO

CREATE NONCLUSTERED INDEX NUK_CERTUSERS_PENDING_STATUS ON certusers_pending(awardstatus)
GO

CREATE TABLE cert_schedule (certid numeric(8, 0) NOT NULL, awarddate datetime NOT NULL, last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

ALTER TABLE cert_schedule ADD CONSTRAINT PK_CERT_SCHEDULE PRIMARY KEY (certid, awarddate)
GO

ALTER TABLE cert_schedule ADD CONSTRAINT FK_CERT_SCHEDULE_CERTID FOREIGN KEY (certid) REFERENCES certifications (certid)
GO

CREATE NONCLUSTERED INDEX NUK_CERT_SCHEDULE_DATE ON cert_schedule(awarddate)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150522-ge-1', 'gian.espino', 'nschema/111.changeLog.xml', GETDATE(), 372, '8:f9ced2bae1d6d462316a95242d921d58', 'addColumn tableName=certifications; createTable tableName=certusers_pending; addPrimaryKey constraintName=PK_CERTUSERS_PENDING, tableName=certusers_pending; addForeignKeyConstraint baseTableName=certusers_pending, constraintName=FK_CERTUSERS_PENDI...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150522-ge-2::gian.espino
ALTER TABLE sessiondetail ADD enrollBeginDate datetime
GO

ALTER TABLE sessiondetail ADD enrollDeadline datetime
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150522-ge-2', 'gian.espino', 'nschema/111.changeLog.xml', GETDATE(), 373, '8:a8a2f5b6ceb8f8cb8977b4879e16f741', 'addColumn tableName=sessiondetail', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150526-ge-1::gian.espino
CREATE TABLE transcript_stat_log (transcriptid nvarchar(15) NOT NULL, seq int NOT NULL, logdate datetime NOT NULL, fromstatus int NOT NULL, tostatus int NOT NULL, comments nvarchar (max), last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

ALTER TABLE transcript_stat_log ADD CONSTRAINT PK_TRANS_STATLOG PRIMARY KEY (transcriptid, seq)
GO

ALTER TABLE transcript_stat_log ADD CONSTRAINT FK_TRANS_STATLOG_TID FOREIGN KEY (transcriptid) REFERENCES transcript (transcriptid)
GO

CREATE NONCLUSTERED INDEX NUK_TRANS_STATLOG_LOGDATE ON transcript_stat_log(logdate)
GO

CREATE VIEW transcript_stat_log_vw AS select l.transcriptid, l.seq, t.userid, t.learningid, lo.title, l.logdate, l.fromstatus, l.tostatus, l.comments
	    	  from transcript_stat_log l
	    	          inner join valid_transcript_vw t on l.transcriptid = t.transcriptid
	    	          inner join learningObject lo on t.learningid = lo.learningid
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150526-ge-1', 'gian.espino', 'nschema/111.changeLog.xml', GETDATE(), 374, '8:e645d99d2dbe7599afc2842ede68072d', 'createTable tableName=transcript_stat_log; addPrimaryKey constraintName=PK_TRANS_STATLOG, tableName=transcript_stat_log; addForeignKeyConstraint baseTableName=transcript_stat_log, constraintName=FK_TRANS_STATLOG_TID, referencedTableName=transcript...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150527-bc-1::ben.chan
CREATE TABLE template_widget_lboard_config (widget_id nvarchar(15) NOT NULL, title nvarchar(255) NOT NULL, description nvarchar(255) NOT NULL, valid_from datetime, valid_to datetime)
GO

ALTER TABLE template_widget_lboard_config ADD CONSTRAINT PK_LBOARD_CONFIG PRIMARY KEY (widget_id)
GO

ALTER TABLE template_widget_lboard_config ADD CONSTRAINT FK_WIDGET_LBOARD_CONFIG FOREIGN KEY (widget_id) REFERENCES template_widget (id) ON DELETE CASCADE
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150527-bc-1', 'ben.chan', 'nschema/111.changeLog.xml', GETDATE(), 375, '8:df1e4685208320a1a55c6ed6aaae85c7', 'createTable tableName=template_widget_lboard_config; addPrimaryKey constraintName=PK_LBOARD_CONFIG, tableName=template_widget_lboard_config; addForeignKeyConstraint baseTableName=template_widget_lboard_config, constraintName=FK_WIDGET_LBOARD_CONFI...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150526-lt-1::lenny.tang
ALTER TABLE code_training_center ADD organization_tag nvarchar(255)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'code_training_center'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.code_training_center'; DECLARE @ColumnName SYSNAME set @ColumnName = N'organization_tag'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Client Specific Schema. Please refer to CSS013';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 ('20150526-lt-1', 'lenny.tang', 'nschema/111.changeLog.xml', GETDATE(), 376, '8:b7c53883fb99f3762ad8880cc65c5223', 'addColumn tableName=code_training_center', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150526-ca-1::chris.au
ALTER TABLE users_meta ADD locale nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'users_meta'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.users_meta'; 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150526-ca-1', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 377, '8:887a235f11e92a8369a1d6bacb21dc0f', 'addColumn tableName=users_meta', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150526-ca-2::chris.au
CREATE VIEW localized_certusers_vw AS select a.userid, a.certid, case when res.value_varchar is null then c.title else res.value_varchar end as title from certusers a
			inner join users_meta b
			on a.userid = b.user_meta_id
			inner join certifications c
			on a.certid = c.certid
			left join 
				(select ia.certid, ia.userid, max(len(ilang.locale)) as len
				from certusers ia
				inner join users_meta ib
				on ia.userid = ib.user_meta_id
				inner join certifications ic
				on ia.certid = ic.certid
				inner join language_bundle ilang on ic.certid = ilang.object_id and ilang.object_type = 'CR'
				and ib.locale like ilang.locale + '%'
				group by ia.certid, ia.userid) maxlength 
			on maxlength.certid = a.certid and maxlength.userid = a.userid
			left join language_bundle automatched
			on automatched.object_id = a.certid and automatched.object_type = 'CR'
			and len(automatched.locale) = maxlength.len and b.locale like automatched.locale + '%'
			left join resource_string res
			on res.bundle_id = automatched.bundle_id and res.resource_key = c.title_key
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150526-ca-2', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 378, '8:58dac7ddb24342fff44dc98c244425bf', 'createView viewName=localized_certusers_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150526-ca-5::chris.au
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 ('20150526-ca-5', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 379, '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', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150526-ca-6::chris.au
update users_meta set locale = (select locale from temp_lang_locale where cast(languageid as char(2)) = cast(users_meta.languagepref as char(2)))
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 ('20150526-ca-6', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 380, '8:e139fbefa23c2d885f672d8c17b931c1', 'sql; dropTable tableName=temp_lang_locale', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150526-ok-1::oikwan.cheung
ALTER TABLE custom_enroll_attributes ADD session_id nvarchar(15)
GO

ALTER TABLE dynamic_attribute_dropdown_val ADD attribute_description nvarchar(85)
GO

ALTER TABLE custom_enroll_attr_reminder ADD session_id nvarchar(15)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150526-ok-1', 'oikwan.cheung', 'nschema/111.changeLog.xml', GETDATE(), 381, '8:f17368141c500866237a52a13e8fbf1d', 'addColumn tableName=custom_enroll_attributes; addColumn tableName=dynamic_attribute_dropdown_val; addColumn tableName=custom_enroll_attr_reminder', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150526-ok-2::oikwan.cheung
ALTER TABLE custom_enroll_attributes ALTER COLUMN policy_id varchar(15) NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150526-ok-2', 'oikwan.cheung', 'nschema/111.changeLog.xml', GETDATE(), 382, '8:dc25172308eeaf4dc37deb2c4658f485', 'dropNotNullConstraint columnName=policy_id, tableName=custom_enroll_attributes', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150526-ok-3::oikwan.cheung
ALTER TABLE custom_enroll_attr_reminder DROP CONSTRAINT PK_CUST_ENROLL_ATTR_NOTIF
GO

ALTER TABLE custom_enroll_attr_reminder ALTER COLUMN policy_id varchar(15) NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150526-ok-3', 'oikwan.cheung', 'nschema/111.changeLog.xml', GETDATE(), 383, '8:0ea302b73723714a24ae269c0750204c', 'dropPrimaryKey constraintName=PK_CUST_ENROLL_ATTR_NOTIF, tableName=custom_enroll_attr_reminder; dropNotNullConstraint columnName=policy_id, tableName=custom_enroll_attr_reminder', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150428-yt-2::yan.tung
-- New table for evaluation summary
CREATE TABLE evaluation_summary (evaluationsummaryid nvarchar(85) NOT NULL, learningid nvarchar(85) NOT NULL, trainingsessionid nvarchar(85) NOT NULL, evaluationid nvarchar(85) NOT NULL, questionid nvarchar(85) NOT NULL, questionnum smallint NOT NULL, choiceid nvarchar(85), sectionindex smallint NOT NULL, questiontypeCode smallint NOT NULL, count int NOT NULL, textanswer nvarchar(1000), answerkey nvarchar(500), 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'evaluation_summary'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.evaluation_summary';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table used to retain the evaluation summary';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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'evaluationsummaryid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'trainingsessionid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Training Session 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'evaluationid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Evaluation 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'questionid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Question 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'questionnum'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Question sequence number';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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'choiceid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Choice 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'sectionindex'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Question Section Number';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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'questiontypeCode'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Question 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'count'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The count of each answer';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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'textanswer'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The text answer';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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; DECLARE @ColumnName SYSNAME set @ColumnName = N'answerkey'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The Answer Key for each question';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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; 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'evaluation_summary'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.evaluation_summary'; 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150428-yt-2', 'yan.tung', 'nschema/111.changeLog.xml', GETDATE(), 384, '8:c8717de93793a1df49381605556bb11a', 'createTable tableName=evaluation_summary', 'New table for evaluation summary', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150610-rl-1::robert.lowe
ALTER TABLE user_group_mem_otherdetail ALTER COLUMN jobtitle nvarchar(2000)
GO

ALTER TABLE user_group_mem_otherdetail ALTER COLUMN employeeno nvarchar(2000)
GO

ALTER TABLE user_group_mem_otherdetail ALTER COLUMN locationcode nvarchar(2000)
GO

ALTER TABLE user_group_mem_otherdetail ALTER COLUMN departmentid nvarchar(2000)
GO

ALTER TABLE user_group_mem_otherdetail ALTER COLUMN costcenter nvarchar(2000)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150610-rl-1', 'robert.lowe', 'nschema/111.changeLog.xml', GETDATE(), 385, '8:cc759c390428ba29c1fda850d103623f', 'modifyDataType columnName=jobtitle, tableName=user_group_mem_otherdetail; modifyDataType columnName=employeeno, tableName=user_group_mem_otherdetail; modifyDataType columnName=locationcode, tableName=user_group_mem_otherdetail; modifyDataType colu...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

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

CREATE VIEW localized_certusers_vw AS select a.userid, a.certid, a.awardid, case when res_title.value_varchar is null then c.title else res_title.value_varchar end as title 
			, case when res_desc.value_varchar is null then c.description else res_desc.value_varchar end as description
			from certusers a
			inner join users_meta b
			on a.userid = b.user_meta_id
			inner join certifications c
			on a.certid = c.certid
			left join 
				(select ia.certid, ia.userid, max(len(ilang.locale)) as len
				from certusers ia
				inner join users_meta ib
				on ia.userid = ib.user_meta_id
				inner join certifications ic
				on ia.certid = ic.certid
				inner join language_bundle ilang on ic.certid = ilang.object_id and ilang.object_type = 'CR'
				and ib.locale like ilang.locale + '%'
				group by ia.certid, ia.userid) maxlength 
			on maxlength.certid = a.certid and maxlength.userid = a.userid
			left join language_bundle automatched
			on automatched.object_id = a.certid and automatched.object_type = 'CR'
			and len(automatched.locale) = maxlength.len and b.locale like automatched.locale + '%'
			left join resource_string res_title
			on res_title.bundle_id = automatched.bundle_id and res_title.resource_key = c.title_key 
			left join resource_string res_desc
			on res_desc.bundle_id = automatched.bundle_id and res_desc.resource_key = c.description_key
GO

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

-- Changeset nschema/111.changeLog.xml::20150608-wc-1::wilson.choi
ALTER TABLE code_pool ADD lastSentDate datetime
GO

ALTER TABLE code_pool ADD reminderFrequency char(1) CONSTRAINT DF_code_pool_reminderFrequency DEFAULT 'D'
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'lastSentDate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that the reminder was last sent';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

CREATE TABLE question_pool_reminder_detail (id nvarchar(85) NOT NULL, poolid int NOT NULL, userid nvarchar(85) NOT NULL)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'question_pool_reminder_detail'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.question_pool_reminder_detail';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the question pending approval reminder recipient 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'question_pool_reminder_detail'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_pool_reminder_detail'; 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'question_pool_reminder_detail'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_pool_reminder_detail'; 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

ALTER TABLE question_pool_reminder_detail ADD CONSTRAINT PK_QP_REMINDER_DETAIL_PID PRIMARY KEY (id)
GO

ALTER TABLE question_pool_reminder_detail ADD CONSTRAINT FK_QP_REMINDER_DETAIL_PID FOREIGN KEY (poolid) REFERENCES code_pool (poolid)
GO

ALTER TABLE question_pool_reminder_detail ADD CONSTRAINT FK_QP_REMINDER_DETAIL_USERID FOREIGN KEY (userid) REFERENCES users_assignment (userid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150608-wc-1', 'wilson.choi', 'nschema/111.changeLog.xml', GETDATE(), 387, '8:dd6a1fbdc36803a26fa5b46f838dca2f', 'addColumn tableName=code_pool; createTable tableName=question_pool_reminder_detail; addPrimaryKey constraintName=PK_QP_REMINDER_DETAIL_PID, tableName=question_pool_reminder_detail; addForeignKeyConstraint baseTableName=question_pool_reminder_detai...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150608-kl-1::kalam
ALTER TABLE question_pool_auto_exemption ADD status nvarchar(1) CONSTRAINT DF_question_pool_auto_exemption_status DEFAULT 'D' NOT NULL
GO

ALTER TABLE question_pool_auto_exemption ADD min_question int CONSTRAINT DF_question_pool_auto_exemption_min_question DEFAULT 1 NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150608-kl-1', 'kalam', 'nschema/111.changeLog.xml', GETDATE(), 388, '8:9e03b6c9ba0318603aed479da47a3bd6', 'addColumn tableName=question_pool_auto_exemption', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150610-ge-1::gian.espino
ALTER TABLE individual_competency ADD enforce_approval char(1) CONSTRAINT DF_individual_competency_enforce_approval DEFAULT 'N' NOT NULL
GO

ALTER TABLE individual_competency ADD approval_type char(1)
GO

CREATE TABLE comp_approval_queue (competency_id nvarchar(15) NOT NULL, user_id nvarchar(85) NOT NULL, seq int NOT NULL, approver_id nvarchar(85), approval_date datetime, approver_remarks nvarchar (max), status char(1) NOT NULL, requester_id nvarchar(85) NOT NULL, request_date datetime NOT NULL, requester_remarks nvarchar (max), current_level_id nvarchar(15), target_level_id nvarchar(15), target_level_expire datetime, last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

ALTER TABLE comp_approval_queue ADD CONSTRAINT PK_COMP_AP_Q PRIMARY KEY (competency_id, user_id, seq)
GO

ALTER TABLE comp_approval_queue ADD CONSTRAINT FK_COMP_AP_Q_CID FOREIGN KEY (competency_id) REFERENCES individual_competency (id)
GO

ALTER TABLE comp_approval_queue ADD CONSTRAINT FK_COMP_AP_Q_UID FOREIGN KEY (user_id) REFERENCES users_assignment (userid)
GO

CREATE NONCLUSTERED INDEX NUK_COMP_AP_Q_OWNER ON comp_approval_queue(approver_id, status)
GO

CREATE NONCLUSTERED INDEX NUK_COMP_AP_Q_REQ ON comp_approval_queue(requester_id)
GO

CREATE VIEW comp_approval_queue_vw AS select c.competency_id, ic.name as competency_name, ic.enforce_approval, ic.approval_type, c.seq,
	    	       c.user_id, u.currentPID as user_pid, u.familyname as user_familyname, u.givenname as user_givenname, 
	    	       c.approver_id, a.currentPID as approver_pid, a.familyname as approver_familyname, a.givenname as approver_givenname,
	    	       c.approval_date, c.approver_remarks, c.status, c.requester_id, r.currentPID as requester_pid,
	    	       r.familyname as requester_familyname, r.givenname as requester_givenname, c.request_date, c.requester_remarks,
	    	       c.current_level_id, crl.title as current_level_title, c.target_level_id, trl.title as target_level_title,
	    	       c.target_level_expire, c.last_updated, c.updater_legal_name, c.update_meaning_text, c.last_updater_uid
	    	  from comp_approval_queue c
	    	          inner join individual_competency ic on c.competency_id = ic.id
	    	          left join valid_users_vw u on c.user_id = u.userid
	    	          left join valid_users_vw a on c.approver_id = a.userid
	    	          left join valid_users_vw r on c.requester_id = r.userid
	    	          left join competency_level tcl on c.target_level_id = tcl.id
	    	          left join tms_responsibility_level trl on tcl.res_id = trl.id
	    	          left join competency_level ccl on c.current_level_id = ccl.id
	    	          left join tms_responsibility_level crl on ccl.res_id = crl.id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150610-ge-1', 'gian.espino', 'nschema/111.changeLog.xml', GETDATE(), 389, '8:d932b94ccfc57de3a1a2b78d1cd4c604', 'addColumn tableName=individual_competency; createTable tableName=comp_approval_queue; addPrimaryKey constraintName=PK_COMP_AP_Q, tableName=comp_approval_queue; addForeignKeyConstraint baseTableName=comp_approval_queue, constraintName=FK_COMP_AP_Q_...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150610-ge-2::gian.espino
ALTER TABLE user_competency ADD expire_date datetime
GO

CREATE TABLE comp_level_validity (competency_level_id nvarchar(15) NOT NULL, orgid nvarchar(15) NOT NULL, valid_period int NOT NULL, valid_period_unit char(1) NOT NULL, expire_email_profile_id nvarchar(15), last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

ALTER TABLE comp_level_validity ADD CONSTRAINT PK_COMPLV_VALID PRIMARY KEY (competency_level_id, orgid)
GO

ALTER TABLE comp_level_validity ADD CONSTRAINT FK_COMPLV_VALID_LVID FOREIGN KEY (competency_level_id) REFERENCES competency_level (id)
GO

ALTER TABLE comp_level_validity ADD CONSTRAINT FK_COMPLV_VALID_ORG FOREIGN KEY (orgid) REFERENCES code_orgstructure (orgid)
GO

CREATE NONCLUSTERED INDEX NUK_COMPLV_VALID_LVID ON comp_level_validity(competency_level_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150610-ge-2', 'gian.espino', 'nschema/111.changeLog.xml', GETDATE(), 390, '8:7b91f1928fb5edc46b134da342900a84', 'addColumn tableName=user_competency; createTable tableName=comp_level_validity; addPrimaryKey constraintName=PK_COMPLV_VALID, tableName=comp_level_validity; addForeignKeyConstraint baseTableName=comp_level_validity, constraintName=FK_COMPLV_VALID_...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150616-jw-1::joe.wong
-- Insert ndadmin as target audience when new system language added because of news migration
insert into permissions (objectid, objecttype, identityid, permissioncode, identitytype, operator, count) 
			select id, 'ASLT', 'ndadmin', 1, 'U', 'OR', 1
			from active_system_language
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150616-jw-1', 'joe.wong', 'nschema/111.changeLog.xml', GETDATE(), 391, '8:0387778bf34c15a81a3ffe2af13f0ddc', 'sql', 'Insert ndadmin as target audience when new system language added because of news migration', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150612-rr-1::ray.ruff
ALTER TABLE transcript ADD restricted_start_date datetime
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150612-rr-1', 'ray.ruff', 'nschema/111.changeLog.xml', GETDATE(), 392, '8:95ee60c7b002b93e81ab875ee49d4b8c', 'addColumn tableName=transcript', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150612-rr-2::ray.ruff
ALTER TABLE certifications ADD prev_expire_date_based char(1) CONSTRAINT DF_certifications_prev_expire_date_based DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150612-rr-2', 'ray.ruff', 'nschema/111.changeLog.xml', GETDATE(), 393, '8:ddd9ec0ac82702fa5ee6aa637219d830', 'addColumn tableName=certifications', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150618-sg-1::shravan.gurrala
ALTER TABLE learningObject ADD display_learning_id nvarchar(85)
GO

UPDATE learningObject SET display_learning_id = learningid
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150618-sg-1', 'shravan.gurrala', 'nschema/111.changeLog.xml', GETDATE(), 394, '8:0a739927d42dd7dd1a8fdbf30fc61f66', 'addColumn tableName=learningObject; update tableName=learningObject', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150623-kl-1::kalam.chan
CREATE TABLE auto_exemption_requirement (policy_id nvarchar(85) NOT NULL, qp_auto_exempt_id nvarchar(85) NOT NULL, status nvarchar(1) CONSTRAINT DF_auto_exemption_requirement_status DEFAULT 'D' NOT NULL, min_question int CONSTRAINT DF_auto_exemption_requirement_min_question DEFAULT 1 NOT NULL)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'auto_exemption_requirement'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.auto_exemption_requirement';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the auto exempt 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

ALTER TABLE auto_exemption_requirement ADD CONSTRAINT PK_AUTO_EXEMPT_REQ PRIMARY KEY (policy_id, qp_auto_exempt_id)
GO

ALTER TABLE auto_exemption_requirement ADD CONSTRAINT FK_AUTO_EXEMPT_REQ_PID FOREIGN KEY (policy_id) REFERENCES auto_exempt_policy (id)
GO

ALTER TABLE auto_exemption_requirement ADD CONSTRAINT FK_AUTO_EXEMPT_REQ_QPID FOREIGN KEY (qp_auto_exempt_id) REFERENCES question_pool_auto_exemption (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150623-kl-1', 'kalam.chan', 'nschema/111.changeLog.xml', GETDATE(), 395, '8:e29e672fe1c2537bf3f82a126ab85edf', 'createTable tableName=auto_exemption_requirement; addPrimaryKey constraintName=PK_AUTO_EXEMPT_REQ, tableName=auto_exemption_requirement; addForeignKeyConstraint baseTableName=auto_exemption_requirement, constraintName=FK_AUTO_EXEMPT_REQ_PID, refer...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150623-kl-2::kalam.chan
ALTER TABLE question_pool_auto_exemption
			DROP CONSTRAINT DF_question_pool_auto_exemption_min_question,
			CONSTRAINT DF_question_pool_auto_exemption_status,
			COLUMN status,
			COLUMN min_question
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150623-kl-2', 'kalam.chan', 'nschema/111.changeLog.xml', GETDATE(), 396, '8:48a755a910f8e55610dd0c8fe4baba99', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150627-sn-1::sam.ng
-- [PERF 8928] New index for transcript table, used for recent activity widget
CREATE NONCLUSTERED INDEX NUK_TRAN_STARTDATE ON transcript(startdate)
GO

CREATE NONCLUSTERED INDEX NUK_TRAN_FINISHDATE ON transcript(finishdate)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150627-sn-1', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 397, '8:20d3944420f711616980963125fa58f2', 'createIndex indexName=NUK_TRAN_STARTDATE, tableName=transcript; createIndex indexName=NUK_TRAN_FINISHDATE, tableName=transcript', '[PERF 8928] New index for transcript table, used for recent activity widget', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150710-sn-1::sam.ng
-- [PERF 8893] New index for looking up user groups belonged to a user
CREATE NONCLUSTERED INDEX NUK_USER_GROUP_MEM_USERID ON user_group_membership(userid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150710-sn-1', 'sam.ng', 'nschema/111.changeLog.xml', GETDATE(), 398, '8:1eacf1623ba176fe52d8cfe37bebcd6a', 'createIndex indexName=NUK_USER_GROUP_MEM_USERID, tableName=user_group_membership', '[PERF 8893] New index for looking up user groups belonged to a user', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150624-sg-1::shravan.gurrala
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, lang.bundle_id,
			case when lang.ilangid is null then lang.locale else cast(lang.ilangid as char(5)) end language_tag, lo.display_learning_id
			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 ('20150624-sg-1', 'shravan.gurrala', 'nschema/111.changeLog.xml', GETDATE(), 399, '8:edcabb937a4e6e86f7c3e17bcbe0c8a8', 'dropView viewName=learningObject_vw; createView viewName=learningObject_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150627-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, lang.bundle_id,
			case when lang.ilangid is null then lang.locale else cast(lang.ilangid as char) end language_tag, lo.display_learning_id
			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 ('20150627-ca-1', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 400, '8:dfc3b8a6db950550b1e11ba241e8c496', 'dropView viewName=learningObject_vw; createView viewName=learningObject_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150627-ca-2::chris.au
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,
			t1.bundle_id,
		    t1.allowBundleSwitch,
			case when lang.ilangid is null then lang.locale else cast(lang.ilangid as char) end language_tag
		  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
			  inner join language_bundle lang on lang.bundle_id = t1.bundle_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,
			b.bundle_id,
		    b.allowBundleSwitch,
			case when lang.ilangid is null then lang.locale else cast(lang.ilangid as char) end language_tag
		  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
			  inner join language_bundle lang on b.bundle_id = lang.bundle_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 ('20150627-ca-2', 'chris.au', 'nschema/111.changeLog.xml', GETDATE(), 401, '8:2117f1b08f23c24b935aaeeb633bb825', 'dropView viewName=aggregated_transcript_vw; createView viewName=aggregated_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/111.changeLog.xml::20150701-sg-1::shravan.gurrala
ALTER TABLE learningObject ADD UNIQUE (display_learning_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150701-sg-1', 'shravan.gurrala', 'nschema/111.changeLog.xml', GETDATE(), 402, '8:2ad27fb61921f5b1d898328cc8bd362c', 'addUniqueConstraint tableName=learningObject', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::15Jun2015-sg-1::shravan.gurrala
UPDATE ekpversion SET dblevel = 1111, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('15Jun2015-sg-1', 'shravan.gurrala', 'nschema/1111.changeLog.xml', GETDATE(), 403, '8:86c2d922cf1d085d2d1b8dd9afe5de0b', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150617-tw-1::tomas.weigel
-- [DEV 8878] New approach to store resource string information
-- 			for language bundles
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR' AND name =
			'i_resource_string'
			)
			DROP TRIGGER i_resource_string
GO

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

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

ALTER TABLE resource_string ADD reference_id nvarchar(100)
GO

ALTER TABLE resource_string ADD field nvarchar(100)
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'reference_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Id of the referenced localizable 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'resource_string'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.resource_string'; DECLARE @ColumnName SYSNAME set @ColumnName = N'field'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Localizable data field type (enum value)';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 ALTER COLUMN resource_key nvarchar(85) NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150617-tw-1', 'tomas.weigel', 'nschema/1111.changeLog.xml', GETDATE(), 404, '8:96ee0e4559cda39031f4c46940855052', 'sql; sql; sql; addColumn tableName=resource_string; dropNotNullConstraint columnName=resource_key, tableName=resource_string', '[DEV 8878] New approach to store resource string information
			for language bundles', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150620-tw-2::tomas.weigel
-- [DEV 8878] Language bundle data migration for exams
-- Language bundle creation for each exam
-- Title column
-- Description column
-- Exam section title column
-- Exam section description column
-- Exam print title
insert into language_bundle
				(bundle_id,
				object_id,
				object_type,
				locale,
				is_primary)
			select
				CAST(ROW_NUMBER() OVER (ORDER BY testid) AS VARCHAR) + 'TS' as rownum,
				testid,
				'TS',
				'*ALL*',
				'Y'
			from mastertest
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_TITLE',
				null,
				b.bundle_id,
				title,
				'TS_TITLE',
				testid
			from
				mastertest t
			join
				language_bundle b on t.testid = b.object_id and b.object_type = 'TS'
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_ntext, 
				field, 
				reference_id)
			select
				b.bundle_id + '_DESCRIPTION',
				null,
				b.bundle_id,
				longDescription,
				'TS_DESCRIPTION',
				testid
			from
				mastertest t
			join
				language_bundle b on t.testid = b.object_id and b.object_type = 'TS'
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_SECTION_TITLE_' + CAST(s.sectionnum AS VARCHAR),
				null,
				b.bundle_id,
				title,
				'TS_SECTION_TITLE',
				s.id
			from
				mastersection s
			join
				language_bundle b on s.testid = b.object_id and b.object_type = 'TS'
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_SECTION_DESCRIPTION_' + CAST(s.sectionnum AS VARCHAR),
				null,
				b.bundle_id,
				description,
				'TS_SECTION_DESCRIPTION',
				s.id
			from
				mastersection s
			join
				language_bundle b on s.testid = b.object_id and b.object_type = 'TS'
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_PRINT_TITLE',
				null,
				b.bundle_id,
				printTitle,
				'TS_PRINT_TITLE',
				p.testid
			from
				mastertest_print p
			join
				language_bundle b on p.testid = b.object_id and b.object_type = 'TS'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150620-tw-2', 'tomas.weigel', 'nschema/1111.changeLog.xml', GETDATE(), 405, '8:f2f8395f2e8dee5906c0fd44aa544e12', 'sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql', '[DEV 8878] Language bundle data migration for exams
Language bundle creation for each exam
Title column
Description column
Exam section title column
Exam section description column
Exam print title', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150622-tw-3::tomas.weigel
-- [DEV 8878] Refactoring - Language bundle data migration for LO
-- Create single column primary key for objectives table
-- Primary key data migration
-- Resource string data update for LO
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR' AND name =
			'i_objectives_topics'
			)
			DROP TRIGGER i_objectives_topics
GO

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

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

ALTER TABLE objectives_topics ADD id nvarchar(100)
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'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique identifier';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
				objectives_topics
			set
				id = learningid + '_' + CAST(entrynum AS VARCHAR)
GO

UPDATE
				resource_string
			SET
				field = 'LO_TITLE',
				reference_id = (SELECT learningid FROM learningObject WHERE learningObject.title_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM learningObject WHERE learningObject.title_key = resource_string.resource_key)
GO

UPDATE
				resource_string
			SET
				field = 'LO_DESCRIPTION',
				reference_id = (SELECT learningid FROM learningObject WHERE learningObject.description_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM learningObject WHERE learningObject.description_key = resource_string.resource_key)
GO

UPDATE
				resource_string
			SET
				field = 'LO_SUPPORT_CONTACT',
				reference_id = (SELECT learningid FROM learningObject WHERE learningObject.support_contact_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM learningObject WHERE learningObject.support_contact_key = resource_string.resource_key)
GO

UPDATE
				resource_string
			SET
				field = 'LO_AUDIENCE_DESCRIPTION',
				reference_id = (SELECT id FROM audience WHERE description_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM audience WHERE description_key = resource_string.resource_key)
GO

UPDATE
				resource_string
			SET
				field = 'LO_OBJECTIVE_DESCRIPTION',
				reference_id = (SELECT id FROM objectives_topics WHERE description_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM objectives_topics WHERE description_key = resource_string.resource_key)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150622-tw-3', 'tomas.weigel', 'nschema/1111.changeLog.xml', GETDATE(), 406, '8:d82a16200a6df1aa07e20c8ff0310aa5', 'sql; sql; sql; addColumn tableName=objectives_topics; sql; sql; sql; sql; sql; sql; sql; sql', '[DEV 8878] Refactoring - Language bundle data migration for LO
Create single column primary key for objectives table
Primary key data migration
Resource string data update for LO', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150622-tw-4::tomas.weigel
-- [DEV 8878] Refactoring - Language bundle data migration for news
-- Resource string data update for news
UPDATE
				resource_string
			SET
				field = 'NW_TITLE',
				reference_id = (SELECT artid FROM article WHERE article.title_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM article WHERE article.title_key = resource_string.resource_key)
GO

UPDATE
				resource_string
			SET
				field = 'NW_TEASER',
				reference_id = (SELECT artid FROM article WHERE article.teaser_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM article WHERE article.teaser_key = resource_string.resource_key)
GO

UPDATE
				resource_string
			SET
				field = 'NW_TEXT',
				reference_id = (SELECT artid FROM article WHERE article.text1_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM article WHERE article.text1_key = resource_string.resource_key)
GO

UPDATE
				resource_string
			SET
				field = 'NW_PICTURE_URL',
				reference_id = (SELECT artid FROM article WHERE article.picture_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM article WHERE article.picture_key = resource_string.resource_key)
GO

UPDATE
				resource_string
			SET
				field = 'NW_DOWNLOAD_URL',
				reference_id = (SELECT artid FROM article WHERE article.downloadurl_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM article WHERE article.downloadurl_key = resource_string.resource_key)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150622-tw-4', 'tomas.weigel', 'nschema/1111.changeLog.xml', GETDATE(), 407, '8:bcfa99983fd0085700505400022bf387', 'sql; sql; sql; sql; sql', '[DEV 8878] Refactoring - Language bundle data migration for news
Resource string data update for news', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150622-tw-5::tomas.weigel
-- [DEV 8878] Refactoring - Language bundle data migration for certifications
-- Resource string data update for certifications
UPDATE
				resource_string
			SET
				field = 'CR_TITLE',
				reference_id = (SELECT CAST(certid AS VARCHAR(100)) FROM certifications WHERE certifications.title_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM certifications WHERE certifications.title_key = resource_string.resource_key)
GO

UPDATE
				resource_string
			SET
				field = 'CR_DESCRIPTION',
				reference_id = (SELECT CAST(certid AS VARCHAR(100)) FROM certifications WHERE certifications.description_key = resource_string.resource_key)
			WHERE EXISTS
				(SELECT * FROM certifications WHERE certifications.description_key = resource_string.resource_key)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150622-tw-5', 'tomas.weigel', 'nschema/1111.changeLog.xml', GETDATE(), 408, '8:e7e481e1bdffaca2bc114541867dd980', 'sql; sql; sql; sql', '[DEV 8878] Refactoring - Language bundle data migration for certifications
Resource string data update for certifications', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150623-ok-1::oikwan.cheung
-- Updated this table to store learning id of additional enrollment info
-- Updated this table to store learning id of additional enrollment info notification reminder
ALTER TABLE custom_enroll_attributes ADD learning_id nvarchar(85)
GO

ALTER TABLE custom_enroll_attr_reminder ADD learning_id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150623-ok-1', 'oikwan.cheung', 'nschema/1111.changeLog.xml', GETDATE(), 409, '8:1c86e261f6c79c35dc3d8c964f4cd9bb', 'addColumn tableName=custom_enroll_attributes; addColumn tableName=custom_enroll_attr_reminder', 'Updated this table to store learning id of additional enrollment info
Updated this table to store learning id of additional enrollment info notification reminder', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150624-kc-1::kc
ALTER TABLE auto_exemption_requirement ADD req_score int CONSTRAINT DF_auto_exemption_requirement_req_score DEFAULT 100 NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'auto_exemption_requirement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.auto_exemption_requirement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'req_score'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'required score for auto exemption';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 ('20150624-kc-1', 'kc', 'nschema/1111.changeLog.xml', GETDATE(), 410, '8:c060a68e9df40837e6b14e5e8d0c5d3b', 'addColumn tableName=auto_exemption_requirement', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150623-mw-1::martin.wang
-- [DEV 8491] New table for checklist template
CREATE TABLE checklist_template (template_id nvarchar(85) NOT NULL, template_name nvarchar(255) 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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'checklist_template'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.checklist_template';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores details of checklist 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.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'checklist_template'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.checklist_template'; DECLARE @ColumnName SYSNAME set @ColumnName = N'template_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of checklist template (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'checklist_template'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.checklist_template'; DECLARE @ColumnName SYSNAME set @ColumnName = N'template_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Name of checklist 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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'checklist_template'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.checklist_template'; DECLARE @ColumnName SYSNAME set @ColumnName = N'created_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that the record was created';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'checklist_template'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.checklist_template'; DECLARE @ColumnName SYSNAME set @ColumnName = N'creator_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'User ID of creator';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'checklist_template'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.checklist_template'; 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'checklist_template'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.checklist_template'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updater_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'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 checklist_template ADD CONSTRAINT PK_CHECKLIST_TEMPLATE PRIMARY KEY (template_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150623-mw-1', 'martin.wang', 'nschema/1111.changeLog.xml', GETDATE(), 411, '8:da345fa575be56c0373ebec7d087c313', 'createTable tableName=checklist_template; addPrimaryKey constraintName=PK_CHECKLIST_TEMPLATE, tableName=checklist_template', '[DEV 8491] New table for checklist template', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150623-mw-2::martin.wang
-- [DEV 8491] New table for checklist template items
CREATE TABLE checklist_template_item (item_id nvarchar(85) NOT NULL, description nvarchar(255) NOT NULL, template_id nvarchar(85) NOT NULL)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'checklist_template_item'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.checklist_template_item';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores details of checklist template items';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'checklist_template_item'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.checklist_template_item'; DECLARE @ColumnName SYSNAME set @ColumnName = N'item_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of checklist template item';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'checklist_template_item'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.checklist_template_item'; DECLARE @ColumnName SYSNAME set @ColumnName = N'description'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Name of checklist template item';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'checklist_template_item'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.checklist_template_item'; DECLARE @ColumnName SYSNAME set @ColumnName = N'template_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of checklist 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

ALTER TABLE checklist_template_item ADD CONSTRAINT PK_CHECKLIST_TEMPLATE_ITEM PRIMARY KEY (item_id)
GO

ALTER TABLE checklist_template_item ADD CONSTRAINT FK_CHECKLIST_TEMPLATE_ITEM FOREIGN KEY (template_id) REFERENCES checklist_template (template_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150623-mw-2', 'martin.wang', 'nschema/1111.changeLog.xml', GETDATE(), 412, '8:8822be8c694b3f09fd26a9d2c3ee8810', 'createTable tableName=checklist_template_item; addPrimaryKey constraintName=PK_CHECKLIST_TEMPLATE_ITEM, tableName=checklist_template_item; addForeignKeyConstraint baseTableName=checklist_template_item, constraintName=FK_CHECKLIST_TEMPLATE_ITEM, re...', '[DEV 8491] New table for checklist template items', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150626-tw-1::tomas.weigel
DROP VIEW localized_certusers_vw
GO

CREATE VIEW localized_certusers_vw AS select a.userid, a.certid, a.awardid, case when res_title.value_varchar is null then c.title else res_title.value_varchar end as title 
			, case when res_desc.value_varchar is null then c.description else res_desc.value_varchar end as description
			from certusers a
			inner join users_meta b
			on a.userid = b.user_meta_id
			inner join certifications c
			on a.certid = c.certid
			left join 
				(select ia.certid, ia.userid, max(len(ilang.locale)) as len
				from certusers ia
				inner join users_meta ib
				on ia.userid = ib.user_meta_id
				inner join certifications ic
				on ia.certid = ic.certid
				inner join language_bundle ilang on ic.certid = ilang.object_id and ilang.object_type = 'CR'
				and ib.locale like ilang.locale + '%'
				group by ia.certid, ia.userid) maxlength 
			on maxlength.certid = a.certid and maxlength.userid = a.userid
			left join language_bundle automatched
			on automatched.object_id = a.certid and automatched.object_type = 'CR'
			and len(automatched.locale) = maxlength.len and b.locale like automatched.locale + '%'
			left join resource_string res_title
			on res_title.bundle_id = automatched.bundle_id and res_title.field = 'CR_TITLE' 
			left join resource_string res_desc
			on res_desc.bundle_id = automatched.bundle_id and res_desc.field = 'CR_DESCRIPTION'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150626-tw-1', 'tomas.weigel', 'nschema/1111.changeLog.xml', GETDATE(), 413, '8:250bdfba7ba264a3cfcf37eaa14d6ad8', 'dropView viewName=localized_certusers_vw; createView viewName=localized_certusers_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150630-tw-1::tomas.weigel
-- TECH9027 - Remove title_key, description_key and support_contact_key columns from the view
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,lang.ilangid as language, lang.locale, lang.bundle_id,
			case when lang.ilangid is null then lang.locale else cast(lang.ilangid as char) end language_tag, lo.display_learning_id
			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 ('20150630-tw-1', 'tomas.weigel', 'nschema/1111.changeLog.xml', GETDATE(), 414, '8:35b03466fe7724955e3f3d3b84ded6c0', 'dropView viewName=learningObject_vw; createView viewName=learningObject_vw', 'TECH9027 - Remove title_key, description_key and support_contact_key columns from the view', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150702-tw-1::tomas.weigel
-- Fix derby build
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,lang.ilangid as language, lang.locale, lang.bundle_id,
			case when lang.ilangid is null then lang.locale else cast(lang.ilangid as char(85)) end language_tag, lo.display_learning_id
			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 ('20150702-tw-1', 'tomas.weigel', 'nschema/1111.changeLog.xml', GETDATE(), 415, '8:2aa57c89096da8c462522182d5abade2', 'dropView viewName=learningObject_vw; createView viewName=learningObject_vw', 'Fix derby build', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150627-ca-1::chris.au
-- Create single column primary key for question_type8_item
-- Primary key data migration
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR'
			AND name =
			'i_question_type8_item'
			)
			DROP TRIGGER i_question_type8_item
GO

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

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

ALTER TABLE question_type8_item ADD id nvarchar(100)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'question_type8_item'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_type8_item'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique identifier';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
			question_type8_item
			set
			id = questionid + '_' +
			CAST(qindex AS VARCHAR)
GO

ALTER TABLE question_type8_item DROP CONSTRAINT PK_QUESTION_TYPE8_ITEM
GO

ALTER TABLE question_type8_item ALTER COLUMN id nvarchar(100) NOT NULL
GO

ALTER TABLE question_type8_item ADD CONSTRAINT PK_QUESTION_TYPE8_ITEM PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150627-ca-1', 'chris.au', 'nschema/1111.changeLog.xml', GETDATE(), 416, '8:a51bd6770eb1f4cf403aeff13c00b7dc', 'sql; sql; sql; addColumn tableName=question_type8_item; sql; sql; sql; dropPrimaryKey constraintName=PK_QUESTION_TYPE8_ITEM, tableName=question_type8_item; addNotNullConstraint columnName=id, tableName=question_type8_item; addPrimaryKey constraint...', 'Create single column primary key for question_type8_item
Primary key data migration', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150702-ca-1::chris.au
-- [DEV 887] Language bundle data migration for questions
-- Language bundle creation for each question
-- Common columns
-- Single choice and Multipl choices
-- Question_type1 columns (Single choice)
-- Question_type2 columns (True and false)
-- Question_type3 columns (Matching)
-- Question_type4 columns (Essay)
-- Question_type5 columns (Fill in the blank)
-- Question_type6 columns (Rating)
-- Question_type7 columns (Multiple choice )
-- Question_type8 columns (Triple rating)
-- question_type_drag_and_drop columns (Drag and drop)
-- question_type_hotspot columns (Hotspot)
-- Question_type9 columns (Audio Capture)
insert into language_bundle
			(bundle_id,
			object_id,
			object_type,
			locale,
			is_primary)
			select
			CAST(ROW_NUMBER() OVER (ORDER BY
			questionid) AS VARCHAR) + 'QN' as rownum,
			questionid,
			'QN',
			'*ALL*',
			'Y'
			from question
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,referenceinfo,'QN_REF',questionid, null,b.bundle_id + 'QN_REF' 
			 from question q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,imageobject,'QN_MEDIA_IMAGEOBJECT',questionid, null,b.bundle_id + 'QN_MEDIA_IMAGEOBJECT' 
			 from qattributes q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,videoobject,'QN_MEDIA_VIDEOOBJECT',questionid, null,b.bundle_id + 'QN_MEDIA_VIDEOOBJECT' 
			 from qattributes q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,audioobject,'QN_MEDIA_AUDIOOBJECT',questionid, null,b.bundle_id + 'QN_MEDIA_AUDIOOBJECT' 
			 from qattributes q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,otherobject,'QN_MEDIA_OTHEROBJECT',questionid, null,b.bundle_id + 'QN_MEDIA_OTHEROBJECT' 
			 from qattributes q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice,'QN_CHOICE',choiceid, null,b.bundle_id + 'QN_CHOICE'+ cast(q.displayorder as nvarchar) 
			 from question_choices q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_SC',questionid, null,b.bundle_id + 'QN_HINT_SC' 
			 from question_type1 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_SC',questionid, null,b.bundle_id + 'QN_POSTNOTE_SC' 
			 from question_type1 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_SC',questionid, null,b.bundle_id + 'QN_PRENOTE_SC' 
			 from question_type1 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext2,'QN_TEXT_SC',questionid, null,b.bundle_id + 'QN_TEXT_SC' 
			 from question_type1 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_TF',questionid, null,b.bundle_id + 'QN_HINT_TF' 
			 from question_type2 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_TF',questionid, null,b.bundle_id + 'QN_POSTNOTE_TF' 
			 from question_type2 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_TF',questionid, null,b.bundle_id + 'QN_PRENOTE_TF' 
			 from question_type2 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext2,'QN_TEXT_TF',questionid, null,b.bundle_id + 'QN_TEXT_TF' 
			 from question_type2 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_MATCH',questionid, null,b.bundle_id + 'QN_HINT_MATCH' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_MATCH',questionid, null,b.bundle_id + 'QN_POSTNOTE_MATCH' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_MATCH',questionid, null,b.bundle_id + 'QN_PRENOTE_MATCH' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext2,'QN_TEXT_MATCH',questionid, null,b.bundle_id + 'QN_TEXT_MATCH' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice1,'QN_CHOICE1',questionid, null,b.bundle_id + 'QN_CHOICE1' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match1,'QN_MATCH1',questionid, null,b.bundle_id + 'QN_MATCH1' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice2,'QN_CHOICE2',questionid, null,b.bundle_id + 'QN_CHOICE2' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match2,'QN_MATCH2',questionid, null,b.bundle_id + 'QN_MATCH2' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice3,'QN_CHOICE3',questionid, null,b.bundle_id + 'QN_CHOICE3' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match3,'QN_MATCH3',questionid, null,b.bundle_id + 'QN_MATCH3' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice4,'QN_CHOICE4',questionid, null,b.bundle_id + 'QN_CHOICE4' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match4,'QN_MATCH4',questionid, null,b.bundle_id + 'QN_MATCH4' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice5,'QN_CHOICE5',questionid, null,b.bundle_id + 'QN_CHOICE5' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match5,'QN_MATCH5',questionid, null,b.bundle_id + 'QN_MATCH5' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice6,'QN_CHOICE6',questionid, null,b.bundle_id + 'QN_CHOICE6' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match6,'QN_MATCH6',questionid, null,b.bundle_id + 'QN_MATCH6' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice7,'QN_CHOICE7',questionid, null,b.bundle_id + 'QN_CHOICE7' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match7,'QN_MATCH7',questionid, null,b.bundle_id + 'QN_MATCH7' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice8,'QN_CHOICE8',questionid, null,b.bundle_id + 'QN_CHOICE8' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match8,'QN_MATCH8',questionid, null,b.bundle_id + 'QN_MATCH8' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice9,'QN_CHOICE9',questionid, null,b.bundle_id + 'QN_CHOICE9' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match9,'QN_MATCH9',questionid, null,b.bundle_id + 'QN_MATCH9' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,choice10,'QN_CHOICE10',questionid, null,b.bundle_id + 'QN_CHOICE10' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,match10,'QN_MATCH10',questionid, null,b.bundle_id + 'QN_MATCH10' 
			 from question_type3 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_ESSAY',questionid, null,b.bundle_id + 'QN_HINT_ESSAY' 
			 from question_type4 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_ESSAY',questionid, null,b.bundle_id + 'QN_POSTNOTE_ESSAY' 
			 from question_type4 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_ESSAY',questionid, null,b.bundle_id + 'QN_PRENOTE_ESSAY' 
			 from question_type4 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext2,'QN_TEXT_ESSAY',questionid, null,b.bundle_id + 'QN_TEXT_ESSAY' 
			 from question_type4 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_FITB',questionid, null,b.bundle_id + 'QN_HINT_FITB' 
			 from question_type5 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_FITB',questionid, null,b.bundle_id + 'QN_POSTNOTE_FITB' 
			 from question_type5 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_FITB',questionid, null,b.bundle_id + 'QN_PRENOTE_FITB' 
			 from question_type5 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext2,'QN_TEXT_FITB',questionid, null,b.bundle_id + 'QN_TEXT_FITB' 
			 from question_type5 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,answer,'QN_ANSWER',questionid, null,b.bundle_id + 'QN_ANSWER' 
			 from question_type5 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_RT',questionid, null,b.bundle_id + 'QN_HINT_RT' 
			 from question_type6 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_RT',questionid, null,b.bundle_id + 'QN_POSTNOTE_RT' 
			 from question_type6 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_RT',questionid, null,b.bundle_id + 'QN_PRENOTE_RT' 
			 from question_type6 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext2,'QN_TEXT_RT',questionid, null,b.bundle_id + 'QN_TEXT_RT' 
			 from question_type6 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,lowlabel,'QN_LOW_LABEL_RT',questionid, null,b.bundle_id + 'QN_LOW_LABEL_RT' 
			 from question_type6 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,highlabel,'QN_HIGH_LABEL_RT',questionid, null,b.bundle_id + 'QN_HIGH_LABEL_RT' 
			 from question_type6 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_MC',questionid, null,b.bundle_id + 'QN_HINT_MC' 
			 from question_type7 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_MC',questionid, null,b.bundle_id + 'QN_POSTNOTE_MC' 
			 from question_type7 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_MC',questionid, null,b.bundle_id + 'QN_PRENOTE_MC' 
			 from question_type7 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext1,'QN_TEXT_MC',questionid, null,b.bundle_id + 'QN_TEXT_MC' 
			 from question_type7 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_TR',questionid, null,b.bundle_id + 'QN_HINT_TR' 
			 from question_type8 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_TR',questionid, null,b.bundle_id + 'QN_POSTNOTE_TR' 
			 from question_type8 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_TR',questionid, null,b.bundle_id + 'QN_PRENOTE_TR' 
			 from question_type8 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext,'QN_TEXT_TR',questionid, null,b.bundle_id + 'QN_TEXT_TR' 
			 from question_type8 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,columnheading1,'QN_COLUMN_HEADING1',questionid, null,b.bundle_id + 'QN_COLUMN_HEADING1' 
			 from question_type8 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,columnheading2,'QN_COLUMN_HEADING2',questionid, null,b.bundle_id + 'QN_COLUMN_HEADING2' 
			 from question_type8 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,columnheading3,'QN_COLUMN_HEADING3',questionid, null,b.bundle_id + 'QN_COLUMN_HEADING3' 
			 from question_type8 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,highlabel,'QN_HIGH_LABEL_TR',questionid, null,b.bundle_id + 'QN_HIGH_LABEL_TR' 
			 from question_type8 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,lowlabel,'QN_LOW_LABEL_TR',questionid, null,b.bundle_id + 'QN_LOW_LABEL_TR' 
			 from question_type8 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext,'QN_ITEM',id, null,b.bundle_id + 'QN_ITEM' + cast(q.qindex as nvarchar)
			 from question_type8_item q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_DD',questionid, null,b.bundle_id + 'QN_HINT_DD' 
			 from question_type_drag_and_drop q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_DD',questionid, null,b.bundle_id + 'QN_POSTNOTE_DD' 
			 from question_type_drag_and_drop q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_DD',questionid, null,b.bundle_id + 'QN_PRENOTE_DD' 
			 from question_type_drag_and_drop q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext,'QN_TEXT_DD',questionid, null,b.bundle_id + 'QN_TEXT_DD' 
			 from question_type_drag_and_drop q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_HS',questionid, null,b.bundle_id + 'QN_HINT_HS' 
			 from question_type_hotspot q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_HS',questionid, null,b.bundle_id + 'QN_POSTNOTE_HS' 
			 from question_type_hotspot q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_HS',questionid, null,b.bundle_id + 'QN_PRENOTE_HS' 
			 from question_type_hotspot q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext,'QN_TEXT_HS',questionid, null,b.bundle_id + 'QN_TEXT_HS' 
			 from question_type_hotspot q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,hint,'QN_HINT_AC',questionid, null,b.bundle_id + 'QN_HINT_AC' 
			 from question_type9 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,postnote,'QN_POSTNOTE_AC',questionid, null,b.bundle_id + 'QN_POSTNOTE_AC' 
			 from question_type9 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_varchar,field,reference_id,resource_key,id)
			 select b.bundle_id,prenote,'QN_PRENOTE_AC',questionid, null,b.bundle_id + 'QN_PRENOTE_AC' 
			 from question_type9 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

insert into resource_string
			(bundle_id,value_ntext,field,reference_id,resource_key,id)
			 select b.bundle_id,qtext2,'QN_TEXT_AC',questionid, null,b.bundle_id + 'QN_TEXT_AC' 
			 from question_type9 q join language_bundle b on q.questionid = b.object_id and b.object_type = 'QN'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150702-ca-1', 'chris.au', 'nschema/1111.changeLog.xml', GETDATE(), 417, '8:9056f22cb0f9a12a41ec87c51d49ed38', 'sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sq...', '[DEV 887] Language bundle data migration for questions
Language bundle creation for each question
Common columns
Single choice and Multipl choices
Question_type1 columns (Single choice)
Question_type2 columns (True and false)
Question_type3 column...', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150707-mw-1::martin.wang
-- [DEV 8523] New table for question attribute
CREATE TABLE question_attribute (question_id nvarchar(85) NOT NULL, attribute_id nvarchar(15) NOT NULL, attribute_value_data nvarchar(2000) NOT NULL, last_updated datetime NOT NULL, last_updater_uid nvarchar(85) NOT NULL)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'question_attribute'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.question_attribute';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores details of question attributes';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'question_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attribute'; DECLARE @ColumnName SYSNAME set @ColumnName = N'question_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of question (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'question_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attribute'; DECLARE @ColumnName SYSNAME set @ColumnName = N'attribute_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of question attribute';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'question_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attribute'; DECLARE @ColumnName SYSNAME set @ColumnName = N'attribute_value_data'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Value of question attribute';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'question_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attribute'; 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'question_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attribute'; 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 question_attribute ADD CONSTRAINT PK_QUESTION_ATTR PRIMARY KEY (attribute_id, question_id)
GO

ALTER TABLE question_attribute ADD CONSTRAINT FK_ATTRIBUTE_ID_QUESTION_ATTR FOREIGN KEY (attribute_id) REFERENCES dynamic_attribute (attribute_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150707-mw-1', 'martin.wang', 'nschema/1111.changeLog.xml', GETDATE(), 418, '8:248836aee29998abc8d62d5bf1d3eed7', 'createTable tableName=question_attribute; addPrimaryKey constraintName=PK_QUESTION_ATTR, tableName=question_attribute; addForeignKeyConstraint baseTableName=question_attribute, constraintName=FK_ATTRIBUTE_ID_QUESTION_ATTR, referencedTableName=dyna...', '[DEV 8523] New table for question attribute', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150708-ge-1::gian.espino
ALTER TABLE comp_level_validity ADD sticky char(1) CONSTRAINT DF_comp_level_validity_sticky DEFAULT 'N' NOT NULL
GO

ALTER TABLE user_competency ADD nreminders int CONSTRAINT DF_user_competency_nreminders DEFAULT 0 NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150708-ge-1', 'gian.espino', 'nschema/1111.changeLog.xml', GETDATE(), 419, '8:bd1e98248e62a4f2cbf1135dea6d61e2', 'addColumn tableName=comp_level_validity; addColumn tableName=user_competency', '', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150710-ca-1::chris.au
-- [DEV 8901] EML6 - Fixed Question Draw on Exam Start
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR'
			AND name =
			'i_testinstance'
			)
			DROP TRIGGER i_testinstance
GO

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

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

ALTER TABLE testinstance ADD bundle_id nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'testinstance'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.testinstance'; 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

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

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

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

ALTER TABLE testquestion ADD bundle_id nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'testquestion'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.testquestion'; 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150710-ca-1', 'chris.au', 'nschema/1111.changeLog.xml', GETDATE(), 420, '8:f544e7baaeab8538e7954a674e4393b2', 'sql; sql; sql; addColumn tableName=testinstance; sql; sql; sql; addColumn tableName=testquestion', '[DEV 8901] EML6 - Fixed Question Draw on Exam Start', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150718-ca-1::chris.au
-- [DEV 8901] EML6 - Fixed Question Draw on Exam Start
UPDATE testinstance
			SET testinstance.bundle_id=lb.bundle_id
			FROM language_bundle lb
			WHERE lb.object_id=testinstance.testid
			AND lb.object_type='TS'
			AND lb.is_primary='Y'
			AND testinstance.bundle_id is null
GO

UPDATE testquestion
			SET testquestion.bundle_id=lb.bundle_id
			FROM language_bundle lb
			WHERE lb.object_id=testquestion.questionid
			AND lb.object_type='QN'
			AND lb.is_primary='Y'
			AND testquestion.bundle_id is null
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150718-ca-1', 'chris.au', 'nschema/1111.changeLog.xml', GETDATE(), 421, '8:fcb8434a2ea1648c7dd36722542f6902', 'sql; sql; sql; sql; sql; sql', '[DEV 8901] EML6 - Fixed Question Draw on Exam Start', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150723-sn-1::sam.ng
-- [TTP 9189] address the issue when testinstance.testid refers to an non-existing exam
INSERT INTO language_bundle(bundle_id, object_id, object_type, locale, is_primary) 
        	VALUES ('*MISSING-EXAM-BUNDLE*', '*MISSING-EXAM*', 'TS', '*ALL*', 'Y')
GO

UPDATE testinstance
			SET testinstance.bundle_id='*MISSING-EXAM-BUNDLE*'
			WHERE testinstance.bundle_id is null
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150723-sn-1', 'sam.ng', 'nschema/1111.changeLog.xml', GETDATE(), 422, '8:2eee78293323d66c069070c55dba7ccb', 'sql; sql', '[TTP 9189] address the issue when testinstance.testid refers to an non-existing exam', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150718-ca-2::chris.au
-- [DEV 8901] EML6 - add not null constraint to bundle_id
ALTER TABLE testinstance ALTER COLUMN bundle_id nvarchar(85) NOT NULL
GO

ALTER TABLE testquestion 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 ('20150718-ca-2', 'chris.au', 'nschema/1111.changeLog.xml', GETDATE(), 423, '8:aea66b396564c5b1966b39719230e44f', 'addNotNullConstraint columnName=bundle_id, tableName=testinstance; addNotNullConstraint columnName=bundle_id, tableName=testquestion', '[DEV 8901] EML6 - add not null constraint to bundle_id', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150720-kc-1::kelvin.chan
-- [DEV8528] New table for exam required attribute for questions
CREATE TABLE mastersection_attr (id nvarchar(85) NOT NULL, section_id nvarchar(15) NOT NULL, attribute_id nvarchar(15) NOT NULL, attribute_value_data nvarchar(2000), 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'mastersection_attr'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.mastersection_attr';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores details of required question attributes for exams';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'mastersection_attr'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.mastersection_attr'; 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'mastersection_attr'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.mastersection_attr'; DECLARE @ColumnName SYSNAME set @ColumnName = N'section_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of exam section';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'mastersection_attr'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.mastersection_attr'; DECLARE @ColumnName SYSNAME set @ColumnName = N'attribute_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of question attribute';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'mastersection_attr'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.mastersection_attr'; DECLARE @ColumnName SYSNAME set @ColumnName = N'attribute_value_data'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Value of question attribute';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'mastersection_attr'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.mastersection_attr'; 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'mastersection_attr'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.mastersection_attr'; 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'mastersection_attr'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.mastersection_attr'; 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'mastersection_attr'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.mastersection_attr'; 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 mastersection_attr ADD CONSTRAINT PK_EXAM_ATTR PRIMARY KEY (id)
GO

ALTER TABLE mastersection_attr ADD CONSTRAINT UK_EXAM_ATTR UNIQUE (section_id, attribute_id)
GO

ALTER TABLE mastersection_attr ADD CONSTRAINT FK_ATTRIBUTE_ID_EXAM_ATTR FOREIGN KEY (attribute_id) REFERENCES dynamic_attribute (attribute_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150720-kc-1', 'kelvin.chan', 'nschema/1111.changeLog.xml', GETDATE(), 424, '8:b0fc9eb4f8279be962222024d57b84d8', 'createTable tableName=mastersection_attr; addPrimaryKey constraintName=PK_EXAM_ATTR, tableName=mastersection_attr; addUniqueConstraint constraintName=UK_EXAM_ATTR, tableName=mastersection_attr; addForeignKeyConstraint baseTableName=mastersection_a...', '[DEV8528] New table for exam required attribute for questions', 'EXECUTED', NULL, NULL, '4.8.0', '1515184631')
GO

-- Changeset nschema/1111.changeLog.xml::20150721-kc-1::kelvin.chan
