migration_history.go 2.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118
  1. package db
  2. import (
  3. "context"
  4. "database/sql"
  5. "strings"
  6. )
  7. type MigrationHistory struct {
  8. Version string
  9. CreatedTs int64
  10. }
  11. type MigrationHistoryUpsert struct {
  12. Version string
  13. }
  14. type MigrationHistoryFind struct {
  15. Version *string
  16. }
  17. func (db *DB) FindMigrationHistoryList(ctx context.Context, find *MigrationHistoryFind) ([]*MigrationHistory, error) {
  18. tx, err := db.DBInstance.BeginTx(ctx, nil)
  19. if err != nil {
  20. return nil, err
  21. }
  22. defer tx.Rollback()
  23. list, err := findMigrationHistoryList(ctx, tx, find)
  24. if err != nil {
  25. return nil, err
  26. }
  27. return list, nil
  28. }
  29. func (db *DB) UpsertMigrationHistory(ctx context.Context, upsert *MigrationHistoryUpsert) (*MigrationHistory, error) {
  30. tx, err := db.DBInstance.BeginTx(ctx, nil)
  31. if err != nil {
  32. return nil, err
  33. }
  34. defer tx.Rollback()
  35. migrationHistory, err := upsertMigrationHistory(ctx, tx, upsert)
  36. if err != nil {
  37. return nil, err
  38. }
  39. if err := tx.Commit(); err != nil {
  40. return nil, err
  41. }
  42. return migrationHistory, nil
  43. }
  44. func findMigrationHistoryList(ctx context.Context, tx *sql.Tx, find *MigrationHistoryFind) ([]*MigrationHistory, error) {
  45. where, args := []string{"1 = 1"}, []interface{}{}
  46. if v := find.Version; v != nil {
  47. where, args = append(where, "version = ?"), append(args, *v)
  48. }
  49. query := `
  50. SELECT
  51. version,
  52. created_ts
  53. FROM
  54. migration_history
  55. WHERE ` + strings.Join(where, " AND ") + `
  56. ORDER BY version DESC
  57. `
  58. rows, err := tx.QueryContext(ctx, query, args...)
  59. if err != nil {
  60. return nil, err
  61. }
  62. defer rows.Close()
  63. migrationHistoryList := make([]*MigrationHistory, 0)
  64. for rows.Next() {
  65. var migrationHistory MigrationHistory
  66. if err := rows.Scan(
  67. &migrationHistory.Version,
  68. &migrationHistory.CreatedTs,
  69. ); err != nil {
  70. return nil, err
  71. }
  72. migrationHistoryList = append(migrationHistoryList, &migrationHistory)
  73. }
  74. if err := rows.Err(); err != nil {
  75. return nil, err
  76. }
  77. return migrationHistoryList, nil
  78. }
  79. func upsertMigrationHistory(ctx context.Context, tx *sql.Tx, upsert *MigrationHistoryUpsert) (*MigrationHistory, error) {
  80. query := `
  81. INSERT INTO migration_history (
  82. version
  83. )
  84. VALUES (?)
  85. ON CONFLICT(version) DO UPDATE
  86. SET
  87. version=EXCLUDED.version
  88. RETURNING version, created_ts
  89. `
  90. var migrationHistory MigrationHistory
  91. if err := tx.QueryRowContext(ctx, query, upsert.Version).Scan(
  92. &migrationHistory.Version,
  93. &migrationHistory.CreatedTs,
  94. ); err != nil {
  95. return nil, err
  96. }
  97. return &migrationHistory, nil
  98. }