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 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; }