From 78d82791afb8fd3da4f767393b918ee401b41aec Mon Sep 17 00:00:00 2001
From: Chris Lovering <chris.lovering.95@gmail.com>
Date: Sun, 5 Sep 2021 22:35:31 +0100
Subject: Initialise metricity at runtime

Currently the bot cannot start in dev as the site errors, saying that metricity doesn't exist. Previously this note existing was fine, unless you needed to use metricity data. With the recent addition of django-prometheus, metricity is now required on boot.

This PR moves the init of metricity from a docker-compose volume, into running of the site. This means that external projects using site, that don't have access to the init.sql file to mount a volume, now also init metricity.
---
 postgres/init.sql | 48 ++++++++++++++++++++++--------------------------
 1 file changed, 22 insertions(+), 26 deletions(-)

(limited to 'postgres/init.sql')

diff --git a/postgres/init.sql b/postgres/init.sql
index 190a705c..ea748480 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -1,8 +1,4 @@
-CREATE DATABASE metricity;
-
-\c metricity;
-
-CREATE TABLE users (
+CREATE TABLE IF NOT EXISTS users (
     id varchar,
     joined_at timestamp,
     primary key(id)
@@ -11,14 +7,14 @@ CREATE TABLE users (
 INSERT INTO users VALUES (
     0,
     current_timestamp
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO users VALUES (
     1,
     current_timestamp
-);
+) ON CONFLICT (id) DO NOTHING;
 
-CREATE TABLE channels (
+CREATE TABLE IF NOT EXISTS channels (
     id varchar,
     name varchar,
     primary key(id)
@@ -27,44 +23,44 @@ CREATE TABLE channels (
 INSERT INTO channels VALUES(
     '267659945086812160',
     'python-general'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO channels VALUES(
     '11',
     'help-apple'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO channels VALUES(
     '12',
     'help-cherry'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO channels VALUES(
     '21',
     'ot0-hello'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO channels VALUES(
     '22',
     'ot1-world'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO channels VALUES(
     '31',
     'voice-chat-0'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO channels VALUES(
     '32',
     'code-help-voice-0'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO channels VALUES(
     '1234',
     'zebra'
-);
+) ON CONFLICT (id) DO NOTHING;
 
-CREATE TABLE messages (
+CREATE TABLE IF NOT EXISTS messages (
     id varchar,
     author_id varchar references users(id),
     is_deleted boolean,
@@ -79,7 +75,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '267659945086812160'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO messages VALUES(
     1,
@@ -87,7 +83,7 @@ INSERT INTO messages VALUES(
     false,
     now() + INTERVAL '10 minutes,',
     '1234'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO messages VALUES(
     2,
@@ -95,7 +91,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '11'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO messages VALUES(
     3,
@@ -103,7 +99,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '12'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO messages VALUES(
     4,
@@ -111,7 +107,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '21'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO messages VALUES(
     5,
@@ -119,7 +115,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '22'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO messages VALUES(
     6,
@@ -127,7 +123,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '31'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO messages VALUES(
     7,
@@ -135,7 +131,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '32'
-);
+) ON CONFLICT (id) DO NOTHING;
 
 INSERT INTO messages VALUES(
     8,
@@ -143,4 +139,4 @@ INSERT INTO messages VALUES(
     true,
     now(),
     '32'
-);
+) ON CONFLICT (id) DO NOTHING;
-- 
cgit v1.2.3


From d245bbd600811762a74544aeff6086a6c94dd7f3 Mon Sep 17 00:00:00 2001
From: Chris Lovering <chris.lovering.95@gmail.com>
Date: Tue, 7 Sep 2021 09:59:18 +0100
Subject: Create and populate metricity in a single connection

---
 manage.py         | 35 ++++++++++-------------------------
 postgres/init.sql | 21 +++++++++++++++++++++
 2 files changed, 31 insertions(+), 25 deletions(-)

(limited to 'postgres/init.sql')

diff --git a/manage.py b/manage.py
index cb4eb567..308f917d 100755
--- a/manage.py
+++ b/manage.py
@@ -152,36 +152,21 @@ class SiteManager:
         from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
 
         db_url_parts = SiteManager.parse_db_url(os.environ["DATABASE_URL"])
-        db_connection_kwargs = {
-            "host": db_url_parts.hostname,
-            "port": db_url_parts.port,
-            "user": db_url_parts.username,
-            "password": db_url_parts.password,
-        }
-        # Connect to pysite first to create metricity db
         conn = psycopg2.connect(
-            database=db_url_parts.path[1:],
-            **db_connection_kwargs
+            host=db_url_parts.hostname,
+            port=db_url_parts.port,
+            user=db_url_parts.username,
+            password=db_url_parts.password,
+            database=db_url_parts.path[1:]
         )
+        # Required to create a db from `cursor.execute()`
         conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
 
-        with conn.cursor() as cursor:
-            cursor.execute("SELECT 1 FROM pg_catalog.pg_database WHERE datname = 'metricity'")
-            exists = cursor.fetchone()
-            if exists:
-                # Assume metricity is already populated if it exists
-                return
-            print("Creating metricity relations and populating with some data.")
-            cursor.execute("CREATE DATABASE metricity")
-        conn.close()
-
-        # Switch connection to metricity and initialise some data
-        conn = psycopg2.connect(
-            database="metricity",
-            **db_connection_kwargs
-        )
         with conn.cursor() as cursor, open("postgres/init.sql", encoding="utf-8") as f:
-            cursor.execute(f.read())
+            cursor.execute(
+                f.read(),
+                ("metricity", db_url_parts.username, db_url_parts.password)
+            )
         conn.close()
 
     def prepare_server(self) -> None:
diff --git a/postgres/init.sql b/postgres/init.sql
index ea748480..ee1acd8d 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -1,3 +1,24 @@
+DO
+$do$
+DECLARE
+  _db TEXT := %s;
+  _user TEXT := %s;
+  _password TEXT := %s;
+BEGIN
+  CREATE EXTENSION IF NOT EXISTS dblink;
+  IF EXISTS (SELECT 1 FROM pg_database WHERE datname = _db) THEN
+    RAISE NOTICE 'Database already exists';
+  ELSE
+    PERFORM dblink_connect(
+        'host=localhost user=' || _user ||
+        ' password=' || _password ||
+        ' dbname=' || current_database()
+    );
+    PERFORM dblink_exec('CREATE DATABASE ' || _db);
+  END IF;
+END
+$do$;
+
 CREATE TABLE IF NOT EXISTS users (
     id varchar,
     joined_at timestamp,
-- 
cgit v1.2.3


From 04ab6e6e10eace3ee103cef61f2ee94f3d2129fb Mon Sep 17 00:00:00 2001
From: Chris Lovering <chris.lovering.95@gmail.com>
Date: Tue, 7 Sep 2021 18:19:11 +0100
Subject: Correctly attribute SO author for SQL function

---
 postgres/init.sql | 4 ++++
 1 file changed, 4 insertions(+)

(limited to 'postgres/init.sql')

diff --git a/postgres/init.sql b/postgres/init.sql
index ee1acd8d..55bb468f 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -1,3 +1,7 @@
+-- The following function is from Stack Overflow
+-- https://stackoverflow.com/questions/18389124/simulate-create-database-if-not-exists-for-postgresql/36218838#36218838
+-- User frankhommers (https://stackoverflow.com/users/971229/frankhommers)
+
 DO
 $do$
 DECLARE
-- 
cgit v1.2.3

