澳门新萄京规范化的SQL数据修改语句总计

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[PLSystem]’) AND type in (N’U’))
DROP TABLE [dbo].[PLSystem]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[PLSystem]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[PLSystem](
[PLSystemID] [tinyint] NOT NULL,
[PLSystemName] [varchar](20) NULL,
[PLSystemFlag] [char](2) NULL,
CONSTRAINT [XPKPLSystem] PRIMARY KEY CLUSTERED
(
[PLSystemID] ASC
)
) ON [PRIMARY]
END
GO

INSERT INTO [User] (loginname, password, usernumber, name)
VALUES(‘admin’, ‘admin’, ‘001’, ‘管理员’)

动用sqlserver搭建高可用双机热备的Quartz集群铺排【附源码】,双机热备quartz

  一般拿Timer和Quartz相比较的,几乎就是对Quartz的污辱,两个的作用根本就不在贰个层级上,如本篇介绍的Quartz庞大的集群机制,能够运用基于

sqlserver,mysql的集群方案,当然还足以在第三方插件的根基上实现quartz系列化到热炒的mongodb,redis,震惊力总来讲之,接下去本篇就和豪门聊

一聊怎么搭建基于sqlserver的quartz集群,实现这样一种双机热备的强劲成效。

 

一:下载sqlserver版的建表脚本

   
首先大家可以通过github上寻找quartz的源代码,在源码项指标/database/tables目录下,能够找到firebird,oracle,mysql,sqlserver等建库脚本,

本篇只需拿取sqlserver版本就可以。  
如下图所示

 

澳门新萄京 1

澳门新萄京 2

 

   

   
 从地方的截图中可以见见,笔者接下去要做的专门的学问正是加多二个你要求成立的database名字,这里取为:【quartz】,完整的本子如下:

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

 

澳门新萄京 4

 

二:配置quartz的集群参数

    当大家写var scheduler =
StdSchedulerFactory.GetDefaultScheduler()这段代码的时候,假设大家看过源码的话,会分晓那些GetScheduler的

进度中有一个开首化方法【Instantiate】方法,此格局中您会开采在做DBProvider的时候会须求多少个参数来开头化DB的,比如上边看看的多少个标红军家属性。

 1             IList<string> dsNames = cfg.GetPropertyGroups(PropertyDataSourcePrefix);
 2             foreach (string dataSourceName in dsNames)
 3             {
 4                 string datasourceKey = "{0}.{1}".FormatInvariant(PropertyDataSourcePrefix, dataSourceName);
 5                 NameValueCollection propertyGroup = cfg.GetPropertyGroup(datasourceKey, true);
 6                 PropertiesParser pp = new PropertiesParser(propertyGroup);
 7 
 8                 Type cpType = loadHelper.LoadType(pp.GetStringProperty(PropertyDbProviderType, null));
 9 
10                 // custom connectionProvider...
11                 if (cpType != null)
12                 {
13                     IDbProvider cp;
14                     try
15                     {
16                         cp = ObjectUtils.InstantiateType<IDbProvider>(cpType);
17                     }
18                     catch (Exception e)
19                     {
20                         initException = new SchedulerException("ConnectionProvider of type '{0}' could not be instantiated.".FormatInvariant(cpType), e);
21                         throw initException;
22                     }
23 
24                     try
25                     {
26                         // remove the type name, so it isn't attempted to be set
27                         pp.UnderlyingProperties.Remove(PropertyDbProviderType);
28 
29                         ObjectUtils.SetObjectProperties(cp, pp.UnderlyingProperties);
30                         cp.Initialize();
31                     }
32                     catch (Exception e)
33                     {
34                         initException = new SchedulerException("ConnectionProvider type '{0}' props could not be configured.".FormatInvariant(cpType), e);
35                         throw initException;
36                     }
37 
38                     dbMgr = DBConnectionManager.Instance;
39                     dbMgr.AddConnectionProvider(dataSourceName, cp);
40                 }
41                 else
42                 {
43                     string dsProvider = pp.GetStringProperty(PropertyDataSourceProvider, null);
44                     string dsConnectionString = pp.GetStringProperty(PropertyDataSourceConnectionString, null);
45                     string dsConnectionStringName = pp.GetStringProperty(PropertyDataSourceConnectionStringName, null);
46 
47                     if (dsConnectionString == null && !String.IsNullOrEmpty(dsConnectionStringName))
48                     {
49 
50                         ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings[dsConnectionStringName];
51                         if (connectionStringSettings == null)
52                         {
53                             initException = new SchedulerException("Named connection string '{0}' not found for DataSource: {1}".FormatInvariant(dsConnectionStringName, dataSourceName));
54                             throw initException;
55                         }
56                         dsConnectionString = connectionStringSettings.ConnectionString;
57                     }
58 
59                     if (dsProvider == null)
60                     {
61                         initException = new SchedulerException("Provider not specified for DataSource: {0}".FormatInvariant(dataSourceName));
62                         throw initException;
63                     }
64                     if (dsConnectionString == null)
65                     {
66                         initException = new SchedulerException("Connection string not specified for DataSource: {0}".FormatInvariant(dataSourceName));
67                         throw initException;
68                     }
69                     try
70                     {
71                         DbProvider dbp = new DbProvider(dsProvider, dsConnectionString);
72                         dbp.Initialize();
73 
74                         dbMgr = DBConnectionManager.Instance;
75                         dbMgr.AddConnectionProvider(dataSourceName, dbp);
76                     }
77                     catch (Exception exception)
78                     {
79                         initException = new SchedulerException("Could not Initialize DataSource: {0}".FormatInvariant(dataSourceName), exception);
80                         throw initException;
81                     }
82                 }
83             }

 

   
 接下来的标题就是那多少个属性是何等安插进去的,留神察看地点代码,你会意识全部的配置的源流都源于于cfg变量,ok,接下去你能够持续翻看代码,相信

