X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=server%2Fmodels%2Fvideo%2Fvideo.ts;h=39fe2100789b94b9e2ea768cadbebd72bffd12b5;hb=b2977eecb8eb5d599df0c6a7ab99a437a6a969c7;hp=15b4dda5b2fb57fee266cdab07fb422b921fc7fb;hpb=57c36b277e68b764dd34cb2e449f6e2ca3d1e9b6;p=github%2FChocobozzz%2FPeerTube.git

diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 15b4dda5b..39fe21007 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -83,7 +83,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate'
 import { ActorModel } from '../activitypub/actor'
 import { AvatarModel } from '../avatar/avatar'
 import { ServerModel } from '../server/server'
-import { buildTrigramSearchIndex, createSearchTrigramQuery, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
+import { buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
 import { TagModel } from './tag'
 import { VideoAbuseModel } from './video-abuse'
 import { VideoChannelModel } from './video-channel'
@@ -98,26 +98,22 @@ import { VideoCaptionModel } from './video-caption'
 const indexes: Sequelize.DefineIndexesOptions[] = [
   buildTrigramSearchIndex('video_name_trigram', 'name'),
 
+  { fields: [ 'createdAt' ] },
+  { fields: [ 'publishedAt' ] },
+  { fields: [ 'duration' ] },
+  { fields: [ 'category' ] },
+  { fields: [ 'licence' ] },
+  { fields: [ 'nsfw' ] },
+  { fields: [ 'language' ] },
+  { fields: [ 'waitTranscoding' ] },
+  { fields: [ 'state' ] },
+  { fields: [ 'remote' ] },
+  { fields: [ 'views' ] },
+  { fields: [ 'likes' ] },
+  { fields: [ 'channelId' ] },
   {
-    fields: [ 'createdAt' ]
-  },
-  {
-    fields: [ 'duration' ]
-  },
-  {
-    fields: [ 'views' ]
-  },
-  {
-    fields: [ 'likes' ]
-  },
-  {
-    fields: [ 'uuid' ]
-  },
-  {
-    fields: [ 'channelId' ]
-  },
-  {
-    fields: [ 'id', 'privacy', 'state', 'waitTranscoding' ]
+    fields: [ 'uuid' ],
+    unique: true
   },
   {
     fields: [ 'url'],
@@ -133,16 +129,22 @@ export enum ScopeNames {
   WITH_SCHEDULED_UPDATE = 'WITH_SCHEDULED_UPDATE'
 }
 
+type AvailableForListOptions = {
+  actorId: number,
+  filter?: VideoFilter,
+  categoryOneOf?: number[],
+  nsfw?: boolean,
+  licenceOneOf?: number[],
+  languageOneOf?: string[],
+  tagsOneOf?: string[],
+  tagsAllOf?: string[],
+  withFiles?: boolean,
+  accountId?: number,
+  videoChannelId?: number
+}
+
 @Scopes({
-  [ScopeNames.AVAILABLE_FOR_LIST]: (options: {
-    actorId: number,
-    hideNSFW: boolean,
-    filter?: VideoFilter,
-    category?: number,
-    withFiles?: boolean,
-    accountId?: number,
-    videoChannelId?: number
-  }) => {
+  [ScopeNames.AVAILABLE_FOR_LIST]: (options: AvailableForListOptions) => {
     const accountInclude = {
       attributes: [ 'id', 'name' ],
       model: AccountModel.unscoped(),
@@ -197,6 +199,8 @@ export enum ScopeNames {
 
     // Force actorId to be a number to avoid SQL injections
     const actorIdNumber = parseInt(options.actorId.toString(), 10)
+
+    // FIXME: It would be more efficient to use a CTE so we join AFTER the filters, but sequelize does not support it...
     const query: IFindOptions<VideoModel> = {
       where: {
         id: {
@@ -205,16 +209,22 @@ export enum ScopeNames {
           ),
           [ Sequelize.Op.in ]: Sequelize.literal(
             '(' +
-            'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
-            'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
-            'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
-            ' UNION ' +
-            'SELECT "video"."id" AS "id" FROM "video" ' +
-            'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-            'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
-            'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
-            'LEFT JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
-            'WHERE "actor"."serverId" IS NULL OR "actorFollow"."actorId" = ' + actorIdNumber +
+              'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
+              'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
+              'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
+              ' UNION ' +
+              'SELECT "video"."id" AS "id" FROM "video" ' +
+              'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+              'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
+              'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
+              'WHERE "actor"."serverId" IS NULL ' +
+              ' UNION ALL ' +
+              'SELECT "video"."id" AS "id" FROM "video" ' +
+              'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+              'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
+              'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
+              'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
+              'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
             ')'
           )
         },
@@ -243,13 +253,55 @@ export enum ScopeNames {
       })
     }
 
-    // Hide nsfw videos?
-    if (options.hideNSFW === true) {
-      query.where['nsfw'] = false
+    // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN()
+    if (options.tagsAllOf || options.tagsOneOf) {
+      const createTagsIn = (tags: string[]) => {
+        return tags.map(t => VideoModel.sequelize.escape(t))
+                   .join(', ')
+      }
+
+      if (options.tagsOneOf) {
+        query.where['id'][Sequelize.Op.in] = Sequelize.literal(
+          '(' +
+            'SELECT "videoId" FROM "videoTag" ' +
+            'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
+            'WHERE "tag"."name" IN (' + createTagsIn(options.tagsOneOf) + ')' +
+          ')'
+        )
+      }
+
+      if (options.tagsAllOf) {
+        query.where['id'][Sequelize.Op.in] = Sequelize.literal(
+            '(' +
+              'SELECT "videoId" FROM "videoTag" ' +
+              'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
+              'WHERE "tag"."name" IN (' + createTagsIn(options.tagsAllOf) + ')' +
+              'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length +
+            ')'
+        )
+      }
+    }
+
+    if (options.nsfw === true || options.nsfw === false) {
+      query.where['nsfw'] = options.nsfw
+    }
+
+    if (options.categoryOneOf) {
+      query.where['category'] = {
+        [Sequelize.Op.or]: options.categoryOneOf
+      }
+    }
+
+    if (options.licenceOneOf) {
+      query.where['licence'] = {
+        [Sequelize.Op.or]: options.licenceOneOf
+      }
     }
 
-    if (options.category) {
-      query.where['category'] = options.category
+    if (options.languageOneOf) {
+      query.where['language'] = {
+        [Sequelize.Op.or]: options.languageOneOf
+      }
     }
 
     if (options.accountId) {
@@ -325,7 +377,7 @@ export enum ScopeNames {
     include: [
       {
         model: () => VideoFileModel.unscoped(),
-        required: true
+        required: false
       }
     ]
   },
@@ -555,8 +607,6 @@ export class VideoModel extends Model<VideoModel> {
         }) as VideoChannelModel
       }
 
-      logger.debug('Sending delete of video %s.', instance.url)
-
       return sendDeleteVideo(instance, options.transaction)
     }
 
@@ -567,7 +617,7 @@ export class VideoModel extends Model<VideoModel> {
   static async removeFiles (instance: VideoModel) {
     const tasks: Promise<any>[] = []
 
-    logger.debug('Removing files of video %s.', instance.url)
+    logger.info('Removing files of video %s.', instance.url)
 
     tasks.push(instance.removeThumbnail())
 
@@ -588,7 +638,7 @@ export class VideoModel extends Model<VideoModel> {
     // Do not wait video deletion because we could be in a transaction
     Promise.all(tasks)
       .catch(err => {
-        logger.error('Some errors when removing files of video %s in after destroy hook.', instance.uuid, { err })
+        logger.error('Some errors when removing files of video %s in before destroy hook.', instance.uuid, { err })
       })
 
     return undefined
@@ -756,9 +806,13 @@ export class VideoModel extends Model<VideoModel> {
     start: number,
     count: number,
     sort: string,
-    hideNSFW: boolean,
+    nsfw: boolean,
     withFiles: boolean,
-    category?: number,
+    categoryOneOf?: number[],
+    licenceOneOf?: number[],
+    languageOneOf?: string[],
+    tagsOneOf?: string[],
+    tagsAllOf?: string[],
     filter?: VideoFilter,
     accountId?: number,
     videoChannelId?: number
@@ -774,13 +828,17 @@ export class VideoModel extends Model<VideoModel> {
       method: [
         ScopeNames.AVAILABLE_FOR_LIST, {
           actorId: serverActor.id,
-          hideNSFW: options.hideNSFW,
-          category: options.category,
+          nsfw: options.nsfw,
+          categoryOneOf: options.categoryOneOf,
+          licenceOneOf: options.licenceOneOf,
+          languageOneOf: options.languageOneOf,
+          tagsOneOf: options.tagsOneOf,
+          tagsAllOf: options.tagsAllOf,
           filter: options.filter,
           withFiles: options.withFiles,
           accountId: options.accountId,
           videoChannelId: options.videoChannelId
-        }
+        } as AvailableForListOptions
       ]
     }
 
@@ -794,15 +852,84 @@ export class VideoModel extends Model<VideoModel> {
       })
   }
 
-  static async searchAndPopulateAccountAndServer (value: string, start: number, count: number, sort: string, hideNSFW: boolean) {
+  static async searchAndPopulateAccountAndServer (options: {
+    search?: string
+    start?: number
+    count?: number
+    sort?: string
+    startDate?: string // ISO 8601
+    endDate?: string // ISO 8601
+    nsfw?: boolean
+    categoryOneOf?: number[]
+    licenceOneOf?: number[]
+    languageOneOf?: string[]
+    tagsOneOf?: string[]
+    tagsAllOf?: string[]
+    durationMin?: number // seconds
+    durationMax?: number // seconds
+  }) {
+    const whereAnd = [ ]
+
+    if (options.startDate || options.endDate) {
+      const publishedAtRange = { }
+
+      if (options.startDate) publishedAtRange[Sequelize.Op.gte] = options.startDate
+      if (options.endDate) publishedAtRange[Sequelize.Op.lte] = options.endDate
+
+      whereAnd.push({ publishedAt: publishedAtRange })
+    }
+
+    if (options.durationMin || options.durationMax) {
+      const durationRange = { }
+
+      if (options.durationMin) durationRange[Sequelize.Op.gte] = options.durationMin
+      if (options.durationMax) durationRange[Sequelize.Op.lte] = options.durationMax
+
+      whereAnd.push({ duration: durationRange })
+    }
+
+    const attributesInclude = []
+    const escapedSearch = VideoModel.sequelize.escape(options.search)
+    const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
+    if (options.search) {
+      whereAnd.push(
+        {
+          id: {
+            [ Sequelize.Op.in ]: Sequelize.literal(
+              '(' +
+                'SELECT "video"."id" FROM "video" WHERE ' +
+                'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
+                'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
+                'UNION ALL ' +
+                'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
+                'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
+                'WHERE "tag"."name" = ' + escapedSearch +
+              ')'
+            )
+          }
+        }
+      )
+
+      attributesInclude.push(createSimilarityAttribute('VideoModel.name', options.search))
+    }
+
+    // Cannot search on similarity if we don't have a search
+    if (!options.search) {
+      attributesInclude.push(
+        Sequelize.literal('0 as similarity')
+      )
+    }
+
     const query: IFindOptions<VideoModel> = {
       attributes: {
-        include: [ createSimilarityAttribute('VideoModel.name', value) ]
+        include: attributesInclude
       },
-      offset: start,
-      limit: count,
-      order: getSort(sort),
-      where: createSearchTrigramQuery('VideoModel.name', value)
+      offset: options.start,
+      limit: options.count,
+      order: getSort(options.sort),
+      where: {
+        [ Sequelize.Op.and ]: whereAnd
+      }
     }
 
     const serverActor = await getServerActor()
@@ -810,8 +937,13 @@ export class VideoModel extends Model<VideoModel> {
       method: [
         ScopeNames.AVAILABLE_FOR_LIST, {
           actorId: serverActor.id,
-          hideNSFW
-        }
+          nsfw: options.nsfw,
+          categoryOneOf: options.categoryOneOf,
+          licenceOneOf: options.licenceOneOf,
+          languageOneOf: options.languageOneOf,
+          tagsOneOf: options.tagsOneOf,
+          tagsAllOf: options.tagsAllOf
+        } as AvailableForListOptions
       ]
     }
 
@@ -825,8 +957,10 @@ export class VideoModel extends Model<VideoModel> {
       })
   }
 
-  static load (id: number) {
-    return VideoModel.findById(id)
+  static load (id: number, t?: Sequelize.Transaction) {
+    const options = t ? { transaction: t } : undefined
+
+    return VideoModel.findById(id, options)
   }
 
   static loadByUrlAndPopulateAccount (url: string, t?: Sequelize.Transaction) {
@@ -1221,7 +1355,8 @@ export class VideoModel extends Model<VideoModel> {
         mimeType: VIDEO_EXT_MIMETYPE[file.extname],
         href: this.getVideoFileUrl(file, baseUrlHttp),
         width: file.resolution,
-        size: file.size
+        size: file.size,
+        fps: file.fps
       })
 
       url.push({
@@ -1437,21 +1572,25 @@ export class VideoModel extends Model<VideoModel> {
   removeThumbnail () {
     const thumbnailPath = join(CONFIG.STORAGE.THUMBNAILS_DIR, this.getThumbnailName())
     return unlinkPromise(thumbnailPath)
+      .catch(err => logger.warn('Cannot delete thumbnail %s.', thumbnailPath, { err }))
   }
 
   removePreview () {
-    // Same name than video thumbnail
-    return unlinkPromise(CONFIG.STORAGE.PREVIEWS_DIR + this.getPreviewName())
+    const previewPath = join(CONFIG.STORAGE.PREVIEWS_DIR + this.getPreviewName())
+    return unlinkPromise(previewPath)
+      .catch(err => logger.warn('Cannot delete preview %s.', previewPath, { err }))
   }
 
   removeFile (videoFile: VideoFileModel) {
     const filePath = join(CONFIG.STORAGE.VIDEOS_DIR, this.getVideoFilename(videoFile))
     return unlinkPromise(filePath)
+      .catch(err => logger.warn('Cannot delete file %s.', filePath, { err }))
   }
 
   removeTorrent (videoFile: VideoFileModel) {
     const torrentPath = join(CONFIG.STORAGE.TORRENTS_DIR, this.getTorrentFileName(videoFile))
     return unlinkPromise(torrentPath)
+      .catch(err => logger.warn('Cannot delete torrent %s.', torrentPath, { err }))
   }
 
   getActivityStreamDuration () {