· 6 years ago · Dec 02, 2019, 10:36 AM
1SET NOCOUNT ONSET NOCOUNT ON
2
3SET XACT_ABORT ONSET XACT_ABORT ON
4
5DECLARE @TARGET_SCHEMA_VERSION INT;
6DECLARE @DISABLE_HEAVY_MIGRATIONS BIT;
7SET @TARGET_SCHEMA_VERSION = 7;SET @TARGET_SCHEMA_VERSION = 7
8
9SET @DISABLE_HEAVY_MIGRATIONS = 1;SET @DISABLE_HEAVY_MIGRATIONS = 1
10
11PRINT 'Installing Hangfire SQL objects...';
12BEGIN TRANSACTION;
13DECLARE @SchemaLockResult INT;
14EXEC @SchemaLockResult = sp_getapplock @Resource = 'HangFire:SchemaLock', @LockMode = 'Exclusive'
15
16
17IF NOT EXISTS (SELECT [schema_id] FROM [sys].[schemas] WHERE [name] = @_p2)
18BEGIN
19 EXEC (@_p3);
20 PRINT @_p4;
21END
22ELSE
23 PRINT @_p5;
24DECLARE @SCHEMA_ID int;
25SELECT @SCHEMA_ID = [schema_id] FROM [sys].[schemas] WHERE [name] = @_p6;
26create table [HangFire].[Schema]([Version] [int] NOT NULL,
27CONSTRAINT [PK_HangFire_Schema] PRIMARY KEY CLUSTERED ( [Version] ASC ))
28
29
30IF NOT EXISTS(SELECT [object_id] FROM [sys].[tables]
31 WHERE [name] = @_p7 AND [schema_id] = @SCHEMA_ID)
32BEGIN
33 CREATE TABLE [HangFire].[Schema](
34 [Version] [int] NOT NULL,
35 CONSTRAINT [PK_HangFire_Schema] PRIMARY KEY CLUSTERED ([Version] ASC)
36 );
37 PRINT @_p8;
38END
39ELSE
40 PRINT @_p9;
41DECLARE @CURRENT_SCHEMA_VERSION int;
42SELECT @CURRENT_SCHEMA_VERSION = [Version] FROM [HangFire].[Schema];
43PRINT @_p10 + CASE WHEN @CURRENT_SCHEMA_VERSION IS NULL THEN @_p11 ELSE CONVERT(nvarchar, @CURRENT_SCHEMA_VERSION) END;
44
45
46
47IF @CURRENT_SCHEMA_VERSION IS NOT NULL AND @CURRENT_SCHEMA_VERSION > @TARGET_SCHEMA_VERSION
48BEGIN
49 ROLLBACK TRANSACTION;
50 PRINT @_p12 + CAST(@CURRENT_SCHEMA_VERSION AS NVARCHAR) +
51 @_p13 + CAST(@TARGET_SCHEMA_VERSION AS NVARCHAR) +
52 @_p14;
53 RETURN;
54END
55SET @DISABLE_HEAVY_MIGRATIONS = @_p16
56
57
58
59
60create table [HangFire].[Job]([Id] [int] IDENTITY ( @_p19 , @_p20 ) NOT NULL,
61[StateId] [int] NULL,
62[StateName] [nvarchar](@_p21) NULL,
63[InvocationData] [nvarchar](max) NOT NULL,
64[Arguments] [nvarchar](max) NOT NULL,
65[CreatedAt] [datetime] NOT NULL,
66[ExpireAt] [datetime] NULL,
67CONSTRAINT [PK_HangFire_Job] PRIMARY KEY CLUSTERED ( [Id] ASC ))
68
69
70
71
72create table [HangFire].[State]([Id] [int] IDENTITY ( @_p24 , @_p25 ) NOT NULL,
73[JobId] [int] NOT NULL,
74[Name] [nvarchar](@_p26) NOT NULL,
75[Reason] [nvarchar](@_p27) NULL,
76[CreatedAt] [datetime] NOT NULL,
77[Data] [nvarchar](max) NULL,
78CONSTRAINT [PK_HangFire_State] PRIMARY KEY CLUSTERED ( [Id] ASC ))
79
80
81
82
83
84
85create table [HangFire].[JobParameter]([Id] [int] IDENTITY ( @_p31 , @_p32 ) NOT NULL,
86[JobId] [int] NOT NULL,
87[Name] [nvarchar](@_p33) NOT NULL,
88[Value] [nvarchar](max) NULL,
89CONSTRAINT [PK_HangFire_JobParameter] PRIMARY KEY CLUSTERED ( [Id] ASC ))
90
91
92
93
94
95
96create table [HangFire].[JobQueue]([Id] [int] IDENTITY ( @_p37 , @_p38 ) NOT NULL,
97[JobId] [int] NOT NULL,
98[Queue] [nvarchar](@_p39) NOT NULL,
99[FetchedAt] [datetime] NULL,
100CONSTRAINT [PK_HangFire_JobQueue] PRIMARY KEY CLUSTERED ( [Id] ASC ))
101
102
103
104
105
106
107create table [HangFire].[Server]([Id] [nvarchar](@_p43) NOT NULL,
108[Data] [nvarchar](max) NULL,
109[LastHeartbeat] [datetime] NULL,
110CONSTRAINT [PK_HangFire_Server] PRIMARY KEY CLUSTERED ( [Id] ASC ))
111
112
113create table [HangFire].[Hash]([Id] [int] IDENTITY ( @_p45 , @_p46 ) NOT NULL,
114[Key] [nvarchar](@_p47) NOT NULL,
115[Name] [nvarchar](@_p48) NOT NULL,
116[StringValue] [nvarchar](max) NULL,
117[IntValue] [int] NULL,
118[ExpireAt] [datetime] NULL,
119CONSTRAINT [PK_HangFire_Hash] PRIMARY KEY CLUSTERED ( [Id] ASC ))
120
121
122
123
124create table [HangFire].[List]([Id] [int] IDENTITY ( @_p51 , @_p52 ) NOT NULL,
125[Key] [nvarchar](@_p53) NOT NULL,
126[Value] [nvarchar](max) NULL,
127[ExpireAt] [datetime] NULL,
128CONSTRAINT [PK_HangFire_List] PRIMARY KEY CLUSTERED ( [Id] ASC ))
129
130
131create table [HangFire].[Set]([Id] [int] IDENTITY ( @_p55 , @_p56 ) NOT NULL,
132[Key] [nvarchar](@_p57) NOT NULL,
133[Score] [float] NOT NULL,
134[Value] [nvarchar](@_p58) NOT NULL,
135[ExpireAt] [datetime] NULL,
136CONSTRAINT [PK_HangFire_Set] PRIMARY KEY CLUSTERED ( [Id] ASC ))
137
138
139
140
141create table [HangFire].[Value]([Id] [int] IDENTITY ( @_p61 , @_p62 ) NOT NULL,
142[Key] [nvarchar](@_p63) NOT NULL,
143[StringValue] [nvarchar](max) NULL,
144[IntValue] [int] NULL,
145[ExpireAt] [datetime] NULL,
146CONSTRAINT [PK_HangFire_Value] PRIMARY KEY CLUSTERED ( [Id] ASC ))
147
148
149
150
151create table [HangFire].[Counter]([Id] [int] IDENTITY ( @_p66 , @_p67 ) NOT NULL,
152[Key] [nvarchar](@_p68) NOT NULL,
153[Value] [tinyint] NOT NULL,
154[ExpireAt] [datetime] NULL,
155CONSTRAINT [PK_HangFire_Counter] PRIMARY KEY CLUSTERED ( [Id] ASC ))
156
157
158
159
160SET @CURRENT_SCHEMA_VERSION = @_p71
161
162IF @CURRENT_SCHEMA_VERSION IS NULL
163BEGIN
164 IF @DISABLE_HEAVY_MIGRATIONS = @_p15
165 BEGIN
166 SET @DISABLE_HEAVY_MIGRATIONS = @_p16;
167 PRINT @_p17;
168 END
169
170 PRINT @_p18;
171
172 -- Create job tables
173 CREATE TABLE [HangFire].[Job] (
174 [Id] [int] IDENTITY(@_p19,@_p20) NOT NULL,
175 [StateId] [int] NULL,
176 [StateName] [nvarchar](@_p21) NULL, -- To speed-up queries.
177 [InvocationData] [nvarchar](max) NOT NULL,
178 [Arguments] [nvarchar](max) NOT NULL,
179 [CreatedAt] [datetime] NOT NULL,
180 [ExpireAt] [datetime] NULL,
181
182 CONSTRAINT [PK_HangFire_Job] PRIMARY KEY CLUSTERED ([Id] ASC)
183 );
184 PRINT @_p22;
185
186 CREATE NONCLUSTERED INDEX [IX_HangFire_Job_StateName] ON [HangFire].[Job] ([StateName] ASC);
187 PRINT @_p23;
188
189 -- Job history table
190
191 CREATE TABLE [HangFire].[State] (
192 [Id] [int] IDENTITY(@_p24,@_p25) NOT NULL,
193 [JobId] [int] NOT NULL,
194 [Name] [nvarchar](@_p26) NOT NULL,
195 [Reason] [nvarchar](@_p27) NULL,
196 [CreatedAt] [datetime] NOT NULL,
197 [Data] [nvarchar](max) NULL,
198
199 CONSTRAINT [PK_HangFire_State] PRIMARY KEY CLUSTERED ([Id] ASC)
200 );
201 PRINT @_p28;
202
203 ALTER TABLE [HangFire].[State] ADD CONSTRAINT [FK_HangFire_State_Job] FOREIGN KEY([JobId])
204 REFERENCES [HangFire].[Job] ([Id])
205 ON UPDATE CASCADE
206 ON DELETE CASCADE;
207 PRINT @_p29;
208
209 CREATE NONCLUSTERED INDEX [IX_HangFire_State_JobId] ON [HangFire].[State] ([JobId] ASC);
210 PRINT @_p30;
211
212 -- Job parameters table
213
214 CREATE TABLE [HangFire].[JobParameter](
215 [Id] [int] IDENTITY(@_p31,@_p32) NOT NULL,
216 [JobId] [int] NOT NULL,
217 [Name] [nvarchar](@_p33) NOT NULL,
218 [Value] [nvarchar](max) NULL,
219
220 CONSTRAINT [PK_HangFire_JobParameter] PRIMARY KEY CLUSTERED ([Id] ASC)
221 );
222 PRINT @_p34;
223
224 ALTER TABLE [HangFire].[JobParameter] ADD CONSTRAINT [FK_HangFire_JobParameter_Job] FOREIGN KEY([JobId])
225 REFERENCES [HangFire].[Job] ([Id])
226 ON UPDATE CASCADE
227 ON DELETE CASCADE;
228 PRINT @_p35;
229
230 CREATE NONCLUSTERED INDEX [IX_HangFire_JobParameter_JobIdAndName] ON [HangFire].[JobParameter] (
231 [JobId] ASC,
232 [Name] ASC
233 );
234 PRINT @_p36;
235
236 -- Job queue table
237
238 CREATE TABLE [HangFire].[JobQueue](
239 [Id] [int] IDENTITY(@_p37,@_p38) NOT NULL,
240 [JobId] [int] NOT NULL,
241 [Queue] [nvarchar](@_p39) NOT NULL,
242 [FetchedAt] [datetime] NULL,
243
244 CONSTRAINT [PK_HangFire_JobQueue] PRIMARY KEY CLUSTERED ([Id] ASC)
245 );
246 PRINT @_p40;
247
248 CREATE NONCLUSTERED INDEX [IX_HangFire_JobQueue_JobIdAndQueue] ON [HangFire].[JobQueue] (
249 [JobId] ASC,
250 [Queue] ASC
251 );
252 PRINT @_p41;
253
254 CREATE NONCLUSTERED INDEX [IX_HangFire_JobQueue_QueueAndFetchedAt] ON [HangFire].[JobQueue] (
255 [Queue] ASC,
256 [FetchedAt] ASC
257 );
258 PRINT @_p42;
259
260 -- Servers table
261
262 CREATE TABLE [HangFire].[Server](
263 [Id] [nvarchar](@_p43) NOT NULL,
264 [Data] [nvarchar](max) NULL,
265 [LastHeartbeat] [datetime] NULL,
266
267 CONSTRAINT [PK_HangFire_Server] PRIMARY KEY CLUSTERED ([Id] ASC)
268 );
269 PRINT @_p44;
270
271 -- Extension tables
272
273 CREATE TABLE [HangFire].[Hash](
274 [Id] [int] IDENTITY(@_p45,@_p46) NOT NULL,
275 [Key] [nvarchar](@_p47) NOT NULL,
276 [Name] [nvarchar](@_p48) NOT NULL,
277 [StringValue] [nvarchar](max) NULL,
278 [IntValue] [int] NULL,
279 [ExpireAt] [datetime] NULL,
280
281 CONSTRAINT [PK_HangFire_Hash] PRIMARY KEY CLUSTERED ([Id] ASC)
282 );
283 PRINT @_p49;
284
285 CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_Hash_KeyAndName] ON [HangFire].[Hash] (
286 [Key] ASC,
287 [Name] ASC
288 );
289 PRINT @_p50;
290
291 CREATE TABLE [HangFire].[List](
292 [Id] [int] IDENTITY(@_p51,@_p52) NOT NULL,
293 [Key] [nvarchar](@_p53) NOT NULL,
294 [Value] [nvarchar](max) NULL,
295 [ExpireAt] [datetime] NULL,
296
297 CONSTRAINT [PK_HangFire_List] PRIMARY KEY CLUSTERED ([Id] ASC)
298 );
299 PRINT @_p54;
300
301 CREATE TABLE [HangFire].[Set](
302 [Id] [int] IDENTITY(@_p55,@_p56) NOT NULL,
303 [Key] [nvarchar](@_p57) NOT NULL,
304 [Score] [float] NOT NULL,
305 [Value] [nvarchar](@_p58) NOT NULL,
306 [ExpireAt] [datetime] NULL,
307
308 CONSTRAINT [PK_HangFire_Set] PRIMARY KEY CLUSTERED ([Id] ASC)
309 );
310 PRINT @_p59;
311
312 CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_Set_KeyAndValue] ON [HangFire].[Set] (
313 [Key] ASC,
314 [Value] ASC
315 );
316 PRINT @_p60;
317
318 CREATE TABLE [HangFire].[Value](
319 [Id] [int] IDENTITY(@_p61,@_p62) NOT NULL,
320 [Key] [nvarchar](@_p63) NOT NULL,
321 [StringValue] [nvarchar](max) NULL,
322 [IntValue] [int] NULL,
323 [ExpireAt] [datetime] NULL,
324
325 CONSTRAINT [PK_HangFire_Value] PRIMARY KEY CLUSTERED (
326 [Id] ASC
327 )
328 );
329 PRINT @_p64;
330
331 CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_Value_Key] ON [HangFire].[Value] (
332 [Key] ASC
333 );
334 PRINT @_p65;
335
336 CREATE TABLE [HangFire].[Counter](
337 [Id] [int] IDENTITY(@_p66,@_p67) NOT NULL,
338 [Key] [nvarchar](@_p68) NOT NULL,
339 [Value] [tinyint] NOT NULL,
340 [ExpireAt] [datetime] NULL,
341
342 CONSTRAINT [PK_HangFire_Counter] PRIMARY KEY CLUSTERED ([Id] ASC)
343 );
344 PRINT @_p69;
345
346 CREATE NONCLUSTERED INDEX [IX_HangFire_Counter_Key] ON [HangFire].[Counter] ([Key] ASC)
347 INCLUDE ([Value]);
348 PRINT @_p70;
349
350 SET @CURRENT_SCHEMA_VERSION = @_p71;
351END
352
353
354
355
356
357DROP TABLE [HangFire].[Value]
358
359DROP TABLE [HangFire].[Hash]
360
361
362
363
364SET @CURRENT_SCHEMA_VERSION = @_p76
365
366IF @CURRENT_SCHEMA_VERSION = @_p72
367BEGIN
368 PRINT @_p73;
369
370 -- https://github.com/odinserj/HangFire/issues/83
371
372 DROP INDEX [IX_HangFire_Counter_Key] ON [HangFire].[Counter];
373
374 ALTER TABLE [HangFire].[Counter] ALTER COLUMN [Value] SMALLINT NOT NULL;
375
376 CREATE NONCLUSTERED INDEX [IX_HangFire_Counter_Key] ON [HangFire].[Counter] ([Key] ASC)
377 INCLUDE ([Value]);
378 PRINT @_p74;
379
380 DROP TABLE [HangFire].[Value];
381 DROP TABLE [HangFire].[Hash];
382 PRINT @_p75
383
384 DELETE FROM [HangFire].[Server] WHERE [LastHeartbeat] IS NULL;
385 ALTER TABLE [HangFire].[Server] ALTER COLUMN [LastHeartbeat] DATETIME NOT NULL;
386
387 SET @CURRENT_SCHEMA_VERSION = @_p76;
388END
389
390
391
392create table [HangFire].[Hash]([Id] [int] IDENTITY ( @_p80 , @_p81 ) NOT NULL,
393[Key] [nvarchar](@_p82) NOT NULL,
394[Field] [nvarchar](@_p83) NOT NULL,
395[Value] [nvarchar](max) NULL,
396[ExpireAt] [datetime2](@_p84) NULL,
397CONSTRAINT [PK_HangFire_Hash] PRIMARY KEY CLUSTERED ( [Id] ASC ))
398
399
400
401
402SET @CURRENT_SCHEMA_VERSION = @_p87
403
404IF @CURRENT_SCHEMA_VERSION = @_p77
405BEGIN
406 PRINT @_p78;
407
408 DROP INDEX [IX_HangFire_JobQueue_JobIdAndQueue] ON [HangFire].[JobQueue];
409 PRINT @_p79;
410
411 CREATE TABLE [HangFire].[Hash](
412 [Id] [int] IDENTITY(@_p80,@_p81) NOT NULL,
413 [Key] [nvarchar](@_p82) NOT NULL,
414 [Field] [nvarchar](@_p83) NOT NULL,
415 [Value] [nvarchar](max) NULL,
416 [ExpireAt] [datetime2](@_p84) NULL,
417
418 CONSTRAINT [PK_HangFire_Hash] PRIMARY KEY CLUSTERED ([Id] ASC)
419 );
420 PRINT @_p85;
421
422 CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_Hash_Key_Field] ON [HangFire].[Hash] (
423 [Key] ASC,
424 [Field] ASC
425 );
426 PRINT @_p86;
427
428 SET @CURRENT_SCHEMA_VERSION = @_p87;
429END
430
431create table [HangFire].[AggregatedCounter]([Id] [int] IDENTITY ( @_p90 , @_p91 ) NOT NULL,
432[Key] [nvarchar](@_p92) NOT NULL,
433[Value] [bigint] NOT NULL,
434[ExpireAt] [datetime] NULL,
435CONSTRAINT [PK_HangFire_CounterAggregated] PRIMARY KEY CLUSTERED ( [Id] ASC ))
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451SET @CURRENT_SCHEMA_VERSION = @_p99
452
453IF @CURRENT_SCHEMA_VERSION = @_p88
454BEGIN
455 PRINT @_p89;
456
457 CREATE TABLE [HangFire].[AggregatedCounter] (
458 [Id] [int] IDENTITY(@_p90,@_p91) NOT NULL,
459 [Key] [nvarchar](@_p92) NOT NULL,
460 [Value] [bigint] NOT NULL,
461 [ExpireAt] [datetime] NULL,
462
463 CONSTRAINT [PK_HangFire_CounterAggregated] PRIMARY KEY CLUSTERED ([Id] ASC)
464 );
465 PRINT @_p93;
466
467 CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_CounterAggregated_Key] ON [HangFire].[AggregatedCounter] (
468 [Key] ASC
469 ) INCLUDE ([Value]);
470 PRINT @_p94;
471
472 CREATE NONCLUSTERED INDEX [IX_HangFire_Hash_ExpireAt] ON [HangFire].[Hash] ([ExpireAt])
473 INCLUDE ([Id]);
474
475 CREATE NONCLUSTERED INDEX [IX_HangFire_Job_ExpireAt] ON [HangFire].[Job] ([ExpireAt])
476 INCLUDE ([Id]);
477
478 CREATE NONCLUSTERED INDEX [IX_HangFire_List_ExpireAt] ON [HangFire].[List] ([ExpireAt])
479 INCLUDE ([Id]);
480
481 CREATE NONCLUSTERED INDEX [IX_HangFire_Set_ExpireAt] ON [HangFire].[Set] ([ExpireAt])
482 INCLUDE ([Id]);
483
484 PRINT @_p95;
485
486 CREATE NONCLUSTERED INDEX [IX_HangFire_Hash_Key] ON [HangFire].[Hash] ([Key] ASC)
487 INCLUDE ([ExpireAt]);
488 PRINT @_p96;
489
490 CREATE NONCLUSTERED INDEX [IX_HangFire_List_Key] ON [HangFire].[List] ([Key] ASC)
491 INCLUDE ([ExpireAt], [Value]);
492 PRINT @_p97;
493
494 CREATE NONCLUSTERED INDEX [IX_HangFire_Set_Key] ON [HangFire].[Set] ([Key] ASC)
495 INCLUDE ([ExpireAt], [Value]);
496 PRINT @_p98;
497
498 SET @CURRENT_SCHEMA_VERSION = @_p99;
499END
500
501
502
503
504
505
506
507
508
509
510
511
512
513SET @CURRENT_SCHEMA_VERSION = @_p110
514
515IF @CURRENT_SCHEMA_VERSION = @_p100
516BEGIN
517 PRINT @_p101;
518
519 DROP INDEX [IX_HangFire_JobQueue_QueueAndFetchedAt] ON [HangFire].[JobQueue];
520 PRINT @_p102;
521
522 ALTER TABLE [HangFire].[JobQueue] ALTER COLUMN [Queue] NVARCHAR (@_p103) NOT NULL;
523 PRINT @_p104;
524
525 CREATE NONCLUSTERED INDEX [IX_HangFire_JobQueue_QueueAndFetchedAt] ON [HangFire].[JobQueue] (
526 [Queue] ASC,
527 [FetchedAt] ASC
528 );
529 PRINT @_p105;
530
531 ALTER TABLE [HangFire].[Server] DROP CONSTRAINT [PK_HangFire_Server]
532 PRINT @_p106;
533
534 ALTER TABLE [HangFire].[Server] ALTER COLUMN [Id] NVARCHAR (@_p107) NOT NULL;
535 PRINT @_p108;
536
537 ALTER TABLE [HangFire].[Server] ADD CONSTRAINT [PK_HangFire_Server] PRIMARY KEY CLUSTERED
538 (
539 [Id] ASC
540 );
541 PRINT @_p109;
542
543 SET @CURRENT_SCHEMA_VERSION = @_p110;
544END
545
546IF @CURRENT_SCHEMA_VERSION = @_p111 AND @DISABLE_HEAVY_MIGRATIONS = @_p112
547BEGIN
548 PRINT @_p113 + CAST(@CURRENT_SCHEMA_VERSION AS NVARCHAR) +
549 @_p114 + CAST(@TARGET_SCHEMA_VERSION AS NVARCHAR) +
550 @_p115;
551END
552ELSE IF @CURRENT_SCHEMA_VERSION = @_p116
553BEGIN
554 PRINT @_p117;
555
556 -- First, we will drop all the secondary indexes on the HangFire.Set table, because we will
557 -- modify that table, and unknown indexes may be added there (see https://github.com/HangfireIO/Hangfire/issues/844).
558 -- So, we'll drop all of them, and then re-create the required index with a well-known name.
559
560 DECLARE @dropIndexSql NVARCHAR(MAX) = @_p118;
561 SELECT @dropIndexSql += @_p119 + QUOTENAME(SCHEMA_NAME(o.[schema_id])) + @_p120 + QUOTENAME(o.name) + @_p121 + QUOTENAME(i.name) + @_p122
562 FROM sys.indexes AS i
563 INNER JOIN sys.tables AS o
564 ON i.[object_id] = o.[object_id]
565 WHERE i.is_primary_key = @_p123
566 AND i.index_id <> @_p124
567 AND o.is_ms_shipped = @_p125
568 AND SCHEMA_NAME(o.[schema_id]) = @_p126
569 AND o.name = @_p127;
570
571 EXEC sp_executesql @dropIndexSql;
572 PRINT @_p128;
573
574 -- Next, we'll remove the unnecessary indexes. They were unnecessary in the previous schema,
575 -- and are unnecessary in the new schema as well. We'll not re-create them.
576
577 DROP INDEX [IX_HangFire_Hash_Key] ON [HangFire].[Hash];
578 PRINT @_p129;
579
580 -- Next, all the indexes that cover expiration will be filtered, to include only non-null values. This
581 -- will prevent unnecessary index modifications â we are seeking these indexes only for non-null
582 -- expiration time. Also, they include the Id column by a mistake. So we'll re-create them later in the
583 -- migration.
584
585 DROP INDEX [IX_HangFire_Hash_ExpireAt] ON [HangFire].[Hash];
586 PRINT @_p130;
587
588 DROP INDEX [IX_HangFire_Job_ExpireAt] ON [HangFire].[Job];
589 PRINT @_p131;
590
591 DROP INDEX [IX_HangFire_List_ExpireAt] ON [HangFire].[List];
592 PRINT @_p132;
593
594 -- IX_HangFire_Job_StateName index can also be optimized, since we are querying it only with a
595 -- non-null state name. This will decrease the number of operations, when creating a background job.
596 -- It will be recreated later in the migration.
597
598 DROP INDEX [IX_HangFire_Job_StateName] ON [HangFire].Job;
599 PRINT @_p133;
600
601 -- Dropping foreign key constraints based on the JobId column, because we need to modify the underlying
602 -- column type of the clustered index to BIGINT. We'll recreate them later in the migration.
603
604 ALTER TABLE [HangFire].[JobParameter] DROP CONSTRAINT [FK_HangFire_JobParameter_Job];
605 PRINT @_p134;
606
607 ALTER TABLE [HangFire].[State] DROP CONSTRAINT [FK_HangFire_State_Job];
608 PRINT @_p135;
609
610 -- We are going to create composite clustered indexes that are more natural for the following tables,
611 -- so the following indexes will be unnecessary. Natural sorting will keep related data close to each
612 -- other, and simplify the index modifications by the cost of fragmentation and additional page splits.
613
614 DROP INDEX [UX_HangFire_CounterAggregated_Key] ON [HangFire].[AggregatedCounter];
615 PRINT @_p136;
616
617 DROP INDEX [IX_HangFire_Counter_Key] ON [HangFire].[Counter];
618 PRINT @_p137;
619
620 DROP INDEX [IX_HangFire_JobParameter_JobIdAndName] ON [HangFire].[JobParameter];
621 PRINT @_p138;
622
623 DROP INDEX [IX_HangFire_JobQueue_QueueAndFetchedAt] ON [HangFire].[JobQueue];
624 PRINT @_p139;
625
626 DROP INDEX [UX_HangFire_Hash_Key_Field] ON [HangFire].[Hash];
627 PRINT @_p140;
628
629 DROP INDEX [IX_HangFire_List_Key] ON [HangFire].[List];
630 PRINT @_p141;
631
632 DROP INDEX [IX_HangFire_State_JobId] ON [HangFire].[State];
633 PRINT @_p142;
634
635 -- Then, we need to drop the primary key constraints, to modify id columns to the BIGINT type. Some of them
636 -- will be re-created later in the migration. But some of them would be removed forever, because their
637 -- uniqueness property sometimes unnecessary.
638
639 ALTER TABLE [HangFire].[AggregatedCounter] DROP CONSTRAINT [PK_HangFire_CounterAggregated];
640 PRINT @_p143;
641
642 ALTER TABLE [HangFire].[Counter] DROP CONSTRAINT [PK_HangFire_Counter];
643 PRINT @_p144;
644
645 ALTER TABLE [HangFire].[Hash] DROP CONSTRAINT [PK_HangFire_Hash];
646 PRINT @_p145;
647
648 ALTER TABLE [HangFire].[Job] DROP CONSTRAINT [PK_HangFire_Job];
649 PRINT @_p146;
650
651 ALTER TABLE [HangFire].[JobParameter] DROP CONSTRAINT [PK_HangFire_JobParameter];
652 PRINT @_p147;
653
654 ALTER TABLE [HangFire].[JobQueue] DROP CONSTRAINT [PK_HangFire_JobQueue];
655 PRINT @_p148;
656
657 ALTER TABLE [HangFire].[List] DROP CONSTRAINT [PK_HangFire_List];
658 PRINT @_p149;
659
660 ALTER TABLE [HangFire].[Set] DROP CONSTRAINT [PK_HangFire_Set];
661 PRINT @_p150;
662
663 ALTER TABLE [HangFire].[State] DROP CONSTRAINT [PK_HangFire_State];
664 PRINT @_p151;
665
666 -- We are removing identity columns of the following tables completely, their clustered
667 -- index will be based on natural values. So, instead of modifying them to BIGINT, we
668 -- are dropping them.
669
670 ALTER TABLE [HangFire].[AggregatedCounter] DROP COLUMN [Id];
671 PRINT @_p152;
672
673 ALTER TABLE [HangFire].[Counter] DROP COLUMN [Id];
674 PRINT @_p153;
675
676 ALTER TABLE [HangFire].[Hash] DROP COLUMN [Id];
677 PRINT @_p154;
678
679 ALTER TABLE [HangFire].[Set] DROP COLUMN [Id];
680 PRINT @_p155;
681
682 ALTER TABLE [HangFire].[JobParameter] DROP COLUMN [Id];
683 PRINT @_p156;
684
685 -- Then we need to modify all the remaining Id columns to be of type BIGINT.
686
687 ALTER TABLE [HangFire].[List] ALTER COLUMN [Id] BIGINT NOT NULL;
688 PRINT @_p157;
689
690 ALTER TABLE [HangFire].[Job] ALTER COLUMN [Id] BIGINT NOT NULL;
691 PRINT @_p158;
692
693 ALTER TABLE [HangFire].[Job] ALTER COLUMN [StateId] BIGINT NULL;
694 PRINT @_p159;
695
696 ALTER TABLE [HangFire].[JobParameter] ALTER COLUMN [JobId] BIGINT NOT NULL;
697 PRINT @_p160;
698
699 ALTER TABLE [HangFire].[JobQueue] ALTER COLUMN [JobId] BIGINT NOT NULL;
700 PRINT @_p161;
701
702 ALTER TABLE [HangFire].[State] ALTER COLUMN [Id] BIGINT NOT NULL;
703 PRINT @_p162;
704
705 ALTER TABLE [HangFire].[State] ALTER COLUMN [JobId] BIGINT NOT NULL;
706 PRINT @_p163;
707
708 ALTER TABLE [HangFire].[Counter] ALTER COLUMN [Value] INT NOT NULL;
709 PRINT @_p164;
710
711 -- Adding back all the Primary Key constraints or clustered indexes where PKs aren't appropriate.
712
713 ALTER TABLE [HangFire].[AggregatedCounter] ADD CONSTRAINT [PK_HangFire_CounterAggregated] PRIMARY KEY CLUSTERED (
714 [Key] ASC
715 );
716 PRINT @_p165;
717
718 CREATE CLUSTERED INDEX [CX_HangFire_Counter] ON [HangFire].[Counter] ([Key]);
719 PRINT @_p166;
720
721 ALTER TABLE [HangFire].[Hash] ADD CONSTRAINT [PK_HangFire_Hash] PRIMARY KEY CLUSTERED (
722 [Key] ASC,
723 [Field] ASC
724 );
725 PRINT @_p167;
726
727 ALTER TABLE [HangFire].[Job] ADD CONSTRAINT [PK_HangFire_Job] PRIMARY KEY CLUSTERED ([Id] ASC);
728 PRINT @_p168;
729
730 ALTER TABLE [HangFire].[JobParameter] ADD CONSTRAINT [PK_HangFire_JobParameter] PRIMARY KEY CLUSTERED (
731 [JobId] ASC,
732 [Name] ASC
733 );
734 PRINT @_p169;
735
736 ALTER TABLE [HangFire].[JobQueue] ADD CONSTRAINT [PK_HangFire_JobQueue] PRIMARY KEY CLUSTERED (
737 [Queue] ASC,
738 [Id] ASC
739 );
740 PRINT @_p170;
741
742 ALTER TABLE [HangFire].[List] ADD CONSTRAINT [PK_HangFire_List] PRIMARY KEY CLUSTERED (
743 [Key] ASC,
744 [Id] ASC
745 );
746 PRINT @_p171;
747
748 ALTER TABLE [HangFire].[Set] ADD CONSTRAINT [PK_HangFire_Set] PRIMARY KEY CLUSTERED (
749 [Key] ASC,
750 [Value] ASC
751 );
752 PRINT @_p172;
753
754 ALTER TABLE [HangFire].[State] ADD CONSTRAINT [PK_HangFire_State] PRIMARY KEY CLUSTERED (
755 [JobId] ASC,
756 [Id]
757 );
758 PRINT @_p173;
759
760 -- Creating secondary, nonclustered indexes
761
762 CREATE NONCLUSTERED INDEX [IX_HangFire_Job_StateName] ON [HangFire].[Job] ([StateName])
763 WHERE [StateName] IS NOT NULL;
764 PRINT @_p174;
765
766 CREATE NONCLUSTERED INDEX [IX_HangFire_Set_Score] ON [HangFire].[Set] ([Score])
767 WHERE [Score] IS NOT NULL;
768 PRINT @_p175;
769
770 CREATE NONCLUSTERED INDEX [IX_HangFire_Server_LastHeartbeat] ON [HangFire].[Server] ([LastHeartbeat]);
771 PRINT @_p176;
772
773 -- Creating filtered indexes for ExpireAt columns
774
775 CREATE NONCLUSTERED INDEX [IX_HangFire_AggregatedCounter_ExpireAt] ON [HangFire].[AggregatedCounter] ([ExpireAt])
776 WHERE [ExpireAt] IS NOT NULL;
777 PRINT @_p177;
778
779 CREATE NONCLUSTERED INDEX [IX_HangFire_Hash_ExpireAt] ON [HangFire].[Hash] ([ExpireAt])
780 WHERE [ExpireAt] IS NOT NULL;
781 PRINT @_p178;
782
783 CREATE NONCLUSTERED INDEX [IX_HangFire_Job_ExpireAt] ON [HangFire].[Job] ([ExpireAt])
784 INCLUDE ([StateName])
785 WHERE [ExpireAt] IS NOT NULL;
786 PRINT @_p179;
787
788 CREATE NONCLUSTERED INDEX [IX_HangFire_List_ExpireAt] ON [HangFire].[List] ([ExpireAt])
789 WHERE [ExpireAt] IS NOT NULL;
790 PRINT @_p180;
791
792 CREATE NONCLUSTERED INDEX [IX_HangFire_Set_ExpireAt] ON [HangFire].[Set] ([ExpireAt])
793 WHERE [ExpireAt] IS NOT NULL;
794 PRINT @_p181;
795
796 -- Restoring foreign keys
797
798 ALTER TABLE [HangFire].[State] ADD CONSTRAINT [FK_HangFire_State_Job] FOREIGN KEY([JobId])
799 REFERENCES [HangFire].[Job] ([Id])
800 ON UPDATE CASCADE
801 ON DELETE CASCADE;
802 PRINT @_p182;
803
804 ALTER TABLE [HangFire].[JobParameter] ADD CONSTRAINT [FK_HangFire_JobParameter_Job] FOREIGN KEY([JobId])
805 REFERENCES [HangFire].[Job] ([Id])
806 ON UPDATE CASCADE
807 ON DELETE CASCADE;
808 PRINT @_p183;
809
810 SET @CURRENT_SCHEMA_VERSION = @_p184;
811END
812
813
814
815
816
817SET @CURRENT_SCHEMA_VERSION = @_p189
818
819IF @CURRENT_SCHEMA_VERSION = @_p185
820BEGIN
821 PRINT @_p186;
822
823 DROP INDEX [IX_HangFire_Set_Score] ON [HangFire].[Set];
824 PRINT @_p187;
825
826 CREATE NONCLUSTERED INDEX [IX_HangFire_Set_Score] ON [HangFire].[Set] ([Key], [Score]);
827 PRINT @_p188;
828
829 SET @CURRENT_SCHEMA_VERSION = @_p189;
830END
831UPDATE [HangFire].[Schema] SET [Version] = @CURRENT_SCHEMA_VERSION
832IF @@ROWCOUNT = @_p190
833 INSERT INTO [HangFire].[Schema] ([Version]) VALUES (@CURRENT_SCHEMA_VERSION)
834PRINT @_p191;
835COMMIT TRANSACTION;
836PRINT @_p192;