1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117

package dal

import (
	"fmt"
	"time"
)

// List names one of the two tables of create-09.sql, each a set of strings.
type List int

const (
	BlockedFeeds List = iota
	AllowedRequesters
)

func (l List) table() (table, column string) {
	switch l {
	case BlockedFeeds:
		return "blocked_feeds", "url"
	case AllowedRequesters:
		return "allowed_requesters", "moniker"
	}
	panic(fmt.Sprintf("unknown list %d", l))
}

func (repo *Repo) IsInList(list List, entry string) (bool, error) {

	repo.muDb.RLock()
	defer repo.muDb.RUnlock()

	table, column := list.table()
	var count int
	err := repo.db.QueryRow(`SELECT COUNT(*) FROM `+table+` WHERE `+column+`=?`, entry).Scan(&count)
	return count != 0, err
}

func (repo *Repo) IsListEmpty(list List) (bool, error) {

	repo.muDb.RLock()
	defer repo.muDb.RUnlock()

	table, _ := list.table()
	var count int
	err := repo.db.QueryRow(`SELECT COUNT(*) FROM ` + table).Scan(&count)
	return count == 0, err
}

// AddToList adds every entry, in one transaction; an entry already there
// keeps its added_at.
func (repo *Repo) AddToList(list List, entries []string, when time.Time) error {

	repo.muDb.Lock()
	defer repo.muDb.Unlock()

	table, column := list.table()
	tx, err := repo.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	for _, e := range entries {
		if _, err = tx.Exec(`INSERT INTO `+table+` (`+column+`, added_at) VALUES (?, ?)
			ON CONFLICT DO NOTHING`, e, when); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// RemoveFromList removes every entry, in one transaction, and returns those
// that were not there.
func (repo *Repo) RemoveFromList(list List, entries []string) (notFound []string, err error) {

	repo.muDb.Lock()
	defer repo.muDb.Unlock()

	table, column := list.table()
	tx, err := repo.db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()
	for _, e := range entries {
		res, err := tx.Exec(`DELETE FROM `+table+` WHERE `+column+`=?`, e)
		if err != nil {
			return nil, err
		}
		if n, err := res.RowsAffected(); err != nil {
			return nil, err
		} else if n == 0 {
			notFound = append(notFound, e)
		}
	}
	return notFound, tx.Commit()
}

func (repo *Repo) GetList(list List) ([]string, error) {

	repo.muDb.RLock()
	defer repo.muDb.RUnlock()

	table, column := list.table()
	rows, err := repo.db.Query(`SELECT ` + column + ` FROM ` + table + ` ORDER BY ` + column)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var res []string
	for rows.Next() {
		var e string
		if err = rows.Scan(&e); err != nil {
			return nil, err
		}
		res = append(res, e)
	}
	return res, rows.Err()
}