/// <summary>
/// Register an entity profile that describes entity mapping and other options
/// </summary>
public void AddEntityProfile <TEntity>(EntityProfile profile)
{
if (profile == null)
{
throw new ArgumentNullException(nameof(profile));
}
var entityType = typeof(TEntity);
if (_supportedEntityTypes.ContainsKey(entityType))
{
throw new ConfigurationException($"Cannot add profile for {entityType.FullName} as it already exists in configuration.");
}
_supportedEntityTypes.Add(typeof(TEntity), profile);
}
/// <summary>
/// Generates sql upset command for bunch of elements
/// </summary>
/// <param name="elements">elements that have to be upserted</param>
/// <param name="entityProfile">elements type profile (contains mapping and other options)</param>
/// <param name="cancellationToken"></param>
/// <returns>Returns a text of an sql upset command and collection of database parameters</returns>
public IList <SqlCommandBuilderResult> Generate <TEntity>(ICollection <TEntity> elements, EntityProfile entityProfile, CancellationToken cancellationToken)
where TEntity : class
{
if (elements == null)
{
throw new ArgumentNullException(nameof(elements));
}
if (entityProfile == null)
{
throw new ArgumentNullException(nameof(entityProfile));
}
if (elements.Count == 0)
{
throw new ArgumentException("There is no elements in the collection. At least one element must be.", nameof(elements));
}
_logger.LogTrace($"Generating upsert sql for {elements.Count} elements.");
if (entityProfile.UniqueConstraint == null)
{
throw new ArgumentException($"There is no unique constraint defined in the {entityProfile.GetType().FullName}", nameof(entityProfile));
}
if (entityProfile.UniqueConstraint.UniqueProperties.All(p => p == null))
{
throw new ArgumentException($"There is no unique properties defined in the {entityProfile.GetType().FullName}", nameof(entityProfile));
}
var result = new List <SqlCommandBuilderResult>();
if (_logger.IsEnabled(LogLevel.Debug))
{
_logger.LogDebug($"{nameof(TEntity)}: {typeof(TEntity).FullName}");
_logger.LogDebug($"{nameof(elements)}.Count: {elements.Count}");
}
var(columns, returningClause, upsertClause) = this.GenerateClauses(entityProfile);
var hasReturningClause = !string.IsNullOrWhiteSpace(returningClause);
cancellationToken.ThrowIfCancellationRequested();
const int MAX_PARAMS_PER_CMD = 65_535;
var commandHeader = $"insert into {entityProfile.TableName} ({columns}) values ";
var entireCommandLength = commandHeader.Length
+ (returningClause?.Length ?? 0)
+ columns.Length * elements.Count;
if (_logger.IsEnabled(LogLevel.Debug))
{
_logger.LogDebug($"approximateEntireCommandLength: {entireCommandLength}");
}
var paramsCount = elements.Count * entityProfile.MaxPossibleSqlParameters;
var sqlParameters = new List <NpgsqlParameter>(paramsCount);
var commandBuilder = new StringBuilder(entireCommandLength);
commandBuilder.Append(commandHeader);
var elementIndex = -1;
var elementAbsIndex = -1;
using (var elementsEnumerator = elements.GetEnumerator()) {
while (elementsEnumerator.MoveNext())
{
elementAbsIndex++;
var item = elementsEnumerator.Current;
if (item == null)
{
continue;
}
elementIndex++;
cancellationToken.ThrowIfCancellationRequested();
commandBuilder.Append('(');
var firstPropertyValue = true;
foreach (var pair in entityProfile.Properties)
{
try {
var propInfo = pair.Value;
if (propInfo.IsAutoGenerated)
{
continue;
}
var delimiter = firstPropertyValue ? "" : ", ";
commandBuilder.Append(delimiter);
if (propInfo.IsDynamicallyInvoked())
{
var paramName = $"@param_{propInfo.DbColumnName}_{elementIndex}";
var value = propInfo.GetPropertyValue(item);
if (value == null)
{
// as count of parameters are limited, it's better to save params for non null values
commandBuilder.Append("null");
}
else
{
sqlParameters.Add(new NpgsqlParameter(paramName, propInfo.DbColumnType)
{
Value = value
});
commandBuilder.Append(paramName);
}
}
else
{
var propValue = propInfo.GetPropertyValueAsString(item);
commandBuilder.Append(propValue);
}
firstPropertyValue = false;
}
catch (Exception ex) {
var message = $"an error occurred while processing a property {pair.Key} of {entityProfile.EntityType.Name} entity, item idx: {elementAbsIndex}";
throw new SqlGenerationException(SqlOperation.Upsert, message, ex);
}
}
commandBuilder.Append(')');
if (sqlParameters.Count + entityProfile.MaxPossibleSqlParameters > MAX_PARAMS_PER_CMD)
{
if (hasReturningClause)
{
commandBuilder.AppendLine(upsertClause);
commandBuilder.Append(" returning ");
commandBuilder.Append(returningClause);
}
else
{
commandBuilder.Append(upsertClause);
}
commandBuilder.Append(";");
result.Add(new SqlCommandBuilderResult(
commandBuilder.ToString(),
sqlParameters,
isThereReturningClause: hasReturningClause,
elementsCount: elementIndex + 1
));
if (_logger.IsEnabled(LogLevel.Debug))
{
var(cmdSize, suffix) = ((long)commandBuilder.Length * 2).PrettifySize();
_logger.LogDebug($"Generated sql upsert command for {elementIndex + 1} {entityProfile.EntityType.Name} elements, command size {cmdSize:F2} {suffix}");
}
sqlParameters = new List <NpgsqlParameter>(sqlParameters.Count);
commandBuilder.Clear();
commandBuilder.Append(commandHeader);
elementIndex = -1;
}
else
{
// Finished with properties
if (elements.Count > 1 && elementAbsIndex < elements.Count - 1)
{
commandBuilder.Append(", ");
}
}
}
}
if (elementIndex == -1 && result.Count == 0)
{
throw new ArgumentException("There is no elements in the collection. At least one element must be.", nameof(elements));
}
if (hasReturningClause)
{
commandBuilder.AppendLine(upsertClause);
commandBuilder.Append(" returning ");
commandBuilder.Append(returningClause);
}
else
{
commandBuilder.Append(upsertClause);
}
commandBuilder.Append(";");
result.Add(new SqlCommandBuilderResult(
commandBuilder.ToString(),
sqlParameters,
isThereReturningClause: hasReturningClause,
elementsCount: elementIndex + 1
));
return(result);
}
/// <summary>
/// In one pass generates both columns and returning clauses
/// </summary>
/// <param name="entityProfile">elements type profile (contains mapping and other options)</param>
/// <returns>
/// Returns named tuple with generated columns, returning and upsert clauses.
/// If there is no properties that has to be included into returning clause then ReturningClause item in the result tuple will be an empty string.
/// </returns>
// ReSharper disable once MemberCanBePrivate.Global Needed to be public for unit testing purpose
public (string Columns, string ReturningClause, string UpsertClause) GenerateClauses(EntityProfile entityProfile)
{
if (entityProfile == null)
{
throw new ArgumentNullException(nameof(entityProfile));
}
var properties = entityProfile.Properties.Values;
var upsertClause = GenerateOnConflictClause(entityProfile.UniqueConstraint);
var firstUpdateSetColumn = true;
var returningClause = "";
var firstReturningColumn = true;
var columns = "";
var firstColumn = true;
foreach (var propInfo in properties)
{
if (propInfo.IsUpdatedAfterInsert || propInfo.IsUpdatedAfterUpdate)
{
var returningDelimiter = firstReturningColumn ? "" : ", ";
returningClause += $"{returningDelimiter}\"{propInfo.DbColumnName}\"";
firstReturningColumn = false;
}
if (propInfo.IsAutoGenerated)
{
continue;
}
if (!propInfo.IsPartOfUniqueConstraint)
{
var upsertSetDelimiter = firstUpdateSetColumn ? " do update set " : ", ";
upsertClause += $"{upsertSetDelimiter}\"{propInfo.DbColumnName}\" = excluded.\"{propInfo.DbColumnName}\"";
firstUpdateSetColumn = false;
}
var delimiter = firstColumn ? "" : ", ";
columns += $"{delimiter}\"{propInfo.DbColumnName}\"";
firstColumn = false;
}
if (_logger.IsEnabled(LogLevel.Debug))
{
_logger.LogDebug($"columns: {columns}");
_logger.LogDebug($"upsertClause: {upsertClause}");
_logger.LogDebug($"returningClause: {returningClause}");
}
return(columns, returningClause, upsertClause);
}
/// <summary> Generates bulk delete sql command with where clause using in operand
/// </summary>
/// <param name="elements">elements that have to be deleted</param>
/// <param name="entityProfile">elements type profile (contains mapping and other options)</param>
/// <param name="cancellationToken"></param>
/// <returns>Returns a text of an sql delete command and collection of database parameters</returns>
public IList <SqlCommandBuilderResult> Generate <TEntity>(ICollection <TEntity> elements,
EntityProfile entityProfile,
CancellationToken cancellationToken)
where TEntity : class
{
if (elements == null)
{
throw new ArgumentNullException(nameof(elements));
}
if (entityProfile == null)
{
throw new ArgumentNullException(nameof(entityProfile));
}
if (elements.Count == 0)
{
throw new ArgumentException($"There is no elements in the collection. At least one element must be.", nameof(elements));
}
var privateKeys = entityProfile.Properties
.Values
.Where(x => x.IsPrivateKey)
.ToList();
if (privateKeys.Count == 0)
{
throw new ArgumentException($"Entity {entityProfile.EntityType.FullName} must have at least one private key.",
nameof(entityProfile));
}
if (privateKeys.Count > 1)
{
throw new ArgumentException(
$"Cannot generate delete sql command with collapsed where clause as there are more than one private keys in the entity {entityProfile.EntityType.FullName}",
nameof(entityProfile));
}
_logger.LogTrace($"Generating delete sql for {elements.Count} elements.");
if (_logger.IsEnabled(LogLevel.Debug))
{
_logger.LogDebug($"{nameof(TEntity)}: {typeof(TEntity).FullName}");
_logger.LogDebug($"{nameof(elements)}.Count: {elements.Count}");
}
cancellationToken.ThrowIfCancellationRequested();
var commandHeader = $"delete from {entityProfile.TableName} where \"{privateKeys[0].DbColumnName}\" in (";
var result = new List <SqlCommandBuilderResult>();
var cmdBuilder = new StringBuilder();
var sqlParameters = privateKeys[0].IsDynamicallyInvoked()
? new List <NpgsqlParameter>(Math.Min(elements.Count, MAX_PARAMS_PER_CMD))
: null;
cmdBuilder.Append(commandHeader);
var elementAbsIndex = -1;
var elementIndex = -1;
using (var elementsEnumerator = elements.GetEnumerator()) {
while (elementsEnumerator.MoveNext())
{
elementAbsIndex++;
if (elementsEnumerator.Current == null)
{
continue;
}
try {
elementIndex++;
var whereDelimiter = elementIndex == 0 ? "" : ",";
if (sqlParameters != null && sqlParameters.Count + 1 > MAX_PARAMS_PER_CMD)
{
cmdBuilder.AppendLine(");");
result.Add(new SqlCommandBuilderResult
(
cmdBuilder.ToString(),
sqlParameters,
isThereReturningClause: false,
elementsCount: elementIndex
));
if (_logger.IsEnabled(LogLevel.Debug))
{
var(cmdSize, suffix) = ((long)cmdBuilder.Length * 2).PrettifySize();
_logger.LogDebug($"Generated sql where-in-delete command for {elementIndex + 1} {entityProfile.EntityType.Name} elements, command size {cmdSize:F2} {suffix}");
}
sqlParameters = new List <NpgsqlParameter>(sqlParameters.Count);
cmdBuilder.Clear();
cmdBuilder.Append(commandHeader);
whereDelimiter = "";
elementIndex = 0;
}
else if (elementIndex == MAX_IN_CLAUSE_IDS + 1)
{
cmdBuilder.AppendLine(");");
cmdBuilder.Append(commandHeader);
whereDelimiter = "";
}
if (privateKeys[0].IsDynamicallyInvoked())
{
var paramName = $"@param_{privateKeys[0].DbColumnName}_{elementIndex}";
cmdBuilder.Append($"{whereDelimiter}{paramName}");
var value = privateKeys[0].GetPropertyValue(elementsEnumerator.Current);
if (value == null)
{
throw new ArgumentException($"Private key must not be null. property: {privateKeys[0].DbColumnName}, item index: {elementAbsIndex}",
nameof(elements));
}
sqlParameters.Add(new NpgsqlParameter(paramName, privateKeys[0].DbColumnType)
{
Value = value
});
}
else
{
var propValue = privateKeys[0].GetPropertyValueAsString(elementsEnumerator.Current);
cmdBuilder.Append($"{whereDelimiter}{propValue}");
}
}
catch (Exception ex) {
var message = $"an error occurred while calculating {privateKeys[0].DbColumnName} of item at index {elementAbsIndex}";
throw new SqlGenerationException(SqlOperation.Delete, message, ex);
}
}
}
if (elementIndex == -1 && result.Count == 0)
{
throw new ArgumentException($"There is no elements in the collection. At least one element must be.", nameof(elements));
}
cmdBuilder.AppendLine(");");
result.Add(new SqlCommandBuilderResult
(
cmdBuilder.ToString(),
sqlParameters ?? new List <NpgsqlParameter>(),
isThereReturningClause: false,
elementsCount: elementIndex + 1
));
return(result);
}
/// <summary>
/// Generates bulk update sql command
/// </summary>
/// <param name="elements">elements that have to be updated</param>
/// <param name="entityProfile">elements type profile (contains mapping and other options)</param>
/// <param name="cancellationToken"></param>
/// <returns>Returns a text of an sql update command and collection of database parameters</returns>
public IList <SqlCommandBuilderResult> Generate <TEntity>(ICollection <TEntity> elements,
EntityProfile entityProfile,
CancellationToken cancellationToken)
where TEntity : class
{
if (elements == null)
{
throw new ArgumentNullException(nameof(elements));
}
if (entityProfile == null)
{
throw new ArgumentNullException(nameof(entityProfile));
}
if (elements.Count == 0)
{
throw new ArgumentException($"There is no elements in the collection. At least one element must be.", nameof(elements));
}
_logger.LogTrace($"Generating update sql for {elements.Count} elements.");
var resultCommand = "";
if (_logger.IsEnabled(LogLevel.Debug))
{
_logger.LogDebug($"{nameof(TEntity)}: {typeof(TEntity).FullName}");
_logger.LogDebug($"{nameof(elements)}.Count: {elements.Count}");
}
cancellationToken.ThrowIfCancellationRequested();
const int MAX_PARAMS_PER_CMD = 65_535;
var paramsCount = elements.Count * entityProfile.MaxPossibleSqlParameters;
var sqlParameters = new List <NpgsqlParameter>(Math.Min(paramsCount, MAX_PARAMS_PER_CMD));
var result = new List <SqlCommandBuilderResult>();
var isThereReturningClause = false;
var allElementsAreNull = true;
var elementIndex = 0;
var elementAbsIndex = 0;
using (var elementsEnumerator = elements.GetEnumerator()) {
var thereIsMoreElements = true;
// I'd like to build the first update command, so I can estimate an approximate size of all commands
// ignore all null items until find the first not null item
while (elementsEnumerator.Current == null && thereIsMoreElements)
{
thereIsMoreElements = elementsEnumerator.MoveNext();
elementAbsIndex++;
}
if (thereIsMoreElements)
{
allElementsAreNull = false;
var(commandForOneItem, itemParameters, hasReturningClause)
= GenerateForItem(entityProfile, elementsEnumerator.Current, null, 0, elementAbsIndex);
isThereReturningClause = hasReturningClause;
sqlParameters.AddRange(itemParameters);
var maxElementsInCmd = (int)Math.Ceiling(MAX_PARAMS_PER_CMD / (double)itemParameters.Count);
var entireCommandLength = elements.Count * itemParameters.Count <= MAX_PARAMS_PER_CMD
? commandForOneItem.Length * elements.Count
: commandForOneItem.Length * maxElementsInCmd;
if (_logger.IsEnabled(LogLevel.Debug))
{
_logger.LogDebug($"entire command length: {entireCommandLength}");
}
var commandBuilder = new StringBuilder(entireCommandLength);
commandBuilder.AppendLine(commandForOneItem);
while (elementsEnumerator.MoveNext())
{
elementAbsIndex++;
// ignore all null items
if (elementsEnumerator.Current == null)
{
continue;
}
elementIndex++;
if (sqlParameters.Count + entityProfile.MaxPossibleSqlParameters > MAX_PARAMS_PER_CMD)
{
result.Add(new SqlCommandBuilderResult(
commandBuilder.ToString(),
sqlParameters,
isThereReturningClause,
elementsCount: elementIndex
));
if (_logger.IsEnabled(LogLevel.Debug))
{
var(cmdSize, suffix) = ((long)commandBuilder.Length * 2).PrettifySize();
_logger.LogDebug($"Generated sql update command for {elementIndex + 1} {entityProfile.EntityType.Name} elements, command size {cmdSize:F2} {suffix}");
}
commandBuilder.Clear();
sqlParameters = new List <NpgsqlParameter>(sqlParameters.Count);
elementIndex = 0;
}
(commandForOneItem, itemParameters, _)
= GenerateForItem(entityProfile, elementsEnumerator.Current, commandBuilder, elementIndex, elementAbsIndex);
sqlParameters.AddRange(itemParameters);
commandBuilder.AppendLine(commandForOneItem);
}
resultCommand = commandBuilder.ToString();
if (_logger.IsEnabled(LogLevel.Debug))
{
_logger.LogDebug($"result command: {resultCommand}");
}
}
}
if (allElementsAreNull)
{
throw new ArgumentException("There is no elements in the collection. At least one element must be.", nameof(elements));
}
result.Add(new SqlCommandBuilderResult(
command: resultCommand,
sqlParameters: sqlParameters,
isThereReturningClause: isThereReturningClause,
elementsCount: elementIndex + 1
));
return(result);
}
/// <inheritdoc />
public IList <SqlCommandBuilderResult> Generate <TEntity>(ICollection <TEntity> elements, EntityProfile entityProfile, CancellationToken cancellationToken) where TEntity : class
{
if (elements == null)
{
throw new ArgumentNullException(nameof(elements));
}
if (entityProfile == null)
{
throw new ArgumentNullException(nameof(entityProfile));
}
if (elements.Count == 0)
{
throw new ArgumentException("There is no elements in the collection. At least one element must be.", nameof(elements));
}
var privateKeyCount = entityProfile.Properties.Count(x => x.Value.IsPrivateKey);
if (privateKeyCount == 0)
{
throw new ArgumentException($"Entity {entityProfile.EntityType.FullName} must have at least one private key.",
nameof(entityProfile));
}
if (privateKeyCount == 1)
{
_logger.LogDebug($"WhereIn sql-delete command builder has been selected as there is only one private key in entity {entityProfile.EntityType.FullName}");
return(_whereInBuilder.Generate(elements, entityProfile, cancellationToken));
}
_logger.LogDebug($"WhereOr sql-delete command builder has been selected as there are more than one private keys in entity {entityProfile.EntityType.FullName}");
return(_whereOrBuilder.Generate(elements, entityProfile, cancellationToken));
}
/// <summary>
/// Generates bulk delete sql command
/// </summary>
/// <param name="elements">elements that have to be deleted</param>
/// <param name="entityProfile">elements type profile (contains mapping and other options)</param>
/// <param name="cancellationToken"></param>
/// <returns>Returns a text of an sql delete command and collection of database parameters</returns>
public IList <SqlCommandBuilderResult> Generate <TEntity>(ICollection <TEntity> elements, EntityProfile entityProfile,
CancellationToken cancellationToken)
where TEntity : class
{
if (elements == null)
{
throw new ArgumentNullException(nameof(elements));
}
if (entityProfile == null)
{
throw new ArgumentNullException(nameof(entityProfile));
}
if (elements.Count == 0)
{
throw new ArgumentException($"There is no elements in the collection. At least one element must be.", nameof(elements));
}
_logger.LogTrace($"Generating delete sql for {elements.Count} elements.");
var result = new List <SqlCommandBuilderResult>();
var resultCommand = "";
var paramsCount = elements.Count * entityProfile.Properties
.Values
.Count(propInfo => propInfo.IsPrivateKey);
var sqlParameters = new List <NpgsqlParameter>(Math.Min(paramsCount, MAX_PARAMS_PER_CMD));
if (_logger.IsEnabled(LogLevel.Debug))
{
_logger.LogDebug($"{nameof(TEntity)}: {typeof(TEntity).FullName}");
_logger.LogDebug($"{nameof(elements)}.Count: {elements.Count}");
}
cancellationToken.ThrowIfCancellationRequested();
var allElementsAreNull = true;
var elementIndex = -1;
using (var elementsEnumerator = elements.GetEnumerator()) {
var thereIsMoreElements = true;
// ignore all null items until find the first not null item
while (elementsEnumerator.Current == null && thereIsMoreElements)
{
elementIndex++;
thereIsMoreElements = elementsEnumerator.MoveNext();
}
if (thereIsMoreElements)
{
allElementsAreNull = false;
var(commandForOneItem, itemParameters) = GenerateForItem(entityProfile, elementsEnumerator.Current, null, elementIndex);
sqlParameters.AddRange(itemParameters);
var commandBuilder = new StringBuilder(commandForOneItem.Length * elements.Count - elementIndex);
commandBuilder.AppendLine(commandForOneItem);
while (elementsEnumerator.MoveNext())
{
elementIndex++;
// ignore all null items
if (elementsEnumerator.Current == null)
{
continue;
}
if (sqlParameters.Count + entityProfile.MaxPossibleSqlParameters > MAX_PARAMS_PER_CMD)
{
result.Add(new SqlCommandBuilderResult(
commandBuilder.ToString(),
sqlParameters,
isThereReturningClause: false,
elementsCount: elementIndex
));
if (_logger.IsEnabled(LogLevel.Debug))
{
var(cmdSize, suffix) = ((long)commandBuilder.Length * 2).PrettifySize();
_logger.LogDebug($"Generated sql simple delete command for {elementIndex} {entityProfile.EntityType.Name} elements, command size {cmdSize:F2} {suffix}");
}
commandBuilder.Clear();
sqlParameters = new List <NpgsqlParameter>(sqlParameters.Count);
elementIndex = 0;
}
(commandForOneItem, itemParameters) = GenerateForItem(entityProfile, elementsEnumerator.Current, commandBuilder, elementIndex);
sqlParameters.AddRange(itemParameters);
commandBuilder.AppendLine(commandForOneItem);
}
resultCommand = commandBuilder.ToString();
if (_logger.IsEnabled(LogLevel.Debug))
{
_logger.LogDebug($"result command: {resultCommand}");
}
}
}
if (allElementsAreNull)
{
throw new ArgumentException($"There is no elements in the collection. At least one element must be.", nameof(elements));
}
result.Add(new SqlCommandBuilderResult(
resultCommand,
sqlParameters,
isThereReturningClause: false,
elementsCount: elementIndex + 1
));
return(result);
}
private (bool NeedOperated, bool NeedNotOperated, bool NeedProblem) GetExtendedFailureInformation(EntityProfile entityProfile)
{
if (entityProfile == null)
{
throw new ArgumentNullException(nameof(entityProfile));
}
var needOperated = entityProfile.IsOperatedElementsEnabled ?? _options.IsOperatedElementsEnabled;
var needNotOperated = entityProfile.IsNotOperatedElementsEnabled ?? _options.IsNotOperatedElementsEnabled;
var needProblem = entityProfile.IsProblemElementsEnabled ?? _options.IsProblemElementsEnabled;
return(needOperated, needNotOperated, needProblem);
}
