From a2494d4275c6bbe578156601b0fda0cf203e7af7 Mon Sep 17 00:00:00 2001
From: Chris Lovering <chris.lovering.95@gmail.com>
Date: Thu, 9 Sep 2021 15:53:32 +0100
Subject: Init metricity using docker-compose init volume

---
 postgres/init.sql | 73 ++++++++++++++++++++-----------------------------------
 1 file changed, 26 insertions(+), 47 deletions(-)

(limited to 'postgres/init.sql')

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;
+);
-- 
cgit v1.2.3

