gdb_func.go 27 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882
  1. // Copyright GoFrame Author(https://goframe.org). All Rights Reserved.
  2. //
  3. // This Source Code Form is subject to the terms of the MIT License.
  4. // If a copy of the MIT was not distributed with this file,
  5. // You can obtain one at https://github.com/gogf/gf.
  6. package gdb
  7. import (
  8. "bytes"
  9. "context"
  10. "fmt"
  11. "reflect"
  12. "regexp"
  13. "strings"
  14. "time"
  15. "github.com/gogf/gf/v2/container/garray"
  16. "github.com/gogf/gf/v2/internal/empty"
  17. "github.com/gogf/gf/v2/internal/reflection"
  18. "github.com/gogf/gf/v2/internal/utils"
  19. "github.com/gogf/gf/v2/os/gstructs"
  20. "github.com/gogf/gf/v2/os/gtime"
  21. "github.com/gogf/gf/v2/text/gregex"
  22. "github.com/gogf/gf/v2/text/gstr"
  23. "github.com/gogf/gf/v2/util/gconv"
  24. "github.com/gogf/gf/v2/util/gmeta"
  25. "github.com/gogf/gf/v2/util/gutil"
  26. )
  27. // iString is the type assert api for String.
  28. type iString interface {
  29. String() string
  30. }
  31. // iIterator is the type assert api for Iterator.
  32. type iIterator interface {
  33. Iterator(f func(key, value interface{}) bool)
  34. }
  35. // iInterfaces is the type assert api for Interfaces.
  36. type iInterfaces interface {
  37. Interfaces() []interface{}
  38. }
  39. // iTableName is the interface for retrieving table name fro struct.
  40. type iTableName interface {
  41. TableName() string
  42. }
  43. const (
  44. OrmTagForStruct = "orm"
  45. OrmTagForTable = "table"
  46. OrmTagForWith = "with"
  47. OrmTagForWithWhere = "where"
  48. OrmTagForWithOrder = "order"
  49. OrmTagForDo = "do"
  50. )
  51. var (
  52. // quoteWordReg is the regular expression object for a word check.
  53. quoteWordReg = regexp.MustCompile(`^[a-zA-Z0-9\-_]+$`)
  54. // Priority tags for struct converting for orm field mapping.
  55. structTagPriority = append([]string{OrmTagForStruct}, gconv.StructTagPriority...)
  56. )
  57. // WithDB injects given db object into context and returns a new context.
  58. func WithDB(ctx context.Context, db DB) context.Context {
  59. if db == nil {
  60. return ctx
  61. }
  62. dbCtx := db.GetCtx()
  63. if ctxDb := DBFromCtx(dbCtx); ctxDb != nil {
  64. return dbCtx
  65. }
  66. ctx = context.WithValue(ctx, ctxKeyForDB, db)
  67. return ctx
  68. }
  69. // DBFromCtx retrieves and returns DB object from context.
  70. func DBFromCtx(ctx context.Context) DB {
  71. if ctx == nil {
  72. return nil
  73. }
  74. v := ctx.Value(ctxKeyForDB)
  75. if v != nil {
  76. return v.(DB)
  77. }
  78. return nil
  79. }
  80. // ToSQL formats and returns the last one of sql statements in given closure function.
  81. func ToSQL(ctx context.Context, f func(ctx context.Context) error) (sql string, err error) {
  82. var manager = &CatchSQLManager{
  83. SQLArray: garray.NewStrArray(),
  84. DoCommit: false,
  85. }
  86. ctx = context.WithValue(ctx, ctxKeyCatchSQL, manager)
  87. err = f(ctx)
  88. sql, _ = manager.SQLArray.PopRight()
  89. return
  90. }
  91. // CatchSQL catches and returns all sql statements that are executed in given closure function.
  92. func CatchSQL(ctx context.Context, f func(ctx context.Context) error) (sqlArray []string, err error) {
  93. var manager = &CatchSQLManager{
  94. SQLArray: garray.NewStrArray(),
  95. DoCommit: true,
  96. }
  97. ctx = context.WithValue(ctx, ctxKeyCatchSQL, manager)
  98. err = f(ctx)
  99. return manager.SQLArray.Slice(), err
  100. }
  101. // isDoStruct checks and returns whether given type is a DO struct.
  102. func isDoStruct(object interface{}) bool {
  103. // It checks by struct name like "XxxForDao", to be compatible with old version.
  104. // TODO remove this compatible codes in future.
  105. reflectType := reflect.TypeOf(object)
  106. if gstr.HasSuffix(reflectType.String(), modelForDaoSuffix) {
  107. return true
  108. }
  109. // It checks by struct meta for DO struct in version.
  110. if ormTag := gmeta.Get(object, OrmTagForStruct); !ormTag.IsEmpty() {
  111. match, _ := gregex.MatchString(
  112. fmt.Sprintf(`%s\s*:\s*([^,]+)`, OrmTagForDo),
  113. ormTag.String(),
  114. )
  115. if len(match) > 1 {
  116. return gconv.Bool(match[1])
  117. }
  118. }
  119. return false
  120. }
  121. // getTableNameFromOrmTag retrieves and returns the table name from struct object.
  122. func getTableNameFromOrmTag(object interface{}) string {
  123. var tableName string
  124. // Use the interface value.
  125. if r, ok := object.(iTableName); ok {
  126. tableName = r.TableName()
  127. }
  128. // User meta data tag "orm".
  129. if tableName == "" {
  130. if ormTag := gmeta.Get(object, OrmTagForStruct); !ormTag.IsEmpty() {
  131. match, _ := gregex.MatchString(
  132. fmt.Sprintf(`%s\s*:\s*([^,]+)`, OrmTagForTable),
  133. ormTag.String(),
  134. )
  135. if len(match) > 1 {
  136. tableName = match[1]
  137. }
  138. }
  139. }
  140. // Use the struct name of snake case.
  141. if tableName == "" {
  142. if t, err := gstructs.StructType(object); err != nil {
  143. panic(err)
  144. } else {
  145. tableName = gstr.CaseSnakeFirstUpper(
  146. gstr.StrEx(t.String(), "."),
  147. )
  148. }
  149. }
  150. return tableName
  151. }
  152. // ListItemValues retrieves and returns the elements of all item struct/map with key `key`.
  153. // Note that the parameter `list` should be type of slice which contains elements of map or struct,
  154. // or else it returns an empty slice.
  155. //
  156. // The parameter `list` supports types like:
  157. // []map[string]interface{}
  158. // []map[string]sub-map
  159. // []struct
  160. // []struct:sub-struct
  161. // Note that the sub-map/sub-struct makes sense only if the optional parameter `subKey` is given.
  162. // See gutil.ListItemValues.
  163. func ListItemValues(list interface{}, key interface{}, subKey ...interface{}) (values []interface{}) {
  164. return gutil.ListItemValues(list, key, subKey...)
  165. }
  166. // ListItemValuesUnique retrieves and returns the unique elements of all struct/map with key `key`.
  167. // Note that the parameter `list` should be type of slice which contains elements of map or struct,
  168. // or else it returns an empty slice.
  169. // See gutil.ListItemValuesUnique.
  170. func ListItemValuesUnique(list interface{}, key string, subKey ...interface{}) []interface{} {
  171. return gutil.ListItemValuesUnique(list, key, subKey...)
  172. }
  173. // GetInsertOperationByOption returns proper insert option with given parameter `option`.
  174. func GetInsertOperationByOption(option int) string {
  175. var operator string
  176. switch option {
  177. case InsertOptionReplace:
  178. operator = "REPLACE"
  179. case InsertOptionIgnore:
  180. operator = "INSERT IGNORE"
  181. default:
  182. operator = "INSERT"
  183. }
  184. return operator
  185. }
  186. // DataToMapDeep converts `value` to map type recursively(if attribute struct is embedded).
  187. // The parameter `value` should be type of *map/map/*struct/struct.
  188. // It supports embedded struct definition for struct.
  189. func DataToMapDeep(value interface{}) map[string]interface{} {
  190. m := gconv.Map(value, structTagPriority...)
  191. for k, v := range m {
  192. switch v.(type) {
  193. case time.Time, *time.Time, gtime.Time, *gtime.Time:
  194. m[k] = v
  195. default:
  196. // Use string conversion in default.
  197. if s, ok := v.(iString); ok {
  198. m[k] = s.String()
  199. } else {
  200. m[k] = v
  201. }
  202. }
  203. }
  204. return m
  205. }
  206. // doHandleTableName adds prefix string and quote chars for table name. It handles table string like:
  207. // "user", "user u", "user,user_detail", "user u, user_detail ut", "user as u, user_detail as ut",
  208. // "user.user u", "`user`.`user` u".
  209. //
  210. // Note that, this will automatically check the table prefix whether already added, if true it does
  211. // nothing to the table name, or else adds the prefix to the table name and returns new table name with prefix.
  212. func doQuoteTableName(table, prefix, charLeft, charRight string) string {
  213. var (
  214. index int
  215. chars = charLeft + charRight
  216. array1 = gstr.SplitAndTrim(table, ",")
  217. )
  218. for k1, v1 := range array1 {
  219. array2 := gstr.SplitAndTrim(v1, " ")
  220. // Trim the security chars.
  221. array2[0] = gstr.Trim(array2[0], chars)
  222. // Check whether it has database name.
  223. array3 := gstr.Split(gstr.Trim(array2[0]), ".")
  224. for k, v := range array3 {
  225. array3[k] = gstr.Trim(v, chars)
  226. }
  227. index = len(array3) - 1
  228. // If the table name already has the prefix, skips the prefix adding.
  229. if len(array3[index]) <= len(prefix) || array3[index][:len(prefix)] != prefix {
  230. array3[index] = prefix + array3[index]
  231. }
  232. array2[0] = gstr.Join(array3, ".")
  233. // Add the security chars.
  234. array2[0] = doQuoteString(array2[0], charLeft, charRight)
  235. array1[k1] = gstr.Join(array2, " ")
  236. }
  237. return gstr.Join(array1, ",")
  238. }
  239. // doQuoteWord checks given string `s` a word, if true quotes it with `charLeft` and `charRight`
  240. // and returns the quoted string; or else returns `s` without any change.
  241. func doQuoteWord(s, charLeft, charRight string) string {
  242. if quoteWordReg.MatchString(s) && !gstr.ContainsAny(s, charLeft+charRight) {
  243. return charLeft + s + charRight
  244. }
  245. return s
  246. }
  247. // doQuoteString quotes string with quote chars.
  248. // For example, if quote char is '`':
  249. // "null" => "NULL"
  250. // "user" => "`user`"
  251. // "user u" => "`user` u"
  252. // "user,user_detail" => "`user`,`user_detail`"
  253. // "user u, user_detail ut" => "`user` u,`user_detail` ut"
  254. // "user.user u, user.user_detail ut" => "`user`.`user` u,`user`.`user_detail` ut"
  255. // "u.id, u.name, u.age" => "`u`.`id`,`u`.`name`,`u`.`age`"
  256. // "u.id asc" => "`u`.`id` asc".
  257. func doQuoteString(s, charLeft, charRight string) string {
  258. array1 := gstr.SplitAndTrim(s, ",")
  259. for k1, v1 := range array1 {
  260. array2 := gstr.SplitAndTrim(v1, " ")
  261. array3 := gstr.Split(gstr.Trim(array2[0]), ".")
  262. if len(array3) == 1 {
  263. if strings.EqualFold(array3[0], "NULL") {
  264. array3[0] = doQuoteWord(array3[0], "", "")
  265. } else {
  266. array3[0] = doQuoteWord(array3[0], charLeft, charRight)
  267. }
  268. } else if len(array3) >= 2 {
  269. array3[0] = doQuoteWord(array3[0], charLeft, charRight)
  270. // Note:
  271. // mysql: u.uid
  272. // mssql double dots: Database..Table
  273. array3[len(array3)-1] = doQuoteWord(array3[len(array3)-1], charLeft, charRight)
  274. }
  275. array2[0] = gstr.Join(array3, ".")
  276. array1[k1] = gstr.Join(array2, " ")
  277. }
  278. return gstr.Join(array1, ",")
  279. }
  280. func getFieldsFromStructOrMap(structOrMap interface{}) (fields []string) {
  281. fields = []string{}
  282. if utils.IsStruct(structOrMap) {
  283. structFields, _ := gstructs.Fields(gstructs.FieldsInput{
  284. Pointer: structOrMap,
  285. RecursiveOption: gstructs.RecursiveOptionEmbeddedNoTag,
  286. })
  287. for _, structField := range structFields {
  288. if tag := structField.Tag(OrmTagForStruct); tag != "" && gregex.IsMatchString(regularFieldNameRegPattern, tag) {
  289. fields = append(fields, tag)
  290. } else {
  291. fields = append(fields, structField.Name())
  292. }
  293. }
  294. } else {
  295. fields = gutil.Keys(structOrMap)
  296. }
  297. return
  298. }
  299. // GetPrimaryKeyCondition returns a new where condition by primary field name.
  300. // The optional parameter `where` is like follows:
  301. // 123 => primary=123
  302. // []int{1, 2, 3} => primary IN(1,2,3)
  303. // "john" => primary='john'
  304. // []string{"john", "smith"} => primary IN('john','smith')
  305. // g.Map{"id": g.Slice{1,2,3}} => id IN(1,2,3)
  306. // g.Map{"id": 1, "name": "john"} => id=1 AND name='john'
  307. // etc.
  308. //
  309. // Note that it returns the given `where` parameter directly if the `primary` is empty
  310. // or length of `where` > 1.
  311. func GetPrimaryKeyCondition(primary string, where ...interface{}) (newWhereCondition []interface{}) {
  312. if len(where) == 0 {
  313. return nil
  314. }
  315. if primary == "" {
  316. return where
  317. }
  318. if len(where) == 1 {
  319. var (
  320. rv = reflect.ValueOf(where[0])
  321. kind = rv.Kind()
  322. )
  323. if kind == reflect.Ptr {
  324. rv = rv.Elem()
  325. kind = rv.Kind()
  326. }
  327. switch kind {
  328. case reflect.Map, reflect.Struct:
  329. // Ignore the parameter `primary`.
  330. break
  331. default:
  332. return []interface{}{map[string]interface{}{
  333. primary: where[0],
  334. }}
  335. }
  336. }
  337. return where
  338. }
  339. // formatSql formats the sql string and its arguments before executing.
  340. // The internal handleArguments function might be called twice during the SQL procedure,
  341. // but do not worry about it, it's safe and efficient.
  342. func formatSql(sql string, args []interface{}) (newSql string, newArgs []interface{}) {
  343. // DO NOT do this as there may be multiple lines and comments in the sql.
  344. // sql = gstr.Trim(sql)
  345. // sql = gstr.Replace(sql, "\n", " ")
  346. // sql, _ = gregex.ReplaceString(`\s{2,}`, ` `, sql)
  347. return handleArguments(sql, args)
  348. }
  349. type formatWhereHolderInput struct {
  350. WhereHolder
  351. OmitNil bool
  352. OmitEmpty bool
  353. Schema string
  354. Table string // Table is used for fields mapping and filtering internally.
  355. }
  356. func isKeyValueCanBeOmitEmpty(omitEmpty bool, whereType string, key, value interface{}) bool {
  357. if !omitEmpty {
  358. return false
  359. }
  360. // Eg:
  361. // Where("id", []int{}).All() -> SELECT xxx FROM xxx WHERE 0=1
  362. // Where("name", "").All() -> SELECT xxx FROM xxx WHERE `name`=''
  363. // OmitEmpty().Where("id", []int{}).All() -> SELECT xxx FROM xxx
  364. // OmitEmpty().Where("name", "").All() -> SELECT xxx FROM xxx
  365. // OmitEmpty().Where("1").All() -> SELECT xxx FROM xxx WHERE 1
  366. switch whereType {
  367. case whereHolderTypeNoArgs:
  368. return false
  369. case whereHolderTypeIn:
  370. return gutil.IsEmpty(value)
  371. default:
  372. if gstr.Count(gconv.String(key), "?") == 0 && gutil.IsEmpty(value) {
  373. return true
  374. }
  375. }
  376. return false
  377. }
  378. // formatWhereHolder formats where statement and its arguments for `Where` and `Having` statements.
  379. func formatWhereHolder(ctx context.Context, db DB, in formatWhereHolderInput) (newWhere string, newArgs []interface{}) {
  380. var (
  381. buffer = bytes.NewBuffer(nil)
  382. reflectInfo = reflection.OriginValueAndKind(in.Where)
  383. )
  384. switch reflectInfo.OriginKind {
  385. case reflect.Array, reflect.Slice:
  386. newArgs = formatWhereInterfaces(db, gconv.Interfaces(in.Where), buffer, newArgs)
  387. case reflect.Map:
  388. for key, value := range DataToMapDeep(in.Where) {
  389. if in.OmitNil && empty.IsNil(value) {
  390. continue
  391. }
  392. if in.OmitEmpty && empty.IsEmpty(value) {
  393. continue
  394. }
  395. newArgs = formatWhereKeyValue(formatWhereKeyValueInput{
  396. Db: db,
  397. Buffer: buffer,
  398. Args: newArgs,
  399. Key: key,
  400. Value: value,
  401. Prefix: in.Prefix,
  402. Type: in.Type,
  403. })
  404. }
  405. case reflect.Struct:
  406. // If the `where` parameter is `DO` struct, it then adds `OmitNil` option for this condition,
  407. // which will filter all nil parameters in `where`.
  408. if isDoStruct(in.Where) {
  409. in.OmitNil = true
  410. }
  411. // If `where` struct implements `iIterator` interface,
  412. // it then uses its Iterate function to iterate its key-value pairs.
  413. // For example, ListMap and TreeMap are ordered map,
  414. // which implement `iIterator` interface and are index-friendly for where conditions.
  415. if iterator, ok := in.Where.(iIterator); ok {
  416. iterator.Iterator(func(key, value interface{}) bool {
  417. ketStr := gconv.String(key)
  418. if in.OmitNil && empty.IsNil(value) {
  419. return true
  420. }
  421. if in.OmitEmpty && empty.IsEmpty(value) {
  422. return true
  423. }
  424. newArgs = formatWhereKeyValue(formatWhereKeyValueInput{
  425. Db: db,
  426. Buffer: buffer,
  427. Args: newArgs,
  428. Key: ketStr,
  429. Value: value,
  430. OmitEmpty: in.OmitEmpty,
  431. Prefix: in.Prefix,
  432. Type: in.Type,
  433. })
  434. return true
  435. })
  436. break
  437. }
  438. // Automatically mapping and filtering the struct attribute.
  439. var (
  440. reflectType = reflectInfo.OriginValue.Type()
  441. structField reflect.StructField
  442. data = DataToMapDeep(in.Where)
  443. )
  444. // If `Prefix` is given, it checks and retrieves the table name.
  445. if in.Prefix != "" {
  446. hasTable, _ := db.GetCore().HasTable(in.Prefix)
  447. if hasTable {
  448. in.Table = in.Prefix
  449. } else {
  450. ormTagTableName := getTableNameFromOrmTag(in.Where)
  451. if ormTagTableName != "" {
  452. in.Table = ormTagTableName
  453. }
  454. }
  455. }
  456. // Mapping and filtering fields if `Table` is given.
  457. if in.Table != "" {
  458. data, _ = db.GetCore().mappingAndFilterData(ctx, in.Schema, in.Table, data, true)
  459. }
  460. // Put the struct attributes in sequence in Where statement.
  461. for i := 0; i < reflectType.NumField(); i++ {
  462. structField = reflectType.Field(i)
  463. foundKey, foundValue := gutil.MapPossibleItemByKey(data, structField.Name)
  464. if foundKey != "" {
  465. if in.OmitNil && empty.IsNil(foundValue) {
  466. continue
  467. }
  468. if in.OmitEmpty && empty.IsEmpty(foundValue) {
  469. continue
  470. }
  471. newArgs = formatWhereKeyValue(formatWhereKeyValueInput{
  472. Db: db,
  473. Buffer: buffer,
  474. Args: newArgs,
  475. Key: foundKey,
  476. Value: foundValue,
  477. OmitEmpty: in.OmitEmpty,
  478. Prefix: in.Prefix,
  479. Type: in.Type,
  480. })
  481. }
  482. }
  483. default:
  484. // Where filter.
  485. var omitEmptyCheckValue interface{}
  486. if len(in.Args) == 1 {
  487. omitEmptyCheckValue = in.Args[0]
  488. } else {
  489. omitEmptyCheckValue = in.Args
  490. }
  491. if isKeyValueCanBeOmitEmpty(in.OmitEmpty, in.Type, in.Where, omitEmptyCheckValue) {
  492. return
  493. }
  494. // Usually a string.
  495. whereStr := gstr.Trim(gconv.String(in.Where))
  496. // Is `whereStr` a field name which composed as a key-value condition?
  497. // Eg:
  498. // Where("id", 1)
  499. // Where("id", g.Slice{1,2,3})
  500. if gregex.IsMatchString(regularFieldNameWithoutDotRegPattern, whereStr) && len(in.Args) == 1 {
  501. newArgs = formatWhereKeyValue(formatWhereKeyValueInput{
  502. Db: db,
  503. Buffer: buffer,
  504. Args: newArgs,
  505. Key: whereStr,
  506. Value: in.Args[0],
  507. OmitEmpty: in.OmitEmpty,
  508. Prefix: in.Prefix,
  509. Type: in.Type,
  510. })
  511. in.Args = in.Args[:0]
  512. break
  513. }
  514. // If the first part is column name, it automatically adds prefix to the column.
  515. if in.Prefix != "" {
  516. array := gstr.Split(whereStr, " ")
  517. if ok, _ := db.GetCore().HasField(ctx, in.Table, array[0]); ok {
  518. whereStr = in.Prefix + "." + whereStr
  519. }
  520. }
  521. // Regular string and parameter place holder handling.
  522. // Eg:
  523. // Where("id in(?) and name=?", g.Slice{1,2,3}, "john")
  524. i := 0
  525. for {
  526. if i >= len(in.Args) {
  527. break
  528. }
  529. // Sub query, which is always used along with a string condition.
  530. if model, ok := in.Args[i].(*Model); ok {
  531. index := -1
  532. whereStr, _ = gregex.ReplaceStringFunc(`(\?)`, whereStr, func(s string) string {
  533. index++
  534. if i+len(newArgs) == index {
  535. sqlWithHolder, holderArgs := model.getFormattedSqlAndArgs(
  536. ctx, queryTypeNormal, false,
  537. )
  538. newArgs = append(newArgs, holderArgs...)
  539. // Automatically adding the brackets.
  540. return "(" + sqlWithHolder + ")"
  541. }
  542. return s
  543. })
  544. in.Args = gutil.SliceDelete(in.Args, i)
  545. continue
  546. }
  547. i++
  548. }
  549. buffer.WriteString(whereStr)
  550. }
  551. if buffer.Len() == 0 {
  552. return "", in.Args
  553. }
  554. if len(in.Args) > 0 {
  555. newArgs = append(newArgs, in.Args...)
  556. }
  557. newWhere = buffer.String()
  558. if len(newArgs) > 0 {
  559. if gstr.Pos(newWhere, "?") == -1 {
  560. if gregex.IsMatchString(lastOperatorRegPattern, newWhere) {
  561. // Eg: Where/And/Or("uid>=", 1)
  562. newWhere += "?"
  563. } else if gregex.IsMatchString(regularFieldNameRegPattern, newWhere) {
  564. newWhere = db.GetCore().QuoteString(newWhere)
  565. if len(newArgs) > 0 {
  566. if utils.IsArray(newArgs[0]) {
  567. // Eg:
  568. // Where("id", []int{1,2,3})
  569. // Where("user.id", []int{1,2,3})
  570. newWhere += " IN (?)"
  571. } else if empty.IsNil(newArgs[0]) {
  572. // Eg:
  573. // Where("id", nil)
  574. // Where("user.id", nil)
  575. newWhere += " IS NULL"
  576. newArgs = nil
  577. } else {
  578. // Eg:
  579. // Where/And/Or("uid", 1)
  580. // Where/And/Or("user.uid", 1)
  581. newWhere += "=?"
  582. }
  583. }
  584. }
  585. }
  586. }
  587. return handleArguments(newWhere, newArgs)
  588. }
  589. // formatWhereInterfaces formats `where` as []interface{}.
  590. func formatWhereInterfaces(db DB, where []interface{}, buffer *bytes.Buffer, newArgs []interface{}) []interface{} {
  591. if len(where) == 0 {
  592. return newArgs
  593. }
  594. if len(where)%2 != 0 {
  595. buffer.WriteString(gstr.Join(gconv.Strings(where), ""))
  596. return newArgs
  597. }
  598. var str string
  599. for i := 0; i < len(where); i += 2 {
  600. str = gconv.String(where[i])
  601. if buffer.Len() > 0 {
  602. buffer.WriteString(" AND " + db.GetCore().QuoteWord(str) + "=?")
  603. } else {
  604. buffer.WriteString(db.GetCore().QuoteWord(str) + "=?")
  605. }
  606. if s, ok := where[i+1].(Raw); ok {
  607. buffer.WriteString(gconv.String(s))
  608. } else {
  609. newArgs = append(newArgs, where[i+1])
  610. }
  611. }
  612. return newArgs
  613. }
  614. type formatWhereKeyValueInput struct {
  615. Db DB // Db is the underlying DB object for current operation.
  616. Buffer *bytes.Buffer // Buffer is the sql statement string without Args for current operation.
  617. Args []interface{} // Args is the full arguments of current operation.
  618. Key string // The field name, eg: "id", "name", etc.
  619. Value interface{} // The field value, can be any types.
  620. Type string // The value in Where type.
  621. OmitEmpty bool // Ignores current condition key if `value` is empty.
  622. Prefix string // Field prefix, eg: "user", "order", etc.
  623. }
  624. // formatWhereKeyValue handles each key-value pair of the parameter map.
  625. func formatWhereKeyValue(in formatWhereKeyValueInput) (newArgs []interface{}) {
  626. var (
  627. quotedKey = in.Db.GetCore().QuoteWord(in.Key)
  628. holderCount = gstr.Count(quotedKey, "?")
  629. )
  630. if isKeyValueCanBeOmitEmpty(in.OmitEmpty, in.Type, quotedKey, in.Value) {
  631. return in.Args
  632. }
  633. if in.Prefix != "" && !gstr.Contains(quotedKey, ".") {
  634. quotedKey = in.Prefix + "." + quotedKey
  635. }
  636. if in.Buffer.Len() > 0 {
  637. in.Buffer.WriteString(" AND ")
  638. }
  639. // If the value is type of slice, and there's only one '?' holder in
  640. // the key string, it automatically adds '?' holder chars according to its arguments count
  641. // and converts it to "IN" statement.
  642. var (
  643. reflectValue = reflect.ValueOf(in.Value)
  644. reflectKind = reflectValue.Kind()
  645. )
  646. switch reflectKind {
  647. // Slice argument.
  648. case reflect.Slice, reflect.Array:
  649. if holderCount == 0 {
  650. in.Buffer.WriteString(quotedKey + " IN(?)")
  651. in.Args = append(in.Args, in.Value)
  652. } else {
  653. if holderCount != reflectValue.Len() {
  654. in.Buffer.WriteString(quotedKey)
  655. in.Args = append(in.Args, in.Value)
  656. } else {
  657. in.Buffer.WriteString(quotedKey)
  658. in.Args = append(in.Args, gconv.Interfaces(in.Value)...)
  659. }
  660. }
  661. default:
  662. if in.Value == nil || empty.IsNil(reflectValue) {
  663. if gregex.IsMatchString(regularFieldNameRegPattern, in.Key) {
  664. // The key is a single field name.
  665. in.Buffer.WriteString(quotedKey + " IS NULL")
  666. } else {
  667. // The key may have operation chars.
  668. in.Buffer.WriteString(quotedKey)
  669. }
  670. } else {
  671. // It also supports "LIKE" statement, which we consider it an operator.
  672. quotedKey = gstr.Trim(quotedKey)
  673. if gstr.Pos(quotedKey, "?") == -1 {
  674. like := " LIKE"
  675. if len(quotedKey) > len(like) && gstr.Equal(quotedKey[len(quotedKey)-len(like):], like) {
  676. // Eg: Where(g.Map{"name like": "john%"})
  677. in.Buffer.WriteString(quotedKey + " ?")
  678. } else if gregex.IsMatchString(lastOperatorRegPattern, quotedKey) {
  679. // Eg: Where(g.Map{"age > ": 16})
  680. in.Buffer.WriteString(quotedKey + " ?")
  681. } else if gregex.IsMatchString(regularFieldNameRegPattern, in.Key) {
  682. // The key is a regular field name.
  683. in.Buffer.WriteString(quotedKey + "=?")
  684. } else {
  685. // The key is not a regular field name.
  686. // Eg: Where(g.Map{"age > 16": nil})
  687. // Issue: https://github.com/gogf/gf/issues/765
  688. if empty.IsEmpty(in.Value) {
  689. in.Buffer.WriteString(quotedKey)
  690. break
  691. } else {
  692. in.Buffer.WriteString(quotedKey + "=?")
  693. }
  694. }
  695. } else {
  696. in.Buffer.WriteString(quotedKey)
  697. }
  698. if s, ok := in.Value.(Raw); ok {
  699. in.Buffer.WriteString(gconv.String(s))
  700. } else {
  701. in.Args = append(in.Args, in.Value)
  702. }
  703. }
  704. }
  705. return in.Args
  706. }
  707. // handleArguments is an important function, which handles the sql and all its arguments
  708. // before committing them to underlying driver.
  709. func handleArguments(sql string, args []interface{}) (newSql string, newArgs []interface{}) {
  710. newSql = sql
  711. // insertHolderCount is used to calculate the inserting position for the '?' holder.
  712. insertHolderCount := 0
  713. // Handles the slice arguments.
  714. if len(args) > 0 {
  715. for index, arg := range args {
  716. reflectInfo := reflection.OriginValueAndKind(arg)
  717. switch reflectInfo.OriginKind {
  718. case reflect.Slice, reflect.Array:
  719. // It does not split the type of []byte.
  720. // Eg: table.Where("name = ?", []byte("john"))
  721. if _, ok := arg.([]byte); ok {
  722. newArgs = append(newArgs, arg)
  723. continue
  724. }
  725. if reflectInfo.OriginValue.Len() == 0 {
  726. // Empty slice argument, it converts the sql to a false sql.
  727. // Eg:
  728. // Query("select * from xxx where id in(?)", g.Slice{}) -> select * from xxx where 0=1
  729. // Where("id in(?)", g.Slice{}) -> WHERE 0=1
  730. if gstr.Contains(newSql, "?") {
  731. whereKeyWord := " WHERE "
  732. if p := gstr.PosI(newSql, whereKeyWord); p == -1 {
  733. return "0=1", []interface{}{}
  734. } else {
  735. return gstr.SubStr(newSql, 0, p+len(whereKeyWord)) + "0=1", []interface{}{}
  736. }
  737. }
  738. } else {
  739. for i := 0; i < reflectInfo.OriginValue.Len(); i++ {
  740. newArgs = append(newArgs, reflectInfo.OriginValue.Index(i).Interface())
  741. }
  742. }
  743. // If the '?' holder count equals the length of the slice,
  744. // it does not implement the arguments splitting logic.
  745. // Eg: db.Query("SELECT ?+?", g.Slice{1, 2})
  746. if len(args) == 1 && gstr.Count(newSql, "?") == reflectInfo.OriginValue.Len() {
  747. break
  748. }
  749. // counter is used to finding the inserting position for the '?' holder.
  750. var (
  751. counter = 0
  752. replaced = false
  753. )
  754. newSql, _ = gregex.ReplaceStringFunc(`\?`, newSql, func(s string) string {
  755. if replaced {
  756. return s
  757. }
  758. counter++
  759. if counter == index+insertHolderCount+1 {
  760. replaced = true
  761. insertHolderCount += reflectInfo.OriginValue.Len() - 1
  762. return "?" + strings.Repeat(",?", reflectInfo.OriginValue.Len()-1)
  763. }
  764. return s
  765. })
  766. // Special struct handling.
  767. case reflect.Struct:
  768. switch arg.(type) {
  769. // The underlying driver supports time.Time/*time.Time types.
  770. case time.Time, *time.Time:
  771. newArgs = append(newArgs, arg)
  772. continue
  773. case gtime.Time:
  774. newArgs = append(newArgs, arg.(gtime.Time).Time)
  775. continue
  776. case *gtime.Time:
  777. newArgs = append(newArgs, arg.(*gtime.Time).Time)
  778. continue
  779. default:
  780. // It converts the struct to string in default
  781. // if it has implemented the String interface.
  782. if v, ok := arg.(iString); ok {
  783. newArgs = append(newArgs, v.String())
  784. continue
  785. }
  786. }
  787. newArgs = append(newArgs, arg)
  788. default:
  789. newArgs = append(newArgs, arg)
  790. }
  791. }
  792. }
  793. return
  794. }
  795. // FormatSqlWithArgs binds the arguments to the sql string and returns a complete
  796. // sql string, just for debugging.
  797. func FormatSqlWithArgs(sql string, args []interface{}) string {
  798. index := -1
  799. newQuery, _ := gregex.ReplaceStringFunc(
  800. `(\?|:v\d+|\$\d+|@p\d+)`,
  801. sql,
  802. func(s string) string {
  803. index++
  804. if len(args) > index {
  805. if args[index] == nil {
  806. return "null"
  807. }
  808. // Parameters of type Raw do not require special treatment
  809. if v, ok := args[index].(Raw); ok {
  810. return gconv.String(v)
  811. }
  812. reflectInfo := reflection.OriginValueAndKind(args[index])
  813. if reflectInfo.OriginKind == reflect.Ptr &&
  814. (reflectInfo.OriginValue.IsNil() || !reflectInfo.OriginValue.IsValid()) {
  815. return "null"
  816. }
  817. switch reflectInfo.OriginKind {
  818. case reflect.String, reflect.Map, reflect.Slice, reflect.Array:
  819. return `'` + gstr.QuoteMeta(gconv.String(args[index]), `'`) + `'`
  820. case reflect.Struct:
  821. if t, ok := args[index].(time.Time); ok {
  822. return `'` + t.Format(`2006-01-02 15:04:05`) + `'`
  823. }
  824. return `'` + gstr.QuoteMeta(gconv.String(args[index]), `'`) + `'`
  825. }
  826. return gconv.String(args[index])
  827. }
  828. return s
  829. })
  830. return newQuery
  831. }