DatabasePrefixing

One DB, several sites

Created by: btodoroff, Last modification: 01 Nov 2004 (18:02 UTC) by SEWilco

What is Database Prefixing?


On some hosting services you only get one DB but you can have several domains or sub-domains. Since I am in such a situation I developed the following process to allow multiple independent sites in a single DB. This requires having different table names for each site, by adding a site prefix to each table. (See note at bottom about shared information)

For example:

SELECT * from TIKI_USERS ...
becomes:

SELECT * from TESTSITE_TIKI_USERS ...
for a prefix of "TESTSITE_"

SELECT * from OTHERSCHEMA.TIKI_USERS ...
for a prefix of "OTHERSCHEMA."

SELECT * from TIKI_USERS ...
if you leave the prefix as "", the default.

This was developed using the latest CVS for 1.8 as of 5pm PST 1/9/04. It should continue to work for 1.8, but most likely will fail on <1.8. It is only tested with MySQL. I've done my best to get the Wiki formating accurate, but if in doubt use the attached text file for guidence.

NOTE: The instructions have gotten simpler in recent days since I got CVS access, so the text file has more steps than are needed. The text file is still the reference for the exact characters in each search and replace, but it now contains several search/replace steps that are no longer necessary. In short the wiki page contains the correct steps, use the text file if you are not sure about the exact characters in a string listed in the wiki page.

Here are the steps:

For all search/replace operations be sure to match case.

In db/tiki.sql replace:
"DROP TABLE IF EXISTS " with "DROP TABLE IF EXISTS ##PREFIX##"
"CREATE TABLE " with "CREATE TABLE ##PREFIX##"
"INSERT IGNORE INTO " with "INSERT IGNORE INTO ##PREFIX##"
"INSERT INTO " with "INSERT INTO ##PREFIX##"
"UPDATE " with "UPDATE ##PREFIX##"

Save as db/prefix_tiki.sql.

In db/prefix_tiki.sql replace "##PREFIX##" with your chosen prefix. In this example we'll use "photon_" as the prefix. Save this file under a new name like "photon_tiki.sql". Now use "photon_tiki.sql" to add and configure the tables in your shared DB.

You now have a prefixed DB to run your tiki site. Next we need to change the php source to use the prefixed site. This will once again use a masive set of search and replace operations.
Note that ` is the carret not the single quote '! This is usually on the same key as the tilde (~) on US keyboards. For all files matching *.php or *.inc in all subdirectories replace:

Replace this With This
`tiki_`".$GLOBALS["db_prefix_tiki"]."tiki_
`users_`".$GLOBALS["db_prefix_tiki"]."users_
`messu_`".$GLOBALS["db_prefix_tiki"]."messu_
'sessions'$GLOBALS["db_prefix_tiki"].'sessions'
'galaxia_'$GLOBALS["db_prefix_tiki"].'galaxia_'


At the top of the file lib\galaxia\config.php change:

// Common prefix used for all database table names, e.g. galaxia_
if (!defined('GALAXIA_TABLE_PREFIX')) {
define('GALAXIA_TABLE_PREFIX', 'galaxia_');
}

to:

// Common prefix used for all database table names, e.g. galaxia_
if (!defined('GALAXIA_TABLE_PREFIX')) {
define('GALAXIA_TABLE_PREFIX', $GLOBALS["db_prefix_tiki"].'galaxia_');
}

In db/tiki-db.php add:

$db_prefix_tiki = 'photon_';

replacing "photon_" with your prefix just after the definition of $tikidomain.

Now just put it all on your server and you are ready to go. To set up the next site on the same database just do the search/replace on prefix_tiki.sql with a new prefix, copy the source to the new site's root, and change the value of $db_prefix_tiki to match the new prefix.


Multiple Related Sites in One DB

There also can be a need for several sites in one DB to share information. For example, if the user information is shared then a user which registers on one site becomes recognized on all sites. (Added as suggestion for (( ReleaseOne)) )
  • Issues
    • Shared users is an obvious application.
    • What other information might be shared? News?
    • Several related sites may share user data, but also allow more than one user file so there can be more than one cluster of related sites, such as one cluster for farming sites and another for railroad sites.
    • Different sites may want different restrictions, such as when having a public site and a members-only site.
      • Use of permission groups across/between several sites needs to be examined.


Related Items

Documentation » Technical Documentation » Database Tables

List of database tables used in bitweaver

  •    •    •    •  Array

Comments

How to share user database with two script?

by Anthony, 31 May 2007 (00:09 UTC)
Hi i might be little of topic, but i'm wondering if you know how i can share a user database with two script?

here is what i want to do:
when a member register in one script, they are also register in the other script, when they login to one they are automatically login to the other.