History of DatabasePrefixing
Version 5 | Current version | |||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
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:
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. 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 ReleaseClyde )
| 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:
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. 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)) )
|