forked from go-ozzo/ozzo-dbx
-
Notifications
You must be signed in to change notification settings - Fork 31
/
query.go
384 lines (335 loc) · 10.6 KB
/
query.go
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
382
383
384
// Copyright 2016 Qiang Xue. All rights reserved.
// Use of this source code is governed by a MIT-style
// license that can be found in the LICENSE file.
package dbx
import (
"context"
"database/sql"
"database/sql/driver"
"encoding/hex"
"errors"
"fmt"
"strings"
"time"
)
// ExecHookFunc executes before op allowing custom handling like auto fail/retry.
type ExecHookFunc func(q *Query, op func() error) error
// OneHookFunc executes right before the query populate the row result from One() call (aka. op).
type OneHookFunc func(q *Query, a interface{}, op func(b interface{}) error) error
// AllHookFunc executes right before the query populate the row result from All() call (aka. op).
type AllHookFunc func(q *Query, sliceA interface{}, op func(sliceB interface{}) error) error
// Params represents a list of parameter values to be bound to a SQL statement.
// The map keys are the parameter names while the map values are the corresponding parameter values.
type Params map[string]interface{}
// Executor prepares, executes, or queries a SQL statement.
type Executor interface {
// Exec executes a SQL statement
Exec(query string, args ...interface{}) (sql.Result, error)
// ExecContext executes a SQL statement with the given context
ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
// Query queries a SQL statement
Query(query string, args ...interface{}) (*sql.Rows, error)
// QueryContext queries a SQL statement with the given context
QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
// Prepare creates a prepared statement
Prepare(query string) (*sql.Stmt, error)
}
// Query represents a SQL statement to be executed.
type Query struct {
executor Executor
sql, rawSQL string
placeholders []string
params Params
stmt *sql.Stmt
ctx context.Context
// hooks
execHook ExecHookFunc
oneHook OneHookFunc
allHook AllHookFunc
// FieldMapper maps struct field names to DB column names.
FieldMapper FieldMapFunc
// LastError contains the last error (if any) of the query.
// LastError is cleared by Execute(), Row(), Rows(), One(), and All().
LastError error
// LogFunc is used to log the SQL statement being executed.
LogFunc LogFunc
// PerfFunc is used to log the SQL execution time. It is ignored if nil.
// Deprecated: Please use QueryLogFunc and ExecLogFunc instead.
PerfFunc PerfFunc
// QueryLogFunc is called each time when performing a SQL query that returns data.
QueryLogFunc QueryLogFunc
// ExecLogFunc is called each time when a SQL statement is executed.
ExecLogFunc ExecLogFunc
}
// NewQuery creates a new Query with the given SQL statement.
func NewQuery(db *DB, executor Executor, sql string) *Query {
rawSQL, placeholders := db.processSQL(sql)
return &Query{
executor: executor,
sql: sql,
rawSQL: rawSQL,
placeholders: placeholders,
params: Params{},
ctx: db.ctx,
FieldMapper: db.FieldMapper,
LogFunc: db.LogFunc,
PerfFunc: db.PerfFunc,
QueryLogFunc: db.QueryLogFunc,
ExecLogFunc: db.ExecLogFunc,
}
}
// SQL returns the original SQL used to create the query.
// The actual SQL (RawSQL) being executed is obtained by replacing the named
// parameter placeholders with anonymous ones.
func (q *Query) SQL() string {
return q.sql
}
// Context returns the context associated with the query.
func (q *Query) Context() context.Context {
return q.ctx
}
// WithContext associates a context with the query.
func (q *Query) WithContext(ctx context.Context) *Query {
q.ctx = ctx
return q
}
// WithExecHook associates the provided exec hook function with the query.
//
// It is called for every Query resolver (Execute(), One(), All(), Row(), Column()),
// allowing you to implement auto fail/retry or any other additional handling.
func (q *Query) WithExecHook(fn ExecHookFunc) *Query {
q.execHook = fn
return q
}
// WithOneHook associates the provided hook function with the query,
// called on q.One(), allowing you to implement custom struct scan based
// on the One() argument and/or result.
func (q *Query) WithOneHook(fn OneHookFunc) *Query {
q.oneHook = fn
return q
}
// WithOneHook associates the provided hook function with the query,
// called on q.All(), allowing you to implement custom slice scan based
// on the All() argument and/or result.
func (q *Query) WithAllHook(fn AllHookFunc) *Query {
q.allHook = fn
return q
}
// logSQL returns the SQL statement with parameters being replaced with the actual values.
// The result is only for logging purpose and should not be used to execute.
func (q *Query) logSQL() string {
s := q.sql
for k, v := range q.params {
if valuer, ok := v.(driver.Valuer); ok && valuer != nil {
v, _ = valuer.Value()
}
var sv string
if str, ok := v.(string); ok {
sv = "'" + strings.Replace(str, "'", "''", -1) + "'"
} else if bs, ok := v.([]byte); ok {
sv = "0x" + hex.EncodeToString(bs)
} else {
sv = fmt.Sprintf("%v", v)
}
s = strings.Replace(s, "{:"+k+"}", sv, -1)
}
return s
}
// Params returns the parameters to be bound to the SQL statement represented by this query.
func (q *Query) Params() Params {
return q.params
}
// Prepare creates a prepared statement for later queries or executions.
// Close() should be called after finishing all queries.
func (q *Query) Prepare() *Query {
stmt, err := q.executor.Prepare(q.rawSQL)
if err != nil {
q.LastError = err
return q
}
q.stmt = stmt
return q
}
// Close closes the underlying prepared statement.
// Close does nothing if the query has not been prepared before.
func (q *Query) Close() error {
if q.stmt == nil {
return nil
}
err := q.stmt.Close()
q.stmt = nil
return err
}
// Bind sets the parameters that should be bound to the SQL statement.
// The parameter placeholders in the SQL statement are in the format of "{:ParamName}".
func (q *Query) Bind(params Params) *Query {
if len(q.params) == 0 {
q.params = params
} else {
for k, v := range params {
q.params[k] = v
}
}
return q
}
// Execute executes the SQL statement without retrieving data.
func (q *Query) Execute() (sql.Result, error) {
var result sql.Result
execErr := q.execWrap(func() error {
var err error
result, err = q.execute()
return err
})
return result, execErr
}
func (q *Query) execute() (result sql.Result, err error) {
err = q.LastError
q.LastError = nil
if err != nil {
return
}
var params []interface{}
params, err = replacePlaceholders(q.placeholders, q.params)
if err != nil {
return
}
start := time.Now()
if q.ctx == nil {
if q.stmt == nil {
result, err = q.executor.Exec(q.rawSQL, params...)
} else {
result, err = q.stmt.Exec(params...)
}
} else {
if q.stmt == nil {
result, err = q.executor.ExecContext(q.ctx, q.rawSQL, params...)
} else {
result, err = q.stmt.ExecContext(q.ctx, params...)
}
}
if q.ExecLogFunc != nil {
q.ExecLogFunc(q.ctx, time.Now().Sub(start), q.logSQL(), result, err)
}
if q.LogFunc != nil {
q.LogFunc("[%.2fms] Execute SQL: %v", float64(time.Now().Sub(start).Milliseconds()), q.logSQL())
}
if q.PerfFunc != nil {
q.PerfFunc(time.Now().Sub(start).Nanoseconds(), q.logSQL(), true)
}
return
}
// One executes the SQL statement and populates the first row of the result into a struct or NullStringMap.
// Refer to Rows.ScanStruct() and Rows.ScanMap() for more details on how to specify
// the variable to be populated.
// Note that when the query has no rows in the result set, an sql.ErrNoRows will be returned.
func (q *Query) One(a interface{}) error {
return q.execWrap(func() error {
rows, err := q.Rows()
if err != nil {
return err
}
if q.oneHook != nil {
return q.oneHook(q, a, rows.one)
}
return rows.one(a)
})
}
// All executes the SQL statement and populates all the resulting rows into a slice of struct or NullStringMap.
// The slice must be given as a pointer. Each slice element must be either a struct or a NullStringMap.
// Refer to Rows.ScanStruct() and Rows.ScanMap() for more details on how each slice element can be.
// If the query returns no row, the slice will be an empty slice (not nil).
func (q *Query) All(slice interface{}) error {
return q.execWrap(func() error {
rows, err := q.Rows()
if err != nil {
return err
}
if q.allHook != nil {
return q.allHook(q, slice, rows.all)
}
return rows.all(slice)
})
}
// Row executes the SQL statement and populates the first row of the result into a list of variables.
// Note that the number of the variables should match to that of the columns in the query result.
// Note that when the query has no rows in the result set, an sql.ErrNoRows will be returned.
func (q *Query) Row(a ...interface{}) error {
return q.execWrap(func() error {
rows, err := q.Rows()
if err != nil {
return err
}
return rows.row(a...)
})
}
// Column executes the SQL statement and populates the first column of the result into a slice.
// Note that the parameter must be a pointer to a slice.
func (q *Query) Column(a interface{}) error {
return q.execWrap(func() error {
rows, err := q.Rows()
if err != nil {
return err
}
return rows.column(a)
})
}
// Rows executes the SQL statement and returns a Rows object to allow retrieving data row by row.
func (q *Query) Rows() (rows *Rows, err error) {
err = q.LastError
q.LastError = nil
if err != nil {
return
}
var params []interface{}
params, err = replacePlaceholders(q.placeholders, q.params)
if err != nil {
return
}
start := time.Now()
var rr *sql.Rows
if q.ctx == nil {
if q.stmt == nil {
rr, err = q.executor.Query(q.rawSQL, params...)
} else {
rr, err = q.stmt.Query(params...)
}
} else {
if q.stmt == nil {
rr, err = q.executor.QueryContext(q.ctx, q.rawSQL, params...)
} else {
rr, err = q.stmt.QueryContext(q.ctx, params...)
}
}
rows = &Rows{rr, q.FieldMapper}
if q.QueryLogFunc != nil {
q.QueryLogFunc(q.ctx, time.Now().Sub(start), q.logSQL(), rr, err)
}
if q.LogFunc != nil {
q.LogFunc("[%.2fms] Query SQL: %v", float64(time.Now().Sub(start).Milliseconds()), q.logSQL())
}
if q.PerfFunc != nil {
q.PerfFunc(time.Now().Sub(start).Nanoseconds(), q.logSQL(), false)
}
return
}
func (q *Query) execWrap(op func() error) error {
if q.execHook != nil {
return q.execHook(q, op)
}
return op()
}
// replacePlaceholders converts a list of named parameters into a list of anonymous parameters.
func replacePlaceholders(placeholders []string, params Params) ([]interface{}, error) {
if len(placeholders) == 0 {
return nil, nil
}
var result []interface{}
for _, name := range placeholders {
if value, ok := params[name]; ok {
result = append(result, value)
} else {
return nil, errors.New("Named parameter not found: " + name)
}
}
return result, nil
}