diff options
Diffstat (limited to 'src/db')
35 files changed, 121 insertions, 91 deletions
diff --git a/src/db/migrations/0003.sql b/src/db/migrations/0003.sql new file mode 100644 index 0000000..285a8f5 --- /dev/null +++ b/src/db/migrations/0003.sql @@ -0,0 +1,11 @@ +BEGIN TRANSACTION; +SET search_path = public; + +-- Migration Start +ALTER SCHEMA admin RENAME TO xssbook; +-- Migration End; + +-- Set Current Revision +UPDATE sys.database_info SET curr_revision = 3 WHERE name = current_database(); + +COMMIT TRANSACTION; diff --git a/src/db/rest/comment/api_comment.sql b/src/db/rest/comment/api_comment.sql index c8a0e19..36db10c 100644 --- a/src/db/rest/comment/api_comment.sql +++ b/src/db/rest/comment/api_comment.sql @@ -7,13 +7,13 @@ CREATE VIEW api.comment AS c.created, c.modified FROM - admin.comment c + xssbook.comment c LEFT JOIN - admin.post p + xssbook.post p ON p.id = c.post_id LEFT JOIN - admin.user u + xssbook.user u ON u.id = c.user_id WHERE @@ -25,5 +25,5 @@ CREATE VIEW api.comment AS GRANT SELECT ON TABLE api.comment TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.comment +GRANT SELECT ON TABLE xssbook.comment TO rest_anon, rest_user; diff --git a/src/db/rest/comment/api_comment_delete.sql b/src/db/rest/comment/api_comment_delete.sql index 262b2ed..9762562 100644 --- a/src/db/rest/comment/api_comment_delete.sql +++ b/src/db/rest/comment/api_comment_delete.sql @@ -11,7 +11,7 @@ BEGIN PERFORM _api.raise_deny(); END IF; - UPDATE admin.comment SET + UPDATE xssbook.comment SET deleted = TRUE, modified = clock_timestamp() WHERE id = OLD.id; @@ -22,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.comment_delete() TO rest_user; GRANT DELETE ON TABLE api.comment TO rest_user; -GRANT UPDATE ON TABLE admin.comment +GRANT UPDATE ON TABLE xssbook.comment TO rest_user; CREATE TRIGGER api_comment_delete_trgr diff --git a/src/db/rest/comment/api_comment_insert.sql b/src/db/rest/comment/api_comment_insert.sql index 990beef..f3c78aa 100644 --- a/src/db/rest/comment/api_comment_insert.sql +++ b/src/db/rest/comment/api_comment_insert.sql @@ -16,7 +16,7 @@ BEGIN ); PERFORM TRUE - FROM admin.post + FROM xssbook.post WHERE id = NEW.post_id; IF NOT FOUND THEN @@ -26,7 +26,7 @@ BEGIN ); END IF; - INSERT INTO admin.comment ( + INSERT INTO xssbook.comment ( user_id, post_id, content @@ -46,7 +46,7 @@ GRANT EXECUTE ON FUNCTION _api.comment_insert() TO rest_user; GRANT INSERT ON TABLE api.comment TO rest_user; -GRANT INSERT ON TABLE admin.comment +GRANT INSERT ON TABLE xssbook.comment TO rest_user; GRANT UPDATE ON TABLE sys.comment_id_seq TO rest_user; diff --git a/src/db/rest/comment/api_comment_update.sql b/src/db/rest/comment/api_comment_update.sql index b8fc16d..856cd26 100644 --- a/src/db/rest/comment/api_comment_update.sql +++ b/src/db/rest/comment/api_comment_update.sql @@ -27,7 +27,7 @@ BEGIN END IF; IF _changed THEN - UPDATE admin.comment SET + UPDATE xssbook.comment SET content = NEW.content, modified = clock_timestamp() WHERE id = OLD.id; @@ -41,7 +41,7 @@ GRANT EXECUTE ON FUNCTION _api.comment_update() TO rest_user; GRANT UPDATE ON TABLE api.comment TO rest_user; -GRANT UPDATE ON TABLE admin.comment +GRANT UPDATE ON TABLE xssbook.comment TO rest_user; CREATE TRIGGER api_comment_update_trgr diff --git a/src/db/rest/follow/api_follow.sql b/src/db/rest/follow/api_follow.sql index c0a07e5..6893240 100644 --- a/src/db/rest/follow/api_follow.sql +++ b/src/db/rest/follow/api_follow.sql @@ -7,9 +7,9 @@ CREATE VIEW api.follow AS f.created, f.modified FROM - admin.follow f; + xssbook.follow f; GRANT SELECT ON TABLE api.follow TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.follow +GRANT SELECT ON TABLE xssbook.follow TO rest_anon, rest_user; diff --git a/src/db/rest/follow/api_follow_delete.sql b/src/db/rest/follow/api_follow_delete.sql index 46454cf..f9a5f57 100644 --- a/src/db/rest/follow/api_follow_delete.sql +++ b/src/db/rest/follow/api_follow_delete.sql @@ -11,7 +11,7 @@ BEGIN PERFORM _api.raise_deny(); END IF; - UPDATE admin.follow SET + UPDATE xssbook.follow SET value = FALSE, modified = clock_timestamp() WHERE id = OLD.id; @@ -22,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.follow_delete() TO rest_user; GRANT DELETE ON TABLE api.follow TO rest_user; -GRANT UPDATE ON TABLE admin.follow +GRANT UPDATE ON TABLE xssbook.follow TO rest_user; CREATE TRIGGER api_follow_delete_trgr diff --git a/src/db/rest/follow/api_follow_insert.sql b/src/db/rest/follow/api_follow_insert.sql index 6351855..0a20e04 100644 --- a/src/db/rest/follow/api_follow_insert.sql +++ b/src/db/rest/follow/api_follow_insert.sql @@ -14,7 +14,7 @@ BEGIN NEW.value := COALESCE(NEW.value, TRUE); - INSERT INTO admin.follow ( + INSERT INTO xssbook.follow ( follower_id, followee_id, value @@ -34,7 +34,7 @@ GRANT EXECUTE ON FUNCTION _api.follow_insert() TO rest_user; GRANT INSERT ON TABLE api.follow TO rest_user; -GRANT INSERT ON TABLE admin.follow +GRANT INSERT ON TABLE xssbook.follow TO rest_user; GRANT UPDATE ON TABLE sys.follow_id_seq TO rest_user; diff --git a/src/db/rest/follow/api_follow_update.sql b/src/db/rest/follow/api_follow_update.sql index 2164829..52fa7de 100644 --- a/src/db/rest/follow/api_follow_update.sql +++ b/src/db/rest/follow/api_follow_update.sql @@ -20,7 +20,7 @@ BEGIN END IF; IF _changed THEN - UPDATE admin.follow SET + UPDATE xssbook.follow SET value = NEW.value, modified = clock_timestamp() WHERE id = OLD.id; @@ -34,7 +34,7 @@ GRANT EXECUTE ON FUNCTION _api.follow_update() TO rest_user; GRANT UPDATE ON TABLE api.follow TO rest_user; -GRANT UPDATE ON TABLE admin.follow +GRANT UPDATE ON TABLE xssbook.follow TO rest_user; CREATE TRIGGER api_follow_update_trgr diff --git a/src/db/rest/like/api_like.sql b/src/db/rest/like/api_like.sql index 6588b43..f3d8e85 100644 --- a/src/db/rest/like/api_like.sql +++ b/src/db/rest/like/api_like.sql @@ -8,9 +8,9 @@ CREATE VIEW api.like AS l.created, l.modified FROM - admin.like l; + xssbook.like l; GRANT SELECT ON TABLE api.like TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.like +GRANT SELECT ON TABLE xssbook.like TO rest_anon, rest_user; diff --git a/src/db/rest/like/api_like_delete.sql b/src/db/rest/like/api_like_delete.sql index 7209a40..b8231b7 100644 --- a/src/db/rest/like/api_like_delete.sql +++ b/src/db/rest/like/api_like_delete.sql @@ -11,7 +11,7 @@ BEGIN PERFORM _api.raise_deny(); END IF; - UPDATE admin.like SET + UPDATE xssbook.like SET value = FALSE, modified = clock_timestamp() WHERE id = OLD.id; @@ -22,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.like_delete() TO rest_user; GRANT DELETE ON TABLE api.like TO rest_user; -GRANT UPDATE ON TABLE admin.like +GRANT UPDATE ON TABLE xssbook.like TO rest_user; CREATE TRIGGER api_like_delete_trgr diff --git a/src/db/rest/like/api_like_insert.sql b/src/db/rest/like/api_like_insert.sql index a02ad4e..1c8bc8a 100644 --- a/src/db/rest/like/api_like_insert.sql +++ b/src/db/rest/like/api_like_insert.sql @@ -17,7 +17,7 @@ BEGIN NEW.value := COALESCE(NEW.value, TRUE); - INSERT INTO admin.like ( + INSERT INTO xssbook.like ( user_id, post_id, comment_id, @@ -39,7 +39,7 @@ GRANT EXECUTE ON FUNCTION _api.like_insert() TO rest_user; GRANT INSERT ON TABLE api.like TO rest_user; -GRANT INSERT ON TABLE admin.like +GRANT INSERT ON TABLE xssbook.like TO rest_user; GRANT UPDATE ON TABLE sys.like_id_seq TO rest_user; diff --git a/src/db/rest/like/api_like_update.sql b/src/db/rest/like/api_like_update.sql index 76db73a..1aab8cb 100644 --- a/src/db/rest/like/api_like_update.sql +++ b/src/db/rest/like/api_like_update.sql @@ -20,7 +20,7 @@ BEGIN END IF; IF _changed THEN - UPDATE admin.like SET + UPDATE xssbook.like SET value = NEW.value, modified = clock_timestamp() WHERE id = OLD.id; @@ -34,7 +34,7 @@ GRANT EXECUTE ON FUNCTION _api.like_update() TO rest_user; GRANT UPDATE ON TABLE api.like TO rest_user; -GRANT UPDATE ON TABLE admin.like +GRANT UPDATE ON TABLE xssbook.like TO rest_user; CREATE TRIGGER api_like_update_trgr diff --git a/src/db/rest/login/_api_validate_role.sql b/src/db/rest/login/_api_validate_role.sql index 9f1e54f..dcf9c17 100644 --- a/src/db/rest/login/_api_validate_role.sql +++ b/src/db/rest/login/_api_validate_role.sql @@ -20,7 +20,7 @@ $BODY$; CREATE CONSTRAINT TRIGGER api_validate_role_trgr AFTER INSERT OR UPDATE - ON admin.user + ON xssbook.user FOR EACH ROW EXECUTE PROCEDURE _api.validate_role(); diff --git a/src/db/rest/login/_api_verify_jwt.sql b/src/db/rest/login/_api_verify_jwt.sql index 9e63cc9..3d47052 100644 --- a/src/db/rest/login/_api_verify_jwt.sql +++ b/src/db/rest/login/_api_verify_jwt.sql @@ -31,7 +31,7 @@ BEGIN _user_id = _payload->>'user_id'; - UPDATE admin.user + UPDATE xssbook.user SET seen = clock_timestamp() WHERE id = _user_id; @@ -43,5 +43,5 @@ GRANT EXECUTE ON FUNCTION _api.verify_jwt(TEXT) TO rest_anon, rest_user; GRANT SELECT ON TABLE sys.database_info TO rest_anon, rest_user; -GRANT UPDATE ON TABLE admin.user +GRANT UPDATE ON TABLE xssbook.user TO rest_anon, rest_user; diff --git a/src/db/rest/login/api_login.sql b/src/db/rest/login/api_login.sql index 0cf0535..6f0ba1c 100644 --- a/src/db/rest/login/api_login.sql +++ b/src/db/rest/login/api_login.sql @@ -11,7 +11,7 @@ DECLARE _token sys.JWT; BEGIN SELECT role INTO _role - FROM admin.user u + FROM xssbook.user u WHERE u.username = login.username AND u.password = login.password; @@ -23,7 +23,7 @@ BEGIN END IF; SELECT id INTO _user_id - FROM admin.user u + FROM xssbook.user u WHERE u.username = login.username; _token = _api.sign_jwt( @@ -37,5 +37,5 @@ $BODY$; GRANT EXECUTE ON FUNCTION api.login(TEXT, TEXT) TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.user +GRANT SELECT ON TABLE xssbook.user TO rest_anon, rest_user; diff --git a/src/db/rest/media/_api_serve_system_media.sql b/src/db/rest/media/_api_serve_system_media.sql index 5cd87c2..7e38949 100644 --- a/src/db/rest/media/_api_serve_system_media.sql +++ b/src/db/rest/media/_api_serve_system_media.sql @@ -13,12 +13,12 @@ BEGIN '{"Content-Disposition": "inline; filename=\"%s\""},' '{"Cache-Control": "max-age=259200"}]' , m.mime, m.name) - FROM admin.media m + FROM xssbook.media m WHERE m.id = _media_id INTO _headers; SELECT m.content - FROM admin.media m + FROM xssbook.media m WHERE m.id = _media_id INTO _data; @@ -33,5 +33,5 @@ $BODY$; GRANT EXECUTE ON FUNCTION _api.serve_system_media(INTEGER) TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.media +GRANT SELECT ON TABLE xssbook.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 index 3487493..c5942f1 100644 --- a/src/db/rest/media/_api_serve_user_media.sql +++ b/src/db/rest/media/_api_serve_user_media.sql @@ -13,12 +13,12 @@ BEGIN '{"Content-Disposition": "inline"},' '{"Cache-Control": "max-age=259200"}]' , m.mime) - FROM admin.user_media m + FROM xssbook.user_media m WHERE m.id = _media_id INTO _headers; SELECT m.content - FROM admin.user_media m + FROM xssbook.user_media m WHERE m.id = _media_id INTO _data; @@ -33,5 +33,5 @@ $BODY$; GRANT EXECUTE ON FUNCTION _api.serve_user_media(INTEGER) TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.user_media +GRANT SELECT ON TABLE xssbook.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 index c079ba9..08f6483 100644 --- a/src/db/rest/media/_api_serve_user_or_default_media.sql +++ b/src/db/rest/media/_api_serve_user_or_default_media.sql @@ -1,6 +1,6 @@ CREATE FUNCTION _api.serve_user_or_default_media( _user_id INTEGER, - _type admin.user_media_type, + _type xssbook.user_media_type, _default TEXT ) RETURNS sys."*/*" @@ -11,7 +11,7 @@ DECLARE BEGIN SELECT id - FROM admin.user_media m + FROM xssbook.user_media m WHERE m.type = _type AND m.user_id = _user_id INTO _media_id; @@ -21,7 +21,7 @@ BEGIN END IF; SELECT id - FROM admin.media m + FROM xssbook.media m WHERE m.name = _default INTO _media_id; @@ -33,9 +33,9 @@ BEGIN END $BODY$; -GRANT EXECUTE ON FUNCTION _api.serve_user_or_default_media(INTEGER, admin.user_media_type, TEXT) +GRANT EXECUTE ON FUNCTION _api.serve_user_or_default_media(INTEGER, xssbook.user_media_type, TEXT) TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.user_media +GRANT SELECT ON TABLE xssbook.user_media TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.media +GRANT SELECT ON TABLE xssbook.media TO rest_anon, rest_user; diff --git a/src/db/rest/media/api_delete_user_media.sql b/src/db/rest/media/api_delete_user_media.sql index d9a4529..4db4540 100644 --- a/src/db/rest/media/api_delete_user_media.sql +++ b/src/db/rest/media/api_delete_user_media.sql @@ -1,5 +1,5 @@ CREATE FUNCTION api.delete_user_media( - media_type admin.user_media_type + media_type xssbook.user_media_type ) RETURNS void LANGUAGE plpgsql VOLATILE @@ -11,16 +11,16 @@ BEGIN _user_id = _api.get_user_id(); DELETE FROM - admin.user_media + xssbook.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) +GRANT EXECUTE ON FUNCTION api.delete_user_media(xssbook.user_media_type) TO rest_user; -GRANT DELETE ON TABLE admin.user_media +GRANT DELETE ON TABLE xssbook.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_profile_avatar.sql b/src/db/rest/media/api_profile_avatar.sql index b3e456c..0bcd4ab 100644 --- a/src/db/rest/media/api_profile_avatar.sql +++ b/src/db/rest/media/api_profile_avatar.sql @@ -10,7 +10,7 @@ BEGIN _default := 'default_avatar_' || MOD(user_id, 25) || '.png'; RETURN _api.serve_user_or_default_media( user_id, - 'avatar'::admin.user_media_type, + 'avatar'::xssbook.user_media_type, _default ); END diff --git a/src/db/rest/media/api_profile_banner.sql b/src/db/rest/media/api_profile_banner.sql index d98f553..cdadaf4 100644 --- a/src/db/rest/media/api_profile_banner.sql +++ b/src/db/rest/media/api_profile_banner.sql @@ -10,7 +10,7 @@ BEGIN _default := 'default_banner_' || MOD(user_id, 25) || '.png'; RETURN _api.serve_user_or_default_media( user_id, - 'banner'::admin.user_media_type, + 'banner'::xssbook.user_media_type, _default ); END @@ -18,7 +18,7 @@ $BODY$; GRANT EXECUTE ON FUNCTION api.profile_banner(INTEGER) TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.user_media +GRANT SELECT ON TABLE xssbook.user_media TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.media +GRANT SELECT ON TABLE xssbook.media TO rest_anon, rest_user; diff --git a/src/db/rest/media/api_update_user_media.sql b/src/db/rest/media/api_update_user_media.sql index be8835a..a2d8f5c 100644 --- a/src/db/rest/media/api_update_user_media.sql +++ b/src/db/rest/media/api_update_user_media.sql @@ -1,5 +1,5 @@ CREATE FUNCTION api.update_user_media( - media_type admin.user_media_type, + media_type xssbook.user_media_type, mime TEXT, content TEXT ) @@ -13,7 +13,7 @@ BEGIN _user_id = _api.get_user_id(); _data = decode(content, 'base64'); - INSERT INTO admin.user_media ( + INSERT INTO xssbook.user_media ( user_id, content, mime, @@ -33,9 +33,9 @@ BEGIN END $BODY$; -GRANT EXECUTE ON FUNCTION api.update_user_media(admin.user_media_type, TEXT, TEXT) +GRANT EXECUTE ON FUNCTION api.update_user_media(xssbook.user_media_type, TEXT, TEXT) TO rest_user; -GRANT INSERT, UPDATE ON TABLE admin.user_media +GRANT INSERT, UPDATE ON TABLE xssbook.user_media TO rest_user; GRANT UPDATE ON TABLE sys.user_media_id_seq TO rest_user; diff --git a/src/db/rest/post/api_post.sql b/src/db/rest/post/api_post.sql index b5c42a8..5796a19 100644 --- a/src/db/rest/post/api_post.sql +++ b/src/db/rest/post/api_post.sql @@ -10,13 +10,13 @@ CREATE VIEW api.post AS COALESCE(l.lc, 0) AS like_count FROM - admin.post p + xssbook.post p LEFT JOIN ( SELECT COUNT(c.id) as cc, c.post_id FROM - admin.comment c + xssbook.comment c GROUP BY c.post_id ) c @@ -27,14 +27,14 @@ CREATE VIEW api.post AS COUNT(l.id) as lc, l.post_id FROM - admin.like l + xssbook.like l GROUP BY l.post_id ) l ON p.id = l.post_id LEFT JOIN - admin.user u + xssbook.user u ON u.id = p.user_id WHERE @@ -46,5 +46,5 @@ CREATE VIEW api.post AS GRANT SELECT ON TABLE api.post TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.post +GRANT SELECT ON TABLE xssbook.post TO rest_anon, rest_user; diff --git a/src/db/rest/post/api_post_delete.sql b/src/db/rest/post/api_post_delete.sql index 8f26b40..5c2a014 100644 --- a/src/db/rest/post/api_post_delete.sql +++ b/src/db/rest/post/api_post_delete.sql @@ -11,7 +11,7 @@ BEGIN PERFORM _api.raise_deny(); END IF; - UPDATE admin.post SET + UPDATE xssbook.post SET deleted = TRUE, modified = clock_timestamp() WHERE id = OLD.id; @@ -22,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.post_delete() TO rest_user; GRANT DELETE ON TABLE api.post TO rest_user; -GRANT UPDATE ON TABLE admin.post +GRANT UPDATE ON TABLE xssbook.post TO rest_user; CREATE TRIGGER api_post_delete_trgr diff --git a/src/db/rest/post/api_post_insert.sql b/src/db/rest/post/api_post_insert.sql index e0594dc..c71eb01 100644 --- a/src/db/rest/post/api_post_insert.sql +++ b/src/db/rest/post/api_post_insert.sql @@ -16,7 +16,7 @@ BEGIN _max => 4096 ); - INSERT INTO admin.post ( + INSERT INTO xssbook.post ( user_id, content ) VALUES ( @@ -34,7 +34,7 @@ GRANT EXECUTE ON FUNCTION _api.post_insert() TO rest_user; GRANT INSERT ON TABLE api.post TO rest_user; -GRANT INSERT ON TABLE admin.post +GRANT INSERT ON TABLE xssbook.post TO rest_user; GRANT UPDATE ON TABLE sys.post_id_seq TO rest_user; diff --git a/src/db/rest/post/api_post_update.sql b/src/db/rest/post/api_post_update.sql index 7b4360d..351a698 100644 --- a/src/db/rest/post/api_post_update.sql +++ b/src/db/rest/post/api_post_update.sql @@ -27,7 +27,7 @@ BEGIN END IF; IF _changed THEN - UPDATE admin.post SET + UPDATE xssbook.post SET content = NEW.content, modified = clock_timestamp() WHERE id = OLD.id; @@ -41,7 +41,7 @@ GRANT EXECUTE ON FUNCTION _api.post_update() TO rest_user; GRANT UPDATE ON TABLE api.post TO rest_user; -GRANT UPDATE ON TABLE admin.post +GRANT UPDATE ON TABLE xssbook.post TO rest_user; CREATE TRIGGER api_post_update_trgr diff --git a/src/db/rest/rest.sql b/src/db/rest/rest.sql index 6c3fb7d..e86ab23 100644 --- a/src/db/rest/rest.sql +++ b/src/db/rest/rest.sql @@ -7,7 +7,7 @@ CREATE SCHEMA api; DROP SCHEMA IF EXISTS _api CASCADE; CREATE SCHEMA _api; -GRANT USAGE ON SCHEMA admin TO rest_anon, rest_user; +GRANT USAGE ON SCHEMA xssbook TO rest_anon, rest_user; GRANT USAGE ON SCHEMA sys TO rest_anon, rest_user; GRANT USAGE ON SCHEMA api TO rest_anon, rest_user; diff --git a/src/db/rest/user/api_update_password.sql b/src/db/rest/user/api_update_password.sql index 34cc1ac..acc53de 100644 --- a/src/db/rest/user/api_update_password.sql +++ b/src/db/rest/user/api_update_password.sql @@ -20,7 +20,7 @@ BEGIN SELECT password INTO _real_password - FROM admin.user + FROM xssbook.user WHERE id = _user_id; IF _real_password <> current_password THEN @@ -30,7 +30,7 @@ BEGIN END IF; UPDATE - admin.user + xssbook.user SET "password" = new_password WHERE @@ -40,5 +40,5 @@ $BODY$; GRANT EXECUTE ON FUNCTION api.update_password(TEXT, TEXT) TO rest_user; -GRANT SELECT, UPDATE ON TABLE admin.user +GRANT SELECT, UPDATE ON TABLE xssbook.user TO rest_user; diff --git a/src/db/rest/user/api_user.sql b/src/db/rest/user/api_user.sql index 0660f42..2087ab9 100644 --- a/src/db/rest/user/api_user.sql +++ b/src/db/rest/user/api_user.sql @@ -29,13 +29,13 @@ CREATE VIEW api.user AS mb.mime AS banner_mime FROM - admin.user u + xssbook.user u LEFT JOIN ( SELECT COUNT(f.id) as fc, f.followee_id FROM - admin.follow f + xssbook.follow f GROUP BY f.followee_id ) f @@ -46,7 +46,7 @@ CREATE VIEW api.user AS COUNT(fl.id) as fc, fl.follower_id FROM - admin.follow fl + xssbook.follow fl GROUP BY fl.follower_id ) fl @@ -57,7 +57,7 @@ CREATE VIEW api.user AS COUNT(c.id) as cc, c.user_id FROM - admin.comment c + xssbook.comment c GROUP BY c.user_id ) c @@ -68,7 +68,7 @@ CREATE VIEW api.user AS COUNT(p.id) as pc, p.user_id FROM - admin.post p + xssbook.post p GROUP BY p.user_id ) p @@ -79,7 +79,7 @@ CREATE VIEW api.user AS COUNT(l.id) as lc, l.user_id FROM - admin.like l + xssbook.like l GROUP BY l.user_id ) l @@ -90,7 +90,7 @@ CREATE VIEW api.user AS ma.mime, ma.user_id FROM - admin.user_media ma + xssbook.user_media ma WHERE ma.type = 'avatar' ) ma @@ -101,7 +101,7 @@ CREATE VIEW api.user AS mb.mime, mb.user_id FROM - admin.user_media mb + xssbook.user_media mb WHERE mb.type = 'banner' ) mb @@ -112,5 +112,5 @@ CREATE VIEW api.user AS GRANT SELECT ON TABLE api.user TO rest_anon, rest_user; -GRANT SELECT ON TABLE admin.user +GRANT SELECT ON TABLE xssbook.user TO rest_anon, rest_user; diff --git a/src/db/rest/user/api_user_delete.sql b/src/db/rest/user/api_user_delete.sql index 4389fa0..fbcf719 100644 --- a/src/db/rest/user/api_user_delete.sql +++ b/src/db/rest/user/api_user_delete.sql @@ -11,7 +11,7 @@ BEGIN PERFORM _api.raise_deny(); END IF; - UPDATE admin.user SET + UPDATE xssbook.user SET deleted = TRUE, modified = clock_timestamp() WHERE id = _user_id; @@ -22,7 +22,7 @@ GRANT EXECUTE ON FUNCTION _api.user_delete() TO rest_user; GRANT DELETE ON TABLE api.user TO rest_user; -GRANT UPDATE ON TABLE admin.user +GRANT UPDATE ON TABLE xssbook.user TO rest_user; CREATE TRIGGER api_user_delete_trgr diff --git a/src/db/rest/user/api_user_insert.sql b/src/db/rest/user/api_user_insert.sql index 1a6ef7c..cf70a8a 100644 --- a/src/db/rest/user/api_user_insert.sql +++ b/src/db/rest/user/api_user_insert.sql @@ -22,7 +22,7 @@ BEGIN _max => 24 ); - PERFORM TRUE FROM admin.user + PERFORM TRUE FROM xssbook.user WHERE username = NEW.username; IF FOUND THEN @@ -84,7 +84,7 @@ BEGIN ); NEW.profile_bio = COALESCE(NEW.profile_bio, ''::text); - INSERT INTO admin.user ( + INSERT INTO xssbook.user ( username, password, first_name, @@ -118,7 +118,7 @@ GRANT EXECUTE ON FUNCTION _api.user_insert() TO rest_anon, rest_user; GRANT INSERT ON TABLE api.user TO rest_anon, rest_user; -GRANT INSERT ON TABLE admin.user +GRANT INSERT ON TABLE xssbook.user TO rest_anon, rest_user; GRANT UPDATE ON TABLE sys.user_id_seq TO rest_anon, rest_user; diff --git a/src/db/rest/user/api_user_update.sql b/src/db/rest/user/api_user_update.sql index c26c680..27b9042 100644 --- a/src/db/rest/user/api_user_update.sql +++ b/src/db/rest/user/api_user_update.sql @@ -24,7 +24,7 @@ BEGIN ); IF NEW.username IS DISTINCT FROM OLD.username THEN - PERFORM TRUE FROM admin.user + PERFORM TRUE FROM xssbook.user WHERE username = NEW.username; IF FOUND THEN PERFORM _api.raise_unique('username'); @@ -117,7 +117,7 @@ BEGIN END IF; IF _changed THEN - UPDATE admin.user SET + UPDATE xssbook.user SET username = NEW.username, first_name = NEW.first_name, last_name = NEW.last_name, @@ -138,7 +138,7 @@ GRANT EXECUTE ON FUNCTION _api.user_update() TO rest_user; GRANT UPDATE ON TABLE api.user TO rest_user; -GRANT UPDATE ON TABLE admin.user +GRANT UPDATE ON TABLE xssbook.user TO rest_user; CREATE TRIGGER api_user_update_trgr diff --git a/src/db/rest/util/_api_get_user_id.sql b/src/db/rest/util/_api_get_user_id.sql index e86afc3..799affc 100644 --- a/src/db/rest/util/_api_get_user_id.sql +++ b/src/db/rest/util/_api_get_user_id.sql @@ -10,7 +10,7 @@ BEGIN TRUE )::JSON->>'user_id'; - UPDATE admin.user + UPDATE xssbook.user SET seen = clock_timestamp() WHERE id = _user_id; @@ -18,5 +18,5 @@ BEGIN END $BODY$; -GRANT UPDATE ON TABLE admin.user +GRANT UPDATE ON TABLE xssbook.user TO rest_anon, rest_user; diff --git a/src/db/rest/util/api_root.sql b/src/db/rest/util/api_root.sql new file mode 100644 index 0000000..7141a5f --- /dev/null +++ b/src/db/rest/util/api_root.sql @@ -0,0 +1,19 @@ + +CREATE FUNCTION FUNCTION _api.root() +RETURNS JSON +LANGUAGE plpgsql; +AS $BODY$ +DECLARE +openapi JSON = $$ + { + "swagger": "2.0", + "info": { + "title": "XSSBook", + "description": "XSSBook Api Documentation" + } + } +$$; +BEGIN + RETURN openapi; +END +$BODY$; |