private SqlExpression BuildVersionFilter(PersistRequestBuilderContext context, SqlTableRef filteredTable, List <PersistParameterBinding> currentBindings)
        {
            SqlExpression result = null;

            foreach (var column in context.Type.GetVersionColumns())
            {
                var fieldIndex = GetFieldIndex(context.Type, column);
                if (!context.Task.AvailableFields[fieldIndex])
                {
                    continue;
                }
                PersistParameterBinding binding;
                if (!context.VersionParameterBindings.TryGetValue(column, out binding))
                {
                    var typeMapping = driver.GetTypeMapping(column);
                    binding = new PersistParameterBinding(typeMapping, fieldIndex, ParameterTransmissionType.Regular, PersistParameterBindingType.VersionFilter);
                    context.VersionParameterBindings.Add(column, binding);
                }
                var filteredColumn = filteredTable[column.Name];
                if (filteredColumn.IsNullReference())
                {
                    continue;
                }
                var filterValue = binding.ParameterReference;
                // Handle decimal precision issue
                if (Type.GetTypeCode(column.ValueType) == TypeCode.Decimal)
                {
                    filterValue = SqlDml.Cast(filterValue, driver.MapValueType(column));
                }
                result &= SqlDml.Variant(binding, filteredColumn == filterValue, SqlDml.IsNull(filteredColumn));
                currentBindings.Add(binding);
            }
            return(result);
        }
