github.com/kyleu/dbaudit@v0.0.2-0.20240321155047-ff2f2c940496/app/lib/database/migrate/db.go (about)

     1  // Package migrate - Content managed by Project Forge, see [projectforge.md] for details.
     2  package migrate
     3  
     4  import (
     5  	"context"
     6  	"database/sql"
     7  
     8  	"github.com/jmoiron/sqlx"
     9  	"github.com/pkg/errors"
    10  
    11  	"github.com/kyleu/dbaudit/app/lib/database"
    12  	"github.com/kyleu/dbaudit/app/lib/filter"
    13  	"github.com/kyleu/dbaudit/app/util"
    14  )
    15  
    16  const (
    17  	migrationTable    = "migration"
    18  	migrationTableSQL = `if not exists (select * from sysobjects where name='migration' and xtype='U')
    19  create table migration (
    20    "idx" int not null,
    21    "title" varchar(max) not null,
    22    "src" varchar(max) not null,
    23    "created" datetime not null,
    24    primary key (idx)
    25  );`
    26  )
    27  
    28  func ListMigrations(ctx context.Context, s *database.Service, params *filter.Params, tx *sqlx.Tx, logger util.Logger) Migrations {
    29  	params = filter.ParamsWithDefaultOrdering(migrationTable, params, &filter.Ordering{Column: "created", Asc: false})
    30  	var rows []*migrationRow
    31  	q := database.SQLSelect("*", migrationTable, "", params.OrderByString(), params.Limit, params.Offset, s.Type)
    32  	err := s.Select(ctx, &rows, q, tx, logger)
    33  	if err != nil {
    34  		logger.Errorf("error retrieving migrations: %+v", err)
    35  		return nil
    36  	}
    37  	return toMigrations(rows)
    38  }
    39  
    40  func createMigrationTableIfNeeded(ctx context.Context, s *database.Service, tx *sqlx.Tx, logger util.Logger) error {
    41  	q := database.SQLSelectSimple("count(*) as x", migrationTable, s.Type)
    42  	_, err := s.SingleInt(ctx, q, tx, logger)
    43  	if err != nil {
    44  		logger.Info("first run, creating migration table")
    45  		_, err = s.Exec(ctx, migrationTableSQL, nil, -1, logger)
    46  		if err != nil {
    47  			return errors.Wrapf(err, "error creating migration table: %+v", err)
    48  		}
    49  	}
    50  	return nil
    51  }
    52  
    53  func getMigrationByIdx(ctx context.Context, s *database.Service, idx int, tx *sqlx.Tx, logger util.Logger) *Migration {
    54  	row := &migrationRow{}
    55  	q := database.SQLSelectSimple("*", "migration", s.Type, "idx = @p1")
    56  	err := s.Get(ctx, row, q, tx, logger, idx)
    57  	if err != nil {
    58  		if errors.Is(err, sql.ErrNoRows) {
    59  			return nil
    60  		}
    61  		logger.Errorf("error getting migration by idx [%v]: %+v", idx, err)
    62  		return nil
    63  	}
    64  	return row.toMigration()
    65  }
    66  
    67  func removeMigrationByIdx(ctx context.Context, s *database.Service, idx int, tx *sqlx.Tx, logger util.Logger) error {
    68  	q := database.SQLDelete("migration", "idx = @p1", s.Type)
    69  	_, err := s.Delete(ctx, q, tx, 1, logger, idx)
    70  	if err != nil {
    71  		return errors.Wrap(err, "error removing migration")
    72  	}
    73  	return nil
    74  }
    75  
    76  func newMigration(ctx context.Context, s *database.Service, e *Migration, tx *sqlx.Tx, logger util.Logger) error {
    77  	q := database.SQLInsert("migration", []string{"idx", "title", "src", "created"}, 1, s.Type)
    78  	return s.Insert(ctx, q, tx, logger, e.Idx, e.Title, e.Src, util.TimeCurrent())
    79  }
    80  
    81  func maxMigrationIdx(ctx context.Context, s *database.Service, tx *sqlx.Tx, logger util.Logger) int {
    82  	q := database.SQLSelectSimple("max(idx) as x", "migration", s.Type)
    83  	max, err := s.SingleInt(ctx, q, tx, logger)
    84  	if err != nil {
    85  		logger.Errorf("error getting migrations: %+v", err)
    86  		return -1
    87  	}
    88  	return int(max)
    89  }