From c5f39ea2cd7cf02246705ea8872d3b350526165c Mon Sep 17 00:00:00 2001
From: Freya Murphy <freya@freyacat.org>
Date: Fri, 24 May 2024 09:05:42 -0400
Subject: initial

---
 src/db/migrations/0000.sql | 89 ++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 89 insertions(+)
 create mode 100644 src/db/migrations/0000.sql

(limited to 'src/db/migrations/0000.sql')

diff --git a/src/db/migrations/0000.sql b/src/db/migrations/0000.sql
new file mode 100644
index 0000000..bc4b3b9
--- /dev/null
+++ b/src/db/migrations/0000.sql
@@ -0,0 +1,89 @@
+BEGIN TRANSACTION;
+SET search_path = public;
+
+-- Migration Start
+
+CREATE SCHEMA sys;
+
+ALTER SCHEMA sys OWNER TO website;
+
+CREATE TABLE sys.database_info (
+	name TEXT DEFAULT ''::text NOT NULL,
+	curr_revision INTEGER DEFAULT 0 NOT NULL
+);
+
+ALTER TABLE ONLY sys.database_info
+	ADD CONSTRAINT database_info_pkey PRIMARY KEY (name);
+
+ALTER TABLE sys.database_info OWNER TO website;
+
+INSERT INTO sys.database_info
+	(name, curr_revision) VALUES (current_database(), 0);
+
+CREATE SCHEMA admin;
+
+ALTER SCHEMA admin OWNER TO website;
+
+CREATE SEQUENCE sys.request_log_id_seq
+	START WITH 1
+	INCREMENT BY 1
+	NO MINVALUE
+	NO MAXVALUE
+	CACHE 1;
+
+CREATE TYPE admin.request_log_method_type AS ENUM (
+	'GET', 'HEAD', 'POST', 'PUT', 'DELETE',
+	'CONNECT', 'OPTIONS', 'TRACE', 'PATCH'
+);
+
+CREATE TABLE admin.request_log (
+	id		INTEGER DEFAULT nextval('sys.request_log_id_seq'::regclass) NOT NULL,
+	date	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	ip		INET NOT NULL,
+	method	admin.request_log_method_type NOT NULL,
+	uri		TEXT NOT NULL
+);
+
+ALTER TABLE admin.request_log OWNER TO website;
+
+ALTER TABLE ONLY admin.request_log
+	ADD CONSTRAINT request_log_pkey PRIMARY KEY (id);
+
+CREATE TABLE admin.banned (
+	ip      INET NOT NULL,
+	date    TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	reason  TEXT DEFAULT 'unspecified'::text NOT NULL
+);
+
+ALTER TABLE admin.banned OWNER TO website;
+
+ALTER TABLE ONLY admin.banned
+	ADD CONSTRAINT banned_pkey PRIMARY KEY (ip);
+
+CREATE SEQUENCE sys.comment_id_seq
+	START WITH 1
+	INCREMENT BY 1
+	NO MINVALUE
+	NO MAXVALUE
+	CACHE 1;
+
+CREATE TABLE admin.comment (
+	id      INTEGER DEFAULT nextval('sys.comment_id_seq'::regclass) NOT NULL,
+	author  TEXT NOT NULL,
+	content TEXT NOT NULL,
+	page    TEXT NOT NULL,
+	created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	ip      INET NOT NULL,
+	vulgar  BOOLEAN DEFAULT FALSE
+);
+
+ALTER TABLE admin.comment OWNER TO website;
+
+ALTER TABLE ONLY admin.comment
+	ADD CONSTRAINT comment_pkey PRIMARY KEY (id);
+-- Migration End;
+
+-- Set Current Revision
+UPDATE sys.database_info SET curr_revision = 1 WHERE name = current_database();
+
+COMMIT TRANSACTION;
-- 
cgit v1.2.3-freya

