From 57c36b277e68b764dd34cb2e449f6e2ca3d1e9b6 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 19 Jul 2018 16:17:54 +0200 Subject: [PATCH] Begin advanced search --- .travis.yml | 6 -- client/src/app/app.module.ts | 2 + client/src/app/header/header.component.ts | 2 +- client/src/app/search/index.ts | 3 + .../src/app/search/search-routing.module.ts | 23 +++++ client/src/app/search/search.component.html | 19 ++++ client/src/app/search/search.component.scss | 93 +++++++++++++++++++ client/src/app/search/search.component.ts | 93 +++++++++++++++++++ client/src/app/search/search.module.ts | 25 +++++ client/src/app/search/search.service.ts | 46 +++++++++ client/src/app/shared/shared.module.ts | 9 +- .../video/video-miniature.component.html | 2 +- .../video/video-thumbnail.component.html | 2 +- client/src/app/shared/video/video.service.ts | 51 +++------- client/src/app/videos/video-list/index.ts | 2 +- .../video-list/video-search.component.ts | 77 --------------- .../src/app/videos/videos-routing.module.ts | 12 +-- client/src/app/videos/videos.module.ts | 4 +- scripts/clean/server/test.sh | 8 +- server.ts | 2 +- 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 +-- support/doc/docker.md | 4 + support/doc/production.md | 9 +- 38 files changed, 584 insertions(+), 247 deletions(-) create mode 100644 client/src/app/search/index.ts create mode 100644 client/src/app/search/search-routing.module.ts create mode 100644 client/src/app/search/search.component.html create mode 100644 client/src/app/search/search.component.scss create mode 100644 client/src/app/search/search.component.ts create mode 100644 client/src/app/search/search.module.ts create mode 100644 client/src/app/search/search.service.ts delete mode 100644 client/src/app/videos/video-list/video-search.component.ts create mode 100644 server/controllers/api/search.ts create mode 100644 server/middlewares/validators/search.ts diff --git a/.travis.yml b/.travis.yml index 51892e504..042719529 100644 --- a/.travis.yml +++ b/.travis.yml @@ -29,12 +29,6 @@ before_script: - cp ffmpeg-*-64bit-static/{ffmpeg,ffprobe,ffserver} $HOME/bin - export PATH=$HOME/bin:$PATH - export NODE_TEST_IMAGE=true - - psql -c 'create database peertube_test1;' -U postgres - - psql -c 'create database peertube_test2;' -U postgres - - psql -c 'create database peertube_test3;' -U postgres - - psql -c 'create database peertube_test4;' -U postgres - - psql -c 'create database peertube_test5;' -U postgres - - psql -c 'create database peertube_test6;' -U postgres - psql -c "create user peertube with password 'peertube';" -U postgres matrix: diff --git a/client/src/app/app.module.ts b/client/src/app/app.module.ts index 48886fd4e..9d655c523 100644 --- a/client/src/app/app.module.ts +++ b/client/src/app/app.module.ts @@ -18,6 +18,7 @@ import { VideosModule } from './videos' import { buildFileLocale, getCompleteLocale, isDefaultLocale } from '../../../shared/models/i18n' import { getDevLocale, isOnDevLocale } from '@app/shared/i18n/i18n-utils' import { LanguageChooserComponent } from '@app/menu/language-chooser.component' +import { SearchModule } from '@app/search' export function metaFactory (serverService: ServerService): MetaLoader { return new MetaStaticLoader({ @@ -52,6 +53,7 @@ export function metaFactory (serverService: ServerService): MetaLoader { LoginModule, ResetPasswordModule, SignupModule, + SearchModule, SharedModule, VideosModule, diff --git a/client/src/app/header/header.component.ts b/client/src/app/header/header.component.ts index 0e999fbb1..f73d40947 100644 --- a/client/src/app/header/header.component.ts +++ b/client/src/app/header/header.component.ts @@ -24,7 +24,7 @@ export class HeaderComponent implements OnInit { } doSearch () { - this.router.navigate([ '/videos', 'search' ], { + this.router.navigate([ '/search' ], { queryParams: { search: this.searchValue } }) } diff --git a/client/src/app/search/index.ts b/client/src/app/search/index.ts new file mode 100644 index 000000000..40f4e021f --- /dev/null +++ b/client/src/app/search/index.ts @@ -0,0 +1,3 @@ +export * from './search-routing.module' +export * from './search.component' +export * from './search.module' diff --git a/client/src/app/search/search-routing.module.ts b/client/src/app/search/search-routing.module.ts new file mode 100644 index 000000000..0ac9e6b57 --- /dev/null +++ b/client/src/app/search/search-routing.module.ts @@ -0,0 +1,23 @@ +import { NgModule } from '@angular/core' +import { RouterModule, Routes } from '@angular/router' +import { MetaGuard } from '@ngx-meta/core' +import { SearchComponent } from '@app/search/search.component' + +const searchRoutes: Routes = [ + { + path: 'search', + component: SearchComponent, + canActivate: [ MetaGuard ], + data: { + meta: { + title: 'Search' + } + } + } +] + +@NgModule({ + imports: [ RouterModule.forChild(searchRoutes) ], + exports: [ RouterModule ] +}) +export class SearchRoutingModule {} diff --git a/client/src/app/search/search.component.html b/client/src/app/search/search.component.html new file mode 100644 index 000000000..b8c4d7dc5 --- /dev/null +++ b/client/src/app/search/search.component.html @@ -0,0 +1,19 @@ +
+ No results found +
+ +
+
+ {{ pagination.totalItems | myNumberFormatter }} results for {{ currentSearch }} +
+ +
+ + +
+ {{ video.name }} + {{ video.publishedAt | myFromNow }} - {{ video.views | myNumberFormatter }} views + +
+
+
diff --git a/client/src/app/search/search.component.scss b/client/src/app/search/search.component.scss new file mode 100644 index 000000000..06e3c9542 --- /dev/null +++ b/client/src/app/search/search.component.scss @@ -0,0 +1,93 @@ +@import '_variables'; +@import '_mixins'; + +.no-result { + height: 70vh; + display: flex; + align-items: center; + justify-content: center; + font-size: 16px; + font-weight: $font-semibold; +} + +.search-result { + margin-left: 40px; + margin-top: 40px; + + .results-counter { + font-size: 15px; + padding-bottom: 20px; + margin-bottom: 30px; + border-bottom: 1px solid #DADADA; + + .search-value { + font-weight: $font-semibold; + } + } + + .entry { + display: flex; + min-height: 130px; + padding-bottom: 20px; + margin-bottom: 20px; + + &.video { + + my-video-thumbnail { + margin-right: 10px; + } + + .video-info { + flex-grow: 1; + + .video-info-name { + @include disable-default-a-behaviour; + + color: #000; + display: block; + width: fit-content; + font-size: 18px; + font-weight: $font-semibold; + } + + .video-info-date-views { + font-size: 14px; + } + + .video-info-account { + @include disable-default-a-behaviour; + + display: block; + width: fit-content; + overflow: hidden; + text-overflow: ellipsis; + white-space: nowrap; + font-size: 14px; + color: #585858; + + &:hover { + color: #303030; + } + } + } + } + } +} + +@media screen and (max-width: 800px) { + .entry { + flex-direction: column; + height: auto; + text-align: center; + + &.video { + .video-info-name { + margin: auto; + } + + my-video-thumbnail { + margin-right: 0; + } + } + } +} diff --git a/client/src/app/search/search.component.ts b/client/src/app/search/search.component.ts new file mode 100644 index 000000000..be1cb3689 --- /dev/null +++ b/client/src/app/search/search.component.ts @@ -0,0 +1,93 @@ +import { Component, OnDestroy, OnInit } from '@angular/core' +import { ActivatedRoute } from '@angular/router' +import { RedirectService } from '@app/core' +import { NotificationsService } from 'angular2-notifications' +import { Subscription } from 'rxjs' +import { SearchService } from '@app/search/search.service' +import { ComponentPagination } from '@app/shared/rest/component-pagination.model' +import { I18n } from '@ngx-translate/i18n-polyfill' +import { Video } from '../../../../shared' +import { MetaService } from '@ngx-meta/core' + +@Component({ + selector: 'my-search', + styleUrls: [ './search.component.scss' ], + templateUrl: './search.component.html' +}) +export class SearchComponent implements OnInit, OnDestroy { + videos: Video[] = [] + pagination: ComponentPagination = { + currentPage: 1, + itemsPerPage: 10, // It's per object type (so 10 videos, 10 video channels etc) + totalItems: null + } + + private subActivatedRoute: Subscription + private currentSearch: string + + constructor ( + private i18n: I18n, + private route: ActivatedRoute, + private metaService: MetaService, + private redirectService: RedirectService, + private notificationsService: NotificationsService, + private searchService: SearchService + ) { } + + ngOnInit () { + this.subActivatedRoute = this.route.queryParams.subscribe( + queryParams => { + const querySearch = queryParams['search'] + + if (!querySearch) return this.redirectService.redirectToHomepage() + if (querySearch === this.currentSearch) return + + this.currentSearch = querySearch + this.updateTitle() + + this.reload() + }, + + err => this.notificationsService.error('Error', err.text) + ) + } + + ngOnDestroy () { + if (this.subActivatedRoute) this.subActivatedRoute.unsubscribe() + } + + search () { + return this.searchService.searchVideos(this.currentSearch, this.pagination) + .subscribe( + ({ videos, totalVideos }) => { + this.videos = this.videos.concat(videos) + this.pagination.totalItems = totalVideos + }, + + error => { + this.notificationsService.error(this.i18n('Error'), error.message) + } + ) + } + + onNearOfBottom () { + // Last page + if (this.pagination.totalItems <= (this.pagination.currentPage * this.pagination.itemsPerPage)) return + + this.pagination.currentPage += 1 + this.search() + } + + private reload () { + this.pagination.currentPage = 1 + this.pagination.totalItems = null + + this.videos = [] + + this.search() + } + + private updateTitle () { + this.metaService.setTitle(this.i18n('Search') + ' ' + this.currentSearch) + } +} diff --git a/client/src/app/search/search.module.ts b/client/src/app/search/search.module.ts new file mode 100644 index 000000000..c6ec74d20 --- /dev/null +++ b/client/src/app/search/search.module.ts @@ -0,0 +1,25 @@ +import { NgModule } from '@angular/core' +import { SharedModule } from '../shared' +import { SearchComponent } from '@app/search/search.component' +import { SearchService } from '@app/search/search.service' +import { SearchRoutingModule } from '@app/search/search-routing.module' + +@NgModule({ + imports: [ + SearchRoutingModule, + SharedModule + ], + + declarations: [ + SearchComponent + ], + + exports: [ + SearchComponent + ], + + providers: [ + SearchService + ] +}) +export class SearchModule { } diff --git a/client/src/app/search/search.service.ts b/client/src/app/search/search.service.ts new file mode 100644 index 000000000..02d5f5915 --- /dev/null +++ b/client/src/app/search/search.service.ts @@ -0,0 +1,46 @@ +import { catchError, switchMap } from 'rxjs/operators' +import { HttpClient, HttpParams } from '@angular/common/http' +import { Injectable } from '@angular/core' +import { Observable } from 'rxjs' +import { ComponentPagination } from '@app/shared/rest/component-pagination.model' +import { VideoService } from '@app/shared/video/video.service' +import { RestExtractor, RestService } from '@app/shared' +import { environment } from 'environments/environment' +import { ResultList, Video } from '../../../../shared' +import { Video as VideoServerModel } from '@app/shared/video/video.model' + +export type SearchResult = { + videosResult: { totalVideos: number, videos: Video[] } +} + +@Injectable() +export class SearchService { + static BASE_SEARCH_URL = environment.apiUrl + '/api/v1/search/' + + constructor ( + private authHttp: HttpClient, + private restExtractor: RestExtractor, + private restService: RestService, + private videoService: VideoService + ) {} + + searchVideos ( + search: string, + componentPagination: ComponentPagination + ): Observable<{ videos: Video[], totalVideos: number }> { + const url = SearchService.BASE_SEARCH_URL + 'videos' + + const pagination = this.restService.componentPaginationToRestPagination(componentPagination) + + let params = new HttpParams() + params = this.restService.addRestGetParams(params, pagination) + params = params.append('search', search) + + return this.authHttp + .get>(url, { params }) + .pipe( + switchMap(res => this.videoService.extractVideos(res)), + catchError(err => this.restExtractor.handleError(err)) + ) + } +} diff --git a/client/src/app/shared/shared.module.ts b/client/src/app/shared/shared.module.ts index fdfb90600..99df61cdb 100644 --- a/client/src/app/shared/shared.module.ts +++ b/client/src/app/shared/shared.module.ts @@ -37,9 +37,14 @@ import { I18n } from '@ngx-translate/i18n-polyfill' import { FormValidatorService } from '@app/shared/forms/form-validators/form-validator.service' import { CustomConfigValidatorsService, - LoginValidatorsService, ReactiveFileComponent, + LoginValidatorsService, + ReactiveFileComponent, ResetPasswordValidatorsService, - UserValidatorsService, VideoAbuseValidatorsService, VideoChannelValidatorsService, VideoCommentValidatorsService, VideoValidatorsService + UserValidatorsService, + VideoAbuseValidatorsService, + VideoChannelValidatorsService, + VideoCommentValidatorsService, + VideoValidatorsService } from '@app/shared/forms' import { I18nPrimengCalendarService } from '@app/shared/i18n/i18n-primeng-calendar' import { ScreenService } from '@app/shared/misc/screen.service' diff --git a/client/src/app/shared/video/video-miniature.component.html b/client/src/app/shared/video/video-miniature.component.html index 20020e2a8..3010e5ccc 100644 --- a/client/src/app/shared/video/video-miniature.component.html +++ b/client/src/app/shared/video/video-miniature.component.html @@ -3,7 +3,7 @@
{{ video.name }} diff --git a/client/src/app/shared/video/video-thumbnail.component.html b/client/src/app/shared/video/video-thumbnail.component.html index 971f352ba..4909cf3f1 100644 --- a/client/src/app/shared/video/video-thumbnail.component.html +++ b/client/src/app/shared/video/video-thumbnail.component.html @@ -2,7 +2,7 @@ [routerLink]="['/videos/watch', video.uuid]" [attr.title]="video.name" class="video-thumbnail" > - +
{{ video.durationLabel }} diff --git a/client/src/app/shared/video/video.service.ts b/client/src/app/shared/video/video.service.ts index b4c1f10f9..f316d31ea 100644 --- a/client/src/app/shared/video/video.service.ts +++ b/client/src/app/shared/video/video.service.ts @@ -231,27 +231,6 @@ export class VideoService { return this.buildBaseFeedUrls(params) } - searchVideos ( - search: string, - videoPagination: ComponentPagination, - sort: VideoSortField - ): Observable<{ videos: Video[], totalVideos: number }> { - const url = VideoService.BASE_VIDEO_URL + 'search' - - const pagination = this.restService.componentPaginationToRestPagination(videoPagination) - - let params = new HttpParams() - params = this.restService.addRestGetParams(params, pagination, sort) - params = params.append('search', search) - - return this.authHttp - .get>(url, { params }) - .pipe( - switchMap(res => this.extractVideos(res)), - catchError(err => this.restExtractor.handleError(err)) - ) - } - removeVideo (id: number) { return this.authHttp .delete(VideoService.BASE_VIDEO_URL + id) @@ -289,21 +268,7 @@ export class VideoService { .pipe(catchError(err => this.restExtractor.handleError(err))) } - private setVideoRate (id: number, rateType: VideoRateType) { - const url = VideoService.BASE_VIDEO_URL + id + '/rate' - const body: UserVideoRateUpdate = { - rating: rateType - } - - return this.authHttp - .put(url, body) - .pipe( - map(this.restExtractor.extractDataBool), - catchError(err => this.restExtractor.handleError(err)) - ) - } - - private extractVideos (result: ResultList) { + extractVideos (result: ResultList) { return this.serverService.localeObservable .pipe( map(translations => { @@ -319,4 +284,18 @@ export class VideoService { }) ) } + + private setVideoRate (id: number, rateType: VideoRateType) { + const url = VideoService.BASE_VIDEO_URL + id + '/rate' + const body: UserVideoRateUpdate = { + rating: rateType + } + + return this.authHttp + .put(url, body) + .pipe( + map(this.restExtractor.extractDataBool), + catchError(err => this.restExtractor.handleError(err)) + ) + } } diff --git a/client/src/app/videos/video-list/index.ts b/client/src/app/videos/video-list/index.ts index 5e7c7886c..5f7c8bd48 100644 --- a/client/src/app/videos/video-list/index.ts +++ b/client/src/app/videos/video-list/index.ts @@ -1,3 +1,3 @@ +export * from './video-local.component' export * from './video-recently-added.component' export * from './video-trending.component' -export * from './video-search.component' diff --git a/client/src/app/videos/video-list/video-search.component.ts b/client/src/app/videos/video-list/video-search.component.ts deleted file mode 100644 index 33ed3f00e..000000000 --- a/client/src/app/videos/video-list/video-search.component.ts +++ /dev/null @@ -1,77 +0,0 @@ -import { Component, OnDestroy, OnInit } from '@angular/core' -import { ActivatedRoute, Router } from '@angular/router' -import { Location } from '@angular/common' -import { RedirectService } from '@app/core' -import { immutableAssign } from '@app/shared/misc/utils' -import { NotificationsService } from 'angular2-notifications' -import { Subscription } from 'rxjs' -import { AuthService } from '../../core/auth' -import { AbstractVideoList } from '../../shared/video/abstract-video-list' -import { VideoService } from '../../shared/video/video.service' -import { I18n } from '@ngx-translate/i18n-polyfill' -import { ScreenService } from '@app/shared/misc/screen.service' - -@Component({ - selector: 'my-videos-search', - styleUrls: [ '../../shared/video/abstract-video-list.scss' ], - templateUrl: '../../shared/video/abstract-video-list.html' -}) -export class VideoSearchComponent extends AbstractVideoList implements OnInit, OnDestroy { - titlePage: string - currentRoute = '/videos/search' - loadOnInit = false - - protected otherRouteParams = { - search: '' - } - private subActivatedRoute: Subscription - - constructor ( - protected router: Router, - protected route: ActivatedRoute, - protected notificationsService: NotificationsService, - protected authService: AuthService, - protected location: Location, - protected i18n: I18n, - protected screenService: ScreenService, - private videoService: VideoService, - private redirectService: RedirectService - ) { - super() - - this.titlePage = i18n('Search') - } - - ngOnInit () { - super.ngOnInit() - - this.subActivatedRoute = this.route.queryParams.subscribe( - queryParams => { - const querySearch = queryParams['search'] - - if (!querySearch) return this.redirectService.redirectToHomepage() - if (this.otherRouteParams.search === querySearch) return - - this.otherRouteParams.search = querySearch - this.reloadVideos() - }, - - err => this.notificationsService.error('Error', err.text) - ) - } - - ngOnDestroy () { - super.ngOnDestroy() - - if (this.subActivatedRoute) this.subActivatedRoute.unsubscribe() - } - - getVideosObservable (page: number) { - const newPagination = immutableAssign(this.pagination, { currentPage: page }) - return this.videoService.searchVideos(this.otherRouteParams.search, newPagination, this.sort) - } - - generateSyndicationList () { - throw new Error('Search does not support syndication.') - } -} diff --git a/client/src/app/videos/videos-routing.module.ts b/client/src/app/videos/videos-routing.module.ts index da786c0f9..538a43c6d 100644 --- a/client/src/app/videos/videos-routing.module.ts +++ b/client/src/app/videos/videos-routing.module.ts @@ -1,8 +1,7 @@ import { NgModule } from '@angular/core' -import { RouterModule, Routes, UrlSegment } from '@angular/router' +import { RouterModule, Routes } from '@angular/router' import { VideoLocalComponent } from '@app/videos/video-list/video-local.component' import { MetaGuard } from '@ngx-meta/core' -import { VideoSearchComponent } from './video-list' import { VideoRecentlyAddedComponent } from './video-list/video-recently-added.component' import { VideoTrendingComponent } from './video-list/video-trending.component' import { VideosComponent } from './videos.component' @@ -45,15 +44,6 @@ const videosRoutes: Routes = [ } } }, - { - path: 'search', - component: VideoSearchComponent, - data: { - meta: { - title: 'Search videos' - } - } - }, { path: 'upload', loadChildren: 'app/videos/+video-edit/video-add.module#VideoAddModule', diff --git a/client/src/app/videos/videos.module.ts b/client/src/app/videos/videos.module.ts index 7c3d457b3..c38257e08 100644 --- a/client/src/app/videos/videos.module.ts +++ b/client/src/app/videos/videos.module.ts @@ -1,7 +1,6 @@ import { NgModule } from '@angular/core' import { VideoLocalComponent } from '@app/videos/video-list/video-local.component' import { SharedModule } from '../shared' -import { VideoSearchComponent } from './video-list' import { VideoRecentlyAddedComponent } from './video-list/video-recently-added.component' import { VideoTrendingComponent } from './video-list/video-trending.component' import { VideosRoutingModule } from './videos-routing.module' @@ -18,8 +17,7 @@ import { VideosComponent } from './videos.component' VideoTrendingComponent, VideoRecentlyAddedComponent, - VideoLocalComponent, - VideoSearchComponent + VideoLocalComponent ], exports: [ diff --git a/scripts/clean/server/test.sh b/scripts/clean/server/test.sh index 42651d3a8..3b8fe39ed 100755 --- a/scripts/clean/server/test.sh +++ b/scripts/clean/server/test.sh @@ -3,10 +3,14 @@ set -eu for i in $(seq 1 6); do - dropdb --if-exists "peertube_test$i" + dbname="peertube_test$i" + + dropdb --if-exists "$dbname" rm -rf "./test$i" rm -f "./config/local-test.json" rm -f "./config/local-test-$i.json" - createdb -O peertube "peertube_test$i" + createdb -O peertube "$dbname" + psql -c "CREATE EXTENSION pg_trgm;" "$dbname" + psql -c "CREATE EXTENSION unaccent;" "$dbname" redis-cli KEYS "bull-localhost:900$i*" | grep -v empty | xargs --no-run-if-empty redis-cli DEL done diff --git a/server.ts b/server.ts index 104de2153..1bfec724b 100644 --- a/server.ts +++ b/server.ts @@ -49,7 +49,7 @@ if (errorMessage !== null) { // Trust our proxy (IP forwarding...) app.set('trust proxy', CONFIG.TRUST_PROXY) -// Security middlewares +// Security middleware app.use(helmet({ frameguard: { action: 'deny' // we only allow it for /videos/embed, see server/controllers/client.ts 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') diff --git a/support/doc/docker.md b/support/doc/docker.md index 087961802..cab336344 100644 --- a/support/doc/docker.md +++ b/support/doc/docker.md @@ -53,6 +53,10 @@ $ docker-compose up **Important**: note that you'll get the initial `root` user password from the program output, so check out your logs to find them. +### What now? + +See the production guide ["What now" section](/support/doc/production.md#what-now). + ### Upgrade Pull the latest images and rerun PeerTube: diff --git a/support/doc/production.md b/support/doc/production.md index 8310e7fda..8d2a4da11 100644 --- a/support/doc/production.md +++ b/support/doc/production.md @@ -41,6 +41,13 @@ $ sudo -u postgres createuser -P peertube $ sudo -u postgres createdb -O peertube peertube_prod ``` +Then enable extensions PeerTube needs: + +``` +$ sudo -u postgres psql -c "CREATE EXTENSION pg_trgm;" peertube_prod +$ sudo -u postgres psql -c "CREATE EXTENSION unaccent;" peertube_prod +``` + ### Prepare PeerTube directory Fetch the latest tagged version of Peertube @@ -194,7 +201,7 @@ Now your instance is up you can: ## Upgrade -### PeerTube code +### PeerTube instance **Check the changelog (in particular BREAKING CHANGES!):** https://github.com/Chocobozzz/PeerTube/blob/develop/CHANGELOG.md -- 2.25.1