]> git.immae.eu Git - github/Chocobozzz/PeerTube.git/blame - server/models/utils.ts
Improve quick settings UI
[github/Chocobozzz/PeerTube.git] / server / models / utils.ts
CommitLineData
16c016e8 1import { literal, Op, OrderItem, Sequelize } from 'sequelize'
49cff3a4 2import validator from 'validator'
57c36b27 3
95153292 4type SortType = { sortModel: string, sortValue: string }
06215f15 5
9a629c6e 6// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ]
1735c825
C
7function getSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
8 const { direction, field } = buildDirectionAndField(value)
9
9d8ef212 10 let finalField: string | ReturnType<typeof Sequelize.col>
4b54f136
C
11
12 if (field.toLowerCase() === 'match') { // Search
1735c825
C
13 finalField = Sequelize.col('similarity')
14 } else {
15 finalField = field
4b54f136 16 }
5c39adb7 17
1735c825 18 return [ [ finalField, direction ], lastSort ]
9a629c6e
C
19}
20
87a0cac6
C
21function getAdminUsersSort (value: string): OrderItem[] {
22 const { direction, field } = buildDirectionAndField(value)
23
24 let finalField: string | ReturnType<typeof Sequelize.col>
25
26 if (field === 'videoQuotaUsed') { // Users list
27 finalField = Sequelize.col('videoQuotaUsed')
28 } else {
29 finalField = field
30 }
31
32 const nullPolicy = direction === 'ASC'
33 ? 'NULLS FIRST'
34 : 'NULLS LAST'
35
36 // FIXME: typings
37 return [ [ finalField as any, direction, nullPolicy ], [ 'id', 'ASC' ] ]
38}
39
49cff3a4
C
40function getPlaylistSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
41 const { direction, field } = buildDirectionAndField(value)
42
43 if (field.toLowerCase() === 'name') {
44 return [ [ 'displayName', direction ], lastSort ]
45 }
46
47 return getSort(value, lastSort)
48}
49
c1125bca
RK
50function getCommentSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
51 const { direction, field } = buildDirectionAndField(value)
52
53 if (field === 'totalReplies') {
54 return [
55 [ Sequelize.literal('"totalReplies"'), direction ],
56 lastSort
57 ]
58 }
59
60 return getSort(value, lastSort)
61}
62
1735c825
C
63function getVideoSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
64 const { direction, field } = buildDirectionAndField(value)
5c39adb7 65
1735c825 66 if (field.toLowerCase() === 'trending') { // Sort by aggregation
9a629c6e
C
67 return [
68 [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ],
69
70 [ Sequelize.col('VideoModel.views'), direction ],
71
8e286cdc
RK
72 lastSort
73 ]
74 } else if (field === 'publishedAt') {
75 return [
76 [ 'ScheduleVideoUpdate', 'updateAt', direction + ' NULLS LAST' ],
77
78 [ Sequelize.col('VideoModel.publishedAt'), direction ],
79
9a629c6e
C
80 lastSort
81 ]
82 }
83
9d8ef212 84 let finalField: string | ReturnType<typeof Sequelize.col>
1735c825
C
85
86 // Alias
87 if (field.toLowerCase() === 'match') { // Search
88 finalField = Sequelize.col('similarity')
89 } else {
90 finalField = field
91 }
92
0c691a18
C
93 const firstSort: OrderItem = typeof finalField === 'string'
94 ? finalField.split('.').concat([ direction ]) as OrderItem
1735c825 95 : [ finalField, direction ]
afa4374a
C
96
97 return [ firstSort, lastSort ]
5c39adb7
C
98}
99
95153292 100function getBlacklistSort (model: any, value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
1735c825 101 const [ firstSort ] = getSort(value)
792dbaf0 102
0c691a18 103 if (model) return [ [ literal(`"${model}.${firstSort[0]}" ${firstSort[1]}`) ], lastSort ] as OrderItem[]
3bb6c526 104 return [ firstSort, lastSort ]
792dbaf0
GS
105}
106
bae61627 107function getInstanceFollowsSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
cb5ce4cb
C
108 const { direction, field } = buildDirectionAndField(value)
109
110 if (field === 'redundancyAllowed') {
111 return [
bae61627 112 [ 'ActorFollowing.Server.redundancyAllowed', direction ],
cb5ce4cb
C
113 lastSort
114 ]
115 }
116
117 return getSort(value, lastSort)
118}
119
9f79ade6 120function isOutdated (model: { createdAt: Date, updatedAt: Date }, refreshInterval: number) {
304a84d5 121 if (!model.createdAt || !model.updatedAt) {
7a4fd56c 122 throw new Error('Miss createdAt & updatedAt attributes to model')
304a84d5
C
123 }
124
9f79ade6
C
125 const now = Date.now()
126 const createdAtTime = model.createdAt.getTime()
127 const updatedAtTime = model.updatedAt.getTime()
128
129 return (now - createdAtTime) > refreshInterval && (now - updatedAtTime) > refreshInterval
130}
131
1735c825
C
132function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldName = 'value', nullable = false) {
133 if (nullable && (value === null || value === undefined)) return
134
3fd3ab2d
C
135 if (validator(value) === false) {
136 throw new Error(`"${value}" is not a valid ${fieldName}.`)
137 }
138}
139
57c36b27
C
140function buildTrigramSearchIndex (indexName: string, attribute: string) {
141 return {
142 name: indexName,
b49f22d8
C
143 // FIXME: gin_trgm_ops is not taken into account in Sequelize 6, so adding it ourselves in the literal function
144 fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + ')) gin_trgm_ops') as any ],
57c36b27
C
145 using: 'gin',
146 operator: 'gin_trgm_ops'
147 }
148}
149
150function createSimilarityAttribute (col: string, value: string) {
151 return Sequelize.fn(
152 'similarity',
153
154 searchTrigramNormalizeCol(col),
155
156 searchTrigramNormalizeValue(value)
157 )
158}
159
696d83fd 160function buildBlockedAccountSQL (blockerIds: number[]) {
7ad9b984
C
161 const blockerIdsString = blockerIds.join(', ')
162
418d092a 163 return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
9d6b9d10 164 ' UNION ' +
7ad9b984
C
165 'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
166 'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
167 'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
418d092a
C
168}
169
1c58423f
C
170function buildBlockedAccountSQLOptimized (columnNameJoin: string, blockerIds: number[]) {
171 const blockerIdsString = blockerIds.join(', ')
172
173 return [
174 literal(
175 `NOT EXISTS (` +
176 ` SELECT 1 FROM "accountBlocklist" ` +
177 ` WHERE "targetAccountId" = ${columnNameJoin} ` +
178 ` AND "accountId" IN (${blockerIdsString})` +
179 `)`
180 ),
181
182 literal(
183 `NOT EXISTS (` +
184 ` SELECT 1 FROM "account" ` +
185 ` INNER JOIN "actor" ON account."actorId" = actor.id ` +
186 ` INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` +
187 ` WHERE "account"."id" = ${columnNameJoin} ` +
188 ` AND "serverBlocklist"."accountId" IN (${blockerIdsString})` +
189 `)`
190 )
191 ]
192}
193
418d092a
C
194function buildServerIdsFollowedBy (actorId: any) {
195 const actorIdNumber = parseInt(actorId + '', 10)
196
197 return '(' +
198 'SELECT "actor"."serverId" FROM "actorFollow" ' +
199 'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
200 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
d0800f76 201 ')'
418d092a 202}
7ad9b984 203
418d092a
C
204function buildWhereIdOrUUID (id: number | string) {
205 return validator.isInt('' + id) ? { id } : { uuid: id }
7ad9b984
C
206}
207
3acc5084
C
208function parseAggregateResult (result: any) {
209 if (!result) return 0
210
211 const total = parseInt(result + '', 10)
212 if (isNaN(total)) return 0
213
214 return total
215}
216
bae61627
C
217function parseRowCountResult (result: any) {
218 if (result.length !== 0) return result[0].total
219
220 return 0
221}
222
16c016e8 223function createSafeIn (sequelize: Sequelize, stringArr: (string | number)[]) {
5f3e2425
C
224 return stringArr.map(t => {
225 return t === null
226 ? null
16c016e8 227 : sequelize.escape('' + t)
5f3e2425 228 }).join(', ')
3caf77d3
C
229}
230
6b9c966f
C
231function buildLocalAccountIdsIn () {
232 return literal(
233 '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
234 )
235}
236
237function buildLocalActorIdsIn () {
238 return literal(
239 '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
240 )
241}
242
5f3e2425
C
243function buildDirectionAndField (value: string) {
244 let field: string
245 let direction: 'ASC' | 'DESC'
246
247 if (value.substring(0, 1) === '-') {
248 direction = 'DESC'
249 field = value.substring(1)
250 } else {
251 direction = 'ASC'
252 field = value
253 }
254
255 return { direction, field }
256}
257
feb34f6b
C
258function searchAttribute (sourceField?: string, targetField?: string) {
259 if (!sourceField) return {}
0d3a2982
RK
260
261 return {
feb34f6b 262 [targetField]: {
c0d2eac3
C
263 // FIXME: ts error
264 [Op.iLike as any]: `%${sourceField}%`
feb34f6b 265 }
0d3a2982
RK
266 }
267}
268
5c39adb7
C
269// ---------------------------------------------------------------------------
270
65fcc311 271export {
7ad9b984 272 buildBlockedAccountSQL,
1c58423f 273 buildBlockedAccountSQLOptimized,
6b9c966f 274 buildLocalActorIdsIn,
49cff3a4 275 getPlaylistSort,
06215f15 276 SortType,
6b9c966f 277 buildLocalAccountIdsIn,
792dbaf0 278 getSort,
c1125bca 279 getCommentSort,
87a0cac6 280 getAdminUsersSort,
9a629c6e 281 getVideoSort,
95153292 282 getBlacklistSort,
57c36b27
C
283 createSimilarityAttribute,
284 throwIfNotValid,
418d092a
C
285 buildServerIdsFollowedBy,
286 buildTrigramSearchIndex,
9f79ade6 287 buildWhereIdOrUUID,
3acc5084 288 isOutdated,
3caf77d3 289 parseAggregateResult,
bae61627 290 getInstanceFollowsSort,
5f3e2425 291 buildDirectionAndField,
e0a92917 292 createSafeIn,
bae61627
C
293 searchAttribute,
294 parseRowCountResult
57c36b27
C
295}
296
297// ---------------------------------------------------------------------------
298
299function searchTrigramNormalizeValue (value: string) {
2cebd797 300 return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
57c36b27
C
301}
302
303function searchTrigramNormalizeCol (col: string) {
304 return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
65fcc311 305}