diff options
| -rw-r--r-- | docker-compose.yml | 2 | ||||
| -rwxr-xr-x | manage.py | 74 | ||||
| -rw-r--r-- | postgres/init.sql | 73 |
3 files changed, 109 insertions, 40 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: @@ -1,10 +1,9 @@ #!/usr/bin/env python import os -import re import socket import sys import time -from typing import List +from urllib.parse import SplitResult, urlsplit import django from django.contrib.auth import get_user_model @@ -42,7 +41,7 @@ class SiteManager: --verbose Sets verbose console output. """ - def __init__(self, args: List[str]): + def __init__(self, args: list[str]): self.debug = "--debug" in args self.silent = "--silent" in args @@ -56,6 +55,22 @@ class SiteManager: print("Starting in debug mode.") @staticmethod + def parse_db_url(db_url: str) -> SplitResult: + """Validate and split the given databse url.""" + db_url_parts = urlsplit(db_url) + if not all(( + db_url_parts.hostname, + db_url_parts.port, + db_url_parts.username, + db_url_parts.password, + db_url_parts.path + )): + raise ValueError( + "The DATABASE_URL environment variable is not a valid PostgreSQL database URL." + ) + return db_url_parts + + @staticmethod def create_superuser() -> None: """Create a default django admin super user in development environments.""" print("Creating a superuser.") @@ -90,12 +105,9 @@ class SiteManager: print("Waiting for PostgreSQL database.") # Get database URL based on environmental variable passed in compose - database_url = os.environ["DATABASE_URL"] - match = re.search(r"@([\w.]+):(\d+)/", database_url) - if not match: - raise OSError("Valid DATABASE_URL environmental variable not found.") - domain = match.group(1) - port = int(match.group(2)) + database_url_parts = SiteManager.parse_db_url(os.environ["DATABASE_URL"]) + domain = database_url_parts.hostname + port = database_url_parts.port # Attempt to connect to the database socket s = socket.socket(socket.AF_INET, socket.SOCK_STREAM) @@ -129,12 +141,45 @@ 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.""" - 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 +233,11 @@ class SiteManager: def main() -> None: """Entry point for Django management script.""" + # 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": SiteManager(sys.argv).run_server() diff --git a/postgres/init.sql b/postgres/init.sql index 190a705c..55bb468f 100644 --- a/postgres/init.sql +++ b/postgres/init.sql @@ -1,8 +1,29 @@ -CREATE DATABASE metricity; - -\c metricity; - -CREATE TABLE users ( +-- 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 ( id varchar, joined_at timestamp, primary key(id) @@ -11,14 +32,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 +48,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 +100,7 @@ INSERT INTO messages VALUES( false, now(), '267659945086812160' -); +) ON CONFLICT (id) DO NOTHING; INSERT INTO messages VALUES( 1, @@ -87,7 +108,7 @@ INSERT INTO messages VALUES( false, now() + INTERVAL '10 minutes,', '1234' -); +) ON CONFLICT (id) DO NOTHING; INSERT INTO messages VALUES( 2, @@ -95,7 +116,7 @@ INSERT INTO messages VALUES( false, now(), '11' -); +) ON CONFLICT (id) DO NOTHING; INSERT INTO messages VALUES( 3, @@ -103,7 +124,7 @@ INSERT INTO messages VALUES( false, now(), '12' -); +) ON CONFLICT (id) DO NOTHING; INSERT INTO messages VALUES( 4, @@ -111,7 +132,7 @@ INSERT INTO messages VALUES( false, now(), '21' -); +) ON CONFLICT (id) DO NOTHING; INSERT INTO messages VALUES( 5, @@ -119,7 +140,7 @@ INSERT INTO messages VALUES( false, now(), '22' -); +) ON CONFLICT (id) DO NOTHING; INSERT INTO messages VALUES( 6, @@ -127,7 +148,7 @@ INSERT INTO messages VALUES( false, now(), '31' -); +) ON CONFLICT (id) DO NOTHING; INSERT INTO messages VALUES( 7, @@ -135,7 +156,7 @@ INSERT INTO messages VALUES( false, now(), '32' -); +) ON CONFLICT (id) DO NOTHING; INSERT INTO messages VALUES( 8, @@ -143,4 +164,4 @@ INSERT INTO messages VALUES( true, now(), '32' -); +) ON CONFLICT (id) DO NOTHING; |