News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Share user database for 2 (or more) different forums.

Started by Spaceman-Spiff, September 03, 2004, 09:14:44 PM

Previous topic - Next topic

Hj Ahmad Rasyid Hj Ismail

Quote from: GrassrootsPA on March 01, 2010, 01:03:59 PM
Hey Abu,

I tried that but have been unable to get it to work. (There seems to be some bug in it. Plus isn't not exactly what I'm looking for)

In general, I'm blown away by how wonderful SMF is, but the one thing that I do not like is how difficult it seems to be to share login info for 2 forums installed on the same database (with different table prefixes, etc).

From a newbie's perspective, I assumed it would be pretty easy to have a universal login if 2 installs shared the same db, but I am finding out I thought wrong!

(In a perfect world I want to have 2 forums on separate domains with the same login)
Are you sure? It seems easy to me. Is the multiple forum mod that you are referring too? http://custom.simplemachines.org/mods/index.php?mod=2137

Well, I too think that sharing database should be made easy. However, it is not (not yet?) with smf. May be there will be in the near future. But I do think others (here in smf) can guide you there, if you really want to learn for it.

GrassrootsPA

Thanks Abu: Yes, that's the mod I have been having an issue with (duplicate characters in the location address ending get created)

But even if I could get that installed it is not exactly what I'm trying to do.

I want to have two separate installs on two different domains sharing the same database.

Example:
DomainA.com (forum in that root)
DomainB.com (forum in that root)

Someone signs up on DomainA.com's forum? BLAM, they can login to DomainB.com's forum if they want.

Hope this helps explain what I would ultimately like to do!
I'm Feeling This!

Hj Ahmad Rasyid Hj Ismail

Quote from: GrassrootsPA on March 01, 2010, 06:28:26 PM
Thanks Abu: Yes, that's the mod I have been having an issue with (duplicate characters in the location address ending get created)

But even if I could get that installed it is not exactly what I'm trying to do.

I want to have two separate installs on two different domains sharing the same database.

Example:
DomainA.com (forum in that root)
DomainB.com (forum in that root)

Someone signs up on DomainA.com's forum? BLAM, they can login to DomainB.com's forum if they want.

Hope this helps explain what I would ultimately like to do!
Yes, this thread is the right mod for you. But you have to make manual modifications. It is quite easy to do for 1.1.x but not so for 2.0 RCx. You can try but there are a lot of files that need changes.

GrassrootsPA

Wow, this is great news. Maybe when RC3 comes out and some bugs are worked out it will be able to be done  :D
I'm Feeling This!

Hj Ahmad Rasyid Hj Ismail

As advised in the earlier posts, you might need a good software to do all the necessary changes. Good luck!

mkmeister

#265
OK, here's a step by step of exactly what I did. There were ZERO code changes, and only used MySQL to pull it off as noted in a previous post.

As a note, I'm using SMF 1.1.11 and MySQL 5.0.x. If SMF 2.x uses a similar table structure (haven't looked at it personally yet), I'd imagine it'd work just as well and still no code changes. They don't even need to be on the same database, but it's easier, and my instructions will assume they're using the same database with just different prefixes for the two (or more) installations of SMF. It does not matter what domain they are hosted on as long as they use the same MySQL server.

This will share user logins and PMs.

Assume you have two installations. One is an already established forum installation, and it uses the prefix "forumA_". The other is a new forum that you want to share forumA's logins and PMs. It has the prefix "forumB_". They're both in the database called "smf".

Log onto MySQL. There are a variety of tools for this but I just use commandline.
Now, once in MySQL, issue the following.
USE smf;

First, just move forumB's tables you want shared out of the way.
ALTER TABLE forumB_members RENAME TO forumB_members_old;
ALTER TABLE forumB_personal_messages RENAME TO forumB_personal_messages_old;
ALTER TABLE forumB_pm_recipients RENAME TO forumB_pm_recipients_old;


WARNING: Your forumA install will NOT be accessible once you do the following step until all steps are complete. It'd be wise to make a backup first too.
Next, rename forumA's tables to use a "shared_" prefix.
ALTER TABLE forumA_members RENAME TO shared_members;
ALTER TABLE forumA_personal_messages RENAME TO shared_personal_messages;
ALTER TABLE forumA_pm_recipients RENAME TO shared_pm_recipients;


Finally, create views of the shared tables for both forumA and forumB so they see the exact same tables and the info is the same for both.
First, forumA:
CREATE VIEW forumA_members AS SELECT * FROM shared_members;
CREATE VIEW forumA_personal_messages AS SELECT * FROM shared_personal_messages;
CREATE VIEW forumA_pm_recipients AS SELECT * FROM shared_pm_recipients;


