IBM Connections 4.0: Follow a community by default

by Michael Urspringer - 14.03.2013

*** MANIPULATING DATA DIRECTLY WITHIN THE DATABASE MAY LEAD TO DATA LOSS AND DATA CORRUPTION. THIS IS NOT SOMETHING WHICH IS OFFICIALLY SUPPRTED SO USE IT ON YOUR OWN RISK  AND DO NOT BLAME IBM FOR ANY DAMAGE ***

We just had the customer request that every user who will be added to a community should automatically follow the community as well. As this is by default only done for users who created the community or are added as owner to a community we needed to find a solution for normal users as well.

If a user follows a community there is a row in the  table SNCOMM.FOLLOWING which contains the COMMUNITY_UUID (from SNCOMM.COMMUNITY) for the commnity beeing followed and the MEMBER_UUID (from SNCOMM.MEMBER).

So you can define a database trigger which automatically adds a row to SNCOMM.FOLLOWING as soon as a new member row will be created in SNCOMM.MEMBER. The SQL code for this trigger will be:

CREATE TRIGGER SNCOMM.auto_follow_community
AFTER INSERT ON SNCOMM.MEMBER
REFERENCING NEW AS NEW_MEMBER
FOR EACH ROW 
WHEN ( ROLE = 0 AND ( NOT EXISTS (SELECT * FROM SNCOMM.FOLLOWING WHERE NEW_MEMBER.MEMBER_UUID = SNCOMM.FOLLOWING.MEMBER_UUID AND NEW_MEMBER.COMMUNITY_UUID = SNCOMM.FOLLOWING.COMMUNITY_UUID)))
INSERT INTO SNCOMM.FOLLOWING (COMMUNITY_UUID, MEMBER_UUID, CREATED_BY, CREATED) 
VALUES (NEW_MEMBER.COMMUNITY_UUID, NEW_MEMBER.MEMBER_UUID, NEW_MEMBER.MEMBER_UUID, NEW_MEMBER.CREATED)

The trigger fires only for “ROLE=0” which means only normal members and not owners (which would be “ROLE=1”). This is because Connections by itself does already add such a row to the table SNCOMM.FOLLOWING or all owners.

This trigger was tested with DB 2 but should be working as well with the other supported databases.

Caution: Be sure to drop the DB trigger before upgrading or changing Db schemas. You can re-apply it afterwards.

*** MANIPULATING DATA DIRECTLY WITHIN THE DATABASE MAY LEAD TO DATA LOSS AND DATA CORRUPTION. THIS IS NOT SOMETHING WHICH IS OFFICIALLY SUPPRTED SO USE IT ON YOUR OWN RISK  AND DO NOT BLAME IBM FOR ANY DAMAGE ***

 

Tagged with:  

10 Responses to IBM Connections 4.0: Follow a community by default

  1. Klaus says:

    Very cool hack, thx for sharing!

  2. Manuel R says:

    Would you know how to add everyone to a community automatically?

  3. I am quite sure that you can do that also with a SQL script. I would suggest to get in contact with IBM Software Services to help you here.

  4. bluebiker4me says:

    This offers many other possibilities… thx.

  5. Again to make sure: Manipulating the data structure on the database level is DANGEROUS as the structure is not documented and may change at any time with no warranty. Use this really at your own risk and do not blame IBM for any data corruption you will get if you do this! That was the reason why this entry was private before (and maybe it would have been better not to set it to public ;-))

  6. Benjamin says:

    Saved my day – thank you very much for sharing this!

    • Benjamin says:

      I just use the following Statement to add single followers:
      INSERT INTO SNCOMM.FOLLOWING (COMMUNITY_UUID, MEMBER_UUID, CREATED_BY, CREATED)
      VALUES (‘2b6038ff-a130-481a-b26a-456240c04a8a’, ‘6bc243a0-4abc-4083-b584-a9b50a62f47f’, ‘6bc243a0-4abc-4083-b584-a9b50a62f47f’, ‘2014-02-07 11:00:20.865’)

  7. Anatoly Kireev says:

    Hi, here is an update for Connections 5.0 and Oracle dialect.
    CREATE OR REPLACE TRIGGER AUTO_FOLLOW_COMMUNITY
    AFTER INSERT ON SNCOMM.MEMBER
    REFERENCING NEW AS NEW_MEMBER
    FOR EACH ROW
    DECLARE
    mem_cnt integer;
    BEGIN
    SELECT COUNT(*) into mem_cnt FROM SNCOMM.FOLLOWING WHERE :NEW_MEMBER.MEMBER_UUID = SNCOMM.FOLLOWING.MEMBER_UUID AND :NEW_MEMBER.COMMUNITY_UUID = SNCOMM.FOLLOWING.COMMUNITY_UUID;
    IF ( :NEW_MEMBER.ROLE = 0 AND (mem_cnt=0 )) then
    INSERT INTO SNCOMM.FOLLOWING (COMMUNITY_UUID, MEMBER_UUID, ORG_ID, CREATED_BY, CREATED)
    VALUES (:NEW_MEMBER.COMMUNITY_UUID, :NEW_MEMBER.MEMBER_UUID, ‘a’, :NEW_MEMBER.MEMBER_UUID, :NEW_MEMBER.CREATED);
    END IF;
    END;

  8. Thanks a lot, Anatoly!

Leave a Reply

© 2000-2015 Michael Urspringer