aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models
diff options
context:
space:
mode:
Diffstat (limited to 'server/models')
-rw-r--r--server/models/account/user.ts8
-rw-r--r--server/models/utils.ts12
-rw-r--r--server/models/video/video.ts209
3 files changed, 140 insertions, 89 deletions
diff --git a/server/models/account/user.ts b/server/models/account/user.ts
index e75039521..aac691d66 100644
--- a/server/models/account/user.ts
+++ b/server/models/account/user.ts
@@ -31,6 +31,7 @@ import {
31 isUserPasswordValid, 31 isUserPasswordValid,
32 isUserRoleValid, 32 isUserRoleValid,
33 isUserUsernameValid, 33 isUserUsernameValid,
34 isUserVideoLanguages,
34 isUserVideoQuotaDailyValid, 35 isUserVideoQuotaDailyValid,
35 isUserVideoQuotaValid, 36 isUserVideoQuotaValid,
36 isUserVideosHistoryEnabledValid, 37 isUserVideosHistoryEnabledValid,
@@ -147,6 +148,12 @@ export class UserModel extends Model<UserModel> {
147 @Column 148 @Column
148 autoPlayVideo: boolean 149 autoPlayVideo: boolean
149 150
151 @AllowNull(true)
152 @Default(null)
153 @Is('UserVideoLanguages', value => throwIfNotValid(value, isUserVideoLanguages, 'video languages'))
154 @Column(DataType.ARRAY(DataType.STRING))
155 videoLanguages: string[]
156
150 @AllowNull(false) 157 @AllowNull(false)
151 @Default(UserAdminFlag.NONE) 158 @Default(UserAdminFlag.NONE)
152 @Is('UserAdminFlags', value => throwIfNotValid(value, isUserAdminFlagsValid, 'user admin flags')) 159 @Is('UserAdminFlags', value => throwIfNotValid(value, isUserAdminFlagsValid, 'user admin flags'))
@@ -551,6 +558,7 @@ export class UserModel extends Model<UserModel> {
551 webTorrentEnabled: this.webTorrentEnabled, 558 webTorrentEnabled: this.webTorrentEnabled,
552 videosHistoryEnabled: this.videosHistoryEnabled, 559 videosHistoryEnabled: this.videosHistoryEnabled,
553 autoPlayVideo: this.autoPlayVideo, 560 autoPlayVideo: this.autoPlayVideo,
561 videoLanguages: this.videoLanguages,
554 role: this.role, 562 role: this.role,
555 roleLabel: USER_ROLE_LABELS[ this.role ], 563 roleLabel: USER_ROLE_LABELS[ this.role ],
556 videoQuota: this.videoQuota, 564 videoQuota: this.videoQuota,
diff --git a/server/models/utils.ts b/server/models/utils.ts
index 2b172f608..206e108c3 100644
--- a/server/models/utils.ts
+++ b/server/models/utils.ts
@@ -1,7 +1,7 @@
1import { Sequelize } from 'sequelize-typescript' 1import { Model, Sequelize } from 'sequelize-typescript'
2import * as validator from 'validator' 2import * as validator from 'validator'
3import { OrderItem } from 'sequelize'
4import { Col } from 'sequelize/types/lib/utils' 3import { Col } from 'sequelize/types/lib/utils'
4import { OrderItem } from 'sequelize/types'
5 5
6type SortType = { sortModel: any, sortValue: string } 6type SortType = { sortModel: any, sortValue: string }
7 7
@@ -127,6 +127,11 @@ function parseAggregateResult (result: any) {
127 return total 127 return total
128} 128}
129 129
130const createSafeIn = (model: typeof Model, stringArr: string[]) => {
131 return stringArr.map(t => model.sequelize.escape(t))
132 .join(', ')
133}
134
130// --------------------------------------------------------------------------- 135// ---------------------------------------------------------------------------
131 136
132export { 137export {
@@ -141,7 +146,8 @@ export {
141 buildTrigramSearchIndex, 146 buildTrigramSearchIndex,
142 buildWhereIdOrUUID, 147 buildWhereIdOrUUID,
143 isOutdated, 148 isOutdated,
144 parseAggregateResult 149 parseAggregateResult,
150 createSafeIn
145} 151}
146 152
147// --------------------------------------------------------------------------- 153// ---------------------------------------------------------------------------
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index eccf0a4fa..92d07b5bc 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -83,6 +83,7 @@ import {
83 buildBlockedAccountSQL, 83 buildBlockedAccountSQL,
84 buildTrigramSearchIndex, 84 buildTrigramSearchIndex,
85 buildWhereIdOrUUID, 85 buildWhereIdOrUUID,
86 createSafeIn,
86 createSimilarityAttribute, 87 createSimilarityAttribute,
87 getVideoSort, 88 getVideoSort,
88 isOutdated, 89 isOutdated,
@@ -227,6 +228,8 @@ type AvailableForListIDsOptions = {
227 trendingDays?: number 228 trendingDays?: number
228 user?: UserModel, 229 user?: UserModel,
229 historyOfUser?: UserModel 230 historyOfUser?: UserModel
231
232 baseWhere?: WhereOptions[]
230} 233}
231 234
232@Scopes(() => ({ 235@Scopes(() => ({
@@ -270,34 +273,34 @@ type AvailableForListIDsOptions = {
270 return query 273 return query
271 }, 274 },
272 [ ScopeNames.AVAILABLE_FOR_LIST_IDS ]: (options: AvailableForListIDsOptions) => { 275 [ ScopeNames.AVAILABLE_FOR_LIST_IDS ]: (options: AvailableForListIDsOptions) => {
273 const attributes = options.withoutId === true ? [] : [ 'id' ] 276 const whereAnd = options.baseWhere ? options.baseWhere : []
274 277
275 const query: FindOptions = { 278 const query: FindOptions = {
276 raw: true, 279 raw: true,
277 attributes, 280 attributes: options.withoutId === true ? [] : [ 'id' ],
278 where: {
279 id: {
280 [ Op.and ]: [
281 {
282 [ Op.notIn ]: Sequelize.literal(
283 '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
284 )
285 }
286 ]
287 },
288 channelId: {
289 [ Op.notIn ]: Sequelize.literal(
290 '(' +
291 'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
292 buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
293 ')' +
294 ')'
295 )
296 }
297 },
298 include: [] 281 include: []
299 } 282 }
300 283
284 whereAnd.push({
285 id: {
286 [ Op.notIn ]: Sequelize.literal(
287 '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
288 )
289 }
290 })
291
292 whereAnd.push({
293 channelId: {
294 [ Op.notIn ]: Sequelize.literal(
295 '(' +
296 'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
297 buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
298 ')' +
299 ')'
300 )
301 }
302 })
303
301 // Only list public/published videos 304 // Only list public/published videos
302 if (!options.filter || options.filter !== 'all-local') { 305 if (!options.filter || options.filter !== 'all-local') {
303 const privacyWhere = { 306 const privacyWhere = {
@@ -317,7 +320,7 @@ type AvailableForListIDsOptions = {
317 ] 320 ]
318 } 321 }
319 322
320 Object.assign(query.where, privacyWhere) 323 whereAnd.push(privacyWhere)
321 } 324 }
322 325
323 if (options.videoPlaylistId) { 326 if (options.videoPlaylistId) {
@@ -387,86 +390,114 @@ type AvailableForListIDsOptions = {
387 390
388 // Force actorId to be a number to avoid SQL injections 391 // Force actorId to be a number to avoid SQL injections
389 const actorIdNumber = parseInt(options.followerActorId.toString(), 10) 392 const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
390 query.where[ 'id' ][ Op.and ].push({ 393 whereAnd.push({
391 [ Op.in ]: Sequelize.literal( 394 id: {
392 '(' + 395 [ Op.in ]: Sequelize.literal(
393 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' + 396 '(' +
394 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' + 397 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
395 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + 398 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
396 ' UNION ALL ' + 399 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
397 'SELECT "video"."id" AS "id" FROM "video" ' + 400 ' UNION ALL ' +
398 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + 401 'SELECT "video"."id" AS "id" FROM "video" ' +
399 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + 402 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
400 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + 403 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
401 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + 404 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
402 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + 405 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
403 localVideosReq + 406 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
404 ')' 407 localVideosReq +
405 ) 408 ')'
409 )
410 }
406 }) 411 })
407 } 412 }
408 413
409 if (options.withFiles === true) { 414 if (options.withFiles === true) {
410 query.where[ 'id' ][ Op.and ].push({ 415 whereAnd.push({
411 [ Op.in ]: Sequelize.literal( 416 id: {
412 '(SELECT "videoId" FROM "videoFile")' 417 [ Op.in ]: Sequelize.literal(
413 ) 418 '(SELECT "videoId" FROM "videoFile")'
419 )
420 }
414 }) 421 })
415 } 422 }
416 423
417 // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN() 424 // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN()
418 if (options.tagsAllOf || options.tagsOneOf) { 425 if (options.tagsAllOf || options.tagsOneOf) {
419 const createTagsIn = (tags: string[]) => {
420 return tags.map(t => VideoModel.sequelize.escape(t))
421 .join(', ')
422 }
423
424 if (options.tagsOneOf) { 426 if (options.tagsOneOf) {
425 query.where[ 'id' ][ Op.and ].push({ 427 whereAnd.push({
426 [ Op.in ]: Sequelize.literal( 428 id: {
427 '(' + 429 [ Op.in ]: Sequelize.literal(
428 'SELECT "videoId" FROM "videoTag" ' + 430 '(' +
429 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + 431 'SELECT "videoId" FROM "videoTag" ' +
430 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsOneOf) + ')' + 432 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
431 ')' 433 'WHERE "tag"."name" IN (' + createSafeIn(VideoModel, options.tagsOneOf) + ')' +
432 ) 434 ')'
435 )
436 }
433 }) 437 })
434 } 438 }
435 439
436 if (options.tagsAllOf) { 440 if (options.tagsAllOf) {
437 query.where[ 'id' ][ Op.and ].push({ 441 whereAnd.push({
438 [ Op.in ]: Sequelize.literal( 442 id: {
439 '(' + 443 [ Op.in ]: Sequelize.literal(
440 'SELECT "videoId" FROM "videoTag" ' + 444 '(' +
441 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + 445 'SELECT "videoId" FROM "videoTag" ' +
442 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsAllOf) + ')' + 446 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
443 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length + 447 'WHERE "tag"."name" IN (' + createSafeIn(VideoModel, options.tagsAllOf) + ')' +
444 ')' 448 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length +
445 ) 449 ')'
450 )
451 }
446 }) 452 })
447 } 453 }
448 } 454 }
449 455
450 if (options.nsfw === true || options.nsfw === false) { 456 if (options.nsfw === true || options.nsfw === false) {
451 query.where[ 'nsfw' ] = options.nsfw 457 whereAnd.push({ nsfw: options.nsfw })
452 } 458 }
453 459
454 if (options.categoryOneOf) { 460 if (options.categoryOneOf) {
455 query.where[ 'category' ] = { 461 whereAnd.push({
456 [ Op.or ]: options.categoryOneOf 462 category: {
457 } 463 [ Op.or ]: options.categoryOneOf
464 }
465 })
458 } 466 }
459 467
460 if (options.licenceOneOf) { 468 if (options.licenceOneOf) {
461 query.where[ 'licence' ] = { 469 whereAnd.push({
462 [ Op.or ]: options.licenceOneOf 470 licence: {
463 } 471 [ Op.or ]: options.licenceOneOf
472 }
473 })
464 } 474 }
465 475
466 if (options.languageOneOf) { 476 if (options.languageOneOf) {
467 query.where[ 'language' ] = { 477 let videoLanguages = options.languageOneOf
468 [ Op.or ]: options.languageOneOf 478 if (options.languageOneOf.find(l => l === '_unknown')) {
479 videoLanguages = videoLanguages.concat([ null ])
469 } 480 }
481
482 whereAnd.push({
483 [Op.or]: [
484 {
485 language: {
486 [ Op.or ]: videoLanguages
487 }
488 },
489 {
490 id: {
491 [ Op.in ]: Sequelize.literal(
492 '(' +
493 'SELECT "videoId" FROM "videoCaption" ' +
494 'WHERE "language" IN (' + createSafeIn(VideoModel, options.languageOneOf) + ') ' +
495 ')'
496 )
497 }
498 }
499 ]
500 })
470 } 501 }
471 502
472 if (options.trendingDays) { 503 if (options.trendingDays) {
@@ -490,6 +521,10 @@ type AvailableForListIDsOptions = {
490 query.subQuery = false 521 query.subQuery = false
491 } 522 }
492 523
524 query.where = {
525 [ Op.and ]: whereAnd
526 }
527
493 return query 528 return query
494 }, 529 },
495 [ ScopeNames.WITH_THUMBNAILS ]: { 530 [ ScopeNames.WITH_THUMBNAILS ]: {
@@ -1175,7 +1210,7 @@ export class VideoModel extends Model<VideoModel> {
1175 throw new Error('Try to filter all-local but no user has not the see all videos right') 1210 throw new Error('Try to filter all-local but no user has not the see all videos right')
1176 } 1211 }
1177 1212
1178 const query: FindOptions = { 1213 const query: FindOptions & { where?: null } = {
1179 offset: options.start, 1214 offset: options.start,
1180 limit: options.count, 1215 limit: options.count,
1181 order: getVideoSort(options.sort) 1216 order: getVideoSort(options.sort)
@@ -1299,16 +1334,13 @@ export class VideoModel extends Model<VideoModel> {
1299 ) 1334 )
1300 } 1335 }
1301 1336
1302 const query: FindOptions = { 1337 const query = {
1303 attributes: { 1338 attributes: {
1304 include: attributesInclude 1339 include: attributesInclude
1305 }, 1340 },
1306 offset: options.start, 1341 offset: options.start,
1307 limit: options.count, 1342 limit: options.count,
1308 order: getVideoSort(options.sort), 1343 order: getVideoSort(options.sort)
1309 where: {
1310 [ Op.and ]: whereAnd
1311 }
1312 } 1344 }
1313 1345
1314 const serverActor = await getServerActor() 1346 const serverActor = await getServerActor()
@@ -1323,7 +1355,8 @@ export class VideoModel extends Model<VideoModel> {
1323 tagsOneOf: options.tagsOneOf, 1355 tagsOneOf: options.tagsOneOf,
1324 tagsAllOf: options.tagsAllOf, 1356 tagsAllOf: options.tagsAllOf,
1325 user: options.user, 1357 user: options.user,
1326 filter: options.filter 1358 filter: options.filter,
1359 baseWhere: whereAnd
1327 } 1360 }
1328 1361
1329 return VideoModel.getAvailableForApi(query, queryOptions) 1362 return VideoModel.getAvailableForApi(query, queryOptions)
@@ -1590,7 +1623,7 @@ export class VideoModel extends Model<VideoModel> {
1590 } 1623 }
1591 1624
1592 private static async getAvailableForApi ( 1625 private static async getAvailableForApi (
1593 query: FindOptions, 1626 query: FindOptions & { where?: null }, // Forbid where field in query
1594 options: AvailableForListIDsOptions, 1627 options: AvailableForListIDsOptions,
1595 countVideos = true 1628 countVideos = true
1596 ) { 1629 ) {
@@ -1609,11 +1642,15 @@ export class VideoModel extends Model<VideoModel> {
1609 ] 1642 ]
1610 } 1643 }
1611 1644
1612 const [ count, rowsId ] = await Promise.all([ 1645 const [ count, ids ] = await Promise.all([
1613 countVideos ? VideoModel.scope(countScope).count(countQuery) : Promise.resolve<number>(undefined), 1646 countVideos
1614 VideoModel.scope(idsScope).findAll(query) 1647 ? VideoModel.scope(countScope).count(countQuery)
1648 : Promise.resolve<number>(undefined),
1649
1650 VideoModel.scope(idsScope)
1651 .findAll(query)
1652 .then(rows => rows.map(r => r.id))
1615 ]) 1653 ])
1616 const ids = rowsId.map(r => r.id)
1617 1654
1618 if (ids.length === 0) return { data: [], total: count } 1655 if (ids.length === 0) return { data: [], total: count }
1619 1656