X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=server%2Fmodels%2Fvideo%2Fvideo-channel.ts;h=91dafbcf1d996d6b6973d0495bdc1486c9b1a0a3;hb=714e33a7428b71ef98129ce85a4bd64140bcd912;hp=0c8aef18ff8e98540faa5175500f8ebec2a377d8;hpb=a59f210ff3a4cc87dd19aeaaf0f43f63a8310791;p=github%2FChocobozzz%2FPeerTube.git

diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 0c8aef18f..91dafbcf1 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -1,5 +1,4 @@
-import * as Bluebird from 'bluebird'
-import { FindOptions, literal, Op, ScopeOptions } from 'sequelize'
+import { FindOptions, Includeable, literal, Op, QueryTypes, ScopeOptions, Transaction, WhereOptions } from 'sequelize'
 import {
   AllowNull,
   BeforeDestroy,
@@ -18,29 +17,33 @@ import {
   Table,
   UpdatedAt
 } from 'sequelize-typescript'
+import { CONFIG } from '@server/initializers/config'
 import { MAccountActor } from '@server/types/models'
+import { pick } from '@shared/core-utils'
+import { AttributesOnly } from '@shared/typescript-utils'
 import { ActivityPubActor } from '../../../shared/models/activitypub'
 import { VideoChannel, VideoChannelSummary } from '../../../shared/models/videos'
 import {
   isVideoChannelDescriptionValid,
-  isVideoChannelNameValid,
+  isVideoChannelDisplayNameValid,
   isVideoChannelSupportValid
 } from '../../helpers/custom-validators/video-channels'
 import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants'
 import { sendDeleteActor } from '../../lib/activitypub/send'
 import {
-  MChannelAccountDefault,
+  MChannel,
   MChannelActor,
-  MChannelActorAccountDefaultVideos,
   MChannelAP,
+  MChannelBannerAccountDefault,
   MChannelFormattable,
   MChannelSummaryFormattable
 } from '../../types/models/video'
 import { AccountModel, ScopeNames as AccountModelScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
-import { ActorModel, unusedActorAttributesForAPI } from '../activitypub/actor'
-import { ActorFollowModel } from '../activitypub/actor-follow'
-import { AvatarModel } from '../avatar/avatar'
+import { ActorModel, unusedActorAttributesForAPI } from '../actor/actor'
+import { ActorFollowModel } from '../actor/actor-follow'
+import { ActorImageModel } from '../actor/actor-image'
 import { ServerModel } from '../server/server'
+import { setAsUpdated } from '../shared'
 import { buildServerIdsFollowedBy, buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
 import { VideoModel } from './video'
 import { VideoPlaylistModel } from './video-playlist'
@@ -50,6 +53,7 @@ export enum ScopeNames {
   SUMMARY = 'SUMMARY',
   WITH_ACCOUNT = 'WITH_ACCOUNT',
   WITH_ACTOR = 'WITH_ACTOR',
+  WITH_ACTOR_BANNER = 'WITH_ACTOR_BANNER',
   WITH_VIDEOS = 'WITH_VIDEOS',
   WITH_STATS = 'WITH_STATS'
 }
@@ -57,6 +61,9 @@ export enum ScopeNames {
 type AvailableForListOptions = {
   actorId: number
   search?: string
+  host?: string
+  handles?: string[]
+  forCount?: boolean
 }
 
 type AvailableWithStatsOptions = {
@@ -82,36 +89,120 @@ export type SummaryOptions = {
     // Only list local channels OR channels that are on an instance followed by actorId
     const inQueryInstanceFollow = buildServerIdsFollowedBy(options.actorId)
 
+    const whereActorAnd: WhereOptions[] = [
+      {
+        [Op.or]: [
+          {
+            serverId: null
+          },
+          {
+            serverId: {
+              [Op.in]: Sequelize.literal(inQueryInstanceFollow)
+            }
+          }
+        ]
+      }
+    ]
+
+    let serverRequired = false
+    let whereServer: WhereOptions
+
+    if (options.host && options.host !== WEBSERVER.HOST) {
+      serverRequired = true
+      whereServer = { host: options.host }
+    }
+
+    if (options.host === WEBSERVER.HOST) {
+      whereActorAnd.push({
+        serverId: null
+      })
+    }
+
+    if (Array.isArray(options.handles) && options.handles.length !== 0) {
+      const or: string[] = []
+
+      for (const handle of options.handles || []) {
+        const [ preferredUsername, host ] = handle.split('@')
+
+        if (!host || host === WEBSERVER.HOST) {
+          or.push(`("preferredUsername" = ${VideoChannelModel.sequelize.escape(preferredUsername)} AND "serverId" IS NULL)`)
+        } else {
+          or.push(
+            `(` +
+              `"preferredUsername" = ${VideoChannelModel.sequelize.escape(preferredUsername)} ` +
+              `AND "host" = ${VideoChannelModel.sequelize.escape(host)}` +
+            `)`
+          )
+        }
+      }
+
+      whereActorAnd.push({
+        id: {
+          [Op.in]: literal(`(SELECT "actor".id FROM actor LEFT JOIN server on server.id = actor."serverId" WHERE ${or.join(' OR ')})`)
+        }
+      })
+    }
+
+    const channelActorInclude: Includeable[] = []
+    const accountActorInclude: Includeable[] = []
+
+    if (options.forCount !== true) {
+      accountActorInclude.push({
+        model: ServerModel,
+        required: false
+      })
+
+      accountActorInclude.push({
+        model: ActorImageModel,
+        as: 'Avatars',
+        required: false
+      })
+
+      channelActorInclude.push({
+        model: ActorImageModel,
+        as: 'Avatars',
+        required: false
+      })
+
+      channelActorInclude.push({
+        model: ActorImageModel,
+        as: 'Banners',
+        required: false
+      })
+    }
+
+    if (options.forCount !== true || serverRequired) {
+      channelActorInclude.push({
+        model: ServerModel,
+        duplicating: false,
+        required: serverRequired,
+        where: whereServer
+      })
+    }
+
     return {
       include: [
         {
           attributes: {
             exclude: unusedActorAttributesForAPI
           },
-          model: ActorModel,
+          model: ActorModel.unscoped(),
           where: {
-            [Op.or]: [
-              {
-                serverId: null
-              },
-              {
-                serverId: {
-                  [Op.in]: Sequelize.literal(inQueryInstanceFollow)
-                }
-              }
-            ]
-          }
+            [Op.and]: whereActorAnd
+          },
+          include: channelActorInclude
         },
         {
-          model: AccountModel,
+          model: AccountModel.unscoped(),
           required: true,
           include: [
             {
               attributes: {
                 exclude: unusedActorAttributesForAPI
               },
-              model: ActorModel, // Default scope includes avatar and server
-              required: true
+              model: ActorModel.unscoped(),
+              required: true,
+              include: accountActorInclude
             }
           ]
         }
@@ -119,30 +210,32 @@ export type SummaryOptions = {
     }
   },
   [ScopeNames.SUMMARY]: (options: SummaryOptions = {}) => {
+    const include: Includeable[] = [
+      {
+        attributes: [ 'id', 'preferredUsername', 'url', 'serverId' ],
+        model: ActorModel.unscoped(),
+        required: options.actorRequired ?? true,
+        include: [
+          {
+            attributes: [ 'host' ],
+            model: ServerModel.unscoped(),
+            required: false
+          },
+          {
+            model: ActorImageModel,
+            as: 'Avatars',
+            required: false
+          }
+        ]
+      }
+    ]
+
     const base: FindOptions = {
-      attributes: [ 'id', 'name', 'description', 'actorId' ],
-      include: [
-        {
-          attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
-          model: ActorModel.unscoped(),
-          required: options.actorRequired ?? true,
-          include: [
-            {
-              attributes: [ 'host' ],
-              model: ServerModel.unscoped(),
-              required: false
-            },
-            {
-              model: AvatarModel.unscoped(),
-              required: false
-            }
-          ]
-        }
-      ]
+      attributes: [ 'id', 'name', 'description', 'actorId' ]
     }
 
     if (options.withAccount === true) {
-      base.include.push({
+      include.push({
         model: AccountModel.scope({
           method: [ AccountModelScopeNames.SUMMARY, { withAccountBlockerIds: options.withAccountBlockerIds } as AccountSummaryOptions ]
         }),
@@ -150,6 +243,8 @@ export type SummaryOptions = {
       })
     }
 
+    base.include = include
+
     return base
   },
   [ScopeNames.WITH_ACCOUNT]: {
@@ -165,6 +260,20 @@ export type SummaryOptions = {
       ActorModel
     ]
   },
+  [ScopeNames.WITH_ACTOR_BANNER]: {
+    include: [
+      {
+        model: ActorModel,
+        include: [
+          {
+            model: ActorImageModel,
+            required: false,
+            as: 'Banners'
+          }
+        ]
+      }
+    ]
+  },
   [ScopeNames.WITH_VIDEOS]: {
     include: [
       VideoModel
@@ -202,6 +311,16 @@ export type SummaryOptions = {
               ')'
             ),
             'viewsPerDay'
+          ],
+          [
+            literal(
+              '(' +
+              'SELECT COALESCE(SUM("video".views), 0) AS totalViews ' +
+              'FROM "video" ' +
+              'WHERE "video"."channelId" = "VideoChannelModel"."id"' +
+              ')'
+            ),
+            'totalViews'
           ]
         ]
       }
@@ -221,10 +340,10 @@ export type SummaryOptions = {
     }
   ]
 })
-export class VideoChannelModel extends Model<VideoChannelModel> {
+export class VideoChannelModel extends Model<Partial<AttributesOnly<VideoChannelModel>>> {
 
   @AllowNull(false)
-  @Is('VideoChannelName', value => throwIfNotValid(value, isVideoChannelNameValid, 'name'))
+  @Is('VideoChannelName', value => throwIfNotValid(value, isVideoChannelDisplayNameValid, 'name'))
   @Column
   name: string
 
@@ -265,8 +384,7 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
   @BelongsTo(() => AccountModel, {
     foreignKey: {
       allowNull: false
-    },
-    hooks: true
+    }
   })
   Account: AccountModel
 
@@ -311,35 +429,51 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
       }
     }
 
-    return VideoChannelModel.count(query)
+    return VideoChannelModel.unscoped().count(query)
   }
 
-  static listForApi (parameters: {
-    actorId: number
-    start: number
-    count: number
-    sort: string
-  }) {
-    const { actorId } = parameters
+  static async getStats () {
 
-    const query = {
-      offset: parameters.start,
-      limit: parameters.count,
-      order: getSort(parameters.sort)
+    function getActiveVideoChannels (days: number) {
+      const options = {
+        type: QueryTypes.SELECT as QueryTypes.SELECT,
+        raw: true
+      }
+
+      const query = `
+SELECT          COUNT(DISTINCT("VideoChannelModel"."id")) AS "count"
+FROM            "videoChannel"                            AS "VideoChannelModel"
+INNER JOIN      "video"                                   AS "Videos"
+ON              "VideoChannelModel"."id" = "Videos"."channelId"
+AND             ("Videos"."publishedAt" > Now() - interval '${days}d')
+INNER JOIN      "account" AS "Account"
+ON              "VideoChannelModel"."accountId" = "Account"."id"
+INNER JOIN      "actor" AS "Account->Actor"
+ON              "Account"."actorId" = "Account->Actor"."id"
+AND             "Account->Actor"."serverId" IS NULL
+LEFT OUTER JOIN "server" AS "Account->Actor->Server"
+ON              "Account->Actor"."serverId" = "Account->Actor->Server"."id"`
+
+      return VideoChannelModel.sequelize.query<{ count: string }>(query, options)
+                              .then(r => parseInt(r[0].count, 10))
     }
 
-    const scopes = {
-      method: [ ScopeNames.FOR_API, { actorId } as AvailableForListOptions ]
+    const totalLocalVideoChannels = await VideoChannelModel.count()
+    const totalLocalDailyActiveVideoChannels = await getActiveVideoChannels(1)
+    const totalLocalWeeklyActiveVideoChannels = await getActiveVideoChannels(7)
+    const totalLocalMonthlyActiveVideoChannels = await getActiveVideoChannels(30)
+    const totalHalfYearActiveVideoChannels = await getActiveVideoChannels(180)
+
+    return {
+      totalLocalVideoChannels,
+      totalLocalDailyActiveVideoChannels,
+      totalLocalWeeklyActiveVideoChannels,
+      totalLocalMonthlyActiveVideoChannels,
+      totalHalfYearActiveVideoChannels
     }
-    return VideoChannelModel
-      .scope(scopes)
-      .findAndCountAll(query)
-      .then(({ rows, count }) => {
-        return { total: count, data: rows }
-      })
   }
 
-  static listLocalsForSitemap (sort: string): Bluebird<MChannelActor[]> {
+  static listLocalsForSitemap (sort: string): Promise<MChannelActor[]> {
     const query = {
       attributes: [ ],
       offset: 0,
@@ -360,26 +494,43 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
       .findAll(query)
   }
 
-  static searchForApi (options: {
-    actorId: number
-    search: string
+  static listForApi (parameters: Pick<AvailableForListOptions, 'actorId'> & {
     start: number
     count: number
     sort: string
   }) {
-    const attributesInclude = []
-    const escapedSearch = VideoModel.sequelize.escape(options.search)
-    const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
-    attributesInclude.push(createSimilarityAttribute('VideoChannelModel.name', options.search))
+    const { actorId } = parameters
 
     const query = {
-      attributes: {
-        include: attributesInclude
-      },
-      offset: options.start,
-      limit: options.count,
-      order: getSort(options.sort),
-      where: {
+      offset: parameters.start,
+      limit: parameters.count,
+      order: getSort(parameters.sort)
+    }
+
+    const getScope = (forCount: boolean) => {
+      return { method: [ ScopeNames.FOR_API, { actorId, forCount } as AvailableForListOptions ] }
+    }
+
+    return Promise.all([
+      VideoChannelModel.scope(getScope(true)).count(),
+      VideoChannelModel.scope(getScope(false)).findAll(query)
+    ]).then(([ total, data ]) => ({ total, data }))
+  }
+
+  static searchForApi (options: Pick<AvailableForListOptions, 'actorId' | 'search' | 'host' | 'handles'> & {
+    start: number
+    count: number
+    sort: string
+  }) {
+    let attributesInclude: any[] = [ literal('0 as similarity') ]
+    let where: WhereOptions
+
+    if (options.search) {
+      const escapedSearch = VideoChannelModel.sequelize.escape(options.search)
+      const escapedLikeSearch = VideoChannelModel.sequelize.escape('%' + options.search + '%')
+      attributesInclude = [ createSimilarityAttribute('VideoChannelModel.name', options.search) ]
+
+      where = {
         [Op.or]: [
           Sequelize.literal(
             'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
@@ -391,18 +542,35 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
       }
     }
 
-    const scopes = {
-      method: [ ScopeNames.FOR_API, { actorId: options.actorId } as AvailableForListOptions ]
+    const query = {
+      attributes: {
+        include: attributesInclude
+      },
+      offset: options.start,
+      limit: options.count,
+      order: getSort(options.sort),
+      where
     }
-    return VideoChannelModel
-      .scope(scopes)
-      .findAndCountAll(query)
-      .then(({ rows, count }) => {
-        return { total: count, data: rows }
-      })
+
+    const getScope = (forCount: boolean) => {
+      return {
+        method: [
+          ScopeNames.FOR_API, {
+            ...pick(options, [ 'actorId', 'host', 'handles' ]),
+
+            forCount
+          } as AvailableForListOptions
+        ]
+      }
+    }
+
+    return Promise.all([
+      VideoChannelModel.scope(getScope(true)).count(query),
+      VideoChannelModel.scope(getScope(false)).findAll(query)
+    ]).then(([ total, data ]) => ({ total, data }))
   }
 
-  static listByAccount (options: {
+  static listByAccountForAPI (options: {
     accountId: number
     start: number
     count: number
@@ -425,64 +593,67 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
       }
       : null
 
-    const query = {
-      offset: options.start,
-      limit: options.count,
-      order: getSort(options.sort),
-      include: [
-        {
-          model: AccountModel,
-          where: {
-            id: options.accountId
-          },
-          required: true
-        }
-      ],
-      where
+    const getQuery = (forCount: boolean) => {
+      const accountModel = forCount
+        ? AccountModel.unscoped()
+        : AccountModel
+
+      return {
+        offset: options.start,
+        limit: options.count,
+        order: getSort(options.sort),
+        include: [
+          {
+            model: accountModel,
+            where: {
+              id: options.accountId
+            },
+            required: true
+          }
+        ],
+        where
+      }
     }
 
-    const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ]
+    const findScopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR_BANNER ]
 
     if (options.withStats === true) {
-      scopes.push({
+      findScopes.push({
         method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
       })
     }
 
-    return VideoChannelModel
-      .scope(scopes)
-      .findAndCountAll(query)
-      .then(({ rows, count }) => {
-        return { total: count, data: rows }
-      })
+    return Promise.all([
+      VideoChannelModel.unscoped().count(getQuery(true)),
+      VideoChannelModel.scope(findScopes).findAll(getQuery(false))
+    ]).then(([ total, data ]) => ({ total, data }))
   }
 
-  static loadByIdAndPopulateAccount (id: number): Bluebird<MChannelAccountDefault> {
-    return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
-      .findByPk(id)
-  }
-
-  static loadByIdAndAccount (id: number, accountId: number): Bluebird<MChannelAccountDefault> {
+  static listAllByAccount (accountId: number): Promise<MChannel[]> {
     const query = {
-      where: {
-        id,
-        accountId
-      }
+      limit: CONFIG.VIDEO_CHANNELS.MAX_PER_USER,
+      include: [
+        {
+          attributes: [],
+          model: AccountModel.unscoped(),
+          where: {
+            id: accountId
+          },
+          required: true
+        }
+      ]
     }
 
-    return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
-      .findOne(query)
+    return VideoChannelModel.findAll(query)
   }
 
-  static loadAndPopulateAccount (id: number): Bluebird<MChannelAccountDefault> {
+  static loadAndPopulateAccount (id: number, transaction?: Transaction): Promise<MChannelBannerAccountDefault> {
     return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
-      .findByPk(id)
+      .scope([ ScopeNames.WITH_ACTOR_BANNER, ScopeNames.WITH_ACCOUNT ])
+      .findByPk(id, { transaction })
   }
 
-  static loadByUrlAndPopulateAccount (url: string): Bluebird<MChannelAccountDefault> {
+  static loadByUrlAndPopulateAccount (url: string): Promise<MChannelBannerAccountDefault> {
     const query = {
       include: [
         {
@@ -490,7 +661,14 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
           required: true,
           where: {
             url
-          }
+          },
+          include: [
+            {
+              model: ActorImageModel,
+              required: false,
+              as: 'Banners'
+            }
+          ]
         }
       ]
     }
@@ -508,7 +686,7 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
     return VideoChannelModel.loadByNameAndHostAndPopulateAccount(name, host)
   }
 
-  static loadLocalByNameAndPopulateAccount (name: string): Bluebird<MChannelAccountDefault> {
+  static loadLocalByNameAndPopulateAccount (name: string): Promise<MChannelBannerAccountDefault> {
     const query = {
       include: [
         {
@@ -517,17 +695,24 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
           where: {
             preferredUsername: name,
             serverId: null
-          }
+          },
+          include: [
+            {
+              model: ActorImageModel,
+              required: false,
+              as: 'Banners'
+            }
+          ]
         }
       ]
     }
 
     return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
+      .scope([ ScopeNames.WITH_ACCOUNT ])
       .findOne(query)
   }
 
-  static loadByNameAndHostAndPopulateAccount (name: string, host: string): Bluebird<MChannelAccountDefault> {
+  static loadByNameAndHostAndPopulateAccount (name: string, host: string): Promise<MChannelBannerAccountDefault> {
     const query = {
       include: [
         {
@@ -541,6 +726,11 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
               model: ServerModel,
               required: true,
               where: { host }
+            },
+            {
+              model: ActorImageModel,
+              required: false,
+              as: 'Banners'
             }
           ]
         }
@@ -548,22 +738,10 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
     }
 
     return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
+      .scope([ ScopeNames.WITH_ACCOUNT ])
       .findOne(query)
   }
 
-  static loadAndPopulateAccountAndVideos (id: number): Bluebird<MChannelActorAccountDefaultVideos> {
-    const options = {
-      include: [
-        VideoModel
-      ]
-    }
-
-    return VideoChannelModel.unscoped()
-      .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_VIDEOS ])
-      .findByPk(id, options)
-  }
-
   toFormattedSummaryJSON (this: MChannelSummaryFormattable): VideoChannelSummary {
     const actor = this.Actor.toFormattedSummaryJSON()
 
@@ -573,6 +751,9 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
       displayName: this.getDisplayName(),
       url: actor.url,
       host: actor.host,
+      avatars: actor.avatars,
+
+      // TODO: remove, deprecated in 4.2
       avatar: actor.avatar
     }
   }
@@ -595,6 +776,8 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
         })
     }
 
+    const totalViews = this.get('totalViews') as number
+
     const actor = this.Actor.toFormattedJSON()
     const videoChannel = {
       id: this.id,
@@ -602,11 +785,18 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
       description: this.description,
       support: this.support,
       isLocal: this.Actor.isOwned(),
-      createdAt: this.createdAt,
       updatedAt: this.updatedAt,
+
       ownerAccount: undefined,
+
       videosCount,
-      viewsPerDay
+      viewsPerDay,
+      totalViews,
+
+      avatars: actor.avatars,
+
+      // TODO: remove, deprecated in 4.2
+      avatar: actor.avatar
     }
 
     if (this.Account) videoChannel.ownerAccount = this.Account.toFormattedJSON()
@@ -640,4 +830,8 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
   isOutdated () {
     return this.Actor.isOutdated()
   }
+
+  setAsUpdated (transaction?: Transaction) {
+    return setAsUpdated('videoChannel', this.id, transaction)
+  }
 }