/// <summary>
/// Adds a field-value pair to be updated. If value is equal to defaultValue, value will NOT be added to this update row
/// </summary>
/// <param name="field">The field name associated with the value</param>
/// <param name="value">Any value (string, number, enum, ...)</param>
/// <param name="defaultValue">Default value (usually defined in database structure)</param>
/// <param name="isFlag">If set to true the value, "0x" will be append to value</param>
/// <param name="noQuotes">If value is a string and this is set to true, value will not be 'quoted' (SQL variables)</param>
public void AddValue <T>(string field, T value, T defaultValue, bool isFlag = false, bool noQuotes = false)
{
// T used because it is compile time safe. We know that value and defaultValue got the same type
// ReSharper disable CompareNonConstrainedGenericWithNull
if (value == null)
{
// ReSharper restore CompareNonConstrainedGenericWithNull
return;
}
if (value is float || value is double)
{
if (Math.Abs(Convert.ToDouble(value) - Convert.ToDouble(defaultValue)) < 0.000001)
{
return;
}
}
if (value.Equals(defaultValue))
{
return;
}
Values[SQLUtil.AddBackQuotes(field)] = SQLUtil.ToSQLValue(value, isFlag, noQuotes);
}
/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>Single insert row (data)</returns>
public string Build()
{
if (NoData)
{
return("-- " + _headerComment + Environment.NewLine);
}
StringBuilder query = new StringBuilder();
if (_row.CommentOut)
{
query.Append("-- ");
}
query.Append("(");
foreach (object value in SQLUtil.GetFields <T>().Select(field => field.Item2.GetValue(_row.Data)))
{
if (value == null)
{
query.Append("UNKNOWN");
query.Append(SQLUtil.CommaSeparator);
}
else
{
Array arr = value as Array;
if (arr != null)
{
foreach (object v in arr)
{
if (v == null)
{
query.Append("UNKNOWN");
}
else
{
query.Append(SQLUtil.ToSQLValue(v));
}
query.Append(SQLUtil.CommaSeparator);
}
}
else
{
query.Append(SQLUtil.ToSQLValue(value));
query.Append(SQLUtil.CommaSeparator);
}
}
}
query.Remove(query.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", "
query.Append("),");
if (!string.IsNullOrWhiteSpace(_row.Comment))
{
query.Append(" -- " + _row.Comment);
}
return(query.ToString());
}
/// <summary>
/// Adds a field-value pair to be updated
/// </summary>
/// <param name="field">The field name associated with the value</param>
/// <param name="value">Any value (string, number, enum, ...)</param>
/// <param name="isFlag">If set to true the value, "0x" will be append to value</param>
/// <param name="noQuotes">If value is a string and this is set to true, value will not be 'quoted' (SQL variables)</param>
public void AddValue(string field, object value, bool isFlag = false, bool noQuotes = false)
{
if (value == null)
{
value = "";
}
Values[SQLUtil.AddBackQuotes(field)] = SQLUtil.ToSQLValue(value, isFlag, noQuotes);
}
/// <summary>
/// <para>Adds a value to this row</para>
/// <remarks>null value will be 0</remarks>
/// </summary>
/// <param name="field">The field name associated with the value</param>
/// <param name="value">Any value (string, number, enum, ...)</param>
/// <param name="isFlag">If set to true the value, "0x" will be append to value</param>
/// <param name="noQuotes">If value is a string and this is set to true, value will not be 'quoted' (SQL variables)</param>
public void AddValue(string field, object value, bool isFlag = false, bool noQuotes = false)
{
if (value == null)
{
value = 0;
}
_values.Add(SQLUtil.ToSQLValue(value, isFlag, noQuotes).ToString());
FieldNames.Add(field);
}
/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>Single insert row (data)</returns>
public string Build()
{
if (NoData)
{
return("-- " + _headerComment + Environment.NewLine);
}
StringBuilder query = new StringBuilder();
if (_row.CommentOut)
{
query.Append("-- ");
}
query.Append("(");
foreach (var field in _databaseFields)
{
object value = field.Item2.GetValue(_row.Data);
if (value == null)
{
if (field.Item3.Any(a => a.Nullable))
{
query.Append("NULL");
query.Append(SQLUtil.CommaSeparator);
}
else
{
query.Append("UNKNOWN");
query.Append(SQLUtil.CommaSeparator);
}
}
else
{
if (value is Blob blob)
{
query.Append(SQLUtil.ToSQLValue(blob));
query.Append(SQLUtil.CommaSeparator);
}
else if (value is Array arr)
{
foreach (object v in arr)
{
if (v == null)
{
if (field.Item3.Any(a => a.Nullable))
{
query.Append("NULL");
}
else
{
query.Append("UNKNOWN");
}
}
else
{
query.Append(SQLUtil.ToSQLValue(v, noQuotes: field.Item3.Any(a => a.NoQuotes)));
}
query.Append(SQLUtil.CommaSeparator);
}
}
else
{
query.Append(SQLUtil.ToSQLValue(value, noQuotes: field.Item3.Any(a => a.NoQuotes == true)));
query.Append(SQLUtil.CommaSeparator);
}
}
}
query.Remove(query.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", "
query.Append("),");
if (!string.IsNullOrWhiteSpace(_row.Comment))
{
query.Append(" -- " + _row.Comment);
}
return(query.ToString());
}
/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>A single update query</returns>
public virtual string Build()
{
StringBuilder query = new StringBuilder();
if (CommentOut)
{
query.Append("-- ");
}
// Return empty if there are no values or where clause or no table name set
if (!WhereClause.HasConditions)
{
return(string.Empty);
}
query.Append("UPDATE ");
query.Append(SQLUtil.GetTableName <T>());
query.Append(" SET ");
bool hasValues = false;
foreach (var field in _databaseFields)
{
object value = field.Item2.GetValue(_value.Data);
Array arr = value as Array;
if (arr != null)
{
for (int i = 0; i < arr.Length; i++)
{
object v = arr.GetValue(i);
if (v == null)
{
continue;
}
query.Append(SQLUtil.AddBackQuotes(field.Item3.First().Name + (field.Item3.First().StartAtZero ? i : i + 1)));
query.Append("=");
query.Append(SQLUtil.ToSQLValue(v, noQuotes: field.Item3.Any(a => a.NoQuotes)));
query.Append(SQLUtil.CommaSeparator);
hasValues = true;
}
continue;
}
if (value == null)
{
continue;
}
if (field.Item2.Name != "VerifiedBuild" || !Settings.SkipOnlyVerifiedBuildUpdateRows)
{
hasValues = true;
}
query.Append(field.Item1);
query.Append("=");
query.Append(SQLUtil.ToSQLValue(value, noQuotes: field.Item3.Any(a => a.NoQuotes)));
query.Append(SQLUtil.CommaSeparator);
}
if (!hasValues)
{
return(string.Empty);
}
query.Remove(query.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", "
query.Append(" WHERE ");
query.Append(WhereClause.Build());
query.Append(";");
if (!string.IsNullOrWhiteSpace(_value.Comment))
{
query.Append(" -- " + _value.Comment);
}
return(query.ToString());
}
public string Build()
{
if (_conditions == null || _conditions.Count == 0)
{
return(string.Empty);
}
StringBuilder whereClause = new StringBuilder();
if (_onlyPrimaryKeys && _conditions.GetPrimaryKeyCount() == 1)
{
var field = _databaseFields.Single(f => f.Item2 == _primaryKeyReflectionField);
whereClause.Append(field.Item1);
if (_conditions.Count == 1)
{
whereClause.Append("=");
whereClause.Append(field.Item2.GetValue(_conditions.First().Data));
}
else
{
whereClause.Append(" IN (");
foreach (Row <T> condition in _conditions)
{
object value = field.Item2.GetValue(condition.Data);
whereClause.Append(SQLUtil.ToSQLValue(value));
if (!string.IsNullOrEmpty(condition.Comment))
{
whereClause.Append(" /*" + condition.Comment + "*/");
}
whereClause.Append(SQLUtil.CommaSeparator);
}
whereClause.Remove(whereClause.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", "
whereClause.Append(")");
}
}
else
{
if (_conditions.Count > 1)
{
var result = BuildDistinct();
if (result != null)
{
return(result);
}
// Fallback to standard AND where clauses if the distinct one failed
}
foreach (Row <T> condition in _conditions)
{
whereClause.Append("(");
foreach (var field in _databaseFields)
{
object value = field.Item2.GetValue(condition.Data);
if (value == null ||
(_onlyPrimaryKeys &&
field.Item3.Any(a => !a.IsPrimaryKey)))
{
continue;
}
whereClause.Append(field.Item1);
whereClause.Append("=");
whereClause.Append(SQLUtil.ToSQLValue(value));
whereClause.Append(" AND ");
}
whereClause.Remove(whereClause.Length - 5, 5); // remove last " AND "
whereClause.Append(")");
whereClause.Append(" OR ");
}
whereClause.Remove(whereClause.Length - 4, 4); // remove last " OR ";
}
return(whereClause.ToString());
}
private string BuildDistinct()
{
// 1. Get a list of all conditions as list of field/value pairs
List <WhereCondition> whereConditions = new List <WhereCondition>();
foreach (Row <T> condition in _conditions)
{
var whereCondition = new WhereCondition();
foreach (var field in _databaseFields)
{
object value = field.Item2.GetValue(condition.Data);
if (value == null ||
(_onlyPrimaryKeys &&
field.Item3.Any(a => !a.IsPrimaryKey)))
{
continue;
}
whereCondition.Add(field.Item1, SQLUtil.ToSQLValue(value));
}
if (!whereCondition.IsEmpty)
{
whereConditions.Add(whereCondition);
}
}
// 2. Check if all conditions share the same fields
bool allShareSameFields = true;
var baselineConditionFields = whereConditions.First().FieldValuePairs.Keys;
foreach (var condition in whereConditions)
{
if (!condition.FieldValuePairs.Keys.SequenceEqual(baselineConditionFields))
{
allShareSameFields = false;
}
}
if (!allShareSameFields)
{
return(null);
}
// ToDo: support the case with more than 2 fields
if (baselineConditionFields.Count != 2)
{
return(null);
}
// 3. Get a distinct of values for each field
var fieldsWithDistinctValues = (from condition in whereConditions
from fieldValuePair in condition.FieldValuePairs
group fieldValuePair by fieldValuePair.Key into byField
select new { Field = byField.Key, Values = byField.ToList().Select(pair => pair.Value).Distinct().ToList() }
).OrderBy(field => field.Values.Count()).ToList();
// 4. Get the field with lowest different values
var fieldWithLowestDifferentValues = fieldsWithDistinctValues.First().Field;
// ToDo: support the case with more than 2 fields
var secondField = fieldsWithDistinctValues.Skip(1).First().Field;
// 5. Group conditions by the field with lowest different values
var conditionsByFieldWithLowestDifferentValues = whereConditions.GroupBy(cond => cond.FieldValuePairs[fieldWithLowestDifferentValues])
.ToDictionary(g => g.Key, g => g.ToList());
// 6. Build the where clause using the field with lowest different values for the first condition
StringBuilder whereClause = new StringBuilder();
foreach (var pair in conditionsByFieldWithLowestDifferentValues)
{
whereClause.Append("(");
whereClause.Append(fieldWithLowestDifferentValues);
whereClause.Append("=");
whereClause.Append(pair.Key);
whereClause.Append(" AND ");
whereClause.Append(secondField);
if (pair.Value.Select(cond => cond.FieldValuePairs[secondField]).Count() == 1)
{
whereClause.Append("=");
whereClause.Append(pair.Value.Select(cond => cond.FieldValuePairs[secondField]).First());
}
else
{
whereClause.Append(" IN (");
whereClause.Append(string.Join(',', pair.Value.Select(cond => cond.FieldValuePairs[secondField])));
whereClause.Append(")");
}
whereClause.Append(")");
whereClause.Append(" OR ");
}
whereClause.Length -= 4; // remove last " OR ";
return(whereClause.ToString());
}
public string Build()
{
if (_conditions == null || _conditions.Count == 0)
{
return(string.Empty);
}
StringBuilder whereClause = new StringBuilder();
if (_onlyPrimaryKeys && _conditions.GetPrimaryKeyCount() == 1)
{
var field = SQLUtil.GetFields <T>().Single(f => f.Item2 == SQLUtil.GetFirstPrimaryKey <T>());
whereClause.Append(field.Item1);
if (_conditions.Count == 1)
{
whereClause.Append("=");
whereClause.Append(field.Item2.GetValue(_conditions.First().Data));
}
else
{
whereClause.Append(" IN (");
foreach (Row <T> condition in _conditions)
{
object value = field.Item2.GetValue(condition.Data);
whereClause.Append(SQLUtil.ToSQLValue(value));
if (!string.IsNullOrEmpty(condition.Comment))
{
whereClause.Append(" /*" + condition.Comment + "*/");
}
whereClause.Append(SQLUtil.CommaSeparator);
}
whereClause.Remove(whereClause.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", "
whereClause.Append(")");
}
}
else
{
foreach (Row <T> condition in _conditions)
{
whereClause.Append("(");
foreach (var field in SQLUtil.GetFields <T>())
{
object value = field.Item2.GetValue(condition.Data);
if (value == null ||
(_onlyPrimaryKeys &&
field.Item3.Any(a => !a.IsPrimaryKey)))
{
continue;
}
whereClause.Append(field.Item1);
whereClause.Append("=");
whereClause.Append(SQLUtil.ToSQLValue(value));
whereClause.Append(" AND ");
}
whereClause.Remove(whereClause.Length - 5, 5); // remove last " AND "
whereClause.Append(")");
whereClause.Append(" OR ");
}
whereClause.Remove(whereClause.Length - 4, 4); // remove last " OR ";
}
return(whereClause.ToString());
}
/// <summary>
/// Adds a field-value pair to be updated
/// </summary>
/// <param name="field">The field name associated with the value</param>
/// <param name="value">Any value (string, number, enum, ...)</param>
/// <param name="isFlag">If set to true the value, "0x" will be append to value</param>
/// <param name="noQuotes">If value is a string and this is set to true, value will not be 'quoted' (SQL variables)</param>
public void AddValue(string field, object value, bool isFlag = false, bool noQuotes = false)
{
if (value != null)
{
_values.Add(new KeyValuePair <string, object>(SQLUtil.AddBackQuotes(field), SQLUtil.ToSQLValue(value, isFlag, noQuotes)));
}
}