Esempio n. 1
0
        private void VisitCorrelationStatement(CorrelationStatement statement, string functionName)
        {
            SelectStatement statement1 = (SelectStatement)statement.First;
            SelectStatement statement2 = (SelectStatement)statement.Second;

            if (((statement1.From.Count != 1) && (!(statement1.From[0].Source is Name))) ||
                ((statement2.From.Count != 1) && (!(statement2.From[0].Source is Name))))
            {
                throw new NotImplementedException();
            }
            else if ((statement1.Output.Count != statement2.Output.Count) && (statement1.Output.Count == 0))
            {
                throw new NotImplementedException();
            }

            string table1 = ((Name)(statement1.From[0].Source)).LastPart;
            string table2 = ((Name)(statement2.From[0].Source)).LastPart;

            ExpressionToken        whereExpression = null;
            List <ExpressionToken> expressions1    = new List <ExpressionToken>();
            List <ExpressionToken> expressions2    = new List <ExpressionToken>();

            for (int i = 0; i < statement1.Output.Count; i++)
            {
                if ((!(statement1.Output[i] is Name)) && (!(statement2.Output[i] is Name)))
                {
                    throw new NotImplementedException();
                }
                expressions1.Add(Sql.Name(table1, ((Name)(statement1.Output[i])).LastPart));
                expressions2.Add(Sql.Name(table2, ((Name)(statement2.Output[i])).LastPart));
                if (whereExpression == null)
                {
                    whereExpression = expressions1[i].IsEqual(expressions2[i]);
                }
                else
                {
                    whereExpression = whereExpression.And(expressions1[i].IsEqual(expressions2[i]));
                }
            }
            if (statement2.Where != null)
            {
                whereExpression = whereExpression.And(AddPrefixToExpressionToken(((ExpressionToken)statement2.Where), table2));
            }

            ExpressionToken tempExpression = Sql.Function(functionName, Sql.Select.From(table2).Where(whereExpression));

            if (statement1.Where != null)
            {
                tempExpression = tempExpression.And(AddPrefixToExpressionToken(((ExpressionToken)statement1.Where), table1));
            }

            SelectStatement correlationStatement =
                Sql.Select.Output(expressions1).From(table1)
                .Where(tempExpression);

            VisitStatement(correlationStatement);
        }
        private void VisitCorrelationStatement(CorrelationStatement statement, string functionName)
        {
            SelectStatement statement1 = (SelectStatement)statement.First;
            SelectStatement statement2 = (SelectStatement)statement.Second;

            if (((statement1.From.Count != 1) && (!(statement1.From[0].Source is Name))) ||
                ((statement2.From.Count != 1) && (!(statement2.From[0].Source is Name))))
            {
                throw new NotImplementedException();
            }
            else if ((statement1.Output.Count != statement2.Output.Count) && (statement1.Output.Count == 0))
            {
                throw new NotImplementedException();
            }

            string table1          = ((Name)(statement1.From[0].Source)).LastPart;
            string table2          = ((Name)(statement2.From[0].Source)).LastPart;
            var    table1Name      = ((Name)(statement1.From[0].Source));
            var    table2Name      = ((Name)(statement2.From[0].Source));
            var    statement1Alias = statement1.Alias;

            //here we have 2 possible cases
            //case 1) table1Name equals table2Name
            // In this case "EXIST" statement will not work properly.To fix that,  first statement, including it's own "WHERE" expression
            //should be placed inside subquery with alias
            //case 2) table1Name not equal table2Name
            // in this case it is not nessesary to use subquery for first statement. Just place  WHERE clause to EXIST part
            var isSameTableMode = string.Equals($"{table1Name.FirstPart}.{table1Name.LastPart}"
                                                , $"{table2Name.FirstPart}.{table2Name.LastPart}"
                                                , StringComparison.OrdinalIgnoreCase);

            ExpressionToken        whereExpression = null;
            List <ExpressionToken> expressions1    = new List <ExpressionToken>();
            List <ExpressionToken> expressions2    = new List <ExpressionToken>();

            for (int i = 0; i < statement1.Output.Count; i++)
            {
                if ((!(statement1.Output[i] is Name)) && (!(statement2.Output[i] is Name)))
                {
                    throw new NotImplementedException();
                }

                Name name1 = null;

                if (isSameTableMode)
                {
                    // use alias of first statements since we will use subquery for it
                    name1 = Sql.Name(statement1Alias, ((Name)(statement1.Output[i])).Alias).As(((Name)(statement1.Output[i])).Alias);
                }
                else
                {
                    //use table name or alias directly
                    if (!string.IsNullOrWhiteSpace(table1Name.Alias))
                    {
                        name1 = Sql.Name(table1Name.Alias, ((Name)(statement1.Output[i])).LastPart).As(((Name)(statement1.Output[i])).Alias);
                    }
                    else
                    {
                        name1 = Sql.Name(table1Name.FirstPart, table1Name.LastPart, ((Name)(statement1.Output[i])).LastPart).As(((Name)(statement1.Output[i])).Alias);
                    }
                }

                expressions1.Add(name1);

                if (!string.IsNullOrWhiteSpace(table2Name.Alias))
                {
                    expressions2.Add(Sql.Name(table2Name.Alias, ((Name)(statement2.Output[i])).LastPart).As(((Name)(statement2.Output[i])).Alias));
                }
                else
                {
                    expressions2.Add(Sql.Name(table2Name.FirstPart, table2Name.LastPart, ((Name)(statement2.Output[i])).LastPart).As(((Name)(statement2.Output[i])).Alias));
                }

                if (whereExpression == null)
                {
                    whereExpression = expressions1[i].IsEqual(expressions2[i]);
                }
                else
                {
                    whereExpression = whereExpression.And(expressions1[i].IsEqual(expressions2[i]));
                }
            }

            if (statement2.Where != null)
            {
                whereExpression = whereExpression.And(AddPrefixToExpressionToken(((ExpressionToken)statement2.Where), table2Name.LastPart));
            }

            ExpressionToken tempExpression = Sql.Function(functionName, Sql.Select.From(table2Name).Where(whereExpression));

            if (statement1.Where != null && !isSameTableMode)
            {
                tempExpression = tempExpression.And(AddPrefixToExpressionToken(((ExpressionToken)statement1.Where), table1Name.LastPart));
            }

            SelectStatement correlationStatement = null;

            correlationStatement = isSameTableMode
                ? Sql.Select.Output(expressions1) //case 1
                                   .From(statement1)
                                   .Where(tempExpression)
                : Sql.Select.Output(expressions1) //case 2
                                   .From(table1Name)
                                   .Where(tempExpression);


            VisitStatement(correlationStatement);
        }