public int UpdateExtendedTriggerProperties(ConnectionAndTransactionHolder conn, IOperableTrigger trigger, string state, IJobDetail jobDetail)
{
SimplePropertiesTriggerProperties properties = GetTriggerProperties(trigger);
using (IDbCommand cmd = adoUtil.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(UpdateSimplePropsTrigger, tablePrefix, schedNameLiteral)))
{
adoUtil.AddCommandParameter(cmd, "string1", properties.String1);
adoUtil.AddCommandParameter(cmd, "string1", properties.String2);
adoUtil.AddCommandParameter(cmd, "string3", properties.String3);
adoUtil.AddCommandParameter(cmd, "int1", properties.Int1);
adoUtil.AddCommandParameter(cmd, "int2", properties.Int2);
adoUtil.AddCommandParameter(cmd, "long1", properties.Long1);
adoUtil.AddCommandParameter(cmd, "long2", properties.Long2);
adoUtil.AddCommandParameter(cmd, "decimal1", properties.Decimal1);
adoUtil.AddCommandParameter(cmd, "decimal2", properties.Decimal2);
adoUtil.AddCommandParameter(cmd, "boolean1", properties.Boolean1);
adoUtil.AddCommandParameter(cmd, "boolean2", properties.Boolean2);
adoUtil.AddCommandParameter(cmd, "triggerName", trigger.Key.Name);
adoUtil.AddCommandParameter(cmd, "triggerGroup", trigger.Key.Group);
return(cmd.ExecuteNonQuery());
}
}
public async Task <int> UpdateExtendedTriggerProperties(ConnectionAndTransactionHolder conn, IOperableTrigger trigger, string state, IJobDetail jobDetail)
{
SimplePropertiesTriggerProperties properties = GetTriggerProperties(trigger);
using (var cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(UpdateSimplePropsTrigger, TablePrefix, SchedNameLiteral)))
{
DbAccessor.AddCommandParameter(cmd, "string1", properties.String1);
DbAccessor.AddCommandParameter(cmd, "string2", properties.String2);
DbAccessor.AddCommandParameter(cmd, "string3", properties.String3);
DbAccessor.AddCommandParameter(cmd, "int1", properties.Int1);
DbAccessor.AddCommandParameter(cmd, "int2", properties.Int2);
DbAccessor.AddCommandParameter(cmd, "long1", properties.Long1);
DbAccessor.AddCommandParameter(cmd, "long2", properties.Long2);
DbAccessor.AddCommandParameter(cmd, "decimal1", properties.Decimal1);
DbAccessor.AddCommandParameter(cmd, "decimal2", properties.Decimal2);
DbAccessor.AddCommandParameter(cmd, "boolean1", DbAccessor.GetDbBooleanValue(properties.Boolean1));
DbAccessor.AddCommandParameter(cmd, "boolean2", DbAccessor.GetDbBooleanValue(properties.Boolean2));
DbAccessor.AddCommandParameter(cmd, "triggerName", trigger.Key.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", trigger.Key.Group);
return(await cmd.ExecuteNonQueryAsync().ConfigureAwait(false));
}
}
private async Task LockViaInsert(
Guid requestorId,
ConnectionAndTransactionHolder conn,
string lockName,
string sql,
CancellationToken cancellationToken)
{
if (sql == null)
{
throw new ArgumentNullException(nameof(sql));
}
Log.DebugFormat("Inserting new lock row for lock: '{0}' being obtained by thread: {1}", lockName, requestorId);
using (var cmd = AdoUtil.PrepareCommand(conn, sql))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
if (await cmd.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false) != 1)
{
throw new InvalidOperationException(
AdoJobStoreUtil.ReplaceTablePrefix("No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks + " for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
}
}
}
public async Task <TriggerPropertyBundle> LoadExtendedTriggerProperties(
ConnectionAndTransactionHolder conn,
TriggerKey triggerKey,
CancellationToken cancellationToken = default)
{
using (var cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(SelectSimplePropsTrigger, TablePrefix)))
{
DbAccessor.AddCommandParameter(cmd, "schedulerName", SchedName);
DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using (var rs = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
if (await rs.ReadAsync(cancellationToken).ConfigureAwait(false))
{
SimplePropertiesTriggerProperties properties = new SimplePropertiesTriggerProperties();
properties.String1 = rs.GetString(ColumnStrProp1);
properties.String2 = rs.GetString(ColumnStrProp2);
properties.String3 = rs.GetString(ColumnStrProp3);
properties.Int1 = rs.GetInt32(ColumnIntProp1);
properties.Int2 = rs.GetInt32(ColumnIntProp2);
properties.Long1 = rs.GetInt64(ColumnLongProp1);
properties.Long2 = rs.GetInt64(ColumnLongProp2);
properties.Decimal1 = rs.GetDecimal(ColumnDecProp1);
properties.Decimal2 = rs.GetDecimal(ColumnDecProp2);
properties.Boolean1 = DbAccessor.GetBooleanFromDbValue(rs[ColumnBoolProp1]);
properties.Boolean2 = DbAccessor.GetBooleanFromDbValue(rs[ColumnBoolProp2]);
properties.TimeZoneId = rs.GetString(ColumnTimeZoneId);
return(GetTriggerPropertyBundle(properties));
}
}
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, TablePrefix));
}
/// <summary>
/// Execute the SQL that will lock the proper database row.
/// </summary>
/// <param name="conn"></param>
/// <param name="lockName"></param>
/// <param name="expandedSQL"></param>
protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL)
{
try
{
using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
{
AdoUtil.AddCommandParameter(cmd, 1, "lockName", lockName);
if (Log.IsDebugEnabled)
{
Log.Debug("Lock '" + lockName + "' is being obtained: " + Thread.CurrentThread.Name);
}
int numUpdate = cmd.ExecuteNonQuery();
if (numUpdate < 1)
{
throw new Exception(
AdoJobStoreUtil.ReplaceTablePrefix(
"No row exists in table " + TablePrefixSubst + TableLocks + " for lock named: " +
lockName, TablePrefix));
}
}
}
catch (Exception sqle)
{
if (Log.IsDebugEnabled)
{
Log.Debug(
"Lock '" + lockName + "' was not obtained by: " +
Thread.CurrentThread.Name);
}
throw new LockException(
"Failure obtaining db row lock: " + sqle.Message, sqle);
}
}
/// <summary>
/// Execute the SQL select for update that will lock the proper database row.
/// </summary>
protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL, string expandedInsertSQL)
{
try
{
using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
bool found = false;
using (IDataReader rs = cmd.ExecuteReader())
{
found = rs.Read();
}
if (!found)
{
using (IDbCommand cmd2 = AdoUtil.PrepareCommand(conn, expandedInsertSQL))
{
AdoUtil.AddCommandParameter(cmd2, "lockName", lockName);
int res = cmd2.ExecuteNonQuery();
if (res != 1)
{
throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix(
"No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks +
" for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
}
}
}
}
}
catch (Exception sqle)
{
throw new LockException("Failure obtaining db row lock: "
+ sqle.Message, sqle);
}
}
public async Task <TriggerPropertyBundle> LoadExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
{
using (var cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix, SchedNameLiteral)))
{
DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using (var rs = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
{
if (await rs.ReadAsync().ConfigureAwait(false))
{
string cronExpr = rs.GetString(AdoConstants.ColumnCronExpression);
string timeZoneId = rs.GetString(AdoConstants.ColumnTimeZoneId);
CronScheduleBuilder cb = CronScheduleBuilder.CronSchedule(cronExpr);
if (timeZoneId != null)
{
cb.InTimeZone(TimeZoneUtil.FindTimeZoneById(timeZoneId));
}
return(new TriggerPropertyBundle(cb, null, null));
}
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix, SchedNameLiteral));
}
}
/// <summary>
/// Execute the SQL that will lock the proper database row.
/// </summary>
/// <param name="conn"></param>
/// <param name="lockName"></param>
/// <param name="expandedSQL"></param>
/// <param name="expandedInsertSQL"></param>
protected override void ExecuteSQL(ConnectionAndTransactionHolder conn, string lockName, string expandedSQL, string expandedInsertSQL)
{
// attempt lock two times (to work-around possible race conditions in inserting the lock row the first time running)
int count = 0;
do
{
count++;
try
{
using (IDbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSQL))
{
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
if (Log.IsDebugEnabled)
{
Log.DebugFormat("Lock '{0}' is being obtained: {1}", lockName, Thread.CurrentThread.Name);
}
int numUpdate = cmd.ExecuteNonQuery();
if (numUpdate < 1)
{
if (Log.IsDebugEnabled)
{
Log.DebugFormat("Inserting new lock row for lock: '{0}' being obtained by thread: {1}", lockName, Thread.CurrentThread.Name);
}
using (IDbCommand cmd2 = AdoUtil.PrepareCommand(conn, expandedInsertSQL))
{
AdoUtil.AddCommandParameter(cmd2, "lockName", lockName);
int res = cmd2.ExecuteNonQuery();
if (res != 1)
{
if (count < 3)
{
// pause a bit to give another thread some time to commit the insert of the new lock row
try
{
Thread.Sleep(TimeSpan.FromSeconds(1));
}
catch (ThreadInterruptedException)
{
Thread.CurrentThread.Interrupt();
}
// try again ...
continue;
}
throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix(
"No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks +
" for lock named: " + lockName, TablePrefix, SchedulerNameLiteral));
}
}
break; // obtained lock, no need to retry
}
}
}
catch (Exception sqle)
{
if (Log.IsDebugEnabled)
{
Log.DebugFormat("Lock '{0}' was not obtained by: {1}{2}", lockName, Thread.CurrentThread.Name, (count < 3 ? " - will try again." : ""));
}
if (count < 3)
{
// pause a bit to give another thread some time to commit the insert of the new lock row
try
{
Thread.Sleep(TimeSpan.FromSeconds(1));
}
catch (ThreadInterruptedException)
{
Thread.CurrentThread.Interrupt();
}
// try again ...
continue;
}
throw new LockException("Failure obtaining db row lock: " + sqle.Message, sqle);
}
} while (count < 2);
}
/// <summary>
/// Execute the SQL select for update that will lock the proper database row.
/// </summary>
protected override async Task ExecuteSQL(
Guid requestorId,
ConnectionAndTransactionHolder conn,
string lockName,
string expandedSql,
string expandedInsertSql,
CancellationToken cancellationToken)
{
Exception initCause = null;
// attempt lock two times (to work-around possible race conditions in inserting the lock row the first time running)
int count = 0;
// Configurable lock retry attempts
var maxRetryLocal = MaxRetry;
var retryPeriodLocal = RetryPeriod;
do
{
count++;
try
{
using (DbCommand cmd = AdoUtil.PrepareCommand(conn, expandedSql))
{
AdoUtil.AddCommandParameter(cmd, "schedulerName", SchedName);
AdoUtil.AddCommandParameter(cmd, "lockName", lockName);
bool found;
using (var rs = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
if (Log.IsDebugEnabled())
{
Log.DebugFormat("Lock '{0}' is being obtained: {1}", lockName, requestorId);
}
found = await rs.ReadAsync(cancellationToken).ConfigureAwait(false);
}
if (!found)
{
if (Log.IsDebugEnabled())
{
Log.DebugFormat("Inserting new lock row for lock: '{0}' being obtained by thread: {1}", lockName, requestorId);
}
using (DbCommand cmd2 = AdoUtil.PrepareCommand(conn, expandedInsertSql))
{
AdoUtil.AddCommandParameter(cmd2, "schedulerName", SchedName);
AdoUtil.AddCommandParameter(cmd2, "lockName", lockName);
int res = await cmd2.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
if (res != 1)
{
if (count < maxRetryLocal)
{
// pause a bit to give another thread some time to commit the insert of the new lock row
await Task.Delay(retryPeriodLocal, cancellationToken).ConfigureAwait(false);
// try again ...
continue;
}
throw new Exception(AdoJobStoreUtil.ReplaceTablePrefix(
"No row exists, and one could not be inserted in table " + TablePrefixSubst + TableLocks +
" for lock named: " + lockName, TablePrefix));
}
}
}
}
// obtained lock, go
return;
}
catch (Exception sqle)
{
if (initCause == null)
{
initCause = sqle;
}
if (Log.IsDebugEnabled())
{
Log.DebugFormat("Lock '{0}' was not obtained by: {1}{2}", lockName, requestorId, count < maxRetryLocal ? " - will try again." : "");
}
if (count < maxRetryLocal)
{
// pause a bit to give another thread some time to commit the insert of the new lock row
await Task.Delay(retryPeriodLocal, cancellationToken).ConfigureAwait(false);
// try again ...
continue;
}
throw new LockException("Failure obtaining db row lock: " + sqle.Message, sqle);
}
} while (count < maxRetryLocal + 1);
throw new LockException("Failure obtaining db row lock, reached maximum number of attempts. Initial exception (if any) attached as root cause.", initCause);
}
public async Task <TriggerPropertyBundle> LoadExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
{
using (var cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, TablePrefix, SchedNameLiteral)))
{
DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using (var rs = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
{
if (await rs.ReadAsync().ConfigureAwait(false))
{
int repeatCount = rs.GetInt32(AdoConstants.ColumnRepeatCount);
TimeSpan repeatInterval = DbAccessor.GetTimeSpanFromDbValue(rs[AdoConstants.ColumnRepeatInterval]) ?? TimeSpan.Zero;
int timesTriggered = rs.GetInt32(AdoConstants.ColumnTimesTriggered);
SimpleScheduleBuilder sb = SimpleScheduleBuilder.Create()
.WithRepeatCount(repeatCount)
.WithInterval(repeatInterval);
string[] statePropertyNames = { "timesTriggered" };
object[] statePropertyValues = { timesTriggered };
return(new TriggerPropertyBundle(sb, statePropertyNames, statePropertyValues));
}
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, TablePrefix, SchedNameLiteral));
}
}
public async Task <TriggerPropertyBundle> LoadExtendedTriggerProperties(
ConnectionAndTransactionHolder conn,
TriggerKey triggerKey,
CancellationToken cancellationToken = default)
{
using var cmd = DbAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix));
DbAccessor.AddCommandParameter(cmd, "schedulerName", SchedName);
DbAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
DbAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using var rs = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
if (await rs.ReadAsync(cancellationToken).ConfigureAwait(false))
{
return(ReadTriggerPropertyBundle(rs));
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, TablePrefix));
}
public TriggerPropertyBundle LoadExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
{
using (IDbCommand cmd = commandAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, tablePrefix, schedNameLiteral)))
{
commandAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
commandAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using (IDataReader rs = cmd.ExecuteReader())
{
if (rs.Read())
{
int repeatCount = rs.GetInt32(AdoConstants.ColumnRepeatCount);
long repeatInterval = rs.GetInt64(AdoConstants.ColumnRepeatInterval);
int timesTriggered = rs.GetInt32(AdoConstants.ColumnTimesTriggered);
SimpleScheduleBuilder sb = SimpleScheduleBuilder.Create()
.WithRepeatCount(repeatCount)
.WithInterval(TimeSpan.FromMilliseconds(repeatInterval));
string[] statePropertyNames = { "timesTriggered" };
object[] statePropertyValues = { timesTriggered };
return(new TriggerPropertyBundle(sb, statePropertyNames, statePropertyValues));
}
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, tablePrefix, schedNameLiteral));
}
}
public TriggerPropertyBundle LoadExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
{
using (IDbCommand cmd = commandAccessor.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(SelectSimplePropsTrigger, tablePrefix, schedNameLiteral)))
{
commandAccessor.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
commandAccessor.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using (IDataReader rs = cmd.ExecuteReader())
{
if (rs.Read())
{
SimplePropertiesTriggerProperties properties = new SimplePropertiesTriggerProperties();
properties.String1 = (rs.GetString(ColumnStrProp1));
properties.String2 = (rs.GetString(ColumnStrProp2));
properties.String3 = (rs.GetString(ColumnStrProp3));
properties.Int1 = (rs.GetInt32(ColumnIntProp1));
properties.Int2 = (rs.GetInt32(ColumnIntProp2));
properties.Long1 = (rs.GetInt32(ColumnLongProp1));
properties.Long2 = (rs.GetInt32(ColumnLongProp2));
properties.Decimal1 = (rs.GetDecimal(ColumnDecProp1));
properties.Decimal2 = (rs.GetDecimal(ColumnDecProp2));
properties.Boolean1 = (rs.GetBoolean(ColumnBoolProp1));
properties.Boolean2 = (rs.GetBoolean(ColumnBoolProp2));
return(GetTriggerPropertyBundle(properties));
}
}
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectSimpleTrigger, tablePrefix, schedNameLiteral));
}
public TriggerPropertyBundle LoadExtendedTriggerProperties(ConnectionAndTransactionHolder conn, TriggerKey triggerKey)
{
using (IDbCommand cmd = adoUtil.PrepareCommand(conn, AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, tablePrefix, schedNameLiteral)))
{
adoUtil.AddCommandParameter(cmd, "triggerName", triggerKey.Name);
adoUtil.AddCommandParameter(cmd, "triggerGroup", triggerKey.Group);
using (IDataReader rs = cmd.ExecuteReader())
{
if (rs.Read())
{
string cronExpr = rs.GetString(AdoConstants.ColumnCronExpression);
string timeZoneId = rs.GetString(AdoConstants.ColumnTimeZoneId);
CronScheduleBuilder cb = CronScheduleBuilder.CronSchedule(cronExpr);
if (timeZoneId != null)
{
cb.InTimeZone(TimeZoneInfo.FindSystemTimeZoneById(timeZoneId));
}
return(new TriggerPropertyBundle(cb, null, null));
}
}
throw new InvalidOperationException("No record found for selection of Trigger with key: '" + triggerKey + "' and statement: " + AdoJobStoreUtil.ReplaceTablePrefix(StdAdoConstants.SqlSelectCronTriggers, tablePrefix, schedNameLiteral));
}
}
