示例#1
0
        public static SqlExpression GenericPad(SqlFunctionCall node)
        {
            string paddingFunction;

            switch (node.FunctionType)
            {
            case SqlFunctionType.PadLeft:
                paddingFunction = "lpad";
                break;

            case SqlFunctionType.PadRight:
                paddingFunction = "rpad";
                break;

            default:
                throw new InvalidOperationException();
            }
            var operand = node.Arguments[0];
            var result  = SqlDml.Case();

            result.Add(
                SqlDml.CharLength(operand) < node.Arguments[1],
                SqlDml.FunctionCall(paddingFunction, node.Arguments));
            result.Else = operand;
            return(result);
        }
示例#2
0
        public void SqlCaseReplacingTest()
        {
            SqlCase c1 = SqlDml.Case();
            SqlCase c2 = SqlDml.Case(SqlDml.Literal(1));

            c2.Else = SqlDml.Null;
            c2[1]   = 2;
            c2[2]   = 3;
            c2[4]   = 5;
            c2[6]   = 7;
            c1.ReplaceWith(c2);

            bool passed = false;

            try {
                c1.ReplaceWith(1);
            }
            catch {
                passed = true;
            }

            Assert.IsTrue(passed);
            Assert.AreNotSame(c1, c2);
            //Possibly NUnit compares items of IEnumirable
            //and if they the same it sugests two objects are equals
            //Assert.AreNotEqual(c1, c2);
            Assert.AreEqual(c1.NodeType, c2.NodeType);
            Assert.AreEqual(c1.Value, c2.Value);
            Assert.AreEqual(c1.Else, c2.Else);
            Assert.AreEqual(c1.Count, c2.Count);
            foreach (KeyValuePair <SqlExpression, SqlExpression> p in c1)
            {
                Assert.AreEqual(p.Value, c2[p.Key]);
            }
        }
        public void Test012()
        {
            string nativeSql = @"SELECT 
                                  CASE p.staff_id
                                  WHEN 1 THEN 'STAFF_1'
                                  WHEN 2 THEN 'STAFF_2'
                                  ELSE 'STAFF_OTHER'
                                  END AS Staff,
                                  SUM(p.amount) AS Total
                                FROM
                                  payment p
                                GROUP BY
                                  p.staff_id";

            SqlTableRef payment = SqlDml.TableRef(schema.Tables["payment"], "p");

            SqlSelect select       = SqlDml.Select(payment);
            SqlCase   totalPayment = SqlDml.Case(payment["staff_id"]);

            totalPayment[1]   = SqlDml.Literal("STAFF_1");
            totalPayment[2]   = SqlDml.Literal("STAFF_2");
            totalPayment.Else = SqlDml.Literal("STAFF_OTHER");
            select.Columns.Add(totalPayment, "Staff");

            select.Columns.Add(SqlDml.Sum(payment["amount"]), "Total");
            select.GroupBy.AddRange(payment["staff_id"]);

            Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
        }
示例#4
0
        public void Test012()
        {
            string nativeSql = @"SELECT 
                                  CASE il.TrackId
                                  WHEN 1 THEN 'STAFF_1'
                                  WHEN 2 THEN 'STAFF_2'
                                  ELSE 'STAFF_OTHER'
                                  END AS shippers,
                                  SUM(il.UnitPrice) AS TotalUnits
                           FROM [invoiceline] il
                           GROUP BY il.TrackId";

            SqlTableRef invoiceLine = SqlDml.TableRef(schema.Tables["invoiceline"], "il");

            SqlSelect select       = SqlDml.Select(invoiceLine);
            SqlCase   totalPayment = SqlDml.Case(invoiceLine["TrackId"]);

            totalPayment[1]   = SqlDml.Literal("STAFF_1");
            totalPayment[2]   = SqlDml.Literal("STAFF_2");
            totalPayment.Else = SqlDml.Literal("STAFF_OTHER");
            select.Columns.Add(totalPayment, "shippers");

            select.Columns.Add(SqlDml.Sum(invoiceLine["UnitPrice"]), "TotalUnits");
            select.GroupBy.AddRange(invoiceLine["TrackId"]);

            Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
        }