Example #2
0
 private SqlExpression GetJoinExpression(SqlExpression leftExpression, SqlExpression rightExpression, JoinProvider provider, int index)
 {
     if (provider.EqualColumns.Length > index)
     {
         if (providerInfo.Supports(ProviderFeatures.DateTimeEmulation))
         {
             if (provider.EqualColumns[index].First.Type == typeof(DateTime))
             {
                 leftExpression = SqlDml.Cast(leftExpression, SqlType.DateTime);
             }
             if (provider.EqualColumns[index].Second.Type == typeof(DateTime))
             {
                 rightExpression = SqlDml.Cast(rightExpression, SqlType.DateTime);
             }
         }
         if (providerInfo.Supports(ProviderFeatures.DateTimeOffsetEmulation))
         {
             if (provider.EqualColumns[index].First.Type == typeof(DateTimeOffset))
             {
                 leftExpression = SqlDml.Cast(leftExpression, SqlType.DateTimeOffset);
             }
             if (provider.EqualColumns[index].Second.Type == typeof(DateTimeOffset))
             {
                 rightExpression = SqlDml.Cast(rightExpression, SqlType.DateTimeOffset);
             }
         }
     }
     return(leftExpression == rightExpression);
 }
        public void InsertAndSelectTest()
        {
            var schema = ExtractDefaultSchema();

            EnsureTableNotExists(schema, TableName);
            var table        = schema.CreateTable(TableName);
            var idColumnType = Driver.TypeMappings[typeof(int)].MapType();
            var idColumn     = table.CreateColumn(IdColumnName, idColumnType);

            table.CreatePrimaryKey("PK_" + TableName, idColumn);
            for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
            {
                var mapping = typeMappings[columnIndex];
                var column  = table.CreateColumn(GetColumnName(columnIndex), mapping.MapType());
                column.IsNullable = true;
            }
            ExecuteNonQuery(SqlDdl.Create(table));
            var tableRef = SqlDml.TableRef(table);

            using (var insertCommand = Connection.CreateCommand()) {
                var insertQuery = SqlDml.Insert(tableRef);
                var idParameter = insertCommand.CreateParameter();
                idParameter.DbType        = DbType.Int32;
                idParameter.ParameterName = IdParameterName;
                insertCommand.Parameters.Add(idParameter);
                insertQuery.Values.Add(tableRef[IdColumnName], SqlDml.ParameterRef(IdParameterName));
                var parameters = new List <DbParameter>();
                for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
                {
                    var           mapping             = typeMappings[columnIndex];
                    var           parameterName       = GetParameterName(columnIndex);
                    SqlExpression parameterExpression = SqlDml.ParameterRef(parameterName);
                    if (mapping.ParameterCastRequired)
                    {
                        parameterExpression = SqlDml.Cast(parameterExpression, mapping.MapType());
                    }
                    insertQuery.Values.Add(tableRef[GetColumnName(columnIndex)], parameterExpression);
                    var parameter = insertCommand.CreateParameter();
                    parameter.ParameterName = parameterName;
                    parameters.Add(parameter);
                    insertCommand.Parameters.Add(parameter);
                }
                var insertQueryText = Driver.Compile(insertQuery).GetCommandText();
                insertCommand.CommandText = insertQueryText;
                for (int rowIndex = 0; rowIndex < testValues[0].Length; rowIndex++)
                {
                    idParameter.Value = rowIndex;
                    for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
                    {
                        typeMappings[columnIndex].BindValue(parameters[columnIndex], testValues[columnIndex][rowIndex]);
                    }
                    insertCommand.ExecuteNonQuery();
                }
            }
            var resultQuery = SqlDml.Select(tableRef);

            resultQuery.Columns.Add(SqlDml.Asterisk);
            resultQuery.OrderBy.Add(tableRef[IdColumnName]);
            VerifyResults(Connection.CreateCommand(resultQuery));
        }
        protected override SqlProvider VisitFreeText(FreeTextProvider provider)
        {
            SqlFreeTextTable fromTable;

            QueryParameterBinding[] bindings;

            var stringTypeMapping = Driver.GetTypeMapping(StringType);
            var criteriaBinding   = new QueryParameterBinding(
                stringTypeMapping, provider.SearchCriteria.Invoke, QueryParameterBindingType.Regular);

            var index   = provider.PrimaryIndex.Resolve(Handlers.Domain.Model);
            var table   = Mapping[index.ReflectedType];
            var columns = provider.Header.Columns.Select(column => column.Name).ToList();

            if (provider.TopN == null)
            {
                fromTable = SqlDml.FreeTextTable(table, criteriaBinding.ParameterReference, columns);
                bindings  = new[] { criteriaBinding };
            }
            else
            {
                var intTypeMapping = Driver.GetTypeMapping(Int32Type);
                var topNBinding    = new QueryParameterBinding(intTypeMapping, context => provider.TopN.Invoke(context), QueryParameterBindingType.Regular);
                fromTable = SqlDml.FreeTextTable(table, criteriaBinding.ParameterReference, columns, topNBinding.ParameterReference);
                bindings  = new[] { criteriaBinding, topNBinding };
            }
            var fromTableRef = SqlDml.QueryRef(fromTable);
            var select       = SqlDml.Select(fromTableRef);

            select.Columns.Add(fromTableRef.Columns[0]);
            select.Columns.Add(SqlDml.Cast(fromTableRef.Columns[1], SqlType.Double), "RANK");

            return(CreateProvider(select, bindings, provider));
        }
