From eb0ef4252028fd1c3d2626105ee25e22519c9a09 Mon Sep 17 00:00:00 2001
From: Jan Vales <jan@jvales.net>
Date: Mon, 7 Nov 2016 17:25:45 +0100
Subject: [PATCH] sql setup script - should be standalone.

---
 .setup.sql | 50 ++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 50 insertions(+)
 create mode 100644 .setup.sql

diff --git a/.setup.sql b/.setup.sql
new file mode 100644
index 0000000..b049b73
--- /dev/null
+++ b/.setup.sql
@@ -0,0 +1,50 @@
+-- We may need to limit the bots permissions :)
+
+-- pw: flagbotpw
+CREATE ROLE flagbot LOGIN
+  ENCRYPTED PASSWORD 'md58096a1ddf68ea631cefdf8d8f2455952'
+  SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
+
+
+CREATE DATABASE flagbot
+  WITH OWNER = flagbot
+       ENCODING = 'UTF8'
+       TABLESPACE = pg_default
+       LC_COLLATE = 'en_US.UTF-8'
+       LC_CTYPE = 'en_US.UTF-8'
+       CONNECTION LIMIT = -1;
+
+
+\connect flagbot
+
+
+CREATE TABLE IF NOT EXISTS flag_ids (
+service character varying(64) NOT NULL,
+team character varying(64) NOT NULL,
+host character varying(10) NOT NULL,
+port character varying(10) NOT NULL,
+flag_id character varying(128) NOT NULL,
+received timestamp without time zone NOT NULL DEFAULT date_trunc('second'::text, now()),
+handed_out timestamp without time zone,
+status integer NOT NULL DEFAULT 0,
+CONSTRAINT flag_ids_pkey PRIMARY KEY (service, flag_id)
+);
+
+ALTER TABLE flag_ids OWNER TO flagbot;
+
+
+CREATE TABLE IF NOT EXISTS flags (
+fid serial NOT NULL,
+flag character varying(32) NOT NULL,
+service character varying(32),
+flag_id character varying(128),
+submitter character varying(32),
+received timestamp without time zone NOT NULL DEFAULT date_trunc('second'::text, now()),
+submitted timestamp without time zone,
+status integer NOT NULL DEFAULT 0, 
+srvresponse character varying(128),
+CONSTRAINT flags_pkey PRIMARY KEY (fid),
+CONSTRAINT flags_flag_key UNIQUE (flag)
+);
+
+ALTER TABLE flags OWNER TO flagbot;
-- 
2.43.0