DROP TABLE question_attribute
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150721-kc-1', 'kelvin.chan', 'nschema/1111.changeLog.xml', GETDATE(), 425, '8:252739bd4327800350c2cc0849d9b818', 'dropTable tableName=question_attribute', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150721-kc-2::kelvin.chan
-- [DEV 8523] New table for question attribute
CREATE TABLE question_attribute (id nvarchar(85) NOT NULL, question_id nvarchar(85) NOT NULL, attribute_id nvarchar(15) NOT NULL, attribute_value_data nvarchar(2000), last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

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

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

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

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

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

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

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

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

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

ALTER TABLE question_attribute ADD CONSTRAINT PK_QUESTION_ATTR PRIMARY KEY (id)
GO

ALTER TABLE question_attribute ADD CONSTRAINT UK_QUESTION_ATTR UNIQUE (attribute_id, question_id)
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150721-kc-2', 'kelvin.chan', 'nschema/1111.changeLog.xml', GETDATE(), 426, '8:9951476de5329c15f4751de82b45bdfa', 'createTable tableName=question_attribute; addPrimaryKey constraintName=PK_QUESTION_ATTR, tableName=question_attribute; addUniqueConstraint constraintName=UK_QUESTION_ATTR, tableName=question_attribute; addForeignKeyConstraint baseTableName=questio...', '[DEV 8523] New table for question attribute', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150722-sn-1::sam.ng
-- [DEV 8881] New table for question_bundle
CREATE TABLE question_bundle (bundle_id nvarchar(85) NOT NULL, questionid nvarchar(85) NOT NULL, latest_version_bundle_id nvarchar(85) NOT NULL, version int NOT NULL, last_updated datetime, updater_legal_name nvarchar(250), update_meaning_text nvarchar(500), last_updater_uid nvarchar(85))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'question_bundle'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.question_bundle';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores details specific to question bundle, including versioning info. Conceptually, it is a subclass of language_bundle.';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.name = N'MS_Description' AND ep.minor_id=0); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName; END
GO

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

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'question_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'latest_version_bundle_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of question bundle for the latest version';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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_bundle'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.question_bundle'; DECLARE @ColumnName SYSNAME set @ColumnName = N'version'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Version number of this question';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

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

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

ALTER TABLE question_bundle ADD CONSTRAINT PK_QUESTION_BUNDLE PRIMARY KEY (bundle_id)
GO

ALTER TABLE question_bundle ADD CONSTRAINT FK_BUNDLE_ID_QUESTION_BUNDLE FOREIGN KEY (bundle_id) REFERENCES language_bundle (bundle_id)
GO

ALTER TABLE question_bundle ADD CONSTRAINT UK_QUESTION_BUNDLE UNIQUE (questionid, latest_version_bundle_id, version)
GO

INSERT INTO question_bundle(bundle_id, questionid, latest_version_bundle_id, version)
        	SELECT b.bundle_id, 
        	       case when q.archivereferenceid is null then q.questionid else q.archivereferenceid end,
        	       case when q.archivereferenceid is null then b.bundle_id else latest_b.bundle_id end,
        	       q.version 
        	FROM question q 
        	JOIN language_bundle b ON q.questionid = b.object_id and b.object_type = 'QN'
        	LEFT JOIN language_bundle latest_b ON q.archivereferenceid = latest_b.object_id and latest_b.object_type = 'QN' and b.locale = latest_b.locale
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150722-sn-1', 'sam.ng', 'nschema/1111.changeLog.xml', GETDATE(), 427, '8:c90448d1bd44c2be94ba39afba98cdb7', 'createTable tableName=question_bundle; addPrimaryKey constraintName=PK_QUESTION_BUNDLE, tableName=question_bundle; addForeignKeyConstraint baseTableName=question_bundle, constraintName=FK_BUNDLE_ID_QUESTION_BUNDLE, referencedTableName=language_bun...', '[DEV 8881] New table for question_bundle', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150722-sn-2::sam.ng
-- [DEV 8881] rename testquestion.bundle_id to latest_version_bundle_id
exec sp_rename 'testquestion.bundle_id', 'latest_version_bundle_id'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150722-sn-2', 'sam.ng', 'nschema/1111.changeLog.xml', GETDATE(), 428, '8:dfad0b3f8d652d1b6e8f02f95bfebe1a', 'renameColumn newColumnName=latest_version_bundle_id, oldColumnName=bundle_id, tableName=testquestion', '[DEV 8881] rename testquestion.bundle_id to latest_version_bundle_id', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150723-ge-1::gian.espino
ALTER TABLE comp_approval_queue ADD request_type char(1)
GO

ALTER TABLE comp_approval_queue ADD current_level_expire datetime
GO

DROP VIEW comp_approval_queue_vw
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150723-ge-1', 'gian.espino', 'nschema/1111.changeLog.xml', GETDATE(), 429, '8:e48f965b06fa85931dad719b6f091138', 'addColumn tableName=comp_approval_queue; dropView viewName=comp_approval_queue_vw; createView viewName=comp_approval_queue_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150722-sn-3::sam.ng
ALTER TABLE testinstance ADD CONSTRAINT FK_BUNDLE_ID_TESTINSTANCE FOREIGN KEY (bundle_id) REFERENCES language_bundle (bundle_id)
GO

