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. --- postgres/init.sql | 48 ++++++++++++++++++++++-------------------------- 1 file changed, 22 insertions(+), 26 deletions(-) (limited to 'postgres/init.sql') 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 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 'postgres/init.sql') 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 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(+) (limited to 'postgres/init.sql') 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