From 9db412a09da490093a1dfdcc6036b8cd7fa619ee Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Tue, 27 Oct 2020 01:51:27 +0000 Subject: Add message block query --- pydis_site/apps/api/models/bot/metricity.py | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index eed1deb4..afbcbad8 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -1,5 +1,7 @@ from django.db import connections +BLOCK_INTERVAL = 10 * 60 # 10 minute blocks + class NotFound(Exception): """Raised when an entity cannot be found.""" @@ -43,3 +45,25 @@ class Metricity: raise NotFound() return values[0] + + def total_message_blocks(self, user_id: str) -> int: + """Query number of 10 minute blocks the user has been active during.""" + self.cursor.execute( + """ + SELECT + COUNT(*) + FROM ( + SELECT + to_timestamp(floor((extract('epoch' from created_at) / 600 )) * 600) + AT TIME ZONE 'UTC' AS interval + FROM messages + WHERE author_id='%s' AND NOT is_deleted GROUP BY interval) block_query; + """, + [user_id] + ) + values = self.cursor.fetchone() + + if not values: + raise NotFound() + + return values[0] -- cgit v1.2.3 From b63d79ac0f663d2062082d8cccf7dc0e072ceeb1 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Tue, 27 Oct 2020 01:51:55 +0000 Subject: Update viewset to include activity_blocks response item --- pydis_site/apps/api/viewsets/bot/user.py | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index 5205dc97..79f90163 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -110,7 +110,9 @@ class UserViewSet(ModelViewSet): #### Response format >>> { ... "verified_at": "2020-10-06T21:54:23.540766", - ... "total_messages": 2 + ... "total_messages": 2, + ... "voice_banned": False, + ... "activity_blocks": 1 ...} #### Status codes @@ -255,6 +257,7 @@ class UserViewSet(ModelViewSet): data = metricity.user(user.id) data["total_messages"] = metricity.total_messages(user.id) data["voice_banned"] = voice_banned + data["activity_blocks"] = metricity.total_message_blocks(user.id) return Response(data, status=status.HTTP_200_OK) except NotFound: return Response(dict(detail="User not found in metricity"), -- cgit v1.2.3 From 7899fe70ac654bdee58850734c2cb26ba7c04a0d Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Tue, 27 Oct 2020 01:52:02 +0000 Subject: Update tests with new activity_blocks key --- pydis_site/apps/api/tests/test_users.py | 10 +++++++--- 1 file changed, 7 insertions(+), 3 deletions(-) diff --git a/pydis_site/apps/api/tests/test_users.py b/pydis_site/apps/api/tests/test_users.py index 72ffcb3c..c422f895 100644 --- a/pydis_site/apps/api/tests/test_users.py +++ b/pydis_site/apps/api/tests/test_users.py @@ -409,7 +409,8 @@ class UserMetricityTests(APISubdomainTestCase): # Given verified_at = "foo" total_messages = 1 - self.mock_metricity_user(verified_at, total_messages) + total_blocks = 1 + self.mock_metricity_user(verified_at, total_messages, total_blocks) # When url = reverse('bot:user-metricity-data', args=[0], host='api') @@ -421,6 +422,7 @@ class UserMetricityTests(APISubdomainTestCase): "verified_at": verified_at, "total_messages": total_messages, "voice_banned": False, + "activity_blocks": total_blocks }) def test_no_metricity_user(self): @@ -440,7 +442,7 @@ class UserMetricityTests(APISubdomainTestCase): {'exception': ObjectDoesNotExist, 'voice_banned': False}, ] - self.mock_metricity_user("foo", 1) + self.mock_metricity_user("foo", 1, 1) for case in cases: with self.subTest(exception=case['exception'], voice_banned=case['voice_banned']): @@ -453,13 +455,14 @@ class UserMetricityTests(APISubdomainTestCase): self.assertEqual(response.status_code, 200) self.assertEqual(response.json()["voice_banned"], case["voice_banned"]) - def mock_metricity_user(self, verified_at, total_messages): + def mock_metricity_user(self, verified_at, total_messages, total_blocks): 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 + self.metricity.total_message_blocks.return_value = total_blocks def mock_no_metricity_user(self): patcher = patch("pydis_site.apps.api.viewsets.bot.user.Metricity") @@ -468,3 +471,4 @@ class UserMetricityTests(APISubdomainTestCase): self.metricity = self.metricity.return_value.__enter__.return_value self.metricity.user.side_effect = NotFound() self.metricity.total_messages.side_effect = NotFound() + self.metricity.total_message_blocks.side_effect = NotFound() -- cgit v1.2.3 From 2337453ef485017b519c47f31129063e0a0de011 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Tue, 27 Oct 2020 01:57:06 +0000 Subject: Clean up SQL query --- pydis_site/apps/api/models/bot/metricity.py | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index afbcbad8..93d0df71 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -57,7 +57,11 @@ class Metricity: to_timestamp(floor((extract('epoch' from created_at) / 600 )) * 600) AT TIME ZONE 'UTC' AS interval FROM messages - WHERE author_id='%s' AND NOT is_deleted GROUP BY interval) block_query; + WHERE + author_id='%s' + AND NOT is_deleted + GROUP BY interval + ) block_query; """, [user_id] ) -- cgit v1.2.3 From 8c7eab5966089a64dcf0899c4f5c28462f745cdb Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Tue, 27 Oct 2020 02:11:30 +0000 Subject: Constant was never used :man_facepalming: --- pydis_site/apps/api/models/bot/metricity.py | 7 +++++-- 1 file changed, 5 insertions(+), 2 deletions(-) diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 93d0df71..81d6e788 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -49,12 +49,15 @@ class Metricity: def total_message_blocks(self, user_id: str) -> int: """Query number of 10 minute blocks the user has been active during.""" self.cursor.execute( - """ + f""" SELECT COUNT(*) FROM ( SELECT - to_timestamp(floor((extract('epoch' from created_at) / 600 )) * 600) + to_timestamp( + floor((extract('epoch' from created_at) / {BLOCK_INTERVAL} )) + * {BLOCK_INTERVAL} + ) AT TIME ZONE 'UTC' AS interval FROM messages WHERE -- cgit v1.2.3 From 2ef2a936ca43dd615cf6fb831b868d38abb58bae Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Tue, 27 Oct 2020 02:16:40 +0000 Subject: Use SQL formatting instead of f-strings for injecting values --- pydis_site/apps/api/models/bot/metricity.py | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 81d6e788..6f03baf0 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -55,8 +55,8 @@ class Metricity: FROM ( SELECT to_timestamp( - floor((extract('epoch' from created_at) / {BLOCK_INTERVAL} )) - * {BLOCK_INTERVAL} + floor((extract('epoch' from created_at) / %d )) + * %d ) AT TIME ZONE 'UTC' AS interval FROM messages @@ -66,7 +66,7 @@ class Metricity: GROUP BY interval ) block_query; """, - [user_id] + [BLOCK_INTERVAL, BLOCK_INTERVAL, user_id] ) values = self.cursor.fetchone() -- cgit v1.2.3 From a6f3a94cab63b52fd450442bbc0b24fdafbbb580 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Tue, 27 Oct 2020 02:19:54 +0000 Subject: Remove unnecessary f-string marker --- pydis_site/apps/api/models/bot/metricity.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 6f03baf0..4313d2e2 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -49,7 +49,7 @@ class Metricity: def total_message_blocks(self, user_id: str) -> int: """Query number of 10 minute blocks the user has been active during.""" self.cursor.execute( - f""" + """ SELECT COUNT(*) FROM ( -- cgit v1.2.3 From 09d775c57b69c661a8de692c1b4a417331d5ac57 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Tue, 27 Oct 2020 02:43:59 +0000 Subject: Remove unnecessary timestamp conversion --- pydis_site/apps/api/models/bot/metricity.py | 6 +----- 1 file changed, 1 insertion(+), 5 deletions(-) diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 4313d2e2..60b2e73d 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -54,11 +54,7 @@ class Metricity: COUNT(*) FROM ( SELECT - to_timestamp( - floor((extract('epoch' from created_at) / %d )) - * %d - ) - AT TIME ZONE 'UTC' AS interval + (floor((extract('epoch' from created_at) / %d )) * %d) AS interval FROM messages WHERE author_id='%s' -- cgit v1.2.3 From c9c4b5f7b886482680d3b27e6a7b305b3bc42fc8 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Wed, 28 Oct 2020 20:53:03 +0000 Subject: Add new column to Postgres init --- postgres/init.sql | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) diff --git a/postgres/init.sql b/postgres/init.sql index 922ce1ad..75a9154c 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -16,15 +16,18 @@ INSERT INTO users VALUES ( CREATE TABLE messages ( id varchar, author_id varchar references users(id), - primary key(id) + primary key(id), + is_deleted boolean ); INSERT INTO messages VALUES( 0, - 0 + 0, + false ); INSERT INTO messages VALUES( 1, - 0 + 0, + false ); -- cgit v1.2.3 From 3595f62f00ef8786317c4af1f3522bd13f7656a1 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Fri, 30 Oct 2020 23:57:32 +0000 Subject: Update docstring in metricity block query --- pydis_site/apps/api/models/bot/metricity.py | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 60b2e73d..bb1db708 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -47,7 +47,11 @@ class Metricity: return values[0] def total_message_blocks(self, user_id: str) -> int: - """Query number of 10 minute blocks the user has been active during.""" + """ + Query number of 10 minute blocks during which the user has been active. + + This metric prevents users from spamming to achieve the message total threshold. + """ self.cursor.execute( """ SELECT -- cgit v1.2.3 From 7968dca8d3b6a9ae43e888bc045af211425acb28 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Fri, 30 Oct 2020 23:57:51 +0000 Subject: Indent metricity block query correctly --- pydis_site/apps/api/models/bot/metricity.py | 18 +++++++++--------- 1 file changed, 9 insertions(+), 9 deletions(-) diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index bb1db708..e7fc92fc 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -55,15 +55,15 @@ class Metricity: self.cursor.execute( """ SELECT - COUNT(*) - FROM ( - SELECT - (floor((extract('epoch' from created_at) / %d )) * %d) AS interval - FROM messages - WHERE - author_id='%s' - AND NOT is_deleted - GROUP BY interval + COUNT(*) + FROM ( + SELECT + (floor((extract('epoch' from created_at) / %d )) * %d) AS interval + FROM messages + WHERE + author_id='%s' + AND NOT is_deleted + GROUP BY interval ) block_query; """, [BLOCK_INTERVAL, BLOCK_INTERVAL, user_id] -- cgit v1.2.3