aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/utils.ts
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2023-01-10 11:09:30 +0100
committerChocobozzz <me@florianbigard.com>2023-01-10 11:56:08 +0100
commit8c4bbd946d2247c2e239cbbf8773d2d31c1a57aa (patch)
tree4e7d3711cccb095d4356c59554bdfd4e16502832 /server/models/utils.ts
parenta8749f7c3b137f433e6944bb99fd721a6f0cfc1e (diff)
downloadPeerTube-8c4bbd946d2247c2e239cbbf8773d2d31c1a57aa.tar.gz
PeerTube-8c4bbd946d2247c2e239cbbf8773d2d31c1a57aa.tar.zst
PeerTube-8c4bbd946d2247c2e239cbbf8773d2d31c1a57aa.zip
Refactor model utils
Diffstat (limited to 'server/models/utils.ts')
-rw-r--r--server/models/utils.ts317
1 files changed, 0 insertions, 317 deletions
diff --git a/server/models/utils.ts b/server/models/utils.ts
deleted file mode 100644
index 93723816f..000000000
--- a/server/models/utils.ts
+++ /dev/null
@@ -1,317 +0,0 @@
1import { literal, Model, ModelStatic, Op, OrderItem, Sequelize } from 'sequelize'
2import validator from 'validator'
3import { forceNumber } from '@shared/core-utils'
4import { AttributesOnly } from '@shared/typescript-utils'
5
6type SortType = { sortModel: string, sortValue: string }
7
8// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ]
9function getSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
10 const { direction, field } = buildDirectionAndField(value)
11
12 let finalField: string | ReturnType<typeof Sequelize.col>
13
14 if (field.toLowerCase() === 'match') { // Search
15 finalField = Sequelize.col('similarity')
16 } else {
17 finalField = field
18 }
19
20 return [ [ finalField, direction ], lastSort ]
21}
22
23function getAdminUsersSort (value: string): OrderItem[] {
24 const { direction, field } = buildDirectionAndField(value)
25
26 let finalField: string | ReturnType<typeof Sequelize.col>
27
28 if (field === 'videoQuotaUsed') { // Users list
29 finalField = Sequelize.col('videoQuotaUsed')
30 } else {
31 finalField = field
32 }
33
34 const nullPolicy = direction === 'ASC'
35 ? 'NULLS FIRST'
36 : 'NULLS LAST'
37
38 // FIXME: typings
39 return [ [ finalField as any, direction, nullPolicy ], [ 'id', 'ASC' ] ]
40}
41
42function getPlaylistSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
43 const { direction, field } = buildDirectionAndField(value)
44
45 if (field.toLowerCase() === 'name') {
46 return [ [ 'displayName', direction ], lastSort ]
47 }
48
49 return getSort(value, lastSort)
50}
51
52function getCommentSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
53 const { direction, field } = buildDirectionAndField(value)
54
55 if (field === 'totalReplies') {
56 return [
57 [ Sequelize.literal('"totalReplies"'), direction ],
58 lastSort
59 ]
60 }
61
62 return getSort(value, lastSort)
63}
64
65function getVideoSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
66 const { direction, field } = buildDirectionAndField(value)
67
68 if (field.toLowerCase() === 'trending') { // Sort by aggregation
69 return [
70 [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ],
71
72 [ Sequelize.col('VideoModel.views'), direction ],
73
74 lastSort
75 ]
76 } else if (field === 'publishedAt') {
77 return [
78 [ 'ScheduleVideoUpdate', 'updateAt', direction + ' NULLS LAST' ],
79
80 [ Sequelize.col('VideoModel.publishedAt'), direction ],
81
82 lastSort
83 ]
84 }
85
86 let finalField: string | ReturnType<typeof Sequelize.col>
87
88 // Alias
89 if (field.toLowerCase() === 'match') { // Search
90 finalField = Sequelize.col('similarity')
91 } else {
92 finalField = field
93 }
94
95 const firstSort: OrderItem = typeof finalField === 'string'
96 ? finalField.split('.').concat([ direction ]) as OrderItem
97 : [ finalField, direction ]
98
99 return [ firstSort, lastSort ]
100}
101
102function getBlacklistSort (model: any, value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
103 const [ firstSort ] = getSort(value)
104
105 if (model) return [ [ literal(`"${model}.${firstSort[0]}" ${firstSort[1]}`) ], lastSort ] as OrderItem[]
106 return [ firstSort, lastSort ]
107}
108
109function getInstanceFollowsSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
110 const { direction, field } = buildDirectionAndField(value)
111
112 if (field === 'redundancyAllowed') {
113 return [
114 [ 'ActorFollowing.Server.redundancyAllowed', direction ],
115 lastSort
116 ]
117 }
118
119 return getSort(value, lastSort)
120}
121
122function getChannelSyncSort (value: string): OrderItem[] {
123 const { direction, field } = buildDirectionAndField(value)
124 if (field.toLowerCase() === 'videochannel') {
125 return [
126 [ literal('"VideoChannel.name"'), direction ]
127 ]
128 }
129 return [ [ field, direction ] ]
130}
131
132function isOutdated (model: { createdAt: Date, updatedAt: Date }, refreshInterval: number) {
133 if (!model.createdAt || !model.updatedAt) {
134 throw new Error('Miss createdAt & updatedAt attributes to model')
135 }
136
137 const now = Date.now()
138 const createdAtTime = model.createdAt.getTime()
139 const updatedAtTime = model.updatedAt.getTime()
140
141 return (now - createdAtTime) > refreshInterval && (now - updatedAtTime) > refreshInterval
142}
143
144function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldName = 'value', nullable = false) {
145 if (nullable && (value === null || value === undefined)) return
146
147 if (validator(value) === false) {
148 throw new Error(`"${value}" is not a valid ${fieldName}.`)
149 }
150}
151
152function buildTrigramSearchIndex (indexName: string, attribute: string) {
153 return {
154 name: indexName,
155 // FIXME: gin_trgm_ops is not taken into account in Sequelize 6, so adding it ourselves in the literal function
156 fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + ')) gin_trgm_ops') as any ],
157 using: 'gin',
158 operator: 'gin_trgm_ops'
159 }
160}
161
162function createSimilarityAttribute (col: string, value: string) {
163 return Sequelize.fn(
164 'similarity',
165
166 searchTrigramNormalizeCol(col),
167
168 searchTrigramNormalizeValue(value)
169 )
170}
171
172function buildBlockedAccountSQL (blockerIds: number[]) {
173 const blockerIdsString = blockerIds.join(', ')
174
175 return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
176 ' UNION ' +
177 'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
178 'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
179 'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
180}
181
182function buildServerIdsFollowedBy (actorId: any) {
183 const actorIdNumber = forceNumber(actorId)
184
185 return '(' +
186 'SELECT "actor"."serverId" FROM "actorFollow" ' +
187 'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
188 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
189 ')'
190}
191
192function buildWhereIdOrUUID (id: number | string) {
193 return validator.isInt('' + id) ? { id } : { uuid: id }
194}
195
196function parseAggregateResult (result: any) {
197 if (!result) return 0
198
199 const total = forceNumber(result)
200 if (isNaN(total)) return 0
201
202 return total
203}
204
205function parseRowCountResult (result: any) {
206 if (result.length !== 0) return result[0].total
207
208 return 0
209}
210
211function createSafeIn (sequelize: Sequelize, toEscape: (string | number)[], additionalUnescaped: string[] = []) {
212 return toEscape.map(t => {
213 return t === null
214 ? null
215 : sequelize.escape('' + t)
216 }).concat(additionalUnescaped).join(', ')
217}
218
219function buildLocalAccountIdsIn () {
220 return literal(
221 '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
222 )
223}
224
225function buildLocalActorIdsIn () {
226 return literal(
227 '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
228 )
229}
230
231function buildDirectionAndField (value: string) {
232 let field: string
233 let direction: 'ASC' | 'DESC'
234
235 if (value.substring(0, 1) === '-') {
236 direction = 'DESC'
237 field = value.substring(1)
238 } else {
239 direction = 'ASC'
240 field = value
241 }
242
243 return { direction, field }
244}
245
246function searchAttribute (sourceField?: string, targetField?: string) {
247 if (!sourceField) return {}
248
249 return {
250 [targetField]: {
251 // FIXME: ts error
252 [Op.iLike as any]: `%${sourceField}%`
253 }
254 }
255}
256
257function buildSQLAttributes <M extends Model> (options: {
258 model: ModelStatic<M>
259 tableName: string
260
261 excludeAttributes?: Exclude<keyof AttributesOnly<M>, symbol>[]
262 aliasPrefix?: string
263}) {
264 const { model, tableName, aliasPrefix, excludeAttributes } = options
265
266 const attributes = Object.keys(model.getAttributes()) as Exclude<keyof AttributesOnly<M>, symbol>[]
267
268 return attributes
269 .filter(a => {
270 if (!excludeAttributes) return true
271 if (excludeAttributes.includes(a)) return false
272
273 return true
274 })
275 .map(a => {
276 return `"${tableName}"."${a}" AS "${aliasPrefix || ''}${a}"`
277 })
278}
279
280// ---------------------------------------------------------------------------
281
282export {
283 buildSQLAttributes,
284 buildBlockedAccountSQL,
285 buildLocalActorIdsIn,
286 getPlaylistSort,
287 SortType,
288 buildLocalAccountIdsIn,
289 getSort,
290 getCommentSort,
291 getAdminUsersSort,
292 getVideoSort,
293 getBlacklistSort,
294 getChannelSyncSort,
295 createSimilarityAttribute,
296 throwIfNotValid,
297 buildServerIdsFollowedBy,
298 buildTrigramSearchIndex,
299 buildWhereIdOrUUID,
300 isOutdated,
301 parseAggregateResult,
302 getInstanceFollowsSort,
303 buildDirectionAndField,
304 createSafeIn,
305 searchAttribute,
306 parseRowCountResult
307}
308
309// ---------------------------------------------------------------------------
310
311function searchTrigramNormalizeValue (value: string) {
312 return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
313}
314
315function searchTrigramNormalizeCol (col: string) {
316 return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
317}