2 Replies Latest reply on Apr 6, 2004 8:07 PM by jlrobins_socialserve

    JBossCMP and multiple database schemas (using postgres) ?

    jlrobins_socialserve

      Greetings,

      Is it possible to have JBossCMP properly use multiple database schemas within a single datasource?

      We have a medium sized EJB application (~50 beans, many table-relations) split into roughly three segments -- common beans, webapplication #1-specific beans, and webapplication #2-specific beans. We had the 'great' idea of attempting to persist webapp #1's beans into one postgresql schema space, and #2's into another, with the common elements populating the default public schema. This would have our tables roughly follow our java package hierarchy, and reduce the table-bloat in any one schema.

      Seeing no direct XML tags supporting database schemas, we tried specifying the fully qualified table name as the <table-name> value in the jbosscmp-jdbc.xml file. This seems to hit bump after bump with the code enabled by also specifying <create-table>true</create-table> (given "agency.person" as fully qualified table name):

      1) Named primary and foreign keys for said table get named with pattern "pk_agency.person", yeilding an invalid schema name. Good fix would be to *append*, not prepend, "_pk" to the tablename yeilding the constraint name. Our quick fix was to adjust the Postgresql datasource mapping to yeild nameless constraints (ignore ?1 in the constraint generation template).

      2) Use of DatabaseMetaData.getTables() to sniff out if the table exists before creation fails. Seems that JBoss is most likely passing in "" as the schema name, and "agency.person" as the table name (reasonable, if JBoss is schema-naive). A proper fix would most likely have JBossCMP sniff the table name to see if it contains any periods, and, if so, then split on the period and use the left hand side as the schema name (bailing if more than one period is found, yada yada yada). Ugly fix would be to do the same vodoo inside the JDBC driver (using postgresql, we have that capability).

      Has anyone ever successfully gotten up and running using a similar deployment scenario?