start database (user and post), and initial barebones home page

1 files changed, 171 insertions, 0 deletions

diff --git a/db/migrations/0000.sql b/db/migrations/0000.sql
new file mode 100644
index 0000000..f3577d4
--- /dev/null
+++ b/db/migrations/0000.sql
@@ -0,0 +1,171 @@
+BEGIN TRANSACTION;
+SET search_path = public;
+
+-- Migration Start
+
+CREATE SCHEMA sys;
+
+ALTER SCHEMA sys OWNER TO xssbook;
+
+CREATE DOMAIN sys."*/*" AS BYTEA;
+
+CREATE TABLE sys.database_info (
+	name TEXT DEFAULT ''::text NOT NULL,
+	jwt_secret TEXT DEFAULT ''::text NOT NULL,
+	curr_revision INTEGER DEFAULT 0 NOT NULL
+);
+
+ALTER TABLE sys.database_info
+	ADD CONSTRAINT database_info_pkey PRIMARY KEY (name);
+
+ALTER TABLE sys.database_info OWNER TO xssbook;
+
+INSERT INTO sys.database_info
+	(name, curr_revision) VALUES (current_database(), 0);
+
+CREATE TYPE sys.JWT AS (
+	token TEXT
+);
+
+CREATE SCHEMA admin;
+
+ALTER SCHEMA admin OWNER TO xssbook;
+
+CREATE SEQUENCE IF NOT EXISTS sys.user_id_seq
+	START WITH 1
+	INCREMENT BY 1
+	NO MINVALUE
+	NO MAXVALUE
+	CACHE 1;
+
+ALTER TABLE sys.user_id_seq OWNER TO xssbook;
+
+CREATE TABLE admin.user (
+	id				INTEGER DEFAULT nextval('sys.user_id_seq'::regclass) NOT NULL,
+	username		TEXT NOT NULL,
+	password		TEXT NOT NULL,
+	role			NAME DEFAULT 'rest_user'::text NOT NULL,
+	first_name		TEXT DEFAULT ''::text NOT NULL,
+	last_name		TEXT DEFAULT ''::text NOT NULL,
+	middle_name		TEXT DEFAULT ''::text NOT NULL,
+	email			TEXT DEFAULT ''::text NOT NULL,
+	gender			TEXT DEFAULT ''::text NOT NULL,
+	join_date		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	birth_date		TIMESTAMP WITH TIME ZONE NOT NULL,
+	profile_avatar	BYTEA,
+	profile_banner	BYTEA,
+	profile_bio		TEXT DEFAULT ''::text NOT NULL
+);
+
+ALTER TABLE admin.user OWNER TO xssbook;
+
+ALTER TABLE ONLY admin.user
+	ADD CONSTRAINT user_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY admin.user
+	ADD CONSTRAINT user_username_unique UNIQUE (username);
+
+CREATE SEQUENCE IF NOT EXISTS sys.post_id_seq
+	START WITH 1
+	INCREMENT BY 1
+	NO MINVALUE
+	NO MAXVALUE
+	CACHE 1;
+
+ALTER TABLE sys.post_id_seq OWNER TO xssbook;
+
+CREATE TABLE admin.post (
+	id		INTEGER DEFAULT nextval('sys.post_id_seq'::regclass) NOT NULL,
+	user_id	INTEGER NOT NULL,
+	content	TEXT DEFAULT ''::text NOT NULL,
+	date	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
+);
+
+ALTER TABLE admin.post OWNER TO xssbook;
+
+ALTER TABLE ONLY admin.post
+	ADD CONSTRAINT post_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY admin.post
+	ADD CONSTRAINT post_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;
+
+CREATE SEQUENCE IF NOT EXISTS 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,
+	user_id	INTEGER NOT NULL,
+	post_id INTEGER NOT NULL,
+	content TEXT DEFAULT ''::text NOT NULL,
+	date	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
+);
+
+ALTER TABLE admin.comment OWNER TO xssbook;
+
+ALTER TABLE ONLY admin.comment
+	ADD CONSTRAINT comment_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY admin.comment
+	ADD CONSTRAINT comment_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;
+
+ALTER TABLE ONLY admin.comment
+	ADD CONSTRAINT comment_post_id_fkey FOREIGN KEY (post_id) REFERENCES admin.post (id) ON DELETE CASCADE;
+
+CREATE TABLE admin.like (
+	user_id		INTEGER NOT NULL,
+	post_id		INTEGER,
+	comment_id  INTEGER,
+	date		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
+);
+
+ALTER TABLE admin.like OWNER TO xssbook;
+
+ALTER TABLE ONLY admin.like
+	ADD CONSTRAINT like_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;
+
+ALTER TABLE ONLY admin.like
+	ADD CONSTRAINT like_post_id_fkey FOREIGN KEY (post_id) REFERENCES admin.post (id) ON DELETE CASCADE;
+
+ALTER TABLE ONLY admin.like
+	ADD CONSTRAINT like_comment_id_fkey FOREIGN KEY (comment_id) REFERENCES admin.comment (id) ON DELETE CASCADE;
+
+CREATE TABLE admin.follow (
+	follower_id	INTEGER NOT NULL,
+	followee_id	INTEGER NOT NULL,
+	date		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
+);
+
+ALTER TABLE admin.follow OWNER TO xssbook;
+
+ALTER TABLE ONLY admin.follow
+	ADD CONSTRAINT follow_pkey PRIMARY KEY (follower_id, followee_id);
+
+ALTER TABLE ONLY admin.follow
+	ADD CONSTRAINT follow_follower_id FOREIGN KEY (follower_id) REFERENCES admin.user (id) ON DELETE CASCADE;
+
+ALTER TABLE ONLY admin.follow
+	ADD CONSTRAINT follow_followee_id FOREIGN KEY (followee_id) REFERENCES admin.user (id) ON DELETE CASCADE;
+
+CREATE TABLE admin.media (
+	name			TEXT NOT NULL,
+	content			BYTEA NOT NULL,
+	type			TEXT NOT NULL
+);
+
+ALTER TABLE admin.media OWNER TO xssbook;
+
+ALTER TABLE ONLY admin.media
+	ADD CONSTRAINT media_pkey PRIMARY KEY (name);
+
+ALTER DATABASE xssbook SET search_path = admin,public;
+ALTER DATABASE xssbook SET bytea_output = 'hex';
+-- Migration End;
+
+-- Set Current Revision
+UPDATE sys.database_info SET curr_revision = 1 WHERE name = current_database();
+
+COMMIT TRANSACTION;