Mailing-List: contact user-help@empire-db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@empire-db.apache.org
Received-SPF: neutral (nike.apache.org: local policy)
References: <507F0F5F.90601@j-b-s.de>
 <B297D4737108A0459B66C3A85BB7F90147027E@kn.esteam.de>
In-Reply-To: <B297D4737108A0459B66C3A85BB7F90147027E@kn.esteam.de>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8
Message-Id: <E55A2445-B0B6-4C76-83DA-6514409E1947@j-b-s.de>
Cc: "<user@empire-db.apache.org>" <user@empire-db.apache.org>
From: "mailinglist@j-b-s.de" <mailinglist@j-b-s.de>
Subject: Re: SCHEMA
Date: Fri, 19 Oct 2012 01:24:31 +0200
To: "user@empire-db.apache.org" <user@empire-db.apache.org>

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 a=
nd a schema seems to be used more like a structural/grouping thing. Adding a=
liases, 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 c=
onsistent across different db's. So a table might have a different name or i=
s 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 ha=
rdcoded strings.

Lets now assume a particular table is copied into another schema (call it ba=
ckup or user playground, however), defining the tables in the ctor is no lon=
ger 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 re=
sult 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 e=
xisting 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 DBInsta=
nce 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 tabl=
e is located in a different schema.

What I am essentially looking for is:

MyDb extends DbDatabase {
    MyDb() {
        super();
       =20
        addTable("TableA1", "SchemaA);
        addTable("TableA2", "SchemaACopied");
        addTable("TableB", "SchemaB");
    }
}

that's the only thing I know: it's guaranteed tables exist per system, but n=
either 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 a=
ssume your db env is considerably more stable than mine or I'm using empire w=
rong?

Jens

Von meinem iPad gesendet

Am 18.10.2012 um 22:44 schrieb Rainer D=C3=B6bele <doebele@esteam.de>:

> Hi Jens,
>=20
> here are my anwsers:
>=20
>> from: Jens Breitenstein [mailto:mailinglist@j-b-s.de]
>> to: user@empire-db.apache.org
>> re: SCHEMA
>>=20
>> Hi all!
>>=20
>> 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?).
>=20
> Yep.
>=20
>> This works as expected but unfortunately I can not join tables across
>> schemas, means across empire DBDatabases while it is perfectly working
>> SQL.
>=20
> 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?
>=20
>> 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?
>=20
> 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.
>=20
> Actually I use cross schema joins myself a lot with Oracle, so I doubt the=
re is a problem with Empire-db.
>=20
> Please check your table grants - or simply give the user (or schema) that y=
our connecting with the "IMPORT_FULL_DATABASE" role.
>=20
>>=20
>> If not: a possible workaround might be:
>>=20
>> 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.
>>=20
>> When generating SQL the existing logic has to be changed to something
>> like this:
>>=20
>> String schema =3D null;
>> if (null !=3D table.getSchema()) {
>>     schema =3D table.getSchema()
>> } else {
>>     if (null !=3D database.getSchema()) {
>>         schema =3D database.getSchema()
>>     }
>> }
>>=20
>> // continue with prepending schema before "." + tablename //
>> addFrom()...
>>=20
>=20
> Doesn't make sense to me. In fact the schema name is a property of the Dat=
abase (which is in fact the schema) and not a property of each individual ta=
ble within a database.
>=20
>>=20
>> What you think?
>>=20
>> (Again)
>>=20
>> Jens
>=20