Hi Rainer,
Sorry to bother you guys and thx for always answering quickly.
Unfortunately the database is nothing I can change. It's grown over decades and
a schema seems to be used more like a structural/grouping thing. Adding
aliases, changing user roles/rights is not an option.
Lets assume the database contains
SchemaA, TableA1, TableA2
SchemaB, TableB
we have hundreds of schema definitions in one db and this is sometimes not
consistent across different db's. So a table might have a different name or is
located in a different schema.
Lets further assume only one JDBC url is used than it looks like this
MyDbA extends DbDatabase {
MyDbA() {
super();
setSchema("SchemaA");
addTable("TableA1");
addTable("TableA2");
}
}
and now the trouble starts:
In case "TableA1" has a different name in another system, I am screwed up. I
solved this by making all table names configurable instead of relying on
hardcoded strings.
Lets now assume a particular table is copied into another schema (call it
backup or user playground, however), defining the tables in the ctor is no
longer possible, means I have to change source code to run the same software on
a different env, because schema is part of the database definition. As a result
I need two database instances now to deal with different schemas.
Now source will change to:
MyDbA extends DbDatabase {
MyDbA() {
super();
setSchema("SchemaA");
addTable("TableA1");
// addTable("TableA2") won't work any longer, because table is not
existing in this schema on a different environment
}
}
MyDbACopy extends DbDatabase {
MyDbCopy() {
super();
setSchema("SchemaACopy");
addTable("TableA2"); // hey here it is!
}
}
Because I can't configure the schema name per DbDatabase table a new DBInstance
is required, thus a source change as a consequence is required, too. and only
due to the fact the same software uses a different database and a table is
located in a different schema.
What I am essentially looking for is:
MyDb extends DbDatabase {
MyDb() {
super();
addTable("TableA1", "SchemaA);
addTable("TableA2", "SchemaACopied");
addTable("TableB", "SchemaB");
}
}
that's the only thing I know: it's guaranteed tables exist per system, but
neither name nor schema can be expected to be the same. So I am looking for a
more dynamic approach as long the schema is affected, the table name is not an
issue any longer.
Maybe it helps if you give me a hint how cross joining works in your env. I
assume your db env is considerably more stable than mine or I'm using empire
wrong?
Jens
Von meinem iPad gesendet
Am 18.10.2012 um 22:44 schrieb Rainer Döbele <[email protected]>:
> Hi Jens,
>
> here are my anwsers:
>
>> from: Jens Breitenstein [mailto:[email protected]]
>> to: [email protected]
>> re: SCHEMA
>>
>> Hi all!
>>
>> I am dealing with several hundreds of tables spread over multiple
>> schema's in one database.
>> As "schema" is an attribute on the DBDatabase itself I worked around it
>> by creating several empire DBDatabase objects, each containing the
>> required tables per schema (correct?).
>
> Yep.
>
>> This works as expected but unfortunately I can not join tables across
>> schemas, means across empire DBDatabases while it is perfectly working
>> SQL.
>
> The question is why. If the SQL is correct, then the reason might be, that
> access privileges in your DBMS are wrong.
> Can you post some of your statements?
>
>> I tried to set "schema" attribute to "null" and including the schema in
>> the tables name e.g. SCHEMA.TABLENAME but empire detects the "." as
>> "illegal" character and the name is quoted, thus the final name becomes
>> "SCHEMA.TABLE" which is rejected by oracle (table not found).
>> Did I miss something?
>
> Well, you should not set the schema to null - cause that is what is is for.
> And if it is not working properly we need to fix it.
>
> Actually I use cross schema joins myself a lot with Oracle, so I doubt there
> is a problem with Empire-db.
>
> Please check your table grants - or simply give the user (or schema) that
> your connecting with the "IMPORT_FULL_DATABASE" role.
>
>>
>> If not: a possible workaround might be:
>>
>> Add a (optional) schema attribute on the DBTable. Due to the fact it is
>> a new attribute and two new methods (setter/getter to the attribute)
>> existing code is not affected.
>>
>> When generating SQL the existing logic has to be changed to something
>> like this:
>>
>> String schema = null;
>> if (null != table.getSchema()) {
>> schema = table.getSchema()
>> } else {
>> if (null != database.getSchema()) {
>> schema = database.getSchema()
>> }
>> }
>>
>> // continue with prepending schema before "." + tablename //
>> addFrom()...
>>
>
> Doesn't make sense to me. In fact the schema name is a property of the
> Database (which is in fact the schema) and not a property of each individual
> table within a database.
>
>>
>> What you think?
>>
>> (Again)
>>
>> Jens
>