Example #5
0
        protected override SqlProvider VisitFreeText(FreeTextProvider provider)
        {
            var rankColumnName    = provider.Header.Columns.Last().Name;
            var stringTypeMapping = Driver.GetTypeMapping(typeof(string));
            var binding           = new QueryParameterBinding(stringTypeMapping,
                                                              provider.SearchCriteria.Invoke, QueryParameterBindingType.Regular);

            SqlSelect select           = SqlDml.Select();
            var       realPrimaryIndex = provider.PrimaryIndex.Resolve(Handlers.Domain.Model);
            var       index            = realPrimaryIndex.ReflectedType.Indexes.PrimaryIndex;
            var       query            = BuildProviderQuery(index);
            var       table            = Mapping[realPrimaryIndex.ReflectedType];
            var       fromTable        = SqlDml.FreeTextTable(table, binding.ParameterReference, table.Columns.Select(column => column.Name).AddOne(rankColumnName).ToList());
            var       fromTableRef     = SqlDml.QueryRef(fromTable);

            foreach (var column in query.Columns)
            {
                select.Columns.Add(fromTableRef.Columns[column.Name] ?? column);
            }
            select.Columns.Add(SqlDml.Cast(fromTableRef.Columns[rankColumnName], SqlType.Double), rankColumnName);
            select.From = fromTableRef;
            if (provider.TopN != null)
            {
                select.Limit = provider.TopN.Invoke();
                select.OrderBy.Add(select.Columns[rankColumnName], false);
            }
            return(CreateProvider(select, binding, provider));
        }
        public void SelectConstantsTest()
        {
            var queries = new List <SqlSelect>();
            var command = Connection.CreateCommand();

            for (int rowIndex = 0; rowIndex < testValues[0].Length; rowIndex++)
            {
                var query = SqlDml.Select();
                queries.Add(query);
                query.Columns.Add(SqlDml.Literal(rowIndex), IdColumnName);
                for (int columnIndex = 0; columnIndex < testValues.Length; columnIndex++)
                {
                    var columnName      = GetColumnName(columnIndex);
                    var value           = testValues[columnIndex][rowIndex];
                    var mapping         = typeMappings[columnIndex];
                    var valueExpression = value == null
            ? (SqlExpression)SqlDml.Null
            : mapping.ParameterCastRequired
              ? (SqlExpression)SqlDml.Cast(SqlDml.Literal(value), mapping.MapType())
              : SqlDml.Literal(value);
                    query.Columns.Add(valueExpression, columnName);
                }
            }
            var unionQueryRef = SqlDml.QueryRef(queries.Cast <ISqlQueryExpression>().Aggregate(SqlDml.UnionAll));
            var resultQuery   = SqlDml.Select(unionQueryRef);

            resultQuery.Columns.Add(SqlDml.Asterisk);
            resultQuery.OrderBy.Add(unionQueryRef[IdColumnName]);
            command.CommandText = Driver.Compile(resultQuery).GetCommandText();
            VerifyResults(command, true);
        }
Example #7
0
 private static SqlExpression DateTimeOffsetToLocalDateTime(SqlExpression dateTimeOffset)
 {
     return(SqlDml.Cast(
                SqlDml.DateTimePlusInterval(
                    Switchoffset(dateTimeOffset, "+00:00"),
                    SqlDml.DateTimeMinusDateTime(SqlDml.Native("getdate()"), SqlDml.Native("getutcdate()"))),
                SqlType.DateTime));
 }
