Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package store
import (
"context"
"database/sql"
"fmt"
"time"
"github.com/pkg/errors"
"google.golang.org/protobuf/encoding/protojson"
"github.com/bytebase/bytebase/backend/common"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/store/qb"
)
type ReleaseMessage struct {
ProjectID string
ReleaseID string
Payload *storepb.ReleasePayload
Deleted bool
Creator string
At time.Time
Train string
Iteration int32
Category string
}
type FindReleaseMessage struct {
ProjectID *string
ReleaseID *string
Category *string
Limit *int
Offset *int
ShowDeleted bool
}
type UpdateReleaseMessage struct {
ProjectID string
ReleaseID string
Deleted *bool
Payload *storepb.ReleasePayload
Category *string
}
func (s *Store) CreateRelease(ctx context.Context, release *ReleaseMessage, creator string) (*ReleaseMessage, error) {
p, err := protojson.Marshal(release.Payload)
if err != nil {
return nil, errors.Wrapf(err, "failed to marshal release payload")
}
tx, err := s.GetDB().BeginTx(ctx, nil)
if err != nil {
return nil, errors.Wrapf(err, "failed to begin tx")
}
defer tx.Rollback()
// Atomically get next iteration for (project, train)
// Lock all rows for this (project, train) to prevent concurrent inserts with same iteration
var maxIteration sql.NullInt64
err = tx.QueryRowContext(ctx,
`SELECT COALESCE(MAX(iteration), -1) FROM (
SELECT iteration FROM release WHERE project = $1 AND train = $2 FOR UPDATE
) sub`,
release.ProjectID, release.Train,
).Scan(&maxIteration)
if err != nil && err != sql.ErrNoRows {
return nil, errors.Wrapf(err, "failed to get max iteration")
}
nextIteration := int32(0)
if maxIteration.Valid {
nextIteration = int32(maxIteration.Int64) + 1
}
// Compute release_id = train + formatted iteration
releaseID := fmt.Sprintf("%s%02d", release.Train, nextIteration)
q := qb.Q().Space(`
INSERT INTO release (
creator,
project,
payload,
release_id,
train,
iteration,
category
) VALUES (
?,
?,
?,
?,
?,
?,
?
) RETURNING created_at
`, creator, release.ProjectID, p, releaseID, release.Train, nextIteration, release.Category)
query, args, err := q.ToSQL()
if err != nil {
return nil, errors.Wrapf(err, "failed to build sql")
}
var createdTime time.Time
if err := tx.QueryRowContext(ctx, query, args...).Scan(&createdTime); err != nil {
return nil, errors.Wrapf(err, "failed to insert release")
}
if err := tx.Commit(); err != nil {
return nil, errors.Wrapf(err, "failed to commit tx")
}
release.Creator = creator
release.At = createdTime
release.ReleaseID = releaseID
release.Iteration = nextIteration
return release, nil
}
func (s *Store) GetRelease(ctx context.Context, find *FindReleaseMessage) (*ReleaseMessage, error) {
releases, err := s.ListReleases(ctx, find)
if err != nil {
return nil, errors.Wrapf(err, "failed to list releases")
}
if len(releases) == 0 {
return nil, nil
}
if len(releases) > 1 {
return nil, errors.Errorf("found %d releases, expect 1", len(releases))
}
return releases[0], nil
}
func (s *Store) ListReleases(ctx context.Context, find *FindReleaseMessage) ([]*ReleaseMessage, error) {
q := qb.Q().Space(`
SELECT
deleted,
project,
creator,
created_at,
payload,
release_id,
train,
iteration,
category
FROM release
WHERE TRUE
`)
if v := find.ProjectID; v != nil {
q.And("project = ?", *v)
}
if v := find.ReleaseID; v != nil {
q.And("release_id = ?", *v)
}
if v := find.Category; v != nil {
q.And("category = ?", *v)
}
if !find.ShowDeleted {
q.And("deleted = ?", false)
}
// created_at defaults to now() and is not unique; (project, train,
// iteration) is the primary key.
q.Space("ORDER BY release.created_at DESC, release.project DESC, release.train DESC, release.iteration DESC")
if v := find.Limit; v != nil {
q.Space("LIMIT ?", *v)
}
if v := find.Offset; v != nil {
q.Space("OFFSET ?", *v)
}
query, args, err := q.ToSQL()
if err != nil {
return nil, errors.Wrapf(err, "failed to build sql")
}
rows, err := s.GetDB().QueryContext(ctx, query, args...)
if err != nil {
return nil, errors.Wrapf(err, "failed to query rows")
}
defer rows.Close()
var releases []*ReleaseMessage
for rows.Next() {
r := ReleaseMessage{
Payload: &storepb.ReleasePayload{},
}
var payload []byte
if err := rows.Scan(
&r.Deleted,
&r.ProjectID,
&r.Creator,
&r.At,
&payload,
&r.ReleaseID,
&r.Train,
&r.Iteration,
&r.Category,
); err != nil {
return nil, errors.Wrapf(err, "failed to scan rows")
}
if err := common.ProtojsonUnmarshaler.Unmarshal(payload, r.Payload); err != nil {
return nil, errors.Wrapf(err, "failed to unmarshal payload")
}
releases = append(releases, &r)
}
if err := rows.Err(); err != nil {
return nil, errors.Wrapf(err, "rows err")
}
return releases, nil
}
func (s *Store) UpdateRelease(ctx context.Context, update *UpdateReleaseMessage) (*ReleaseMessage, error) {
set := qb.Q()
if v := update.Deleted; v != nil {
set.Comma("deleted = ?", *v)
}
if v := update.Payload; v != nil {
payload, err := protojson.Marshal(update.Payload)
if err != nil {
return nil, errors.Wrapf(err, "failed to marshal payload")
}
set.Comma("payload = ?", payload)
}
if v := update.Category; v != nil {
set.Comma("category = ?", *v)
}
if set.Len() == 0 {
return nil, errors.New("no update field provided")
}
query, args, err := qb.Q().Space("UPDATE release SET ? WHERE project = ? AND release_id = ?", set, update.ProjectID, update.ReleaseID).ToSQL()
if err != nil {
return nil, errors.Wrapf(err, "failed to build sql")
}
if _, err := s.GetDB().ExecContext(ctx, query, args...); err != nil {
return nil, errors.Wrapf(err, "failed to query row")
}
return s.GetRelease(ctx, &FindReleaseMessage{
ProjectID: &update.ProjectID,
ReleaseID: &update.ReleaseID,
})
}
func (s *Store) ListReleaseCategories(ctx context.Context, projectID string) ([]string, error) {
query := `
SELECT DISTINCT category
FROM release
WHERE project = $1
AND category != ''
AND deleted = FALSE
ORDER BY category ASC
`
rows, err := s.GetDB().QueryContext(ctx, query, projectID)
if err != nil {
return nil, errors.Wrapf(err, "failed to query rows")
}
defer rows.Close()
var categories []string
for rows.Next() {
var category string
if err := rows.Scan(&category); err != nil {
return nil, errors.Wrapf(err, "failed to scan row")
}
categories = append(categories, category)
}
if err := rows.Err(); err != nil {
return nil, errors.Wrapf(err, "rows err")
}
return categories, nil
}