prefix for your database tables

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.