Simple and Powerful ORM for Go, support mysql,postgres,tidb,sqlite3,mssql,oracle https://xorm.io
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

646 lines
16KB

  1. // Copyright 2017 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. "database/sql"
  7. "fmt"
  8. "testing"
  9. "time"
  10. "github.com/stretchr/testify/assert"
  11. "xorm.io/core"
  12. )
  13. func TestGetVar(t *testing.T) {
  14. assert.NoError(t, prepareEngine())
  15. type GetVar struct {
  16. Id int64 `xorm:"autoincr pk"`
  17. Msg string `xorm:"varchar(255)"`
  18. Age int
  19. Money float32
  20. Created time.Time `xorm:"created"`
  21. }
  22. assert.NoError(t, testEngine.Sync2(new(GetVar)))
  23. var data = GetVar{
  24. Msg: "hi",
  25. Age: 28,
  26. Money: 1.5,
  27. }
  28. _, err := testEngine.InsertOne(&data)
  29. assert.NoError(t, err)
  30. var msg string
  31. has, err := testEngine.Table("get_var").Cols("msg").Get(&msg)
  32. assert.NoError(t, err)
  33. assert.Equal(t, true, has)
  34. assert.Equal(t, "hi", msg)
  35. var age int
  36. has, err = testEngine.Table("get_var").Cols("age").Get(&age)
  37. assert.NoError(t, err)
  38. assert.Equal(t, true, has)
  39. assert.Equal(t, 28, age)
  40. var ageMax int
  41. has, err = testEngine.SQL("SELECT max(age) FROM "+testEngine.TableName("get_var", true)+" WHERE `id` = ?", data.Id).Get(&ageMax)
  42. assert.NoError(t, err)
  43. assert.Equal(t, true, has)
  44. assert.Equal(t, 28, ageMax)
  45. var age2 int64
  46. has, err = testEngine.Table("get_var").Cols("age").
  47. Where("age > ?", 20).
  48. And("age < ?", 30).
  49. Get(&age2)
  50. assert.NoError(t, err)
  51. assert.Equal(t, true, has)
  52. assert.EqualValues(t, 28, age2)
  53. var age3 int8
  54. has, err = testEngine.Table("get_var").Cols("age").Get(&age3)
  55. assert.NoError(t, err)
  56. assert.Equal(t, true, has)
  57. assert.EqualValues(t, 28, age3)
  58. var age4 int16
  59. has, err = testEngine.Table("get_var").Cols("age").
  60. Where("age > ?", 20).
  61. And("age < ?", 30).
  62. Get(&age4)
  63. assert.NoError(t, err)
  64. assert.Equal(t, true, has)
  65. assert.EqualValues(t, 28, age4)
  66. var age5 int32
  67. has, err = testEngine.Table("get_var").Cols("age").
  68. Where("age > ?", 20).
  69. And("age < ?", 30).
  70. Get(&age5)
  71. assert.NoError(t, err)
  72. assert.Equal(t, true, has)
  73. assert.EqualValues(t, 28, age5)
  74. var age6 int
  75. has, err = testEngine.Table("get_var").Cols("age").Get(&age6)
  76. assert.NoError(t, err)
  77. assert.Equal(t, true, has)
  78. assert.EqualValues(t, 28, age6)
  79. var age7 int64
  80. has, err = testEngine.Table("get_var").Cols("age").
  81. Where("age > ?", 20).
  82. And("age < ?", 30).
  83. Get(&age7)
  84. assert.NoError(t, err)
  85. assert.Equal(t, true, has)
  86. assert.EqualValues(t, 28, age7)
  87. var age8 int8
  88. has, err = testEngine.Table("get_var").Cols("age").Get(&age8)
  89. assert.NoError(t, err)
  90. assert.Equal(t, true, has)
  91. assert.EqualValues(t, 28, age8)
  92. var age9 int16
  93. has, err = testEngine.Table("get_var").Cols("age").
  94. Where("age > ?", 20).
  95. And("age < ?", 30).
  96. Get(&age9)
  97. assert.NoError(t, err)
  98. assert.Equal(t, true, has)
  99. assert.EqualValues(t, 28, age9)
  100. var age10 int32
  101. has, err = testEngine.Table("get_var").Cols("age").
  102. Where("age > ?", 20).
  103. And("age < ?", 30).
  104. Get(&age10)
  105. assert.NoError(t, err)
  106. assert.Equal(t, true, has)
  107. assert.EqualValues(t, 28, age10)
  108. var id sql.NullInt64
  109. has, err = testEngine.Table("get_var").Cols("id").Get(&id)
  110. assert.NoError(t, err)
  111. assert.Equal(t, true, has)
  112. assert.Equal(t, true, id.Valid)
  113. assert.EqualValues(t, data.Id, id.Int64)
  114. var msgNull sql.NullString
  115. has, err = testEngine.Table("get_var").Cols("msg").Get(&msgNull)
  116. assert.NoError(t, err)
  117. assert.Equal(t, true, has)
  118. assert.Equal(t, true, msgNull.Valid)
  119. assert.EqualValues(t, data.Msg, msgNull.String)
  120. var nullMoney sql.NullFloat64
  121. has, err = testEngine.Table("get_var").Cols("money").Get(&nullMoney)
  122. assert.NoError(t, err)
  123. assert.Equal(t, true, has)
  124. assert.Equal(t, true, nullMoney.Valid)
  125. assert.EqualValues(t, data.Money, nullMoney.Float64)
  126. var money float64
  127. has, err = testEngine.Table("get_var").Cols("money").Get(&money)
  128. assert.NoError(t, err)
  129. assert.Equal(t, true, has)
  130. assert.Equal(t, "1.5", fmt.Sprintf("%.1f", money))
  131. var money2 float64
  132. if testEngine.Dialect().DBType() == core.MSSQL {
  133. has, err = testEngine.SQL("SELECT TOP 1 money FROM " + testEngine.TableName("get_var", true)).Get(&money2)
  134. } else {
  135. has, err = testEngine.SQL("SELECT money FROM " + testEngine.TableName("get_var", true) + " LIMIT 1").Get(&money2)
  136. }
  137. assert.NoError(t, err)
  138. assert.Equal(t, true, has)
  139. assert.Equal(t, "1.5", fmt.Sprintf("%.1f", money2))
  140. var money3 float64
  141. has, err = testEngine.SQL("SELECT money FROM " + testEngine.TableName("get_var", true) + " WHERE money > 20").Get(&money3)
  142. assert.NoError(t, err)
  143. assert.Equal(t, false, has)
  144. var valuesString = make(map[string]string)
  145. has, err = testEngine.Table("get_var").Get(&valuesString)
  146. assert.NoError(t, err)
  147. assert.Equal(t, true, has)
  148. assert.Equal(t, 5, len(valuesString))
  149. assert.Equal(t, "1", valuesString["id"])
  150. assert.Equal(t, "hi", valuesString["msg"])
  151. assert.Equal(t, "28", valuesString["age"])
  152. assert.Equal(t, "1.5", valuesString["money"])
  153. // for mymysql driver, interface{} will be []byte, so ignore it currently
  154. if testEngine.Dialect().DriverName() != "mymysql" {
  155. var valuesInter = make(map[string]interface{})
  156. has, err = testEngine.Table("get_var").Where("id = ?", 1).Select("*").Get(&valuesInter)
  157. assert.NoError(t, err)
  158. assert.Equal(t, true, has)
  159. assert.Equal(t, 5, len(valuesInter))
  160. assert.EqualValues(t, 1, valuesInter["id"])
  161. assert.Equal(t, "hi", fmt.Sprintf("%s", valuesInter["msg"]))
  162. assert.EqualValues(t, 28, valuesInter["age"])
  163. assert.Equal(t, "1.5", fmt.Sprintf("%v", valuesInter["money"]))
  164. }
  165. var valuesSliceString = make([]string, 5)
  166. has, err = testEngine.Table("get_var").Get(&valuesSliceString)
  167. assert.NoError(t, err)
  168. assert.Equal(t, true, has)
  169. assert.Equal(t, "1", valuesSliceString[0])
  170. assert.Equal(t, "hi", valuesSliceString[1])
  171. assert.Equal(t, "28", valuesSliceString[2])
  172. assert.Equal(t, "1.5", valuesSliceString[3])
  173. var valuesSliceInter = make([]interface{}, 5)
  174. has, err = testEngine.Table("get_var").Get(&valuesSliceInter)
  175. assert.NoError(t, err)
  176. assert.Equal(t, true, has)
  177. v1, err := convertInt(valuesSliceInter[0])
  178. assert.NoError(t, err)
  179. assert.EqualValues(t, 1, v1)
  180. assert.Equal(t, "hi", fmt.Sprintf("%s", valuesSliceInter[1]))
  181. v3, err := convertInt(valuesSliceInter[2])
  182. assert.NoError(t, err)
  183. assert.EqualValues(t, 28, v3)
  184. v4, err := convertFloat(valuesSliceInter[3])
  185. assert.NoError(t, err)
  186. assert.Equal(t, "1.5", fmt.Sprintf("%v", v4))
  187. }
  188. func TestGetStruct(t *testing.T) {
  189. assert.NoError(t, prepareEngine())
  190. type UserinfoGet struct {
  191. Uid int `xorm:"pk autoincr"`
  192. IsMan bool
  193. }
  194. assert.NoError(t, testEngine.Sync2(new(UserinfoGet)))
  195. session := testEngine.NewSession()
  196. defer session.Close()
  197. var err error
  198. if testEngine.Dialect().DBType() == core.MSSQL {
  199. err = session.Begin()
  200. assert.NoError(t, err)
  201. _, err = session.Exec("SET IDENTITY_INSERT userinfo_get ON")
  202. assert.NoError(t, err)
  203. }
  204. cnt, err := session.Insert(&UserinfoGet{Uid: 2})
  205. assert.NoError(t, err)
  206. assert.EqualValues(t, 1, cnt)
  207. if testEngine.Dialect().DBType() == core.MSSQL {
  208. err = session.Commit()
  209. assert.NoError(t, err)
  210. }
  211. user := UserinfoGet{Uid: 2}
  212. has, err := testEngine.Get(&user)
  213. assert.NoError(t, err)
  214. assert.True(t, has)
  215. type NoIdUser struct {
  216. User string `xorm:"unique"`
  217. Remain int64
  218. Total int64
  219. }
  220. assert.NoError(t, testEngine.Sync2(&NoIdUser{}))
  221. userCol := testEngine.GetColumnMapper().Obj2Table("User")
  222. _, err = testEngine.Where("`"+userCol+"` = ?", "xlw").Delete(&NoIdUser{})
  223. assert.NoError(t, err)
  224. cnt, err = testEngine.Insert(&NoIdUser{"xlw", 20, 100})
  225. assert.NoError(t, err)
  226. assert.EqualValues(t, 1, cnt)
  227. noIdUser := new(NoIdUser)
  228. has, err = testEngine.Where("`"+userCol+"` = ?", "xlw").Get(noIdUser)
  229. assert.NoError(t, err)
  230. assert.True(t, has)
  231. }
  232. func TestGetSlice(t *testing.T) {
  233. assert.NoError(t, prepareEngine())
  234. type UserinfoSlice struct {
  235. Uid int `xorm:"pk autoincr"`
  236. IsMan bool
  237. }
  238. assertSync(t, new(UserinfoSlice))
  239. var users []UserinfoSlice
  240. has, err := testEngine.Get(&users)
  241. assert.False(t, has)
  242. assert.Error(t, err)
  243. }
  244. func TestGetError(t *testing.T) {
  245. assert.NoError(t, prepareEngine())
  246. type GetError struct {
  247. Uid int `xorm:"pk autoincr"`
  248. IsMan bool
  249. }
  250. assertSync(t, new(GetError))
  251. var info = new(GetError)
  252. has, err := testEngine.Get(&info)
  253. assert.False(t, has)
  254. assert.Error(t, err)
  255. has, err = testEngine.Get(info)
  256. assert.False(t, has)
  257. assert.NoError(t, err)
  258. }
  259. func TestJSONString(t *testing.T) {
  260. assert.NoError(t, prepareEngine())
  261. type JsonString struct {
  262. Id int64
  263. Content string `xorm:"json"`
  264. }
  265. type JsonJson struct {
  266. Id int64
  267. Content []string `xorm:"json"`
  268. }
  269. assertSync(t, new(JsonJson))
  270. _, err := testEngine.Insert(&JsonJson{
  271. Content: []string{"1", "2"},
  272. })
  273. assert.NoError(t, err)
  274. var js JsonString
  275. has, err := testEngine.Table("json_json").Get(&js)
  276. assert.NoError(t, err)
  277. assert.True(t, has)
  278. assert.EqualValues(t, 1, js.Id)
  279. assert.EqualValues(t, `["1","2"]`, js.Content)
  280. var jss []JsonString
  281. err = testEngine.Table("json_json").Find(&jss)
  282. assert.NoError(t, err)
  283. assert.EqualValues(t, 1, len(jss))
  284. assert.EqualValues(t, `["1","2"]`, jss[0].Content)
  285. }
  286. func TestGetActionMapping(t *testing.T) {
  287. assert.NoError(t, prepareEngine())
  288. type ActionMapping struct {
  289. ActionId string `xorm:"pk"`
  290. ActionName string `xorm:"index"`
  291. ScriptId string `xorm:"unique"`
  292. RollbackId string `xorm:"unique"`
  293. Env string
  294. Tags string
  295. Description string
  296. UpdateTime time.Time `xorm:"updated"`
  297. DeleteTime time.Time `xorm:"deleted"`
  298. }
  299. assertSync(t, new(ActionMapping))
  300. _, err := testEngine.Insert(&ActionMapping{
  301. ActionId: "1",
  302. ScriptId: "2",
  303. })
  304. assert.NoError(t, err)
  305. var valuesSlice = make([]string, 2)
  306. has, err := testEngine.Table(new(ActionMapping)).
  307. Cols("script_id", "rollback_id").
  308. ID("1").Get(&valuesSlice)
  309. assert.NoError(t, err)
  310. assert.True(t, has)
  311. assert.EqualValues(t, "2", valuesSlice[0])
  312. assert.EqualValues(t, "", valuesSlice[1])
  313. }
  314. func TestGetStructId(t *testing.T) {
  315. type TestGetStruct struct {
  316. Id int64
  317. }
  318. assert.NoError(t, prepareEngine())
  319. assertSync(t, new(TestGetStruct))
  320. _, err := testEngine.Insert(&TestGetStruct{})
  321. assert.NoError(t, err)
  322. _, err = testEngine.Insert(&TestGetStruct{})
  323. assert.NoError(t, err)
  324. type maxidst struct {
  325. Id int64
  326. }
  327. //var id int64
  328. var maxid maxidst
  329. sql := "select max(id) as id from " + testEngine.TableName(&TestGetStruct{}, true)
  330. has, err := testEngine.SQL(sql).Get(&maxid)
  331. assert.NoError(t, err)
  332. assert.True(t, has)
  333. assert.EqualValues(t, 2, maxid.Id)
  334. }
  335. func TestContextGet(t *testing.T) {
  336. type ContextGetStruct struct {
  337. Id int64
  338. Name string
  339. }
  340. assert.NoError(t, prepareEngine())
  341. assertSync(t, new(ContextGetStruct))
  342. _, err := testEngine.Insert(&ContextGetStruct{Name: "1"})
  343. assert.NoError(t, err)
  344. sess := testEngine.NewSession()
  345. defer sess.Close()
  346. context := NewMemoryContextCache()
  347. var c2 ContextGetStruct
  348. has, err := sess.ID(1).NoCache().ContextCache(context).Get(&c2)
  349. assert.NoError(t, err)
  350. assert.True(t, has)
  351. assert.EqualValues(t, 1, c2.Id)
  352. assert.EqualValues(t, "1", c2.Name)
  353. sql, args := sess.LastSQL()
  354. assert.True(t, len(sql) > 0)
  355. assert.True(t, len(args) > 0)
  356. var c3 ContextGetStruct
  357. has, err = sess.ID(1).NoCache().ContextCache(context).Get(&c3)
  358. assert.NoError(t, err)
  359. assert.True(t, has)
  360. assert.EqualValues(t, 1, c3.Id)
  361. assert.EqualValues(t, "1", c3.Name)
  362. sql, args = sess.LastSQL()
  363. assert.True(t, len(sql) == 0)
  364. assert.True(t, len(args) == 0)
  365. }
  366. func TestContextGet2(t *testing.T) {
  367. type ContextGetStruct2 struct {
  368. Id int64
  369. Name string
  370. }
  371. assert.NoError(t, prepareEngine())
  372. assertSync(t, new(ContextGetStruct2))
  373. _, err := testEngine.Insert(&ContextGetStruct2{Name: "1"})
  374. assert.NoError(t, err)
  375. context := NewMemoryContextCache()
  376. var c2 ContextGetStruct2
  377. has, err := testEngine.ID(1).NoCache().ContextCache(context).Get(&c2)
  378. assert.NoError(t, err)
  379. assert.True(t, has)
  380. assert.EqualValues(t, 1, c2.Id)
  381. assert.EqualValues(t, "1", c2.Name)
  382. var c3 ContextGetStruct2
  383. has, err = testEngine.ID(1).NoCache().ContextCache(context).Get(&c3)
  384. assert.NoError(t, err)
  385. assert.True(t, has)
  386. assert.EqualValues(t, 1, c3.Id)
  387. assert.EqualValues(t, "1", c3.Name)
  388. }
  389. type GetCustomTableInterface interface {
  390. TableName() string
  391. }
  392. type MyGetCustomTableImpletation struct {
  393. Id int64 `json:"id"`
  394. Name string `json:"name"`
  395. }
  396. const getCustomTableName = "GetCustomTableInterface"
  397. func (m *MyGetCustomTableImpletation) TableName() string {
  398. return getCustomTableName
  399. }
  400. func TestGetCustomTableInterface(t *testing.T) {
  401. assert.NoError(t, prepareEngine())
  402. assert.NoError(t, testEngine.Table(getCustomTableName).Sync2(new(MyGetCustomTableImpletation)))
  403. exist, err := testEngine.IsTableExist(getCustomTableName)
  404. assert.NoError(t, err)
  405. assert.True(t, exist)
  406. _, err = testEngine.Insert(&MyGetCustomTableImpletation{
  407. Name: "xlw",
  408. })
  409. assert.NoError(t, err)
  410. var c GetCustomTableInterface = new(MyGetCustomTableImpletation)
  411. has, err := testEngine.Get(c)
  412. assert.NoError(t, err)
  413. assert.True(t, has)
  414. }
  415. func TestGetNullVar(t *testing.T) {
  416. type TestGetNullVarStruct struct {
  417. Id int64
  418. Name string
  419. Age int
  420. }
  421. assert.NoError(t, prepareEngine())
  422. assertSync(t, new(TestGetNullVarStruct))
  423. affected, err := testEngine.Exec("insert into " + testEngine.TableName(new(TestGetNullVarStruct), true) + " (name,age) values (null,null)")
  424. assert.NoError(t, err)
  425. a, _ := affected.RowsAffected()
  426. assert.EqualValues(t, 1, a)
  427. var name string
  428. has, err := testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("name").Get(&name)
  429. assert.NoError(t, err)
  430. assert.True(t, has)
  431. assert.EqualValues(t, "", name)
  432. var age int
  433. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age)
  434. assert.NoError(t, err)
  435. assert.True(t, has)
  436. assert.EqualValues(t, 0, age)
  437. var age2 int8
  438. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age2)
  439. assert.NoError(t, err)
  440. assert.True(t, has)
  441. assert.EqualValues(t, 0, age2)
  442. var age3 int16
  443. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age3)
  444. assert.NoError(t, err)
  445. assert.True(t, has)
  446. assert.EqualValues(t, 0, age3)
  447. var age4 int32
  448. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age4)
  449. assert.NoError(t, err)
  450. assert.True(t, has)
  451. assert.EqualValues(t, 0, age4)
  452. var age5 int64
  453. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age5)
  454. assert.NoError(t, err)
  455. assert.True(t, has)
  456. assert.EqualValues(t, 0, age5)
  457. var age6 uint
  458. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age6)
  459. assert.NoError(t, err)
  460. assert.True(t, has)
  461. assert.EqualValues(t, 0, age6)
  462. var age7 uint8
  463. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age7)
  464. assert.NoError(t, err)
  465. assert.True(t, has)
  466. assert.EqualValues(t, 0, age7)
  467. var age8 int16
  468. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age8)
  469. assert.NoError(t, err)
  470. assert.True(t, has)
  471. assert.EqualValues(t, 0, age8)
  472. var age9 int32
  473. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age9)
  474. assert.NoError(t, err)
  475. assert.True(t, has)
  476. assert.EqualValues(t, 0, age9)
  477. var age10 int64
  478. has, err = testEngine.Table(new(TestGetNullVarStruct)).Where("id = ?", 1).Cols("age").Get(&age10)
  479. assert.NoError(t, err)
  480. assert.True(t, has)
  481. assert.EqualValues(t, 0, age10)
  482. }
  483. func TestCustomTypes(t *testing.T) {
  484. type MyInt int
  485. type MyString string
  486. type TestCustomizeStruct struct {
  487. Id int64
  488. Name MyString
  489. Age MyInt
  490. }
  491. assert.NoError(t, prepareEngine())
  492. assertSync(t, new(TestCustomizeStruct))
  493. var s = TestCustomizeStruct{
  494. Name: "test",
  495. Age: 32,
  496. }
  497. _, err := testEngine.Insert(&s)
  498. assert.NoError(t, err)
  499. assert.EqualValues(t, 1, s.Id)
  500. var name MyString
  501. has, err := testEngine.Table(new(TestCustomizeStruct)).ID(s.Id).Cols("name").Get(&name)
  502. assert.NoError(t, err)
  503. assert.True(t, has)
  504. assert.EqualValues(t, "test", name)
  505. var age MyInt
  506. has, err = testEngine.Table(new(TestCustomizeStruct)).ID(s.Id).Select("age").Get(&age)
  507. assert.NoError(t, err)
  508. assert.True(t, has)
  509. assert.EqualValues(t, 32, age)
  510. }
  511. func TestGetViaMapCond(t *testing.T) {
  512. type GetViaMapCond struct {
  513. Id int64
  514. Platform int
  515. Index int
  516. }
  517. assert.NoError(t, prepareEngine())
  518. assertSync(t, new(GetViaMapCond))
  519. var (
  520. r GetViaMapCond
  521. platformStr = colMapper.Obj2Table("Platform")
  522. indexStr = colMapper.Obj2Table("Index")
  523. query = map[string]interface{}{
  524. platformStr: 1,
  525. indexStr: 1,
  526. }
  527. )
  528. has, err := testEngine.Where(query).Get(&r)
  529. assert.NoError(t, err)
  530. assert.False(t, has)
  531. }