From 78d82791afb8fd3da4f767393b918ee401b41aec Mon Sep 17 00:00:00 2001 From: Chris Lovering 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. --- manage.py | 58 +++++++++++++++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 55 insertions(+), 3 deletions(-) (limited to 'manage.py') 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() -- cgit v1.2.3 From a211f99319df66ff11a75746c5f7ab40ac19122a Mon Sep 17 00:00:00 2001 From: Chris Lovering Date: Mon, 6 Sep 2021 21:03:14 +0100 Subject: Use context manager for file open Also remove a useless print statement to reduce noise --- manage.py | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'manage.py') diff --git a/manage.py b/manage.py index ad9bae5f..63602d63 100755 --- a/manage.py +++ b/manage.py @@ -161,7 +161,6 @@ class SiteManager: 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") @@ -171,8 +170,8 @@ class SiteManager: database="metricity", **db_connection_kwargs ) - with conn.cursor() as cursor: - cursor.execute(open("postgres/init.sql").read()) + with conn.cursor() as cursor, open("postgres/init.sql", encoding="utf-8") as f: + cursor.execute(f.read()) def prepare_server(self) -> None: """Perform preparation tasks before running the server.""" -- cgit v1.2.3 From 661454f9d53508f99a244b4a0ebf34be9b6d3008 Mon Sep 17 00:00:00 2001 From: Chris Lovering Date: Tue, 7 Sep 2021 09:37:47 +0100 Subject: Move db url validation to cls method This standardises the way we validate db urls, and ensures each place that uses a db url manually properly validates it first. --- manage.py | 33 +++++++++++++++++++++------------ 1 file changed, 21 insertions(+), 12 deletions(-) (limited to 'manage.py') diff --git a/manage.py b/manage.py index 63602d63..70855ba8 100755 --- a/manage.py +++ b/manage.py @@ -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 @@ -55,6 +54,20 @@ class SiteManager: os.environ.setdefault("DEBUG", "true") 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 OSError("Valid DATABASE_URL environment variable not found.") + return db_url_parts + @staticmethod def create_superuser() -> None: """Create a default django admin super user in development environments.""" @@ -90,12 +103,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) @@ -140,9 +150,8 @@ class SiteManager: """ 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_url_parts = SiteManager.parse_db_url(os.environ["DATABASE_URL"]) db_connection_kwargs = { "host": db_url_parts.hostname, "port": db_url_parts.port, -- cgit v1.2.3 From 6de87e47e313b4f533e8d3adfeb0b8202e938ca0 Mon Sep 17 00:00:00 2001 From: Chris Lovering Date: Tue, 7 Sep 2021 09:38:16 +0100 Subject: Close db conns when finished psycopg2's context managers don't do this for us, so we need to close conns manually. --- manage.py | 2 ++ 1 file changed, 2 insertions(+) (limited to 'manage.py') diff --git a/manage.py b/manage.py index 70855ba8..cb4eb567 100755 --- a/manage.py +++ b/manage.py @@ -173,6 +173,7 @@ class SiteManager: 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( @@ -181,6 +182,7 @@ class SiteManager: ) with conn.cursor() as cursor, open("postgres/init.sql", encoding="utf-8") as f: cursor.execute(f.read()) + conn.close() def prepare_server(self) -> None: """Perform preparation tasks before running the server.""" -- cgit v1.2.3 From d245bbd600811762a74544aeff6086a6c94dd7f3 Mon Sep 17 00:00:00 2001 From: Chris Lovering 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 'manage.py') 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 a6206855c77b34f4e0705fea7fd7ac358dc88d89 Mon Sep 17 00:00:00 2001 From: ChrisJL Date: Tue, 7 Sep 2021 11:17:42 +0100 Subject: Fix spelling of `initialising` in manage.py Co-authored-by: Vivaan Verma <54081925+doublevcodes@users.noreply.github.com> --- manage.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'manage.py') diff --git a/manage.py b/manage.py index 308f917d..22ae45d7 100755 --- a/manage.py +++ b/manage.py @@ -142,10 +142,10 @@ class SiteManager: @staticmethod def run_metricity_init() -> None: """ - Initilise metricity relations and populate with some testing data. + 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 initilising it's own db, since they do not have + 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 -- cgit v1.2.3 From d612110b67269e7132bd0bc891f2c90a9b806c89 Mon Sep 17 00:00:00 2001 From: Chris Lovering Date: Tue, 7 Sep 2021 11:39:09 +0100 Subject: Remove unnecessary variable Co-authored-by: Vivaan Verma <54081925+doublevcodes@users.noreply.github.com> --- manage.py | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) (limited to 'manage.py') diff --git a/manage.py b/manage.py index 22ae45d7..0fa8ebe3 100755 --- a/manage.py +++ b/manage.py @@ -229,9 +229,8 @@ 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: + # 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() -- cgit v1.2.3 From 4411ae4bbffabbf962b5270cd7aaee5b6495ab6e Mon Sep 17 00:00:00 2001 From: Chris Lovering Date: Tue, 7 Sep 2021 18:16:54 +0100 Subject: Raise a ValueError with a useful message for invalid db urls --- manage.py | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'manage.py') diff --git a/manage.py b/manage.py index 0fa8ebe3..a8895126 100755 --- a/manage.py +++ b/manage.py @@ -65,7 +65,9 @@ class SiteManager: db_url_parts.password, db_url_parts.path )): - raise OSError("Valid DATABASE_URL environment variable not found.") + raise ValueError( + "The DATABASE_URL environment variable is not a valid PostgreSQL database URL." + ) return db_url_parts @staticmethod -- cgit v1.2.3 From 71252e7d40fed7dcee26ef45a2eb1f8f242b83e9 Mon Sep 17 00:00:00 2001 From: Chris Lovering Date: Tue, 7 Sep 2021 18:30:33 +0100 Subject: Add print statement to log when metricity is being initialised --- manage.py | 2 ++ 1 file changed, 2 insertions(+) (limited to 'manage.py') diff --git a/manage.py b/manage.py index a8895126..1e613e93 100755 --- a/manage.py +++ b/manage.py @@ -153,6 +153,8 @@ class SiteManager: 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, -- cgit v1.2.3