prefix for your database tables

[GameCrash]

General

This hack has been created using a vB 2.2.5 but I think it should be working with any vB 2.x.x. This hack is BETA, I'm sure you will get some database errors on your board. Please do NOT use it on production boards.

What this hack does

This hack allows you to add a prefix to your table names (example: if you use the prefix "vb_" your table "user" would be named "vb_user". This is important if you want to install multiple vBulletins on one database.

How does it work

If $dbprefix is set in the config.php, the table names are changed in every query. This works as the following: if the table {prefix}{tablename} exists, it will be changed to {prefix}{tablename}, if not, it will be left as {tablename}. This will make it possible to have some tables changed to vb_* and others not. This is for the possibility you want to use some tables (for example the user related tables) in multiple boards.

(okay, this is a bit confusing, but my english isn't very well ;))

How to install (there will be a better manual for the final release)

#####
# in ./global.php, ./admin/global.php and ./mod/global.php:

Replace

$DB_site->password=$dbpassword;

with

$DB_site->password=$dbpassword;
$DB_site->prefix=$dbprefix;


#####
# in ./admin/db_mysql.php:

Find

function query($query_string) {
global $query_count,$showqueries,$explain,$querytime;
// do query

and below it, add

if ($this->prefix != "") {
$query_string = $this->addprefix($query_string);
}


Find

function fetch_array($query_id=-1,$query_string="") {

and above it, add

function addprefix($query) {
static $tablenames;
if (!is_array($tablenames)) {
$tablenames = array();
$len = strlen($this->prefix);
$q = mysql_list_tables($this->database,$this->link_id);
while(list($table) = mysql_fetch_array($q)) {
if (strstr($table,$this->prefix)) {
$short = substr($table,$len);
$tablenames["$short"] = $table;
}
}
}
reset($tablenames);
$query = str_replace("\n"," ",$query);
$query = str_replace("\t"," ",$query);
while (list($short,$new) = each($tablenames)) {
while(preg_match("/SELECT(.*)([ ,(]+)$short([\.]+)(.*)FROM /i",$query)) {
$query = preg_replace("/SELECT(.*)([ ,(]+)$short([\.]+)(.*)FROM /i","SELECT\\1\\2$new\\3\\4FROM ",$query);
}
while(preg_match("/UPDATE(.*)([ ,]+)$short([ ,]+)SET/i",$query)) {
$query = preg_replace("/UPDATE(.*)([ ,]+)$short([ ,]+)SET/i","UPDATE\\1\\2$new\\3\\4SET",$query);
}
while(preg_match("/INTO(.*)([ ,]+)$short([ (]+)/i",$query)) {
$query = preg_replace("/INTO(.*)([ ,]+)$short([ (]+)/i","INTO\\1\\2$new\\3\\4",$query);
}
while(preg_match("/ FROM(.*)([ ,=(]+)$short([ ,\.]+)/i",$query)) {
$query = preg_replace("/ FROM(.*)([ ,=(]+)$short([ ,\.]+)/i"," FROM\\1\\2$new\\3",$query);
}
while(preg_match("/ FROM(.*)([ ,=(]+)$short$/i",$query)) {
$query = preg_replace("/ FROM(.*)([ ,=(]+)$short$/i"," FROM\\1\\2$new",$query);
}
}
return $query;
}


#####
# in ./admin/config.php

Somewhere, add

// tablename prefix
$dbprefix='vb_';


That should be all file changes. Now you can rename the tables you want to have using the prefix to "vb_*".

That's all... post bug reports, questions etc. here, do not pm, email or icq me about this hack - you will be ignored. Thanks.

[Admin]

Wouldn't this make queries slower, PHP-wise? (with all the regex's for each string)

BTW, I don't think you took into consideration qualifying (SELECT tblName.field FROM tblName). :)

[GameCrash]

Yes, of course it will make the the board a bit slower (as every hack, only a bit more ;)) but... would you prefer to change 1137 queries by hand? I don't have any benachmark yet but I don't think this will be too drastic...

I don't understand your problem with SELECT tblName.field FROM tblName? This should work without problems...

[John]

Don't forget to mention that you need to have two licences for two boards, and so on :)

[Admin]

Let's say I have this query:
SELECT post.postid, user.username
FROM post
LEFT JOIN user ON (user.userid=post.userid)
WHERE post.userid <> 0
Will it be parsed ok? (just making sure :))

Oh, and you also need to edit /mod/global.php just like the other global.php's.

[GameCrash]

It SHOULD work (I will try this query later) - but no warranty, that's why it's beta ;)

EDIT:

Yes, it works. It is translated to SELECT vb_post.postid, vb_user.username FROM vb_post LEFT JOIN vb_user ON (vb_user.userid=vb_post.userid) WHERE vb_post.userid <> 0

And thanks for the hint on mod/global.php - I never use the mod cp so I always forget it ;)

[Admin]

Well, very nice. :)

[snyx]

would this cause any problems when upgradeing?

[GameCrash]

It could cause problems if there are tables used by multiple vBulletin boards and if this tables change between the versions...

Please note, that before running the upgrade script you MUST redo the file changes, otherwise the upgrade script will not work. And don't forget to create a full backup of your database...

[Floris]

I have set up a test board yesterday on my winxp system here :P apache/mysql/php, so this would be nice to test! I will install several instances and try to run multiple with the same userbase and some other equal sections and see which errors i will run into.

YES< I only have once license, but this is on my second system behind the router, in the network, and not public. URI can't be reached through internet.

[GameCrash]

That would be great, thank you :)

[snyx]

Originally posted by xiphoid
YES< I only have once license, but this is on my second system behind the router, in the network, and not public. URI can't be reached through internet. lol, chill man its okay to run a vb for test purposes :D

[Admin]

I was thinking about this yesterday... wouldn't it be easier to just preg_replace() the whole query with the array of tables?

[GameCrash]

Well... I have never used preg_replace with arrays... let me think about it ;)

[GameCrash]

Originally posted by xiphoid
I have set up a test board yesterday on my winxp system here :P apache/mysql/php, so this would be nice to test! I will install several instances and try to run multiple with the same userbase and some other equal sections and see which errors i will run into.

Did you test it yet?

[Link14716]

well, I see this hack, but I am wondering how to rename my tables?

[Link14716]

Nevermind about that, I figured it out, but now, when I go to the board, I am greeted with this error (note, that I renamed index.php to index1.php)
Database error in vBulletin 2.2.6:

Invalid SQL: INSERT INTO vggmmb_v5_session (\4sessionhash,userid,host,useragent,lastactivity, location) VALUES ('54197df039674b2245709c015eb9657f','2','24.240.14 7.243','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;','1026008749','/index1.php')
mysql error: You have an error in your SQL syntax near '\4sessionhash,userid,host,useragent,lastactivity, location) VALUES ('54197df03967' at line 1

mysql error number: 1064

Date: Sunday 07th of July 2002 03:25:50 AM
Script: http://mb.vggmn.com//index1.php
Referer:


Any suggestions?

Same thing also happens anywhere.....

[GameCrash]

Your prefix is 'vggmmb_v5_'?

What PHP version do you use?

[agamer]

i installed this and am getting little errors e-mailed to me, as well as on the board.


Database error in vBulletin :

Invalid SQL: SELECT template FROM template WHERE title='options'
mysql error: Table 'template' doesn't exist

mysql error number: 1146



i have it as vb_template, why is it not working correctly?

I have edited the files, and double checked them, and triple checked them today, i have followed the instructions down to the letter.

I can't figure it out.

I'm running 2.2.6.


-agamer

[GameCrash]

I'm really wondering about this because this is a very simple SQL Query that shouldn't make probmels... What page is this error on? Are you sure you have added $DB_site->prefix=$dbprefix; to all three global.php files?

[petermw]

A little confused but this would allow multiple boards (with multiple licenses) to share a db, correct? So a user from one board could keep his username password for the other board. I tired doing this manually about a year ago and got a few problems with recent posts, post counts, cookies etc. Anyone given this a test run yet?

Thanks for the effort,
Peter

[GameCrash]

It should work. because of the posts and post counts, that would be a problem, maybe. But if you specify the cookie path, you should not have problems with this.

[petermw]

Game Crash, question. I have a slightly different use in mind and am wondering if this is the hack for me. I want user info to be written to two separate db's when a user signs up. So for example, you sign up at XYZ.com and you are also able to use that username/password at ZYX.com. Aside from the information provided at signup it isnt important to me that the db's share anything else. Is this possible?...

Thanks,
Peter

[N!ck]

has anyone tried chen's suggestion (using preg_replace() for all the table names)?

[GameCrash]

@petermw: This would be a completely different hack that would add a new Database connection and a new query...

@nicksaunders: No, I didn't have the time for it yet. And I don't think it makes sense to work on this hack any more as this functionality (prefixes) has been added to vB3...

[Bro_Joey_Gowdy]

I only have one database and am wanting to install a test board for vb2.3.0 to simply see if I want it or not - will this work with vb2.3.0 ?

https://vborg.vbsupport.ru/showthread.php?s=&postid=354511

[GameCrash]

Yes, it should work. But please note that I don't work on this hack any more as vB3 has this function built in (and I think much less ressource intensive). I would prefer you to use the prefix hack for the test board, not the production board as I cannot guarantee that it works stable...

[petermw]

Do you know of a thread which outlines vb 3 functionality. Id like to read about this hack being part of the new release.

Thanks,
P

[GameCrash]

Well, it's not this hack... but I know that vB3 does support a custom prefix, see http://www.vbulletin.com/forum/showthread.php?threadid=64027&highlight=prefix and http://www.vbulletin.com/forum/showthread.php?threadid=62743&highlight=prefix for example...

[Bro_Joey_Gowdy]

Thanks for the info will only use it on the test board to simple see if i want to keep the test board, thanks.

[Bro_Joey_Gowdy]

Hmmmm..... Im not doing something right, take a lok at the errors... anythoughts?


Warning: Cannot add header information - headers already sent by (output started at /www.joeygowdy.com/gundamarmada/admin/db_mysql.php:163) in /www.joeygowdy.com/gundamarmada/admin/functions.php on line 1652

Warning: Variable passed to reset() is not an array or object in /www.joeygowdy.com/gundamarmada/admin/db_mysql.php on line 163

Warning: Variable passed to each() is not an array or object in /www.joeygowdy.com/gundamarmada/admin/db_mysql.php on line 166


Line 163 is reset ( $ tablenames ) ;

and Line 166 is while ( list ( $ short , $ new ) = each( $ tablenames ) ) {

Also, would useing phpmyadmin help ?

[GameCrash]

Hmm... this would happen if you don't have any table with the prefix... try changing if (!is_array($tablenames)) {
$len = strlen($this->prefix); to if (!is_array($tablenames)) {
$tablenames = array();
$len = strlen($this->prefix); - I will add this to the instructions if it works...

[Bro_Joey_Gowdy]

Thanks, Its looking good now - will keep ya informed on any other problems - also im useing vb2.3.0

[gmarik]

Could you put the hack in a .txt file? Uhh?

[GameCrash]

Why? If you own a license you can see the codes in the first post. If not, you can't download any text file ;)

[Peakin]

hmm, new user to vb here, applied this hack to vb 2.3.2 all well and good, works a charm, but, now when posting a msg, a word is the same as a table name eg "access" its replacing it with prefix_access..

this only happens in mid sentence, eg:

access

is ok, but in sentence:

blablbablah vb_access blah

anyway to stop this?

please help.

ps. i should add that i am using a front end for vb (auto account creation in vb) and just had to heavily mod it to work with vb 2.3.2, so i wont be able to use vb3 for some time, so thats not an option...

[GameCrash]

Hm, I'll have a look at it... should be possible to do something here...

[Peakin]

Thanks man, I'd appreciate that =)

[geekylucas]

hmm, new user to vb here, applied this hack to vb 2.3.2 all well and good, works a charm, but, now when posting a msg, a word is the same as a table name eg "access" its replacing it with prefix_access..

Did anyone manage to figure out a way to prevent this happening (aside from upgrading to vbulletin 3)? I'm having the same problem. =(