aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorGravatar Boris Muratov <[email protected]>2021-03-16 03:02:29 +0200
committerGravatar GitHub <[email protected]>2021-03-16 03:02:29 +0200
commit8276ba5e4a9fb9627156331d4fa5cf57e0933252 (patch)
treeb99e9fc6b7153718628604b4793e4baab150cf28
parentMerge pull request #454 from python-discord/deploy-environment (diff)
parentMerge 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.sql109
-rw-r--r--pydis_site/apps/api/models/bot/metricity.py41
-rw-r--r--pydis_site/apps/api/tests/test_users.py39
-rw-r--r--pydis_site/apps/api/viewsets/bot/user.py31
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)