News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Question about sharing tables

Started by DLD, April 20, 2004, 03:28:44 PM

Previous topic - Next topic

DLD

ok I have several instances of SMF running (6 total) which were upgraded from yabbse and have been running for some time. I know it is almost imposiable to combine all of them together into one instance of SMF so what I am looking to do is share the member table with all of them so a when a user signs up at one site that user is now signed up for all sites.

If this can be done easily then making a HTML based forum page that has links to the other sites will be easier.

Thanks for any help I can get :)

Oldiesmann

I'm not sure how easy that would be to do. I guess you could modify Register.php for each site so that when they registered for one site, it would automatically copy the information to the other tables. However, in order for this to work properly, the cookie name has to be the same on each board.
Michael Eshom
Christian Metal Fans

DLD

I was thinking more on the lines of changing the settings.php so that it would read the same table for all the boards, but I have no idea where to start

The cookies are already he same name :)

Oldiesmann

Ok. I see what you're saying. Yeah, you could do that, but it would be a pain in the rear. If you do want to do that, here's how:

Go through every file in the sources directory and replace every occurrence of {$db_prefix}members with the name of the table you want to use. This would probably take you at least 20 or 30 minutes, but it can be done. Just make sure you put all the boards in maintenance mode before you do that.
Michael Eshom
Christian Metal Fans

Jack.R.Abbit™

I think this is what you would really need to do.
1) eliminate/combine all you member tables into one and give them a prefix that is different than all your instances of SMF.
2) find everyplace where SMF is querying the member table (189 times by my count).
3) instead of using the "{$db_prefix}" variable, you would need to use the new prefix you gave your member table.

This should work for sharing member log in info... however, I suspect this will pretty much prevent you from ever doing an upgrade.

I just did this locally and it did work.  I renamed my members table to be "smf_shared_members" and then did a search and replace like:
<search>
{$db_prefix}members
</search>

<replace>
smf_shared_members
</replace>

Unfortunately, if you want to use a variable to hold the "shared" prefix, you'll need to examine each file that uses it and locate each function that uses it and add the variable to the globals for that function.  That was too much work just to test it so I just hard coded the prefix in.

[edit]I guess I took too long to write that... Oldiesman had it answered long before I did...  but at least I can confirm that it works[/edit]

MrCue

Wouldnt it be a good idea if all the table names were defined in settings.php. That way we can easily change which tables are shared.
I am neither a Pessimist nor an Optimist, Just a Realist.

Eve-Online Forum | View Latest Eve-Online Kills | Site Map | SMF Installation

Oldiesmann

That would either create a huge server load (having it look at Settings.php everytime it wanted to run a query) or add a ton of global variables (one for each table name), which would in turn increase the size of Settings.php and just about every other file. The way it's done now is much easier.
Michael Eshom
Christian Metal Fans

DLD

what I was looking for is something like yabb gold was where you could change the folder of members that way several installs could share the same folder.

The problem I am going to run into is I have 3 seperate mysql servers (we have sites on different hosts) so therefore the mysql database and the tables are different. I don't mind converting everything to one database but we have over 900 members total and over 20000 posts and I relly want to keep them instead of starting all over again just to get what I want out of it.

Jack.R.Abbit™

Quote from: Oldiesmann on April 20, 2004, 05:58:26 PM
That would either create a huge server load (having it look at Settings.php everytime it wanted to run a query) or add a ton of global variables (one for each table name), which would in turn increase the size of Settings.php and just about every other file. The way it's done now is much easier.
I had considered this for a mod to do some time in the idle future (which could be never).  I planned it like so...

rather than using things like this:
{$db_prefix}members and {$db_prefix}attachments

$db_prefix could be an array and you would use things like this:
{$db_prefix['members']}members and {$db_prefix['attachments']}attachments

$db_prefix is already a made global for the functions that need it so you are not really adding a bunch of globals... just making one a bit larger.  And you wouldn't have to look in Setting.php everytime.  It would really work exactly as it does now, except that the $db_prefix array in Settings.php would need to have an entry for each tablename.  And of course the admin page might need to be adjusted to make it so you can change each one.

Converting to something like this is not easy though because many (most? all?) the queries would need to be rewitten slightly because I think the $db_prefix array can't be put into the query string the same way.  It would take more than just a bunch (33) of search and replaces.

MrCue

i think you can do {$db_prefix[tbl_name]} without escaping from the string.
I am neither a Pessimist nor an Optimist, Just a Realist.

Eve-Online Forum | View Latest Eve-Online Kills | Site Map | SMF Installation

[Unknown]

You wouldn't need the braces.  It's either {$db_prefix['table']} or $db_prefix- not {$db_prefix
}.

-[Unknown]

Christian Land

Quote from: Oldiesmann on April 20, 2004, 05:58:26 PM
That would either create a huge server load (having it look at Settings.php everytime it wanted to run a query) or add a ton of global variables (one for each table name), which would in turn increase the size of Settings.php and just about every other file. The way it's done now is much easier.

::) Sorry, oldiesmann, but you're telling "some not so right things" ;D

1. "That would either create a huge server load (having it look at Settings.php everytime it wanted to run a query) "

Nope... it wouldn't increase server-load at all... currently you already have to open Settings.php if you query the database, because thats where all settings for the DB-Connection are stored ($db_prefix, $db_name,...) - and you only have to open it once for each script-call, not for each query!

2. "add a ton of global variables (one for each table name), which would in turn increase the size of Settings.php and just about every other file. The way it's done now is much easier."

Again: wrong! It would add 33 variables (or constants) which isn't much. Together, they would probably take 1kb of memory (more likely: even less). The filesize wouldn't change much, either. There is no real difference if you write:

$result = db_query("
SELECT t.ID_BOARD, MAX(m.ID_MSG) AS ID_MSG, MAX(m.posterTime) AS posterTime
FROM {$db_prefix}messages AS m, {$db_prefix}topics AS t
WHERE m.ID_MSG = t.ID_LAST_MSG
AND t.ID_BOARD" . (count($setboards) == 1 ? " = $setboards[0]" : ' IN (' . implode(', ', $setboards) . ')') . "
GROUP BY t.ID_BOARD", __FILE__, __LINE__);


or

$result = db_query('
SELECT t.ID_BOARD, MAX(m.ID_MSG) AS ID_MSG, MAX(m.posterTime) AS posterTime
FROM '.TABLE_MESSAGES.' AS m, '.TABLE_TOPICS.' AS t
WHERE m.ID_MSG = t.ID_LAST_MSG
AND t.ID_BOARD' . (count($setboards) == 1 ? " = $setboards[0]" : ' IN (' . implode(', ', $setboards) . ')') . "
GROUP BY t.ID_BOARD", __FILE__, __LINE__);


depending on the query, it would use a few bytes less or a few bytes more than the current version...

If you use constants for the table name, you could even remove all "global $db_prefix" lines from the current source...

Thats the way nearly all professional PHP-scripts are handling their table-names...

Oldiesmann

Ok... I was wrong... I'm not an expert by any means and wasn't thinking in terms of having to look at Settings.php only once... :)
Michael Eshom
Christian Metal Fans

[Unknown]

Constants are actually slower than scalars in PHP.  I don't know why, but they are a joke and just are basically unchangable auto globals.  Using an indexed array might be as slow, but not as ugly :P.

-[Unknown]

Christian Land

I tested it with 10.000.000 assignments of constants to variables and variables to variables.... there was no measurable difference....  both took roughly 10s... of course i don't know how php treats variable-assignments in loops... but i guess we are talking about differences in really small fractions of a second.... so it doesn't matter (IMHO) if you use variables or constants... but moving the table names into special vars/consts would make the code more "useable"...

[Unknown]

I'm talking about using them not assigning them, since use is going to happen more than assignment in many cases.

-[Unknown]

Christian Land

Quote from: [Unknown] on April 22, 2004, 05:48:24 PM
I'm talking about using them not assigning them, since use is going to happen more than assignment in many cases.

Take a look at: http://www.php-mag.net/itr/online_artikel/psecom,id,546,nodeid,114.html

I only had very few time to check it, but according to their benchmark-scripts, using constants is 1.5 times faster on my machine than using global variables...

[Unknown]

> The first test measures whether accessing constants or global variables is faster. In PHP 4.2.3, globals were faster. In PHP4.3.3 and PHP5b4, the reverse is true.

I'm not sure but I think I tested it in 4.3.1.

-[Unknown]

Advertisement: