cpolis/cmd/backend/articles_contributors.go

package backend

import (
	"fmt"
	"log"
)

func (db *DB) WriteArticleContributors(articleID int64, contributorIDs []int64) error {
	query := "INSERT INTO articles_contributors (article_id, contributor_id) VALUES (?, ?)"

	for i := 0; i < TxMaxRetries; i++ {
		err := func() error {
			tx, err := db.Begin()
			if err != nil {
				return fmt.Errorf("error starting transaction: %v", err)
			}

			for _, contributorID := range contributorIDs {
				if _, err := tx.Exec(query, articleID, contributorID); err != nil {
					if rollbackErr := tx.Rollback(); rollbackErr != nil {
						log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
					}
					return fmt.Errorf("error inserting into articles_contributors: %v", err)
				}
			}

			if err = tx.Commit(); err != nil {
				return fmt.Errorf("error committing transaction: %v", err)
			}
			return nil
		}()
		if err == nil {
			return nil
		}

		log.Println(err)
		wait(i)
	}
	return fmt.Errorf("error: %v unsuccessful retries for DB operation, aborting", TxMaxRetries)
}

func (db *DB) GetArticleContributors(c *Config, articleID int64) ([]*User, error) {
	query := `
    SELECT u.id
    FROM articles a
        INNER JOIN articles_contributors ac ON a.id = ac.article_id
        INNER JOIN users u ON ac.contributor_id = u.id
    WHERE a.id = ?
    `
	rows, err := db.Query(query, articleID)
	if err != nil {
		return nil, fmt.Errorf("error querying articles_contributors: %v", err)
	}

	contributors := make([]*User, 0)
	for rows.Next() {
		var contributorID int64

		if err = rows.Scan(&contributorID); err != nil {
			return nil, fmt.Errorf("error scanning rows: %v", err)
		}

		contributor, err := db.GetUser(c, contributorID)
		if err != nil {
			return nil, fmt.Errorf("error getting user info for article contributor: %v", err)
		}

		contributors = append(contributors, contributor)
	}

	return contributors, nil
}

func (db *DB) UpdateArticleContributors(articleID int64, contributorIDs []int64) error {
	deleteQuery := "DELETE FROM articles_contributors WHERE article_id = ?"
	insertQuery := "INSERT INTO articles_contributors (article_id, contributor_id) VALUES (?, ?)"

	for i := 0; i < TxMaxRetries; i++ {
		err := func() error {
			tx, err := db.Begin()
			if err != nil {
				return fmt.Errorf("error starting transaction: %v", err)
			}

			if _, err := tx.Exec(deleteQuery, articleID); err != nil {
				if rollbackErr := tx.Rollback(); rollbackErr != nil {
					log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
				}
				return fmt.Errorf("error deleting entries from articles_contributors before inserting new ones: %v", err)
			}

			for _, contributorID := range contributorIDs {
				if _, err := tx.Exec(insertQuery, articleID, contributorID); err != nil {
					if rollbackErr := tx.Rollback(); rollbackErr != nil {
						log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
					}
					return fmt.Errorf("error inserting new entries into articles_contributors: %v", err)
				}
			}

			if err = tx.Commit(); err != nil {
				return fmt.Errorf("error committing transaction: %v", err)
			}
			return nil
		}()
		if err == nil {
			return nil
		}

		log.Println(err)
		wait(i)
	}
	return fmt.Errorf("error: %v unsuccessful retries for DB operation, aborting", TxMaxRetries)
}