D5 Shared database - sharing all but theming/layout/navigation
Warning - I'm a noob, please be gentle
Here's a pretty much self-documented settings.php file showing how I've got my multi-site working. If someone familiar with Drupal's (v5) database structures could scan this and comment I'd really appreciate it. Note ALL the core tables are listed here, as per grepping "CREATE TABLE" from the .install files.
$db_prefix = array(
'default' => 'watchout_drupal.shb1sh_', /* new tables get created here, */
/* need to analyze and then add (shared or separate?) below */
'cache' => 'shb1sh_', /* must be separate */
'cache_filter' => 'shb1sh_', /* must be separate */
'cache_menu' => 'shb1sh_', /* must be separate */
'cache_page' => 'shb1sh_', /* must be separate */
'variable' => 'shb1sh_', /* must be separate */
'accesslog' => 'shb1sh_', /* site tracking - separate */
'client' => 'shb1sh_', /* site tracking - separate */
'client_system' => 'shb1sh_', /* site tracking - separate */
'flood' => 'shb1sh_', /* site tracking - separate */
'watchdog' => 'shb1sh_', /* site tracking - separate */
'blocks' => 'shb1sh_', /* theme layout nav - separate */
'blocks_roles' => 'shb1sh_', /* theme layout nav - separate */
'boxes' => 'shb1sh_', /* theme layout nav - separate */
'menu' => 'shb1sh_', /* theme layout nav - separate */
'system' => 'shb1sh_', /* theme layout nav - separate */
'queue' => 'shb1sh_', /* unknown - separate */
'sequences' => 'shared_', /* must be shared */
'access' => 'shared_', /* users - shared */
'authmap' => 'shared_', /* users - shared */
'profile_fields' => 'shared_', /* users - shared */
'profile_values' => 'shared_', /* users - shared */
'sessions' => 'shared_', /* users - shared */
'users' => 'shared_', /* users - shared */
'filter_formats' => 'shared_', /* user-roles - shared */
'filters' => 'shared_', /* user-roles - shared */
'permission' => 'shared_', /* user-roles - shared */
'role' => 'shared_', /* user-roles - shared */
'users_roles' => 'shared_', /* user-roles - shared */
'term_data' => 'shared_', /* taxonomy - shared */
'term_hierarchy' => 'shared_', /* taxonomy - shared */
'term_relation' => 'shared_', /* taxonomy - shared */
'term_synonym' => 'shared_', /* taxonomy - shared */
'vocabulary' => 'shared_', /* taxonomy - shared */
'vocabulary_node_types' => 'shared_', /* taxonomy - shared */
'aggregator_category_feed' => 'shared_', /* content - shared */
'aggregator_category_item' => 'shared_', /* content - shared */
'aggregator_feed' => 'shared_', /* content - shared */
'aggregator_item' => 'shared_', /* content - shared */
'book' => 'shared_', /* content - shared */
'comments' => 'shared_', /* content - shared */
'contact' => 'shared_', /* content - shared */
'file_revisions' => 'shared_', /* content - shared */
'files' => 'shared_', /* content - shared */
'files_backup' => 'shared_', /* content - shared */
'files_tmp' => 'shared_', /* content - shared */
'forum' => 'shared_', /* content - shared */
'history' => 'shared_', /* content - shared */
'locales_meta' => 'shared_', /* content - shared */
'locales_source' => 'shared_', /* content - shared */
'locales_target' => 'shared_', /* content - shared */
'node' => 'shared_', /* content - shared */
'node_accesaggregator_category' => 'shared_', /* content - shared */
'node_access' => 'shared_', /* content - shared */
'node_comment_statistics' => 'shared_', /* content - shared */
'node_counter' => 'shared_', /* content - shared */
'node_revisions' => 'shared_', /* content - shared */
'node_type' => 'shared_', /* content - shared */
'old_revisions' => 'shared_', /* content - shared */
'poll' => 'shared_', /* content - shared */
'poll_choices' => 'shared_', /* content - shared */
'poll_votes' => 'shared_', /* content - shared */
'search_dataset' => 'shared_', /* content - shared */
'search_index' => 'shared_', /* content - shared */
'search_total' => 'shared_', /* content - shared */
'term_node' => 'shared_', /* content - shared */
'url_alias' => 'shared_', /* content - shared */
);I've pieced the logic together for this from literally hundreds of hours of googling various threads and postings, and I feel pretty OK about this configuration for this scenario - I want to share users (including roles and profiles), as well as all content, including taxonomy. I want to keep theming, layout and navigation separate.
However, I'd like to expand my understanding of the principles involved in handling those tables that link across these areas, especially if I want to for example not share the content but yes share the taxonomy.
For example the block_roles table; I think I've played it safe by keeping it separate with blocks, while roles are shared, and I've put both filters and filter_formats with roles since the latter shares a field there. And url_alias just tracks the path/pathauto attribute for nodes, nothing to do with menuing, right?
Anyone know of a canonical source of database schema documentation for version 5?
Thanks in advance
whoops
forgot one table (thanks schema module):
'aggregator_category' => 'shared_', /* content - shared */
hairy palms
Yes I realize I'm talking to myself here :)
The "watchout database as default" idea has worked well!
I intend to try to remember to inspect a module's .install file for CREATE TABLE or other database alteration commands, but of course I'll forget, and sure enough last night I went ahead and installed Devel without doing so.
My development routine includes a series of scripted dumps of my databases to text files that are kept under version control (along with everything else in my whole XAMPP stack!), so the next time I ran a "svn status" check I saw that I suddenly had "watchout_drupal" data files where there wasn't any before.
Decided Devel's tables should be separate rather than shared, created them with the appropriate prefixing into the shared database and then dropped them from watchout, set that DB back to empty and finally, updated my settings.php file:
'devel_queries' => 'shb1sh_', /* contrib devel - separate /
'devel_times' => 'shb1sh_', / contrib devel - separate */
.
Tested everything, svn delete-revert-added as appropriate, then went ahead and committed, feeling rather smug I must say :)
All seems well so far, but I'd really appreciate someone having a scan of my shared vs site-specific groupings above and letting me know what they think.
After more googling on the many attempts to get v5's database relationships documented, I'm coming to accept that it just wasn't meant to be. . .
Actually, you are not
Actually, you are not talking to yourself. ;-)
I am very interested in what you have done, as I am trying to set up a site (site.com) with multiple subdomains (abc.site.com, xyz.site.com, etc.) running off a single shared database. The objective is to upload a single audio file that can be accessed by all subdomains. Access to that single file will, however, be controlled by TAC or tac_lite. Users with access to abc.site.com will be completely different from those with access to xyz.site.com. Also, the themes used by the two subdomains will be different. Layout, navigation will be similar, however.
I can set up the subdomains no problem. I also understand how to set up a separate themes folder withing each of the subdomains' own folders. The issue for me is what DB tables are shared and not shared.
I've checked into the domain module and although its capabilities appear outstanding, I am not sure that it is right for me.
Any thought you might be able to share with me would be most appreciated. Meantime I am going to experiment with what you have so kindly provided above. best, david
Very hairy topic
Ahh, feels like someone's just washed up on my desert island :)
Warning! Keep in mind I'm just going through this to give me a theming/navigation development platform, not for a production site. I've read elsewhere there can be major issues when it comes to upgrading, or even just updating modules, and that shared tables isn't actually officially supported. . .
In that sense I'd say Domain Access is a great solution, as apparently they've "canned" the sharing of tables and may prevent you (us) from shooting ourselves in the foot, or at least give you a place to go if it breaks.
However I think in your case, choosing your node access scheme is an even bigger issue than sharing tables. I'd definitely choose just one if you can, the patch(es?) to core to allow multiple node access schemes to work together logically would make me very nervous on a production site - I'm a Keep it Simple Stupid kind of guy myself :)
From my googling/browsing around these topics, I'd personally avoid taxonomy-based permissions and go with something like OG - apparently OG and DA can now play together OK if you're careful - but OG user roles doesn't fit in this scenario, as it uses a different node access model.
In any case, back to the topic at hand (shared tables). I tried to make my sample settings.php pretty self-documenting, as it lists EVERY core-created table, including those that may not even exist in your site yet, and puts them into what I saw as logical related categories. Note also the empty-watchout-db as default idea for handling new tables popping up.
Now in my specific scenario (theming sandbox with shared content) I wanted separate layout, theming and navigation, and everything else to be shared. Now you say you want navigation to be "similar" - in other words not exactly the same ==> separate, so there go boxes/blocks/menus right? But watch out for where these might connect to other groups that you're sharing, e.g. blocks_roles. Although I'm guessing you're keeping roles separate as well, if so no worries, all separate.
If you want the actual themes to be different, then system is separate, and you'll need to enable both modules and themes separately for each site. However if you're just changing the files a bit and you keep the theme names the same under each site's folder rather than under /sites/all you might be able to get away with sharing them and keep your modules in sync - just remember to still enable them explicitly in each site if they have a significant install routine, in case it touches separated tables. . .
If you're really going to use taxonomy for access control, then presumably you'll be separating the term- and vocabulary-tables out, but of course you'll need to think carefully about the intersection(s?) between those and your shared node- tables. This is where some canonical database schema docs would be very helpful. . .
There have been some very interesting posts and howtos about sharing ALL tables (much safer IMO), and using tricks like MySQL Views, overriding variables from your settings.php, or putting filtering by taxonomy into the sites' templates - I'd personally advise checking those out and/or investigating DA for a production site rather than messing with shared tables.
If you do experiment with shared/site-specific tables and find success with a given setup, please come back and post it here (Multi-site Group, not necessarily this topic) with whatever tips and thought's you've collected along the way. . .
And good luck!
There have been some very
I think that ultimately this is the direction I will be taking. As you note above, there are just too many potential landmines with vocabularies, terms, etc.
Nonetheless, thanks for your posts -- very interesting material. best
http://drupal.org/user/34423
Warning! - edit sequences table!
Make sure if you are importing data from even one pre-existing database (not just merging multiple ones together) into your new virgin shared-database setup, that you edit the shared_sequences table.
What got me is that the NAME column's values must now change, from my example:
users_uid ==> shared_users_uid
node_nid ==> shared_node_nid
node_revisions_vid ==> shared_node_revisions_vid
shb1sh_menu_mid ==> shb1sh_menu_mid
If you ARE merging multiple databases, then you need to also increase the numbers to whichever is highest among your source tables.
I should have read this more carefully: http://drupal.org/node/147828
I have not built the sites
I have not built the sites or the DB tables yet. I was merely going to edit the array in settings.php prior to installation. Is that the best way to do it? Or should I set up the subdomains with their own prefixed tables, then at that point go into settings.php for each subdomain and edit the array to the tables I want to share? Thanks for your help.
http://drupal.org/user/34423
Short answer: the latter
Details:
What I did was set up each site in the one shared database, prefixing ALL tables. First one uses the shared_ prefix, this is not necessarily even a real site unless you're setting up a master-slave scenario; delete the site folder and hosts/vhosts entries later.
Then edit the settings.php on the second one (first actual site), setting up which tables are shared and which site-specific.
Don't forget your shared_sequences table as above.
Test everything out as much as you can, then delete the rows not being used. Contrib modules schema, Site Documentation and Devel have proved helpful to me, they not only tell you what's going on but actively give advice. Note Devel sets up two new tables, IMO should be separate.
Yes, that was my instinct,
Yes, that was my instinct, thanks for confirming. I appreciate your assistance, you will help a great number of people by having shared your experience.
http://drupal.org/user/34423
Update
I tried another setup, this time making all the block/nav ones shared:
'blocks' => 'shared_', /* theme layout nav - shared*/'blocks_roles' => 'shared_', /* theme layout nav - shared*/
'boxes' => 'shared_', /* theme layout nav - shared*/
'menu' => 'shared_', /* theme layout nav - shared*/
'system' => 'shared_', /* theme layout nav - shared*/
Remember my use case is testing/playing with a lot of different themes atop a common content base. This worked fine with each theme having the same name/folder under each sites/ folder, but I ran into some problems with those themes that don't follow the standard region naming conventions - basically this only worked when all the regions used are the same in all the themes. The other problem was having a lot of themes all activated really slowed things down whenever I visited an admin/ page related to themes, even timing out sometimes. So I changed system back to prefixed and reverted the theme folder names back to normal ones, only activating the relevant one and the performance issue went away. Downside is I have to manually update all the sites whenever trying a new module.
Note the boxes/blocks tables can stay shared, as their entries are specific to the theme name so they don't step on each other in my use case (site-to-theme is one-to-one). And it's real handy keeping one menu tree for all, I've learned to export the CVS, manipulate/copy in a spreadsheet and import back in when I need more than one version of the same links - they're only activated by the blocks system on a per theme/site basis, so no conflicts.
Current version:
'boxes' => 'shared_', /* theme layout nav - now shared */'blocks' => 'shared_', /* theme layout nav - now shared */
'blocks_roles' => 'shared_', /* theme layout nav - now shared */
'menu' => 'shared_', /* theme layout nav - now shared*/
'system' => 'hhfrmwk_', /* switch back to separate - too many theme?s */
/* 'queue' => '$1', commenting out yet-uncreated and unknown table so it goes to watchout database */
Anybody know what queue's for? Hasn't been created in my test setup so far. . .