320 lines
9.8 KiB
Go
320 lines
9.8 KiB
Go
package backend
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
"log"
|
|
"os"
|
|
"time"
|
|
|
|
"github.com/google/uuid"
|
|
)
|
|
|
|
type Article struct {
|
|
Created time.Time
|
|
Title string
|
|
BannerLink string
|
|
Summary string
|
|
UUID uuid.UUID
|
|
ID int64
|
|
CreatorID int64
|
|
IssueID int64
|
|
EditedID int64
|
|
Clicks int
|
|
Published bool
|
|
Rejected bool
|
|
IsInIssue bool
|
|
AutoGenerated bool
|
|
}
|
|
|
|
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, banner_link, summary, published, rejected, creator_id, issue_id, edited_id, clicks, is_in_issue, auto_generated, uuid)
|
|
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.BannerLink, a.Summary, a.Published, a.Rejected, a.CreatorID, id, a.EditedID, 0, a.IsInIssue, a.AutoGenerated, a.UUID.String())
|
|
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, banner_link, summary, published, creator_id, issue_id, edited_id, clicks, is_in_issue, auto_generated, uuid
|
|
FROM articles
|
|
WHERE id = ?
|
|
`
|
|
row := db.QueryRow(query, id)
|
|
|
|
article := new(Article)
|
|
var created []byte
|
|
var uuidString string
|
|
var err error
|
|
|
|
if err := row.Scan(&article.Title, &created, &article.BannerLink, &article.Summary, &article.Published, &article.CreatorID, &article.IssueID, &article.EditedID, &article.Clicks, &article.IsInIssue, &article.AutoGenerated, &uuidString); 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)
|
|
}
|
|
|
|
article.UUID, err = uuid.Parse(uuidString)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error parsing uuid: %v", err)
|
|
}
|
|
|
|
return article, nil
|
|
}
|
|
|
|
func (db *DB) GetCertainArticles(attribute string, value bool) ([]*Article, error) {
|
|
query := fmt.Sprintf(`
|
|
SELECT id, title, created, banner_link, summary, creator_id, issue_id, clicks, published, rejected, is_in_issue, auto_generated, uuid
|
|
FROM articles
|
|
WHERE %s = ?
|
|
`, attribute)
|
|
rows, err := db.Query(query, value)
|
|
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
|
|
var uuidString string
|
|
|
|
if err = rows.Scan(&article.ID, &article.Title, &created, &article.BannerLink, &article.Summary, &article.CreatorID, &article.IssueID, &article.Clicks, &article.Published, &article.Rejected, &article.IsInIssue, &article.AutoGenerated, &uuidString); err != nil {
|
|
return nil, fmt.Errorf("error scanning article row: %v", err)
|
|
}
|
|
|
|
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)
|
|
}
|
|
|
|
article.UUID, err = uuid.Parse(uuidString)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("error parsing uuid: %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, banner_link, summary, clicks, auto_generated, uuid
|
|
FROM articles
|
|
WHERE issue_id = ? AND published = true AND is_in_issue = 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
|
|
var uuidString string
|
|
|
|
if err = rows.Scan(&article.ID, &article.Title, &created, &article.BannerLink, &article.Summary, &article.Clicks, &article.AutoGenerated, &uuidString); 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)
|
|
}
|
|
|
|
article.UUID, err = uuid.Parse(uuidString)
|
|
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 uuid: %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)
|
|
}
|
|
|
|
func (db *DB) DeleteArticle(id int64) error {
|
|
articlesTagsQuery := "DELETE FROM articles_tags WHERE article_id = ?"
|
|
articlesContributorsQuery := "DELETE FROM articles_contributors WHERE article_id = ?"
|
|
articlesAuthorsQuery := "DELETE FROM articles_authors WHERE article_id = ?"
|
|
articlesQuery := "DELETE FROM articles WHERE id = ?"
|
|
|
|
_, err := db.Exec(articlesTagsQuery, id)
|
|
if err != nil {
|
|
return fmt.Errorf("error deleting articles_tags %v from DB: %v", id, err)
|
|
}
|
|
|
|
_, err = db.Exec(articlesContributorsQuery, id)
|
|
if err != nil {
|
|
return fmt.Errorf("error deleting articles_contributors %v from DB: %v", id, err)
|
|
}
|
|
|
|
_, err = db.Exec(articlesAuthorsQuery, id)
|
|
if err != nil {
|
|
return fmt.Errorf("error deleting articles_authors %v from DB: %v", id, err)
|
|
}
|
|
|
|
_, err = db.Exec(articlesQuery, id)
|
|
if err != nil {
|
|
return fmt.Errorf("error deleting article %v from DB: %v", id, err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
func WriteArticleToFile(c *Config, articleUUID uuid.UUID, content []byte) error {
|
|
articleAbsName := fmt.Sprint(c.ArticleDir, "/", articleUUID, ".md")
|
|
|
|
if err := os.WriteFile(articleAbsName, content, 0644); err != nil {
|
|
return fmt.Errorf("error writing article %v to file: %v", articleUUID, err)
|
|
}
|
|
|
|
return nil
|
|
}
|