Add missing url unique index in local video viewer

3 files changed, 34 insertions, 17 deletions

diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts
index 2ef3da2e7..54380f7bf 100644
--- a/server/initializers/constants.ts
+++ b/server/initializers/constants.ts
@@ -6,7 +6,6 @@ import { randomInt, root } from '@shared/core-utils'
 import {
   AbuseState,
   JobType,
-  UserRegistrationState,
   VideoChannelSyncState,
   VideoImportState,
   VideoPrivacy,
@@ -26,7 +25,7 @@ import { CONFIG, registerConfigChangedHandler } from './config'
 
 // ---------------------------------------------------------------------------
 
-const LAST_MIGRATION_VERSION = 750
+const LAST_MIGRATION_VERSION = 755
 
 // ---------------------------------------------------------------------------
 
@@ -79,8 +78,6 @@ const SORTABLE_COLUMNS = {
   ACCOUNT_FOLLOWERS: [ 'createdAt' ],
   CHANNEL_FOLLOWERS: [ 'createdAt' ],
 
-  USER_REGISTRATIONS: [ 'createdAt', 'state' ],
-
   VIDEOS: [ 'name', 'duration', 'createdAt', 'publishedAt', 'originallyPublishedAt', 'views', 'likes', 'trending', 'hot', 'best' ],
 
   // Don't forget to update peertube-search-index with the same values
@@ -293,10 +290,6 @@ const CONSTRAINTS_FIELDS = {
   ABUSE_MESSAGES: {
     MESSAGE: { min: 2, max: 3000 } // Length
   },
-  USER_REGISTRATIONS: {
-    REASON_MESSAGE: { min: 2, max: 3000 }, // Length
-    MODERATOR_MESSAGE: { min: 2, max: 3000 } // Length
-  },
   VIDEO_BLACKLIST: {
     REASON: { min: 2, max: 300 } // Length
   },
@@ -523,12 +516,6 @@ const ABUSE_STATES: { [ id in AbuseState ]: string } = {
   [AbuseState.ACCEPTED]: 'Accepted'
 }
 
-const USER_REGISTRATION_STATES: { [ id in UserRegistrationState ]: string } = {
-  [UserRegistrationState.PENDING]: 'Pending',
-  [UserRegistrationState.REJECTED]: 'Rejected',
-  [UserRegistrationState.ACCEPTED]: 'Accepted'
-}
-
 const VIDEO_PLAYLIST_PRIVACIES: { [ id in VideoPlaylistPrivacy ]: string } = {
   [VideoPlaylistPrivacy.PUBLIC]: 'Public',
   [VideoPlaylistPrivacy.UNLISTED]: 'Unlisted',
@@ -673,7 +660,7 @@ const USER_PASSWORD_CREATE_LIFETIME = 60000 * 60 * 24 * 7 // 7 days
 
 const TWO_FACTOR_AUTH_REQUEST_TOKEN_LIFETIME = 60000 * 10 // 10 minutes
 
-const EMAIL_VERIFY_LIFETIME = 60000 * 60 // 60 minutes
+const USER_EMAIL_VERIFY_LIFETIME = 60000 * 60 // 60 minutes
 
 const NSFW_POLICY_TYPES: { [ id: string ]: NSFWPolicyType } = {
   DO_NOT_LIST: 'do_not_list',
@@ -1082,14 +1069,13 @@ export {
   VIDEO_TRANSCODING_FPS,
   FFMPEG_NICE,
   ABUSE_STATES,
-  USER_REGISTRATION_STATES,
   LRU_CACHE,
   REQUEST_TIMEOUTS,
   MAX_LOCAL_VIEWER_WATCH_SECTIONS,
   USER_PASSWORD_RESET_LIFETIME,
   USER_PASSWORD_CREATE_LIFETIME,
   MEMOIZE_TTL,
-  EMAIL_VERIFY_LIFETIME,
+  USER_EMAIL_VERIFY_LIFETIME,
   OVERVIEWS,
   SCHEDULER_INTERVALS_MS,
   REPEAT_JOBS,
diff --git a/server/initializers/migrations/0755-unique-viewer-url.ts b/server/initializers/migrations/0755-unique-viewer-url.ts
new file mode 100644
index 000000000..b3dff9258
--- /dev/null
+++ b/server/initializers/migrations/0755-unique-viewer-url.ts
@@ -0,0 +1,27 @@
+import * as Sequelize from 'sequelize'
+
+async function up (utils: {
+  transaction: Sequelize.Transaction
+  queryInterface: Sequelize.QueryInterface
+  sequelize: Sequelize.Sequelize
+  db: any
+}): Promise<void> {
+  const { transaction } = utils
+
+  const query = 'DELETE FROM "localVideoViewer" t1 ' +
+  'USING (SELECT MIN(id) as id, "url" FROM "localVideoViewer" GROUP BY "url" HAVING COUNT(*) > 1) t2 ' +
+  'WHERE t1."url" = t2."url" AND t1.id <> t2.id'
+
+  await utils.sequelize.query(query, { transaction })
+}
+
+async function down (utils: {
+  queryInterface: Sequelize.QueryInterface
+  transaction: Sequelize.Transaction
+}) {
+}
+
+export {
+  up,
+  down
+}
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
index 9d0d89a59..274117e86 100644
--- a/server/models/view/local-video-viewer.ts
+++ b/server/models/view/local-video-viewer.ts
@@ -21,6 +21,10 @@ import { LocalVideoViewerWatchSectionModel } from './local-video-viewer-watch-se
   indexes: [
     {
       fields: [ 'videoId' ]
+    },
+    {
+      fields: [ 'url' ],
+      unique: true
     }
   ]
 })