On 6/9/06, Moshe Weitzman <weitzman@tejasa.com> wrote:
My suggestion is to identify which database tables contain administrative or configuration data vs. which tables contain content and then store them in separate databases (brief research indicates that MySQL supports cross-database queries but Postgres does not, so this functionality would be turned off with Postgres). Ideally, it would then be possible to clone both databases, add modules or change settings in the sandbox until happy, and then copy back only the admin/config database and any filesystem mods. In one step, the production system would be upgraded.
We would have to decide which tables fall into each category and possibly refactor some tables if they span both categories. Perhaps it is not possible to separate them all cleanly in this way, but it seems worth trying.
right and right. it would be outstanding if we could pull this off. Migrating changes to production is a major headache, and this would make it really easy to roll forward and even roll back.
i used to work at a company which worked this way. we called relatively static tables 'domain' tables and relatively dynamic 'transactional'. i like your terms of configuration and content better i think.
yes, that prefix trick is very cool and requires no changes from core or contrib. but i have a better idea. Lets store this mapping in a new configuration table. Then, modules or the installer can write into this table. we just need to run this pseudocode in settings.php where the $db_prefix stuff is now:
// set prefixes for each table as specified by the prefixes table $sql = SELECT prefix, name FROM prefixes; $result = db_query($sql); while ($row = db_fetch_object($result) { $db_prefix[$row->name] = $row->prefix; }
from there on, queries will always hit the right DB.
as a next step, i sigest identifying which tables go where and resolving splitting up ambigous tables
This is something I was thinking about the other day, but from a different angle. Some CMS have built in support for "production", "test" and "development" environments. I think RoR and Plone have this. Then I thought that multi site is so easy in Drupal that one can use the same codebase with 3 different databases (or 3 different prefixes in the same db). Now what Barry is proposing "configuration" (or "system") vs. "content" is a really good differentiator here, and could be used in many situations, such as sharing tables between sites, upgrades, ...etc.