· 5 years ago · Mar 18, 2020, 02:14 PM
1PRINT N'Defines column AutomationControllerId from T_AutomationWorkflow as nullable'
2GO
3
4DECLARE @PatchIdentifier NVARCHAR(50) = N'77XYZ831-E124-4E2B-B8BC-904DFCFB3KYC'
5DECLARE @PatchDescription NVARCHAR(255) = N'Defines column AutomationControllerId from T_AutomationWorkflow as nullable'
6DECLARE @BatchId UNIQUEIDENTIFIER = NEWID()
7DECLARE @ServiceHistoryId BIGINT = $(ServiceHistoryId)
8DECLARE @LastOperationHistorySequence BIGINT = (SELECT [dbo].[F_GetTicks] ())
9IF (NOT EXISTS(SELECT * FROM [Control].[T_DatabasePatches] WHERE PatchIdentifier = @PatchIdentifier))
10BEGIN
11
12 IF (exists(
13 SELECT * FROM sys.columns c
14 inner join sys.tables t ON (c.object_id = t.object_id)
15 WHERE c.name = 'AutomationControllerId' and t.name = 'T_AutomationWorkflow' and schema_name(t.schema_id) = 'CoreDataModel'
16 and c.is_nullable = 0))
17 BEGIN
18 EXEC('alter table CoreDataModel.T_AutomationWorkflow alter column [AutomationControllerId] bigint null');
19 END
20
21 INSERT INTO [Control].[T_DatabasePatches]
22 ([Application]
23 ,[PatchIdentifier]
24 ,[PatchDescription]
25 ,[PatchVersion]
26 ,[PatchDate]
27 ,[ServiceHistoryId])
28 VALUES
29 ('Core'
30 ,@PatchIdentifier
31 ,@PatchDescription
32 ,'$(TargetVersionSuffix)'
33 ,GETUTCDATE()
34 ,@ServiceHistoryId)
35
36END
37GO
38
39PRINT N'Creates the IoT File Storage Table and respective indexes'
40GO
41
42DECLARE @PatchIdentifier NVARCHAR(50) = N'284C17AA-D71E-412E-8561-B5CD59619C17'
43DECLARE @PatchDescription NVARCHAR(255) = N'Creates the IoT File Storage Table and respective indexes'
44DECLARE @BatchId UNIQUEIDENTIFIER = NEWID()
45DECLARE @ServiceHistoryId BIGINT = $(ServiceHistoryId)
46DECLARE @LastOperationHistorySequence BIGINT = (SELECT [dbo].[F_GetTicks] ())
47IF (NOT EXISTS(SELECT * FROM [Control].[T_DatabasePatches] WHERE PatchIdentifier = @PatchIdentifier))
48BEGIN
49
50 CREATE TABLE [Control].[T_IoTFileStorage](
51 [IoTFileStorageId] [bigint] IDENTITY(1,1) NOT NULL,
52 [OriginalFileName] [nvarchar](512) NOT NULL,
53 [FileGUID] [varchar](32) NOT NULL,
54 [CreatedOn] [datetime] NOT NULL,
55 [FileLocationURI] [nvarchar](1024) NOT NULL,
56 CONSTRAINT [PK_IoTFileStorage] PRIMARY KEY CLUSTERED
57 (
58 [IoTFileStorageId] ASC
59 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [FG_IntegrationTableDat]
60 ) ON [FG_IntegrationTableDat]
61
62 ALTER TABLE [Control].[T_IoTFileStorage] ADD DEFAULT (getutcdate()) FOR [CreatedOn]
63
64 CREATE NONCLUSTERED INDEX [Idx_IoTFileStorage_FileGUID] ON [Control].[T_IoTFileStorage]
65 (
66 [FileGUID] ASC
67 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [FG_IntegrationTableIdx]
68
69
70 INSERT INTO [Control].[T_DatabasePatches]
71 ([Application]
72 ,[PatchIdentifier]
73 ,[PatchDescription]
74 ,[PatchVersion]
75 ,[PatchDate]
76 ,[ServiceHistoryId])
77 VALUES
78 ('Core'
79 ,@PatchIdentifier
80 ,@PatchDescription
81 ,'$(TargetVersionSuffix)'
82 ,GETUTCDATE()
83 ,@ServiceHistoryId)
84
85END
86GO