Community type 2 and 3 + update community type op

feature set

This feature enables the new community types as defined in the original doc:

• type 1: Topic: anyone can post or comment
• type 2: Journal: guests can comment but not post. only members can post.
• type 3: Council: only members can post or comment

test cases

for all tests, the account ismember will be a member of all the communities and notmember won't be.

• create a community type 1 hive-111119
• create a community type 2 hive-211119
• create a community type 3 hive-311119
• Community type 1 tests
• • post in community type 1 from member => not muted
• • post in community type 1 from non member => not muted
• • comment in community type 1 from member => not muted
• • comment in community type 1 from non member => not muted
• • comment in community type 1 from member at depth 2 => not muted
• Community type 2 tests
• • post in community type 2 from member => not muted
• • post in community type 2 from non member => muted
• • comment in community type 2 from member => not muted
• • comment in community type 2 from non member => not muted
• • comment in community type 2 from member at depth 2 => not muted
• Community type 3 tests
• • post in community type 3 from member => not muted
• • post in community type 3 from non member => muted
• • comment in community type 3 from member => not muted
• • comment in community type 3 from non member => muted
• • comment in community type 3 from member at depth 2 => not muted
• Change community type 1 to community type 3
• • change ismember to a mod (to validate that rules apply to roles above member and not just to member)
• • post in community type 3 from member => not muted
• • post in community type 3 from non member => muted
• • comment in community type 3 from member => not muted
• • comment in community type 3 from non member => muted

hive/db/sql_scripts/hive_post_operations.sql

@@ -25,6 +25,66 @@ BEGIN
 END
 $function$;
 
+
+DROP FUNCTION IF EXISTS hivemind_app.process_community_post;
+CREATE OR REPLACE FUNCTION hivemind_app.process_community_post(_block_num hivemind_app.hive_posts.block_num%TYPE, _community_support_start_block hivemind_app.hive_posts.block_num%TYPE, _parent_permlink hivemind_app.hive_permlink_data.permlink%TYPE, _author_id hivemind_app.hive_posts.author_id%TYPE, is_comment bool)
+RETURNS TABLE(is_muted bool, community_id hivemind_app.hive_posts.community_id%TYPE)
+LANGUAGE plpgsql
+as
+    $$
+declare
+        __community_type_id SMALLINT;
+        __role_id SMALLINT;
+        __member_role CONSTANT SMALLINT := 2;
+        __community_type_topic CONSTANT SMALLINT := 1;
+        __community_type_journal CONSTANT SMALLINT := 2;
+        __community_type_council CONSTANT SMALLINT := 3;
+        __is_muted bool := TRUE;
+        __is_parent_muted bool := FALSE;
+        __community_id hivemind_app.hive_posts.community_id%TYPE;
+BEGIN
+        IF _block_num < _community_support_start_block THEN
+            __is_muted := FALSE;
+            __community_id := NULL;
+        ELSE
+            IF is_comment = TRUE THEN
+                SELECT hc.type_id, hc.id, hivemind_app.hive_posts.is_muted INTO __community_type_id, __community_id, __is_parent_muted
+                FROM hivemind_app.hive_permlink_data
+                    JOIN hivemind_app.hive_posts ON hivemind_app.hive_permlink_data.id = hivemind_app.hive_posts.permlink_id
+                    JOIN hivemind_app.hive_communities hc ON hivemind_app.hive_posts.community_id = hc.id
+                WHERE hivemind_app.hive_permlink_data.permlink = _parent_permlink;
+            ELSE
+                SELECT type_id, id INTO __community_type_id, __community_id from hivemind_app.hive_communities where name = _parent_permlink;
+            END IF;
+
+            -- __is_muted can be TRUE here if it's a comment and its parent is muted
+            IF __is_parent_muted = TRUE THEN
+                __is_muted := TRUE;
+            ELSEIF __community_id IS NOT NULL THEN
+                IF __community_type_id = __community_type_topic THEN
+                    __is_muted := FALSE;
+                ELSE
+                    IF __community_type_id = __community_type_journal AND is_comment = TRUE THEN
+                        __is_muted := FALSE;
+                    ELSE
+                        select role_id into __role_id from hivemind_app.hive_roles where hivemind_app.hive_roles.community_id = __community_id AND account_id = _author_id;
+                        IF __community_type_id = __community_type_journal AND is_comment = FALSE AND __role_id IS NOT NULL AND __role_id >= __member_role THEN
+                            __is_muted := FALSE;
+                        ELSIF __community_type_id = __community_type_council AND __role_id IS NOT NULL AND __role_id >= __member_role THEN
+                            __is_muted := FALSE;
+                        END IF;
+                    END IF;
+                END IF;
+            ELSE
+                __is_muted := FALSE;
+            END IF;
+        END IF;
+
+        RETURN QUERY SELECT __is_muted, __community_id;
+    END;
+$$;
+
+
 DROP FUNCTION IF EXISTS hivemind_app.process_hive_post_operation;
 ;
 CREATE OR REPLACE FUNCTION hivemind_app.process_hive_post_operation(
@@ -58,17 +118,14 @@ if _parent_author != '' THEN
     root_id, is_muted, is_valid,
     author_id, permlink_id, created_at, updated_at, sc_hot, sc_trend, active, payout_at, cashout_time, counter_deleted, block_num, block_num_created)
   SELECT php.id AS parent_id, php.depth + 1 AS depth,
