Quartz.net 3.x使用总结(二)——Db持久化和集群
时间：2021-07-01 10:21:17
帮助过：10人阅读
this script is for SQL Server and Azure SQL
create database [QuartzDb]
go
USE [QuartzDb]
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS]‘)
AND OBJECTPROPERTY(id, N
‘ISFOREIGNKEY‘)
= 1)
ALTER TABLE [dbo].
[QRTZ_TRIGGERS] DROP CONSTRAINT FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS]‘)
AND OBJECTPROPERTY(id, N
‘ISFOREIGNKEY‘)
= 1)
ALTER TABLE [dbo].
[QRTZ_CRON_TRIGGERS] DROP CONSTRAINT FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS]‘)
AND OBJECTPROPERTY(id, N
‘ISFOREIGNKEY‘)
= 1)
ALTER TABLE [dbo].
[QRTZ_SIMPLE_TRIGGERS] DROP CONSTRAINT FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS]‘)
AND OBJECTPROPERTY(id, N
‘ISFOREIGNKEY‘)
= 1)
ALTER TABLE [dbo].
[QRTZ_SIMPROP_TRIGGERS] DROP CONSTRAINT FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS
GO
IF EXISTS (
SELECT * FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N
‘[dbo].[FK_QRTZ_JOB_LISTENERS_QRTZ_JOB_DETAILS]‘)
AND parent_object_id
= OBJECT_ID(N
‘[dbo].[QRTZ_JOB_LISTENERS]‘))
ALTER TABLE [dbo].
[QRTZ_JOB_LISTENERS] DROP CONSTRAINT [FK_QRTZ_JOB_LISTENERS_QRTZ_JOB_DETAILS]
IF EXISTS (
SELECT * FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N
‘[dbo].[FK_QRTZ_TRIGGER_LISTENERS_QRTZ_TRIGGERS]‘)
AND parent_object_id
= OBJECT_ID(N
‘[dbo].[QRTZ_TRIGGER_LISTENERS]‘))
ALTER TABLE [dbo].
[QRTZ_TRIGGER_LISTENERS] DROP CONSTRAINT [FK_QRTZ_TRIGGER_LISTENERS_QRTZ_TRIGGERS]
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_CALENDARS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_CALENDARS]
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_CRON_TRIGGERS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_CRON_TRIGGERS]
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_BLOB_TRIGGERS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_BLOB_TRIGGERS]
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_FIRED_TRIGGERS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_FIRED_TRIGGERS]
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_PAUSED_TRIGGER_GRPS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_PAUSED_TRIGGER_GRPS]
GO
IF EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N
‘[dbo].[QRTZ_JOB_LISTENERS]‘)
AND type
in (N
‘U‘))
DROP TABLE [dbo].
[QRTZ_JOB_LISTENERS]
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_SCHEDULER_STATE]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_SCHEDULER_STATE]
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_LOCKS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_LOCKS]
GO
IF EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N
‘[dbo].[QRTZ_TRIGGER_LISTENERS]‘)
AND type
in (N
‘U‘))
DROP TABLE [dbo].
[QRTZ_TRIGGER_LISTENERS]
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_JOB_DETAILS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_JOB_DETAILS]
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_SIMPLE_TRIGGERS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_SIMPLE_TRIGGERS]
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_SIMPROP_TRIGGERS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].QRTZ_SIMPROP_TRIGGERS
GO
IF EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id
= OBJECT_ID(N
‘[dbo].[QRTZ_TRIGGERS]‘)
AND OBJECTPROPERTY(id, N
‘ISUSERTABLE‘)
= 1)
DROP TABLE [dbo].
[QRTZ_TRIGGERS]
GO
CREATE TABLE [dbo].
[QRTZ_CALENDARS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[CALENDAR_NAME] [NVARCHAR] (
200)
NOT NULL ,
[CALENDAR] [VARBINARY](
MAX)
NOT NULL
)
GO
CREATE TABLE [dbo].
[QRTZ_CRON_TRIGGERS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[TRIGGER_NAME] [NVARCHAR] (
150)
NOT NULL ,
[TRIGGER_GROUP] [NVARCHAR] (
150)
NOT NULL ,
[CRON_EXPRESSION] [NVARCHAR] (
120)
NOT NULL ,
[TIME_ZONE_ID] [NVARCHAR] (
80)
)
GO
CREATE TABLE [dbo].
[QRTZ_FIRED_TRIGGERS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[ENTRY_ID] [NVARCHAR] (
140)
NOT NULL ,
[TRIGGER_NAME] [NVARCHAR] (
150)
NOT NULL ,
[TRIGGER_GROUP] [NVARCHAR] (
150)
NOT NULL ,
[INSTANCE_NAME] [NVARCHAR] (
200)
NOT NULL ,
[FIRED_TIME] [BIGINT] NOT NULL ,
[SCHED_TIME] [BIGINT] NOT NULL ,
[PRIORITY] [INTEGER] NOT NULL ,
[STATE] [NVARCHAR] (
16)
NOT NULL,
[JOB_NAME] [NVARCHAR] (
150)
NULL ,
[JOB_GROUP] [NVARCHAR] (
150)
NULL ,
[IS_NONCONCURRENT] BIT NULL ,
[REQUESTS_RECOVERY] BIT NULL
)
GO
CREATE TABLE [dbo].
[QRTZ_PAUSED_TRIGGER_GRPS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[TRIGGER_GROUP] [NVARCHAR] (
150)
NOT NULL
)
GO
CREATE TABLE [dbo].
[QRTZ_SCHEDULER_STATE] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[INSTANCE_NAME] [NVARCHAR] (
200)
NOT NULL ,
[LAST_CHECKIN_TIME] [BIGINT] NOT NULL ,
[CHECKIN_INTERVAL] [BIGINT] NOT NULL
)
GO
CREATE TABLE [dbo].
[QRTZ_LOCKS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[LOCK_NAME] [NVARCHAR] (
40)
NOT NULL
)
GO
CREATE TABLE [dbo].
[QRTZ_JOB_DETAILS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[JOB_NAME] [NVARCHAR] (
150)
NOT NULL ,
[JOB_GROUP] [NVARCHAR] (
150)
NOT NULL ,
[DESCRIPTION] [NVARCHAR] (
250)
NULL ,
[JOB_CLASS_NAME] [NVARCHAR] (
250)
NOT NULL ,
[IS_DURABLE] BIT NOT NULL ,
[IS_NONCONCURRENT] BIT NOT NULL ,
[IS_UPDATE_DATA] BIT NOT NULL ,
[REQUESTS_RECOVERY] BIT NOT NULL ,
[JOB_DATA] [VARBINARY](
MAX)
NULL
)
GO
CREATE TABLE [dbo].
[QRTZ_SIMPLE_TRIGGERS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[TRIGGER_NAME] [NVARCHAR] (
150)
NOT NULL ,
[TRIGGER_GROUP] [NVARCHAR] (
150)
NOT NULL ,
[REPEAT_COUNT] [INTEGER] NOT NULL ,
[REPEAT_INTERVAL] [BIGINT] NOT NULL ,
[TIMES_TRIGGERED] [INTEGER] NOT NULL
)
GO
CREATE TABLE [dbo].
[QRTZ_SIMPROP_TRIGGERS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[TRIGGER_NAME] [NVARCHAR] (
150)
NOT NULL ,
[TRIGGER_GROUP] [NVARCHAR] (
150)
NOT NULL ,
[STR_PROP_1] [NVARCHAR] (
512)
NULL,
[STR_PROP_2] [NVARCHAR] (
512)
NULL,
[STR_PROP_3] [NVARCHAR] (
512)
NULL,
[INT_PROP_1] [INT] NULL,
[INT_PROP_2] [INT] NULL,
[LONG_PROP_1] [BIGINT] NULL,
[LONG_PROP_2] [BIGINT] NULL,
[DEC_PROP_1] [NUMERIC] (
13,
4)
NULL,
[DEC_PROP_2] [NUMERIC] (
13,
4)
NULL,
[BOOL_PROP_1] BIT NULL,
[BOOL_PROP_2] BIT NULL,
[TIME_ZONE_ID] [NVARCHAR] (
80)
NULL
)
GO
CREATE TABLE [dbo].
[QRTZ_BLOB_TRIGGERS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[TRIGGER_NAME] [NVARCHAR] (
150)
NOT NULL ,
[TRIGGER_GROUP] [NVARCHAR] (
150)
NOT NULL ,
[BLOB_DATA] [VARBINARY](
MAX)
NULL
)
GO
CREATE TABLE [dbo].
[QRTZ_TRIGGERS] (
[SCHED_NAME] [NVARCHAR] (
120)
NOT NULL ,
[TRIGGER_NAME] [NVARCHAR] (
150)
NOT NULL ,
[TRIGGER_GROUP] [NVARCHAR] (
150)
NOT NULL ,
[JOB_NAME] [NVARCHAR] (
150)
NOT NULL ,
[JOB_GROUP] [NVARCHAR] (
150)
NOT NULL ,
[DESCRIPTION] [NVARCHAR] (
250)
NULL ,
[NEXT_FIRE_TIME] [BIGINT] NULL ,
[PREV_FIRE_TIME] [BIGINT] NULL ,
[PRIORITY] [INTEGER] NULL ,
[TRIGGER_STATE] [NVARCHAR] (
16)
NOT NULL ,
[TRIGGER_TYPE] [NVARCHAR] (
8)
NOT NULL ,
[START_TIME] [BIGINT] NOT NULL ,
[END_TIME] [BIGINT] NULL ,
[CALENDAR_NAME] [NVARCHAR] (
200)
NULL ,
[MISFIRE_INSTR] [INTEGER] NULL ,
[JOB_DATA] [VARBINARY](
MAX)
NULL
)
GO
ALTER TABLE [dbo].
[QRTZ_CALENDARS] WITH NOCHECK ADD
CONSTRAINT [PK_QRTZ_CALENDARS] PRIMARY KEY CLUSTERED
(
[SCHED_NAME],
[CALENDAR_NAME]
)
GO
ALTER TABLE [dbo].
[QRTZ_CRON_TRIGGERS] WITH NOCHECK ADD
CONSTRAINT [PK_QRTZ_CRON_TRIGGERS] PRIMARY KEY CLUSTERED
(
[SCHED_NAME],
[TRIGGER_NAME],
[TRIGGER_GROUP]
)
GO
ALTER TABLE [dbo].
[QRTZ_FIRED_TRIGGERS] WITH NOCHECK ADD
CONSTRAINT [PK_QRTZ_FIRED_TRIGGERS] PRIMARY KEY CLUSTERED
(
[SCHED_NAME],
[ENTRY_ID]
)
GO
ALTER TABLE [dbo].
[QRTZ_PAUSED_TRIGGER_GRPS] WITH NOCHECK ADD
CONSTRAINT [PK_QRTZ_PAUSED_TRIGGER_GRPS] PRIMARY KEY CLUSTERED
(
[
