Remote StatusNet Groups

StatusNet Logo

As some of you may know, I've recently moved from identi.ca to my own StatusNet instance at micro.jpope.org. While it has mostly been painless, there have been a few issues. One issue that I've had was concerning remote groups (i.e. groups located on identi.ca or elsewhere). I'd subscribe to the group and end up not being able to post to those groups. This particular issue seems to not only be limited to myself either (see issue 3507 and possibly related issue 3092). The fix for me was stated in issue 3092. If it turns out that I can't post to a group, I'll add an entry to the group_alias table for that group. Oddly enough, it only affects some groups and not all. At the moment I can't tell why this is but, at least there is a workaround, even if it's slightly manual. Here's a few queries that I am now using to help 'fix' the problem on my instance.

EDIT: I have updated the queries slightly.

A few quick notes:

  • First things first, I'm using phpMyAdmin for ease of use. You should be able to create the views from the command line but, the final update query I have saved as a bookmarked query in phpMyAdmin so I can easily come back to it.

  • The end result of these queries will end up creating a few views and writing to the group_alias table. Make sure to backup your statusnet database beforehand. In case something breaks, you'll easily be able to fix it.

  • In order to keep the queries simple, they are only looking for the first group within a notice. If you have notices posted (at least attempted to be posted) to multiple groups, the second, third, etc... are going to be ignored. The queries are also currently missing the group tags at the very end of notices. If you have issues with posting to certain groups, make sure you've got at least one notice where the group in question's tag is in the middle of the notice.

  • The three views that will be created, will need to be edited with your profile id, I'm on a single user instance therefore my id number is '1'. If your profile id differs, look for the line containing "`profile_id` = 1" and change as necessary.

  • I'm going to attempt to explain things as though you've got at least some experience with phpMyAdmin. If anything needs to be explained in further detail, please let me know.

  • These queries, and the end result, only check for the name of the group as it is entered in the database. If the group has other aliases that you'd rather use instead of the main group name, you'll still need to manually add them in. There's not much that can be done using just mysql to find the other aliases for remote groups.

vwGroupSuccess

This first query will check for successful group postings. It will look in the notice table for the bang '!' and look to see if it ended up being rendered with HTML coding. The query also excludes any reshared notices.

SELECT DISTINCT LOWER(SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1)) 'successful' 
FROM `notice` 
WHERE (`content` LIKE '%!%' OR `content` LIKE '!%') AND `rendered` LIKE CONCAT('%','nickname group\">',SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1),'%') AND `profile_id` = 1 AND ISNULL( `repeat_of` ) AND LOWER(SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1)) <> ''

To create the view first see bullet point two above and then select your statusnet database and head for the SQL tab in the main screen. Paste this in, edit the 'profile_id' if needed and click 'Go':

CREATE OR REPLACE VIEW vwGroupSuccess AS 
SELECT DISTINCT LOWER(SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1)) 'successful' 
FROM `notice` 
WHERE (`content` LIKE '%!%' OR `content` LIKE '!%') AND `rendered` LIKE CONCAT('%','nickname group\">',SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1),'%') AND `profile_id` = 1 AND ISNULL( `repeat_of` ) AND LOWER(SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1)) <> ''

You should see a yellow box on top saying success.

vwGroupFailed

This query does the exact opposite of the first. It still looks in the notice table for the bang but, it excludes those that get rendered with HTML while still excluding reshared notices.

SELECT DISTINCT LOWER(SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1)) 'unsuccessful' 
FROM `notice` 
WHERE (`content` LIKE '%!%' OR `content` LIKE '!%') AND `rendered` NOT LIKE CONCAT('%','nickname group\">',SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1),'%') AND `profile_id` = 1 AND ISNULL( `repeat_of` ) AND LOWER(SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1)) <> ''

Same as above, run this to create the view. (don't forget to change 'profile_id'...):

CREATE OR REPLACE VIEW vwGroupFailed AS 
SELECT DISTINCT LOWER(SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1)) 'unsuccessful' 
FROM `notice` 
WHERE (`content` LIKE '%!%' OR `content` LIKE '!%') AND `rendered` NOT LIKE CONCAT('%','nickname group\">',SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1),'%') AND `profile_id` = 1 AND ISNULL( `repeat_of` ) AND LOWER(SUBSTR(`content`,LOCATE('!',`content`)+1,LOCATE(' ',CONCAT(SUBSTR(`content`,LOCATE('!',`content`)+1),' '))-1)) <> ''

Should be success again..

vwGroupAccuracy

This is the final view to be created. This one will grab all of the groups you are subscribed to and compare them to the previous views we just created. It also checks to see if there is an alias created for the groups. The end result of this view is what I used for my page here listing my groups: Link. This view is mostly just for reference.

SELECT `user_group`.`nickname` 'groupnick' , `group_member`.`group_id` , `user_group`.`uri` 'group_url' , CASE WHEN ISNULL(`linked`.`successful`) THEN '' ELSE 'X' END 'successful_group' , CASE WHEN ISNULL(`failed`.`unsuccessful`) THEN '' ELSE 'X' END 'failed_group' , CASE WHEN ISNULL(`group_alias`.`group_id`) THEN '' ELSE 'X' END 'has_alias' , `user_group`.`created` 'group_db_entry' , `group_member`.`created` 'joined_group'
FROM `group_member` INNER JOIN `user_group` ON `group_member`.`group_id` = `user_group`.`id` LEFT JOIN `vwGroupSuccess` `linked` ON LOWER(`user_group`.`nickname`) = `linked`.`successful` LEFT JOIN `vwGroupFailed` `failed` ON LOWER(`user_group`.`nickname`) = `failed`.`unsuccessful` LEFT JOIN `group_alias` ON `group_member`.`group_id` = `group_alias`.`group_id`
WHERE `group_member`.`profile_id` = 1
ORDER BY `groupnick`