你拜访到有二个Initialize方法正是做cfg变量的发轫化,如下代码所示:

 1   public void Initialize()
 2         {
 3             // short-circuit if already initialized
 4             if (cfg != null)
 5             {
 6                 return;
 7             }
 8             if (initException != null)
 9             {
10                 throw initException;
11             }
12 
13             NameValueCollection props = (NameValueCollection) ConfigurationManager.GetSection(ConfigurationSectionName);
14 
15             string requestedFile = QuartzEnvironment.GetEnvironmentVariable(PropertiesFile);
16 
17             string propFileName = requestedFile != null && requestedFile.Trim().Length > 0 ? requestedFile : "~/quartz.config";
18 
19             // check for specials
20             try
21             {
22                 propFileName = FileUtil.ResolveFile(propFileName);
23             }
24             catch (SecurityException)
25             {
26                 log.WarnFormat("Unable to resolve file path '{0}' due to security exception, probably running under medium trust");
27                 propFileName = "quartz.config";
28             }
29 
30             if (props == null && File.Exists(propFileName))
31             {
32                 // file system
33                 try
34                 {
35                     PropertiesParser pp = PropertiesParser.ReadFromFileResource(propFileName);
36                     props = pp.UnderlyingProperties;
37                     Log.Info(string.Format("Quartz.NET properties loaded from configuration file '{0}'", propFileName));
38                 }
39                 catch (Exception ex)
40                 {
41                     Log.Error("Could not load properties for Quartz from file {0}: {1}".FormatInvariant(propFileName, ex.Message), ex);
42                 }
43 
44             }
45             if (props == null)
46             {
47                 // read from assembly
48                 try
49                 {
50                     PropertiesParser pp = PropertiesParser.ReadFromEmbeddedAssemblyResource("Quartz.quartz.config");
51                     props = pp.UnderlyingProperties;
52                     Log.Info("Default Quartz.NET properties loaded from embedded resource file");
53                 }
54                 catch (Exception ex)
55                 {
56                     Log.Error("Could not load default properties for Quartz from Quartz assembly: {0}".FormatInvariant(ex.Message), ex);
57                 }
58             }
59             if (props == null)
60             {
61                 throw new SchedulerConfigException(
62                     @"Could not find <quartz> configuration section from your application config or load default configuration from assembly.
63 Please add configuration to your application config file to correctly initialize Quartz.");
64             }
65             Initialize(OverrideWithSysProps(props));
66         }

     

   
 稳重阅读方面包车型大巴一串代码,你会发觉,暗中认可quartz参数配置来源于多个地方。

  1. app.config中的section节点。

  2. bin目录下的~/quartz.config文件。

3.
默许配置的NameValueCollection字典集结,也等于上一篇博客给大家做的一个示范。

   

   
 小编个人不怎么喜欢通过quartz.config文件举行布署,那样也轻便写死,所以自身要么喜欢使用最轻巧易行的NameValueCollection配置,因为它的多寡源可来源

于第三方存款和储蓄结构中,配置代码如下:

 1                 //1.首先创建一个作业调度池
 2                 var properties = new NameValueCollection();
 3                 //存储类型
 4                 properties["quartz.jobStore.type"] = "Quartz.Impl.AdoJobStore.JobStoreTX, Quartz";
 5 
 6                 //驱动类型
 7                 properties["quartz.jobStore.driverDelegateType"] = "Quartz.Impl.AdoJobStore.SqlServerDelegate, Quartz";                //数据源名称
 8                 properties["quartz.jobStore.dataSource"] = "myDS";
 9 
10                 //连接字符串
11                 properties["quartz.dataSource.myDS.connectionString"] = @"server=.;Initial Catalog=quartz;Integrated Security=True";
12                 //sqlserver版本
13                 properties["quartz.dataSource.myDS.provider"] = "SqlServer-20";
14 
15                 //是否集群
16                 properties["quartz.jobStore.clustered"] = "true";
17                 properties["quartz.scheduler.instanceId"] = "AUTO";

 

   
上面包车型大巴代码配置作者都加过详细的笺注,咱们应该都能看得懂,并且那几个安插便是这么定死的,没什么修改的长空,我们记住就可以。

 

三:Job和Trigger定义

   
 在集群中情状下,job和trigger的概念该怎么写的?咱们也绝不想的太复杂,注意一点就可以了,在Schedule叁个Job时候,通过CheckExists判别一下

以此Job在Scheduler中是不是业已存在了,假若存在,你就不可能重复经过Schedule去重新调解三个Job就可以了。。。所以推断的代码也很简单,如下所示:

 1          IScheduler scheduler = factory.GetScheduler();
 2 
 3                 scheduler.Start();
 4 
 5                 var jobKey = JobKey.Create("myjob", "group");
 6 
 7                 if (scheduler.CheckExists(jobKey))
 8                 {
 9                     Console.WriteLine("当前job已经存在,无需调度:{0}", jobKey.ToString());
10                 }
11                 else
12                 {
13                     IJobDetail job = JobBuilder.Create<HelloJob>()
14                            .WithDescription("使用quartz进行持久化存储")
15                            .StoreDurably()
16                            .RequestRecovery()
17                            .WithIdentity(jobKey)
18                            .UsingJobData("count", 1)
19                            .Build();
20 
21                     ITrigger trigger = TriggerBuilder.Create().WithSimpleSchedule(x => x.WithIntervalInSeconds(2).RepeatForever())
22                                                               .Build();
23 
24                     scheduler.ScheduleJob(job, trigger);
25 
26                     Console.WriteLine("调度进行中!!!");
27                 }

 

上面这段代码,我们就足以布置在多台机器中了,是还是不是很轻易?

 

四:庞大的cluster完整演示

   

   
 全体的开始化事业都做完了,接下去大家copy一份bin文件,同不平时间张开八个console程序,如下所示,能够看出job职责只会被一个console调整,别的

三个在空等待。

澳门新萄京 5

 

     
 然后你肯定很诧异的跑到sqlserver中去看看,是不是早就有job和trigger的db存储,很欢喜呢,数据都有的。。。

澳门新萄京 6

 

     
 好了,一切都以那么完美,接下去能够显得一下quartz集群下的高可用啦,若是某三个console挂了,那么另一台console会把这些职分给接过来,实

现壮大的高可用。。。所以本人要做的事体就是把console1关掉,再看看console2是否足以开始调治job了???

澳门新萄京 7

 

到家,那个正是本篇给大家介绍的Quartz的Cluster集群,一台挂,另一台顶住,双机热备,当然那些console你能够布署在多台机械中,要做的正是维持各

个server的年华一同,因为quarz是重视于本机server的年月,好了,本篇就先提及此处吧。

 

小礼品走一波,双击666。。。  完整代码:SimpleSchedulerApp.zip

 

一般拿Timer和Quartz绝相比的,大致正是对Quartz的侮辱,两个的功…

2 存款和储蓄进度

SQL语句的转变?
在SQL3000数据库中能够用上面语句创设数量及表,我想在Mysql数据库也用此语句成立,但会冒出语法错误。请大家帮一下忙,把改成在MYSQL数据库也能运转的话语,但不能够更动其余的。只修改语法错误。。。

复制代码 代码如下:

–1
CREATE DATABASE [FFMS]

扩充字段 复制代码 代码如下: IF NOT EXISTS
(SELECT * FROM SYSCOLUMNS WHERE ID=OBJECT_ID(‘QueryDataSource’) and
name = ‘IsCrossTable’) BEGIN ALTER TABLE QueryDataSource…

–2
USE [FFMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[Account]’) AND OBJECTPROPERTY(id,
N’IsUserTable’) = 1)
BEGIN
CREATE TABLE [dbo].[Account](
[id] [int] IDENTITY NOT NULL,
[userid] [int] NULL,
[accountitemid] [int] NULL,
[amount] [decimal] NULL,
[accountdate] [datetime] NULL,
[remarks] [varchar] NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[User]’) AND OBJECTPROPERTY(id, N’IsUserTable’) =
1)
BEGIN
CREATE TABLE [dbo].[User](
[id] [int] IDENTITY NOT NULL,
[loginname] [varchar] NULL,
[password] [varchar] NULL,
[usernumber] [varchar] NULL,
[name] [varchar] NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[AccountItem]’) AND OBJECTPROPERTY(id,
N’IsUserTable’) = 1)
BEGIN
CREATE TABLE [dbo].[AccountItem](
[id] [int] IDENTITY NOT NULL,
[pid] [int] NULL,
[accountitemnumber] [varchar] NULL,
[name] [varchar] NULL,
[type] [int] NULL,
CONSTRAINT [PK_AccountItem] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[ViewAccount]’) AND OBJECTPROPERTY(id, N’IsView’)
= 1)
EXEC dbo.sp_executesql @statement = N’CREATE VIEW
[dbo].[ViewAccount]
AS
SELECT dbo.Account.id, dbo.Account.amount, dbo.Account.accountdate,
dbo.Account.remarks, dbo.Account.userid, dbo.[User].loginname,
dbo.[User].usernumber, dbo.[User].name AS username,
dbo.Account.accountitemid, dbo.AccountItem.accountitemnumber,
dbo.AccountItem.name AS accountitemname, dbo.AccountItem.type AS
accountitemtype, dbo.AccountItem.pid AS asaccountitempid,
ParentAccountItem.accountitemnumber AS accountitempnumber,
ParentAccountItem.name AS accountitempname
FROM dbo.Account INNER JOIN
dbo.[User] ON dbo.Account.userid = dbo.[User].id INNER JOIN
dbo.AccountItem ON dbo.Account.accountitemid = dbo.AccountItem.id INNER
JOIN
dbo.AccountItem AS ParentAccountItem ON dbo.AccountItem.pid =
ParentAccountItem.id

GO

1 增添字段

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PLSystem_Insert]’) and OBJECTPROPERTY(id,
N’IsProcedure’) = 1)
drop procedure [dbo].[PLSystem_Insert]
go
CREATE PROCEDURE dbo.PLSystem_Insert
(
@PLSystemID smallint,
@PLSystemName varchar(20),
@PLSystemFlag char(2)
)
AS
INSERT INTO dbo.PLSystem
(
PLSystemID,
PLSystemName,
PLSystemFlag
)
VALUES
(
@PLSystemID,
@PLSystemName,
@PLSystemFlag
)
go

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图