示例#5
0
        private SqlExpression GenericPad(SqlFunctionCall node)
        {
            var operand           = node.Arguments[0];
            var actualLength      = SqlDml.CharLength(operand);
            var requiredLength    = node.Arguments[1];
            var paddingExpression = node.Arguments.Count > 2
        ? SqlDml.FunctionCall("REPLICATE", node.Arguments[2], requiredLength - actualLength)
        : SqlDml.FunctionCall("SPACE", requiredLength - actualLength);
            SqlExpression resultExpression;

            switch (node.FunctionType)
            {
            case SqlFunctionType.PadLeft:
                resultExpression = paddingExpression + operand;
                break;

            case SqlFunctionType.PadRight:
                resultExpression = operand + paddingExpression;
                break;

            default:
                throw new InvalidOperationException();
            }
            var result = SqlDml.Case();

            result.Add(actualLength < requiredLength, resultExpression);
            result.Else = operand;
            return(result);
        }
        private static SqlExpression Max(SqlExpression left, SqlExpression right)
        {
            var result = SqlDml.Case();

            result.Add(left > right, left);
            result.Else = right;
            return(result);
        }
示例#7
0
        /// <summary>
        /// Performs "rounding as tought in school" on the specified argument.
        /// </summary>
        /// <param name="value">The value to round.</param>
        /// <returns>Result of rounding.</returns>
        public static SqlExpression RegularRound(SqlExpression value)
        {
            var result = SqlDml.Case();

            result.Add(value > 0, SqlDml.Truncate(value + 0.5));
            result.Else = SqlDml.Truncate(value - 0.5);
            return(result);
        }
示例#8
0
        /// <summary>
        /// Converts the specified interval expression to expression
        /// that represents absolute value (duration) of the specified interval.
        /// This is a generic implementation that uses comparison with zero interval.
        /// It's suitable for any server, but can be inefficient.
        /// </summary>
        /// <param name="source">The source.</param>
        /// <returns>Result of conversion.</returns>
        public static SqlExpression IntervalAbs(SqlExpression source)
        {
            var result = SqlDml.Case();

            result.Add(source >= SqlDml.Literal(new TimeSpan(0)), source);
            result.Else = SqlDml.IntervalNegate(source);
            return(result);
        }
示例#9
0
        /// <summary>
        /// Performs banker's rounding on the specified argument.
        /// </summary>
        /// <param name="value">The value to round.</param>
        /// <returns>Result of rounding.</returns>
        public static SqlExpression BankersRound(SqlExpression value)
        {
            var mainPart  = 2 * SqlDml.Floor((value + 0.5) / 2);
            var extraPart = SqlDml.Case();

            extraPart.Add(value - mainPart > 0.5, 1);
            extraPart.Else = 0;
            return(mainPart + extraPart);
        }
示例#10
0
        public static SqlExpression StringCompare(
            [Type(typeof(string))] SqlExpression strA,
            [Type(typeof(string))] SqlExpression strB)
        {
            var result = SqlDml.Case();

            result.Add(strA > strB, SqlDml.Literal(1));
            result.Add(strA < strB, SqlDml.Literal(-1));
            result.Else = SqlDml.Literal(0);
            return(result);
        }
示例#11
0
        public static SqlExpression DateTimeDaysInMonth(
            [Type(typeof(int))] SqlExpression year,
            [Type(typeof(int))] SqlExpression month)
        {
            var februaryCase = SqlDml.Case();

            februaryCase.Add(DateTimeIsLeapYear(year), 29);
            februaryCase.Else = 28;

            var result = SqlDml.Case();

            result.Add(SqlDml.In(month, SqlDml.Array(1, 3, 5, 7, 8, 10, 12)), 31);
            result.Add(month == 2, februaryCase);
            result.Else = 30;

            return(result);
        }
