CREATE TABLE certusers_course_objectives (id nvarchar(85) NOT NULL, certusers_awardid nvarchar(85) NOT NULL, objective_description nvarchar(4000))
GO

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

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers_course_objectives'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_course_objectives'; DECLARE @ColumnName SYSNAME set @ColumnName = N'certusers_awardid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Award ID of the certusers';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = 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_course_objectives'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_course_objectives'; DECLARE @ColumnName SYSNAME set @ColumnName = N'objective_description'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Course objectives descriptions (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

ALTER TABLE certusers_course_objectives ADD CONSTRAINT PK_CERTUSERS_COURSE_OBJECTIVES PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150929-js-2', 'janice.sung', 'nschema/112.changeLog.xml', GETDATE(), 488, '8:a8b5a250ab480662afffd3a37ad62e89', 'createTable tableName=certusers_course_objectives; addPrimaryKey constraintName=PK_CERTUSERS_COURSE_OBJECTIVES, tableName=certusers_course_objectives', 'New table to store course objectives snapshot information for certusers', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150929-js-3::janice.sung
-- New table to store attributes snapshot for certusers including the course module attributes, user attribute and user attribute extensions
CREATE TABLE certusers_attribute (id nvarchar(85) NOT NULL, certusers_awardid nvarchar(85) NOT NULL, attribute_type char(2) NOT NULL, attribute_no int, attribute_code nvarchar(2000), attribute_value nvarchar(2000))
GO

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

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_attribute'; DECLARE @ColumnName SYSNAME set @ColumnName = N'certusers_awardid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Award ID of the certusers';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = 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_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_attribute'; DECLARE @ColumnName SYSNAME set @ColumnName = N'attribute_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Attribute type (Module Attribute / User Attribute / User Attribtue Extension)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = 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_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_attribute'; DECLARE @ColumnName SYSNAME set @ColumnName = N'attribute_no'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Attribute 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_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_attribute'; DECLARE @ColumnName SYSNAME set @ColumnName = N'attribute_code'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Attribute 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_attribute'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_attribute'; DECLARE @ColumnName SYSNAME set @ColumnName = N'attribute_value'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Attribute value';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE certusers_attribute ADD CONSTRAINT PK_CERTUSERS_ATTRIBUTE PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150929-js-3', 'janice.sung', 'nschema/112.changeLog.xml', GETDATE(), 489, '8:fe8711a3897d3b36983c77ac3a6ae4f7', 'createTable tableName=certusers_attribute; addPrimaryKey constraintName=PK_CERTUSERS_ATTRIBUTE, tableName=certusers_attribute', 'New table to store attributes snapshot for certusers including the course module attributes, user attribute and user attribute extensions', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20150929-js-4::janice.sung
-- New table to store course instructor snapshot information for certusers
CREATE TABLE certusers_instructor (id nvarchar(85) NOT NULL, certusers_awardid nvarchar(85) NOT NULL, instructor_givenname nvarchar(85), instructor_familyname nvarchar(85), instructor_jobtitle nvarchar(85), instructor_department nvarchar(85), instructor_da_givenname nvarchar(85), instructor_da_familyname nvarchar(85), instructor_da_jobtitle nvarchar(85), instructor_da_department nvarchar(85))
GO

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

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers_instructor'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_instructor'; DECLARE @ColumnName SYSNAME set @ColumnName = N'certusers_awardid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Award ID of the certusers';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = 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_instructor'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_instructor'; DECLARE @ColumnName SYSNAME set @ColumnName = N'instructor_givenname'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Instructor''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_instructor'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_instructor'; DECLARE @ColumnName SYSNAME set @ColumnName = N'instructor_familyname'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Instructor''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_instructor'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_instructor'; DECLARE @ColumnName SYSNAME set @ColumnName = N'instructor_jobtitle'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Instructor''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_instructor'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_instructor'; DECLARE @ColumnName SYSNAME set @ColumnName = N'instructor_department'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Instructor''s department name';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'certusers_instructor'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_instructor'; DECLARE @ColumnName SYSNAME set @ColumnName = N'instructor_da_givenname'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Instructor''s DA''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_instructor'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_instructor'; DECLARE @ColumnName SYSNAME set @ColumnName = N'instructor_da_familyname'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Instructor''s DA''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_instructor'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_instructor'; DECLARE @ColumnName SYSNAME set @ColumnName = N'instructor_da_jobtitle'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Instructor''s DA''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_instructor'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.certusers_instructor'; DECLARE @ColumnName SYSNAME set @ColumnName = N'instructor_da_department'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Instructor''s DA''s department name';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

ALTER TABLE certusers_instructor ADD CONSTRAINT PK_CERTUSERS_INSTRUCTOR PRIMARY KEY (id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150929-js-4', 'janice.sung', 'nschema/112.changeLog.xml', GETDATE(), 490, '8:3fa7ec59d2fa00feb147a26a144a6dd7', 'createTable tableName=certusers_instructor; addPrimaryKey constraintName=PK_CERTUSERS_INSTRUCTOR, tableName=certusers_instructor', 'New table to store course instructor snapshot information for certusers', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151002-ca-1::chris.au
CREATE TABLE temp_template_section (id nvarchar(15) NOT NULL)
GO

insert into temp_template_section(id) select
			s.id from
			appraisal_template_section s
			inner join appraisal_template_page p on
			s.page_id = p.id
			inner join appraisal_template t on p.template_id =
			t.id
			where t.id in
			(select template_id from appraisal_status group by
			template_id having
			count(1) > 2)
GO

update appraisal_s_dimension set appraisee_comments_req = 'Y',
			appraiser_comments_req = 'Y'
			where appraisee_comments_req is null
			and
			exists (select 1 from temp_template_section where id =
			appraisal_s_dimension.section_id)
GO

update appraisal_s_dimension set appraisee_comments_req = 'N',
			appraiser_comments_req = 'N'
			where appraisee_comments_req is null
GO

update appraisal_s_goal_result set appraisee_comments_req = 'Y',
			appraiser_comments_req = 'Y'
			where appraisee_comments_req is null
			and
			exists (select 1 from
			temp_template_section where id =
			appraisal_s_goal_result.section_id)
GO

update appraisal_s_goal_result set appraisee_comments_req = 'N',
			appraiser_comments_req = 'N'
			where appraisee_comments_req is null
GO

update appraisal_s_competency set appraisee_comments_req = 'Y',
			appraiser_comments_req = 'Y'
			where appraisee_comments_req is null
			and
			exists (select 1 from
			temp_template_section where id =
			appraisal_s_competency.section_id)
GO

update appraisal_s_competency set appraisee_comments_req = 'N',
			appraiser_comments_req = 'N'
			where appraisee_comments_req is null
GO

update appraisal_s_competency set display_na = case when
			use_comp_rating = 'N' and (display_appraisee_rating = 'Y' or
			display_appraiser_rating = 'Y' or display_agreed_rating = 'Y') then
			'Y' else 'N' end
			where (display_na is null)
GO

update appraisal_s_free_text set is_req = 'Y'
			where is_req is null
			and exists (select 1 from temp_template_section where id =
			appraisal_s_free_text.section_id)
GO

update appraisal_s_free_text set is_req = 'N'
			where is_req is null
GO

update appraisal_s_sign_off set appraisee_comments_req = 'Y',
			appraiser_comments_req = 'Y' ,
			appraisee_acceptance_req = 'Y' ,
			appraiser_acceptance_req = 'Y' ,
			compliance_attitude_req = 'Y' ,
			career_dev_dialogue_req = 'Y' ,
			s_appraiser_acceptance_req = 'Y' ,
			s_appraiser_comments_req = 'Y'
			where appraisee_comments_req is null
			and exists (select 1 from temp_template_section where id =
			appraisal_s_sign_off .section_id)
GO

update appraisal_s_sign_off set appraisee_comments_req = 'N',
			appraiser_comments_req = 'N' ,
			appraisee_acceptance_req = 'N' ,
			appraiser_acceptance_req = 'N' ,
			compliance_attitude_req = 'N' ,
			career_dev_dialogue_req = 'N' ,
			s_appraiser_acceptance_req = 'N' ,
			s_appraiser_comments_req = 'N'
			where appraisee_comments_req is null
GO

update appraisal_s_single_choice set is_req = 'Y'
			where is_req is
			null
			and exists (select 1 from temp_template_section where id =
			appraisal_s_single_choice.section_id)
GO

update appraisal_s_single_choice set is_req = 'N'
			where is_req is
			null
GO

update appraisal_s_future_objective set objective_req = 'Y',
			target_date_req = 'Y',
			how_req = 'Y',
			details_req = 'Y'
			where objective_req is null
			and exists (select 1 from
			temp_template_section where id =
			appraisal_s_future_objective.section_id)
GO

update appraisal_s_future_objective set objective_req = 'N',
			target_date_req = 'N',
			how_req = 'N',
			details_req = 'N'
			where objective_req is null
GO

update appraisal_s_achievement_rec set objective_req = 'Y',
			target_date_req = 'Y',
			how_req = 'Y',
			details_req = 'Y'
			where objective_req is null
			and exists (select 1 from
			temp_template_section where id =
			appraisal_s_achievement_rec.section_id)
GO

update appraisal_s_achievement_rec set objective_req = 'N',
			target_date_req = 'N',
			how_req = 'N',
			details_req = 'N'
			where objective_req is null
GO

update appraisal_s_career_dev set objective_req = 'Y',
			target_date_req = 'Y',
			how_req = 'Y',
			details_req = 'Y',
			measure_req = 'Y'
			where objective_req is null
			and exists (select 1 from
			temp_template_section where id =
			appraisal_s_career_dev.section_id)
GO

update appraisal_s_career_dev set objective_req = 'N',
			target_date_req = 'N',
			how_req = 'N',
			details_req = 'N',
			measure_req = 'N'
			where objective_req is null
GO

DROP TABLE temp_template_section
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151002-ca-1', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 491, '8:ba629d5e18f5e8ae20650f7a942206d4', 'createTable tableName=temp_template_section; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; sql; dropTable tableName=temp_template_section', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151030-ca-1::chris.au
update appraisal_s_achievement_rec set 
			target_date_req = 'N'
GO

update appraisal_s_goal_result set appraisee_comments_req = 'Y',
			appraiser_comments_req = 'Y'
GO

update appraisal_s_sign_off set 
			compliance_attitude_req = 'N' ,
			career_dev_dialogue_req = 'N' 
			where career_dev_dialogue_req = 'Y'
GO

update appraisal_s_sign_off set 
			appraisee_acceptance_req = 'N' ,
			appraiser_acceptance_req = 'N',
			s_appraiser_acceptance_req = 'N'
			where appraisee_comments_req = 'Y'
GO

update appraisal_s_sign_off set 
			appraisee_acceptance_req = 'Y' ,
			appraiser_acceptance_req = 'Y' ,
			s_appraiser_acceptance_req = 'Y'
			where appraisee_comments_req = 'N'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151030-ca-1', 'chris.au', 'nschema/112.changeLog.xml', GETDATE(), 492, '8:473abc21cd0b0b9037055ed159f04759', 'sql; sql; sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151003-rr-1::ray.ruff
-- Basic property information for Demo Units, a new type of course resource
CREATE TABLE demo_unit (demo_unit_key nvarchar(15) NOT NULL, demo_unit_id nvarchar(85) NOT NULL, name nvarchar(85), demo_unit_type int CONSTRAINT DF_demo_unit_demo_unit_type DEFAULT 0 NOT NULL, unit_owner nvarchar(85), contact nvarchar(255), unit_description nvarchar(1000), picture_url nvarchar(255), storage_location nvarchar(255), other_location nvarchar(255), packaging nvarchar(255), items_per_unit int CONSTRAINT DF_demo_unit_items_per_unit DEFAULT 0 NOT NULL, delivery_time_days int CONSTRAINT DF_demo_unit_delivery_time_days DEFAULT 0 NOT NULL, return_time_days int CONSTRAINT DF_demo_unit_return_time_days DEFAULT 0 NOT NULL, creator_uid nvarchar(85), datecreated datetime, last_updater_uid nvarchar(85), last_updated datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.demo_unit';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Property information for Demo Units, a bookable course resource';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'demo_unit'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit'; DECLARE @ColumnName SYSNAME set @ColumnName = N'demo_unit_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique internal 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'demo_unit'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit'; DECLARE @ColumnName SYSNAME set @ColumnName = N'demo_unit_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'User assigned 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'demo_unit'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit'; DECLARE @ColumnName SYSNAME set @ColumnName = N'name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit descriptive 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'demo_unit'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit'; DECLARE @ColumnName SYSNAME set @ColumnName = N'demo_unit_type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit 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'demo_unit'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit'; DECLARE @ColumnName SYSNAME set @ColumnName = N'unit_owner'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit owner';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

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

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

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

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

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

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

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

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

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit'; DECLARE @ColumnName SYSNAME set @ColumnName = N'last_updated'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit descriptive 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

ALTER TABLE demo_unit ADD CONSTRAINT PK_DEMO_UNIT_KEY PRIMARY KEY (demo_unit_key)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151003-rr-1', 'ray.ruff', 'nschema/112.changeLog.xml', GETDATE(), 493, '8:790f501d2ea1beb9faba910c237dc80c', 'createTable tableName=demo_unit; addPrimaryKey constraintName=PK_DEMO_UNIT_KEY, tableName=demo_unit', 'Basic property information for Demo Units, a new type of course resource', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151003-rr-2::ray.ruff
-- New properties for facilities
ALTER TABLE facilities ADD category char(1) CONSTRAINT DF_facilities_category DEFAULT 'T' NOT NULL
GO

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'facilities'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.facilities'; DECLARE @ColumnName SYSNAME set @ColumnName = N'moveable_seating'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Moveable seating indicator';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 facilities ADD n_three_phase int CONSTRAINT DF_facilities_n_three_phase DEFAULT 0 NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'facilities'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.facilities'; DECLARE @ColumnName SYSNAME set @ColumnName = N'n_three_phase'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Number of three-phase power connectors';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 facilities ADD n_interactive_whiteboards int CONSTRAINT DF_facilities_n_interactive_whiteboards DEFAULT 0 NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'facilities'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.facilities'; DECLARE @ColumnName SYSNAME set @ColumnName = N'n_interactive_whiteboards'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Number of interactive whiteboards';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 facilities ADD n_monitors int CONSTRAINT DF_facilities_n_monitors DEFAULT 0 NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'facilities'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.facilities'; DECLARE @ColumnName SYSNAME set @ColumnName = N'n_monitors'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Number of Monitors';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 facilities ADD video_conf_system char(1) CONSTRAINT DF_facilities_video_conf_system DEFAULT 'N' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'facilities'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.facilities'; DECLARE @ColumnName SYSNAME set @ColumnName = N'video_conf_system'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Video conferencing indicator';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20151003-rr-2', 'ray.ruff', 'nschema/112.changeLog.xml', GETDATE(), 494, '8:535f61ad24ccd32ddfb5605c8e6e4629', 'addColumn tableName=facilities; addColumn tableName=facilities; addColumn tableName=facilities; addColumn tableName=facilities; addColumn tableName=facilities; addColumn tableName=facilities', 'New properties for facilities', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151006-cc-1::chiwang.chow
-- Vendor Logo
ALTER TABLE code_vendorname ADD picture nvarchar(500)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'code_vendorname'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.code_vendorname'; DECLARE @ColumnName SYSNAME set @ColumnName = N'picture'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Logo image';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 code_vendorname ADD show_picture char(1) CONSTRAINT DF_code_vendorname_show_picture DEFAULT 'N' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'code_vendorname'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.code_vendorname'; DECLARE @ColumnName SYSNAME set @ColumnName = N'show_picture'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Show picture in learning module 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 ('20151006-cc-1', 'chiwang.chow', 'nschema/112.changeLog.xml', GETDATE(), 495, '8:b319e378b0d06d4b827628d9404ad573', 'addColumn tableName=code_vendorname; addColumn tableName=code_vendorname', 'Vendor Logo', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151007-js-1::janice.sung
-- New column for storing snapshot learning object display id 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 display_learning_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'display_learning_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Display Learning 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 ('20151007-js-1', 'janice.sung', 'nschema/112.changeLog.xml', GETDATE(), 496, '8:404551b4479271fc2bf9929d9b0734ee', 'sql; sql; sql; addColumn tableName=certusers', 'New column for storing snapshot learning object display id of cert awarded to user', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151008-rr-1::ray.ruff
-- Assignment of Demo Units to Learning Objects
CREATE TABLE demo_unit_assignment (demo_unit_key nvarchar(15) NOT NULL, learningid nvarchar(85) NOT NULL, sessionid nvarchar(15) NOT NULL)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_assignment'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.demo_unit_assignment';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Assignment of Demo Units to Learning Objects';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'demo_unit_assignment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_assignment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'demo_unit_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique internal 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'demo_unit_assignment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_assignment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'learningid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learning Object ID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

ALTER TABLE demo_unit_assignment ADD CONSTRAINT PK_DEMO_UNIT_ASSIGNMENT PRIMARY KEY (demo_unit_key, learningid, sessionid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151008-rr-1', 'ray.ruff', 'nschema/112.changeLog.xml', GETDATE(), 497, '8:4fbfd22bcafc3f8ed5a2dfce0a752708', 'createTable tableName=demo_unit_assignment; addPrimaryKey constraintName=PK_DEMO_UNIT_ASSIGNMENT, tableName=demo_unit_assignment', 'Assignment of Demo Units to Learning Objects', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151014-yt-1::yan.tung
-- Reservation record for demo unit
CREATE TABLE demo_unit_reservation (reservation_id nvarchar(15) NOT NULL, demo_unit_key nvarchar(15) NOT NULL, learningid nvarchar(85) NOT NULL, sessionid nvarchar(15) NOT NULL, eventid nvarchar(15) NOT NULL, deliveryid nvarchar(15), reserve_date datetime, reservation_status nvarchar(20), creator_uid nvarchar(85), datecreated datetime, last_updater_uid nvarchar(85), last_updated datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_reservation'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.demo_unit_reservation';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'module session can reserve different set of demo unit';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'demo_unit_reservation'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reservation'; DECLARE @ColumnName SYSNAME set @ColumnName = N'reservation_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique internal 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'demo_unit_reservation'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reservation'; DECLARE @ColumnName SYSNAME set @ColumnName = N'demo_unit_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit 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'demo_unit_reservation'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reservation'; DECLARE @ColumnName SYSNAME set @ColumnName = N'learningid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learning Object ID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_reservation'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reservation'; DECLARE @ColumnName SYSNAME set @ColumnName = N'eventid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Event 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'demo_unit_reservation'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reservation'; DECLARE @ColumnName SYSNAME set @ColumnName = N'deliveryid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Delivery 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'demo_unit_reservation'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reservation'; DECLARE @ColumnName SYSNAME set @ColumnName = N'reserve_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit reserve 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'demo_unit_reservation'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reservation'; DECLARE @ColumnName SYSNAME set @ColumnName = N'reservation_status'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit reservation 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'demo_unit_reservation'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reservation'; DECLARE @ColumnName SYSNAME set @ColumnName = N'creator_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Reservation creator UID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

ALTER TABLE demo_unit_reservation ADD CONSTRAINT PK_DEMO_UNIT_RESERVE_KEY PRIMARY KEY (reservation_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151014-yt-1', 'yan.tung', 'nschema/112.changeLog.xml', GETDATE(), 498, '8:d5ec253dee2a45cc647ae02db6aa0687', 'createTable tableName=demo_unit_reservation; addPrimaryKey constraintName=PK_DEMO_UNIT_RESERVE_KEY, tableName=demo_unit_reservation', 'Reservation record for demo unit', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151014-yt-2::yan.tung
-- Demo unit delivery and collection information
CREATE TABLE demo_unit_delivery (deliveryid nvarchar(15) NOT NULL, learningid nvarchar(85) NOT NULL, sessionid nvarchar(15) NOT NULL, eventid nvarchar(15) NOT NULL, before_after_trainingdays int CONSTRAINT DF_demo_unit_delivery_before_after_trainingdays DEFAULT 0, delivery_time_days int CONSTRAINT DF_demo_unit_delivery_delivery_time_days DEFAULT 0, is_express_delivery char(1) CONSTRAINT DF_demo_unit_delivery_is_express_delivery DEFAULT 'N' NOT NULL, delivery_to_label nvarchar(50), delivery_to_address nvarchar(255), contact_name nvarchar(255), contact_phone nvarchar(255), contact_email nvarchar(255), corr_email nvarchar(255), corr_email_subject nvarchar(255), corr_email_content nvarchar(255), send_date datetime, delivery_status nvarchar(20), type nvarchar(20), creator_uid nvarchar(85), datecreated datetime, last_updater_uid nvarchar(85), last_updated datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_delivery'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo unit delivery and collection information';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'deliveryid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique internal 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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'learningid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learning Object ID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'eventid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Event 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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'before_after_trainingdays'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit time to deliver (in days)';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

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

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'contact_name'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Delivery or collection contact 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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'contact_phone'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Delivery or collection contact telephone 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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'contact_email'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Delivery or collection contact email';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'send_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Delivery or collection correspondence send out 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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'delivery_status'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit delivery 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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'type'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Delivery or Collection';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

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

ALTER TABLE demo_unit_delivery ADD CONSTRAINT PK_DEMO_UNIT_DELIVERY_KEY PRIMARY KEY (deliveryid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151014-yt-2', 'yan.tung', 'nschema/112.changeLog.xml', GETDATE(), 499, '8:b067d339b7a26412b52017893cf5e5bf', 'createTable tableName=demo_unit_delivery; addPrimaryKey constraintName=PK_DEMO_UNIT_DELIVERY_KEY, tableName=demo_unit_delivery', 'Demo unit delivery and collection information', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151014-yt-3::yan.tung
-- Demo unit delivery and collection reminder
CREATE TABLE demo_unit_reminder (reminderid nvarchar(15) NOT NULL, reminder_date datetime, reminder_email nvarchar(255), learningid nvarchar(85) NOT NULL, sessionid nvarchar(15) NOT NULL, eventid nvarchar(15) NOT NULL, type nvarchar(20), creator_uid nvarchar(85), datecreated datetime, last_updater_uid nvarchar(85), last_updated datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_reminder'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.demo_unit_reminder';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo unit delivery and collection reminder';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'demo_unit_reminder'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reminder'; DECLARE @ColumnName SYSNAME set @ColumnName = N'reminderid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique internal 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'demo_unit_reminder'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_reminder'; DECLARE @ColumnName SYSNAME set @ColumnName = N'reminder_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Reminder date for delivery or collection';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

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

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

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

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

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

ALTER TABLE demo_unit_reminder ADD CONSTRAINT PK_DEMO_UNIT_REMINDER_KEY PRIMARY KEY (reminderid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151014-yt-3', 'yan.tung', 'nschema/112.changeLog.xml', GETDATE(), 500, '8:69779afb788aa9add2f3c0f4d6d5200a', 'createTable tableName=demo_unit_reminder; addPrimaryKey constraintName=PK_DEMO_UNIT_REMINDER_KEY, tableName=demo_unit_reminder', 'Demo unit delivery and collection reminder', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151015-yt-1::yan.tung
-- Add delivery date back to demo_unit_delivery
ALTER TABLE demo_unit_delivery ADD delivery_date datetime
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151015-yt-1', 'yan.tung', 'nschema/112.changeLog.xml', GETDATE(), 501, '8:49e48e069182fd290f3c8a3cf4e51e79', 'addColumn tableName=demo_unit_delivery', 'Add delivery date back to demo_unit_delivery', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151006-hf-1::hubert.fajarit
ALTER TABLE cschedule ADD set_endedStatus_after_endDate char(1) CONSTRAINT DF_cschedule_set_endedStatus_after_endDate DEFAULT 'N' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'cschedule'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.cschedule'; DECLARE @ColumnName SYSNAME set @ColumnName = N'set_endedStatus_after_endDate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Flag for determining if Session Status will be updated to Ended once End Date day is reached';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20151006-hf-1', 'hubert.fajarit', 'nschema/112.changeLog.xml', GETDATE(), 502, '8:9636656ac607847d2e8c628b25e19a95', 'addColumn tableName=cschedule', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151015-dl-1::derek.liu
-- New properties for facilities
ALTER TABLE facilities ADD address nvarchar(255)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'facilities'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.facilities'; DECLARE @ColumnName SYSNAME set @ColumnName = N'address'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Specific location address';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20151015-dl-1', 'derek.liu', 'nschema/112.changeLog.xml', GETDATE(), 503, '8:cdb2ec97aa015b15fd9333dc4edd4573', 'addColumn tableName=facilities', 'New properties for facilities', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151016-cc-1::chiwang.chow
-- Add a column for demo unit reservation end date
ALTER TABLE demo_unit_reservation ADD reserve_finishdate datetime
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151016-cc-1', 'chiwang.chow', 'nschema/112.changeLog.xml', GETDATE(), 504, '8:765ea98abea109fb2bc24686f020e88d', 'addColumn tableName=demo_unit_reservation', 'Add a column for demo unit reservation end date', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151016-hf-2::hubert.fajarit
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 ('20151016-hf-2', 'hubert.fajarit', 'nschema/112.changeLog.xml', GETDATE(), 505, '8:1f0ebbed93b0311ce49c71e77f0127dc', 'dropView viewName=cschedule_vw; createView viewName=cschedule_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151016-sh-1::sergio.hui
-- Add a column for storing seating arrangement
ALTER TABLE module_session_schedule ADD seating_arrangement nvarchar(20)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'module_session_schedule'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.module_session_schedule'; DECLARE @ColumnName SYSNAME set @ColumnName = N'seating_arrangement'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Seating Arrangement';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20151016-sh-1', 'sergio.hui', 'nschema/112.changeLog.xml', GETDATE(), 506, '8:346acf44f17fe8b88a1450b6a1af78fd', 'addColumn tableName=module_session_schedule', 'Add a column for storing seating arrangement', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151019-yt-1::yan.tung
-- Remove deliveryid, reservation_status column from demo_unit_reservation table
DECLARE @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE demo_unit_reservation 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'demo_unit_reservation') AND [c].[name] = N'deliveryid'
EXEC sp_executesql @sql
GO

ALTER TABLE demo_unit_reservation DROP COLUMN deliveryid
GO

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

ALTER TABLE demo_unit_reservation DROP COLUMN reservation_status
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151019-yt-1', 'yan.tung', 'nschema/112.changeLog.xml', GETDATE(), 507, '8:2ea799cbe5713a672d210bd63da07b1e', 'dropColumn columnName=deliveryid, tableName=demo_unit_reservation; dropColumn columnName=reservation_status, tableName=demo_unit_reservation', 'Remove deliveryid, reservation_status column from demo_unit_reservation table', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151019-yt-2::yan.tung
-- Demo units in Demo Unit Delivery note or Collection Receipt
CREATE TABLE demo_unit_delivery_item (delivery_item_id nvarchar(15) NOT NULL, demo_unit_key nvarchar(15) NOT NULL, deliveryid nvarchar(15), reserve_date datetime, creator_uid nvarchar(85), datecreated datetime, last_updater_uid nvarchar(85), last_updated datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_delivery_item'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery_item';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'demo unit';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (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'demo_unit_delivery_item'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery_item'; DECLARE @ColumnName SYSNAME set @ColumnName = N'delivery_item_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique internal 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'demo_unit_delivery_item'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery_item'; DECLARE @ColumnName SYSNAME set @ColumnName = N'demo_unit_key'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit 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'demo_unit_delivery_item'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery_item'; DECLARE @ColumnName SYSNAME set @ColumnName = N'deliveryid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Delivery 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'demo_unit_delivery_item'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery_item'; DECLARE @ColumnName SYSNAME set @ColumnName = N'reserve_date'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit reserve 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'demo_unit_delivery_item'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery_item'; DECLARE @ColumnName SYSNAME set @ColumnName = N'creator_uid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Reservation creator UID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

ALTER TABLE demo_unit_delivery_item ADD CONSTRAINT PK_DEMOUNIT_DELI_ITEM_KEY PRIMARY KEY (delivery_item_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151019-yt-2', 'yan.tung', 'nschema/112.changeLog.xml', GETDATE(), 508, '8:962083cb2c35fd5a055ea516bb03f05b', 'createTable tableName=demo_unit_delivery_item; addPrimaryKey constraintName=PK_DEMOUNIT_DELI_ITEM_KEY, tableName=demo_unit_delivery_item', 'Demo units in Demo Unit Delivery note or Collection Receipt', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151019-yt-3::yan.tung
-- Extend the column size of reminder_email on demo_unit_reminder
ALTER TABLE demo_unit_reminder ALTER COLUMN reminder_email NVARCHAR(1000)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151019-yt-3', 'yan.tung', 'nschema/112.changeLog.xml', GETDATE(), 509, '8:36d14526729e5163b4c54c4466738637', 'sql; sql; sql', 'Extend the column size of reminder_email on demo_unit_reminder', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151020-js-1::janice.sung
-- Data Migration for the new fields for certusers table
UPDATE certusers SET 
				cert_code = (SELECT c.certification_code FROM certifications c WHERE c.certid = certusers.certid),
				cert_title = (SELECT c.title FROM certifications c WHERE c.certid = certusers.certid),
				user_currentpid = (SELECT u.currentPID FROM users u WHERE u.userid = certusers.userid),
				course_name = (SELECT lo.title FROM learningObject lo WHERE lo.learningid = certusers.learningid),
				course_duration_comments = (SELECT CASE WHEN certusers.awardedby IS NULL THEN NULL ELSE lo.duration END FROM learningObject lo WHERE lo.learningid = certusers.learningid),
				
				course_startdate = (SELECT t.startdate FROM transcript t WHERE t.transcriptid = certusers.awardedby),
				course_enddate = (SELECT t.finishdate FROM transcript t WHERE t.transcriptid = certusers.awardedby),
				user_locale = (SELECT pref.languagepref FROM userprefs pref WHERE pref.userid = certusers.userid),
				course_module_description = 
					(SELECT lo.description1 FROM learningObject lo INNER JOIN transcript t ON t.learningid = lo.learningid 
						WHERE lo.learningid = certusers.learningid AND t.transcriptid = certusers.awardedby),
				course_session_title = 
					(SELECT (CASE WHEN t.learningType = 'C' THEN sd.session_title ELSE cs.session_title END) 
						FROM transcript t 
							LEFT JOIN cschedule cs ON cs.courseid = t.learningid AND cs.eventid = t.sessionid 
                        	LEFT JOIN sessiondetail sd ON sd.currid = t.learningid AND sd.sessionid = t.sessionid 
						WHERE t.learningid = certusers.learningid AND t.transcriptid = certusers.awardedby),
				
				tran_updater_legal_name = (SELECT t.updater_legal_name FROM transcript t WHERE t.transcriptid = certusers.awardedby),
				tran_update_meaning_text = (SELECT t.update_meaning_text FROM transcript t WHERE t.transcriptid = certusers.awardedby),
				tran_last_updated = (SELECT t.last_updated FROM transcript t WHERE t.transcriptid = certusers.awardedby),
				course_performance_grade = (SELECT t.perfGradeid FROM transcript t WHERE t.transcriptid = certusers.awardedby),
				
				user_givenname = (SELECT u.givenname FROM users u WHERE u.userid = certusers.userid),
				user_familyname = (SELECT u.familyname FROM users u WHERE u.userid = certusers.userid),
				user_employeeno = (SELECT u.employeeno FROM users u WHERE u.userid = certusers.userid),
				user_jobtitle = (SELECT u.jobtitle FROM users u WHERE u.userid = certusers.userid),
				user_dateofbirth = (SELECT u.dateofbirth FROM users u WHERE u.userid = certusers.userid),
				
				user_address1 = (SELECT ui.b_address1 FROM userinfo ui WHERE ui.userid = certusers.userid),
				user_address2 = (SELECT ui.b_address2 FROM userinfo ui WHERE ui.userid = certusers.userid),
				user_city = (SELECT ui.b_city FROM userinfo ui WHERE ui.userid = certusers.userid),
				task_approver_givenname = 
					(SELECT ta.givenname FROM task_signoff_users tu INNER JOIN users ta ON ta.userid = tu.signoff_user_id WHERE tu.transcript_id = certusers.awardedby),
				task_approver_familyname = 
					(SELECT ta.familyname FROM task_signoff_users tu INNER JOIN users ta ON ta.userid = tu.signoff_user_id WHERE tu.transcript_id = certusers.awardedby),
					
				cert_template_id = 
					(SELECT MAX(r.id) FROM ekpfiles_revision r LEFT JOIN ekpfiles f ON r.path = f.path LEFT JOIN certifications c ON f.repositoryid = c.templateid WHERE c.certid = certusers.certid),
				display_learning_id = 
					(SELECT CASE WHEN certusers.learningid IS NULL THEN NULL ELSE lo.display_learning_id END FROM learningObject lo WHERE lo.learningid = certusers.learningid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151020-js-1', 'janice.sung', 'nschema/112.changeLog.xml', GETDATE(), 510, '8:e3f964a1c9080ba1d9691f9064c4c5ed', 'sql', 'Data Migration for the new fields for certusers table', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151021-hf-3::hubert.fajarit
exec sp_rename 'cschedule.set_endedStatus_after_endDate', 'set_endedstatus_on_enddate'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151021-hf-3', 'hubert.fajarit', 'nschema/112.changeLog.xml', GETDATE(), 511, '8:71c12819110beff940e850e8537f6196', 'renameColumn newColumnName=set_endedstatus_on_enddate, oldColumnName=set_endedStatus_after_endDate, tableName=cschedule', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151021-hf-4::hubert.fajarit
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 ('20151021-hf-4', 'hubert.fajarit', 'nschema/112.changeLog.xml', GETDATE(), 512, '8:1f0ebbed93b0311ce49c71e77f0127dc', 'dropView viewName=cschedule_vw; createView viewName=cschedule_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151023-hf-5::hubert.fajarit
ALTER TABLE users_assignment ADD block_notification char(1) CONSTRAINT DF_users_assignment_block_notification DEFAULT 'N' NOT NULL
GO

ALTER TABLE email_profile ADD force_send char(1) CONSTRAINT DF_email_profile_force_send DEFAULT 'Y' NOT NULL
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151023-hf-5', 'hubert.fajarit', 'nschema/112.changeLog.xml', GETDATE(), 513, '8:9162a3d7de460f5fa8ce978a5db25fff', 'addColumn tableName=users_assignment; addColumn tableName=email_profile', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151022-yt-1::yan.tung
-- Rename table demo_unit_delivery to demo_unit_correspondence and change schema
ALTER TABLE demo_unit_delivery ADD corrid nvarchar(15) NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'corrid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique internal 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 @sql [nvarchar](MAX)
SELECT @sql = N'ALTER TABLE demo_unit_delivery 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'demo_unit_delivery') AND [c].[name] = N'learningid'
EXEC sp_executesql @sql
GO

ALTER TABLE demo_unit_delivery DROP COLUMN learningid
GO

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

ALTER TABLE demo_unit_delivery DROP COLUMN sessionid
GO

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

ALTER TABLE demo_unit_delivery DROP COLUMN eventid
GO

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

ALTER TABLE demo_unit_delivery DROP COLUMN delivery_status
GO

ALTER TABLE demo_unit_delivery DROP CONSTRAINT PK_DEMO_UNIT_DELIVERY_KEY
GO

ALTER TABLE demo_unit_delivery ADD CONSTRAINT PK_DEMO_UNIT_CORR_KEY PRIMARY KEY (corrid)
GO

exec sp_rename 'demo_unit_delivery', 'demo_unit_correspondence'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151022-yt-1', 'yan.tung', 'nschema/112.changeLog.xml', GETDATE(), 514, '8:10813d4fb7c6e27ae0aee9fa97b1c38f', 'addColumn tableName=demo_unit_delivery; dropColumn columnName=learningid, tableName=demo_unit_delivery; dropColumn columnName=sessionid, tableName=demo_unit_delivery; dropColumn columnName=eventid, tableName=demo_unit_delivery; dropColumn columnNa...', 'Rename table demo_unit_delivery to demo_unit_correspondence and change schema', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151023-yt-1::yan.tung
-- Demo unit delivery information
CREATE TABLE demo_unit_delivery (deliveryid nvarchar(15) NOT NULL, learningid nvarchar(85) NOT NULL, sessionid nvarchar(15) NOT NULL, eventid nvarchar(15) NOT NULL, delivery_status nvarchar(20), action_performed nvarchar(20), creator_uid nvarchar(85), datecreated datetime, last_updater_uid nvarchar(85), last_updated datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_delivery'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo unit delivery information';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'deliveryid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Unique internal 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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'learningid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learning Object ID';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'eventid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Event 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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'delivery_status'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Demo Unit delivery 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'demo_unit_delivery'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_delivery'; DECLARE @ColumnName SYSNAME set @ColumnName = N'action_performed'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'action performed, can be send delivery note or collection receipt';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

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

ALTER TABLE demo_unit_delivery ADD CONSTRAINT PK_DEMO_UNIT_DELIVERY_KEY PRIMARY KEY (deliveryid)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151023-yt-1', 'yan.tung', 'nschema/112.changeLog.xml', GETDATE(), 515, '8:8f287e72d30e2e1d6f544c2134e16e23', 'createTable tableName=demo_unit_delivery; addPrimaryKey constraintName=PK_DEMO_UNIT_DELIVERY_KEY, tableName=demo_unit_delivery', 'Demo unit delivery information', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151023-sh-1::sergio.hui
ALTER TABLE demo_unit_assignment ADD is_optional char(1)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'demo_unit_assignment'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.demo_unit_assignment'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_optional'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'the Demo Unit is an optional 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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151023-sh-1', 'sergio.hui', 'nschema/112.changeLog.xml', GETDATE(), 516, '8:4ab37f5f50963c52175a40770dce399d', 'addColumn tableName=demo_unit_assignment', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151028-wc-1::wilson.choi
-- [DEV 9884] User Targeting Template as UI Permission Setting Option
IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'i_permissions'
            )
            DROP TRIGGER i_permissions
GO

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

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

ALTER TABLE permissions ADD is_pts_objecttype char(1) CONSTRAINT DF_permissions_is_pts_objecttype DEFAULT 'N' NOT NULL
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'permissions'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.permissions'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_pts_objecttype'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'the permission is a template 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

ALTER TABLE permissions DROP CONSTRAINT PK_PERMISSION
GO

ALTER TABLE permissions ADD CONSTRAINT PK_PERMISSION PRIMARY KEY (objectid, objecttype, identityid, permissioncode, identitytype, is_pts_objecttype)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151028-wc-1', 'wilson.choi', 'nschema/112.changeLog.xml', GETDATE(), 517, '8:f058e020cf51e76c269ecbff23a2c369', 'sql; sql; sql; addColumn tableName=permissions; dropPrimaryKey constraintName=PK_PERMISSION, tableName=permissions; addPrimaryKey constraintName=PK_PERMISSION, tableName=permissions', '[DEV 9884] User Targeting Template as UI Permission Setting Option', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151028-wc-2::wilson.choi
-- [DEV 9884] User Targeting Template as UI Permission Setting Option
CREATE TABLE permission_mapping (objectid nvarchar(255) NOT NULL, objecttype nvarchar(5) NOT NULL, resolved_objectid nvarchar(255) NOT NULL, is_pts_objecttype char(1) CONSTRAINT DF_permission_mapping_is_pts_objecttype DEFAULT 'N' NOT NULL)
GO

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

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'permission_mapping'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.permission_mapping'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_pts_objecttype'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'the permission is a template 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

ALTER TABLE permission_mapping ADD CONSTRAINT PK_PERMISSION_MAPPING PRIMARY KEY (objectid, objecttype)
GO

CREATE NONCLUSTERED INDEX NUK_PERMISSION_MAPPING ON permission_mapping(resolved_objectid)
GO

INSERT INTO permission_mapping(objectid, objecttype, resolved_objectid, is_pts_objecttype)
        	SELECT distinct objectid, objecttype, objectid, 'N' FROM permissions
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151028-wc-2', 'wilson.choi', 'nschema/112.changeLog.xml', GETDATE(), 518, '8:fc063e597af336ea39287832ee730d77', 'createTable tableName=permission_mapping; addPrimaryKey constraintName=PK_PERMISSION_MAPPING, tableName=permission_mapping; createIndex indexName=NUK_PERMISSION_MAPPING, tableName=permission_mapping; sql', '[DEV 9884] User Targeting Template as UI Permission Setting Option', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151028-wc-3::wilson.choi
-- [DEV 9884] User Targeting Template as UI Permission Setting Option
IF EXISTS (
            SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'i_permission_mapping'
            )
            DROP TRIGGER i_permission_mapping
GO

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

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

DROP TABLE permission_mapping
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151028-wc-3', 'wilson.choi', 'nschema/112.changeLog.xml', GETDATE(), 519, '8:416307d5d4d8ec4423d1c4600cef1094', 'sql; sql; sql; dropTable tableName=permission_mapping', '[DEV 9884] User Targeting Template as UI Permission Setting Option', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151028-wc-4::wilson.choi
-- [DEV 9884] User Targeting Template as UI Permission Setting Option
CREATE TABLE permission_mapping (objectid nvarchar(255) NOT NULL, objecttype nvarchar(5) NOT NULL, permissioncode int NOT NULL, resolved_objectid nvarchar(255) NOT NULL, is_pts_objecttype char(1) CONSTRAINT DF_permission_mapping_is_pts_objecttype DEFAULT 'N' NOT NULL)
GO

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

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'permission_mapping'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.permission_mapping'; DECLARE @ColumnName SYSNAME set @ColumnName = N'objecttype'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Object 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'permission_mapping'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.permission_mapping'; DECLARE @ColumnName SYSNAME set @ColumnName = N'permissioncode'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Permission 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'permission_mapping'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.permission_mapping'; DECLARE @ColumnName SYSNAME set @ColumnName = N'resolved_objectid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'the resolved permission object id';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'permission_mapping'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.permission_mapping'; DECLARE @ColumnName SYSNAME set @ColumnName = N'is_pts_objecttype'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'the permission is a template 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

ALTER TABLE permission_mapping ADD CONSTRAINT PK_PERMISSION_MAPPING PRIMARY KEY (objectid, objecttype, permissioncode)
GO

CREATE NONCLUSTERED INDEX NUK_PERMISSION_MAPPING ON permission_mapping(resolved_objectid)
GO

INSERT INTO permission_mapping(objectid, objecttype, permissioncode, resolved_objectid, is_pts_objecttype)
        	SELECT distinct objectid, objecttype, permissioncode, objectid, 'N' FROM permissions
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151028-wc-4', 'wilson.choi', 'nschema/112.changeLog.xml', GETDATE(), 520, '8:d0573513c886baa93ae9a39b401b9a0f', 'createTable tableName=permission_mapping; addPrimaryKey constraintName=PK_PERMISSION_MAPPING, tableName=permission_mapping; createIndex indexName=NUK_PERMISSION_MAPPING, tableName=permission_mapping; sql', '[DEV 9884] User Targeting Template as UI Permission Setting Option', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151029-wc-1::wilson.choi
CREATE VIEW permissions_vw AS select pm.objectid, pm.objecttype, p.identityid, pm.permissioncode, 
				p.identitytype, p.operator, p.count, p.last_updated, 
				p.last_updater_uid, p.update_meaning_text, p.updater_legal_name 
				from permission_mapping pm inner join permissions p 
				on (pm.resolved_objectid=p.objectid and pm.objecttype=p.objecttype and pm.permissioncode=p.permissioncode and pm.is_pts_objecttype=p.is_pts_objecttype)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151029-wc-1', 'wilson.choi', 'nschema/112.changeLog.xml', GETDATE(), 521, '8:9e4a59019cc5f524229ad4e0a06faacd', 'createView viewName=permissions_vw', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151102-js-1::janice.sung
-- Change the column size of objectid to keep the primary key column size less than 900 for mssql
ALTER TABLE permissions DROP CONSTRAINT PK_PERMISSION
GO

IF EXISTS (SELECT Name FROM sysindexes WHERE Name = 'idx_ekpfilepermissionsusers') 
			DROP INDEX permissions.idx_ekpfilepermissionsusers
GO

IF EXISTS (SELECT Name FROM sysindexes WHERE Name = 'idx_ekpfilepermissionsobject') 
			DROP INDEX permissions.idx_ekpfilepermissionsobject
GO

IF EXISTS (SELECT Name FROM sysindexes WHERE Name = 'idx_permissions_count') 
			DROP INDEX permissions.idx_permissions_count
GO

ALTER TABLE permissions ALTER COLUMN objectid NVARCHAR(120) NOT NULL
GO

CREATE INDEX idx_ekpfilepermissionsusers ON [permissions]([objectid], [identityid], [objecttype])
GO

CREATE INDEX idx_ekpfilepermissionsobject ON [permissions]([objectid], [objecttype])
GO

CREATE INDEX idx_permissions_count ON [permissions]([objecttype], [identitytype], [identityid], [objectid], [permissioncode], [count])
GO

ALTER TABLE permissions ADD CONSTRAINT PK_PERMISSION PRIMARY KEY (objectid, objecttype, identityid, permissioncode, identitytype, is_pts_objecttype)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151102-js-1', 'janice.sung', 'nschema/112.changeLog.xml', GETDATE(), 522, '8:ac565cc40131c286715622f973b0f958', 'dropPrimaryKey constraintName=PK_PERMISSION, tableName=permissions; sql; sql; sql; sql; addPrimaryKey constraintName=PK_PERMISSION, tableName=permissions', 'Change the column size of objectid to keep the primary key column size less than 900 for mssql', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151102-hf-6::hubert.fajarit
DROP VIEW userprefs
GO

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

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

ALTER TABLE users_assignment DROP COLUMN block_notification
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151102-hf-6', 'hubert.fajarit', 'nschema/112.changeLog.xml', GETDATE(), 523, '8:dd7502c1b4b4c8e6508fc0c4e6ee9d31', 'dropView viewName=userprefs; dropDefaultValue columnName=block_notification, tableName=users_assignment; dropColumn columnName=block_notification, tableName=users_assignment', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/112.changeLog.xml::20151102-hf-7::hubert.fajarit
ALTER TABLE users_meta ADD block_notification char(1) CONSTRAINT DF_users_meta_block_notification DEFAULT 'N' NOT NULL
GO

CREATE VIEW userprefs AS select userprefs_assignment.userid, 
	               userprefs_assignment.contentServer,
	               userprefs_assignment.ui, 
	               users_meta.languagepref,
	               userprefs_assignment.autoMailList,
	               userprefs_assignment.autoRegMsg,
	               users_meta.useExternalMail,
	               userprefs_assignment.initialURL,
	               userprefs_assignment.resultsPerPage,
	               userprefs_assignment.catalogid,
	               userprefs_assignment.userPref1, 
	               userprefs_assignment.userPref2, 
	               userprefs_assignment.time_zone, 
	               users_meta.email_forward_type,
	               users_meta.forward_to_email, 
	               userprefs_assignment.welcome_email_senddate,
	               users_meta.block_notification
	        from userprefs_assignment
	        JOIN users_assignment on userprefs_assignment.userid = users_assignment.userid
	        JOIN users_meta on users_assignment.user_meta_id = users_meta.user_meta_id
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151102-hf-7', 'hubert.fajarit', 'nschema/112.changeLog.xml', GETDATE(), 524, '8:55193de5fc178f25ca685597b2430a47', 'addColumn tableName=users_meta; createView viewName=userprefs', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::26Oct2015-sg-1::shravan.gurrala
UPDATE ekpversion SET dblevel = 1121, splevel = 0
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('26Oct2015-sg-1', 'shravan.gurrala', 'nschema/1121.changeLog.xml', GETDATE(), 525, '8:828bc299b256b211f01c18acc18adace', 'update tableName=ekpversion', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151028-ca-1::chris.au
-- [DEV 8636] Deadlines Per Program Module
CREATE TABLE submod_tran_deadline (id nvarchar(85) NOT NULL, master_transcriptid nvarchar(15) NOT NULL, program_id nvarchar(85) NOT NULL, program_session_id nvarchar(15) NOT NULL, submod_completion_deadline datetime)
GO

DECLARE @DynamicSchemaName SYSNAME set @DynamicSchemaName = SCHEMA_NAME();
DECLARE @TableName SYSNAME set @TableName = N'submod_tran_deadline'; DECLARE @FullTableName SYSNAME; SET @FullTableName = @DynamicSchemaName + N'.submod_tran_deadline';DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Stores program module deadline of master 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.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'submod_tran_deadline'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.submod_tran_deadline'; 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'submod_tran_deadline'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.submod_tran_deadline'; DECLARE @ColumnName SYSNAME set @ColumnName = N'master_transcriptid'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Master transcript id for shared 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'submod_tran_deadline'; DECLARE @FullTableName SYSNAME set @FullTableName = @DynamicSchemaName + N'.submod_tran_deadline'; DECLARE @ColumnName SYSNAME set @ColumnName = N'program_id'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Learning id of parent program session';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END
GO

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

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

ALTER TABLE submod_tran_deadline ADD CONSTRAINT PK_SUBMOD_TRAN_DEADLINE PRIMARY KEY (id)
GO

ALTER TABLE submod_tran_deadline ADD CONSTRAINT UK_SUBMOD_TRAN_DEADLINE UNIQUE (master_transcriptid, program_id, program_session_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151028-ca-1', 'chris.au', 'nschema/1121.changeLog.xml', GETDATE(), 526, '8:459fa5d665edd6d5aa9d0e30d2f5d5fd', 'createTable tableName=submod_tran_deadline; addPrimaryKey constraintName=PK_SUBMOD_TRAN_DEADLINE, tableName=submod_tran_deadline; addUniqueConstraint constraintName=UK_SUBMOD_TRAN_DEADLINE, tableName=submod_tran_deadline', '[DEV 8636] Deadlines Per Program Module', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151028-ca-2::chris.au
-- [DEV 8636] Deadlines Per Program Module - config
ALTER TABLE currcourses ADD complete_by_date datetime
GO

ALTER TABLE currcourses ADD complete_in_days int
GO

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

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151028-ca-2', 'chris.au', 'nschema/1121.changeLog.xml', GETDATE(), 527, '8:9ce85eac61ae77d6032a04d317ea3617', 'addColumn tableName=currcourses', '[DEV 8636] Deadlines Per Program Module - config', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151104-ca-1::chris.au
-- [DEV 8636] Deadlines Per Program Module - add column for cache table
ALTER TABLE current_progress_status ADD program_module_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 @ColumnName SYSNAME set @ColumnName = N'program_module_duedate'; DECLARE @MS_DescriptionValue NVARCHAR(3749); SET @MS_DescriptionValue = N'Program Module Deadline in program context';DECLARE @MS_Description NVARCHAR(3749) set @MS_Description = NULL; SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(3749)) AS [MS_Description] FROM sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(@FullTableName) AND ep.minor_id=COLUMNPROPERTY(ep.major_id, @ColumnName, 'ColumnId') AND ep.name = N'MS_Description'); IF @MS_Description IS NULL BEGIN EXEC sys.sp_addextendedproperty @name  = N'MS_Description', @value = @MS_DescriptionValue, @level0type = N'SCHEMA', @level0name = @DynamicSchemaName, @level1type = N'TABLE', @level1name = @TableName, @level2type = N'COLUMN', @level2name = @ColumnName; END ELSE BEGIN EXEC sys.sp_updateextendedproperty @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 ('20151104-ca-1', 'chris.au', 'nschema/1121.changeLog.xml', GETDATE(), 528, '8:f5af8472921787bdfdee947e17a22395', 'addColumn tableName=current_progress_status', '[DEV 8636] Deadlines Per Program Module - add column for cache table', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151105-ca-2::chris.au
-- [DEV 8636] Deadlines Per Program Module - data migration
delete from submod_tran_deadline
GO

insert into submod_tran_deadline (id, master_transcriptid, program_id, program_session_id)
			select CAST(ROW_NUMBER() OVER (ORDER BY u.master_transcriptid) AS VARCHAR) as rownum, u.master_transcriptid, u.currid, u.sessionid from 
			(
				select transcriptid as master_transcriptid, currid, sessionid from transcript
				where creditTranscriptLink is null and transcriptid != curriculumTranscriptLink and currid is not null and sessionid is not null
				union
				select creditTranscriptLink as master_transcriptid, currid, sessionid from transcript 
				where creditTranscriptLink is not null and transcriptid != curriculumTranscriptLink and currid is not null and sessionid is not null
			) as u
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151105-ca-2', 'chris.au', 'nschema/1121.changeLog.xml', GETDATE(), 529, '8:abc12bd9919ceb97d563cfa4e2b3dd4e', 'sql; sql; sql; sql; sql', '[DEV 8636] Deadlines Per Program Module - data migration', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151105-dc-1::dennis.cheung
-- [DEV 9884] User Targeting Template Performance Optimization
DROP INDEX NUK_PERMISSION_MAPPING ON permission_mapping
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151105-dc-1', 'dennis.cheung', 'nschema/1121.changeLog.xml', GETDATE(), 530, '8:e6620d46cbe6a525504967483fd73b44', 'dropIndex indexName=NUK_PERMISSION_MAPPING, tableName=permission_mapping', '[DEV 9884] User Targeting Template Performance Optimization', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151105-dc-2::dennis.cheung
-- [DEV 9884] User Targeting Template Performance Optimization
ALTER TABLE permission_mapping DROP CONSTRAINT PK_PERMISSION_MAPPING
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151105-dc-2', 'dennis.cheung', 'nschema/1121.changeLog.xml', GETDATE(), 531, '8:7579908c7bade0a23eaf06ec4ec8491e', 'dropPrimaryKey constraintName=PK_PERMISSION_MAPPING, tableName=permission_mapping', '[DEV 9884] User Targeting Template Performance Optimization', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151105-dc-3::dennis.cheung
-- [DEV 9884] User Targeting Template Performance Optimization
DELETE FROM permission_mapping
GO

ALTER TABLE permission_mapping ALTER COLUMN objectid NVARCHAR(120) NOT NULL
GO

ALTER TABLE permission_mapping ALTER COLUMN resolved_objectid NVARCHAR(120) NOT NULL
GO

ALTER TABLE permission_mapping ADD CONSTRAINT PK_PERMISSION_MAPPING PRIMARY KEY (objecttype, permissioncode, objectid)
GO

CREATE NONCLUSTERED INDEX NUK_PERMISSION_MAPPING ON permission_mapping(resolved_objectid)
GO

INSERT INTO permission_mapping(objectid, objecttype, permissioncode, resolved_objectid, is_pts_objecttype)
        	SELECT distinct objectid, objecttype, permissioncode, objectid, 'N' FROM permissions
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151105-dc-3', 'dennis.cheung', 'nschema/1121.changeLog.xml', GETDATE(), 532, '8:922f6b296e5f7d4a2237153955416e05', 'sql; sql; sql; sql; sql; addPrimaryKey constraintName=PK_PERMISSION_MAPPING, tableName=permission_mapping; createIndex indexName=NUK_PERMISSION_MAPPING, tableName=permission_mapping; sql', '[DEV 9884] User Targeting Template Performance Optimization', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151111-ca-1::chris.au
-- [DEV 8849] CERTML9 - PDF Template Configurable at the Bundle Level - data migration
insert into resource_string
				(id, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select CAST(ROW_NUMBER() OVER (ORDER BY a.bundle_id) AS VARCHAR) + '_CR_TEMPLATEID' as rownum,
				   a.bundle_id, 
				   cert.templateid, 
				   'CR_TEMPLATEID', 
				   cert.certid 
			from language_bundle a
			inner join certifications cert
			on a.object_id = cert.certid
			where a.object_type = 'CR' and a.is_primary = 'Y'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151111-ca-1', 'chris.au', 'nschema/1121.changeLog.xml', GETDATE(), 533, '8:d7464392711423600531cb566e0657cd', 'sql; sql; sql; sql', '[DEV 8849] CERTML9 - PDF Template Configurable at the Bundle Level - data migration', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151113-kh-1::kit.ho
-- Add Columns for better performance of permission checking
IF EXISTS (
			SELECT 1 FROM sys.objects WHERE type = 'TR' AND name =
			'i_permission_template'
			)
			DROP TRIGGER i_permission_template
GO

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

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

ALTER TABLE permission_template ADD is_auto_generated char(1) CONSTRAINT DF_permission_template_is_auto_generated DEFAULT 'N' NOT NULL
GO

ALTER TABLE permission_template ADD criteria_hash int
GO

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

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

CREATE NONCLUSTERED INDEX NUK_PERMISSION_TEMPLATE_HASH ON permission_template(criteria_hash)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151113-kh-1', 'kit.ho', 'nschema/1121.changeLog.xml', GETDATE(), 534, '8:2c21a1d53304c219730ffd966e5385d7', 'sql; sql; sql; addColumn tableName=permission_template; createIndex indexName=NUK_PERMISSION_TEMPLATE_HASH, tableName=permission_template', 'Add Columns for better performance of permission checking', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151118-sn-1::sam.ng
ALTER TABLE permission_template DROP CONSTRAINT UK_PERMISSION_TEMPLATE
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151118-sn-1', 'sam.ng', 'nschema/1121.changeLog.xml', GETDATE(), 535, '8:ce453317fbb8b3fc95f2bd63dd3e7baf', 'dropUniqueConstraint constraintName=UK_PERMISSION_TEMPLATE, tableName=permission_template', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20150819-sn-1::sam.ng
DROP INDEX nuk_objtype_perm_permissions ON permissions
GO

CREATE NONCLUSTERED INDEX nuk_objtype_perm_permissions ON permissions(objecttype, permissioncode, is_pts_objecttype)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20150819-sn-1', 'sam.ng', 'nschema/1121.changeLog.xml', GETDATE(), 536, '8:8305d09b3b563e469fd21405b1264e73', 'dropIndex indexName=nuk_objtype_perm_permissions, tableName=permissions; createIndex indexName=nuk_objtype_perm_permissions, tableName=permissions', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151201-sn-1::sam.ng
DROP INDEX NUK_PERMISSION_MAPPING ON permission_mapping
GO

CREATE NONCLUSTERED INDEX NUK_PERMISSION_MAPPING ON permission_mapping(resolved_objectid, objecttype, permissioncode, is_pts_objecttype)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151201-sn-1', 'sam.ng', 'nschema/1121.changeLog.xml', GETDATE(), 537, '8:26568b6ce03b08ea1e7a9dbab1f5047e', 'dropIndex indexName=NUK_PERMISSION_MAPPING, tableName=permission_mapping; createIndex indexName=NUK_PERMISSION_MAPPING, tableName=permission_mapping', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151202-nl-1::nick.lee
-- [DEV 10171] Add Duration Comments to language bundles - data migration
INSERT INTO resource_string 
				(id, 
				bundle_id, 
				value_varchar, 
				reference_id, 
				field)
			SELECT CAST(ROW_NUMBER() OVER (ORDER BY lb.bundle_id) AS VARCHAR) + '_LO_DURATION_COMMENTS' as rownum, 
				lb.bundle_id, 
				lo.duration, 
				lo.learningid, 
				'LO_DURATION_COMMENTS'
			FROM learningObject lo 
			JOIN language_bundle lb ON lb.object_type='LO' AND lb.is_primary='Y' AND lb.object_id=lo.learningid
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151202-nl-1', 'nick.lee', 'nschema/1121.changeLog.xml', GETDATE(), 538, '8:9d997c6f22afd0ed83cfcc538cf6b620', 'sql; sql; sql; sql', '[DEV 10171] Add Duration Comments to language bundles - data migration', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151208-dc-1::dennis.cheung
-- Add program_
ALTER TABLE curriculum ADD progress_tracker char(1)
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151208-dc-1', 'dennis.cheung', 'nschema/1121.changeLog.xml', GETDATE(), 539, '8:ae80782d71b5e2e6967c4da2776fbfa1', 'addColumn tableName=curriculum', 'Add program_', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151011-rr-1::ray.ruff
-- New column for Vendor table
ALTER TABLE code_vendorname ADD vendor_ref_code nvarchar(100)
GO

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151011-rr-1', 'ray.ruff', 'nschema/1121.changeLog.xml', GETDATE(), 540, '8:08133dba96c81754220bd68a336f8a98', 'addColumn tableName=code_vendorname', 'New column for Vendor table', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/1121.changeLog.xml::20151011-rr-2::ray.ruff
-- Initial value for vendor_ref_code
update code_vendorname
            set vendor_ref_code = vendorname
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151011-rr-2', 'ray.ruff', 'nschema/1121.changeLog.xml', GETDATE(), 541, '8:0584ec359f3477481f572b9fa0060109', 'sql', 'Initial value for vendor_ref_code', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

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

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

-- Changeset nschema/120.changeLog.xml::20151215-kk-1::kumar.khiani
-- [PD-2881] Language bundle data migration for Competency
-- Language bundle creation for each Competency
-- Title column
-- Description column
insert into language_bundle
				(bundle_id,
				object_id,
				object_type,
				locale,
				is_primary)
			select
				CAST(ROW_NUMBER() OVER (ORDER BY id) AS VARCHAR) + 'COMP' as rownum,
				id,
				'COMP',
				'*ALL*',
				'Y'
			from individual_competency
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_TITLE',
				null,
				b.bundle_id,
				name,
				'COMP_TITLE',
				id
			from
				individual_competency t
			join
				language_bundle b on t.id = b.object_id and b.object_type = 'COMP'
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_ntext, 
				field, 
				reference_id)
			select
				b.bundle_id + '_DESCRIPTION',
				null,
				b.bundle_id,
				description,
				'COMP_DESCRIPTION',
				id
			from
				individual_competency t
			join
				language_bundle b on t.id = b.object_id and b.object_type = 'COMP'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151215-kk-1', 'kumar.khiani', 'nschema/120.changeLog.xml', GETDATE(), 543, '8:986a2620438906d26fd046796922a6b9', 'sql; sql; sql; sql; sql; sql; sql', '[PD-2881] Language bundle data migration for Competency
Language bundle creation for each Competency
Title column
Description column', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/120.changeLog.xml::20151216-wc-1::wilson.choi
-- [PD-2320] EML2A - Cover Sheet for Exam Bundle - data migration
insert into resource_string
				(id, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select cast(row_number() OVER (ORDER BY a.bundle_id) AS VARCHAR) + '_TS_COVERSHEET_ID' as rownum, 
				   a.bundle_id, 
				   testprint.coverSheetId, 
				   'TS_COVERSHEET_ID', 
				   testprint.testid 
			from language_bundle a
			inner join mastertest_print testprint
			on a.object_id = testprint.testid
			where a.object_type = 'TS' and a.is_primary = 'Y'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151216-wc-1', 'wilson.choi', 'nschema/120.changeLog.xml', GETDATE(), 544, '8:fcad9a45787937719489cfc4e69c4d3b', 'sql; sql; sql; sql', '[PD-2320] EML2A - Cover Sheet for Exam Bundle - data migration', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/120.changeLog.xml::20151223-wp-1::william.puk
-- [PD-2923] Language Bundle Data Migration for Job Profile
-- Language bundle creation for each Job Profile
-- Name column
-- Description column
insert into language_bundle
				(bundle_id,
				object_id,
				object_type,
				locale,
				is_primary)
			select
				CAST(ROW_NUMBER() OVER (ORDER BY id) AS VARCHAR) + 'JPF' as rownum,
				id,
				'JPF',
				'*ALL*',
				'Y'
			from job_profile
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_NAME',
				null,
				b.bundle_id,
				name,
				'JPF_NAME',
				id
			from
				job_profile jp
			join
				language_bundle b on jp.id = b.object_id and b.object_type = 'JPF'
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_ntext, 
				field, 
				reference_id)
			select
				b.bundle_id + '_DESCRIPTION',
				null,
				b.bundle_id,
				description,
				'JPF_DESCRIPTION',
				id
			from
				job_profile jp
			join
				language_bundle b on jp.id = b.object_id and b.object_type = 'JPF'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151223-wp-1', 'william.puk', 'nschema/120.changeLog.xml', GETDATE(), 545, '8:c0181054175c6bac3c5e3a0eb10b7c46', 'sql; sql; sql; sql; sql; sql; sql', '[PD-2923] Language Bundle Data Migration for Job Profile
Language bundle creation for each Job Profile
Name column
Description column', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/120.changeLog.xml::20160122-yc-1::yanfang.cui
IF EXISTS (
			SELECT *
			FROM sys.objects
			WHERE type = 'TR' AND name =
			'i_competency_level'
			)
			DROP TRIGGER i_competency_level
GO

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

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

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160122-yc-1', 'yanfang.cui', 'nschema/120.changeLog.xml', GETDATE(), 546, '8:9df8ee3a7096700f283e775481d049ae', 'sql; sql; sql', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/120.changeLog.xml::20151228-mw-1::martin.wang
-- [PD-2588] COMPML3 - Proficiency Level Title Field in Competency
ALTER TABLE competency_level ADD title nvarchar(255)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151228-mw-1', 'martin.wang', 'nschema/120.changeLog.xml', GETDATE(), 547, '8:adc7de55bf165c0cdc85317d990d134f', 'addColumn tableName=competency_level', '[PD-2588] COMPML3 - Proficiency Level Title Field in Competency', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/120.changeLog.xml::20151228-mw-2::martin.wang
-- [PD-2588] COMPML3 - Proficiency Level Title Field in Competency - data migration
update competency_level set title=(select title from tms_responsibility_level trl where trl.id=competency_level.res_id)
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20151228-mw-2', 'martin.wang', 'nschema/120.changeLog.xml', GETDATE(), 548, '8:0e549945050780a9c7ade0c7d1d48551', 'sql', '[PD-2588] COMPML3 - Proficiency Level Title Field in Competency - data migration', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

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

INSERT INTO permission_mapping (objectid, objecttype, permissioncode, resolved_objectid, is_pts_objecttype) VALUES ('R420', 'RT', 1, 'R420', 'N')
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160107-jw-1', 'joe.wong', 'nschema/120.changeLog.xml', GETDATE(), 549, '8:a43a15d8fccc9b863008e4ef7920d0ef', 'insert tableName=permissions; insert tableName=permission_mapping', '', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/120.changeLog.xml::20160115-vn-1::vincent.ni
-- [PD-2881] COMPML3A - Additional Competency Metadata Fields
-- Proficiency Level data migration for Competency Language Bundle
-- Proficiency Level Title
-- Proficiency Level Description
insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_varchar, 
				field, 
				reference_id)
			select
				b.bundle_id + '_PLEVEL_TITLE' + CAST(l.res_id AS VARCHAR),
				null,
				b.bundle_id,
				l.title,
				'COMP_PLEVEL_TITLE',
				l.id
			from
				competency_level l
			left join
				individual_competency t on t.id = l.competency_id
			left join
				language_bundle b on t.id = b.object_id and b.object_type = 'COMP' and b.is_primary = 'Y'
GO

insert into resource_string
				(id, 
				resource_key, 
				bundle_id, 
				value_ntext, 
				field, 
				reference_id)
			select
				b.bundle_id + '_PLEVEL_DESCRIPTION_' + CAST(l.res_id AS VARCHAR),
				null,
				b.bundle_id,
				l.desc_text,
				'COMP_PLEVEL_DESCRIPTION',
				l.id
			from
				competency_level l
			left join
				individual_competency t on t.id = l.competency_id
			left join
				language_bundle b on t.id = b.object_id and b.object_type = 'COMP' and b.is_primary = 'Y'
GO

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('20160115-vn-1', 'vincent.ni', 'nschema/120.changeLog.xml', GETDATE(), 550, '8:f98b794eaf2e2836d5188575a3a7bc09', 'sql; sql; sql; sql', '[PD-2881] COMPML3A - Additional Competency Metadata Fields
Proficiency Level data migration for Competency Language Bundle
Proficiency Level Title
Proficiency Level Description', 'EXECUTED', NULL, NULL, '4.8.0', '0667398387')
GO

-- Changeset nschema/120.changeLog.xml::20160118-wc-1::wilson.choi
