empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "mailinglist@j-b-s.de" <mailingl...@j-b-s.de>
Subject Re: SCHEMA
Date Thu, 18 Oct 2012 23:24:31 GMT
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 <doebele@esteam.de>:

> Hi Jens,
> 
> here are my anwsers:
> 
>> from: Jens Breitenstein [mailto:mailinglist@j-b-s.de]
>> to: user@empire-db.apache.org
>> 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
> 

Mime
View raw message