And next, forumB:
CREATE VIEW forumB_members AS SELECT * FROM shared_members;
CREATE VIEW forumB_personal_messages AS SELECT * FROM shared_personal_messages;
CREATE VIEW forumB_pm_recipients AS SELECT * FROM shared_pm_recipients;


Presto! Finished! Both forumA and forumB share users and PMs!

If anybody can spot corrections or something wrong with the above instructions, let me know and I'll correct it.

EDIT: Corrected a couple of typos.

GrassrootsPA

#266
mkmeister,

Three 'dumb' questions from a SMF newbie:  ;)

1) Are you using phpMyAdmin for the MYSQL edits?

2) One can do this sharing for 3 or more databases as well (not just two), right?

3) Would it be possible for someone to create a mod that does this sharing? (I personally am uncomfortable messing around with MYSQL tables and would prefer a mod, if possible)
I'm Feeling This!

Hj Ahmad Rasyid Hj Ismail

Quote from: mkmeister on March 04, 2010, 03:35:19 PM
OK, here's a step by step of exactly what I did. There were ZERO code changes, and only used MySQL to pull it off as noted in a previous post.

As a note, I'm using SMF 1.1.11 and MySQL 5.0.x. If SMF 2.x uses a similar table structure (haven't looked at it personally yet), I'd imagine it'd work just as well and still no code changes. They don't even need to be on the same database, but it's easier, and my instructions will assume they're using the same database with just different prefixes for the two (or more) installations of SMF. It does not matter what domain they are hosted on as long as they use the same MySQL server.

This will share user logins and PMs.

Assume you have two installations. One is an already established forum installation, and it uses the prefix "forumA_". The other is a new forum that you want to share forumA's logins and PMs. It has the prefix "forumB_". They're both in the database called "smf".

Log onto MySQL. There are a variety of tools for this but I just use commandline.
Now, once in MySQL, issue the following.
USE smf;

First, just move forumB's tables you want shared out of the way.
ALTER TABLE forumB_members RENAME TO forumB_members_old;
ALTER TABLE forumB_personal_messages RENAME TO forumB_personal_messages_old;
ALTER TABLE forumB_pm_recipients RENAME TO forumB_pm_recipients_old;


WARNING: Your forumA install will NOT be accessible once you do the following step until all steps are complete. It'd be wise to make a backup first too.
Next, rename forumA's tables to use a "shared_" prefix.
ALTER TABLE forumA_members RENAME TO shared_members;
ALTER TABLE forumA_personal_messages RENAME TO shared_personal_messages;
ALTER TABLE forumA_pm_recipients RENAME TO shared_pm_recipients;


Finally, create views of the shared tables for both forumA and forumB so they see the exact same tables and the info is the same for both.
First, forumA:
CREATE VIEW forumA_members AS SELECT * FROM shared_members;
CREATE VIEW forumA_personal_messages AS SELECT * FROM personal_messages;
CREATE VIEW forumA_pm_recipients AS SELECT * FROM shared_pm_recipients;


And next, forumB:
CREATE VIEW forumB_members AS SELECT * FROM shared_members;
CREATE VIEW forumB_personal_messages AS SELECT * FROM personal_messages;
CREATE VIEW forumB_pm_recipients AS SELECT * FROM shared_pm_recipients;


Presto! Finished! Both forumA and forumB share users and PMs!

If anybody can spot corrections or something wrong with the above instructions, let me know and I'll correct it.
This is nice if the data is fixed i.e. with no new data coming in. If not, you will have to keep copying all the time, right? Or am I getting it all wrong?

Arantor

No, creating VIEWs allows MySQL to silently deal with it and SMF won't notice or care.

Note that member avatars are shared if they use an external URL or pick from the gallery (since that's stored in the members table, along with their post count) but not if they upload their own (since that's stored in the attachments table)

mkmeister

Quote from: GrassrootsPA on March 04, 2010, 09:27:44 PM
mkmeister,

Three 'dumb' questions from a SMF newbie:  ;)

1) Are you using phpMyAdmin for the MYSQL edits?

2) One can do this sharing for 3 or more databases as well (not just two), right?

3) Would it be possible for someone to create a mod that does this sharing? (I personally am uncomfortable messing around with MYSQL tables and would prefer a mod, if possible)

1. Nope, just commandline (in Linux), but phpMyAdmin would work just fine.

2. Yes, you can do it for as many installs of SMF that you want.

3. I suppose so? When I get some free time I'll certainly look into it.

mkmeister

Quote from: Arantor on March 05, 2010, 07:15:54 AM
No, creating VIEWs allows MySQL to silently deal with it and SMF won't notice or care.

Note that member avatars are shared if they use an external URL or pick from the gallery (since that's stored in the members table, along with their post count) but not if they upload their own (since that's stored in the attachments table)

