You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by Pa...@cexp.com on 2006/09/27 21:31:24 UTC
Abator introspection patch
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
Re: Abator introspection patch
Posted by Jeff Butler <je...@gmail.com>.
Hi Patrick,
I've commited an enhancement for Abator to SVN for this issue. You can
check the source out from SVN and build it to test in your environment.
Following the example we've been discussing, this configuration generates
objects properly for the public synonym "FRED" that points to "HR.EMPLOYEES
":
<table schema="HR" tableName="EMPLOYEES">
<property name="ignoreQualifiersAtRuntime" value="true" />
<property name="runtimeTableName" value="FRED" />
</table>
There's also a new page in the Abator documentation for Oracle that
describes this enhancement. Let me know how this works for you.
Jeff Butler
On 9/28/06, Patrick.Higgins@cexp.com <Pa...@cexp.com> wrote:
>
> I'm using plain abator.
>
> -----Original Message-----
> *From:* Jeff Butler [mailto:jeffgbutler@gmail.com]
> *Sent:* Thursday, September 28, 2006 3:55 PM
> *To:* dev@ibatis.apache.org
> *Subject:* Re: Abator introspection patch
>
> Sure - I understand you'll have to restrict Abator to a specific schema -
> but my enhancement would allow you to tell Abator not to include the schema
> in its generated SQL.
>
> I think the way to deal with synonyms would be to generate against the
> table the synonym pointed to - but make abator write the code to interact
> with the synonym rather than the specific table. This would also be
> possible with my proposed enhancement. So if you have synonym FRED pointing
> to HR.EMPLOYEES, you could generate against HR.EMPLOYEES, but have abator
> write the SQL to FRED.
>
> Are you using the eclipse plugin or plain abator? If you're using plain
> abator, then I'll get something committed in a day or two and you could try
> it.
>
> Jeff Butler
>
>
>
>
RE: Abator introspection patch
Posted by Pa...@cexp.com.
I'm using plain abator.
-----Original Message-----
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Thursday, September 28, 2006 3:55 PM
To: dev@ibatis.apache.org
Subject: Re: Abator introspection patch
Sure - I understand you'll have to restrict Abator to a specific schema - but my enhancement would allow you to tell Abator not to include the schema in its generated SQL.
I think the way to deal with synonyms would be to generate against the table the synonym pointed to - but make abator write the code to interact with the synonym rather than the specific table. This would also be possible with my proposed enhancement. So if you have synonym FRED pointing to HR.EMPLOYEES, you could generate against HR.EMPLOYEES, but have abator write the SQL to FRED.
Are you using the eclipse plugin or plain abator? If you're using plain abator, then I'll get something committed in a day or two and you could try it.
Jeff Butler
On 9/28/06, Patrick.Higgins@cexp.com < Patrick.Higgins@cexp.com > wrote:
We have to restrict the search at generation time to a specific schema so we don't pick up definitions from other people's tables. If you do that, you don't see tables from other schemas, even if there's a public synonym for that table. At least, that's the behavior I see with the 10.2.0 driver against a 9.2.0.6 <http://9.2.0.6/> database.
I do realize that we need to generate against a specific schema and run without one. That capability would be very helpful. At present, I don't see any way to deal with the synonyms, especially if keys on them can't be detected. We might need to code a special Oracle introspector to look at the system views and apply the unqualified schema resolution rules. Ugh.
-----Original Message-----
From: Jeff Butler [mailto: <ma...@gmail.com> jeffgbutler@gmail.com]
Sent: Thursday, September 28, 2006 3:32 PM
To: dev@ibatis.apache.org
Subject: Re: Abator introspection patch
Not so...FRED is a public synonym (not in any schema). It's just Oracle JDBC wierdness with synonyms. I saw all kinds of wierd things in my testing.
Maybe you misunderstood my proposed fix...I'm proposing a way for you to GENERATE against a specific schema, but RUN without any schema specified. This means that the default schema (or synonym) for the user would be used.
I really think this would resolve the issue. I'm not proposing that you change anything about how your database is defined.
Jeff Butler
On 9/28/06, Patrick.Higgins@cexp.com < Patrick.Higgins@cexp.com > wrote:
The includeSynonyms trick does not work with public synonyms for tables in other schemas. Your example is just an alias for a table in the same schema (HR.EMPLOYEES and HR.FRED are both in schema HR). We create synonyms only to reference tables in other schemas as though they were in our schema.
We might be able to get by without synonyms, but not specifying a schema will simply not work, because we have 8 schemas that all have the same table names. Currently, Abator just uses the first table it finds, which doesn't correspond to the user's default schema. We don't want to hardcode the schema name into the abator configs because each developer uses a different one. We might be able to use an entity reference to store these externally, but that brings up another problem I found with Abator that may have already been addressed in SVN, but the entity resolver throws an exception rather than returning null for entities it doesn't recognize, so you can't define your own entities in the DOCTYPE.
Re: Abator introspection patch
Posted by Jeff Butler <je...@gmail.com>.
Sure - I understand you'll have to restrict Abator to a specific schema -
but my enhancement would allow you to tell Abator not to include the schema
in its generated SQL.
I think the way to deal with synonyms would be to generate against the table
the synonym pointed to - but make abator write the code to interact with the
synonym rather than the specific table. This would also be possible with my
proposed enhancement. So if you have synonym FRED pointing to HR.EMPLOYEES,
you could generate against HR.EMPLOYEES, but have abator write the SQL to
FRED.
Are you using the eclipse plugin or plain abator? If you're using plain
abator, then I'll get something committed in a day or two and you could try
it.
Jeff Butler
On 9/28/06, Patrick.Higgins@cexp.com <Pa...@cexp.com> wrote:
>
> We have to restrict the search at generation time to a specific schema so
> we don't pick up definitions from other people's tables. If you do that, you
> don't see tables from other schemas, even if there's a public synonym for
> that table. At least, that's the behavior I see with the 10.2.0 driver
> against a 9.2.0.6 database.
>
> I do realize that we need to generate against a specific schema and run
> without one. That capability would be very helpful. At present, I don't see
> any way to deal with the synonyms, especially if keys on them can't be
> detected. We might need to code a special Oracle introspector to look at the
> system views and apply the unqualified schema resolution rules. Ugh.
>
> -----Original Message-----
> *From:* Jeff Butler [mailto:jeffgbutler@gmail.com]
> *Sent:* Thursday, September 28, 2006 3:32 PM
> *To:* dev@ibatis.apache.org
> *Subject:* Re: Abator introspection patch
>
> Not so...FRED is a public synonym (not in any schema). It's just Oracle
> JDBC wierdness with synonyms. I saw all kinds of wierd things in my
> testing.
>
> Maybe you misunderstood my proposed fix...I'm proposing a way for you to
> GENERATE against a specific schema, but RUN without any schema specified.
> This means that the default schema (or synonym) for the user would be used.
>
> I really think this would resolve the issue. I'm not proposing that you
> change anything about how your database is defined.
>
> Jeff Butler
>
>
>
> On 9/28/06, Patrick.Higgins@cexp.com <Patrick.Higgins@cexp.com > wrote:
> >
> > The includeSynonyms trick does not work with public synonyms for tables
> > in other schemas. Your example is just an alias for a table in the same
> > schema (HR.EMPLOYEES and HR.FRED are both in schema HR). We create
> > synonyms only to reference tables in other schemas as though they were in
> > our schema.
> >
> > We might be able to get by without synonyms, but not specifying a schema
> > will simply not work, because we have 8 schemas that all have the same table
> > names. Currently, Abator just uses the first table it finds, which doesn't
> > correspond to the user's default schema. We don't want to hardcode the
> > schema name into the abator configs because each developer uses a different
> > one. We might be able to use an entity reference to store these externally,
> > but that brings up another problem I found with Abator that may have already
> > been addressed in SVN, but the entity resolver throws an exception rather
> > than returning null for entities it doesn't recognize, so you can't define
> > your own entities in the DOCTYPE.
> >
> >
> >
> >
> >
RE: Abator introspection patch
Posted by Pa...@cexp.com.
We have to restrict the search at generation time to a specific schema so we don't pick up definitions from other people's tables. If you do that, you don't see tables from other schemas, even if there's a public synonym for that table. At least, that's the behavior I see with the 10.2.0 driver against a 9.2.0.6 database.
I do realize that we need to generate against a specific schema and run without one. That capability would be very helpful. At present, I don't see any way to deal with the synonyms, especially if keys on them can't be detected. We might need to code a special Oracle introspector to look at the system views and apply the unqualified schema resolution rules. Ugh.
-----Original Message-----
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Thursday, September 28, 2006 3:32 PM
To: dev@ibatis.apache.org
Subject: Re: Abator introspection patch
Not so...FRED is a public synonym (not in any schema). It's just Oracle JDBC wierdness with synonyms. I saw all kinds of wierd things in my testing.
Maybe you misunderstood my proposed fix...I'm proposing a way for you to GENERATE against a specific schema, but RUN without any schema specified. This means that the default schema (or synonym) for the user would be used.
I really think this would resolve the issue. I'm not proposing that you change anything about how your database is defined.
Jeff Butler
On 9/28/06, Patrick.Higgins@cexp.com < Patrick.Higgins@cexp.com > wrote:
The includeSynonyms trick does not work with public synonyms for tables in other schemas. Your example is just an alias for a table in the same schema (HR.EMPLOYEES and HR.FRED are both in schema HR). We create synonyms only to reference tables in other schemas as though they were in our schema.
We might be able to get by without synonyms, but not specifying a schema will simply not work, because we have 8 schemas that all have the same table names. Currently, Abator just uses the first table it finds, which doesn't correspond to the user's default schema. We don't want to hardcode the schema name into the abator configs because each developer uses a different one. We might be able to use an entity reference to store these externally, but that brings up another problem I found with Abator that may have already been addressed in SVN, but the entity resolver throws an exception rather than returning null for entities it doesn't recognize, so you can't define your own entities in the DOCTYPE.
Re: Abator introspection patch
Posted by Jeff Butler <je...@gmail.com>.
Not so...FRED is a public synonym (not in any schema). It's just Oracle
JDBC wierdness with synonyms. I saw all kinds of wierd things in my
testing.
Maybe you misunderstood my proposed fix...I'm proposing a way for you to
GENERATE against a specific schema, but RUN without any schema specified.
This means that the default schema (or synonym) for the user would be used.
I really think this would resolve the issue. I'm not proposing that you
change anything about how your database is defined.
Jeff Butler
On 9/28/06, Patrick.Higgins@cexp.com <Pa...@cexp.com> wrote:
>
> The includeSynonyms trick does not work with public synonyms for tables
> in other schemas. Your example is just an alias for a table in the same
> schema (HR.EMPLOYEES and HR.FRED are both in schema HR). We create
> synonyms only to reference tables in other schemas as though they were in
> our schema.
>
> We might be able to get by without synonyms, but not specifying a schema
> will simply not work, because we have 8 schemas that all have the same table
> names. Currently, Abator just uses the first table it finds, which doesn't
> correspond to the user's default schema. We don't want to hardcode the
> schema name into the abator configs because each developer uses a different
> one. We might be able to use an entity reference to store these externally,
> but that brings up another problem I found with Abator that may have already
> been addressed in SVN, but the entity resolver throws an exception rather
> than returning null for entities it doesn't recognize, so you can't define
> your own entities in the DOCTYPE.
>
>
>
>
>
RE: Abator introspection patch
Posted by Pa...@cexp.com.
The includeSynonyms trick does not work with public synonyms for tables in other schemas. Your example is just an alias for a table in the same schema (HR.EMPLOYEES and HR.FRED are both in schema HR). We create synonyms only to reference tables in other schemas as though they were in our schema.
We might be able to get by without synonyms, but not specifying a schema will simply not work, because we have 8 schemas that all have the same table names. Currently, Abator just uses the first table it finds, which doesn't correspond to the user's default schema. We don't want to hardcode the schema name into the abator configs because each developer uses a different one. We might be able to use an entity reference to store these externally, but that brings up another problem I found with Abator that may have already been addressed in SVN, but the entity resolver throws an exception rather than returning null for entities it doesn't recognize, so you can't define your own entities in the DOCTYPE.
-----Original Message-----
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Thursday, September 28, 2006 11:02 AM
To: dev@ibatis.apache.org
Subject: Re: Abator introspection patch
Never mind - I installed Oracle myself and did some testing. Here's what I found...
getColumns() won't return data from synonyms unless the "includeSynonyms" property is set to true on the DB connection. Even then, getColumns() will return the schema of the target table. For example, I created a public synonym "FRED" for " HR.EMPLOYEES". getColumns() returns the columns as if they are in table "HR.FRED".
This means that you can use abator AS IS to generate objects from synonyms. Set the property "includeSynonyms" to "true" on the abator connection configuration, specify the synonym as the table name, and don't include any schema information.
But...no amount of coaxing will persuade Oracle to return primaryKey or index information from a table synonym, so the generated objects will likely be incomplete (BTW - the "select *" method doesn't help here either).
Bottom line - generating objects from Oracle synonyms is problematical at best.
One thing that would help (and would be a good addition to abator) is the ability to ignore the schema qualifier in generated objects and change the table name. I'm thinking this:
<table tableName="EMPLOYEES" schema="HR">
<property name="ignoreQualifiers" value="true"/>
<property name="tableSynonym" value="FRED"/>
</table>
This would generate objects based on HR.EMPLOYEES, but the SQL would write queries to FRED.
Thoughts???
Jeff Butler
On 9/28/06, Patrick.Higgins@cexp.com < Patrick.Higgins@cexp.com > wrote:
I'll have to experiment some more with the JDBC metadata APIs to find out how they respond. I'll see if I can get to that this afternoon.
Re: Abator introspection patch
Posted by Jeff Butler <je...@gmail.com>.
Never mind - I installed Oracle myself and did some testing. Here's what I
found...
getColumns() won't return data from synonyms unless the "includeSynonyms"
property is set to true on the DB connection. Even then, getColumns() will
return the schema of the target table. For example, I created a public
synonym "FRED" for "HR.EMPLOYEES". getColumns() returns the columns as if
they are in table "HR.FRED".
This means that you can use abator AS IS to generate objects from synonyms.
Set the property "includeSynonyms" to "true" on the abator connection
configuration, specify the synonym as the table name, and don't include any
schema information.
But...no amount of coaxing will persuade Oracle to return primaryKey or
index information from a table synonym, so the generated objects will likely
be incomplete (BTW - the "select *" method doesn't help here either).
Bottom line - generating objects from Oracle synonyms is problematical at
best.
One thing that would help (and would be a good addition to abator) is the
ability to ignore the schema qualifier in generated objects and change the
table name. I'm thinking this:
<table tableName="EMPLOYEES" schema="HR">
<property name="ignoreQualifiers" value="true"/>
<property name="tableSynonym" value="FRED"/>
</table>
This would generate objects based on HR.EMPLOYEES, but the SQL would write
queries to FRED.
Thoughts???
Jeff Butler
On 9/28/06, Patrick.Higgins@cexp.com <Pa...@cexp.com> wrote:
>
> I'll have to experiment some more with the JDBC metadata APIs to find out
> how they respond. I'll see if I can get to that this afternoon.
>
>
>
>
>
RE: Abator introspection patch
Posted by Pa...@cexp.com.
I'll have to experiment some more with the JDBC metadata APIs to find out how they respond. I'll see if I can get to that this afternoon.
-----Original Message-----
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Wednesday, September 27, 2006 9:50 PM
To: dev@ibatis.apache.org
Subject: Re: Abator introspection patch
Following your example...
I'm assuming DatabaseMetaData.getColumns(null, null, 'BAR', null) would return columns for both the public alias BAR and PAT.BAR.
However, could you try DatabaseMetaData.getColumns(null, '', 'BAR', null)? I'm wondering if only columns for the public alias would be returned in this case.
If so, this could be an easy change to Abator. Currently, Abator does not distinguish between an empty string and null for schema/catalog - but the spec does make a distinction here.
If this works, we could change Abator to allow this:
<table tableName="BAR" schema="" />
To mean a different thing than this:
<table tableName="BAR" />
Jeff Butler
On 9/27/06, Patrick.Higgins@cexp.com < Patrick.Higgins@cexp.com > wrote:
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: <ma...@gmail.com> jeffgbutler@gmail.com]
Sent: Wednesday, September 27, 2006 2:30 PM
To: dev@ibatis.apache.org <ma...@ibatis.apache.org>
Subject: Re: Abator introspection patch
Some 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, Patrick.Higgins@cexp.com < Patrick.Higgins@cexp.com > 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
Re: Abator introspection patch
Posted by Jeff Butler <je...@gmail.com>.
Following your example...
I'm assuming DatabaseMetaData.getColumns(null, null, 'BAR', null) would
return columns for both the public alias BAR and PAT.BAR.
However, could you try DatabaseMetaData.getColumns(null, '', 'BAR', null)?
I'm wondering if only columns for the public alias would be returned in this
case.
If so, this could be an easy change to Abator. Currently, Abator does not
distinguish between an empty string and null for schema/catalog - but the
spec does make a distinction here.
If this works, we could change Abator to allow this:
<table tableName="BAR" schema="" />
To mean a different thing than this:
<table tableName="BAR" />
Jeff Butler
On 9/27/06, Patrick.Higgins@cexp.com <Pa...@cexp.com> wrote:
>
> 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:jeffgbutler@gmail.com]
> *Sent:* Wednesday, September 27, 2006 2:30 PM
> *To:* dev@ibatis.apache.org
> *Subject:* Re: Abator introspection patch
>
> Some 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, Patrick.Higgins@cexp.com < Patrick.Higgins@cexp.com > 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
> >
> >
> >
>
RE: Abator introspection patch
Posted by Pa...@cexp.com.
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:jeffgbutler@gmail.com]
Sent: Wednesday, September 27, 2006 2:30 PM
To: dev@ibatis.apache.org
Subject: Re: Abator introspection patch
Some 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, Patrick.Higgins@cexp.com < Patrick.Higgins@cexp.com > 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
Re: Abator introspection patch
Posted by Jeff Butler <je...@gmail.com>.
Some 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, Patrick.Higgins@cexp.com <Patrick.Higgins@cexp.com > 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
>
>
>