-
Notifications
You must be signed in to change notification settings - Fork 0
/
get.go
143 lines (132 loc) · 4.43 KB
/
get.go
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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
package video
import (
"context"
"database/sql"
"errors"
"fmt"
"github.com/ystv/web-api/services/creator/types/series"
"github.com/ystv/web-api/services/creator/types/video"
)
// GetItem returns a VideoItem by it's ID.
func (s *Store) GetItem(ctx context.Context, videoID int) (video.Item, error) {
v := video.Item{}
err := s.db.GetContext(ctx, &v,
`SELECT item.video_id, item.series_id, item.name video_name, item.url,
item.description, item.thumbnail, duration, item.views, item.tags,
item.status, preset.preset_id, preset.name preset_name, broadcast_date,
item.created_at, users.user_id AS created_by_id, users.nickname AS created_by_nick
FROM video.items item
LEFT JOIN video.encode_presets preset ON item.preset_id = preset.preset_id
INNER JOIN people.users users ON users.user_id = item.created_by
WHERE video_id = $1
LIMIT 1;`, videoID)
if err != nil {
err = fmt.Errorf("failed to get video meta: %w", err)
return video.Item{}, err
}
err = s.db.SelectContext(ctx, &v.Files,
`SELECT uri, name, status, size, mime_type
FROM video.files file
INNER JOIN video.encode_formats format ON file.format_id = format.format_id
WHERE video_id = $1;`, videoID)
if err != nil {
err = fmt.Errorf("failed to get video files: %w", err)
return video.Item{}, err
}
return v, nil
}
// ListMeta returns a list of VideoMeta's
func (s *Store) ListMeta(ctx context.Context) ([]video.Meta, error) {
v := []video.Meta{}
err := s.db.SelectContext(ctx, &v,
`SELECT video_id, series_id, name video_name, url,
duration, views, tags, status, broadcast_date, created_at
FROM video.items
ORDER BY broadcast_date DESC;`)
return v, err
}
// ListMetaByUser returns a list of VideoMeta's for a given user
func (s *Store) ListMetaByUser(ctx context.Context, userID int) ([]video.Meta, error) {
v := []video.Meta{}
err := s.db.SelectContext(ctx, &v,
`SELECT video_id, series_id, name video_name, url,
duration, views, tags, status, broadcast_date, created_at
FROM video.items
WHERE created_by = $1
ORDER BY broadcast_date DESC;`, userID)
return v, err
}
// ListByCalendarMonth returns a list of VideoMeta's for a given month/year
func (s *Store) ListByCalendarMonth(ctx context.Context, year, month int) ([]video.MetaCal, error) {
v := []video.MetaCal{}
err := s.db.SelectContext(ctx, &v,
`SELECT video_id, name, status, broadcast_date
FROM video.items
WHERE EXTRACT(YEAR FROM broadcast_date) = $1 AND
EXTRACT(MONTH FROM broadcast_date) = $2;`, year, month)
return v, err
}
// OfSeries returns all the videos belonging to a series
func (s *Store) OfSeries(ctx context.Context, seriesID int) ([]video.Meta, error) {
v := []video.Meta{}
//TODO Update this select to fill all fields
err := s.db.Select(&v,
`SELECT video_id, series_id, name video_name, url,
duration, views, tags, status, broadcast_date, created_at
FROM video.items
WHERE series_id = $1;`, seriesID)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
return []video.Meta{}, series.ErrChildrenVideosNotFound
}
return []video.Meta{}, err
}
return v, nil
}
// Search performs a full-text search on video library
//
// Uses postgres' full-text search, video and series tables to try to make some sense
func (m *Store) Search(ctx context.Context, query string) ([]video.Meta, error) {
videos := []video.Meta{}
err := m.db.SelectContext(ctx, &videos,
`SELECT
video_id,
name,
url,
description,
thumbnail,
broadcast_date,
views,
duration,
tags,
status
FROM (
SELECT
video.video_id,
video.name,
video.url,
video.description,
video.thumbnail,
video.views,
video.duration,
video.tags,
video.status,
video.broadcast_date broadcast_date,
to_tsvector(video.name) || ' ' ||
to_tsvector(video.description) || ' ' ||
to_tsvector(unnest(video.tags)) || ' ' ||
to_tsvector(CAST(video.broadcast_date AS text)) || ' ' ||
to_tsvector(unnest(array_agg(series.name))) || ' ' ||
to_tsvector(unnest(array_agg(series.description)))
AS document
FROM video.items video
INNER JOIN video.series series ON video.series_id = series.series_id
GROUP BY video.video_id) p_search,
ts_rank_cd(p_search.document, replace(plainto_tsquery($1)::text, '&', '|')::tsquery) rank
WHERE p_search.document @@ replace(plainto_tsquery($1)::text, '&', '|')::tsquery
ORDER BY rank DESC;`, query)
if err != nil {
return nil, fmt.Errorf("failed to search videos: %w", err)
}
return videos, nil
}