} // Process
private async Task <Dictionary <string, ChangeDescriptor> > GetAndProcessRoutineChangesAsync(SqlConnection con, string connectionString, int changesCount)
{
var cmdGetRoutineList = new SqlCommand();
cmdGetRoutineList.Connection = con;
cmdGetRoutineList.CommandTimeout = 60;
cmdGetRoutineList.CommandType = System.Data.CommandType.StoredProcedure;
cmdGetRoutineList.CommandText = "ormv2.GetRoutineList";
cmdGetRoutineList.Parameters.Add("maxRowver", System.Data.SqlDbType.BigInt).Value = MaxRowDate ?? 0;
var changesList = new Dictionary <string, ChangeDescriptor>();
using (var reader = await cmdGetRoutineList.ExecuteReaderAsync())
{
if (!this.IsRunning)
{
return(null);
}
var columns = new string[] { "CatalogName", "SchemaName", "RoutineName", "RoutineType", "rowver", "IsDeleted", "ObjectId", "LastUpdateByHostName", "JsonMetadata", "ParametersXml", "ParametersHash", "ResultSetXml", "ResultSetHash" };
// maps column ordinals to proper names
var ix = columns.Select(s => new { s, Value = reader.GetOrdinal(s) }).ToDictionary(p => p.s, p => p.Value);
var curRow = 0;
while (await reader.ReadAsync())
{
if (!this.IsRunning)
{
break;
}
if (changesCount == 1)
{
//!this._log.info(`(single change) ${ dbSource.Name}\t[${ row.SchemaName}].[${row.RoutineName}]`);
}
var newCachedRoutine = new CachedRoutine()
{
Routine = reader.GetString(ix["RoutineName"]),
Schema = reader.GetString(ix["SchemaName"]),
Type = reader.GetString(ix["RoutineType"]),
IsDeleted = reader.GetBoolean(ix["IsDeleted"]),
Parameters = new List <RoutineParameterV2>(),
RowVer = reader.GetInt64(ix["rowver"])
};
var parmHash = reader.GetSqlBinary(ix["ParametersHash"]);
var resultSetHash = reader.GetSqlBinary(ix["ResultSetHash"]);
if (!parmHash.IsNull)
{
newCachedRoutine.ParametersHash = string.Concat(parmHash.Value.Select(item => item.ToString("x2")));
}
if (!resultSetHash.IsNull)
{
newCachedRoutine.ResultSetHash = string.Concat(resultSetHash.Value.Select(item => item.ToString("x2")));
}
var lastUpdateByHostName = reader.GetString(ix["LastUpdateByHostName"]);
string jsonMetadata = null;
if (!reader.IsDBNull(ix["JsonMetadata"]))
{
jsonMetadata = reader.GetString(ix["JsonMetadata"]);
}
if (!string.IsNullOrWhiteSpace(jsonMetadata))
{
try
{
newCachedRoutine.jsDALMetadata = JsonConvert.DeserializeObject <jsDALMetadata>(jsonMetadata);
}
catch (Exception ex)
{
newCachedRoutine.jsDALMetadata = new jsDALMetadata()
{
Error = ex.ToString()
};
}
}
string parametersXml = null;
if (!reader.IsDBNull(ix["ParametersXml"]))
{
parametersXml = reader.GetString(ix["ParametersXml"]);
var parameterList = ExtractParameters(parametersXml);
newCachedRoutine.Parameters = parameterList;
}
curRow++;
var perc = ((double)curRow / (double)changesCount) * 100.0;
this.Status = $"{ DateTime.Now.ToString("yyyy-MM-dd, HH:mm:ss")} - Overall progress: {curRow} of { changesCount } ({ perc.ToString("##0.00")}%) - [{ newCachedRoutine.Schema }].[{ newCachedRoutine.Routine }]";
if (curRow % 10 == 0)
{
Hubs.WorkerMonitor.Instance.NotifyObservers();
}
if (!newCachedRoutine.IsDeleted)
{
/*
* Resultset METADATA
*/
if (newCachedRoutine.ResultSetRowver >= newCachedRoutine.RowVer)
{
Log.Verbose("Result set metadata up to date");
}
else
{
//logLine.Append("Generating result set metadata");
//console.log("Generating result set metadata");
// generate using legacy FMTONLY way if explicitly requested
if ((newCachedRoutine.jsDALMetadata?.jsDAL?.fmtOnlyResultSet ?? false))
{
// can't use the same connection
await GenerateFmtOnlyResultsetsAsync(connectionString, newCachedRoutine);
}
else
{
string resultSetXml = null;
if (!reader.IsDBNull(ix["ResultSetXml"]))
{
resultSetXml = reader.GetString(ix["ResultSetXml"]);
(var resultSetMetdata, var error) = ExtractResultSetMetadata(resultSetXml);
newCachedRoutine.ResultSetRowver = newCachedRoutine.RowVer;
if (error == null)
{
newCachedRoutine.ResultSetMetadata = new Dictionary <string, List <ResultSetFieldMetadata> >()
{
[string.Intern("Table0")] = resultSetMetdata
};
}
else
{
newCachedRoutine.ResultSetError = error;
}
}
}
}
} // !IsDeleted
newCachedRoutine.PrecalculateJsGenerationValues(this.Endpoint);
Endpoint.AddToCache(newCachedRoutine.RowVer, newCachedRoutine, lastUpdateByHostName, out var changesDesc);
if (changesDesc != null)
{
if (!changesList.ContainsKey(newCachedRoutine.FullName.ToLower()))
{
changesList.Add(newCachedRoutine.FullName.ToLower(), changesDesc);
}
// TODO: Else update - so last change is what gets through?
// ... Two things to consider here.. 1. We use changesList to determine if there was a significant change to the metadata so we can generate a new file and notify subscribers
// 2. ..nothing else actually...? just think about the same sproc changing multiple times ....no sproc can only come back once per iteration!!!!
}
if (!this.MaxRowDate.HasValue || newCachedRoutine.RowVer > this.MaxRowDate.Value)
{
this.MaxRowDate = newCachedRoutine.RowVer;
}
} // while reader.Read
} // using reader
return(changesList);
}