示例#12
0
        public void SqlCaseCloneTest()
        {
            SqlCase c = SqlDml.Case();

            c.Value = SqlDml.Literal(1);
            c[SqlDml.Literal(1)] = SqlDml.Literal("A");
            c[SqlDml.Literal(2)] = SqlDml.Literal("B");
            c.Else = SqlDml.Literal("C");

            SqlCase cClone = (SqlCase)c.Clone();

            Assert.AreNotEqual(c, cClone);
            Assert.AreNotEqual(c.Value, cClone.Value);
            Assert.AreNotEqual(c.Else, cClone.Else);
            Assert.AreEqual(c.NodeType, cClone.NodeType);
            Assert.AreEqual(c.Value.NodeType, cClone.Value.NodeType);
            Assert.AreEqual(c.Else.NodeType, cClone.Else.NodeType);
            Assert.AreEqual(c.Count, cClone.Count);
        }
示例#13
0
        public SqlExpression BooleanToInt(SqlExpression expression)
        {
            // optimization: omitting BooleanToInt(IntToBoolean(x)) sequences
            if (expression.NodeType == SqlNodeType.NotEquals)
            {
                var binary = (SqlBinary)expression;
                var left   = binary.Left;
                var right  = binary.Right as SqlLiteral <int>;
                if (!ReferenceEquals(right, null) && right.Value == 0)
                {
                    return(left);
                }
            }

            var result = SqlDml.Case();

            result.Add(expression, 1);
            result.Else = 0;
            return(SqlDml.Cast(result, booleanType));
        }
示例#14
0
        private SqlSelect BuildTypedQuery(IndexInfo index)
        {
            var underlyingIndex = index.UnderlyingIndexes[0];
            var baseQuery       = BuildProviderQuery(underlyingIndex);
            var query           = SqlDml.Select(baseQuery.From);

            query.Where = baseQuery.Where;

            var baseColumns       = baseQuery.Columns.ToList();
            var typeIdColumnIndex = index.Columns
                                    .Select((c, i) => new { c, i })
                                    .Single(p => p.c.Field.IsTypeId).i;
            var type         = index.ReflectedType;
            var typeIdColumn = SqlDml.ColumnRef(
                SqlDml.Column(SqlDml.Literal(TypeIdRegistry[type])),
                WellKnown.TypeIdFieldName);
            var discriminatorMap = type.Hierarchy.TypeDiscriminatorMap;

            if (discriminatorMap != null)
            {
                var discriminatorColumnIndex = underlyingIndex.Columns.IndexOf(discriminatorMap.Column);
                var discriminatorColumn      = baseQuery.From.Columns[discriminatorColumnIndex];
                var sqlCase = SqlDml.Case(discriminatorColumn);
                foreach (var pair in discriminatorMap)
                {
                    var discriminatorValue = GetDiscriminatorValue(discriminatorMap, pair.First);
                    var typeId             = TypeIdRegistry[pair.Second];
                    sqlCase.Add(SqlDml.Literal(discriminatorValue), SqlDml.Literal(typeId));
                }
                if (discriminatorMap.Default != null)
                {
                    sqlCase.Else = SqlDml.Literal(TypeIdRegistry[discriminatorMap.Default]);
                }
                typeIdColumn = SqlDml.ColumnRef(
                    SqlDml.Column(sqlCase),
                    WellKnown.TypeIdFieldName);
            }
            baseColumns.Insert(typeIdColumnIndex, typeIdColumn);
            query.Columns.AddRange(baseColumns);
            return(query);
        }
        protected override SqlExpression VisitConditional(ConditionalExpression expression)
        {
            var          check     = Visit(expression.Test);
            var          ifTrue    = Visit(expression.IfTrue);
            var          ifFalse   = Visit(expression.IfFalse);
            SqlContainer container = ifTrue as SqlContainer;

            if (container != null)
            {
                ifTrue = TryUnwrapEnum(container);
            }
            container = ifFalse as SqlContainer;
            if (container != null)
            {
                ifFalse = TryUnwrapEnum(container);
            }
            var boolCheck = fixBooleanExpressions
        ? booleanExpressionConverter.BooleanToInt(check)
        : check;
            var varCheck = boolCheck as SqlVariant;

            if (!varCheck.IsNullReference())
            {
                return(SqlDml.Variant(varCheck.Id, ifFalse, ifTrue));
            }
            if (fixBooleanExpressions && IsBooleanExpression(expression))
            {
                var c = SqlDml.Case();
                c[check] = booleanExpressionConverter.BooleanToInt(ifTrue);
                c.Else   = booleanExpressionConverter.BooleanToInt(ifFalse);
                return(booleanExpressionConverter.IntToBoolean(c));
            }
            else
            {
                var c = SqlDml.Case();
                c[check] = ifTrue;
                c.Else   = ifFalse;
                return(c);
            }
        }
        private SqlExpression VisitCast(UnaryExpression cast, SqlExpression operand)
        {
            var sourceType = cast.Operand.Type.StripNullable();
            var targetType = cast.Type.StripNullable();

            if (sourceType == targetType || targetType == typeof(object) || sourceType == typeof(object))
            {
                return(operand);
            }
            if (IsEnumUnderlyingType(sourceType, targetType) || IsEnumUnderlyingType(targetType, sourceType))
            {
                return(operand);
            }
            // Special case for boolean cast
            if (fixBooleanExpressions && IsBooleanExpression(cast.Operand))
            {
                var result = SqlDml.Case();
                result.Add(operand, 1);
                result.Else = 0;
                operand     = result;
            }
            return(SqlDml.Cast(operand, driver.MapValueType(targetType)));
        }
