login and register, liking on homepage

4 files changed, 27 insertions, 9 deletions

diff --git a/db/rest/comment/api_comment.sql b/src/db/rest/comment/api_comment.sql
index e50ca2f..c8a0e19 100644
--- a/db/rest/comment/api_comment.sql
+++ b/src/db/rest/comment/api_comment.sql
@@ -4,10 +4,24 @@ CREATE VIEW api.comment AS
 		c.user_id,
 		c.post_id,
 		c.content,
-		c.date
+		c.created,
+		c.modified
 	FROM
 		admin.comment c
-	ORDER BY id ASC;
+	LEFT JOIN
+		admin.post p
+	ON
+		p.id = c.post_id
+	LEFT JOIN
+		admin.user u
+	ON
+		u.id = c.user_id
+	WHERE
+		c.deleted <> TRUE AND
+		p.deleted <> TRUE AND
+		u.deleted <> TRUE
+	ORDER BY
+		id ASC;
 
 GRANT SELECT ON TABLE api.comment
 	TO rest_anon, rest_user;
diff --git a/db/rest/comment/api_comment_delete.sql b/src/db/rest/comment/api_comment_delete.sql
index d7db8a4..262b2ed 100644
--- a/db/rest/comment/api_comment_delete.sql
+++ b/src/db/rest/comment/api_comment_delete.sql
@@ -11,9 +11,10 @@ BEGIN
 		PERFORM _api.raise_deny();
 	END IF;
 
-	DELETE FROM admin.comment
-	WHERE user_id = _user_id
-	AND id = OLD.id;
+	UPDATE admin.comment SET
+		deleted = TRUE,
+		modified = clock_timestamp()
+		WHERE id = OLD.id;
 END
 $BODY$;
 
@@ -21,7 +22,7 @@ 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
+GRANT UPDATE ON TABLE admin.comment
 	TO rest_user;
 
 CREATE TRIGGER api_comment_delete_trgr
diff --git a/db/rest/comment/api_comment_insert.sql b/src/db/rest/comment/api_comment_insert.sql
index 878e194..990beef 100644
--- a/db/rest/comment/api_comment_insert.sql
+++ b/src/db/rest/comment/api_comment_insert.sql
@@ -34,7 +34,9 @@ BEGIN
 		_user_id,
 		NEW.post_id,
 		NEW.content
-	);
+	)
+	RETURNING id
+	INTO NEW.id;
 
 	RETURN NEW;
 END
diff --git a/db/rest/comment/api_comment_update.sql b/src/db/rest/comment/api_comment_update.sql
index d6b4aca..b8fc16d 100644
--- a/db/rest/comment/api_comment_update.sql
+++ b/src/db/rest/comment/api_comment_update.sql
@@ -27,8 +27,9 @@ BEGIN
 	END IF;
 
 	IF _changed THEN
-		UPDATE admin.comment
-			SET content = NEW.content
+		UPDATE admin.comment SET
+			content = NEW.content,
+			modified = clock_timestamp()
 			WHERE id = OLD.id;
 	END IF;