/// <summary>
/// Executes a multi-record insert query clause with <em>SELECT UNION ALL</em>.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="storage">The storage to use.</param>
/// <param name="tableName">The table name to against which to execute the query.</param>
/// <param name="parameters">The parameters to insert.</param>
/// <param name="nameMap">If provided, maps property names from <typeparamref name="T"/> to ones provided in the map.</param>
/// <param name="onlyOnceColumns">If given, SQL parameter values for the given <typeparamref name="T"/> property types are generated only once. Effective only when <paramref name="useSqlParams"/> is <em>TRUE</em>.</param>
/// <param name="useSqlParams"><em>TRUE</em> if the query should be in parameterized form. <em>FALSE</em> otherwise.</param>
/// <returns>The rows affected.</returns>
public static Task <int> ExecuteMultipleInsertIntoAsync <T>(this IRelationalStorage storage, string tableName, IEnumerable <T> parameters, IReadOnlyDictionary <string, string> nameMap = null, IEnumerable <string> onlyOnceColumns = null, bool useSqlParams = true)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentException("The name must be a legal SQL table name", "tableName");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
var storageConsts = DbConstantsStore.GetDbConstants(storage.InvariantName);
var startEscapeIndicator = storageConsts.StartEscapeIndicator;
var endEscapeIndicator = storageConsts.EndEscapeIndicator;
//SqlParameters map is needed in case the query needs to be parameterized in order to avoid two
//reflection passes as first a query needs to be constructed and after that when a database
//command object has been created, parameters need to be provided to them.
var sqlParameters = new Dictionary <string, object>();
const string insertIntoValuesTemplate = "INSERT INTO {0} ({1}) SELECT {2};";
var columns = string.Empty;
var values = new List <string>();
if (parameters.Any())
{
//Type and property information are the same for all of the objects.
//The following assumes the property names will be retrieved in the same
//order as is the index iteration done.
var onlyOnceRow = new List <string>();
var properties = parameters.First().GetType().GetProperties();
columns = string.Join(",", nameMap == null ? properties.Select(pn => string.Format("{0}{1}{2}", startEscapeIndicator, pn.Name, endEscapeIndicator)) : properties.Select(pn => string.Format("{0}{1}{2}", startEscapeIndicator, (nameMap.ContainsKey(pn.Name) ? nameMap[pn.Name] : pn.Name), endEscapeIndicator)));
if (onlyOnceColumns != null && onlyOnceColumns.Any())
{
var onlyOnceProperties = properties.Where(pn => onlyOnceColumns.Contains(pn.Name)).Select(pn => pn).ToArray();
var onlyOnceData = parameters.First();
for (int i = 0; i < onlyOnceProperties.Length; ++i)
{
var currentProperty = onlyOnceProperties[i];
var parameterValue = currentProperty.GetValue(onlyOnceData, null);
if (useSqlParams)
{
var parameterName = string.Format("@{0}", (nameMap.ContainsKey(onlyOnceProperties[i].Name) ? nameMap[onlyOnceProperties[i].Name] : onlyOnceProperties[i].Name));
onlyOnceRow.Add(parameterName);
sqlParameters.Add(parameterName, parameterValue);
}
else
{
onlyOnceRow.Add(string.Format(sqlFormatProvider, "{0}", parameterValue));
}
}
}
var dataRows = new List <string>();
var multiProperties = onlyOnceColumns == null ? properties : properties.Where(pn => !onlyOnceColumns.Contains(pn.Name)).Select(pn => pn).ToArray();
int parameterCount = 0;
foreach (var row in parameters)
{
for (int i = 0; i < multiProperties.Length; ++i)
{
var currentProperty = multiProperties[i];
var parameterValue = currentProperty.GetValue(row, null);
if (useSqlParams)
{
var parameterName = string.Format(indexedParameterTemplate, parameterCount);
dataRows.Add(parameterName);
sqlParameters.Add(parameterName, parameterValue);
++parameterCount;
}
else
{
dataRows.Add(string.Format(sqlFormatProvider, "{0}", parameterValue));
}
}
values.Add(string.Format("{0}", string.Join(",", onlyOnceRow.Concat(dataRows))));
dataRows.Clear();
}
}
var query = string.Format(insertIntoValuesTemplate, tableName, columns, string.Join(storageConsts.UnionAllSelectTemplate, values));
return(storage.ExecuteAsync(query, command =>
{
if (useSqlParams)
{
foreach (var sp in sqlParameters)
{
var p = command.CreateParameter();
p.ParameterName = sp.Key;
p.Value = sp.Value ?? DBNull.Value;
p.Direction = ParameterDirection.Input;
command.Parameters.Add(p);
}
}
}));
}
/// <summary>
/// Inserts the given statistics counters to the Orleans database.
/// </summary>
/// <param name="deploymentId">The deployment ID.</param>
/// <param name="hostName">The hostname.</param>
/// <param name="siloOrClientName">The silo or client name.</param>
/// <param name="id">The silo address or client ID.</param>
/// <param name="counters">The counters to be inserted.</param>
internal Task InsertStatisticsCountersAsync(string deploymentId, string hostName, string siloOrClientName,
string id, List <ICounter> counters)
{
var queryTemplate = dbStoredQueries.InsertOrleansStatisticsKey;
//Zero statistic values mean either that the system is not running or no updates. Such values are not inserted and pruned
//here so that no insert query or parameters are generated.
counters =
counters.Where(i => !"0".Equals(i.IsValueDelta ? i.GetDeltaString() : i.GetValueString())).ToList();
if (counters.Count == 0)
{
return(TaskDone.Done);
}
//Note that the following is almost the same as RelationalStorageExtensions.ExecuteMultipleInsertIntoAsync
//the only difference being that some columns are skipped. Likely it would be beneficial to introduce
//a "skip list" to RelationalStorageExtensions.ExecuteMultipleInsertIntoAsync.
//The template contains an insert for online. The part after SELECT is copied
//out so that certain parameters can be multiplied by their count. Effectively
//this turns a query of type (transaction details vary by vendor)
//BEGIN TRANSACTION; INSERT INTO [OrleansStatisticsTable] <columns> SELECT <variables>; COMMIT TRANSACTION;
//to BEGIN TRANSACTION; INSERT INTO [OrleansStatisticsTable] <columns> SELECT <variables>; UNION ALL <variables> COMMIT TRANSACTION;
//where the UNION ALL is multiplied as many times as there are counters to insert.
int startFrom = queryTemplate.IndexOf("SELECT", StringComparison.Ordinal) + "SELECT".Length + 1;
//This +1 is to have a space between SELECT and the first parameter name to not to have a SQL syntax error.
int lastSemicolon = queryTemplate.LastIndexOf(";", StringComparison.Ordinal);
int endTo = lastSemicolon > 0 ? queryTemplate.LastIndexOf(";", lastSemicolon - 1, StringComparison.Ordinal) : -1;
var template = queryTemplate.Substring(startFrom, endTo - startFrom);
var parameterNames = template.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(i => i.Trim()).ToArray();
var collectionOfParametersToBeUnionized = new List <string>();
var parametersToBeUnioned = new string[parameterNames.Length];
for (int counterIndex = 0; counterIndex < counters.Count; ++counterIndex)
{
for (int parameterNameIndex = 0; parameterNameIndex < parameterNames.Length; ++parameterNameIndex)
{
if (InsertStatisticsMultiupdateColumns.Contains(parameterNames[parameterNameIndex]))
{
//These parameters change for each row. The format is
//@StatValue0, @StatValue1, @StatValue2, ... @sStatValue{counters.Count}.
parametersToBeUnioned[parameterNameIndex] = $"{parameterNames[parameterNameIndex]}{counterIndex}";
}
else
{
//These parameters remain constant for every and each row.
parametersToBeUnioned[parameterNameIndex] = parameterNames[parameterNameIndex];
}
}
collectionOfParametersToBeUnionized.Add($"{string.Join(",", parametersToBeUnioned)}");
}
var storageConsts = DbConstantsStore.GetDbConstants(storage.InvariantName);
var query = queryTemplate.Replace(template, string.Join(storageConsts.UnionAllSelectTemplate, collectionOfParametersToBeUnionized));
return(ExecuteAsync(query, command =>
new DbStoredQueries.Columns(command)
{
DeploymentId = deploymentId,
HostName = hostName,
Counters = counters,
Name = siloOrClientName,
Id = id
}));
}