aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2018-08-31 09:53:07 +0200
committerChocobozzz <me@florianbigard.com>2018-08-31 09:53:07 +0200
commitafd2cba5541fcc4d182176b8797f8416df831869 (patch)
tree0aec33df66f9fa2e58186dbb2e367ea478fda9fe /server
parent2d3741d6d92e9bd1f41694c7442a6d1da434e1f2 (diff)
downloadPeerTube-afd2cba5541fcc4d182176b8797f8416df831869.tar.gz
PeerTube-afd2cba5541fcc4d182176b8797f8416df831869.tar.zst
PeerTube-afd2cba5541fcc4d182176b8797f8416df831869.zip
Try to improve sql videos list query
Split the complex query in 2 different queries
Diffstat (limited to 'server')
-rw-r--r--server/models/video/video.ts234
1 files changed, 144 insertions, 90 deletions
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 695990b17..48232fb7d 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -24,7 +24,8 @@ import {
24 Model, 24 Model,
25 Scopes, 25 Scopes,
26 Table, 26 Table,
27 UpdatedAt 27 UpdatedAt,
28 IIncludeOptions
28} from 'sequelize-typescript' 29} from 'sequelize-typescript'
29import { VideoPrivacy, VideoResolution, VideoState } from '../../../shared' 30import { VideoPrivacy, VideoResolution, VideoState } from '../../../shared'
30import { VideoTorrentObject } from '../../../shared/models/activitypub/objects' 31import { VideoTorrentObject } from '../../../shared/models/activitypub/objects'
@@ -88,6 +89,7 @@ import { ScheduleVideoUpdateModel } from './schedule-video-update'
88import { VideoCaptionModel } from './video-caption' 89import { VideoCaptionModel } from './video-caption'
89import { VideoBlacklistModel } from './video-blacklist' 90import { VideoBlacklistModel } from './video-blacklist'
90import { copy, remove, rename, stat, writeFile } from 'fs-extra' 91import { copy, remove, rename, stat, writeFile } from 'fs-extra'
92import { immutableAssign } from '../../tests/utils'
91 93
92// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation 94// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation
93const indexes: Sequelize.DefineIndexesOptions[] = [ 95const indexes: Sequelize.DefineIndexesOptions[] = [
@@ -117,7 +119,8 @@ const indexes: Sequelize.DefineIndexesOptions[] = [
117] 119]
118 120
119export enum ScopeNames { 121export enum ScopeNames {
120 AVAILABLE_FOR_LIST = 'AVAILABLE_FOR_LIST', 122 AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS',
123 FOR_API = 'FOR_API',
121 WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS', 124 WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS',
122 WITH_TAGS = 'WITH_TAGS', 125 WITH_TAGS = 'WITH_TAGS',
123 WITH_FILES = 'WITH_FILES', 126 WITH_FILES = 'WITH_FILES',
@@ -125,34 +128,37 @@ export enum ScopeNames {
125 WITH_BLACKLISTED = 'WITH_BLACKLISTED' 128 WITH_BLACKLISTED = 'WITH_BLACKLISTED'
126} 129}
127 130
128type AvailableForListOptions = { 131type ForAPIOptions = {
129 actorId: number, 132 ids: number[]
130 includeLocalVideos: boolean, 133 withFiles?: boolean
131 filter?: VideoFilter, 134}
132 categoryOneOf?: number[], 135
133 nsfw?: boolean, 136type AvailableForListIDsOptions = {
134 licenceOneOf?: number[], 137 actorId: number
135 languageOneOf?: string[], 138 includeLocalVideos: boolean
136 tagsOneOf?: string[], 139 filter?: VideoFilter
137 tagsAllOf?: string[], 140 categoryOneOf?: number[]
138 withFiles?: boolean, 141 nsfw?: boolean
139 accountId?: number, 142 licenceOneOf?: number[]
143 languageOneOf?: string[]
144 tagsOneOf?: string[]
145 tagsAllOf?: string[]
146 withFiles?: boolean
147 accountId?: number
140 videoChannelId?: number 148 videoChannelId?: number
141} 149}
142 150
143@Scopes({ 151@Scopes({
144 [ScopeNames.AVAILABLE_FOR_LIST]: (options: AvailableForListOptions) => { 152 [ScopeNames.FOR_API]: (options: ForAPIOptions) => {
145 const accountInclude = { 153 const accountInclude = {
146 attributes: [ 'id', 'name' ], 154 attributes: [ 'id', 'name' ],
147 model: AccountModel.unscoped(), 155 model: AccountModel.unscoped(),
148 required: true, 156 required: true,
149 where: {},
150 include: [ 157 include: [
151 { 158 {
152 attributes: [ 'id', 'uuid', 'preferredUsername', 'url', 'serverId', 'avatarId' ], 159 attributes: [ 'id', 'uuid', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
153 model: ActorModel.unscoped(), 160 model: ActorModel.unscoped(),
154 required: true, 161 required: true,
155 where: VideoModel.buildActorWhereWithFilter(options.filter),
156 include: [ 162 include: [
157 { 163 {
158 attributes: [ 'host' ], 164 attributes: [ 'host' ],
@@ -172,7 +178,6 @@ type AvailableForListOptions = {
172 attributes: [ 'name', 'description', 'id' ], 178 attributes: [ 'name', 'description', 'id' ],
173 model: VideoChannelModel.unscoped(), 179 model: VideoChannelModel.unscoped(),
174 required: true, 180 required: true,
175 where: {},
176 include: [ 181 include: [
177 { 182 {
178 attributes: [ 'uuid', 'preferredUsername', 'url', 'serverId', 'avatarId' ], 183 attributes: [ 'uuid', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
@@ -194,10 +199,29 @@ type AvailableForListOptions = {
194 ] 199 ]
195 } 200 }
196 201
197 // FIXME: It would be more efficient to use a CTE so we join AFTER the filters, but sequelize does not support it...
198 const query: IFindOptions<VideoModel> = { 202 const query: IFindOptions<VideoModel> = {
199 where: { 203 where: {
200 id: { 204 id: {
205 [Sequelize.Op.any]: options.ids
206 }
207 },
208 include: [ videoChannelInclude ]
209 }
210
211 if (options.withFiles === true) {
212 query.include.push({
213 model: VideoFileModel.unscoped(),
214 required: true
215 })
216 }
217
218 return query
219 },
220 [ScopeNames.AVAILABLE_FOR_LIST_IDS]: (options: AvailableForListIDsOptions) => {
221 const query: IFindOptions<VideoModel> = {
222 attributes: [ 'id' ],
223 where: {
224 id: {
201 [Sequelize.Op.notIn]: Sequelize.literal( 225 [Sequelize.Op.notIn]: Sequelize.literal(
202 '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")' 226 '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
203 ) 227 )
@@ -217,7 +241,48 @@ type AvailableForListOptions = {
217 } 241 }
218 ] 242 ]
219 }, 243 },
220 include: [ videoChannelInclude ] 244 include: [ ]
245 }
246
247 if (options.filter || options.accountId || options.videoChannelId) {
248 const videoChannelInclude: IIncludeOptions = {
249 attributes: [],
250 model: VideoChannelModel.unscoped(),
251 required: true
252 }
253
254 if (options.videoChannelId) {
255 videoChannelInclude.where = {
256 id: options.videoChannelId
257 }
258 }
259
260 if (options.filter || options.accountId) {
261 const accountInclude: IIncludeOptions = {
262 attributes: [],
263 model: AccountModel.unscoped(),
264 required: true
265 }
266
267 if (options.filter) {
268 accountInclude.include = [
269 {
270 attributes: [],
271 model: ActorModel.unscoped(),
272 required: true,
273 where: VideoModel.buildActorWhereWithFilter(options.filter)
274 }
275 ]
276 }
277
278 if (options.accountId) {
279 accountInclude.where = { id: options.accountId }
280 }
281
282 videoChannelInclude.include = [ accountInclude ]
283 }
284
285 query.include.push(videoChannelInclude)
221 } 286 }
222 287
223 if (options.actorId) { 288 if (options.actorId) {
@@ -235,17 +300,17 @@ type AvailableForListOptions = {
235 const actorIdNumber = parseInt(options.actorId.toString(), 10) 300 const actorIdNumber = parseInt(options.actorId.toString(), 10)
236 query.where['id'][ Sequelize.Op.in ] = Sequelize.literal( 301 query.where['id'][ Sequelize.Op.in ] = Sequelize.literal(
237 '(' + 302 '(' +
238 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' + 303 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
239 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' + 304 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
240 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + 305 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
241 ' UNION ALL ' + 306 ' UNION ALL ' +
242 'SELECT "video"."id" AS "id" FROM "video" ' + 307 'SELECT "video"."id" AS "id" FROM "video" ' +
243 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + 308 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
244 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + 309 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
245 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + 310 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
246 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + 311 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
247 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + 312 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
248 localVideosReq + 313 localVideosReq +
249 ')' 314 ')'
250 ) 315 )
251 } 316 }
@@ -308,18 +373,6 @@ type AvailableForListOptions = {
308 } 373 }
309 } 374 }
310 375
311 if (options.accountId) {
312 accountInclude.where = {
313 id: options.accountId
314 }
315 }
316
317 if (options.videoChannelId) {
318 videoChannelInclude.where = {
319 id: options.videoChannelId
320 }
321 }
322
323 return query 376 return query
324 }, 377 },
325 [ScopeNames.WITH_ACCOUNT_DETAILS]: { 378 [ScopeNames.WITH_ACCOUNT_DETAILS]: {
@@ -848,33 +901,22 @@ export class VideoModel extends Model<VideoModel> {
848 // actorId === null has a meaning, so just check undefined 901 // actorId === null has a meaning, so just check undefined
849 const actorId = options.actorId !== undefined ? options.actorId : (await getServerActor()).id 902 const actorId = options.actorId !== undefined ? options.actorId : (await getServerActor()).id
850 903
851 const scopes = { 904 const queryOptions = {
852 method: [ 905 actorId,
853 ScopeNames.AVAILABLE_FOR_LIST, { 906 nsfw: options.nsfw,
854 actorId, 907 categoryOneOf: options.categoryOneOf,
855 nsfw: options.nsfw, 908 licenceOneOf: options.licenceOneOf,
856 categoryOneOf: options.categoryOneOf, 909 languageOneOf: options.languageOneOf,
857 licenceOneOf: options.licenceOneOf, 910 tagsOneOf: options.tagsOneOf,
858 languageOneOf: options.languageOneOf, 911 tagsAllOf: options.tagsAllOf,
859 tagsOneOf: options.tagsOneOf, 912 filter: options.filter,
860 tagsAllOf: options.tagsAllOf, 913 withFiles: options.withFiles,
861 filter: options.filter, 914 accountId: options.accountId,
862 withFiles: options.withFiles, 915 videoChannelId: options.videoChannelId,
863 accountId: options.accountId, 916 includeLocalVideos: options.includeLocalVideos
864 videoChannelId: options.videoChannelId,
865 includeLocalVideos: options.includeLocalVideos
866 } as AvailableForListOptions
867 ]
868 } 917 }
869 918
870 return VideoModel.scope(scopes) 919 return VideoModel.getAvailableForApi(query, queryOptions)
871 .findAndCountAll(query)
872 .then(({ rows, count }) => {
873 return {
874 data: rows,
875 total: count
876 }
877 })
878 } 920 }
879 921
880 static async searchAndPopulateAccountAndServer (options: { 922 static async searchAndPopulateAccountAndServer (options: {
@@ -960,29 +1002,18 @@ export class VideoModel extends Model<VideoModel> {
960 } 1002 }
961 1003
962 const serverActor = await getServerActor() 1004 const serverActor = await getServerActor()
963 const scopes = { 1005 const queryOptions = {
964 method: [ 1006 actorId: serverActor.id,
965 ScopeNames.AVAILABLE_FOR_LIST, { 1007 includeLocalVideos: options.includeLocalVideos,
966 actorId: serverActor.id, 1008 nsfw: options.nsfw,
967 includeLocalVideos: options.includeLocalVideos, 1009 categoryOneOf: options.categoryOneOf,
968 nsfw: options.nsfw, 1010 licenceOneOf: options.licenceOneOf,
969 categoryOneOf: options.categoryOneOf, 1011 languageOneOf: options.languageOneOf,
970 licenceOneOf: options.licenceOneOf, 1012 tagsOneOf: options.tagsOneOf,
971 languageOneOf: options.languageOneOf, 1013 tagsAllOf: options.tagsAllOf
972 tagsOneOf: options.tagsOneOf,
973 tagsAllOf: options.tagsAllOf
974 } as AvailableForListOptions
975 ]
976 } 1014 }
977 1015
978 return VideoModel.scope(scopes) 1016 return VideoModel.getAvailableForApi(query, queryOptions)
979 .findAndCountAll(query)
980 .then(({ rows, count }) => {
981 return {
982 data: rows,
983 total: count
984 }
985 })
986 } 1017 }
987 1018
988 static load (id: number, t?: Sequelize.Transaction) { 1019 static load (id: number, t?: Sequelize.Transaction) {
@@ -1094,7 +1125,7 @@ export class VideoModel extends Model<VideoModel> {
1094 }) as any, // FIXME: typings 1125 }) as any, // FIXME: typings
1095 where: { 1126 where: {
1096 [field]: { 1127 [field]: {
1097 [Sequelize.Op.not]: null, 1128 [Sequelize.Op.not]: null
1098 }, 1129 },
1099 privacy: VideoPrivacy.PUBLIC, 1130 privacy: VideoPrivacy.PUBLIC,
1100 state: VideoState.PUBLISHED 1131 state: VideoState.PUBLISHED
@@ -1116,6 +1147,29 @@ export class VideoModel extends Model<VideoModel> {
1116 return {} 1147 return {}
1117 } 1148 }
1118 1149
1150 private static async getAvailableForApi (query: IFindOptions<VideoModel>, options: AvailableForListIDsOptions) {
1151 const idsScope = {
1152 method: [
1153 ScopeNames.AVAILABLE_FOR_LIST_IDS, options
1154 ]
1155 }
1156
1157 const { count, rows: rowsId } = await VideoModel.scope(idsScope).findAndCountAll(query)
1158 const ids = rowsId.map(r => r.id)
1159
1160 if (ids.length === 0) return { data: [], total: count }
1161
1162 const apiScope = {
1163 method: [ ScopeNames.FOR_API, { ids, withFiles: options.withFiles } as ForAPIOptions ]
1164 }
1165 const rows = await VideoModel.scope(apiScope).findAll(immutableAssign(query, { offset: 0 }))
1166
1167 return {
1168 data: rows,
1169 total: count
1170 }
1171 }
1172
1119 private static getCategoryLabel (id: number) { 1173 private static getCategoryLabel (id: number) {
1120 return VIDEO_CATEGORIES[id] || 'Misc' 1174 return VIDEO_CATEGORIES[id] || 'Misc'
1121 } 1175 }