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.

3 files changed, 77 insertions, 31 deletions

diff --git a/docker-compose.yml b/docker-compose.yml
index 05867a46..37678949 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -18,8 +18,6 @@ services:
       POSTGRES_DB: pysite
       POSTGRES_PASSWORD: pysite
       POSTGRES_USER: pysite
-    volumes:
-      - ./postgres/init.sql:/docker-entrypoint-initdb.d/init.sql
 
   web:
     build:
diff --git a/manage.py b/manage.py
index 66ad26f4..ad9bae5f 100755
--- a/manage.py
+++ b/manage.py
@@ -129,12 +129,58 @@ class SiteManager:
                 name="pythondiscord.local:8000"
             )
 
+    @staticmethod
+    def run_metricity_init() -> None:
+        """
+        Initilise metricity relations and populate with some testing data.
+
+        This is done at run time since other projects, like Python bot,
+        rely on the site initilising it's own db, since they do not have
+        access to the init.sql file to mount a docker-compose volume.
+        """
+        import psycopg2
+        from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
+        from urllib.parse import urlsplit
+        # The database URL has already been validated in `wait_for_postgres()`
+        db_url_parts = urlsplit(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
+        )
+        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
+                print("Metricity already exists, not creating.")
+                return
+            print("Creating metricity relations and populating with some data.")
+            cursor.execute("CREATE DATABASE metricity")
+
+        # Switch connection to metricity and initialise some data
+        conn = psycopg2.connect(
+            database="metricity",
+            **db_connection_kwargs
+        )
+        with conn.cursor() as cursor:
+            cursor.execute(open("postgres/init.sql").read())
+
     def prepare_server(self) -> None:
         """Perform preparation tasks before running the server."""
-        django.setup()
-
+        self.wait_for_postgres()
         if self.debug:
-            self.wait_for_postgres()
+            self.run_metricity_init()
+
+        django.setup()
 
         print("Applying migrations.")
         call_command("migrate", verbosity=self.verbosity)
@@ -188,6 +234,12 @@ class SiteManager:
 
 def main() -> None:
     """Entry point for Django management script."""
+    # Always run metricity init in CI
+    in_ci = os.environ.get("CI", "false").lower() == "true"
+    if in_ci:
+        SiteManager.wait_for_postgres()
+        SiteManager.run_metricity_init()
+
     # Use the custom site manager for launching the server
     if len(sys.argv) > 1 and sys.argv[1] == "run":
         SiteManager(sys.argv).run_server()
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;