cpolis/cmd/model/articles.go

package model

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"
)

type Article struct {
	Title       string
	Created     time.Time
	Description string
	Content     string
	Published   bool
	Rejected    bool
	ID          int64
	AuthorID    int64
	IssueID     int64
}

func (db *DB) AddArticle(a *Article) (int64, error) {
	var id int64
	txOptions := &sql.TxOptions{Isolation: sql.LevelSerializable}
	selectQuery := "SELECT id FROM issues WHERE published = false"
	insertQuery := `
    INSERT INTO articles
        (title, description, content, published, rejected, author_id, issue_id)
    VALUES (?, ?, ?, ?, ?, ?, ?)
    `

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

			if err = tx.QueryRow(selectQuery).Scan(&id); err != nil {
				if rollbackErr := tx.Rollback(); rollbackErr != nil {
					log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
				}
				return 0, fmt.Errorf("error getting issue ID when adding article to DB: %v", err)
			}

			result, err := tx.Exec(insertQuery, a.Title, a.Description,
				a.Content, a.Published, a.Rejected, a.AuthorID, id)
			if err != nil {
				if rollbackErr := tx.Rollback(); rollbackErr != nil {
					log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
				}
				return 0, fmt.Errorf("error inserting article into DB: %v", err)
			}

			id, err := result.LastInsertId()
			if err != nil {
				if rollbackErr := tx.Rollback(); rollbackErr != nil {
					log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
				}
				return 0, fmt.Errorf("error retrieving ID of added article: %v", err)
			}

			if err = tx.Commit(); err != nil {
				return 0, fmt.Errorf("error committing transaction when adding article to DB: %v", err)
			}
			return id, nil
		}()
		if err == nil {
			return id, nil
		}

		log.Println(err)
		wait(i)
	}

	return 0, fmt.Errorf("error: %v unsuccessful retries for DB operation, aborting", TxMaxRetries)
}

func (db *DB) GetArticle(id int64) (*Article, error) {
	query := `
    SELECT title, created, description, content, published, author_id
    FROM articles
    WHERE id = ?
    `
	row := db.QueryRow(query, id)

	article := new(Article)
	var created []byte
	var err error

	if err := row.Scan(&article.Title, &created, &article.Description,
		&article.Content, &article.Published, &article.AuthorID); err != nil {
		return nil, fmt.Errorf("error scanning article row: %v", err)
	}

	article.ID = id
	article.Created, err = time.Parse("2006-01-02 15:04:05", string(created))
	if err != nil {
		return nil, fmt.Errorf("error parsing created: %v", err)
	}

	return article, nil
}

func (db *DB) GetCertainArticles(published, rejected bool) ([]*Article, error) {
	query := `
    SELECT id, title, created, description, content, author_id
    FROM articles
    WHERE published = ?
    AND rejected = ?
    `
	rows, err := db.Query(query, published, rejected)
	if err != nil {
		return nil, fmt.Errorf("error querying articles: %v", err)
	}

	articleList := make([]*Article, 0)
	for rows.Next() {
		article := new(Article)
		var created []byte

		if err = rows.Scan(&article.ID, &article.Title, &created,
			&article.Description, &article.Content, &article.AuthorID); err != nil {
			return nil, fmt.Errorf("error scanning article row: %v", err)
		}

		article.Published = false
		article.Created, err = time.Parse("2006-01-02 15:04:05", string(created))
		if err != nil {
			return nil, fmt.Errorf("error parsing created: %v", err)
		}

		articleList = append(articleList, article)
	}

	return articleList, nil
}

func (db *DB) GetCurrentIssueArticles() ([]*Article, error) {
	var issueID int64
	txOptions := &sql.TxOptions{Isolation: sql.LevelSerializable}
	issueQuery := "SELECT id FROM issues WHERE published = false"
	articlesQuery := `
    SELECT id, title, created, description, content, author_id
    FROM articles
    WHERE issue_id = ? AND published = true
    `

	for i := 0; i < TxMaxRetries; i++ {
		id, err := func() ([]*Article, error) {
			tx, err := db.BeginTx(context.Background(), txOptions)
			if err != nil {
				return nil, fmt.Errorf("error starting transaction: %v", err)
			}

			row := tx.QueryRow(issueQuery)
			if err := row.Scan(&issueID); err != nil {
				if rollbackErr := tx.Rollback(); rollbackErr != nil {
					log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
				}
				return nil, fmt.Errorf("error querying DB for unpublished issue: %v", err)
			}

			rows, err := tx.Query(articlesQuery, issueID)
			if err != nil {
				if rollbackErr := tx.Rollback(); rollbackErr != nil {
					log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
				}
				return nil, fmt.Errorf("error querying DB for articles of issue %v: %v", issueID, err)
			}

			articleList := make([]*Article, 0)
			for rows.Next() {
				article := new(Article)
				var created []byte

				if err = rows.Scan(&article.ID, &article.Title, &created,
					&article.Description, &article.Content, &article.AuthorID); err != nil {
					if rollbackErr := tx.Rollback(); rollbackErr != nil {
						log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
					}
					return nil, fmt.Errorf("error scanning article from issue %v: %v", issueID, err)
				}

				article.Created, err = time.Parse("2006-01-02 15:04:05", string(created))
				if err != nil {
					if rollbackErr := tx.Rollback(); rollbackErr != nil {
						log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
					}
					return nil, fmt.Errorf("error parsing created: %v", err)
				}

				articleList = append(articleList, article)
			}

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

			return articleList, nil
		}()
		if err == nil {
			return id, nil
		}

		log.Println(err)
		wait(i)
	}

	return nil, fmt.Errorf("error: %v unsuccessful retries for DB operation, aborting", TxMaxRetries)
}

func (db *DB) AddArticleToCurrentIssue(id int64) error {
	var issueID int64
	txOptions := &sql.TxOptions{Isolation: sql.LevelSerializable}
	selectQuery := "SELECT id FROM issues WHERE published = false"
	updateQuery := "UPDATE articles SET issue_id = ? WHERE id = ?"

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

			if err = tx.QueryRow(selectQuery).Scan(&issueID); err != nil {
				if rollbackErr := tx.Rollback(); rollbackErr != nil {
					log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
				}
				return fmt.Errorf("error scanning row: %v", err)
			}

			_, err = db.Exec(updateQuery, issueID, id)
			if err != nil {
				if rollbackErr := tx.Rollback(); rollbackErr != nil {
					log.Fatalf("transaction error: %v, rollback error: %v", err, rollbackErr)
				}
				return fmt.Errorf("error updating issueID for article: %v", err)
			}

			if err = tx.Commit(); err != nil {
				return fmt.Errorf("error committing transaction when getting articles of issue %v: %v", issueID, 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)
}