xTuple.com xTupleU Blog & News Customer Support

Drupal Integration - PostgreSQL Schema or Database

Hello,

I am working on some Drupal integration with PostBooks and wanted to get some feedback on the database setup options. I have heard that xTuple is using a new schema within the xTuple database. However, I read this post and I’m not sure if it is worth the effort: http://drupal.org/node/45332

What at the benefits of putting Drupal in a Schema vs. a separate database? Drupal can handle multiple database connections (http://drupal.org/node/18429), so that approach seems more straight forward and doesn’t involve any Core hacks.

One benefit I can see for Schemas is not having to call “db_set_active(‘xTuple’);” and “db_set_active(‘default’);” in your module to switch back and forth between the databases. Is this correct?

I think it depends on the goals you want to accomplish with the integration.

Do you want to control drupal user accounts and roles?

Realtime contact/CRM account creation from people signing up on your website?

How realtime do you want it to be- Postgres can’t query across databases on it’s own (AFAIK).

If you have the drupal and xtuple in the same database, and perhaps in separate schemas - you can make your integration more tight by using triggers and views applied/replacing drupal tables (of course this breaks the core drupal somewhat).

Separate databases IMO is the way to go. Bridge whatever other integration with jitterbit or php and cron scripts. Perhaps park your website on a hosted server (outside your LAN) with some replication to a local server (LAN), xTuple client updating drupal info to the hosted server in a one way loop.
Keep your web db separate from your ERP db, and preferably separate servers.

(I’ve got a FR in for making xtuple more ‘schema aware’, which would help things a bit, right now it only plays nicely in the public schema.)