statement.go 38 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407
  1. // Copyright 2015 The Xorm Authors. All rights reserved.
  2. // Use of this source code is governed by a BSD-style
  3. // license that can be found in the LICENSE file.
  4. package xorm
  5. import (
  6. "bytes"
  7. "database/sql/driver"
  8. "encoding/json"
  9. "errors"
  10. "fmt"
  11. "reflect"
  12. "strings"
  13. "time"
  14. "github.com/go-xorm/builder"
  15. "github.com/go-xorm/core"
  16. )
  17. type incrParam struct {
  18. colName string
  19. arg interface{}
  20. }
  21. type decrParam struct {
  22. colName string
  23. arg interface{}
  24. }
  25. type exprParam struct {
  26. colName string
  27. expr string
  28. }
  29. // Statement save all the sql info for executing SQL
  30. type Statement struct {
  31. RefTable *core.Table
  32. Engine *Engine
  33. Start int
  34. LimitN int
  35. idParam *core.PK
  36. OrderStr string
  37. JoinStr string
  38. joinArgs []interface{}
  39. GroupByStr string
  40. HavingStr string
  41. ColumnStr string
  42. selectStr string
  43. columnMap map[string]bool
  44. useAllCols bool
  45. OmitStr string
  46. AltTableName string
  47. tableName string
  48. RawSQL string
  49. RawParams []interface{}
  50. UseCascade bool
  51. UseAutoJoin bool
  52. StoreEngine string
  53. Charset string
  54. UseCache bool
  55. UseAutoTime bool
  56. noAutoCondition bool
  57. IsDistinct bool
  58. IsForUpdate bool
  59. TableAlias string
  60. allUseBool bool
  61. checkVersion bool
  62. unscoped bool
  63. mustColumnMap map[string]bool
  64. nullableMap map[string]bool
  65. incrColumns map[string]incrParam
  66. decrColumns map[string]decrParam
  67. exprColumns map[string]exprParam
  68. cond builder.Cond
  69. }
  70. // Init reset all the statement's fields
  71. func (statement *Statement) Init() {
  72. statement.RefTable = nil
  73. statement.Start = 0
  74. statement.LimitN = 0
  75. statement.OrderStr = ""
  76. statement.UseCascade = true
  77. statement.JoinStr = ""
  78. statement.joinArgs = make([]interface{}, 0)
  79. statement.GroupByStr = ""
  80. statement.HavingStr = ""
  81. statement.ColumnStr = ""
  82. statement.OmitStr = ""
  83. statement.columnMap = make(map[string]bool)
  84. statement.AltTableName = ""
  85. statement.tableName = ""
  86. statement.idParam = nil
  87. statement.RawSQL = ""
  88. statement.RawParams = make([]interface{}, 0)
  89. statement.UseCache = true
  90. statement.UseAutoTime = true
  91. statement.noAutoCondition = false
  92. statement.IsDistinct = false
  93. statement.IsForUpdate = false
  94. statement.TableAlias = ""
  95. statement.selectStr = ""
  96. statement.allUseBool = false
  97. statement.useAllCols = false
  98. statement.mustColumnMap = make(map[string]bool)
  99. statement.nullableMap = make(map[string]bool)
  100. statement.checkVersion = true
  101. statement.unscoped = false
  102. statement.incrColumns = make(map[string]incrParam)
  103. statement.decrColumns = make(map[string]decrParam)
  104. statement.exprColumns = make(map[string]exprParam)
  105. statement.cond = builder.NewCond()
  106. }
  107. // NoAutoCondition if you do not want convert bean's field as query condition, then use this function
  108. func (statement *Statement) NoAutoCondition(no ...bool) *Statement {
  109. statement.noAutoCondition = true
  110. if len(no) > 0 {
  111. statement.noAutoCondition = no[0]
  112. }
  113. return statement
  114. }
  115. // Alias set the table alias
  116. func (statement *Statement) Alias(alias string) *Statement {
  117. statement.TableAlias = alias
  118. return statement
  119. }
  120. // SQL adds raw sql statement
  121. func (statement *Statement) SQL(query interface{}, args ...interface{}) *Statement {
  122. switch query.(type) {
  123. case (*builder.Builder):
  124. var err error
  125. statement.RawSQL, statement.RawParams, err = query.(*builder.Builder).ToSQL()
  126. if err != nil {
  127. statement.Engine.logger.Error(err)
  128. }
  129. case string:
  130. statement.RawSQL = query.(string)
  131. statement.RawParams = args
  132. default:
  133. statement.Engine.logger.Error("unsupported sql type")
  134. }
  135. return statement
  136. }
  137. // Where add Where statement
  138. func (statement *Statement) Where(query interface{}, args ...interface{}) *Statement {
  139. return statement.And(query, args...)
  140. }
  141. // And add Where & and statement
  142. func (statement *Statement) And(query interface{}, args ...interface{}) *Statement {
  143. switch query.(type) {
  144. case string:
  145. cond := builder.Expr(query.(string), args...)
  146. statement.cond = statement.cond.And(cond)
  147. case builder.Cond:
  148. cond := query.(builder.Cond)
  149. statement.cond = statement.cond.And(cond)
  150. for _, v := range args {
  151. if vv, ok := v.(builder.Cond); ok {
  152. statement.cond = statement.cond.And(vv)
  153. }
  154. }
  155. default:
  156. // TODO: not support condition type
  157. }
  158. return statement
  159. }
  160. // Or add Where & Or statement
  161. func (statement *Statement) Or(query interface{}, args ...interface{}) *Statement {
  162. switch query.(type) {
  163. case string:
  164. cond := builder.Expr(query.(string), args...)
  165. statement.cond = statement.cond.Or(cond)
  166. case builder.Cond:
  167. cond := query.(builder.Cond)
  168. statement.cond = statement.cond.Or(cond)
  169. for _, v := range args {
  170. if vv, ok := v.(builder.Cond); ok {
  171. statement.cond = statement.cond.Or(vv)
  172. }
  173. }
  174. default:
  175. // TODO: not support condition type
  176. }
  177. return statement
  178. }
  179. // In generate "Where column IN (?) " statement
  180. func (statement *Statement) In(column string, args ...interface{}) *Statement {
  181. in := builder.In(statement.Engine.Quote(column), args...)
  182. statement.cond = statement.cond.And(in)
  183. return statement
  184. }
  185. // NotIn generate "Where column NOT IN (?) " statement
  186. func (statement *Statement) NotIn(column string, args ...interface{}) *Statement {
  187. notIn := builder.NotIn(statement.Engine.Quote(column), args...)
  188. statement.cond = statement.cond.And(notIn)
  189. return statement
  190. }
  191. func (statement *Statement) setRefValue(v reflect.Value) error {
  192. var err error
  193. statement.RefTable, err = statement.Engine.autoMapType(reflect.Indirect(v))
  194. if err != nil {
  195. return err
  196. }
  197. statement.tableName = statement.Engine.tbName(v)
  198. return nil
  199. }
  200. // Table tempororily set table name, the parameter could be a string or a pointer of struct
  201. func (statement *Statement) Table(tableNameOrBean interface{}) *Statement {
  202. v := rValue(tableNameOrBean)
  203. t := v.Type()
  204. if t.Kind() == reflect.String {
  205. statement.AltTableName = tableNameOrBean.(string)
  206. } else if t.Kind() == reflect.Struct {
  207. var err error
  208. statement.RefTable, err = statement.Engine.autoMapType(v)
  209. if err != nil {
  210. statement.Engine.logger.Error(err)
  211. return statement
  212. }
  213. statement.AltTableName = statement.Engine.tbName(v)
  214. }
  215. return statement
  216. }
  217. // Auto generating update columnes and values according a struct
  218. func buildUpdates(engine *Engine, table *core.Table, bean interface{},
  219. includeVersion bool, includeUpdated bool, includeNil bool,
  220. includeAutoIncr bool, allUseBool bool, useAllCols bool,
  221. mustColumnMap map[string]bool, nullableMap map[string]bool,
  222. columnMap map[string]bool, update, unscoped bool) ([]string, []interface{}) {
  223. var colNames = make([]string, 0)
  224. var args = make([]interface{}, 0)
  225. for _, col := range table.Columns() {
  226. if !includeVersion && col.IsVersion {
  227. continue
  228. }
  229. if col.IsCreated {
  230. continue
  231. }
  232. if !includeUpdated && col.IsUpdated {
  233. continue
  234. }
  235. if !includeAutoIncr && col.IsAutoIncrement {
  236. continue
  237. }
  238. if col.IsDeleted && !unscoped {
  239. continue
  240. }
  241. if use, ok := columnMap[strings.ToLower(col.Name)]; ok && !use {
  242. continue
  243. }
  244. fieldValuePtr, err := col.ValueOf(bean)
  245. if err != nil {
  246. engine.logger.Error(err)
  247. continue
  248. }
  249. fieldValue := *fieldValuePtr
  250. fieldType := reflect.TypeOf(fieldValue.Interface())
  251. requiredField := useAllCols
  252. includeNil := useAllCols
  253. if b, ok := getFlagForColumn(mustColumnMap, col); ok {
  254. if b {
  255. requiredField = true
  256. } else {
  257. continue
  258. }
  259. }
  260. // !evalphobia! set fieldValue as nil when column is nullable and zero-value
  261. if b, ok := getFlagForColumn(nullableMap, col); ok {
  262. if b && col.Nullable && isZero(fieldValue.Interface()) {
  263. var nilValue *int
  264. fieldValue = reflect.ValueOf(nilValue)
  265. fieldType = reflect.TypeOf(fieldValue.Interface())
  266. includeNil = true
  267. }
  268. }
  269. var val interface{}
  270. if fieldValue.CanAddr() {
  271. if structConvert, ok := fieldValue.Addr().Interface().(core.Conversion); ok {
  272. data, err := structConvert.ToDB()
  273. if err != nil {
  274. engine.logger.Error(err)
  275. } else {
  276. val = data
  277. }
  278. goto APPEND
  279. }
  280. }
  281. if structConvert, ok := fieldValue.Interface().(core.Conversion); ok {
  282. data, err := structConvert.ToDB()
  283. if err != nil {
  284. engine.logger.Error(err)
  285. } else {
  286. val = data
  287. }
  288. goto APPEND
  289. }
  290. if fieldType.Kind() == reflect.Ptr {
  291. if fieldValue.IsNil() {
  292. if includeNil {
  293. args = append(args, nil)
  294. colNames = append(colNames, fmt.Sprintf("%v=?", engine.Quote(col.Name)))
  295. }
  296. continue
  297. } else if !fieldValue.IsValid() {
  298. continue
  299. } else {
  300. // dereference ptr type to instance type
  301. fieldValue = fieldValue.Elem()
  302. fieldType = reflect.TypeOf(fieldValue.Interface())
  303. requiredField = true
  304. }
  305. }
  306. switch fieldType.Kind() {
  307. case reflect.Bool:
  308. if allUseBool || requiredField {
  309. val = fieldValue.Interface()
  310. } else {
  311. // if a bool in a struct, it will not be as a condition because it default is false,
  312. // please use Where() instead
  313. continue
  314. }
  315. case reflect.String:
  316. if !requiredField && fieldValue.String() == "" {
  317. continue
  318. }
  319. // for MyString, should convert to string or panic
  320. if fieldType.String() != reflect.String.String() {
  321. val = fieldValue.String()
  322. } else {
  323. val = fieldValue.Interface()
  324. }
  325. case reflect.Int8, reflect.Int16, reflect.Int, reflect.Int32, reflect.Int64:
  326. if !requiredField && fieldValue.Int() == 0 {
  327. continue
  328. }
  329. val = fieldValue.Interface()
  330. case reflect.Float32, reflect.Float64:
  331. if !requiredField && fieldValue.Float() == 0.0 {
  332. continue
  333. }
  334. val = fieldValue.Interface()
  335. case reflect.Uint8, reflect.Uint16, reflect.Uint, reflect.Uint32, reflect.Uint64:
  336. if !requiredField && fieldValue.Uint() == 0 {
  337. continue
  338. }
  339. t := int64(fieldValue.Uint())
  340. val = reflect.ValueOf(&t).Interface()
  341. case reflect.Struct:
  342. if fieldType.ConvertibleTo(core.TimeType) {
  343. t := fieldValue.Convert(core.TimeType).Interface().(time.Time)
  344. if !requiredField && (t.IsZero() || !fieldValue.IsValid()) {
  345. continue
  346. }
  347. val = engine.formatColTime(col, t)
  348. } else if nulType, ok := fieldValue.Interface().(driver.Valuer); ok {
  349. val, _ = nulType.Value()
  350. } else {
  351. if !col.SQLType.IsJson() {
  352. engine.autoMapType(fieldValue)
  353. if table, ok := engine.Tables[fieldValue.Type()]; ok {
  354. if len(table.PrimaryKeys) == 1 {
  355. pkField := reflect.Indirect(fieldValue).FieldByName(table.PKColumns()[0].FieldName)
  356. // fix non-int pk issues
  357. if pkField.IsValid() && (!requiredField && !isZero(pkField.Interface())) {
  358. val = pkField.Interface()
  359. } else {
  360. continue
  361. }
  362. } else {
  363. //TODO: how to handler?
  364. panic("not supported")
  365. }
  366. } else {
  367. val = fieldValue.Interface()
  368. }
  369. } else {
  370. // Blank struct could not be as update data
  371. if requiredField || !isStructZero(fieldValue) {
  372. bytes, err := json.Marshal(fieldValue.Interface())
  373. if err != nil {
  374. panic(fmt.Sprintf("mashal %v failed", fieldValue.Interface()))
  375. }
  376. if col.SQLType.IsText() {
  377. val = string(bytes)
  378. } else if col.SQLType.IsBlob() {
  379. val = bytes
  380. }
  381. } else {
  382. continue
  383. }
  384. }
  385. }
  386. case reflect.Array, reflect.Slice, reflect.Map:
  387. if !requiredField {
  388. if fieldValue == reflect.Zero(fieldType) {
  389. continue
  390. }
  391. if fieldType.Kind() == reflect.Array {
  392. if isArrayValueZero(fieldValue) {
  393. continue
  394. }
  395. } else if fieldValue.IsNil() || !fieldValue.IsValid() || fieldValue.Len() == 0 {
  396. continue
  397. }
  398. }
  399. if col.SQLType.IsText() {
  400. bytes, err := json.Marshal(fieldValue.Interface())
  401. if err != nil {
  402. engine.logger.Error(err)
  403. continue
  404. }
  405. val = string(bytes)
  406. } else if col.SQLType.IsBlob() {
  407. var bytes []byte
  408. var err error
  409. if fieldType.Kind() == reflect.Slice &&
  410. fieldType.Elem().Kind() == reflect.Uint8 {
  411. if fieldValue.Len() > 0 {
  412. val = fieldValue.Bytes()
  413. } else {
  414. continue
  415. }
  416. } else if fieldType.Kind() == reflect.Array &&
  417. fieldType.Elem().Kind() == reflect.Uint8 {
  418. val = fieldValue.Slice(0, 0).Interface()
  419. } else {
  420. bytes, err = json.Marshal(fieldValue.Interface())
  421. if err != nil {
  422. engine.logger.Error(err)
  423. continue
  424. }
  425. val = bytes
  426. }
  427. } else {
  428. continue
  429. }
  430. default:
  431. val = fieldValue.Interface()
  432. }
  433. APPEND:
  434. args = append(args, val)
  435. if col.IsPrimaryKey && engine.dialect.DBType() == "ql" {
  436. continue
  437. }
  438. colNames = append(colNames, fmt.Sprintf("%v = ?", engine.Quote(col.Name)))
  439. }
  440. return colNames, args
  441. }
  442. func (statement *Statement) needTableName() bool {
  443. return len(statement.JoinStr) > 0
  444. }
  445. func (statement *Statement) colName(col *core.Column, tableName string) string {
  446. if statement.needTableName() {
  447. var nm = tableName
  448. if len(statement.TableAlias) > 0 {
  449. nm = statement.TableAlias
  450. }
  451. return statement.Engine.Quote(nm) + "." + statement.Engine.Quote(col.Name)
  452. }
  453. return statement.Engine.Quote(col.Name)
  454. }
  455. func buildConds(engine *Engine, table *core.Table, bean interface{},
  456. includeVersion bool, includeUpdated bool, includeNil bool,
  457. includeAutoIncr bool, allUseBool bool, useAllCols bool, unscoped bool,
  458. mustColumnMap map[string]bool, tableName, aliasName string, addedTableName bool) (builder.Cond, error) {
  459. var conds []builder.Cond
  460. for _, col := range table.Columns() {
  461. if !includeVersion && col.IsVersion {
  462. continue
  463. }
  464. if !includeUpdated && col.IsUpdated {
  465. continue
  466. }
  467. if !includeAutoIncr && col.IsAutoIncrement {
  468. continue
  469. }
  470. if engine.dialect.DBType() == core.MSSQL && (col.SQLType.Name == core.Text || col.SQLType.IsBlob() || col.SQLType.Name == core.TimeStampz) {
  471. continue
  472. }
  473. if col.SQLType.IsJson() {
  474. continue
  475. }
  476. var colName string
  477. if addedTableName {
  478. var nm = tableName
  479. if len(aliasName) > 0 {
  480. nm = aliasName
  481. }
  482. colName = engine.Quote(nm) + "." + engine.Quote(col.Name)
  483. } else {
  484. colName = engine.Quote(col.Name)
  485. }
  486. fieldValuePtr, err := col.ValueOf(bean)
  487. if err != nil {
  488. engine.logger.Error(err)
  489. continue
  490. }
  491. if col.IsDeleted && !unscoped { // tag "deleted" is enabled
  492. if engine.dialect.DBType() == core.MSSQL {
  493. conds = append(conds, builder.IsNull{colName})
  494. } else {
  495. conds = append(conds, builder.IsNull{colName}.Or(builder.Eq{colName: "0001-01-01 00:00:00"}))
  496. }
  497. }
  498. fieldValue := *fieldValuePtr
  499. if fieldValue.Interface() == nil {
  500. continue
  501. }
  502. fieldType := reflect.TypeOf(fieldValue.Interface())
  503. requiredField := useAllCols
  504. if b, ok := getFlagForColumn(mustColumnMap, col); ok {
  505. if b {
  506. requiredField = true
  507. } else {
  508. continue
  509. }
  510. }
  511. if fieldType.Kind() == reflect.Ptr {
  512. if fieldValue.IsNil() {
  513. if includeNil {
  514. conds = append(conds, builder.Eq{colName: nil})
  515. }
  516. continue
  517. } else if !fieldValue.IsValid() {
  518. continue
  519. } else {
  520. // dereference ptr type to instance type
  521. fieldValue = fieldValue.Elem()
  522. fieldType = reflect.TypeOf(fieldValue.Interface())
  523. requiredField = true
  524. }
  525. }
  526. var val interface{}
  527. switch fieldType.Kind() {
  528. case reflect.Bool:
  529. if allUseBool || requiredField {
  530. val = fieldValue.Interface()
  531. } else {
  532. // if a bool in a struct, it will not be as a condition because it default is false,
  533. // please use Where() instead
  534. continue
  535. }
  536. case reflect.String:
  537. if !requiredField && fieldValue.String() == "" {
  538. continue
  539. }
  540. // for MyString, should convert to string or panic
  541. if fieldType.String() != reflect.String.String() {
  542. val = fieldValue.String()
  543. } else {
  544. val = fieldValue.Interface()
  545. }
  546. case reflect.Int8, reflect.Int16, reflect.Int, reflect.Int32, reflect.Int64:
  547. if !requiredField && fieldValue.Int() == 0 {
  548. continue
  549. }
  550. val = fieldValue.Interface()
  551. case reflect.Float32, reflect.Float64:
  552. if !requiredField && fieldValue.Float() == 0.0 {
  553. continue
  554. }
  555. val = fieldValue.Interface()
  556. case reflect.Uint8, reflect.Uint16, reflect.Uint, reflect.Uint32, reflect.Uint64:
  557. if !requiredField && fieldValue.Uint() == 0 {
  558. continue
  559. }
  560. t := int64(fieldValue.Uint())
  561. val = reflect.ValueOf(&t).Interface()
  562. case reflect.Struct:
  563. if fieldType.ConvertibleTo(core.TimeType) {
  564. t := fieldValue.Convert(core.TimeType).Interface().(time.Time)
  565. if !requiredField && (t.IsZero() || !fieldValue.IsValid()) {
  566. continue
  567. }
  568. val = engine.formatColTime(col, t)
  569. } else if _, ok := reflect.New(fieldType).Interface().(core.Conversion); ok {
  570. continue
  571. } else if valNul, ok := fieldValue.Interface().(driver.Valuer); ok {
  572. val, _ = valNul.Value()
  573. if val == nil {
  574. continue
  575. }
  576. } else {
  577. if col.SQLType.IsJson() {
  578. if col.SQLType.IsText() {
  579. bytes, err := json.Marshal(fieldValue.Interface())
  580. if err != nil {
  581. engine.logger.Error(err)
  582. continue
  583. }
  584. val = string(bytes)
  585. } else if col.SQLType.IsBlob() {
  586. var bytes []byte
  587. var err error
  588. bytes, err = json.Marshal(fieldValue.Interface())
  589. if err != nil {
  590. engine.logger.Error(err)
  591. continue
  592. }
  593. val = bytes
  594. }
  595. } else {
  596. engine.autoMapType(fieldValue)
  597. if table, ok := engine.Tables[fieldValue.Type()]; ok {
  598. if len(table.PrimaryKeys) == 1 {
  599. pkField := reflect.Indirect(fieldValue).FieldByName(table.PKColumns()[0].FieldName)
  600. // fix non-int pk issues
  601. //if pkField.Int() != 0 {
  602. if pkField.IsValid() && !isZero(pkField.Interface()) {
  603. val = pkField.Interface()
  604. } else {
  605. continue
  606. }
  607. } else {
  608. //TODO: how to handler?
  609. panic(fmt.Sprintln("not supported", fieldValue.Interface(), "as", table.PrimaryKeys))
  610. }
  611. } else {
  612. val = fieldValue.Interface()
  613. }
  614. }
  615. }
  616. case reflect.Array:
  617. continue
  618. case reflect.Slice, reflect.Map:
  619. if fieldValue == reflect.Zero(fieldType) {
  620. continue
  621. }
  622. if fieldValue.IsNil() || !fieldValue.IsValid() || fieldValue.Len() == 0 {
  623. continue
  624. }
  625. if col.SQLType.IsText() {
  626. bytes, err := json.Marshal(fieldValue.Interface())
  627. if err != nil {
  628. engine.logger.Error(err)
  629. continue
  630. }
  631. val = string(bytes)
  632. } else if col.SQLType.IsBlob() {
  633. var bytes []byte
  634. var err error
  635. if (fieldType.Kind() == reflect.Array || fieldType.Kind() == reflect.Slice) &&
  636. fieldType.Elem().Kind() == reflect.Uint8 {
  637. if fieldValue.Len() > 0 {
  638. val = fieldValue.Bytes()
  639. } else {
  640. continue
  641. }
  642. } else {
  643. bytes, err = json.Marshal(fieldValue.Interface())
  644. if err != nil {
  645. engine.logger.Error(err)
  646. continue
  647. }
  648. val = bytes
  649. }
  650. } else {
  651. continue
  652. }
  653. default:
  654. val = fieldValue.Interface()
  655. }
  656. conds = append(conds, builder.Eq{colName: val})
  657. }
  658. return builder.And(conds...), nil
  659. }
  660. // TableName return current tableName
  661. func (statement *Statement) TableName() string {
  662. if statement.AltTableName != "" {
  663. return statement.AltTableName
  664. }
  665. return statement.tableName
  666. }
  667. // ID generate "where id = ? " statement or for composite key "where key1 = ? and key2 = ?"
  668. func (statement *Statement) ID(id interface{}) *Statement {
  669. idValue := reflect.ValueOf(id)
  670. idType := reflect.TypeOf(idValue.Interface())
  671. switch idType {
  672. case ptrPkType:
  673. if pkPtr, ok := (id).(*core.PK); ok {
  674. statement.idParam = pkPtr
  675. return statement
  676. }
  677. case pkType:
  678. if pk, ok := (id).(core.PK); ok {
  679. statement.idParam = &pk
  680. return statement
  681. }
  682. }
  683. switch idType.Kind() {
  684. case reflect.String:
  685. statement.idParam = &core.PK{idValue.Convert(reflect.TypeOf("")).Interface()}
  686. return statement
  687. }
  688. statement.idParam = &core.PK{id}
  689. return statement
  690. }
  691. // Incr Generate "Update ... Set column = column + arg" statement
  692. func (statement *Statement) Incr(column string, arg ...interface{}) *Statement {
  693. k := strings.ToLower(column)
  694. if len(arg) > 0 {
  695. statement.incrColumns[k] = incrParam{column, arg[0]}
  696. } else {
  697. statement.incrColumns[k] = incrParam{column, 1}
  698. }
  699. return statement
  700. }
  701. // Decr Generate "Update ... Set column = column - arg" statement
  702. func (statement *Statement) Decr(column string, arg ...interface{}) *Statement {
  703. k := strings.ToLower(column)
  704. if len(arg) > 0 {
  705. statement.decrColumns[k] = decrParam{column, arg[0]}
  706. } else {
  707. statement.decrColumns[k] = decrParam{column, 1}
  708. }
  709. return statement
  710. }
  711. // SetExpr Generate "Update ... Set column = {expression}" statement
  712. func (statement *Statement) SetExpr(column string, expression string) *Statement {
  713. k := strings.ToLower(column)
  714. statement.exprColumns[k] = exprParam{column, expression}
  715. return statement
  716. }
  717. // Generate "Update ... Set column = column + arg" statement
  718. func (statement *Statement) getInc() map[string]incrParam {
  719. return statement.incrColumns
  720. }
  721. // Generate "Update ... Set column = column - arg" statement
  722. func (statement *Statement) getDec() map[string]decrParam {
  723. return statement.decrColumns
  724. }
  725. // Generate "Update ... Set column = {expression}" statement
  726. func (statement *Statement) getExpr() map[string]exprParam {
  727. return statement.exprColumns
  728. }
  729. func (statement *Statement) col2NewColsWithQuote(columns ...string) []string {
  730. newColumns := make([]string, 0)
  731. for _, col := range columns {
  732. col = strings.Replace(col, "`", "", -1)
  733. col = strings.Replace(col, statement.Engine.QuoteStr(), "", -1)
  734. ccols := strings.Split(col, ",")
  735. for _, c := range ccols {
  736. fields := strings.Split(strings.TrimSpace(c), ".")
  737. if len(fields) == 1 {
  738. newColumns = append(newColumns, statement.Engine.quote(fields[0]))
  739. } else if len(fields) == 2 {
  740. newColumns = append(newColumns, statement.Engine.quote(fields[0])+"."+
  741. statement.Engine.quote(fields[1]))
  742. } else {
  743. panic(errors.New("unwanted colnames"))
  744. }
  745. }
  746. }
  747. return newColumns
  748. }
  749. // Distinct generates "DISTINCT col1, col2 " statement
  750. func (statement *Statement) Distinct(columns ...string) *Statement {
  751. statement.IsDistinct = true
  752. statement.Cols(columns...)
  753. return statement
  754. }
  755. // ForUpdate generates "SELECT ... FOR UPDATE" statement
  756. func (statement *Statement) ForUpdate() *Statement {
  757. statement.IsForUpdate = true
  758. return statement
  759. }
  760. // Select replace select
  761. func (statement *Statement) Select(str string) *Statement {
  762. statement.selectStr = str
  763. return statement
  764. }
  765. // Cols generate "col1, col2" statement
  766. func (statement *Statement) Cols(columns ...string) *Statement {
  767. cols := col2NewCols(columns...)
  768. for _, nc := range cols {
  769. statement.columnMap[strings.ToLower(nc)] = true
  770. }
  771. newColumns := statement.col2NewColsWithQuote(columns...)
  772. statement.ColumnStr = strings.Join(newColumns, ", ")
  773. statement.ColumnStr = strings.Replace(statement.ColumnStr, statement.Engine.quote("*"), "*", -1)
  774. return statement
  775. }
  776. // AllCols update use only: update all columns
  777. func (statement *Statement) AllCols() *Statement {
  778. statement.useAllCols = true
  779. return statement
  780. }
  781. // MustCols update use only: must update columns
  782. func (statement *Statement) MustCols(columns ...string) *Statement {
  783. newColumns := col2NewCols(columns...)
  784. for _, nc := range newColumns {
  785. statement.mustColumnMap[strings.ToLower(nc)] = true
  786. }
  787. return statement
  788. }
  789. // UseBool indicates that use bool fields as update contents and query contiditions
  790. func (statement *Statement) UseBool(columns ...string) *Statement {
  791. if len(columns) > 0 {
  792. statement.MustCols(columns...)
  793. } else {
  794. statement.allUseBool = true
  795. }
  796. return statement
  797. }
  798. // Omit do not use the columns
  799. func (statement *Statement) Omit(columns ...string) {
  800. newColumns := col2NewCols(columns...)
  801. for _, nc := range newColumns {
  802. statement.columnMap[strings.ToLower(nc)] = false
  803. }
  804. statement.OmitStr = statement.Engine.Quote(strings.Join(newColumns, statement.Engine.Quote(", ")))
  805. }
  806. // Nullable Update use only: update columns to null when value is nullable and zero-value
  807. func (statement *Statement) Nullable(columns ...string) {
  808. newColumns := col2NewCols(columns...)
  809. for _, nc := range newColumns {
  810. statement.nullableMap[strings.ToLower(nc)] = true
  811. }
  812. }
  813. // Top generate LIMIT limit statement
  814. func (statement *Statement) Top(limit int) *Statement {
  815. statement.Limit(limit)
  816. return statement
  817. }
  818. // Limit generate LIMIT start, limit statement
  819. func (statement *Statement) Limit(limit int, start ...int) *Statement {
  820. statement.LimitN = limit
  821. if len(start) > 0 {
  822. statement.Start = start[0]
  823. }
  824. return statement
  825. }
  826. // OrderBy generate "Order By order" statement
  827. func (statement *Statement) OrderBy(order string) *Statement {
  828. if len(statement.OrderStr) > 0 {
  829. statement.OrderStr += ", "
  830. }
  831. statement.OrderStr += order
  832. return statement
  833. }
  834. // Desc generate `ORDER BY xx DESC`
  835. func (statement *Statement) Desc(colNames ...string) *Statement {
  836. var buf bytes.Buffer
  837. fmt.Fprintf(&buf, statement.OrderStr)
  838. if len(statement.OrderStr) > 0 {
  839. fmt.Fprint(&buf, ", ")
  840. }
  841. newColNames := statement.col2NewColsWithQuote(colNames...)
  842. fmt.Fprintf(&buf, "%v DESC", strings.Join(newColNames, " DESC, "))
  843. statement.OrderStr = buf.String()
  844. return statement
  845. }
  846. // Asc provide asc order by query condition, the input parameters are columns.
  847. func (statement *Statement) Asc(colNames ...string) *Statement {
  848. var buf bytes.Buffer
  849. fmt.Fprintf(&buf, statement.OrderStr)
  850. if len(statement.OrderStr) > 0 {
  851. fmt.Fprint(&buf, ", ")
  852. }
  853. newColNames := statement.col2NewColsWithQuote(colNames...)
  854. fmt.Fprintf(&buf, "%v ASC", strings.Join(newColNames, " ASC, "))
  855. statement.OrderStr = buf.String()
  856. return statement
  857. }
  858. // Join The joinOP should be one of INNER, LEFT OUTER, CROSS etc - this will be prepended to JOIN
  859. func (statement *Statement) Join(joinOP string, tablename interface{}, condition string, args ...interface{}) *Statement {
  860. var buf bytes.Buffer
  861. if len(statement.JoinStr) > 0 {
  862. fmt.Fprintf(&buf, "%v %v JOIN ", statement.JoinStr, joinOP)
  863. } else {
  864. fmt.Fprintf(&buf, "%v JOIN ", joinOP)
  865. }
  866. switch tablename.(type) {
  867. case []string:
  868. t := tablename.([]string)
  869. if len(t) > 1 {
  870. fmt.Fprintf(&buf, "%v AS %v", statement.Engine.Quote(t[0]), statement.Engine.Quote(t[1]))
  871. } else if len(t) == 1 {
  872. fmt.Fprintf(&buf, statement.Engine.Quote(t[0]))
  873. }
  874. case []interface{}:
  875. t := tablename.([]interface{})
  876. l := len(t)
  877. var table string
  878. if l > 0 {
  879. f := t[0]
  880. v := rValue(f)
  881. t := v.Type()
  882. if t.Kind() == reflect.String {
  883. table = f.(string)
  884. } else if t.Kind() == reflect.Struct {
  885. table = statement.Engine.tbName(v)
  886. }
  887. }
  888. if l > 1 {
  889. fmt.Fprintf(&buf, "%v AS %v", statement.Engine.Quote(table),
  890. statement.Engine.Quote(fmt.Sprintf("%v", t[1])))
  891. } else if l == 1 {
  892. fmt.Fprintf(&buf, statement.Engine.Quote(table))
  893. }
  894. default:
  895. fmt.Fprintf(&buf, statement.Engine.Quote(fmt.Sprintf("%v", tablename)))
  896. }
  897. fmt.Fprintf(&buf, " ON %v", condition)
  898. statement.JoinStr = buf.String()
  899. statement.joinArgs = append(statement.joinArgs, args...)
  900. return statement
  901. }
  902. // GroupBy generate "Group By keys" statement
  903. func (statement *Statement) GroupBy(keys string) *Statement {
  904. statement.GroupByStr = keys
  905. return statement
  906. }
  907. // Having generate "Having conditions" statement
  908. func (statement *Statement) Having(conditions string) *Statement {
  909. statement.HavingStr = fmt.Sprintf("HAVING %v", conditions)
  910. return statement
  911. }
  912. // Unscoped always disable struct tag "deleted"
  913. func (statement *Statement) Unscoped() *Statement {
  914. statement.unscoped = true
  915. return statement
  916. }
  917. func (statement *Statement) genColumnStr() string {
  918. var buf bytes.Buffer
  919. if statement.RefTable == nil {
  920. return ""
  921. }
  922. columns := statement.RefTable.Columns()
  923. for _, col := range columns {
  924. if statement.OmitStr != "" {
  925. if _, ok := getFlagForColumn(statement.columnMap, col); ok {
  926. continue
  927. }
  928. }
  929. if col.MapType == core.ONLYTODB {
  930. continue
  931. }
  932. if buf.Len() != 0 {
  933. buf.WriteString(", ")
  934. }
  935. if col.IsPrimaryKey && statement.Engine.Dialect().DBType() == "ql" {
  936. buf.WriteString("id() AS ")
  937. }
  938. if statement.JoinStr != "" {
  939. if statement.TableAlias != "" {
  940. buf.WriteString(statement.TableAlias)
  941. } else {
  942. buf.WriteString(statement.TableName())
  943. }
  944. buf.WriteString(".")
  945. }
  946. statement.Engine.QuoteTo(&buf, col.Name)
  947. }
  948. return buf.String()
  949. }
  950. func (statement *Statement) genCreateTableSQL() string {
  951. return statement.Engine.dialect.CreateTableSql(statement.RefTable, statement.TableName(),
  952. statement.StoreEngine, statement.Charset)
  953. }
  954. func (statement *Statement) genIndexSQL() []string {
  955. var sqls []string
  956. tbName := statement.TableName()
  957. quote := statement.Engine.Quote
  958. for idxName, index := range statement.RefTable.Indexes {
  959. if index.Type == core.IndexType {
  960. sql := fmt.Sprintf("CREATE INDEX %v ON %v (%v);", quote(indexName(tbName, idxName)),
  961. quote(tbName), quote(strings.Join(index.Cols, quote(","))))
  962. sqls = append(sqls, sql)
  963. }
  964. }
  965. return sqls
  966. }
  967. func uniqueName(tableName, uqeName string) string {
  968. return fmt.Sprintf("UQE_%v_%v", tableName, uqeName)
  969. }
  970. func (statement *Statement) genUniqueSQL() []string {
  971. var sqls []string
  972. tbName := statement.TableName()
  973. for _, index := range statement.RefTable.Indexes {
  974. if index.Type == core.UniqueType {
  975. sql := statement.Engine.dialect.CreateIndexSql(tbName, index)
  976. sqls = append(sqls, sql)
  977. }
  978. }
  979. return sqls
  980. }
  981. func (statement *Statement) genDelIndexSQL() []string {
  982. var sqls []string
  983. tbName := statement.TableName()
  984. for idxName, index := range statement.RefTable.Indexes {
  985. var rIdxName string
  986. if index.Type == core.UniqueType {
  987. rIdxName = uniqueName(tbName, idxName)
  988. } else if index.Type == core.IndexType {
  989. rIdxName = indexName(tbName, idxName)
  990. }
  991. sql := fmt.Sprintf("DROP INDEX %v", statement.Engine.Quote(rIdxName))
  992. if statement.Engine.dialect.IndexOnTable() {
  993. sql += fmt.Sprintf(" ON %v", statement.Engine.Quote(statement.TableName()))
  994. }
  995. sqls = append(sqls, sql)
  996. }
  997. return sqls
  998. }
  999. func (statement *Statement) genAddColumnStr(col *core.Column) (string, []interface{}) {
  1000. quote := statement.Engine.Quote
  1001. sql := fmt.Sprintf("ALTER TABLE %v ADD %v;", quote(statement.TableName()),
  1002. col.String(statement.Engine.dialect))
  1003. return sql, []interface{}{}
  1004. }
  1005. func (statement *Statement) buildConds(table *core.Table, bean interface{}, includeVersion bool, includeUpdated bool, includeNil bool, includeAutoIncr bool, addedTableName bool) (builder.Cond, error) {
  1006. return buildConds(statement.Engine, table, bean, includeVersion, includeUpdated, includeNil, includeAutoIncr, statement.allUseBool, statement.useAllCols,
  1007. statement.unscoped, statement.mustColumnMap, statement.TableName(), statement.TableAlias, addedTableName)
  1008. }
  1009. func (statement *Statement) genConds(bean interface{}) (string, []interface{}, error) {
  1010. if !statement.noAutoCondition {
  1011. var addedTableName = (len(statement.JoinStr) > 0)
  1012. autoCond, err := statement.buildConds(statement.RefTable, bean, true, true, false, true, addedTableName)
  1013. if err != nil {
  1014. return "", nil, err
  1015. }
  1016. statement.cond = statement.cond.And(autoCond)
  1017. }
  1018. statement.processIDParam()
  1019. return builder.ToSQL(statement.cond)
  1020. }
  1021. func (statement *Statement) genGetSQL(bean interface{}) (string, []interface{}) {
  1022. v := rValue(bean)
  1023. isStruct := v.Kind() == reflect.Struct
  1024. if isStruct {
  1025. statement.setRefValue(v)
  1026. }
  1027. var columnStr = statement.ColumnStr
  1028. if len(statement.selectStr) > 0 {
  1029. columnStr = statement.selectStr
  1030. } else {
  1031. // TODO: always generate column names, not use * even if join
  1032. if len(statement.JoinStr) == 0 {
  1033. if len(columnStr) == 0 {
  1034. if len(statement.GroupByStr) > 0 {
  1035. columnStr = statement.Engine.Quote(strings.Replace(statement.GroupByStr, ",", statement.Engine.Quote(","), -1))
  1036. } else {
  1037. columnStr = statement.genColumnStr()
  1038. }
  1039. }
  1040. } else {
  1041. if len(columnStr) == 0 {
  1042. if len(statement.GroupByStr) > 0 {
  1043. columnStr = statement.Engine.Quote(strings.Replace(statement.GroupByStr, ",", statement.Engine.Quote(","), -1))
  1044. }
  1045. }
  1046. }
  1047. }
  1048. if len(columnStr) == 0 {
  1049. columnStr = "*"
  1050. }
  1051. var condSQL string
  1052. var condArgs []interface{}
  1053. if isStruct {
  1054. condSQL, condArgs, _ = statement.genConds(bean)
  1055. } else {
  1056. condSQL, condArgs, _ = builder.ToSQL(statement.cond)
  1057. }
  1058. return statement.genSelectSQL(columnStr, condSQL), append(statement.joinArgs, condArgs...)
  1059. }
  1060. func (statement *Statement) genCountSQL(bean interface{}) (string, []interface{}) {
  1061. statement.setRefValue(rValue(bean))
  1062. condSQL, condArgs, _ := statement.genConds(bean)
  1063. var selectSQL = statement.selectStr
  1064. if len(selectSQL) <= 0 {
  1065. if statement.IsDistinct {
  1066. selectSQL = fmt.Sprintf("count(DISTINCT %s)", statement.ColumnStr)
  1067. } else {
  1068. selectSQL = "count(*)"
  1069. }
  1070. }
  1071. return statement.genSelectSQL(selectSQL, condSQL), append(statement.joinArgs, condArgs...)
  1072. }
  1073. func (statement *Statement) genSumSQL(bean interface{}, columns ...string) (string, []interface{}) {
  1074. statement.setRefValue(rValue(bean))
  1075. var sumStrs = make([]string, 0, len(columns))
  1076. for _, colName := range columns {
  1077. if !strings.Contains(colName, " ") && !strings.Contains(colName, "(") {
  1078. colName = statement.Engine.Quote(colName)
  1079. }
  1080. sumStrs = append(sumStrs, fmt.Sprintf("COALESCE(sum(%s),0)", colName))
  1081. }
  1082. sumSelect := strings.Join(sumStrs, ", ")
  1083. condSQL, condArgs, _ := statement.genConds(bean)
  1084. return statement.genSelectSQL(sumSelect, condSQL), append(statement.joinArgs, condArgs...)
  1085. }
  1086. func (statement *Statement) genSelectSQL(columnStr, condSQL string) (a string) {
  1087. var distinct string
  1088. if statement.IsDistinct && !strings.HasPrefix(columnStr, "count") {
  1089. distinct = "DISTINCT "
  1090. }
  1091. var dialect = statement.Engine.Dialect()
  1092. var quote = statement.Engine.Quote
  1093. var top string
  1094. var mssqlCondi string
  1095. statement.processIDParam()
  1096. var buf bytes.Buffer
  1097. if len(condSQL) > 0 {
  1098. fmt.Fprintf(&buf, " WHERE %v", condSQL)
  1099. }
  1100. var whereStr = buf.String()
  1101. var fromStr = " FROM "
  1102. if dialect.DBType() == core.MSSQL && strings.Contains(statement.TableName(), "..") {
  1103. fromStr += statement.TableName()
  1104. } else {
  1105. fromStr += quote(statement.TableName())
  1106. }
  1107. if statement.TableAlias != "" {
  1108. if dialect.DBType() == core.ORACLE {
  1109. fromStr += " " + quote(statement.TableAlias)
  1110. } else {
  1111. fromStr += " AS " + quote(statement.TableAlias)
  1112. }
  1113. }
  1114. if statement.JoinStr != "" {
  1115. fromStr = fmt.Sprintf("%v %v", fromStr, statement.JoinStr)
  1116. }
  1117. if dialect.DBType() == core.MSSQL {
  1118. if statement.LimitN > 0 {
  1119. top = fmt.Sprintf(" TOP %d ", statement.LimitN)
  1120. }
  1121. if statement.Start > 0 {
  1122. var column string
  1123. if len(statement.RefTable.PKColumns()) == 0 {
  1124. for _, index := range statement.RefTable.Indexes {
  1125. if len(index.Cols) == 1 {
  1126. column = index.Cols[0]
  1127. break
  1128. }
  1129. }
  1130. if len(column) == 0 {
  1131. column = statement.RefTable.ColumnsSeq()[0]
  1132. }
  1133. } else {
  1134. column = statement.RefTable.PKColumns()[0].Name
  1135. }
  1136. if statement.needTableName() {
  1137. if len(statement.TableAlias) > 0 {
  1138. column = statement.TableAlias + "." + column
  1139. } else {
  1140. column = statement.TableName() + "." + column
  1141. }
  1142. }
  1143. var orderStr string
  1144. if len(statement.OrderStr) > 0 {
  1145. orderStr = " ORDER BY " + statement.OrderStr
  1146. }
  1147. var groupStr string
  1148. if len(statement.GroupByStr) > 0 {
  1149. groupStr = " GROUP BY " + statement.GroupByStr
  1150. }
  1151. mssqlCondi = fmt.Sprintf("(%s NOT IN (SELECT TOP %d %s%s%s%s%s))",
  1152. column, statement.Start, column, fromStr, whereStr, orderStr, groupStr)
  1153. }
  1154. }
  1155. // !nashtsai! REVIEW Sprintf is considered slowest mean of string concatnation, better to work with builder pattern
  1156. a = fmt.Sprintf("SELECT %v%v%v%v%v", distinct, top, columnStr, fromStr, whereStr)
  1157. if len(mssqlCondi) > 0 {
  1158. if len(whereStr) > 0 {
  1159. a += " AND " + mssqlCondi
  1160. } else {
  1161. a += " WHERE " + mssqlCondi
  1162. }
  1163. }
  1164. if statement.GroupByStr != "" {
  1165. a = fmt.Sprintf("%v GROUP BY %v", a, statement.GroupByStr)
  1166. }
  1167. if statement.HavingStr != "" {
  1168. a = fmt.Sprintf("%v %v", a, statement.HavingStr)
  1169. }
  1170. if statement.OrderStr != "" {
  1171. a = fmt.Sprintf("%v ORDER BY %v", a, statement.OrderStr)
  1172. }
  1173. if dialect.DBType() != core.MSSQL && dialect.DBType() != core.ORACLE {
  1174. if statement.Start > 0 {
  1175. a = fmt.Sprintf("%v LIMIT %v OFFSET %v", a, statement.LimitN, statement.Start)
  1176. } else if statement.LimitN > 0 {
  1177. a = fmt.Sprintf("%v LIMIT %v", a, statement.LimitN)
  1178. }
  1179. } else if dialect.DBType() == core.ORACLE {
  1180. if statement.Start != 0 || statement.LimitN != 0 {
  1181. a = fmt.Sprintf("SELECT %v FROM (SELECT %v,ROWNUM RN FROM (%v) at WHERE ROWNUM <= %d) aat WHERE RN > %d", columnStr, columnStr, a, statement.Start+statement.LimitN, statement.Start)
  1182. }
  1183. }
  1184. if statement.IsForUpdate {
  1185. a = dialect.ForUpdateSql(a)
  1186. }
  1187. return
  1188. }
  1189. func (statement *Statement) processIDParam() {
  1190. if statement.idParam == nil {
  1191. return
  1192. }
  1193. for i, col := range statement.RefTable.PKColumns() {
  1194. var colName = statement.colName(col, statement.TableName())
  1195. if i < len(*(statement.idParam)) {
  1196. statement.cond = statement.cond.And(builder.Eq{colName: (*(statement.idParam))[i]})
  1197. } else {
  1198. statement.cond = statement.cond.And(builder.Eq{colName: ""})
  1199. }
  1200. }
  1201. }
  1202. func (statement *Statement) joinColumns(cols []*core.Column, includeTableName bool) string {
  1203. var colnames = make([]string, len(cols))
  1204. for i, col := range cols {
  1205. if includeTableName {
  1206. colnames[i] = statement.Engine.Quote(statement.TableName()) +
  1207. "." + statement.Engine.Quote(col.Name)
  1208. } else {
  1209. colnames[i] = statement.Engine.Quote(col.Name)
  1210. }
  1211. }
  1212. return strings.Join(colnames, ", ")
  1213. }
  1214. func (statement *Statement) convertIDSQL(sqlStr string) string {
  1215. if statement.RefTable != nil {
  1216. cols := statement.RefTable.PKColumns()
  1217. if len(cols) == 0 {
  1218. return ""
  1219. }
  1220. colstrs := statement.joinColumns(cols, false)
  1221. sqls := splitNNoCase(sqlStr, " from ", 2)
  1222. if len(sqls) != 2 {
  1223. return ""
  1224. }
  1225. var top string
  1226. if statement.LimitN > 0 && statement.Engine.dialect.DBType() == core.MSSQL {
  1227. top = fmt.Sprintf("TOP %d ", statement.LimitN)
  1228. }
  1229. return fmt.Sprintf("SELECT %s%s FROM %v", top, colstrs, sqls[1])
  1230. }
  1231. return ""
  1232. }
  1233. func (statement *Statement) convertUpdateSQL(sqlStr string) (string, string) {
  1234. if statement.RefTable == nil || len(statement.RefTable.PrimaryKeys) != 1 {
  1235. return "", ""
  1236. }
  1237. colstrs := statement.joinColumns(statement.RefTable.PKColumns(), true)
  1238. sqls := splitNNoCase(sqlStr, "where", 2)
  1239. if len(sqls) != 2 {
  1240. if len(sqls) == 1 {
  1241. return sqls[0], fmt.Sprintf("SELECT %v FROM %v",
  1242. colstrs, statement.Engine.Quote(statement.TableName()))
  1243. }
  1244. return "", ""
  1245. }
  1246. var whereStr = sqls[1]
  1247. //TODO: for postgres only, if any other database?
  1248. var paraStr string
  1249. if statement.Engine.dialect.DBType() == core.POSTGRES {
  1250. paraStr = "$"
  1251. } else if statement.Engine.dialect.DBType() == core.MSSQL {
  1252. paraStr = ":"
  1253. }
  1254. if paraStr != "" {
  1255. if strings.Contains(sqls[1], paraStr) {
  1256. dollers := strings.Split(sqls[1], paraStr)
  1257. whereStr = dollers[0]
  1258. for i, c := range dollers[1:] {
  1259. ccs := strings.SplitN(c, " ", 2)
  1260. whereStr += fmt.Sprintf(paraStr+"%v %v", i+1, ccs[1])
  1261. }
  1262. }
  1263. }
  1264. return sqls[0], fmt.Sprintf("SELECT %v FROM %v WHERE %v",
  1265. colstrs, statement.Engine.Quote(statement.TableName()),
  1266. whereStr)
  1267. }