db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lach, Thierry" <Thierry.L...@bbdodetroit.com>
Subject RE: Putting schema into the select clause
Date Mon, 30 Aug 2004 17:09:51 GMT

Comments inline.

-----Original Message-----
From: Thomas Fischer [mailto:Fischer@seitenbau.net]
Sent: Saturday, August 28, 2004 5:32 AM
To: Apache Torque Users List
Subject: RE: Putting schema into the select clause

> Hi,
>
> I believe the simplest way to allow fully qualified table names is to
use schema.tablename as tablename in the
> database schema xml. This would mean that one would use
schemaname.tablename instead of tablename all the time,
> but I do not know a sql statement where this would not work. The
problem with this approach is that internally,
> columns are often referenced by their "fully qualified name", i.e.
"tablename.columnname", and afterwards, the
> separate tablename and columnname are extracted by determining the
position of the dot via
> fullyQualifiedColumnName.indexof("."). For the above approach to work,
these places would have to be replaced by
> fullyQualifiedColumnName.lastIndexof(".").
> Some months ago, somebody has said that he had made lots of changes to
make fully qualified tablenames work, but I
> did not see any patches. Perhaps you should contact him ?

This should not be an issue if the schema is added only at runtime while
creating the sql statement, at the Village level rather than the Torque
level.

>
> More comments inserted below
>
> "Lach, Thierry" <Thierry.Lach@bbdodetroit.com> schrieb am 27.08.2004
> 21:20:57:
>
> >
> > 1.  Make sure the schema is optional.
> >
>
> This is certainly a good idea
>
> > 2.  Make sure that the schema can be overridden at runtime.  We've
got
> > several schema that are identical and it would be nice to be able to

> > do runtime selection of the schema (especially for reporting
purposes)
> > while using a single connection (alternative would be to have
multiple
> > identifiers in torque.properties).
> >
>
> I am not sure how much work this is. At the moment, tablenames cannot
be changed at runtime, so my guess would
> be that the mechanisms to do this would have to be built in from the
beginnings. It would seeem to me that the
> effort and code complexity to achieve this do not justify the
benefits, but of course this is my personal opinion.
>

I'll go one further and throw out the proposal that tablenames should be
able to be changed at runtime.  If the tablename or schema could be
selected at runtime, then the process to consolidate from the multiple
schemas that I mention above becomes something like:

     String[] schema = { ... };
     String[] tablename = { ... };

     for (int i = 0; i < tablebname.length; i++) {
         importData(schema[i], tablename[i]);
     }

Instead of

     importDataSchema1Table1();
     importDataSchema1Table2();
     importDataSchema2Table3();
     importDataSchema2Table4();
     ...
     ...
     ...


Each one of the procedures in the second example would use almost
identical code but would (probably) have to have a specific torque
connection for each different schema, and a different table definition
for each name, so the only way the code could be reused between them
would be via cut/paste, not dynamic configuration.

> > 3.  Suggest you use aliases.
> >
> > So your sql becomes
> >
> > SELECT ALIAS.COLUMN FROM SCHEMA.TABLENAME ALIAS WHERE  ALIAS.COLUMNB
> 5
> >
> >
> > Or
> >
> > SELECT ALIAS.COLUMN FROM SCHEMA.TABLENAME AS ALIAS WHERE
ALIAS.COLUMNB > 5
> >
> >
> > Depending on the required syntax.
>
> I do not see the reason behind this suggestion. This is certainly a
lot of work, but what are the benefits ?
>

The main reason is that all (AFAIK) databases support some type of table
aliasing which is in one of the two formats given above, while the use
of schema.table.column may not be as widely supported.

------<clip>-----

This message and any attachments contain information, which may be confidential or privileged.
 If you are not the intended recipient, please refrain from any disclosure, copying, distribution
or use of this information.  Please be aware that such actions are prohibited.  If you have
received this transmission in error, kindly notify us by calling 1-800-262-4723 or e-mail
to helpdesk@bbdo.com. We appreciate your cooperation.

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message