From c8c6cb8754bd0917e35eb157925028a9b6f1dcb9 Mon Sep 17 00:00:00 2001
From: Lucas Lindström <lucas@britepaymentgroup.com>
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/init.sql')

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 <lucas@britepaymentgroup.com>
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/init.sql')

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 <lucas@britepaymentgroup.com>
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/init.sql')

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

