func (db *PodcastDBReader) SelectAll(page int64) (*models.PodcastList, error) {
var numRows int64
q := "SELECT COUNT(id) FROM podcasts"
if err := db.QueryRowx(q).Scan(&numRows); err != nil {
return nil, dbErr(err, q)
}
result := &models.PodcastList{
Page: models.NewPage(page, numRows),
}
q = `SELECT p.id, p.title, p.enclosure_url, p.description,
p.channel_id, c.title AS name, c.image, p.pub_date, p.source
FROM podcasts p
JOIN channels c ON c.id = p.channel_id
ORDER BY p.pub_date DESC
OFFSET $1 LIMIT $2`
err := sqlx.Select(
db,
&result.Podcasts,
q,
result.Page.Offset,
result.Page.PageSize)
return result, dbErr(err, q)
}
func GetGalleries(db sqlx.Ext) ([]Gallery, error) {
q := `SELECT * FROM galleries WHERE displayed`
res := []Gallery{}
err := sqlx.Select(db, &res, q)
return res, tracederror.New(err)
}
func GetHiddenGalleries(db sqlx.Ext) ([]Gallery, error) {
q := `SELECT * FROM galleries`
res := []Gallery{}
err := sqlx.Select(db, &res, q)
return res, tracederror.New(err)
}
// GetAllFeeds returns all feeds from the database.
func (d *Handle) GetAllFeeds() ([]*FeedInfo, error) {
d.syncMutex.Lock()
defer d.syncMutex.Unlock()
feeds := []*FeedInfo{}
err := sqlx.Select(d.queryer, &feeds, "SELECT * FROM feed_info")
return feeds, err
}
func (db *PodcastDBReader) SelectBookmarked(userID, page int64) (*models.PodcastList, error) {
q := `SELECT COUNT(DISTINCT(p.id)) FROM podcasts p
JOIN bookmarks b ON b.podcast_id = p.id
WHERE b.user_id=$1`
var numRows int64
if err := db.QueryRowx(q, userID).Scan(&numRows); err != nil {
return nil, dbErr(err, q)
}
result := &models.PodcastList{
Page: models.NewPage(page, numRows),
}
q = `SELECT p.id, p.title, p.enclosure_url, p.description,
p.channel_id, c.title AS name, c.image, p.pub_date, p.source
FROM podcasts p
JOIN channels c ON c.id = p.channel_id
JOIN bookmarks b ON b.podcast_id = p.id
WHERE b.user_id=$1
GROUP BY p.id, p.title, c.title, c.image, b.id
ORDER BY b.id DESC
OFFSET $2 LIMIT $3`
err := sqlx.Select(
db,
&result.Podcasts,
q,
userID,
result.Page.Offset,
result.Page.PageSize)
return result, dbErr(err, q)
}
func (db *PodcastDBReader) SelectByChannelID(channelID, page int64) (*models.PodcastList, error) {
q := "SELECT COUNT(id) FROM podcasts WHERE channel_id=$1"
var numRows int64
if err := db.QueryRowx(q, channelID).Scan(&numRows); err != nil {
return nil, dbErr(err, q)
}
result := &models.PodcastList{
Page: models.NewPage(page, numRows),
}
q = `SELECT id, title, enclosure_url, description, pub_date, source
FROM podcasts
WHERE channel_id=$1
ORDER BY pub_date DESC
OFFSET $2 LIMIT $3`
err := sqlx.Select(
db,
&result.Podcasts,
q,
channelID,
result.Page.Offset,
result.Page.PageSize)
return result, dbErr(err, q)
}
// GetAllUsers returns all Users from the database.
func (d *Handle) GetAllUsers() ([]User, error) {
var all []User
d.syncMutex.Lock()
defer d.syncMutex.Unlock()
err := sqlx.Select(d.queryer, &all, "SELECT * FROM user")
return all, err
}
// GetStaleFeeds returns all feeds that haven't gotten new content in more
// than 14 days.
func (d *Handle) GetStaleFeeds() ([]*FeedInfo, error) {
d.syncMutex.Lock()
defer d.syncMutex.Unlock()
var res []*FeedInfo
err := sqlx.Select(d.queryer, &res, `SELECT feed_info.id, feed_info.name, feed_info.url, feed_info.last_poll_error FROM (SELECT feed_info_id, MAX(added_on) as MaxTime FROM feed_item GROUP BY feed_info_id) r, feed_info INNER JOIN feed_item f ON f.feed_info_id = r.feed_info_id AND f.added_on = r.MaxTime AND r.MaxTime < datetime('now','-14 days') AND f.feed_info_id = feed_info.id group by f.feed_info_id;`)
return res, err
}
// GetFeedsWithErrors returns all feeds that had an error on their last
// check.
func (d *Handle) GetFeedsWithErrors() ([]*FeedInfo, error) {
d.syncMutex.Lock()
defer d.syncMutex.Unlock()
var feeds []*FeedInfo
err := sqlx.Select(d.queryer, &feeds, "SELECT * from feed_info WHERE last_poll_error IS NOT NULL and last_poll_error <> ''")
return feeds, err
}
func (balancer *Balancer) Select(result interface{}, query string, args ...interface{}) error {
q, e := balancer.db()
if e != nil {
return e
}
return sqlx.Select(q, result, query, args...)
}
func (db *PodcastDBReader) Search(query string) ([]models.Podcast, error) {
q := `SELECT p.id, p.title, p.enclosure_url, p.description,
p.channel_id, p.pub_date, c.title AS name, c.image, p.source
FROM podcasts p, plainto_tsquery($1) as q, channels c
WHERE (p.tsv @@ q) AND p.channel_id = c.id
ORDER BY ts_rank_cd(p.tsv, plainto_tsquery($1)) DESC LIMIT $2`
var podcasts []models.Podcast
return podcasts, dbErr(sqlx.Select(db, &podcasts, q, query, maxSearchRows), q)
}
func GetCategories(db sqlx.Ext) ([]BlogCategory, error) {
q := `
SELECT * FROM blog_categories ORDER BY id;
`
res := []BlogCategory{}
err := sqlx.Select(db, &res, q)
return res, tracederror.New(err)
}
func (db *ChannelDBReader) Search(query string) ([]models.Channel, error) {
q := `SELECT c.id, c.title, c.description, c.url, c.image, c.website
FROM channels c, plainto_tsquery($1) as q
WHERE (c.tsv @@ q)
ORDER BY ts_rank_cd(c.tsv, plainto_tsquery($1)) DESC LIMIT 20`
var channels []models.Channel
return channels, dbErr(sqlx.Select(db, &channels, q, query), q)
}
// GetFeedUsers returns all the users subscribed to a given feedURL
func (d *Handle) GetFeedUsers(feedURL string) ([]User, error) {
d.syncMutex.Lock()
defer d.syncMutex.Unlock()
var all []User
err := sqlx.Select(d.queryer, &all, `SELECT user.* FROM user
INNER JOIN user_feeds ON user.id = user_feeds.user_id
INNER JOIN feed_info ON user_feeds.feed_info_id = feed_info.id
WHERE feed_info.url = ?`, feedURL)
return all, err
}
// GetAllFeedsWithUsers returns all feeds from the database that have
// subscribers
func (d *Handle) GetAllFeedsWithUsers() ([]*FeedInfo, error) {
d.syncMutex.Lock()
defer d.syncMutex.Unlock()
var feeds []*FeedInfo
err := sqlx.Select(d.queryer, &feeds, `SELECT feed_info.* FROM feed_info
LEFT JOIN user_feeds ON feed_info.id = user_feeds.feed_info_id
GROUP BY user_feeds.feed_info_id
HAVING COUNT(user_feeds.feed_info_id) > 0`)
return feeds, err
}
func IsValidCategory(db sqlx.Ext, category string) (bool, error) {
q := `SELECT * FROM blog_categories WHERE category = $1`
res := []BlogCategory{}
err := sqlx.Select(db, &res, q, category)
if err != nil {
return false, tracederror.New(err)
}
return len(res) == 1, nil
}
func (db *ChannelDBReader) SelectSubscribed(userID int64) ([]models.Channel, error) {
q := `SELECT c.id, c.title, c.description, c.image, c.url, c.website
FROM channels c
JOIN subscriptions s ON s.channel_id = c.id
WHERE s.user_id=$1 AND title IS NOT NULL AND title != ''
GROUP BY c.id
ORDER BY title
`
var channels []models.Channel
return channels, dbErr(sqlx.Select(db, &channels, q, userID), q)
}
func (db *PodcastDBReader) SearchBookmarked(query string, userID int64) ([]models.Podcast, error) {
q := `SELECT p.id, p.title, p.enclosure_url, p.description,
p.channel_id, p.pub_date, c.title AS name, c.image, p.source
FROM podcasts p, plainto_tsquery($2) as q, channels c, bookmarks b
WHERE (p.tsv @@ q OR c.tsv @@ q)
AND p.channel_id = c.id
AND b.podcast_id = p.id
AND b.user_id=$1
ORDER BY ts_rank_cd(p.tsv, plainto_tsquery($2)) DESC LIMIT $3`
var podcasts []models.Podcast
return podcasts, dbErr(sqlx.Select(db, &podcasts, q, userID, query, maxSearchRows), q)
}
// GetFeedsByName returns all feeds that contain the given string in their
// name.
func (d *Handle) GetFeedsByName(name string) ([]*FeedInfo, error) {
d.syncMutex.Lock()
defer d.syncMutex.Unlock()
var feeds []*FeedInfo
err := sqlx.Select(d.queryer, &feeds, `SELECT feed_info.* FROM feed_info
INNER JOIN user_feeds ON feed_info.id = user_feeds.feed_info_id
INNER JOIN user ON user_feeds.user_id = user.id
WHERE feed_info.name LIKE ?
ORDER BY feed_info.name`, fmt.Sprintf("%%%s%%", name))
return feeds, err
}
// GetMostRecentGUIDsForFeed retrieves the most recent GUIDs for a given feed
// up to max. GUIDs are returned ordered with the most recent first.
func (d *Handle) GetMostRecentGUIDsForFeed(feedID int64, max int) ([]string, error) {
var items []FeedItem
d.syncMutex.Lock()
defer d.syncMutex.Unlock()
err := sqlx.Select(d.queryer, &items, "SELECT guid FROM feed_item WHERE feed_info_id = ? ORDER BY added_on DESC LIMIT ?", feedID, max)
d.logger.Infof("Got last %d guids for feed_id: %d.", len(items), feedID)
knownGuids := make([]string, len(items))
for i, v := range items {
knownGuids[i] = v.GUID
}
return knownGuids, err
}
func GetRecentBlogPosts(db sqlx.Ext, count int32, offset int32) ([]BlogPost, error) {
q := `
SELECT p.*, c.category AS category_value
FROM blog_posts p JOIN blog_categories c ON c.id = p.category
WHERE publish IS NOT NULL AND NOT c.hidden
ORDER BY publish DESC
LIMIT $1
OFFSET $2
`
res := []BlogPost{}
err := sqlx.Select(db, &res, q, count, offset)
return res, tracederror.New(err)
}
func RunYarpNarp() {
var zusagen Zusagen
dbx := sqlx.NewDb(db, *driver)
if err := sqlx.Select(dbx, &zusagen, "SELECT nick, kommt, kommentar, registered FROM zusagen"); err != nil {
fmt.Fprintln(os.Stderr, "Datenbankfehler:", err)
return
}
sort.Sort(zusagen)
width, trunc := TermWidth()
width -= zusagen.minWidth()
w := tabwriter.NewWriter(os.Stdout, 0, 4, 1, ' ', tabwriter.DiscardEmptyColumns)
fmt.Fprintf(w, "Nick\tKommt\tLetzte Änderung\t%s\n", maybeTruncate("Kommentar", width, trunc))
for _, z := range zusagen {
fmt.Fprintf(w, "%s\t%v\t%s\t%s\n", z.Nick, formatBool(z.Kommt), z.Registered.In(time.Local).Format("2006-01-02 15:04:05"), maybeTruncate(z.Kommentar, width, trunc))
}
w.Flush()
}
func (db *BookmarkDBReader) SelectByUserID(userID int64) ([]int64, error) {
q := "SELECT podcast_id FROM bookmarks WHERE user_id=$1"
var result []int64
err := sqlx.Select(db, &result, q, userID)
return result, dbErr(err, q)
}
func (db *SubscriptionDBReader) SelectByUserID(userID int64) ([]int64, error) {
q := "SELECT channel_id FROM subscriptions WHERE user_id=$1"
var result []int64
err := sqlx.Select(db, &result, q, userID)
return result, dbErr(err, q)
}
func (db *ChannelDBReader) SelectAll() ([]models.Channel, error) {
q := `SELECT id, title, description, categories, url, image, website
FROM channels`
var channels []models.Channel
return channels, dbErr(sqlx.Select(db, &channels, q), q)
}