SQLProvider/src/SQLProvider.Common/Operators.fs at master · fsprojects/SQLProvider · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
namespace FSharp.Data.Sql

open System.Linq

[<Struct>]
type ConditionOperator =
    | Like
    | NotLike
    | Equal
    | NotEqual
    | GreaterThan
    | LessThan
    | GreaterEqual
    | LessEqual
    | IsNull
    | NotNull
    | In
    | NotIn
    | NestedIn
    | NestedNotIn
    | NestedExists
    | NestedNotExists
    with
    override x.ToString() =
        // NOTE: these are MS SQL Server textual representations of the operators.
        // other providers may need to provide their own versions.
        match x with
        | Like          -> "LIKE"
        | NotLike       -> "NOT LIKE"
        | Equal         -> "="
        | NotEqual      -> "<>"
        | GreaterThan   -> ">"
        | LessThan      -> "<"
        | GreaterEqual  -> ">="
        | LessEqual     -> "<="
        | IsNull        -> "IS NULL"
        | NotNull       -> "IS NOT NULL"
        | In            -> "IN"
        | NestedIn      -> "IN"
        | NotIn         -> "NOT IN"
        | NestedNotIn   -> "NOT IN"
        | NestedExists  -> "EXISTS"
        | NestedNotExists  -> "NOT EXISTS"

[<Struct>]
type AggregateOperation = // Aggregate (column name if not default)
| KeyOp of key: string
| MaxOp of max: string
| MinOp of min: string
| SumOp of sum: string
| AvgOp of avg: string
| CountOp of count: string
| CountDistOp of countDist: string
| StdDevOp of std: string
| VarianceOp of var: string

type SelectOperations =
| DotNetSide = 0
| DatabaseSide = 1

[<AutoOpenAttribute>]
module ColumnSchema =

    type alias = string
    type Condition =
        // this is  (table alias * column name * operator * right hand value ) list  * (the same again list)
        // basically any AND or OR expression can have N terms and can have N nested condition children
        // this is largely from my CRM type provider. I don't think in practice for the SQL provider
        // you will ever have more than what is representable in a traditional binary expression tree, but
        // changing it would be a lot of effort ;)
        | And of (alias * SqlColumnType * ConditionOperator * obj option) list * (Condition list) option
        | Or of (alias * SqlColumnType * ConditionOperator * obj option) list * (Condition list) option
        | ConstantTrue
        | ConstantFalse
        | NotSupported of System.Linq.Expressions.Expression

    and CanonicalOp =
    //String functions
    | Substring of SqlItemOrColumn
    | SubstringWithLength of SqlItemOrColumn*SqlItemOrColumn
    | ToUpper
    | ToLower
    | Trim
    | Length
    | Replace of SqlItemOrColumn*SqlItemOrColumn
    | IndexOf of SqlItemOrColumn
    | IndexOfStart of SqlItemOrColumn*SqlItemOrColumn
    // Date functions
    | Date
    | Year
    | Month
    | Day
    | Hour
    | Minute
    | Second
    | AddYears of SqlItemOrColumn
    | AddMonths of int
    | AddDays of SqlItemOrColumn
    | AddHours of float
    | AddMinutes of SqlItemOrColumn
    | AddSeconds of float
    | DateDiffDays of SqlItemOrColumn
    | DateDiffSecs of SqlItemOrColumn

    // Numerical functions
    | Abs
    | Ceil
    | Floor
    | Round
    | RoundDecimals of int
    | Truncate
    | Sqrt
    | Sin
    | Cos
    | Tan
    | ASin
    | ACos
    | ATan
    | Pow of SqlItemOrColumn
    | PowConst of SqlItemOrColumn
    | Greatest of SqlItemOrColumn
    | Least of SqlItemOrColumn
    // Other
    | BasicMath of string*obj //operation, constant
    | BasicMathLeft of string*obj //operation, constant
    | BasicMathOfColumns of string*string*SqlColumnType //operation, alias, column
    | CaseSql of Condition * SqlItemOrColumn // operation, if-false
    | CaseNotSql of Condition * SqlItemOrColumn // operation, if-true
    | CaseSqlPlain of Condition * obj * obj // with 2 constants
    | CastVarchar
    | CastInt

    and SqlColumnType =
    | KeyColumn of string
    | CanonicalOperation of CanonicalOp * SqlColumnType
    | GroupColumn of AggregateOperation * SqlColumnType

    // More recursion, because you mighn want to say e.g.
    // where (x.Substring(x.IndexOf("."), (x.Length-x.IndexOf("."))
    and SqlItemOrColumn =
    | SqlCol of string*SqlColumnType //alias*column
    | SqlConstant of obj

    type ProjectionParameter =
    | EntityColumn of string
    | OperationColumn of string*SqlColumnType//name*operations

// Dummy operators, these are placeholders that are replaced in the expression tree traversal with special server-side operations such as In, Like
// The operators here are used to force the compiler to statically check against the correct types
[<AutoOpenAttribute>]
module Operators =
    //// In
    let (|=|) (a:'a) (b:'a seq) = false
    /// Not In
    let (|<>|) (a:'a) (b:'a seq) = false
    /// Like
    let (=%) (a:'a) (b:string) = false
    /// Not Like
    let (<>%) (a:'a) (b:string) = false
    /// Left join
    let private leftJoin (a:'a) = a
    let (!!) (a:IQueryable<'a>) = query { for x in a do select (leftJoin x) }

    /// Standard Deviation
    let StdDev (a:'a) = 1m

    /// Variance
    let Variance (a:'a) = 1m