355 lines
9.2 KiB
Go
355 lines
9.2 KiB
Go
package db
|
|
|
|
import "context"
|
|
import "database/sql"
|
|
import "errors"
|
|
import "fmt"
|
|
import "time"
|
|
|
|
import "codit/internal/models"
|
|
import "codit/internal/util"
|
|
|
|
var ErrFieldValueNotFound = errors.New("field value not found")
|
|
var ErrFieldValueInUse = errors.New("field value is in use by cards")
|
|
|
|
type defaultBoardFieldValue struct {
|
|
Field string
|
|
Value string
|
|
Label string
|
|
Color string
|
|
}
|
|
|
|
var defaultBoardFieldValues = []defaultBoardFieldValue{
|
|
{Field: "status", Value: "todo", Label: "To Do", Color: "#1976d2"},
|
|
{Field: "status", Value: "in_progress", Label: "In Progress", Color: "#f59e0b"},
|
|
{Field: "status", Value: "done", Label: "Done", Color: "#16a34a"},
|
|
{Field: "type", Value: "task", Label: "Task", Color: "#1976d2"},
|
|
{Field: "type", Value: "bug", Label: "Bug", Color: "#d32f2f"},
|
|
{Field: "type", Value: "story", Label: "Story", Color: "#388e3c"},
|
|
{Field: "type", Value: "epic", Label: "Epic", Color: "#7b1fa2"},
|
|
{Field: "priority", Value: "low", Label: "Low", Color: "#4caf50"},
|
|
{Field: "priority", Value: "medium", Label: "Medium", Color: "#2196f3"},
|
|
{Field: "priority", Value: "high", Label: "High", Color: "#ff9800"},
|
|
{Field: "priority", Value: "urgent", Label: "Urgent", Color: "#f44336"},
|
|
}
|
|
|
|
var fieldValueCols = `
|
|
fv.public_id, brd.public_id,
|
|
fv.field, fv.value, fv.label, fv.color, fv.display_order,
|
|
fv.start_date, fv.end_date,
|
|
fv.created_at, fv.updated_at`
|
|
|
|
var fieldValueFrom = `
|
|
FROM board_field_values fv
|
|
JOIN boards brd ON brd.id = fv.board_id`
|
|
|
|
func scanFieldValue(row interface{ Scan(...any) error }, fv *models.BoardFieldValue) error {
|
|
return row.Scan(
|
|
&fv.ID, &fv.BoardID,
|
|
&fv.Field, &fv.Value, &fv.Label, &fv.Color, &fv.DisplayOrder,
|
|
&fv.StartDate, &fv.EndDate,
|
|
&fv.CreatedAt, &fv.UpdatedAt,
|
|
)
|
|
}
|
|
|
|
func (s *Store) SeedDefaultBoardFieldValues(ctx context.Context, boardID string) error {
|
|
var i int
|
|
var item defaultBoardFieldValue
|
|
var fv models.BoardFieldValue
|
|
var err error
|
|
|
|
for i = 0; i < len(defaultBoardFieldValues); i++ {
|
|
item = defaultBoardFieldValues[i]
|
|
fv = models.BoardFieldValue{
|
|
Field: item.Field,
|
|
Value: item.Value,
|
|
Label: item.Label,
|
|
Color: item.Color,
|
|
}
|
|
_, err = s.CreateBoardFieldValue(ctx, boardID, fv)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (s *Store) ListBoardFieldValues(boardID, field string) ([]models.BoardFieldValue, error) {
|
|
return listBoardFieldValues(s, boardID, field)
|
|
}
|
|
|
|
func listBoardFieldValues(execer sqlExecutor, boardID string, field string) ([]models.BoardFieldValue, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var result []models.BoardFieldValue
|
|
var fv models.BoardFieldValue
|
|
|
|
rows, err = execer.Query(
|
|
`SELECT`+fieldValueCols+fieldValueFrom+`
|
|
WHERE brd.public_id = ? AND fv.field = ?
|
|
ORDER BY fv.display_order ASC, fv.created_at ASC`, boardID, field)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
fv = models.BoardFieldValue{}
|
|
err = scanFieldValue(rows, &fv)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
result = append(result, fv)
|
|
}
|
|
return result, rows.Err()
|
|
}
|
|
|
|
func (s *Store) CreateBoardFieldValue(ctx context.Context, boardID string, fv models.BoardFieldValue) (models.BoardFieldValue, error) {
|
|
var id string
|
|
var err error
|
|
var now int64
|
|
var internalBoardID int64
|
|
var maxOrder int64
|
|
var result models.BoardFieldValue
|
|
var tx txExecutor
|
|
var owned bool
|
|
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return result, err
|
|
}
|
|
now = time.Now().Unix()
|
|
|
|
tx, owned, err = s.beginImmediateContext(ctx)
|
|
if err != nil {
|
|
return result, err
|
|
}
|
|
err = tx.QueryRow(`SELECT id FROM boards WHERE public_id = ? AND delete_at = 0`, boardID).Scan(&internalBoardID)
|
|
if err == sql.ErrNoRows {
|
|
rollbackIfOwned(tx, owned)
|
|
return result, ErrBoardNotFound
|
|
}
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return result, err
|
|
}
|
|
|
|
err = tx.QueryRow(
|
|
`SELECT COALESCE(MAX(display_order), -1) FROM board_field_values WHERE board_id = ? AND field = ?`,
|
|
internalBoardID, fv.Field).Scan(&maxOrder)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return result, err
|
|
}
|
|
|
|
_, err = tx.Exec(`
|
|
INSERT INTO board_field_values
|
|
(public_id, board_id, field, value, label, color, display_order, start_date, end_date, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
id, internalBoardID, fv.Field, fv.Value, fv.Label, fv.Color,
|
|
maxOrder+1, fv.StartDate, fv.EndDate, now, now)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return result, err
|
|
}
|
|
|
|
err = scanFieldValue(
|
|
tx.QueryRow(`SELECT`+fieldValueCols+fieldValueFrom+` WHERE fv.public_id = ?`, id),
|
|
&result)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return result, err
|
|
}
|
|
err = commitIfOwned(tx, owned)
|
|
if err != nil {
|
|
return result, err
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
func (s *Store) UpdateBoardFieldValue(boardID, valueID string, fv models.BoardFieldValue) (models.BoardFieldValue, error) {
|
|
var now int64
|
|
var res sql.Result
|
|
var n int64
|
|
var err error
|
|
var updated models.BoardFieldValue
|
|
|
|
now = time.Now().Unix()
|
|
res, err = s.Exec(`
|
|
UPDATE board_field_values
|
|
SET label = ?, color = ?, start_date = ?, end_date = ?, updated_at = ?
|
|
WHERE public_id = ?
|
|
AND board_id = (SELECT id FROM boards WHERE public_id = ? AND delete_at = 0)`,
|
|
fv.Label, fv.Color, fv.StartDate, fv.EndDate, now,
|
|
valueID, boardID)
|
|
if err != nil {
|
|
return updated, err
|
|
}
|
|
n, err = res.RowsAffected()
|
|
if err != nil {
|
|
return updated, err
|
|
}
|
|
if n == 0 {
|
|
return updated, ErrFieldValueNotFound
|
|
}
|
|
|
|
err = scanFieldValue(
|
|
s.QueryRow(`SELECT`+fieldValueCols+fieldValueFrom+` WHERE fv.public_id = ?`, valueID),
|
|
&updated)
|
|
return updated, err
|
|
}
|
|
|
|
// blockPropsCol maps a field name to the corresponding block_properties column.
|
|
// Returns ("", false) for unknown fields.
|
|
func blockPropsCol(field string) (string, bool) {
|
|
var cols = map[string]string{
|
|
"status": "status",
|
|
"type": "card_type",
|
|
"priority": "priority",
|
|
"sprint": "sprint",
|
|
}
|
|
var col, ok = cols[field]
|
|
return col, ok
|
|
}
|
|
|
|
func countBoardFieldValueRefs(execer sqlExecutor, boardID string, field string, value string) (int, error) {
|
|
var col string
|
|
var ok bool
|
|
var count int
|
|
var err error
|
|
|
|
col, ok = blockPropsCol(field)
|
|
if !ok {
|
|
return 0, nil
|
|
}
|
|
err = execer.QueryRow(
|
|
fmt.Sprintf(`
|
|
SELECT COUNT(*) FROM block_properties bp
|
|
JOIN blocks bl ON bl.id = bp.block_id
|
|
JOIN boards brd ON brd.id = bl.board_id
|
|
WHERE brd.public_id = ? AND bl.delete_at = 0 AND bp.%s = ?`, col),
|
|
boardID, value).Scan(&count)
|
|
return count, err
|
|
}
|
|
|
|
func (s *Store) CountBoardFieldValueRefs(boardID, field, value string) (int, error) {
|
|
return countBoardFieldValueRefs(s, boardID, field, value)
|
|
}
|
|
|
|
func (s *Store) ReorderBoardFieldValues(ctx context.Context, boardID, field string, ids []string) ([]models.BoardFieldValue, error) {
|
|
var internalBoardID int64
|
|
var err error
|
|
var now int64
|
|
var tx txExecutor
|
|
var owned bool
|
|
var i int
|
|
var id string
|
|
var result []models.BoardFieldValue
|
|
var execResult sql.Result
|
|
var n int64
|
|
|
|
now = time.Now().Unix()
|
|
tx, owned, err = s.beginImmediateContext(ctx)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
err = tx.QueryRow(`SELECT id FROM boards WHERE public_id = ? AND delete_at = 0`, boardID).Scan(&internalBoardID)
|
|
if err == sql.ErrNoRows {
|
|
rollbackIfOwned(tx, owned)
|
|
return nil, ErrBoardNotFound
|
|
}
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return nil, err
|
|
}
|
|
|
|
for i = 0; i < len(ids); i++ {
|
|
id = ids[i]
|
|
execResult, err = tx.Exec(`
|
|
UPDATE board_field_values SET display_order = ?, updated_at = ?
|
|
WHERE public_id = ? AND board_id = ? AND field = ?`,
|
|
i, now, id, internalBoardID, field)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return nil, err
|
|
}
|
|
n, err = execResult.RowsAffected()
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return nil, err
|
|
}
|
|
if n == 0 {
|
|
rollbackIfOwned(tx, owned)
|
|
return nil, ErrFieldValueNotFound
|
|
}
|
|
}
|
|
|
|
result, err = listBoardFieldValues(tx, boardID, field)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return nil, err
|
|
}
|
|
err = commitIfOwned(tx, owned)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
return result, nil
|
|
}
|
|
|
|
func (s *Store) DeleteBoardFieldValue(ctx context.Context, boardID, valueID string) error {
|
|
var err error
|
|
var res sql.Result
|
|
var n int64
|
|
var value string
|
|
var field string
|
|
var count int
|
|
var tx txExecutor
|
|
var owned bool
|
|
|
|
tx, owned, err = s.beginImmediateContext(ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
err = tx.QueryRow(`
|
|
SELECT fv.value, fv.field FROM board_field_values fv
|
|
JOIN boards brd ON brd.id = fv.board_id
|
|
WHERE fv.public_id = ? AND brd.public_id = ?`, valueID, boardID).Scan(&value, &field)
|
|
if err == sql.ErrNoRows {
|
|
rollbackIfOwned(tx, owned)
|
|
return ErrFieldValueNotFound
|
|
}
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
|
|
count, err = countBoardFieldValueRefs(tx, boardID, field, value)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
if count > 0 {
|
|
rollbackIfOwned(tx, owned)
|
|
return fmt.Errorf("%w: %d card(s)", ErrFieldValueInUse, count)
|
|
}
|
|
|
|
res, err = tx.Exec(`
|
|
DELETE FROM board_field_values
|
|
WHERE public_id = ?
|
|
AND board_id = (SELECT id FROM boards WHERE public_id = ? AND delete_at = 0)`,
|
|
valueID, boardID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
n, err = res.RowsAffected()
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
if n == 0 {
|
|
rollbackIfOwned(tx, owned)
|
|
return ErrFieldValueNotFound
|
|
}
|
|
return commitIfOwned(tx, owned)
|
|
}
|