post comments, refactor post loading, hide load more btn

12 files changed, 394 insertions, 10 deletions

diff --git a/db/rest/comment/api_comment.sql b/db/rest/comment/api_comment.sql
new file mode 100644
index 0000000..e50ca2f
--- /dev/null
+++ b/db/rest/comment/api_comment.sql
@@ -0,0 +1,15 @@
+CREATE VIEW api.comment AS
+	SELECT
+		c.id,
+		c.user_id,
+		c.post_id,
+		c.content,
+		c.date
+	FROM
+		admin.comment c
+	ORDER BY id ASC;
+
+GRANT SELECT ON TABLE api.comment
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.comment
+	TO rest_anon, rest_user;
diff --git a/db/rest/comment/api_comment_delete.sql b/db/rest/comment/api_comment_delete.sql
new file mode 100644
index 0000000..d7db8a4
--- /dev/null
+++ b/db/rest/comment/api_comment_delete.sql
@@ -0,0 +1,31 @@
+CREATE FUNCTION _api.comment_delete()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	DELETE FROM admin.comment
+	WHERE user_id = _user_id
+	AND id = OLD.id;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.comment_delete()
+	TO rest_user;
+GRANT DELETE ON TABLE api.comment
+	TO rest_user;
+GRANT DELETE ON TABLE admin.comment
+	TO rest_user;
+
+CREATE TRIGGER api_comment_delete_trgr
+	INSTEAD OF DELETE
+			ON api.comment
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.comment_delete();
diff --git a/db/rest/comment/api_comment_insert.sql b/db/rest/comment/api_comment_insert.sql
new file mode 100644
index 0000000..878e194
--- /dev/null
+++ b/db/rest/comment/api_comment_insert.sql
@@ -0,0 +1,56 @@
+CREATE FUNCTION _api.comment_insert()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	NEW.content	:= _api.trim(NEW.content);
+	PERFORM _api.validate_text(
+		_text => NEW.content,
+		_column => 'content',
+		_min => 1,
+		_max => 1024
+	);
+
+	PERFORM TRUE
+	FROM admin.post
+	WHERE id = NEW.post_id;
+
+	IF NOT FOUND THEN
+		PERFORM _api.raise(
+			_msg => 'api_null_post',
+			_err => 400
+		);
+	END IF;
+
+	INSERT INTO admin.comment (
+		user_id,
+		post_id,
+		content
+	) VALUES (
+		_user_id,
+		NEW.post_id,
+		NEW.content
+	);
+
+	RETURN NEW;
+END
+$BODY$;
+
+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
+	TO rest_user;
+GRANT UPDATE ON TABLE sys.comment_id_seq
+	TO rest_user;
+
+CREATE TRIGGER api_comment_insert_trgr
+	INSTEAD OF INSERT
+			ON api.comment
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.comment_insert();
diff --git a/db/rest/comment/api_comment_update.sql b/db/rest/comment/api_comment_update.sql
new file mode 100644
index 0000000..d6b4aca
--- /dev/null
+++ b/db/rest/comment/api_comment_update.sql
@@ -0,0 +1,50 @@
+CREATE FUNCTION _api.comment_update()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+	_changed BOOLEAN;
+BEGIN
+	_user_id = _api.get_user_id();
+	_changed = FALSE;
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	NEW.content = COALESCE(NEW.content, OLD.content);
+	NEW.content := _api.trim(NEW.content);
+	PERFORM _api.validate_text(
+		_text => NEW.content,
+		_column => 'content',
+		_min => 1,
+		_max => 1024
+	);
+
+	IF NEW.content IS DISTINCT FROM OLD.content THEN
+		_changed = TRUE;
+	END IF;
+
+	IF _changed THEN
+		UPDATE admin.comment
+			SET content = NEW.content
+			WHERE id = OLD.id;
+	END IF;
+
+	RETURN NEW;
+END
+$BODY$;
+
+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
+	TO rest_user;
+
+CREATE TRIGGER api_comment_update_trgr
+	INSTEAD OF UPDATE
+			ON api.comment
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.comment_update();
diff --git a/db/rest/post/api_post.sql b/db/rest/post/api_post.sql
index b49289c..375f292 100644
--- a/db/rest/post/api_post.sql
+++ b/db/rest/post/api_post.sql
@@ -3,10 +3,21 @@ CREATE VIEW api.post AS
 		p.id,
 		p.user_id,
 		p.content,
