From c8c6cb8754bd0917e35eb157925028a9b6f1dcb9 Mon Sep 17 00:00:00 2001 From: Lucas Lindström Date: Tue, 6 Oct 2020 21:29:34 +0200 Subject: Added metricity db connection and user bot API --- postgres/init.sql | 34 ++++++++++++++++++++++++++++++++++ 1 file changed, 34 insertions(+) create mode 100644 postgres/init.sql (limited to 'postgres') diff --git a/postgres/init.sql b/postgres/init.sql new file mode 100644 index 00000000..fd29ddbc --- /dev/null +++ b/postgres/init.sql @@ -0,0 +1,34 @@ +CREATE DATABASE metricity; + +\c metricity; + +CREATE TABLE users ( + id varchar(255), + name varchar(255) not null, + avatar_hash varchar(255), + joined_at timestamp not null, + created_at timestamp not null, + is_staff boolean not null, + opt_out boolean default false, + bot boolean default false, + is_guild boolean default true, + is_verified boolean default false, + public_flags text default '{}', + verified_at timestamp, + primary key(id) +); + +INSERT INTO users VALUES ( + 0, + 'foo', + 'bar', + current_timestamp, + current_timestamp, + false, + false, + false, + true, + false, + '{}', + NULL +); -- cgit v1.2.3 From 15086b4ab392a8bdc6c33414f0c4e2a294f4a2ef Mon Sep 17 00:00:00 2001 From: Lucas Lindström Date: Tue, 6 Oct 2020 22:40:14 +0200 Subject: Added total message count to metricity data response. --- postgres/init.sql | 16 ++++++++++++++++ pydis_site/apps/api/viewsets/bot/user.py | 9 ++++++++- 2 files changed, 24 insertions(+), 1 deletion(-) (limited to 'postgres') diff --git a/postgres/init.sql b/postgres/init.sql index fd29ddbc..45ad440c 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -32,3 +32,19 @@ INSERT INTO users VALUES ( '{}', NULL ); + +CREATE TABLE messages ( + id varchar(255), + author_id varchar(255) references users(id), + primary key(id) +); + +INSERT INTO messages VALUES( + 0, + 0 +); + +INSERT INTO messages VALUES( + 1, + 0 +); diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index 059bc0f0..b3d880cc 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -76,7 +76,8 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): ... "is_guild": True, ... "is_verified": False, ... "public_flags": {}, - ... "verified_at": null + ... "verified_at": None, + ... "total_messages": 2 ...} #### Status codes @@ -157,9 +158,15 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): column_keys = ["id", "name", "avatar_hash", "joined_at", "created_at", "is_staff", "opt_out", "bot", "is_guild", "is_verified", "public_flags", "verified_at"] with connections['metricity'].cursor() as cursor: + # Get user data query = f"SELECT {','.join(column_keys)} FROM users WHERE id = '%s'" cursor.execute(query, [user.id]) values = cursor.fetchone() data = dict(zip(column_keys, values)) data["public_flags"] = json.loads(data["public_flags"]) + + # Get message count + cursor.execute("SELECT COUNT(*) FROM messages WHERE author_id = '%s'", [user.id]) + data["total_messages"], = cursor.fetchone() + return Response(data, status=status.HTTP_200_OK) -- cgit v1.2.3 From e83f445a9b8d2db4523e261759bb73ea83ed54c3 Mon Sep 17 00:00:00 2001 From: Lucas Lindström Date: Tue, 6 Oct 2020 23:56:47 +0200 Subject: Reduce metricity db setup script and API response to the bare necessities. --- postgres/init.sql | 28 ++++------------------------ pydis_site/apps/api/viewsets/bot/user.py | 29 ++++------------------------- 2 files changed, 8 insertions(+), 49 deletions(-) (limited to 'postgres') diff --git a/postgres/init.sql b/postgres/init.sql index 45ad440c..922ce1ad 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -3,39 +3,19 @@ CREATE DATABASE metricity; \c metricity; CREATE TABLE users ( - id varchar(255), - name varchar(255) not null, - avatar_hash varchar(255), - joined_at timestamp not null, - created_at timestamp not null, - is_staff boolean not null, - opt_out boolean default false, - bot boolean default false, - is_guild boolean default true, - is_verified boolean default false, - public_flags text default '{}', + id varchar, verified_at timestamp, primary key(id) ); INSERT INTO users VALUES ( 0, - 'foo', - 'bar', - current_timestamp, - current_timestamp, - false, - false, - false, - true, - false, - '{}', - NULL + current_timestamp ); CREATE TABLE messages ( - id varchar(255), - author_id varchar(255) references users(id), + id varchar, + author_id varchar references users(id), primary key(id) ); diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index b3d880cc..1b1af841 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -1,5 +1,3 @@ -import json - from django.db import connections from rest_framework import status from rest_framework.decorators import action @@ -65,18 +63,7 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): #### Response format >>> { - ... "id": "0", - ... "name": "foo", - ... "avatar_hash": "bar", - ... "joined_at": "2020-10-06T18:17:30.101677", - ... "created_at": "2020-10-06T18:17:30.101677", - ... "is_staff": False, - ... "opt_out": False, - ... "bot": False, - ... "is_guild": True, - ... "is_verified": False, - ... "public_flags": {}, - ... "verified_at": None, + ... "verified_at": "2020-10-06T21:54:23.540766", ... "total_messages": 2 ...} @@ -155,18 +142,10 @@ class UserViewSet(BulkCreateModelMixin, ModelViewSet): def metricity_data(self, request: Request, pk: str = None) -> Response: """Request handler for metricity_data endpoint.""" user = self.get_object() - column_keys = ["id", "name", "avatar_hash", "joined_at", "created_at", "is_staff", - "opt_out", "bot", "is_guild", "is_verified", "public_flags", "verified_at"] with connections['metricity'].cursor() as cursor: - # Get user data - query = f"SELECT {','.join(column_keys)} FROM users WHERE id = '%s'" - cursor.execute(query, [user.id]) - values = cursor.fetchone() - data = dict(zip(column_keys, values)) - data["public_flags"] = json.loads(data["public_flags"]) - - # Get message count + data = {} + cursor.execute("SELECT verified_at FROM users WHERE id = '%s'", [user.id]) + data["verified_at"], = cursor.fetchone() cursor.execute("SELECT COUNT(*) FROM messages WHERE author_id = '%s'", [user.id]) data["total_messages"], = cursor.fetchone() - return Response(data, status=status.HTTP_200_OK) -- cgit v1.2.3