aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
Diffstat (limited to 'server')
-rw-r--r--server/controllers/api/index.ts2
-rw-r--r--server/controllers/api/search.ts43
-rw-r--r--server/controllers/api/videos/index.ts23
-rw-r--r--server/controllers/client.ts10
-rw-r--r--server/helpers/database-utils.ts2
-rw-r--r--server/initializers/constants.ts4
-rw-r--r--server/initializers/database.ts43
-rw-r--r--server/middlewares/sort.ts7
-rw-r--r--server/middlewares/validators/index.ts1
-rw-r--r--server/middlewares/validators/search.ts22
-rw-r--r--server/middlewares/validators/sort.ts3
-rw-r--r--server/middlewares/validators/videos.ts15
-rw-r--r--server/models/activitypub/actor.ts6
-rw-r--r--server/models/utils.ts52
-rw-r--r--server/models/video/video.ts92
-rw-r--r--server/tests/utils/videos/videos.ts12
16 files changed, 233 insertions, 104 deletions
diff --git a/server/controllers/api/index.ts b/server/controllers/api/index.ts
index c386a6710..e928a7478 100644
--- a/server/controllers/api/index.ts
+++ b/server/controllers/api/index.ts
@@ -9,6 +9,7 @@ import { videosRouter } from './videos'
9import { badRequest } from '../../helpers/express-utils' 9import { badRequest } from '../../helpers/express-utils'
10import { videoChannelRouter } from './video-channel' 10import { videoChannelRouter } from './video-channel'
11import * as cors from 'cors' 11import * as cors from 'cors'
12import { searchRouter } from './search'
12 13
13const apiRouter = express.Router() 14const apiRouter = express.Router()
14 15
@@ -26,6 +27,7 @@ apiRouter.use('/accounts', accountsRouter)
26apiRouter.use('/video-channels', videoChannelRouter) 27apiRouter.use('/video-channels', videoChannelRouter)
27apiRouter.use('/videos', videosRouter) 28apiRouter.use('/videos', videosRouter)
28apiRouter.use('/jobs', jobsRouter) 29apiRouter.use('/jobs', jobsRouter)
30apiRouter.use('/search', searchRouter)
29apiRouter.use('/ping', pong) 31apiRouter.use('/ping', pong)
30apiRouter.use('/*', badRequest) 32apiRouter.use('/*', badRequest)
31 33
diff --git a/server/controllers/api/search.ts b/server/controllers/api/search.ts
new file mode 100644
index 000000000..2ff340b59
--- /dev/null
+++ b/server/controllers/api/search.ts
@@ -0,0 +1,43 @@
1import * as express from 'express'
2import { isNSFWHidden } from '../../helpers/express-utils'
3import { getFormattedObjects } from '../../helpers/utils'
4import { VideoModel } from '../../models/video/video'
5import {
6 asyncMiddleware,
7 optionalAuthenticate,
8 paginationValidator,
9 searchValidator,
10 setDefaultPagination,
11 setDefaultSearchSort,
12 videosSearchSortValidator
13} from '../../middlewares'
14
15const searchRouter = express.Router()
16
17searchRouter.get('/videos',
18 paginationValidator,
19 setDefaultPagination,
20 videosSearchSortValidator,
21 setDefaultSearchSort,
22 optionalAuthenticate,
23 searchValidator,
24 asyncMiddleware(searchVideos)
25)
26
27// ---------------------------------------------------------------------------
28
29export { searchRouter }
30
31// ---------------------------------------------------------------------------
32
33async function searchVideos (req: express.Request, res: express.Response) {
34 const resultList = await VideoModel.searchAndPopulateAccountAndServer(
35 req.query.search as string,
36 req.query.start as number,
37 req.query.count as number,
38 req.query.sort as string,
39 isNSFWHidden(res)
40 )
41
42 return res.json(getFormattedObjects(resultList.data, resultList.total))
43}
diff --git a/server/controllers/api/videos/index.ts b/server/controllers/api/videos/index.ts
index bbb5b8b4c..547522123 100644
--- a/server/controllers/api/videos/index.ts
+++ b/server/controllers/api/videos/index.ts
@@ -38,7 +38,6 @@ import {
38 videosAddValidator, 38 videosAddValidator,
39 videosGetValidator, 39 videosGetValidator,
40 videosRemoveValidator, 40 videosRemoveValidator,
41 videosSearchValidator,
42 videosSortValidator, 41 videosSortValidator,
43 videosUpdateValidator 42 videosUpdateValidator
44} from '../../../middlewares' 43} from '../../../middlewares'
@@ -50,7 +49,6 @@ import { blacklistRouter } from './blacklist'
50import { videoCommentRouter } from './comment' 49import { videoCommentRouter } from './comment'
51import { rateVideoRouter } from './rate' 50import { rateVideoRouter } from './rate'
52import { VideoFilter } from '../../../../shared/models/videos/video-query.type' 51import { VideoFilter } from '../../../../shared/models/videos/video-query.type'
53import { VideoSortField } from '../../../../client/src/app/shared/video/sort-field.type'
54import { createReqFiles, isNSFWHidden } from '../../../helpers/express-utils' 52import { createReqFiles, isNSFWHidden } from '../../../helpers/express-utils'
55import { ScheduleVideoUpdateModel } from '../../../models/video/schedule-video-update' 53import { ScheduleVideoUpdateModel } from '../../../models/video/schedule-video-update'
56import { videoCaptionsRouter } from './captions' 54import { videoCaptionsRouter } from './captions'
@@ -94,15 +92,6 @@ videosRouter.get('/',
94 optionalAuthenticate, 92 optionalAuthenticate,
95 asyncMiddleware(listVideos) 93 asyncMiddleware(listVideos)
96) 94)
97videosRouter.get('/search',
98 videosSearchValidator,
99 paginationValidator,
100 videosSortValidator,
101 setDefaultSort,
102 setDefaultPagination,
103 optionalAuthenticate,
104 asyncMiddleware(searchVideos)
105)
106videosRouter.put('/:id', 95videosRouter.put('/:id',
107 authenticate, 96 authenticate,
108 reqVideoFileUpdate, 97 reqVideoFileUpdate,
@@ -432,15 +421,3 @@ async function removeVideo (req: express.Request, res: express.Response) {
432 421
433 return res.type('json').status(204).end() 422 return res.type('json').status(204).end()
434} 423}
435
436async function searchVideos (req: express.Request, res: express.Response, next: express.NextFunction) {
437 const resultList = await VideoModel.searchAndPopulateAccountAndServer(
438 req.query.search as string,
439 req.query.start as number,
440 req.query.count as number,
441 req.query.sort as VideoSortField,
442 isNSFWHidden(res)
443 )
444
445 return res.json(getFormattedObjects(resultList.data, resultList.total))
446}
diff --git a/server/controllers/client.ts b/server/controllers/client.ts
index 352d45fbf..bbb518c1b 100644
--- a/server/controllers/client.ts
+++ b/server/controllers/client.ts
@@ -5,6 +5,7 @@ import { ACCEPT_HEADERS, STATIC_MAX_AGE } from '../initializers'
5import { asyncMiddleware } from '../middlewares' 5import { asyncMiddleware } from '../middlewares'
6import { buildFileLocale, getCompleteLocale, is18nLocale, LOCALE_FILES } from '../../shared/models/i18n/i18n' 6import { buildFileLocale, getCompleteLocale, is18nLocale, LOCALE_FILES } from '../../shared/models/i18n/i18n'
7import { ClientHtml } from '../lib/client-html' 7import { ClientHtml } from '../lib/client-html'
8import { logger } from '../helpers/logger'
8 9
9const clientsRouter = express.Router() 10const clientsRouter = express.Router()
10 11
@@ -66,9 +67,14 @@ clientsRouter.use('/client/*', (req: express.Request, res: express.Response, nex
66 67
67// Always serve index client page (the client is a single page application, let it handle routing) 68// Always serve index client page (the client is a single page application, let it handle routing)
68// Try to provide the right language index.html 69// Try to provide the right language index.html
69clientsRouter.use('/(:language)?', function (req, res) { 70clientsRouter.use('/(:language)?', async function (req, res) {
70 if (req.accepts(ACCEPT_HEADERS) === 'html') { 71 if (req.accepts(ACCEPT_HEADERS) === 'html') {
71 return generateHTMLPage(req, res, req.params.language) 72 try {
73 await generateHTMLPage(req, res, req.params.language)
74 return
75 } catch (err) {
76 logger.error('Cannot generate HTML page.', err)
77 }
72 } 78 }
73 79
74 return res.status(404).end() 80 return res.status(404).end()
diff --git a/server/helpers/database-utils.ts b/server/helpers/database-utils.ts
index 11304cafb..53f881fb3 100644
--- a/server/helpers/database-utils.ts
+++ b/server/helpers/database-utils.ts
@@ -1,6 +1,6 @@
1import * as retry from 'async/retry' 1import * as retry from 'async/retry'
2import * as Bluebird from 'bluebird' 2import * as Bluebird from 'bluebird'
3import { Model } from 'sequelize-typescript' 3import { Model, Sequelize } from 'sequelize-typescript'
4import { logger } from './logger' 4import { logger } from './logger'
5 5
6function retryTransactionWrapper <T, A, B, C> ( 6function retryTransactionWrapper <T, A, B, C> (
diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts
index ba48399de..b966c0acb 100644
--- a/server/initializers/constants.ts
+++ b/server/initializers/constants.ts
@@ -35,7 +35,9 @@ const SORTABLE_COLUMNS = {
35 VIDEO_COMMENT_THREADS: [ 'createdAt' ], 35 VIDEO_COMMENT_THREADS: [ 'createdAt' ],
36 BLACKLISTS: [ 'id', 'name', 'duration', 'views', 'likes', 'dislikes', 'uuid', 'createdAt' ], 36 BLACKLISTS: [ 'id', 'name', 'duration', 'views', 'likes', 'dislikes', 'uuid', 'createdAt' ],
37 FOLLOWERS: [ 'createdAt' ], 37 FOLLOWERS: [ 'createdAt' ],
38 FOLLOWING: [ 'createdAt' ] 38 FOLLOWING: [ 'createdAt' ],
39
40 VIDEOS_SEARCH: [ 'bestmatch', 'name', 'duration', 'createdAt', 'publishedAt', 'views', 'likes' ]
39} 41}
40 42
41const OAUTH_LIFETIME = { 43const OAUTH_LIFETIME = {
diff --git a/server/initializers/database.ts b/server/initializers/database.ts
index 434d7ef19..045f41a96 100644
--- a/server/initializers/database.ts
+++ b/server/initializers/database.ts
@@ -80,6 +80,14 @@ async function initDatabaseModels (silent: boolean) {
80 ScheduleVideoUpdateModel 80 ScheduleVideoUpdateModel
81 ]) 81 ])
82 82
83 // Check extensions exist in the database
84 await checkPostgresExtensions()
85
86 // Create custom PostgreSQL functions
87 await createFunctions()
88
89 await sequelizeTypescript.query('CREATE EXTENSION IF NOT EXISTS pg_trgm', { raw: true })
90
83 if (!silent) logger.info('Database %s is ready.', dbname) 91 if (!silent) logger.info('Database %s is ready.', dbname)
84 92
85 return 93 return
@@ -91,3 +99,38 @@ export {
91 initDatabaseModels, 99 initDatabaseModels,
92 sequelizeTypescript 100 sequelizeTypescript
93} 101}
102
103// ---------------------------------------------------------------------------
104
105async function checkPostgresExtensions () {
106 const extensions = [
107 'pg_trgm',
108 'unaccent'
109 ]
110
111 for (const extension of extensions) {
112 const query = `SELECT true AS enabled FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;`
113 const [ res ] = await sequelizeTypescript.query(query, { raw: true })
114
115 if (!res || res.length === 0 || res[ 0 ][ 'enabled' ] !== true) {
116 // Try to create the extension ourself
117 try {
118 await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true })
119
120 } catch {
121 const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` +
122 `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.`
123 throw new Error(errorMessage)
124 }
125 }
126 }
127}
128
129async function createFunctions () {
130 const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(varchar)
131 RETURNS text AS $$
132 SELECT unaccent($1)
133 $$ LANGUAGE sql IMMUTABLE;`
134
135 return sequelizeTypescript.query(query, { raw: true })
136}
diff --git a/server/middlewares/sort.ts b/server/middlewares/sort.ts
index cdb809e75..6307ee154 100644
--- a/server/middlewares/sort.ts
+++ b/server/middlewares/sort.ts
@@ -8,6 +8,12 @@ function setDefaultSort (req: express.Request, res: express.Response, next: expr
8 return next() 8 return next()
9} 9}
10 10
11function setDefaultSearchSort (req: express.Request, res: express.Response, next: express.NextFunction) {
12 if (!req.query.sort) req.query.sort = '-bestmatch'
13
14 return next()
15}
16
11function setBlacklistSort (req: express.Request, res: express.Response, next: express.NextFunction) { 17function setBlacklistSort (req: express.Request, res: express.Response, next: express.NextFunction) {
12 let newSort: SortType = { sortModel: undefined, sortValue: undefined } 18 let newSort: SortType = { sortModel: undefined, sortValue: undefined }
13 19
@@ -33,5 +39,6 @@ function setBlacklistSort (req: express.Request, res: express.Response, next: ex
33 39
34export { 40export {
35 setDefaultSort, 41 setDefaultSort,
42 setDefaultSearchSort,
36 setBlacklistSort 43 setBlacklistSort
37} 44}
diff --git a/server/middlewares/validators/index.ts b/server/middlewares/validators/index.ts
index b69e1f14b..e3f0f5963 100644
--- a/server/middlewares/validators/index.ts
+++ b/server/middlewares/validators/index.ts
@@ -10,3 +10,4 @@ export * from './videos'
10export * from './video-blacklist' 10export * from './video-blacklist'
11export * from './video-channels' 11export * from './video-channels'
12export * from './webfinger' 12export * from './webfinger'
13export * from './search'
diff --git a/server/middlewares/validators/search.ts b/server/middlewares/validators/search.ts
new file mode 100644
index 000000000..774845e8a
--- /dev/null
+++ b/server/middlewares/validators/search.ts
@@ -0,0 +1,22 @@
1import * as express from 'express'
2import { areValidationErrors } from './utils'
3import { logger } from '../../helpers/logger'
4import { query } from 'express-validator/check'
5
6const searchValidator = [
7 query('search').not().isEmpty().withMessage('Should have a valid search'),
8
9 (req: express.Request, res: express.Response, next: express.NextFunction) => {
10 logger.debug('Checking search parameters', { parameters: req.params })
11
12 if (areValidationErrors(req, res)) return
13
14 return next()
15 }
16]
17
18// ---------------------------------------------------------------------------
19
20export {
21 searchValidator
22}
diff --git a/server/middlewares/validators/sort.ts b/server/middlewares/validators/sort.ts
index 925f47e57..00bde548c 100644
--- a/server/middlewares/validators/sort.ts
+++ b/server/middlewares/validators/sort.ts
@@ -7,6 +7,7 @@ const SORTABLE_ACCOUNTS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.ACCOUNT
7const SORTABLE_JOBS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.JOBS) 7const SORTABLE_JOBS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.JOBS)
8const SORTABLE_VIDEO_ABUSES_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEO_ABUSES) 8const SORTABLE_VIDEO_ABUSES_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEO_ABUSES)
9const SORTABLE_VIDEOS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEOS) 9const SORTABLE_VIDEOS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEOS)
10const SORTABLE_VIDEOS_SEARCH_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEOS_SEARCH)
10const SORTABLE_VIDEO_COMMENT_THREADS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEO_COMMENT_THREADS) 11const SORTABLE_VIDEO_COMMENT_THREADS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEO_COMMENT_THREADS)
11const SORTABLE_BLACKLISTS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.BLACKLISTS) 12const SORTABLE_BLACKLISTS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.BLACKLISTS)
12const SORTABLE_VIDEO_CHANNELS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEO_CHANNELS) 13const SORTABLE_VIDEO_CHANNELS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEO_CHANNELS)
@@ -18,6 +19,7 @@ const accountsSortValidator = checkSort(SORTABLE_ACCOUNTS_COLUMNS)
18const jobsSortValidator = checkSort(SORTABLE_JOBS_COLUMNS) 19const jobsSortValidator = checkSort(SORTABLE_JOBS_COLUMNS)
19const videoAbusesSortValidator = checkSort(SORTABLE_VIDEO_ABUSES_COLUMNS) 20const videoAbusesSortValidator = checkSort(SORTABLE_VIDEO_ABUSES_COLUMNS)
20const videosSortValidator = checkSort(SORTABLE_VIDEOS_COLUMNS) 21const videosSortValidator = checkSort(SORTABLE_VIDEOS_COLUMNS)
22const videosSearchSortValidator = checkSort(SORTABLE_VIDEOS_SEARCH_COLUMNS)
21const videoCommentThreadsSortValidator = checkSort(SORTABLE_VIDEO_COMMENT_THREADS_COLUMNS) 23const videoCommentThreadsSortValidator = checkSort(SORTABLE_VIDEO_COMMENT_THREADS_COLUMNS)
22const blacklistSortValidator = checkSort(SORTABLE_BLACKLISTS_COLUMNS) 24const blacklistSortValidator = checkSort(SORTABLE_BLACKLISTS_COLUMNS)
23const videoChannelsSortValidator = checkSort(SORTABLE_VIDEO_CHANNELS_COLUMNS) 25const videoChannelsSortValidator = checkSort(SORTABLE_VIDEO_CHANNELS_COLUMNS)
@@ -30,6 +32,7 @@ export {
30 usersSortValidator, 32 usersSortValidator,
31 videoAbusesSortValidator, 33 videoAbusesSortValidator,
32 videoChannelsSortValidator, 34 videoChannelsSortValidator,
35 videosSearchSortValidator,
33 videosSortValidator, 36 videosSortValidator,
34 blacklistSortValidator, 37 blacklistSortValidator,
35 accountsSortValidator, 38 accountsSortValidator,
diff --git a/server/middlewares/validators/videos.ts b/server/middlewares/validators/videos.ts
index abb23b510..d9af2aa0a 100644
--- a/server/middlewares/validators/videos.ts
+++ b/server/middlewares/validators/videos.ts
@@ -1,6 +1,6 @@
1import * as express from 'express' 1import * as express from 'express'
2import 'express-validator' 2import 'express-validator'
3import { body, param, query, ValidationChain } from 'express-validator/check' 3import { body, param, ValidationChain } from 'express-validator/check'
4import { UserRight, VideoPrivacy } from '../../../shared' 4import { UserRight, VideoPrivacy } from '../../../shared'
5import { 5import {
6 isBooleanValid, 6 isBooleanValid,
@@ -172,18 +172,6 @@ const videosRemoveValidator = [
172 } 172 }
173] 173]
174 174
175const videosSearchValidator = [
176 query('search').not().isEmpty().withMessage('Should have a valid search'),
177
178 (req: express.Request, res: express.Response, next: express.NextFunction) => {
179 logger.debug('Checking videosSearch parameters', { parameters: req.params })
180
181 if (areValidationErrors(req, res)) return
182
183 return next()
184 }
185]
186
187const videoAbuseReportValidator = [ 175const videoAbuseReportValidator = [
188 param('id').custom(isIdOrUUIDValid).not().isEmpty().withMessage('Should have a valid id'), 176 param('id').custom(isIdOrUUIDValid).not().isEmpty().withMessage('Should have a valid id'),
189 body('reason').custom(isVideoAbuseReasonValid).withMessage('Should have a valid reason'), 177 body('reason').custom(isVideoAbuseReasonValid).withMessage('Should have a valid reason'),
@@ -240,7 +228,6 @@ export {
240 videosUpdateValidator, 228 videosUpdateValidator,
241 videosGetValidator, 229 videosGetValidator,
242 videosRemoveValidator, 230 videosRemoveValidator,
243 videosSearchValidator,
244 videosShareValidator, 231 videosShareValidator,
245 232
246 videoAbuseReportValidator, 233 videoAbuseReportValidator,
diff --git a/server/models/activitypub/actor.ts b/server/models/activitypub/actor.ts
index 1d0e54ee3..38a689fea 100644
--- a/server/models/activitypub/actor.ts
+++ b/server/models/activitypub/actor.ts
@@ -88,6 +88,12 @@ enum ScopeNames {
88 }, 88 },
89 { 89 {
90 fields: [ 'inboxUrl', 'sharedInboxUrl' ] 90 fields: [ 'inboxUrl', 'sharedInboxUrl' ]
91 },
92 {
93 fields: [ 'serverId' ]
94 },
95 {
96 fields: [ 'avatarId' ]
91 } 97 }
92 ] 98 ]
93}) 99})
diff --git a/server/models/utils.ts b/server/models/utils.ts
index 59ce83c16..49d32c24f 100644
--- a/server/models/utils.ts
+++ b/server/models/utils.ts
@@ -1,6 +1,8 @@
1// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ] 1// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ]
2import { Sequelize } from 'sequelize-typescript'
3
2function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) { 4function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) {
3 let field: string 5 let field: any
4 let direction: 'ASC' | 'DESC' 6 let direction: 'ASC' | 'DESC'
5 7
6 if (value.substring(0, 1) === '-') { 8 if (value.substring(0, 1) === '-') {
@@ -11,6 +13,9 @@ function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) {
11 field = value 13 field = value
12 } 14 }
13 15
16 // Alias
17 if (field.toLowerCase() === 'bestmatch') field = Sequelize.col('similarity')
18
14 return [ [ field, direction ], lastSort ] 19 return [ [ field, direction ], lastSort ]
15} 20}
16 21
@@ -27,10 +32,53 @@ function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldN
27 } 32 }
28} 33}
29 34
35function buildTrigramSearchIndex (indexName: string, attribute: string) {
36 return {
37 name: indexName,
38 fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + '))') as any ],
39 using: 'gin',
40 operator: 'gin_trgm_ops'
41 }
42}
43
44function createSimilarityAttribute (col: string, value: string) {
45 return Sequelize.fn(
46 'similarity',
47
48 searchTrigramNormalizeCol(col),
49
50 searchTrigramNormalizeValue(value)
51 )
52}
53
54function createSearchTrigramQuery (col: string, value: string) {
55 return {
56 [ Sequelize.Op.or ]: [
57 // FIXME: use word_similarity instead of just similarity?
58 Sequelize.where(searchTrigramNormalizeCol(col), ' % ', searchTrigramNormalizeValue(value)),
59
60 Sequelize.where(searchTrigramNormalizeCol(col), ' LIKE ', searchTrigramNormalizeValue(`%${value}%`))
61 ]
62 }
63}
64
30// --------------------------------------------------------------------------- 65// ---------------------------------------------------------------------------
31 66
32export { 67export {
33 getSort, 68 getSort,
34 getSortOnModel, 69 getSortOnModel,
35 throwIfNotValid 70 createSimilarityAttribute,
71 throwIfNotValid,
72 buildTrigramSearchIndex,
73 createSearchTrigramQuery
74}
75
76// ---------------------------------------------------------------------------
77
78function searchTrigramNormalizeValue (value: string) {
79 return Sequelize.fn('lower', Sequelize.fn('unaccent', value))
80}
81
82function searchTrigramNormalizeCol (col: string) {
83 return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
36} 84}
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 74a3a5d05..15b4dda5b 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -83,7 +83,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate'
83import { ActorModel } from '../activitypub/actor' 83import { ActorModel } from '../activitypub/actor'
84import { AvatarModel } from '../avatar/avatar' 84import { AvatarModel } from '../avatar/avatar'
85import { ServerModel } from '../server/server' 85import { ServerModel } from '../server/server'
86import { getSort, throwIfNotValid } from '../utils' 86import { buildTrigramSearchIndex, createSearchTrigramQuery, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
87import { TagModel } from './tag' 87import { TagModel } from './tag'
88import { VideoAbuseModel } from './video-abuse' 88import { VideoAbuseModel } from './video-abuse'
89import { VideoChannelModel } from './video-channel' 89import { VideoChannelModel } from './video-channel'
@@ -94,6 +94,37 @@ import { VideoTagModel } from './video-tag'
94import { ScheduleVideoUpdateModel } from './schedule-video-update' 94import { ScheduleVideoUpdateModel } from './schedule-video-update'
95import { VideoCaptionModel } from './video-caption' 95import { VideoCaptionModel } from './video-caption'
96 96
97// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation
98const indexes: Sequelize.DefineIndexesOptions[] = [
99 buildTrigramSearchIndex('video_name_trigram', 'name'),
100
101 {
102 fields: [ 'createdAt' ]
103 },
104 {
105 fields: [ 'duration' ]
106 },
107 {
108 fields: [ 'views' ]
109 },
110 {
111 fields: [ 'likes' ]
112 },
113 {
114 fields: [ 'uuid' ]
115 },
116 {
117 fields: [ 'channelId' ]
118 },
119 {
120 fields: [ 'id', 'privacy', 'state', 'waitTranscoding' ]
121 },
122 {
123 fields: [ 'url'],
124 unique: true
125 }
126]
127
97export enum ScopeNames { 128export enum ScopeNames {
98 AVAILABLE_FOR_LIST = 'AVAILABLE_FOR_LIST', 129 AVAILABLE_FOR_LIST = 'AVAILABLE_FOR_LIST',
99 WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS', 130 WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS',
@@ -309,36 +340,7 @@ export enum ScopeNames {
309}) 340})
310@Table({ 341@Table({
311 tableName: 'video', 342 tableName: 'video',
312 indexes: [ 343 indexes
313 {
314 fields: [ 'name' ]
315 },
316 {
317 fields: [ 'createdAt' ]
318 },
319 {
320 fields: [ 'duration' ]
321 },
322 {
323 fields: [ 'views' ]
324 },
325 {
326 fields: [ 'likes' ]
327 },
328 {
329 fields: [ 'uuid' ]
330 },
331 {
332 fields: [ 'channelId' ]
333 },
334 {
335 fields: [ 'id', 'privacy', 'state', 'waitTranscoding' ]
336 },
337 {
338 fields: [ 'url'],
339 unique: true
340 }
341 ]
342}) 344})
343export class VideoModel extends Model<VideoModel> { 345export class VideoModel extends Model<VideoModel> {
344 346
@@ -794,33 +796,13 @@ export class VideoModel extends Model<VideoModel> {
794 796
795 static async searchAndPopulateAccountAndServer (value: string, start: number, count: number, sort: string, hideNSFW: boolean) { 797 static async searchAndPopulateAccountAndServer (value: string, start: number, count: number, sort: string, hideNSFW: boolean) {
796 const query: IFindOptions<VideoModel> = { 798 const query: IFindOptions<VideoModel> = {
799 attributes: {
800 include: [ createSimilarityAttribute('VideoModel.name', value) ]
801 },
797 offset: start, 802 offset: start,
798 limit: count, 803 limit: count,
799 order: getSort(sort), 804 order: getSort(sort),
800 where: { 805 where: createSearchTrigramQuery('VideoModel.name', value)
801 [Sequelize.Op.or]: [
802 {
803 name: {
804 [ Sequelize.Op.iLike ]: '%' + value + '%'
805 }
806 },
807 {
808 preferredUsernameChannel: Sequelize.where(Sequelize.col('VideoChannel->Actor.preferredUsername'), {
809 [ Sequelize.Op.iLike ]: '%' + value + '%'
810 })
811 },
812 {
813 preferredUsernameAccount: Sequelize.where(Sequelize.col('VideoChannel->Account->Actor.preferredUsername'), {
814 [ Sequelize.Op.iLike ]: '%' + value + '%'
815 })
816 },
817 {
818 host: Sequelize.where(Sequelize.col('VideoChannel->Account->Actor->Server.host'), {
819 [ Sequelize.Op.iLike ]: '%' + value + '%'
820 })
821 }
822 ]
823 }
824 } 806 }
825 807
826 const serverActor = await getServerActor() 808 const serverActor = await getServerActor()
diff --git a/server/tests/utils/videos/videos.ts b/server/tests/utils/videos/videos.ts
index 74bf7354e..a42d0f043 100644
--- a/server/tests/utils/videos/videos.ts
+++ b/server/tests/utils/videos/videos.ts
@@ -248,9 +248,9 @@ function removeVideo (url: string, token: string, id: number | string, expectedS
248} 248}
249 249
250function searchVideo (url: string, search: string) { 250function searchVideo (url: string, search: string) {
251 const path = '/api/v1/videos' 251 const path = '/api/v1/search/videos'
252 const req = request(url) 252 const req = request(url)
253 .get(path + '/search') 253 .get(path)
254 .query({ search }) 254 .query({ search })
255 .set('Accept', 'application/json') 255 .set('Accept', 'application/json')
256 256
@@ -271,10 +271,10 @@ function searchVideoWithToken (url: string, search: string, token: string) {
271} 271}
272 272
273function searchVideoWithPagination (url: string, search: string, start: number, count: number, sort?: string) { 273function searchVideoWithPagination (url: string, search: string, start: number, count: number, sort?: string) {
274 const path = '/api/v1/videos' 274 const path = '/api/v1/search/videos'
275 275
276 const req = request(url) 276 const req = request(url)
277 .get(path + '/search') 277 .get(path)
278 .query({ start }) 278 .query({ start })
279 .query({ search }) 279 .query({ search })
280 .query({ count }) 280 .query({ count })
@@ -287,10 +287,10 @@ function searchVideoWithPagination (url: string, search: string, start: number,
287} 287}
288 288
289function searchVideoWithSort (url: string, search: string, sort: string) { 289function searchVideoWithSort (url: string, search: string, sort: string) {
290 const path = '/api/v1/videos' 290 const path = '/api/v1/search/videos'
291 291
292 return request(url) 292 return request(url)
293 .get(path + '/search') 293 .get(path)
294 .query({ search }) 294 .query({ search })
295 .query({ sort }) 295 .query({ sort })
296 .set('Accept', 'application/json') 296 .set('Accept', 'application/json')