diff options
author | Chocobozzz <me@florianbigard.com> | 2023-01-10 11:09:30 +0100 |
---|---|---|
committer | Chocobozzz <me@florianbigard.com> | 2023-01-10 11:56:08 +0100 |
commit | 8c4bbd946d2247c2e239cbbf8773d2d31c1a57aa (patch) | |
tree | 4e7d3711cccb095d4356c59554bdfd4e16502832 /server/models/utils.ts | |
parent | a8749f7c3b137f433e6944bb99fd721a6f0cfc1e (diff) | |
download | PeerTube-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.ts | 317 |
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 @@ | |||
1 | import { literal, Model, ModelStatic, Op, OrderItem, Sequelize } from 'sequelize' | ||
2 | import validator from 'validator' | ||
3 | import { forceNumber } from '@shared/core-utils' | ||
4 | import { AttributesOnly } from '@shared/typescript-utils' | ||
5 | |||
6 | type SortType = { sortModel: string, sortValue: string } | ||
7 | |||
8 | // Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ] | ||
9 | function 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 | |||
23 | function 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 | |||
42 | function 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 | |||
52 | function 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 | |||
65 | function 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 | |||
102 | function 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 | |||
109 | function 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 | |||
122 | function 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 | |||
132 | function 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 | |||
144 | function 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 | |||
152 | function 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 | |||
162 | function createSimilarityAttribute (col: string, value: string) { | ||
163 | return Sequelize.fn( | ||
164 | 'similarity', | ||
165 | |||
166 | searchTrigramNormalizeCol(col), | ||
167 | |||
168 | searchTrigramNormalizeValue(value) | ||
169 | ) | ||
170 | } | ||
171 | |||
172 | function 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 | |||
182 | function 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 | |||
192 | function buildWhereIdOrUUID (id: number | string) { | ||
193 | return validator.isInt('' + id) ? { id } : { uuid: id } | ||
194 | } | ||
195 | |||
196 | function 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 | |||
205 | function parseRowCountResult (result: any) { | ||
206 | if (result.length !== 0) return result[0].total | ||
207 | |||
208 | return 0 | ||
209 | } | ||
210 | |||
211 | function 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 | |||
219 | function 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 | |||
225 | function buildLocalActorIdsIn () { | ||
226 | return literal( | ||
227 | '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)' | ||
228 | ) | ||
229 | } | ||
230 | |||
231 | function 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 | |||
246 | function 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 | |||
257 | function 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 | |||
282 | export { | ||
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 | |||
311 | function searchTrigramNormalizeValue (value: string) { | ||
312 | return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value)) | ||
313 | } | ||
314 | |||
315 | function searchTrigramNormalizeCol (col: string) { | ||
316 | return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col))) | ||
317 | } | ||