-
-
Notifications
You must be signed in to change notification settings - Fork 21
/
QueryContext.fs
381 lines (311 loc) · 18.3 KB
/
QueryContext.fs
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
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
namespace SqlHydra.Query
open System
open System.Data.Common
open System.Threading
open SqlKata
/// Contains methods that compile and read a query.
type QueryContext(conn: DbConnection, compiler: SqlKata.Compilers.Compiler) =
let setProviderDbType (param: DbParameter) (propertyName: string) (providerDbType: string) =
let property = param.GetType().GetProperty(propertyName)
let dbTypeSetter = property.GetSetMethod()
let value = Enum.Parse(property.PropertyType, providerDbType)
dbTypeSetter.Invoke(param, [|value|]) |> ignore
let setParameterDbType (param: DbParameter) (qp: QueryParameter) =
match qp.ProviderDbType, compiler with
| Some type', :? SqlKata.Compilers.PostgresCompiler ->
setProviderDbType param "NpgsqlDbType" type'
| Some type', :? SqlKata.Compilers.SqlServerCompiler ->
setProviderDbType param "SqlDbType" type'
| _ -> ()
let mutable logger = fun (r: SqlResult) -> ()
interface IDisposable with
member this.Dispose() =
conn.Dispose()
this.Transaction |> Option.iter (fun t -> t.Dispose())
this.Transaction <- None
#if NETSTANDARD2_1_OR_GREATER
interface IAsyncDisposable with
member this.DisposeAsync() =
task {
do! conn.DisposeAsync()
match this.Transaction with
| Some t -> do! t.DisposeAsync()
| None -> ()
this.Transaction <- None
} |> ValueTask
#endif
member this.Connection = conn
member this.Compiler = compiler
/// Logs a SqlKata compiled query with a user provided log function.
/// Ex: queryContext.Logger <- printfn "SQL: %O"
member this.Logger
with get () = logger
// Wrap the SqlResult to override query logging
and set fn = logger <- LoggedSqlResult >> unbox<SqlResult> >> fn
/// Updates the SqlResult with the latest cmd.CommandText and then logs the query.
member private this.LogCommand (sqlResult: SqlResult, cmd: DbCommand) =
sqlResult.Sql <- cmd.CommandText
this.Logger sqlResult
member val Transaction : DbTransaction option = None with get,set
member this.BeginTransaction(?isolationLevel: Data.IsolationLevel) =
this.Transaction <-
match isolationLevel with
| Some il -> conn.BeginTransaction(il) |> Some
| None -> conn.BeginTransaction() |> Some
#if NETSTANDARD2_1_OR_GREATER
// Return ValueTask to mirror DbConnection.BeginTransactionAsync, so that if F# ever gets a ValueTask CE we can use it here
member this.BeginTransactionAsync(?isolationLevel: Data.IsolationLevel, ?cancellationToken: CancellationToken) = ValueTask <| task {
let! trans =
match isolationLevel with
| Some il -> conn.BeginTransactionAsync(il, ?cancellationToken = cancellationToken)
| None -> conn.BeginTransactionAsync(?cancellationToken = cancellationToken)
this.Transaction <- Some trans
}
#endif
member this.CommitTransaction() =
match this.Transaction with
| Some t -> t.Commit(); this.Transaction <- None
| None -> failwith "No transaction was started."
#if NETSTANDARD2_1_OR_GREATER
member this.CommitTransactionAsync(?cancellationToken: CancellationToken) = task {
match this.Transaction with
| Some t ->
do! t.CommitAsync(?cancellationToken = cancellationToken)
this.Transaction <- None
| None -> failwith "No transaction was started."
}
#endif
member this.RollbackTransaction() =
match this.Transaction with
| Some t -> t.Rollback(); this.Transaction <- None
| None -> failwith "No transaction was started."
#if NETSTANDARD2_1_OR_GREATER
member this.RollbackTransactionAsync(?cancellationToken: CancellationToken) = task {
match this.Transaction with
| Some t ->
do! t.RollbackAsync(?cancellationToken = cancellationToken)
this.Transaction <- None
| None -> failwith "No transaction was started."
}
#endif
member private this.TrySetTransaction(cmd: DbCommand) =
this.Transaction |> Option.iter (fun t -> cmd.Transaction <- t)
/// Builds a DbCommand with CommandText and Parameters from a SqlKata compiled query.
member this.BuildCommand(compiledQuery: SqlResult, ?log: bool) =
let log = defaultArg log true
if log then this.Logger compiledQuery
let cmd = conn.CreateCommand()
cmd |> this.TrySetTransaction
cmd.CommandText <- compiledQuery.Sql
for kvp in compiledQuery.NamedBindings do
let p = cmd.CreateParameter()
p.ParameterName <- kvp.Key
p.Value <-
match kvp.Value with
| :? QueryParameter as qp ->
do setParameterDbType p qp
qp.Value
| _ ->
kvp.Value
// SqlHydra must manually handle DateOnly and TimeOnly conversions of all parameters
|> KataUtils.convertIfDateOnlyTimeOnly
cmd.Parameters.Add(p) |> ignore
cmd
/// Builds an ADO.NET DbCommand from a SqlKata query.
member this.BuildCommand(query: Query) =
let compiledQuery = compiler.Compile(query)
this.BuildCommand(compiledQuery)
/// Returns an ADO.NET data reader for a given query.
member this.GetReader<'T, 'Reader & #DbDataReader> (query: SelectQuery<'T>) =
let cmd = this.BuildCommand(query.ToKataQuery()) // do not dispose cmd
cmd.ExecuteReader() :?> 'Reader
/// Returns an ADO.NET data reader for a given query.
member this.GetReaderAsync<'T, 'Reader & #DbDataReader> (query: SelectQuery<'T>) =
this.GetReaderAsyncWithOptions<'T, 'Reader>(query)
/// Returns an ADO.NET data reader for a given query.
member this.GetReaderAsyncWithOptions<'T, 'Reader & #DbDataReader> (query: SelectQuery<'T>, ?cancel: CancellationToken) =
task { // Must wrap in task to prevent `EndExecuteNonQuery` ex in NET6_0_OR_GREATER
let cmd = this.BuildCommand(query.ToKataQuery()) // do not dispose cmd
let! reader = cmd.ExecuteReaderAsync(cancel |> Option.defaultValue CancellationToken.None)
return reader :?> 'Reader
}
/// Executes a select query with a given readEntity builder function.
member this.Read<'Entity, 'Reader & #DbDataReader> (readEntityBuilder: 'Reader -> (unit -> 'Entity)) (query: SelectQuery<'Entity>) =
use cmd = this.BuildCommand(query.ToKataQuery())
use reader = cmd.ExecuteReader() :?> 'Reader
let readEntity = readEntityBuilder reader
seq [|
while reader.Read() do
readEntity()
|]
/// Executes a select query with a given readEntity builder function.
member this.ReadAsync<'Entity, 'Reader & #DbDataReader> (readEntityBuilder: 'Reader -> (unit -> 'Entity)) (query: SelectQuery<'Entity>) =
this.ReadAsyncWithOptions (query, readEntityBuilder)
/// Executes a select query with a given readEntity builder function and optional args.
member this.ReadAsyncWithOptions<'Entity, 'Reader & #DbDataReader>
(query: SelectQuery<'Entity>, readEntityBuilder: 'Reader -> (unit -> 'Entity), ?cancel: CancellationToken) =
task { // Must wrap in task to prevent `EndExecuteNonQuery` ex in NET6_0_OR_GREATER
let cancel = defaultArg cancel CancellationToken.None
use cmd = this.BuildCommand (query.ToKataQuery())
use! reader = cmd.ExecuteReaderAsync(cancel)
let readEntity = readEntityBuilder (reader :?> 'Reader)
let results = ResizeArray<'Entity>()
let! hasMore = reader.ReadAsync(cancel)
let mutable hasMore = hasMore
while hasMore && not cancel.IsCancellationRequested do
results.Add(readEntity ())
let! hasMore' = reader.ReadAsync(cancel)
hasMore <- hasMore'
return results :> seq<'Entity>
}
/// Executes a select query with a given readEntity builder function for a single (option) result.
member this.ReadOne<'Entity, 'Reader & #DbDataReader> (readEntityBuilder: 'Reader -> (unit -> 'Entity)) (query: SelectQuery<'Entity>) =
this.Read readEntityBuilder query |> Seq.tryHead
/// Executes a select query with a given readEntity builder function for a single (option) result.
member this.ReadOneAsync<'Entity, 'Reader & #DbDataReader> (readEntityBuilder: 'Reader -> (unit -> 'Entity)) (query: SelectQuery<'Entity>) =
this.ReadOneAsyncWithOptions(query, readEntityBuilder)
/// Executes a select query with a given readEntity builder function for a single (option) result with optional args.
member this.ReadOneAsyncWithOptions<'Entity, 'Reader & #DbDataReader>
(query: SelectQuery<'Entity>, readEntityBuilder: 'Reader -> (unit -> 'Entity), ?cancel: CancellationToken) =
task { // Must wrap in task to prevent `EndExecuteNonQuery` ex in NET6_0_OR_GREATER
let! entities = this.ReadAsyncWithOptions (query, readEntityBuilder, cancel |> Option.defaultValue CancellationToken.None)
return entities |> Seq.tryHead
}
member this.Insert<'T, 'InsertReturn when 'InsertReturn : struct> (iq: InsertQuery<'T, 'InsertReturn>) =
let compiledQuery = iq.ToKataQuery() |> compiler.Compile
use cmd = this.BuildCommand(compiledQuery, log = false) // We will log manually below to capture query changes
// Applies on conflict modifier if in spec
let applyOnConflict =
match iq.Spec.InsertType with
| InsertOrReplace -> OnConflict.insertOrReplace
| OnConflictDoUpdate (conflictFields, updateFields) -> OnConflict.onConflictDoUpdate conflictFields updateFields
| OnConflictDoNothing conflictFields -> OnConflict.onConflictDoNothing conflictFields
| Insert -> id
KataUtils.failIfIdentityOnConflict iq.Spec
// Did the user select an identity field?
match iq.Spec.IdentityField with
| Some identityField ->
// Try apply on conflict
cmd.CommandText <- cmd.CommandText |> applyOnConflict
// Fix postgres identity
if compiler :? SqlKata.Compilers.PostgresCompiler
then cmd.CommandText <- cmd.CommandText |> Fixes.Postgres.fixIdentityQuery identityField
// Fix oracle identity
elif compiler :? SqlKata.Compilers.OracleCompiler
then cmd.CommandText <- cmd.CommandText |> Fixes.Oracle.fixIdentityQuery identityField
// Fix mssql guid identity
elif compiler :? SqlKata.Compilers.SqlServerCompiler && typeof<'InsertReturn> = typeof<System.Guid>
then cmd.CommandText <- cmd.CommandText |> Fixes.MsSql.fixGuidIdentityQuery identityField
this.LogCommand(compiledQuery, cmd)
// Execute insert and return identity
if compiler :? SqlKata.Compilers.OracleCompiler then
let outputParam = cmd.CreateParameter()
outputParam.ParameterName <- "outputParam"
outputParam.DbType <- Data.DbType.Decimal
outputParam.Direction <- Data.ParameterDirection.Output
cmd.Parameters.Add(outputParam) |> ignore
let _ = cmd.ExecuteNonQuery()
// 'InsertReturn type set via `getId` in the builder
Convert.ChangeType(outputParam.Value, typeof<'InsertReturn>) :?> 'InsertReturn
else
let identity = cmd.ExecuteScalar()
// 'InsertReturn type set via `getId` in the builder
Convert.ChangeType(identity, typeof<'InsertReturn>) :?> 'InsertReturn
| None ->
// Try apply on conflict
cmd.CommandText <- cmd.CommandText |> applyOnConflict
// Fix Oracle multi-insert query
if compiler :? SqlKata.Compilers.OracleCompiler && iq.Spec.Entities.Length > 1
then cmd.CommandText <- cmd.CommandText |> Fixes.Oracle.fixMultiInsertQuery
this.LogCommand(compiledQuery, cmd)
let results = cmd.ExecuteNonQuery()
// 'InsertReturn is `int` here -- NOTE: must include `'InsertReturn : struct` constraint
Convert.ChangeType(results, typeof<'InsertReturn>) :?> 'InsertReturn
member this.InsertAsync<'T, 'InsertReturn when 'InsertReturn : struct> (query: InsertQuery<'T, 'InsertReturn>) =
this.InsertAsyncWithOptions(query)
member this.InsertAsyncWithOptions<'T, 'InsertReturn when 'InsertReturn : struct> (iq: InsertQuery<'T, 'InsertReturn>, ?cancel: CancellationToken) =
task { // Must wrap in task to prevent `EndExecuteNonQuery` ex in NET6_0_OR_GREATER
let compiledQuery = iq.ToKataQuery() |> compiler.Compile
use cmd = this.BuildCommand(compiledQuery, log = false) // We will log manually below to capture query changes
// Applies on conflict modifier if in spec
let applyOnConflict =
match iq.Spec.InsertType with
| InsertOrReplace -> OnConflict.insertOrReplace
| OnConflictDoUpdate (conflictFields, updateFields) -> OnConflict.onConflictDoUpdate conflictFields updateFields
| OnConflictDoNothing conflictFields -> OnConflict.onConflictDoNothing conflictFields
| Insert -> id
KataUtils.failIfIdentityOnConflict iq.Spec
// Did the user select an identity field?
match iq.Spec.IdentityField with
| Some identityField ->
// Try apply on conflict
cmd.CommandText <- cmd.CommandText |> applyOnConflict
// Fix postgres identity
if compiler :? SqlKata.Compilers.PostgresCompiler
then cmd.CommandText <- cmd.CommandText |> Fixes.Postgres.fixIdentityQuery identityField
// Fix oracle identity
elif compiler :? SqlKata.Compilers.OracleCompiler
then cmd.CommandText <- cmd.CommandText |> Fixes.Oracle.fixIdentityQuery identityField
// Fix mssql guid identity
elif compiler :? SqlKata.Compilers.SqlServerCompiler && typeof<'InsertReturn> = typeof<System.Guid>
then cmd.CommandText <- cmd.CommandText |> Fixes.MsSql.fixGuidIdentityQuery identityField
this.LogCommand(compiledQuery, cmd)
// Execute insert and return identity
if compiler :? SqlKata.Compilers.OracleCompiler then
let outputParam = cmd.CreateParameter()
outputParam.ParameterName <- "outputParam"
outputParam.DbType <- Data.DbType.Decimal
outputParam.Direction <- Data.ParameterDirection.Output
cmd.Parameters.Add(outputParam) |> ignore
let! _ = cmd.ExecuteNonQueryAsync(cancel |> Option.defaultValue CancellationToken.None)
// 'InsertReturn type set via `getId` in the builder
return Convert.ChangeType(outputParam.Value, typeof<'InsertReturn>) :?> 'InsertReturn
else
let! identity = cmd.ExecuteScalarAsync(cancel |> Option.defaultValue CancellationToken.None)
// 'InsertReturn type set via `getId` in the builder
return Convert.ChangeType(identity, typeof<'InsertReturn>) :?> 'InsertReturn
| None ->
// Try apply on conflict
cmd.CommandText <- cmd.CommandText |> applyOnConflict
// Fix Oracle multi-insert query
if compiler :? SqlKata.Compilers.OracleCompiler && iq.Spec.Entities.Length > 1
then cmd.CommandText <- cmd.CommandText |> Fixes.Oracle.fixMultiInsertQuery
this.LogCommand(compiledQuery, cmd)
let! results = cmd.ExecuteNonQueryAsync(cancel |> Option.defaultValue CancellationToken.None)
// 'InsertReturn is `int` here -- NOTE: must include `'InsertReturn : struct` constraint
return Convert.ChangeType(results, typeof<'InsertReturn>) :?> 'InsertReturn
}
member this.Update (query: UpdateQuery<'T>) =
use cmd = this.BuildCommand(query.ToKataQuery())
cmd.ExecuteNonQuery()
member this.UpdateAsync (query: UpdateQuery<'T>) =
this.UpdateAsyncWithOptions(query)
member this.UpdateAsyncWithOptions (query: UpdateQuery<'T>, ?cancel: CancellationToken) =
task { // Must wrap in task to prevent `EndExecuteNonQuery` ex in NET6_0_OR_GREATER
use cmd = this.BuildCommand(query.ToKataQuery())
return! cmd.ExecuteNonQueryAsync(cancel |> Option.defaultValue CancellationToken.None)
}
member this.Delete (query: DeleteQuery<'T>) =
use cmd = this.BuildCommand(query.ToKataQuery())
cmd.ExecuteNonQuery()
member this.DeleteAsync (query: DeleteQuery<'T>) =
this.DeleteAsyncWithOptions(query)
member this.DeleteAsyncWithOptions (query: DeleteQuery<'T>, ?cancel: CancellationToken) =
task { // Must wrap in task to prevent `EndExecuteNonQuery` ex in NET6_0_OR_GREATER
use cmd = this.BuildCommand(query.ToKataQuery())
return! cmd.ExecuteNonQueryAsync(cancel |> Option.defaultValue CancellationToken.None)
}
member this.Count (query: SelectQuery<int>) =
use cmd = this.BuildCommand(query.ToKataQuery())
match cmd.ExecuteScalar() with
| :? int64 as count -> Convert.ToInt32 count
| _ as count -> count :?> int
member this.CountAsync (query: SelectQuery<int>) =
this.CountAsyncWithOptions(query)
member this.CountAsyncWithOptions (query: SelectQuery<int>, ?cancel: CancellationToken) =
task { // Must wrap in task to prevent `EndExecuteNonQuery` ex in NET6_0_OR_GREATER
use cmd = this.BuildCommand(query.ToKataQuery())
match! cmd.ExecuteScalarAsync(cancel |> Option.defaultValue CancellationToken.None) with
| :? int64 as count -> return Convert.ToInt32 count
| count -> return count :?> int
}