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. --- docker-compose.yml | 2 -- manage.py | 58 +++++++++++++++++++++++++++++++++++++++++++++++++++--- postgres/init.sql | 48 +++++++++++++++++++++----------------------- 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; -- 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(-) 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(-) 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(+) 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(-) 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(-) 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(-) 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(-) 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 04ab6e6e10eace3ee103cef61f2ee94f3d2129fb Mon Sep 17 00:00:00 2001 From: Chris Lovering 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(+) 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 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(+) 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