Merge pull request #409 from dementati/feat/397-398-metricity-db-and-api

Added metricity db connection and user bot API

8 files changed, 164 insertions, 1 deletions

diff --git a/.coveragerc b/.coveragerc
index f5ddf08d..0cccc47c 100644
--- a/.coveragerc
+++ b/.coveragerc
@@ -12,6 +12,7 @@ omit =
     */admin.py
     */apps.py
     */urls.py
+    pydis_site/apps/api/models/bot/metricity.py
     pydis_site/wsgi.py
     pydis_site/settings.py
     pydis_site/utils/resources.py
diff --git a/azure-pipelines.yml b/azure-pipelines.yml
index f273dad3..4f90aafe 100644
--- a/azure-pipelines.yml
+++ b/azure-pipelines.yml
@@ -66,6 +66,7 @@ jobs:
         env:
           CI: azure
           DATABASE_URL: postgres://pysite:pysite@localhost:7777/pysite
+          METRICITY_DB_URL: postgres://pysite:pysite@localhost:7777/metricity
         displayName: 'Run Tests'
 
       - script: coverage report -m && coverage xml
diff --git a/docker-compose.yml b/docker-compose.yml
index 73d2ff85..7287d8d5 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -18,6 +18,8 @@ services:
       POSTGRES_DB: pysite
       POSTGRES_PASSWORD: pysite
       POSTGRES_USER: pysite
+    volumes:
+      - ./postgres/init.sql:/docker-entrypoint-initdb.d/init.sql
 
   web:
     build:
@@ -40,6 +42,7 @@ services:
       - staticfiles:/var/www/static
     environment:
       DATABASE_URL: postgres://pysite:pysite@postgres:5432/pysite
+      METRICITY_DB_URL: postgres://pysite:pysite@postgres:5432/metricity
       SECRET_KEY: suitable-for-development-only
       STATIC_ROOT: /var/www/static
 
diff --git a/postgres/init.sql b/postgres/init.sql
new file mode 100644
index 00000000..922ce1ad
--- /dev/null
+++ b/postgres/init.sql
@@ -0,0 +1,30 @@
+CREATE DATABASE metricity;
+
+\c metricity;
+
+CREATE TABLE users (
+    id varchar,
+    verified_at timestamp,
+    primary key(id)
+);
+
+INSERT INTO users VALUES (
+    0,
+    current_timestamp
+);
+
+CREATE TABLE messages (
+    id varchar,
+    author_id varchar references users(id),
+    primary key(id)
+);
+
+INSERT INTO messages VALUES(
+    0,
+    0
+);
+
+INSERT INTO messages VALUES(
+    1,
+    0
+);
diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
new file mode 100644
index 00000000..25b42fa2
--- /dev/null
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -0,0 +1,42 @@
+from django.db import connections
+
+
+class NotFound(Exception):
+    """Raised when an entity cannot be found."""
+
+    pass
+
+
+class Metricity:
+    """Abstraction for a connection to the metricity database."""
+
+    def __init__(self):
+        self.cursor = connections['metricity'].cursor()
+
+    def __enter__(self):
+        return self
+
+    def __exit__(self, *_):
+        self.cursor.close()
+
+    def user(self, user_id: str) -> dict:
+        """Query a user's data."""
+        columns = ["verified_at"]
+        query = f"SELECT {','.join(columns)} FROM users WHERE id = '%s'"
+        self.cursor.execute(query, [user_id])
+        values = self.cursor.fetchone()
+
+        if not values:
+            raise NotFound()
+
+        return dict(zip(columns, values))
+
+    def total_messages(self, user_id: str) -> int:
+        """Query total number of messages for a user."""
+        self.cursor.execute("SELECT COUNT(*) FROM messages WHERE author_id = '%s'", [user_id])
+        values = self.cursor.fetchone()
+
+        if not values:
+            raise NotFound()
+
+        return values[0]
diff --git a/pydis_site/apps/api/tests/test_users.py b/pydis_site/apps/api/tests/test_users.py
index 825e4edb..d03785ae 100644
--- a/pydis_site/apps/api/tests/test_users.py
+++ b/pydis_site/apps/api/tests/test_users.py
@@ -1,7 +1,10 @@
+from unittest.mock import patch
+
 from django_hosts.resolvers import reverse
 
 from .base import APISubdomainTestCase
 from ..models import Role, User
+from ..models.bot.metricity import NotFound
 
 
 class UnauthedUserAPITests(APISubdomainTestCase):
