My reply on the thread....
-------- Original Message --------
Subject: Re: [jpa-spec users] [jsr338-experts] Re: JPA schema generation
Date: Mon, 21 May 2012 13:01:43 -0700
From: Linda DeMichiel <linda.demichiel_at_oracle.com>
Organization: Oracle Corporation
To: struberg_at_yahoo.de
Hi Mark,
On 5/21/2012 12:18 PM, struberg_at_yahoo.de wrote:
> Hi lords and ladies!
>
> I originally replied to this thread on 9th already, but it seems it
> didn't get through. Trying again via the webmail.
Thanks for your perseverence -- this is helpful.
Your email went only to me though. Are you a member of the jpa-spec
project? If you are, you should be able to post to the users_at_jpa-spec.java.net
email list.
> ---------------
>
> I basically share the same sentiment regarding automated schema
> creation.
>
>
> Maybe I'm understanding your approach the wrong way, so I better sync
> up:
>
> * You like to have the EE container create the schema at deploy time or
> runtime if you add a new tenant?
> * For production as well? Or only for tests?
>
It depends on the app. Please see below.
> I hope I got this wrong, because I've never seen a big project where
> you do _not_ need one of the following
> * additional tables for non-jpa use (e.g. accessed via nativeQuery or
> even JDBC)
> * additional indices for performance tuning
> * sometimes even non-portable indices (Oracle Text Search)
> * doing manual statistic runs or enabling automatically scheduled
> statistic runs
> * setting up tons of other stuff: triggers, stored procedures, db
> replication to other nodes, etc
I'm not going to disagree with you on any of the above
> * where do you get the required db privileges from?
>
There are 2 distinct cases here in my view:
(1) the application (aka JPA provider) "owns" the database -- this
corresponds to the separate-database provisioning case, where the tenant
is allocated a database rather than just a schema in a database. In this case,
the platform provider hands the JPA provider a datasource that has been properly
configured for the provider to create schemas and tables in the database.
(2) the application (JPA provider) owns only a schema in the database,
and receives a datasource that restricts access to that schema. In this
case, the provider will be restricted to creating tables within that
schema.
>
> In the projects *I* know, you most times cannot even use the
> auto-generated stuff for your testing db. This feature is imo really
> only good for small samples which use in-memory derby or hsqldb. Of
> course, the generated schema is the base (and really good), but most
> times you need many manual steps in addition.
>
Agree
> So, did I get the intention wrong?
>
I think so. At least we seem to be in agreement AFAICT.
In more complex scenarios and for production apps, I'm expecting schema
generation to be a multi-phase process. Perhaps there in an initial
schema generated by the JPA provider (and output as DDL) which is then
tuned by a database expert (eg DBA) to the needs of the application.
That resulting schema might then be submitted as DDL packaged with the
application. Assuming the target database type is known (which I am assuming
it is in such cases), that DDL might be used to produce database-specific
artifacts such as you suggest above.
>
> Regarding the Indexes. I'd rather have a typesafe @Index nnotation
> which allows for own typesafe index groups.
> Just a very first idea:
>
> public @interface Index {
> int value() default Integer.MAX_VALUE; // for ordering the index if
> no group annotation is used
> boolean unique() default false;
> // add asc, desc etc and other stuff as you like
> }
>
Not sure I'm following. I was proposing that if the index included multiple
columns that the @Indexes annotation would need to be applied to the class.
>
> @Entity
> public class MyUser {
>
> // @Index meta-information to show that this is an index group
> private @Index(unique=true)@interface LoginId {
> int value() default Integer.MAX_VALUE; // mandatory for @Index
> groups: for ordering inside the grp
> }
> private @Index_at_interface CarLookup {
> // if no int value() is specified in an index, the order is
> undefined
> }
>
> @LoginId(1);
> private String tenant;
>
> @LoginId(2);
> private String userLogin;
>
> @CarLookup
> private String carVendor;
>
> @CarLookup
> private String carType;
> }
>
>
> The
> @Index an groups should also be respected when sorting the query
> criterias. For older DBs you still sometimes need unnecessary
> nativeQueries just to get the order right to match the data (first
> query
> criteria should let you get rid of most data). Not sure if this is
> still true nowadays with the latest DBs but I think even Oracle10 had
> problems with queries which didnt fit the order of the index.
>
Please explain further. A good query optimizer should consider the indexes.
(But that doesn't *guarantee* that the result would be in that order)
>
> Maybe
> for logicalKeys we could do something similar to EmbeddedId for
> @NaturalId. Automatically generating a unique index for those fields
> and
> making them usable in em.find(MyEntity, mylogicalKey) ...
>
Beyond a certain point I start worrying about too many indexes being
added - and that being counterproductive.
> More about it in JPA_SPEC-22
>
>
>
> txs and LieGrue,
> strub
>
>
> PS:
> hello everybody and thanks for the hard work on JPA! I'm some random
> old jerk who started writing DB frameworks some decades ago on the
> mainframe (in C on OS/360), and use/write db stuff in Java since late
> 90s or so.
OS/360? Wow.
> PPS: plz excuse me if I miss something obvious...
I don't think you have. Thanks again for taking the trouble to write this up.
-Linda