Forked from Dalmirog-zz/Octopus.Core.UpgradeScripts.Script0045 - MoveDeploymentChannelIdToColumn.sql
Last active
September 9, 2016 02:22
-
-
Save markryd/0ef66e2c3de8d33d6433de631cf125ec to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 channel c | |
where c.ProjectId = d.ProjectId | |
order by | |
c.ProjectId, | |
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment