private void SetUpJobStateRecursively(SqlDatabaseConnectorJobConfiguration config, JobState.JobState jobState, string description = "")
{
var state = new DatabaseJobState
{
Name = config.JobName,
State = jobState,
LastExecutionDate = DateTime.UtcNow
};
if (!string.IsNullOrEmpty(description))
{
state.Fields.Add(new Field("Description", description));
}
foreach (var changeTable in config.ChangeTables)
{
state.SetChangetableVersionThreshold(changeTable.TableName,
GetIntegration(config).GetLastChangeVersion(config, changeTable));
}
foreach (var eventTable in config.EventTables)
{
state.SetChangetableVersionThreshold(eventTable.TableName,
GetIntegration(config).GetLastChangeVersion(config, eventTable));
}
_stateService.SaveState(state);
foreach (var relatingSource in config.ForeignSources)
{
SetUpJobStateRecursively(relatingSource.SqlDatabaseConnectorConfiguration,
JobState.JobState.ForeignTable,
$"{(string.IsNullOrEmpty(description) ? "" : description + " -> ")} Relating to {config.MainTable.TableName}.{relatingSource.Relation.Key} with {relatingSource.Relation.Value}");
}
}
public IEnumerable <AddDocument> FetchDocuments(SqlDatabaseConnectorJobConfiguration config, string constraintColumn, bool constraintIsIntType, IEnumerable <string> idsToSelect)
{
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string.Format("SELECT * FROM {0} WHERE {1}",
config.MainTable.TableName,
CreateWhereConstraint(idsToSelect, constraintColumn, constraintIsIntType));
Log.Logger.Debug($"{config.JobName}: Fetching initial document batch with: {cmd.CommandText}");
conn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var document = DataRowToDocument(dr, constraintColumn, config.JobName);
if (document == null)
{
yield break;
}
yield return(document);
}
}
}
}
public long GetLastChangeVersion(SqlDatabaseConnectorJobConfiguration config, TableDetail changeTableSettings)
{
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string.Format(
"SELECT TOP ( 1 ) SYS_CHANGE_VERSION FROM CHANGETABLE (CHANGES {0}, 0) as CT ORDER BY SYS_CHANGE_VERSION desc",
changeTableSettings.TableName);
Log.Logger.Debug($"{config.JobName}: Getting the latest version number with: {cmd.CommandText}");
try
{
conn.Open();
var scalar = cmd.ExecuteScalar();
if (scalar != null)
{
return((long)scalar);
}
}
catch (SqlException e)
{
Log.Error(e, $"{config.JobName}: SQL error when executing {cmd.CommandText}");
throw;
}
}
return(0);
}
private void SetErrorState(SqlDatabaseConnectorJobConfiguration config, Exception e)
{
try
{
var jobState = GetCurrentJobState(config);
jobState.SetErrorState(e);
if (jobState.State != JobState.JobState.Error)
{
jobState.LastWorkingState = jobState.State;
}
jobState.State = JobState.JobState.Error;
_stateService.SaveState(jobState);
}
catch (Exception newExceptione)
{
Log.Error(newExceptione, "Error when saving error state: {message} for job {jobName}", newExceptione.Message, config.JobName);
var errorState = new DatabaseJobState
{
Name = config.JobName,
State = JobState.JobState.Paused,
LastExecutionDate = DateTime.UtcNow,
ErrorCount = 1,
LastWorkingState = JobState.JobState.Paused,
RecentErrorDate = DateTime.UtcNow,
Message = $"Job threw an exception, {e.Message}, and then threw an exception when trying to save the error state: {newExceptione.Message}"
};
_stateService.SaveState(errorState);
}
}
public long GetLastChangeVersion(SqlDatabaseConnectorJobConfiguration config, EventTable eventTableSettings)
{
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string.Format(
"SELECT MAX({1}) FROM {0}",
eventTableSettings.TableName,
eventTableSettings.EventSequenceColumnName);
Log.Logger.Debug($"{config.JobName}: Getting the latest version number with: {cmd.CommandText}");
try
{
conn.Open();
var scalar = cmd.ExecuteScalar();
if (scalar.GetType().GetTypeInfo().IsValueType)
{
return((int)scalar);
}
return(0);
}
catch (SqlException e)
{
Log.Error(e, $"{config.JobName}: SQL error when executing {cmd.CommandText}");
throw;
}
}
}
public IEnumerable <string> DiscoverInitialIds(SqlDatabaseConnectorJobConfiguration config)
{
if (config.MainTable == null)
{
throw new MissingFieldException("A main table needs to be configured in order to know where to get the data from");
}
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string.Format("SELECT {0} FROM {1}",
config.MainTable.PrimaryKeyName,
config.MainTable.TableName);
Log.Logger.Debug($"{config.JobName}: Discovering Ids with: {cmd.CommandText}");
conn.Open();
using (var dr = cmd.ExecuteReader())
{
int idOrdinal = -1;
while (dr.Read())
{
if (idOrdinal == -1)
{
idOrdinal = dr.GetOrdinal(config.MainTable.PrimaryKeyName);
}
yield return(dr.GetValue(idOrdinal).ToString());
}
}
}
}
public IEnumerable <string> DiscoverMainTableIds(SqlDatabaseConnectorJobConfiguration config, string constraintColumn, IList <string> foreignIds)
{
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string.Format("SELECT DISTINCT {0} FROM {1} WHERE {2}",
config.MainTable.PrimaryKeyName,
config.MainTable.TableName,
CreateWhereConstraint(foreignIds, constraintColumn, config.MainTable.PrimaryKeyIsInteger));
Log.Logger.Debug($"{config.JobName}: Fetching initial document batch with: {cmd.CommandText}");
conn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
int idOrdinal = -1;
while (dr.Read())
{
if (idOrdinal == -1)
{
idOrdinal = dr.GetOrdinal(config.MainTable.PrimaryKeyName);
}
yield return(dr.GetValue(idOrdinal).ToString());
}
}
}
}
}
private SourceChanges FetchDocumentsUsingWorkTask(
SqlDatabaseConnectorJobConfiguration config, WorkTask workTask)
{
Log.Logger.Information($"{config.JobName}: Fetching documents using ids in worktask: {workTask.Id}");
try
{
//Get the documents from the data view
var documents = FetchDocumentsRecursivly(
config,
config.MainTable.PrimaryKeyName,
config.MainTable.PrimaryKeyIsInteger,
workTask.Instructions);
//marks the work task as completed (deletes the task)
_workTaskService.TaskCompleted(workTask);
//_stateService.SaveState(jobState, true);
return(new SourceChanges(documents, new List <Field>()));
}
catch (Exception e)
{
Log.Logger.Warning($"Reverting work task to its original state due to error ({e.Message}");
_workTaskService.RevertTask(workTask);
var state = GetCurrentJobState(config);
state.BatchCount++;
_stateService.SaveState(state);
throw;
}
}
/// <summary>
/// Discover all changes listed in the change tables
/// </summary>
/// <param name="config"></param>
/// <param name="stateBase"></param>
/// <returns></returns>
private IEnumerable <DataChange> DiscoverDataChangesUsingChangetables(SqlDatabaseConnectorJobConfiguration config, DatabaseJobState stateBase)
{
foreach (var changeTable in config.ChangeTables)
{
var dataChange = new DataChange(changeTable);
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string.Format(
"SELECT * FROM CHANGETABLE (CHANGES {0}, {1}) as CT ORDER BY SYS_CHANGE_VERSION asc",
changeTable.TableName,
stateBase.GetChangetableVersionThreshold(changeTable.TableName));
Log.Logger.Debug($"{config.JobName}: Getting data changes from changetables with: {cmd.CommandText}");
try
{
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dataChange.AddChange(DataRowToChangeTableEvent(reader, changeTable));
}
}
}
catch (SqlException e)
{
Log.Error(e, $"{config.JobName}: SQL error when executing {cmd.CommandText}");
throw;
}
}
yield return(dataChange);
}
}
private IDatabaseIntegration GetIntegration(SqlDatabaseConnectorJobConfiguration config)
{
if (!_databaseIntegrations.ContainsKey(config.IntegrationType))
{
throw new Exception(string.Format("No integration logic for {0} has been installed. " +
"Set it up in your bootstrapper. " +
"You currently have the following installed: {1}"
, config.IntegrationType, string.Join(", ", _databaseIntegrations.Keys)));
}
return(_databaseIntegrations[config.IntegrationType]);
}
/// <summary>
/// Using a DataChange object containing changes in a changeTable, this function
/// discovers which rows (using the MainTables idColumn) that has changed in the
/// main table
/// </summary>
/// <param name="config"></param>
/// <param name="change"></param>
/// <param name="changeType"></param>
/// <returns></returns>
private IEnumerable <string> GetDataChangesInMainTable(SqlDatabaseConnectorJobConfiguration config, DataChange change, EventType changeType)
{
var ids = changeType == EventType.Add ? change.AddedIds : change.DeletedIds;
if (!ids.Any())
{
yield break;
}
if (config.MainTable.PrimaryKeyName == change.Table.PrimaryKeyName) //if the column names are the same, its a fair assumption to say they are a direct mapping
{
foreach (var id in ids)
{
yield return(id);
}
}
var constraintBatches = CreateTSqlWhereConstraintBatches(
change.Table.PrimaryKeyName, ids, change.Table.PrimaryKeyIsInteger, config.BatchSize);
foreach (var constraintBatch in constraintBatches.Where(x => !string.IsNullOrEmpty(x)))
{
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string.Format("SELECT DISTINCT {0} FROM {1} WHERE {2}",
config.MainTable.PrimaryKeyName,
config.MainTable.TableName,
constraintBatch);
cmd.CommandTimeout = 300;
Log.Logger.Debug($"{config.JobName}: Fetching changed documents with: {cmd.CommandText}");
conn.Open();
using (var dr = cmd.ExecuteReader())
{
int idOrdinal = -1;
while (dr.Read())
{
if (idOrdinal == -1)
{
idOrdinal = dr.GetOrdinal(config.MainTable.PrimaryKeyName);
}
yield return(dr.GetValue(idOrdinal).ToString());
}
}
}
}
}
private SourceChanges FetchDocumentsUsingIncremental(SqlDatabaseConnectorJobConfiguration config, DatabaseJobState jobStateBase)
{
//Not 100% threadsafe. But its good enough. If two concurrent threads do a discover, it'll still work fine
var stateRightNow = new DatabaseJobState(_stateService.LoadState(config.JobName));
if (stateRightNow.State == JobState.JobState.Discovering)
{
Log.Logger.Information(
$"{config.JobName}: State in DISCOVER phase. Another job is already at it. Doing nothing.");
return(new SourceChanges( ));
}
jobStateBase.Status = JobStatus.Ok;
jobStateBase.State = JobState.JobState.Discovering;
_stateService.SaveState(jobStateBase);
Log.Logger.Information($"{config.JobName}: Fetching incremental documents");
var idsToFetch = FetchChangedIdsRecursively(config, EventType.Add, false, jobStateBase).ToList();
IList <AddDocument> adds = null;
if (idsToFetch.Any())
{
var numBatches = _workTaskService.WriteTasks(config.JobName, config.BatchSize, idsToFetch);
jobStateBase.BatchCount = numBatches;
jobStateBase.LastDiscoverDate = DateTime.UtcNow;
jobStateBase.LastDiscoverBatchCount = numBatches;
if (idsToFetch.Count <= config.BatchSize)
{
var task = _workTaskService.GetNextTask(config.JobName);
jobStateBase.BatchCount--;
adds = FetchDocumentsUsingWorkTask(config, task).Adds;
}
}
var plainState = _stateService.LoadState(config.JobName);
jobStateBase = new DatabaseJobState(plainState);
var idFieldsToDelete = FetchChangedIdsRecursively(config, EventType.Delete, true, jobStateBase)
.Select(x => new Field(config.MainTable.PrimaryKeyName, x)).ToList();
jobStateBase.State = JobState.JobState.IncrementalCrawling;
_stateService.SaveState(jobStateBase);
return(new SourceChanges(
adds ?? new List <AddDocument>(),
idFieldsToDelete));
}
private bool ResetIfConfigured(SqlDatabaseConnectorJobConfiguration config, DatabaseJobState jobStateBase)
{
if (config.ResetEveryXHour <= 0)
{
return(false);
}
var hoursSinceLastCrawl = (DateTime.UtcNow - jobStateBase.InitDate.Value).TotalHours;
if (hoursSinceLastCrawl >= config.ResetEveryXHour)
{
ResetConnector(config.JobName);
return(true);
}
return(false);
}
private IEnumerable <DataChange> DiscoverDataChangesUsingEventTables(SqlDatabaseConnectorJobConfiguration config, DatabaseJobState stateBase)
{
foreach (var eventTable in config.EventTables)
{
var dataChange = new DataChange(new TableDetail()
{
TableName = eventTable.TableName,
PrimaryKeyName = eventTable.MainTableIdColumnName
});
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string
.Format(
"SELECT {0}, {1}, {2} FROM {3} WHERE {0} > {4} ORDER BY {0} asc",
eventTable.EventSequenceColumnName,
eventTable.MainTableIdColumnName,
eventTable.EventTypeColumnName,
eventTable.TableName,
stateBase.GetChangetableVersionThreshold(eventTable.TableName));
Log.Logger.Debug($"{config.JobName}: Getting data changes from changetables with: {cmd.CommandText}");
try
{
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dataChange.AddChange(DataRowToChangeTableEvent(reader, eventTable));
}
}
}
catch (SqlException e)
{
Log.Logger.Error($"{config.JobName}: SQL error when executing {cmd.CommandText}", e);
throw;
}
}
yield return(dataChange);
}
}
/// <summary>
/// Tries to get the current job state
/// If no state exists, a new job state will be created and initiated
/// </summary>
/// <param name="config"></param>
private DatabaseJobState GetCurrentJobState(SqlDatabaseConnectorJobConfiguration config)
{
var state = _stateService.LoadState(config.JobName);
if (state != null)
{
return(new DatabaseJobState(state));
}
SetUpJobStateRecursively(config, JobState.JobState.InitialCrawling);
state = _stateService.LoadState(config.JobName);
if (state != null)
{
return(new DatabaseJobState(state));
}
throw new Exception(
$"Unable to find the job status file for {config.JobName} even though it was just created. ");
}
private IList <AddDocument> FetchDocumentsRecursivly(SqlDatabaseConnectorJobConfiguration config, string constraintColumn, bool constraintIsIntType, IList <string> ids)
{
var documents = GetIntegration(config).FetchDocuments(config, constraintColumn, constraintIsIntType, ids).ToList();
foreach (var foreignSource in config.ForeignSources)
{
var foreignRowsToFetch = documents.SelectMany(x => x.Fields)
.Where(x => x.Name.Equals(foreignSource.Relation.Key))
.Select(x => x.Value).ToList();
if (foreignRowsToFetch.Count == 0)
{
continue;
}
var foreignDocuments = FetchDocumentsRecursivly(
foreignSource.SqlDatabaseConnectorConfiguration, foreignSource.Relation.Value, foreignSource.RelationIsInteger, foreignRowsToFetch);
documents.MergeWithDocuments(foreignDocuments, foreignSource.Relation, foreignSource.SqlDatabaseConnectorConfiguration.JobName);
}
return(documents);
}
public SourceChanges ExecuteFetch(SqlDatabaseConnectorJobConfiguration config)
{
try
{
if (config.ChangeTables.Any() && config.EventTables.Any())
{
throw new Exception("Cannot handle both change tables and event tables at the same time");
}
var jobState = GetCurrentJobState(config);
jobState.LastExecutionDate = DateTime.UtcNow;
var changes = HandleState(jobState, config);
return(changes);
}
catch (Exception e)
{
Log.Logger.Error(e, $"{config.JobName}: error while executing fetch. Setting job to error state");
SetErrorState(config, e);
throw;
}
}
private List <string> FetchChangedIdsRecursively(SqlDatabaseConnectorJobConfiguration config, EventType eventType, bool saveState, DatabaseJobState stateBase)
{
var changedIds = GetIntegration(config).DiscoverIncrementalIds(config, ref stateBase, eventType).ToList();
if (saveState)
{
_stateService.SaveState(stateBase);
}
foreach (var foreignSource in config.ForeignSources)
{
var ids = FetchChangedIdsRecursively(foreignSource.SqlDatabaseConnectorConfiguration, eventType, saveState, stateBase);
if (!ids.Any())
{
continue;
}
var mainTableIds = GetIntegration(config).DiscoverMainTableIds(config, foreignSource.Relation.Key, ids.ToList());
changedIds.AddRange(mainTableIds);
}
return(changedIds.Distinct().ToList());
}
public IEnumerable <string> DiscoverIncrementalIds(SqlDatabaseConnectorJobConfiguration config, ref DatabaseJobState stateBase, EventType changeType)
{
var discoverer = _changeDiscoverers.FirstOrDefault(x => x.CanHandle(config));
if (discoverer == null)
{
Log.Debug("{jobName}: No incremental strategy for {changeType} (no discoverer registered that handles the configuration), no delta crawls will be possible.", config.JobName, changeType);
return(new List <string>());
}
var dataChanges = discoverer.DiscoverChanges(config, stateBase).ToList();
foreach (var dataChange in dataChanges)
{
if (!dataChange.AddedIds.Any() && !dataChange.DeletedIds.Any())
{
continue;
}
stateBase.SetChangetableVersionThreshold(dataChange.Table.TableName, dataChange.HighestVersionAdded, true);
}
return(dataChanges.SelectMany(
dataChange => GetDataChangesInMainTable(config, dataChange, changeType)));
}
private SourceChanges HandleState(DatabaseJobState jobStateBase, SqlDatabaseConnectorJobConfiguration configuration)
{
switch (jobStateBase.State)
{
case JobState.JobState.Paused:
return(new SourceChanges());
case JobState.JobState.InitialCrawling:
return(HandleInitialCrawlingState(jobStateBase, configuration));
case JobState.JobState.Discovering:
return(HandleDiscoveringState(jobStateBase));
case JobState.JobState.Error:
return(HandleErrorState(jobStateBase, configuration));
case JobState.JobState.IncrementalCrawling:
return(HandleIncrementalCrawling(configuration, jobStateBase));
default:
throw new NotSupportedException($"{jobStateBase.Name}: State is in a non-supported state: {jobStateBase.State}.");
}
}
private SourceChanges HandleIncrementalCrawling(SqlDatabaseConnectorJobConfiguration configuration, DatabaseJobState jobStateBase)
{
//try to get the first file of batch ids
var workTask = _workTaskService.GetNextTask(configuration.JobName);
if (workTask != null)
{
Log.Logger.Information(
$"{jobStateBase.Name}: State in INCREMENTAL CRAWLING phase (still {jobStateBase.BatchCount} batches left)");
if (jobStateBase.BatchCount > 0)
{
jobStateBase.BatchCount--;
}
jobStateBase.Status = JobStatus.Ok;
_stateService.SaveState(jobStateBase);
return(FetchDocumentsUsingWorkTask(configuration, workTask));
}
if (ResetIfConfigured(configuration, jobStateBase))
{
return(new SourceChanges());
}
return(FetchDocumentsUsingIncremental(configuration, jobStateBase));
}
/// <summary>
/// If the job currently is in an Error state it will be reset to its previously working state
/// With an exception. If the previously state was Discover mode it will be reset to IncrementalCrawling
/// Why? Because there might be a few document batches that needs to be taken care of
/// </summary>
/// <param name="jobStateBase"></param>
/// <param name="configuration"></param>
/// <returns></returns>
private SourceChanges HandleErrorState(DatabaseJobState jobStateBase, SqlDatabaseConnectorJobConfiguration configuration)
{
if (jobStateBase.ErrorCount > ErrorCountLimit)
{
var pausingMessage = $"Job has been paused due to the Error count limit's been reached: {ErrorCountLimit}. The error count has now been reset to 0.";
jobStateBase.State = JobState.JobState.Paused;
jobStateBase.Message = pausingMessage;
jobStateBase.ErrorCount = 0;
_stateService.SaveState(jobStateBase);
Log.Logger.Warning(pausingMessage);
return(new SourceChanges());
}
var lastKnownState = jobStateBase.LastWorkingState;
if (lastKnownState == JobState.JobState.Discovering)
{
lastKnownState = JobState.JobState.IncrementalCrawling;
}
Log.Logger.Warning($"{jobStateBase.Name}: State is in ERROR mode. Retrying (retry number {jobStateBase.ErrorCount}) by setting it to {lastKnownState}");
jobStateBase.State = lastKnownState;
jobStateBase.ErrorCount++;
_stateService.SaveState(jobStateBase);
return(HandleState(jobStateBase, configuration));
}
public bool CanHandle(SqlDatabaseConnectorJobConfiguration config)
{
return(config.EventTables.Any());
}
/// <summary>
/// Will set the state to Discover and start to discover all document ids
/// the Ids will be serialized to batch files
/// </summary>
/// <param name="jobStateBase"></param>
/// <param name="config"></param>
private SourceChanges HandleInitialCrawlingState(DatabaseJobState jobStateBase, SqlDatabaseConnectorJobConfiguration config)
{
jobStateBase.State = JobState.JobState.Discovering;
jobStateBase.Status = JobStatus.Ok;
_stateService.SaveState(jobStateBase);
try
{
//Discover all ids
var discovedIds = GetIntegration(config).DiscoverInitialIds(config);
//Serialize them to batches
var numBatches = _workTaskService.WriteTasks(config.JobName, config.BatchSize, discovedIds.ToList());
jobStateBase.BatchCount = numBatches;
jobStateBase.State = JobState.JobState.IncrementalCrawling;
jobStateBase.LastDiscoverBatchCount = numBatches;
_stateService.SaveState(jobStateBase);
return(HandleIncrementalCrawling(config, jobStateBase));
}
catch (Exception e)
{
jobStateBase.SetErrorState(e);
_stateService.SaveState(jobStateBase);
throw;
}
}
public IEnumerable <DataChange> DiscoverChanges(SqlDatabaseConnectorJobConfiguration config, DatabaseJobState stateBase)
{
return(DiscoverDataChangesUsingEventTables(config, stateBase));
}
