[development] Separating configuration vs data tables

[Alan Dixon]

I'd like to see a solution to this problem as well.

But there are a few tables that don't fall into just configuration and
content - there's also log tables (the drupal one, as well as some
module specific ones), and perhaps user tables, and maybe comments.
For some kinds of sites where content is all centrally controlled, you
may want to put up some new content and test it in a staging
environment before pushing it up to your production machine, but you
wouldn't want to overwrite those log, comment or user tables. Or you
might want to merge up your new content, if some of your content is
controlled, and other parts is contributed from the users.

In addition, when you start thinking about flexinode/cck and all the
different ways that it's used (sometimes table content is considered
content, sometimes config) or taxonomy (fixed categories vs.
freetagging), the division between content and configuration becomes
very murky.

As an interesting example, I just upgraded my Ubuntu to dapper (an
aside - it was amazing!). Similarly to freebsd as well, if you've
customized your config files, it'll ask you if you want to keep your
existing ones, or replace them with the new ones. So I wonder if
something like that would be relatively straightforward and useful,
even if there will always be problems in specific cases.

Conclusion: rather than trying to categorize in detail all the
different tables, I'd propose allowing them to be in just three
different categories:
a. transactional (a good word after all ...)
b. content
c. configuration

where the default is 'content'. This imaginary script would then:

1. ignore all the log tables
2. interactively allow each of the content tables to be ignored,
merged or replaced, after an analysis of the differences.
3. replace all the configuration tables (and save the difference
somewhwere as a sql script that could be called to selectively reverse
some changes? ah, now it's getting ambitious).

Some problems would occur if you're merging node tables - you'd need
to update all the related id's in the modules for example.



On 6/9/06, Moshe Weitzman <weitzman at 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
>
>


-- 
Alan Dixon, Web Developer
http://alan.g.dixon.googlepages.com/