-      (CASE
-          WHEN _block_num > _community_support_start_block THEN
-            COALESCE(php.community_id, (select hc.id from hivemind_app.hive_communities hc where hc.name = _parent_permlink))
-          ELSE NULL
-      END) AS community_id,
+      pcp.community_id AS community_id,
       COALESCE(php.category_id, (select hcg.id from hivemind_app.hive_category_data hcg where hcg.category = _parent_permlink)) AS category_id,
       (CASE(php.root_id)
           WHEN 0 THEN php.id
           ELSE php.root_id
         END) AS root_id,
-      php.is_muted AS is_muted, php.is_valid AS is_valid,
+      pcp.is_muted AS is_muted,
+      php.is_valid AS is_valid,
       ha.id AS author_id, hpd.id AS permlink_id, _date AS created_at,
       _date AS updated_at,
       hivemind_app.calculate_time_part_of_hot(_date) AS sc_hot,
@@ -77,6 +134,7 @@ if _parent_author != '' THEN
         _block_num as block_num, _block_num as block_num_created
   FROM hivemind_app.hive_accounts ha,
         hivemind_app.hive_permlink_data hpd,
+        hivemind_app.process_community_post(_block_num, _community_support_start_block, _parent_permlink, ha.id, TRUE) pcp,
         hivemind_app.hive_posts php
   INNER JOIN hivemind_app.hive_accounts pha ON pha.id = php.author_id
   INNER JOIN hivemind_app.hive_permlink_data phpd ON phpd.id = php.permlink_id
@@ -106,14 +164,10 @@ ELSE
     active, payout_at, cashout_time, counter_deleted, block_num, block_num_created,
     tags_ids)
   SELECT 0 AS parent_id, 0 AS depth,
-      (CASE
-        WHEN _block_num > _community_support_start_block THEN
-          (select hc.id FROM hivemind_app.hive_communities hc WHERE hc.name = _parent_permlink)
-        ELSE NULL
-      END) AS community_id,
+      pcp.community_id AS community_id,
       (SELECT hcg.id FROM hivemind_app.hive_category_data hcg WHERE hcg.category = _parent_permlink) AS category_id,
       0 as root_id, -- will use id as root one if no parent
-      false AS is_muted, true AS is_valid,
+      pcp.is_muted AS is_muted, true AS is_valid,
       ha.id AS author_id, hpd.id AS permlink_id, _date AS created_at,
       _date AS updated_at,
       hivemind_app.calculate_time_part_of_hot(_date) AS sc_hot,
@@ -125,7 +179,8 @@ ELSE
           FROM hivemind_app.prepare_tags( ARRAY_APPEND(_metadata_tags, _parent_permlink ) )
         ) as tags_ids
   FROM hivemind_app.hive_accounts ha,
-        hivemind_app.hive_permlink_data hpd
+       hivemind_app.process_community_post(_block_num, _community_support_start_block, _parent_permlink, ha.id, FALSE) pcp,
+       hivemind_app.hive_permlink_data hpd
   WHERE ha.name = _author and hpd.permlink = _permlink
 
   ON CONFLICT ON CONSTRAINT hive_posts_ux1 DO UPDATE SET