Example #8
0
        protected override SqlExpression ProcessAggregate(SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
        {
            var result = base.ProcessAggregate(source, sourceColumns, aggregateColumn);

            if (aggregateColumn.AggregateType == AggregateType.Sum || aggregateColumn.AggregateType == AggregateType.Avg)
            {
                result = SqlDml.Cast(result, Driver.MapValueType(aggregateColumn.Type));
            }
            return(result);
        }
        public void SqlCastCloneTest()
        {
            SqlCast c      = SqlDml.Cast(SqlDml.Literal(1), SqlType.Decimal, 6, 4);
            SqlCast cClone = (SqlCast)c.Clone();

            Assert.AreNotEqual(c, cClone);
            Assert.AreNotEqual(c.Operand, cClone.Operand);
            Assert.AreEqual(c.NodeType, cClone.NodeType);
            Assert.AreEqual(c.Operand.NodeType, cClone.Operand.NodeType);
            Assert.AreEqual(c.Type, cClone.Type);
        }
Example #10
0
 private static SqlExpression DateTimeOffsetToLocalDateTime(SqlExpression dateTimeOffset)
 {
     return(SqlDml.Cast(
                SqlDml.DateTimePlusInterval(
                    DateTimeOffsetToUtcDateTime(dateTimeOffset),
                    SqlDml.DateTimeMinusDateTime(
                        DateTimeOffsetToUtcDateTime(
                            SqlDml.Native("SYSTIMESTAMP")),
                        SqlDml.Native("SYSTIMESTAMP"))),
                SqlType.DateTime));
 }
Example #11
0
        internal static SqlExpression GenericIntervalConstruct(
            SqlExpression days,
            SqlExpression hours,
            SqlExpression minutes,
            SqlExpression seconds,
            SqlExpression milliseconds)
        {
            var m           = milliseconds + 1000L * (seconds + 60L * (minutes + 60L * (hours + 24L * days)));
            var nanoseconds = NanosecondsPerMillisecond * SqlDml.Cast(m, SqlType.Int64);

            return(SqlDml.IntervalConstruct(nanoseconds));
        }
Example #12
0
 private static SqlExpression DateTimeOffsetTruncate(SqlExpression dateTimeOffset)
 {
     return(SqlDml.Cast(
                DateAddMillisecond(
                    DateAddSecond(
                        DateAddMinute(
                            DateAddHour(dateTimeOffset,
                                        -SqlDml.Extract(SqlDateTimeOffsetPart.Hour, dateTimeOffset)),
                            -SqlDml.Extract(SqlDateTimeOffsetPart.Minute, dateTimeOffset)),
                        -SqlDml.Extract(SqlDateTimeOffsetPart.Second, dateTimeOffset)),
                    -SqlDml.Extract(SqlDateTimeOffsetPart.Millisecond, dateTimeOffset)),
                SqlType.DateTime));
 }
        private static SqlExpression TryCastToDecimalPS(SqlExpression value, short precision, short scale)
        {
            var context  = ExpressionTranslationContext.Current;
            var provider = context.ProviderInfo.ProviderName;

            if (provider.Equals(WellKnown.Provider.PostgreSql, StringComparison.Ordinal) ||
                provider.Equals(WellKnown.Provider.Oracle, StringComparison.Ordinal))
            {
                // to fit result into .Net decimal since Npgsql client libarary does not provide a way to make in on reading
                return(SqlDml.Cast(value, SqlType.Decimal, precision, scale));
            }
            return(value);
        }
Example #14
0
 private static SqlExpression DateTimeOffsetToLocalTime(SqlExpression dateTimeOffset)
 {
     return(SqlDml.FunctionCall("FROM_TZ",
                                SqlDml.Cast(
                                    dateTimeOffset
                                    -
                                    (SysExtractUtc(SqlDml.Native("CURRENT_TIMESTAMP"))
                                     - SysExtractUtc(
                                         SqlDml.FunctionCall("FROM_TZ",
                                                             SqlDml.Cast(SqlDml.Native("CURRENT_TIMESTAMP"), SqlType.DateTime),
                                                             DateTimeOffsetExtractPart(dateTimeOffset, "TZR")))),
                                    SqlType.DateTime)
                                , SqlDml.Native("sessiontimezone")));
 }
        protected override SqlExpression ProcessAggregate(SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
        {
            var result = base.ProcessAggregate(source, sourceColumns, aggregateColumn);

            if (aggregateColumn.AggregateType == AggregateType.Avg)
            {
                switch (Type.GetTypeCode(aggregateColumn.Type))
                {
                case TypeCode.Single:
                case TypeCode.Double:
                    result = SqlDml.Cast(result, Driver.MapValueType(aggregateColumn.Type));
                    break;
                }
            }
            return(result);
        }
Example #16
0
        private SqlExpression GetOrderByExpression(SqlExpression expression, SortProvider provider, int index)
        {
            if (provider.Header.Columns.Count <= index)
            {
                return(expression);
            }

            if (providerInfo.Supports(ProviderFeatures.DateTimeEmulation) && provider.Header.Columns[index].Type == typeof(DateTime))
            {
                return(SqlDml.Cast(expression, SqlType.DateTime));
            }
            if (providerInfo.Supports(ProviderFeatures.DateTimeOffsetEmulation) && provider.Header.Columns[index].Type == typeof(DateTimeOffset))
            {
                return(SqlDml.Cast(expression, SqlType.DateTimeOffset));
            }
            return(expression);
        }
Example #17
0
        /// <inheritdoc/>
        public override void Visit(SqlFunctionCall node)
        {
            switch (node.FunctionType)
            {
            case SqlFunctionType.Concat:
                var exprs = new SqlExpression[node.Arguments.Count];
                node.Arguments.CopyTo(exprs, 0);
                Visit(SqlDml.Concat(exprs));
                return;

            case SqlFunctionType.DateTimeTruncate:
                Visit(SqlDml.Cast(node.Arguments[0], new SqlValueType("Date")));
                return;

            case SqlFunctionType.IntervalToMilliseconds:
                Visit(CastToLong(node.Arguments[0]) / NanosecondsPerMillisecond);
                return;

            case SqlFunctionType.IntervalConstruct:
            case SqlFunctionType.IntervalToNanoseconds:
                Visit(CastToLong(node.Arguments[0]));
                return;

            case SqlFunctionType.DateTimeAddMonths:
                Visit(DateAddMonth(node.Arguments[0], node.Arguments[1]));
                return;

            case SqlFunctionType.DateTimeAddYears:
                Visit(DateAddYear(node.Arguments[0], node.Arguments[1]));
                return;

            case SqlFunctionType.DateTimeConstruct:
                Visit(DateAddDay(DateAddMonth(DateAddYear(SqlDml.Cast(SqlDml.Literal(new DateTime(2001, 1, 1)), SqlType.DateTime),
                                                          node.Arguments[0] - 2001),
                                              node.Arguments[1] - 1),
                                 node.Arguments[2] - 1));
                return;

            case SqlFunctionType.DateTimeToStringIso:
                Visit(DateTimeToStringIso(node.Arguments[0]));
                return;
            }

            base.Visit(node);
        }
        private SqlExpression VisitParameterAccess(Expression e, bool smartNull)
        {
            var type = e.Type;

            // In rare cases (when calculated column is just parameter access) we need to strip cast to object.
            if (e.NodeType == ExpressionType.Convert && e.Type == typeof(object))
            {
                type = ((UnaryExpression)e).Operand.Type;
            }
            bool optimizeBooleanParameter = type == typeof(bool);

            type = type.StripNullable();
            var typeMapping = driver.GetTypeMapping(type);
            var expression  = parameterExtractor.ExtractParameter <object>(e);
            var bindingType = optimizeBooleanParameter
        ? QueryParameterBindingType.BooleanConstant
        : (smartNull
            ? QueryParameterBindingType.SmartNull
            : QueryParameterBindingType.Regular);
            var           binding = RegisterParameterBinding(typeMapping, expression, bindingType);
            SqlExpression result;

            if (optimizeBooleanParameter)
            {
                result = SqlDml.Variant(binding, SqlFalse, SqlTrue);
                if (fixBooleanExpressions)
                {
                    result = booleanExpressionConverter.IntToBoolean(result);
                }
            }
            else
            {
                result = binding.ParameterReference;
                if (type == typeof(bool) && fixBooleanExpressions)
                {
                    result = booleanExpressionConverter.IntToBoolean(result);
                }
                else if (typeMapping.ParameterCastRequired)
                {
                    result = SqlDml.Cast(result, typeMapping.MapType());
                }
            }
            return(result);
        }
Example #19
0
        public void AddTest()
        {
            SqlLiteral <int> l1 = SqlDml.Literal(1);
            SqlLiteral <int> l2 = SqlDml.Literal(2);
            SqlBinary        b  = l1 + l2;

            Assert.AreEqual(b.NodeType, SqlNodeType.Add);

            b = b - ~l1;
            Assert.AreEqual(b.NodeType, SqlNodeType.Subtract);
            Assert.AreEqual(b.Right.NodeType, SqlNodeType.BitNot);

            SqlSelect s = SqlDml.Select();

            s.Columns.Add(1, "id");
            b = b / s;
            Assert.AreEqual(b.NodeType, SqlNodeType.Divide);
            Assert.AreEqual(b.Right.NodeType, SqlNodeType.SubSelect);

            SqlCast c = SqlDml.Cast(l1, SqlType.Decimal);

            Assert.AreEqual(c.NodeType, SqlNodeType.Cast);

            SqlFunctionCall l = SqlDml.CharLength(SqlDml.Literal("name"));

            b = c % l;
            Assert.AreEqual(b.NodeType, SqlNodeType.Modulo);
            Assert.AreEqual(b.Right.NodeType, SqlNodeType.FunctionCall);

            b = l1 * (-l2);
            Assert.AreEqual(b.NodeType, SqlNodeType.Multiply);
            Assert.AreEqual(b.Right.NodeType, SqlNodeType.Negate);

            SqlBatch    batch = SqlDml.Batch();
            SqlVariable v1    = SqlDml.Variable("v1", SqlType.Double);

            batch.Add(v1.Declare());
            batch.Add(SqlDml.Assign(v1, 1.0));
            s = SqlDml.Select();
            s.Columns.Add(b, "value");
            batch.Add(s);
        }
Example #20
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));
        }