-		p.date
+		p.date,
+		COALESCE(c.cc, 0)
+			AS comment_count
 	FROM
 		admin.post p
-	ORDER BY id DESC;
+	LEFT JOIN (
+		SELECT
+			COUNT(c.id) as cc,
+			c.post_id
+		FROM
+			admin.comment c
+		GROUP BY
+			c.post_id
+	) c ON p.id = c.post_id
+	ORDER BY p.id DESC;
 
 GRANT SELECT ON TABLE api.post
 	TO rest_anon, rest_user;
diff --git a/db/rest/post/api_post_insert.sql b/db/rest/post/api_post_insert.sql
index 02b9d8d..8b2eb48 100644
--- a/db/rest/post/api_post_insert.sql
+++ b/db/rest/post/api_post_insert.sql
@@ -7,6 +7,8 @@ DECLARE
 BEGIN
 	_user_id = _api.get_user_id();
 
+	NEW.content	:= _api.trim(NEW.content);
+
 	PERFORM _api.validate_text(
 		_text => NEW.content,
 		_column => 'content',
diff --git a/db/rest/post/api_post_update.sql b/db/rest/post/api_post_update.sql
index 915d0cd..70230d0 100644
--- a/db/rest/post/api_post_update.sql
+++ b/db/rest/post/api_post_update.sql
@@ -3,13 +3,45 @@ RETURNS TRIGGER
 LANGUAGE plpgsql VOLATILE
 AS $BODY$
 DECLARE
-	_length INTEGER;
+	_user_id INTEGER;
+	_changed BOOLEAN;
 BEGIN
+	_user_id = _api.get_user_id();
+	_changed = FALSE;
+
+	IF OLD.user_id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	NEW.content = COALESCE(NEW.content, OLD.content);
+	NEW.content := _api.trim(NEW.content);
+	PERFORM _api.validate_text(
+		_text => NEW.content,
+		_column => 'content',
+		_min => 1,
+		_max => 4096
+	);
+
+	IF NEW.content IS DISTINCT FROM OLD.content THEN
+		_changed = TRUE;
+	END IF;
+
+	IF _changed THEN
+		UPDATE admin.post
+			SET content = NEW.content
+			WHERE id = OLD.id;
+	END IF;
+
 	RETURN NEW;
 END
 $BODY$;
 
-GRANT EXECUTE ON FUNCTION _api.post_update() TO rest_user;
+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
+	TO rest_user;
 
 CREATE TRIGGER api_post_update_trgr
 	INSTEAD OF UPDATE
diff --git a/db/rest/rest.sql b/db/rest/rest.sql
index a286f9d..54f5118 100644
--- a/db/rest/rest.sql
+++ b/db/rest/rest.sql
@@ -14,6 +14,7 @@ GRANT USAGE ON SCHEMA api TO rest_anon, rest_user;
 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;
@@ -34,6 +35,12 @@ GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user;
 \i /db/rest/post/api_post_update.sql;
 \i /db/rest/post/api_post_delete.sql;
 
+-- comment
+\i /db/rest/comment/api_comment.sql;
+\i /db/rest/comment/api_comment_insert.sql;
+\i /db/rest/comment/api_comment_update.sql;
+\i /db/rest/comment/api_comment_delete.sql;
+
 -- login
 \i /db/rest/login/_api_sign_jwt.sql;
 \i /db/rest/login/_api_verify_jwt.sql;
diff --git a/db/rest/user/api_user_insert.sql b/db/rest/user/api_user_insert.sql
index da3ae2d..2297ecd 100644
--- a/db/rest/user/api_user_insert.sql
+++ b/db/rest/user/api_user_insert.sql
@@ -5,6 +5,16 @@ AS $BODY$
 DECLARE
 	_length INTEGER;
 BEGIN
+
+	NEW.username	:= _api.trim(NEW.username);
+	NEW.password	:= _api.trim(NEW.password);
+	NEW.first_name	:= _api.trim(NEW.first_name);
+	NEW.last_name	:= _api.trim(NEW.last_name);
+	NEW.middle_name	:= _api.trim(NEW.middle_name);
+	NEW.email		:= _api.trim(NEW.email);
+	NEW.gender		:= _api.trim(NEW.gender);
+	NEW.profile_bio	:= _api.trim(NEW.profile_bio);
+
 	PERFORM _api.validate_text(
 		_text => NEW.username,
 		_column => 'username',
@@ -22,6 +32,7 @@ BEGIN
 	PERFORM _api.validate_text(
 		_text => NEW.password,
 		_column => 'password',
+		_min => 1,
 		_max => 256
 	);
 
@@ -82,8 +93,6 @@ BEGIN
 		email,
 		gender,
 		birth_date,
-		profile_avatar,
-		profile_banner,
 		profile_bio
 	) VALUES (
 		NEW.username,
@@ -94,8 +103,6 @@ BEGIN
 		NEW.email,
 		NEW.gender,
 		NEW.birth_date,
-		NEW.profile_avatar,
-		NEW.profile_banner,
 		NEW.profile_bio
 	);
 
diff --git a/db/rest/user/api_user_update.sql b/db/rest/user/api_user_update.sql
index c6e7f4f..28e4368 100644
--- a/db/rest/user/api_user_update.sql
+++ b/db/rest/user/api_user_update.sql
@@ -3,15 +3,161 @@ RETURNS TRIGGER
 LANGUAGE plpgsql VOLATILE
 AS $BODY$
 DECLARE
-	_length INTEGER;
+	_user_id INTEGER;
+	_changed BOOLEAN;
 BEGIN
+	_user_id = _api.get_user_id();
+	_changed = FALSE;
+
+	IF OLD.id <> _user_id THEN
+		PERFORM _api.raise_deny();
+	END IF;
+
+	-- username
+	NEW.username = COALESCE(NEW.username, OLD.username);
+	NEW.username := _api.trim(NEW.username);
+	PERFORM _api.validate_text(
+		_text => NEW.username,
+		_column => 'username',
+		_min => 1,
+		_max => 24
+	);
+
+	IF NEW.username IS DISTINCT FROM OLD.username THEN
+		PERFORM TRUE FROM admin.user
+			WHERE username = NEW.username;
+		IF FOUND THEN
+			PERFORM _api.raise_unique('username');
+		END IF;
+		_changed = TRUE;
+	END IF;
+
+	-- password
+	SELECT password
+		INTO OLD.password
+		FROM admin.user
+		WHERE id = OLD.id;
+
+	NEW.password = COALESCE(NEW.password, OLD.password);
+	NEW.password := _api.trim(NEW.password);
+	PERFORM _api.validate_text(
+		_text => NEW.password,
+		_column => 'password',
+		_min => 1,
+		_max => 256
+	);
+
+	IF NEW.password IS DISTINCT FROM OLD.password THEN
+		_changed = TRUE;
+	END IF;
+
+	-- first name
+	NEW.first_name = COALESCE(NEW.first_name, OLD.first_name);
+	NEW.first_name := _api.trim(NEW.first_name);
+	PERFORM _api.validate_text(
+		_text => NEW.first_name,
+		_column => 'first_name',
+		_max => 256
+	);
+
+	IF NEW.first_name IS DISTINCT FROM OLD.first_name THEN
+		_changed = TRUE;
+	END IF;
+
+	-- last name
+	NEW.last_name = COALESCE(NEW.last_name, OLD.last_name);
+	NEW.last_name := _api.trim(NEW.last_name);
+	PERFORM _api.validate_text(
+		_text => NEW.last_name,
+		_column => 'last_name',
+		_max => 256
+	);
+
+	IF NEW.last_name IS DISTINCT FROM OLD.last_name THEN
+		_changed = TRUE;
+	END IF;
+
+	-- middle name
+	NEW.middle_name = COALESCE(NEW.middle_name, OLD.middle_name);
+	NEW.middle_name := _api.trim(NEW.middle_name);
+	PERFORM _api.validate_text(
+		_text => NEW.middle_name,
+		_column => 'middle_name',
+		_max => 256
+	);
+
+	IF NEW.middle_name IS DISTINCT FROM OLD.middle_name THEN
+		_changed = TRUE;
+	END IF;
+
+	-- email
+	NEW.email = COALESCE(NEW.email, OLD.email);
+	NEW.email := _api.trim(NEW.email);
+	PERFORM _api.validate_text(
+		_text => NEW.email,
+		_column => 'email',
+		_max => 256
+	);
+
+	IF NEW.email IS DISTINCT FROM OLD.email THEN
+		_changed = TRUE;
+	END IF;
+
+	-- gender
+	NEW.gender = COALESCE(NEW.gender, OLD.gender);
+	NEW.gender := _api.trim(NEW.gender);
+	PERFORM _api.validate_text(
+		_text => NEW.gender,
+		_column => 'gender',
+		_max => 256
+	);
+
+	IF NEW.gender IS DISTINCT FROM OLD.gender THEN
+		_changed = TRUE;
+	END IF;
+
+	-- birth date
+	NEW.birth_date = COALESCE(NEW.birth_date, OLD.birth_date);
+	IF NEW.birth_date IS DISTINCT FROM OLD.birth_date THEN
+		_changed = TRUE;
+	END IF;
+
+	-- profile bio
+	NEW.profile_bio = COALESCE(NEW.profile_bio, OLD.profile_bio);
+	NEW.profile_bio := _api.trim(NEW.profile_bio);
+	PERFORM _api.validate_text(
+		_text => NEW.profile_bio,
+		_column => 'profile_bio',
+		_max => 2048
+	);
+
+	IF NEW.profile_bio IS DISTINCT FROM OLD.profile_bio THEN
+		_changed = TRUE;
+	END IF;
+
+	IF _changed THEN
+		UPDATE admin.user SET
+			username = NEW.username,
+			password = NEW.password,
+			first_name = NEW.first_name,
+			last_name = NEW.last_name,
+			middle_name = NEW.middle_name,
+			email = NEW.email,
+			gender = NEW.gender,
+			birth_date = NEW.birth_date,
+			profile_bio = NEW.profile_bio
+			WHERE id = OLD.id;
+	END IF;
+
 	RETURN NEW;
 END
 $BODY$;
 
 GRANT EXECUTE ON FUNCTION _api.user_update()
 	TO rest_user;
-GRANT DELETE ON TABLE api.user
+GRANT UPDATE ON TABLE api.user
+	TO rest_user;
+GRANT UPDATE ON TABLE admin.user
 	TO rest_user;
 
 CREATE TRIGGER api_user_update_trgr
diff --git a/db/rest/util/_api_trim.sql b/db/rest/util/_api_trim.sql
new file mode 100644
index 0000000..c972282
--- /dev/null
+++ b/db/rest/util/_api_trim.sql
@@ -0,0 +1,25 @@
+CREATE FUNCTION _api.trim(
+	_text TEXT
+)
+RETURNS TEXT
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_new TEXT;
+BEGIN
+
+	IF _text IS NULL THEN
+		RETURN NULL;
+	END IF;
+
+	_new = _text;
+	_new = TRIM(_new);
+	_new = REGEXP_REPLACE(_new, '^(?: |\r|\n)*', '');
+	_new = REGEXP_REPLACE(_new, '(?: |\r|\n)*$', '');
+
+	RETURN _new;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.trim(TEXT)
+	TO rest_anon, rest_user;
diff --git a/db/rest/util/_api_validate_text.sql b/db/rest/util/_api_validate_text.sql
index e4a6a7b..ff3a227 100644
--- a/db/rest/util/_api_validate_text.sql
+++ b/db/rest/util/_api_validate_text.sql
@@ -33,6 +33,7 @@ BEGIN
 			_detail => _column,
 			_hint => _min || ''
 		);
+		RETURN FALSE;
 	END IF;
 
 	IF _max IS NOT NULL AND _length > _max THEN
@@ -41,6 +42,7 @@ BEGIN
 			_detail => _column,
 			_hint => _max || ''
 		);
+		RETURN FALSE;
 	END IF;
 
 	RETURN TRUE;