8gDmqEE9

1if not exists (select column_name from INFORMATION_SCHEMA.columns where TABLE_SCHEMA = 'dbo' and table_name = 'Deployment' and column_name = 'ChannelId')
2BEGIN
3	ALTER TABLE dbo.Deployment ADD ChannelId nvarchar(50) NULL 
4END
5GO
6​
7-- Update column from json blob. Try to extract rather than joining on Release since it may have changed.
8UPDATE dbo.Deployment
9SET ChannelId = c.Id
10FROM dbo.Deployment d INNER JOIN dbo.Channel c ON d.[JSON] LIKE '%"ChannelID":"' + c.Id + '"%'
11WHERE ChannelId IS NULL
12GO
13​
14-- Any that are still empty, get channelId from the release
15UPDATE dbo.Deployment
16SET ChannelId = r.ChannelId
17FROM dbo.Deployment d INNER JOIN dbo.Release r ON d.ReleaseId = r.Id
18WHERE d.ChannelId IS NULL and r.ChannelId IS NOT NULL
19GO
20​
21-- If still empty get the default for the project (have some cases where release is missing)
22UPDATE dbo.Deployment
23SET ChannelId = x.ChannelId
24FROM dbo.Deployment d  cross apply (
25select top 1 c.Id as ChannelId from channel c 
26 where c.ProjectId = d.ProjectId
27 order by 
28	c.ProjectId,
29	case when c.json like '%IsDefault":true%' then 0 else 1 end) as x
30WHERE d.ChannelId IS NULL and x.ChannelId IS NOT NULL
31GO
32​
33IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='IX_Deployment_ChannelId' AND object_id = OBJECT_ID('[dbo].[Deployment]'))
34begin
35	DROP INDEX IX_Deployment_ChannelId ON dbo.Deployment
36end
37GO
38​
39-- And make channel non-nullable on Release
40ALTER TABLE dbo.Deployment 
41	ALTER COLUMN ChannelId nvarchar(50) NOT NULL
42GO
43​
44CREATE INDEX IX_Deployment_ChannelId ON dbo.Deployment (ChannelId)
45GO