server/models/video/video-comment.ts

   1 import { FindOptions, Op, Order, QueryTypes, Sequelize, Transaction } from 'sequelize'
   2 import {
   3   AllowNull,
   4   BelongsTo,
   5   Column,
   6   CreatedAt,
   7   DataType,
   8   ForeignKey,
   9   HasMany,
  10   Is,
  11   Model,
  12   Scopes,
  13   Table,
  14   UpdatedAt
  15 } from 'sequelize-typescript'
  16 import { getServerActor } from '@server/models/application/application'
  17 import { MAccount, MAccountId, MUserAccountId } from '@server/types/models'
  18 import { pick, uniqify } from '@shared/core-utils'
  19 import { AttributesOnly } from '@shared/typescript-utils'
  20 import { ActivityTagObject, ActivityTombstoneObject } from '../../../shared/models/activitypub/objects/common-objects'
  21 import { VideoCommentObject } from '../../../shared/models/activitypub/objects/video-comment-object'
  22 import { VideoComment, VideoCommentAdmin } from '../../../shared/models/videos/comment/video-comment.model'
  23 import { actorNameAlphabet } from '../../helpers/custom-validators/activitypub/actor'
  24 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
  25 import { regexpCapture } from '../../helpers/regexp'
  26 import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants'
  27 import {
  28   MComment,
  29   MCommentAdminFormattable,
  30   MCommentAP,
  31   MCommentFormattable,
  32   MCommentId,
  33   MCommentOwner,
  34   MCommentOwnerReplyVideoLight,
  35   MCommentOwnerVideo,
  36   MCommentOwnerVideoFeed,
  37   MCommentOwnerVideoReply,
  38   MVideoImmutable
  39 } from '../../types/models/video'
  40 import { VideoCommentAbuseModel } from '../abuse/video-comment-abuse'
  41 import { AccountModel } from '../account/account'
  42 import { ActorModel } from '../actor/actor'
  43 import { buildLocalAccountIdsIn, buildSQLAttributes, throwIfNotValid } from '../shared'
  44 import { ListVideoCommentsOptions, VideoCommentListQueryBuilder } from './sql/comment/video-comment-list-query-builder'
  45 import { VideoModel } from './video'
  46 import { VideoChannelModel } from './video-channel'
  47
  48 export enum ScopeNames {
  49   WITH_ACCOUNT = 'WITH_ACCOUNT',
  50   WITH_IN_REPLY_TO = 'WITH_IN_REPLY_TO',
  51   WITH_VIDEO = 'WITH_VIDEO'
  52 }
  53
  54 @Scopes(() => ({
  55   [ScopeNames.WITH_ACCOUNT]: {
  56     include: [
  57       {
  58         model: AccountModel
  59       }
  60     ]
  61   },
  62   [ScopeNames.WITH_IN_REPLY_TO]: {
  63     include: [
  64       {
  65         model: VideoCommentModel,
  66         as: 'InReplyToVideoComment'
  67       }
  68     ]
  69   },
  70   [ScopeNames.WITH_VIDEO]: {
  71     include: [
  72       {
  73         model: VideoModel,
  74         required: true,
  75         include: [
  76           {
  77             model: VideoChannelModel,
  78             required: true,
  79             include: [
  80               {
  81                 model: AccountModel,
  82                 required: true
  83               }
  84             ]
  85           }
  86         ]
  87       }
  88     ]
  89   }
  90 }))
  91 @Table({
  92   tableName: 'videoComment',
  93   indexes: [
  94     {
  95       fields: [ 'videoId' ]
  96     },
  97     {
  98       fields: [ 'videoId', 'originCommentId' ]
  99     },
 100     {
 101       fields: [ 'url' ],
 102       unique: true
 103     },
 104     {
 105       fields: [ 'accountId' ]
 106     },
 107     {
 108       fields: [
 109         { name: 'createdAt', order: 'DESC' }
 110       ]
 111     }
 112   ]
 113 })
 114 export class VideoCommentModel extends Model<Partial<AttributesOnly<VideoCommentModel>>> {
 115   @CreatedAt
 116   createdAt: Date
 117
 118   @UpdatedAt
 119   updatedAt: Date
 120
 121   @AllowNull(true)
 122   @Column(DataType.DATE)
 123   deletedAt: Date
 124
 125   @AllowNull(false)
 126   @Is('VideoCommentUrl', value => throwIfNotValid(value, isActivityPubUrlValid, 'url'))
 127   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.URL.max))
 128   url: string
 129
 130   @AllowNull(false)
 131   @Column(DataType.TEXT)
 132   text: string
 133
 134   @ForeignKey(() => VideoCommentModel)
 135   @Column
 136   originCommentId: number
 137
 138   @BelongsTo(() => VideoCommentModel, {
 139     foreignKey: {
 140       name: 'originCommentId',
 141       allowNull: true
 142     },
 143     as: 'OriginVideoComment',
 144     onDelete: 'CASCADE'
 145   })
 146   OriginVideoComment: VideoCommentModel
 147
 148   @ForeignKey(() => VideoCommentModel)
 149   @Column
 150   inReplyToCommentId: number
 151
 152   @BelongsTo(() => VideoCommentModel, {
 153     foreignKey: {
 154       name: 'inReplyToCommentId',
 155       allowNull: true
 156     },
 157     as: 'InReplyToVideoComment',
 158     onDelete: 'CASCADE'
 159   })
 160   InReplyToVideoComment: VideoCommentModel | null
 161
 162   @ForeignKey(() => VideoModel)
 163   @Column
 164   videoId: number
 165
 166   @BelongsTo(() => VideoModel, {
 167     foreignKey: {
 168       allowNull: false
 169     },
 170     onDelete: 'CASCADE'
 171   })
 172   Video: VideoModel
 173
 174   @ForeignKey(() => AccountModel)
 175   @Column
 176   accountId: number
 177
 178   @BelongsTo(() => AccountModel, {
 179     foreignKey: {
 180       allowNull: true
 181     },
 182     onDelete: 'CASCADE'
 183   })
 184   Account: AccountModel
 185
 186   @HasMany(() => VideoCommentAbuseModel, {
 187     foreignKey: {
 188       name: 'videoCommentId',
 189       allowNull: true
 190     },
 191     onDelete: 'set null'
 192   })
 193   CommentAbuses: VideoCommentAbuseModel[]
 194
 195   // ---------------------------------------------------------------------------
 196
 197   static getSQLAttributes (tableName: string, aliasPrefix = '') {
 198     return buildSQLAttributes({
 199       model: this,
 200       tableName,
 201       aliasPrefix
 202     })
 203   }
 204
 205   // ---------------------------------------------------------------------------
 206
 207   static loadById (id: number, t?: Transaction): Promise<MComment> {
 208     const query: FindOptions = {
 209       where: {
 210         id
 211       }
 212     }
 213
 214     if (t !== undefined) query.transaction = t
 215
 216     return VideoCommentModel.findOne(query)
 217   }
 218
 219   static loadByIdAndPopulateVideoAndAccountAndReply (id: number, t?: Transaction): Promise<MCommentOwnerVideoReply> {
 220     const query: FindOptions = {
 221       where: {
 222         id
 223       }
 224     }
 225
 226     if (t !== undefined) query.transaction = t
 227
 228     return VideoCommentModel
 229       .scope([ ScopeNames.WITH_VIDEO, ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_IN_REPLY_TO ])
 230       .findOne(query)
 231   }
 232
 233   static loadByUrlAndPopulateAccountAndVideo (url: string, t?: Transaction): Promise<MCommentOwnerVideo> {
 234     const query: FindOptions = {
 235       where: {
 236         url
 237       }
 238     }
 239
 240     if (t !== undefined) query.transaction = t
 241
 242     return VideoCommentModel.scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_VIDEO ]).findOne(query)
 243   }
 244
 245   static loadByUrlAndPopulateReplyAndVideoUrlAndAccount (url: string, t?: Transaction): Promise<MCommentOwnerReplyVideoLight> {
 246     const query: FindOptions = {
 247       where: {
 248         url
 249       },
 250       include: [
 251         {
 252           attributes: [ 'id', 'url' ],
 253           model: VideoModel.unscoped()
 254         }
 255       ]
 256     }
 257
 258     if (t !== undefined) query.transaction = t
 259
 260     return VideoCommentModel.scope([ ScopeNames.WITH_IN_REPLY_TO, ScopeNames.WITH_ACCOUNT ]).findOne(query)
 261   }
 262
 263   static listCommentsForApi (parameters: {
 264     start: number
 265     count: number
 266     sort: string
 267
 268     onLocalVideo?: boolean
 269     isLocal?: boolean
 270     search?: string
 271     searchAccount?: string
 272     searchVideo?: string
 273   }) {
 274     const queryOptions: ListVideoCommentsOptions = {
 275       ...pick(parameters, [ 'start', 'count', 'sort', 'isLocal', 'search', 'searchVideo', 'searchAccount', 'onLocalVideo' ]),
 276
 277       selectType: 'api',
 278       notDeleted: true
 279     }
 280
 281     return Promise.all([
 282       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentAdminFormattable>(),
 283       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
 284     ]).then(([ rows, count ]) => {
 285       return { total: count, data: rows }
 286     })
 287   }
 288
 289   static async listThreadsForApi (parameters: {
 290     videoId: number
 291     isVideoOwned: boolean
 292     start: number
 293     count: number
 294     sort: string
 295     user?: MUserAccountId
 296   }) {
 297     const { videoId, user } = parameters
 298
 299     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user })
 300
 301     const commonOptions: ListVideoCommentsOptions = {
 302       selectType: 'api',
 303       videoId,
 304       blockerAccountIds
 305     }
 306
 307     const listOptions: ListVideoCommentsOptions = {
 308       ...commonOptions,
 309       ...pick(parameters, [ 'sort', 'start', 'count' ]),
 310
 311       isThread: true,
 312       includeReplyCounters: true
 313     }
 314
 315     const countOptions: ListVideoCommentsOptions = {
 316       ...commonOptions,
 317
 318       isThread: true
 319     }
 320
 321     const notDeletedCountOptions: ListVideoCommentsOptions = {
 322       ...commonOptions,
 323
 324       notDeleted: true
 325     }
 326
 327     return Promise.all([
 328       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, listOptions).listComments<MCommentAdminFormattable>(),
 329       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, countOptions).countComments(),
 330       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, notDeletedCountOptions).countComments()
 331     ]).then(([ rows, count, totalNotDeletedComments ]) => {
 332       return { total: count, data: rows, totalNotDeletedComments }
 333     })
 334   }
 335
 336   static async listThreadCommentsForApi (parameters: {
 337     videoId: number
 338     threadId: number
 339     user?: MUserAccountId
 340   }) {
 341     const { user } = parameters
 342
 343     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user })
 344
 345     const queryOptions: ListVideoCommentsOptions = {
 346       ...pick(parameters, [ 'videoId', 'threadId' ]),
 347
 348       selectType: 'api',
 349       sort: 'createdAt',
 350
 351       blockerAccountIds,
 352       includeReplyCounters: true
 353     }
 354
 355     return Promise.all([
 356       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentAdminFormattable>(),
 357       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
 358     ]).then(([ rows, count ]) => {
 359       return { total: count, data: rows }
 360     })
 361   }
 362
 363   static listThreadParentComments (comment: MCommentId, t: Transaction, order: 'ASC' | 'DESC' = 'ASC'): Promise<MCommentOwner[]> {
 364     const query = {
 365       order: [ [ 'createdAt', order ] ] as Order,
 366       where: {
 367         id: {
 368           [Op.in]: Sequelize.literal('(' +
 369             'WITH RECURSIVE children (id, "inReplyToCommentId") AS ( ' +
 370               `SELECT id, "inReplyToCommentId" FROM "videoComment" WHERE id = ${comment.id} ` +
 371               'UNION ' +
 372               'SELECT "parent"."id", "parent"."inReplyToCommentId" FROM "videoComment" "parent" ' +
 373               'INNER JOIN "children" ON "children"."inReplyToCommentId" = "parent"."id"' +
 374             ') ' +
 375             'SELECT id FROM children' +
 376           ')'),
 377           [Op.ne]: comment.id
 378         }
 379       },
 380       transaction: t
 381     }
 382
 383     return VideoCommentModel
 384       .scope([ ScopeNames.WITH_ACCOUNT ])
 385       .findAll(query)
 386   }
 387
 388   static async listAndCountByVideoForAP (parameters: {
 389     video: MVideoImmutable
 390     start: number
 391     count: number
 392   }) {
 393     const { video } = parameters
 394
 395     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user: null })
 396
 397     const queryOptions: ListVideoCommentsOptions = {
 398       ...pick(parameters, [ 'start', 'count' ]),
 399
 400       selectType: 'comment-only',
 401       videoId: video.id,
 402       sort: 'createdAt',
 403
 404       blockerAccountIds
 405     }
 406
 407     return Promise.all([
 408       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MComment>(),
 409       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
 410     ]).then(([ rows, count ]) => {
 411       return { total: count, data: rows }
 412     })
 413   }
 414
 415   static async listForFeed (parameters: {
 416     start: number
 417     count: number
 418     videoId?: number
 419     accountId?: number
 420     videoChannelId?: number
 421   }) {
 422     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user: null })
 423
 424     const queryOptions: ListVideoCommentsOptions = {
 425       ...pick(parameters, [ 'start', 'count', 'accountId', 'videoId', 'videoChannelId' ]),
 426
 427       selectType: 'feed',
 428
 429       sort: '-createdAt',
 430       onPublicVideo: true,
 431       notDeleted: true,
 432
 433       blockerAccountIds
 434     }
 435
 436     return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentOwnerVideoFeed>()
 437   }
 438
 439   static listForBulkDelete (ofAccount: MAccount, filter: { onVideosOfAccount?: MAccountId } = {}) {
 440     const queryOptions: ListVideoCommentsOptions = {
 441       selectType: 'comment-only',
 442
 443       accountId: ofAccount.id,
 444       videoAccountOwnerId: filter.onVideosOfAccount?.id,
 445
 446       notDeleted: true,
 447       count: 5000
 448     }
 449
 450     return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MComment>()
 451   }
 452
 453   static async getStats () {
 454     const totalLocalVideoComments = await VideoCommentModel.count({
 455       include: [
 456         {
 457           model: AccountModel.unscoped(),
 458           required: true,
 459           include: [
 460             {
 461               model: ActorModel.unscoped(),
 462               required: true,
 463               where: {
 464                 serverId: null
 465               }
 466             }
 467           ]
 468         }
 469       ]
 470     })
 471     const totalVideoComments = await VideoCommentModel.count()
 472
 473     return {
 474       totalLocalVideoComments,
 475       totalVideoComments
 476     }
 477   }
 478
 479   static listRemoteCommentUrlsOfLocalVideos () {
 480     const query = `SELECT "videoComment".url FROM "videoComment" ` +
 481       `INNER JOIN account ON account.id = "videoComment"."accountId" ` +
 482       `INNER JOIN actor ON actor.id = "account"."actorId" AND actor."serverId" IS NOT NULL ` +
 483       `INNER JOIN video ON video.id = "videoComment"."videoId" AND video.remote IS FALSE`
 484
 485     return VideoCommentModel.sequelize.query<{ url: string }>(query, {
 486       type: QueryTypes.SELECT,
 487       raw: true
 488     }).then(rows => rows.map(r => r.url))
 489   }
 490
 491   static cleanOldCommentsOf (videoId: number, beforeUpdatedAt: Date) {
 492     const query = {
 493       where: {
 494         updatedAt: {
 495           [Op.lt]: beforeUpdatedAt
 496         },
 497         videoId,
 498         accountId: {
 499           [Op.notIn]: buildLocalAccountIdsIn()
 500         },
 501         // Do not delete Tombstones
 502         deletedAt: null
 503       }
 504     }
 505
 506     return VideoCommentModel.destroy(query)
 507   }
 508
 509   getCommentStaticPath () {
 510     return this.Video.getWatchStaticPath() + ';threadId=' + this.getThreadId()
 511   }
 512
 513   getThreadId (): number {
 514     return this.originCommentId || this.id
 515   }
 516
 517   isOwned () {
 518     if (!this.Account) return false
 519
 520     return this.Account.isOwned()
 521   }
 522
 523   markAsDeleted () {
 524     this.text = ''
 525     this.deletedAt = new Date()
 526     this.accountId = null
 527   }
 528
 529   isDeleted () {
 530     return this.deletedAt !== null
 531   }
 532
 533   extractMentions () {
 534     let result: string[] = []
 535
 536     const localMention = `@(${actorNameAlphabet}+)`
 537     const remoteMention = `${localMention}@${WEBSERVER.HOST}`
 538
 539     const mentionRegex = this.isOwned()
 540       ? '(?:(?:' + remoteMention + ')|(?:' + localMention + '))' // Include local mentions?
 541       : '(?:' + remoteMention + ')'
 542
 543     const firstMentionRegex = new RegExp(`^${mentionRegex} `, 'g')
 544     const endMentionRegex = new RegExp(` ${mentionRegex}$`, 'g')
 545     const remoteMentionsRegex = new RegExp(' ' + remoteMention + ' ', 'g')
 546
 547     result = result.concat(
 548       regexpCapture(this.text, firstMentionRegex)
 549         .map(([ , username1, username2 ]) => username1 || username2),
 550
 551       regexpCapture(this.text, endMentionRegex)
 552         .map(([ , username1, username2 ]) => username1 || username2),
 553
 554       regexpCapture(this.text, remoteMentionsRegex)
 555         .map(([ , username ]) => username)
 556     )
 557
 558     // Include local mentions
 559     if (this.isOwned()) {
 560       const localMentionsRegex = new RegExp(' ' + localMention + ' ', 'g')
 561
 562       result = result.concat(
 563         regexpCapture(this.text, localMentionsRegex)
 564           .map(([ , username ]) => username)
 565       )
 566     }
 567
 568     return uniqify(result)
 569   }
 570
 571   toFormattedJSON (this: MCommentFormattable) {
 572     return {
 573       id: this.id,
 574       url: this.url,
 575       text: this.text,
 576
 577       threadId: this.getThreadId(),
 578       inReplyToCommentId: this.inReplyToCommentId || null,
 579       videoId: this.videoId,
 580
 581       createdAt: this.createdAt,
 582       updatedAt: this.updatedAt,
 583       deletedAt: this.deletedAt,
 584
 585       isDeleted: this.isDeleted(),
 586
 587       totalRepliesFromVideoAuthor: this.get('totalRepliesFromVideoAuthor') || 0,
 588       totalReplies: this.get('totalReplies') || 0,
 589
 590       account: this.Account
 591         ? this.Account.toFormattedJSON()
 592         : null
 593     } as VideoComment
 594   }
 595
 596   toFormattedAdminJSON (this: MCommentAdminFormattable) {
 597     return {
 598       id: this.id,
 599       url: this.url,
 600       text: this.text,
 601
 602       threadId: this.getThreadId(),
 603       inReplyToCommentId: this.inReplyToCommentId || null,
 604       videoId: this.videoId,
 605
 606       createdAt: this.createdAt,
 607       updatedAt: this.updatedAt,
 608
 609       video: {
 610         id: this.Video.id,
 611         uuid: this.Video.uuid,
 612         name: this.Video.name
 613       },
 614
 615       account: this.Account
 616         ? this.Account.toFormattedJSON()
 617         : null
 618     } as VideoCommentAdmin
 619   }
 620
 621   toActivityPubObject (this: MCommentAP, threadParentComments: MCommentOwner[]): VideoCommentObject | ActivityTombstoneObject {
 622     let inReplyTo: string
 623     // New thread, so in AS we reply to the video
 624     if (this.inReplyToCommentId === null) {
 625       inReplyTo = this.Video.url
 626     } else {
 627       inReplyTo = this.InReplyToVideoComment.url
 628     }
 629
 630     if (this.isDeleted()) {
 631       return {
 632         id: this.url,
 633         type: 'Tombstone',
 634         formerType: 'Note',
 635         inReplyTo,
 636         published: this.createdAt.toISOString(),
 637         updated: this.updatedAt.toISOString(),
 638         deleted: this.deletedAt.toISOString()
 639       }
 640     }
 641
 642     const tag: ActivityTagObject[] = []
 643     for (const parentComment of threadParentComments) {
 644       if (!parentComment.Account) continue
 645
 646       const actor = parentComment.Account.Actor
 647
 648       tag.push({
 649         type: 'Mention',
 650         href: actor.url,
 651         name: `@${actor.preferredUsername}@${actor.getHost()}`
 652       })
 653     }
 654
 655     return {
 656       type: 'Note' as 'Note',
 657       id: this.url,
 658
 659       content: this.text,
 660       mediaType: 'text/markdown',
 661
 662       inReplyTo,
 663       updated: this.updatedAt.toISOString(),
 664       published: this.createdAt.toISOString(),
 665       url: this.url,
 666       attributedTo: this.Account.Actor.url,
 667       tag
 668     }
 669   }
 670
 671   private static async buildBlockerAccountIds (options: {
 672     user: MUserAccountId
 673   }): Promise<number[]> {
 674     const { user } = options
 675
 676     const serverActor = await getServerActor()
 677     const blockerAccountIds = [ serverActor.Account.id ]
 678
 679     if (user) blockerAccountIds.push(user.Account.id)
 680
 681     return blockerAccountIds
 682   }
 683 }