Example #21
0
        public override void Visit(SqlExtract node)
        {
            if (node.DateTimeOffsetPart == SqlDateTimeOffsetPart.DayOfWeek)
            {
                Visit((DatePartWeekDay(node.Operand) + DateFirst + 6) % 7);
                return;
            }
            switch (node.DateTimeOffsetPart)
            {
            case SqlDateTimeOffsetPart.TimeZoneHour:
                Visit(DateTimeOffsetTimeZoneInMinutes(node.Operand) / 60);
                return;

            case SqlDateTimeOffsetPart.TimeZoneMinute:
                Visit(DateTimeOffsetTimeZoneInMinutes(node.Operand) % 60);
                return;

            case SqlDateTimeOffsetPart.Date:
                DateTimeOffsetTruncate(node.Operand).AcceptVisitor(this);
                return;

            case SqlDateTimeOffsetPart.DateTime:
                DateTimeOffsetTruncateOffset(node.Operand).AcceptVisitor(this);
                return;

            case SqlDateTimeOffsetPart.LocalDateTime:
                DateTimeOffsetToLocalDateTime(node.Operand).AcceptVisitor(this);
                return;

            case SqlDateTimeOffsetPart.UtcDateTime:
                SqlDml.Cast(Switchoffset(node.Operand, "+00:00"), SqlType.DateTime).AcceptVisitor(this);
                return;

            case SqlDateTimeOffsetPart.Offset:
                DateTimeOffsetPartOffset(node.Operand).AcceptVisitor(this);
                return;
            }
            base.Visit(node);
        }
