server/models/video/video-comment.ts

   1 import { AllowNull, BelongsTo, Column, CreatedAt, DataType, ForeignKey, Is, Model, Scopes, Table, UpdatedAt } from 'sequelize-typescript'
   2 import { ActivityTagObject, ActivityTombstoneObject } from '../../../shared/models/activitypub/objects/common-objects'
   3 import { VideoCommentObject } from '../../../shared/models/activitypub/objects/video-comment-object'
   4 import { VideoComment } from '../../../shared/models/videos/video-comment.model'
   5 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
   6 import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants'
   7 import { AccountModel } from '../account/account'
   8 import { ActorModel } from '../activitypub/actor'
   9 import { buildBlockedAccountSQL, buildLocalAccountIdsIn, getCommentSort, throwIfNotValid } from '../utils'
  10 import { VideoModel } from './video'
  11 import { VideoChannelModel } from './video-channel'
  12 import { getServerActor } from '../../helpers/utils'
  13 import { actorNameAlphabet } from '../../helpers/custom-validators/activitypub/actor'
  14 import { regexpCapture } from '../../helpers/regexp'
  15 import { uniq } from 'lodash'
  16 import { FindOptions, Op, Order, ScopeOptions, Sequelize, Transaction } from 'sequelize'
  17 import * as Bluebird from 'bluebird'
  18 import {
  19   MComment,
  20   MCommentAP,
  21   MCommentFormattable,
  22   MCommentId,
  23   MCommentOwner,
  24   MCommentOwnerReplyVideoLight,
  25   MCommentOwnerVideo,
  26   MCommentOwnerVideoFeed,
  27   MCommentOwnerVideoReply
  28 } from '../../typings/models/video'
  29 import { MUserAccountId } from '@server/typings/models'
  30
  31 enum ScopeNames {
  32   WITH_ACCOUNT = 'WITH_ACCOUNT',
  33   WITH_IN_REPLY_TO = 'WITH_IN_REPLY_TO',
  34   WITH_VIDEO = 'WITH_VIDEO',
  35   ATTRIBUTES_FOR_API = 'ATTRIBUTES_FOR_API'
  36 }
  37
  38 @Scopes(() => ({
  39   [ScopeNames.ATTRIBUTES_FOR_API]: (serverAccountId: number, userAccountId?: number) => {
  40     return {
  41       attributes: {
  42         include: [
  43           [
  44             Sequelize.literal(
  45               '(' +
  46                 'WITH "blocklist" AS (' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')' +
  47                 'SELECT COUNT("replies"."id") - (' +
  48                   'SELECT COUNT("replies"."id") ' +
  49                   'FROM "videoComment" AS "replies" ' +
  50                   'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' +
  51                   'AND "accountId" IN (SELECT "id" FROM "blocklist")' +
  52                 ')' +
  53                 'FROM "videoComment" AS "replies" ' +
  54                 'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' +
  55                 'AND "accountId" NOT IN (SELECT "id" FROM "blocklist")' +
  56               ')'
  57             ),
  58             'totalReplies'
  59           ],
  60           [
  61             Sequelize.literal(
  62               '(' +
  63                 'SELECT COUNT("replies"."id") ' +
  64                 'FROM "videoComment" AS "replies" ' +
  65                 'INNER JOIN "video" ON "video"."id" = "replies"."videoId" ' +
  66                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
  67                 'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' +
  68                 'AND "replies"."accountId" = "videoChannel"."accountId"' +
  69               ')'
  70             ),
  71             'totalRepliesFromVideoAuthor'
  72           ]
  73         ]
  74       }
  75     } as FindOptions
  76   },
  77   [ScopeNames.WITH_ACCOUNT]: {
  78     include: [
  79       {
  80         model: AccountModel
  81       }
  82     ]
  83   },
  84   [ScopeNames.WITH_IN_REPLY_TO]: {
  85     include: [
  86       {
  87         model: VideoCommentModel,
  88         as: 'InReplyToVideoComment'
  89       }
  90     ]
  91   },
  92   [ScopeNames.WITH_VIDEO]: {
  93     include: [
  94       {
  95         model: VideoModel,
  96         required: true,
  97         include: [
  98           {
  99             model: VideoChannelModel,
 100             required: true,
 101             include: [
 102               {
 103                 model: AccountModel,
 104                 required: true
 105               }
 106             ]
 107           }
 108         ]
 109       }
 110     ]
 111   }
 112 }))
 113 @Table({
 114   tableName: 'videoComment',
 115   indexes: [
 116     {
 117       fields: [ 'videoId' ]
 118     },
 119     {
 120       fields: [ 'videoId', 'originCommentId' ]
 121     },
 122     {
 123       fields: [ 'url' ],
 124       unique: true
 125     },
 126     {
 127       fields: [ 'accountId' ]
 128     }
 129   ]
 130 })
 131 export class VideoCommentModel extends Model<VideoCommentModel> {
 132   @CreatedAt
 133   createdAt: Date
 134
 135   @UpdatedAt
 136   updatedAt: Date
 137
 138   @AllowNull(true)
 139   @Column(DataType.DATE)
 140   deletedAt: Date
 141
 142   @AllowNull(false)
 143   @Is('VideoCommentUrl', value => throwIfNotValid(value, isActivityPubUrlValid, 'url'))
 144   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.URL.max))
 145   url: string
 146
 147   @AllowNull(false)
 148   @Column(DataType.TEXT)
 149   text: string
 150
 151   @ForeignKey(() => VideoCommentModel)
 152   @Column
 153   originCommentId: number
 154
 155   @BelongsTo(() => VideoCommentModel, {
 156     foreignKey: {
 157       name: 'originCommentId',
 158       allowNull: true
 159     },
 160     as: 'OriginVideoComment',
 161     onDelete: 'CASCADE'
 162   })
 163   OriginVideoComment: VideoCommentModel
 164
 165   @ForeignKey(() => VideoCommentModel)
 166   @Column
 167   inReplyToCommentId: number
 168
 169   @BelongsTo(() => VideoCommentModel, {
 170     foreignKey: {
 171       name: 'inReplyToCommentId',
 172       allowNull: true
 173     },
 174     as: 'InReplyToVideoComment',
 175     onDelete: 'CASCADE'
 176   })
 177   InReplyToVideoComment: VideoCommentModel | null
 178
 179   @ForeignKey(() => VideoModel)
 180   @Column
 181   videoId: number
 182
 183   @BelongsTo(() => VideoModel, {
 184     foreignKey: {
 185       allowNull: false
 186     },
 187     onDelete: 'CASCADE'
 188   })
 189   Video: VideoModel
 190
 191   @ForeignKey(() => AccountModel)
 192   @Column
 193   accountId: number
 194
 195   @BelongsTo(() => AccountModel, {
 196     foreignKey: {
 197       allowNull: true
 198     },
 199     onDelete: 'CASCADE'
 200   })
 201   Account: AccountModel
 202
 203   static loadById (id: number, t?: Transaction): Bluebird<MComment> {
 204     const query: FindOptions = {
 205       where: {
 206         id
 207       }
 208     }
 209
 210     if (t !== undefined) query.transaction = t
 211
 212     return VideoCommentModel.findOne(query)
 213   }
 214
 215   static loadByIdAndPopulateVideoAndAccountAndReply (id: number, t?: Transaction): Bluebird<MCommentOwnerVideoReply> {
 216     const query: FindOptions = {
 217       where: {
 218         id
 219       }
 220     }
 221
 222     if (t !== undefined) query.transaction = t
 223
 224     return VideoCommentModel
 225       .scope([ ScopeNames.WITH_VIDEO, ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_IN_REPLY_TO ])
 226       .findOne(query)
 227   }
 228
 229   static loadByUrlAndPopulateAccountAndVideo (url: string, t?: Transaction): Bluebird<MCommentOwnerVideo> {
 230     const query: FindOptions = {
 231       where: {
 232         url
 233       }
 234     }
 235
 236     if (t !== undefined) query.transaction = t
 237
 238     return VideoCommentModel.scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_VIDEO ]).findOne(query)
 239   }
 240
 241   static loadByUrlAndPopulateReplyAndVideoUrlAndAccount (url: string, t?: Transaction): Bluebird<MCommentOwnerReplyVideoLight> {
 242     const query: FindOptions = {
 243       where: {
 244         url
 245       },
 246       include: [
 247         {
 248           attributes: [ 'id', 'url' ],
 249           model: VideoModel.unscoped()
 250         }
 251       ]
 252     }
 253
 254     if (t !== undefined) query.transaction = t
 255
 256     return VideoCommentModel.scope([ ScopeNames.WITH_IN_REPLY_TO, ScopeNames.WITH_ACCOUNT ]).findOne(query)
 257   }
 258
 259   static async listThreadsForApi (parameters: {
 260     videoId: number
 261     start: number
 262     count: number
 263     sort: string
 264     user?: MUserAccountId
 265   }) {
 266     const { videoId, start, count, sort, user } = parameters
 267
 268     const serverActor = await getServerActor()
 269     const serverAccountId = serverActor.Account.id
 270     const userAccountId = user ? user.Account.id : undefined
 271
 272     const query = {
 273       offset: start,
 274       limit: count,
 275       order: getCommentSort(sort),
 276       where: {
 277         videoId,
 278         inReplyToCommentId: null,
 279         accountId: {
 280           [Op.notIn]: Sequelize.literal(
 281             '(' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')'
 282           )
 283         }
 284       }
 285     }
 286
 287     const scopes: (string | ScopeOptions)[] = [
 288       ScopeNames.WITH_ACCOUNT,
 289       {
 290         method: [ ScopeNames.ATTRIBUTES_FOR_API, serverAccountId, userAccountId ]
 291       }
 292     ]
 293
 294     return VideoCommentModel
 295       .scope(scopes)
 296       .findAndCountAll(query)
 297       .then(({ rows, count }) => {
 298         return { total: count, data: rows }
 299       })
 300   }
 301
 302   static async listThreadCommentsForApi (parameters: {
 303     videoId: number
 304     threadId: number
 305     user?: MUserAccountId
 306   }) {
 307     const { videoId, threadId, user } = parameters
 308
 309     const serverActor = await getServerActor()
 310     const serverAccountId = serverActor.Account.id
 311     const userAccountId = user ? user.Account.id : undefined
 312
 313     const query = {
 314       order: [ [ 'createdAt', 'ASC' ], [ 'updatedAt', 'ASC' ] ] as Order,
 315       where: {
 316         videoId,
 317         [Op.or]: [
 318           { id: threadId },
 319           { originCommentId: threadId }
 320         ],
 321         accountId: {
 322           [Op.notIn]: Sequelize.literal(
 323             '(' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')'
 324           )
 325         }
 326       }
 327     }
 328
 329     const scopes: any[] = [
 330       ScopeNames.WITH_ACCOUNT,
 331       {
 332         method: [ ScopeNames.ATTRIBUTES_FOR_API, serverAccountId, userAccountId ]
 333       }
 334     ]
 335
 336     return VideoCommentModel
 337       .scope(scopes)
 338       .findAndCountAll(query)
 339       .then(({ rows, count }) => {
 340         return { total: count, data: rows }
 341       })
 342   }
 343
 344   static listThreadParentComments (comment: MCommentId, t: Transaction, order: 'ASC' | 'DESC' = 'ASC'): Bluebird<MCommentOwner[]> {
 345     const query = {
 346       order: [ [ 'createdAt', order ] ] as Order,
 347       where: {
 348         id: {
 349           [Op.in]: Sequelize.literal('(' +
 350             'WITH RECURSIVE children (id, "inReplyToCommentId") AS ( ' +
 351               `SELECT id, "inReplyToCommentId" FROM "videoComment" WHERE id = ${comment.id} ` +
 352               'UNION ' +
 353               'SELECT "parent"."id", "parent"."inReplyToCommentId" FROM "videoComment" "parent" ' +
 354               'INNER JOIN "children" ON "children"."inReplyToCommentId" = "parent"."id"' +
 355             ') ' +
 356             'SELECT id FROM children' +
 357           ')'),
 358           [Op.ne]: comment.id
 359         }
 360       },
 361       transaction: t
 362     }
 363
 364     return VideoCommentModel
 365       .scope([ ScopeNames.WITH_ACCOUNT ])
 366       .findAll(query)
 367   }
 368
 369   static listAndCountByVideoId (videoId: number, start: number, count: number, t?: Transaction, order: 'ASC' | 'DESC' = 'ASC') {
 370     const query = {
 371       order: [ [ 'createdAt', order ] ] as Order,
 372       offset: start,
 373       limit: count,
 374       where: {
 375         videoId
 376       },
 377       transaction: t
 378     }
 379
 380     return VideoCommentModel.findAndCountAll<MComment>(query)
 381   }
 382
 383   static listForFeed (start: number, count: number, videoId?: number): Bluebird<MCommentOwnerVideoFeed[]> {
 384     const query = {
 385       order: [ [ 'createdAt', 'DESC' ] ] as Order,
 386       offset: start,
 387       limit: count,
 388       where: {},
 389       include: [
 390         {
 391           attributes: [ 'name', 'uuid' ],
 392           model: VideoModel.unscoped(),
 393           required: true
 394         }
 395       ]
 396     }
 397
 398     if (videoId) query.where['videoId'] = videoId
 399
 400     return VideoCommentModel
 401       .scope([ ScopeNames.WITH_ACCOUNT ])
 402       .findAll(query)
 403   }
 404
 405   static async getStats () {
 406     const totalLocalVideoComments = await VideoCommentModel.count({
 407       include: [
 408         {
 409           model: AccountModel,
 410           required: true,
 411           include: [
 412             {
 413               model: ActorModel,
 414               required: true,
 415               where: {
 416                 serverId: null
 417               }
 418             }
 419           ]
 420         }
 421       ]
 422     })
 423     const totalVideoComments = await VideoCommentModel.count()
 424
 425     return {
 426       totalLocalVideoComments,
 427       totalVideoComments
 428     }
 429   }
 430
 431   static cleanOldCommentsOf (videoId: number, beforeUpdatedAt: Date) {
 432     const query = {
 433       where: {
 434         updatedAt: {
 435           [Op.lt]: beforeUpdatedAt
 436         },
 437         videoId,
 438         accountId: {
 439           [Op.notIn]: buildLocalAccountIdsIn()
 440         }
 441       }
 442     }
 443
 444     return VideoCommentModel.destroy(query)
 445   }
 446
 447   getCommentStaticPath () {
 448     return this.Video.getWatchStaticPath() + ';threadId=' + this.getThreadId()
 449   }
 450
 451   getThreadId (): number {
 452     return this.originCommentId || this.id
 453   }
 454
 455   isOwned () {
 456     if (!this.Account) {
 457       return false
 458     }
 459
 460     return this.Account.isOwned()
 461   }
 462
 463   isDeleted () {
 464     return this.deletedAt !== null
 465   }
 466
 467   extractMentions () {
 468     let result: string[] = []
 469
 470     const localMention = `@(${actorNameAlphabet}+)`
 471     const remoteMention = `${localMention}@${WEBSERVER.HOST}`
 472
 473     const mentionRegex = this.isOwned()
 474       ? '(?:(?:' + remoteMention + ')|(?:' + localMention + '))' // Include local mentions?
 475       : '(?:' + remoteMention + ')'
 476
 477     const firstMentionRegex = new RegExp(`^${mentionRegex} `, 'g')
 478     const endMentionRegex = new RegExp(` ${mentionRegex}$`, 'g')
 479     const remoteMentionsRegex = new RegExp(' ' + remoteMention + ' ', 'g')
 480
 481     result = result.concat(
 482       regexpCapture(this.text, firstMentionRegex)
 483         .map(([ , username1, username2 ]) => username1 || username2),
 484
 485       regexpCapture(this.text, endMentionRegex)
 486         .map(([ , username1, username2 ]) => username1 || username2),
 487
 488       regexpCapture(this.text, remoteMentionsRegex)
 489         .map(([ , username ]) => username)
 490     )
 491
 492     // Include local mentions
 493     if (this.isOwned()) {
 494       const localMentionsRegex = new RegExp(' ' + localMention + ' ', 'g')
 495
 496       result = result.concat(
 497         regexpCapture(this.text, localMentionsRegex)
 498           .map(([ , username ]) => username)
 499       )
 500     }
 501
 502     return uniq(result)
 503   }
 504
 505   toFormattedJSON (this: MCommentFormattable) {
 506     return {
 507       id: this.id,
 508       url: this.url,
 509       text: this.text,
 510       threadId: this.originCommentId || this.id,
 511       inReplyToCommentId: this.inReplyToCommentId || null,
 512       videoId: this.videoId,
 513       createdAt: this.createdAt,
 514       updatedAt: this.updatedAt,
 515       deletedAt: this.deletedAt,
 516       isDeleted: this.isDeleted(),
 517       totalRepliesFromVideoAuthor: this.get('totalRepliesFromVideoAuthor') || 0,
 518       totalReplies: this.get('totalReplies') || 0,
 519       account: this.Account ? this.Account.toFormattedJSON() : null
 520     } as VideoComment
 521   }
 522
 523   toActivityPubObject (this: MCommentAP, threadParentComments: MCommentOwner[]): VideoCommentObject | ActivityTombstoneObject {
 524     let inReplyTo: string
 525     // New thread, so in AS we reply to the video
 526     if (this.inReplyToCommentId === null) {
 527       inReplyTo = this.Video.url
 528     } else {
 529       inReplyTo = this.InReplyToVideoComment.url
 530     }
 531
 532     if (this.isDeleted()) {
 533       return {
 534         id: this.url,
 535         type: 'Tombstone',
 536         formerType: 'Note',
 537         inReplyTo,
 538         published: this.createdAt.toISOString(),
 539         updated: this.updatedAt.toISOString(),
 540         deleted: this.deletedAt.toISOString()
 541       }
 542     }
 543
 544     const tag: ActivityTagObject[] = []
 545     for (const parentComment of threadParentComments) {
 546       if (!parentComment.Account) continue
 547
 548       const actor = parentComment.Account.Actor
 549
 550       tag.push({
 551         type: 'Mention',
 552         href: actor.url,
 553         name: `@${actor.preferredUsername}@${actor.getHost()}`
 554       })
 555     }
 556
 557     return {
 558       type: 'Note' as 'Note',
 559       id: this.url,
 560       content: this.text,
 561       inReplyTo,
 562       updated: this.updatedAt.toISOString(),
 563       published: this.createdAt.toISOString(),
 564       url: this.url,
 565       attributedTo: this.Account.Actor.url,
 566       tag
 567     }
 568   }
 569 }