CREATE TABLE enrollment_policy_ext_attr (id nvarchar(85) NOT NULL, policy_id nvarchar(15) NOT NULL, attribute_id nvarchar(15) NOT NULL, is_mandatory char(1) NOT NULL)
GO

ALTER TABLE enrollment_policy_ext_attr ADD CONSTRAINT PK_ENROLL_POLICY_EXT_ATTR PRIMARY KEY (id)
GO

ALTER TABLE enrollment_policy_ext_attr ADD CONSTRAINT FK_ENROLLMENT_POLICY_ID FOREIGN KEY (policy_id) REFERENCES enrollment_policy (policy_id)
GO

ALTER TABLE enrollment_policy_ext_attr ADD CONSTRAINT FK_ENROLL_POLICY_EXT_ATTR_ID 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 ('20160118-wc-1', 'wilson.choi', 'nschema/120.changeLog.xml', GETDATE(), 551, '8:da2585606f18a7b1ff6f62dff6506e44', 'createTable tableName=enrollment_policy_ext_attr; addPrimaryKey constraintName=PK_ENROLL_POLICY_EXT_ATTR, tableName=enrollment_policy_ext_attr; addForeignKeyConstraint baseTableName=enrollment_policy_ext_attr, constraintName=FK_ENROLLMENT_POLICY_I...', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160126-sn-1::sam.ng
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('course_competency_exp') AND NAME ='NUK_COURSE_COMP_USER')
    			DROP INDEX NUK_COURSE_COMP_USER ON course_competency_exp
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160126-sn-1', 'sam.ng', 'nschema/120.changeLog.xml', GETDATE(), 552, '8:5c3971721e27add8fffec4435bb262cb', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160126-sn-4::sam.ng
CREATE NONCLUSTERED INDEX NUK_COURSE_COMP_USER ON course_competency_exp(course_id, competency_id, user_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160126-sn-4', 'sam.ng', 'nschema/120.changeLog.xml', GETDATE(), 553, '8:fa0ae39c810ce444d788f536e6081465', 'createIndex indexName=NUK_COURSE_COMP_USER, tableName=course_competency_exp', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160122-js-1::janice.sung
-- New cert description column for storing snapshot information of cert awarded to user
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR' AND name =
			'i_certusers'
			)
			DROP TRIGGER i_certusers
GO

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

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

ALTER TABLE certusers ADD cert_description nvarchar(4000)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'cert_description'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Certification Description (language bundle specific)';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 ('20160122-js-1', 'janice.sung', 'nschema/120.changeLog.xml', GETDATE(), 554, '8:8c50aa55eefaa2c32622ea57636347a8', 'sql; sql; sql; addColumn tableName=certusers', 'New cert description column for storing snapshot information of cert awarded to user', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160122-js-2::janice.sung
-- Data Migration for the new cert_description field for certusers table
UPDATE certusers SET cert_description = (SELECT c.description FROM certifications c WHERE c.certid = certusers.certid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160122-js-2', 'janice.sung', 'nschema/120.changeLog.xml', GETDATE(), 555, '8:c4583d16a88ed18df45c99cf05a26f45', 'sql', 'Data Migration for the new cert_description field for certusers table', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160125-vy-1::victor.yan
-- [ZD 28216] Extend the column size of fax and b_mobile in users_meta table
ALTER TABLE users_meta ALTER COLUMN fax NVARCHAR(85)
GO

ALTER TABLE users_meta ALTER COLUMN b_mobile NVARCHAR(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160125-vy-1', 'victor.yan', 'nschema/120.changeLog.xml', GETDATE(), 556, '8:6bc8ffe0e5a1779e7267e41478651fb6', 'sql; sql; sql', '[ZD 28216] Extend the column size of fax and b_mobile in users_meta table', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160128-yc-1::yanfang.cui
-- [PD-3910] Program Assigned Module Deadlines Relative to Program Enrollment
ALTER TABLE currcourses ADD complete_days_flag char(1) CONSTRAINT DF_currcourses_complete_days_flag DEFAULT 'A'
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'currcourses'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.currcourses'; DECLARE @ColumnName SYSNAME set @ColumnName = N'complete_days_flag'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'is from Assigned Module Enrollment';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 ('20160128-yc-1', 'yanfang.cui', 'nschema/120.changeLog.xml', GETDATE(), 557, '8:0ca37ab79dbc379c8e29f606e4ae56e0', 'addColumn tableName=currcourses', '[PD-3910] Program Assigned Module Deadlines Relative to Program Enrollment', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160128-jw-1::joe.wong
-- New options under enforce ordering option in program modules properties.
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR'
			AND name =
			'i_sessiondetail'
			)
			DROP TRIGGER i_sessiondetail
GO

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

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

ALTER TABLE sessiondetail ADD allow_advance_on_fail char(1) CONSTRAINT DF_sessiondetail_allow_advance_on_fail DEFAULT 'N' NOT NULL
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'allow_advance_on_fail'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Allow advance to next program module order on failing';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 hide_blocked_modules char(1) CONSTRAINT DF_sessiondetail_hide_blocked_modules DEFAULT 'N' NOT NULL
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'hide_blocked_modules'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Hide modules blocked by preceding modules in Learners Available Modules';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 ('20160128-jw-1', 'joe.wong', 'nschema/120.changeLog.xml', GETDATE(), 558, '8:76ca7eca73b8646038960b1644c196ed', 'sql; sql; sql; addColumn tableName=sessiondetail; addColumn tableName=sessiondetail', 'New options under enforce ordering option in program modules properties.', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160129-ca-1::chris.au
DROP VIEW users
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160129-ca-1', 'chris.au', 'nschema/120.changeLog.xml', GETDATE(), 559, '8:57b4925dbd5204f80757f0c661be58b4', 'dropView viewName=users; createView viewName=users', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160129-ca-2::chris.au
DROP VIEW valid_users_vw
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160129-ca-2', 'chris.au', 'nschema/120.changeLog.xml', GETDATE(), 560, '8:4f0ce573da46ce0954399c3a8b52af2c', 'dropView viewName=valid_users_vw; createView viewName=valid_users_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160125-kk-1::kumar.khiani
-- [PD-3747] New Owner Id column for storing Job Profiles Owner
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR' AND name =
			'i_job_profile'
			)
			DROP TRIGGER i_job_profile
GO

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

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

ALTER TABLE job_profile ADD owner_id nvarchar(85)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160125-kk-1', 'kumar.khiani', 'nschema/120.changeLog.xml', GETDATE(), 561, '8:85489f6cab21899858d03fbc1d68a12b', 'sql; sql; sql; addColumn tableName=job_profile', '[PD-3747] New Owner Id column for storing Job Profiles Owner', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160212-jw-1::joe.wong
-- New options of using legacy mode in page rendering.
ALTER TABLE screenLayout ADD legacymode char(1) CONSTRAINT DF_screenLayout_legacymode DEFAULT 'Y' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'screenLayout'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.screenLayout'; DECLARE @ColumnName SYSNAME set @ColumnName = N'legacymode'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Use legacy mode in page rendering';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 ('20160212-jw-1', 'joe.wong', 'nschema/120.changeLog.xml', GETDATE(), 562, '8:8f12335c55b543561fbe5311b2be7e24', 'addColumn tableName=screenLayout', 'New options of using legacy mode in page rendering.', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160212-js-1::janice.sung
-- Additional Certificate Fields
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR' AND name =
			'i_certusers'
			)
			DROP TRIGGER i_certusers
GO

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

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

ALTER TABLE certusers ADD tran_score nvarchar(20)
GO

ALTER TABLE certusers ADD tran_credits float(53)
GO

