diff options
Diffstat (limited to 'src/db/rest')
| -rw-r--r-- | src/db/rest/media/_api_serve_system_media.sql (renamed from src/db/rest/util/_api_serve_media.sql) | 20 | ||||
| -rw-r--r-- | src/db/rest/media/_api_serve_user_media.sql | 37 | ||||
| -rw-r--r-- | src/db/rest/media/_api_serve_user_or_default_media.sql | 41 | ||||
| -rw-r--r-- | src/db/rest/media/api_profile_avatar.sql | 30 | ||||
| -rw-r--r-- | src/db/rest/media/api_profile_banner.sql | 13 | ||||
| -rw-r--r-- | src/db/rest/post/api_post.sql | 15 | ||||
| -rw-r--r-- | src/db/rest/rest.sql | 4 | ||||
| -rw-r--r-- | src/db/rest/user/api_user.sql | 67 | ||||
| -rw-r--r-- | src/db/rest/util/_api_raise_not_found.sql | 16 |
9 files changed, 204 insertions, 39 deletions
diff --git a/src/db/rest/util/_api_serve_media.sql b/src/db/rest/media/_api_serve_system_media.sql index c2e213a..5cd87c2 100644 --- a/src/db/rest/util/_api_serve_media.sql +++ b/src/db/rest/media/_api_serve_system_media.sql @@ -1,4 +1,4 @@ -CREATE FUNCTION _api.serve_media( +CREATE FUNCTION _api.serve_system_media( _media_id INTEGER ) RETURNS sys."*/*" @@ -8,34 +8,30 @@ DECLARE _headers TEXT; _data BYTEA; BEGIN - SELECT FORMAT( '[{"Content-Type": "%s"},' '{"Content-Disposition": "inline; filename=\"%s\""},' '{"Cache-Control": "max-age=259200"}]' - , m.type, m.name) + , m.mime, m.name) FROM admin.media m - WHERE m.id = _media_id INTO _headers; - - PERFORM SET_CONFIG('response.headers', _headers, true); + WHERE m.id = _media_id + INTO _headers; SELECT m.content FROM admin.media m WHERE m.id = _media_id INTO _data; - IF FOUND THEN + IF _data IS NOT NULL THEN + PERFORM SET_CONFIG('response.headers', _headers, true); RETURN(_data); ELSE - PERFORM _api.raise( - _msg => 'api_not_found', - _err => 404 - ); + PERFORM _api.raise_not_found(); END IF; END $BODY$; -GRANT EXECUTE ON FUNCTION _api.serve_media(INTEGER) +GRANT EXECUTE ON FUNCTION _api.serve_system_media(INTEGER) TO rest_anon, rest_user; GRANT SELECT ON TABLE admin.media TO rest_anon, rest_user; diff --git a/src/db/rest/media/_api_serve_user_media.sql b/src/db/rest/media/_api_serve_user_media.sql new file mode 100644 index 0000000..3487493 --- /dev/null +++ b/src/db/rest/media/_api_serve_user_media.sql @@ -0,0 +1,37 @@ +CREATE FUNCTION _api.serve_user_media( + _media_id INTEGER +) +RETURNS sys."*/*" +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _headers TEXT; + _data BYTEA; +BEGIN + SELECT FORMAT( + '[{"Content-Type": "%s"},' + '{"Content-Disposition": "inline"},' + '{"Cache-Control": "max-age=259200"}]' + , m.mime) + FROM admin.user_media m + WHERE m.id = _media_id + INTO _headers; + + SELECT m.content + FROM admin.user_media m + WHERE m.id = _media_id + INTO _data; + + IF _data IS NOT NULL THEN + PERFORM SET_CONFIG('response.headers', _headers, true); + RETURN(_data); + ELSE + PERFORM _api.raise_not_found(); + END IF; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.serve_user_media(INTEGER) + TO rest_anon, rest_user; +GRANT SELECT ON TABLE admin.user_media + TO rest_anon, rest_user; diff --git a/src/db/rest/media/_api_serve_user_or_default_media.sql b/src/db/rest/media/_api_serve_user_or_default_media.sql new file mode 100644 index 0000000..c079ba9 --- /dev/null +++ b/src/db/rest/media/_api_serve_user_or_default_media.sql @@ -0,0 +1,41 @@ +CREATE FUNCTION _api.serve_user_or_default_media( + _user_id INTEGER, + _type admin.user_media_type, + _default TEXT +) +RETURNS sys."*/*" +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _media_id INTEGER; +BEGIN + + SELECT id + FROM admin.user_media m + WHERE m.type = _type + AND m.user_id = _user_id + INTO _media_id; + + IF FOUND THEN + RETURN _api.serve_user_media(_media_id); + END IF; + + SELECT id + FROM admin.media m + WHERE m.name = _default + INTO _media_id; + + IF FOUND THEN + RETURN _api.serve_system_media(_media_id); + END IF; + + PERFORM _api_raise_not_found(); +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.serve_user_or_default_media(INTEGER, admin.user_media_type, TEXT) + 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_avatar.sql b/src/db/rest/media/api_profile_avatar.sql index 8607999..b3e456c 100644 --- a/src/db/rest/media/api_profile_avatar.sql +++ b/src/db/rest/media/api_profile_avatar.sql @@ -5,32 +5,16 @@ RETURNS sys."*/*" LANGUAGE plpgsql VOLATILE AS $BODY$ DECLARE - _id INTEGER; - _mod INTEGER; - _name TEXT; + _default 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); + _default := 'default_avatar_' || MOD(user_id, 25) || '.png'; + RETURN _api.serve_user_or_default_media( + user_id, + 'avatar'::admin.user_media_type, + _default + ); 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 index 272d021..d98f553 100644 --- a/src/db/rest/media/api_profile_banner.sql +++ b/src/db/rest/media/api_profile_banner.sql @@ -4,10 +4,21 @@ CREATE FUNCTION api.profile_banner( RETURNS sys."*/*" LANGUAGE plpgsql VOLATILE AS $BODY$ +DECLARE + _default TEXT; BEGIN - PERFORM _api.raise_deny(); + _default := 'default_banner_' || MOD(user_id, 25) || '.png'; + RETURN _api.serve_user_or_default_media( + user_id, + 'banner'::admin.user_media_type, + _default + ); END $BODY$; GRANT EXECUTE ON FUNCTION api.profile_banner(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/post/api_post.sql b/src/db/rest/post/api_post.sql index 0d60473..b5c42a8 100644 --- a/src/db/rest/post/api_post.sql +++ b/src/db/rest/post/api_post.sql @@ -6,7 +6,9 @@ CREATE VIEW api.post AS p.created, p.modified, COALESCE(c.cc, 0) - AS comment_count + AS comment_count, + COALESCE(l.lc, 0) + AS like_count FROM admin.post p LEFT JOIN ( @@ -20,6 +22,17 @@ CREATE VIEW api.post AS ) c ON p.id = c.post_id + LEFT JOIN ( + SELECT + COUNT(l.id) as lc, + l.post_id + FROM + admin.like l + GROUP BY + l.post_id + ) l + ON + p.id = l.post_id LEFT JOIN admin.user u ON diff --git a/src/db/rest/rest.sql b/src/db/rest/rest.sql index e203f27..783866a 100644 --- a/src/db/rest/rest.sql +++ b/src/db/rest/rest.sql @@ -15,7 +15,6 @@ GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user; -- util \i /db/rest/util/_api_trim.sql; -\i /db/rest/util/_api_serve_media.sql; \i /db/rest/util/_api_raise.sql; \i /db/rest/util/_api_raise_null.sql; \i /db/rest/util/_api_raise_unique.sql; @@ -47,6 +46,9 @@ GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user; \i /db/rest/like/api_like_delete.sql; -- media +\i /db/rest/media/_api_serve_user_media.sql; +\i /db/rest/media/_api_serve_system_media.sql; +\i /db/rest/media/_api_serve_user_or_default_media.sql; \i /db/rest/media/api_profile_avatar.sql; \i /db/rest/media/api_profile_banner.sql; diff --git a/src/db/rest/user/api_user.sql b/src/db/rest/user/api_user.sql index 6735775..d71fd1b 100644 --- a/src/db/rest/user/api_user.sql +++ b/src/db/rest/user/api_user.sql @@ -13,9 +13,74 @@ CREATE VIEW api.user AS u.profile_bio, u.created, u.modified, - u.seen + u.seen, + COALESCE(f.fc, 0) + AS follower_count, + COALESCE(fl.fc, 0) + AS followed_count, + COALESCE(c.cc, 0) + AS comment_count, + COALESCE(p.pc, 0) + AS post_count, + COALESCE(l.lc, 0) + AS like_count FROM admin.user u + LEFT JOIN ( + SELECT + COUNT(f.id) as fc, + f.followee_id + FROM + admin.follow f + GROUP BY + f.followee_id + ) f + ON + u.id = f.followee_id + LEFT JOIN ( + SELECT + COUNT(fl.id) as fc, + fl.follower_id + FROM + admin.follow fl + GROUP BY + fl.follower_id + ) fl + ON + u.id = fl.follower_id + LEFT JOIN ( + SELECT + COUNT(c.id) as cc, + c.user_id + FROM + admin.comment c + GROUP BY + c.user_id + ) c + ON + u.id = c.user_id + LEFT JOIN ( + SELECT + COUNT(p.id) as pc, + p.user_id + FROM + admin.post p + GROUP BY + p.user_id + ) p + ON + u.id = p.user_id + LEFT JOIN ( + SELECT + COUNT(l.id) as lc, + l.user_id + FROM + admin.like l + GROUP BY + l.user_id + ) l + ON + u.id = l.user_id WHERE u.deleted <> TRUE; diff --git a/src/db/rest/util/_api_raise_not_found.sql b/src/db/rest/util/_api_raise_not_found.sql new file mode 100644 index 0000000..f4997a6 --- /dev/null +++ b/src/db/rest/util/_api_raise_not_found.sql @@ -0,0 +1,16 @@ +CREATE FUNCTION _api.raise_not_found() +RETURNS BOOLEAN +LANGUAGE plpgsql VOLATILE +AS $BODY$ +BEGIN + PERFORM _api.raise( + _msg => 'api_not_found', + _err => 404 + ); + + RETURN TRUE; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.raise_not_found() + TO rest_anon, rest_user; |