Agreed, the only time you'd have to recreate the view is if the table structure changed in some way, but I'm guessing that doesn't happen too often.

Arantor makes a good point about some possibly quirky behavior. The way to solve that of course would be to get more complex with the views. Could probably accomplish only sharing certain data between the two installations by creating yet another table to store the non-shared data and getting selective with your query for the view.

For example, if you didn't want to share your PMs across and wanted your post count to be relevant to the forum you're on, I guess you could skip the part where the PM tables are shared, create a new table called something like "forumA_members_counts", and create fields for the ID_MEMBER, posts, instantMessages, and unreadMessages, remove those fields completely from the shared_members table, and then in your view you could do something like this:
CREATE VIEW forumA_members AS SELECT * FROM shared_members LEFT JOIN forumA_members_counts ON forumA_members_counts.ID_MEMBER = shared_members.ID_MEMBER;

I would think that'd be the most useful and polished method of sharing only certain data relevant to each forum.

Thoughts, Arantor?

Arantor

You'd also possibly have to split off the avatar column too, as that's where URL/gallery avatar is stored. Theory sounds good; and it's exactly what views are designed for, abstracting the physical structure away below the application level.

Not sure I'd LEFT JOIN, though, since I'd argue those are mandatory fields, so I'd probably INNER JOIN instead but that's not really a huge thing.

Oh... and membergroups. Membergroups are stored in the row too: ID_GROUP, ID_POST_GROUP, additionalGroups. If the rules are exactly the same on both forums and permissions too (down to the physical group ids) it's fine. If there's any difference, you're likely to want to split them out as well into the individual forum tables.

(People look at me funny when I tell them it's a huge amount of work. It's because everything is so tightly wrapped it's not funny.)

GrassrootsPA

mkmeister, glad to hear you will look into making a mod around this.

Very excited to hear this! Thanks for all you do.
I'm Feeling This!

Hj Ahmad Rasyid Hj Ismail

Quote from: Arantor on March 05, 2010, 07:15:54 AM
No, creating VIEWs allows MySQL to silently deal with it and SMF won't notice or care.

Note that member avatars are shared if they use an external URL or pick from the gallery (since that's stored in the members table, along with their post count) but not if they upload their own (since that's stored in the attachments table)
Aah! Clever...

mkmeister

Quote from: Arantor on March 05, 2010, 10:25:33 AM
(People look at me funny when I tell them it's a huge amount of work. It's because everything is so tightly wrapped it's not funny.)

Haha, yeah, I'm gettin' that feeling. I'm not so sure it'd be as much of a mod as a one-off script, but even then it'd pretty much force the person doing it to make the choice once and stick with it as far as what exactly they want shared, because of all the potential data moving around and tables creation/dropping if someone would decide to change their mind. I guess I don't feel it's a mod's job to potentially constantly be dropping and adding tables, fields, views, etc.

Even then... migrating from a few forums that already exist could be... is "itchy" the right word for it? That doesn't even consider that there might possibly be a mod out there that actually adds a field to a table that I'd be replacing with a view... which means it would break that mod if the mod was installed after the view is already created, since a view only captures the fields that exist at the time it was created. I don't know if any mods actually alter core table structures or anything, but it's a possibility to consider.

GrassrootsPA

#275
What about some sort of mod that all forums that want to share the data would need to install and turn on? (like a key?)

How about the creation of a "dummy" mysql table all the installs sync data with?

Trying to think outside the box here (really want this feature)
I'm Feeling This!

Arantor

Mods do modify core tables, they're perfectly allowed to provided they don't break anything.

The one thing I would say to you is that this requires MySQL 5 and that you run the risk of trying to support people who still use MySQL 4 (yes, some people still do, just as some people still run PHP 4.3.x)

mkmeister

Quote from: Arantor on March 07, 2010, 08:38:22 AM
Mods do modify core tables, they're perfectly allowed to provided they don't break anything.

The one thing I would say to you is that this requires MySQL 5 and that you run the risk of trying to support people who still use MySQL 4 (yes, some people still do, just as some people still run PHP 4.3.x)

Good point about MySQL 4, but if it did views, I'd just simply required 5. In any case, since mods do modify core tables, a mod that creates views like this is completely out of the question.

It'd have to be a code patch to be a mod.

Arantor

Most mods modify code too, but that's why I cannot recommend this being a mod because the core tables do often get changed. (I've done it more than once)

Though, I don't modify any core table in SimpleDesk; I just add new tables.

Hj Ahmad Rasyid Hj Ismail

Would it work the same with SMF 2.0 RC2 and later? I mean if this really involves MySQL only, it should work right?

Advertisement: