aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
Diffstat (limited to 'server')
-rw-r--r--server/controllers/api/users/my-subscriptions.ts2
-rw-r--r--server/initializers/constants.ts2
-rw-r--r--server/initializers/migrations/0770-actor-preferred-username.ts44
-rw-r--r--server/models/account/account-video-rate.ts6
-rw-r--r--server/models/account/account.ts10
-rw-r--r--server/models/actor/actor-follow.ts22
-rw-r--r--server/models/actor/actor.ts36
-rw-r--r--server/models/video/video-channel.ts19
8 files changed, 95 insertions, 46 deletions
diff --git a/server/controllers/api/users/my-subscriptions.ts b/server/controllers/api/users/my-subscriptions.ts
index 6ba8ba597..6e2aa3711 100644
--- a/server/controllers/api/users/my-subscriptions.ts
+++ b/server/controllers/api/users/my-subscriptions.ts
@@ -99,7 +99,7 @@ async function areSubscriptionsExist (req: express.Request, res: express.Respons
99 const obj = results.find(r => { 99 const obj = results.find(r => {
100 const server = r.ActorFollowing.Server 100 const server = r.ActorFollowing.Server
101 101
102 return r.ActorFollowing.preferredUsername === sanitizedHandle.name && 102 return r.ActorFollowing.preferredUsername.toLowerCase() === sanitizedHandle.name.toLowerCase() &&
103 ( 103 (
104 (!server && !sanitizedHandle.host) || 104 (!server && !sanitizedHandle.host) ||
105 (server.host === sanitizedHandle.host) 105 (server.host === sanitizedHandle.host)
diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts
index adf24b73f..1dfc9fb27 100644
--- a/server/initializers/constants.ts
+++ b/server/initializers/constants.ts
@@ -27,7 +27,7 @@ import { CONFIG, registerConfigChangedHandler } from './config'
27 27
28// --------------------------------------------------------------------------- 28// ---------------------------------------------------------------------------
29 29
30const LAST_MIGRATION_VERSION = 765 30const LAST_MIGRATION_VERSION = 770
31 31
32// --------------------------------------------------------------------------- 32// ---------------------------------------------------------------------------
33 33
diff --git a/server/initializers/migrations/0770-actor-preferred-username.ts b/server/initializers/migrations/0770-actor-preferred-username.ts
new file mode 100644
index 000000000..217813f7f
--- /dev/null
+++ b/server/initializers/migrations/0770-actor-preferred-username.ts
@@ -0,0 +1,44 @@
1import * as Sequelize from 'sequelize'
2
3async function up (utils: {
4 transaction: Sequelize.Transaction
5 queryInterface: Sequelize.QueryInterface
6 sequelize: Sequelize.Sequelize
7 db: any
8}): Promise<void> {
9 const { transaction } = utils
10
11 await utils.sequelize.query('drop index if exists "actor_preferred_username"', { transaction })
12 await utils.sequelize.query('drop index if exists "actor_preferred_username_server_id"', { transaction })
13
14 await utils.sequelize.query(
15 'DELETE FROM "actor" v1 USING (' +
16 'SELECT MIN(id) as id, lower("preferredUsername") AS "lowerPreferredUsername", "serverId" ' +
17 'FROM "actor" ' +
18 'GROUP BY "lowerPreferredUsername", "serverId" HAVING COUNT(*) > 1 AND "serverId" IS NOT NULL' +
19 ') v2 ' +
20 'WHERE lower(v1."preferredUsername") = v2."lowerPreferredUsername" AND v1."serverId" = v2."serverId" AND v1.id <> v2.id',
21 { transaction }
22 )
23
24 await utils.sequelize.query(
25 'DELETE FROM "actor" v1 USING (' +
26 'SELECT MIN(id) as id, lower("preferredUsername") AS "lowerPreferredUsername", "serverId" ' +
27 'FROM "actor" ' +
28 'GROUP BY "lowerPreferredUsername", "serverId" HAVING COUNT(*) > 1 AND "serverId" IS NULL' +
29 ') v2 ' +
30 'WHERE lower(v1."preferredUsername") = v2."lowerPreferredUsername" AND v1."serverId" IS NULL AND v1.id <> v2.id',
31 { transaction }
32 )
33}
34
35async function down (utils: {
36 queryInterface: Sequelize.QueryInterface
37 transaction: Sequelize.Transaction
38}) {
39}
40
41export {
42 up,
43 down
44}
diff --git a/server/models/account/account-video-rate.ts b/server/models/account/account-video-rate.ts
index 9e7ef4394..18ff07d53 100644
--- a/server/models/account/account-video-rate.ts
+++ b/server/models/account/account-video-rate.ts
@@ -189,8 +189,10 @@ export class AccountVideoRateModel extends Model<Partial<AttributesOnly<AccountV
189 model: ActorModel.unscoped(), 189 model: ActorModel.unscoped(),
190 required: true, 190 required: true,
191 where: { 191 where: {
192 preferredUsername: accountName, 192 [Op.and]: [
193 serverId: null 193 ActorModel.wherePreferredUsername(accountName),
194 { serverId: null }
195 ]
194 } 196 }
195 } 197 }
196 ] 198 ]
diff --git a/server/models/account/account.ts b/server/models/account/account.ts
index 5bf29f45a..ec4e8d946 100644
--- a/server/models/account/account.ts
+++ b/server/models/account/account.ts
@@ -37,8 +37,8 @@ import { ActorImageModel } from '../actor/actor-image'
37import { ApplicationModel } from '../application/application' 37import { ApplicationModel } from '../application/application'
38import { ServerModel } from '../server/server' 38import { ServerModel } from '../server/server'
39import { ServerBlocklistModel } from '../server/server-blocklist' 39import { ServerBlocklistModel } from '../server/server-blocklist'
40import { UserModel } from '../user/user'
41import { buildSQLAttributes, getSort, throwIfNotValid } from '../shared' 40import { buildSQLAttributes, getSort, throwIfNotValid } from '../shared'
41import { UserModel } from '../user/user'
42import { VideoModel } from '../video/video' 42import { VideoModel } from '../video/video'
43import { VideoChannelModel } from '../video/video-channel' 43import { VideoChannelModel } from '../video/video-channel'
44import { VideoCommentModel } from '../video/video-comment' 44import { VideoCommentModel } from '../video/video-comment'
@@ -296,9 +296,7 @@ export class AccountModel extends Model<Partial<AttributesOnly<AccountModel>>> {
296 { 296 {
297 model: ActorModel, 297 model: ActorModel,
298 required: true, 298 required: true,
299 where: { 299 where: ActorModel.wherePreferredUsername(name)
300 preferredUsername: name
301 }
302 } 300 }
303 ] 301 ]
304 } 302 }
@@ -321,9 +319,7 @@ export class AccountModel extends Model<Partial<AttributesOnly<AccountModel>>> {
321 { 319 {
322 model: ActorModel, 320 model: ActorModel,
323 required: true, 321 required: true,
324 where: { 322 where: ActorModel.wherePreferredUsername(name),
325 preferredUsername: name
326 },
327 include: [ 323 include: [
328 { 324 {
329 model: ServerModel, 325 model: ServerModel,
diff --git a/server/models/actor/actor-follow.ts b/server/models/actor/actor-follow.ts
index 32e5d78b0..0f199d208 100644
--- a/server/models/actor/actor-follow.ts
+++ b/server/models/actor/actor-follow.ts
@@ -37,8 +37,8 @@ import { logger } from '../../helpers/logger'
37import { ACTOR_FOLLOW_SCORE, CONSTRAINTS_FIELDS, FOLLOW_STATES, SERVER_ACTOR_NAME, SORTABLE_COLUMNS } from '../../initializers/constants' 37import { ACTOR_FOLLOW_SCORE, CONSTRAINTS_FIELDS, FOLLOW_STATES, SERVER_ACTOR_NAME, SORTABLE_COLUMNS } from '../../initializers/constants'
38import { AccountModel } from '../account/account' 38import { AccountModel } from '../account/account'
39import { ServerModel } from '../server/server' 39import { ServerModel } from '../server/server'
40import { doesExist } from '../shared/query'
41import { buildSQLAttributes, createSafeIn, getSort, searchAttribute, throwIfNotValid } from '../shared' 40import { buildSQLAttributes, createSafeIn, getSort, searchAttribute, throwIfNotValid } from '../shared'
41import { doesExist } from '../shared/query'
42import { VideoChannelModel } from '../video/video-channel' 42import { VideoChannelModel } from '../video/video-channel'
43import { ActorModel, unusedActorAttributesForAPI } from './actor' 43import { ActorModel, unusedActorAttributesForAPI } from './actor'
44import { InstanceListFollowersQueryBuilder, ListFollowersOptions } from './sql/instance-list-followers-query-builder' 44import { InstanceListFollowersQueryBuilder, ListFollowersOptions } from './sql/instance-list-followers-query-builder'
@@ -265,9 +265,7 @@ export class ActorFollowModel extends Model<Partial<AttributesOnly<ActorFollowMo
265 model: ActorModel, 265 model: ActorModel,
266 required: true, 266 required: true,
267 as: 'ActorFollowing', 267 as: 'ActorFollowing',
268 where: { 268 where: ActorModel.wherePreferredUsername(targetName),
269 preferredUsername: targetName
270 },
271 include: [ 269 include: [
272 { 270 {
273 model: VideoChannelModel.unscoped(), 271 model: VideoChannelModel.unscoped(),
@@ -313,24 +311,16 @@ export class ActorFollowModel extends Model<Partial<AttributesOnly<ActorFollowMo
313 if (t.host) { 311 if (t.host) {
314 return { 312 return {
315 [Op.and]: [ 313 [Op.and]: [
316 { 314 ActorModel.wherePreferredUsername(t.name, '$preferredUsername$'),
317 $preferredUsername$: t.name 315 { $host$: t.host }
318 },
319 {
320 $host$: t.host
321 }
322 ] 316 ]
323 } 317 }
324 } 318 }
325 319
326 return { 320 return {
327 [Op.and]: [ 321 [Op.and]: [
328 { 322 ActorModel.wherePreferredUsername(t.name, '$preferredUsername$'),
329 $preferredUsername$: t.name 323 { $serverId$: null }
330 },
331 {
332 $serverId$: null
333 }
334 ] 324 ]
335 } 325 }
336 }) 326 })
diff --git a/server/models/actor/actor.ts b/server/models/actor/actor.ts
index 1432e8757..1524e0533 100644
--- a/server/models/actor/actor.ts
+++ b/server/models/actor/actor.ts
@@ -1,4 +1,4 @@
1import { literal, Op, QueryTypes, Transaction } from 'sequelize' 1import { col, fn, literal, Op, QueryTypes, Transaction, where } from 'sequelize'
2import { 2import {
3 AllowNull, 3 AllowNull,
4 BelongsTo, 4 BelongsTo,
@@ -130,7 +130,8 @@ export const unusedActorAttributesForAPI: (keyof AttributesOnly<ActorModel>)[] =
130 unique: true 130 unique: true
131 }, 131 },
132 { 132 {
133 fields: [ 'preferredUsername', 'serverId' ], 133 fields: [ fn('lower', col('preferredUsername')), 'serverId' ],
134 name: 'actor_preferred_username_lower_server_id',
134 unique: true, 135 unique: true,
135 where: { 136 where: {
136 serverId: { 137 serverId: {
@@ -139,7 +140,8 @@ export const unusedActorAttributesForAPI: (keyof AttributesOnly<ActorModel>)[] =
139 } 140 }
140 }, 141 },
141 { 142 {
142 fields: [ 'preferredUsername' ], 143 fields: [ fn('lower', col('preferredUsername')) ],
144 name: 'actor_preferred_username_lower',
143 unique: true, 145 unique: true,
144 where: { 146 where: {
145 serverId: null 147 serverId: null
@@ -327,6 +329,12 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
327 329
328 // --------------------------------------------------------------------------- 330 // ---------------------------------------------------------------------------
329 331
332 static wherePreferredUsername (preferredUsername: string, colName = 'preferredUsername') {
333 return where(fn('lower', col(colName)), preferredUsername.toLowerCase())
334 }
335
336 // ---------------------------------------------------------------------------
337
330 static async load (id: number): Promise<MActor> { 338 static async load (id: number): Promise<MActor> {
331 const actorServer = await getServerActor() 339 const actorServer = await getServerActor()
332 if (id === actorServer.id) return actorServer 340 if (id === actorServer.id) return actorServer
@@ -372,8 +380,12 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
372 const fun = () => { 380 const fun = () => {
373 const query = { 381 const query = {
374 where: { 382 where: {
375 preferredUsername, 383 [Op.and]: [
376 serverId: null 384 this.wherePreferredUsername(preferredUsername),
385 {
386 serverId: null
387 }
388 ]
377 }, 389 },
378 transaction 390 transaction
379 } 391 }
@@ -395,8 +407,12 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
395 const query = { 407 const query = {
396 attributes: [ 'url' ], 408 attributes: [ 'url' ],
397 where: { 409 where: {
398 preferredUsername, 410 [Op.and]: [
399 serverId: null 411 this.wherePreferredUsername(preferredUsername),
412 {
413 serverId: null
414 }
415 ]
400 }, 416 },
401 transaction 417 transaction
402 } 418 }
@@ -405,7 +421,7 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
405 } 421 }
406 422
407 return ModelCache.Instance.doCache({ 423 return ModelCache.Instance.doCache({
408 cacheType: 'local-actor-name', 424 cacheType: 'local-actor-url',
409 key: preferredUsername, 425 key: preferredUsername,
410 // The server actor never change, so we can easily cache it 426 // The server actor never change, so we can easily cache it
411 whitelist: () => preferredUsername === SERVER_ACTOR_NAME, 427 whitelist: () => preferredUsername === SERVER_ACTOR_NAME,
@@ -415,9 +431,7 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
415 431
416 static loadByNameAndHost (preferredUsername: string, host: string): Promise<MActorFull> { 432 static loadByNameAndHost (preferredUsername: string, host: string): Promise<MActorFull> {
417 const query = { 433 const query = {
418 where: { 434 where: this.wherePreferredUsername(preferredUsername),
419 preferredUsername
420 },
421 include: [ 435 include: [
422 { 436 {
423 model: ServerModel, 437 model: ServerModel,
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 67fccab68..306bc6ade 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -130,13 +130,16 @@ export type SummaryOptions = {
130 for (const handle of options.handles || []) { 130 for (const handle of options.handles || []) {
131 const [ preferredUsername, host ] = handle.split('@') 131 const [ preferredUsername, host ] = handle.split('@')
132 132
133 const sanitizedPreferredUsername = VideoChannelModel.sequelize.escape(preferredUsername.toLowerCase())
134 const sanitizedHost = VideoChannelModel.sequelize.escape(host)
135
133 if (!host || host === WEBSERVER.HOST) { 136 if (!host || host === WEBSERVER.HOST) {
134 or.push(`("preferredUsername" = ${VideoChannelModel.sequelize.escape(preferredUsername)} AND "serverId" IS NULL)`) 137 or.push(`(LOWER("preferredUsername") = ${sanitizedPreferredUsername} AND "serverId" IS NULL)`)
135 } else { 138 } else {
136 or.push( 139 or.push(
137 `(` + 140 `(` +
138 `"preferredUsername" = ${VideoChannelModel.sequelize.escape(preferredUsername)} ` + 141 `LOWER("preferredUsername") = ${sanitizedPreferredUsername} ` +
139 `AND "host" = ${VideoChannelModel.sequelize.escape(host)}` + 142 `AND "host" = ${sanitizedHost}` +
140 `)` 143 `)`
141 ) 144 )
142 } 145 }
@@ -698,8 +701,10 @@ export class VideoChannelModel extends Model<Partial<AttributesOnly<VideoChannel
698 model: ActorModel, 701 model: ActorModel,
699 required: true, 702 required: true,
700 where: { 703 where: {
701 preferredUsername: name, 704 [Op.and]: [
702 serverId: null 705 ActorModel.wherePreferredUsername(name),
706 { serverId: null }
707 ]
703 }, 708 },
704 include: [ 709 include: [
705 { 710 {
@@ -723,9 +728,7 @@ export class VideoChannelModel extends Model<Partial<AttributesOnly<VideoChannel
723 { 728 {
724 model: ActorModel, 729 model: ActorModel,
725 required: true, 730 required: true,
726 where: { 731 where: ActorModel.wherePreferredUsername(name),
727 preferredUsername: name
728 },
729 include: [ 732 include: [
730 { 733 {
731 model: ServerModel, 734 model: ServerModel,