Forum OpenACS Q&A: two ecommerce installations using the same db

Collapse
1: two ecommerce installations using the same db
Posted by Scott Mc Williams on 12/25/00 04:16 AM
Hello all...
  I'm facing a conundrum and was looking for input. Ideally(but not
realistically), someone has already solved this.
  We have two websites... store.corp.com and www.corp.com. Two
totally different looks and behaviours. www probably won't use much
of the acs functionality, especially in regards to users and user
sessions. www is maily a corporate site.  store, on the other hand,
will be pretty much all ecommerce transactions - with the
accompanying user data. They will both be on the same box using the
same db server, but with separate db's.
  I like the idea of separate db's and would rather not stray from
that. However, while the store site is responsible for selling
products, we'd like the corporate site to display the same products
in a more 'brochure'ish way. It would be Really Neat if we could use
the store's product database on the corporate site without copying it
over at certain intervals.
  I thought that adding another database pool to the corporate site
and using that for all product related queries would work, but i ran
into some problems with joins - such as when it joins the template
table(which would be used in the corporate db) and the categories
table(which would only be used in the store db). It doesn't look like
postgres has the ability to join across databases (select * from
storedb..ec_products p, corpdb..ec_templates t). Is this true?
  Is there any great ideas out there as to how to solve this besides
copying the storedb category/product info over to corpdb or tweaking
the code manually?

  Thanks...
    and have a cheery ramadan

    james shannon (mailto:james@motifstudios.com)
Collapse
2: Response to two ecommerce installations using the same db (response to 1)
Posted by Cynthia Kiser on 12/27/00 07:22 PM
I am a PostGres ignoramous (lurking to learn) but if this were an ACS classic site I would solve it with grants - I would give the corp database user read access on the ec_products table owned by the store database user. Then I could do selects (using corp's normal database pools) by referring to tables like STORE.EC_PRODUCTS. Does PostGres do anything like this?
Collapse
3: Response to two ecommerce installations using the same db (response to 1)
Posted by james shannon on 12/27/00 11:23 PM
cynthia...
  that was actually part of my question ("It doesn't look like postgres ...").  I've tried "db..table"(sybase) and "db.table"(other), but neither seem to work. I perused through the postgresql doc files and couldn't find anything that would help, but i'm not sure exactly what what i'm looking for is called.