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
Comments
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
http://davidhertzberg.com
Drupal user page: http://drupal.org/user/34423
Feedback from satisfied customers: http://drupal.org/node/180284
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
http://davidhertzberg.com
Drupal user page: http://drupal.org/user/34423
Feedback from satisfied customers: http://drupal.org/node/180284
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
http://davidhertzberg.com
Drupal user page: http://drupal.org/user/34423
Feedback from satisfied customers: http://drupal.org/node/180284
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
http://davidhertzberg.com
Drupal user page: http://drupal.org/user/34423
Feedback from satisfied customers: http://drupal.org/node/180284
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. . .
Revisiting
I'm planning on posting updated sample SETTINGS.PHP for this "profile" of table sharing, as I've added a bunch of modules and added their tables as shared.
But in looking at them in more detail, I see the cache tables from Views and CCK (cache_views and cache_content) probably should be (I think) not shared but site-specific.
So that's my question, what do y'all think?
Please let me know if I should have posted this followup to a more appropriate location, I thought the context would be useful rather than re-iterating all the background.
crossposted a more general version of this question
to http://drupal.org/node/318863
Anyone for hire?
Will pay up-front. I pretty much need the settings.php customized for a site I am building.
I would like to share all tables with a few exceptions.
This shouldn't take long for anyone with experience setting this up.
Thanks
Dan
see my reply in the other thread
Might not take long, but neither that, nor the fact that you're willing to pay someone, makes it necessarily possible :)
Actually of course anything is possible with enough money, but I really think regular multi-site isn't the way to go with this.
It might be better for you to post a more detailed statement of what you're trying to accomplish, focusing on the ultimate goals, not your current perception of how to get there. Maybe in the forums?
Can you articulate why you want separate user tables? There may be other factors, but your desire to show separate prices doesn't imply prefixing to me. You might want separate sites, but perhaps they will actually share all data AND user tables, which is pretty straightforward.
PS Don't pay up front ;)
The main reason I would like
The main reason I would like to have a multi-site is for the sake of maintaining one database. Both sites will have the same content. The only different would be the shopping cart prices.
Could this be a possible solution?
Installing Ubercart shopping cart in 2 location.
In this case the shopping cart would be maintained separately on each site. This way I could set different prices.
Can this be done?
This can be tricky
Just as pretty much everything in Drupal Ubercart products are nodes of a certain node type. This node type has a separate database table which contains all the node type specific data, but not all node data. Some of it is stored in nodes table containing data shared by all node types. To have content sharing between sites work properly you would have to share both tables and all the tables for other node types.
What I am trying to say is with multisite configuration you either share all the content or none.
If it's only the prices you want different (with exactly the same products etc) my recommendation is to get a developer to make a fairly simple modification* to ubercart to have an additional column in product table storing second price, or a separate table storing prices for multiple sites (for 3+ sites).
*I have very little knowledge of Ubercart code, so this is an assumption made based on other carts I know. It might be fairly complex, but I doubt it.
My understanding is: no
My understanding is that this is very difficult even for the wizards, and fraught with danger - short answer - don't try.
I'm sure it's not so unusual for an e-commerce site to want to show different prices to different users - in my opinion something based on user role would be ideal, and this functionality has nothing to do with keeping data in different tables.
I would suggest talking to e-commerce people, perhaps in the support forums and/or the Ubercart issue queue.
Again, I would suggest posting specific details on what your end goals are, rather than starting out by proposing a specific type of solution.
AFAIC your pricing requirement has nothing to do with multi-site, and in fact I see no need for you to have more than one site at all unless you want it for branding or legal purposes.
Want to piece together a map
Want to piece together a map like this for Drupal 6? I've been working this out with pen and paper for my multi-site infrastructure for courses. Students / staff and access any site bc of a shared user base but their users_role entry isn't shared so while they'll get an account, they won't get access. Then they'll get pushed to a central site which determines if they have access to individual courses based on sync'ing up with an XML based feed of user accounts.
right now i'm sharing users, roles, authmap, tinymce_settings, tinymce_roles, profile_values, profile_fields
It would be cool if we could come up with a list of what can be safely shared and give some usage examples for D6...if you're interested.
"Plaguing the world with Drupal; One Plone, Moodle, Wordpress, Joomla user at a time since 2005." ~ btopro
http://elearning.psu.edu/
http://elearning.psu.edu/projects/
http://elearning.psu.edu/drupalineducation/
Ex Uno Plures
http://elmsln.org/
http://btopro.com/
http://drupal.psu.edu/
nm, another thread
Seems this already exists. It links over here but i'll backlink now:
http://groups.drupal.org/node/15601
"Plaguing the world with Drupal; One Plone, Moodle, Wordpress, Joomla user at a time since 2005." ~ btopro
http://elearning.psu.edu/
http://elearning.psu.edu/projects/
http://elearning.psu.edu/drupalineducation/
Ex Uno Plures
http://elmsln.org/
http://btopro.com/
http://drupal.psu.edu/