trantor/lib/database/submissions.go

package database

import (
	"fmt"
	"time"

	"github.com/prometheus/common/log"
)

const (
	daysExpireSubmission = 30
)

type Submission struct {
	ID           int    `pg:"type:serial"`
	SubmissionID string `pg:"type:varchar(16)"`
	Filename     string
	Status       string
	Comment      string
	LastModified time.Time
	UserID       int    `pg:"type:integer,unique"`
	User         *User  `pg:"rel:has-one"`
	BookID       string `pg:"type:varchar(16),unique"`
	Book         *Book  `pg:"rel:has-one"`
}

func (db *pgDB) AddSubmission(submission Submission, userName string) (id int, err error) {
	if userName != "" {
		user, err := db.getUser(userName)
		if err == nil {
			submission.UserID = user.ID
		}
	}
	submission.LastModified = time.Now()
	_, err = db.sql.Model(&submission).Insert()
	return submission.ID, err
}

func (db *pgDB) UpdateSubmission(id int, status string, book *Book) error {
	_, err := db.sql.Model(&Submission{}).
		Set("status = ?", status).
		Set("book_id = ?", extractID(book)).
		Set("last_modified = CURRENT_TIMESTAMP").
		Where("id = ?", id).
		Update()
	return err
}

func (db *pgDB) UpdateSubmissionByBook(bookID string, status string, book *Book) error {
	_, err := db.sql.Model(&Submission{}).
		Set("status = ?", status).
		Set("book_id = ?", extractID(book)).
		Set("last_modified = CURRENT_TIMESTAMP").
		Where("book_id = ?", bookID).
		Update()
	return err
}

func (db *pgDB) UpdateSubmissionComment(submissionID, bookID, comment string) error {
	_, err := db.sql.Model(&Submission{}).
		Set("comment = ?", comment).
		Where("submission_id = ?", submissionID).
		Where("book_id = ?", bookID).
		Update()
	return err
}

func (db *pgDB) GetComment(bookID string) (string, error) {
	var submission Submission
	err := db.sql.Model(&submission).
		Where("book_id = ?", bookID).
		Select()
	if err != nil {
		return "", err
	}
	return submission.Comment, nil
}

func (db *pgDB) GetSubmission(submissionID string) (submission []Submission, err error) {
	err = db.sql.Model(&submission).
		Relation("Book").
		Where("submission_id = ?", submissionID).
		Select()
	return
}

func (db *pgDB) GetUserSubmissions(userName string) (submission []Submission, err error) {
	err = db.sql.Model(&submission).
		Relation("Book").
		Relation("User").
		Where("username = ?", userName).
		Order("last_modified DESC").
		Select()
	return
}

func (db *pgDB) submissionsCleaner() {
	periodicity := 60 * time.Minute

	for true {
		time.Sleep(periodicity)
		_, err := db.sql.Model(&Submission{}).
			Where("book_id is null").
			Where("last_modified < CURRENT_TIMESTAMP - interval ?", fmt.Sprintf("%d days", daysExpireSubmission)).
			Delete()
		if err != nil {
			log.Error("Error deleting null submissions: ", err)
		}

		_, err = db.sql.Model(&Submission{}).
			TableExpr(`books`).
			Where("book_id = books.id").
			Where("books.active is true").
			Where("last_modified < CURRENT_TIMESTAMP - interval ?", fmt.Sprintf("%d days", daysExpireSubmission)).
			Delete()
		if err != nil {
			log.Error("Error deleting approved submissions: ", err)
		}
	}
}

func extractID(book *Book) interface{} {
	if book == nil {
		return nil
	}
	return book.ID
}