示例#17
0
        public override string Translate(SqlCompilerContext context, SqlMatch node, MatchSection section)
        {
            switch (section)
            {
            case MatchSection.Entry:
                //MATCH is not supported by PostgreSQL, we need some workaround
                SqlRow row = node.Value as SqlRow;
                if (row != null)
                {
                    SqlSelect finalQuery = SqlDml.Select();
                    finalQuery.Columns.Add(5);
                    switch (node.MatchType)
                    {
                        #region SIMPLE

                    case SqlMatchType.None: {
                        bool    existsNull = false;
                        SqlCase c          = SqlDml.Case();
                        {
                            bool subQueryNeeded = true;
                            //if any of the row elements is NULL then true
                            if (row.Count > 0)
                            {
                                bool          allLiteral = true; //if true then there is no NULL element
                                SqlExpression when1      = null;
                                for (int i = 0; i < row.Count; i++)
                                {
                                    bool elementIsNotLiteral = row[i].NodeType != SqlNodeType.Literal;
                                    //if the row element is the NULL value
                                    if (row[i].NodeType == SqlNodeType.Null)
                                    {
                                        existsNull = true;
                                        break;
                                    }
                                    if (allLiteral && elementIsNotLiteral)
                                    {
                                        allLiteral = false;
                                    }
                                    if (elementIsNotLiteral)
                                    {
                                        if (when1 == null)
                                        {
                                            when1 = SqlDml.IsNull(row[i]);
                                        }
                                        else
                                        {
                                            when1 = when1 || SqlDml.IsNull(row[i]);
                                        }
                                    }
                                }
                                if (existsNull)
                                {
                                    //Some row element is the NULL value, MATCH result is true
                                    subQueryNeeded = false;
                                }
                                else if (allLiteral)
                                {
                                    //No row element is the NULL value
                                    subQueryNeeded = true;
                                }
                                else //(!whenNotNeeded)
                                {
                                    //Check if any row element is NULL
                                    c.Add(when1 == null ? true : when1, true);
                                    subQueryNeeded = true;
                                }
                            }
                            //find row in subquery
                            if (subQueryNeeded)
                            {
                                SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                                SqlSelect   q1            = SqlDml.Select(originalQuery);
                                q1.Columns.Add(1);
                                {
                                    SqlTableColumnCollection columns = originalQuery.Columns;
                                    SqlExpression where = null;
                                    for (int i = 0; i < columns.Count; i++)
                                    {
                                        if (i == 0)
                                        {
                                            where = columns[i] == row[i];
                                        }
                                        else
                                        {
                                            where = where && columns[i] == row[i];
                                        }
                                        if (node.Unique)
                                        {
                                            q1.GroupBy.Add(columns[i]);
                                        }
                                    }
                                    q1.Where = where;
                                    if (node.Unique)
                                    {
                                        q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                                    }
                                }
                                //c.Add(Sql.Exists(q1), true);
                                c.Else = SqlDml.Exists(q1);
                            }
                        }
                        if (c.Else == null)
                        {
                            c.Else = false;
                        }
                        if (existsNull)
                        {
                            finalQuery.Where = null;
                        }
                        else if (c.Count > 0)
                        {
                            finalQuery.Where = c;
                        }
                        else
                        {
                            finalQuery.Where = c.Else;
                        }
                        break;
                    }

                        #endregion

                        #region FULL

                    case SqlMatchType.Full: {
                        SqlCase c1 = SqlDml.Case();
                        {
                            bool          noMoreWhenNeeded = false;
                            bool          allNull          = true;
                            SqlExpression when1            = true;
                            //if all row elements are null then true
                            if (row.Count > 0)
                            {
                                bool whenNotNeeded = false;
                                for (int i = 0; i < row.Count; i++)
                                {
                                    //if any row element is surely not the NULL value
                                    if (row[i].NodeType == SqlNodeType.Literal)
                                    {
                                        whenNotNeeded = true;
                                        break;
                                    }
                                    if (allNull && row[i].NodeType != SqlNodeType.Null)
                                    {
                                        allNull = false;
                                    }
                                    if (i == 0)
                                    {
                                        when1 = SqlDml.IsNull(row[i]);
                                    }
                                    else
                                    {
                                        when1 = when1 && SqlDml.IsNull(row[i]);
                                    }
                                }
                                if (allNull)
                                {
                                    when1 = true;
                                }
                                if (!whenNotNeeded)
                                {
                                    c1.Add(when1, true);
                                }
                            }
                            if (!noMoreWhenNeeded)
                            {
                                bool          whenNotNeeded = false;
                                bool          allLiteral    = true;
                                SqlExpression when2         = true;
                                //if no row elements are null then subcase
                                for (int i = 0; i < row.Count; i++)
                                {
                                    if (row[i].NodeType == SqlNodeType.Null)
                                    {
                                        whenNotNeeded = true;
                                        when2         = false;
                                        break;
                                    }
                                    if (allLiteral && row[i].NodeType != SqlNodeType.Literal)
                                    {
                                        allLiteral = false;
                                    }
                                    if (i == 0)
                                    {
                                        when2 = SqlDml.IsNotNull(row[i]);
                                    }
                                    else
                                    {
                                        when2 = when2 && SqlDml.IsNotNull(row[i]);
                                    }
                                }
                                if (allLiteral)
                                {
                                    when2 = true;
                                }
                                if (!whenNotNeeded)
                                {
                                    //find row in subquery
                                    SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                                    SqlSelect   q1            = SqlDml.Select(originalQuery);
                                    q1.Columns.Add(1);
                                    {
                                        SqlTableColumnCollection columns = originalQuery.Columns;
                                        SqlExpression where = null;
                                        for (int i = 0; i < columns.Count; i++)
                                        {
                                            if (i == 0)
                                            {
                                                where = columns[i] == row[i];
                                            }
                                            else
                                            {
                                                where = where && columns[i] == row[i];
                                            }
                                            if (node.Unique)
                                            {
                                                q1.GroupBy.Add(columns[i]);
                                            }
                                        }
                                        q1.Where = where;
                                        if (node.Unique)
                                        {
                                            q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                                        }
                                    }
                                    c1.Add(when2, SqlDml.Exists(q1));
                                }
                            }
                            //else false
                            c1.Else = false;
                        }
                        if (c1.Count > 0)
                        {
                            finalQuery.Where = c1;
                        }
                        else
                        {
                            finalQuery.Where = false;
                        }
                        break;
                    }

                        #endregion

                        #region PARTIAL

                    case SqlMatchType.Partial: {
                        bool    allNull = true;
                        SqlCase c1      = SqlDml.Case();
                        {
                            SqlExpression when1 = true;
                            //if all row elements are null then true
                            if (row.Count > 0)
                            {
                                bool whenNotNeeded = false;
                                for (int i = 0; i < row.Count; i++)
                                {
                                    //if any row element is surely not the NULL value
                                    if (row[i].NodeType == SqlNodeType.Literal)
                                    {
                                        allNull       = false;
                                        whenNotNeeded = true;
                                        break;
                                    }
                                    if (allNull && row[i].NodeType != SqlNodeType.Null)
                                    {
                                        allNull = false;
                                    }
                                    if (i == 0)
                                    {
                                        when1 = SqlDml.IsNull(row[i]);
                                    }
                                    else
                                    {
                                        when1 = when1 && SqlDml.IsNull(row[i]);
                                    }
                                }
                                if (allNull)
                                {
                                    when1 = true;
                                }
                                if (!whenNotNeeded)
                                {
                                    c1.Add(when1, true);
                                }
                            }
                            //otherwise
                            if (!allNull)
                            {
                                //find row in subquery
                                SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                                SqlSelect   q1            = SqlDml.Select(originalQuery);
                                q1.Columns.Add(8);
                                {
                                    SqlTableColumnCollection columns = originalQuery.Columns;
                                    SqlExpression where = null;
                                    for (int i = 0; i < columns.Count; i++)
                                    {
                                        //if row[i] would be NULL then c3 would result in true,
                                        if (row[i].NodeType != SqlNodeType.Null)
                                        {
                                            SqlCase c3 = SqlDml.Case();
                                            c3.Add(SqlDml.IsNull(row[i]), true);
                                            c3.Else = row[i] == columns[i];

                                            if (where == null)
                                            {
                                                where = c3;
                                            }
                                            else
                                            {
                                                where = where && c3;
                                            }
                                        }
                                        if (node.Unique)
                                        {
                                            SqlCase c4 = SqlDml.Case();
                                            c4.Add(SqlDml.IsNull(row[i]), 0);
                                            c4.Else = columns[i];
                                            q1.GroupBy.Add(c4);
                                        }
                                    }
                                    q1.Where = where;
                                    if (node.Unique)
                                    {
                                        q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                                    }
                                }
                                c1.Else = SqlDml.Exists(q1);
                            }
                        }
                        if (c1.Else == null)
                        {
                            c1.Else = false;
                        }
                        if (allNull)
                        {
                            finalQuery.Where = null;
                        }
                        else if (c1.Count > 0)
                        {
                            finalQuery.Where = c1;
                        }
                        else
                        {
                            finalQuery.Where = c1.Else;
                        }
                    }
                    break;

                        #endregion
                    }
                    SqlMatch newNode = SqlDml.Match(SqlDml.Row(), SqlDml.SubQuery(finalQuery).Query, node.Unique, node.MatchType);
                    node.ReplaceWith(newNode);
                    return("EXISTS(SELECT '");
                }
                else
                {
                    throw new InvalidOperationException(Strings.ExSqlMatchValueMustBeAnSqlRowInstance);
                }

            case MatchSection.Specification:
                return("' WHERE EXISTS");

            case MatchSection.Exit:
                return(")");
            }
            return(string.Empty);
        }
