database_sql_test.go 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554
  1. package test
  2. import (
  3. "git.sxidc.com/go-framework/baize/framework/core/infrastructure/database/sql"
  4. "git.sxidc.com/go-tools/utils/strutils"
  5. "git.sxidc.com/go-tools/utils/template"
  6. "github.com/pkg/errors"
  7. "math/rand"
  8. "testing"
  9. "time"
  10. )
  11. func TestDatabaseSqlConditions(t *testing.T) {
  12. conditions := sql.NewConditions().
  13. AddCondition("name1 = ?", "foo").
  14. Equal("age", 20).
  15. Like("name2", "%foo%").
  16. In("id1", []string{"111", "222"}).
  17. NotIn("id2", []string{"33", "444"}).
  18. Not("id3", "555").
  19. LessThan("num1", 10).
  20. LessThanAndEqual("num2", 20).
  21. GreaterThan("num3", 30).
  22. GreaterThanAndEqual("num4", 40)
  23. exceptQueries := []string{
  24. `name1 = ?`,
  25. `age = ?`,
  26. `name2 LIKE ?`,
  27. `id1 IN ?`,
  28. `id2 NOT IN ?`,
  29. `id3 != ?`,
  30. `num1 < ?`,
  31. `num2 <= ?`,
  32. `num3 > ?`,
  33. `num4 >= ?`,
  34. }
  35. exceptArgs := []any{
  36. "foo",
  37. 20,
  38. "%foo%",
  39. []string{"111", "222"},
  40. []string{"33", "444"},
  41. "555",
  42. 10,
  43. 20,
  44. 30,
  45. 40,
  46. }
  47. exceptAnd := `name1 = ? AND age = ? AND name2 LIKE ? AND id1 IN ? AND id2 NOT IN ? AND id3 != ? AND num1 < ? AND num2 <= ? AND num3 > ? AND num4 >= ?`
  48. for i, query := range conditions.Queries() {
  49. if exceptQueries[i] != query {
  50. t.Fatalf("%+v\n", errors.Errorf("Query Error: except %v, actural %v",
  51. exceptQueries[i], query))
  52. }
  53. }
  54. for i, arg := range conditions.Args() {
  55. argStringSlice, ok := arg.([]string)
  56. if ok {
  57. for j, stringArg := range argStringSlice {
  58. exceptArg, ok := exceptArgs[i].([]string)
  59. if !ok {
  60. t.Fatalf("%+v\n", errors.New("参数类型不匹配"))
  61. }
  62. if exceptArg[j] != stringArg {
  63. t.Fatalf("%+v\n", errors.Errorf("Args Error: except %v, actural %v",
  64. exceptQueries[i], stringArg))
  65. }
  66. }
  67. continue
  68. }
  69. if exceptArgs[i] != arg {
  70. t.Fatalf("%+v\n", errors.Errorf("Args Error: except %v, actural %v",
  71. exceptQueries[i], arg))
  72. }
  73. }
  74. if conditions.And() != exceptAnd {
  75. t.Fatalf("%+v\n", errors.Errorf("And Error:\nexcept %v\nactural %v\n",
  76. exceptAnd, conditions.And()))
  77. }
  78. }
  79. func TestDatabaseSqlTableRow(t *testing.T) {
  80. tableRow := sql.NewTableRow().
  81. Add("name", "test").
  82. Add("age", 20)
  83. exceptColumns := []string{
  84. `"name"`,
  85. `"age"`,
  86. }
  87. exceptValues := []any{
  88. "test",
  89. 20,
  90. }
  91. for i, column := range tableRow.Columns() {
  92. if exceptColumns[i] != column {
  93. t.Fatalf("%+v\n", errors.Errorf("Column Error: except %v, actural %v",
  94. exceptColumns[i], column))
  95. }
  96. }
  97. for i, value := range tableRow.Values() {
  98. if exceptValues[i] != value {
  99. t.Fatalf("%+v\n", errors.Errorf("Value Error: except %v, actural %v",
  100. exceptValues[i], value))
  101. }
  102. }
  103. }
  104. func TestDatabaseSqlResult(t *testing.T) {
  105. timeResult := time.Now().Local()
  106. timeStringResult := time.Now().Local().Format("2006-01-02T15:04:05") + "+08:00"
  107. stringResult := strutils.SimpleUUID()
  108. boolResult := rand.Intn(2) == 0
  109. intResult := rand.Int()
  110. int8Result := int8(rand.Int())
  111. int16Result := int16(rand.Int())
  112. int32Result := int32(rand.Int())
  113. int64Result := int64(rand.Int())
  114. uintResult := uint(rand.Int())
  115. uint8Result := uint8(rand.Int())
  116. uint16Result := uint16(rand.Int())
  117. uint32Result := uint32(rand.Int())
  118. uint64Result := uint64(rand.Int())
  119. float32Result := rand.Float32()
  120. float64Result := rand.Float64()
  121. result := sql.Result{
  122. "timeStr": timeStringResult,
  123. "time": timeResult,
  124. "string": stringResult,
  125. "bool": boolResult,
  126. "int": intResult,
  127. "int8": int8Result,
  128. "int16": int16Result,
  129. "int32": int32Result,
  130. "int64": int64Result,
  131. "uint": uintResult,
  132. "uint8": uint8Result,
  133. "uint16": uint16Result,
  134. "uint32": uint32Result,
  135. "uint64": uint64Result,
  136. "float32": float32Result,
  137. "float64": float64Result,
  138. }
  139. if result.ColumnValueTime("timeStr").Format("2006-01-02T15:04:05")+"+08:00" != timeStringResult {
  140. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  141. timeStringResult, result.ColumnValueTime("timeStr")))
  142. }
  143. if !result.ColumnValueTime("time").Equal(timeResult) {
  144. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  145. timeResult, result.ColumnValueTime("time")))
  146. }
  147. if result.ColumnValueString("string") != stringResult {
  148. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  149. stringResult, result.ColumnValueString("string")))
  150. }
  151. if result.ColumnValueBool("bool") != boolResult {
  152. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  153. boolResult, result.ColumnValueBool("bool")))
  154. }
  155. if result.ColumnValueInt("int") != intResult {
  156. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  157. intResult, result.ColumnValueInt("int")))
  158. }
  159. if result.ColumnValueInt8("int8") != int8Result {
  160. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  161. int8Result, result.ColumnValueInt8("int8")))
  162. }
  163. if result.ColumnValueInt16("int16") != int16Result {
  164. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  165. int16Result, result.ColumnValueInt16("int16")))
  166. }
  167. if result.ColumnValueInt32("int32") != int32Result {
  168. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  169. int32Result, result.ColumnValueInt32("int32")))
  170. }
  171. if result.ColumnValueInt64("int64") != int64Result {
  172. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  173. int64Result, result.ColumnValueInt64("int64")))
  174. }
  175. if result.ColumnValueUint("uint") != uintResult {
  176. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  177. uintResult, result.ColumnValueUint("uint")))
  178. }
  179. if result.ColumnValueUint8("uint8") != uint8Result {
  180. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  181. uint8Result, result.ColumnValueUint8("uint8")))
  182. }
  183. if result.ColumnValueUint16("uint16") != uint16Result {
  184. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  185. uint16Result, result.ColumnValueUint16("uint16")))
  186. }
  187. if result.ColumnValueUint32("uint32") != uint32Result {
  188. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  189. uint32Result, result.ColumnValueUint32("uint32")))
  190. }
  191. if result.ColumnValueUint64("uint64") != uint64Result {
  192. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  193. uint64Result, result.ColumnValueUint64("uint64")))
  194. }
  195. if result.ColumnValueFloat32("float32") != float32Result {
  196. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  197. float32Result, result.ColumnValueFloat32("float32")))
  198. }
  199. if result.ColumnValueFloat64("float64") != float64Result {
  200. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  201. float64Result, result.ColumnValueFloat64("float64")))
  202. }
  203. }
  204. type Info struct {
  205. ID string `sqlresult:"column:id"`
  206. Name string `sqlresult:"column:name"`
  207. Age int32 `sqlresult:"column:age"`
  208. EnterTime time.Time `sqlresult:"column:enter_time"`
  209. EnterTimeStr string `sqlresult:"column:enter_time"`
  210. }
  211. func TestDatabaseParseSqlResult(t *testing.T) {
  212. id1 := strutils.SimpleUUID()
  213. name1 := strutils.SimpleUUID()
  214. age1 := rand.Int31()
  215. enterTime1 := time.Now().Local()
  216. id2 := strutils.SimpleUUID()
  217. name2 := strutils.SimpleUUID()
  218. age2 := rand.Int31()
  219. enterTime2 := time.Now().Local()
  220. result1 := sql.Result{
  221. "id": id1,
  222. "name": name1,
  223. "age": age1,
  224. "enter_time": enterTime1,
  225. }
  226. result2 := sql.Result{
  227. "id": id2,
  228. "name": name2,
  229. "age": age2,
  230. "enter_time": enterTime2,
  231. }
  232. results := []sql.Result{result1, result2}
  233. var nameResult string
  234. nameResults := make([]string, 0)
  235. err := sql.ParseSqlResultWithColumn(result1, &nameResult, "name")
  236. if err != nil {
  237. t.Fatalf("%+v\n", err)
  238. }
  239. if nameResult != name1 {
  240. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  241. name1, nameResult))
  242. }
  243. err = sql.ParseSqlResultWithColumn(results, &nameResults, "name")
  244. if err != nil {
  245. t.Fatalf("%+v\n", err)
  246. }
  247. if len(nameResults) != 2 {
  248. t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", nameResults))
  249. }
  250. for _, r := range nameResults {
  251. if r != name1 && r != name2 {
  252. t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", r))
  253. }
  254. }
  255. info := new(Info)
  256. infos := make([]Info, 0)
  257. err = sql.ParseSqlResult(result2, info)
  258. if err != nil {
  259. t.Fatalf("%+v\n", err)
  260. }
  261. if info.ID != id2 {
  262. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  263. id2, info.ID))
  264. }
  265. if info.Name != name2 {
  266. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  267. name2, info.Name))
  268. }
  269. if info.Age != age2 {
  270. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  271. age2, info.Age))
  272. }
  273. if info.EnterTime.Format(time.DateTime) != enterTime2.Format(time.DateTime) {
  274. t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
  275. enterTime2, info.EnterTime))
  276. }
  277. err = sql.ParseSqlResult(results, &infos)
  278. if err != nil {
  279. t.Fatalf("%+v\n", err)
  280. }
  281. for _, i := range infos {
  282. if i.ID != id1 && i.ID != id2 {
  283. t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", i.ID))
  284. }
  285. if i.Name != name1 && i.Name != name2 {
  286. t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", i.Name))
  287. }
  288. if i.Age != age1 && i.Age != age2 {
  289. t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", i.Age))
  290. }
  291. if i.EnterTime.Format(time.DateTime) != enterTime1.Format(time.DateTime) &&
  292. i.EnterTime.Format(time.DateTime) != enterTime2.Format(time.DateTime) {
  293. t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", i.EnterTime))
  294. }
  295. }
  296. }
  297. func TestDatabaseSqlTemplate(t *testing.T) {
  298. tableName := "test.students"
  299. insertMap, err := sql.InsertExecuteParams{
  300. TableName: tableName,
  301. TableRow: sql.NewTableRow().
  302. Add("id", "").
  303. Add("name", "").
  304. Add("age", "").
  305. Add("enter_time", ""),
  306. }.Map()
  307. if err != nil {
  308. t.Fatalf("%+v\n", err)
  309. }
  310. checkDatabaseSqlClause(t, sql.InsertTpl, insertMap, `
  311. INSERT INTO
  312. test.students ("id","name","age","enter_time")
  313. VALUES
  314. (?, ?, ?, ?)
  315. `)
  316. insertBatchMap, err := sql.InsertBatchExecuteParams{
  317. TableName: tableName,
  318. TableRowBatch: []sql.TableRow{
  319. *sql.NewTableRow().
  320. Add("id", "").
  321. Add("name", "").
  322. Add("age", "").
  323. Add("enter_time", ""),
  324. *sql.NewTableRow().
  325. Add("id", "").
  326. Add("name", "").
  327. Add("age", "").
  328. Add("enter_time", ""),
  329. },
  330. }.Map()
  331. if err != nil {
  332. t.Fatalf("%+v\n", err)
  333. }
  334. checkDatabaseSqlClause(t, sql.InsertTpl, insertBatchMap, `
  335. INSERT INTO
  336. test.students ("id","name","age","enter_time")
  337. VALUES
  338. (?, ?, ?, ?),(?, ?, ?, ?)
  339. `)
  340. deleteMap, err := sql.DeleteExecuteParams{
  341. TableName: tableName,
  342. Conditions: sql.NewConditions().
  343. Equal("id", ""),
  344. }.Map()
  345. if err != nil {
  346. t.Fatalf("%+v\n", err)
  347. }
  348. checkDatabaseSqlClause(t, sql.DeleteTpl, deleteMap, `
  349. DELETE FROM
  350. test.students
  351. WHERE
  352. "id" = ? AND 1 = 1
  353. `)
  354. updateMap, err := sql.UpdateExecuteParams{
  355. TableName: tableName,
  356. TableRow: sql.NewTableRow().
  357. Add("name", "").
  358. Add("age", "").
  359. Add("enter_time", ""),
  360. Conditions: sql.NewConditions().
  361. Equal("id", ""),
  362. }.Map()
  363. if err != nil {
  364. t.Fatalf("%+v\n", err)
  365. }
  366. checkDatabaseSqlClause(t, sql.UpdateTpl, updateMap, `
  367. UPDATE
  368. test.students
  369. SET
  370. "name" = ?, "age" = ?, "enter_time" = ?
  371. WHERE
  372. "id" = ? AND 1 = 1
  373. `)
  374. queryMap, err := sql.QueryExecuteParams{
  375. TableName: tableName,
  376. SelectClauses: []string{"name", "age"},
  377. Conditions: sql.NewConditions().
  378. Equal("name", "").
  379. Equal("age", ""),
  380. OrderBy: "name DESC",
  381. PageNo: 1,
  382. PageSize: 10,
  383. }.Map()
  384. if err != nil {
  385. t.Fatalf("%+v\n", err)
  386. }
  387. checkDatabaseSqlClause(t, sql.QueryTpl, queryMap, `
  388. SELECT
  389. name, age
  390. FROM
  391. test.students
  392. WHERE
  393. "name" = ? AND "age" = ? AND 1 = 1
  394. ORDER BY name DESC
  395. LIMIT 10
  396. OFFSET 0
  397. `)
  398. queryOneMap, err := sql.QueryOneExecuteParams{
  399. TableName: tableName,
  400. SelectClauses: []string{"name", "age"},
  401. Conditions: sql.NewConditions().
  402. Equal("id", ""),
  403. }.Map()
  404. if err != nil {
  405. t.Fatalf("%+v\n", err)
  406. }
  407. checkDatabaseSqlClause(t, sql.QueryTpl, queryOneMap, `
  408. SELECT
  409. name, age
  410. FROM
  411. test.students
  412. WHERE
  413. "id" = ? AND 1 = 1
  414. `)
  415. countMap, err := sql.CountExecuteParams{
  416. TableName: tableName,
  417. Conditions: sql.NewConditions().
  418. Equal("name", "").
  419. Equal("age", ""),
  420. }.Map()
  421. if err != nil {
  422. t.Fatalf("%+v\n", err)
  423. }
  424. checkDatabaseSqlClause(t, sql.CountTpl, countMap, `
  425. SELECT
  426. COUNT(*)
  427. FROM
  428. test.students
  429. WHERE
  430. "name" = ? AND "age" = ? AND 1 = 1
  431. `)
  432. checkExistMap, err := sql.CheckExistExecuteParams{
  433. TableName: tableName,
  434. Conditions: sql.NewConditions().
  435. Equal("name", "").
  436. Equal("age", ""),
  437. }.Map()
  438. if err != nil {
  439. t.Fatalf("%+v\n", err)
  440. }
  441. checkDatabaseSqlClause(t, sql.CountTpl, checkExistMap, `
  442. SELECT
  443. COUNT(*)
  444. FROM
  445. test.students
  446. WHERE
  447. "name" = ? AND "age" = ? AND 1 = 1
  448. `)
  449. checkHasOnlyOneMap, err := sql.CheckHasOnlyOneExecuteParams{
  450. TableName: tableName,
  451. Conditions: sql.NewConditions().
  452. Equal("name", "").
  453. Equal("age", ""),
  454. }.Map()
  455. if err != nil {
  456. t.Fatalf("%+v\n", err)
  457. }
  458. checkDatabaseSqlClause(t, sql.CountTpl, checkHasOnlyOneMap, `
  459. SELECT
  460. COUNT(*)
  461. FROM
  462. test.students
  463. WHERE
  464. "name" = ? AND "age" = ? AND 1 = 1
  465. `)
  466. }
  467. func checkDatabaseSqlClause(t *testing.T, sqlStr string, templateMap map[string]any, clause string) {
  468. parsedSql, err := template.ParseTemplateStringToString(sqlStr, templateMap)
  469. if err != nil {
  470. t.Fatalf("%+v\n", errors.New(err.Error()))
  471. }
  472. if parsedSql != clause {
  473. t.Fatalf("%+v\n", errors.Errorf("Clause Error:\nexcept: %v\nactural: %v\n",
  474. clause, parsedSql))
  475. }
  476. }