1package feeds
  2
  3import (
  4	"database/sql"
  5	"fmt"
  6
  7	"github.com/picosh/pico/pkg/db"
  8	"github.com/picosh/pico/pkg/db/postgres"
  9)
 10
 11// DB is the storage feeds uses.
 12type DB interface {
 13	db.AuthDB
 14	db.PostDB
 15	FindUsersWithPost(space string) ([]*db.User, error)
 16	InsertFeedItems(postID string, items []*db.FeedItem) error
 17	FindFeedItemsByPostID(postID string) ([]*db.FeedItem, error)
 18}
 19
 20// PsqlDB is feeds's postgres storage: the queries apps share, and the ones
 21// only feeds makes.
 22type PsqlDB struct {
 23	*postgres.PsqlDB
 24}
 25
 26var _ DB = (*PsqlDB)(nil)
 27
 28func NewDB(base *postgres.PsqlDB) *PsqlDB {
 29	return &PsqlDB{PsqlDB: base}
 30}
 31
 32func (me *PsqlDB) FindUsersWithPost(space string) ([]*db.User, error) {
 33	var users []*db.User
 34	rs, err := me.Db.Queryx(
 35		`SELECT u.id, u.name, u.created_at
 36		FROM app_users u
 37		INNER JOIN posts ON u.id=posts.user_id
 38		WHERE cur_space='feeds'
 39		GROUP BY u.id, u.name, u.created_at
 40		ORDER BY name ASC`,
 41	)
 42	if err != nil {
 43		return users, err
 44	}
 45	defer func() { _ = rs.Close() }()
 46	for rs.Next() {
 47		var name sql.NullString
 48		user := &db.User{}
 49		err := rs.Scan(
 50			&user.ID,
 51			&name,
 52			&user.CreatedAt,
 53		)
 54		if err != nil {
 55			return users, err
 56		}
 57		user.Name = name.String
 58
 59		users = append(users, user)
 60	}
 61	if rs.Err() != nil {
 62		return users, rs.Err()
 63	}
 64	return users, nil
 65}
 66
 67func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
 68	tx, err := me.Db.Beginx()
 69	if err != nil {
 70		return err
 71	}
 72	defer func() {
 73		_ = tx.Rollback()
 74	}()
 75
 76	for _, item := range items {
 77		_, err := tx.Exec(
 78			`INSERT INTO feed_items (post_id, guid, data) VALUES ($1, $2, $3) RETURNING id;`,
 79			item.PostID,
 80			item.GUID,
 81			item.Data,
 82		)
 83		if err != nil {
 84			return fmt.Errorf(
 85				"post id:%s, link:%s, guid:%s, err:%w",
 86				item.PostID, item.Data.Link, item.GUID, err,
 87			)
 88		}
 89	}
 90
 91	err = tx.Commit()
 92	return err
 93}
 94
 95func (me *PsqlDB) FindFeedItemsByPostID(postID string) ([]*db.FeedItem, error) {
 96	var items []*db.FeedItem
 97	err := me.Db.Select(&items, `SELECT * FROM feed_items WHERE post_id=$1`, postID)
 98	if err != nil {
 99		return nil, err
100	}
101	return items, nil
102}