start custom banner and avatar loading

5 files changed, 57 insertions, 27 deletions

diff --git a/src/db/rest/media/api_profile_avatar.sql b/src/db/rest/media/api_profile_avatar.sql
new file mode 100644
index 0000000..8607999
--- /dev/null
+++ b/src/db/rest/media/api_profile_avatar.sql
@@ -0,0 +1,36 @@
+CREATE FUNCTION api.profile_avatar(
+	user_id INTEGER DEFAULT 0
+)
+RETURNS sys."*/*"
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_id INTEGER;
+	_mod INTEGER;
+	_name TEXT;
+BEGIN
+	SELECT media_id INTO _id
+	FROM admin.user_media m
+	WHERE m.user_id = profile_avatar.user_id
+	AND type = 'avatar'::admin.user_media_type;
+
+	-- get default if not exists
+	IF NOT FOUND THEN
+		_mod = MOD(user_id, 24);
+		_name = 'default_avatar_' || _mod || '.png';
+
+		SELECT id INTO _id
+		FROM admin.media
+		WHERE name = _name;
+	END IF;
+
+	RETURN _api.serve_media(_id);
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION api.profile_avatar(INTEGER)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.user_media
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.media
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/media/api_profile_banner.sql b/src/db/rest/media/api_profile_banner.sql
new file mode 100644
index 0000000..272d021
--- /dev/null
+++ b/src/db/rest/media/api_profile_banner.sql
@@ -0,0 +1,13 @@
+CREATE FUNCTION api.profile_banner(
+	user_id INTEGER DEFAULT 0
+)
+RETURNS sys."*/*"
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	PERFORM _api.raise_deny();
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION api.profile_banner(INTEGER)
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/rest.sql b/src/db/rest/rest.sql
index 3e6737c..e203f27 100644
--- a/src/db/rest/rest.sql
+++ b/src/db/rest/rest.sql
@@ -27,7 +27,6 @@ GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user;
 \i /db/rest/user/api_user_insert.sql;
 \i /db/rest/user/api_user_update.sql;
 \i /db/rest/user/api_user_delete.sql;
-\i /db/rest/user/api_avatar.sql;
 
 -- post
 \i /db/rest/post/api_post.sql;
@@ -47,6 +46,10 @@ GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user;
 \i /db/rest/like/api_like_update.sql;
 \i /db/rest/like/api_like_delete.sql;
 
+-- media
+\i /db/rest/media/api_profile_avatar.sql;
+\i /db/rest/media/api_profile_banner.sql;
+
 -- login
 \i /db/rest/login/_api_sign_jwt.sql;
 \i /db/rest/login/_api_verify_jwt.sql;
diff --git a/src/db/rest/user/api_avatar.sql b/src/db/rest/user/api_avatar.sql
deleted file mode 100644
index 981409f..0000000
--- a/src/db/rest/user/api_avatar.sql
+++ /dev/null
@@ -1,22 +0,0 @@
-CREATE FUNCTION api.avatar(
-	user_id INTEGER DEFAULT 0
-)
-RETURNS sys."*/*"
-LANGUAGE plpgsql VOLATILE
-AS $BODY$
-DECLARE
-	_mod INTEGER;
-	_name TEXT;
-BEGIN
-	_mod = MOD(user_id, 24);
-	_name = 'default_avatar_' || _mod || '.png';
-	RETURN _api.serve_media(_name);
-END
-$BODY$;
-
-GRANT EXECUTE ON FUNCTION api.avatar(INTEGER)
-	TO rest_anon, rest_user;
-GRANT SELECT ON TABLE admin.user
-	TO rest_anon, rest_user;
-GRANT SELECT ON TABLE admin.media
-	TO rest_anon, rest_user;
diff --git a/src/db/rest/util/_api_serve_media.sql b/src/db/rest/util/_api_serve_media.sql
index 8b0f0b8..c2e213a 100644
--- a/src/db/rest/util/_api_serve_media.sql
+++ b/src/db/rest/util/_api_serve_media.sql
@@ -1,5 +1,5 @@
 CREATE FUNCTION _api.serve_media(
-	_name TEXT
+	_media_id INTEGER
 )
 RETURNS sys."*/*"
 LANGUAGE plpgsql VOLATILE
@@ -15,13 +15,13 @@ BEGIN
        '{"Cache-Control": "max-age=259200"}]'
       ,	m.type, m.name)
 	FROM admin.media m
-	WHERE m.name = _name INTO _headers;
+	WHERE m.id = _media_id INTO _headers;
 
 	PERFORM SET_CONFIG('response.headers', _headers, true);
 
 	SELECT m.content
 	FROM admin.media m
-	WHERE m.name = _name
+	WHERE m.id = _media_id
 	INTO _data;
 
 	IF FOUND THEN
@@ -35,7 +35,7 @@ BEGIN
 END
 $BODY$;
 
-GRANT EXECUTE ON FUNCTION _api.serve_media(TEXT)
+GRANT EXECUTE ON FUNCTION _api.serve_media(INTEGER)
 	TO rest_anon, rest_user;
 GRANT SELECT ON TABLE admin.media
 	TO rest_anon, rest_user;