Hi,

Couldn't find information from either documentation or forum (or was confused by my laziness)... And I'm a bit puzzled right now.

During our regular development process on a product, each developper has access to its own schema, containing its own data, so he can tweak schema/content without harming anyone else.
Of course, production and staging environment also have their own schema.
Noone is directly tied to a particular DB repository (or server), just a schema.

Now, we do a complete reverse-engineering process for data classes, involving JDBC configuration and java generation.
It means that the 'schema' information is something only relevant during runtime, and can only read from "local properties" (or parameters).
We also need to access some 'external' (third-parties) tables lying in a separate schema.

This "default-schema" selection (and database selection) is done through a hibernate property-file created by ant.


Many problems arose during the process:

1. We couldn't specify a valid schema-selection. The reveng file must have some schema-selection tags in it, but there is no way to make it use the "default-schema".
Note that we can't use 'all schemas' since some third-parties might be installed multiple times in the same DB (using different schemas) and still be readable by everyone (and sharing the same tables). Again, the accessed schema is defined at runtime.
The only way I found to perform this, is to specify each table "by-hand" using its own <schema-selection> without schema parameter !
For example:
This doesn't work (too many tables found, or no table found):

But this is working:


Of course, the above means that we are not using table-filter since all tables are added "by-hand"

2. All generated hbm.xml files do come with a 'schema' value, meaning that each code generated is tied to a particular schema.
The only way ot override this was to use a home-grown tool to rip-off those schema information.

3. It is not possible to use the <table> tags in the reveng file, since it requires a schema parameter (or it doesn't work, according to own experience and the multiple messages in this forums on this subject), and... we don't know it ! ;)
Of course, the worse thing about this is that we cannot use views at all since we can't tell reverse-engineering what PK to use for that particular view.


Note that it would be possible to "cook" the reveng file at build time, and set all schema values to the right thing, but this is not solving point (2), nor point (1).

What I hear you say between the lines is:

a) your default schema is dependent on who runs the reverse engineering

b) you do need to use schema="something" for the mapping of the tables that are in a "third-party" location (does the third-party location also change ?)

Is the above correct ?



you mean multiple schema's, right ? The default one and the thirdparty schema ?



We could potentially add that support but as of now the following should work:

(I assume you are able to set hibernate.default_schema to the correct value per user since you need that when you run hibernate anyway)



and then do a ant copy/replace where the @..@ token gets replaced with the correct value.



I don't see how you can avoid having to do some build time adjustment of the reveng.xml since you need different values per user as all users can see everything when reverse engineering is performed.

Exactly what I was meaning :) Plus not every developper is connecting to the exact same database server (usually connecting to the one closest to its location).



Well, this is what I was thinking of when saying 'cooking' at the end of the message. And yes, of course, the default-schema is selected correctly in hibernate properties (along with database connection information, C3P0 pool properties, ...).


The default-schema is set to "nicroman". Database is Oracle (case independant), and the schema is correctly defined/setup as "nicroman" in Oracle.
Note that I also tried to put default schema to "NICROMAN" (upper case) instead, but nothing better... schema-selection with nothing returns the same tables, while the "NPO.*" pattern returns nothing at all.



Hmmm... This is weird, since the schema *is* the default schema... Our home-grown ant task is doing just that: remove the schema from xml files whenever it is equal (case independantly) to the default one.
Like above I tried both lower-case and upper-case solutions....

Let me extract a usage example:

Reveng.xml:

Hibernate.properties:

Ant task:

Result hbm file:



Yes, that is for sure... But in our case, property generation could be enough (default-schema). For instance the <schema-selection> tag could have (by default) match-schema set to default-schema. So that, omitting the value anywhere means 'use default'.
However, I first thought that property generation was enough, without going through a complete reveng.xml file adjustement.
Bah... we do have a workaround for now, and we can live with these... But I'm always reluctant of adding home-grown data cooking on top of other tools (needs support, maintenance, ...).

...but there is nothing weird about that kind of "cooking"...



First, Oracle *is* case sensitive when it comes to metadata lookups so if you use pre-beta9 you definilty need to set it to upper case.



Hmmm... This is weird, since the schema *is* the default schema... Our home-grown ant task is doing just that: remove the schema from xml files whenever it is equal (case independantly) to the default one.
Like above I tried both lower-case and upper-case solutions....
[/quote]

so something is wrong...and unittests shows that it at least work when I do it ;)




that schema-selection means find all NPO_INFO tables in all catalogs and all schemas.



if it is default_schema you are trying to set I recommend you use



note the difference ? :)



and what should it put there if the default is not set ? ....i feel the current behavior is consistent (IIF you remember to set the correct property ;)

OMG !

I dont know if I should bump my head in the screen, or simply go to sleep for eons.
I must have seen that property hundreds of time without noticing the slightest error :(

Ok... so... of course, this is working better ...

The 'schema' parameter is not added anymore, and I can select directly table overrides ! Wow... all that fuzz for a stupid char :(
Just wondering why the hell was Hibernate working properly with that mistake !!!

However, this didn't help for the schema-selection part...

Returning hundreds of tables:

Returning nothing at all:

Returning NPO_INFO only




I will try with the latest source during next week.

why should it not ? when it does "select NPO_INFO ..." it will go to the default schema configured by the login to the db...


[/quote]

just try with b9 and if that does not work please report it in jira with sample schema, driver and db version.

Ok... logged it in JIRA (HBX-866).

I tried to monitor actual queries made to the DB, and I find things like:

select ... from all_tables where owner='NICROMAN' and table_name='NPO.*';

Of course this doesn't return anything ! A table_name like 'NPO%' would be better :)

I tested both b8 and b9.
Trying both OracleMetaDataDialect and JDBCMetaDataDialect.

hmm...jdbcmetadatadialect should *definitly* not send that to the db...all our unit tests would fail if that were the case.

OracleMetaDataDialect might, but I would be surprised...i'll look into it.

fixed! note as workaround you can use % in <schema-selection ...> directly.

I am somewhat bitten by this too.

Hibernate Tools 3.2.0.CR1

Oracle9i

<schema-selection match-schema="full_qualified_schema_name" match-table="full_qualified_table_name"/>

works (omitting "match-table" works ....... like .* is supposed to)

<schema-selection match-schema="full_qualified_schema_name" match-table="x"/>

x = *
x = .*
x = *.*
x = %
x = .%
x = %.%

yields nothing at all.
(hibernate default schema is set)

please try with the final release and if it still sends bogus query to the db then report it in jira with info on how to reproduce it. thanks.

Thx .. for some god forsaken reason i was not working with the latest and greatest .... thx that worked :)