示例#18
0
        private void BuildSelectForUniqueMatchNone(SqlMatch node, SqlRow row, SqlSelect finalQuery)
        {
            var existsNull     = false;
            var @case          = SqlDml.Case();
            var subQueryNeeded = true;

            //if any of the row elements is NULL then true
            if (row.Count > 0)
            {
                var           allLiteral = true; //if true then there is no NULL element
                SqlExpression when       = null;
                for (var i = 0; i < row.Count; i++)
                {
                    var elementIsNotLiteral = row[i].NodeType != SqlNodeType.Literal;
                    //if the row element is the NULL value
                    if (row[i].NodeType == SqlNodeType.Null)
                    {
                        existsNull = true;
                        break;
                    }
                    if (allLiteral && elementIsNotLiteral)
                    {
                        allLiteral = false;
                    }
                    if (elementIsNotLiteral)
                    {
                        when = when == null
              ? SqlDml.IsNull(row[i])
              : when || SqlDml.IsNull(row[i]);
                    }
                }
                if (existsNull)
                {
                    //Some row element is the NULL value, MATCH result is true
                    subQueryNeeded = false;
                }
                else if (allLiteral)
                {
                    //No row element is the NULL value
                    subQueryNeeded = true;
                }
                else //(!whenNotNeeded)
                //Check if any row element is NULL
                {
                    _ = @case.Add(when == null ? true : when, true);
                    subQueryNeeded = true;
                }
            }
            //find row in subquery
            if (subQueryNeeded)
            {
                var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                var subquery      = SqlDml.Select(originalQuery);
                subquery.Columns.Add(1);
                var columns = originalQuery.Columns;
                SqlExpression where = null;
                for (var i = 0; i < columns.Count; i++)
                {
                    if (i == 0)
                    {
                        where = columns[i] == row[i];
                    }
                    else
                    {
                        where = where && columns[i] == row[i];
                    }
                    if (node.Unique)
                    {
                        subquery.GroupBy.Add(columns[i]);
                    }
                }
                subquery.Where = where;
                if (node.Unique)
                {
                    subquery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                }
                //c.Add(Sql.Exists(q1), true);
                @case.Else = SqlDml.Exists(subquery);
            }
            if (@case.Else == null)
            {
                @case.Else = false;
            }
            if (existsNull)
            {
                finalQuery.Where = null;
            }
            else if (@case.Count > 0)
            {
                finalQuery.Where = @case;
            }
            else
            {
                finalQuery.Where = @case.Else;
            }
        }
