943 lines
25 KiB
Go
943 lines
25 KiB
Go
package db
|
|
|
|
import "context"
|
|
import "database/sql"
|
|
import "errors"
|
|
import "time"
|
|
|
|
import "codit/internal/models"
|
|
import "codit/internal/util"
|
|
|
|
var ErrParentNotInBoard = errors.New("parent block not found in this board")
|
|
var ErrParentCycle = errors.New("parent would create a cycle in the block tree")
|
|
var ErrBoardNotFound = errors.New("board not found")
|
|
var ErrUserNotFound = errors.New("user not found")
|
|
|
|
const boardSelectCols = `
|
|
b.public_id,
|
|
pr.public_id, pr.slug, pr.name,
|
|
b.title, b.description, b.icon, b.show_description,
|
|
b.card_properties, b.is_template,
|
|
COALESCE(cu.public_id, ''), COALESCE(uu.public_id, ''),
|
|
COALESCE(cu.username, ''), COALESCE(uu.username, ''),
|
|
b.created_at, b.updated_at, b.delete_at`
|
|
|
|
const boardSelectFrom = `
|
|
FROM boards b
|
|
JOIN projects pr ON pr.id = b.project_id
|
|
LEFT JOIN users cu ON cu.id = b.created_by
|
|
LEFT JOIN users uu ON uu.id = b.updated_by`
|
|
|
|
func scanBoard(row interface{ Scan(...any) error }, b *models.Board) error {
|
|
return row.Scan(
|
|
&b.ID,
|
|
&b.ProjectID, &b.ProjectSlug, &b.ProjectName,
|
|
&b.Title, &b.Description, &b.Icon, &b.ShowDescription,
|
|
&b.CardProperties, &b.IsTemplate,
|
|
&b.CreatedBy, &b.UpdatedBy,
|
|
&b.CreatedByName, &b.UpdatedByName,
|
|
&b.CreatedAt, &b.UpdatedAt, &b.DeleteAt,
|
|
)
|
|
}
|
|
|
|
func (s *Store) CreateBoard(ctx context.Context, board models.Board) (models.Board, error) {
|
|
var id string
|
|
var err error
|
|
var nowUnix int64
|
|
var tx txExecutor
|
|
var owned bool
|
|
|
|
if board.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return board, err
|
|
}
|
|
board.ID = id
|
|
}
|
|
nowUnix = time.Now().UTC().Unix()
|
|
board.CreatedAt = nowUnix
|
|
board.UpdatedAt = nowUnix
|
|
if board.CardProperties == "" {
|
|
board.CardProperties = "[]"
|
|
}
|
|
|
|
tx, owned, err = s.beginContext(ctx)
|
|
if err != nil {
|
|
return board, err
|
|
}
|
|
_, err = tx.Exec(`
|
|
INSERT INTO boards (public_id, project_id, title, description, icon, show_description,
|
|
card_properties, is_template, created_by, updated_by, created_at, updated_at, delete_at)
|
|
VALUES (?,
|
|
(SELECT id FROM projects WHERE public_id = ?),
|
|
?, ?, ?, ?, ?, ?,
|
|
(SELECT id FROM users WHERE public_id = ?),
|
|
(SELECT id FROM users WHERE public_id = ?),
|
|
?, ?, 0)`,
|
|
board.ID, board.ProjectID,
|
|
board.Title, board.Description, board.Icon, board.ShowDescription,
|
|
board.CardProperties, board.IsTemplate,
|
|
board.CreatedBy, board.CreatedBy,
|
|
board.CreatedAt, board.UpdatedAt,
|
|
)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return board, err
|
|
}
|
|
_, err = tx.Exec(`
|
|
INSERT INTO board_members (board_id, user_id, role, created_at)
|
|
VALUES (
|
|
(SELECT id FROM boards WHERE public_id = ?),
|
|
(SELECT id FROM users WHERE public_id = ?),
|
|
'admin', ?)`,
|
|
board.ID, board.CreatedBy, board.CreatedAt)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return board, err
|
|
}
|
|
err = commitIfOwned(tx, owned)
|
|
if err != nil {
|
|
return board, err
|
|
}
|
|
return s.GetBoard(board.ID)
|
|
}
|
|
|
|
// GetBoard returns the board only if it is not soft-deleted.
|
|
func (s *Store) GetBoard(id string) (models.Board, error) {
|
|
var board models.Board
|
|
var row *sql.Row
|
|
row = s.QueryRow(
|
|
`SELECT`+boardSelectCols+boardSelectFrom+` WHERE b.public_id = ? AND b.delete_at = 0`, id)
|
|
return board, scanBoard(row, &board)
|
|
}
|
|
|
|
// GetBoardProjectID returns the project public_id for a non-deleted board.
|
|
// Used by requireBoardRole to fall back to the project role.
|
|
func (s *Store) GetBoardProjectID(boardID string) (string, error) {
|
|
var projectID string
|
|
var row *sql.Row
|
|
row = s.QueryRow(`
|
|
SELECT pr.public_id
|
|
FROM boards b JOIN projects pr ON pr.id = b.project_id
|
|
WHERE b.public_id = ? AND b.delete_at = 0`,
|
|
boardID)
|
|
return projectID, row.Scan(&projectID)
|
|
}
|
|
|
|
func (s *Store) ListBoards(projectID string) ([]models.Board, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var boards []models.Board
|
|
var board models.Board
|
|
|
|
rows, err = s.Query(
|
|
`SELECT`+boardSelectCols+boardSelectFrom+`
|
|
WHERE pr.public_id = ? AND b.delete_at = 0
|
|
ORDER BY b.created_at ASC`, projectID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
board = models.Board{}
|
|
err = scanBoard(rows, &board)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
boards = append(boards, board)
|
|
}
|
|
return boards, rows.Err()
|
|
}
|
|
|
|
// ListBoardsWhereExplicitMember returns non-deleted boards in the given project
|
|
// for which the user has an explicit board_members row. Used when the caller
|
|
// has no project-level role but may still have per-board access.
|
|
func (s *Store) ListBoardsWhereExplicitMember(projectID string, userID string) ([]models.Board, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var boards []models.Board
|
|
var board models.Board
|
|
|
|
rows, err = s.Query(
|
|
`SELECT`+boardSelectCols+boardSelectFrom+`
|
|
JOIN board_members bm ON bm.board_id = b.id
|
|
JOIN users mu ON mu.id = bm.user_id
|
|
WHERE pr.public_id = ? AND mu.public_id = ? AND b.delete_at = 0
|
|
ORDER BY b.created_at ASC`, projectID, userID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
board = models.Board{}
|
|
err = scanBoard(rows, &board)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
boards = append(boards, board)
|
|
}
|
|
return boards, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListAllBoardsAdmin() ([]models.Board, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var boards []models.Board
|
|
var board models.Board
|
|
|
|
rows, err = s.Query(
|
|
`SELECT` + boardSelectCols + boardSelectFrom + `
|
|
WHERE b.delete_at = 0
|
|
ORDER BY pr.name ASC, b.title ASC`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
board = models.Board{}
|
|
err = scanBoard(rows, &board)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
boards = append(boards, board)
|
|
}
|
|
return boards, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListAllBoardsForUser(userID string) ([]models.Board, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var boards []models.Board
|
|
var board models.Board
|
|
|
|
rows, err = s.Query(
|
|
`SELECT`+boardSelectCols+boardSelectFrom+`
|
|
WHERE b.delete_at = 0
|
|
AND (
|
|
EXISTS (
|
|
SELECT 1 FROM project_role_bindings prb
|
|
WHERE prb.project_id = b.project_id
|
|
AND prb.subject_type = 'user'
|
|
AND prb.subject_id = (SELECT id FROM users WHERE public_id = ?)
|
|
)
|
|
OR EXISTS (
|
|
SELECT 1 FROM project_role_bindings prb
|
|
JOIN user_groups ug ON ug.id = prb.subject_id
|
|
WHERE prb.project_id = b.project_id
|
|
AND prb.subject_type = 'group'
|
|
AND ug.disabled = 0
|
|
AND (
|
|
ug.scope = 'all_users'
|
|
OR EXISTS (
|
|
SELECT 1 FROM user_group_members ugm
|
|
JOIN users ux ON ux.id = ugm.user_id
|
|
WHERE ugm.group_id = ug.id AND ux.public_id = ?
|
|
)
|
|
)
|
|
)
|
|
OR EXISTS (
|
|
SELECT 1 FROM board_members bm
|
|
JOIN users u ON u.id = bm.user_id
|
|
WHERE bm.board_id = b.id AND u.public_id = ?
|
|
)
|
|
)
|
|
ORDER BY pr.name ASC, b.title ASC`,
|
|
userID, userID, userID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
board = models.Board{}
|
|
err = scanBoard(rows, &board)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
boards = append(boards, board)
|
|
}
|
|
return boards, rows.Err()
|
|
}
|
|
|
|
func (s *Store) UpdateBoard(board models.Board) (models.Board, error) {
|
|
var err error
|
|
var nowUnix int64
|
|
nowUnix = time.Now().UTC().Unix()
|
|
_, err = s.Exec(`
|
|
UPDATE boards SET
|
|
title = ?, description = ?, icon = ?, show_description = ?,
|
|
card_properties = ?,
|
|
updated_by = (SELECT id FROM users WHERE public_id = ?),
|
|
updated_at = ?
|
|
WHERE public_id = ? AND delete_at = 0`,
|
|
board.Title, board.Description, board.Icon, board.ShowDescription,
|
|
board.CardProperties,
|
|
board.UpdatedBy, nowUnix,
|
|
board.ID,
|
|
)
|
|
if err != nil {
|
|
return board, err
|
|
}
|
|
return s.GetBoard(board.ID)
|
|
}
|
|
|
|
func (s *Store) DeleteBoard(id string, updatedBy string) error {
|
|
var err error
|
|
var nowUnix int64
|
|
nowUnix = time.Now().UTC().Unix()
|
|
_, err = s.Exec(`
|
|
UPDATE boards SET delete_at = ?, updated_by = (SELECT id FROM users WHERE public_id = ?), updated_at = ?
|
|
WHERE public_id = ? AND delete_at = 0`,
|
|
nowUnix, updatedBy, nowUnix, id)
|
|
return err
|
|
}
|
|
|
|
// ----- blocks -----
|
|
|
|
const blockSelectCols = `
|
|
bl.public_id,
|
|
b.public_id,
|
|
COALESCE(pb.public_id, ''),
|
|
COALESCE(cu.public_id, ''), COALESCE(uu.public_id, ''),
|
|
bl.type, bl.title, bl.fields,
|
|
bl.created_at, bl.updated_at, bl.completed_at, COALESCE(cbu.public_id, ''), bl.delete_at`
|
|
|
|
const blockSelectFrom = `
|
|
FROM blocks bl
|
|
JOIN boards b ON b.id = bl.board_id
|
|
LEFT JOIN blocks pb ON pb.id = bl.parent_id AND bl.parent_id != 0
|
|
LEFT JOIN users cu ON cu.id = bl.created_by
|
|
LEFT JOIN users uu ON uu.id = bl.updated_by
|
|
LEFT JOIN users cbu ON cbu.id = bl.completed_by`
|
|
|
|
func scanBlock(row interface{ Scan(...any) error }, bl *models.Block) error {
|
|
return row.Scan(
|
|
&bl.ID,
|
|
&bl.BoardID,
|
|
&bl.ParentID,
|
|
&bl.CreatedBy, &bl.UpdatedBy,
|
|
&bl.Type, &bl.Title, &bl.Fields,
|
|
&bl.CreatedAt, &bl.UpdatedAt, &bl.CompletedAt, &bl.CompletedBy, &bl.DeleteAt,
|
|
)
|
|
}
|
|
|
|
// resolveParentBlockID returns the internal row ID for a parent block constrained
|
|
// to the same board. Returns 0 when parentPublicID is "". Returns sql.ErrNoRows
|
|
// when the block doesn't exist, is deleted, or belongs to a different board.
|
|
func resolveParentBlockID(tx txExecutor, boardPublicID, parentPublicID string) (int64, error) {
|
|
var parentID int64
|
|
var row *sql.Row
|
|
if parentPublicID == "" {
|
|
return 0, nil
|
|
}
|
|
row = tx.QueryRow(`
|
|
SELECT bl2.id FROM blocks bl2
|
|
JOIN boards b2 ON b2.id = bl2.board_id
|
|
WHERE bl2.public_id = ? AND b2.public_id = ? AND bl2.delete_at = 0 AND b2.delete_at = 0`,
|
|
parentPublicID, boardPublicID)
|
|
return parentID, row.Scan(&parentID)
|
|
}
|
|
|
|
// hasCycleAfterReparent returns true when assigning newParentID as the parent
|
|
// of blockID would create a cycle (including the self-parent case).
|
|
func hasCycleAfterReparent(tx txExecutor, blockID, newParentID int64) (bool, error) {
|
|
var count int
|
|
var row *sql.Row
|
|
if newParentID == 0 {
|
|
return false, nil
|
|
}
|
|
if newParentID == blockID {
|
|
return true, nil
|
|
}
|
|
// Walk up from newParentID; if blockID appears in that chain there is a cycle.
|
|
var err error
|
|
row = tx.QueryRow(`
|
|
WITH RECURSIVE ancestors(id) AS (
|
|
SELECT ?
|
|
UNION ALL
|
|
SELECT b.parent_id FROM blocks b JOIN ancestors a ON b.id = a.id WHERE b.parent_id != 0
|
|
)
|
|
SELECT COUNT(*) FROM ancestors WHERE id = ?`,
|
|
newParentID, blockID)
|
|
err = row.Scan(&count)
|
|
if err != nil {
|
|
return false, err
|
|
}
|
|
return count > 0, nil
|
|
}
|
|
|
|
func (s *Store) insertBlockHistory(tx txExecutor, blockID int64, insertAt int64) error {
|
|
var err error
|
|
_, err = tx.Exec(`
|
|
INSERT INTO blocks_history
|
|
(block_id, public_id, board_id, parent_id, created_by, updated_by,
|
|
type, title, fields, created_at, updated_at, completed_at, completed_by, delete_at, insert_at)
|
|
SELECT id, public_id, board_id, parent_id, created_by, updated_by,
|
|
type, title, fields, created_at, updated_at, completed_at, completed_by, delete_at, ?
|
|
FROM blocks WHERE id = ?`,
|
|
insertAt, blockID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) CreateBlock(ctx context.Context, block models.Block) (models.Block, error) {
|
|
var id string
|
|
var err error
|
|
var nowUnix int64
|
|
var tx txExecutor
|
|
var owned bool
|
|
var result sql.Result
|
|
var blockID int64
|
|
var parentID int64
|
|
|
|
if block.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return block, err
|
|
}
|
|
block.ID = id
|
|
}
|
|
nowUnix = time.Now().UTC().Unix()
|
|
block.CreatedAt = nowUnix
|
|
block.UpdatedAt = nowUnix
|
|
if block.Fields == "" {
|
|
block.Fields = "{}"
|
|
}
|
|
|
|
tx, owned, err = s.beginContext(ctx)
|
|
if err != nil {
|
|
return block, err
|
|
}
|
|
parentID, err = resolveParentBlockID(tx, block.BoardID, block.ParentID)
|
|
if err == sql.ErrNoRows {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, ErrParentNotInBoard
|
|
}
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, err
|
|
}
|
|
result, err = tx.Exec(`
|
|
INSERT INTO blocks (public_id, board_id, parent_id, created_by, updated_by,
|
|
type, title, fields, created_at, updated_at, completed_at, completed_by, delete_at)
|
|
VALUES (?,
|
|
(SELECT id FROM boards WHERE public_id = ? AND delete_at = 0),
|
|
?,
|
|
(SELECT id FROM users WHERE public_id = ?),
|
|
(SELECT id FROM users WHERE public_id = ?),
|
|
?, ?, ?, ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), 0)`,
|
|
block.ID, block.BoardID,
|
|
parentID,
|
|
block.CreatedBy, block.CreatedBy,
|
|
block.Type, block.Title, block.Fields,
|
|
block.CreatedAt, block.UpdatedAt,
|
|
block.CompletedAt, block.CompletedBy,
|
|
)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, err
|
|
}
|
|
blockID, err = result.LastInsertId()
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, err
|
|
}
|
|
err = s.insertBlockHistory(tx, blockID, nowUnix)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, err
|
|
}
|
|
err = commitIfOwned(tx, owned)
|
|
if err != nil {
|
|
return block, err
|
|
}
|
|
return s.GetBlock(block.BoardID, block.ID)
|
|
}
|
|
|
|
// GetBlock returns a non-deleted block that belongs to the specified non-deleted board.
|
|
func (s *Store) GetBlock(boardID string, id string) (models.Block, error) {
|
|
var block models.Block
|
|
var row *sql.Row
|
|
row = s.QueryRow(
|
|
`SELECT`+blockSelectCols+blockSelectFrom+`
|
|
WHERE bl.public_id = ? AND b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0`,
|
|
id, boardID)
|
|
return block, scanBlock(row, &block)
|
|
}
|
|
|
|
func (s *Store) ListBlocks(boardID string) ([]models.Block, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var blocks []models.Block
|
|
var block models.Block
|
|
|
|
rows, err = s.Query(
|
|
`SELECT`+blockSelectCols+blockSelectFrom+`
|
|
WHERE b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0
|
|
ORDER BY bl.created_at ASC`, boardID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
block = models.Block{}
|
|
err = scanBlock(rows, &block)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
blocks = append(blocks, block)
|
|
}
|
|
return blocks, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListBlocksByType(boardID string, blockType string) ([]models.Block, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var blocks []models.Block
|
|
var block models.Block
|
|
|
|
rows, err = s.Query(
|
|
`SELECT`+blockSelectCols+blockSelectFrom+`
|
|
WHERE b.public_id = ? AND bl.type = ? AND bl.delete_at = 0 AND b.delete_at = 0
|
|
ORDER BY bl.created_at ASC`, boardID, blockType)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
block = models.Block{}
|
|
err = scanBlock(rows, &block)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
blocks = append(blocks, block)
|
|
}
|
|
return blocks, rows.Err()
|
|
}
|
|
|
|
func (s *Store) UpdateBlock(ctx context.Context, block models.Block) (models.Block, error) {
|
|
var err error
|
|
var nowUnix int64
|
|
var tx txExecutor
|
|
var owned bool
|
|
var row *sql.Row
|
|
var blockID int64
|
|
var parentID int64
|
|
var hasCycle bool
|
|
|
|
nowUnix = time.Now().UTC().Unix()
|
|
block.UpdatedAt = nowUnix
|
|
|
|
tx, owned, err = s.beginContext(ctx)
|
|
if err != nil {
|
|
return block, err
|
|
}
|
|
|
|
// Verify block belongs to the specified board and is not deleted.
|
|
row = tx.QueryRow(`
|
|
SELECT bl.id FROM blocks bl
|
|
JOIN boards b ON b.id = bl.board_id
|
|
WHERE bl.public_id = ? AND b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0`,
|
|
block.ID, block.BoardID)
|
|
err = row.Scan(&blockID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, err
|
|
}
|
|
|
|
parentID, err = resolveParentBlockID(tx, block.BoardID, block.ParentID)
|
|
if err == sql.ErrNoRows {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, ErrParentNotInBoard
|
|
}
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, err
|
|
}
|
|
hasCycle, err = hasCycleAfterReparent(tx, blockID, parentID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, err
|
|
}
|
|
if hasCycle {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, ErrParentCycle
|
|
}
|
|
_, err = tx.Exec(`
|
|
UPDATE blocks SET
|
|
parent_id = ?,
|
|
title = ?, fields = ?,
|
|
updated_by = (SELECT id FROM users WHERE public_id = ?),
|
|
updated_at = ?,
|
|
completed_at = ?,
|
|
completed_by = (SELECT id FROM users WHERE public_id = ?)
|
|
WHERE id = ?`,
|
|
parentID,
|
|
block.Title, block.Fields,
|
|
block.UpdatedBy, nowUnix,
|
|
block.CompletedAt, block.CompletedBy,
|
|
blockID,
|
|
)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, err
|
|
}
|
|
err = s.insertBlockHistory(tx, blockID, nowUnix)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return block, err
|
|
}
|
|
err = commitIfOwned(tx, owned)
|
|
if err != nil {
|
|
return block, err
|
|
}
|
|
return s.GetBlock(block.BoardID, block.ID)
|
|
}
|
|
|
|
func (s *Store) PatchBlocks(ctx context.Context, boardID string, patches []models.BlockPatch, updatedBy string) error {
|
|
var err error
|
|
var nowUnix int64
|
|
var tx txExecutor
|
|
var owned bool
|
|
var i int
|
|
var p models.BlockPatch
|
|
var blockID int64
|
|
var newParentID int64
|
|
var hasCycle bool
|
|
var row *sql.Row
|
|
|
|
if len(patches) == 0 {
|
|
return nil
|
|
}
|
|
nowUnix = time.Now().UTC().Unix()
|
|
|
|
tx, owned, err = s.beginContext(ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for i = 0; i < len(patches); i++ {
|
|
p = patches[i]
|
|
// Verify each block belongs to the specified board and is not deleted.
|
|
row = tx.QueryRow(`
|
|
SELECT bl.id FROM blocks bl
|
|
JOIN boards b ON b.id = bl.board_id
|
|
WHERE bl.public_id = ? AND b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0`,
|
|
p.ID, boardID)
|
|
err = row.Scan(&blockID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
if p.ParentID != nil {
|
|
newParentID, err = resolveParentBlockID(tx, boardID, *p.ParentID)
|
|
if err == sql.ErrNoRows {
|
|
rollbackIfOwned(tx, owned)
|
|
return ErrParentNotInBoard
|
|
}
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
hasCycle, err = hasCycleAfterReparent(tx, blockID, newParentID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
if hasCycle {
|
|
rollbackIfOwned(tx, owned)
|
|
return ErrParentCycle
|
|
}
|
|
_, err = tx.Exec(`UPDATE blocks SET parent_id = ? WHERE id = ?`, newParentID, blockID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
}
|
|
if p.Title != nil {
|
|
_, err = tx.Exec(`UPDATE blocks SET title = ? WHERE id = ?`, *p.Title, blockID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
}
|
|
if p.Fields != nil {
|
|
_, err = tx.Exec(`UPDATE blocks SET fields = ? WHERE id = ?`, *p.Fields, blockID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
}
|
|
if p.CompletedAt != nil {
|
|
if *p.CompletedAt > 0 {
|
|
_, err = tx.Exec(`UPDATE blocks SET completed_at = ?, completed_by = (SELECT id FROM users WHERE public_id = ?) WHERE id = ?`, *p.CompletedAt, updatedBy, blockID)
|
|
} else {
|
|
_, err = tx.Exec(`UPDATE blocks SET completed_at = 0, completed_by = NULL WHERE id = ?`, blockID)
|
|
}
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
}
|
|
if p.DeleteAt != nil {
|
|
_, err = tx.Exec(`UPDATE blocks SET delete_at = ? WHERE id = ?`, *p.DeleteAt, blockID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
}
|
|
_, err = tx.Exec(`
|
|
UPDATE blocks SET
|
|
updated_by = (SELECT id FROM users WHERE public_id = ?),
|
|
updated_at = ?
|
|
WHERE id = ?`,
|
|
updatedBy, nowUnix, blockID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
err = s.insertBlockHistory(tx, blockID, nowUnix)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
}
|
|
return commitIfOwned(tx, owned)
|
|
}
|
|
|
|
func (s *Store) DeleteBlock(ctx context.Context, boardID string, id string, updatedBy string) error {
|
|
var err error
|
|
var nowUnix int64
|
|
var tx txExecutor
|
|
var owned bool
|
|
var blockID int64
|
|
var row *sql.Row
|
|
var descendantRows *sql.Rows
|
|
var affectedIDs []int64
|
|
var dID int64
|
|
|
|
nowUnix = time.Now().UTC().Unix()
|
|
tx, owned, err = s.beginContext(ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
// Verify block belongs to the specified board and is not already deleted.
|
|
row = tx.QueryRow(`
|
|
SELECT bl.id FROM blocks bl
|
|
JOIN boards b ON b.id = bl.board_id
|
|
WHERE bl.public_id = ? AND b.public_id = ? AND bl.delete_at = 0 AND b.delete_at = 0`,
|
|
id, boardID)
|
|
err = row.Scan(&blockID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
// Collect the block and all non-deleted descendants so we can record history for each.
|
|
descendantRows, err = tx.Query(`
|
|
WITH RECURSIVE descendants(id) AS (
|
|
SELECT ? AS id
|
|
UNION ALL
|
|
SELECT b.id FROM blocks b
|
|
JOIN descendants d ON b.parent_id = d.id
|
|
WHERE b.delete_at = 0
|
|
)
|
|
SELECT id FROM descendants`, blockID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
for descendantRows.Next() {
|
|
err = descendantRows.Scan(&dID)
|
|
if err != nil {
|
|
descendantRows.Close()
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
affectedIDs = append(affectedIDs, dID)
|
|
}
|
|
descendantRows.Close()
|
|
err = descendantRows.Err()
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
// Soft-delete the block and all its descendants in one statement.
|
|
_, err = tx.Exec(`
|
|
WITH RECURSIVE descendants(id) AS (
|
|
SELECT ? AS id
|
|
UNION ALL
|
|
SELECT b.id FROM blocks b
|
|
JOIN descendants d ON b.parent_id = d.id
|
|
WHERE b.delete_at = 0
|
|
)
|
|
UPDATE blocks SET
|
|
delete_at = ?,
|
|
updated_by = (SELECT id FROM users WHERE public_id = ?),
|
|
updated_at = ?
|
|
WHERE id IN (SELECT id FROM descendants) AND delete_at = 0`,
|
|
blockID, nowUnix, updatedBy, nowUnix)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
for _, dID = range affectedIDs {
|
|
err = s.insertBlockHistory(tx, dID, nowUnix)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
}
|
|
return commitIfOwned(tx, owned)
|
|
}
|
|
|
|
// ----- board members -----
|
|
|
|
func (s *Store) GetBoardMemberRole(boardID string, userID string) (string, error) {
|
|
var role string
|
|
var row *sql.Row
|
|
row = s.QueryRow(`
|
|
SELECT bm.role FROM board_members bm
|
|
JOIN boards b ON b.id = bm.board_id
|
|
JOIN users u ON u.id = bm.user_id
|
|
WHERE b.public_id = ? AND u.public_id = ? AND b.delete_at = 0`,
|
|
boardID, userID)
|
|
return role, row.Scan(&role)
|
|
}
|
|
|
|
func (s *Store) ListBoardMembers(boardID string) ([]models.BoardMember, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var members []models.BoardMember
|
|
var m models.BoardMember
|
|
|
|
rows, err = s.Query(`
|
|
SELECT b.public_id, u.public_id, u.username, bm.role, bm.created_at
|
|
FROM board_members bm
|
|
JOIN boards b ON b.id = bm.board_id
|
|
JOIN users u ON u.id = bm.user_id
|
|
WHERE b.public_id = ? AND b.delete_at = 0
|
|
ORDER BY bm.created_at ASC`, boardID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
m = models.BoardMember{}
|
|
err = rows.Scan(&m.BoardID, &m.UserID, &m.Username, &m.Role, &m.CreatedAt)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
members = append(members, m)
|
|
}
|
|
return members, rows.Err()
|
|
}
|
|
|
|
func (s *Store) ListBoardAssignableUsers(boardID string) ([]models.BoardAssignableUser, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var users []models.BoardAssignableUser
|
|
var u models.BoardAssignableUser
|
|
|
|
rows, err = s.Query(`
|
|
SELECT DISTINCT u.public_id, u.username, u.display_name,
|
|
CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END
|
|
FROM users u
|
|
WHERE u.disabled = 0
|
|
AND EXISTS (SELECT 1 FROM boards b WHERE b.public_id = ? AND b.delete_at = 0)
|
|
AND (
|
|
u.is_admin = 1
|
|
OR EXISTS (
|
|
SELECT 1 FROM board_members bm
|
|
JOIN boards b ON b.id = bm.board_id
|
|
WHERE b.public_id = ? AND b.delete_at = 0 AND bm.user_id = u.id
|
|
)
|
|
OR EXISTS (
|
|
SELECT 1 FROM project_role_bindings prb
|
|
JOIN boards b ON b.project_id = prb.project_id
|
|
WHERE b.public_id = ? AND b.delete_at = 0
|
|
AND prb.subject_type = 'user'
|
|
AND prb.subject_id = u.id
|
|
)
|
|
OR EXISTS (
|
|
SELECT 1 FROM project_role_bindings prb
|
|
JOIN boards b ON b.project_id = prb.project_id
|
|
JOIN user_groups ug ON ug.id = prb.subject_id
|
|
WHERE b.public_id = ? AND b.delete_at = 0
|
|
AND prb.subject_type = 'group'
|
|
AND ug.disabled = 0
|
|
AND (
|
|
ug.scope = 'all_users'
|
|
OR EXISTS (
|
|
SELECT 1 FROM user_group_members ugm
|
|
WHERE ugm.group_id = ug.id AND ugm.user_id = u.id
|
|
)
|
|
)
|
|
)
|
|
)
|
|
ORDER BY COALESCE(NULLIF(u.display_name, ''), u.username), u.username`, boardID, boardID, boardID, boardID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
u = models.BoardAssignableUser{}
|
|
err = rows.Scan(&u.ID, &u.Username, &u.DisplayName, &u.AvatarURL)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
users = append(users, u)
|
|
}
|
|
return users, rows.Err()
|
|
}
|
|
|
|
func (s *Store) UpsertBoardMember(ctx context.Context, member models.BoardMember) error {
|
|
var err error
|
|
var nowUnix int64
|
|
var boardInternalID int64
|
|
var userInternalID int64
|
|
var tx txExecutor
|
|
var owned bool
|
|
|
|
nowUnix = time.Now().UTC().Unix()
|
|
if member.CreatedAt == 0 {
|
|
member.CreatedAt = nowUnix
|
|
}
|
|
tx, owned, err = s.beginImmediateContext(ctx)
|
|
if err != nil { return err }
|
|
err = tx.QueryRow(`SELECT id FROM boards WHERE public_id = ? AND delete_at = 0`, member.BoardID).Scan(&boardInternalID)
|
|
if err == sql.ErrNoRows {
|
|
rollbackIfOwned(tx, owned)
|
|
return ErrBoardNotFound
|
|
}
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
err = tx.QueryRow(`SELECT id FROM users WHERE public_id = ?`, member.UserID).Scan(&userInternalID)
|
|
if err == sql.ErrNoRows {
|
|
rollbackIfOwned(tx, owned)
|
|
return ErrUserNotFound
|
|
}
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`
|
|
INSERT INTO board_members (board_id, user_id, role, created_at)
|
|
VALUES (?, ?, ?, ?)
|
|
ON CONFLICT(board_id, user_id) DO UPDATE SET role = excluded.role`,
|
|
boardInternalID, userInternalID, member.Role, member.CreatedAt)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
return commitIfOwned(tx, owned)
|
|
}
|
|
|
|
func (s *Store) DeleteBoardMember(boardID string, userID string) error {
|
|
var err error
|
|
_, err = s.Exec(`
|
|
DELETE FROM board_members
|
|
WHERE board_id = (SELECT id FROM boards WHERE public_id = ? AND delete_at = 0)
|
|
AND user_id = (SELECT id FROM users WHERE public_id = ?)`,
|
|
boardID, userID)
|
|
return err
|
|
}
|