@@ -389,3 +392,58 @@ class UserPaginatorTests(APISubdomainTestCase):
         url = reverse("bot:user-list", host="api")
         response = self.client.get(url, {"page": 2}).json()
         self.assertEqual(1, response["previous_page_no"])
+
+
+class UserMetricityTests(APISubdomainTestCase):
+    @classmethod
+    def setUpTestData(cls):
+        User.objects.create(
+            id=0,
+            name="Test user",
+            discriminator=1,
+            in_guild=True,
+        )
+
+    def test_get_metricity_data(self):
+        # Given
+        verified_at = "foo"
+        total_messages = 1
+        self.mock_metricity_user(verified_at, total_messages)
+
+        # When
+        url = reverse('bot:user-metricity-data', args=[0], host='api')
+        response = self.client.get(url)
+
+        # Then
+        self.assertEqual(response.status_code, 200)
+        self.assertEqual(response.json(), {
+            "verified_at": verified_at,
+            "total_messages": total_messages,
+        })
+
+    def test_no_metricity_user(self):
+        # Given
+        self.mock_no_metricity_user()
+
+        # When
+        url = reverse('bot:user-metricity-data', args=[0], host='api')
+        response = self.client.get(url)
+
+        # Then
+        self.assertEqual(response.status_code, 404)
+
+    def mock_metricity_user(self, verified_at, total_messages):
+        patcher = patch("pydis_site.apps.api.viewsets.bot.user.Metricity")
+        self.metricity = patcher.start()
+        self.addCleanup(patcher.stop)
+        self.metricity = self.metricity.return_value.__enter__.return_value
+        self.metricity.user.return_value = dict(verified_at=verified_at)
+        self.metricity.total_messages.return_value = total_messages
+
+    def mock_no_metricity_user(self):
+        patcher = patch("pydis_site.apps.api.viewsets.bot.user.Metricity")
+        self.metricity = patcher.start()
+        self.addCleanup(patcher.stop)
+        self.metricity = self.metricity.return_value.__enter__.return_value
+        self.metricity.user.side_effect = NotFound()
+        self.metricity.total_messages.side_effect = NotFound()
diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py
index 3e4b627e..3ab71186 100644
--- a/pydis_site/apps/api/viewsets/bot/user.py
+++ b/pydis_site/apps/api/viewsets/bot/user.py
@@ -9,6 +9,7 @@ from rest_framework.response import Response
 from rest_framework.serializers import ModelSerializer
 from rest_framework.viewsets import ModelViewSet
 
+from pydis_site.apps.api.models.bot.metricity import Metricity, NotFound
 from pydis_site.apps.api.models.bot.user import User
 from pydis_site.apps.api.serializers import UserSerializer
 
@@ -101,6 +102,19 @@ class UserViewSet(ModelViewSet):
     - 200: returned on success
     - 404: if a user with the given `snowflake` could not be found
 
+    ### GET /bot/users/<snowflake:int>/metricity_data
+    Gets metricity data for a single user by ID.
+
+    #### Response format
+    >>> {
+    ...    "verified_at": "2020-10-06T21:54:23.540766",
+    ...    "total_messages": 2
+    ...}
+
+    #### Status codes
+    - 200: returned on success
+    - 404: if a user with the given `snowflake` could not be found
+
     ### POST /bot/users
     Adds a single or multiple new users.
     The roles attached to the user(s) must be roles known by the site.
@@ -221,3 +235,16 @@ class UserViewSet(ModelViewSet):
         serializer.save()
 
         return Response(serializer.data, status=status.HTTP_200_OK)
+
+    @action(detail=True)
+    def metricity_data(self, request: Request, pk: str = None) -> Response:
+        """Request handler for metricity_data endpoint."""
+        user = self.get_object()
+        with Metricity() as metricity:
+            try:
+                data = metricity.user(user.id)
+                data["total_messages"] = metricity.total_messages(user.id)
+                return Response(data, status=status.HTTP_200_OK)
+            except NotFound:
+                return Response(dict(detail="User not found in metricity"),
+                                status=status.HTTP_404_NOT_FOUND)
diff --git a/pydis_site/settings.py b/pydis_site/settings.py
index 5eb812ac..1ae97b86 100644
--- a/pydis_site/settings.py
+++ b/pydis_site/settings.py
@@ -172,7 +172,8 @@ WSGI_APPLICATION = 'pydis_site.wsgi.application'
 # https://docs.djangoproject.com/en/2.1/ref/settings/#databases
 
 DATABASES = {
-    'default': env.db()
+    'default': env.db(),
+    'metricity': env.db('METRICITY_DB_URL'),
 }
 
 # Password validation