And once again, to create the view, run this. (something something profile_id something):

CREATE OR REPLACE VIEW vwGroupAccuracy AS
SELECT `user_group`.`nickname` 'groupnick' , `group_member`.`group_id` , `user_group`.`uri` 'group_url' , CASE WHEN ISNULL(`linked`.`successful`) THEN '' ELSE 'X' END 'successful_group' , CASE WHEN ISNULL(`failed`.`unsuccessful`) THEN '' ELSE 'X' END 'failed_group' , CASE WHEN ISNULL(`group_alias`.`group_id`) THEN '' ELSE 'X' END 'has_alias' , `user_group`.`created` 'group_db_entry' , `group_member`.`created` 'joined_group' 
FROM `group_member` INNER JOIN `user_group` ON `group_member`.`group_id` = `user_group`.`id` LEFT JOIN `vwGroupSuccess` `linked` ON LOWER(`user_group`.`nickname`) = `linked`.`successful` LEFT JOIN `vwGroupFailed` `failed` ON LOWER(`user_group`.`nickname`) = `failed`.`unsuccessful` LEFT JOIN `group_alias` ON `group_member`.`group_id` = `group_alias`.`group_id` 
WHERE `group_member`.`profile_id` = 1 
ORDER BY `groupnick`

Success?

updqryAddGroupAlias

This last query just simplifies the inserting of multiple entries of the group_alias table. If you only have a couple groups that are failing to link properly, you may want to just insert them one at a time. Which you can easily do by selecting the group_alias table on the lefthand section of phpMyAdmin and selecting the 'Insert' tab in the main screen. If you have a bunch of failing groups, as I did initially (my first insert was for fourteen groups), you can paste this query into the SQL tab. I also used a query bookmark so I can go back and easily find and run this query in the future.

INSERT INTO `group_alias`
SELECT `unsuccessful` 'alias' , `id` 'group_id' , now() 'modified'
FROM `vwGroupFailed` INNER JOIN `user_group` ON `unsuccessful` = `nickname` LEFT JOIN `group_alias` ON `unsuccessful` = `alias`
WHERE ISNULL(`group_alias`.`alias`)

Underneath the query box where you pasted in the query, there should be a text box with the heading "Bookmark this SQL query:". Type in a name (I used updqryAddGroupAlias) and click 'Go'. This will run and save the query. You should see a message at the top stating how many rows were inserted. You should also be able to find the query in the "Bookmarked SQL query" section at the bottom of the page. You can also check the group_alias table and see what all groups now have an entry in this table.

And now, that's it. So long as the group in question had an entry in the table, you should be able to post to the group. Try some test notices and see. A post such as

Hello !<groupname>. Thanks for letting me in. I think I'll like it here.

will probably win you over a few fans (or a !spamreport or two). Either way, the notice should now go to the intended group.

If any of this killed your statusnet, say a few curse words, restore your database (you did back it up first, right?), call me a few names that you wouldn't want your mother to hear you say and take a deep breath, your db backup should revert anything that was just done. I have successfully followed these steps on two seperate instances without issue so I do have faith that nothing will go wrong.

Let me know if you do have issues and I'll do my best to help you out. Also, if you are running your own instance, please join the !feds group. There are plenty of us in the group that may be able to help you out with any issues you have.

#VivaLaFederation


Comments:

Dave Lee

Frustratingly, I cannot CREATE VIEWs on my MySQL database, however from previous experience, if you’re having trouble identifying tags at the end of a notice because it’s looking for a space at the end, why not CONCAT a space at the end of the notice field – i.e. replace appropriate instances of notice with CONCAT(`notice`, ' ').

It seems like it might work.

Also, instances where you have punctuation immediately after the tag, i.e.:

“Earlier, I was posting in !feds, and someone said…”

… it doesn’t pick those up.

For what it’s worth, I’ve just run this query on mine:

INSERT INTO group_alias(
    group_id,
    alias
 )
SELECT DISTINCTROW id, nickname
FROM user_group ug
INNER JOIN group_member gm ON ug.id = gm.group_id
WHERE NOT EXISTS (
SELECT 1
FROM group_alias ga
WHERE ug.id = ga.group_id
AND ug.nickname = ga.alias
)
AND gm.profile_id =1

… which just unconditionally adds all group aliases that haven’t already been defined. If you run it twice, the second time will do nothing unless you’ve subscribed to new groups since. I don’t know if there will be any side-effect of these groups being in group_alias if they already work? :o)

Disclaimer: If your statusnet breaks, etc, etc… it’s not my fault.

2011-11-13 03:51:58

jpope

I initially did try appending a space at the end but, for some reason it still wouldn't grab those at the end. So I dropped it off for the time being with the intent of going back and updating the queries. That said, I was able to get it working this morning. :/ So, I'll update the post once I have the fresh queries worked out.

Also, I don't think it'll hurt having the already working groups aliases in the table. I personally didn't want to just so I can go back a look at the individual groups and (maybe) find something as to why some work and some don't. Why did I need an alias for the !tea group but, not the !coffee group?

Thanks for the reply. :D

2011-11-13 08:19:05


Have a response to this post? Please use this link.