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, throwIfNotValid } from '../utils'
  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   static loadById (id: number, t?: Transaction): Promise<MComment> {
 196     const query: FindOptions = {
 197       where: {
 198         id
 199       }
 200     }
 201
 202     if (t !== undefined) query.transaction = t
 203
 204     return VideoCommentModel.findOne(query)
 205   }
 206
 207   static loadByIdAndPopulateVideoAndAccountAndReply (id: number, t?: Transaction): Promise<MCommentOwnerVideoReply> {
 208     const query: FindOptions = {
 209       where: {
 210         id
 211       }
 212     }
 213
 214     if (t !== undefined) query.transaction = t
 215
 216     return VideoCommentModel
 217       .scope([ ScopeNames.WITH_VIDEO, ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_IN_REPLY_TO ])
 218       .findOne(query)
 219   }
 220
 221   static loadByUrlAndPopulateAccountAndVideo (url: string, t?: Transaction): Promise<MCommentOwnerVideo> {
 222     const query: FindOptions = {
 223       where: {
 224         url
 225       }
 226     }
 227
 228     if (t !== undefined) query.transaction = t
 229
 230     return VideoCommentModel.scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_VIDEO ]).findOne(query)
 231   }
 232
 233   static loadByUrlAndPopulateReplyAndVideoUrlAndAccount (url: string, t?: Transaction): Promise<MCommentOwnerReplyVideoLight> {
 234     const query: FindOptions = {
 235       where: {
 236         url
 237       },
 238       include: [
 239         {
 240           attributes: [ 'id', 'url' ],
 241           model: VideoModel.unscoped()
 242         }
 243       ]
 244     }
 245
 246     if (t !== undefined) query.transaction = t
 247
 248     return VideoCommentModel.scope([ ScopeNames.WITH_IN_REPLY_TO, ScopeNames.WITH_ACCOUNT ]).findOne(query)
 249   }
 250
 251   static listCommentsForApi (parameters: {
 252     start: number
 253     count: number
 254     sort: string
 255
 256     onLocalVideo?: boolean
 257     isLocal?: boolean
 258     search?: string
 259     searchAccount?: string
 260     searchVideo?: string
 261   }) {
 262     const queryOptions: ListVideoCommentsOptions = {
 263       ...pick(parameters, [ 'start', 'count', 'sort', 'isLocal', 'search', 'searchVideo', 'searchAccount', 'onLocalVideo' ]),
 264
 265       selectType: 'api',
 266       notDeleted: true
 267     }
 268
 269     return Promise.all([
 270       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentAdminFormattable>(),
 271       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
 272     ]).then(([ rows, count ]) => {
 273       return { total: count, data: rows }
 274     })
 275   }
 276
 277   static async listThreadsForApi (parameters: {
 278     videoId: number
 279     isVideoOwned: boolean
 280     start: number
 281     count: number
 282     sort: string
 283     user?: MUserAccountId
 284   }) {
 285     const { videoId, user } = parameters
 286
 287     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user })
 288
 289     const commonOptions: ListVideoCommentsOptions = {
 290       selectType: 'api',
 291       videoId,
 292       blockerAccountIds
 293     }
 294
 295     const listOptions: ListVideoCommentsOptions = {
 296       ...commonOptions,
 297       ...pick(parameters, [ 'sort', 'start', 'count' ]),
 298
 299       isThread: true,
 300       includeReplyCounters: true
 301     }
 302
 303     const countOptions: ListVideoCommentsOptions = {
 304       ...commonOptions,
 305
 306       isThread: true
 307     }
 308
 309     const notDeletedCountOptions: ListVideoCommentsOptions = {
 310       ...commonOptions,
 311
 312       notDeleted: true
 313     }
 314
 315     return Promise.all([
 316       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, listOptions).listComments<MCommentAdminFormattable>(),
 317       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, countOptions).countComments(),
 318       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, notDeletedCountOptions).countComments()
 319     ]).then(([ rows, count, totalNotDeletedComments ]) => {
 320       return { total: count, data: rows, totalNotDeletedComments }
 321     })
 322   }
 323
 324   static async listThreadCommentsForApi (parameters: {
 325     videoId: number
 326     threadId: number
 327     user?: MUserAccountId
 328   }) {
 329     const { user } = parameters
 330
 331     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user })
 332
 333     const queryOptions: ListVideoCommentsOptions = {
 334       ...pick(parameters, [ 'videoId', 'threadId' ]),
 335
 336       selectType: 'api',
 337       sort: 'createdAt',
 338
 339       blockerAccountIds,
 340       includeReplyCounters: true
 341     }
 342
 343     return Promise.all([
 344       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentAdminFormattable>(),
 345       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
 346     ]).then(([ rows, count ]) => {
 347       return { total: count, data: rows }
 348     })
 349   }
 350
 351   static listThreadParentComments (comment: MCommentId, t: Transaction, order: 'ASC' | 'DESC' = 'ASC'): Promise<MCommentOwner[]> {
 352     const query = {
 353       order: [ [ 'createdAt', order ] ] as Order,
 354       where: {
 355         id: {
 356           [Op.in]: Sequelize.literal('(' +
 357             'WITH RECURSIVE children (id, "inReplyToCommentId") AS ( ' +
 358               `SELECT id, "inReplyToCommentId" FROM "videoComment" WHERE id = ${comment.id} ` +
 359               'UNION ' +
 360               'SELECT "parent"."id", "parent"."inReplyToCommentId" FROM "videoComment" "parent" ' +
 361               'INNER JOIN "children" ON "children"."inReplyToCommentId" = "parent"."id"' +
 362             ') ' +
 363             'SELECT id FROM children' +
 364           ')'),
 365           [Op.ne]: comment.id
 366         }
 367       },
 368       transaction: t
 369     }
 370
 371     return VideoCommentModel
 372       .scope([ ScopeNames.WITH_ACCOUNT ])
 373       .findAll(query)
 374   }
 375
 376   static async listAndCountByVideoForAP (parameters: {
 377     video: MVideoImmutable
 378     start: number
 379     count: number
 380   }) {
 381     const { video } = parameters
 382
 383     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user: null })
 384
 385     const queryOptions: ListVideoCommentsOptions = {
 386       ...pick(parameters, [ 'start', 'count' ]),
 387
 388       selectType: 'comment-only',
 389       videoId: video.id,
 390       sort: 'createdAt',
 391
 392       blockerAccountIds
 393     }
 394
 395     return Promise.all([
 396       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MComment>(),
 397       new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments()
 398     ]).then(([ rows, count ]) => {
 399       return { total: count, data: rows }
 400     })
 401   }
 402
 403   static async listForFeed (parameters: {
 404     start: number
 405     count: number
 406     videoId?: number
 407     accountId?: number
 408     videoChannelId?: number
 409   }) {
 410     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user: null })
 411
 412     const queryOptions: ListVideoCommentsOptions = {
 413       ...pick(parameters, [ 'start', 'count', 'accountId', 'videoId', 'videoChannelId' ]),
 414
 415       selectType: 'feed',
 416
 417       sort: '-createdAt',
 418       onPublicVideo: true,
 419       notDeleted: true,
 420
 421       blockerAccountIds
 422     }
 423
 424     return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MCommentOwnerVideoFeed>()
 425   }
 426
 427   static listForBulkDelete (ofAccount: MAccount, filter: { onVideosOfAccount?: MAccountId } = {}) {
 428     const queryOptions: ListVideoCommentsOptions = {
 429       selectType: 'comment-only',
 430
 431       accountId: ofAccount.id,
 432       videoAccountOwnerId: filter.onVideosOfAccount?.id,
 433
 434       notDeleted: true,
 435       count: 5000
 436     }
 437
 438     return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments<MComment>()
 439   }
 440
 441   static async getStats () {
 442     const totalLocalVideoComments = await VideoCommentModel.count({
 443       include: [
 444         {
 445           model: AccountModel.unscoped(),
 446           required: true,
 447           include: [
 448             {
 449               model: ActorModel.unscoped(),
 450               required: true,
 451               where: {
 452                 serverId: null
 453               }
 454             }
 455           ]
 456         }
 457       ]
 458     })
 459     const totalVideoComments = await VideoCommentModel.count()
 460
 461     return {
 462       totalLocalVideoComments,
 463       totalVideoComments
 464     }
 465   }
 466
 467   static listRemoteCommentUrlsOfLocalVideos () {
 468     const query = `SELECT "videoComment".url FROM "videoComment" ` +
 469       `INNER JOIN account ON account.id = "videoComment"."accountId" ` +
 470       `INNER JOIN actor ON actor.id = "account"."actorId" AND actor."serverId" IS NOT NULL ` +
 471       `INNER JOIN video ON video.id = "videoComment"."videoId" AND video.remote IS FALSE`
 472
 473     return VideoCommentModel.sequelize.query<{ url: string }>(query, {
 474       type: QueryTypes.SELECT,
 475       raw: true
 476     }).then(rows => rows.map(r => r.url))
 477   }
 478
 479   static cleanOldCommentsOf (videoId: number, beforeUpdatedAt: Date) {
 480     const query = {
 481       where: {
 482         updatedAt: {
 483           [Op.lt]: beforeUpdatedAt
 484         },
 485         videoId,
 486         accountId: {
 487           [Op.notIn]: buildLocalAccountIdsIn()
 488         },
 489         // Do not delete Tombstones
 490         deletedAt: null
 491       }
 492     }
 493
 494     return VideoCommentModel.destroy(query)
 495   }
 496
 497   getCommentStaticPath () {
 498     return this.Video.getWatchStaticPath() + ';threadId=' + this.getThreadId()
 499   }
 500
 501   getThreadId (): number {
 502     return this.originCommentId || this.id
 503   }
 504
 505   isOwned () {
 506     if (!this.Account) return false
 507
 508     return this.Account.isOwned()
 509   }
 510
 511   markAsDeleted () {
 512     this.text = ''
 513     this.deletedAt = new Date()
 514     this.accountId = null
 515   }
 516
 517   isDeleted () {
 518     return this.deletedAt !== null
 519   }
 520
 521   extractMentions () {
 522     let result: string[] = []
 523
 524     const localMention = `@(${actorNameAlphabet}+)`
 525     const remoteMention = `${localMention}@${WEBSERVER.HOST}`
 526
 527     const mentionRegex = this.isOwned()
 528       ? '(?:(?:' + remoteMention + ')|(?:' + localMention + '))' // Include local mentions?
 529       : '(?:' + remoteMention + ')'
 530
 531     const firstMentionRegex = new RegExp(`^${mentionRegex} `, 'g')
 532     const endMentionRegex = new RegExp(` ${mentionRegex}$`, 'g')
 533     const remoteMentionsRegex = new RegExp(' ' + remoteMention + ' ', 'g')
 534
 535     result = result.concat(
 536       regexpCapture(this.text, firstMentionRegex)
 537         .map(([ , username1, username2 ]) => username1 || username2),
 538
 539       regexpCapture(this.text, endMentionRegex)
 540         .map(([ , username1, username2 ]) => username1 || username2),
 541
 542       regexpCapture(this.text, remoteMentionsRegex)
 543         .map(([ , username ]) => username)
 544     )
 545
 546     // Include local mentions
 547     if (this.isOwned()) {
 548       const localMentionsRegex = new RegExp(' ' + localMention + ' ', 'g')
 549
 550       result = result.concat(
 551         regexpCapture(this.text, localMentionsRegex)
 552           .map(([ , username ]) => username)
 553       )
 554     }
 555
 556     return uniqify(result)
 557   }
 558
 559   toFormattedJSON (this: MCommentFormattable) {
 560     return {
 561       id: this.id,
 562       url: this.url,
 563       text: this.text,
 564
 565       threadId: this.getThreadId(),
 566       inReplyToCommentId: this.inReplyToCommentId || null,
 567       videoId: this.videoId,
 568
 569       createdAt: this.createdAt,
 570       updatedAt: this.updatedAt,
 571       deletedAt: this.deletedAt,
 572
 573       isDeleted: this.isDeleted(),
 574
 575       totalRepliesFromVideoAuthor: this.get('totalRepliesFromVideoAuthor') || 0,
 576       totalReplies: this.get('totalReplies') || 0,
 577
 578       account: this.Account
 579         ? this.Account.toFormattedJSON()
 580         : null
 581     } as VideoComment
 582   }
 583
 584   toFormattedAdminJSON (this: MCommentAdminFormattable) {
 585     return {
 586       id: this.id,
 587       url: this.url,
 588       text: this.text,
 589
 590       threadId: this.getThreadId(),
 591       inReplyToCommentId: this.inReplyToCommentId || null,
 592       videoId: this.videoId,
 593
 594       createdAt: this.createdAt,
 595       updatedAt: this.updatedAt,
 596
 597       video: {
 598         id: this.Video.id,
 599         uuid: this.Video.uuid,
 600         name: this.Video.name
 601       },
 602
 603       account: this.Account
 604         ? this.Account.toFormattedJSON()
 605         : null
 606     } as VideoCommentAdmin
 607   }
 608
 609   toActivityPubObject (this: MCommentAP, threadParentComments: MCommentOwner[]): VideoCommentObject | ActivityTombstoneObject {
 610     let inReplyTo: string
 611     // New thread, so in AS we reply to the video
 612     if (this.inReplyToCommentId === null) {
 613       inReplyTo = this.Video.url
 614     } else {
 615       inReplyTo = this.InReplyToVideoComment.url
 616     }
 617
 618     if (this.isDeleted()) {
 619       return {
 620         id: this.url,
 621         type: 'Tombstone',
 622         formerType: 'Note',
 623         inReplyTo,
 624         published: this.createdAt.toISOString(),
 625         updated: this.updatedAt.toISOString(),
 626         deleted: this.deletedAt.toISOString()
 627       }
 628     }
 629
 630     const tag: ActivityTagObject[] = []
 631     for (const parentComment of threadParentComments) {
 632       if (!parentComment.Account) continue
 633
 634       const actor = parentComment.Account.Actor
 635
 636       tag.push({
 637         type: 'Mention',
 638         href: actor.url,
 639         name: `@${actor.preferredUsername}@${actor.getHost()}`
 640       })
 641     }
 642
 643     return {
 644       type: 'Note' as 'Note',
 645       id: this.url,
 646
 647       content: this.text,
 648       mediaType: 'text/markdown',
 649
 650       inReplyTo,
 651       updated: this.updatedAt.toISOString(),
 652       published: this.createdAt.toISOString(),
 653       url: this.url,
 654       attributedTo: this.Account.Actor.url,
 655       tag
 656     }
 657   }
 658
 659   private static async buildBlockerAccountIds (options: {
 660     user: MUserAccountId
 661   }): Promise<number[]> {
 662     const { user } = options
 663
 664     const serverActor = await getServerActor()
 665     const blockerAccountIds = [ serverActor.Account.id ]
 666
 667     if (user) blockerAccountIds.push(user.Account.id)
 668
 669     return blockerAccountIds
 670   }
 671 }