Oracle schemas are
equivalent to users. When you login to a database as user jeff, you have default
access to all tables in schema 'JEFF'. You can change your current schema on a
per-session basis.
Thus, if there are
tables JEFF.FOO and PAT.FOO and you login as jeff and execute "select * from
foo", then you will be getting the data from JEFF.FOO. If you ran "ALTER SESSION
SET CURRENT_SCHEMA = 'PAT'" and ran it again, then you'd see the data in
PAT.FOO.
The real trouble
comes in if there's a table named PAT.BAR, and a synonym is created for it with
"CREATE PUBLIC SYNONYM BAR FOR PAT.BAR". Then, if your current_schema was set to
'JEFF' and you ran "select * from bar", then you'd get the data from PAT.BAR.
However, using the JDBC DatabaseMetaData.getColumns(null, 'JEFF', 'BAR', null)
would return nothing. The BAR table is really in schema 'PAT', but there's no
easy way to know that you can access it without using a schema
qualifier without querying Oracle system views to know that a synonym exists,
and having detailed knowledge of how Oracle resolves unqualified table names.
However, you can just let Oracle take care of it for you by using the
ResultSetMetaData API instead.
The problem here is
that I'm not sure how other databases and JDBC drivers will handle this,
especially the "select * from tablename" part. I imagine that's a very expensive
operation on some DBs. It's probably expensive on Oracle, too. I thought about
trying "select * from tablewhere where 1 = 2", but I'm not sure if the
ResultSetMetaData would have any data or not.
-----Original Message-----
From: Jeff Butler [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 27, 2006 2:30 PM
To: [email protected]
Subject: Re: Abator introspection patchSome questions...How does the actual table get selected at run time? Is there a default schema specified on the db connection? Or, are the aliases not in a schema and there is some way to switch the aliases to the proper schema at runtime?Do you actually want to generate the artifacts based on a specific schema, but just not include the schema in the generated XML?Seems like it would be much easier to give you the option to generate against a specific schema, but then not include the schema in the generated code.Jeff Butler
On 9/27/06, [EMAIL PROTECTED] < [EMAIL PROTECTED] > wrote:I've had trouble with Abator's interaction with Oracle schemas. I've implemented and attached a patch to the DatabaseIntrospector that refactors it into a choice of using the DatabaseMetaData API and the ResultSetMetaData API to get information about a table.
The problem is that we have several schemas with the same tables as well as synonyms for a subset of these. Using DatabaseMetaData.getColumns(...) ends up returning data from an arbitrary schema, and in our case, it's never the one we want. The way to get around this is normally to specify a schema name in the config file, but we actually use different schemas in dev, test, and production. What we really want is the table that is found without any qualification.
The best way I know to do this is just run "select * from tablename" and let Oracle deal with the schema and synonym resolution, the examine the ResultSetMetaData. Luckily, the ResultSetMetaData gives all the information Abator actually uses from the DatabaseMetaData, so I've implemented this as a proof-of-concept.
I tried to refactor the code as little as possible, but it was not especially easy.
Let me know what you think. In particular, I'd like to know if we really need to support both techniques. Is there any case where the ResultSetMetaData approach would fail? Should it be the default? Can we remove the DatabaseMetaData.getColumns(...) code entirely?
--Patrick
