diff options
author | 2021-03-16 03:02:29 +0200 | |
---|---|---|
committer | 2021-03-16 03:02:29 +0200 | |
commit | 8276ba5e4a9fb9627156331d4fa5cf57e0933252 (patch) | |
tree | b99e9fc6b7153718628604b4793e4baab150cf28 | |
parent | Merge pull request #454 from python-discord/deploy-environment (diff) | |
parent | Merge branch 'main' into mbaruh/channel-activity (diff) |
Merge pull request #451 from python-discord/mbaruh/channel-activity
Add route to get a member's data for helper review
-rw-r--r-- | postgres/init.sql | 109 | ||||
-rw-r--r-- | pydis_site/apps/api/models/bot/metricity.py | 41 | ||||
-rw-r--r-- | pydis_site/apps/api/tests/test_users.py | 39 | ||||
-rw-r--r-- | pydis_site/apps/api/viewsets/bot/user.py | 31 |
4 files changed, 216 insertions, 4 deletions
diff --git a/postgres/init.sql b/postgres/init.sql index 740063e7..190a705c 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -13,12 +13,63 @@ INSERT INTO users VALUES ( current_timestamp ); +INSERT INTO users VALUES ( + 1, + current_timestamp +); + +CREATE TABLE channels ( + id varchar, + name varchar, + primary key(id) +); + +INSERT INTO channels VALUES( + '267659945086812160', + 'python-general' +); + +INSERT INTO channels VALUES( + '11', + 'help-apple' +); + +INSERT INTO channels VALUES( + '12', + 'help-cherry' +); + +INSERT INTO channels VALUES( + '21', + 'ot0-hello' +); + +INSERT INTO channels VALUES( + '22', + 'ot1-world' +); + +INSERT INTO channels VALUES( + '31', + 'voice-chat-0' +); + +INSERT INTO channels VALUES( + '32', + 'code-help-voice-0' +); + +INSERT INTO channels VALUES( + '1234', + 'zebra' +); + CREATE TABLE messages ( id varchar, author_id varchar references users(id), is_deleted boolean, created_at timestamp, - channel_id varchar, + channel_id varchar references channels(id), primary key(id) ); @@ -37,3 +88,59 @@ INSERT INTO messages VALUES( now() + INTERVAL '10 minutes,', '1234' ); + +INSERT INTO messages VALUES( + 2, + 0, + false, + now(), + '11' +); + +INSERT INTO messages VALUES( + 3, + 0, + false, + now(), + '12' +); + +INSERT INTO messages VALUES( + 4, + 1, + false, + now(), + '21' +); + +INSERT INTO messages VALUES( + 5, + 1, + false, + now(), + '22' +); + +INSERT INTO messages VALUES( + 6, + 1, + false, + now(), + '31' +); + +INSERT INTO messages VALUES( + 7, + 1, + false, + now(), + '32' +); + +INSERT INTO messages VALUES( + 8, + 1, + true, + now(), + '32' +); diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index cae630f1..5daa5c66 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -1,3 +1,5 @@ +from typing import List, Tuple + from django.db import connections BLOCK_INTERVAL = 10 * 60 # 10 minute blocks @@ -89,3 +91,42 @@ class Metricity: raise NotFound() return values[0] + + def top_channel_activity(self, user_id: str) -> List[Tuple[str, int]]: + """ + Query the top three channels in which the user is most active. + + Help channels are grouped under "the help channels", + and off-topic channels are grouped under "off-topic". + """ + self.cursor.execute( + """ + SELECT + CASE + WHEN channels.name ILIKE 'help-%%' THEN 'the help channels' + WHEN channels.name ILIKE 'ot%%' THEN 'off-topic' + WHEN channels.name ILIKE '%%voice%%' THEN 'voice chats' + ELSE channels.name + END, + COUNT(1) + FROM + messages + LEFT JOIN channels ON channels.id = messages.channel_id + WHERE + author_id = '%s' AND NOT messages.is_deleted + GROUP BY + 1 + ORDER BY + 2 DESC + LIMIT + 3; + """, + [user_id] + ) + + values = self.cursor.fetchall() + + if not values: + raise NotFound() + + return values diff --git a/pydis_site/apps/api/tests/test_users.py b/pydis_site/apps/api/tests/test_users.py index 69bbfefc..c43b916a 100644 --- a/pydis_site/apps/api/tests/test_users.py +++ b/pydis_site/apps/api/tests/test_users.py @@ -410,7 +410,7 @@ class UserMetricityTests(APISubdomainTestCase): joined_at = "foo" total_messages = 1 total_blocks = 1 - self.mock_metricity_user(joined_at, total_messages, total_blocks) + self.mock_metricity_user(joined_at, total_messages, total_blocks, []) # When url = reverse('bot:user-metricity-data', args=[0], host='api') @@ -436,13 +436,24 @@ class UserMetricityTests(APISubdomainTestCase): # Then self.assertEqual(response.status_code, 404) + def test_no_metricity_user_for_review(self): + # Given + self.mock_no_metricity_user() + + # When + url = reverse('bot:user-metricity-review-data', args=[0], host='api') + response = self.client.get(url) + + # Then + self.assertEqual(response.status_code, 404) + def test_metricity_voice_banned(self): cases = [ {'exception': None, 'voice_banned': True}, {'exception': ObjectDoesNotExist, 'voice_banned': False}, ] - self.mock_metricity_user("foo", 1, 1) + self.mock_metricity_user("foo", 1, 1, [["bar", 1]]) for case in cases: with self.subTest(exception=case['exception'], voice_banned=case['voice_banned']): @@ -455,7 +466,27 @@ class UserMetricityTests(APISubdomainTestCase): self.assertEqual(response.status_code, 200) self.assertEqual(response.json()["voice_banned"], case["voice_banned"]) - def mock_metricity_user(self, joined_at, total_messages, total_blocks): + def test_metricity_review_data(self): + # Given + joined_at = "foo" + total_messages = 10 + total_blocks = 1 + channel_activity = [["bar", 4], ["buzz", 6]] + self.mock_metricity_user(joined_at, total_messages, total_blocks, channel_activity) + + # When + url = reverse('bot:user-metricity-review-data', args=[0], host='api') + response = self.client.get(url) + + # Then + self.assertEqual(response.status_code, 200) + self.assertEqual(response.json(), { + "joined_at": joined_at, + "top_channel_activity": channel_activity, + "total_messages": total_messages + }) + + def mock_metricity_user(self, joined_at, total_messages, total_blocks, top_channel_activity): patcher = patch("pydis_site.apps.api.viewsets.bot.user.Metricity") self.metricity = patcher.start() self.addCleanup(patcher.stop) @@ -463,6 +494,7 @@ class UserMetricityTests(APISubdomainTestCase): self.metricity.user.return_value = dict(joined_at=joined_at) self.metricity.total_messages.return_value = total_messages self.metricity.total_message_blocks.return_value = total_blocks + self.metricity.top_channel_activity.return_value = top_channel_activity def mock_no_metricity_user(self): patcher = patch("pydis_site.apps.api.viewsets.bot.user.Metricity") @@ -472,3 +504,4 @@ class UserMetricityTests(APISubdomainTestCase): self.metricity.user.side_effect = NotFound() self.metricity.total_messages.side_effect = NotFound() self.metricity.total_message_blocks.side_effect = NotFound() + self.metricity.top_channel_activity.side_effect = NotFound() diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index 829e2694..25722f5a 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -119,6 +119,22 @@ 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_review_data + Gets metricity data for a single user's review by ID. + + #### Response format + >>> { + ... 'joined_at': '2020-08-26T08:09:43.507000', + ... 'top_channel_activity': [['off-topic', 15], + ... ['talent-pool', 4], + ... ['defcon', 2]], + ... 'total_messages': 22 + ... } + + #### 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. @@ -262,3 +278,18 @@ class UserViewSet(ModelViewSet): except NotFound: return Response(dict(detail="User not found in metricity"), status=status.HTTP_404_NOT_FOUND) + + @action(detail=True) + def metricity_review_data(self, request: Request, pk: str = None) -> Response: + """Request handler for metricity_review_data endpoint.""" + user = self.get_object() + + with Metricity() as metricity: + try: + data = metricity.user(user.id) + data["total_messages"] = metricity.total_messages(user.id) + data["top_channel_activity"] = metricity.top_channel_activity(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) |