ALTER TABLE testquestion ADD CONSTRAINT FK_BUNDLE_ID_TESTQUESTION FOREIGN KEY (latest_version_bundle_id) REFERENCES language_bundle (bundle_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150722-sn-3', 'sam.ng', 'nschema/1111.changeLog.xml', GETDATE(), 430, '8:9d8e470cda96aa14ddb076fb12b15bd8', 'addForeignKeyConstraint baseTableName=testinstance, constraintName=FK_BUNDLE_ID_TESTINSTANCE, referencedTableName=language_bundle; addForeignKeyConstraint baseTableName=testquestion, constraintName=FK_BUNDLE_ID_TESTQUESTION, referencedTableName=la...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150722-dc-1::dennis.cheung
-- Add learningObject.evaluation_lang_option
ALTER TABLE learningObject ADD evaluation_lang_option nvarchar(85)
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150722-dc-1', 'dennis.cheung', 'nschema/1111.changeLog.xml', GETDATE(), 431, '8:b72f6fe88c79ca4f9b1e30563a0720c1', 'addColumn tableName=learningObject', 'Add learningObject.evaluation_lang_option', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150722-dc-2::dennis.cheung
-- Add coursetest.lang_option
ALTER TABLE coursetest ADD lang_option nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'coursetest'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.coursetest'; DECLARE @ColumnName SYSNAME set @ColumnName = N'lang_option'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Language option for Assigned Exam (null-> Auto, *SELECT*, or exam.bundle_id)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150722-dc-2', 'dennis.cheung', 'nschema/1111.changeLog.xml', GETDATE(), 432, '8:7e6ba12f300f72b866a065bdbeafa068', 'addColumn tableName=coursetest', 'Add coursetest.lang_option', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150722-dc-3::dennis.cheung
-- Add certifications.test_lang_option
ALTER TABLE certifications ADD test_lang_option nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certifications'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certifications'; DECLARE @ColumnName SYSNAME set @ColumnName = N'test_lang_option'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Language option for Certification Exam (null-> Auto, *SELECT*, or exam.bundle_id)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150722-dc-3', 'dennis.cheung', 'nschema/1111.changeLog.xml', GETDATE(), 433, '8:2fead1a38724d6d20aba6380dbd8f4a2', 'addColumn tableName=certifications', 'Add certifications.test_lang_option', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150730-dc-1::dennis.cheung
-- Add learningObject.supervisorAssessment_lang_opt
ALTER TABLE learningObject ADD supervisorAssessment_lang_opt nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'learningObject'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.learningObject'; DECLARE @ColumnName SYSNAME set @ColumnName = N'supervisorAssessment_lang_opt'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Language option for SupervisorAssessment Exam (null-> Auto, *SELECT*, or exam.bundle_id)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150730-dc-1', 'dennis.cheung', 'nschema/1111.changeLog.xml', GETDATE(), 434, '8:f03704e0d4d9ae466b0ec176e7e92ff4', 'addColumn tableName=learningObject', 'Add learningObject.supervisorAssessment_lang_opt', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150731-ca-1::chris.au
-- Add article.test_lang_option
ALTER TABLE article ADD test_lang_option nvarchar(85)
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150731-ca-1', 'chris.au', 'nschema/1111.changeLog.xml', GETDATE(), 435, '8:7b68ac221f30637852fcbf2d8e4cd67d', 'addColumn tableName=article', 'Add article.test_lang_option', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150805-tw-1::tomas.weigel
CREATE NONCLUSTERED INDEX nuk_res_string_bundle_id ON resource_string(bundle_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150805-tw-1', 'tomas.weigel', 'nschema/1111.changeLog.xml', GETDATE(), 436, '8:65655113aac8fae16cfe58d8d726082c', 'createIndex indexName=nuk_res_string_bundle_id, tableName=resource_string', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150727-lt-1::lenny.tang
CREATE TABLE user_account_approval_tasks (task_id nvarchar(15) NOT NULL, userid nvarchar(15) NOT NULL, escalated nvarchar(1) CONSTRAINT DF_user_account_approval_tasks_escalated DEFAULT 'N' NOT NULL, approvalstatus nvarchar(1) NOT NULL, receiveddate datetime, completiondate datetime CONSTRAINT DF_user_account_approval_tasks_completiondate DEFAULT null, completedbyuserid nvarchar(15) CONSTRAINT DF_user_account_approval_tasks_completedbyuserid DEFAULT null, remindersent nvarchar(1) CONSTRAINT DF_user_account_approval_tasks_remindersent DEFAULT 'N' NOT NULL)
GO

ALTER TABLE user_account_approval_tasks ADD CONSTRAINT PK_ACCOUNT_APV_TASK PRIMARY KEY (task_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150727-lt-1', 'lenny.tang', 'nschema/1111.changeLog.xml', GETDATE(), 437, '8:c8e83c77d4bfd326078fa4550c127a23', 'createTable tableName=user_account_approval_tasks; addPrimaryKey constraintName=PK_ACCOUNT_APV_TASK, tableName=user_account_approval_tasks', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150803-lt-1::lenny.tang
CREATE TABLE user_org_job_profile_history (change_id nvarchar(15) NOT NULL, user_id nvarchar(15) NOT NULL, start_date datetime CONSTRAINT DF_user_org_job_profile_history_start_date DEFAULT null, value nvarchar(85) CONSTRAINT DF_user_org_job_profile_history_value DEFAULT null, type nvarchar(2) CONSTRAINT DF_user_org_job_profile_history_type DEFAULT null)
GO

ALTER TABLE user_org_job_profile_history ADD CONSTRAINT PK_USER_PROFILEHIST PRIMARY KEY (change_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150803-lt-1', 'lenny.tang', 'nschema/1111.changeLog.xml', GETDATE(), 438, '8:1e4827dced3b0d2cf897efff952085cf', 'createTable tableName=user_org_job_profile_history; addPrimaryKey constraintName=PK_USER_PROFILEHIST, tableName=user_org_job_profile_history', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150807-dc-1::dennis.cheung
CREATE NONCLUSTERED INDEX NUK_COURSE_REVSISON_1 ON course_revision(bundle_id)
GO

CREATE NONCLUSTERED INDEX NUK_CSCHEDULE_2 ON cschedule(bundle_id)
GO

CREATE NONCLUSTERED INDEX NUK_SESSIONDETAIL_2 ON sessiondetail(bundle_id)
GO

CREATE NONCLUSTERED INDEX NUK_TESTINSTANCE_1 ON testinstance(bundle_id)
GO

CREATE NONCLUSTERED INDEX NUK_TESTQUESTION_1 ON testquestion(latest_version_bundle_id)
GO

CREATE NONCLUSTERED INDEX NUK_TRAN_BUNDLE_ID ON transcript(bundle_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150807-dc-1', 'dennis.cheung', 'nschema/1111.changeLog.xml', GETDATE(), 439, '8:4e785b125c9b0de1b468621040ca46a3', 'createIndex indexName=NUK_COURSE_REVSISON_1, tableName=course_revision; createIndex indexName=NUK_CSCHEDULE_2, tableName=cschedule; createIndex indexName=NUK_SESSIONDETAIL_2, tableName=sessiondetail; createIndex indexName=NUK_TESTINSTANCE_1, table...', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150817-sn-1::sam.ng
-- [PERF 8928] New index for transcript table, used for recent activity widget
DROP INDEX NUK_TRAN_FINISHDATE ON transcript
GO

CREATE NONCLUSTERED INDEX NUK_TRAN_STAT_FINISHDATE ON transcript(studentstatus, finishdate)
GO

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

-- Changeset nschema/1111.changeLog.xml::20150820-yt-1::yan.tung
-- patch DATABASECHANGELOG comments
update DATABASECHANGELOG set COMMENTS = '' where ID in ('20150326-tz-1','20150430-bc-1','20150526-lt-1','20150527-bc-1')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150820-yt-1', 'yan.tung', 'nschema/1111.changeLog.xml', GETDATE(), 441, '8:65735ce151a7c3e0bcb6b43ab1a8728b', 'sql', 'patch DATABASECHANGELOG comments', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1111.changeLog.xml::20150820-yt-2::yan.tung
-- patch oracle table comment
INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150820-yt-2', 'yan.tung', 'nschema/1111.changeLog.xml', GETDATE(), 442, '8:cb93bbb4714b30f27a1deca723fc10ba', 'sql', 'patch oracle table comment', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::08Aug2015-sn-1::sam.ng
UPDATE ekpversion SET dblevel = 1112, splevel = 0
GO

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

-- Changeset nschema/1112.changeLog.xml::20150810-pc-1::pierre.collignon
ALTER TABLE filter_templates_criteria ALTER COLUMN filter_criteria_type nvarchar(15)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150810-pc-1', 'pierre.collignon', 'nschema/1112.changeLog.xml', GETDATE(), 444, '8:c212b89ac8a572205ddeb76de9b34f58', 'modifyDataType columnName=filter_criteria_type, tableName=filter_templates_criteria', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150811-kc-1::kelvin.chan
-- [DEV 8637] DB level cache for progress status
CREATE TABLE current_progress_status (id nvarchar(85) NOT NULL, userid nvarchar(85) NOT NULL, transcriptid nvarchar(85) NOT NULL, progress_status nvarchar(1) NOT NULL, duedate datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'current_progress_status'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.current_progress_status';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the current progress status of transcripts (db lvl cache)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'userid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'userid for transcript';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'transcriptid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'transcript id linked for progress status';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'progress_status'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'progress status for transcript and sub-transcripts';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'duedate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'closest due date of program/module';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 current_progress_status ADD CONSTRAINT PK_CURRENT_PROGRESS_STATUS PRIMARY KEY (id)
GO

ALTER TABLE current_progress_status ADD CONSTRAINT UK_CURRENT_PROGRESS_STATUS UNIQUE (transcriptid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150811-kc-1', 'kelvin.chan', 'nschema/1112.changeLog.xml', GETDATE(), 445, '8:0d8272c984af7661b0109c2d99d930dc', 'createTable tableName=current_progress_status; addPrimaryKey constraintName=PK_CURRENT_PROGRESS_STATUS, tableName=current_progress_status; addUniqueConstraint constraintName=UK_CURRENT_PROGRESS_STATUS, tableName=current_progress_status', '[DEV 8637] DB level cache for progress status', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150813-js-1::janice.sung
-- Extend the column size of objectid to include permissions for Module/Program Sessions
ALTER TABLE permissions ALTER COLUMN objectid NVARCHAR(255) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150813-js-1', 'janice.sung', 'nschema/1112.changeLog.xml', GETDATE(), 446, '8:973b9ea0eca50f24858dce63e124fab0', 'sql; sql; sql; sql', 'Extend the column size of objectid to include permissions for Module/Program Sessions', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150813-dc-1::dennis.cheung
-- [DEV 8632] New Training Progress Widget
ALTER TABLE template_widget ADD general_config nvarchar(4000)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'template_widget'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.template_widget'; DECLARE @ColumnName SYSNAME set @ColumnName = N'general_config'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'JSON respresentation of configuration for widget';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150813-dc-1', 'dennis.cheung', 'nschema/1112.changeLog.xml', GETDATE(), 447, '8:477f89703e831443889662fe7a095854', 'addColumn tableName=template_widget', '[DEV 8632] New Training Progress Widget', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150818-ca-1::chris.au
-- [DEV 9330] add column to sessiondetail to store configuration
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 submodule_fail_action char(1)
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'submodule_fail_action'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Action on mandatory component fail(N - do Nothing; F - Fail this program; R - allow learner re-enroll within context of this prgoram)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150818-ca-1', 'chris.au', 'nschema/1112.changeLog.xml', GETDATE(), 448, '8:3bd2e89275390b4489c8d10dcc59a919', 'sql; sql; sql; addColumn tableName=sessiondetail', '[DEV 9330] add column to sessiondetail to store configuration', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150818-ca-2::chris.au
-- Data migration from system configuration
update sessiondetail
            set submodule_fail_action = (select case when
            config_value like 'true' then 'F' else 'N' end as action from
            logical_domain_config
            where config_key =
            'general.failProgramIfRequiredComponentFails')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150818-ca-2', 'chris.au', 'nschema/1112.changeLog.xml', GETDATE(), 449, '8:3aad9e3c50b5ee5bfd2ca754ed84f279', 'sql', 'Data migration from system configuration', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150820-mw-1::martin.wang
-- [Dev#9104] Add field to indicate whether apply enrollment policy for session selection
ALTER TABLE learningObject ADD use_policy_to_select_session char(1) CONSTRAINT DF_learningObject_use_policy_to_select_session DEFAULT 'N' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150820-mw-1', 'martin.wang', 'nschema/1112.changeLog.xml', GETDATE(), 450, '8:f96c9adea1bb6aa8e1406c74d6498db4', 'addColumn tableName=learningObject', '[Dev#9104] Add field to indicate whether apply enrollment policy for session selection', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150821-dc-1::dennis.cheung
DELETE FROM current_progress_status
GO

ALTER TABLE current_progress_status ADD sub_modules nvarchar(4000)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'sub_modules'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'pairs of required sub-modules learning id and 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 ('20150821-dc-1', 'dennis.cheung', 'nschema/1112.changeLog.xml', GETDATE(), 451, '8:acd9dd6ce862ad82f9af0fdd1afd0471', 'delete tableName=current_progress_status; addColumn tableName=current_progress_status', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150828-sn-1::sam.ng
-- New table for overall compliance analysis result
CREATE TABLE comp_analysis_history_overall (analysis_id nvarchar(100) NOT NULL, analysis_user_id nvarchar(85) NOT NULL, analysis_date datetime NOT NULL, compliance_numerator int, compliance_denominator int, compliance_percentage float(53), training_progress_numerator int, training_progress_denominator int, training_progress_percentage float(53))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'overall compliance analysis 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.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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'analysis_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of analysis (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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'analysis_user_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'user who submitted this analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'analysis_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that this analysis was started';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'compliance_numerator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of participants who have comlpeted all selected modules in the overall analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'compliance_denominator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'total number of participants among all groups in the overall analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'compliance_percentage'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'percentage of participants who have completed all selected modules in the overall analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'training_progress_numerator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of successful module completions in the overall analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'training_progress_denominator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of possible module completions in the overall analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'training_progress_percentage'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'percentage of successful module completions in the overall analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 comp_analysis_history_overall ADD CONSTRAINT PK_OVERALL_COMP_ANALYSIS PRIMARY KEY (analysis_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150828-sn-1', 'sam.ng', 'nschema/1112.changeLog.xml', GETDATE(), 452, '8:a9593d15c91e0300f01b7201ea3a629d', 'createTable tableName=comp_analysis_history_overall; addPrimaryKey constraintName=PK_OVERALL_COMP_ANALYSIS, tableName=comp_analysis_history_overall', 'New table for overall compliance analysis result', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150828-sn-2::sam.ng
-- New table for compliance analysis result at group level
CREATE TABLE comp_analysis_history_groups (analysis_id nvarchar(100) NOT NULL, group_id nvarchar(85) NOT NULL, group_name nvarchar (max), group_name_short nvarchar(4000), compliance_numerator int, compliance_denominator int, compliance_percentage float(53), training_progress_numerator int, training_progress_denominator int, training_progress_percentage float(53), own_compliance_numerator int, own_compliance_denominator int, own_compliance_percentage float(53), own_progress_numerator int, own_progress_denominator int, own_progress_percentage float(53))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'compliance analysis result at group level';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'analysis_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'group_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of group';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'group_name_short'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Group name in short for sorting';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'compliance_numerator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of participants who have comlpeted all selected modules within this group (including subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'compliance_denominator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'total number of participants within this group (including subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'compliance_percentage'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'percentage of participants who have completed all selected modules within this group (including subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'training_progress_numerator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of successful module completions within this group (including subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'training_progress_denominator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of possible module completions within this group (including subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'training_progress_percentage'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'percentage of successful module completions within this group (including subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'own_compliance_numerator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of participants who have comlpeted all selected modules within this group (excluding subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'own_compliance_denominator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'total number of participants within this group (excluding subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'own_compliance_percentage'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'percentage of participants who have completed all selected modules within this group (excluding subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'own_progress_numerator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of successful module completions within this group (excluding subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'own_progress_denominator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of possible module completions within this group (excluding subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'own_progress_percentage'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'percentage of successful module completions within this group (excluding subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 comp_analysis_history_groups ADD CONSTRAINT PK_GROUPS_COMP_ANALYSIS PRIMARY KEY (analysis_id, group_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150828-sn-2', 'sam.ng', 'nschema/1112.changeLog.xml', GETDATE(), 453, '8:c539bc2ff065256b2e8f9f47c3884e37', 'createTable tableName=comp_analysis_history_groups; addPrimaryKey constraintName=PK_GROUPS_COMP_ANALYSIS, tableName=comp_analysis_history_groups', 'New table for compliance analysis result at group level', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150828-sn-3::sam.ng
-- New table for compliance analysis result at user assignment level
CREATE TABLE comp_analysis_history_users (analysis_id nvarchar(100) NOT NULL, group_id nvarchar(85) NOT NULL, group_name nvarchar (max), assignment_id nvarchar(85) NOT NULL, display_name nvarchar(4000), training_progress_numerator int, training_progress_denominator int, training_progress_percentage float(53), optional_numerator int, optional_denominator int, last_updated datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'compliance analysis result at user assignment level';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'analysis_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'group_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of group to which the participant belongs';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'group_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Name of group to which the participant belongs';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'assignment_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of participant, which is a user assignment';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'display_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Name of participant for display';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'training_progress_numerator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of successful mandatory module completions by the participant';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'training_progress_denominator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of possible mandatory module completions by the participant';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'training_progress_percentage'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'percentage of successful mandatory module completions by the participant';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'optional_numerator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of successful optional module completions by the participant';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'optional_denominator'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of possible optional module completions by the participant';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that all records of the participant are lastly 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

ALTER TABLE comp_analysis_history_users ADD CONSTRAINT PK_USERS_COMP_ANALYSIS PRIMARY KEY (analysis_id, group_id, assignment_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150828-sn-3', 'sam.ng', 'nschema/1112.changeLog.xml', GETDATE(), 454, '8:c47029998a1902a1657094de4d6fe49d', 'createTable tableName=comp_analysis_history_users; addPrimaryKey constraintName=PK_USERS_COMP_ANALYSIS, tableName=comp_analysis_history_users', 'New table for compliance analysis result at user assignment level', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150828-sn-4::sam.ng
-- New table for compliance analysis result at record level
CREATE TABLE comp_analysis_history_records (analysis_id nvarchar(100) NOT NULL, assignment_id nvarchar(85) NOT NULL, record_type nvarchar(15) NOT NULL, module_id nvarchar(85) NOT NULL, session_id nvarchar(15) NOT NULL, parent_id nvarchar(100) NOT NULL, learning_type nvarchar(1), display_name nvarchar(4000), transcript_id nvarchar(85), transcript_status int, is_mandatory char(1), is_compliant char(1), enrolled_on datetime, completed_on datetime, deadline datetime, last_updated datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'compliance analysis result at record level';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'analysis_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of analysis';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'assignment_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'ID of user assignment to which the training record belongs';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'record_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'record type (toplevel, submodule or substitute)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'module_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Module 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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'session_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Session ID (for program session type at top level only)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'parent_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'session_id + module_id of its parent (or itself if toplevel)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'learning_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learning 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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'display_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Name of this record for display';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'transcript_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Transcript ID, if any';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'transcript_status'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Transcript status, if any';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_mandatory'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'module is mandatory or not (for submodule only)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_compliant'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'record is compliant 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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'enrolled_on'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that this record is enrolled';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'completed_on'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that this record is completed';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'deadline'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Module deadline';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_records'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_records'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Date and time that this record is lastly 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

ALTER TABLE comp_analysis_history_records ADD CONSTRAINT PK_RECORDS_COMP_ANALYSIS PRIMARY KEY (analysis_id, assignment_id, record_type, module_id, session_id, parent_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150828-sn-4', 'sam.ng', 'nschema/1112.changeLog.xml', GETDATE(), 455, '8:6ad3961d967f11f9b0dd42331cfd30c0', 'createTable tableName=comp_analysis_history_records; addPrimaryKey constraintName=PK_RECORDS_COMP_ANALYSIS, tableName=comp_analysis_history_records', 'New table for compliance analysis result at record level', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150831-js-1::janice.sung
-- [DEV#9382] New table for User Attribute display areas configurations
CREATE TABLE user_attribute_config (user_attribute_number int NOT NULL, display_areas nvarchar(15))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'user_attribute_config'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.user_attribute_config';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the user attribute display areas configurations.';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.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'user_attribute_config'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.user_attribute_config'; DECLARE @ColumnName SYSNAME set @ColumnName = N'user_attribute_number'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'User Attribute number (1 to 8)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'user_attribute_config'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.user_attribute_config'; DECLARE @ColumnName SYSNAME set @ColumnName = N'display_areas'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Display areas that the user attributes are applied';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 user_attribute_config ADD CONSTRAINT PK_USER_ATTRIBUTE_CONFIG PRIMARY KEY (user_attribute_number)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150831-js-1', 'janice.sung', 'nschema/1112.changeLog.xml', GETDATE(), 456, '8:16c26ef1e5f556d5fa261cd87b4f8bfd', 'createTable tableName=user_attribute_config; addPrimaryKey constraintName=PK_USER_ATTRIBUTE_CONFIG, tableName=user_attribute_config', '[DEV#9382] New table for User Attribute display areas configurations', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150831-js-2::janice.sung
-- [DEV#9382] Data migration for User Attribute display areas - User Editor Search Criteria
INSERT INTO user_attribute_config (user_attribute_number, display_areas) VALUES (1, null)
GO

INSERT INTO user_attribute_config (user_attribute_number, display_areas) VALUES (2, null)
GO

INSERT INTO user_attribute_config (user_attribute_number, display_areas) VALUES (3, null)
GO

INSERT INTO user_attribute_config (user_attribute_number, display_areas) VALUES (4, null)
GO

INSERT INTO user_attribute_config (user_attribute_number, display_areas) VALUES (5, null)
GO

INSERT INTO user_attribute_config (user_attribute_number, display_areas) VALUES (6, null)
GO

INSERT INTO user_attribute_config (user_attribute_number, display_areas) VALUES (7, null)
GO

INSERT INTO user_attribute_config (user_attribute_number, display_areas) VALUES (8, null)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150831-js-2', 'janice.sung', 'nschema/1112.changeLog.xml', GETDATE(), 457, '8:c3851e85d03c9fb7436787ffc80dda54', 'sql', '[DEV#9382] Data migration for User Attribute display areas - User Editor Search Criteria', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150831-js-3::janice.sung
-- [DEV#9382] New column for User Attribute Extension display areas configurations
ALTER TABLE user_extension_label ADD display_areas nvarchar(15)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'user_extension_label'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.user_extension_label'; DECLARE @ColumnName SYSNAME set @ColumnName = N'display_areas'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Display areas that the user attribute extensions are applied';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150831-js-3', 'janice.sung', 'nschema/1112.changeLog.xml', GETDATE(), 458, '8:36fbea4dac1143d931fcab4971f2c982', 'addColumn tableName=user_extension_label', '[DEV#9382] New column for User Attribute Extension display areas configurations', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150908-sc-1::starry.chan
ALTER TABLE language_custom_string ALTER COLUMN string_value nvarchar(4000)
GO

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

-- Changeset nschema/1112.changeLog.xml::20150909-wc-1::wilson.choi
ALTER TABLE current_progress_status ADD substitute_modules nvarchar (max)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'substitute_modules'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Json List of substitute modules transcript id, null on no substitute';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150909-wc-1', 'wilson.choi', 'nschema/1112.changeLog.xml', GETDATE(), 460, '8:cf74119a9d592d0ba2cae2b95ac88e18', 'addColumn tableName=current_progress_status', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150910-vy-1::victor.yan
-- ZD#29845(ZD#29646 System extremely slow/ZD#29649 System Crashing)
CREATE NONCLUSTERED INDEX NUK_TESTINSTANCE_TESTID ON testinstance(testid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150910-vy-1', 'victor.yan', 'nschema/1112.changeLog.xml', GETDATE(), 461, '8:fd05b9af5f9dbd0d6c47386524ab6215', 'createIndex indexName=NUK_TESTINSTANCE_TESTID, tableName=testinstance', 'ZD#29845(ZD#29646 System extremely slow/ZD#29649 System Crashing)', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150910-vy-2::victor.yan
-- ZD#29845(ZD#29646 System extremely slow/ZD#29649 System Crashing)
CREATE NONCLUSTERED INDEX NUK_TESTINSTANCE_USERID ON testinstance(userid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150910-vy-2', 'victor.yan', 'nschema/1112.changeLog.xml', GETDATE(), 462, '8:88a92d3d68022d29768c83d349a5bc22', 'createIndex indexName=NUK_TESTINSTANCE_USERID, tableName=testinstance', 'ZD#29845(ZD#29646 System extremely slow/ZD#29649 System Crashing)', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150910-vy-3::victor.yan
-- ZD#29845(ZD#29646 System extremely slow/ZD#29649 System Crashing)
CREATE NONCLUSTERED INDEX NUK_USERSASSIGNMENT_METAID ON users_assignment(user_meta_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150910-vy-3', 'victor.yan', 'nschema/1112.changeLog.xml', GETDATE(), 463, '8:f2bb41174233131f58f8c8685990c13f', 'createIndex indexName=NUK_USERSASSIGNMENT_METAID, tableName=users_assignment', 'ZD#29845(ZD#29646 System extremely slow/ZD#29649 System Crashing)', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150910-kh-1::kit.ho
ALTER TABLE comp_analysis_history_overall ADD input_parameters nvarchar (max)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'input_parameters'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Add column for the user filtering input parameters';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150910-kh-1', 'kit.ho', 'nschema/1112.changeLog.xml', GETDATE(), 464, '8:69f62961e8a6781845119aeab76307b2', 'addColumn tableName=comp_analysis_history_overall', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150911-sn-1::sam.ng
ALTER TABLE comp_analysis_history_users ADD is_compliant char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'comp_analysis_history_users'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_users'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_compliant'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'user assignment is compliant 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150911-sn-1', 'sam.ng', 'nschema/1112.changeLog.xml', GETDATE(), 465, '8:4f6642cf62dc536232fe7e760e4f38bb', 'addColumn tableName=comp_analysis_history_users', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150910-sh-1::sergio.hui
-- New column for User Group
ALTER TABLE user_group ADD group_description nvarchar (max)
GO

ALTER TABLE user_group ADD group_attribute nvarchar(255)
GO

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'user_group'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.user_group'; DECLARE @ColumnName SYSNAME set @ColumnName = N'group_attribute'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'User group attribute. To be used in search filter.';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150910-sh-1', 'sergio.hui', 'nschema/1112.changeLog.xml', GETDATE(), 466, '8:1917c70c64b6aa99f821c6bc8e5731a8', 'addColumn tableName=user_group', 'New column for User Group', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150911-tw-1::tomas.weigel
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 ('20150911-tw-1', 'tomas.weigel', 'nschema/1112.changeLog.xml', GETDATE(), 467, '8:1f0ebbed93b0311ce49c71e77f0127dc', 'dropView viewName=cschedule_vw; createView viewName=cschedule_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150916-sn-1::sam.ng
DECLARE @default sysname
			SELECT @default = name FROM sys.objects WHERE type = 'UQ' AND parent_object_id=object_id('learningObject') 
			EXEC ('ALTER TABLE [learningObject] DROP CONSTRAINT ' + @default)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150916-sn-1', 'sam.ng', 'nschema/1112.changeLog.xml', GETDATE(), 468, '8:0c535b5ec48f0e2713938ce0b18e2731', 'sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150916-sg-1::shravan.gurrala
-- Display id for learning object that is provided by user should not be null and should also be unique just as learning id
ALTER TABLE learningObject ALTER COLUMN display_learning_id nvarchar(85) NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150916-sg-1', 'shravan.gurrala', 'nschema/1112.changeLog.xml', GETDATE(), 469, '8:60353deb98425fadef16bb451cc22937', 'addNotNullConstraint columnName=display_learning_id, tableName=learningObject', 'Display id for learning object that is provided by user should not be null and should also be unique just as learning id', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150916-sn-2::sam.ng
ALTER TABLE learningObject ADD UNIQUE (display_learning_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150916-sn-2', 'sam.ng', 'nschema/1112.changeLog.xml', GETDATE(), 470, '8:2ad27fb61921f5b1d898328cc8bd362c', 'addUniqueConstraint tableName=learningObject', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150917-sn-1::sam.ng
ALTER TABLE comp_analysis_history_groups ADD participant_total int
GO

ALTER TABLE comp_analysis_history_groups ADD participant_na int
GO

ALTER TABLE comp_analysis_history_groups ADD own_participant_total int
GO

ALTER TABLE comp_analysis_history_groups ADD own_participant_na int
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'participant_total'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'total number of participants within this group (including subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'participant_na'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of participants with no target training within this group (including subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'own_participant_total'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'total number of participants within this group (excluding subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_groups'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_groups'; DECLARE @ColumnName SYSNAME set @ColumnName = N'own_participant_na'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of participants with no target training within this group (excluding subgroups)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 comp_analysis_history_overall ADD participant_total int
GO

ALTER TABLE comp_analysis_history_overall ADD participant_na int
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'participant_total'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'total number of participants';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'comp_analysis_history_overall'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.comp_analysis_history_overall'; DECLARE @ColumnName SYSNAME set @ColumnName = N'participant_na'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'number of participants with no target training';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150917-sn-1', 'sam.ng', 'nschema/1112.changeLog.xml', GETDATE(), 471, '8:6759338f4875a9b9e6f7ab63fb87d0b6', 'addColumn tableName=comp_analysis_history_groups; addColumn tableName=comp_analysis_history_overall', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1112.changeLog.xml::20150923-wc-1::wilson.choi
-- [DEV 8637] DB level cache for progress status
DROP TABLE current_progress_status
GO

CREATE TABLE current_progress_status (id nvarchar(85) NOT NULL, userid nvarchar(85) NOT NULL, transcriptid nvarchar(85) NOT NULL, progress_status nvarchar(1) NOT NULL, partial_duedate datetime, whole_duedate datetime, sub_modules nvarchar(4000), substitute_modules nvarchar (max))
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'current_progress_status'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.current_progress_status';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'This table stores the current progress status of transcripts (db lvl cache)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'userid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'userid for transcript';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'transcriptid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'transcript id linked for progress status';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'progress_status'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'progress status for transcript and sub-transcripts';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'partial_duedate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The earilest amongst self and all descendent module deadlines';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'whole_duedate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'The earilest of all ancestor transcript deadlines';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'sub_modules'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'pairs of required sub-modules learning id and 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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'current_progress_status'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.current_progress_status'; DECLARE @ColumnName SYSNAME set @ColumnName = N'substitute_modules'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Json List of substitute modules transcript id, null on no substitute';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 current_progress_status ADD CONSTRAINT PK_CURRENT_PROGRESS_STATUS PRIMARY KEY (id)
GO

ALTER TABLE current_progress_status ADD CONSTRAINT UK_CURRENT_PROGRESS_STATUS UNIQUE (transcriptid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150923-wc-1', 'wilson.choi', 'nschema/1112.changeLog.xml', GETDATE(), 472, '8:99d406ab8394fe7d622d88a1b973e865', 'dropTable tableName=current_progress_status; createTable tableName=current_progress_status; addPrimaryKey constraintName=PK_CURRENT_PROGRESS_STATUS, tableName=current_progress_status; addUniqueConstraint constraintName=UK_CURRENT_PROGRESS_STATUS, ...', '[DEV 8637] DB level cache for progress status', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::14Sep2015-sg-1::sam.ng
UPDATE ekpversion SET dblevel = 112, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('14Sep2015-sg-1', 'sam.ng', 'nschema/112.changeLog.xml', GETDATE(), 473, '8:57eb1f5fba5903d6f5dc461f536a073d', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150917-ca-1::chris.au
-- [DEV 9000] Specifying Fields as Optional in Appraisals
ALTER TABLE appraisal_s_dimension ADD appraisee_comments_req char(1)
GO

ALTER TABLE appraisal_s_dimension ADD appraiser_comments_req char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_dimension'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_dimension'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraisee_comments_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'appraisee comments mandatory flag';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_dimension'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_dimension'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraiser_comments_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'appraiser comments mandatory flag';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150917-ca-1', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 474, '8:e5a0cfec040220ee7806125a2d7acedc', 'addColumn tableName=appraisal_s_dimension', '[DEV 9000] Specifying Fields as Optional in Appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150917-vn-1::vincent.ni
-- [DEV 9421] Language bundle data migration for catalog
-- Language bundle creation for each catalog
-- Title column
-- Comments column
insert into language_bundle
				(bundle_id,
				object_id,
				object_type,
				locale,
				is_primary)
			select
				CAST(ROW_NUMBER() OVER (ORDER BY catalogid) AS VARCHAR) + 'CL' as rownum,
				catalogid,
				'CL',
				'*ALL*',
				'Y'
			from code_catalog_structure
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_TITLE',
				null,
				b.bundle_id,
				title,
				'CL_TITLE',
				catalogid
			from
				code_catalog_structure c
			join
				language_bundle b on c.catalogid = b.object_id and b.object_type = 'CL'
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_COMMENTS',
				null,
				b.bundle_id,
				comments,
				'CL_COMMENTS',
				catalogid
			from
				code_catalog_structure c
			join
				language_bundle b on c.catalogid = b.object_id and b.object_type = 'CL'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150917-vn-1', 'vincent.ni', 'nschema/112.changeLog.xml', GETDATE(), 475, '8:98e4cdcf3d9d03e0af353ed3042b7b59', 'sql; sql; sql; sql; sql; sql; sql; sql; sql; sql', '[DEV 9421] Language bundle data migration for catalog
Language bundle creation for each catalog
Title column
Comments column', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150918-ca-1::chris.au
-- [DEV 9000] Specifying Fields as Optional in Appraisals
ALTER TABLE appraisal_s_goal_result ADD appraisee_comments_req char(1)
GO

ALTER TABLE appraisal_s_goal_result ADD appraiser_comments_req char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_goal_result'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_goal_result'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraisee_comments_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'appraisee comments mandatory flag';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_goal_result'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_goal_result'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraiser_comments_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'appraiser comments mandatory flag';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150918-ca-1', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 476, '8:36158b5ee058e28b76eac516f13b8f65', 'addColumn tableName=appraisal_s_goal_result', '[DEV 9000] Specifying Fields as Optional in Appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150922-vn-2::vincent.ni
-- should delete with 20150917-vn-1 before release
delete from resource_string where field='CL_TITLE'
GO

delete from resource_string where field='CL_COMMENTS'
GO

delete from language_bundle where object_type='CL'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150922-vn-2', 'vincent.ni', 'nschema/112.changeLog.xml', GETDATE(), 477, '8:81c270889001e0c4463bd692afbe1715', 'sql; sql; sql', 'should delete with 20150917-vn-1 before release', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150922-vn-3::vincent.ni
-- [DEV 9421] Language bundle data migration for catalog
-- Language bundle creation for each catalog
-- Title column
-- Comments column
insert into language_bundle
				(bundle_id,
				object_id,
				object_type,
				locale,
				is_primary)
			select
				CAST(ROW_NUMBER() OVER (ORDER BY catalogid) AS VARCHAR) + 'CT' as rownum,
				catalogid,
				'CT',
				'*ALL*',
				'Y'
			from code_catalog_structure
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_TITLE',
				null,
				b.bundle_id,
				title,
				'CT_TITLE',
				catalogid
			from
				code_catalog_structure c
			join
				language_bundle b on c.catalogid = b.object_id and b.object_type = 'CT'
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_COMMENTS',
				null,
				b.bundle_id,
				comments,
				'CT_COMMENTS',
				catalogid
			from
				code_catalog_structure c
			join
				language_bundle b on c.catalogid = b.object_id and b.object_type = 'CT'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150922-vn-3', 'vincent.ni', 'nschema/112.changeLog.xml', GETDATE(), 478, '8:67fd3b1327980892b8e7ea27914c88de', 'sql; sql; sql; sql; sql; sql; sql; sql; sql; sql', '[DEV 9421] Language bundle data migration for catalog
Language bundle creation for each catalog
Title column
Comments column', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150921-ca-1::chris.au
-- [DEV 9000] Specifying Fields as Optional in Appraisals
ALTER TABLE appraisal_s_competency ADD appraisee_comments_req char(1)
GO

ALTER TABLE appraisal_s_competency ADD appraiser_comments_req char(1)
GO

ALTER TABLE appraisal_s_competency ADD display_na char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_competency'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_competency'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraisee_comments_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'appraisee comments mandatory flag';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_competency'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_competency'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraiser_comments_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'appraiser comments mandatory flag';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_competency'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_competency'; DECLARE @ColumnName SYSNAME set @ColumnName = N'display_na'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'''Y'' to show the ''NA Column'' and the N/A Rating within the legend area when performing appraisal';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150921-ca-1', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 479, '8:b9b93ea5adc475c80322107a6527c072', 'addColumn tableName=appraisal_s_competency', '[DEV 9000] Specifying Fields as Optional in Appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150923-ca-1::chris.au
-- [DEV 9000] Specifying Fields as Optional in Appraisals
ALTER TABLE appraisal_s_free_text ADD is_req char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_free_text'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_free_text'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for this 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150923-ca-1', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 480, '8:554ad25e278a9b8dc8e38805f855872f', 'addColumn tableName=appraisal_s_free_text', '[DEV 9000] Specifying Fields as Optional in Appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150923-ca-2::chris.au
-- [DEV 9000] Specifying Fields as Optional in Appraisals
ALTER TABLE appraisal_s_single_choice ADD is_req char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_single_choice'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_single_choice'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for this 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150923-ca-2', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 481, '8:022c9dde1cb9c628ba78f505fbe86679', 'addColumn tableName=appraisal_s_single_choice', '[DEV 9000] Specifying Fields as Optional in Appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150923-ca-3::chris.au
-- [DEV 9000] Specifying Fields as Optional in Appraisals
ALTER TABLE appraisal_s_future_objective ADD objective_req char(1)
GO

ALTER TABLE appraisal_s_future_objective ADD target_date_req char(1)
GO

ALTER TABLE appraisal_s_future_objective ADD how_req char(1)
GO

ALTER TABLE appraisal_s_future_objective ADD details_req char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_future_objective'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_future_objective'; DECLARE @ColumnName SYSNAME set @ColumnName = N'objective_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for objective';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_future_objective'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_future_objective'; DECLARE @ColumnName SYSNAME set @ColumnName = N'target_date_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for target 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'appraisal_s_future_objective'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_future_objective'; DECLARE @ColumnName SYSNAME set @ColumnName = N'how_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for how';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_future_objective'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_future_objective'; DECLARE @ColumnName SYSNAME set @ColumnName = N'details_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150923-ca-3', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 482, '8:b17011de1388cbf454c183efc7ec8ffa', 'addColumn tableName=appraisal_s_future_objective', '[DEV 9000] Specifying Fields as Optional in Appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150924-ca-1::chris.au
-- [DEV 9000] Specifying Fields as Optional in Appraisals
ALTER TABLE appraisal_s_achievement_rec ADD objective_req char(1)
GO

ALTER TABLE appraisal_s_achievement_rec ADD target_date_req char(1)
GO

ALTER TABLE appraisal_s_achievement_rec ADD how_req char(1)
GO

ALTER TABLE appraisal_s_achievement_rec ADD details_req char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_achievement_rec'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_achievement_rec'; DECLARE @ColumnName SYSNAME set @ColumnName = N'objective_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for objective';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_achievement_rec'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_achievement_rec'; DECLARE @ColumnName SYSNAME set @ColumnName = N'target_date_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for target 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'appraisal_s_achievement_rec'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_achievement_rec'; DECLARE @ColumnName SYSNAME set @ColumnName = N'how_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for how';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_achievement_rec'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_achievement_rec'; DECLARE @ColumnName SYSNAME set @ColumnName = N'details_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150924-ca-1', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 483, '8:3a0b0c00b632d1f1ce7f5a34c2ec44c7', 'addColumn tableName=appraisal_s_achievement_rec', '[DEV 9000] Specifying Fields as Optional in Appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150925-ca-1::chris.au
-- [DEV 9000] Specifying Fields as Optional in Appraisals
ALTER TABLE appraisal_s_career_dev ADD objective_req char(1)
GO

ALTER TABLE appraisal_s_career_dev ADD target_date_req char(1)
GO

ALTER TABLE appraisal_s_career_dev ADD how_req char(1)
GO

ALTER TABLE appraisal_s_career_dev ADD details_req char(1)
GO

ALTER TABLE appraisal_s_career_dev ADD measure_req char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_career_dev'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_career_dev'; DECLARE @ColumnName SYSNAME set @ColumnName = N'objective_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for objective';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_career_dev'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_career_dev'; DECLARE @ColumnName SYSNAME set @ColumnName = N'target_date_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for target 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'appraisal_s_career_dev'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_career_dev'; DECLARE @ColumnName SYSNAME set @ColumnName = N'how_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for how';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_career_dev'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_career_dev'; DECLARE @ColumnName SYSNAME set @ColumnName = N'details_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for 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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_career_dev'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_career_dev'; DECLARE @ColumnName SYSNAME set @ColumnName = N'measure_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for measure';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150925-ca-1', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 484, '8:a15838b73d6940389880d6224dbe1a6d', 'addColumn tableName=appraisal_s_career_dev', '[DEV 9000] Specifying Fields as Optional in Appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150924-gl-1::garbo.lim
-- [DEV 9512] New column for Role description
ALTER TABLE code_role ADD name nvarchar(85)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'code_role'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.code_role'; DECLARE @ColumnName SYSNAME set @ColumnName = N'name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Role 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

update code_role set name = description
GO

ALTER TABLE code_role ALTER COLUMN name nvarchar(85) NOT NULL
GO

DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE code_role 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'code_role') AND [c].[name] = N'description'
EXEC sp_executesql @sql
GO

ALTER TABLE code_role DROP COLUMN description
GO

ALTER TABLE code_role ADD description nvarchar (max)
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150924-gl-1', 'garbo.lim', 'nschema/112.changeLog.xml', GETDATE(), 485, '8:b9e59e3ff5bb890eac168ec44a195572', 'addColumn tableName=code_role; sql; addNotNullConstraint columnName=name, tableName=code_role; dropColumn columnName=description, tableName=code_role; addColumn tableName=code_role', '[DEV 9512] New column for Role description', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150929-ca-1::chris.au
-- [DEV 9000] Specifying Fields as Optional in Appraisals
ALTER TABLE appraisal_s_sign_off ADD appraisee_comments_req char(1)
GO

ALTER TABLE appraisal_s_sign_off ADD appraiser_comments_req char(1)
GO

ALTER TABLE appraisal_s_sign_off ADD appraisee_acceptance_req char(1)
GO

ALTER TABLE appraisal_s_sign_off ADD appraiser_acceptance_req char(1)
GO

ALTER TABLE appraisal_s_sign_off ADD compliance_attitude_req char(1)
GO

ALTER TABLE appraisal_s_sign_off ADD career_dev_dialogue_req char(1)
GO

ALTER TABLE appraisal_s_sign_off ADD s_appraiser_acceptance_req char(1)
GO

ALTER TABLE appraisal_s_sign_off ADD s_appraiser_comments_req char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'appraisal_s_sign_off'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_sign_off'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraisee_comments_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for appraisee comment';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_sign_off'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_sign_off'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraiser_comments_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for appraiser comment';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_sign_off'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_sign_off'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraisee_acceptance_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for appraisee acceptance';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_sign_off'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_sign_off'; DECLARE @ColumnName SYSNAME set @ColumnName = N'appraiser_acceptance_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for appraiser acceptance';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_sign_off'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_sign_off'; DECLARE @ColumnName SYSNAME set @ColumnName = N'compliance_attitude_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for compliance attitude';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_sign_off'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_sign_off'; DECLARE @ColumnName SYSNAME set @ColumnName = N'career_dev_dialogue_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for career development dialog';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_sign_off'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_sign_off'; DECLARE @ColumnName SYSNAME set @ColumnName = N's_appraiser_acceptance_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for super appraiser acceptance';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'appraisal_s_sign_off'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.appraisal_s_sign_off'; DECLARE @ColumnName SYSNAME set @ColumnName = N's_appraiser_comments_req'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'mandatory flag for super appraiser comment';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20150929-ca-1', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 486, '8:087749380ebbcaa1d7433ed299b8e4c5', 'addColumn tableName=appraisal_s_sign_off', '[DEV 9000] Specifying Fields as Optional in Appraisals', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150929-js-1::janice.sung
-- New columns 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_code nvarchar(85)
GO

ALTER TABLE certusers ADD cert_title nvarchar(4000)
GO

ALTER TABLE certusers ADD user_currentpid nvarchar(85)
GO

ALTER TABLE certusers ADD course_name nvarchar(4000)
GO

ALTER TABLE certusers ADD course_duration_comments nvarchar(255)
GO

ALTER TABLE certusers ADD course_startdate datetime
GO

ALTER TABLE certusers ADD course_enddate datetime
GO

ALTER TABLE certusers ADD user_locale smallint
GO

ALTER TABLE certusers ADD course_module_description nvarchar (max)
GO

ALTER TABLE certusers ADD course_session_title nvarchar(255)
GO

ALTER TABLE certusers ADD course_facility_code nvarchar(255)
GO

ALTER TABLE certusers ADD tran_updater_legal_name nvarchar(250)
GO

ALTER TABLE certusers ADD tran_update_meaning_text nvarchar(500)
GO

ALTER TABLE certusers ADD tran_last_updated datetime
GO

ALTER TABLE certusers ADD course_performance_grade smallint
GO

ALTER TABLE certusers ADD user_givenname nvarchar(85)
GO

ALTER TABLE certusers ADD user_familyname nvarchar(85)
GO

ALTER TABLE certusers ADD user_employeeno nvarchar(85)
GO

ALTER TABLE certusers ADD user_jobtitle nvarchar(85)
GO

ALTER TABLE certusers ADD user_dateofbirth datetime
GO

ALTER TABLE certusers ADD user_address1 nvarchar(150)
GO

ALTER TABLE certusers ADD user_address2 nvarchar(150)
GO

ALTER TABLE certusers ADD user_city nvarchar(50)
GO

ALTER TABLE certusers ADD task_approver_givenname nvarchar(85)
GO

ALTER TABLE certusers ADD task_approver_familyname nvarchar(85)
GO

ALTER TABLE certusers ADD cert_template_id 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'cert_code'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Certification Code';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'cert_title'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Certification Name (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

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_currentpid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s currentPID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'course_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course Name (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

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'course_duration_comments'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course Duration Comments';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'course_startdate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course 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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'course_enddate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course 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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'user_locale'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s User Perference locale';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'course_module_description'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course Module 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

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'course_session_title'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course Session title';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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'course_facility_code'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course Facility Code';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'tran_updater_legal_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Transcript Updater''s 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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'tran_update_meaning_text'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Transcript 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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'tran_last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Transcript Signed 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'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'course_performance_grade'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course Performance Grade';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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_givenname'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s First 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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'user_familyname'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s Last 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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'user_employeeno'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s Employee 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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'user_jobtitle'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s Job Title';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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_dateofbirth'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s Date of Birth';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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_address1'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s Address 1';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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_address2'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s Address 2';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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_city'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learner''s City';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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'task_approver_givenname'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Task Approver''s Given 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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'task_approver_familyname'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Task Approver''s Family 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'certusers'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers'; DECLARE @ColumnName SYSNAME set @ColumnName = N'cert_template_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Certification Template Id, Ekp File Revision 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 ('20150929-js-1', 'janice.sung', 'nschema/112.changeLog.xml', GETDATE(), 487, '8:03806be42a1bfc1c7b7f02453a0a79ed', 'sql; sql; sql; addColumn tableName=certusers', 'New columns for storing snapshot information of cert awarded to user', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150929-js-2::janice.sung
-- New table to store course objectives snapshot information for certusers
