DECLARE @SCHEMA_ID int;
SELECT @SCHEMA_ID = [schema_id] FROM [sys].[schemas] WHERE [name] = 'HangFire_dev';
-- Create the [HangFire_dev].Schema table if not exists
IF NOT EXISTS(SELECT [object_id] FROM [sys].[tables]
WHERE [name] = 'Schema' AND [schema_id] = @SCHEMA_ID)
BEGIN
CREATE TABLE [HangFire_dev].[Schema](
[Version] [int] NOT NULL,
CONSTRAINT [PK_HangFire_Schema] PRIMARY KEY CLUSTERED ([Version] ASC)
);
PRINT 'Created table [HangFire_dev].[Schema]';
END
ELSE
PRINT 'Table [HangFire_dev].[Schema] already exists';
DECLARE @CURRENT_SCHEMA_VERSION int;
SELECT @CURRENT_SCHEMA_VERSION = [Version] FROM [HangFire_dev].[Schema];
PRINT 'Current Hangfire schema version: ' + CASE WHEN @CURRENT_SCHEMA_VERSION IS
NULL THEN 'none' ELSE CONVERT(nvarchar, @CURRENT_SCHEMA_VERSION) END;
IF @CURRENT_SCHEMA_VERSION IS NOT NULL AND @CURRENT_SCHEMA_VERSION >
@TARGET_SCHEMA_VERSION
BEGIN
ROLLBACK TRANSACTION;
PRINT 'Hangfire current database schema version ' +
CAST(@CURRENT_SCHEMA_VERSION AS NVARCHAR) +
' is newer than the configured SqlServerStorage schema version ' +
CAST(@TARGET_SCHEMA_VERSION AS NVARCHAR) +
'. Will not apply any migrations.';
RETURN;
END
-- Install [HangFire_dev] schema objects
IF @CURRENT_SCHEMA_VERSION IS NULL
BEGIN
IF @DISABLE_HEAVY_MIGRATIONS = 1
BEGIN
SET @DISABLE_HEAVY_MIGRATIONS = 0;
PRINT 'Enabling HEAVY_MIGRATIONS, because we are installing objects from
scratch';
END
PRINT 'Installing schema version 1';
-- Create job tables
CREATE TABLE [HangFire_dev].[Job] (
[Id] [int] IDENTITY(1,1) NOT NULL,
[StateId] [int] NULL,
[StateName] [nvarchar](20) NULL, -- To speed-up queries.
[InvocationData] [nvarchar](max) NOT NULL,
[Arguments] [nvarchar](max) NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[ExpireAt] [datetime] NULL,
CONSTRAINT [PK_HangFire_Job] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[Job]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Job_StateName] ON [HangFire_dev].[Job]
([StateName] ASC);
PRINT 'Created index [IX_HangFire_Job_StateName]';
-- Job history table
CREATE TABLE [HangFire_dev].[State] (
[Id] [int] IDENTITY(1,1) NOT NULL,
[JobId] [int] NOT NULL,
[Name] [nvarchar](20) NOT NULL,
[Reason] [nvarchar](100) NULL,
[CreatedAt] [datetime] NOT NULL,
[Data] [nvarchar](max) NULL,
CONSTRAINT [PK_HangFire_State] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[State]';
ALTER TABLE [HangFire_dev].[State] ADD CONSTRAINT [FK_HangFire_State_Job]
FOREIGN KEY([JobId])
REFERENCES [HangFire_dev].[Job] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE;
PRINT 'Created constraint [FK_HangFire_State_Job]';
CREATE NONCLUSTERED INDEX [IX_HangFire_State_JobId] ON [HangFire_dev].[State]
([JobId] ASC);
PRINT 'Created index [IX_HangFire_State_JobId]';
-- Job parameters table
CREATE TABLE [HangFire_dev].[JobParameter](
[Id] [int] IDENTITY(1,1) NOT NULL,
[JobId] [int] NOT NULL,
[Name] [nvarchar](40) NOT NULL,
[Value] [nvarchar](max) NULL,
CONSTRAINT [PK_HangFire_JobParameter] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[JobParameter]';
ALTER TABLE [HangFire_dev].[JobParameter] ADD CONSTRAINT
[FK_HangFire_JobParameter_Job] FOREIGN KEY([JobId])
REFERENCES [HangFire_dev].[Job] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE;
PRINT 'Created constraint [FK_HangFire_JobParameter_Job]';
CREATE NONCLUSTERED INDEX [IX_HangFire_JobParameter_JobIdAndName] ON
[HangFire_dev].[JobParameter] (
[JobId] ASC,
[Name] ASC
);
PRINT 'Created index [IX_HangFire_JobParameter_JobIdAndName]';
-- Job queue table
CREATE TABLE [HangFire_dev].[JobQueue](
[Id] [int] IDENTITY(1,1) NOT NULL,
[JobId] [int] NOT NULL,
[Queue] [nvarchar](20) NOT NULL,
[FetchedAt] [datetime] NULL,
CONSTRAINT [PK_HangFire_JobQueue] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[JobQueue]';
CREATE NONCLUSTERED INDEX [IX_HangFire_JobQueue_JobIdAndQueue] ON
[HangFire_dev].[JobQueue] (
[JobId] ASC,
[Queue] ASC
);
PRINT 'Created index [IX_HangFire_JobQueue_JobIdAndQueue]';
CREATE NONCLUSTERED INDEX [IX_HangFire_JobQueue_QueueAndFetchedAt] ON
[HangFire_dev].[JobQueue] (
[Queue] ASC,
[FetchedAt] ASC
);
PRINT 'Created index [IX_HangFire_JobQueue_QueueAndFetchedAt]';
-- Servers table
CREATE TABLE [HangFire_dev].[Server](
[Id] [nvarchar](200) NOT NULL,
[Data] [nvarchar](max) NULL,
[LastHeartbeat] [datetime] NULL,
CONSTRAINT [PK_HangFire_Server] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[Server]';
-- Extension tables
CREATE TABLE [HangFire_dev].[Hash](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Key] [nvarchar](100) NOT NULL,
[Name] [nvarchar](40) NOT NULL,
[StringValue] [nvarchar](max) NULL,
[IntValue] [int] NULL,
[ExpireAt] [datetime] NULL,
CONSTRAINT [PK_HangFire_Hash] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[Hash]';
CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_Hash_KeyAndName] ON
[HangFire_dev].[Hash] (
[Key] ASC,
[Name] ASC
);
PRINT 'Created index [UX_HangFire_Hash_KeyAndName]';
CREATE TABLE [HangFire_dev].[List](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Key] [nvarchar](100) NOT NULL,
[Value] [nvarchar](max) NULL,
[ExpireAt] [datetime] NULL,
CONSTRAINT [PK_HangFire_List] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[List]';
CREATE TABLE [HangFire_dev].[Set](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Key] [nvarchar](100) NOT NULL,
[Score] [float] NOT NULL,
[Value] [nvarchar](256) NOT NULL,
[ExpireAt] [datetime] NULL,
CONSTRAINT [PK_HangFire_Set] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[Set]';
CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_Set_KeyAndValue] ON
[HangFire_dev].[Set] (
[Key] ASC,
[Value] ASC
);
PRINT 'Created index [UX_HangFire_Set_KeyAndValue]';
CREATE TABLE [HangFire_dev].[Value](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Key] [nvarchar](100) NOT NULL,
[StringValue] [nvarchar](max) NULL,
[IntValue] [int] NULL,
[ExpireAt] [datetime] NULL,
CONSTRAINT [PK_HangFire_Value] PRIMARY KEY CLUSTERED (
[Id] ASC
)
);
PRINT 'Created table [HangFire_dev].[Value]';
CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_Value_Key] ON [HangFire_dev].
[Value] (
[Key] ASC
);
PRINT 'Created index [UX_HangFire_Value_Key]';
CREATE TABLE [HangFire_dev].[Counter](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Key] [nvarchar](100) NOT NULL,
[Value] [tinyint] NOT NULL,
[ExpireAt] [datetime] NULL,
CONSTRAINT [PK_HangFire_Counter] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[Counter]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Counter_Key] ON [HangFire_dev].
[Counter] ([Key] ASC)
INCLUDE ([Value]);
PRINT 'Created index [IX_HangFire_Counter_Key]';
SET @CURRENT_SCHEMA_VERSION = 1;
END
IF @CURRENT_SCHEMA_VERSION = 1
BEGIN
PRINT 'Installing schema version 2';
-- https://github.com/odinserj/HangFire/issues/83
DROP INDEX [IX_HangFire_Counter_Key] ON [HangFire_dev].[Counter];
ALTER TABLE [HangFire_dev].[Counter] ALTER COLUMN [Value] SMALLINT NOT NULL;
CREATE NONCLUSTERED INDEX [IX_HangFire_Counter_Key] ON [HangFire_dev].
[Counter] ([Key] ASC)
INCLUDE ([Value]);
PRINT 'Index [IX_HangFire_Counter_Key] re-created';
DROP TABLE [HangFire_dev].[Value];
DROP TABLE [HangFire_dev].[Hash];
PRINT 'Dropped tables [HangFire_dev].[Value] and [HangFire_dev].[Hash]'
DELETE FROM [HangFire_dev].[Server] WHERE [LastHeartbeat] IS NULL;
ALTER TABLE [HangFire_dev].[Server] ALTER COLUMN [LastHeartbeat] DATETIME NOT
NULL;
SET @CURRENT_SCHEMA_VERSION = 2;
END
IF @CURRENT_SCHEMA_VERSION = 2
BEGIN
PRINT 'Installing schema version 3';
DROP INDEX [IX_HangFire_JobQueue_JobIdAndQueue] ON [HangFire_dev].[JobQueue];
PRINT 'Dropped index [IX_HangFire_JobQueue_JobIdAndQueue]';
CREATE TABLE [HangFire_dev].[Hash](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Key] [nvarchar](100) NOT NULL,
[Field] [nvarchar](100) NOT NULL,
[Value] [nvarchar](max) NULL,
[ExpireAt] [datetime2](7) NULL,
CONSTRAINT [PK_HangFire_Hash] PRIMARY KEY CLUSTERED ([Id] ASC)
);
PRINT 'Created table [HangFire_dev].[Hash]';
CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_Hash_Key_Field] ON
[HangFire_dev].[Hash] (
[Key] ASC,
[Field] ASC
);
PRINT 'Created index [UX_HangFire_Hash_Key_Field]';
SET @CURRENT_SCHEMA_VERSION = 3;
END
IF @CURRENT_SCHEMA_VERSION = 3
BEGIN
PRINT 'Installing schema version 4';
CREATE TABLE [HangFire_dev].[AggregatedCounter] (
[Id] [int] IDENTITY(1,1) NOT NULL,
[Key] [nvarchar](100) NOT NULL,
[Value] [bigint] NOT NULL,
[ExpireAt] [datetime] NULL,
CONSTRAINT [PK_HangFire_CounterAggregated] PRIMARY KEY CLUSTERED ([Id]
ASC)
);
PRINT 'Created table [HangFire_dev].[AggregatedCounter]';
CREATE UNIQUE NONCLUSTERED INDEX [UX_HangFire_CounterAggregated_Key] ON
[HangFire_dev].[AggregatedCounter] (
[Key] ASC
) INCLUDE ([Value]);
PRINT 'Created index [UX_HangFire_CounterAggregated_Key]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Hash_ExpireAt] ON [HangFire_dev].
[Hash] ([ExpireAt])
INCLUDE ([Id]);
CREATE NONCLUSTERED INDEX [IX_HangFire_Job_ExpireAt] ON [HangFire_dev].[Job]
([ExpireAt])
INCLUDE ([Id]);
CREATE NONCLUSTERED INDEX [IX_HangFire_List_ExpireAt] ON [HangFire_dev].
[List] ([ExpireAt])
INCLUDE ([Id]);
CREATE NONCLUSTERED INDEX [IX_HangFire_Set_ExpireAt] ON [HangFire_dev].[Set]
([ExpireAt])
INCLUDE ([Id]);
PRINT 'Created indexes for [ExpireAt] columns';
CREATE NONCLUSTERED INDEX [IX_HangFire_Hash_Key] ON [HangFire_dev].[Hash]
([Key] ASC)
INCLUDE ([ExpireAt]);
PRINT 'Created index [IX_HangFire_Hash_Key]';
CREATE NONCLUSTERED INDEX [IX_HangFire_List_Key] ON [HangFire_dev].[List]
([Key] ASC)
INCLUDE ([ExpireAt], [Value]);
PRINT 'Created index [IX_HangFire_List_Key]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Set_Key] ON [HangFire_dev].[Set]
([Key] ASC)
INCLUDE ([ExpireAt], [Value]);
PRINT 'Created index [IX_HangFire_Set_Key]';
SET @CURRENT_SCHEMA_VERSION = 4;
END
IF @CURRENT_SCHEMA_VERSION = 4
BEGIN
PRINT 'Installing schema version 5';
DROP INDEX [IX_HangFire_JobQueue_QueueAndFetchedAt] ON [HangFire_dev].
[JobQueue];
PRINT 'Dropped index [IX_HangFire_JobQueue_QueueAndFetchedAt] to modify the
[HangFire_dev].[JobQueue].[Queue] column';
ALTER TABLE [HangFire_dev].[JobQueue] ALTER COLUMN [Queue] NVARCHAR (50) NOT
NULL;
PRINT 'Modified [HangFire_dev].[JobQueue].[Queue] length to 50';
CREATE NONCLUSTERED INDEX [IX_HangFire_JobQueue_QueueAndFetchedAt] ON
[HangFire_dev].[JobQueue] (
[Queue] ASC,
[FetchedAt] ASC
);
PRINT 'Re-created index [IX_HangFire_JobQueue_QueueAndFetchedAt]';
ALTER TABLE [HangFire_dev].[Server] DROP CONSTRAINT [PK_HangFire_Server]
PRINT 'Dropped constraint [PK_HangFire_Server] to modify the [HangFire_dev].
[Server].[Id] column';
ALTER TABLE [HangFire_dev].[Server] ALTER COLUMN [Id] NVARCHAR (200) NOT
NULL;
PRINT 'Modified [HangFire_dev].[Server].[Id] length to 200';
ALTER TABLE [HangFire_dev].[Server] ADD CONSTRAINT [PK_HangFire_Server]
PRIMARY KEY CLUSTERED
(
[Id] ASC
);
PRINT 'Re-created constraint [PK_HangFire_Server]';
SET @CURRENT_SCHEMA_VERSION = 5;
END
IF @CURRENT_SCHEMA_VERSION = 5 AND @DISABLE_HEAVY_MIGRATIONS = 1
BEGIN
PRINT 'Migration process STOPPED at schema version ' +
CAST(@CURRENT_SCHEMA_VERSION AS NVARCHAR) +
'. WILL NOT upgrade to schema version ' + CAST(@TARGET_SCHEMA_VERSION AS
NVARCHAR) +
', because @DISABLE_HEAVY_MIGRATIONS option is set.';
END
ELSE IF @CURRENT_SCHEMA_VERSION = 5
BEGIN
PRINT 'Installing schema version 6';
-- First, we will drop all the secondary indexes on the HangFire.Set table,
because we will
-- modify that table, and unknown indexes may be added there (see
https://github.com/HangfireIO/Hangfire/issues/844).
-- So, we'll drop all of them, and then re-create the required index with a
well-known name.
DECLARE @dropIndexSql NVARCHAR(MAX) = N'';
SELECT @dropIndexSql += N'DROP INDEX ' + QUOTENAME(SCHEMA_NAME(o.
[schema_id])) + '.' + QUOTENAME(o.name) + '.' + QUOTENAME(i.name) + ';'
FROM sys.indexes AS i
INNER JOIN sys.tables AS o
ON i.[object_id] = o.[object_id]
WHERE i.is_primary_key = 0
AND i.index_id <> 0
AND o.is_ms_shipped = 0
AND SCHEMA_NAME(o.[schema_id]) = 'HangFire'
AND o.name = 'Set';
EXEC sp_executesql @dropIndexSql;
PRINT 'Dropped all secondary indexes on the [Set] table';
-- Next, we'll remove the unnecessary indexes. They were unnecessary in the
previous schema,
-- and are unnecessary in the new schema as well. We'll not re-create them.
DROP INDEX [IX_HangFire_Hash_Key] ON [HangFire_dev].[Hash];
PRINT 'Dropped unnecessary index [IX_HangFire_Hash_Key]';
-- Next, all the indexes that cover expiration will be filtered, to include
only non-null values. This
-- will prevent unnecessary index modifications – we are seeking these
indexes only for non-null
-- expiration time. Also, they include the Id column by a mistake. So we'll
re-create them later in the
-- migration.
DROP INDEX [IX_HangFire_Hash_ExpireAt] ON [HangFire_dev].[Hash];
PRINT 'Dropped index [IX_HangFire_Hash_ExpireAt]';
DROP INDEX [IX_HangFire_Job_ExpireAt] ON [HangFire_dev].[Job];
PRINT 'Dropped index [IX_HangFire_Job_ExpireAt]';
DROP INDEX [IX_HangFire_List_ExpireAt] ON [HangFire_dev].[List];
PRINT 'Dropped index [IX_HangFire_List_ExpireAt]';
-- IX_HangFire_Job_StateName index can also be optimized, since we are
querying it only with a
-- non-null state name. This will decrease the number of operations, when
creating a background job.
-- It will be recreated later in the migration.
DROP INDEX [IX_HangFire_Job_StateName] ON [HangFire_dev].Job;
PRINT 'Dropped index [IX_HangFire_Job_StateName]';
-- Dropping foreign key constraints based on the JobId column, because we
need to modify the underlying
-- column type of the clustered index to BIGINT. We'll recreate them later in
the migration.
ALTER TABLE [HangFire_dev].[JobParameter] DROP CONSTRAINT
[FK_HangFire_JobParameter_Job];
PRINT 'Dropped constraint [FK_HangFire_JobParameter_Job]';
ALTER TABLE [HangFire_dev].[State] DROP CONSTRAINT [FK_HangFire_State_Job];
PRINT 'Dropped constraint [FK_HangFire_State_Job]';
-- We are going to create composite clustered indexes that are more natural
for the following tables,
-- so the following indexes will be unnecessary. Natural sorting will keep
related data close to each
-- other, and simplify the index modifications by the cost of fragmentation
and additional page splits.
DROP INDEX [UX_HangFire_CounterAggregated_Key] ON [HangFire_dev].
[AggregatedCounter];
PRINT 'Dropped index [UX_HangFire_CounterAggregated_Key]';
DROP INDEX [IX_HangFire_Counter_Key] ON [HangFire_dev].[Counter];
PRINT 'Dropped index [IX_HangFire_Counter_Key]';
DROP INDEX [IX_HangFire_JobParameter_JobIdAndName] ON [HangFire_dev].
[JobParameter];
PRINT 'Dropped index [IX_HangFire_JobParameter_JobIdAndName]';
DROP INDEX [IX_HangFire_JobQueue_QueueAndFetchedAt] ON [HangFire_dev].
[JobQueue];
PRINT 'Dropped index [IX_HangFire_JobQueue_QueueAndFetchedAt]';
DROP INDEX [UX_HangFire_Hash_Key_Field] ON [HangFire_dev].[Hash];
PRINT 'Dropped index [UX_HangFire_Hash_Key_Field]';
DROP INDEX [IX_HangFire_List_Key] ON [HangFire_dev].[List];
PRINT 'Dropped index [IX_HangFire_List_Key]';
DROP INDEX [IX_HangFire_State_JobId] ON [HangFire_dev].[State];
PRINT 'Dropped index [IX_HangFire_State_JobId]';
-- Then, we need to drop the primary key constraints, to modify id columns to
the BIGINT type. Some of them
-- will be re-created later in the migration. But some of them would be
removed forever, because their
-- uniqueness property sometimes unnecessary.
ALTER TABLE [HangFire_dev].[AggregatedCounter] DROP CONSTRAINT
[PK_HangFire_CounterAggregated];
PRINT 'Dropped constraint [PK_HangFire_CounterAggregated]';
ALTER TABLE [HangFire_dev].[Counter] DROP CONSTRAINT [PK_HangFire_Counter];
PRINT 'Dropped constraint [PK_HangFire_Counter]';
ALTER TABLE [HangFire_dev].[Hash] DROP CONSTRAINT [PK_HangFire_Hash];
PRINT 'Dropped constraint [PK_HangFire_Hash]';
ALTER TABLE [HangFire_dev].[Job] DROP CONSTRAINT [PK_HangFire_Job];
PRINT 'Dropped constraint [PK_HangFire_Job]';
ALTER TABLE [HangFire_dev].[JobParameter] DROP CONSTRAINT
[PK_HangFire_JobParameter];
PRINT 'Dropped constraint [PK_HangFire_JobParameter]';
ALTER TABLE [HangFire_dev].[JobQueue] DROP CONSTRAINT [PK_HangFire_JobQueue];
PRINT 'Dropped constraint [PK_HangFire_JobQueue]';
ALTER TABLE [HangFire_dev].[List] DROP CONSTRAINT [PK_HangFire_List];
PRINT 'Dropped constraint [PK_HangFire_List]';
ALTER TABLE [HangFire_dev].[Set] DROP CONSTRAINT [PK_HangFire_Set];
PRINT 'Dropped constraint [PK_HangFire_Set]';
ALTER TABLE [HangFire_dev].[State] DROP CONSTRAINT [PK_HangFire_State];
PRINT 'Dropped constraint [PK_HangFire_State]';
-- We are removing identity columns of the following tables completely, their
clustered
-- index will be based on natural values. So, instead of modifying them to
BIGINT, we
-- are dropping them.
ALTER TABLE [HangFire_dev].[AggregatedCounter] DROP COLUMN [Id];
PRINT 'Dropped [AggregatedCounter].[Id] column, we will cluster on [Key]
column with uniqufier';
ALTER TABLE [HangFire_dev].[Counter] DROP COLUMN [Id];
PRINT 'Dropped [Counter].[Id] column, we will cluster on [Key] column';
ALTER TABLE [HangFire_dev].[Hash] DROP COLUMN [Id];
PRINT 'Dropped [Hash].[Id] column, we will cluster on [Key]/[Field] columns';
ALTER TABLE [HangFire_dev].[Set] DROP COLUMN [Id];
PRINT 'Dropped [Set].[Id] column, we will cluster on [Key]/[Value] columns';
ALTER TABLE [HangFire_dev].[JobParameter] DROP COLUMN [Id];
PRINT 'Dropped [JobParameter].[Id] column, we will cluster on [JobId]/[Name]
columns';
-- Then we need to modify all the remaining Id columns to be of type BIGINT.
ALTER TABLE [HangFire_dev].[List] ALTER COLUMN [Id] BIGINT NOT NULL;
PRINT 'Modified [List].[Id] type to BIGINT';
ALTER TABLE [HangFire_dev].[Job] ALTER COLUMN [Id] BIGINT NOT NULL;
PRINT 'Modified [Job].[Id] type to BIGINT';
ALTER TABLE [HangFire_dev].[Job] ALTER COLUMN [StateId] BIGINT NULL;
PRINT 'Modified [Job].[StateId] type to BIGINT';
ALTER TABLE [HangFire_dev].[JobParameter] ALTER COLUMN [JobId] BIGINT NOT
NULL;
PRINT 'Modified [JobParameter].[JobId] type to BIGINT';
ALTER TABLE [HangFire_dev].[JobQueue] ALTER COLUMN [JobId] BIGINT NOT NULL;
PRINT 'Modified [JobQueue].[JobId] type to BIGINT';
ALTER TABLE [HangFire_dev].[JobQueue] ALTER COLUMN [Id] BIGINT NOT NULL;
PRINT 'Modified [JobQueue].[Id] type to BIGINT';
ALTER TABLE [HangFire_dev].[State] ALTER COLUMN [Id] BIGINT NOT NULL;
PRINT 'Modified [State].[Id] type to BIGINT';
ALTER TABLE [HangFire_dev].[State] ALTER COLUMN [JobId] BIGINT NOT NULL;
PRINT 'Modified [State].[JobId] type to BIGINT';
ALTER TABLE [HangFire_dev].[Counter] ALTER COLUMN [Value] INT NOT NULL;
PRINT 'Modified [Counter].[Value] type to INT';
-- Adding back all the Primary Key constraints or clustered indexes where PKs
aren't appropriate.
ALTER TABLE [HangFire_dev].[AggregatedCounter] ADD CONSTRAINT
[PK_HangFire_CounterAggregated] PRIMARY KEY CLUSTERED (
[Key] ASC
);
PRINT 'Re-created constraint [PK_HangFire_CounterAggregated]';
CREATE CLUSTERED INDEX [CX_HangFire_Counter] ON [HangFire_dev].[Counter]
([Key]);
PRINT 'Created clustered index [CX_HangFire_Counter]';
ALTER TABLE [HangFire_dev].[Hash] ADD CONSTRAINT [PK_HangFire_Hash] PRIMARY
KEY CLUSTERED (
[Key] ASC,
[Field] ASC
);
PRINT 'Re-created constraint [PK_HangFire_Hash]';
ALTER TABLE [HangFire_dev].[Job] ADD CONSTRAINT [PK_HangFire_Job] PRIMARY KEY
CLUSTERED ([Id] ASC);
PRINT 'Re-created constraint [PK_HangFire_Job]';
ALTER TABLE [HangFire_dev].[JobParameter] ADD CONSTRAINT
[PK_HangFire_JobParameter] PRIMARY KEY CLUSTERED (
[JobId] ASC,
[Name] ASC
);
PRINT 'Re-created constraint [PK_HangFire_JobParameter]';
ALTER TABLE [HangFire_dev].[JobQueue] ADD CONSTRAINT [PK_HangFire_JobQueue]
PRIMARY KEY CLUSTERED (
[Queue] ASC,
[Id] ASC
);
PRINT 'Re-created constraint [PK_HangFire_JobQueue]';
ALTER TABLE [HangFire_dev].[List] ADD CONSTRAINT [PK_HangFire_List] PRIMARY
KEY CLUSTERED (
[Key] ASC,
[Id] ASC
);
PRINT 'Re-created constraint [PK_HangFire_List]';
ALTER TABLE [HangFire_dev].[Set] ADD CONSTRAINT [PK_HangFire_Set] PRIMARY KEY
CLUSTERED (
[Key] ASC,
[Value] ASC
);
PRINT 'Re-created constraint [PK_HangFire_Set]';
ALTER TABLE [HangFire_dev].[State] ADD CONSTRAINT [PK_HangFire_State] PRIMARY
KEY CLUSTERED (
[JobId] ASC,
[Id]
);
PRINT 'Re-created constraint [PK_HangFire_State]';
-- Creating secondary, nonclustered indexes
CREATE NONCLUSTERED INDEX [IX_HangFire_Job_StateName] ON [HangFire_dev].[Job]
([StateName])
WHERE [StateName] IS NOT NULL;
PRINT 'Re-created index [IX_HangFire_Job_StateName]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Set_Score] ON [HangFire_dev].[Set]
([Score])
WHERE [Score] IS NOT NULL;
PRINT 'Created index [IX_HangFire_Set_Score]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Server_LastHeartbeat] ON
[HangFire_dev].[Server] ([LastHeartbeat]);
PRINT 'Created index [IX_HangFire_Server_LastHeartbeat]';
-- Creating filtered indexes for ExpireAt columns
CREATE NONCLUSTERED INDEX [IX_HangFire_AggregatedCounter_ExpireAt] ON
[HangFire_dev].[AggregatedCounter] ([ExpireAt])
WHERE [ExpireAt] IS NOT NULL;
PRINT 'Created index [IX_HangFire_AggregatedCounter_ExpireAt]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Hash_ExpireAt] ON [HangFire_dev].
[Hash] ([ExpireAt])
WHERE [ExpireAt] IS NOT NULL;
PRINT 'Re-created index [IX_HangFire_Hash_ExpireAt]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Job_ExpireAt] ON [HangFire_dev].[Job]
([ExpireAt])
INCLUDE ([StateName])
WHERE [ExpireAt] IS NOT NULL;
PRINT 'Re-created index [IX_HangFire_Job_ExpireAt]';
CREATE NONCLUSTERED INDEX [IX_HangFire_List_ExpireAt] ON [HangFire_dev].
[List] ([ExpireAt])
WHERE [ExpireAt] IS NOT NULL;
PRINT 'Re-created index [IX_HangFire_List_ExpireAt]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Set_ExpireAt] ON [HangFire_dev].[Set]
([ExpireAt])
WHERE [ExpireAt] IS NOT NULL;
PRINT 'Re-created index [IX_HangFire_Set_ExpireAt]';
-- Restoring foreign keys
ALTER TABLE [HangFire_dev].[State] ADD CONSTRAINT [FK_HangFire_State_Job]
FOREIGN KEY([JobId])
REFERENCES [HangFire_dev].[Job] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE;
PRINT 'Re-created constraint [FK_HangFire_State_Job]';
ALTER TABLE [HangFire_dev].[JobParameter] ADD CONSTRAINT
[FK_HangFire_JobParameter_Job] FOREIGN KEY([JobId])
REFERENCES [HangFire_dev].[Job] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE;
PRINT 'Re-created constraint [FK_HangFire_JobParameter_Job]';
SET @CURRENT_SCHEMA_VERSION = 6;
END
IF @CURRENT_SCHEMA_VERSION = 6
BEGIN
PRINT 'Installing schema version 7';
DROP INDEX [IX_HangFire_Set_Score] ON [HangFire_dev].[Set];
PRINT 'Dropped index [IX_HangFire_Set_Score]';
CREATE NONCLUSTERED INDEX [IX_HangFire_Set_Score] ON [HangFire_dev].[Set]
([Key], [Score]);
PRINT 'Created index [IX_HangFire_Set_Score] with the proper composite key';
SET @CURRENT_SCHEMA_VERSION = 7;
END
IF @CURRENT_SCHEMA_VERSION = 7 AND @DISABLE_HEAVY_MIGRATIONS = 1
BEGIN
PRINT 'Migration process STOPPED at schema version ' +
CAST(@CURRENT_SCHEMA_VERSION AS NVARCHAR) +
'. WILL NOT upgrade to schema version ' + CAST(@TARGET_SCHEMA_VERSION
AS NVARCHAR) +
', because @DISABLE_HEAVY_MIGRATIONS option is set.';
END
ELSE IF @CURRENT_SCHEMA_VERSION = 7
BEGIN
PRINT 'Installing schema version 8';
ALTER TABLE [HangFire_dev].[Server] DROP CONSTRAINT [PK_HangFire_Server]
PRINT 'Dropped constraint [PK_HangFire_Server] to modify the [HangFire_dev].
[Server].[Id] column';
ALTER TABLE [HangFire_dev].[Server] ALTER COLUMN [Id] NVARCHAR (200) NOT
NULL;
PRINT 'Modified [HangFire_dev].[Server].[Id] length to 200';
ALTER TABLE [HangFire_dev].[Server] ADD CONSTRAINT [PK_HangFire_Server]
PRIMARY KEY CLUSTERED ([Id] ASC);
PRINT 'Re-created constraint [PK_HangFire_Server]';
-- Nothing complicated - we just collecting all the secondary indexes and
primary key names to delete them.
-- We should expect nothing here, because custom columns and indexes can be
applied for the [Counter] table
-- to make replication work on Microsoft Azure, like in the issue below.
-- https://github.com/HangfireIO/Hangfire/issues/1500
DECLARE @dropIndexSql2 NVARCHAR(MAX) = N'';
SELECT @dropIndexSql2 += N'DROP INDEX ' + QUOTENAME(SCHEMA_NAME(o.
[schema_id])) + '.' + QUOTENAME(o.name) + '.' + QUOTENAME(i.name) + ';'
FROM sys.indexes AS i
INNER JOIN sys.tables AS o
ON i.[object_id] = o.[object_id]
WHERE i.is_primary_key = 0
AND i.index_id <> 0
AND o.is_ms_shipped = 0
AND SCHEMA_NAME(o.[schema_id]) = 'HangFire'
AND o.name = 'Counter';
SELECT @dropIndexSql2 += N'ALTER TABLE' + QUOTENAME(SCHEMA_NAME(o.
[schema_id])) + '.' + QUOTENAME(o.name) + ' DROP CONSTRAINT ' + QUOTENAME(c.name) +
';'
FROM sys.key_constraints c
INNER JOIN sys.tables AS o
ON c.[parent_object_id] = o.[object_id]
WHERE o.is_ms_shipped = 0
AND SCHEMA_NAME(o.[schema_id]) = 'HangFire'
AND o.name = 'Counter'
EXEC sp_executesql @dropIndexSql2;
PRINT 'Dropped all indexes on the [HangFire_dev].[Counter] table';
-- [Counter].[Id] column can already be added to make replication work as
written above, so we will re-create it
-- to ensure it is in the expected format.
PRINT 'Checking for existence of the [HangFire_dev].[Counter].[Id] column';
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
'Counter' AND COLUMN_NAME = 'Id' AND TABLE_SCHEMA='HangFire')
BEGIN
ALTER TABLE [HangFire_dev].[Counter] DROP COLUMN [Id];
PRINT 'Dropped [HangFire_dev].[Counter].[Id] column';
END
ALTER TABLE [HangFire_dev].[Counter] ADD [Id] BIGINT IDENTITY(1, 1);
PRINT 'Created [HangFire_dev].[Counter].[Id] column';
ALTER TABLE [HangFire_dev].[Counter] ADD CONSTRAINT [PK_HangFire_Counter]
PRIMARY KEY CLUSTERED (
[Key] ASC,
[Id] ASC
);
PRINT 'Created clustered primary key PK_HangFire_Counter ([Key], [Id])';
-- SqlServerStorageOptions.UseIgnoreDupKeyOption will yield much better
results with INSERT/UPDATE operators
-- instead of MERGE for [Set] and [Hash] tables. This change is also
compatible with older clients, since
-- MERGE operator is used there, so those clients are forward-compatible with
these changes.
ALTER TABLE [HangFire_dev].[Set] REBUILD WITH (IGNORE_DUP_KEY = ON);
PRINT 'Enabled IGNORE_DUP_KEY option for [HangFire_dev].[Set] table';
ALTER TABLE [HangFire_dev].[Hash] REBUILD WITH (IGNORE_DUP_KEY = ON);
PRINT 'Enabled IGNORE_DUP_KEY option for [HangFire_dev].[Hash] table';
ALTER TABLE [HangFire_dev].[JobQueue] DROP CONSTRAINT [PK_HangFire_JobQueue];
PRINT 'Dropped constraint [PK_HangFire_JobQueue] to modify the
[HangFire_dev].[JobQueue].[Id] column';
ALTER TABLE [HangFire_dev].[JobQueue] ALTER COLUMN [Id] BIGINT NOT NULL;
PRINT 'Changed [HangFire_dev].[JobQueue].[Id] column type to BIGINT';
ALTER TABLE [HangFire_dev].[JobQueue] ADD CONSTRAINT [PK_HangFire_JobQueue]
PRIMARY KEY CLUSTERED (
[Queue] ASC,
[Id] ASC
);
PRINT 'Re-created constraint [PK_HangFire_JobQueue]';
SET @CURRENT_SCHEMA_VERSION = 8;
END
IF @CURRENT_SCHEMA_VERSION = 8
BEGIN
PRINT 'Installing schema version 9';
CREATE NONCLUSTERED INDEX [IX_HangFire_State_CreatedAt] ON [HangFire_dev].
[State] ([CreatedAt] ASC)
SET @CURRENT_SCHEMA_VERSION = 9;
END
/*IF @CURRENT_SCHEMA_VERSION = 9
BEGIN
PRINT 'Installing schema version 10';
Insert migration here
SET @CURRENT_SCHEMA_VERSION = 10;
END*/
UPDATE [HangFire_dev].[Schema] SET [Version] = @CURRENT_SCHEMA_VERSION
IF @@ROWCOUNT = 0
INSERT INTO [HangFire_dev].[Schema] ([Version]) VALUES
(@CURRENT_SCHEMA_VERSION)
PRINT 'Hangfire database schema installed';
COMMIT TRANSACTION;
PRINT 'Hangfire SQL objects installed';
