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.

381 lines
9.7KB

  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. "fmt"
  7. "strconv"
  8. "testing"
  9. "time"
  10. "xorm.io/builder"
  11. "xorm.io/core"
  12. "github.com/stretchr/testify/assert"
  13. )
  14. func TestQueryString(t *testing.T) {
  15. assert.NoError(t, prepareEngine())
  16. type GetVar2 struct {
  17. Id int64 `xorm:"autoincr pk"`
  18. Msg string `xorm:"varchar(255)"`
  19. Age int
  20. Money float32
  21. Created time.Time `xorm:"created"`
  22. }
  23. assert.NoError(t, testEngine.Sync2(new(GetVar2)))
  24. var data = GetVar2{
  25. Msg: "hi",
  26. Age: 28,
  27. Money: 1.5,
  28. }
  29. _, err := testEngine.InsertOne(data)
  30. assert.NoError(t, err)
  31. records, err := testEngine.QueryString("select * from " + testEngine.TableName("get_var2", true))
  32. assert.NoError(t, err)
  33. assert.Equal(t, 1, len(records))
  34. assert.Equal(t, 5, len(records[0]))
  35. assert.Equal(t, "1", records[0]["id"])
  36. assert.Equal(t, "hi", records[0]["msg"])
  37. assert.Equal(t, "28", records[0]["age"])
  38. assert.Equal(t, "1.5", records[0]["money"])
  39. }
  40. func TestQueryString2(t *testing.T) {
  41. assert.NoError(t, prepareEngine())
  42. type GetVar3 struct {
  43. Id int64 `xorm:"autoincr pk"`
  44. Msg bool `xorm:"bit"`
  45. }
  46. assert.NoError(t, testEngine.Sync2(new(GetVar3)))
  47. var data = GetVar3{
  48. Msg: false,
  49. }
  50. _, err := testEngine.Insert(data)
  51. assert.NoError(t, err)
  52. records, err := testEngine.QueryString("select * from " + testEngine.TableName("get_var3", true))
  53. assert.NoError(t, err)
  54. assert.Equal(t, 1, len(records))
  55. assert.Equal(t, 2, len(records[0]))
  56. assert.Equal(t, "1", records[0]["id"])
  57. assert.True(t, "0" == records[0]["msg"] || "false" == records[0]["msg"])
  58. }
  59. func toString(i interface{}) string {
  60. switch i.(type) {
  61. case []byte:
  62. return string(i.([]byte))
  63. case string:
  64. return i.(string)
  65. }
  66. return fmt.Sprintf("%v", i)
  67. }
  68. func toInt64(i interface{}) int64 {
  69. switch i.(type) {
  70. case []byte:
  71. n, _ := strconv.ParseInt(string(i.([]byte)), 10, 64)
  72. return n
  73. case int:
  74. return int64(i.(int))
  75. case int64:
  76. return i.(int64)
  77. }
  78. return 0
  79. }
  80. func toFloat64(i interface{}) float64 {
  81. switch i.(type) {
  82. case []byte:
  83. n, _ := strconv.ParseFloat(string(i.([]byte)), 64)
  84. return n
  85. case float64:
  86. return i.(float64)
  87. case float32:
  88. return float64(i.(float32))
  89. }
  90. return 0
  91. }
  92. func TestQueryInterface(t *testing.T) {
  93. assert.NoError(t, prepareEngine())
  94. type GetVarInterface struct {
  95. Id int64 `xorm:"autoincr pk"`
  96. Msg string `xorm:"varchar(255)"`
  97. Age int
  98. Money float32
  99. Created time.Time `xorm:"created"`
  100. }
  101. assert.NoError(t, testEngine.Sync2(new(GetVarInterface)))
  102. var data = GetVarInterface{
  103. Msg: "hi",
  104. Age: 28,
  105. Money: 1.5,
  106. }
  107. _, err := testEngine.InsertOne(data)
  108. assert.NoError(t, err)
  109. records, err := testEngine.QueryInterface("select * from " + testEngine.TableName("get_var_interface", true))
  110. assert.NoError(t, err)
  111. assert.Equal(t, 1, len(records))
  112. assert.Equal(t, 5, len(records[0]))
  113. assert.EqualValues(t, 1, toInt64(records[0]["id"]))
  114. assert.Equal(t, "hi", toString(records[0]["msg"]))
  115. assert.EqualValues(t, 28, toInt64(records[0]["age"]))
  116. assert.EqualValues(t, 1.5, toFloat64(records[0]["money"]))
  117. }
  118. func TestQueryNoParams(t *testing.T) {
  119. assert.NoError(t, prepareEngine())
  120. type QueryNoParams struct {
  121. Id int64 `xorm:"autoincr pk"`
  122. Msg string `xorm:"varchar(255)"`
  123. Age int
  124. Money float32
  125. Created time.Time `xorm:"created"`
  126. }
  127. testEngine.ShowSQL(true)
  128. assert.NoError(t, testEngine.Sync2(new(QueryNoParams)))
  129. var q = QueryNoParams{
  130. Msg: "message",
  131. Age: 20,
  132. Money: 3000,
  133. }
  134. cnt, err := testEngine.Insert(&q)
  135. assert.NoError(t, err)
  136. assert.EqualValues(t, 1, cnt)
  137. assertResult := func(t *testing.T, results []map[string][]byte) {
  138. assert.EqualValues(t, 1, len(results))
  139. id, err := strconv.ParseInt(string(results[0]["id"]), 10, 64)
  140. assert.NoError(t, err)
  141. assert.EqualValues(t, 1, id)
  142. assert.Equal(t, "message", string(results[0]["msg"]))
  143. age, err := strconv.Atoi(string(results[0]["age"]))
  144. assert.NoError(t, err)
  145. assert.EqualValues(t, 20, age)
  146. money, err := strconv.ParseFloat(string(results[0]["money"]), 32)
  147. assert.NoError(t, err)
  148. assert.EqualValues(t, 3000, money)
  149. }
  150. results, err := testEngine.Table("query_no_params").Limit(10).Query()
  151. assert.NoError(t, err)
  152. assertResult(t, results)
  153. results, err = testEngine.SQL("select * from " + testEngine.TableName("query_no_params", true)).Query()
  154. assert.NoError(t, err)
  155. assertResult(t, results)
  156. }
  157. func TestQueryStringNoParam(t *testing.T) {
  158. assert.NoError(t, prepareEngine())
  159. type GetVar4 struct {
  160. Id int64 `xorm:"autoincr pk"`
  161. Msg bool `xorm:"bit"`
  162. }
  163. assert.NoError(t, testEngine.Sync2(new(GetVar4)))
  164. var data = GetVar4{
  165. Msg: false,
  166. }
  167. _, err := testEngine.Insert(data)
  168. assert.NoError(t, err)
  169. records, err := testEngine.Table("get_var4").Limit(1).QueryString()
  170. assert.NoError(t, err)
  171. assert.EqualValues(t, 1, len(records))
  172. assert.EqualValues(t, "1", records[0]["id"])
  173. if testEngine.Dialect().DBType() == core.POSTGRES || testEngine.Dialect().DBType() == core.MSSQL {
  174. assert.EqualValues(t, "false", records[0]["msg"])
  175. } else {
  176. assert.EqualValues(t, "0", records[0]["msg"])
  177. }
  178. records, err = testEngine.Table("get_var4").Where(builder.Eq{"id": 1}).QueryString()
  179. assert.NoError(t, err)
  180. assert.EqualValues(t, 1, len(records))
  181. assert.EqualValues(t, "1", records[0]["id"])
  182. if testEngine.Dialect().DBType() == core.POSTGRES || testEngine.Dialect().DBType() == core.MSSQL {
  183. assert.EqualValues(t, "false", records[0]["msg"])
  184. } else {
  185. assert.EqualValues(t, "0", records[0]["msg"])
  186. }
  187. }
  188. func TestQuerySliceStringNoParam(t *testing.T) {
  189. assert.NoError(t, prepareEngine())
  190. type GetVar6 struct {
  191. Id int64 `xorm:"autoincr pk"`
  192. Msg bool `xorm:"bit"`
  193. }
  194. assert.NoError(t, testEngine.Sync2(new(GetVar6)))
  195. var data = GetVar6{
  196. Msg: false,
  197. }
  198. _, err := testEngine.Insert(data)
  199. assert.NoError(t, err)
  200. records, err := testEngine.Table("get_var6").Limit(1).QuerySliceString()
  201. assert.NoError(t, err)
  202. assert.EqualValues(t, 1, len(records))
  203. assert.EqualValues(t, "1", records[0][0])
  204. if testEngine.Dialect().DBType() == core.POSTGRES || testEngine.Dialect().DBType() == core.MSSQL {
  205. assert.EqualValues(t, "false", records[0][1])
  206. } else {
  207. assert.EqualValues(t, "0", records[0][1])
  208. }
  209. records, err = testEngine.Table("get_var6").Where(builder.Eq{"id": 1}).QuerySliceString()
  210. assert.NoError(t, err)
  211. assert.EqualValues(t, 1, len(records))
  212. assert.EqualValues(t, "1", records[0][0])
  213. if testEngine.Dialect().DBType() == core.POSTGRES || testEngine.Dialect().DBType() == core.MSSQL {
  214. assert.EqualValues(t, "false", records[0][1])
  215. } else {
  216. assert.EqualValues(t, "0", records[0][1])
  217. }
  218. }
  219. func TestQueryInterfaceNoParam(t *testing.T) {
  220. assert.NoError(t, prepareEngine())
  221. type GetVar5 struct {
  222. Id int64 `xorm:"autoincr pk"`
  223. Msg bool `xorm:"bit"`
  224. }
  225. assert.NoError(t, testEngine.Sync2(new(GetVar5)))
  226. var data = GetVar5{
  227. Msg: false,
  228. }
  229. _, err := testEngine.Insert(data)
  230. assert.NoError(t, err)
  231. records, err := testEngine.Table("get_var5").Limit(1).QueryInterface()
  232. assert.NoError(t, err)
  233. assert.EqualValues(t, 1, len(records))
  234. assert.EqualValues(t, 1, toInt64(records[0]["id"]))
  235. assert.EqualValues(t, 0, toInt64(records[0]["msg"]))
  236. records, err = testEngine.Table("get_var5").Where(builder.Eq{"id": 1}).QueryInterface()
  237. assert.NoError(t, err)
  238. assert.EqualValues(t, 1, len(records))
  239. assert.EqualValues(t, 1, toInt64(records[0]["id"]))
  240. assert.EqualValues(t, 0, toInt64(records[0]["msg"]))
  241. }
  242. func TestQueryWithBuilder(t *testing.T) {
  243. assert.NoError(t, prepareEngine())
  244. type QueryWithBuilder struct {
  245. Id int64 `xorm:"autoincr pk"`
  246. Msg string `xorm:"varchar(255)"`
  247. Age int
  248. Money float32
  249. Created time.Time `xorm:"created"`
  250. }
  251. testEngine.ShowSQL(true)
  252. assert.NoError(t, testEngine.Sync2(new(QueryWithBuilder)))
  253. var q = QueryWithBuilder{
  254. Msg: "message",
  255. Age: 20,
  256. Money: 3000,
  257. }
  258. cnt, err := testEngine.Insert(&q)
  259. assert.NoError(t, err)
  260. assert.EqualValues(t, 1, cnt)
  261. assertResult := func(t *testing.T, results []map[string][]byte) {
  262. assert.EqualValues(t, 1, len(results))
  263. id, err := strconv.ParseInt(string(results[0]["id"]), 10, 64)
  264. assert.NoError(t, err)
  265. assert.EqualValues(t, 1, id)
  266. assert.Equal(t, "message", string(results[0]["msg"]))
  267. age, err := strconv.Atoi(string(results[0]["age"]))
  268. assert.NoError(t, err)
  269. assert.EqualValues(t, 20, age)
  270. money, err := strconv.ParseFloat(string(results[0]["money"]), 32)
  271. assert.NoError(t, err)
  272. assert.EqualValues(t, 3000, money)
  273. }
  274. results, err := testEngine.Query(builder.Select("*").From(testEngine.TableName("query_with_builder", true)))
  275. assert.NoError(t, err)
  276. assertResult(t, results)
  277. }
  278. func TestJoinWithSubQuery(t *testing.T) {
  279. assert.NoError(t, prepareEngine())
  280. type JoinWithSubQuery1 struct {
  281. Id int64 `xorm:"autoincr pk"`
  282. Msg string `xorm:"varchar(255)"`
  283. DepartId int64
  284. Money float32
  285. }
  286. type JoinWithSubQueryDepart struct {
  287. Id int64 `xorm:"autoincr pk"`
  288. Name string
  289. }
  290. testEngine.ShowSQL(true)
  291. assert.NoError(t, testEngine.Sync2(new(JoinWithSubQuery1), new(JoinWithSubQueryDepart)))
  292. var depart = JoinWithSubQueryDepart{
  293. Name: "depart1",
  294. }
  295. cnt, err := testEngine.Insert(&depart)
  296. assert.NoError(t, err)
  297. assert.EqualValues(t, 1, cnt)
  298. var q = JoinWithSubQuery1{
  299. Msg: "message",
  300. DepartId: depart.Id,
  301. Money: 3000,
  302. }
  303. cnt, err = testEngine.Insert(&q)
  304. assert.NoError(t, err)
  305. assert.EqualValues(t, 1, cnt)
  306. var querys []JoinWithSubQuery1
  307. err = testEngine.Join("INNER", builder.Select("id").From(testEngine.Quote(testEngine.TableName("join_with_sub_query_depart", true))),
  308. "join_with_sub_query_depart.id = join_with_sub_query1.depart_id").Find(&querys)
  309. assert.NoError(t, err)
  310. assert.EqualValues(t, 1, len(querys))
  311. assert.EqualValues(t, q, querys[0])
  312. }