Major Additions - Share users database among many forums

This is a feature requested many times, and we are still waiting, so I released a patch to be able to run many forums with the same user database.

This is an EXPERIMENTAL PATCH I'm running on my forums.

--- ../../../private/upload/includes/class_core.php     2008-07-10 11:17:32.000000000 +0200
+++ class_core.php      2008-07-11 17:44:20.000000000 +0200
@@ -405,6 +405,33 @@
                $this->connection_recent =& $link;
                $this->querycount++;

+               $shared_tables = Array("user","useractivation","userban","usertitle","userchangelog","usertextfield","userfield","usergroup","userlist","usernote","profilevisitor","reputation","reputationlevel","spamlog","strikes","pm","pmreceipt","pmtext","avatar","profilefield");
+
+               $main_prefix = "FP_";
+               foreach ($shared_tables as $shared_table)
+               {
+                       $orig = TABLE_PREFIX.$shared_table;
+                       $dst  = $main_prefix.$shared_table;
+
+                       $this->sql = str_replace($orig." ",$dst." "  ,$this->sql);
+                       $this->sql = str_replace($orig.".",$dst."."  ,$this->sql);
+                       $this->sql = str_replace($orig."\r",$dst."\r",$this->sql);
+                       $this->sql = str_replace($orig."\n",$dst."\n",$this->sql);
+                       $this->sql = str_replace($orig."\t",$dst."\t",$this->sql);
+
+               }
+

                if ($queryresult = $this->functions[$buffered ? 'query' : 'query_unbuffered']($this->sql, $link))
                {

You can add "session" to $shared_tables if you want to share your sessions among forums under the same domain (Thanks Lionel)

This is only useful for new forums joining anothers one user database.

Requirements:

• An old forum where all users are.
• Sharing the same database, using diferent prefixes for tables (TABLE_PREFIX)

For example: Imagine we have FORUM_A , FORUM_B, and FORUM_C each one with their respective licenses.

FORUM A has table prefix FA_ in database, FORUM_B has FB_ and FORUM_C has FC_

FORUM A is the main, and older forum, where we have all our users.

then we would install FORUM_B and FORUM_C normally, using the same database that FORUM_A uses, but setting up their own table prefixes.


once FB_ and FC_ are installed then we go patching class_core.php from FORUM_B and FORUM_C setting up $main_prefix = "FA_"; so they use FORUM_A for shared tables.

Possible problems:

• memcached issues?, not sure
• avatar/signature pics/etc issues when storage is set to disk.
• more to come...

Author: Optimizacion Web

[Dark Zero]

Interesting approach, i keep an eye on it

[FRANKTHETANK 2]

yea this might come in handy

[Tekmon]

I implimented a similar one where the seperate forums just shared the user, pm and one or two more tables and it works pretty well. However it does require mass changes to the FB and FC PHP files where yours would not?

If I read your correctly it looks like if the table being queried is in the array it will use the FA table prefix?

[lodac]

I have been looking for something like this. I will follow.

[Adrian Schneider]

Another (similar) solution is to use MySQL 5's views.

(Example: db2.user is a view of db1.user)

[mangel.ajo]

Originally Posted by Tekmon

I implimented a similar one where the seperate forums just shared the user, pm and one or two more tables and it works pretty well. However it does require mass changes to the FB and FC PHP files where yours would not?

If I read your correctly it looks like if the table being queried is in the array it will use the FA table prefix?

Is good to know that your modifications worked, It makes me feel better

And, yes, the result is that when any table in "shared tables" is queried the query ends up rewritten so it goes to the FA_ prefix.

[mangel.ajo]

Originally Posted by SirAdrian

Another (similar) solution is to use MySQL 5's views.

(Example: db2.user is a view of db1.user)

Hmmm, if database can gives us that functionality it could be interesting because we wouldn't need to patch at all.

Can you point us to some document about mysql5 views?

How do you setup a view?

Greetings,
Miguel ?ngel.

[Lionel]

add to your array

,"strikes","pm","pmreceipt","pmtext","session","av atar","profilefield"

also take a look at this

[mangel.ajo]

Thanks Lionel!!

Anyway, I'm not sure if the table "session" would be safe to be shared among all forums... why? ... because it points to the threadid or URL the user is watching, and that would be different between one forum and another.

Anyway it would be nice because this way the total user activity in the forums would be reflected.