Server: Add postgresql indexes

9 files changed, 104 insertions, 13 deletions

diff --git a/server/models/author.js b/server/models/author.js
index e0ac868ea..8f5b598c8 100644
--- a/server/models/author.js
+++ b/server/models/author.js
@@ -17,6 +17,14 @@ module.exports = function (sequelize, DataTypes) {
       }
     },
     {
+      indexes: [
+        {
+          fields: [ 'name' ]
+        },
+        {
+          fields: [ 'podId' ]
+        }
+      ],
       classMethods: {
         associate
       }
diff --git a/server/models/oauth-client.js b/server/models/oauth-client.js
index b56838d4c..758c4cf2f 100644
--- a/server/models/oauth-client.js
+++ b/server/models/oauth-client.js
@@ -19,9 +19,17 @@ module.exports = function (sequelize, DataTypes) {
       }
     },
     {
+      indexes: [
+        {
+          fields: [ 'clientId' ],
+          unique: true
+        },
+        {
+          fields: [ 'clientId', 'clientSecret' ],
+          unique: true
+        }
+      ],
       classMethods: {
-        associate,
-
         getByIdAndSecret,
         list,
         loadFirstClient
@@ -34,16 +42,6 @@ module.exports = function (sequelize, DataTypes) {
 
 // ---------------------------------------------------------------------------
 
-function associate (models) {
-  this.hasMany(models.OAuthToken, {
-    foreignKey: {
-      name: 'oAuthClientId',
-      allowNull: false
-    },
-    onDelete: 'cascade'
-  })
-}
-
 function list (callback) {
   return this.findAll().asCallback(callback)
 }
diff --git a/server/models/oauth-token.js b/server/models/oauth-token.js
index f8de4e916..68e7c9ff7 100644
--- a/server/models/oauth-token.js
+++ b/server/models/oauth-token.js
@@ -25,6 +25,22 @@ module.exports = function (sequelize, DataTypes) {
       }
     },
     {
+      indexes: [
+        {
+          fields: [ 'refreshToken' ],
+          unique: true
+        },
+        {
+          fields: [ 'accessToken' ],
+          unique: true
+        },
+        {
+          fields: [ 'userId' ]
+        },
+        {
+          fields: [ 'oAuthClientId' ]
+        }
+      ],
       classMethods: {
         associate,
 
@@ -49,6 +65,14 @@ function associate (models) {
     },
     onDelete: 'cascade'
   })
+
+  this.belongsTo(models.OAuthClient, {
+    foreignKey: {
+      name: 'oAuthClientId',
+      allowNull: false
+    },
+    onDelete: 'cascade'
+  })
 }
 
 function getByRefreshTokenAndPopulateClient (refreshToken) {
diff --git a/server/models/pod.js b/server/models/pod.js
index 84f78f200..83ecd732e 100644
--- a/server/models/pod.js
+++ b/server/models/pod.js
@@ -35,6 +35,14 @@ module.exports = function (sequelize, DataTypes) {
       }
     },
     {
+      indexes: [
+        {
+          fields: [ 'host' ]
+        },
+        {
+          fields: [ 'score' ]
+        }
+      ],
       classMethods: {
         associate,
 
diff --git a/server/models/requestToPod.js b/server/models/requestToPod.js
index 378c2bdcf..f42a53458 100644
--- a/server/models/requestToPod.js
+++ b/server/models/requestToPod.js
@@ -4,6 +4,18 @@
 
 module.exports = function (sequelize, DataTypes) {
   const RequestToPod = sequelize.define('RequestToPod', {}, {
+    indexes: [
+      {
+        fields: [ 'requestId' ]
+      },
+      {
+        fields: [ 'podId' ]
+      },
+      {
+        fields: [ 'requestId', 'podId' ],
+        unique: true
+      }
+    ],
     classMethods: {
       removePodOf
     }
diff --git a/server/models/tag.js b/server/models/tag.js
index d6c2d3bb1..27eecdc84 100644
--- a/server/models/tag.js
+++ b/server/models/tag.js
@@ -11,6 +11,13 @@ module.exports = function (sequelize, DataTypes) {
       }
     },
     {
+      timestamps: false,
+      indexes: [
+        {
+          fields: [ 'name' ],
+          unique: true
+        }
+      ],
       classMethods: {
         associate
       }
diff --git a/server/models/user.js b/server/models/user.js
index 944986a44..631cd96c9 100644
--- a/server/models/user.js
+++ b/server/models/user.js
@@ -38,6 +38,11 @@ module.exports = function (sequelize, DataTypes) {
       }
     },
     {
+      indexes: [
+        {
+          fields: [ 'username' ]
+        }
+      ],
       classMethods: {
         associate,
 
diff --git a/server/models/video.js b/server/models/video.js
index 3ebc48ad4..d1595ce51 100644
--- a/server/models/video.js
+++ b/server/models/video.js
@@ -82,6 +82,26 @@ module.exports = function (sequelize, DataTypes) {
       }
     },
     {
+      indexes: [
+        {
+          fields: [ 'authorId' ]
+        },
+        {
+          fields: [ 'remoteId' ]
+        },
+        {
+          fields: [ 'name' ]
+        },
+        {
+          fields: [ 'createdAt' ]
+        },
+        {
+          fields: [ 'duration' ]
+        },
+        {
+          fields: [ 'infoHash' ]
+        }
+      ],
       classMethods: {
         associate,
 
diff --git a/server/models/videoTag.js b/server/models/videoTag.js
index 0f2b20838..cd9277a6e 100644
--- a/server/models/videoTag.js
+++ b/server/models/videoTag.js
@@ -3,7 +3,16 @@
 // ---------------------------------------------------------------------------
 
 module.exports = function (sequelize, DataTypes) {
-  const VideoTag = sequelize.define('VideoTag', {}, {})
+  const VideoTag = sequelize.define('VideoTag', {}, {
+    indexes: [
+      {
+        fields: [ 'videoId' ]
+      },
+      {
+        fields: [ 'tagId' ]
+      }
+    ]
+  })
 
   return VideoTag
 }