internal override SqlSource VisitJoin(SqlJoin join) {
if (join.JoinType == SqlJoinType.CrossApply) {
// Visit the left side as usual.
join.Left = this.VisitSource(join.Left);
// Visit the condition as usual.
join.Condition = this.VisitExpression(join.Condition);
// Visit the right, with the expressionSink set.
SelectScope s = expressionSink;
expressionSink = new SelectScope();
expressionSink.LeftProduction = SqlGatherProducedAliases.Gather(join.Left);
join.Right = this.VisitSource(join.Right);
// Were liftable expressions found?
SqlSource newSource = join;
foreach (List<SqlColumn> cols in expressionSink.Lifted) {
newSource = PushSourceDown(newSource, cols);
}
expressionSink = s;
return newSource;
}
return base.VisitJoin(join);
}
internal override SqlSource VisitJoin(SqlJoin join) {
// block where clauses from being lifted out of the cardinality-dependent
// side of an outer join.
Scope save = this.current;
try {
switch (join.JoinType) {
case SqlJoinType.Cross:
case SqlJoinType.CrossApply:
case SqlJoinType.Inner:
return base.VisitJoin(join);
case SqlJoinType.LeftOuter:
case SqlJoinType.OuterApply: {
join.Left = this.VisitSource(join.Left);
this.current = null;
join.Right = this.VisitSource(join.Right);
join.Condition = this.VisitExpression(join.Condition);
return join;
}
default:
this.current = null;
return base.VisitJoin(join);
}
}
finally {
this.current = save;
}
}
internal override SqlExpression VisitMultiset(SqlSubSelect sms) {
// allow one big-join per query?
if ((this.options & Options.EnableBigJoin) != 0 &&
!this.hasBigJoin && this.canJoin && this.isTopLevel && this.outerSelect != null
&& !MultisetChecker.HasMultiset(sms.Select.Selection)
&& BigJoinChecker.CanBigJoin(sms.Select)) {
sms.Select = this.VisitSelect(sms.Select);
SqlAlias alias = new SqlAlias(sms.Select);
SqlJoin join = new SqlJoin(SqlJoinType.OuterApply, this.outerSelect.From, alias, null, sms.SourceExpression);
this.outerSelect.From = join;
this.outerSelect.OrderingType = SqlOrderingType.Always;
// make joined expression
SqlExpression expr = (SqlExpression)SqlDuplicator.Copy(sms.Select.Selection);
// make count expression
SqlSelect copySelect = (SqlSelect)SqlDuplicator.Copy(sms.Select);
SqlAlias copyAlias = new SqlAlias(copySelect);
SqlSelect countSelect = new SqlSelect(sql.Unary(SqlNodeType.Count, null, sms.SourceExpression), copyAlias, sms.SourceExpression);
countSelect.OrderingType = SqlOrderingType.Never;
SqlExpression count = sql.SubSelect(SqlNodeType.ScalarSubSelect, countSelect);
// make joined collection
SqlJoinedCollection jc = new SqlJoinedCollection(sms.ClrType, sms.SqlType, expr, count, sms.SourceExpression);
this.hasBigJoin = true;
return jc;
}
else {
return QueryExtractor.Extract(sms, this.parentParameters);
}
}
internal override SqlSource VisitJoin(SqlJoin join) {
this.Visit(join.Left);
List<SqlOrderExpression> leftOrders = this.orders;
this.orders = null;
this.Visit(join.Right);
this.PrependOrderExpressions(leftOrders);
return join;
}
public void ExpressionType_ReturnsJoin()
{
var table = new SqlTable("dbo.Users", "u");
var predicate = SqlExpression.Equal("u.Id", 5);
var expression = new SqlJoin(SqlJoinType.Left, table);
Assert.Equal(SqlExpressionType.Join, expression.ExpressionType);
}
public void Ctor_WithTableAndPredicate_SetsPropertyValues()
{
var table = new SqlTable("dbo.Users", "u");
var predicate = SqlExpression.Equal("u.Id", 5);
var expression = new SqlJoin(SqlJoinType.Left, table, predicate);
Assert.NotNull(expression.Table);
Assert.Same(table, expression.Table);
Assert.NotNull(expression.On);
Assert.NotNull(expression.On.Predicate);
Assert.Same(predicate, expression.On.Predicate);
Assert.Equal(SqlJoinType.Left, expression.Type);
}
internal override SqlSource VisitJoin(SqlJoin join) {
if (join.JoinType == SqlJoinType.CrossApply) {
// Look down the left side to see what table aliases are produced.
HashSet<SqlAlias> p = SqlGatherProducedAliases.Gather(join.Left);
// Look down the right side to see what table aliases are consumed.
HashSet<SqlAlias> c = SqlGatherConsumedAliases.Gather(join.Right);
// Look at each consumed alias and see if they are mentioned in produced.
if (p.Overlaps(c)) {
Annotations.Add(join, new SqlServerCompatibilityAnnotation(Strings.SourceExpressionAnnotation(join.SourceExpression), SqlProvider.ProviderMode.Sql2000));
// Can't reduce because this consumed alias is produced on the left.
return base.VisitJoin(join);
}
// Can turn this into a CROSS JOIN
join.JoinType = SqlJoinType.Cross;
return VisitJoin(join);
}
return base.VisitJoin(join);
}
internal override SqlSource VisitJoin(SqlJoin join)
{
base.VisitJoin(@join);
switch (@join.JoinType)
{
case SqlJoinType.Cross:
case SqlJoinType.Inner:
// reducing either side would effect cardinality of results
break;
case SqlJoinType.LeftOuter:
case SqlJoinType.CrossApply:
case SqlJoinType.OuterApply:
// may reduce to left if no references to the right
if (this.HasEmptySource(@join.Right))
{
SqlAlias a = (SqlAlias)@join.Right;
_removedMap[a] = a;
return(@join.Left);
}
break;
}
return(@join);
}
internal override SqlSource VisitJoin(SqlJoin join)
{
switch (join.JoinType)
{
case SqlJoinType.CrossApply:
case SqlJoinType.OuterApply:
{
this.Visit(join.Left);
if (this.found == null)
{
this.Visit(join.Right);
}
break;
}
default:
{
this.Visit(join.Left);
this.Visit(join.Right);
break;
}
}
return(join);
}
protected virtual SqlExpression VisitJoin(SqlJoin expr)
{
return(expr);
}
static SqlSelectBuilder <TRet, TRet, object> InternalOnMap <T1, T2, TRet>(this IBaseLeftRightJoinOnAble <T1, T2> items, Expression <Func <T1, T2, TRet> > map, Expression <Func <TRet, bool> > on)
{
var it = new SqlJoin(items.Left.Clause.From, items.Right, map, on, items.Type, items.Lateral);
return(new SqlSelectBuilder <TRet, TRet, object>(SelectClause.InitFromItem <TRet>(it)));
}
/// <summary>
/// Visits the specified <see cref="SqlJoin"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlJoin expression)
{
}
internal override SqlSource VisitSource(SqlSource source)
{
source = base.VisitSource(source);
SqlJoin join = source as SqlJoin;
if (join != null)
{
if (join.JoinType == SqlJoinType.OuterApply)
{
// Reduce outer-apply into left-outer-join
HashSet <SqlAlias> leftProducedAliases = SqlGatherProducedAliases.Gather(join.Left);
HashSet <SqlExpression> liftedExpressions = new HashSet <SqlExpression>();
if (SqlPredicateLifter.CanLift(join.Right, leftProducedAliases, liftedExpressions) &&
SqlSelectionLifter.CanLift(join.Right, leftProducedAliases, liftedExpressions) &&
!SqlAliasDependencyChecker.IsDependent(join.Right, leftProducedAliases, liftedExpressions))
{
SqlExpression liftedPredicate = SqlPredicateLifter.Lift(join.Right, leftProducedAliases);
List <List <SqlColumn> > liftedSelections = SqlSelectionLifter.Lift(join.Right, leftProducedAliases, liftedExpressions);
join.JoinType = SqlJoinType.LeftOuter;
join.Condition = liftedPredicate;
if (liftedSelections != null)
{
foreach (List <SqlColumn> selection in liftedSelections)
{
source = this.PushSourceDown(source, selection);
}
}
}
else
{
#warning [FB] REFACTOR: SQL SERVER SPECIFIC: MOVE TO A CTOR PROVIDED SET OF INCOMPATIBLE MODES.
this.AnnotateSqlIncompatibility(join, SqlServerProviderMode.Sql2000);
}
}
else if (join.JoinType == SqlJoinType.CrossApply)
{
// reduce cross apply with special nested left-outer-join's into a single left-outer-join
//
// SELECT x.*, y.*
// FROM X
// CROSS APPLY (
// SELECT y.*
// FROM (
// SELECT ?
// )
// LEFT OUTER JOIN (
// SELECT y.* FROM Y
// ) AS y
//
// ==>
//
// SELECT x.*, y.*
// FROM X
// LEFT OUTER JOIN (
// SELECT y.* FROM Y
// )
SqlJoin leftOuter = this.GetLeftOuterWithUnreferencedSingletonOnLeft(join.Right);
if (leftOuter != null)
{
HashSet <SqlAlias> leftProducedAliases = SqlGatherProducedAliases.Gather(join.Left);
HashSet <SqlExpression> liftedExpressions = new HashSet <SqlExpression>();
if (SqlPredicateLifter.CanLift(leftOuter.Right, leftProducedAliases, liftedExpressions) &&
SqlSelectionLifter.CanLift(leftOuter.Right, leftProducedAliases, liftedExpressions) &&
!SqlAliasDependencyChecker.IsDependent(leftOuter.Right, leftProducedAliases, liftedExpressions)
)
{
SqlExpression liftedPredicate = SqlPredicateLifter.Lift(leftOuter.Right, leftProducedAliases);
List <List <SqlColumn> > liftedSelections = SqlSelectionLifter.Lift(leftOuter.Right, leftProducedAliases, liftedExpressions);
// add intermediate selections
this.GetSelectionsBeforeJoin(join.Right, liftedSelections);
// push down all selections
foreach (List <SqlColumn> selection in liftedSelections.Where(s => s.Count > 0))
{
source = this.PushSourceDown(source, selection);
}
join.JoinType = SqlJoinType.LeftOuter;
join.Condition = this.factory.AndAccumulate(leftOuter.Condition, liftedPredicate);
join.Right = leftOuter.Right;
}
else
{
#warning [FB] REFACTOR: SQL SERVER SPECIFIC: MOVE TO A CTOR PROVIDED SET OF INCOMPATIBLE MODES.
this.AnnotateSqlIncompatibility(join, SqlServerProviderMode.Sql2000);
}
}
}
// re-balance join tree of left-outer-joins to expose LOJ w/ leftside unreferenced
while (join.JoinType == SqlJoinType.LeftOuter)
{
// look for buried left-outer-joined-with-unreferenced singleton
SqlJoin leftLeftOuter = this.GetLeftOuterWithUnreferencedSingletonOnLeft(join.Left);
if (leftLeftOuter == null)
{
break;
}
List <List <SqlColumn> > liftedSelections = new List <List <SqlColumn> >();
// add intermediate selections
this.GetSelectionsBeforeJoin(join.Left, liftedSelections);
// push down all selections
foreach (List <SqlColumn> selection in liftedSelections)
{
source = this.PushSourceDown(source, selection);
}
// bubble this one up on-top of this 'join'.
SqlSource jRight = join.Right;
SqlExpression jCondition = join.Condition;
join.Left = leftLeftOuter.Left;
join.Right = leftLeftOuter;
join.Condition = leftLeftOuter.Condition;
leftLeftOuter.Left = leftLeftOuter.Right;
leftLeftOuter.Right = jRight;
leftLeftOuter.Condition = jCondition;
}
}
return(source);
}
internal override SqlSource VisitJoin(SqlJoin join) {
SqlSource left = this.VisitSource(join.Left);
SqlSource right = this.VisitSource(join.Right);
SqlExpression cond = (SqlExpression)this.Visit(join.Condition);
return new SqlJoin(join.JoinType, left, right, cond, join.SourceExpression);
}
private SqlSelect VisitDefaultIfEmpty(Expression sequence) {
SqlSelect select = this.VisitSequence(sequence);
SqlAlias alias = new SqlAlias(select);
SqlAliasRef aliasRef = new SqlAliasRef(alias);
SqlExpression opt = new SqlOptionalValue(
new SqlColumn(
"test",
sql.Unary(SqlNodeType.OuterJoinedValue,
sql.Value(typeof(int?), this.typeProvider.From(typeof(int)), 1, false, this.dominatingExpression)
)
),
sql.Unary(SqlNodeType.OuterJoinedValue, aliasRef)
);
SqlSelect optSelect = new SqlSelect(opt, alias, this.dominatingExpression);
alias = new SqlAlias(optSelect);
aliasRef = new SqlAliasRef(alias);
SqlExpression litNull = sql.TypedLiteralNull(typeof(string), this.dominatingExpression);
SqlSelect selNull = new SqlSelect(litNull, null, this.dominatingExpression);
SqlAlias aliasNull = new SqlAlias(selNull);
SqlJoin join = new SqlJoin(SqlJoinType.OuterApply, aliasNull, alias, null, this.dominatingExpression);
return new SqlSelect(aliasRef, join, this.dominatingExpression);
}
private SqlSelect VisitSelectMany(Expression sequence, LambdaExpression colSelector, LambdaExpression resultSelector) {
SqlSelect seqSelect = this.VisitSequence(sequence);
SqlAlias seqAlias = new SqlAlias(seqSelect);
SqlAliasRef seqRef = new SqlAliasRef(seqAlias);
this.map[colSelector.Parameters[0]] = seqRef;
SqlNode colSelectorNode = this.VisitSequence(colSelector.Body);
SqlAlias selAlias = new SqlAlias(colSelectorNode);
SqlAliasRef selRef = new SqlAliasRef(selAlias);
SqlJoin join = new SqlJoin(SqlJoinType.CrossApply, seqAlias, selAlias, null, this.dominatingExpression);
SqlExpression projection = selRef;
if (resultSelector != null) {
this.map[resultSelector.Parameters[0]] = seqRef;
this.map[resultSelector.Parameters[1]] = selRef;
projection = this.VisitExpression(resultSelector.Body);
}
return new SqlSelect(projection, join, this.dominatingExpression);
}
private SqlQuery Join(SqlJoin join, ISqlInstruction instruction, Exp on)
{
joins.Add(new JoinInfo(join, instruction, on));
return this;
}
internal override SqlSource VisitJoin(SqlJoin join) {
switch (join.JoinType) {
case SqlJoinType.CrossApply:
case SqlJoinType.OuterApply: {
this.Visit(join.Left);
if (this.found == null) {
this.Visit(join.Right);
}
break;
}
default: {
this.Visit(join.Left);
this.Visit(join.Right);
break;
}
}
return join;
}
internal override SqlSource VisitJoin(SqlJoin join) {
Scope save = this.CurrentScope;
switch (join.JoinType) {
case SqlJoinType.CrossApply:
case SqlJoinType.OuterApply: {
this.Visit(join.Left);
Scope tmp = new Scope(join.Left, this.CurrentScope.ContainingScope);
this.CurrentScope = new Scope(null, tmp);
this.Visit(join.Right);
Scope tmp2 = new Scope(join.Right, tmp);
this.CurrentScope = new Scope(null, tmp2);
this.Visit(join.Condition);
break;
}
default: {
this.Visit(join.Left);
this.Visit(join.Right);
this.CurrentScope = new Scope(null, new Scope(join.Right, new Scope(join.Left, this.CurrentScope.ContainingScope)));
this.Visit(join.Condition);
break;
}
}
this.CurrentScope = save;
return join;
}
/// <summary>
/// parse an sql query text
/// </summary>
/// <param name="query">sql query text</param>
/// <param name="validator">sql query validator against a database</param>
/// <returns></returns>
public static DbQuery Parse(string query, IQueryValidation validator)
{
if (validator == null)
{
throw new ArgumentException("No query database validator provided");
}
int top = -1;
ColumnsSelect columnSelect = null;
var tableCollection = new List <Table>();
SqlJoin join = null;
ExpressionOperator where = null;
TokenItem PreviousToken = default(TokenItem);
TokenItem CurrentToken = default(TokenItem);
var peekedToken = default(TokenItem);
var columnCollection = new List <Column>();
Table joinTable = null;
var queue = new Queue <TokenItem>(ParseTokens(query));
//#if DEBUG
// Console.WriteLine("Query tokens:");
// Console.WriteLine($" {String.Join($"{Environment.NewLine} ", queue)}{Environment.NewLine}");
//#endif
bool EndOfStream = queue.Count == 0;
IEnumerable <Table> AllTables()
{
foreach (var t in tableCollection)
{
yield return(t);
}
if (joinTable != null)
{
yield return(joinTable);
}
}
bool GetToken()
{
if (EndOfStream || (EndOfStream = !queue.TryDequeue(out TokenItem tk)))
{
return(false);
}
//save previous
PreviousToken = CurrentToken;
//get current
CurrentToken = tk;
return(true);
}
bool PeekToken()
{
if (!EndOfStream && queue.TryPeek(out peekedToken))
{
return(true);
}
return(false);
}
bool GetTokenIf(TokenType token)
{
if (!EndOfStream && queue.TryPeek(out peekedToken) && peekedToken.Token == token)
{
CurrentToken = queue.Dequeue();
return(true);
}
return(false);
};
bool GetTokenIfContains(List <TokenType> tokens)
{
if (!EndOfStream && queue.TryPeek(out peekedToken) && tokens.Contains(peekedToken.Token))
{
CurrentToken = queue.Dequeue();
return(true);
}
return(false);
}
Column ReadColumnName(bool readAS = true)
{
Column selectColumn = null;
//read operand [(i).](column)
if (GetTokenIf(TokenType.Identifier))
{
var tableAlias = CurrentToken.Value;
String columnName = null;
String columnAlias = null;
if (GetTokenIf(TokenType.Dot))
{
if (!GetToken())
{
throw new ArgumentException($"column name expected");
}
columnName = CurrentToken.Value;
}
else
{
columnName = tableAlias;
tableAlias = null;
}
//alias
if (readAS && GetTokenIf(TokenType.AS))
{
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException($"Column alias name expected after AS: {CurrentToken.Value}");
}
columnAlias = CurrentToken.Value;
}
selectColumn = new Column(columnName, tableAlias, columnAlias);
}
return(selectColumn);
}
ExpressionOperand ReadOperand(DbColumnType casting)
{
if (!PeekToken())
{
return(null);
}
switch (peekedToken.Token)
{
case TokenType.String:
GetToken();
return(new StringOperand(CurrentToken.Value));
case TokenType.Number:
GetToken();
return(new NumberOperand(CurrentToken.Value, casting));
case TokenType.Identifier:
//get column name
GetToken();
var columnName = CurrentToken.Value;
var position = CurrentToken.Position;
String columnIdentifier = null;
if (GetTokenIf(TokenType.Dot))
{
//tablealias.colummname
if (!GetToken())
{
throw new ArgumentException($"column name expected");
}
columnIdentifier = columnName;
columnName = CurrentToken.Value;
}
Column col = null;
//try to find column in known tables
if (columnIdentifier != null)
{
//find in table by its alias
var aliasTable = AllTables().FirstOrDefault(t => t.Alias == columnIdentifier);
if (aliasTable == null)
{
throw new ArgumentException($"cannot find column: {columnName}");
}
col = new Column(columnName, aliasTable.Alias);
col.Meta = validator.ColumnMetadata(aliasTable.Name, col.Name);
}
else
{
//find the only first in all known tables
var tables = AllTables().Where(t => validator.TableHasColumn(t.Name, columnName)).ToList();
if (tables.Count != 1)
{
throw new ArgumentException($"column: {columnName} could not be found in database or cannot resolve multiple tables");
}
//tableName = tables[0].Name;
col = new Column(columnName, validator.ColumnMetadata(tables[0].Name, columnName));
}
return(new ColumnOperand(col, casting));
default:
return(null);
}
}
ExpressionOperator ReadExpression()
{
//https://stackoverflow.com/questions/3422673/evaluating-a-math-expression-given-in-string-form
var stack = new Stack <ExpressionItem>();
var operStack = new Stack <TokenType>();
ExpressionItem BuildTree()
{
//get higher precedence operator from operator stack
var oper = operStack.Pop();
//get left and right expressions
var right = stack.Pop();
var left = stack.Pop();
//create new expression
var expr = ExpressionOperator.Create(left, oper, right);
//push onto expression stack
stack.Push(expr);
return(expr);
}
var end = false;
while (!end)
{
if (!(end = !PeekToken()))
{
var currOper = peekedToken.Token;
switch (currOper)
{
case TokenType.String:
case TokenType.Number:
case TokenType.Identifier:
//if on top of stack there's a casting oper, apply it
DbColumnType casting = DbColumnType.None;
if (stack.Count > 0 && stack.Peek().ExpressionType == ExpressionItemType.Casting)
{
casting = (stack.Pop() as CastingExpression).Type;
}
//read operand and apply casting if any
stack.Push(ReadOperand(casting));
break;
case TokenType.OpenPar:
//consume the open parenthesis (
GetToken();
//try ahead if it's a casting operator
var currToken = CurrentToken.Token;
//try to get ahead a casting type
if (PeekToken() && (casting = peekedToken.Token.ToCast()).IsCasting())
{
//consume the casting type
GetToken();
if (!GetTokenIf(TokenType.ClosePar))
{
throw new ArgumentException("close parenthesis expected after casting type");
}
//store new casting expression
stack.Push(new CastingExpression(casting));
}
else
{
operStack.Push(currToken);
}
break;
case TokenType.ClosePar: // )
GetToken();
while (operStack.Count > 0 && operStack.Peek() != TokenType.OpenPar)
{
BuildTree();
}
//discard close parenthesis ) operator
operStack.Pop();
break;
default:
//operator
// AND OR == <> > >= < <=
if (currOper == TokenType.AND || currOper == TokenType.OR ||
currOper == TokenType.Equal || currOper == TokenType.NotEqual ||
currOper == TokenType.Greater || currOper == TokenType.GreaterOrEqual ||
currOper == TokenType.Less || currOper == TokenType.LessOrEqual)
{
GetToken();
var thisOper = CurrentToken.Token;
//build tree of all operator on stack with higher precedence than current
while (operStack.Count > 0 &&
Precedence(operStack.Peek()) < Precedence(thisOper))
{
BuildTree();
}
//
operStack.Push(thisOper);
}
else
{
throw new ArgumentException($"operator expected, and we got: {currOper}");
}
break;
}
}
}
//resolve left operator
while (operStack.Count > 0)
{
BuildTree();
}
if (stack.Count == 0)
{
return(null);
}
var item = stack.Pop();
if (item.ExpressionType != ExpressionItemType.Operator)
{
throw new ArgumentException("operator expected on expression");
}
return(item as ExpressionOperator);
}
int Precedence(TokenType token)
{
switch (token)
{
//case TokenType.NOT: // ~ BITWISE NOT
// return 1;
case TokenType.Astherisk: // * / %(modulus)
return(2);
case TokenType.Plus: // + - sign
case TokenType.Minus: // + addition, concatenation -substraction
return(3); // bitwise & AND, | OR |
case TokenType.Equal: //comparison operators
case TokenType.NotEqual:
case TokenType.Greater:
case TokenType.GreaterOrEqual:
case TokenType.Less:
case TokenType.LessOrEqual:
return(4);
case TokenType.NOT:
return(5);
case TokenType.AND:
return(6);
case TokenType.BETWEEN: //ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
case TokenType.IN:
case TokenType.LIKE:
case TokenType.OR:
return(7);
case TokenType.Assign:
return(8);
case TokenType.OpenPar:
//highest
return(16);
default:
return(0);
}
}
#region SELECT
if (!GetTokenIf(TokenType.SELECT))
{
throw new ArgumentException("SELECT expected");
}
//see if it's a function
if (GetTokenIfContains(SelectFunctions))
{
var function = CurrentToken.Token;
if (!GetTokenIf(TokenType.OpenPar))
{
throw new ArgumentException($"expected ( after FUNCTION {CurrentToken.Token}");
}
// COUNT([(i).](column))
var functionColumn = ReadColumnName(readAS: false);
if (!GetTokenIf(TokenType.ClosePar))
{
throw new ArgumentException($"expected ) closing {CurrentToken.Token}");
}
//alias
String functionColumnAlias = null;
if (GetTokenIf(TokenType.AS))
{
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException($"");
}
functionColumnAlias = CurrentToken.Value;
}
columnSelect = new ColumnsSelect(function, functionColumn, functionColumnAlias);
}
else
{
//preceded by TOP if any
//TOP integer PERCENT
if (GetTokenIf(TokenType.TOP))
{
if (!GetTokenIf(TokenType.Number))
{
throw new ArgumentException($"Number expected after TOP");
}
//test for integer
if (!int.TryParse(CurrentToken.Value, out top) || top <= 0)
{
throw new ArgumentException($"TOP [positive integer greater than 0] expected: {CurrentToken.Value}");
}
//PERCENT
if (GetTokenIf(TokenType.PERCENT))
{
//save if for later
}
}
//read columns
//read column selector: comma separated or *
if (GetTokenIf(TokenType.Astherisk))
{
columnSelect = new ColumnsSelect(top);
}
else
{
//mut have at least one column identifier
if (PeekToken() && peekedToken.Token != TokenType.Identifier)
{
throw new ArgumentException("table column name(s) expected");
}
//read first
columnCollection.Add(ReadColumnName());
while (GetTokenIf(TokenType.Comma))
{
//next
columnCollection.Add(ReadColumnName());
}
columnSelect = new ColumnsSelect(top, columnCollection);
}
}
#endregion
#region FROM
if (!GetTokenIf(TokenType.FROM))
{
throw new ArgumentException("FROM expected");
}
do
{
//read identifier: table name
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException("table name identifier after FROM expected");
}
var tableName = CurrentToken.Value;
String tableAlias = null;
//var pos = CurrentToken.Position;
// [table name] AS [alias]
if (GetTokenIf(TokenType.AS))
{
//create it so WHERE can handle it
//tableIdentifier = new DbQueryTableIdentifier(table, CurrentToken.Value);
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException($"Table alias expected after AS");
}
tableAlias = CurrentToken.Value;
}
//tableIdentifier = new DbQueryTableIdentifier(table);
var table = new Table(tableName, tableAlias);
if (!validator.HasTable(table.Name))
{
throw new ArgumentException($"Invalid table name: {table.Name}");
}
tableCollection.Add(table);
}while (GetTokenIf(TokenType.Comma));
if (columnSelect.FullColumns)
{
//fill SELECT * with all columns in FROM if applies
foreach (var col in tableCollection
.SelectMany(
table => validator.ColumnsOf(table.Name),
(table, col) => new Column(col, validator.ColumnMetadata(table.Name, col))))
{
columnSelect.Add(col);
}
}
else
{
//check all SELECT columns
foreach (var col in columnSelect.Columns)
{
string tableName = null;
if (col.HasTableAlias)
{
//find it in table collection
var table = tableCollection.FirstOrDefault(t => t.HasAlias && col.TableAlias == t.Alias);
if (table == null)
{
throw new ArgumentException($"column alias undefined {col}");
}
tableName = table.Name;
if (!validator.TableHasColumn(tableName, col.Name))
{
throw new ArgumentException($"column: {col.Name} could not be found in table {tableName}");
}
}
else
{
//brute force search
var tables = tableCollection.Where(t => validator.TableHasColumn(t.Name, col.Name)).ToList();
if (tables.Count != 1)
{
throw new ArgumentException($"column: {col.Name} could not be found in database or cannot resolve multiple tables");
}
tableName = tables[0].Name;
}
//link column to table, and find its index
col.Meta = validator.ColumnMetadata(tableName, col.Name);
}
}
#endregion
#region JOIN
if (GetTokenIfContains(JoinStarts))
{
//FROM table must has identifier, should be only one here
TokenType JoinCommand = CurrentToken.Token;
ComparisonOperator JoinExpression = null;
if (CurrentToken.Token == TokenType.LEFT || CurrentToken.Token == TokenType.RIGHT ||
CurrentToken.Token == TokenType.FULL)
{
//LEFT OUTER JOIN
//RIGHT OUTER JOIN
//FULL OUTER JOIN
if (!GetTokenIf(TokenType.OUTER))
{
throw new ArgumentException($"OUTER expected after {CurrentToken.Token}");
}
}
else if (!(CurrentToken.Token == TokenType.INNER || CurrentToken.Token == TokenType.CROSS))
{
//INNER JOIN
//CROSS JOIN
throw new ArgumentException($"invalid JOIN keyword {CurrentToken.Token}");
}
if (!GetTokenIf(TokenType.JOIN))
{
throw new ArgumentException($"JOIN expected after {CurrentToken.Token}");
}
//read table name
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException("table name identifier after FROM expected");
}
//check -table name
var tableName = CurrentToken.Value;
String tableAlias = null;
// + identifier
if (GetTokenIf(TokenType.AS))
{
if (!GetTokenIf(TokenType.Identifier))
{
throw new ArgumentException($"Table alias expected after AS");
}
tableAlias = CurrentToken.Value;
}
joinTable = new Table(tableName, tableAlias);
if (!validator.HasTable(joinTable.Name))
{
throw new ArgumentException($"Invalid table name: {joinTable.Name}");
}
//read ON
if (!GetTokenIf(TokenType.ON))
{
throw new ArgumentException($"ON expected in JOIN query");
}
//read expression
//left & right operand must be from different tables
//read left
var left = ReadOperand(DbColumnType.None);
//read operator
if (!GetToken() || !CurrentToken.Token.IsOperator())
{
throw new ArgumentException("operator of JOIN expression expected");
}
var oper = CurrentToken;
//read right
var right = ReadOperand(DbColumnType.None);
//operands must be of type column with different table identifiers
if (!left.IsColumn ||
!right.IsColumn ||
((ColumnOperand)left).Column.TableAlias == ((ColumnOperand)right).Column.TableAlias)
{
throw new ArgumentException("JOIN query expression table identifiers cannot be the same");
}
JoinExpression = new ComparisonOperator(
left,
oper.Token,
right
);
join = new SqlJoin(JoinCommand, joinTable, JoinExpression);
//validate JOIN column tables
}
#endregion
#region WHERE if any
if (GetTokenIf(TokenType.WHERE))
{
if ((where = ReadExpression()) == null)
{
throw new ArgumentException("WHERE expression(s) expected");
}
}
#endregion
if (GetToken() && CurrentToken.Token != TokenType.SemiColon)
{
throw new ArgumentException($"Unexpected: {CurrentToken.Value} @ {CurrentToken.Position}");
}
return(new DbQuery(columnSelect, tableCollection, join, new ColumnsWhere(where)));
}
internal override SqlSource VisitJoin(SqlJoin join) {
base.VisitJoin(join);
switch (join.JoinType) {
case SqlJoinType.Cross:
case SqlJoinType.Inner:
// reducing either side would effect cardinality of results
break;
case SqlJoinType.LeftOuter:
case SqlJoinType.CrossApply:
case SqlJoinType.OuterApply:
// may reduce to left if no references to the right
if (this.HasEmptySource(join.Right)) {
SqlAlias a = (SqlAlias)join.Right;
this.removedMap[a] = a;
return join.Left;
}
break;
}
return join;
}
internal override SqlSource VisitJoin(SqlJoin join) {
base.VisitJoin(join);
if (join.Condition != null) {
this.CheckJoinCondition(join.Condition);
}
return join;
}
protected override SqlExpression VisitJoin(SqlJoin join)
{
Visit(join.Left);
NewLine();
switch (join.JoinType)
{
case SqlJoinType.Cross:
_builder.Append("CROSS JOIN ");
break;
case SqlJoinType.Inner:
_builder.Append("INNER JOIN ");
break;
case SqlJoinType.LeftOuter:
_builder.Append("LEFT OUTER JOIN ");
break;
case SqlJoinType.RightOuter:
_builder.Append("RIGHT OUTER JOIN ");
break;
}
VisitJoinSource(join.Right);
if (join.Condition != null)
{
_builder.Append(" ON ");
Visit(join.Condition);
return join;
}
if (RequiresOnCondition(join.JoinType))
_builder.Append(" ON 1 = 1 ");
return join;
}
private SqlSelect VisitSelect(Expression sequence, LambdaExpression selector) {
SqlSelect source = this.VisitSequence(sequence);
SqlAlias alias = new SqlAlias(source);
SqlAliasRef aref = new SqlAliasRef(alias);
this.map[selector.Parameters[0]] = aref;
SqlNode project = this.Visit(selector.Body);
SqlSelect pselect = project as SqlSelect;
if (pselect != null) {
return new SqlSelect(sql.SubSelect(SqlNodeType.Multiset, pselect, selector.Body.Type), alias, this.dominatingExpression);
}
else if ((project.NodeType == SqlNodeType.Element || project.NodeType == SqlNodeType.ScalarSubSelect) &&
(this.converterStrategy & ConverterStrategy.CanUseOuterApply) != 0) {
SqlSubSelect sub = (SqlSubSelect)project;
SqlSelect inner = sub.Select;
SqlAlias innerAlias = new SqlAlias(inner);
SqlAliasRef innerRef = new SqlAliasRef(innerAlias);
if (project.NodeType == SqlNodeType.Element) {
inner.Selection = new SqlOptionalValue(
new SqlColumn(
"test",
sql.Unary(
SqlNodeType.OuterJoinedValue,
sql.Value(typeof(int?), this.typeProvider.From(typeof(int)), 1, false, this.dominatingExpression)
)
),
sql.Unary(SqlNodeType.OuterJoinedValue, inner.Selection)
);
}
else {
inner.Selection = sql.Unary(SqlNodeType.OuterJoinedValue, inner.Selection);
}
SqlJoin join = new SqlJoin(SqlJoinType.OuterApply, alias, innerAlias, null, this.dominatingExpression);
return new SqlSelect(innerRef, join, this.dominatingExpression);
}
else {
SqlExpression expr = project as SqlExpression;
if (expr != null) {
return new SqlSelect(expr, alias, this.dominatingExpression);
}
else {
throw Error.BadProjectionInSelect();
}
}
}
public JoinInfo(SqlJoin joinType, ISqlInstruction with, Exp on)
{
With = with;
JoinType = joinType;
On = on;
}
private SqlSelect VisitJoin(Expression outerSequence, Expression innerSequence, LambdaExpression outerKeySelector, LambdaExpression innerKeySelector, LambdaExpression resultSelector) {
SqlSelect outerSelect = this.VisitSequence(outerSequence);
SqlSelect innerSelect = this.VisitSequence(innerSequence);
SqlAlias outerAlias = new SqlAlias(outerSelect);
SqlAliasRef outerRef = new SqlAliasRef(outerAlias);
SqlAlias innerAlias = new SqlAlias(innerSelect);
SqlAliasRef innerRef = new SqlAliasRef(innerAlias);
this.map[outerKeySelector.Parameters[0]] = outerRef;
SqlExpression outerKey = this.VisitExpression(outerKeySelector.Body);
this.map[innerKeySelector.Parameters[0]] = innerRef;
SqlExpression innerKey = this.VisitExpression(innerKeySelector.Body);
this.map[resultSelector.Parameters[0]] = outerRef;
this.map[resultSelector.Parameters[1]] = innerRef;
SqlExpression result = this.VisitExpression(resultSelector.Body);
SqlExpression condition = sql.Binary(SqlNodeType.EQ, outerKey, innerKey);
SqlSelect select = null;
if ((this.converterStrategy & ConverterStrategy.CanUseJoinOn) != 0) {
SqlJoin join = new SqlJoin(SqlJoinType.Inner, outerAlias, innerAlias, condition, this.dominatingExpression);
select = new SqlSelect(result, join, this.dominatingExpression);
} else {
SqlJoin join = new SqlJoin(SqlJoinType.Cross, outerAlias, innerAlias, null, this.dominatingExpression);
select = new SqlSelect(result, join, this.dominatingExpression);
select.Where = condition;
}
return select;
}
internal virtual SqlSource VisitJoin(SqlJoin join) {
join.Left = this.VisitSource(join.Left);
join.Right = this.VisitSource(join.Right);
join.Condition = this.VisitExpression(join.Condition);
return join;
}
protected virtual SqlExpression VisitJoin(SqlJoin expr)
{
return expr;
}
internal virtual SqlSource VisitJoin(SqlJoin join) {
join.Left = this.VisitSource(join.Left);
join.Right = this.VisitSource(join.Right);
join.Condition = this.VisitExpression(join.Condition);
return join;
}
public JoinInfo(SqlJoin joinType, ISqlInstruction with, Exp on)
{
With = with;
JoinType = joinType;
On = on;
}
internal override SqlSource VisitJoin(SqlJoin join) {
this.ReferenceColumns(join.Condition);
return base.VisitJoin(join);
}
public SqlQuery Join(SqlQueryBase query, object LocalKey, object OtherKey)
{
var join = new SqlJoin { JoinQuery = query, ForeignKey = OtherKey, LocalKey = LocalKey, Parent = this };
Joins.Add(join);
return this;
}
internal override SqlSource VisitJoin(SqlJoin join) {
if (join.JoinType == SqlJoinType.CrossApply ||
join.JoinType == SqlJoinType.OuterApply) {
join.Left = this.VisitSource(join.Left);
SqlSelect saveSelect = this.currentSelect;
try {
this.currentSelect = this.GetSourceSelect(join.Left);
join.Right = this.VisitSource(join.Right);
this.currentSelect = null;
join.Condition = this.VisitExpression(join.Condition);
return join;
}
finally {
this.currentSelect = saveSelect;
}
}
else {
return base.VisitJoin(join);
}
}
internal override SqlSource VisitJoin(SqlJoin join) {
join.Left = this.VisitSource(join.Left);
join.Right = this.VisitSource(join.Right);
join.Condition = this.VisitPredicate(join.Condition);
return join;
}
/// <summary>
/// Fixed:
/// </summary>
public static Dictionary <string, ControlData> Destinations(
Context context,
SiteSettings ss,
long referenceId,
Dictionary <string, ControlData> addressBook,
string searchRange,
string searchText = "")
{
var joinDepts = new SqlJoin(
"\"Depts\"",
SqlJoin.JoinTypes.LeftOuter,
"\"Users\".\"DeptId\"=\"Depts\".\"DeptId\"");
var joinMailAddresses = new SqlJoin(
"\"MailAddresses\"",
SqlJoin.JoinTypes.Inner,
"\"Users\".\"UserId\"=\"MailAddresses\".\"OwnerId\"");
switch (searchRange)
{
case "DefaultAddressBook":
return(searchText == string.Empty
? addressBook
: addressBook
.Where(o => o.Value.Text.IndexOf(searchText,
System.Globalization.CompareOptions.IgnoreCase |
System.Globalization.CompareOptions.IgnoreKanaType |
System.Globalization.CompareOptions.IgnoreWidth) != -1)
.ToDictionary(o => o.Key, o => new ControlData(o.Value.Text)));
case "SiteUser":
return(DestinationCollection(
context: context,
join: Sqls.SqlJoinCollection(
joinDepts,
joinMailAddresses),
where : Rds.UsersWhere()
.SiteUserWhere(
context: context,
siteId: referenceId)
.MailAddresses_OwnerType("Users")
.SearchText(
context: context,
searchText: searchText)
.Users_TenantId(context.TenantId)));
case "All":
default:
return(!searchText.IsNullOrEmpty()
? DestinationCollection(
context: context,
join: Sqls.SqlJoinCollection(
joinDepts,
joinMailAddresses),
where : Rds.UsersWhere()
.MailAddresses_OwnerType("Users")
.SearchText(
context: context,
searchText: searchText)
.Users_TenantId(context.TenantId))
: new Dictionary <string, ControlData>());
}
}
private SqlUpdate Join(SqlJoin join, ISqlInstruction instruction, Exp on)
{
joins.Add(new JoinInfo(join, instruction, on));
return(this);
}
internal override SqlSource VisitJoin(SqlJoin join)
{
this.ReferenceColumns(@join.Condition);
return(base.VisitJoin(@join));
}
internal override SqlSource VisitJoin(SqlJoin join) {
this.Visit(join.Left);
this.NewLine();
switch (join.JoinType) {
case SqlJoinType.CrossApply:
sb.Append("CROSS APPLY ");
break;
case SqlJoinType.Cross:
sb.Append("CROSS JOIN ");
break;
case SqlJoinType.Inner:
sb.Append("INNER JOIN ");
break;
case SqlJoinType.LeftOuter:
sb.Append("LEFT OUTER JOIN ");
break;
case SqlJoinType.OuterApply:
sb.Append("OUTER APPLY ");
break;
}
SqlJoin rightJoin = join.Right as SqlJoin;
if (rightJoin == null ||
(rightJoin.JoinType == SqlJoinType.Cross
&& join.JoinType != SqlJoinType.CrossApply
&& join.JoinType != SqlJoinType.OuterApply)) {
this.Visit(join.Right);
} else {
this.VisitJoinSource(join.Right);
}
if (join.Condition != null) {
sb.Append(" ON ");
this.Visit(join.Condition);
} else if (this.RequiresOnCondition(join.JoinType)) {
sb.Append(" ON 1=1 ");
}
return join;
}
/// <summary>
/// Visits the specified <see cref="SqlJoin"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlJoin expression)
{
}