示例#19
0
        private void BuildSelectForUniqueMatchFull(SqlMatch node, SqlRow row, SqlSelect finalQuery)
        {
            var           @case            = SqlDml.Case();
            bool          noMoreWhenNeeded = false;
            bool          allNull          = true;
            SqlExpression when1            = true;

            //if all row elements are null then true
            if (row.Count > 0)
            {
                var whenNotNeeded = false;
                for (var i = 0; i < row.Count; i++)
                {
                    //if any row element is surely not the NULL value
                    if (row[i].NodeType == SqlNodeType.Literal)
                    {
                        whenNotNeeded = true;
                        break;
                    }
                    if (allNull && row[i].NodeType != SqlNodeType.Null)
                    {
                        allNull = false;
                    }
                    if (i == 0)
                    {
                        when1 = SqlDml.IsNull(row[i]);
                    }
                    else
                    {
                        when1 = when1 && SqlDml.IsNull(row[i]);
                    }
                }
                if (allNull)
                {
                    when1 = true;
                }
                if (!whenNotNeeded)
                {
                    _ = @case.Add(when1, true);
                }
            }
            if (!noMoreWhenNeeded)
            {
                var           whenNotNeeded = false;
                var           allLiteral    = true;
                SqlExpression when2         = true;
                //if no row elements are null then subcase
                for (var i = 0; i < row.Count; i++)
                {
                    if (row[i].NodeType == SqlNodeType.Null)
                    {
                        whenNotNeeded = true;
                        when2         = false;
                        break;
                    }
                    if (allLiteral && row[i].NodeType != SqlNodeType.Literal)
                    {
                        allLiteral = false;
                    }
                    if (i == 0)
                    {
                        when2 = SqlDml.IsNotNull(row[i]);
                    }
                    else
                    {
                        when2 = when2 && SqlDml.IsNotNull(row[i]);
                    }
                }
                if (allLiteral)
                {
                    when2 = true;
                }
                if (!whenNotNeeded)
                {
                    //find row in subquery
                    var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                    var subQuery      = SqlDml.Select(originalQuery);
                    subQuery.Columns.Add(1);

                    var columns = originalQuery.Columns;
                    SqlExpression where = null;
                    for (int i = 0; i < columns.Count; i++)
                    {
                        if (i == 0)
                        {
                            where = columns[i] == row[i];
                        }
                        else
                        {
                            where = where && columns[i] == row[i];
                        }
                        if (node.Unique)
                        {
                            subQuery.GroupBy.Add(columns[i]);
                        }
                    }
                    subQuery.Where = where;
                    if (node.Unique)
                    {
                        subQuery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                    }
                    _ = @case.Add(when2, SqlDml.Exists(subQuery));
                }
            }
            //else false
            @case.Else       = false;
            finalQuery.Where = @case.Count > 0 ? @case : (SqlExpression)false;
        }
