private SqlString AppendWhere(string commandText, Clauses clauses, SegmentPositions segmentPositions)
{
if (segmentPositions.WhereIndex > -1 &&
(clauses & Clauses.Where) == Clauses.Where)
{
// Remove the WHERE keyword and the subsequent space
var startIndex = segmentPositions.WhereIndex + 6;
var length = commandText.Length - startIndex;
if (segmentPositions.GroupByIndex > -1)
{
// Remove the space before the GROUP BY keyword
length = segmentPositions.GroupByIndex - startIndex;
}
else if (segmentPositions.OrderByIndex > -1)
{
// Remove the space before the ORDER BY keyword
length = segmentPositions.OrderByIndex - startIndex;
}
var segment = commandText.Substring(startIndex, length).Trim();
this.Where = segment;
}
return(this);
}
private void ParseExpression()
{
var expression = Expression;
Console.WriteLine(ExpressionTranslateTemp.Parse(expression, 0));
Clauses.AddRange(ExpressionTranslate.ParseClause(expression));
}
/// <summary>
/// Add a component clause to the query.
/// </summary>
/// <param name="component"></param>
/// <param name="clause"></param>
/// <returns></returns>
public Q Add(string component, AbstractClause clause)
{
clause.Component = component;
Clauses.Add(clause);
return((Q)this);
}
private void BuildQuery(string filterName, FilterClauseEnum clause, FilterConditionEnum condition, string filterValue)
{
FilterName = filterName;
Clause = clause;
Condition = condition;
FilterValue = filterValue;
foreach (FilterClauseEnum val in Enum.GetValues(typeof(FilterClauseEnum)))
{
var isSelected = val == clause;
Clauses.Add(new FilterEnumContainer {
Name = val.ToString(), Value = (int)val, Selected = isSelected
});
}
foreach (FilterConditionEnum val in Enum.GetValues(typeof(FilterConditionEnum)))
{
var isSelected = val == condition;
Conditions.Add(
new FilterEnumContainer {
Name = val.ToString(), Value = (int)val, Selected = isSelected
});
}
}
protected TSpecification Clause(Clause <TEntity> clause)
{
Clauses.Add(new ClauseData(CurrentOperator, clause));
CurrentOperator = Operator.And;
return(this as TSpecification);
}
private Clause <TEntity> CreateInternal(bool allowReturnAll = false)
{
if (!Clauses.Any() && allowReturnAll != true)
{
throw new Exception("No clauses specified, to retrieve all you need to set that it is enabled");
}
Clause <TEntity> clause = null;
foreach (var clauseData in Clauses)
{
if (clause == null)
{
clause = clauseData.Clause;
}
else
{
switch (clauseData.Operator)
{
case Operator.And:
clause &= clauseData.Clause;
break;
case Operator.Or:
clause |= clauseData.Clause;
break;
default:
throw new Exception("Unknown operator used");
}
}
}
return(clause);
}
public SqlSelectQuery <T> Take(int rows)
{
var clause = new SqlRowFetchNextClause(rows);
Clauses.Add(clause);
return(this);
}
public SqlSelectQuery <T> Skip(int rows)
{
var clause = new SqlRowOffsetClause(rows);
Clauses.Add(clause);
return(this);
}
public TSpecification EndGroup()
{
if (!Clauses.Any())
{
return(Parent);
}
var specification = CreateInternal();
if (specification != null)
{
Parent.Clause(specification);
}
else
{
switch (Parent.CurrentOperator)
{
case Operator.And:
Parent.Clause(new TrueSpecification <TEntity>());
break;
case Operator.Or:
Parent.Clause(new FalseSpecification <TEntity>());
break;
}
}
return(Parent);
}
/// <summary>
/// Builds the current set of Clauses and returns the full statement as a string.
/// </summary>
/// <param name="parameters">A list that will be filled with the statements parameters</param>
/// <returns></returns>
public string BuildStatement(List <SQLiteParameter> parameters)
{
StringBuilder builder = new StringBuilder();
int paramsCounter = parameters?.Count ?? 0;
int counter = 0;
// Remove empty expressions
Clauses.RemoveAll(x => x.Expressions.Count == 0);
// Loop through each Where clause (wrapped in parenthesis)
foreach (var clause in Clauses)
{
// Open Parent Clause grouping if we have more then 1 SubClause
int subCounter = 0;
builder.AppendIf(clause.Expressions.Count > 1 && Clauses.Count > 0, '(');
// Append each Sub Clause
foreach (var expression in clause.Expressions)
{
// If we have more sub clauses in this group, append operator
builder.AppendIf(++subCounter > 1, (InnerClauseOperator == LogicOperator.Or) ? " OR " : " AND ");
builder.Append((parameters == null) ? expression.ToString() : expression.BuildExpression(parameters));
}
// Close Parent Clause grouping
builder.AppendIf(clause.Expressions.Count > 1 && Clauses.Count > 0, ')');
// If we have more clauses, append operator
builder.AppendIf(++counter < Clauses.Count, (InnerClauseOperator == LogicOperator.And) ? " OR " : " AND ");
}
return(builder.ToString());
}
public String GetTableAlias()
{
var sqlFromClause = Clauses.First(o => o.Name == "FROM");
var startIndex = sqlFromClause.InputText.LastIndexOf(" AS ");
return(startIndex > 0 ? sqlFromClause.InputText.Substring(startIndex + 4) : "");
}
protected TSpecification Clause(Expression <Func <TEntity, bool> > clauseData)
{
Clauses.Add(new ClauseData(CurrentOperator,
new DirectClause <TEntity>(clauseData)));
CurrentOperator = Operator.And;
return(this as TSpecification);
}
public OrderBySqlClause(Expression[] columns, bool descending)
{
Clauses.AddRange(
new RawSqlClause("ORDER BY"),
SqlClause.Join(columns.Select(c => new SqlExpression(c)), new RawSqlClause(",")),
new RawSqlClause(descending ? "DESC" : "ASC")
);
}
/// <summary>
/// Returns a SQL-string that represents the current object.
/// </summary>
/// <returns></returns>
public override string ToSql()
{
var sql = string.Format("DELETE {0}FROM {1} ", GetParameters(), Context[0]);
Clauses.ForEach(clause => sql += clause.ToSql());
return(sql);
}
/// <summary>
/// Create a new selector from DOM elements.
/// </summary>
///
/// <param name="elements">
/// A sequence of elements.
/// </param>
public Selector(IEnumerable <IDomObject> elements)
{
SelectorClause sel = new SelectorClause();
sel.SelectorType = SelectorType.Elements;
sel.SelectElements = elements;
Clauses.Add(sel);
}
private void AddClause(Clause clause)
{
learnedClauses.Add(clause);
Clauses.Add(clause);
foreach (var literal in clause)
{
ClauseReferences[literal.index].Add(clause);
}
}
public ConfigurableSwitchSortClauseBuilder <TRequest, TEntity, TValue> ThenBy(string entityProperty)
{
var clauseBuilder = ConfigurableSwitchSortClauseBuilder <TRequest, TEntity, TValue>
.From(this, entityProperty);
Clauses.Add(clauseBuilder);
return(clauseBuilder);
}
/// <summary>
/// Ends the current active clause, and creates a new one.
/// </summary>
public void CreateNewClause()
{
// Create new Group
if (CurrentClause.Expressions.Count > 0)
{
CurrentClause = new WhereClause <SelectWhereStatement>();
Clauses.Add(CurrentClause);
}
}
/// <summary>
/// SELECT column_name(s)
/// FROM table_name
/// ORDER BY column_name[ASC|DESC]
/// </summary>
/// <param name="customization"></param>
/// <returns></returns>
public OrderByClause OrderBy(Action <Target, Context> customization)
{
var clause = new OrderByClause(this);
Clauses.Add(clause);
clause.Target.Customize(customization);
return(clause);
}
/// <summary>
/// Create a new selector from a single element.
/// </summary>
///
/// <param name="element">
/// The element to test.
/// </param>
public Selector(IDomObject element)
{
SelectorClause sel = new SelectorClause();
sel.SelectorType = SelectorType.Elements;
sel.SelectElements = new List <IDomObject>();
((List <IDomObject>)sel.SelectElements).Add(element);
Clauses.Add(sel);
}
public ConfigurableSwitchSortClauseBuilder <TRequest, TEntity, TValue> ThenBy <TProp>(
Expression <Func <TEntity, TProp> > entityProperty)
{
var clauseBuilder = ConfigurableSwitchSortClauseBuilder <TRequest, TEntity, TValue>
.From(this, entityProperty);
Clauses.Add(clauseBuilder);
return(clauseBuilder);
}
public int MaxClauseOrder()
{
int max = 0;
if (Clauses.Count() > 0)
{
max = Clauses.Max(qc => qc.ClauseOrder);
}
return(max);
}
public void AddValues(object values)
{
var properties = values.GetType().GetProperties();
Clauses.AddRange(new RawSqlClause("("), new ListSqlClause(properties.Select(p => new RawSqlClause(p.Name))), new RawSqlClause(")"));
Clauses.Add(new RawSqlClause("VALUES"));
Clauses.AddRange(new RawSqlClause("("), new ListSqlClause(properties.Select(p => new ParameterSqlClause(p.GetValue(values)))), new RawSqlClause(")"));
}
public ActionResult DeleteConfirm(int id)
{
Clauses clauses = session.Query <Clauses>().Where(c => c.Id == id).FirstOrDefault();
session.Delete(clauses);
session.FlushAsync();
return(PartialView("~/Views/Clauses/ClausesShow.cshtml"));
}
public LimitMsSqlClause(int limit, int offset)
{
Clauses.AddRange(
new RawSqlClause("OFFSET"),
new RawSqlClause(offset.ToString()),
new RawSqlClause("ROWS"),
new RawSqlClause("FETCH NEXT"),
new RawSqlClause(limit.ToString()),
new RawSqlClause("ROWS ONLY"));
}
internal void SelectColumns(IEnumerable <string> columns)
{
var tableAlias = GetTableAlias();
var sqlClause = Clauses.FirstOrDefault();
if (sqlClause.Name == "SELECT")
{
sqlClause.InputText = string.Join(",", columns.Select(c => string.Format("{0}.{1}", tableAlias, c)));
}
}
public SqlSelectQuery <T> OrderBy(Expression <Func <T, object> > columns)
{
if (columns == null)
{
return(this);
}
var clause = new SqlOrderByClause(columns);
Clauses.Add(clause);
return(this);
}
public SqlSelectQuery <T> Where(Expression <Func <T, bool> > predicate)
{
if (predicate == null)
{
return(this);
}
var clause = new SqlWhereClause(predicate);
Clauses.Add(clause);
return(this);
}
public ActionResult CreateConfirm([Bind(Include = "Id,Name,Text")] Clauses clauses, string idParent)
{
if (ModelState.IsValid)
{
clauses.Cataloges = session.Query <Cataloges>().Where(c => c.Id == int.Parse(idParent)).FirstOrDefault();
session.Save(clauses);
}
return(PartialView("~/Views/Clauses/ClausesShow.cshtml"));
}
/// <summary>
/// Adds the clause is active between dates.
/// </summary>
/// <param name="logicOperator">The logic operator.</param>
/// <param name="startColumn">The start column.</param>
/// <param name="endColumn">The end column.</param>
/// <param name="date">The date.</param>
public void AddClauseIsActiveBetweenDates(LogicOperator logicOperator, string startColumn, string endColumn, DateTime date)
{
var whereClause = new WhereClause(logicOperator, startColumn, Comparison.Equals, null);
whereClause.AddClause(Enums.LogicOperator.Or, startColumn, Comparison.LessOrEquals, date.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture));
Clauses.Add(whereClause);
whereClause = new WhereClause(logicOperator, endColumn, Comparison.Equals, null);
whereClause.AddClause(Enums.LogicOperator.Or, endColumn, Comparison.GreaterOrEquals, date.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture));
Clauses.Add(whereClause);
}
private SqlString AppendWhere(string commandText, Clauses clauses, SegmentPositions segmentPositions)
{
if (segmentPositions.WhereIndex > -1
&& (clauses & Clauses.Where) == Clauses.Where)
{
// Remove the WHERE keyword and the subsequent space
var startIndex = segmentPositions.WhereIndex + 6;
var length = commandText.Length - startIndex;
if (segmentPositions.GroupByIndex > -1)
{
// Remove the space before the GROUP BY keyword
length = segmentPositions.GroupByIndex - startIndex;
}
else if (segmentPositions.OrderByIndex > -1)
{
// Remove the space before the ORDER BY keyword
length = segmentPositions.OrderByIndex - startIndex;
}
var segment = commandText.Substring(startIndex, length).Trim();
this.Where = segment;
}
return this;
}
private SqlString AppendSelect(string commandText, Clauses clauses, SegmentPositions segmentPositions)
{
if (segmentPositions.FromIndex > -1
&& (clauses & Clauses.Select) == Clauses.Select)
{
// Remove the SELECT keyword and the subsequent space
var startIndex = 7;
// Remove the space before the FROM keyword
var length = segmentPositions.FromIndex - startIndex;
var segment = commandText.Substring(startIndex, length).Trim();
this.Select = segment;
}
return this;
}
private SqlString AppendOrderBy(string commandText, Clauses clauses, SegmentPositions segmentPositions)
{
if (segmentPositions.OrderByIndex > -1
&& (clauses & Clauses.OrderBy) == Clauses.OrderBy)
{
// Remove the ORDER BY keyword and the subsequent space
var startIndex = segmentPositions.OrderByIndex + 10;
var length = commandText.Length - startIndex;
var segment = commandText.Substring(startIndex, length).Trim();
this.OrderBy = segment;
}
return this;
}
/// <summary>
/// Parses the specified command text into a SqlString instance populating the specified <see cref="Clauses"/> if
/// present in the command text.
/// </summary>
/// <param name="commandText">The SQL command text.</param>
/// <param name="clauses">The clauses to include in the SqlString.</param>
/// <returns>An SqlString instance populating the specified <see cref="Clauses"/> if present in the command text.</returns>
public static SqlString Parse(string commandText, Clauses clauses)
{
if (string.IsNullOrEmpty(commandText))
{
return new SqlString();
}
if (commandText.Contains(Environment.NewLine))
{
commandText = commandText.Replace(Environment.NewLine, " ");
}
var segmentPositions = SegmentPositions.GetSegmentPositions(commandText);
var sqlString = new SqlString();
sqlString.AppendSelect(commandText, clauses, segmentPositions)
.AppendFrom(commandText, clauses, segmentPositions)
.AppendWhere(commandText, clauses, segmentPositions)
.AppendGroupBy(commandText, clauses, segmentPositions)
.AppendOrderBy(commandText, clauses, segmentPositions);
return sqlString;
}
