aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
Diffstat
-rw-r--r--server/lib/client-html.ts2
-rw-r--r--server/models/video/formatter/video-format-utils.ts (renamed from server/models/video/video-format-utils.ts)20
-rw-r--r--server/models/video/sql/abstract-videos-query-builder.ts15
-rw-r--r--server/models/video/sql/video-model-builder.ts162
-rw-r--r--server/models/video/sql/videos-id-list-query-builder.ts609
-rw-r--r--server/models/video/sql/videos-model-list-query-builder.ts234
-rw-r--r--server/models/video/video-query-builder.ts599
-rw-r--r--server/models/video/video.ts187
8 files changed, 1050 insertions, 778 deletions
diff --git a/server/lib/client-html.ts b/server/lib/client-html.ts
index 4068e3d7b..0191b55ef 100644
--- a/server/lib/client-html.ts
+++ b/server/lib/client-html.ts
@@ -21,9 +21,9 @@ import {
21 WEBSERVER 21 WEBSERVER
22} from '../initializers/constants' 22} from '../initializers/constants'
23import { AccountModel } from '../models/account/account' 23import { AccountModel } from '../models/account/account'
24import { getActivityStreamDuration } from '../models/video/formatter/video-format-utils'
24import { VideoModel } from '../models/video/video' 25import { VideoModel } from '../models/video/video'
25import { VideoChannelModel } from '../models/video/video-channel' 26import { VideoChannelModel } from '../models/video/video-channel'
26import { getActivityStreamDuration } from '../models/video/video-format-utils'
27import { VideoPlaylistModel } from '../models/video/video-playlist' 27import { VideoPlaylistModel } from '../models/video/video-playlist'
28import { MAccountActor, MChannelActor } from '../types/models' 28import { MAccountActor, MChannelActor } from '../types/models'
29import { ServerConfigManager } from './server-config-manager' 29import { ServerConfigManager } from './server-config-manager'
diff --git a/server/models/video/video-format-utils.ts b/server/models/video/formatter/video-format-utils.ts
index 551cb2842..5ddbf74da 100644
--- a/server/models/video/video-format-utils.ts
+++ b/server/models/video/formatter/video-format-utils.ts
@@ -1,17 +1,17 @@
1import { generateMagnetUri } from '@server/helpers/webtorrent' 1import { generateMagnetUri } from '@server/helpers/webtorrent'
2import { getLocalVideoFileMetadataUrl } from '@server/lib/video-paths' 2import { getLocalVideoFileMetadataUrl } from '@server/lib/video-paths'
3import { VideoFile } from '@shared/models/videos/video-file.model' 3import { VideoFile } from '@shared/models/videos/video-file.model'
4import { ActivityTagObject, ActivityUrlObject, VideoObject } from '../../../shared/models/activitypub/objects' 4import { ActivityTagObject, ActivityUrlObject, VideoObject } from '../../../../shared/models/activitypub/objects'
5import { Video, VideoDetails } from '../../../shared/models/videos' 5import { Video, VideoDetails } from '../../../../shared/models/videos'
6import { VideoStreamingPlaylist } from '../../../shared/models/videos/video-streaming-playlist.model' 6import { VideoStreamingPlaylist } from '../../../../shared/models/videos/video-streaming-playlist.model'
7import { isArray } from '../../helpers/custom-validators/misc' 7import { isArray } from '../../../helpers/custom-validators/misc'
8import { MIMETYPES, WEBSERVER } from '../../initializers/constants' 8import { MIMETYPES, WEBSERVER } from '../../../initializers/constants'
9import { 9import {
10 getLocalVideoCommentsActivityPubUrl, 10 getLocalVideoCommentsActivityPubUrl,
11 getLocalVideoDislikesActivityPubUrl, 11 getLocalVideoDislikesActivityPubUrl,
12 getLocalVideoLikesActivityPubUrl, 12 getLocalVideoLikesActivityPubUrl,
13 getLocalVideoSharesActivityPubUrl 13 getLocalVideoSharesActivityPubUrl
14} from '../../lib/activitypub/url' 14} from '../../../lib/activitypub/url'
15import { 15import {
16 MStreamingPlaylistRedundanciesOpt, 16 MStreamingPlaylistRedundanciesOpt,
17 MVideo, 17 MVideo,
@@ -19,10 +19,10 @@ import {
19 MVideoFile, 19 MVideoFile,
20 MVideoFormattable, 20 MVideoFormattable,
21 MVideoFormattableDetails 21 MVideoFormattableDetails
22} from '../../types/models' 22} from '../../../types/models'
23import { MVideoFileRedundanciesOpt } from '../../types/models/video/video-file' 23import { MVideoFileRedundanciesOpt } from '../../../types/models/video/video-file'
24import { VideoModel } from './video' 24import { VideoModel } from '../video'
25import { VideoCaptionModel } from './video-caption' 25import { VideoCaptionModel } from '../video-caption'
26 26
27export type VideoFormattingJSONOptions = { 27export type VideoFormattingJSONOptions = {
28 completeDescription?: boolean 28 completeDescription?: boolean
diff --git a/server/models/video/sql/abstract-videos-query-builder.ts b/server/models/video/sql/abstract-videos-query-builder.ts
new file mode 100644
index 000000000..597a02af7
--- /dev/null
+++ b/server/models/video/sql/abstract-videos-query-builder.ts
@@ -0,0 +1,15 @@
1import { logger } from '@server/helpers/logger'
2import { Sequelize, QueryTypes } from 'sequelize'
3
4export class AbstractVideosQueryBuilder {
5 protected sequelize: Sequelize
6
7 protected query: string
8 protected replacements: any = {}
9
10 protected runQuery (nest?: boolean) {
11 logger.info('Running video query.', { query: this.query, replacements: this.replacements })
12
13 return this.sequelize.query<any>(this.query, { replacements: this.replacements, type: QueryTypes.SELECT, nest })
14 }
15}
diff --git a/server/models/video/sql/video-model-builder.ts b/server/models/video/sql/video-model-builder.ts
new file mode 100644
index 000000000..c428312fe
--- /dev/null
+++ b/server/models/video/sql/video-model-builder.ts
@@ -0,0 +1,162 @@
1import { pick } from 'lodash'
2import { AccountModel } from '@server/models/account/account'
3import { ActorModel } from '@server/models/actor/actor'
4import { ActorImageModel } from '@server/models/actor/actor-image'
5import { ServerModel } from '@server/models/server/server'
6import { UserVideoHistoryModel } from '@server/models/user/user-video-history'
7import { ThumbnailModel } from '../thumbnail'
8import { VideoModel } from '../video'
9import { VideoChannelModel } from '../video-channel'
10import { VideoFileModel } from '../video-file'
11import { VideoStreamingPlaylistModel } from '../video-streaming-playlist'
12
13function buildVideosFromRows (rows: any[]) {
14 const videosMemo: { [ id: number ]: VideoModel } = {}
15 const videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel } = {}
16
17 const thumbnailsDone = new Set<number>()
18 const historyDone = new Set<number>()
19 const videoFilesDone = new Set<number>()
20
21 const videos: VideoModel[] = []
22
23 const avatarKeys = [ 'id', 'filename', 'fileUrl', 'onDisk', 'createdAt', 'updatedAt' ]
24 const actorKeys = [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ]
25 const serverKeys = [ 'id', 'host' ]
26 const videoFileKeys = [
27 'id',
28 'createdAt',
29 'updatedAt',
30 'resolution',
31 'size',
32 'extname',
33 'filename',
34 'fileUrl',
35 'torrentFilename',
36 'torrentUrl',
37 'infoHash',
38 'fps',
39 'videoId',
40 'videoStreamingPlaylistId'
41 ]
42 const videoStreamingPlaylistKeys = [ 'id', 'type', 'playlistUrl' ]
43 const videoKeys = [
44 'id',
45 'uuid',
46 'name',
47 'category',
48 'licence',
49 'language',
50 'privacy',
51 'nsfw',
52 'description',
53 'support',
54 'duration',
55 'views',
56 'likes',
57 'dislikes',
58 'remote',
59 'isLive',
60 'url',
61 'commentsEnabled',
62 'downloadEnabled',
63 'waitTranscoding',
64 'state',
65 'publishedAt',
66 'originallyPublishedAt',
67 'channelId',
68 'createdAt',
69 'updatedAt'
70 ]
71 const buildOpts = { raw: true }
72
73 function buildActor (rowActor: any) {
74 const avatarModel = rowActor.Avatar.id !== null
75 ? new ActorImageModel(pick(rowActor.Avatar, avatarKeys), buildOpts)
76 : null
77
78 const serverModel = rowActor.Server.id !== null
79 ? new ServerModel(pick(rowActor.Server, serverKeys), buildOpts)
80 : null
81
82 const actorModel = new ActorModel(pick(rowActor, actorKeys), buildOpts)
83 actorModel.Avatar = avatarModel
84 actorModel.Server = serverModel
85
86 return actorModel
87 }
88
89 for (const row of rows) {
90 if (!videosMemo[row.id]) {
91 // Build Channel
92 const channel = row.VideoChannel
93 const channelModel = new VideoChannelModel(pick(channel, [ 'id', 'name', 'description', 'actorId' ]), buildOpts)
94 channelModel.Actor = buildActor(channel.Actor)
95
96 const account = row.VideoChannel.Account
97 const accountModel = new AccountModel(pick(account, [ 'id', 'name' ]), buildOpts)
98 accountModel.Actor = buildActor(account.Actor)
99
100 channelModel.Account = accountModel
101
102 const videoModel = new VideoModel(pick(row, videoKeys), buildOpts)
103 videoModel.VideoChannel = channelModel
104
105 videoModel.UserVideoHistories = []
106 videoModel.Thumbnails = []
107 videoModel.VideoFiles = []
108 videoModel.VideoStreamingPlaylists = []
109
110 videosMemo[row.id] = videoModel
111 // Don't take object value to have a sorted array
112 videos.push(videoModel)
113 }
114
115 const videoModel = videosMemo[row.id]
116
117 if (row.userVideoHistory?.id && !historyDone.has(row.userVideoHistory.id)) {
118 const historyModel = new UserVideoHistoryModel(pick(row.userVideoHistory, [ 'id', 'currentTime' ]), buildOpts)
119 videoModel.UserVideoHistories.push(historyModel)
120
121 historyDone.add(row.userVideoHistory.id)
122 }
123
124 if (row.Thumbnails?.id && !thumbnailsDone.has(row.Thumbnails.id)) {
125 const thumbnailModel = new ThumbnailModel(pick(row.Thumbnails, [ 'id', 'type', 'filename' ]), buildOpts)
126 videoModel.Thumbnails.push(thumbnailModel)
127
128 thumbnailsDone.add(row.Thumbnails.id)
129 }
130
131 if (row.VideoFiles?.id && !videoFilesDone.has(row.VideoFiles.id)) {
132 const videoFileModel = new VideoFileModel(pick(row.VideoFiles, videoFileKeys), buildOpts)
133 videoModel.VideoFiles.push(videoFileModel)
134
135 videoFilesDone.add(row.VideoFiles.id)
136 }
137
138 if (row.VideoStreamingPlaylists?.id && !videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]) {
139 const streamingPlaylist = new VideoStreamingPlaylistModel(pick(row.VideoStreamingPlaylists, videoStreamingPlaylistKeys), buildOpts)
140 streamingPlaylist.VideoFiles = []
141
142 videoModel.VideoStreamingPlaylists.push(streamingPlaylist)
143
144 videoStreamingPlaylistMemo[streamingPlaylist.id] = streamingPlaylist
145 }
146
147 if (row.VideoStreamingPlaylists?.VideoFiles?.id && !videoFilesDone.has(row.VideoStreamingPlaylists.VideoFiles.id)) {
148 const streamingPlaylist = videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]
149
150 const videoFileModel = new VideoFileModel(pick(row.VideoStreamingPlaylists.VideoFiles, videoFileKeys), buildOpts)
151 streamingPlaylist.VideoFiles.push(videoFileModel)
152
153 videoFilesDone.add(row.VideoStreamingPlaylists.VideoFiles.id)
154 }
155 }
156
157 return videos
158}
159
160export {
161 buildVideosFromRows
162}
diff --git a/server/models/video/sql/videos-id-list-query-builder.ts b/server/models/video/sql/videos-id-list-query-builder.ts
new file mode 100644
index 000000000..7bb942ea4
--- /dev/null
+++ b/server/models/video/sql/videos-id-list-query-builder.ts
@@ -0,0 +1,609 @@
1import { Sequelize } from 'sequelize'
2import validator from 'validator'
3import { exists } from '@server/helpers/custom-validators/misc'
4import { buildDirectionAndField, createSafeIn } from '@server/models/utils'
5import { MUserAccountId, MUserId } from '@server/types/models'
6import { VideoFilter, VideoPrivacy, VideoState } from '@shared/models'
7import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder'
8
9export type BuildVideosListQueryOptions = {
10 attributes?: string[]
11
12 serverAccountId: number
13 followerActorId: number
14 includeLocalVideos: boolean
15
16 count: number
17 start: number
18 sort: string
19
20 nsfw?: boolean
21 filter?: VideoFilter
22 isLive?: boolean
23
24 categoryOneOf?: number[]
25 licenceOneOf?: number[]
26 languageOneOf?: string[]
27 tagsOneOf?: string[]
28 tagsAllOf?: string[]
29
30 withFiles?: boolean
31
32 accountId?: number
33 videoChannelId?: number
34
35 videoPlaylistId?: number
36
37 trendingAlgorithm?: string // best, hot, or any other algorithm implemented
38 trendingDays?: number
39
40 user?: MUserAccountId
41 historyOfUser?: MUserId
42
43 startDate?: string // ISO 8601
44 endDate?: string // ISO 8601
45 originallyPublishedStartDate?: string
46 originallyPublishedEndDate?: string
47
48 durationMin?: number // seconds
49 durationMax?: number // seconds
50
51 search?: string
52
53 isCount?: boolean
54
55 group?: string
56 having?: string
57}
58
59export class VideosIdListQueryBuilder extends AbstractVideosQueryBuilder {
60 private attributes: string[]
61
62 protected replacements: any = {}
63 private readonly and: string[] = []
64 private joins: string[] = []
65
66 private readonly cte: string[] = []
67
68 private group = ''
69 private having = ''
70
71 private sort = ''
72 private limit = ''
73 private offset = ''
74
75 constructor (protected readonly sequelize: Sequelize) {
76 super()
77 }
78
79 queryVideoIds (options: BuildVideosListQueryOptions) {
80 this.buildIdsListQuery(options)
81
82 return this.runQuery()
83 }
84
85 countVideoIds (countOptions: BuildVideosListQueryOptions): Promise<number> {
86 this.buildIdsListQuery(countOptions)
87
88 return this.runQuery().then(rows => rows.length !== 0 ? rows[0].total : 0)
89 }
90
91 getIdsListQueryAndSort (options: BuildVideosListQueryOptions) {
92 this.buildIdsListQuery(options)
93 return { query: this.query, sort: this.sort, replacements: this.replacements }
94 }
95
96 private buildIdsListQuery (options: BuildVideosListQueryOptions) {
97 this.attributes = options.attributes || [ '"video"."id"' ]
98
99 if (options.group) this.group = options.group
100 if (options.having) this.having = options.having
101
102 this.joins = this.joins.concat([
103 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"',
104 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"',
105 'INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id"'
106 ])
107
108 this.whereNotBlacklisted()
109
110 if (options.serverAccountId) {
111 this.whereNotBlocked(options.serverAccountId, options.user)
112 }
113
114 // Only list public/published videos
115 if (!options.filter || (options.filter !== 'all-local' && options.filter !== 'all')) {
116 this.whereStateAndPrivacyAvailable(options.user)
117 }
118
119 if (options.videoPlaylistId) {
120 this.joinPlaylist(options.videoPlaylistId)
121 }
122
123 if (options.filter && (options.filter === 'local' || options.filter === 'all-local')) {
124 this.whereOnlyLocal()
125 }
126
127 if (options.accountId) {
128 this.whereAccountId(options.accountId)
129 }
130
131 if (options.videoChannelId) {
132 this.whereChannelId(options.videoChannelId)
133 }
134
135 if (options.followerActorId) {
136 this.whereFollowerActorId(options.followerActorId, options.includeLocalVideos)
137 }
138
139 if (options.withFiles === true) {
140 this.whereFileExists()
141 }
142
143 if (options.tagsOneOf) {
144 this.whereTagsOneOf(options.tagsOneOf)
145 }
146
147 if (options.tagsAllOf) {
148 this.whereTagsAllOf(options.tagsAllOf)
149 }
150
151 if (options.nsfw === true) {
152 this.whereNSFW()
153 } else if (options.nsfw === false) {
154 this.whereSFW()
155 }
156
157 if (options.isLive === true) {
158 this.whereLive()
159 } else if (options.isLive === false) {
160 this.whereVOD()
161 }
162
163 if (options.categoryOneOf) {
164 this.whereCategoryOneOf(options.categoryOneOf)
165 }
166
167 if (options.licenceOneOf) {
168 this.whereLicenceOneOf(options.licenceOneOf)
169 }
170
171 if (options.languageOneOf) {
172 this.whereLanguageOneOf(options.languageOneOf)
173 }
174
175 // We don't exclude results in this so if we do a count we don't need to add this complex clause
176 if (options.isCount !== true) {
177 if (options.trendingDays) {
178 this.groupForTrending(options.trendingDays)
179 } else if ([ 'best', 'hot' ].includes(options.trendingAlgorithm)) {
180 this.groupForHotOrBest(options.trendingAlgorithm, options.user)
181 }
182 }
183
184 if (options.historyOfUser) {
185 this.joinHistory(options.historyOfUser.id)
186 }
187
188 if (options.startDate) {
189 this.whereStartDate(options.startDate)
190 }
191
192 if (options.endDate) {
193 this.whereEndDate(options.endDate)
194 }
195
196 if (options.originallyPublishedStartDate) {
197 this.whereOriginallyPublishedStartDate(options.originallyPublishedStartDate)
198 }
199
200 if (options.originallyPublishedEndDate) {
201 this.whereOriginallyPublishedEndDate(options.originallyPublishedEndDate)
202 }
203
204 if (options.durationMin) {
205 this.whereDurationMin(options.durationMin)
206 }
207
208 if (options.durationMax) {
209 this.whereDurationMax(options.durationMax)
210 }
211
212 this.whereSearch(options.search)
213
214 if (options.isCount === true) {
215 this.setCountAttribute()
216 } else {
217 if (exists(options.sort)) {
218 this.setSort(options.sort)
219 }
220
221 if (exists(options.count)) {
222 this.setLimit(options.count)
223 }
224
225 if (exists(options.start)) {
226 this.setOffset(options.start)
227 }
228 }
229
230 const cteString = this.cte.length !== 0
231 ? `WITH ${this.cte.join(', ')} `
232 : ''
233
234 this.query = cteString +
235 'SELECT ' + this.attributes.join(', ') + ' ' +
236 'FROM "video" ' + this.joins.join(' ') + ' ' +
237 'WHERE ' + this.and.join(' AND ') + ' ' +
238 this.group + ' ' +
239 this.having + ' ' +
240 this.sort + ' ' +
241 this.limit + ' ' +
242 this.offset
243 }
244
245 private setCountAttribute () {
246 this.attributes = [ 'COUNT(*) as "total"' ]
247 }
248
249 private joinHistory (userId: number) {
250 this.joins.push('INNER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId"')
251
252 this.and.push('"userVideoHistory"."userId" = :historyOfUser')
253
254 this.replacements.historyOfUser = userId
255 }
256
257 private joinPlaylist (playlistId: number) {
258 this.joins.push(
259 'INNER JOIN "videoPlaylistElement" "video"."id" = "videoPlaylistElement"."videoId" ' +
260 'AND "videoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
261 )
262
263 this.replacements.videoPlaylistId = playlistId
264 }
265
266 private whereStateAndPrivacyAvailable (user?: MUserAccountId) {
267 this.and.push(
268 `("video"."state" = ${VideoState.PUBLISHED} OR ` +
269 `("video"."state" = ${VideoState.TO_TRANSCODE} AND "video"."waitTranscoding" IS false))`
270 )
271
272 if (user) {
273 this.and.push(
274 `("video"."privacy" = ${VideoPrivacy.PUBLIC} OR "video"."privacy" = ${VideoPrivacy.INTERNAL})`
275 )
276 } else { // Or only public videos
277 this.and.push(
278 `"video"."privacy" = ${VideoPrivacy.PUBLIC}`
279 )
280 }
281 }
282
283 private whereOnlyLocal () {
284 this.and.push('"video"."remote" IS FALSE')
285 }
286
287 private whereAccountId (accountId: number) {
288 this.and.push('"account"."id" = :accountId')
289 this.replacements.accountId = accountId
290 }
291
292 private whereChannelId (channelId: number) {
293 this.and.push('"videoChannel"."id" = :videoChannelId')
294 this.replacements.videoChannelId = channelId
295 }
296
297 private whereFollowerActorId (followerActorId: number, includeLocalVideos: boolean) {
298 let query =
299 '(' +
300 ' EXISTS (' +
301 ' SELECT 1 FROM "videoShare" ' +
302 ' INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId" ' +
303 ' AND "actorFollowShare"."actorId" = :followerActorId AND "actorFollowShare"."state" = \'accepted\' ' +
304 ' WHERE "videoShare"."videoId" = "video"."id"' +
305 ' )' +
306 ' OR' +
307 ' EXISTS (' +
308 ' SELECT 1 from "actorFollow" ' +
309 ' WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = :followerActorId ' +
310 ' AND "actorFollow"."state" = \'accepted\'' +
311 ' )'
312
313 if (includeLocalVideos) {
314 query += ' OR "video"."remote" IS FALSE'
315 }
316
317 query += ')'
318
319 this.and.push(query)
320 this.replacements.followerActorId = followerActorId
321 }
322
323 private whereFileExists () {
324 this.and.push(
325 '(' +
326 ' EXISTS (SELECT 1 FROM "videoFile" WHERE "videoFile"."videoId" = "video"."id") ' +
327 ' OR EXISTS (' +
328 ' SELECT 1 FROM "videoStreamingPlaylist" ' +
329 ' INNER JOIN "videoFile" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist"."id" ' +
330 ' WHERE "videoStreamingPlaylist"."videoId" = "video"."id"' +
331 ' )' +
332 ')'
333 )
334 }
335
336 private whereTagsOneOf (tagsOneOf: string[]) {
337 const tagsOneOfLower = tagsOneOf.map(t => t.toLowerCase())
338
339 this.and.push(
340 'EXISTS (' +
341 ' SELECT 1 FROM "videoTag" ' +
342 ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
343 ' WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsOneOfLower) + ') ' +
344 ' AND "video"."id" = "videoTag"."videoId"' +
345 ')'
346 )
347 }
348
349 private whereTagsAllOf (tagsAllOf: string[]) {
350 const tagsAllOfLower = tagsAllOf.map(t => t.toLowerCase())
351
352 this.and.push(
353 'EXISTS (' +
354 ' SELECT 1 FROM "videoTag" ' +
355 ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
356 ' WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsAllOfLower) + ') ' +
357 ' AND "video"."id" = "videoTag"."videoId" ' +
358 ' GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length +
359 ')'
360 )
361 }
362
363 private whereCategoryOneOf (categoryOneOf: number[]) {
364 this.and.push('"video"."category" IN (:categoryOneOf)')
365 this.replacements.categoryOneOf = categoryOneOf
366 }
367
368 private whereLicenceOneOf (licenceOneOf: number[]) {
369 this.and.push('"video"."licence" IN (:licenceOneOf)')
370 this.replacements.licenceOneOf = licenceOneOf
371 }
372
373 private whereLanguageOneOf (languageOneOf: string[]) {
374 const languages = languageOneOf.filter(l => l && l !== '_unknown')
375 const languagesQueryParts: string[] = []
376
377 if (languages.length !== 0) {
378 languagesQueryParts.push('"video"."language" IN (:languageOneOf)')
379 this.replacements.languageOneOf = languages
380
381 languagesQueryParts.push(
382 'EXISTS (' +
383 ' SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' +
384 ' IN (' + createSafeIn(this.sequelize, languages) + ') AND ' +
385 ' "videoCaption"."videoId" = "video"."id"' +
386 ')'
387 )
388 }
389
390 if (languageOneOf.includes('_unknown')) {
391 languagesQueryParts.push('"video"."language" IS NULL')
392 }
393
394 if (languagesQueryParts.length !== 0) {
395 this.and.push('(' + languagesQueryParts.join(' OR ') + ')')
396 }
397 }
398
399 private whereNSFW () {
400 this.and.push('"video"."nsfw" IS TRUE')
401 }
402
403 private whereSFW () {
404 this.and.push('"video"."nsfw" IS FALSE')
405 }
406
407 private whereLive () {
408 this.and.push('"video"."isLive" IS TRUE')
409 }
410
411 private whereVOD () {
412 this.and.push('"video"."isLive" IS FALSE')
413 }
414
415 private whereNotBlocked (serverAccountId: number, user?: MUserAccountId) {
416 const blockerIds = [ serverAccountId ]
417 if (user) blockerIds.push(user.Account.id)
418
419 const inClause = createSafeIn(this.sequelize, blockerIds)
420
421 this.and.push(
422 'NOT EXISTS (' +
423 ' SELECT 1 FROM "accountBlocklist" ' +
424 ' WHERE "accountBlocklist"."accountId" IN (' + inClause + ') ' +
425 ' AND "accountBlocklist"."targetAccountId" = "account"."id" ' +
426 ')' +
427 'AND NOT EXISTS (' +
428 ' SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN (' + inClause + ') ' +
429 ' AND "serverBlocklist"."targetServerId" = "accountActor"."serverId"' +
430 ')'
431 )
432 }
433
434 private whereSearch (search?: string) {
435 if (!search) {
436 this.attributes.push('0 as similarity')
437 return
438 }
439
440 const escapedSearch = this.sequelize.escape(search)
441 const escapedLikeSearch = this.sequelize.escape('%' + search + '%')
442
443 this.cte.push(
444 '"trigramSearch" AS (' +
445 ' SELECT "video"."id", ' +
446 ` similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent(${escapedSearch}))) as similarity ` +
447 ' FROM "video" ' +
448 ' WHERE lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
449 ' lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
450 ')'
451 )
452
453 this.joins.push('LEFT JOIN "trigramSearch" ON "video"."id" = "trigramSearch"."id"')
454
455 let base = '(' +
456 ' "trigramSearch"."id" IS NOT NULL OR ' +
457 ' EXISTS (' +
458 ' SELECT 1 FROM "videoTag" ' +
459 ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
460 ` WHERE lower("tag"."name") = ${escapedSearch} ` +
461 ' AND "video"."id" = "videoTag"."videoId"' +
462 ' )'
463
464 if (validator.isUUID(search)) {
465 base += ` OR "video"."uuid" = ${escapedSearch}`
466 }
467
468 base += ')'
469
470 this.and.push(base)
471 this.attributes.push(`COALESCE("trigramSearch"."similarity", 0) as similarity`)
472 }
473
474 private whereNotBlacklisted () {
475 this.and.push('"video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")')
476 }
477
478 private whereStartDate (startDate: string) {
479 this.and.push('"video"."publishedAt" >= :startDate')
480 this.replacements.startDate = startDate
481 }
482
483 private whereEndDate (endDate: string) {
484 this.and.push('"video"."publishedAt" <= :endDate')
485 this.replacements.endDate = endDate
486 }
487
488 private whereOriginallyPublishedStartDate (startDate: string) {
489 this.and.push('"video"."originallyPublishedAt" >= :originallyPublishedStartDate')
490 this.replacements.originallyPublishedStartDate = startDate
491 }
492
493 private whereOriginallyPublishedEndDate (endDate: string) {
494 this.and.push('"video"."originallyPublishedAt" <= :originallyPublishedEndDate')
495 this.replacements.originallyPublishedEndDate = endDate
496 }
497
498 private whereDurationMin (durationMin: number) {
499 this.and.push('"video"."duration" >= :durationMin')
500 this.replacements.durationMin = durationMin
501 }
502
503 private whereDurationMax (durationMax: number) {
504 this.and.push('"video"."duration" <= :durationMax')
505 this.replacements.durationMax = durationMax
506 }
507
508 private groupForTrending (trendingDays: number) {
509 const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * trendingDays)
510
511 this.joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate')
512 this.replacements.viewsGteDate = viewsGteDate
513
514 this.attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"')
515
516 this.group = 'GROUP BY "video"."id"'
517 }
518
519 private groupForHotOrBest (trendingAlgorithm: string, user?: MUserAccountId) {
520 /**
521 * "Hotness" is a measure based on absolute view/comment/like/dislike numbers,
522 * with fixed weights only applied to their log values.
523 *
524 * This algorithm gives little chance for an old video to have a good score,
525 * for which recent spikes in interactions could be a sign of "hotness" and
526 * justify a better score. However there are multiple ways to achieve that
527 * goal, which is left for later. Yes, this is a TODO :)
528 *
529 * notes:
530 * - weights and base score are in number of half-days.
531 * - all comments are counted, regardless of being written by the video author or not
532 * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58
533 * - we have less interactions than on reddit, so multiply weights by an arbitrary factor
534 */
535 const weights = {
536 like: 3 * 50,
537 dislike: -3 * 50,
538 view: Math.floor((1 / 3) * 50),
539 comment: 2 * 50, // a comment takes more time than a like to do, but can be done multiple times
540 history: -2 * 50
541 }
542
543 this.joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"')
544
545 let attribute =
546 `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+)
547 `+ LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-)
548 `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+)
549 `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+)
550 '+ (SELECT (EXTRACT(epoch FROM "video"."publishedAt") - 1446156582) / 47000) ' // base score (in number of half-days)
551
552 if (trendingAlgorithm === 'best' && user) {
553 this.joins.push(
554 'LEFT JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :bestUser'
555 )
556 this.replacements.bestUser = user.id
557
558 attribute += `+ POWER(COUNT(DISTINCT "userVideoHistory"."id"), 2.0) * ${weights.history} `
559 }
560
561 attribute += 'AS "score"'
562 this.attributes.push(attribute)
563
564 this.group = 'GROUP BY "video"."id"'
565 }
566
567 private setSort (sort: string) {
568 if (sort === '-originallyPublishedAt' || sort === 'originallyPublishedAt') {
569 this.attributes.push('COALESCE("video"."originallyPublishedAt", "video"."publishedAt") AS "publishedAtForOrder"')
570 }
571
572 this.sort = this.buildOrder(sort)
573 }
574
575 private buildOrder (value: string) {
576 const { direction, field } = buildDirectionAndField(value)
577 if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field)
578
579 if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()'
580
581 if ([ 'trending', 'hot', 'best' ].includes(field.toLowerCase())) { // Sort by aggregation
582 return `ORDER BY "score" ${direction}, "video"."views" ${direction}`
583 }
584
585 let firstSort: string
586
587 if (field.toLowerCase() === 'match') { // Search
588 firstSort = '"similarity"'
589 } else if (field === 'originallyPublishedAt') {
590 firstSort = '"publishedAtForOrder"'
591 } else if (field.includes('.')) {
592 firstSort = field
593 } else {
594 firstSort = `"video"."${field}"`
595 }
596
597 return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC`
598 }
599
600 private setLimit (countArg: number) {
601 const count = parseInt(countArg + '', 10)
602 this.limit = `LIMIT ${count}`
603 }
604
605 private setOffset (startArg: number) {
606 const start = parseInt(startArg + '', 10)
607 this.offset = `OFFSET ${start}`
608 }
609}
diff --git a/server/models/video/sql/videos-model-list-query-builder.ts b/server/models/video/sql/videos-model-list-query-builder.ts
new file mode 100644
index 000000000..4ba9dd878
--- /dev/null
+++ b/server/models/video/sql/videos-model-list-query-builder.ts
@@ -0,0 +1,234 @@
1
2import { MUserId } from '@server/types/models'
3import { Sequelize } from 'sequelize'
4import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder'
5import { buildVideosFromRows } from './video-model-builder'
6import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder'
7
8export class VideosModelListQueryBuilder extends AbstractVideosQueryBuilder {
9 private attributes: { [key: string]: string }
10
11 private joins: string[] = []
12
13 private innerQuery: string
14 private innerSort: string
15
16 constructor (protected readonly sequelize: Sequelize) {
17 super()
18 }
19
20 queryVideos (options: BuildVideosListQueryOptions) {
21 this.buildInnerQuery(options)
22 this.buildListQueryFromIdsQuery(options)
23
24 return this.runQuery(true).then(rows => buildVideosFromRows(rows))
25 }
26
27 private buildInnerQuery (options: BuildVideosListQueryOptions) {
28 const idsQueryBuilder = new VideosIdListQueryBuilder(this.sequelize)
29 const { query, sort, replacements } = idsQueryBuilder.getIdsListQueryAndSort(options)
30
31 this.replacements = replacements
32 this.innerQuery = query
33 this.innerSort = sort
34 }
35
36 private buildListQueryFromIdsQuery (options: BuildVideosListQueryOptions) {
37 this.attributes = {
38 '"video".*': ''
39 }
40
41 this.joins = [ 'INNER JOIN "video" ON "tmp"."id" = "video"."id"' ]
42
43 this.includeChannels()
44 this.includeAccounts()
45 this.includeThumbnails()
46
47 if (options.withFiles) {
48 this.includeFiles()
49 }
50
51 if (options.user) {
52 this.includeUserHistory(options.user)
53 }
54
55 if (options.videoPlaylistId) {
56 this.includePlaylist(options.videoPlaylistId)
57 }
58
59 const select = this.buildSelect()
60
61 this.query = `${select} FROM (${this.innerQuery}) AS "tmp" ${this.joins.join(' ')} ${this.innerSort}`
62 }
63
64 private includeChannels () {
65 this.attributes = {
66 ...this.attributes,
67
68 '"VideoChannel"."id"': '"VideoChannel.id"',
69 '"VideoChannel"."name"': '"VideoChannel.name"',
70 '"VideoChannel"."description"': '"VideoChannel.description"',
71 '"VideoChannel"."actorId"': '"VideoChannel.actorId"',
72 '"VideoChannel->Actor"."id"': '"VideoChannel.Actor.id"',
73 '"VideoChannel->Actor"."preferredUsername"': '"VideoChannel.Actor.preferredUsername"',
74 '"VideoChannel->Actor"."url"': '"VideoChannel.Actor.url"',
75 '"VideoChannel->Actor"."serverId"': '"VideoChannel.Actor.serverId"',
76 '"VideoChannel->Actor"."avatarId"': '"VideoChannel.Actor.avatarId"',
77 '"VideoChannel->Actor->Server"."id"': '"VideoChannel.Actor.Server.id"',
78 '"VideoChannel->Actor->Server"."host"': '"VideoChannel.Actor.Server.host"',
79 '"VideoChannel->Actor->Avatar"."id"': '"VideoChannel.Actor.Avatar.id"',
80 '"VideoChannel->Actor->Avatar"."filename"': '"VideoChannel.Actor.Avatar.filename"',
81 '"VideoChannel->Actor->Avatar"."fileUrl"': '"VideoChannel.Actor.Avatar.fileUrl"',
82 '"VideoChannel->Actor->Avatar"."onDisk"': '"VideoChannel.Actor.Avatar.onDisk"',
83 '"VideoChannel->Actor->Avatar"."createdAt"': '"VideoChannel.Actor.Avatar.createdAt"',
84 '"VideoChannel->Actor->Avatar"."updatedAt"': '"VideoChannel.Actor.Avatar.updatedAt"'
85 }
86
87 this.joins = this.joins.concat([
88 'INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"',
89 'INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"',
90
91 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"',
92 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' +
93 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"'
94 ])
95 }
96
97 private includeAccounts () {
98 this.attributes = {
99 ...this.attributes,
100
101 '"VideoChannel->Account"."id"': '"VideoChannel.Account.id"',
102 '"VideoChannel->Account"."name"': '"VideoChannel.Account.name"',
103 '"VideoChannel->Account->Actor"."id"': '"VideoChannel.Account.Actor.id"',
104 '"VideoChannel->Account->Actor"."preferredUsername"': '"VideoChannel.Account.Actor.preferredUsername"',
105 '"VideoChannel->Account->Actor"."url"': '"VideoChannel.Account.Actor.url"',
106 '"VideoChannel->Account->Actor"."serverId"': '"VideoChannel.Account.Actor.serverId"',
107 '"VideoChannel->Account->Actor"."avatarId"': '"VideoChannel.Account.Actor.avatarId"',
108 '"VideoChannel->Account->Actor->Server"."id"': '"VideoChannel.Account.Actor.Server.id"',
109 '"VideoChannel->Account->Actor->Server"."host"': '"VideoChannel.Account.Actor.Server.host"',
110 '"VideoChannel->Account->Actor->Avatar"."id"': '"VideoChannel.Account.Actor.Avatar.id"',
111 '"VideoChannel->Account->Actor->Avatar"."filename"': '"VideoChannel.Account.Actor.Avatar.filename"',
112 '"VideoChannel->Account->Actor->Avatar"."fileUrl"': '"VideoChannel.Account.Actor.Avatar.fileUrl"',
113 '"VideoChannel->Account->Actor->Avatar"."onDisk"': '"VideoChannel.Account.Actor.Avatar.onDisk"',
114 '"VideoChannel->Account->Actor->Avatar"."createdAt"': '"VideoChannel.Account.Actor.Avatar.createdAt"',
115 '"VideoChannel->Account->Actor->Avatar"."updatedAt"': '"VideoChannel.Account.Actor.Avatar.updatedAt"'
116 }
117
118 this.joins = this.joins.concat([
119 'INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"',
120 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"',
121
122 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' +
123 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"',
124
125 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' +
126 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"'
127 ])
128 }
129
130 private includeThumbnails () {
131 this.attributes = {
132 ...this.attributes,
133
134 '"Thumbnails"."id"': '"Thumbnails.id"',
135 '"Thumbnails"."type"': '"Thumbnails.type"',
136 '"Thumbnails"."filename"': '"Thumbnails.filename"'
137 }
138
139 this.joins.push('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"')
140 }
141
142 private includeFiles () {
143 this.attributes = {
144 ...this.attributes,
145
146 '"VideoFiles"."id"': '"VideoFiles.id"',
147 '"VideoFiles"."createdAt"': '"VideoFiles.createdAt"',
148 '"VideoFiles"."updatedAt"': '"VideoFiles.updatedAt"',
149 '"VideoFiles"."resolution"': '"VideoFiles.resolution"',
150 '"VideoFiles"."size"': '"VideoFiles.size"',
151 '"VideoFiles"."extname"': '"VideoFiles.extname"',
152 '"VideoFiles"."filename"': '"VideoFiles.filename"',
153 '"VideoFiles"."fileUrl"': '"VideoFiles.fileUrl"',
154 '"VideoFiles"."torrentFilename"': '"VideoFiles.torrentFilename"',
155 '"VideoFiles"."torrentUrl"': '"VideoFiles.torrentUrl"',
156 '"VideoFiles"."infoHash"': '"VideoFiles.infoHash"',
157 '"VideoFiles"."fps"': '"VideoFiles.fps"',
158 '"VideoFiles"."videoId"': '"VideoFiles.videoId"',
159
160 '"VideoStreamingPlaylists"."id"': '"VideoStreamingPlaylists.id"',
161 '"VideoStreamingPlaylists"."playlistUrl"': '"VideoStreamingPlaylists.playlistUrl"',
162 '"VideoStreamingPlaylists"."type"': '"VideoStreamingPlaylists.type"',
163 '"VideoStreamingPlaylists->VideoFiles"."id"': '"VideoStreamingPlaylists.VideoFiles.id"',
164 '"VideoStreamingPlaylists->VideoFiles"."createdAt"': '"VideoStreamingPlaylists.VideoFiles.createdAt"',
165 '"VideoStreamingPlaylists->VideoFiles"."updatedAt"': '"VideoStreamingPlaylists.VideoFiles.updatedAt"',
166 '"VideoStreamingPlaylists->VideoFiles"."resolution"': '"VideoStreamingPlaylists.VideoFiles.resolution"',
167 '"VideoStreamingPlaylists->VideoFiles"."size"': '"VideoStreamingPlaylists.VideoFiles.size"',
168 '"VideoStreamingPlaylists->VideoFiles"."extname"': '"VideoStreamingPlaylists.VideoFiles.extname"',
169 '"VideoStreamingPlaylists->VideoFiles"."filename"': '"VideoStreamingPlaylists.VideoFiles.filename"',
170 '"VideoStreamingPlaylists->VideoFiles"."fileUrl"': '"VideoStreamingPlaylists.VideoFiles.fileUrl"',
171 '"VideoStreamingPlaylists->VideoFiles"."torrentFilename"': '"VideoStreamingPlaylists.VideoFiles.torrentFilename"',
172 '"VideoStreamingPlaylists->VideoFiles"."torrentUrl"': '"VideoStreamingPlaylists.VideoFiles.torrentUrl"',
173 '"VideoStreamingPlaylists->VideoFiles"."infoHash"': '"VideoStreamingPlaylists.VideoFiles.infoHash"',
174 '"VideoStreamingPlaylists->VideoFiles"."fps"': '"VideoStreamingPlaylists.VideoFiles.fps"',
175 '"VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId"': '"VideoStreamingPlaylists.VideoFiles.videoStreamingPlaylistId"',
176 '"VideoStreamingPlaylists->VideoFiles"."videoId"': '"VideoStreamingPlaylists.VideoFiles.videoId"'
177 }
178
179 this.joins = this.joins.concat([
180 'LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"',
181
182 'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"',
183
184 'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
185 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
186 ])
187 }
188
189 private includeUserHistory (user: MUserId) {
190 this.attributes = {
191 ...this.attributes,
192
193 '"userVideoHistory"."id"': '"userVideoHistory.id"',
194 '"userVideoHistory"."currentTime"': '"userVideoHistory.currentTime"'
195 }
196
197 this.joins.push(
198 'LEFT OUTER JOIN "userVideoHistory" ' +
199 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId'
200 )
201
202 this.replacements.userVideoHistoryId = user.id
203 }
204
205 private includePlaylist (playlistId: number) {
206 this.attributes = {
207 ...this.attributes,
208
209 '"VideoPlaylistElement"."createdAt"': '"VideoPlaylistElement.createdAt"',
210 '"VideoPlaylistElement"."updatedAt"': '"VideoPlaylistElement.updatedAt"',
211 '"VideoPlaylistElement"."url"': '"VideoPlaylistElement.url"',
212 '"VideoPlaylistElement"."position"': '"VideoPlaylistElement.position"',
213 '"VideoPlaylistElement"."startTimestamp"': '"VideoPlaylistElement.startTimestamp"',
214 '"VideoPlaylistElement"."stopTimestamp"': '"VideoPlaylistElement.stopTimestamp"',
215 '"VideoPlaylistElement"."videoPlaylistId"': '"VideoPlaylistElement.videoPlaylistId"'
216 }
217
218 this.joins.push(
219 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' +
220 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
221 )
222
223 this.replacements.videoPlaylistId = playlistId
224 }
225
226 private buildSelect () {
227 return 'SELECT ' + Object.keys(this.attributes).map(key => {
228 const value = this.attributes[key]
229 if (value) return `${key} AS ${value}`
230
231 return key
232 }).join(', ')
233 }
234}
diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts
deleted file mode 100644
index 2aa5e65c8..000000000
--- a/server/models/video/video-query-builder.ts
+++ /dev/null
@@ -1,599 +0,0 @@
1import { Sequelize } from 'sequelize/types'
2import validator from 'validator'
3import { exists } from '@server/helpers/custom-validators/misc'
4import { buildDirectionAndField, createSafeIn } from '@server/models/utils'
5import { MUserAccountId, MUserId } from '@server/types/models'
6import { VideoFilter, VideoPrivacy, VideoState } from '@shared/models'
7
8export type BuildVideosQueryOptions = {
9 attributes?: string[]
10
11 serverAccountId: number
12 followerActorId: number
13 includeLocalVideos: boolean
14
15 count: number
16 start: number
17 sort: string
18
19 nsfw?: boolean
20 filter?: VideoFilter
21 isLive?: boolean
22
23 categoryOneOf?: number[]
24 licenceOneOf?: number[]
25 languageOneOf?: string[]
26 tagsOneOf?: string[]
27 tagsAllOf?: string[]
28
29 withFiles?: boolean
30
31 accountId?: number
32 videoChannelId?: number
33
34 videoPlaylistId?: number
35
36 trendingAlgorithm?: string // best, hot, or any other algorithm implemented
37 trendingDays?: number
38
39 user?: MUserAccountId
40 historyOfUser?: MUserId
41
42 startDate?: string // ISO 8601
43 endDate?: string // ISO 8601
44 originallyPublishedStartDate?: string
45 originallyPublishedEndDate?: string
46
47 durationMin?: number // seconds
48 durationMax?: number // seconds
49
50 search?: string
51
52 isCount?: boolean
53
54 group?: string
55 having?: string
56}
57
58function buildListQuery (sequelize: Sequelize, options: BuildVideosQueryOptions) {
59 const and: string[] = []
60 const joins: string[] = []
61 const replacements: any = {}
62 const cte: string[] = []
63
64 let attributes: string[] = options.attributes || [ '"video"."id"' ]
65 let group = options.group || ''
66 const having = options.having || ''
67
68 joins.push(
69 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"' +
70 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"' +
71 'INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id"'
72 )
73
74 and.push('"video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")')
75
76 if (options.serverAccountId) {
77 const blockerIds = [ options.serverAccountId ]
78 if (options.user) blockerIds.push(options.user.Account.id)
79
80 const inClause = createSafeIn(sequelize, blockerIds)
81
82 and.push(
83 'NOT EXISTS (' +
84 ' SELECT 1 FROM "accountBlocklist" ' +
85 ' WHERE "accountBlocklist"."accountId" IN (' + inClause + ') ' +
86 ' AND "accountBlocklist"."targetAccountId" = "account"."id" ' +
87 ')' +
88 'AND NOT EXISTS (' +
89 ' SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN (' + inClause + ') ' +
90 ' AND "serverBlocklist"."targetServerId" = "accountActor"."serverId"' +
91 ')'
92 )
93 }
94
95 // Only list public/published videos
96 if (!options.filter || (options.filter !== 'all-local' && options.filter !== 'all')) {
97 and.push(
98 `("video"."state" = ${VideoState.PUBLISHED} OR ` +
99 `("video"."state" = ${VideoState.TO_TRANSCODE} AND "video"."waitTranscoding" IS false))`
100 )
101
102 if (options.user) {
103 and.push(
104 `("video"."privacy" = ${VideoPrivacy.PUBLIC} OR "video"."privacy" = ${VideoPrivacy.INTERNAL})`
105 )
106 } else { // Or only public videos
107 and.push(
108 `"video"."privacy" = ${VideoPrivacy.PUBLIC}`
109 )
110 }
111 }
112
113 if (options.videoPlaylistId) {
114 joins.push(
115 'INNER JOIN "videoPlaylistElement" "video"."id" = "videoPlaylistElement"."videoId" ' +
116 'AND "videoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
117 )
118
119 replacements.videoPlaylistId = options.videoPlaylistId
120 }
121
122 if (options.filter && (options.filter === 'local' || options.filter === 'all-local')) {
123 and.push('"video"."remote" IS FALSE')
124 }
125
126 if (options.accountId) {
127 and.push('"account"."id" = :accountId')
128 replacements.accountId = options.accountId
129 }
130
131 if (options.videoChannelId) {
132 and.push('"videoChannel"."id" = :videoChannelId')
133 replacements.videoChannelId = options.videoChannelId
134 }
135
136 if (options.followerActorId) {
137 let query =
138 '(' +
139 ' EXISTS (' +
140 ' SELECT 1 FROM "videoShare" ' +
141 ' INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId" ' +
142 ' AND "actorFollowShare"."actorId" = :followerActorId AND "actorFollowShare"."state" = \'accepted\' ' +
143 ' WHERE "videoShare"."videoId" = "video"."id"' +
144 ' )' +
145 ' OR' +
146 ' EXISTS (' +
147 ' SELECT 1 from "actorFollow" ' +
148 ' WHERE "actorFollow"."targetActorId" = "videoChannel"."actorId" AND "actorFollow"."actorId" = :followerActorId ' +
149 ' AND "actorFollow"."state" = \'accepted\'' +
150 ' )'
151
152 if (options.includeLocalVideos) {
153 query += ' OR "video"."remote" IS FALSE'
154 }
155
156 query += ')'
157
158 and.push(query)
159 replacements.followerActorId = options.followerActorId
160 }
161
162 if (options.withFiles === true) {
163 and.push(
164 '(' +
165 ' EXISTS (SELECT 1 FROM "videoFile" WHERE "videoFile"."videoId" = "video"."id") ' +
166 ' OR EXISTS (' +
167 ' SELECT 1 FROM "videoStreamingPlaylist" ' +
168 ' INNER JOIN "videoFile" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist"."id" ' +
169 ' WHERE "videoStreamingPlaylist"."videoId" = "video"."id"' +
170 ' )' +
171 ')'
172 )
173 }
174
175 if (options.tagsOneOf) {
176 const tagsOneOfLower = options.tagsOneOf.map(t => t.toLowerCase())
177
178 and.push(
179 'EXISTS (' +
180 ' SELECT 1 FROM "videoTag" ' +
181 ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
182 ' WHERE lower("tag"."name") IN (' + createSafeIn(sequelize, tagsOneOfLower) + ') ' +
183 ' AND "video"."id" = "videoTag"."videoId"' +
184 ')'
185 )
186 }
187
188 if (options.tagsAllOf) {
189 const tagsAllOfLower = options.tagsAllOf.map(t => t.toLowerCase())
190
191 and.push(
192 'EXISTS (' +
193 ' SELECT 1 FROM "videoTag" ' +
194 ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
195 ' WHERE lower("tag"."name") IN (' + createSafeIn(sequelize, tagsAllOfLower) + ') ' +
196 ' AND "video"."id" = "videoTag"."videoId" ' +
197 ' GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length +
198 ')'
199 )
200 }
201
202 if (options.nsfw === true) {
203 and.push('"video"."nsfw" IS TRUE')
204 } else if (options.nsfw === false) {
205 and.push('"video"."nsfw" IS FALSE')
206 }
207
208 if (options.isLive === true) {
209 and.push('"video"."isLive" IS TRUE')
210 } else if (options.isLive === false) {
211 and.push('"video"."isLive" IS FALSE')
212 }
213
214 if (options.categoryOneOf) {
215 and.push('"video"."category" IN (:categoryOneOf)')
216 replacements.categoryOneOf = options.categoryOneOf
217 }
218
219 if (options.licenceOneOf) {
220 and.push('"video"."licence" IN (:licenceOneOf)')
221 replacements.licenceOneOf = options.licenceOneOf
222 }
223
224 if (options.languageOneOf) {
225 const languages = options.languageOneOf.filter(l => l && l !== '_unknown')
226 const languagesQueryParts: string[] = []
227
228 if (languages.length !== 0) {
229 languagesQueryParts.push('"video"."language" IN (:languageOneOf)')
230 replacements.languageOneOf = languages
231
232 languagesQueryParts.push(
233 'EXISTS (' +
234 ' SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' +
235 ' IN (' + createSafeIn(sequelize, languages) + ') AND ' +
236 ' "videoCaption"."videoId" = "video"."id"' +
237 ')'
238 )
239 }
240
241 if (options.languageOneOf.includes('_unknown')) {
242 languagesQueryParts.push('"video"."language" IS NULL')
243 }
244
245 if (languagesQueryParts.length !== 0) {
246 and.push('(' + languagesQueryParts.join(' OR ') + ')')
247 }
248 }
249
250 // We don't exclude results in this so if we do a count we don't need to add this complex clause
251 if (options.isCount !== true) {
252 if (options.trendingDays) {
253 const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays)
254
255 joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate')
256 replacements.viewsGteDate = viewsGteDate
257
258 attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"')
259
260 group = 'GROUP BY "video"."id"'
261 } else if ([ 'best', 'hot' ].includes(options.trendingAlgorithm)) {
262 /**
263 * "Hotness" is a measure based on absolute view/comment/like/dislike numbers,
264 * with fixed weights only applied to their log values.
265 *
266 * This algorithm gives little chance for an old video to have a good score,
267 * for which recent spikes in interactions could be a sign of "hotness" and
268 * justify a better score. However there are multiple ways to achieve that
269 * goal, which is left for later. Yes, this is a TODO :)
270 *
271 * notes:
272 * - weights and base score are in number of half-days.
273 * - all comments are counted, regardless of being written by the video author or not
274 * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58
275 * - we have less interactions than on reddit, so multiply weights by an arbitrary factor
276 */
277 const weights = {
278 like: 3 * 50,
279 dislike: -3 * 50,
280 view: Math.floor((1 / 3) * 50),
281 comment: 2 * 50, // a comment takes more time than a like to do, but can be done multiple times
282 history: -2 * 50
283 }
284
285 joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"')
286
287 let attribute =
288 `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+)
289 `+ LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-)
290 `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+)
291 `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+)
292 '+ (SELECT (EXTRACT(epoch FROM "video"."publishedAt") - 1446156582) / 47000) ' // base score (in number of half-days)
293
294 if (options.trendingAlgorithm === 'best' && options.user) {
295 joins.push(
296 'LEFT JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :bestUser'
297 )
298 replacements.bestUser = options.user.id
299
300 attribute += `+ POWER(COUNT(DISTINCT "userVideoHistory"."id"), 2.0) * ${weights.history} `
301 }
302
303 attribute += 'AS "score"'
304 attributes.push(attribute)
305
306 group = 'GROUP BY "video"."id"'
307 }
308 }
309
310 if (options.historyOfUser) {
311 joins.push('INNER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId"')
312
313 and.push('"userVideoHistory"."userId" = :historyOfUser')
314 replacements.historyOfUser = options.historyOfUser.id
315 }
316
317 if (options.startDate) {
318 and.push('"video"."publishedAt" >= :startDate')
319 replacements.startDate = options.startDate
320 }
321
322 if (options.endDate) {
323 and.push('"video"."publishedAt" <= :endDate')
324 replacements.endDate = options.endDate
325 }
326
327 if (options.originallyPublishedStartDate) {
328 and.push('"video"."originallyPublishedAt" >= :originallyPublishedStartDate')
329 replacements.originallyPublishedStartDate = options.originallyPublishedStartDate
330 }
331
332 if (options.originallyPublishedEndDate) {
333 and.push('"video"."originallyPublishedAt" <= :originallyPublishedEndDate')
334 replacements.originallyPublishedEndDate = options.originallyPublishedEndDate
335 }
336
337 if (options.durationMin) {
338 and.push('"video"."duration" >= :durationMin')
339 replacements.durationMin = options.durationMin
340 }
341
342 if (options.durationMax) {
343 and.push('"video"."duration" <= :durationMax')
344 replacements.durationMax = options.durationMax
345 }
346
347 if (options.search) {
348 const escapedSearch = sequelize.escape(options.search)
349 const escapedLikeSearch = sequelize.escape('%' + options.search + '%')
350
351 cte.push(
352 '"trigramSearch" AS (' +
353 ' SELECT "video"."id", ' +
354 ` similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent(${escapedSearch}))) as similarity ` +
355 ' FROM "video" ' +
356 ' WHERE lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
357 ' lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
358 ')'
359 )
360
361 joins.push('LEFT JOIN "trigramSearch" ON "video"."id" = "trigramSearch"."id"')
362
363 let base = '(' +
364 ' "trigramSearch"."id" IS NOT NULL OR ' +
365 ' EXISTS (' +
366 ' SELECT 1 FROM "videoTag" ' +
367 ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
368 ` WHERE lower("tag"."name") = ${escapedSearch} ` +
369 ' AND "video"."id" = "videoTag"."videoId"' +
370 ' )'
371
372 if (validator.isUUID(options.search)) {
373 base += ` OR "video"."uuid" = ${escapedSearch}`
374 }
375
376 base += ')'
377 and.push(base)
378
379 attributes.push(`COALESCE("trigramSearch"."similarity", 0) as similarity`)
380 } else {
381 attributes.push('0 as similarity')
382 }
383
384 if (options.isCount === true) attributes = [ 'COUNT(*) as "total"' ]
385
386 let suffix = ''
387 let order = ''
388 if (options.isCount !== true) {
389
390 if (exists(options.sort)) {
391 if (options.sort === '-originallyPublishedAt' || options.sort === 'originallyPublishedAt') {
392 attributes.push('COALESCE("video"."originallyPublishedAt", "video"."publishedAt") AS "publishedAtForOrder"')
393 }
394
395 order = buildOrder(options.sort)
396 suffix += `${order} `
397 }
398
399 if (exists(options.count)) {
400 const count = parseInt(options.count + '', 10)
401 suffix += `LIMIT ${count} `
402 }
403
404 if (exists(options.start)) {
405 const start = parseInt(options.start + '', 10)
406 suffix += `OFFSET ${start} `
407 }
408 }
409
410 const cteString = cte.length !== 0
411 ? `WITH ${cte.join(', ')} `
412 : ''
413
414 const query = cteString +
415 'SELECT ' + attributes.join(', ') + ' ' +
416 'FROM "video" ' + joins.join(' ') + ' ' +
417 'WHERE ' + and.join(' AND ') + ' ' +
418 group + ' ' +
419 having + ' ' +
420 suffix
421
422 return { query, replacements, order }
423}
424
425function buildOrder (value: string) {
426 const { direction, field } = buildDirectionAndField(value)
427 if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field)
428
429 if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()'
430
431 if ([ 'trending', 'hot', 'best' ].includes(field.toLowerCase())) { // Sort by aggregation
432 return `ORDER BY "score" ${direction}, "video"."views" ${direction}`
433 }
434
435 let firstSort: string
436
437 if (field.toLowerCase() === 'match') { // Search
438 firstSort = '"similarity"'
439 } else if (field === 'originallyPublishedAt') {
440 firstSort = '"publishedAtForOrder"'
441 } else if (field.includes('.')) {
442 firstSort = field
443 } else {
444 firstSort = `"video"."${field}"`
445 }
446
447 return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC`
448}
449
450function wrapForAPIResults (baseQuery: string, replacements: any, options: BuildVideosQueryOptions, order: string) {
451 const attributes = {
452 '"video".*': '',
453 '"VideoChannel"."id"': '"VideoChannel.id"',
454 '"VideoChannel"."name"': '"VideoChannel.name"',
455 '"VideoChannel"."description"': '"VideoChannel.description"',
456 '"VideoChannel"."actorId"': '"VideoChannel.actorId"',
457 '"VideoChannel->Actor"."id"': '"VideoChannel.Actor.id"',
458 '"VideoChannel->Actor"."preferredUsername"': '"VideoChannel.Actor.preferredUsername"',
459 '"VideoChannel->Actor"."url"': '"VideoChannel.Actor.url"',
460 '"VideoChannel->Actor"."serverId"': '"VideoChannel.Actor.serverId"',
461 '"VideoChannel->Actor"."avatarId"': '"VideoChannel.Actor.avatarId"',
462 '"VideoChannel->Account"."id"': '"VideoChannel.Account.id"',
463 '"VideoChannel->Account"."name"': '"VideoChannel.Account.name"',
464 '"VideoChannel->Account->Actor"."id"': '"VideoChannel.Account.Actor.id"',
465 '"VideoChannel->Account->Actor"."preferredUsername"': '"VideoChannel.Account.Actor.preferredUsername"',
466 '"VideoChannel->Account->Actor"."url"': '"VideoChannel.Account.Actor.url"',
467 '"VideoChannel->Account->Actor"."serverId"': '"VideoChannel.Account.Actor.serverId"',
468 '"VideoChannel->Account->Actor"."avatarId"': '"VideoChannel.Account.Actor.avatarId"',
469 '"VideoChannel->Actor->Server"."id"': '"VideoChannel.Actor.Server.id"',
470 '"VideoChannel->Actor->Server"."host"': '"VideoChannel.Actor.Server.host"',
471 '"VideoChannel->Actor->Avatar"."id"': '"VideoChannel.Actor.Avatar.id"',
472 '"VideoChannel->Actor->Avatar"."filename"': '"VideoChannel.Actor.Avatar.filename"',
473 '"VideoChannel->Actor->Avatar"."fileUrl"': '"VideoChannel.Actor.Avatar.fileUrl"',
474 '"VideoChannel->Actor->Avatar"."onDisk"': '"VideoChannel.Actor.Avatar.onDisk"',
475 '"VideoChannel->Actor->Avatar"."createdAt"': '"VideoChannel.Actor.Avatar.createdAt"',
476 '"VideoChannel->Actor->Avatar"."updatedAt"': '"VideoChannel.Actor.Avatar.updatedAt"',
477 '"VideoChannel->Account->Actor->Server"."id"': '"VideoChannel.Account.Actor.Server.id"',
478 '"VideoChannel->Account->Actor->Server"."host"': '"VideoChannel.Account.Actor.Server.host"',
479 '"VideoChannel->Account->Actor->Avatar"."id"': '"VideoChannel.Account.Actor.Avatar.id"',
480 '"VideoChannel->Account->Actor->Avatar"."filename"': '"VideoChannel.Account.Actor.Avatar.filename"',
481 '"VideoChannel->Account->Actor->Avatar"."fileUrl"': '"VideoChannel.Account.Actor.Avatar.fileUrl"',
482 '"VideoChannel->Account->Actor->Avatar"."onDisk"': '"VideoChannel.Account.Actor.Avatar.onDisk"',
483 '"VideoChannel->Account->Actor->Avatar"."createdAt"': '"VideoChannel.Account.Actor.Avatar.createdAt"',
484 '"VideoChannel->Account->Actor->Avatar"."updatedAt"': '"VideoChannel.Account.Actor.Avatar.updatedAt"',
485 '"Thumbnails"."id"': '"Thumbnails.id"',
486 '"Thumbnails"."type"': '"Thumbnails.type"',
487 '"Thumbnails"."filename"': '"Thumbnails.filename"'
488 }
489
490 const joins = [
491 'INNER JOIN "video" ON "tmp"."id" = "video"."id"',
492
493 'INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"',
494 'INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"',
495 'INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"',
496 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"',
497
498 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"',
499 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' +
500 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"',
501
502 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' +
503 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"',
504
505 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' +
506 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"',
507
508 'LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"'
509 ]
510
511 if (options.withFiles) {
512 joins.push('LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"')
513
514 joins.push('LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"')
515 joins.push(
516 'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
517 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
518 )
519
520 Object.assign(attributes, {
521 '"VideoFiles"."id"': '"VideoFiles.id"',
522 '"VideoFiles"."createdAt"': '"VideoFiles.createdAt"',
523 '"VideoFiles"."updatedAt"': '"VideoFiles.updatedAt"',
524 '"VideoFiles"."resolution"': '"VideoFiles.resolution"',
525 '"VideoFiles"."size"': '"VideoFiles.size"',
526 '"VideoFiles"."extname"': '"VideoFiles.extname"',
527 '"VideoFiles"."filename"': '"VideoFiles.filename"',
528 '"VideoFiles"."fileUrl"': '"VideoFiles.fileUrl"',
529 '"VideoFiles"."torrentFilename"': '"VideoFiles.torrentFilename"',
530 '"VideoFiles"."torrentUrl"': '"VideoFiles.torrentUrl"',
531 '"VideoFiles"."infoHash"': '"VideoFiles.infoHash"',
532 '"VideoFiles"."fps"': '"VideoFiles.fps"',
533 '"VideoFiles"."videoId"': '"VideoFiles.videoId"',
534
535 '"VideoStreamingPlaylists"."id"': '"VideoStreamingPlaylists.id"',
536 '"VideoStreamingPlaylists"."playlistUrl"': '"VideoStreamingPlaylists.playlistUrl"',
537 '"VideoStreamingPlaylists"."type"': '"VideoStreamingPlaylists.type"',
538 '"VideoStreamingPlaylists->VideoFiles"."id"': '"VideoStreamingPlaylists.VideoFiles.id"',
539 '"VideoStreamingPlaylists->VideoFiles"."createdAt"': '"VideoStreamingPlaylists.VideoFiles.createdAt"',
540 '"VideoStreamingPlaylists->VideoFiles"."updatedAt"': '"VideoStreamingPlaylists.VideoFiles.updatedAt"',
541 '"VideoStreamingPlaylists->VideoFiles"."resolution"': '"VideoStreamingPlaylists.VideoFiles.resolution"',
542 '"VideoStreamingPlaylists->VideoFiles"."size"': '"VideoStreamingPlaylists.VideoFiles.size"',
543 '"VideoStreamingPlaylists->VideoFiles"."extname"': '"VideoStreamingPlaylists.VideoFiles.extname"',
544 '"VideoStreamingPlaylists->VideoFiles"."filename"': '"VideoStreamingPlaylists.VideoFiles.filename"',
545 '"VideoStreamingPlaylists->VideoFiles"."fileUrl"': '"VideoStreamingPlaylists.VideoFiles.fileUrl"',
546 '"VideoStreamingPlaylists->VideoFiles"."torrentFilename"': '"VideoStreamingPlaylists.VideoFiles.torrentFilename"',
547 '"VideoStreamingPlaylists->VideoFiles"."torrentUrl"': '"VideoStreamingPlaylists.VideoFiles.torrentUrl"',
548 '"VideoStreamingPlaylists->VideoFiles"."infoHash"': '"VideoStreamingPlaylists.VideoFiles.infoHash"',
549 '"VideoStreamingPlaylists->VideoFiles"."fps"': '"VideoStreamingPlaylists.VideoFiles.fps"',
550 '"VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId"': '"VideoStreamingPlaylists.VideoFiles.videoStreamingPlaylistId"',
551 '"VideoStreamingPlaylists->VideoFiles"."videoId"': '"VideoStreamingPlaylists.VideoFiles.videoId"'
552 })
553 }
554
555 if (options.user) {
556 joins.push(
557 'LEFT OUTER JOIN "userVideoHistory" ' +
558 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId'
559 )
560 replacements.userVideoHistoryId = options.user.id
561
562 Object.assign(attributes, {
563 '"userVideoHistory"."id"': '"userVideoHistory.id"',
564 '"userVideoHistory"."currentTime"': '"userVideoHistory.currentTime"'
565 })
566 }
567
568 if (options.videoPlaylistId) {
569 joins.push(
570 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' +
571 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
572 )
573 replacements.videoPlaylistId = options.videoPlaylistId
574
575 Object.assign(attributes, {
576 '"VideoPlaylistElement"."createdAt"': '"VideoPlaylistElement.createdAt"',
577 '"VideoPlaylistElement"."updatedAt"': '"VideoPlaylistElement.updatedAt"',
578 '"VideoPlaylistElement"."url"': '"VideoPlaylistElement.url"',
579 '"VideoPlaylistElement"."position"': '"VideoPlaylistElement.position"',
580 '"VideoPlaylistElement"."startTimestamp"': '"VideoPlaylistElement.startTimestamp"',
581 '"VideoPlaylistElement"."stopTimestamp"': '"VideoPlaylistElement.stopTimestamp"',
582 '"VideoPlaylistElement"."videoPlaylistId"': '"VideoPlaylistElement.videoPlaylistId"'
583 })
584 }
585
586 const select = 'SELECT ' + Object.keys(attributes).map(key => {
587 const value = attributes[key]
588 if (value) return `${key} AS ${value}`
589
590 return key
591 }).join(', ')
592
593 return `${select} FROM (${baseQuery}) AS "tmp" ${joins.join(' ')} ${order}`
594}
595
596export {
597 buildListQuery,
598 wrapForAPIResults
599}
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 44aaa24ef..4979cee50 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -1,6 +1,6 @@
1import * as Bluebird from 'bluebird' 1import * as Bluebird from 'bluebird'
2import { remove } from 'fs-extra' 2import { remove } from 'fs-extra'
3import { maxBy, minBy, pick } from 'lodash' 3import { maxBy, minBy } from 'lodash'
4import { join } from 'path' 4import { join } from 'path'
5import { FindOptions, Includeable, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' 5import { FindOptions, Includeable, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
6import { 6import {
@@ -110,7 +110,16 @@ import { VideoTrackerModel } from '../server/video-tracker'
110import { UserModel } from '../user/user' 110import { UserModel } from '../user/user'
111import { UserVideoHistoryModel } from '../user/user-video-history' 111import { UserVideoHistoryModel } from '../user/user-video-history'
112import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils' 112import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils'
113import {
114 videoFilesModelToFormattedJSON,
115 VideoFormattingJSONOptions,
116 videoModelToActivityPubObject,
117 videoModelToFormattedDetailsJSON,
118 videoModelToFormattedJSON
119} from './formatter/video-format-utils'
113import { ScheduleVideoUpdateModel } from './schedule-video-update' 120import { ScheduleVideoUpdateModel } from './schedule-video-update'
121import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './sql/videos-id-list-query-builder'
122import { VideosModelListQueryBuilder } from './sql/videos-model-list-query-builder'
114import { TagModel } from './tag' 123import { TagModel } from './tag'
115import { ThumbnailModel } from './thumbnail' 124import { ThumbnailModel } from './thumbnail'
116import { VideoBlacklistModel } from './video-blacklist' 125import { VideoBlacklistModel } from './video-blacklist'
@@ -118,17 +127,9 @@ import { VideoCaptionModel } from './video-caption'
118import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel' 127import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
119import { VideoCommentModel } from './video-comment' 128import { VideoCommentModel } from './video-comment'
120import { VideoFileModel } from './video-file' 129import { VideoFileModel } from './video-file'
121import {
122 videoFilesModelToFormattedJSON,
123 VideoFormattingJSONOptions,
124 videoModelToActivityPubObject,
125 videoModelToFormattedDetailsJSON,
126 videoModelToFormattedJSON
127} from './video-format-utils'
128import { VideoImportModel } from './video-import' 130import { VideoImportModel } from './video-import'
129import { VideoLiveModel } from './video-live' 131import { VideoLiveModel } from './video-live'
130import { VideoPlaylistElementModel } from './video-playlist-element' 132import { VideoPlaylistElementModel } from './video-playlist-element'
131import { buildListQuery, BuildVideosQueryOptions, wrapForAPIResults } from './video-query-builder'
132import { VideoShareModel } from './video-share' 133import { VideoShareModel } from './video-share'
133import { VideoStreamingPlaylistModel } from './video-streaming-playlist' 134import { VideoStreamingPlaylistModel } from './video-streaming-playlist'
134import { VideoTagModel } from './video-tag' 135import { VideoTagModel } from './video-tag'
@@ -1607,7 +1608,7 @@ export class VideoModel extends Model<Partial<AttributesOnly<VideoModel>>> {
1607 const serverActor = await getServerActor() 1608 const serverActor = await getServerActor()
1608 const followerActorId = serverActor.id 1609 const followerActorId = serverActor.id
1609 1610
1610 const queryOptions: BuildVideosQueryOptions = { 1611 const queryOptions: BuildVideosListQueryOptions = {
1611 attributes: [ `"${field}"` ], 1612 attributes: [ `"${field}"` ],
1612 group: `GROUP BY "${field}"`, 1613 group: `GROUP BY "${field}"`,
1613 having: `HAVING COUNT("${field}") >= ${threshold}`, 1614 having: `HAVING COUNT("${field}") >= ${threshold}`,
@@ -1619,10 +1620,10 @@ export class VideoModel extends Model<Partial<AttributesOnly<VideoModel>>> {
1619 includeLocalVideos: true 1620 includeLocalVideos: true
1620 } 1621 }
1621 1622
1622 const { query, replacements } = buildListQuery(VideoModel.sequelize, queryOptions) 1623 const queryBuilder = new VideosIdListQueryBuilder(VideoModel.sequelize)
1623 1624
1624 return this.sequelize.query<any>(query, { replacements, type: QueryTypes.SELECT }) 1625 return queryBuilder.queryVideoIds(queryOptions)
1625 .then(rows => rows.map(r => r[field])) 1626 .then(rows => rows.map(r => r[field]))
1626 } 1627 }
1627 1628
1628 static buildTrendingQuery (trendingDays: number) { 1629 static buildTrendingQuery (trendingDays: number) {
@@ -1640,27 +1641,24 @@ export class VideoModel extends Model<Partial<AttributesOnly<VideoModel>>> {
1640 } 1641 }
1641 1642
1642 private static async getAvailableForApi ( 1643 private static async getAvailableForApi (
1643 options: BuildVideosQueryOptions, 1644 options: BuildVideosListQueryOptions,
1644 countVideos = true 1645 countVideos = true
1645 ): Promise<ResultList<VideoModel>> { 1646 ): Promise<ResultList<VideoModel>> {
1646 function getCount () { 1647 function getCount () {
1647 if (countVideos !== true) return Promise.resolve(undefined) 1648 if (countVideos !== true) return Promise.resolve(undefined)
1648 1649
1649 const countOptions = Object.assign({}, options, { isCount: true }) 1650 const countOptions = Object.assign({}, options, { isCount: true })
1650 const { query: queryCount, replacements: replacementsCount } = buildListQuery(VideoModel.sequelize, countOptions) 1651 const queryBuilder = new VideosIdListQueryBuilder(VideoModel.sequelize)
1651 1652
1652 return VideoModel.sequelize.query<any>(queryCount, { replacements: replacementsCount, type: QueryTypes.SELECT }) 1653 return queryBuilder.countVideoIds(countOptions)
1653 .then(rows => rows.length !== 0 ? rows[0].total : 0)
1654 } 1654 }
1655 1655
1656 function getModels () { 1656 function getModels () {
1657 if (options.count === 0) return Promise.resolve([]) 1657 if (options.count === 0) return Promise.resolve([])
1658 1658
1659 const { query, replacements, order } = buildListQuery(VideoModel.sequelize, options) 1659 const queryBuilder = new VideosModelListQueryBuilder(VideoModel.sequelize)
1660 const queryModels = wrapForAPIResults(query, replacements, options, order)
1661 1660
1662 return VideoModel.sequelize.query<any>(queryModels, { replacements, type: QueryTypes.SELECT, nest: true }) 1661 return queryBuilder.queryVideos(options)
1663 .then(rows => VideoModel.buildAPIResult(rows))
1664 } 1662 }
1665 1663
1666 const [ count, rows ] = await Promise.all([ getCount(), getModels() ]) 1664 const [ count, rows ] = await Promise.all([ getCount(), getModels() ])
@@ -1671,153 +1669,6 @@ export class VideoModel extends Model<Partial<AttributesOnly<VideoModel>>> {
1671 } 1669 }
1672 } 1670 }
1673 1671
1674 private static buildAPIResult (rows: any[]) {
1675 const videosMemo: { [ id: number ]: VideoModel } = {}
1676 const videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel } = {}
1677
1678 const thumbnailsDone = new Set<number>()
1679 const historyDone = new Set<number>()
1680 const videoFilesDone = new Set<number>()
1681
1682 const videos: VideoModel[] = []
1683
1684 const avatarKeys = [ 'id', 'filename', 'fileUrl', 'onDisk', 'createdAt', 'updatedAt' ]
1685 const actorKeys = [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ]
1686 const serverKeys = [ 'id', 'host' ]
1687 const videoFileKeys = [
1688 'id',
1689 'createdAt',
1690 'updatedAt',
1691 'resolution',
1692 'size',
1693 'extname',
1694 'filename',
1695 'fileUrl',
1696 'torrentFilename',
1697 'torrentUrl',
1698 'infoHash',
1699 'fps',
1700 'videoId',
1701 'videoStreamingPlaylistId'
1702 ]
1703 const videoStreamingPlaylistKeys = [ 'id', 'type', 'playlistUrl' ]
1704 const videoKeys = [
1705 'id',
1706 'uuid',
1707 'name',
1708 'category',
1709 'licence',
1710 'language',
1711 'privacy',
1712 'nsfw',
1713 'description',
1714 'support',
1715 'duration',
1716 'views',
1717 'likes',
1718 'dislikes',
1719 'remote',
1720 'isLive',
1721 'url',
1722 'commentsEnabled',
1723 'downloadEnabled',
1724 'waitTranscoding',
1725 'state',
1726 'publishedAt',
1727 'originallyPublishedAt',
1728 'channelId',
1729 'createdAt',
1730 'updatedAt'
1731 ]
1732 const buildOpts = { raw: true }
1733
1734 function buildActor (rowActor: any) {
1735 const avatarModel = rowActor.Avatar.id !== null
1736 ? new ActorImageModel(pick(rowActor.Avatar, avatarKeys), buildOpts)
1737 : null
1738
1739 const serverModel = rowActor.Server.id !== null
1740 ? new ServerModel(pick(rowActor.Server, serverKeys), buildOpts)
1741 : null
1742
1743 const actorModel = new ActorModel(pick(rowActor, actorKeys), buildOpts)
1744 actorModel.Avatar = avatarModel
1745 actorModel.Server = serverModel
1746
1747 return actorModel
1748 }
1749
1750 for (const row of rows) {
1751 if (!videosMemo[row.id]) {
1752 // Build Channel
1753 const channel = row.VideoChannel
1754 const channelModel = new VideoChannelModel(pick(channel, [ 'id', 'name', 'description', 'actorId' ]), buildOpts)
1755 channelModel.Actor = buildActor(channel.Actor)
1756
1757 const account = row.VideoChannel.Account
1758 const accountModel = new AccountModel(pick(account, [ 'id', 'name' ]), buildOpts)
1759 accountModel.Actor = buildActor(account.Actor)
1760
1761 channelModel.Account = accountModel
1762
1763 const videoModel = new VideoModel(pick(row, videoKeys), buildOpts)
1764 videoModel.VideoChannel = channelModel
1765
1766 videoModel.UserVideoHistories = []
1767 videoModel.Thumbnails = []
1768 videoModel.VideoFiles = []
1769 videoModel.VideoStreamingPlaylists = []
1770
1771 videosMemo[row.id] = videoModel
1772 // Don't take object value to have a sorted array
1773 videos.push(videoModel)
1774 }
1775
1776 const videoModel = videosMemo[row.id]
1777
1778 if (row.userVideoHistory?.id && !historyDone.has(row.userVideoHistory.id)) {
1779 const historyModel = new UserVideoHistoryModel(pick(row.userVideoHistory, [ 'id', 'currentTime' ]), buildOpts)
1780 videoModel.UserVideoHistories.push(historyModel)
1781
1782 historyDone.add(row.userVideoHistory.id)
1783 }
1784
1785 if (row.Thumbnails?.id && !thumbnailsDone.has(row.Thumbnails.id)) {
1786 const thumbnailModel = new ThumbnailModel(pick(row.Thumbnails, [ 'id', 'type', 'filename' ]), buildOpts)
1787 videoModel.Thumbnails.push(thumbnailModel)
1788
1789 thumbnailsDone.add(row.Thumbnails.id)
1790 }
1791
1792 if (row.VideoFiles?.id && !videoFilesDone.has(row.VideoFiles.id)) {
1793 const videoFileModel = new VideoFileModel(pick(row.VideoFiles, videoFileKeys), buildOpts)
1794 videoModel.VideoFiles.push(videoFileModel)
1795
1796 videoFilesDone.add(row.VideoFiles.id)
1797 }
1798
1799 if (row.VideoStreamingPlaylists?.id && !videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]) {
1800 const streamingPlaylist = new VideoStreamingPlaylistModel(pick(row.VideoStreamingPlaylists, videoStreamingPlaylistKeys), buildOpts)
1801 streamingPlaylist.VideoFiles = []
1802
1803 videoModel.VideoStreamingPlaylists.push(streamingPlaylist)
1804
1805 videoStreamingPlaylistMemo[streamingPlaylist.id] = streamingPlaylist
1806 }
1807
1808 if (row.VideoStreamingPlaylists?.VideoFiles?.id && !videoFilesDone.has(row.VideoStreamingPlaylists.VideoFiles.id)) {
1809 const streamingPlaylist = videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]
1810
1811 const videoFileModel = new VideoFileModel(pick(row.VideoStreamingPlaylists.VideoFiles, videoFileKeys), buildOpts)
1812 streamingPlaylist.VideoFiles.push(videoFileModel)
1813
1814 videoFilesDone.add(row.VideoStreamingPlaylists.VideoFiles.id)
1815 }
1816 }
1817
1818 return videos
1819 }
1820
1821 static getCategoryLabel (id: number) { 1672 static getCategoryLabel (id: number) {
1822 return VIDEO_CATEGORIES[id] || 'Misc' 1673 return VIDEO_CATEGORIES[id] || 'Misc'
1823 } 1674 }
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-06 16:10:51 +0000