示例#20
0
        private void BuildSelectForUniqueMatchPartial(SqlMatch node, SqlRow row, SqlSelect finalQuery)
        {
            bool          allNull = true;
            var           @case   = SqlDml.Case();
            SqlExpression when    = true;

            //if all row elements are null then true
            if (row.Count > 0)
            {
                var whenNotNeeded = false;
                for (var i = 0; i < row.Count; i++)
                {
                    //if any row element is surely not the NULL value
                    if (row[i].NodeType == SqlNodeType.Literal)
                    {
                        allNull       = false;
                        whenNotNeeded = true;
                        break;
                    }
                    if (allNull && row[i].NodeType != SqlNodeType.Null)
                    {
                        allNull = false;
                    }
                    when = i == 0
            ? SqlDml.IsNull(row[i])
            : when && SqlDml.IsNull(row[i]);
                }
                if (allNull)
                {
                    when = true;
                }
                if (!whenNotNeeded)
                {
                    _ = @case.Add(when, true);
                }
            }
            //otherwise
            if (!allNull)
            {
                //find row in subquery
                var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                var subQuery      = SqlDml.Select(originalQuery);
                subQuery.Columns.Add(8);
                var columns = originalQuery.Columns;
                SqlExpression where = null;
                for (var i = 0; i < columns.Count; i++)
                {
                    //if row[i] would be NULL then c3 would result in true,
                    if (row[i].NodeType != SqlNodeType.Null)
                    {
                        SqlCase c3 = SqlDml.Case();
                        _       = c3.Add(SqlDml.IsNull(row[i]), true);
                        c3.Else = row[i] == columns[i];

                        where = where == null ? c3 : where && c3;
                    }
                    if (node.Unique)
                    {
                        var c4 = SqlDml.Case();
                        _       = c4.Add(SqlDml.IsNull(row[i]), 0);
                        c4.Else = columns[i];
                        subQuery.GroupBy.Add(c4);
                    }
                }
                subQuery.Where = where;
                if (node.Unique)
                {
                    subQuery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                }
                @case.Else = SqlDml.Exists(subQuery);
            }
            if (@case.Else == null)
            {
                @case.Else = false;
            }
            if (allNull)
            {
                finalQuery.Where = null;
            }
            else if (@case.Count > 0)
            {
                finalQuery.Where = @case;
            }
            else
            {
                finalQuery.Where = @case.Else;
            }
        }