ALTER TABLE certusers ADD user_department_name nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'tran_score'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Transcript Final Score';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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'tran_credits'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Transcript Credits';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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'user_department_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s Department Name';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 ('20160212-js-1', 'janice.sung', 'nschema/120.changeLog.xml', GETDATE(), 563, '8:5ffdec07b1737d4e215749b971aa7a42', 'sql; sql; sql; addColumn tableName=certusers', 'Additional Certificate Fields', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160212-js-2::janice.sung
-- Data Migration for the additional certificate fields
UPDATE certusers SET 
				tran_score = (SELECT t.final_score FROM transcript t WHERE t.transcriptid = certusers.awardedby),
				tran_credits = (SELECT t.ntraininghours FROM transcript t WHERE t.transcriptid = certusers.awardedby),
				user_department_name = (SELECT u.departmentname FROM users u WHERE u.userid = certusers.userid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160212-js-2', 'janice.sung', 'nschema/120.changeLog.xml', GETDATE(), 564, '8:cd1c8390ea3c0b7aa7e21e1e8790b426', 'sql', 'Data Migration for the additional certificate fields', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160215-vy-1::victor.yan
-- [ZD 28216] Extend the column size of postal code in users_meta table
ALTER TABLE users_meta ALTER COLUMN b_postalcode NVARCHAR(50)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160215-vy-1', 'victor.yan', 'nschema/120.changeLog.xml', GETDATE(), 565, '8:746ff1dfadc6444d5abeeef9de4604e5', 'sql; sql; sql', '[ZD 28216] Extend the column size of postal code in users_meta table', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160217-fr-1::francis.reforma
-- Allow Policy Approval for Non-auto enroll Program Modules
ALTER TABLE sessiondetail ADD policyapv_progmod char(1)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160217-fr-1', 'francis.reforma', 'nschema/120.changeLog.xml', GETDATE(), 566, '8:674fb48167a8f6b766a98a415261a3ab', 'addColumn tableName=sessiondetail', 'Allow Policy Approval for Non-auto enroll Program Modules', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160218-rl-1::robert.lowe
ALTER TABLE users_meta ALTER COLUMN email nvarchar(150)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160218-rl-1', 'robert.lowe', 'nschema/120.changeLog.xml', GETDATE(), 567, '8:11bc9ed51c3d6a2055fd8caace006ddc', 'modifyDataType columnName=email, tableName=users_meta', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160229-sn-1::sam.ng
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('users_meta') AND NAME ='NUK_USER_META_NAME')
    			DROP INDEX NUK_USER_META_NAME ON users_meta
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160229-sn-1', 'sam.ng', 'nschema/120.changeLog.xml', GETDATE(), 568, '8:8c52a1779b0a4ee7bf0f7c94dea82cad', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160229-sn-4::sam.ng
CREATE NONCLUSTERED INDEX NUK_USER_META_NAME ON users_meta(familyname, givenname, currentPID)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160229-sn-4', 'sam.ng', 'nschema/120.changeLog.xml', GETDATE(), 569, '8:e73e021a9622aafe236a4edf2afccfb9', 'createIndex indexName=NUK_USER_META_NAME, tableName=users_meta', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160218-hf-1::hubert.fajarit
-- Enforce approval for program or course withdrawals
CREATE TABLE withdrawal_enrollment_policy (policy_id nvarchar(15) NOT NULL, enable_withdrawal char(1), allow_request_cancel char(1), approverid nvarchar(85), regcontrol smallint, email_profile_id nvarchar(15), 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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal rule of enrollment 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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'policy_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Parent enrollment 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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'enable_withdrawal'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Flag in enabling withdrawal 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.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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'allow_request_cancel'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Flag to allow learner to cancel withdrawal';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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'approverid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Specific approver''s userID';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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'regcontrol'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Approver 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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'email_profile_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'email template 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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal policy last update date';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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'updater_legal_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal policy updater legal name';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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'update_meaning_text'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal policy 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'withdrawal_enrollment_policy'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_enrollment_policy'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updater_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal policy updater UID';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 withdrawal_enrollment_policy ADD CONSTRAINT PK_WITHDRAW_E_P PRIMARY KEY (policy_id)
GO

ALTER TABLE withdrawal_enrollment_policy ADD CONSTRAINT FK_EMAIL_PROFILE_ID FOREIGN KEY (email_profile_id) REFERENCES email_profile (email_profile_id)
GO

CREATE TABLE withdrawal_approval_queue (transcriptid nvarchar(15) NOT NULL, policy_id nvarchar(15) NOT NULL, request_date datetime NOT NULL, reason nvarchar(1000) NOT NULL, status char(1) NOT NULL, approver_id nvarchar(85) NOT NULL, approver_remarks nvarchar(255), approval_date datetime, 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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal Approval Queue';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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'transcriptid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of the transcript for withdrawal';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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'policy_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of the enrollment 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.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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'request_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal request date';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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'reason'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'User remarks';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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'status'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Status of the withdrawal request';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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'approver_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Approver user 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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'approver_remarks'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Approver remarks';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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'approval_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date withdrawal is confirmed';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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal request last update date';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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'updater_legal_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal request updater legal name';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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'update_meaning_text'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal request 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'withdrawal_approval_queue'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.withdrawal_approval_queue'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updater_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Withdrawal request updater UID';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 withdrawal_approval_queue ADD CONSTRAINT PK_WITHDRAW_AP_Q PRIMARY KEY (transcriptid)
GO

CREATE NONCLUSTERED INDEX NUK_WITHDRAW_AP_Q_REQDATE ON withdrawal_approval_queue(request_date)
GO

CREATE NONCLUSTERED INDEX NUK_WITHDRAW_AP_Q_STATUS ON withdrawal_approval_queue(status)
GO

CREATE NONCLUSTERED INDEX NUK_WITHDRAW_AP_Q_APPROVER ON withdrawal_approval_queue(approver_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160218-hf-1', 'hubert.fajarit', 'nschema/120.changeLog.xml', GETDATE(), 570, '8:23f1a050024fbd5e320d7cff8f4407bd', 'createTable tableName=withdrawal_enrollment_policy; addPrimaryKey constraintName=PK_WITHDRAW_E_P, tableName=withdrawal_enrollment_policy; addForeignKeyConstraint baseTableName=withdrawal_enrollment_policy, constraintName=FK_EMAIL_PROFILE_ID, refer...', 'Enforce approval for program or course withdrawals', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160219-kc-1::kelvin.chan
-- Adding root transcript id for bulk delete
ALTER TABLE transcript ADD root_transcriptid nvarchar(15)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'transcript'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.transcript'; DECLARE @ColumnName SYSNAME set @ColumnName = N'root_transcriptid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Root Transcript 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 ('20160219-kc-1', 'kelvin.chan', 'nschema/120.changeLog.xml', GETDATE(), 571, '8:a77f15505b03debf8e44b6d9bb76d751', 'addColumn tableName=transcript', 'Adding root transcript id for bulk delete', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160225-jw-1::joe.wong
-- Change collation on column
ALTER TABLE language_custom_string DROP CONSTRAINT PK_LANG_CUST_STR
GO

alter table language_custom_string alter column string_id nvarchar(85) collate Latin1_General_CS_AS not null
GO

ALTER TABLE language_custom_string ADD CONSTRAINT PK_LANG_CUST_STR PRIMARY KEY (locale_file_id, string_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160225-jw-1', 'joe.wong', 'nschema/120.changeLog.xml', GETDATE(), 572, '8:cc61a2b63c8792ed47bee3bb8c18c80f', 'dropPrimaryKey constraintName=PK_LANG_CUST_STR, tableName=language_custom_string; sql; addPrimaryKey constraintName=PK_LANG_CUST_STR, tableName=language_custom_string', 'Change collation on column', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160229-ca-1::chris.au
-- [PD-4124] Add address fields for training centers
ALTER TABLE code_training_center ADD address1 nvarchar(150)
GO

ALTER TABLE code_training_center ADD address2 nvarchar(150)
GO

ALTER TABLE code_training_center ADD city nvarchar(50)
GO

ALTER TABLE code_training_center ADD province_state nvarchar(50)
GO

ALTER TABLE code_training_center ADD country nvarchar(3)
GO

ALTER TABLE code_training_center ADD postalcode nvarchar(550)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160229-ca-1', 'chris.au', 'nschema/120.changeLog.xml', GETDATE(), 573, '8:9e603111451bb125282b3d20f7bb06c1', 'addColumn tableName=code_training_center', '[PD-4124] Add address fields for training centers', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/120.changeLog.xml::20160303-wc-1::wilson.choi
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,
			t1.transcriptid as resolved_transcriptid
		  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,
			a.creditTranscriptLink as resolved_transcriptid
		  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 ('20160303-wc-1', 'wilson.choi', 'nschema/120.changeLog.xml', GETDATE(), 574, '8:acf132b99bb74f96a72fd9b72a1f564f', 'dropView viewName=aggregated_transcript_vw; createView viewName=aggregated_transcript_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::22Feb2016-sg-1::shravan.gurrala
UPDATE ekpversion SET dblevel = 121, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('22Feb2016-sg-1', 'shravan.gurrala', 'nschema/121.changeLog.xml', GETDATE(), 575, '8:1b6c7c3c89e5259b6a5ba2ce0846e957', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160226-gg-1::gary.gan
-- [PD-2958] CML55 - XLIFF Support for Module Session Metadata
-- Resource string creation for program session title
-- Resource string creation for program session description
-- Resource string creation for module session title
-- Resource string creation for module session description
insert into resource_string
			    (id,
			    resource_key,
			    bundle_id,
			    value_varchar,
			    field,
			    reference_id)
			select
			    cast(row_number() over (order by lb1.bundle_id) as varchar) + '_P_LO_SESSION_TITLE' as rownum,
				NULL,
			    lb1.bundle_id,
			    sd.session_title ,
			    'LO_SESSION_TITLE',
				sd.sessionid 
			from
			    learningObject lo 
			    join sessiondetail sd on lo.learningid=sd.currid
 			    join language_bundle lb1 on lo.learningid=lb1.object_id and lb1.object_type='LO'
GO

insert into resource_string
			    (id,
			    resource_key,
			    bundle_id,
			    value_ntext,
			    field,
			    reference_id)
			select
			    cast(row_number() over (order by lb1.bundle_id) as varchar) + '_P_LO_SESSION_DESCRIPTION' as rownum,
				NULL,
			    lb1.bundle_id,
			    sd.description,
			    'LO_SESSION_DESCRIPTION',
				sd.sessionid 
			from
			    learningObject lo join (
			        select
			            description,
			            sessionid ,
			            currid,
			            lb.bundle_id,
			            lb.is_primary
			        from
			            sessiondetail sd join language_bundle lb on sd.bundle_id=lb.bundle_id) sd on lo.learningid=sd.currid
			    join
			        language_bundle lb1 on lo.learningid=lb1.object_id and lb1.object_type='LO'
			    where 
			    	sd.bundle_id=lb1.bundle_id
GO

insert into resource_string
			    (id,
			    resource_key,
			    bundle_id,
			    value_varchar,
			    field,
			    reference_id)
			select
			    cast(row_number() over (order by lb1.bundle_id) as varchar) + '_M_LO_SESSION_TITLE' as rownum,
			    NULL,
			    lb1.bundle_id,
			    sd.session_title,
			    'LO_SESSION_TITLE',
			    sd.eventid
			from
		        learningObject lo join (
			        select
			            session_title,
			            eventid ,
			            courseid,
			            lb.bundle_id,
			            lb.is_primary
			        from cschedule cs join language_bundle lb on cs.bundle_id=lb.bundle_id
		    	) sd on lo.learningid=sd.courseid
			    join
			        language_bundle lb1 on lo.learningid=lb1.object_id and lb1.object_type='LO'
			where
			    sd.bundle_id = lb1.bundle_id
GO

insert into resource_string
			    (id,
			    resource_key,
			    bundle_id,
			    value_ntext,
			    field,
			    reference_id)
			select
			    cast(row_number() over (order by lb1.bundle_id) as varchar) + '_M_LO_SESSION_DESCRIPTION' as rownum,
			    NULL,
			    lb1.bundle_id,
			    sd.description,
			    'LO_SESSION_DESCRIPTION',
			    sd.eventid
			from
			    learningObject lo join (
			        select
			            description,
			            eventid ,
			            courseid,
			            lb.bundle_id,
			            lb.is_primary
			        from cschedule cs join language_bundle lb on cs.bundle_id=lb.bundle_id
			    ) sd on lo.learningid=sd.courseid
			    join
			        language_bundle lb1 on lo.learningid=lb1.object_id and lb1.object_type='LO'
			where
			    sd.bundle_id = lb1.bundle_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160226-gg-1', 'gary.gan', 'nschema/121.changeLog.xml', GETDATE(), 576, '8:9269bd2493576b6360e560b3a4f98685', 'sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql', '[PD-2958] CML55 - XLIFF Support for Module Session Metadata
Resource string creation for program session title
Resource string creation for program session description
Resource string creation for module session title
Resource string creation for ...', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160428-if-1::ivan.fung
-- [PD-4038] Permissions on News Categories
-- Migrate all existing News Categories to ALL_ORG write permission
-- permission table
-- permission_mapping table
INSERT INTO permissions 
				(objectid,
				objecttype,
				identityid,
				permissioncode,
				identitytype,
				operator,
				count,
				is_pts_objecttype) 
			SELECT
				'NC'+ CAST(newscatid AS NVARCHAR(10)), 
				'NC',
				'*ROOT*',
				2,
				'O',
				'OR',
				0,
				'N' 
			FROM code_newscategory
GO

INSERT INTO permission_mapping 
				(objectid,
				objecttype,
				permissioncode,
				resolved_objectid,
				is_pts_objecttype) 
			SELECT
				'NC'+ CAST(newscatid AS NVARCHAR(10)),
				'NC',
				2,
				'NC'+ CAST(newscatid AS NVARCHAR(10)),
				'N' 
			FROM code_newscategory
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160428-if-1', 'ivan.fung', 'nschema/121.changeLog.xml', GETDATE(), 577, '8:653001add17a836a085b4518c3797ebf', 'sql; sql; sql; sql', '[PD-4038] Permissions on News Categories
Migrate all existing News Categories to ALL_ORG write permission
permission table
permission_mapping table', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160301-nl-1::nick.lee
-- [PD-4081] Drop the column [count]
DROP VIEW permissions_vw
GO

CREATE VIEW permissions_vw AS select pm.objectid, pm.objecttype, p.identityid, pm.permissioncode, 
				p.identitytype, p.operator, p.last_updated, 
				p.last_updater_uid, p.update_meaning_text, p.updater_legal_name 
				from permission_mapping pm inner join permissions p 
				on (pm.resolved_objectid=p.objectid and pm.objecttype=p.objecttype and pm.permissioncode=p.permissioncode and pm.is_pts_objecttype=p.is_pts_objecttype)
GO

DROP INDEX idx_permissions_count ON permissions
GO

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

ALTER TABLE permissions DROP COLUMN count
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160301-nl-1', 'nick.lee', 'nschema/121.changeLog.xml', GETDATE(), 578, '8:5bca8094a86611f596e4d0183ca800d0', 'dropView viewName=permissions_vw; createView viewName=permissions_vw; dropIndex indexName=idx_permissions_count, tableName=permissions; dropColumn columnName=count, tableName=permissions', '[PD-4081] Drop the column [count]', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160309-dc-1::dennis.cheung
-- [PD-4108] ReEnroll upon Certification expire criteria
ALTER TABLE certifications ADD use_autoenroll_filter char(1) CONSTRAINT DF_certifications_use_autoenroll_filter DEFAULT 'N' NOT NULL
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'use_autoenroll_filter'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Whether enable filtering of learner during auto-enroll on certuser expire';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 cert_autoenroll (certid numeric(8, 0) NOT NULL, targetType nvarchar(3) NOT NULL, targetValue nvarchar(85) NOT NULL, startdate datetime, enddate datetime, durationenabled char(1) NOT NULL, durationamount int, durationunit smallint, durationType nvarchar(10), CONSTRAINT pk_cert_autoenroll PRIMARY KEY (certid, targetType, targetValue))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'cert_autoenroll'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.cert_autoenroll';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Filtering of learner during auto-enroll on certuser expire';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'cert_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cert_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'certid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Certification 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'cert_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cert_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'targetType'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The target auto enrollment type, use three letter to represent one type. (Ref: targetType).';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'cert_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cert_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'targetValue'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The target value according to the target 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'cert_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cert_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'startdate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'For time criteria, the start date.';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'cert_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cert_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'enddate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'For time criteria, the end date.';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'cert_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cert_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'durationenabled'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'For relative join dates; Whether enable the duration or not.';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'cert_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cert_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'durationamount'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'For relative join dates; The integer value of duration';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'cert_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cert_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'durationunit'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'For relative join dates; The unit of duration.';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'cert_autoenroll'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cert_autoenroll'; DECLARE @ColumnName SYSNAME set @ColumnName = N'durationType'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'For relative join dates; AL:At Least, LT: Less Than.';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE cert_autoenroll ADD CONSTRAINT fk_cert_autoenroll_certid FOREIGN KEY (certid) REFERENCES certifications (certid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160309-dc-1', 'dennis.cheung', 'nschema/121.changeLog.xml', GETDATE(), 579, '8:0f3cb7cf0aca4ce93e6c9d1b7f7a9d2a', 'addColumn tableName=certifications; createTable tableName=cert_autoenroll; addForeignKeyConstraint baseTableName=cert_autoenroll, constraintName=fk_cert_autoenroll_certid, referencedTableName=certifications', '[PD-4108] ReEnroll upon Certification expire criteria', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160310-gg-1::gary.gan
IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'i_knowledgecenteroptions'
			)
			DROP TRIGGER i_knowledgecenteroptions
GO

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

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160310-gg-1', 'gary.gan', 'nschema/121.changeLog.xml', GETDATE(), 580, '8:daf2851085326d63f1890008b2e26812', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160310-gg-2::gary.gan
-- [PD-4037]Order in Program broken if having elective and mandatory modules
ALTER TABLE knowledgecenteroptions ADD sort_sub_modules char(1) CONSTRAINT DF_knowledgecenteroptions_sort_sub_modules DEFAULT 'R' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'knowledgecenteroptions'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.knowledgecenteroptions'; DECLARE @ColumnName SYSNAME set @ColumnName = N'sort_sub_modules'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'sort sub-modules by R:Required/Elective O:Defined order';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 ('20160310-gg-2', 'gary.gan', 'nschema/121.changeLog.xml', GETDATE(), 581, '8:cd051bf6f73adc55ab3ea31e5f0f8cea', 'addColumn tableName=knowledgecenteroptions', '[PD-4037]Order in Program broken if having elective and mandatory modules', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160329-nl-1::nick.lee
-- [PD-4162] Additional indexes for performance appraisals - drop hotfix indexes
IF  EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NUK_HOTFIX_USER_APPRASIAL_TEMPATE_ID_COUNT')
			DROP INDEX NUK_HOTFIX_USER_APPRASIAL_TEMPATE_ID_COUNT ON user_appraisal
GO

IF  EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NUK_HOTFIX_USER_APPRASIAL_APPRAISEE_ID')
			DROP INDEX NUK_HOTFIX_USER_APPRASIAL_APPRAISEE_ID ON user_appraisal
GO

IF  EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NUK_HOTFIX_GOAL_OWNER_ID')
			DROP INDEX NUK_HOTFIX_GOAL_OWNER_ID ON goal
GO

IF  EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NUK_HOTFIX_APPRAISAL_U_FUTURE_OBJECTIVE_APPRAISAL_ID')
			DROP INDEX NUK_HOTFIX_APPRAISAL_U_FUTURE_OBJECTIVE_APPRAISAL_ID ON appraisal_u_future_objective
GO

IF  EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NUK_HOTFIX_APPRAISAL_U_GOAL_PLAN_APPRAISAL_ID')
			DROP INDEX NUK_HOTFIX_APPRAISAL_U_GOAL_PLAN_APPRAISAL_ID ON appraisal_u_goal_plan
GO

IF  EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NUK_HOTFIX_APPRAISAL_U_GOAL_PLAN_GOAL_ID')
			DROP INDEX NUK_HOTFIX_APPRAISAL_U_GOAL_PLAN_GOAL_ID ON appraisal_u_goal_plan
GO

IF  EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NUK_HOTFIX_APPRAISAL_U_GOAL_RESULT_APPRAISAL_ID')
			DROP INDEX NUK_HOTFIX_APPRAISAL_U_GOAL_RESULT_APPRAISAL_ID ON appraisal_u_goal_result
GO

IF  EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NUK_HOTFIX_APPRAISAL_U_GOAL_RESULT_GOAL_ID')
			DROP INDEX NUK_HOTFIX_APPRAISAL_U_GOAL_RESULT_GOAL_ID ON appraisal_u_goal_result
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160329-nl-1', 'nick.lee', 'nschema/121.changeLog.xml', GETDATE(), 582, '8:2cc59ce3663de19846e3612f2cfafdcf', 'sql; sql; sql; sql; sql; sql; sql; sql', '[PD-4162] Additional indexes for performance appraisals - drop hotfix indexes', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160329-nl-2::nick.lee
-- [PD-4162] Additional indexes for performance appraisals
CREATE NONCLUSTERED INDEX NUK_USER_APPR_TEMPATE_ID ON user_appraisal(appraisal_template_id)
GO

CREATE NONCLUSTERED INDEX NUK_USER_APPR_APPRAISEE_ID ON user_appraisal(appraisee_id)
GO

CREATE NONCLUSTERED INDEX NUK_GOAL_OWNER_ID ON goal(owner_id)
GO

CREATE NONCLUSTERED INDEX NUK_APPR_U_FUTURE_OBJ_APPR_ID ON appraisal_u_future_objective(appraisal_id)
GO

CREATE NONCLUSTERED INDEX NUK_APPR_U_GOAL_PLAN_APPR_ID ON appraisal_u_goal_plan(appraisal_id)
GO

CREATE NONCLUSTERED INDEX NUK_APPR_U_GOAL_PLAN_GOAL_ID ON appraisal_u_goal_plan(goal_id)
GO

CREATE NONCLUSTERED INDEX NUK_APPR_U_GOAL_RESULT_APPR_ID ON appraisal_u_goal_result(appraisal_id)
GO

CREATE NONCLUSTERED INDEX NUK_APPR_U_GOAL_RESULT_GOAL_ID ON appraisal_u_goal_result(goal_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160329-nl-2', 'nick.lee', 'nschema/121.changeLog.xml', GETDATE(), 583, '8:09177f4fe7c06598577ee2b0775d3c7d', 'createIndex indexName=NUK_USER_APPR_TEMPATE_ID, tableName=user_appraisal; createIndex indexName=NUK_USER_APPR_APPRAISEE_ID, tableName=user_appraisal; createIndex indexName=NUK_GOAL_OWNER_ID, tableName=goal; createIndex indexName=NUK_APPR_U_FUTURE_...', '[PD-4162] Additional indexes for performance appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160331-fr-1::francis.reforma
-- Used to identify which enrollment policy has the highest precedence when a user belongs to multiple user group
ALTER TABLE custom_course_policy ADD usergroup_policy_precedence int
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160331-fr-1', 'francis.reforma', 'nschema/121.changeLog.xml', GETDATE(), 584, '8:9b348f0140400b04374337912c79e07c', 'addColumn tableName=custom_course_policy', 'Used to identify which enrollment policy has the highest precedence when a user belongs to multiple user group', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160331-fr-2::francis.reforma
-- Identify the custom enrollment policy type
ALTER TABLE learningObject ADD custom_coursepolicy_type nvarchar(5)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160331-fr-2', 'francis.reforma', 'nschema/121.changeLog.xml', GETDATE(), 585, '8:d707aea80dc9cd710b196802cff65a15', 'addColumn tableName=learningObject', 'Identify the custom enrollment policy type', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160331-fr-3::francis.reforma
-- Populate course policy type if organization based course policy exist
UPDATE learningObject 
			SET custom_coursepolicy_type = 'O' 
			WHERE learningid in (SELECT DISTINCT learning_id FROM custom_course_policy WHERE object_type = 'O')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160331-fr-3', 'francis.reforma', 'nschema/121.changeLog.xml', GETDATE(), 586, '8:9130393e7796a8051ab30e3ead1f6548', 'sql', 'Populate course policy type if organization based course policy exist', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160405-js-1::janice.sung
-- [PD-4214] Program Assigned Module Fixed Date Availability
ALTER TABLE currcourses ADD enrollAvailableDate datetime
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'currcourses'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.currcourses'; DECLARE @ColumnName SYSNAME set @ColumnName = N'enrollAvailableDate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date the assigned module is available for self-enrollment';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 ('20160405-js-1', 'janice.sung', 'nschema/121.changeLog.xml', GETDATE(), 587, '8:e11947eb6b23a97edcb28e66c5373f88', 'addColumn tableName=currcourses', '[PD-4214] Program Assigned Module Fixed Date Availability', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160411-ww-1::winnie.wong
-- [Z30819] Add options to specify or control module start date for modules of learning program
ALTER TABLE sessiondetail ADD mod_start_type char(1) CONSTRAINT DF_sessiondetail_mod_start_type DEFAULT 'D'
GO

ALTER TABLE sessiondetail ADD first_mod_start_date datetime
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'mod_start_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Module start 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'sessiondetail'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.sessiondetail'; DECLARE @ColumnName SYSNAME set @ColumnName = N'first_mod_start_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'First module start date';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 ('20160411-ww-1', 'winnie.wong', 'nschema/121.changeLog.xml', GETDATE(), 588, '8:9afc1f4a327fe936bd3cad9eaa8f5422', 'addColumn tableName=sessiondetail', '[Z30819] Add options to specify or control module start date for modules of learning program', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160411-ww-2::winnie.wong
-- [Z30819] Add options to specify or control module start date for modules of learning program
ALTER TABLE currcourses ADD start_on_date datetime
GO

ALTER TABLE currcourses ADD start_in_days int
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'currcourses'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.currcourses'; DECLARE @ColumnName SYSNAME set @ColumnName = N'start_on_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Start on date';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'currcourses'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.currcourses'; DECLARE @ColumnName SYSNAME set @ColumnName = N'start_in_days'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Start in days';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 ('20160411-ww-2', 'winnie.wong', 'nschema/121.changeLog.xml', GETDATE(), 589, '8:d6f87fe3154d35c41d43142359122a10', 'addColumn tableName=currcourses', '[Z30819] Add options to specify or control module start date for modules of learning program', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160411-ww-3::winnie.wong
-- Set module start type as default
UPDATE sessiondetail 
			SET mod_start_type = 'D' 
			WHERE mod_start_type is null
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160411-ww-3', 'winnie.wong', 'nschema/121.changeLog.xml', GETDATE(), 590, '8:3d0598e7a94e00338fc97d36d32e584b', 'sql', 'Set module start type as default', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160519-yc-1::yanfang.cui
-- [PD-4216] Relative to Order Advancement Assigned Module Availability
ALTER TABLE currcourses ADD enrollDateFlag char(1) CONSTRAINT DF_currcourses_enrollDateFlag DEFAULT 'P' NOT NULL
GO

ALTER TABLE currcourses ADD enrollNDays int CONSTRAINT DF_currcourses_enrollNDays DEFAULT 0
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'currcourses'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.currcourses'; DECLARE @ColumnName SYSNAME set @ColumnName = N'enrollDateFlag'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Flag the assigned module is available for self-enrollment';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'currcourses'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.currcourses'; DECLARE @ColumnName SYSNAME set @ColumnName = N'enrollNDays'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Days the assigned module is available for self-enrollment';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 ('20160519-yc-1', 'yanfang.cui', 'nschema/121.changeLog.xml', GETDATE(), 591, '8:204cf355007f747ebd73181c9a511c55', 'addColumn tableName=currcourses', '[PD-4216] Relative to Order Advancement Assigned Module Availability', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160519-yc-2::yanfang.cui
-- [PD-4216] Relative to Order Advancement Assigned Module Availability, Data migration
UPDATE currcourses
			SET enrollDateFlag = 'T' 
			WHERE enrollAvailableDate IS NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160519-yc-2', 'yanfang.cui', 'nschema/121.changeLog.xml', GETDATE(), 592, '8:e72454523e805f36d48568fdc9b20889', 'sql', '[PD-4216] Relative to Order Advancement Assigned Module Availability, Data migration', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160519-dc-1::dennis.cheung
-- [PD-4586] Performance: lookup active learner by learning object
-- MySQL require all FK has index, workaround by recreate
ALTER TABLE transcript DROP CONSTRAINT fk_transcript_learningid
GO

DROP INDEX NUK_TRAN_LID ON transcript
GO

CREATE NONCLUSTERED INDEX NUK_TRAN_LID ON transcript(learningid, eventid, studentstatus, isOpen, userid)
GO

ALTER TABLE transcript ADD CONSTRAINT fk_transcript_learningid 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 ('20160519-dc-1', 'dennis.cheung', 'nschema/121.changeLog.xml', GETDATE(), 593, '8:8832ea002ccfaedfae39b2cefa00f139', 'dropForeignKeyConstraint baseTableName=transcript, constraintName=fk_transcript_learningid; dropIndex indexName=NUK_TRAN_LID, tableName=transcript; createIndex indexName=NUK_TRAN_LID, tableName=transcript; addForeignKeyConstraint baseTableName=tra...', '[PD-4586] Performance: lookup active learner by learning object
MySQL require all FK has index, workaround by recreate', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160531-kk-1::kumar.khiani
-- [PD-3751] Bring Job Profile notifications to the Job Profile level
ALTER TABLE job_profile ADD assign_assignee_email_id nvarchar(15)
GO

ALTER TABLE job_profile ADD assign_assignee_type char(1) CONSTRAINT DF_job_profile_assign_assignee_type DEFAULT 'S' NOT NULL
GO

ALTER TABLE job_profile ADD assign_da_email_id nvarchar(15)
GO

ALTER TABLE job_profile ADD assign_da_type char(1) CONSTRAINT DF_job_profile_assign_da_type DEFAULT 'S' NOT NULL
GO

ALTER TABLE job_profile ADD assign_owner_email_id nvarchar(15)
GO

ALTER TABLE job_profile ADD assign_owner_type char(1) CONSTRAINT DF_job_profile_assign_owner_type DEFAULT 'S' NOT NULL
GO

ALTER TABLE job_profile ADD deassign_assignee_email_id nvarchar(15)
GO

ALTER TABLE job_profile ADD deassign_assignee_type char(1) CONSTRAINT DF_job_profile_deassign_assignee_type DEFAULT 'S' NOT NULL
GO

ALTER TABLE job_profile ADD deassign_da_email_id nvarchar(15)
GO

ALTER TABLE job_profile ADD deassign_da_type char(1) CONSTRAINT DF_job_profile_deassign_da_type DEFAULT 'S' NOT NULL
GO

ALTER TABLE job_profile ADD deassign_owner_email_id nvarchar(15)
GO

ALTER TABLE job_profile ADD deassign_owner_type char(1) CONSTRAINT DF_job_profile_deassign_owner_type DEFAULT 'S' NOT NULL
GO

ALTER TABLE job_profile ADD CONSTRAINT FK_JP_ASSIGN_ASSIGNEE_EMAIL FOREIGN KEY (assign_assignee_email_id) REFERENCES email_profile (email_profile_id)
GO

ALTER TABLE job_profile ADD CONSTRAINT FK_JP_ASSIGN_DA_EMAIL FOREIGN KEY (assign_da_email_id) REFERENCES email_profile (email_profile_id)
GO

ALTER TABLE job_profile ADD CONSTRAINT FK_JP_ASSIGN_OWNER_EMAIL FOREIGN KEY (assign_owner_email_id) REFERENCES email_profile (email_profile_id)
GO

ALTER TABLE job_profile ADD CONSTRAINT FK_JP_DEASSIGN_ASSIGNEE_EMAIL FOREIGN KEY (deassign_assignee_email_id) REFERENCES email_profile (email_profile_id)
GO

ALTER TABLE job_profile ADD CONSTRAINT FK_JP_DEASSIGN_DA_EMAIL FOREIGN KEY (deassign_da_email_id) REFERENCES email_profile (email_profile_id)
GO

ALTER TABLE job_profile ADD CONSTRAINT FK_JP_DEASSIGN_OWNER_EMAIL FOREIGN KEY (deassign_owner_email_id) REFERENCES email_profile (email_profile_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160531-kk-1', 'kumar.khiani', 'nschema/121.changeLog.xml', GETDATE(), 594, '8:ef3fd01d453f247d01c093cffbe3d698', 'addColumn tableName=job_profile; addForeignKeyConstraint baseTableName=job_profile, constraintName=FK_JP_ASSIGN_ASSIGNEE_EMAIL, referencedTableName=email_profile; addForeignKeyConstraint baseTableName=job_profile, constraintName=FK_JP_ASSIGN_DA_EM...', '[PD-3751] Bring Job Profile notifications to the Job Profile level', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160609-hf-1::hubert.fajarit
ALTER TABLE course ADD allow_self_enroll char(1) CONSTRAINT DF_course_allow_self_enroll DEFAULT 'Y' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'course'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.course'; DECLARE @ColumnName SYSNAME set @ColumnName = N'allow_self_enroll'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Flag for allowing self-enrollment';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 ('20160609-hf-1', 'hubert.fajarit', 'nschema/121.changeLog.xml', GETDATE(), 595, '8:74a0bf2dd32129d612010dc4cfb6497c', 'addColumn tableName=course', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160612-rr-1::ray.ruff
ALTER TABLE mastertest ADD is_anonymous char(1) CONSTRAINT DF_mastertest_is_anonymous DEFAULT 'N' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'mastertest'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.mastertest'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_anonymous'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Flag for allowing anonymous evaluations';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 ('20160612-rr-1', 'ray.ruff', 'nschema/121.changeLog.xml', GETDATE(), 596, '8:4d14d9dfa59b4954be76c98f6e928474', 'addColumn tableName=mastertest', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160613-rr-1::ray.ruff
-- Table to store site activity and performance history
CREATE TABLE system_activity_stats (entry_date datetime NOT NULL, entry_type char(1) NOT NULL, n_logins int NOT NULL, n_enrollments int NOT NULL, n_hits int NOT NULL, n_concurrent_users int NOT NULL, n_cons int NOT NULL, sql_rate float(53) NOT NULL, load_rating int NOT NULL, jvm_memory int NOT NULL, optional_1 int NOT NULL)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'system_activity_stats'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.system_activity_stats';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores snapshots of key activity statistics.';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'entry_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'System date for the stats snapshot';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'entry_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Daily summary (D) or Snapshot (S)';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'n_logins'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Total # of logins since startup';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'n_enrollments'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Total # of enrollments since startup';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'n_hits'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Total # of TX/API hits since startup';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'n_concurrent_users'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Current # of logged-in users';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'n_cons'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Current # of DB connections in use';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'sql_rate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Current SQL rate/sec';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'load_rating'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Current overall load rating';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'jvm_memory'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'JVM Memory use';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'system_activity_stats'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_activity_stats'; DECLARE @ColumnName SYSNAME set @ColumnName = N'optional_1'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Reserved for flexible use';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 system_activity_stats ADD CONSTRAINT PK_SYS_ACT_STATS PRIMARY KEY (entry_date)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160613-rr-1', 'ray.ruff', 'nschema/121.changeLog.xml', GETDATE(), 597, '8:473bc3a7ed3848bfb1868af29d561387', 'createTable tableName=system_activity_stats; addPrimaryKey constraintName=PK_SYS_ACT_STATS, tableName=system_activity_stats', 'Table to store site activity and performance history', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/121.changeLog.xml::20160613-rr-2::ray.ruff
-- Table to store NTS update history
CREATE TABLE system_updates (release_version nvarchar(85) NOT NULL, entry_date datetime NOT NULL)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'system_updates'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.system_updates';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores new build or release changes.';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'system_updates'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_updates'; DECLARE @ColumnName SYSNAME set @ColumnName = N'release_version'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'NTS release/build';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'system_updates'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.system_updates'; DECLARE @ColumnName SYSNAME set @ColumnName = N'entry_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'System date for the entry';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 system_updates ADD CONSTRAINT PK_SYS_UPDATES PRIMARY KEY (release_version)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160613-rr-2', 'ray.ruff', 'nschema/121.changeLog.xml', GETDATE(), 598, '8:d2b4b93239c24c2733f7cea1b0de7646', 'createTable tableName=system_updates; addPrimaryKey constraintName=PK_SYS_UPDATES, tableName=system_updates', 'Table to store NTS update history', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::14June2016-sg-1::shravan.gurrala
UPDATE ekpversion SET dblevel = 122, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('14June2016-sg-1', 'shravan.gurrala', 'nschema/122.changeLog.xml', GETDATE(), 599, '8:ab6a7d274f9f55d4540ef4809ffbae18', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160630-nl-1::nick.lee
-- [PD-3778] Persistance of Finished Background Tasks
CREATE TABLE background_task_log (id nvarchar(85) NOT NULL, node_id smallint NOT NULL, task_key smallint NOT NULL, user_id nvarchar(85), queue_time datetime NOT NULL, start_time datetime, end_time datetime, status char(1) NOT NULL, parameters nvarchar (max), messages nvarchar (max), CONSTRAINT PK_BG_TASK_LOG PRIMARY KEY (id))
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160630-nl-1', 'nick.lee', 'nschema/122.changeLog.xml', GETDATE(), 600, '8:836cca3d6da176e8e6b5377ac36c1c36', 'createTable tableName=background_task_log', '[PD-3778] Persistance of Finished Background Tasks', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160630-nl-2::nick.lee
-- Add Indexes to background_task_log
CREATE INDEX nuk_background_task_log ON background_task_log
			(
				queue_time DESC
				,node_id ASC
				,user_id ASC
			)
GO

CREATE NONCLUSTERED INDEX nuk_bg_task_log_end_time ON background_task_log(end_time)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160630-nl-2', 'nick.lee', 'nschema/122.changeLog.xml', GETDATE(), 601, '8:a62cffb026a2fee9c17a91fe50d69de5', 'sql; createIndex indexName=nuk_bg_task_log_end_time, tableName=background_task_log', 'Add Indexes to background_task_log', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160711-yu-1::yousri.usop
ALTER TABLE question ADD enable_question_attachment char(1) CONSTRAINT DF_question_enable_question_attachment DEFAULT 'N' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'question'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question'; DECLARE @ColumnName SYSNAME set @ColumnName = N'enable_question_attachment'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'add enable_question_attachment column for enabling attachment on questions';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_attachment (id nvarchar(85) NOT NULL, instanceid nvarchar(15) NOT NULL, sectionnum smallint NOT NULL, questionnum smallint NOT NULL, filename nvarchar(85) NOT NULL, display_name nvarchar(85), path nvarchar(255), description nvarchar(255), owner_id nvarchar(85) NOT NULL, uploaded_by nvarchar(85), upload_date datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'question_attachment'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.question_attachment';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'add question_attachment table for enabling question attachment';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_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Question Attachment 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'question_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'instanceid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Test Instance';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_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'sectionnum'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Test 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'question_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'questionnum'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Question 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'question_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'filename'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'filename of the attachment.';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_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'display_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'description for the attachment.';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_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'path'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'attachment path';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_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'description'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'file 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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'question_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'owner_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Owner 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'question_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'uploaded_by'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'uploader';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_attachment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_attachment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'upload_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'date upload of the attachment';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_attachment ADD CONSTRAINT PK_QUESTION_ATTACHMENT PRIMARY KEY (id)
GO

CREATE NONCLUSTERED INDEX NUK_QUESTION_ATTACHMENT ON question_attachment(instanceid, sectionnum, questionnum)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160711-yu-1', 'yousri.usop', 'nschema/122.changeLog.xml', GETDATE(), 602, '8:dc26c3ecdc02d4cf285db6f3f6328852', 'addColumn tableName=question; createTable tableName=question_attachment; addPrimaryKey constraintName=PK_QUESTION_ATTACHMENT, tableName=question_attachment; createIndex indexName=NUK_QUESTION_ATTACHMENT, tableName=question_attachment', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160719-fr-1::francis.reforma
-- Update column size to accommodate long encrypted passwords
ALTER TABLE prev_passwords ALTER COLUMN password NVARCHAR(255) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160719-fr-1', 'francis.reforma', 'nschema/122.changeLog.xml', GETDATE(), 603, '8:a73d3f9e8566ca9312670072491cd999', 'sql; sql; sql', 'Update column size to accommodate long encrypted passwords', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160713-kk-1::kumar.khiani
-- [PD-4825] Tin Can API: Associate Tin Can API activities with Talent Suite sessions and process Launched/Initialized statements
CREATE TABLE tincan_lo_activity (id nvarchar(85) NOT NULL, learning_id nvarchar(85) NOT NULL, session_id nvarchar(15) NOT NULL, tincan_activity_id nvarchar(2000) NOT NULL, tincan_activity_id_hash nvarchar(32) NOT NULL, order_number smallint NOT NULL, CONSTRAINT pk_tincan_lo_activity PRIMARY KEY (id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_lo_activity'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.tincan_lo_activity';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Stores the mapping of the Activity Id to Learning ID and 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.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'tincan_lo_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_lo_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'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'tincan_lo_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_lo_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'learning_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learning Id (Ref: learningObject).';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'tincan_lo_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_lo_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'session_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'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'tincan_lo_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_lo_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'tincan_activity_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Tin Can Activity 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'tincan_lo_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_lo_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'tincan_activity_id_hash'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Hash for Tin Can Activity 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'tincan_lo_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_lo_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'order_number'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Order Number for Tin Can Activity ID within a session';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 tincan_lo_activity ADD CONSTRAINT fk_tincan_lo_activity_lid FOREIGN KEY (learning_id) REFERENCES learningObject (learningid)
GO

CREATE NONCLUSTERED INDEX TINCAN_LO_ACTIVITY_HASH ON tincan_lo_activity(tincan_activity_id_hash)
GO

CREATE NONCLUSTERED INDEX TINCAN_LO_ACTIVITY_LID ON tincan_lo_activity(learning_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160713-kk-1', 'kumar.khiani', 'nschema/122.changeLog.xml', GETDATE(), 604, '8:435935d5e371079aee051f31c82d5ccb', 'createTable tableName=tincan_lo_activity; addForeignKeyConstraint baseTableName=tincan_lo_activity, constraintName=fk_tincan_lo_activity_lid, referencedTableName=learningObject; createIndex indexName=TINCAN_LO_ACTIVITY_HASH, tableName=tincan_lo_ac...', '[PD-4825] Tin Can API: Associate Tin Can API activities with Talent Suite sessions and process Launched/Initialized statements', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160714-sc-1::shu.cui
-- [PD-4768] Tin Can API: accept basic statements and administrator's viewer
CREATE TABLE tincan_statement (id char(36) NOT NULL, create_date datetime NOT NULL, asserted_by nvarchar(85) NOT NULL, object_id nvarchar(2000) NOT NULL, verb_id nvarchar(2000) NOT NULL, actor_name nvarchar(2000) NOT NULL, json nvarchar (max) NOT NULL, CONSTRAINT PK_TINCAN_STATEMENT PRIMARY KEY (id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique ID (primary key), UUID format';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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'create_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Create date of the statement';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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'asserted_by'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'User ID by whom the statement is asserted';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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'object_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Object ID of the statement';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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'verb_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Verb ID of the statement';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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'actor_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Actor name of the statement';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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'json'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Original Json of the statement request';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 tincan_statement ADD CONSTRAINT FK_TINCAN_STATEMENT_ASSERTEDBY FOREIGN KEY (asserted_by) REFERENCES users_meta (user_meta_id)
GO

CREATE NONCLUSTERED INDEX NUK_STATE_CREATE_TIME ON tincan_statement(create_date)
GO

CREATE NONCLUSTERED INDEX NUK_STATE_AST_BY_CREATE_TIME ON tincan_statement(asserted_by, create_date)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160714-sc-1', 'shu.cui', 'nschema/122.changeLog.xml', GETDATE(), 605, '8:6c738a9e84b68473e838abd6c539a5c5', 'createTable tableName=tincan_statement; addForeignKeyConstraint baseTableName=tincan_statement, constraintName=FK_TINCAN_STATEMENT_ASSERTEDBY, referencedTableName=users_meta; createIndex indexName=NUK_STATE_CREATE_TIME, tableName=tincan_statement;...', '[PD-4768] Tin Can API: accept basic statements and administrator''s viewer', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160719-ca-1::chris.au
-- [PD-4843] Recovery of Revision-Related Tasks on Startup
CREATE TABLE pending_mail (id nvarchar(85) NOT NULL, node_id smallint NOT NULL, from_user_id nvarchar(85) NOT NULL, to_users nvarchar (max), cc_users nvarchar (max), subject nvarchar(255), content nvarchar (max), locale smallint, create_time datetime NOT NULL, CONSTRAINT PK_PEND_MAIL PRIMARY KEY (id))
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160719-ca-1', 'chris.au', 'nschema/122.changeLog.xml', GETDATE(), 606, '8:872ff0b2c37b7f76c686a17e3b245251', 'createTable tableName=pending_mail', '[PD-4843] Recovery of Revision-Related Tasks on Startup', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160719-ca-2::chris.au
-- Add Index to pending_mail
CREATE NONCLUSTERED INDEX nuk_pending_mail_create_time ON pending_mail(node_id, create_time)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160719-ca-2', 'chris.au', 'nschema/122.changeLog.xml', GETDATE(), 607, '8:3f6752190f87f865aaa2a6b335ff0c64', 'createIndex indexName=nuk_pending_mail_create_time, tableName=pending_mail', 'Add Index to pending_mail', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160727-vn-1::vincent.ni
ALTER TABLE tincan_statement ADD duration nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'duration'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'add duration column for updating Total Training Time';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 ('20160727-vn-1', 'vincent.ni', 'nschema/122.changeLog.xml', GETDATE(), 608, '8:688dd500df15c30c7dd153279481e341', 'addColumn tableName=tincan_statement', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160728-vn-2::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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160728-vn-2', 'vincent.ni', 'nschema/122.changeLog.xml', GETDATE(), 609, '8:a28a844961adf57750b1241e01765747', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160728-vn-3::vincent.ni
ALTER TABLE cschedule ADD is_allow_update_total_time char(1) CONSTRAINT DF_cschedule_is_allow_update_total_time DEFAULT 'N' NOT NULL
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'is_allow_update_total_time'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Allow trusted statements to update Total Training Time';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 ('20160728-vn-3', 'vincent.ni', 'nschema/122.changeLog.xml', GETDATE(), 610, '8:5cc8aea33759b594044c6f0c9752d575', 'addColumn tableName=cschedule', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160728-vn-4::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, 18)) nregistered
			FROM cschedule
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160728-vn-4', 'vincent.ni', 'nschema/122.changeLog.xml', GETDATE(), 611, '8:1f0ebbed93b0311ce49c71e77f0127dc', 'dropView viewName=cschedule_vw; createView viewName=cschedule_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160726-wl-1::winnie.li
-- [PD-5057] Tin Can API: support for mbox as Inverse Functional Identifier for Actor - Add actor_type to tincan_statement
ALTER TABLE tincan_statement ADD actor_type char(1) CONSTRAINT DF_tincan_statement_actor_type DEFAULT 'U' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'actor_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Actor Type of the statement';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 ('20160726-wl-1', 'winnie.li', 'nschema/122.changeLog.xml', GETDATE(), 612, '8:ccecf4b60eb085cb9b6c6895ec9a6209', 'addColumn tableName=tincan_statement', '[PD-5057] Tin Can API: support for mbox as Inverse Functional Identifier for Actor - Add actor_type to tincan_statement', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160727-dc-1::dennis.cheung
-- [PD-5070] Tin Can API: basic State API support
CREATE TABLE tincan_document (id nvarchar(85) NOT NULL, updated datetime NOT NULL, content_type nvarchar(85) NOT NULL, original_size int NOT NULL, gzip_content varbinary(MAX) NOT NULL, CONSTRAINT pk_tincan_document PRIMARY KEY (id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_document'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_document'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Internal pseudo 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'tincan_document'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_document'; DECLARE @ColumnName SYSNAME set @ColumnName = N'updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The instant of last update of document';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'tincan_document'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_document'; DECLARE @ColumnName SYSNAME set @ColumnName = N'content_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'MIME Content-Type of document';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'tincan_document'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_document'; DECLARE @ColumnName SYSNAME set @ColumnName = N'original_size'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Original document size in bytes';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'tincan_document'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_document'; DECLARE @ColumnName SYSNAME set @ColumnName = N'gzip_content'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'gzipped document content';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 tincan_state (id nvarchar(85) NOT NULL, user_meta_id nvarchar(85) NOT NULL, activity_id nvarchar(2000) NOT NULL, activity_id_hash_sha256 char(64) NOT NULL, agent_type char(1) NOT NULL, agent_name nvarchar(2000) NOT NULL, agent_name_hash_sha256 char(64) NOT NULL, state_id nvarchar(2000) NOT NULL, state_id_hash_sha256 char(64) NOT NULL, registration char(36), document_id nvarchar(85) NOT NULL, CONSTRAINT pk_tincan_state PRIMARY KEY (id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Internal pseudo 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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'user_meta_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'User meta ID by whom have own the record';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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'activity_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'xAPI: activity_id of state';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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'activity_id_hash_sha256'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'sha-256 hash of activity_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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'agent_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Enum (mbox, mbox_sha1sum, openid, account) of state';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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'agent_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'xAPI: agent.mbox or agent.mbox_sha1sum or agent.openid or (agent.account.homePage | \n | agent.account.name) of state';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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'agent_name_hash_sha256'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'sha-256 hash of agent_name';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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'state_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'xAPI: state_id of state';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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'state_id_hash_sha256'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'sha-256 hash of state_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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'registration'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'xAPI: registration of state (UUID)';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'tincan_state'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_state'; DECLARE @ColumnName SYSNAME set @ColumnName = N'document_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Foreign key tincan_document.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 tincan_state ADD CONSTRAINT fk_tincan_state_document FOREIGN KEY (document_id) REFERENCES tincan_document (id)
GO

CREATE UNIQUE NONCLUSTERED INDEX uk_tincan_state ON tincan_state(user_meta_id, activity_id_hash_sha256, agent_type, agent_name_hash_sha256, registration, state_id_hash_sha256)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160727-dc-1', 'dennis.cheung', 'nschema/122.changeLog.xml', GETDATE(), 613, '8:0ff0390074d95a6a847027b75861cdfa', 'createTable tableName=tincan_document; createTable tableName=tincan_state; addForeignKeyConstraint baseTableName=tincan_state, constraintName=fk_tincan_state_document, referencedTableName=tincan_document; createIndex indexName=uk_tincan_state, tab...', '[PD-5070] Tin Can API: basic State API support', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160729-kl-1::kam.lee
-- [PD-5041] Tin Can API: statement viewers should display actions triggered by individual statements
CREATE TABLE tincan_statement_action_info (id nvarchar(85) NOT NULL, actor_name nvarchar(85) NOT NULL, statement_id char(36) NOT NULL, learning_id nvarchar(85) NOT NULL, session_id nvarchar(15) NOT NULL, action_order smallint NOT NULL, action smallint NOT NULL, content nvarchar (max) NOT NULL, CONSTRAINT PK_TINCAN_STATE_ACTION_INFO PRIMARY KEY (id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_statement_action_info'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement_action_info'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique 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'tincan_statement_action_info'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement_action_info'; DECLARE @ColumnName SYSNAME set @ColumnName = N'actor_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'CurrentPID of user_meta';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'tincan_statement_action_info'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement_action_info'; DECLARE @ColumnName SYSNAME set @ColumnName = N'statement_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Id of the statement';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'tincan_statement_action_info'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement_action_info'; DECLARE @ColumnName SYSNAME set @ColumnName = N'learning_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Id of the learningObject';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'tincan_statement_action_info'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement_action_info'; DECLARE @ColumnName SYSNAME set @ColumnName = N'session_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learning object 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'tincan_statement_action_info'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement_action_info'; DECLARE @ColumnName SYSNAME set @ColumnName = N'action_order'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Action order';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'tincan_statement_action_info'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement_action_info'; DECLARE @ColumnName SYSNAME set @ColumnName = N'action'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Log action';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'tincan_statement_action_info'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement_action_info'; DECLARE @ColumnName SYSNAME set @ColumnName = N'content'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Information of Log Action';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 tincan_statement_action_info ADD CONSTRAINT FK_TINCAN_STATEMENTID FOREIGN KEY (statement_id) REFERENCES tincan_statement (id)
GO

CREATE NONCLUSTERED INDEX NUK_STATE_ACT_INFO_STATE_ID ON tincan_statement_action_info(statement_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160729-kl-1', 'kam.lee', 'nschema/122.changeLog.xml', GETDATE(), 614, '8:af834aa36bbbc3b37785495de5a908d0', 'createTable tableName=tincan_statement_action_info; addForeignKeyConstraint baseTableName=tincan_statement_action_info, constraintName=FK_TINCAN_STATEMENTID, referencedTableName=tincan_statement; createIndex indexName=NUK_STATE_ACT_INFO_STATE_ID, ...', '[PD-5041] Tin Can API: statement viewers should display actions triggered by individual statements', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160810-gg-1::gary.gan
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 ('20160810-gg-1', 'gary.gan', 'nschema/122.changeLog.xml', GETDATE(), 615, '8:aec52f6e9a0246c3847e18c79d9373f4', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160810-gg-2::gary.gan
-- [PD-5093] Option to specify who can select the program session
ALTER TABLE sessiondetail ADD session_selector nvarchar(2) CONSTRAINT DF_sessiondetail_session_selector DEFAULT 'P' NOT NULL
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'session_selector'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Who can select the session, P-Participant R-Reviewer PR-Participant and Reviewer';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 ('20160810-gg-2', 'gary.gan', 'nschema/122.changeLog.xml', GETDATE(), 616, '8:1ada3bd1acc188a38b991dffe72af414', 'addColumn tableName=sessiondetail', '[PD-5093] Option to specify who can select the program session', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160810-gg-3::gary.gan
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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160810-gg-3', 'gary.gan', 'nschema/122.changeLog.xml', GETDATE(), 617, '8:a28a844961adf57750b1241e01765747', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160810-gg-4::gary.gan
-- [PD-5089] Option to specify who can select the session
ALTER TABLE cschedule ADD session_selector nvarchar(2) CONSTRAINT DF_cschedule_session_selector DEFAULT 'P' NOT NULL
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'session_selector'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Who can select the session, P-Participant R-Reviewer PR-Participant and Reviewer';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 ('20160810-gg-4', 'gary.gan', 'nschema/122.changeLog.xml', GETDATE(), 618, '8:34f1132f898962d49d8ebd3573c3d48f', 'addColumn tableName=cschedule', '[PD-5089] Option to specify who can select the session', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160810-gg-5::gary.gan
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, 18)) nregistered
			FROM cschedule
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160810-gg-5', 'gary.gan', 'nschema/122.changeLog.xml', GETDATE(), 619, '8:1f0ebbed93b0311ce49c71e77f0127dc', 'dropView viewName=cschedule_vw; createView viewName=cschedule_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160810-sc-1::shu.cui
-- [PD-5135] Tin Can API: result properties other than result.duration
ALTER TABLE tincan_statement ADD raw_score nvarchar(20)
GO

ALTER TABLE tincan_statement ADD scaled_score nvarchar(20)
GO

ALTER TABLE tincan_statement ADD success char(1)
GO

ALTER TABLE tincan_statement ADD response nvarchar(2000)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'raw_score'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Raw score of result';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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'scaled_score'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Scaled score of result';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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'success'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Whether is successful, T - true, F - false';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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'response'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Response of result';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 ('20160810-sc-1', 'shu.cui', 'nschema/122.changeLog.xml', GETDATE(), 620, '8:391e53f27fc5637a5e278ca49de659f6', 'addColumn tableName=tincan_statement', '[PD-5135] Tin Can API: result properties other than result.duration', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160811-js-1::janice.sung
ALTER TABLE tincan_statement ADD obj_def_type nvarchar(2000)
GO

ALTER TABLE tincan_statement ADD obj_def_interaction_type nvarchar(85)
GO

ALTER TABLE tincan_statement ADD obj_def_correct_res_pattern nvarchar(2000)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'obj_def_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Object definition 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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'obj_def_interaction_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Object definition interaction 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'tincan_statement'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_statement'; DECLARE @ColumnName SYSNAME set @ColumnName = N'obj_def_correct_res_pattern'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Object definition correct response pattern';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 ('20160811-js-1', 'janice.sung', 'nschema/122.changeLog.xml', GETDATE(), 621, '8:73666ddcf291dc3d743a1658a6fe1df7', 'addColumn tableName=tincan_statement', '', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160811-js-2::janice.sung
-- [PD-5136] Tin Can API: Interaction Activities
CREATE TABLE tincan_context_activity (id nvarchar(85) NOT NULL, statement_id char(36) NOT NULL, context_type char(1) NOT NULL, activity_id nvarchar(2000) NOT NULL, CONSTRAINT PK_TINCAN_CONTEXT_ACTIVITY PRIMARY KEY (id))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'tincan_context_activity'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.tincan_context_activity';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table store the Tincan context activities.';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'tincan_context_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_context_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique 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'tincan_context_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_context_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'statement_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Id of the statement';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'tincan_context_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_context_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'context_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Context Type [P-Parent/G-Grouping/C-Category/O-Other]';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'tincan_context_activity'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.tincan_context_activity'; DECLARE @ColumnName SYSNAME set @ColumnName = N'activity_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Context Activity 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 tincan_context_activity ADD CONSTRAINT FK_TINCAN_CONTEXT_STATEMENTID FOREIGN KEY (statement_id) REFERENCES tincan_statement (id)
GO

CREATE NONCLUSTERED INDEX NUK_TINCAN_CONTEXT_STATEID ON tincan_context_activity(statement_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160811-js-2', 'janice.sung', 'nschema/122.changeLog.xml', GETDATE(), 622, '8:7f3b08ea6d20911690130dfbb9de1ea4', 'createTable tableName=tincan_context_activity; addForeignKeyConstraint baseTableName=tincan_context_activity, constraintName=FK_TINCAN_CONTEXT_STATEMENTID, referencedTableName=tincan_statement; createIndex indexName=NUK_TINCAN_CONTEXT_STATEID, tab...', '[PD-5136] Tin Can API: Interaction Activities', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160815-nl-1::nick.lee
-- [PD-4794] External Evaluation Workflow Link
CREATE NONCLUSTERED INDEX nuk_extern_mail_url ON extern_mail(url)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160815-nl-1', 'nick.lee', 'nschema/122.changeLog.xml', GETDATE(), 623, '8:86289bf9eabf89bfc5fb51c56f5c2491', 'createIndex indexName=nuk_extern_mail_url, tableName=extern_mail', '[PD-4794] External Evaluation Workflow Link', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160822-gg-1::gary.gan
-- [PD-4129] Default Preset Filter
ALTER TABLE filter_templates ADD is_default char(1) CONSTRAINT DF_filter_templates_is_default DEFAULT 'N' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'filter_templates'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.filter_templates'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_default'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'If this filter template is default';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 ('20160822-gg-1', 'gary.gan', 'nschema/122.changeLog.xml', GETDATE(), 624, '8:b10bf30a23eb29398f767a1db2a1adc9', 'addColumn tableName=filter_templates', '[PD-4129] Default Preset Filter', 'EXECUTED', NULL, NULL, '4.8.0', '1179394455')
GO

-- Changeset nschema/122.changeLog.xml::20160808-fr-1::francis.reforma
-- Support the functionality self-award certification
ALTER TABLE certusers ADD self_award_certificate char(1) CONSTRAINT DF_certusers_self_award_certificate DEFAULT 'N'
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'self_award_certificate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'flag if the certificate awarded is self awarded';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 certusers_selfaward (id nvarchar(85) NOT NULL, user_id nvarchar(85) NOT NULL, cert_id numeric(8, 0) NOT NULL, status char(1) NOT NULL, comments nvarchar(255), date_achieved datetime NOT NULL, expire_date datetime, attachment_id nvarchar(85), cert_code nvarchar(85) NOT NULL, cert_title nvarchar(4000) NOT NULL, approver_name nvarchar(85), last_updated datetime, last_updater_uid nvarchar(85), updater_legal_name nvarchar(250), update_meaning_text nvarchar(500))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers_selfaward'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.certusers_selfaward';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Stores the records of the self award certificates';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'record 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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'user_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'owner of the certificate';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'cert_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'certificate 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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'status'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'status of the certificate(0 - Open for Editing, 1 - Pending Approval, 2 - Approved, 3 - Approval Denied, 4 - Cancelled)';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'comments'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'comment for the self-award certificate';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'date_achieved'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'certificate award date';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'expire_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'expiration date of the self-award certificate or computed date from certificate';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'attachment_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'attachment 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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'cert_code'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'code of the certificate';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'cert_title'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'title of the certificate';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'approver_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'approver of the certificate';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'recent date the self-award certificate is 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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updater_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'id of the user with the recent update';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'updater_legal_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'legal name of the user with the recent update';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'certusers_selfaward'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_selfaward'; DECLARE @ColumnName SYSNAME set @ColumnName = N'update_meaning_text'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'update 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.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 certusers_selfaward ADD CONSTRAINT PK_CERTUSERS_AWARD PRIMARY KEY (id)
GO

CREATE NONCLUSTERED INDEX NUK_CERTUSERS_AWARD_USER_ID ON certusers_selfaward(user_id)
GO

CREATE NONCLUSTERED INDEX NUK_CERTUSERS_AWARD_ID_STATUS ON certusers_selfaward(user_id, status)
GO

