2 files changed, 67 insertions, 0 deletions
diff --git a/src/db/rest/media/api_delete_user_media.sql b/src/db/rest/media/api_delete_user_media.sql
new file mode 100644
index 0000000..d9a4529
--- /dev/null
+++ b/src/db/rest/media/api_delete_user_media.sql
@@ -0,0 +1,26 @@
+CREATE FUNCTION api.delete_user_media(
+	media_type admin.user_media_type
+)
+RETURNS void
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_data BYTEA;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	DELETE FROM
+		admin.user_media
+	WHERE
+		"type" = media_type AND
+		"user_id" = _user_id;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION api.delete_user_media(admin.user_media_type)
+	TO rest_user;
+GRANT DELETE ON TABLE admin.user_media
+	TO rest_user;
+GRANT UPDATE ON TABLE sys.user_media_id_seq
+	TO rest_user;
diff --git a/src/db/rest/media/api_update_user_media.sql b/src/db/rest/media/api_update_user_media.sql
new file mode 100644
index 0000000..be8835a
--- /dev/null
+++ b/src/db/rest/media/api_update_user_media.sql
@@ -0,0 +1,41 @@
+CREATE FUNCTION api.update_user_media(
+	media_type admin.user_media_type,
+	mime TEXT,
+	content TEXT
+)
+RETURNS void
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_data BYTEA;
+BEGIN
+	_user_id = _api.get_user_id();
+	_data = decode(content, 'base64');
+
+	INSERT INTO admin.user_media (
+		user_id,
+		content,
+		mime,
+		type
+	) VALUES (
+		_user_id,
+		_data,
+		mime,
+		media_type
+	) ON CONFLICT (
+		"user_id", "type"
+	) DO UPDATE SET
+		"content" = excluded.content,
+		"mime" = excluded.mime,
+		"type" = excluded.type,
+		"modified" = clock_timestamp();
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION api.update_user_media(admin.user_media_type, TEXT, TEXT)
+	TO rest_user;
+GRANT INSERT, UPDATE ON TABLE admin.user_media
+	TO rest_user;
+GRANT UPDATE ON TABLE sys.user_media_id_seq
+	TO rest_user;