Example #22
0
        public void SqlCastReplacingTest()
        {
            SqlCast c          = SqlDml.Cast(1, SqlType.Float);
            SqlCast cReplacing = SqlDml.Cast(2, SqlType.Char);

            c.ReplaceWith(cReplacing);

            bool passed = false;

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

            Assert.IsTrue(passed);
            Assert.AreNotEqual(c, cReplacing);
            Assert.AreEqual(c.NodeType, cReplacing.NodeType);
            Assert.AreEqual(c.Operand, cReplacing.Operand);
            Assert.AreEqual(c.Type, cReplacing.Type);
        }
        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)));
        }
        public void SelectParametersTest()
        {
            int parameterIndex = 0;
            var queries        = new List <SqlSelect>();
            var command        = Connection.CreateCommand();

            for (int rowIndex = 0; rowIndex < testValues[0].Length; rowIndex++)
            {
                var query = SqlDml.Select();
                queries.Add(query);
                query.Columns.Add(SqlDml.Literal(rowIndex), IdColumnName);
                for (int columnIndex = 0; columnIndex < testValues.Length; columnIndex++)
                {
                    var           mapping             = typeMappings[columnIndex];
                    var           columnName          = GetColumnName(columnIndex);
                    var           parameterName       = GetParameterName(parameterIndex++);
                    SqlExpression parameterExpression = SqlDml.ParameterRef(parameterName);
                    if (mapping.ParameterCastRequired)
                    {
                        parameterExpression = SqlDml.Cast(parameterExpression, mapping.MapType());
                    }
                    query.Columns.Add(parameterExpression, columnName);
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = parameterName;
                    typeMappings[columnIndex].BindValue(parameter, testValues[columnIndex][rowIndex]);
                    command.Parameters.Add(parameter);
                }
            }
            var unionQueryRef = SqlDml.QueryRef(queries.Cast <ISqlQueryExpression>().Aggregate(SqlDml.UnionAll));
            var resultQuery   = SqlDml.Select(unionQueryRef);

            resultQuery.Columns.Add(SqlDml.Asterisk);
            resultQuery.OrderBy.Add(unionQueryRef[IdColumnName]);
            command.CommandText = Driver.Compile(resultQuery).GetCommandText();
            VerifyResults(command);
        }
        protected override SqlExpression VisitUnary(UnaryExpression expression)
        {
            var operand = Visit(expression.Operand);

            if (expression.Method != null)
            {
                return(CompileMember(expression.Method, null, operand));
            }

            switch (expression.NodeType)
            {
            case ExpressionType.ArrayLength:
                if (expression.Operand.Type != typeof(byte[]))
                {
                    throw new NotSupportedException(string.Format(Strings.ExTypeXIsNotSupported, expression.Operand.Type));
                }
                return(SqlDml.Cast(SqlDml.BinaryLength(operand), driver.MapValueType(typeof(int))));

            case ExpressionType.Negate:
            case ExpressionType.NegateChecked:
                return(SqlDml.Negate(operand));

            case ExpressionType.UnaryPlus:
                return(operand);

            case ExpressionType.Not:
                return(IsBooleanExpression(expression.Operand)
            ? SqlDml.Not(operand)
            : SqlDml.BitNot(operand));

            case ExpressionType.Convert:
            case ExpressionType.ConvertChecked:
                return(VisitCast(expression, operand));
            }
            return(operand);
        }
