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(-) (limited to 'postgres') 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 6804f4f0f19d7c0047870b7da4b79ab099140b98 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Sun, 1 Nov 2020 18:41:51 +0000 Subject: Add created_at to metricity database bootstrap --- postgres/init.sql | 11 +++++++---- 1 file changed, 7 insertions(+), 4 deletions(-) (limited to 'postgres') diff --git a/postgres/init.sql b/postgres/init.sql index 75a9154c..40538492 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -16,18 +16,21 @@ INSERT INTO users VALUES ( CREATE TABLE messages ( id varchar, author_id varchar references users(id), - primary key(id), - is_deleted boolean + is_deleted boolean, + created_at timestamp, + primary key(id) ); INSERT INTO messages VALUES( 0, 0, - false + false, + now() ); INSERT INTO messages VALUES( 1, 0, - false + false, + now() + INTERVAL '10 minutes' ); -- cgit v1.2.3 From 23bf022cf8fae66225e829c4a0abbccb2940edb7 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Mon, 2 Nov 2020 18:25:27 +0000 Subject: Exclude bot commands and seasonalbot commands from voice gate --- postgres/init.sql | 7 +++++-- pydis_site/apps/api/models/bot/metricity.py | 20 +++++++++++++++++--- 2 files changed, 22 insertions(+), 5 deletions(-) (limited to 'postgres') diff --git a/postgres/init.sql b/postgres/init.sql index 40538492..c77fec9e 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -18,6 +18,7 @@ CREATE TABLE messages ( author_id varchar references users(id), is_deleted boolean, created_at timestamp, + channel_id varchar, primary key(id) ); @@ -25,12 +26,14 @@ INSERT INTO messages VALUES( 0, 0, false, - now() + now(), + '267659945086812160' ); INSERT INTO messages VALUES( 1, 0, false, - now() + INTERVAL '10 minutes' + now() + INTERVAL '10 minutes,', + '1234' ); diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 379b0757..29d03d8b 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -2,6 +2,11 @@ from django.db import connections BLOCK_INTERVAL = 10 * 60 # 10 minute blocks +EXCLUDE_CHANNELS = [ + "267659945086812160", # Bot commands + "607247579608121354" # SeasonalBot commands +] + class NotFound(Exception): """Raised when an entity cannot be found.""" @@ -36,8 +41,16 @@ class Metricity: 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' AND NOT is_deleted", - [user_id] + """ + SELECT + COUNT(*) + FROM messages + WHERE + author_id = '%s' + AND NOT is_deleted + AND NOT %s::varchar[] @> ARRAY[channel_id] + """, + [user_id, EXCLUDE_CHANNELS] ) values = self.cursor.fetchone() @@ -63,10 +76,11 @@ class Metricity: WHERE author_id='%s' AND NOT is_deleted + AND NOT %s::varchar[] @> ARRAY[channel_id] GROUP BY interval ) block_query; """, - [BLOCK_INTERVAL, BLOCK_INTERVAL, user_id] + [BLOCK_INTERVAL, BLOCK_INTERVAL, user_id, EXCLUDE_CHANNELS] ) values = self.cursor.fetchone() -- cgit v1.2.3 From eff717bb38e5b556751ba4f23a53cc6662baa7dd Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Sat, 19 Dec 2020 03:43:23 +0000 Subject: Update verified_at fields to joined_at fields --- postgres/init.sql | 2 +- pydis_site/apps/api/tests/test_users.py | 10 +++++----- pydis_site/apps/api/viewsets/bot/user.py | 2 +- 3 files changed, 7 insertions(+), 7 deletions(-) (limited to 'postgres') diff --git a/postgres/init.sql b/postgres/init.sql index c77fec9e..740063e7 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -4,7 +4,7 @@ CREATE DATABASE metricity; CREATE TABLE users ( id varchar, - verified_at timestamp, + joined_at timestamp, primary key(id) ); diff --git a/pydis_site/apps/api/tests/test_users.py b/pydis_site/apps/api/tests/test_users.py index c422f895..69bbfefc 100644 --- a/pydis_site/apps/api/tests/test_users.py +++ b/pydis_site/apps/api/tests/test_users.py @@ -407,10 +407,10 @@ class UserMetricityTests(APISubdomainTestCase): def test_get_metricity_data(self): # Given - verified_at = "foo" + joined_at = "foo" total_messages = 1 total_blocks = 1 - self.mock_metricity_user(verified_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') @@ -419,7 +419,7 @@ class UserMetricityTests(APISubdomainTestCase): # Then self.assertEqual(response.status_code, 200) self.assertEqual(response.json(), { - "verified_at": verified_at, + "joined_at": joined_at, "total_messages": total_messages, "voice_banned": False, "activity_blocks": total_blocks @@ -455,12 +455,12 @@ 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, total_blocks): + def mock_metricity_user(self, joined_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.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 diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index 79f90163..829e2694 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -109,7 +109,7 @@ class UserViewSet(ModelViewSet): #### Response format >>> { - ... "verified_at": "2020-10-06T21:54:23.540766", + ... "joined_at": "2020-10-06T21:54:23.540766", ... "total_messages": 2, ... "voice_banned": False, ... "activity_blocks": 1 -- cgit v1.2.3