Init metricity using docker-compose init volume

3 files changed, 28 insertions, 84 deletions

diff --git a/docker-compose.yml b/docker-compose.yml
index 37678949..05867a46 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -18,6 +18,8 @@ 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 648d6635..d8258281 100755
--- a/manage.py
+++ b/manage.py
@@ -141,44 +141,8 @@ class SiteManager:
                 name="pythondiscord.local:8000"
             )
 
-    @staticmethod
-    def run_metricity_init() -> None:
-        """
-        Initialise metricity relations and populate with some testing data.
-
-        This is done at run time since other projects, like Python bot,
-        rely on the site initialising 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
-
-        print("Initialising metricity.")
-
-        db_url_parts = SiteManager.parse_db_url(os.environ["DATABASE_URL"])
-        conn = psycopg2.connect(
-            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, open("postgres/init.sql", encoding="utf-8") as f:
-            cursor.execute(
-                f.read(),
-                ("metricity", db_url_parts.username, db_url_parts.password)
-            )
-        conn.close()
-
     def prepare_server(self) -> None:
         """Perform preparation tasks before running the server."""
-        self.wait_for_postgres()
-        if self.debug:
-            self.run_metricity_init()
-
         django.setup()
 
         print("Applying migrations.")
@@ -236,7 +200,6 @@ def main() -> None:
     # Always run metricity init when in CI, indicated by the CI env var
     if os.environ.get("CI", "false").lower() == "true":
         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":
diff --git a/postgres/init.sql b/postgres/init.sql
index 55bb468f..190a705c 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -1,29 +1,8 @@
--- 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
-  _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 (
+CREATE DATABASE metricity;
+
+\c metricity;
+
+CREATE TABLE users (
     id varchar,
     joined_at timestamp,
     primary key(id)
@@ -32,14 +11,14 @@ CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS channels (
+CREATE TABLE channels (
     id varchar,
     name varchar,
     primary key(id)
@@ -48,44 +27,44 @@ CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS messages (
+CREATE TABLE messages (
     id varchar,
     author_id varchar references users(id),
     is_deleted boolean,
@@ -100,7 +79,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '267659945086812160'
-) ON CONFLICT (id) DO NOTHING;
+);
 
 INSERT INTO messages VALUES(
     1,
@@ -108,7 +87,7 @@ INSERT INTO messages VALUES(
     false,
     now() + INTERVAL '10 minutes,',
     '1234'
-) ON CONFLICT (id) DO NOTHING;
+);
 
 INSERT INTO messages VALUES(
     2,
@@ -116,7 +95,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '11'
-) ON CONFLICT (id) DO NOTHING;
+);
 
 INSERT INTO messages VALUES(
     3,
@@ -124,7 +103,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '12'
-) ON CONFLICT (id) DO NOTHING;
+);
 
 INSERT INTO messages VALUES(
     4,
@@ -132,7 +111,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '21'
-) ON CONFLICT (id) DO NOTHING;
+);
 
 INSERT INTO messages VALUES(
     5,
@@ -140,7 +119,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '22'
-) ON CONFLICT (id) DO NOTHING;
+);
 
 INSERT INTO messages VALUES(
     6,
@@ -148,7 +127,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '31'
-) ON CONFLICT (id) DO NOTHING;
+);
 
 INSERT INTO messages VALUES(
     7,
@@ -156,7 +135,7 @@ INSERT INTO messages VALUES(
     false,
     now(),
     '32'
-) ON CONFLICT (id) DO NOTHING;
+);
 
 INSERT INTO messages VALUES(
     8,
@@ -164,4 +143,4 @@ INSERT INTO messages VALUES(
     true,
     now(),
     '32'
-) ON CONFLICT (id) DO NOTHING;
+);