Octopus.Core.UpgradeScripts.Script0045 - MoveDeploymentChannelIdToColumn.sql

if not exists (select column_name from INFORMATION_SCHEMA.columns where TABLE_SCHEMA = 'dbo' and table_name = 'Deployment' and column_name = 'ChannelId')
BEGIN
	ALTER TABLE dbo.Deployment ADD ChannelId nvarchar(50) NULL 
END
GO
​
-- Update column from json blob. Try to extract rather than joining on Release since it may have changed.
UPDATE dbo.Deployment
SET ChannelId = c.Id
FROM dbo.Deployment d INNER JOIN dbo.Channel c ON d.[JSON] LIKE '%"ChannelID":"' + c.Id + '"%'
WHERE ChannelId IS NULL
GO
​
-- Any that are still empty, get channelId from the release
UPDATE dbo.Deployment
SET ChannelId = r.ChannelId
FROM dbo.Deployment d INNER JOIN dbo.Release r ON d.ReleaseId = r.Id
WHERE d.ChannelId IS NULL and r.ChannelId IS NOT NULL
GO
​
-- If still empty get the default for the project (have some cases where release is missing)
UPDATE dbo.Deployment
SET ChannelId = x.ChannelId
FROM dbo.Deployment d  cross apply (
select top 1 c.Id as ChannelId from project p inner join channel c on p.Id = c.projectId
 where p.Id = d.ProjectId
 order by 
	p.Id,
	case when c.json like '%IsDefault":true%' then 0 else 1 end) as x
WHERE d.ChannelId IS NULL and x.ChannelId IS NOT NULL
GO
​
IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='IX_Deployment_ChannelId' AND object_id = OBJECT_ID('[dbo].[Deployment]'))
begin
	DROP INDEX IX_Deployment_ChannelId ON dbo.Deployment
end
GO
​
-- And make channel non-nullable on Release
ALTER TABLE dbo.Deployment 
	ALTER COLUMN ChannelId nvarchar(50) NOT NULL
GO
​
CREATE INDEX IX_Deployment_ChannelId ON dbo.Deployment (ChannelId)
GO