/// <summary>
/// Initializes a new instance of the <see cref="BinaryCriteria"/> class.
/// </summary>
/// <param name="left">The left operand.</param>
/// <param name="op">The operator.</param>
/// <param name="right">The right operand.</param>
/// <exception cref="ArgumentNullException">
/// Left or right operand is null.
/// </exception>
/// <exception cref="ArgumentOutOfRangeException">Operator is not a binary one.</exception>
public BinaryCriteria(BaseCriteria left, CriteriaOperator op, BaseCriteria right)
{
if (ReferenceEquals(left, null))
{
throw new ArgumentNullException("left");
}
if (ReferenceEquals(right, null))
{
throw new ArgumentNullException("right");
}
if (op < CriteriaOperator.AND || op > CriteriaOperator.NotLike)
{
throw new ArgumentOutOfRangeException("op");
}
this.left = left;
this.right = right;
this.op = op;
}
private static BaseCriteria JoinIf(BaseCriteria criteria1, BaseCriteria criteria2, CriteriaOperator op)
{
if (ReferenceEquals(null, criteria1))
{
throw new ArgumentNullException("criteria1");
}
if (ReferenceEquals(null, criteria2))
{
throw new ArgumentNullException("criteria2");
}
if (criteria1.IsEmpty)
{
return(criteria2);
}
if (criteria2.IsEmpty)
{
return(criteria1);
}
return(new BinaryCriteria(criteria1, op, criteria2));
}
public BaseCriteria InStatement(BaseCriteria statement)
{
return(In(statement));
}
public BaseCriteria NotIn(BaseCriteria statement)
{
return(null);
}
public CaseBuilder WhenThen(BaseCriteria when, object then)
{
this.when.Add(when);
this.then.Add(then);
return(this);
}
public CaseBuilder When(BaseCriteria when)
{
this.when.Add(when);
return(this);
}
public InnerJoin(string toTable, string alias, BaseCriteria onCriteria)
: base(null, toTable, alias, onCriteria)
{
}
/// <summary>
/// Validates the specified criteria.
/// </summary>
/// <param name="criteria">The criteria.</param>
public void Validate(BaseCriteria criteria)
{
Visit(criteria);
}
public UnaryCriteria(string op, BaseCriteria operand)
{
}
public InnerJoin(IDictionary <string, Join> joins, string toTable, string alias, BaseCriteria onCriteria)
: base(joins, toTable, alias, onCriteria)
{
}
public BaseCriteria Process(BaseCriteria criteria)
{
return(Visit(criteria));
}
protected Join(IDictionary <string, Join> joins, string toTable, string alias, BaseCriteria onCriteria)
: base(toTable, alias)
{
this.joins = joins;
this.onCriteria = onCriteria;
if (!ReferenceEquals(this.onCriteria, null))
{
var aliases = JoinAliasLocator.Locate(this.onCriteria.ToStringIgnoreParams());
if (aliases != null && aliases.Count > 0)
{
referencedAliases = aliases;
}
}
var toTableAliases = JoinAliasLocator.Locate(this.Table);
if (toTableAliases != null && toTableAliases.Count > 0)
{
if (referencedAliases == null)
{
referencedAliases = toTableAliases;
}
else
{
foreach (var x in toTableAliases)
{
referencedAliases.Add(x);
}
}
}
if (joins != null)
{
if (joins.ContainsKey(this.Name))
{
throw new ArgumentException(String.Format(
"There is already a join with alias '{0}'", this.Name));
}
joins.Add(this.Name, this);
}
}
public BinaryCriteria(BaseCriteria left, string op, BaseCriteria right)
{
}
private void ToJson(JsonWriter writer, BaseCriteria criteria, JsonSerializer serializer)
{
if (criteria is null ||
criteria.IsEmpty)
{
writer.WriteNull();
return;
}
if (criteria is ValueCriteria valueCriteria)
{
var value = valueCriteria.Value;
if (value != null && value is IEnumerable && !(value is string))
{
// make sure that first value in array won't be recognized as a operator
// while deserializing (e.g. if values are [">", "a", "b"], serialize them
// as [[">", "a", "b"]] so that the first > won't be recognized as GE operator.
writer.WriteStartArray();
serializer.Serialize(writer, value);
writer.WriteEndArray();
return;
}
serializer.Serialize(writer, value);
return;
}
if (criteria is ParamCriteria prm)
{
writer.WriteStartArray();
serializer.Serialize(writer, prm.Name);
writer.WriteEndArray();
return;
}
if (criteria is Criteria crit)
{
writer.WriteStartArray();
serializer.Serialize(writer, crit.Expression);
writer.WriteEndArray();
return;
}
if (criteria is UnaryCriteria unary)
{
writer.WriteStartArray();
writer.WriteValue(OperatorToKey[(int)unary.Operator]);
ToJson(writer, unary.Operand, serializer);
writer.WriteEndArray();
return;
}
if (criteria is BinaryCriteria binary)
{
writer.WriteStartArray();
ToJson(writer, binary.LeftOperand, serializer);
writer.WriteValue(OperatorToKey[(int)binary.Operator]);
ToJson(writer, binary.RightOperand, serializer);
writer.WriteEndArray();
return;
}
throw new JsonSerializationException(string.Format("Can't serialize criteria of type {0}", criteria.GetType().FullName));
}
/// <summary>
/// Gets the next display order value for a table or a group of records.</summary>
/// <param name="connection">
/// Connection (required).</param>
/// <param name="row">
/// Row with a display order field (required).</param>
/// <param name="filter">
/// Filter for records (can be null).</param>
/// <returns>
/// One more of maximum display order values of records in the group.
/// If none, 1.</returns>
public static int GetNextValue(IDbConnection connection, IDisplayOrderRow row, BaseCriteria filter = null)
{
return(GetNextValue(connection, ((Row)row).Table, row.DisplayOrderField, filter));
}
/// <summary>
/// Sets a records display order to to requested value, and also renumbers other records
/// in the group as required.</summary>
/// <param name="connection">
/// Connection (required).</param>
/// <param name="row">
/// Row with a display order and ID field (should implement IDbIdRow interface).</param>
/// <param name="filter">
/// Filter that will determine the record group (can be null).</param>
/// <param name="recordID">
/// ID value of the record.</param>
/// <param name="newDisplayOrder">
/// New display order of the record.</param>
/// <param name="descendingKeyOrder">
/// Will records with same display order values be sorted in ascending or descending ID order?
/// For example, if records with ID's 1, 2, 3 has display order value of "0", their actual display
/// orders are 1, 2 and 3. If this parameter is set to true (descending), their display orders will
/// become 3, 2, 1. This parameter controls if records that are added recently and has no display
/// order value assigned (or 0) be shown at start or at the end.</param>
/// <returns>
/// If any of the display order values is changed true.</returns>
public static bool ReorderValues(IDbConnection connection, IDisplayOrderRow row, BaseCriteria filter = null,
Int64?recordID = null, int newDisplayOrder = 1, bool descendingKeyOrder = false, bool hasUniqueConstraint = false)
{
return(ReorderValues(connection, ((Row)row).Table, (Field)((IIdRow)row).IdField, row.DisplayOrderField, filter, recordID,
newDisplayOrder, descendingKeyOrder, hasUniqueConstraint));
}
/// <summary>
/// Sets a records display order to to requested value, and also renumbers other records
/// in the group as required.</summary>
/// <param name="connection">
/// Connection (required).</param>
/// <param name="tableName">
/// Tablename (required).</param>
/// <param name="keyField">
/// ID field meta that will be used to locate the record (required).</param>
/// <param name="orderField">
/// Display order field meta.</param>
/// <param name="filter">
/// Filter that will determine the record group (can be null).</param>
/// <param name="recordID">
/// ID value of the record.</param>
/// <param name="newDisplayOrder">
/// New display order of the record.</param>
/// <param name="descendingKeyOrder">
/// Will records with same display order values be sorted in ascending or descending ID order?
/// For example, if records with ID's 1, 2, 3 has display order value of "0", their actual display
/// orders are 1, 2 and 3. If this parameter is set to true (descending), their display orders will
/// become 3, 2, 1. This parameter controls if records that are added recently and has no display
/// order value assigned (or 0) be shown at start or at the end.</param>
/// <returns>
/// If any of the display order values is changed true.</returns>
public static bool ReorderValues(IDbConnection connection, string tableName, Field keyField, Field orderField,
BaseCriteria filter = null, Int64?recordID = null, int newDisplayOrder = 1,
bool descendingKeyOrder = false, bool hasUniqueConstraint = false)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if (tableName == null || tableName.Length == 0)
{
throw new ArgumentNullException("tableName");
}
if (keyField == null)
{
throw new ArgumentNullException("keyField");
}
if (orderField == null)
{
throw new ArgumentNullException("orderField");
}
// last assigned display order value
int order = 0;
// a list that will contain an element for each record, and hold old and new display
// order values of records
List <OrderRecord> orderRecords = new List <OrderRecord>();
// link to the order entry for record whose display order value is asked to be changed
OrderRecord changing = null;
// query to fetch id and display order values of the records in the group
SqlQuery query = new SqlQuery()
.Select(
keyField,
orderField)
.From(
tableName, Alias.T0)
.Where(
filter)
.OrderBy(
orderField);
// determine display order for records with same display order values
// based on ID ordering set
query.OrderBy(keyField.Name, desc: descendingKeyOrder);
// read all existing records
using (IDataReader reader = SqlHelper.ExecuteReader(connection, query))
{
while (reader.Read())
{
// each records actual display order value is one more than previous one
order++;
// create an entry to hold current and new display order value of the record
OrderRecord r = new OrderRecord();
// record ID
r.recordID = Convert.ToInt64(reader.GetValue(0));
// old display order field value (not the actual display order!)
r.oldOrder = Convert.ToInt32(reader.GetValue(1));
// new display order value (actual one to be set)
r.newOrder = order;
orderRecords.Add(r);
// if this is the one that is requested to be changed, hold a link to its entry
if (recordID == r.recordID)
{
changing = r;
}
}
}
// ensure that the new display order is within limits
// if its lower than 1 or bigger than record count, fix it
if (newDisplayOrder <= 0)
{
newDisplayOrder = 1;
}
else if (newDisplayOrder > order)
{
newDisplayOrder = order;
}
// if the record whose display order is to be changed can be found, and its display order value is different
// than the one in database
if (changing != null && changing.newOrder != newDisplayOrder)
{
// let's say record had a display order value of 6and now it will become 10, the records with actual
// display orders of 7, 8, 9, 10 will become 6, 7, 8, 9 orders.
//
// WARNING: notice that array is 0 based, so record with actual display order of 7 is in the
// 6th index in the array)
for (int i = changing.newOrder; i < newDisplayOrder; i++)
{
orderRecords[i].newOrder = i;
}
// if the records display order is to be changed from 9 to 5, the records with actual orders of 5, 6, 7, 8
// is going to be 6, 7, 8, 9 ordered.
for (int i = newDisplayOrder - 1; i < changing.newOrder - 1; i++)
{
orderRecords[i].newOrder = i + 2;
}
// as the records that will be changing are assigned new orders, we may assign new display order
// directly.
changing.newOrder = newDisplayOrder;
}
// from now on, apply the new display order values by preparing queries
// StringBuilder that will contain query(s)
StringBuilder queries = new StringBuilder();
if (SqlSettings.CurrentDialect.NeedsExecuteBlockStatement())
{
queries.AppendLine("EXECUTE BLOCK AS");
queries.AppendLine("BEGIN");
}
int updateCount = 0;
Action <long, long> appendSingleUpdate = delegate(long id, long newOrder)
{
queries.AppendLine(String.Format(
"UPDATE {0} SET {1} = {2} WHERE {3} = {4};", tableName,
orderField.Name, newOrder, keyField.Name, id));
updateCount++;
};
if (hasUniqueConstraint)
{
var byCurrentOrder = new Dictionary <Int64, OrderRecord>();
foreach (var rec in orderRecords)
{
byCurrentOrder[rec.oldOrder] = rec;
}
var list = new List <OrderRecord>();
list.AddRange(orderRecords);
list.Sort((x, y) => (x.newOrder - y.newOrder));
foreach (var rec in list)
{
if (rec.oldOrder != rec.newOrder)
{
byCurrentOrder.Remove(rec.oldOrder);
OrderRecord congestion;
if (byCurrentOrder.TryGetValue(rec.newOrder, out congestion))
{
var empty = list.Count * 2;
while (byCurrentOrder.ContainsKey(empty))
{
empty++;
}
congestion.oldOrder = empty;
appendSingleUpdate(congestion.recordID, empty);
byCurrentOrder[empty] = congestion;
}
appendSingleUpdate(rec.recordID, rec.newOrder);
byCurrentOrder[rec.newOrder] = rec;
}
}
}
else
{
// StringBuilder that will contain IN(...) part of the latest query
StringBuilder sb = new StringBuilder();
// scan all display order changing records
int start = 0;
while (start < orderRecords.Count)
{
OrderRecord rs = orderRecords[start];
// if this records display order is not changed, skip it
if (rs.oldOrder == rs.newOrder)
{
start++;
continue;
}
// find the difference between old and new display orders
int difference = rs.oldOrder - rs.newOrder;
// clear the IN(...) list
sb.Length = 0;
// add this records ID to the IN (...) part
sb.Append(rs.recordID);
// now we'll find all following records whose display orders are changed same amount
// (difference between old and new is same), so we will update them with just one query
// like UPDATE ORDER = ORDER + 1 WHERE ID IN (X, Y, Z....).
int finish = start;
while (finish + 1 < orderRecords.Count)
{
// if we found more than 100 records whose display orders changed same amount, to
// limit IN(...) part to overgrow, break searching and run the query. Collect the
// rest in another query. If query is too complex, might result in performance
// degration in SQL server
if (finish - start >= 100)
{
break;
}
OrderRecord rf = orderRecords[finish + 1];
// is this records display order value changed same amount
if (rf.oldOrder - rf.newOrder != difference)
{
break;
}
sb.Append(',');
sb.Append(rf.recordID);
finish++;
}
// if only one record in batch, no need to use IN clause
if (start == finish)
{
queries.AppendLine(String.Format(
"UPDATE {0} SET {1} = {2} WHERE {3} = {4};", tableName,
orderField.Name, rs.newOrder, keyField.Name, rs.recordID));
updateCount++;
}
else
{
// batch update, use IN (...)
OrderRecord rf = orderRecords[finish];
queries.AppendLine(String.Format(
"UPDATE {0} SET {1} = {1} - ({2}) WHERE ({3} IN ({4}));",
tableName,
orderField.Name,
rs.oldOrder - rs.newOrder,
keyField.Name,
sb.ToString()));
updateCount++;
}
start = finish + 1;
}
}
if (queries.Length > 0 && updateCount > 0)
{
if (SqlSettings.CurrentDialect.NeedsExecuteBlockStatement())
{
queries.AppendLine("END;");
}
SqlHelper.ExecuteNonQuery(connection, queries.ToString());
// one ore more records has changed display order values
return(true);
}
else
{
// nothing changed, all display orders are same
return(false);
}
}
