private static Dictionary <string, dynamic> RetrieveOutputParameterValues(CachedRoutine cachedRoutine, SqlCommand cmd)
{
var outputParameterDictionary = new Dictionary <string, dynamic>();
if (cachedRoutine?.Parameters != null)
{
var outputParmList = (from p in cachedRoutine.Parameters
where p.IsOutput && !p.IsResult/*IsResult parameters do not have a name so cannot be accessed in loop below*/
select p).ToList();
// Retrieve OUT-parameters and their values
foreach (var outParm in outputParmList)
{
object val = null;
val = cmd.Parameters[outParm.Name].Value;
if (val == DBNull.Value)
{
val = null;
}
outputParameterDictionary.Add(outParm.Name.TrimStart('@'), val);
}
}
return(outputParameterDictionary);
}
private async Task GenerateFmtOnlyResultsetsAsync(string connectionString, CachedRoutine newCachedRoutine)
{
try
{
// get schema details of all result sets
(var resultSets, var resultSetError) = await OrmDAL.RoutineGetFmtOnlyResultsAsync(connectionString, newCachedRoutine.Schema, newCachedRoutine.Routine, newCachedRoutine.Parameters);
if (resultSets != null)
{
var resultSetsDictionary = new Dictionary <string, List <ResultSetFieldMetadata> >();
foreach (DataTable dt in resultSets.Tables)
{
var lst = new List <ResultSetFieldMetadata>();
for (var rowIx = 0; rowIx < dt.Rows.Count; rowIx++)
{
DataRow row = dt.Rows[rowIx];
var schemaRow = new ResultSetFieldMetadata()
{
ColumnName = (string)row["ColumnName"],
DataType = GetCSharpType(row),
DbDataType = (string)row["DataTypeName"],
ColumnSize = Convert.ToInt32(row["ColumnSize"]),
NumericalPrecision = Convert.ToUInt16(row["NumericPrecision"]),
NumericalScale = Convert.ToUInt16(row["NumericScale"])
};
lst.Add(schemaRow);
}
resultSetsDictionary.Add(dt.TableName, lst);
}
newCachedRoutine.ResultSetMetadata = resultSetsDictionary;
var resultSetJson = JsonConvert.SerializeObject(resultSetsDictionary);
using (var hash = SHA256Managed.Create())
{
newCachedRoutine.ResultSetHash = string.Concat(hash.ComputeHash(System.Text.Encoding.UTF8
.GetBytes(resultSetJson))
.Select(item => item.ToString("x2")));
}
newCachedRoutine.ResultSetRowver = newCachedRoutine.RowVer;
newCachedRoutine.ResultSetError = resultSetError;
}
}
catch (Exception e)
{
newCachedRoutine.ResultSetRowver = newCachedRoutine.RowVer;
newCachedRoutine.ResultSetError = e.ToString();
}
}
private static PluginSetParameterValue GetParameterValueFromPlugins(CachedRoutine routine, string parameterName, List <ExecutionPlugin> plugins)
{
foreach (var plugin in plugins)
{
try
{
var val = plugin.GetParameterValue(routine.Schema, routine.Routine, parameterName);
if (val != PluginSetParameterValue.DontSet)
{
return(val);
}
}
catch (Exception ex)
{
SessionLog.Error("Plugin {0} GetParameterValue failed", plugin.Name);
SessionLog.Exception(ex);
}
}
return(PluginSetParameterValue.DontSet);
}
private static string ProcessMetadata(Dictionary <string, string> requestHeaders, ref Dictionary <string, string> responseHeaders, CachedRoutine cachedRoutine)
{
if (cachedRoutine?.jsDALMetadata?.jsDAL?.security?.requiresCaptcha ?? false)
{
if (!requestHeaders.ContainsKey("captcha-val"))
{
System.Threading.Thread.Sleep(1000 * 5); // TODO: Make this configurable? We intentionally slow down requests that do not conform
return("captcha-val header not specified");
}
if (jsdal_server_core.Settings.SettingsInstance.Instance.Settings.GoogleRecaptchaSecret == null)
{
return("The setting GoogleRecaptchaSecret is not configured on this jsDAL server.");
}
var captchaHeaderVal = requestHeaders.Val("captcha-val");
if (captchaHeaderVal != null)
{
var t = ValidateGoogleRecaptchaAsync(captchaHeaderVal);
t.Wait();
var capResp = t.Result;
if (responseHeaders == null)
{
responseHeaders = new Dictionary <string, string>();
}
responseHeaders["captcha-ret"] = capResp ? "1" : "0";
if (capResp)
{
return(null);
}
else
{
return("Captcha failed.");
}
}
}
return(null);
}
private static void SetupSqlCommandParameters(SqlCommand cmd, CachedRoutine cachedRoutine, Dictionary <string, string> inputParameters, List <ExecutionPlugin> plugins, string remoteIpAddress)
{
/*
* Parameters
* ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
* (Parm not specified) -> (Has default in sproc def) -> Add to command with C# null to inherit default value
* (No default) -> Don't add to command, should result in SQL Exception
*
* Parm specified as null -> DBNull.Value (regardless of Sproc default value)
* $jsDAL$DBNull -> Add to command with value as DBNull.Value
*
*
*/
// TODO: Possible Parallel.Foreach here?
cachedRoutine?.Parameters?.ForEach(expectedParm =>
{
if (expectedParm.IsResult)
{
return;
}
(var sqlType, var udtType) = Controllers.ExecController.GetSqlDbTypeFromParameterType(expectedParm.SqlDataType);
object parmValue = null;
var newSqlParm = new SqlParameter(expectedParm.Name, sqlType, expectedParm.MaxLength);
newSqlParm.UdtTypeName = udtType;
if (!expectedParm.IsOutput)
{
newSqlParm.Direction = ParameterDirection.Input;
}
else
{
newSqlParm.Direction = ParameterDirection.InputOutput;
}
// trim leading '@'
var expectedParmName = expectedParm.Name.Substring(1);
var pluginParamVal = GetParameterValueFromPlugins(cachedRoutine, expectedParmName, plugins);
// if the expected parameter was defined in the request or if a plugin provided an override
if (inputParameters.ContainsKey(expectedParmName) || pluginParamVal != PluginSetParameterValue.DontSet)
{
object val = null;
// TODO: Should input parameter if specified be able to override any plugin value?
// TODO: For now a plugin value take precendence over an input value - we can perhaps make this a property of the plugin return value (e.g. AllowInputOverride)
if (pluginParamVal != PluginSetParameterValue.DontSet)
{
val = pluginParamVal.Value;
}
else if (inputParameters.ContainsKey(expectedParmName))
{
val = inputParameters[expectedParmName];
}
// look for special jsDAL Server variables
val = jsDALServerVariables.Parse(remoteIpAddress, val);
if (val == null || val == DBNull.Value)
{
parmValue = DBNull.Value;
}
// TODO: Consider making this 'null' mapping configurable. This is just a nice to have for when the client does not call the API correctly
// convert the string value of 'null' to actual DBNull null
else if (val.ToString().Trim().ToLower().Equals(Strings.@null))
{
parmValue = DBNull.Value;
}
else
{
parmValue = ConvertParameterValue(expectedParmName, sqlType, val.ToString(), udtType);
}
newSqlParm.Value = parmValue;
cmd.Parameters.Add(newSqlParm);
}
else
{
// if (expectedParm.HasDefault && !expectedParm.IsOutput/*SQL does not apply default values to OUT parameters so OUT parameters will always be mandatory to define*/)
if (expectedParm.HasDefault || expectedParm.IsOutput)
{
// TODO: If expectedParm.IsOutput and the expectedParm not specified, refer to Endpoint config on strategy ... either auto specify and make null or let SQL throw
// If no explicit value was specified but the parameter has it's own default...
// Then DO NOT set newSqlParm.Value so that the DB engine applies the default defined in SQL
newSqlParm.Value = null;
cmd.Parameters.Add(newSqlParm);
}
else
{
// SQL Parameter does not get added to cmd.Parameters and SQL will throw
}
}
}); // foreach Parameter
}
} // execRoutine
private static SqlCommand CreateSqlCommand(SqlConnection con, int commandTimeOutInSeconds, CachedRoutine cachedRoutine, Controllers.ExecController.ExecType type)
{
var cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandTimeout = commandTimeOutInSeconds;
var isTVF = cachedRoutine.Type == "TVF";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = string.Format("[{0}].[{1}]", cachedRoutine.Schema, cachedRoutine.Routine);
if (isTVF)
{
string parmCsvList = string.Join(",", cachedRoutine.Parameters.Where(p => !p.IsResult).Select(p => p.Name).ToArray());
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("select * from [{0}].[{1}]({2})", cachedRoutine.Schema, cachedRoutine.Routine, parmCsvList);
}
else if (type == Controllers.ExecController.ExecType.Scalar)
{
string parmCsvList = string.Join(",", cachedRoutine.Parameters.Where(p => !p.IsResult).Select(p => p.Name).ToArray());
if (cachedRoutine.Type.Equals(string.Intern("PROCEDURE"), StringComparison.CurrentCultureIgnoreCase))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = string.Format("[{0}].[{1}]", cachedRoutine.Schema, cachedRoutine.Routine);
}
else if (cachedRoutine.Type.Equals(string.Intern("FUNCTION"), StringComparison.OrdinalIgnoreCase))
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("select [{0}].[{1}]({2})", cachedRoutine.Schema, cachedRoutine.Routine, parmCsvList);
}
}
return(cmd);
}
} // 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);
}
