aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video/video.ts
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2021-06-10 08:53:32 +0200
committerChocobozzz <me@florianbigard.com>2021-06-10 09:22:58 +0200
commite5dbd5084e7ae91ce118c0bccd5b84c47b88c55f (patch)
treee7ae22528a0cf5b181f7cefbf867e641e9cacef9 /server/models/video/video.ts
parentff0ea0cd8e3b0ecad445672deb75b193babeddc2 (diff)
downloadPeerTube-e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f.tar.gz
PeerTube-e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f.tar.zst
PeerTube-e5dbd5084e7ae91ce118c0bccd5b84c47b88c55f.zip
Refactor video query builder
Diffstat (limited to 'server/models/video/video.ts')
-rw-r--r--server/models/video/video.ts187
1 files changed, 19 insertions, 168 deletions
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 44aaa24ef..4979cee50 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -1,6 +1,6 @@
1import * as Bluebird from 'bluebird' 1import * as Bluebird from 'bluebird'
2import { remove } from 'fs-extra' 2import { remove } from 'fs-extra'
3import { maxBy, minBy, pick } from 'lodash' 3import { maxBy, minBy } from 'lodash'
4import { join } from 'path' 4import { join } from 'path'
5import { FindOptions, Includeable, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' 5import { FindOptions, Includeable, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
6import { 6import {
@@ -110,7 +110,16 @@ import { VideoTrackerModel } from '../server/video-tracker'
110import { UserModel } from '../user/user' 110import { UserModel } from '../user/user'
111import { UserVideoHistoryModel } from '../user/user-video-history' 111import { UserVideoHistoryModel } from '../user/user-video-history'
112import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils' 112import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils'
113import {
114 videoFilesModelToFormattedJSON,
115 VideoFormattingJSONOptions,
116 videoModelToActivityPubObject,
117 videoModelToFormattedDetailsJSON,
118 videoModelToFormattedJSON
119} from './formatter/video-format-utils'
113import { ScheduleVideoUpdateModel } from './schedule-video-update' 120import { ScheduleVideoUpdateModel } from './schedule-video-update'
121import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './sql/videos-id-list-query-builder'
122import { VideosModelListQueryBuilder } from './sql/videos-model-list-query-builder'
114import { TagModel } from './tag' 123import { TagModel } from './tag'
115import { ThumbnailModel } from './thumbnail' 124import { ThumbnailModel } from './thumbnail'
116import { VideoBlacklistModel } from './video-blacklist' 125import { VideoBlacklistModel } from './video-blacklist'
@@ -118,17 +127,9 @@ import { VideoCaptionModel } from './video-caption'
118import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel' 127import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
119import { VideoCommentModel } from './video-comment' 128import { VideoCommentModel } from './video-comment'
120import { VideoFileModel } from './video-file' 129import { VideoFileModel } from './video-file'
121import {
122 videoFilesModelToFormattedJSON,
123 VideoFormattingJSONOptions,
124 videoModelToActivityPubObject,
125 videoModelToFormattedDetailsJSON,
126 videoModelToFormattedJSON
127} from './video-format-utils'
128import { VideoImportModel } from './video-import' 130import { VideoImportModel } from './video-import'
129import { VideoLiveModel } from './video-live' 131import { VideoLiveModel } from './video-live'
130import { VideoPlaylistElementModel } from './video-playlist-element' 132import { VideoPlaylistElementModel } from './video-playlist-element'
131import { buildListQuery, BuildVideosQueryOptions, wrapForAPIResults } from './video-query-builder'
132import { VideoShareModel } from './video-share' 133import { VideoShareModel } from './video-share'
133import { VideoStreamingPlaylistModel } from './video-streaming-playlist' 134import { VideoStreamingPlaylistModel } from './video-streaming-playlist'
134import { VideoTagModel } from './video-tag' 135import { VideoTagModel } from './video-tag'
@@ -1607,7 +1608,7 @@ export class VideoModel extends Model<Partial<AttributesOnly<VideoModel>>> {
1607 const serverActor = await getServerActor() 1608 const serverActor = await getServerActor()
1608 const followerActorId = serverActor.id 1609 const followerActorId = serverActor.id
1609 1610
1610 const queryOptions: BuildVideosQueryOptions = { 1611 const queryOptions: BuildVideosListQueryOptions = {
1611 attributes: [ `"${field}"` ], 1612 attributes: [ `"${field}"` ],
1612 group: `GROUP BY "${field}"`, 1613 group: `GROUP BY "${field}"`,
1613 having: `HAVING COUNT("${field}") >= ${threshold}`, 1614 having: `HAVING COUNT("${field}") >= ${threshold}`,
@@ -1619,10 +1620,10 @@ export class VideoModel extends Model<Partial<AttributesOnly<VideoModel>>> {
1619 includeLocalVideos: true 1620 includeLocalVideos: true
1620 } 1621 }
1621 1622
1622 const { query, replacements } = buildListQuery(VideoModel.sequelize, queryOptions) 1623 const queryBuilder = new VideosIdListQueryBuilder(VideoModel.sequelize)
1623 1624
1624 return this.sequelize.query<any>(query, { replacements, type: QueryTypes.SELECT }) 1625 return queryBuilder.queryVideoIds(queryOptions)
1625 .then(rows => rows.map(r => r[field])) 1626 .then(rows => rows.map(r => r[field]))
1626 } 1627 }
1627 1628
1628 static buildTrendingQuery (trendingDays: number) { 1629 static buildTrendingQuery (trendingDays: number) {
@@ -1640,27 +1641,24 @@ export class VideoModel extends Model<Partial<AttributesOnly<VideoModel>>> {
1640 } 1641 }
1641 1642
1642 private static async getAvailableForApi ( 1643 private static async getAvailableForApi (
1643 options: BuildVideosQueryOptions, 1644 options: BuildVideosListQueryOptions,
1644 countVideos = true 1645 countVideos = true
1645 ): Promise<ResultList<VideoModel>> { 1646 ): Promise<ResultList<VideoModel>> {
1646 function getCount () { 1647 function getCount () {
1647 if (countVideos !== true) return Promise.resolve(undefined) 1648 if (countVideos !== true) return Promise.resolve(undefined)
1648 1649
1649 const countOptions = Object.assign({}, options, { isCount: true }) 1650 const countOptions = Object.assign({}, options, { isCount: true })
1650 const { query: queryCount, replacements: replacementsCount } = buildListQuery(VideoModel.sequelize, countOptions) 1651 const queryBuilder = new VideosIdListQueryBuilder(VideoModel.sequelize)
1651 1652
1652 return VideoModel.sequelize.query<any>(queryCount, { replacements: replacementsCount, type: QueryTypes.SELECT }) 1653 return queryBuilder.countVideoIds(countOptions)
1653 .then(rows => rows.length !== 0 ? rows[0].total : 0)
1654 } 1654 }
1655 1655
1656 function getModels () { 1656 function getModels () {
1657 if (options.count === 0) return Promise.resolve([]) 1657 if (options.count === 0) return Promise.resolve([])
1658 1658
1659 const { query, replacements, order } = buildListQuery(VideoModel.sequelize, options) 1659 const queryBuilder = new VideosModelListQueryBuilder(VideoModel.sequelize)
1660 const queryModels = wrapForAPIResults(query, replacements, options, order)
1661 1660
1662 return VideoModel.sequelize.query<any>(queryModels, { replacements, type: QueryTypes.SELECT, nest: true }) 1661 return queryBuilder.queryVideos(options)
1663 .then(rows => VideoModel.buildAPIResult(rows))
1664 } 1662 }
1665 1663
1666 const [ count, rows ] = await Promise.all([ getCount(), getModels() ]) 1664 const [ count, rows ] = await Promise.all([ getCount(), getModels() ])
@@ -1671,153 +1669,6 @@ export class VideoModel extends Model<Partial<AttributesOnly<VideoModel>>> {
1671 } 1669 }
1672 } 1670 }
1673 1671
1674 private static buildAPIResult (rows: any[]) {
1675 const videosMemo: { [ id: number ]: VideoModel } = {}
1676 const videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel } = {}
1677
1678 const thumbnailsDone = new Set<number>()
1679 const historyDone = new Set<number>()
1680 const videoFilesDone = new Set<number>()
1681
1682 const videos: VideoModel[] = []
1683
1684 const avatarKeys = [ 'id', 'filename', 'fileUrl', 'onDisk', 'createdAt', 'updatedAt' ]
1685 const actorKeys = [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ]
1686 const serverKeys = [ 'id', 'host' ]
1687 const videoFileKeys = [
1688 'id',
1689 'createdAt',
1690 'updatedAt',
1691 'resolution',
1692 'size',
1693 'extname',
1694 'filename',
1695 'fileUrl',
1696 'torrentFilename',
1697 'torrentUrl',
1698 'infoHash',
1699 'fps',
1700 'videoId',
1701 'videoStreamingPlaylistId'
1702 ]
1703 const videoStreamingPlaylistKeys = [ 'id', 'type', 'playlistUrl' ]
1704 const videoKeys = [
1705 'id',
1706 'uuid',
1707 'name',
1708 'category',
1709 'licence',
1710 'language',
1711 'privacy',
1712 'nsfw',
1713 'description',
1714 'support',
1715 'duration',
1716 'views',
1717 'likes',
1718 'dislikes',
1719 'remote',
1720 'isLive',
1721 'url',
1722 'commentsEnabled',
1723 'downloadEnabled',
1724 'waitTranscoding',
1725 'state',
1726 'publishedAt',
1727 'originallyPublishedAt',
1728 'channelId',
1729 'createdAt',
1730 'updatedAt'
1731 ]
1732 const buildOpts = { raw: true }
1733
1734 function buildActor (rowActor: any) {
1735 const avatarModel = rowActor.Avatar.id !== null
1736 ? new ActorImageModel(pick(rowActor.Avatar, avatarKeys), buildOpts)
1737 : null
1738
1739 const serverModel = rowActor.Server.id !== null
1740 ? new ServerModel(pick(rowActor.Server, serverKeys), buildOpts)
1741 : null
1742
1743 const actorModel = new ActorModel(pick(rowActor, actorKeys), buildOpts)
1744 actorModel.Avatar = avatarModel
1745 actorModel.Server = serverModel
1746
1747 return actorModel
1748 }
1749
1750 for (const row of rows) {
1751 if (!videosMemo[row.id]) {
1752 // Build Channel
1753 const channel = row.VideoChannel
1754 const channelModel = new VideoChannelModel(pick(channel, [ 'id', 'name', 'description', 'actorId' ]), buildOpts)
1755 channelModel.Actor = buildActor(channel.Actor)
1756
1757 const account = row.VideoChannel.Account
1758 const accountModel = new AccountModel(pick(account, [ 'id', 'name' ]), buildOpts)
1759 accountModel.Actor = buildActor(account.Actor)
1760
1761 channelModel.Account = accountModel
1762
1763 const videoModel = new VideoModel(pick(row, videoKeys), buildOpts)
1764 videoModel.VideoChannel = channelModel
1765
1766 videoModel.UserVideoHistories = []
1767 videoModel.Thumbnails = []
1768 videoModel.VideoFiles = []
1769 videoModel.VideoStreamingPlaylists = []
1770
1771 videosMemo[row.id] = videoModel
1772 // Don't take object value to have a sorted array
1773 videos.push(videoModel)
1774 }
1775
1776 const videoModel = videosMemo[row.id]
1777
1778 if (row.userVideoHistory?.id && !historyDone.has(row.userVideoHistory.id)) {
1779 const historyModel = new UserVideoHistoryModel(pick(row.userVideoHistory, [ 'id', 'currentTime' ]), buildOpts)
1780 videoModel.UserVideoHistories.push(historyModel)
1781
1782 historyDone.add(row.userVideoHistory.id)
1783 }
1784
1785 if (row.Thumbnails?.id && !thumbnailsDone.has(row.Thumbnails.id)) {
1786 const thumbnailModel = new ThumbnailModel(pick(row.Thumbnails, [ 'id', 'type', 'filename' ]), buildOpts)
1787 videoModel.Thumbnails.push(thumbnailModel)
1788
1789 thumbnailsDone.add(row.Thumbnails.id)
1790 }
1791
1792 if (row.VideoFiles?.id && !videoFilesDone.has(row.VideoFiles.id)) {
1793 const videoFileModel = new VideoFileModel(pick(row.VideoFiles, videoFileKeys), buildOpts)
1794 videoModel.VideoFiles.push(videoFileModel)
1795
1796 videoFilesDone.add(row.VideoFiles.id)
1797 }
1798
1799 if (row.VideoStreamingPlaylists?.id && !videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]) {
1800 const streamingPlaylist = new VideoStreamingPlaylistModel(pick(row.VideoStreamingPlaylists, videoStreamingPlaylistKeys), buildOpts)
1801 streamingPlaylist.VideoFiles = []
1802
1803 videoModel.VideoStreamingPlaylists.push(streamingPlaylist)
1804
1805 videoStreamingPlaylistMemo[streamingPlaylist.id] = streamingPlaylist
1806 }
1807
1808 if (row.VideoStreamingPlaylists?.VideoFiles?.id && !videoFilesDone.has(row.VideoStreamingPlaylists.VideoFiles.id)) {
1809 const streamingPlaylist = videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]
1810
1811 const videoFileModel = new VideoFileModel(pick(row.VideoStreamingPlaylists.VideoFiles, videoFileKeys), buildOpts)
1812 streamingPlaylist.VideoFiles.push(videoFileModel)
1813
1814 videoFilesDone.add(row.VideoStreamingPlaylists.VideoFiles.id)
1815 }
1816 }
1817
1818 return videos
1819 }
1820
1821 static getCategoryLabel (id: number) { 1672 static getCategoryLabel (id: number) {
1822 return VIDEO_CATEGORIES[id] || 'Misc' 1673 return VIDEO_CATEGORIES[id] || 'Misc'
1823 } 1674 }