start custom banner and avatar loading

1 files changed, 49 insertions, 4 deletions

diff --git a/src/db/migrations/0000.sql b/src/db/migrations/0000.sql
index b60c55b..7e14ac4 100644
--- a/src/db/migrations/0000.sql
+++ b/src/db/migrations/0000.sql
@@ -187,16 +187,61 @@ ALTER TABLE ONLY admin.follow
 ALTER TABLE ONLY admin.follow
 	ADD CONSTRAINT follow_follower_unique UNIQUE (follower_id, followee_id);
 
+CREATE SEQUENCE IF NOT EXISTS sys.media_id_seq
+	START WITH 1
+	INCREMENT BY 1
+	NO MINVALUE
+	NO MAXVALUE
+	CACHE 1;
+
 CREATE TABLE admin.media (
-	name			TEXT NOT NULL,
-	content			BYTEA NOT NULL,
-	type			TEXT NOT NULL
+	id			INTEGER DEFAULT nextval('sys.media_id_seq'::regclass) NOT NULL,
+	name		TEXT NOT NULL,
+	content		BYTEA NOT NULL,
+	type		TEXT NOT NULL,
+	created		TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
+	modified	TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
 );
 
 ALTER TABLE admin.media OWNER TO xssbook;
 
 ALTER TABLE ONLY admin.media
-	ADD CONSTRAINT media_pkey PRIMARY KEY (name);
+	ADD CONSTRAINT media_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY admin.media
+	ADD CONSTRAINT media_name_unique UNIQUE (name);
+
+CREATE SEQUENCE IF NOT EXISTS sys.user_media_id_seq
+	START WITH 1
+	INCREMENT BY 1
+	NO MINVALUE
+	NO MAXVALUE
+	CACHE 1;
+
+CREATE TYPE admin.user_media_type AS ENUM (
+	'avatar', 'banner'
+);
+
+CREATE TABLE admin.user_media (
+	id			INTEGER DEFAULT nextval('sys.user_media_id_seq'::regclass) NOT NULL,
+	media_id	INTEGER NOT NULL,
+	user_id		INTEGER NOT NULL,
+	type		admin.user_media_type NOT NULL
+);
+
+ALTER TABLE admin.user_media OWNER TO xssbook;
+
+ALTER TABLE ONLY admin.user_media
+	ADD CONSTRAINT user_media_pkey PRIMARY KEY (id);
+
+ALTER TABLE ONLY admin.user_media
+	ADD CONSTRAINT user_media_media_id_fkey FOREIGN KEY (media_id) REFERENCES admin.media (id) ON DELETE CASCADE;
+
+ALTER TABLE ONLY admin.user_media
+	ADD CONSTRAINT user_media_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;
+
+ALTER TABLE ONLY admin.user_media
+	ADD CONSTRAINT user_media_type_unique UNIQUE (user_id, type);
 
 ALTER DATABASE xssbook SET search_path = admin,public;
 ALTER DATABASE xssbook SET bytea_output = 'hex';