From 730efe6cff7b61610d97bc6d3401acf5617bd17b Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Sun, 18 Oct 2020 19:38:32 +0100 Subject: Exclude deleted messages from total message count --- pydis_site/apps/api/models/bot/metricity.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'pydis_site/apps/api/models') diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 25b42fa2..cdfbb499 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -33,7 +33,7 @@ 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'", [user_id]) + self.cursor.execute("SELECT COUNT(*) FROM messages WHERE author_id = '%s' AND NOT is_deleted", [user_id]) values = self.cursor.fetchone() if not values: -- cgit v1.2.3 From d0853d28b34789756d03f414dc33fa84bc774142 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Sun, 18 Oct 2020 19:42:39 +0100 Subject: Split query into multiple lines for total messages --- pydis_site/apps/api/models/bot/metricity.py | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'pydis_site/apps/api/models') diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index cdfbb499..eed1deb4 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -33,7 +33,10 @@ 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]) + self.cursor.execute( + "SELECT COUNT(*) FROM messages WHERE author_id = '%s' AND NOT is_deleted", + [user_id] + ) values = self.cursor.fetchone() if not values: -- cgit v1.2.3 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(+) (limited to 'pydis_site/apps/api/models') 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 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(-) (limited to 'pydis_site/apps/api/models') 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(-) (limited to 'pydis_site/apps/api/models') 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(-) (limited to 'pydis_site/apps/api/models') 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(-) (limited to 'pydis_site/apps/api/models') 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(-) (limited to 'pydis_site/apps/api/models') 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 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(-) (limited to 'pydis_site/apps/api/models') 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(-) (limited to 'pydis_site/apps/api/models') 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 From d8e387b40cd6f3987a0815bf46b7b19888e83be9 Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Sat, 31 Oct 2020 00:13:08 +0000 Subject: Change format character in metricity data endpoint --- pydis_site/apps/api/models/bot/metricity.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'pydis_site/apps/api/models') diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index e7fc92fc..379b0757 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -58,7 +58,7 @@ class Metricity: COUNT(*) FROM ( SELECT - (floor((extract('epoch' from created_at) / %d )) * %d) AS interval + (floor((extract('epoch' from created_at) / %s )) * %s) AS interval FROM messages WHERE author_id='%s' -- 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 'pydis_site/apps/api/models') 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 e1008870619f92bc76ed61eb9adbc6ada795b23f Mon Sep 17 00:00:00 2001 From: Joe Banks Date: Sat, 19 Dec 2020 03:40:25 +0000 Subject: Swap verified_at for joined_at --- pydis_site/apps/api/models/bot/metricity.py | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'pydis_site/apps/api/models') diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 29d03d8b..cae630f1 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -28,7 +28,8 @@ class Metricity: def user(self, user_id: str) -> dict: """Query a user's data.""" - columns = ["verified_at"] + # TODO: Swap this back to some sort of verified at date + columns = ["joined_at"] query = f"SELECT {','.join(columns)} FROM users WHERE id = '%s'" self.cursor.execute(query, [user_id]) values = self.cursor.fetchone() -- cgit v1.2.3