Example #26
0
        protected override SqlProvider VisitFreeText(FreeTextProvider provider)
        {
            var rankColumnName    = provider.Header.Columns[provider.Header.Columns.Count - 1].Name;
            var stringTypeMapping = Driver.GetTypeMapping(WellKnownTypes.StringType);
            var binding           = new QueryParameterBinding(stringTypeMapping,
                                                              provider.SearchCriteria.Invoke, QueryParameterBindingType.Regular);

            var select           = SqlDml.Select();
            var realPrimaryIndex = provider.PrimaryIndex.Resolve(Handlers.Domain.Model);
            var index            = realPrimaryIndex.ReflectedType.Indexes.PrimaryIndex;
            var query            = BuildProviderQuery(index);
            var table            = Mapping[realPrimaryIndex.ReflectedType];
            var fromTable        = SqlDml.FreeTextTable(table, binding.ParameterReference,
                                                        table.Columns.Select(column => column.Name).Append(rankColumnName).ToArray(table.Columns.Count + 1));
            var fromTableRef = SqlDml.QueryRef(fromTable);

            foreach (var column in query.Columns)
            {
                select.Columns.Add(fromTableRef.Columns[column.Name] ?? column);
            }

            select.Columns.Add(SqlDml.Cast(fromTableRef.Columns[rankColumnName], SqlType.Double), rankColumnName);
            select.From = fromTableRef;
            if (provider.TopN == null)
            {
                return(CreateProvider(select, binding, provider));
            }

            var intTypeMapping = Driver.GetTypeMapping(typeof(int));
            var topNBinding    = new QueryParameterBinding(
                intTypeMapping, context => provider.TopN.Invoke(context), QueryParameterBindingType.Regular);

            select.Limit = topNBinding.ParameterReference;
            select.OrderBy.Add(select.Columns[rankColumnName], false);
            return(CreateProvider(select, new[] { binding, topNBinding }, provider));
        }
