· 8 years ago · Nov 29, 2017, 07:10 PM
1-- this script is for SQL Server and Azure SQL
2
3USE quartzjobstore
4GO
5
6IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1)
7ALTER TABLE [dbo].[QRTZ_TRIGGERS] DROP CONSTRAINT FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS
8GO
9
10IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1)
11ALTER TABLE [dbo].[QRTZ_CRON_TRIGGERS] DROP CONSTRAINT FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS
12GO
13
14IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1)
15ALTER TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] DROP CONSTRAINT FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS
16GO
17
18IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1)
19ALTER TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] DROP CONSTRAINT FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS
20GO
21
22IF 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]'))
23ALTER TABLE [dbo].[QRTZ_JOB_LISTENERS] DROP CONSTRAINT [FK_QRTZ_JOB_LISTENERS_QRTZ_JOB_DETAILS]
24
25IF 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]'))
26ALTER TABLE [dbo].[QRTZ_TRIGGER_LISTENERS] DROP CONSTRAINT [FK_QRTZ_TRIGGER_LISTENERS_QRTZ_TRIGGERS]
27
28
29IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_CALENDARS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
30DROP TABLE [dbo].[QRTZ_CALENDARS]
31GO
32
33IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_CRON_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
34DROP TABLE [dbo].[QRTZ_CRON_TRIGGERS]
35GO
36
37IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_BLOB_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
38DROP TABLE [dbo].[QRTZ_BLOB_TRIGGERS]
39GO
40
41IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_FIRED_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
42DROP TABLE [dbo].[QRTZ_FIRED_TRIGGERS]
43GO
44
45IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_PAUSED_TRIGGER_GRPS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
46DROP TABLE [dbo].[QRTZ_PAUSED_TRIGGER_GRPS]
47GO
48
49IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QRTZ_JOB_LISTENERS]') AND type in (N'U'))
50DROP TABLE [dbo].[QRTZ_JOB_LISTENERS]
51
52IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_SCHEDULER_STATE]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
53DROP TABLE [dbo].[QRTZ_SCHEDULER_STATE]
54GO
55
56IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_LOCKS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
57DROP TABLE [dbo].[QRTZ_LOCKS]
58GO
59IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QRTZ_TRIGGER_LISTENERS]') AND type in (N'U'))
60DROP TABLE [dbo].[QRTZ_TRIGGER_LISTENERS]
61
62
63IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_JOB_DETAILS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
64DROP TABLE [dbo].[QRTZ_JOB_DETAILS]
65GO
66
67IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_SIMPLE_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
68DROP TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS]
69GO
70
71IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_SIMPROP_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
72DROP TABLE [dbo].QRTZ_SIMPROP_TRIGGERS
73GO
74
75IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
76DROP TABLE [dbo].[QRTZ_TRIGGERS]
77GO
78
79CREATE TABLE [dbo].[QRTZ_CALENDARS] (
80 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
81 [CALENDAR_NAME] [NVARCHAR] (200) NOT NULL ,
82 [CALENDAR] [IMAGE] NOT NULL
83)
84GO
85
86CREATE TABLE [dbo].[QRTZ_CRON_TRIGGERS] (
87 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
88 [TRIGGER_NAME] [NVARCHAR] (150) NOT NULL ,
89 [TRIGGER_GROUP] [NVARCHAR] (150) NOT NULL ,
90 [CRON_EXPRESSION] [NVARCHAR] (120) NOT NULL ,
91 [TIME_ZONE_ID] [NVARCHAR] (80)
92)
93GO
94
95CREATE TABLE [dbo].[QRTZ_FIRED_TRIGGERS] (
96 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
97 [ENTRY_ID] [NVARCHAR] (140) NOT NULL ,
98 [TRIGGER_NAME] [NVARCHAR] (150) NOT NULL ,
99 [TRIGGER_GROUP] [NVARCHAR] (150) NOT NULL ,
100 [INSTANCE_NAME] [NVARCHAR] (200) NOT NULL ,
101 [FIRED_TIME] [BIGINT] NOT NULL ,
102 [SCHED_TIME] [BIGINT] NOT NULL ,
103 [PRIORITY] [INTEGER] NOT NULL ,
104 [STATE] [NVARCHAR] (16) NOT NULL,
105 [JOB_NAME] [NVARCHAR] (150) NULL ,
106 [JOB_GROUP] [NVARCHAR] (150) NULL ,
107 [IS_NONCONCURRENT] BIT NULL ,
108 [REQUESTS_RECOVERY] BIT NULL
109)
110GO
111
112CREATE TABLE [dbo].[QRTZ_PAUSED_TRIGGER_GRPS] (
113 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
114 [TRIGGER_GROUP] [NVARCHAR] (150) NOT NULL
115)
116GO
117
118CREATE TABLE [dbo].[QRTZ_SCHEDULER_STATE] (
119 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
120 [INSTANCE_NAME] [NVARCHAR] (200) NOT NULL ,
121 [LAST_CHECKIN_TIME] [BIGINT] NOT NULL ,
122 [CHECKIN_INTERVAL] [BIGINT] NOT NULL
123)
124GO
125
126CREATE TABLE [dbo].[QRTZ_LOCKS] (
127 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
128 [LOCK_NAME] [NVARCHAR] (40) NOT NULL
129)
130GO
131
132CREATE TABLE [dbo].[QRTZ_JOB_DETAILS] (
133 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
134 [JOB_NAME] [NVARCHAR] (150) NOT NULL ,
135 [JOB_GROUP] [NVARCHAR] (150) NOT NULL ,
136 [DESCRIPTION] [NVARCHAR] (250) NULL ,
137 [JOB_CLASS_NAME] [NVARCHAR] (250) NOT NULL ,
138 [IS_DURABLE] BIT NOT NULL ,
139 [IS_NONCONCURRENT] BIT NOT NULL ,
140 [IS_UPDATE_DATA] BIT NOT NULL ,
141 [REQUESTS_RECOVERY] BIT NOT NULL ,
142 [JOB_DATA] [IMAGE] NULL
143)
144GO
145
146CREATE TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] (
147 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
148 [TRIGGER_NAME] [NVARCHAR] (150) NOT NULL ,
149 [TRIGGER_GROUP] [NVARCHAR] (150) NOT NULL ,
150 [REPEAT_COUNT] [INTEGER] NOT NULL ,
151 [REPEAT_INTERVAL] [BIGINT] NOT NULL ,
152 [TIMES_TRIGGERED] [INTEGER] NOT NULL
153)
154GO
155
156CREATE TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] (
157 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
158 [TRIGGER_NAME] [NVARCHAR] (150) NOT NULL ,
159 [TRIGGER_GROUP] [NVARCHAR] (150) NOT NULL ,
160 [STR_PROP_1] [NVARCHAR] (512) NULL,
161 [STR_PROP_2] [NVARCHAR] (512) NULL,
162 [STR_PROP_3] [NVARCHAR] (512) NULL,
163 [INT_PROP_1] [INT] NULL,
164 [INT_PROP_2] [INT] NULL,
165 [LONG_PROP_1] [BIGINT] NULL,
166 [LONG_PROP_2] [BIGINT] NULL,
167 [DEC_PROP_1] [NUMERIC] (13,4) NULL,
168 [DEC_PROP_2] [NUMERIC] (13,4) NULL,
169 [BOOL_PROP_1] BIT NULL,
170 [BOOL_PROP_2] BIT NULL,
171 [TIME_ZONE_ID] [NVARCHAR] (80) NULL
172)
173GO
174
175CREATE TABLE [dbo].[QRTZ_BLOB_TRIGGERS] (
176 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
177 [TRIGGER_NAME] [NVARCHAR] (150) NOT NULL ,
178 [TRIGGER_GROUP] [NVARCHAR] (150) NOT NULL ,
179 [BLOB_DATA] [IMAGE] NULL
180)
181GO
182
183CREATE TABLE [dbo].[QRTZ_TRIGGERS] (
184 [SCHED_NAME] [NVARCHAR] (120) NOT NULL ,
185 [TRIGGER_NAME] [NVARCHAR] (150) NOT NULL ,
186 [TRIGGER_GROUP] [NVARCHAR] (150) NOT NULL ,
187 [JOB_NAME] [NVARCHAR] (150) NOT NULL ,
188 [JOB_GROUP] [NVARCHAR] (150) NOT NULL ,
189 [DESCRIPTION] [NVARCHAR] (250) NULL ,
190 [NEXT_FIRE_TIME] [BIGINT] NULL ,
191 [PREV_FIRE_TIME] [BIGINT] NULL ,
192 [PRIORITY] [INTEGER] NULL ,
193 [TRIGGER_STATE] [NVARCHAR] (16) NOT NULL ,
194 [TRIGGER_TYPE] [NVARCHAR] (8) NOT NULL ,
195 [START_TIME] [BIGINT] NOT NULL ,
196 [END_TIME] [BIGINT] NULL ,
197 [CALENDAR_NAME] [NVARCHAR] (200) NULL ,
198 [MISFIRE_INSTR] [INTEGER] NULL ,
199 [JOB_DATA] [IMAGE] NULL
200)
201GO
202
203ALTER TABLE [dbo].[QRTZ_CALENDARS] WITH NOCHECK ADD
204 CONSTRAINT [PK_QRTZ_CALENDARS] PRIMARY KEY CLUSTERED
205 (
206 [SCHED_NAME],
207 [CALENDAR_NAME]
208 )
209GO
210
211ALTER TABLE [dbo].[QRTZ_CRON_TRIGGERS] WITH NOCHECK ADD
212 CONSTRAINT [PK_QRTZ_CRON_TRIGGERS] PRIMARY KEY CLUSTERED
213 (
214 [SCHED_NAME],
215 [TRIGGER_NAME],
216 [TRIGGER_GROUP]
217 )
218GO
219
220ALTER TABLE [dbo].[QRTZ_FIRED_TRIGGERS] WITH NOCHECK ADD
221 CONSTRAINT [PK_QRTZ_FIRED_TRIGGERS] PRIMARY KEY CLUSTERED
222 (
223 [SCHED_NAME],
224 [ENTRY_ID]
225 )
226GO
227
228ALTER TABLE [dbo].[QRTZ_PAUSED_TRIGGER_GRPS] WITH NOCHECK ADD
229 CONSTRAINT [PK_QRTZ_PAUSED_TRIGGER_GRPS] PRIMARY KEY CLUSTERED
230 (
231 [SCHED_NAME],
232 [TRIGGER_GROUP]
233 )
234GO
235
236ALTER TABLE [dbo].[QRTZ_SCHEDULER_STATE] WITH NOCHECK ADD
237 CONSTRAINT [PK_QRTZ_SCHEDULER_STATE] PRIMARY KEY CLUSTERED
238 (
239 [SCHED_NAME],
240 [INSTANCE_NAME]
241 )
242GO
243
244ALTER TABLE [dbo].[QRTZ_LOCKS] WITH NOCHECK ADD
245 CONSTRAINT [PK_QRTZ_LOCKS] PRIMARY KEY CLUSTERED
246 (
247 [SCHED_NAME],
248 [LOCK_NAME]
249 )
250GO
251
252ALTER TABLE [dbo].[QRTZ_JOB_DETAILS] WITH NOCHECK ADD
253 CONSTRAINT [PK_QRTZ_JOB_DETAILS] PRIMARY KEY CLUSTERED
254 (
255 [SCHED_NAME],
256 [JOB_NAME],
257 [JOB_GROUP]
258 )
259GO
260
261ALTER TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] WITH NOCHECK ADD
262 CONSTRAINT [PK_QRTZ_SIMPLE_TRIGGERS] PRIMARY KEY CLUSTERED
263 (
264 [SCHED_NAME],
265 [TRIGGER_NAME],
266 [TRIGGER_GROUP]
267 )
268GO
269
270ALTER TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] WITH NOCHECK ADD
271 CONSTRAINT [PK_QRTZ_SIMPROP_TRIGGERS] PRIMARY KEY CLUSTERED
272 (
273 [SCHED_NAME],
274 [TRIGGER_NAME],
275 [TRIGGER_GROUP]
276 )
277GO
278
279ALTER TABLE [dbo].[QRTZ_TRIGGERS] WITH NOCHECK ADD
280 CONSTRAINT [PK_QRTZ_TRIGGERS] PRIMARY KEY CLUSTERED
281 (
282 [SCHED_NAME],
283 [TRIGGER_NAME],
284 [TRIGGER_GROUP]
285 )
286GO
287
288ALTER TABLE [dbo].QRTZ_BLOB_TRIGGERS WITH NOCHECK ADD
289 CONSTRAINT [PK_QRTZ_BLOB_TRIGGERS] PRIMARY KEY CLUSTERED
290 (
291 [SCHED_NAME],
292 [TRIGGER_NAME],
293 [TRIGGER_GROUP]
294 )
295GO
296
297ALTER TABLE [dbo].[QRTZ_CRON_TRIGGERS] ADD
298 CONSTRAINT [FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS] FOREIGN KEY
299 (
300 [SCHED_NAME],
301 [TRIGGER_NAME],
302 [TRIGGER_GROUP]
303 ) REFERENCES [dbo].[QRTZ_TRIGGERS] (
304 [SCHED_NAME],
305 [TRIGGER_NAME],
306 [TRIGGER_GROUP]
307 ) ON DELETE CASCADE
308GO
309
310ALTER TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] ADD
311 CONSTRAINT [FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS] FOREIGN KEY
312 (
313 [SCHED_NAME],
314 [TRIGGER_NAME],
315 [TRIGGER_GROUP]
316 ) REFERENCES [dbo].[QRTZ_TRIGGERS] (
317 [SCHED_NAME],
318 [TRIGGER_NAME],
319 [TRIGGER_GROUP]
320 ) ON DELETE CASCADE
321GO
322
323ALTER TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] ADD
324 CONSTRAINT [FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS] FOREIGN KEY
325 (
326 [SCHED_NAME],
327 [TRIGGER_NAME],
328 [TRIGGER_GROUP]
329 ) REFERENCES [dbo].[QRTZ_TRIGGERS] (
330 [SCHED_NAME],
331 [TRIGGER_NAME],
332 [TRIGGER_GROUP]
333 ) ON DELETE CASCADE
334GO
335
336ALTER TABLE [dbo].[QRTZ_TRIGGERS] ADD
337 CONSTRAINT [FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS] FOREIGN KEY
338 (
339 [SCHED_NAME],
340 [JOB_NAME],
341 [JOB_GROUP]
342 ) REFERENCES [dbo].[QRTZ_JOB_DETAILS] (
343 [SCHED_NAME],
344 [JOB_NAME],
345 [JOB_GROUP]
346 )
347GO
348
349CREATE INDEX IDX_QRTZ_T_J ON QRTZ_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP)
350CREATE INDEX IDX_QRTZ_T_JG ON QRTZ_TRIGGERS(SCHED_NAME,JOB_GROUP)
351CREATE INDEX IDX_QRTZ_T_C ON QRTZ_TRIGGERS(SCHED_NAME,CALENDAR_NAME)
352CREATE INDEX IDX_QRTZ_T_G ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP)
353CREATE INDEX IDX_QRTZ_T_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE)
354CREATE INDEX IDX_QRTZ_T_N_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE)
355CREATE INDEX IDX_QRTZ_T_N_G_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE)
356CREATE INDEX IDX_QRTZ_T_NEXT_FIRE_TIME ON QRTZ_TRIGGERS(SCHED_NAME,NEXT_FIRE_TIME)
357CREATE INDEX IDX_QRTZ_T_NFT_ST ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME)
358CREATE INDEX IDX_QRTZ_T_NFT_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME)
359CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE)
360CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE_GRP ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE)
361
362CREATE INDEX IDX_QRTZ_FT_TRIG_INST_NAME ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME)
363CREATE INDEX IDX_QRTZ_FT_INST_JOB_REQ_RCVRY ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY)
364CREATE INDEX IDX_QRTZ_FT_J_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP)
365CREATE INDEX IDX_QRTZ_FT_JG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_GROUP)
366CREATE INDEX IDX_QRTZ_FT_T_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
367CREATE INDEX IDX_QRTZ_FT_TG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_GROUP)
368GO