XF 1.3 Importing User Groups and User Group Members

[Floyd R Turbo]

I am working on a DNN > XF conversion. I have managed to get all of the forum content and member information ported over, but not user groups.

I have the MSSQL database and was able to create a master list of what users are part of which usergroup in that database, so I will be able to re-create the xf_user_group_relation table manually and then import it into mySQL via phpMyAdmin. I have noticed that in the xf_user table, the column secondary_group_ids seems to correlate to the list in the relation table.

Will I need to synchronize both of these or can I just import the relation table and then run a cron or rebuilt an index to do that?

Is there another way to do this instead of via SQL?

Thanks in advance.

 

[Floyd R Turbo]

Any XF database experts out there?

 

[Floyd R Turbo]

Still looking for an answer to this one, anyone???

I haven't done any trial & error yet...hoping to hear from someone who has done this already possibly, or at least someone who knows how xf_user/secondary_group_ids relates or interacts with xf_user_group_relation

@Kier ?
@Mike ?

 

[Floyd R Turbo]

Well I set up the user groups and built the xf_user_group_relation table based on the source database and imported it. I set Registered as primary group for everyone except unconfirmed accounts, etc.

The result is no change in user group assignments. So it appears that the secondary groups column in xf_user is the one that matters. So do I need to build both so that they match?

In MySQL the text in the secondary group field looks weird, like is someone is part of groups 3 & 4 it shows 332c34, but when I export it, it comes across (in excel) as 3,4. If I set things up in excel I think I would have to save it as text delimited with a different delimiter, and then import that appropriately.

I use DigDB with Excel so I can easily rebuild the secondary group column however I need to. I really just need confirmation as to whether this is necessary, or if I am missing something / is there a simpler way.

 

[Floyd R Turbo]

Updating my question/issue...for anyone that might be able to answer it. 4 days and no response?

The table xf_user appears to have columns that are "mirrors" of the xf_user_group_relation table, specifically, user_group_id and secondary_group_ids. It appears that user_group_id matches up with whatever is set as the primary group in the relation table, and secondary_group_ids lists all additional (non-primary) groups that each user is a member of.

I'm guessing that there is a good reason why the information is duplicated in two places.

So my question is, do I have to synchronize these columns between the two tables, or is there a way to cause the columns in xf_user to be "rebuilt" based on the values in xf_user_group_relation via ACP or otherwise?

Then, if this is the case, the way the values of secondary_group_ids is displayed in phpmyadmin is a bit odd. For example if I have a user that has 2 (registered) and primary and 3 and 4 as secondary, this is displayed as 332c34. When I export this table and open in excel, it shows it as 3,4.

In another XF install where I have many more user groups, this field for one users is 332c342c352c392c31302c31312c3132 which in excel shows up as 3,4,5,9,10,11,12, which maps out if you replace the 2c3 with a comma and eliminate the other 3's....but I don't know if this will happen automatically when I import a CSV excel file or not (CSV puts quotes around "3,4,5,9,10,11,12"...)

Any insight on how to accomplish this other than I have outlined above (using DigDB to synch and re-create the secondary ID column and importing that via phpmyadmin) would be appreciated.

 

[Floyd R Turbo]

Interestingly enough, I went to check the xf_user_group_relation table this morning and notice that it has reverted back to what it was before I replaced it last night. So it appears that this table it built based on the values set in the 2 columns in xf_user.

Now, if I knew how to cause this build process to occur (cron?) then I would only need to build the columns in xf_user....

 

[Floyd R Turbo]

As it turns out, rebuilding the user cache replaced the xf_user_group_relation table with the primary and secondary group IDs, presumably for use elsewhere.

So I finally figured this out. And as a result, I am super-proud of my ability to avoid having to write some complex SQL script to do it.

Essentially, what I did was take the formatted table that matched the structure of the relation table, and used an Excel VB code snippet I pulled off the web to transpose all secondary roles was associated with one user ID into the same row with said ID.

Then I used DigDB to combine the columns and put commas between them all, then weed out the extra commas so that it matched the format.

I then pasted these columns into the exported xf_user file and replaced the primary and secondary columns.

Here's where it got tricky. I couldn't figure out how to force excel to put quotation marks around every single cell in the saved CSV file. A little googling told me you could do this with OpenOffice and possibly Access, but I found it easier to use notepad++. So in excel I replaced the comma in the secondary IDs column with a unique word (in this case, comma) and then opened that up in n++ and replaced all commas with "," and appended and prepended each line with " and then replaced all instances of the word "comma" with an actual comma.

Import as CSV in phpmyadmin, rebuild user cache, success!