From c8c6cb8754bd0917e35eb157925028a9b6f1dcb9 Mon Sep 17 00:00:00 2001 From: Lucas Lindström Date: Tue, 6 Oct 2020 21:29:34 +0200 Subject: Added metricity db connection and user bot API --- pydis_site/apps/api/viewsets/bot/user.py | 46 ++++++++++++++++++++++++++++++++ pydis_site/settings.py | 3 ++- 2 files changed, 48 insertions(+), 1 deletion(-) (limited to 'pydis_site') diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index 9571b3d7..0eeacbb3 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -1,3 +1,10 @@ +import json + +from django.db import connections +from rest_framework import status +from rest_framework.decorators import action +from rest_framework.request import Request +from rest_framework.response import Response from rest_framework.viewsets import ModelViewSet from rest_framework_bulk import BulkCreateModelMixin @@ -53,6 +60,29 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): - 200: returned on success - 404: if a user with the given `snowflake` could not be found + ### GET /bot/users//metricity_data + Gets metricity data for a single user by ID. + + #### Response format + >>> { + ... "id": "0", + ... "name": "foo", + ... "avatar_hash": "bar", + ... "joined_at": "2020-10-06T18:17:30.101677", + ... "created_at": "2020-10-06T18:17:30.101677", + ... "is_staff": False, + ... "opt_out": False, + ... "bot": False, + ... "is_guild": True, + ... "is_verified": False, + ... "public_flags": {}, + ... "verified_at": null + ...} + + #### 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. @@ -115,7 +145,23 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): #### Status codes - 204: returned on success - 404: if a user with the given `snowflake` does not exist + + """ serializer_class = UserSerializer queryset = User.objects + + @action(detail=True) + def metricity_data(self, request: Request, pk: str = None) -> Response: + """Request handler for metricity_data endpoint.""" + user = self.get_object() + column_keys = ["id", "name", "avatar_hash", "joined_at", "created_at", "is_staff", + "opt_out", "bot", "is_guild", "is_verified", "public_flags", "verified_at"] + with connections['metricity'].cursor() as cursor: + query = f"SELECT {','.join(column_keys)} FROM users WHERE id = '%s'" + cursor.execute(query, [user.id]) + values = cursor.fetchone() + data = dict(zip(column_keys, values)) + data["public_flags"] = json.loads(data["public_flags"]) + return Response(data, status=status.HTTP_200_OK) diff --git a/pydis_site/settings.py b/pydis_site/settings.py index 3769fa25..2e78e458 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 -- cgit v1.2.3 From a188ab8ebfa9299addb7fd0effce2de7a0509645 Mon Sep 17 00:00:00 2001 From: Lucas Lindström Date: Tue, 6 Oct 2020 22:02:08 +0200 Subject: Fix minor style issue. --- pydis_site/apps/api/viewsets/bot/user.py | 2 -- 1 file changed, 2 deletions(-) (limited to 'pydis_site') diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index 0eeacbb3..059bc0f0 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -145,8 +145,6 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): #### Status codes - 204: returned on success - 404: if a user with the given `snowflake` does not exist - - """ serializer_class = UserSerializer -- cgit v1.2.3 From 15086b4ab392a8bdc6c33414f0c4e2a294f4a2ef Mon Sep 17 00:00:00 2001 From: Lucas Lindström Date: Tue, 6 Oct 2020 22:40:14 +0200 Subject: Added total message count to metricity data response. --- postgres/init.sql | 16 ++++++++++++++++ pydis_site/apps/api/viewsets/bot/user.py | 9 ++++++++- 2 files changed, 24 insertions(+), 1 deletion(-) (limited to 'pydis_site') diff --git a/postgres/init.sql b/postgres/init.sql index fd29ddbc..45ad440c 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -32,3 +32,19 @@ INSERT INTO users VALUES ( '{}', NULL ); + +CREATE TABLE messages ( + id varchar(255), + author_id varchar(255) 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/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index 059bc0f0..b3d880cc 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -76,7 +76,8 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): ... "is_guild": True, ... "is_verified": False, ... "public_flags": {}, - ... "verified_at": null + ... "verified_at": None, + ... "total_messages": 2 ...} #### Status codes @@ -157,9 +158,15 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): column_keys = ["id", "name", "avatar_hash", "joined_at", "created_at", "is_staff", "opt_out", "bot", "is_guild", "is_verified", "public_flags", "verified_at"] with connections['metricity'].cursor() as cursor: + # Get user data query = f"SELECT {','.join(column_keys)} FROM users WHERE id = '%s'" cursor.execute(query, [user.id]) values = cursor.fetchone() data = dict(zip(column_keys, values)) data["public_flags"] = json.loads(data["public_flags"]) + + # Get message count + cursor.execute("SELECT COUNT(*) FROM messages WHERE author_id = '%s'", [user.id]) + data["total_messages"], = cursor.fetchone() + return Response(data, status=status.HTTP_200_OK) -- cgit v1.2.3 From e83f445a9b8d2db4523e261759bb73ea83ed54c3 Mon Sep 17 00:00:00 2001 From: Lucas Lindström Date: Tue, 6 Oct 2020 23:56:47 +0200 Subject: Reduce metricity db setup script and API response to the bare necessities. --- postgres/init.sql | 28 ++++------------------------ pydis_site/apps/api/viewsets/bot/user.py | 29 ++++------------------------- 2 files changed, 8 insertions(+), 49 deletions(-) (limited to 'pydis_site') diff --git a/postgres/init.sql b/postgres/init.sql index 45ad440c..922ce1ad 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -3,39 +3,19 @@ CREATE DATABASE metricity; \c metricity; CREATE TABLE users ( - id varchar(255), - name varchar(255) not null, - avatar_hash varchar(255), - joined_at timestamp not null, - created_at timestamp not null, - is_staff boolean not null, - opt_out boolean default false, - bot boolean default false, - is_guild boolean default true, - is_verified boolean default false, - public_flags text default '{}', + id varchar, verified_at timestamp, primary key(id) ); INSERT INTO users VALUES ( 0, - 'foo', - 'bar', - current_timestamp, - current_timestamp, - false, - false, - false, - true, - false, - '{}', - NULL + current_timestamp ); CREATE TABLE messages ( - id varchar(255), - author_id varchar(255) references users(id), + id varchar, + author_id varchar references users(id), primary key(id) ); diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index b3d880cc..1b1af841 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -1,5 +1,3 @@ -import json - from django.db import connections from rest_framework import status from rest_framework.decorators import action @@ -65,18 +63,7 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): #### Response format >>> { - ... "id": "0", - ... "name": "foo", - ... "avatar_hash": "bar", - ... "joined_at": "2020-10-06T18:17:30.101677", - ... "created_at": "2020-10-06T18:17:30.101677", - ... "is_staff": False, - ... "opt_out": False, - ... "bot": False, - ... "is_guild": True, - ... "is_verified": False, - ... "public_flags": {}, - ... "verified_at": None, + ... "verified_at": "2020-10-06T21:54:23.540766", ... "total_messages": 2 ...} @@ -155,18 +142,10 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): def metricity_data(self, request: Request, pk: str = None) -> Response: """Request handler for metricity_data endpoint.""" user = self.get_object() - column_keys = ["id", "name", "avatar_hash", "joined_at", "created_at", "is_staff", - "opt_out", "bot", "is_guild", "is_verified", "public_flags", "verified_at"] with connections['metricity'].cursor() as cursor: - # Get user data - query = f"SELECT {','.join(column_keys)} FROM users WHERE id = '%s'" - cursor.execute(query, [user.id]) - values = cursor.fetchone() - data = dict(zip(column_keys, values)) - data["public_flags"] = json.loads(data["public_flags"]) - - # Get message count + data = {} + cursor.execute("SELECT verified_at FROM users WHERE id = '%s'", [user.id]) + data["verified_at"], = cursor.fetchone() cursor.execute("SELECT COUNT(*) FROM messages WHERE author_id = '%s'", [user.id]) data["total_messages"], = cursor.fetchone() - return Response(data, status=status.HTTP_200_OK) -- cgit v1.2.3 From 484eba7715fcbcc195d66f5a60ff56c8167ecf0e Mon Sep 17 00:00:00 2001 From: Lucas Lindström Date: Thu, 8 Oct 2020 00:17:47 +0200 Subject: Broke out metricity connection into an abstraction and added metricity endpoint unit tests. --- .coveragerc | 1 + pydis_site/apps/api/models/bot/metricity.py | 42 +++++++++++++++++++++ pydis_site/apps/api/tests/test_users.py | 58 +++++++++++++++++++++++++++++ pydis_site/apps/api/viewsets/bot/user.py | 17 +++++---- 4 files changed, 110 insertions(+), 8 deletions(-) create mode 100644 pydis_site/apps/api/models/bot/metricity.py (limited to 'pydis_site') 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/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 a02fce8a..76a21d3a 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): @@ -170,3 +173,58 @@ class UserModelTests(APISubdomainTestCase): def test_correct_username_formatting(self): """Tests the username property with both name and discriminator formatted together.""" self.assertEqual(self.user_with_roles.username, "Test User with two roles#0001") + + +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 1b1af841..352d77c0 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -1,4 +1,3 @@ -from django.db import connections from rest_framework import status from rest_framework.decorators import action from rest_framework.request import Request @@ -6,6 +5,7 @@ from rest_framework.response import Response from rest_framework.viewsets import ModelViewSet from rest_framework_bulk import BulkCreateModelMixin +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 @@ -142,10 +142,11 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): def metricity_data(self, request: Request, pk: str = None) -> Response: """Request handler for metricity_data endpoint.""" user = self.get_object() - with connections['metricity'].cursor() as cursor: - data = {} - cursor.execute("SELECT verified_at FROM users WHERE id = '%s'", [user.id]) - data["verified_at"], = cursor.fetchone() - cursor.execute("SELECT COUNT(*) FROM messages WHERE author_id = '%s'", [user.id]) - data["total_messages"], = cursor.fetchone() - return Response(data, status=status.HTTP_200_OK) + 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) -- cgit v1.2.3