From 57c36b277e68b764dd34cb2e449f6e2ca3d1e9b6 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 19 Jul 2018 16:17:54 +0200 Subject: Begin advanced search --- server/controllers/api/index.ts | 2 + server/controllers/api/search.ts | 43 +++++++++++++++ server/controllers/api/videos/index.ts | 23 --------- server/controllers/client.ts | 10 +++- server/helpers/database-utils.ts | 2 +- server/initializers/constants.ts | 4 +- server/initializers/database.ts | 43 +++++++++++++++ server/middlewares/sort.ts | 7 +++ server/middlewares/validators/index.ts | 1 + server/middlewares/validators/search.ts | 22 ++++++++ server/middlewares/validators/sort.ts | 3 ++ server/middlewares/validators/videos.ts | 15 +----- server/models/activitypub/actor.ts | 6 +++ server/models/utils.ts | 52 ++++++++++++++++++- server/models/video/video.ts | 92 +++++++++++++-------------------- server/tests/utils/videos/videos.ts | 12 ++--- 16 files changed, 233 insertions(+), 104 deletions(-) create mode 100644 server/controllers/api/search.ts create mode 100644 server/middlewares/validators/search.ts (limited to 'server') 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' import { badRequest } from '../../helpers/express-utils' import { videoChannelRouter } from './video-channel' import * as cors from 'cors' +import { searchRouter } from './search' const apiRouter = express.Router() @@ -26,6 +27,7 @@ apiRouter.use('/accounts', accountsRouter) apiRouter.use('/video-channels', videoChannelRouter) apiRouter.use('/videos', videosRouter) apiRouter.use('/jobs', jobsRouter) +apiRouter.use('/search', searchRouter) apiRouter.use('/ping', pong) apiRouter.use('/*', badRequest) 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 @@ +import * as express from 'express' +import { isNSFWHidden } from '../../helpers/express-utils' +import { getFormattedObjects } from '../../helpers/utils' +import { VideoModel } from '../../models/video/video' +import { + asyncMiddleware, + optionalAuthenticate, + paginationValidator, + searchValidator, + setDefaultPagination, + setDefaultSearchSort, + videosSearchSortValidator +} from '../../middlewares' + +const searchRouter = express.Router() + +searchRouter.get('/videos', + paginationValidator, + setDefaultPagination, + videosSearchSortValidator, + setDefaultSearchSort, + optionalAuthenticate, + searchValidator, + asyncMiddleware(searchVideos) +) + +// --------------------------------------------------------------------------- + +export { searchRouter } + +// --------------------------------------------------------------------------- + +async function searchVideos (req: express.Request, res: express.Response) { + const resultList = await VideoModel.searchAndPopulateAccountAndServer( + req.query.search as string, + req.query.start as number, + req.query.count as number, + req.query.sort as string, + isNSFWHidden(res) + ) + + return res.json(getFormattedObjects(resultList.data, resultList.total)) +} 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 { videosAddValidator, videosGetValidator, videosRemoveValidator, - videosSearchValidator, videosSortValidator, videosUpdateValidator } from '../../../middlewares' @@ -50,7 +49,6 @@ import { blacklistRouter } from './blacklist' import { videoCommentRouter } from './comment' import { rateVideoRouter } from './rate' import { VideoFilter } from '../../../../shared/models/videos/video-query.type' -import { VideoSortField } from '../../../../client/src/app/shared/video/sort-field.type' import { createReqFiles, isNSFWHidden } from '../../../helpers/express-utils' import { ScheduleVideoUpdateModel } from '../../../models/video/schedule-video-update' import { videoCaptionsRouter } from './captions' @@ -94,15 +92,6 @@ videosRouter.get('/', optionalAuthenticate, asyncMiddleware(listVideos) ) -videosRouter.get('/search', - videosSearchValidator, - paginationValidator, - videosSortValidator, - setDefaultSort, - setDefaultPagination, - optionalAuthenticate, - asyncMiddleware(searchVideos) -) videosRouter.put('/:id', authenticate, reqVideoFileUpdate, @@ -432,15 +421,3 @@ async function removeVideo (req: express.Request, res: express.Response) { return res.type('json').status(204).end() } - -async function searchVideos (req: express.Request, res: express.Response, next: express.NextFunction) { - const resultList = await VideoModel.searchAndPopulateAccountAndServer( - req.query.search as string, - req.query.start as number, - req.query.count as number, - req.query.sort as VideoSortField, - isNSFWHidden(res) - ) - - return res.json(getFormattedObjects(resultList.data, resultList.total)) -} 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' import { asyncMiddleware } from '../middlewares' import { buildFileLocale, getCompleteLocale, is18nLocale, LOCALE_FILES } from '../../shared/models/i18n/i18n' import { ClientHtml } from '../lib/client-html' +import { logger } from '../helpers/logger' const clientsRouter = express.Router() @@ -66,9 +67,14 @@ clientsRouter.use('/client/*', (req: express.Request, res: express.Response, nex // Always serve index client page (the client is a single page application, let it handle routing) // Try to provide the right language index.html -clientsRouter.use('/(:language)?', function (req, res) { +clientsRouter.use('/(:language)?', async function (req, res) { if (req.accepts(ACCEPT_HEADERS) === 'html') { - return generateHTMLPage(req, res, req.params.language) + try { + await generateHTMLPage(req, res, req.params.language) + return + } catch (err) { + logger.error('Cannot generate HTML page.', err) + } } 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 @@ import * as retry from 'async/retry' import * as Bluebird from 'bluebird' -import { Model } from 'sequelize-typescript' +import { Model, Sequelize } from 'sequelize-typescript' import { logger } from './logger' function retryTransactionWrapper ( 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 = { VIDEO_COMMENT_THREADS: [ 'createdAt' ], BLACKLISTS: [ 'id', 'name', 'duration', 'views', 'likes', 'dislikes', 'uuid', 'createdAt' ], FOLLOWERS: [ 'createdAt' ], - FOLLOWING: [ 'createdAt' ] + FOLLOWING: [ 'createdAt' ], + + VIDEOS_SEARCH: [ 'bestmatch', 'name', 'duration', 'createdAt', 'publishedAt', 'views', 'likes' ] } const 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) { ScheduleVideoUpdateModel ]) + // Check extensions exist in the database + await checkPostgresExtensions() + + // Create custom PostgreSQL functions + await createFunctions() + + await sequelizeTypescript.query('CREATE EXTENSION IF NOT EXISTS pg_trgm', { raw: true }) + if (!silent) logger.info('Database %s is ready.', dbname) return @@ -91,3 +99,38 @@ export { initDatabaseModels, sequelizeTypescript } + +// --------------------------------------------------------------------------- + +async function checkPostgresExtensions () { + const extensions = [ + 'pg_trgm', + 'unaccent' + ] + + for (const extension of extensions) { + const query = `SELECT true AS enabled FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;` + const [ res ] = await sequelizeTypescript.query(query, { raw: true }) + + if (!res || res.length === 0 || res[ 0 ][ 'enabled' ] !== true) { + // Try to create the extension ourself + try { + await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true }) + + } catch { + const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` + + `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.` + throw new Error(errorMessage) + } + } + } +} + +async function createFunctions () { + const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(varchar) + RETURNS text AS $$ + SELECT unaccent($1) + $$ LANGUAGE sql IMMUTABLE;` + + return sequelizeTypescript.query(query, { raw: true }) +} 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 return next() } +function setDefaultSearchSort (req: express.Request, res: express.Response, next: express.NextFunction) { + if (!req.query.sort) req.query.sort = '-bestmatch' + + return next() +} + function setBlacklistSort (req: express.Request, res: express.Response, next: express.NextFunction) { let newSort: SortType = { sortModel: undefined, sortValue: undefined } @@ -33,5 +39,6 @@ function setBlacklistSort (req: express.Request, res: express.Response, next: ex export { setDefaultSort, + setDefaultSearchSort, setBlacklistSort } 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' export * from './video-blacklist' export * from './video-channels' export * from './webfinger' +export * 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 @@ +import * as express from 'express' +import { areValidationErrors } from './utils' +import { logger } from '../../helpers/logger' +import { query } from 'express-validator/check' + +const searchValidator = [ + query('search').not().isEmpty().withMessage('Should have a valid search'), + + (req: express.Request, res: express.Response, next: express.NextFunction) => { + logger.debug('Checking search parameters', { parameters: req.params }) + + if (areValidationErrors(req, res)) return + + return next() + } +] + +// --------------------------------------------------------------------------- + +export { + searchValidator +} 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 const SORTABLE_JOBS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.JOBS) const SORTABLE_VIDEO_ABUSES_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEO_ABUSES) const SORTABLE_VIDEOS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEOS) +const SORTABLE_VIDEOS_SEARCH_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEOS_SEARCH) const SORTABLE_VIDEO_COMMENT_THREADS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEO_COMMENT_THREADS) const SORTABLE_BLACKLISTS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.BLACKLISTS) const SORTABLE_VIDEO_CHANNELS_COLUMNS = createSortableColumns(SORTABLE_COLUMNS.VIDEO_CHANNELS) @@ -18,6 +19,7 @@ const accountsSortValidator = checkSort(SORTABLE_ACCOUNTS_COLUMNS) const jobsSortValidator = checkSort(SORTABLE_JOBS_COLUMNS) const videoAbusesSortValidator = checkSort(SORTABLE_VIDEO_ABUSES_COLUMNS) const videosSortValidator = checkSort(SORTABLE_VIDEOS_COLUMNS) +const videosSearchSortValidator = checkSort(SORTABLE_VIDEOS_SEARCH_COLUMNS) const videoCommentThreadsSortValidator = checkSort(SORTABLE_VIDEO_COMMENT_THREADS_COLUMNS) const blacklistSortValidator = checkSort(SORTABLE_BLACKLISTS_COLUMNS) const videoChannelsSortValidator = checkSort(SORTABLE_VIDEO_CHANNELS_COLUMNS) @@ -30,6 +32,7 @@ export { usersSortValidator, videoAbusesSortValidator, videoChannelsSortValidator, + videosSearchSortValidator, videosSortValidator, blacklistSortValidator, 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 @@ import * as express from 'express' import 'express-validator' -import { body, param, query, ValidationChain } from 'express-validator/check' +import { body, param, ValidationChain } from 'express-validator/check' import { UserRight, VideoPrivacy } from '../../../shared' import { isBooleanValid, @@ -172,18 +172,6 @@ const videosRemoveValidator = [ } ] -const videosSearchValidator = [ - query('search').not().isEmpty().withMessage('Should have a valid search'), - - (req: express.Request, res: express.Response, next: express.NextFunction) => { - logger.debug('Checking videosSearch parameters', { parameters: req.params }) - - if (areValidationErrors(req, res)) return - - return next() - } -] - const videoAbuseReportValidator = [ param('id').custom(isIdOrUUIDValid).not().isEmpty().withMessage('Should have a valid id'), body('reason').custom(isVideoAbuseReasonValid).withMessage('Should have a valid reason'), @@ -240,7 +228,6 @@ export { videosUpdateValidator, videosGetValidator, videosRemoveValidator, - videosSearchValidator, videosShareValidator, 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 { }, { fields: [ 'inboxUrl', 'sharedInboxUrl' ] + }, + { + fields: [ 'serverId' ] + }, + { + fields: [ 'avatarId' ] } ] }) 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 @@ // Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ] +import { Sequelize } from 'sequelize-typescript' + function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) { - let field: string + let field: any let direction: 'ASC' | 'DESC' if (value.substring(0, 1) === '-') { @@ -11,6 +13,9 @@ function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) { field = value } + // Alias + if (field.toLowerCase() === 'bestmatch') field = Sequelize.col('similarity') + return [ [ field, direction ], lastSort ] } @@ -27,10 +32,53 @@ function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldN } } +function buildTrigramSearchIndex (indexName: string, attribute: string) { + return { + name: indexName, + fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + '))') as any ], + using: 'gin', + operator: 'gin_trgm_ops' + } +} + +function createSimilarityAttribute (col: string, value: string) { + return Sequelize.fn( + 'similarity', + + searchTrigramNormalizeCol(col), + + searchTrigramNormalizeValue(value) + ) +} + +function createSearchTrigramQuery (col: string, value: string) { + return { + [ Sequelize.Op.or ]: [ + // FIXME: use word_similarity instead of just similarity? + Sequelize.where(searchTrigramNormalizeCol(col), ' % ', searchTrigramNormalizeValue(value)), + + Sequelize.where(searchTrigramNormalizeCol(col), ' LIKE ', searchTrigramNormalizeValue(`%${value}%`)) + ] + } +} + // --------------------------------------------------------------------------- export { getSort, getSortOnModel, - throwIfNotValid + createSimilarityAttribute, + throwIfNotValid, + buildTrigramSearchIndex, + createSearchTrigramQuery +} + +// --------------------------------------------------------------------------- + +function searchTrigramNormalizeValue (value: string) { + return Sequelize.fn('lower', Sequelize.fn('unaccent', value)) +} + +function searchTrigramNormalizeCol (col: string) { + return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col))) } 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' import { ActorModel } from '../activitypub/actor' import { AvatarModel } from '../avatar/avatar' import { ServerModel } from '../server/server' -import { getSort, throwIfNotValid } from '../utils' +import { buildTrigramSearchIndex, createSearchTrigramQuery, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils' import { TagModel } from './tag' import { VideoAbuseModel } from './video-abuse' import { VideoChannelModel } from './video-channel' @@ -94,6 +94,37 @@ import { VideoTagModel } from './video-tag' import { ScheduleVideoUpdateModel } from './schedule-video-update' import { VideoCaptionModel } from './video-caption' +// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation +const indexes: Sequelize.DefineIndexesOptions[] = [ + buildTrigramSearchIndex('video_name_trigram', 'name'), + + { + fields: [ 'createdAt' ] + }, + { + fields: [ 'duration' ] + }, + { + fields: [ 'views' ] + }, + { + fields: [ 'likes' ] + }, + { + fields: [ 'uuid' ] + }, + { + fields: [ 'channelId' ] + }, + { + fields: [ 'id', 'privacy', 'state', 'waitTranscoding' ] + }, + { + fields: [ 'url'], + unique: true + } +] + export enum ScopeNames { AVAILABLE_FOR_LIST = 'AVAILABLE_FOR_LIST', WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS', @@ -309,36 +340,7 @@ export enum ScopeNames { }) @Table({ tableName: 'video', - indexes: [ - { - fields: [ 'name' ] - }, - { - fields: [ 'createdAt' ] - }, - { - fields: [ 'duration' ] - }, - { - fields: [ 'views' ] - }, - { - fields: [ 'likes' ] - }, - { - fields: [ 'uuid' ] - }, - { - fields: [ 'channelId' ] - }, - { - fields: [ 'id', 'privacy', 'state', 'waitTranscoding' ] - }, - { - fields: [ 'url'], - unique: true - } - ] + indexes }) export class VideoModel extends Model { @@ -794,33 +796,13 @@ export class VideoModel extends Model { static async searchAndPopulateAccountAndServer (value: string, start: number, count: number, sort: string, hideNSFW: boolean) { const query: IFindOptions = { + attributes: { + include: [ createSimilarityAttribute('VideoModel.name', value) ] + }, offset: start, limit: count, order: getSort(sort), - where: { - [Sequelize.Op.or]: [ - { - name: { - [ Sequelize.Op.iLike ]: '%' + value + '%' - } - }, - { - preferredUsernameChannel: Sequelize.where(Sequelize.col('VideoChannel->Actor.preferredUsername'), { - [ Sequelize.Op.iLike ]: '%' + value + '%' - }) - }, - { - preferredUsernameAccount: Sequelize.where(Sequelize.col('VideoChannel->Account->Actor.preferredUsername'), { - [ Sequelize.Op.iLike ]: '%' + value + '%' - }) - }, - { - host: Sequelize.where(Sequelize.col('VideoChannel->Account->Actor->Server.host'), { - [ Sequelize.Op.iLike ]: '%' + value + '%' - }) - } - ] - } + where: createSearchTrigramQuery('VideoModel.name', value) } 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 } function searchVideo (url: string, search: string) { - const path = '/api/v1/videos' + const path = '/api/v1/search/videos' const req = request(url) - .get(path + '/search') + .get(path) .query({ search }) .set('Accept', 'application/json') @@ -271,10 +271,10 @@ function searchVideoWithToken (url: string, search: string, token: string) { } function searchVideoWithPagination (url: string, search: string, start: number, count: number, sort?: string) { - const path = '/api/v1/videos' + const path = '/api/v1/search/videos' const req = request(url) - .get(path + '/search') + .get(path) .query({ start }) .query({ search }) .query({ count }) @@ -287,10 +287,10 @@ function searchVideoWithPagination (url: string, search: string, start: number, } function searchVideoWithSort (url: string, search: string, sort: string) { - const path = '/api/v1/videos' + const path = '/api/v1/search/videos' return request(url) - .get(path + '/search') + .get(path) .query({ search }) .query({ sort }) .set('Accept', 'application/json') -- cgit v1.2.3