Example #27
0
        public void ComposeQuery()
        {
            using (var session = Domain.OpenSession())
                using (var tx = session.OpenTransaction()) {
                    var builder = session.Services.Get <QueryBuilder>();
                    Assert.That(builder, Is.Not.Null);

                    var       binding = builder.CreateParameterBinding(typeof(int), context => 43);
                    SqlSelect select;
                    if (StorageProviderInfo.Instance.CheckProviderIs(StorageProvider.Firebird))
                    {
                        //require cast of parameters on sql level
                        select = SqlDml.Select(SqlDml.Cast(binding.ParameterReference, SqlType.Int32));
                    }
                    else
                    {
                        select = SqlDml.Select(binding.ParameterReference);
                    }

                    var compiled = builder.CompileQuery(select);
                    Assert.That(compiled, Is.Not.Null);

                    var request = builder.CreateRequest(compiled, new[] { binding });
                    Assert.That(request, Is.Not.Null);

                    var command = builder.CreateCommand(request);
                    Assert.That(command, Is.Not.Null);

                    using (command) {
                        var result = Convert.ToInt32(command.ExecuteScalar());
                        Assert.That(result, Is.EqualTo(43));
                    }

                    tx.Complete();
                }
        }
        protected override SqlExpression ProcessAggregate(
            SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
        {
            var aggregateType = aggregateColumn.Type;
            var result        = base.ProcessAggregate(source, sourceColumns, aggregateColumn);

            if (aggregateColumn.AggregateType == AggregateType.Avg)
            {
                var originType = source.Origin.Header.Columns[aggregateColumn.SourceIndex].Type;
                // floats are promoted to doubles, but we need the same type
                if (originType == aggregateType && originType != typeof(float))
                {
                    return(result);
                }
                var sqlType = Driver.MapValueType(aggregateType);
                return(SqlDml.Cast(SqlDml.Avg(SqlDml.Cast(sourceColumns[aggregateColumn.SourceIndex], sqlType)), sqlType));
            }
            // cast to decimal is dangerous, because 'decimal' defaults to integer type
            if (aggregateColumn.AggregateType == AggregateType.Sum && aggregateType != typeof(decimal))
            {
                return(SqlDml.Cast(result, Driver.MapValueType(aggregateType)));
            }
            return(result);
        }
Example #29
0
        public override void Visit(SqlFunctionCall node)
        {
            const double nanosecondsPerSecond = 1000000000.0;

            switch (node.FunctionType)
            {
            case SqlFunctionType.PadLeft:
            case SqlFunctionType.PadRight:
                SqlHelper.GenericPad(node).AcceptVisitor(this);
                return;

            case SqlFunctionType.Rand:
                SqlDml.FunctionCall(translator.Translate(SqlFunctionType.Rand)).AcceptVisitor(this);
                return;

            case SqlFunctionType.Square:
                SqlDml.Power(node.Arguments[0], 2).AcceptVisitor(this);
                return;

            case SqlFunctionType.IntervalConstruct:
                ((node.Arguments[0] / SqlDml.Literal(nanosecondsPerSecond)) * OneSecondInterval).AcceptVisitor(this);
                return;

            case SqlFunctionType.IntervalToMilliseconds:
                SqlHelper.IntervalToMilliseconds(node.Arguments[0]).AcceptVisitor(this);
                return;

            case SqlFunctionType.IntervalToNanoseconds:
                SqlHelper.IntervalToNanoseconds(node.Arguments[0]).AcceptVisitor(this);
                return;

            case SqlFunctionType.IntervalAbs:
                SqlHelper.IntervalAbs(node.Arguments[0]).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeConstruct:
                var newNode = (SqlDml.Literal(new DateTime(2001, 1, 1))
                               + OneYearInterval * (node.Arguments[0] - 2001)
                               + OneMonthInterval * (node.Arguments[1] - 1)
                               + OneDayInterval * (node.Arguments[2] - 1));
                newNode.AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeTruncate:
                (SqlDml.FunctionCall("date_trunc", "day", node.Arguments[0])).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeAddMonths:
                (node.Arguments[0] + node.Arguments[1] * OneMonthInterval).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeAddYears:
                (node.Arguments[0] + node.Arguments[1] * OneYearInterval).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeToStringIso:
                DateTimeToStringIso(node.Arguments[0]).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeOffsetTimeOfDay:
                DateTimeOffsetTimeOfDay(node.Arguments[0]).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeOffsetAddMonths:
                SqlDml.Cast(node.Arguments[0] + node.Arguments[1] * OneMonthInterval, SqlType.DateTimeOffset).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeOffsetAddYears:
                SqlDml.Cast(node.Arguments[0] + node.Arguments[1] * OneYearInterval, SqlType.DateTimeOffset).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeOffsetConstruct:
                ConstructDateTimeOffset(node.Arguments[0], node.Arguments[1]).AcceptVisitor(this);
                return;

            case SqlFunctionType.DateTimeToDateTimeOffset:
                SqlDml.Cast(node.Arguments[0], SqlType.DateTimeOffset).AcceptVisitor(this);
                return;
            }
            base.Visit(node);
        }
Example #30
0
 protected SqlExpression ConstructDateTimeOffsetFromExpressions(SqlExpression datetimeStringExpression, SqlExpression offsetStringExpression)
 {
     return(SqlDml.Cast(SqlDml.Concat(datetimeStringExpression, " ", offsetStringExpression), SqlType.DateTimeOffset));
 }